<?php

namespace WMS\Services;

use WMS\Core\Database;
use WMS\Repositories\AgendaRepository;
use WMS\Repositories\ItemRepository;
use WMS\Repositories\BatchRepository;
use WMS\Repositories\BinRepository;
use WMS\Repositories\StockRepository;
use WMS\Repositories\StockMovementRepository;
use WMS\Integrations\Sap\SapServiceLayerClient;
use WMS\Services\AuditService;

/**
 * Servicio de Agenda de Recepcion.
 *
 * Flujo:
 * 1. Crear agenda (fecha planificada, almacen)
 * 2. Asociar OCs a la agenda (fetch de SAP via Service Layer)
 * 3. Abrir agenda para recepcion fisica
 * 4. Operadores escanean productos a ciegas (codigo barras, qty, lote, vencimiento, lote proveedor)
 * 5. Comparar escaneado vs esperado (mostrar diferencias)
 * 6. Cerrar agenda -> crear Goods Receipt en SAP (PurchaseDeliveryNotes)
 */
class AgendaService
{
    private AgendaRepository $agendaRepo;
    private ItemRepository $itemRepo;
    private BatchRepository $batchRepo;
    private BinRepository $binRepo;
    private StockRepository $stockRepo;
    private StockMovementRepository $movementRepo;

    public function __construct()
    {
        $this->agendaRepo   = new AgendaRepository();
        $this->itemRepo     = new ItemRepository();
        $this->batchRepo    = new BatchRepository();
        $this->binRepo      = new BinRepository();
        $this->stockRepo    = new StockRepository();
        $this->movementRepo = new StockMovementRepository();
    }

    /**
     * Crea una nueva agenda de recepcion.
     */
    public function createAgenda(array $data, int $userId): array
    {
        $agendaNumber = Database::nextDocNumber('AGENDA');

        $agendaId = $this->agendaRepo->insert([
            'agenda_number' => $agendaNumber,
            'warehouse_id'  => $data['warehouse_id'],
            'status'        => 'DRAFT',
            'planned_date'  => $data['planned_date'],
            'notes'         => $data['notes'] ?? null,
            'created_by'    => $userId,
        ]);

        AuditService::log('reception_agendas', $agendaId, 'AGENDA_CREATE', null, [
            'agenda_number' => $agendaNumber,
            'warehouse_id'  => $data['warehouse_id'],
            'planned_date'  => $data['planned_date'],
            'notes'         => $data['notes'] ?? null,
        ], $userId);

        return $this->agendaRepo->getWithDetails($agendaId);
    }

    /**
     * Importa una OC de SAP creando agenda + referencia + asignación automática
     * al usuario logueado si su rol es op_recepcion.
     */
    public function importFromSapPurchaseOrder(array $data, int $userId, ?string $userRole = null): array
    {
        if (empty($data['sap_doc_entry']) && empty($data['sap_doc_num'])) {
            throw new \RuntimeException('Debe enviar sap_doc_entry o sap_doc_num');
        }
        if (empty($data['warehouse_id'])) {
            throw new \RuntimeException('warehouse_id requerido');
        }

        // 1. Crear agenda DRAFT
        $agenda = $this->createAgenda([
            'warehouse_id' => $data['warehouse_id'],
            'planned_date' => $data['planned_date'] ?? date('Y-m-d'),
            'notes'        => $data['notes'] ?? ('Auto-creada desde OC SAP #' . ($data['sap_doc_num'] ?? $data['sap_doc_entry'])),
        ], $userId);

        $agendaId = (int) $agenda['id'];

        // 2. Asociar la OC
        try {
            $this->addReference($agendaId, [
                'sap_doc_type'  => 'PurchaseOrder',
                'sap_doc_entry' => $data['sap_doc_entry'] ?? null,
                'sap_doc_num'   => $data['sap_doc_num'] ?? null,
            ], $userId);
        } catch (\Throwable $e) {
            // Si falla addReference, dejar la agenda DRAFT vacía pero propagar el error
            throw new \RuntimeException('Agenda creada pero falló la asociación de la OC: ' . $e->getMessage());
        }

        // 3. Asignación automática:
        //    a) Si el importador tiene rol op_recepcion → él mismo
        //    b) Si no, buscar un op_recepcion ACTIVO (last_activity_at en últimos 15 min)
        //       con menor cantidad de agendas activas (DRAFT/OPEN) asignadas (balanceo de carga)
        $role = strtolower(trim((string)$userRole));
        $assignTo = null;
        $assignReason = null;

        if ($role === 'op_recepcion') {
            $assignTo = $userId;
            $assignReason = 'Auto-asignación: importador es op_recepcion';
        } else {
            $db = Database::getConnection();
            $stmt = $db->prepare(
                "SELECT u.id, u.username, u.full_name, u.last_activity_at,
                        (SELECT COUNT(*) FROM reception_agendas a
                         WHERE a.assigned_to = u.id AND a.status IN ('DRAFT','OPEN')) AS active_load
                 FROM users u
                 WHERE u.role = 'op_recepcion'
                   AND u.is_active = 1
                   AND u.last_activity_at IS NOT NULL
                   AND u.last_activity_at >= (NOW() - INTERVAL 15 MINUTE)
                 ORDER BY active_load ASC, u.last_activity_at DESC
                 LIMIT 1"
            );
            $stmt->execute();
            $candidate = $stmt->fetch();
            if ($candidate) {
                $assignTo = (int) $candidate['id'];
                $assignReason = 'Auto-asignación: op_recepcion online con menor carga (' . $candidate['username'] . ', ' . $candidate['active_load'] . ' agendas activas)';
            }
        }

        if ($assignTo) {
            $this->assignAgenda($agendaId, $assignTo, $userId, $assignReason);
        }

        $result = $this->agendaRepo->getWithDetails($agendaId);
        $result['_auto_assign_reason'] = $assignReason;
        return $result;
    }

    /**
     * Asocia una Orden de Compra (OC) de SAP a la agenda.
     * Obtiene la OC via Service Layer y extrae sus lineas.
     */
    public function addReference(int $agendaId, array $data, int $userId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if ($agenda['status'] !== 'DRAFT') {
            throw new \RuntimeException('Solo se pueden agregar referencias a agendas en estado DRAFT');
        }

        $sapDocEntry = isset($data['sap_doc_entry']) ? (int)$data['sap_doc_entry'] : null;

        // Obtener OC de SAP
        $sap = new SapServiceLayerClient();
        // Resolve DocNum → DocEntry if needed
        if (empty($sapDocEntry) && !empty($data['sap_doc_num'])) {
            $docNum = (int)$data['sap_doc_num'];
            $qr = $sap->get("/PurchaseOrders?\$filter=DocNum eq {$docNum}&\$select=DocEntry,DocNum&\$top=1");
            if (empty($qr['value'])) {
                throw new \RuntimeException("OC con Nro. {$docNum} no encontrada en SAP");
            }
            $sapDocEntry = (int)$qr['value'][0]['DocEntry'];
        }
        if (empty($sapDocEntry)) {
            throw new \RuntimeException("Debe ingresar un Nro. de OC valido");
        }

        $oc = $sap->get("/PurchaseOrders({$sapDocEntry})");

        if (empty($oc['DocEntry'])) {
            throw new \RuntimeException("No se pudo obtener la OC #{$sapDocEntry} de SAP");
        }

        // Verificar duplicado (movido aquí para tener $sapDocEntry resuelto)
        $db = Database::getConnection();
        $stmt = $db->prepare(
            "SELECT id FROM reception_agenda_refs
             WHERE agenda_id = ? AND sap_doc_entry = ? AND sap_doc_type = 'PurchaseOrder'"
        );
        $stmt->execute([$agendaId, $sapDocEntry]);
        if ($stmt->fetch()) {
            throw new \RuntimeException("La OC #{$sapDocEntry} ya esta asociada a esta agenda");
        }

        $db->beginTransaction();

        try {
            // Insertar referencia
            $stmtRef = $db->prepare(
                "INSERT INTO reception_agenda_refs
                 (agenda_id, sap_doc_type, sap_doc_entry, sap_doc_num, ref_type, supplier_code, supplier_name, bpl_id, status)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );

            // Determinar tipo de referencia segun tipo de OC
            $refType = 'OC_PLAZA'; // default
            if (!empty($oc['U_TipoOC'])) {
                $refType = match ($oc['U_TipoOC']) {
                    'IMP'  => 'OC_IMPORTACION',
                    'PROD' => 'OT_PRODUCCION',
                    default => 'OC_PLAZA',
                };
            }

            // BPLId (sucursal SAP) tomado de la OC original
            $bplId = isset($oc['BPL_IDAssignedToInvoice']) ? (int)$oc['BPL_IDAssignedToInvoice'] : (isset($oc['BPLId']) ? (int)$oc['BPLId'] : null);

            $stmtRef->execute([
                $agendaId,
                'PurchaseOrder',
                $oc['DocEntry'],
                $oc['DocNum'],
                $refType,
                $oc['CardCode'] ?? null,
                $oc['CardName'] ?? null,
                $bplId,
                'PENDING',
            ]);
            $refId = (int) $db->lastInsertId();

            // Insertar lineas de la OC
            if (!empty($oc['DocumentLines'])) {
                $stmtLine = $db->prepare(
                    "INSERT INTO reception_agenda_lines
                     (agenda_id, ref_id, line_num, item_id, item_code, item_name,
                      expected_qty, received_qty, difference_qty, sap_line_num, status)
                     VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, ?, 'PENDING')"
                );

                foreach ($oc['DocumentLines'] as $i => $sapLine) {
                    $itemCode = $sapLine['ItemCode'] ?? '';
                    $itemName = $sapLine['ItemDescription'] ?? '';

                    // Buscar item en WMS local
                    $localItem = $this->itemRepo->findByCode($itemCode);
                    $itemId = $localItem ? $localItem['id'] : null;

                    $expectedQty = (float) ($sapLine['Quantity'] ?? 0);
                    $sapLineNum  = $sapLine['LineNum'] ?? $i;

                    $stmtLine->execute([
                        $agendaId,
                        $refId,
                        $i + 1,
                        $itemId,
                        $itemCode,
                        $itemName,
                        $expectedQty,
                        $sapLineNum,
                    ]);
                }
            }

            $db->commit();

            AuditService::log('reception_agenda_refs', (int)$refId, 'AGENDA_ADD_REF', null, [
                'agenda_id'     => $agendaId,
                'sap_doc_entry' => $sapDocEntry,
                'sap_doc_num'   => $oc['DocNum'] ?? null,
                'sap_doc_type'  => 'PurchaseOrder',
                'supplier_code' => $oc['CardCode'] ?? null,
                'lines_added'   => count($oc['DocumentLines'] ?? []),
            ], $userId);

            return $this->agendaRepo->getWithDetails($agendaId);
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * Elimina una referencia (OC) de la agenda y sus lineas.
     */
    public function removeReference(int $agendaId, int $refId, int $userId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if ($agenda['status'] !== 'DRAFT') {
            throw new \RuntimeException('Solo se pueden eliminar referencias de agendas en estado DRAFT');
        }

        $db = Database::getConnection();

        // Verificar que la referencia pertenece a esta agenda
        $stmt = $db->prepare("SELECT id FROM reception_agenda_refs WHERE id = ? AND agenda_id = ?");
        $stmt->execute([$refId, $agendaId]);
        if (!$stmt->fetch()) {
            throw new \RuntimeException('Referencia no encontrada en esta agenda');
        }

        $db->beginTransaction();

        try {
            // Eliminar lineas asociadas
            $db->prepare("DELETE FROM reception_agenda_lines WHERE ref_id = ? AND agenda_id = ?")
               ->execute([$refId, $agendaId]);

            // Eliminar referencia
            $db->prepare("DELETE FROM reception_agenda_refs WHERE id = ?")
               ->execute([$refId]);

            $db->commit();

            AuditService::log('reception_agenda_refs', $refId, 'AGENDA_REMOVE_REF', null, [
                'agenda_id' => $agendaId,
                'ref_id'    => $refId,
            ], $userId);

            return $this->agendaRepo->getWithDetails($agendaId);
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * Asigna la agenda a un operador responsable.
     */
    public function assignAgenda(int $agendaId, int $assigneeId, int $byUserId, ?string $notes = null): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if (in_array($agenda['status'], ['CLOSED', 'CANCELLED'])) {
            throw new \RuntimeException('No se puede asignar una agenda cerrada o cancelada');
        }

        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT id, username, full_name, is_active FROM users WHERE id = ?");
        $stmt->execute([$assigneeId]);
        $assignee = $stmt->fetch();
        if (!$assignee || !$assignee['is_active']) {
            throw new \RuntimeException('Usuario destino inválido o inactivo');
        }

        $this->agendaRepo->update($agendaId, [
            'assigned_to' => $assigneeId,
            'assigned_at' => date('Y-m-d H:i:s'),
            'assigned_by' => $byUserId,
        ]);

        AuditService::log('reception_agendas', $agendaId, 'AGENDA_ASSIGN', [
            'assigned_to_prev' => $agenda['assigned_to'] ?? null,
        ], [
            'agenda_number'   => $agenda['agenda_number'] ?? null,
            'assigned_to'     => $assigneeId,
            'assignee_user'   => $assignee['username'],
            'assignee_name'   => $assignee['full_name'],
            'assigned_by'     => $byUserId,
            'notes'           => $notes,
        ], $byUserId);

        return $this->agendaRepo->getWithDetails($agendaId);
    }

    /**
     * Abre la agenda para recepcion fisica.
     * Valida que tenga al menos una referencia asociada.
     */
    public function openAgenda(int $agendaId, int $userId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if ($agenda['status'] !== 'DRAFT') {
            throw new \RuntimeException('Solo se pueden abrir agendas en estado DRAFT');
        }

        // Verificar que tiene al menos una referencia
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) FROM reception_agenda_refs WHERE agenda_id = ?");
        $stmt->execute([$agendaId]);
        $refCount = (int) $stmt->fetchColumn();

        if ($refCount === 0) {
            throw new \RuntimeException('La agenda debe tener al menos una OC asociada para poder abrirla');
        }

        $this->agendaRepo->update($agendaId, [
            'status'    => 'OPEN',
            'opened_at' => date('Y-m-d H:i:s'),
        ]);

        AuditService::log('reception_agendas', $agendaId, 'AGENDA_OPEN', [
            'status_prev' => 'DRAFT',
        ], [
            'agenda_number' => $agenda['agenda_number'] ?? null,
            'refs_count'    => $refCount,
        ], $userId);

        return $this->agendaRepo->getWithDetails($agendaId);
    }

    /**
     * Registra un escaneo ciego de producto.
     * Intenta hacer match con una linea de la agenda por item_code.
     * Actualiza received_qty en la linea correspondiente.
     */
    public function scanItem(int $agendaId, array $data, int $userId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if ($agenda['status'] !== 'OPEN') {
            throw new \RuntimeException('La agenda debe estar ABIERTA para registrar escaneos');
        }

        $itemCode    = trim((string)($data['item_code'] ?? ''));
        $itemId      = isset($data['item_id']) ? (int)$data['item_id'] : null;
        $scannedQty  = (float) ($data['quantity'] ?? 0);
        $batchNumber = $data['batch_number'] ?? null;
        $expiryDate  = $data['expiry_date'] ?? null;
        $supplierLot = $data['supplier_lot'] ?? null;
        $binId       = $data['bin_id'] ?? null;

        // Bug #4 — validación defensiva de cantidad
        if ($scannedQty <= 0) {
            throw new \RuntimeException('La cantidad escaneada debe ser mayor a 0');
        }
        if ($itemCode === '' && !$itemId) {
            throw new \RuntimeException('Debe enviar item_code o item_id');
        }

        $db = Database::getConnection();
        $db->beginTransaction();

        try {
            // Resolver el código escaneado contra el catálogo (barcode / item_code / sap_item_code).
            // Esto convierte un EAN en el item_id real del WMS para que el matching funcione
            // independientemente de si la línea de la agenda tiene el código SAP o el código WMS.
            $resolvedCode = $itemCode;
            if (!$itemId && $itemCode !== '') {
                $stmtR = $db->prepare(
                    "SELECT id, item_code, sap_item_code, barcode
                     FROM items
                     WHERE barcode = ? OR item_code = ? OR sap_item_code = ?
                     LIMIT 2"
                );
                $stmtR->execute([$itemCode, $itemCode, $itemCode]);
                $hits = $stmtR->fetchAll();
                if (count($hits) === 1) {
                    $itemId       = (int) $hits[0]['id'];
                    $resolvedCode = $hits[0]['item_code'];
                } elseif (count($hits) > 1) {
                    throw new \RuntimeException("El código '{$itemCode}' está asociado a múltiples items en el catálogo. Revisar duplicados.");
                }
            }

            // Bug #7 — buscar primero por item_id (catálogo), después por item_code/sap_item_code como fallback.
            $matchedLine = null;
            if ($itemId) {
                $stmt = $db->prepare(
                    "SELECT id, item_id, item_code, expected_qty, received_qty, batch_number, status
                     FROM reception_agenda_lines
                     WHERE agenda_id = ? AND item_id = ?
                     ORDER BY (expected_qty - received_qty) DESC, id ASC
                     LIMIT 1"
                );
                $stmt->execute([$agendaId, $itemId]);
                $matchedLine = $stmt->fetch() ?: null;
            }
            if (!$matchedLine && $resolvedCode !== '') {
                $stmt = $db->prepare(
                    "SELECT l.id, l.item_id, l.item_code, l.expected_qty, l.received_qty, l.batch_number, l.status
                     FROM reception_agenda_lines l
                     LEFT JOIN items i ON i.id = l.item_id
                     WHERE l.agenda_id = ?
                       AND (l.item_code = ? OR i.item_code = ? OR i.sap_item_code = ? OR i.barcode = ?)
                     ORDER BY (l.expected_qty - l.received_qty) DESC, l.id ASC
                     LIMIT 1"
                );
                $stmt->execute([$agendaId, $resolvedCode, $resolvedCode, $resolvedCode, $itemCode]);
                $matchedLine = $stmt->fetch() ?: null;
            }

            $lineId = null;
            $auditOld = null;
            $auditNew = null;

            if ($matchedLine) {
                $lineId = (int) $matchedLine['id'];

                $prevReceived  = (float) $matchedLine['received_qty'];
                $newReceivedQty = $prevReceived + $scannedQty;
                $expectedQty   = (float) $matchedLine['expected_qty'];
                $differenceQty = $newReceivedQty - $expectedQty;

                // Bug #2 — status correcto según ENUM('PENDING','PARTIAL','COMPLETED','OVER','SHORT')
                if ($newReceivedQty == 0)                       $lineStatus = 'PENDING';
                elseif ($newReceivedQty < $expectedQty)         $lineStatus = 'PARTIAL';
                elseif ($newReceivedQty == $expectedQty)        $lineStatus = 'COMPLETED';
                else                                            $lineStatus = 'OVER';

                // Bug #5 — el batch de cabecera solo se setea si estaba vacío.
                // Si llega un batch distinto al que ya hay, se marca 'MULTI' para no perder señal.
                $headerBatch = $matchedLine['batch_number'] ?? null;
                $newHeaderBatch = $headerBatch;
                if (!empty($batchNumber)) {
                    if (empty($headerBatch)) {
                        $newHeaderBatch = $batchNumber;
                    } elseif ($headerBatch !== $batchNumber && $headerBatch !== 'MULTI') {
                        $newHeaderBatch = 'MULTI';
                    }
                }

                $db->prepare(
                    "UPDATE reception_agenda_lines
                     SET received_qty = ?, difference_qty = ?, batch_number = ?,
                         expiry_date = COALESCE(?, expiry_date), supplier_lot = COALESCE(?, supplier_lot),
                         status = ?
                     WHERE id = ?"
                )->execute([$newReceivedQty, $differenceQty, $newHeaderBatch, $expiryDate, $supplierLot, $lineStatus, $lineId]);

                $auditOld = [
                    'received_qty_prev' => $prevReceived,
                    'status_prev'       => $matchedLine['status'] ?? null,
                    'header_batch_prev' => $headerBatch,
                ];
                $auditNew = [
                    'agenda_id'      => $agendaId,
                    'line_id'        => $lineId,
                    'item_code'      => $matchedLine['item_code'],
                    'expected_qty'   => $expectedQty,
                    'scanned_qty'    => $scannedQty,
                    'received_total' => $newReceivedQty,
                    'difference_qty' => $differenceQty,
                    'line_status'    => $lineStatus,
                    'batch_number'   => $batchNumber,
                    'expiry_date'    => $expiryDate,
                    'supplier_lot'   => $supplierLot,
                    'bin_id'         => $binId,
                ];
            } else {
                // Producto no esperado: verificar si la agenda lo permite
                if (!$this->allowUnexpectedProducts($agendaId)) {
                    throw new \RuntimeException(
                        "El articulo '{$itemCode}' no esta en la agenda y este tipo de agenda no permite productos inesperados"
                    );
                }

                $auditNew = [
                    'agenda_id'    => $agendaId,
                    'line_id'      => null,
                    'item_code'    => $itemCode,
                    'unexpected'   => true,
                    'scanned_qty'  => $scannedQty,
                    'batch_number' => $batchNumber,
                    'expiry_date'  => $expiryDate,
                    'supplier_lot' => $supplierLot,
                    'bin_id'       => $binId,
                ];
            }

            // Registrar escaneo individual (trazabilidad por escaneo)
            $db->prepare(
                "INSERT INTO reception_scans
                 (agenda_id, line_id, item_code, batch_number, expiry_date, supplier_lot, scanned_qty, operator_id, scanned_at, bin_id)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?)"
            )->execute([
                $agendaId,
                $lineId,
                $itemCode !== '' ? $itemCode : ($matchedLine['item_code'] ?? ''),
                $batchNumber,
                $expiryDate,
                $supplierLot,
                $scannedQty,
                $userId,
                $binId,
            ]);
            $scanId = (int) $db->lastInsertId();

            $db->commit();

            // Bug #3 — auditoría sobre la LÍNEA escaneada (o sobre el escaneo si no hubo match)
            if ($lineId) {
                AuditService::log('reception_agenda_lines', $lineId, 'AGENDA_SCAN', $auditOld, $auditNew, $userId);
            } else {
                AuditService::log('reception_scans', $scanId, 'AGENDA_SCAN_UNEXPECTED', null, $auditNew, $userId);
            }

            return $this->agendaRepo->getWithDetails($agendaId);
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * Obtiene las diferencias entre lo esperado y lo recibido.
     * Incluye lineas con discrepancia y productos escaneados no esperados.
     */
    public function getDifferences(int $agendaId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }

        $db = Database::getConnection();

        // Diferencias en lineas esperadas
        $stmt = $db->prepare(
            "SELECT l.item_code, l.item_name, l.expected_qty, l.received_qty,
                    (l.received_qty - l.expected_qty) AS difference_qty,
                    l.batch_number, l.supplier_lot,
                    CASE
                        WHEN l.received_qty = 0 THEN 'NO_RECIBIDO'
                        WHEN l.received_qty < l.expected_qty THEN 'PARCIAL'
                        WHEN l.received_qty = l.expected_qty THEN 'COMPLETO'
                        WHEN l.received_qty > l.expected_qty THEN 'EXCEDENTE'
                    END AS status,
                    r.sap_doc_num AS ref_doc_num
             FROM reception_agenda_lines l
             LEFT JOIN reception_agenda_refs r ON l.ref_id = r.id
             WHERE l.agenda_id = ?
             ORDER BY l.item_code"
        );
        $stmt->execute([$agendaId]);
        $expectedLines = $stmt->fetchAll();

        // Productos escaneados no asociados a ninguna linea (inesperados)
        $stmt = $db->prepare(
            "SELECT s.item_code,
                    COALESCE(i.item_name, 'Articulo no registrado') AS item_name,
                    0 AS expected_qty,
                    SUM(s.scanned_qty) AS received_qty,
                    SUM(s.scanned_qty) AS difference_qty,
                    s.batch_number, s.supplier_lot,
                    'INESPERADO' AS status,
                    NULL AS ref_doc_num
             FROM reception_scans s
             LEFT JOIN items i ON s.item_code = i.item_code
             WHERE s.agenda_id = ? AND s.line_id IS NULL
             GROUP BY s.item_code, i.item_name, s.batch_number, s.supplier_lot"
        );
        $stmt->execute([$agendaId]);
        $unexpectedItems = $stmt->fetchAll();

        return [
            'expected_lines'   => $expectedLines,
            'unexpected_items' => $unexpectedItems,
            'summary' => [
                'total_expected_lines' => count($expectedLines),
                'completed'  => count(array_filter($expectedLines, fn($l) => $l['status'] === 'COMPLETO')),
                'partial'    => count(array_filter($expectedLines, fn($l) => $l['status'] === 'PARCIAL')),
                'not_received' => count(array_filter($expectedLines, fn($l) => $l['status'] === 'NO_RECIBIDO')),
                'excess'     => count(array_filter($expectedLines, fn($l) => $l['status'] === 'EXCEDENTE')),
                'unexpected' => count($unexpectedItems),
            ],
        ];
    }

    /**
     * Cierra la agenda y crea PurchaseDeliveryNotes en SAP.
     * Agrupa lineas por referencia (OC) y crea un documento SAP por cada una.
     */
    public function closeAgenda(int $agendaId, int $userId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if ($agenda['status'] !== 'OPEN') {
            throw new \RuntimeException('Solo se pueden cerrar agendas en estado OPEN');
        }

        $db = Database::getConnection();
        $db->beginTransaction();

        try {
            // Obtener referencias con sus lineas recibidas
            $stmt = $db->prepare(
                "SELECT r.id AS ref_id, r.sap_doc_entry, r.sap_doc_num, r.supplier_code, r.sap_doc_type, r.bpl_id
                 FROM reception_agenda_refs r
                 WHERE r.agenda_id = ?"
            );
            $stmt->execute([$agendaId]);
            $refs = $stmt->fetchAll();

            $sapResults = [];

            foreach ($refs as $ref) {
                // Obtener lineas recibidas para esta referencia
                $stmt = $db->prepare(
                    "SELECT l.*, GROUP_CONCAT(
                         DISTINCT CONCAT_WS('|', s.batch_number, s.scanned_qty, s.expiry_date, s.supplier_lot)
                     ) AS scan_details
                     FROM reception_agenda_lines l
                     LEFT JOIN reception_scans s ON s.line_id = l.id AND s.agenda_id = l.agenda_id
                     WHERE l.agenda_id = ? AND l.ref_id = ? AND l.received_qty > 0
                     GROUP BY l.id"
                );
                $stmt->execute([$agendaId, $ref['ref_id']]);
                $lines = $stmt->fetchAll();

                if (empty($lines)) {
                    // Marcar referencia sin lineas recibidas
                    $db->prepare("UPDATE reception_agenda_refs SET status = 'SKIPPED' WHERE id = ?")
                       ->execute([$ref['ref_id']]);
                    continue;
                }

                // Construir payload de PurchaseDeliveryNotes para SAP
                $sapData = [
                    'CardCode'      => $ref['supplier_code'],
                    'DocDate'       => date('Y-m-d'),
                    'BPL_IDAssignedToInvoice' => isset($ref['bpl_id']) ? (int)$ref['bpl_id'] : null,
                    'DocumentLines' => [],
                ];

                foreach ($lines as $line) {
                    $sapLine = [
                        'ItemCode'      => $line['item_code'],
                        'Quantity'      => (float) $line['received_qty'],
                        'BaseType'      => 22, // PurchaseOrder
                        'BaseEntry'     => (int) $ref['sap_doc_entry'],
                        'BaseLine'      => (int) $line['sap_line_num'],
                    ];

                    // Agregar lotes si tiene batch
                    if (!empty($line['batch_number'])) {
                        $sapLine['BatchNumbers'] = [];

                        // Obtener escaneos individuales para esta linea con sus lotes
                        $stmtScans = $db->prepare(
                            "SELECT batch_number, SUM(scanned_qty) AS qty, expiry_date, supplier_lot
                             FROM reception_scans
                             WHERE agenda_id = ? AND line_id = ? AND batch_number IS NOT NULL
                             GROUP BY batch_number, expiry_date, supplier_lot"
                        );
                        $stmtScans->execute([$agendaId, $line['id']]);
                        $scanBatches = $stmtScans->fetchAll();

                        foreach ($scanBatches as $sb) {
                            $batchEntry = [
                                'BatchNumber' => $sb['batch_number'],
                                'Quantity'    => (float) $sb['qty'],
                            ];
                            if (!empty($sb['expiry_date'])) {
                                $batchEntry['ExpiryDate'] = $sb['expiry_date'];
                            }
                            if (!empty($sb['supplier_lot'])) {
                                $batchEntry['ManufacturerSerialNumber'] = $sb['supplier_lot'];
                            }
                            $sapLine['BatchNumbers'][] = $batchEntry;
                        }
                    }

                    $sapData['DocumentLines'][] = $sapLine;
                }

                // Crear PurchaseDeliveryNotes en SAP
                try {
                    $sap = new SapServiceLayerClient();
                    $result = $sap->post('/PurchaseDeliveryNotes', $sapData);

                    if (isset($result['DocEntry'])) {
                        // Actualizar referencia con datos de SAP
                        $db->prepare(
                            "UPDATE reception_agenda_refs
                             SET status = 'COMPLETED'
                             WHERE id = ?"
                        )->execute([$ref['ref_id']]);

                        $sapResults[] = [
                            'ref_id'        => $ref['ref_id'],
                            'sap_doc_entry' => $result['DocEntry'],
                            'sap_doc_num'   => $result['DocNum'] ?? null,
                            'status'        => 'SUCCESS',
                        ];
                    }

                    // Log exito en sap_sync_log
                    $db->prepare(
                        "INSERT INTO sap_sync_log
                         (sync_type, direction, sap_doc_entry, sap_doc_type, wms_reference_id, status)
                         VALUES (?, ?, ?, ?, ?, ?)"
                    )->execute([
                        'DOC_RECEPTION_AGENDA',
                        'WMS_TO_SAP',
                        $result['DocEntry'] ?? null,
                        'PurchaseDeliveryNote',
                        $agendaId,
                        'SUCCESS',
                    ]);
                } catch (\Throwable $e) {
                    // Log error en sap_sync_log
                    $db->prepare(
                        "INSERT INTO sap_sync_log
                         (sync_type, direction, sap_doc_type, wms_reference_id, status, error_message)
                         VALUES (?, ?, ?, ?, ?, ?)"
                    )->execute([
                        'DOC_RECEPTION_AGENDA',
                        'WMS_TO_SAP',
                        'PurchaseDeliveryNote',
                        $agendaId,
                        'ERROR',
                        $e->getMessage(),
                    ]);

                    $db->prepare("UPDATE reception_agenda_refs SET status = 'ERROR' WHERE id = ?")
                       ->execute([$ref['ref_id']]);

                    $sapResults[] = [
                        'ref_id' => $ref['ref_id'],
                        'status' => 'ERROR',
                        'error'  => $e->getMessage(),
                    ];
                }
            }

            // Crear stock entries para las lineas recibidas
            $this->createStockEntries($agendaId, $agenda['warehouse_id'], $userId);

            // Cerrar agenda
            $this->agendaRepo->update($agendaId, [
                'status'    => 'CLOSED',
                'closed_at' => date('Y-m-d H:i:s'),
            ]);

            // Actualizar lineas finales
            $db->prepare(
                "UPDATE reception_agenda_lines
                 SET difference_qty = (received_qty - expected_qty)
                 WHERE agenda_id = ?"
            )->execute([$agendaId]);

            $db->commit();

            AuditService::log('reception_agendas', $agendaId, 'AGENDA_CLOSE', [
                'status_prev' => 'OPEN',
            ], [
                'agenda_number' => $agenda['agenda_number'] ?? null,
                'refs_processed'=> count($refs),
                'sap_documents' => array_map(fn($r) => $r['sap_doc_entry'] ?? null, $sapResults),
            ], $userId);

            $result = $this->agendaRepo->getWithDetails($agendaId);
            $result['sap_results'] = $sapResults;

            return $result;
        } catch (\Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * Reintenta la creación del PurchaseDeliveryNote en SAP para una referencia
     * que quedó en estado ERROR (típicamente por permisos / sucursal / Service Layer caído).
     */
    public function retrySapForRef(int $refId, int $userId): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare(
            "SELECT r.id AS ref_id, r.agenda_id, r.sap_doc_entry, r.sap_doc_num,
                    r.supplier_code, r.bpl_id, r.status,
                    a.warehouse_id, a.agenda_number
             FROM reception_agenda_refs r
             JOIN reception_agendas a ON a.id = r.agenda_id
             WHERE r.id = ?"
        );
        $stmt->execute([$refId]);
        $ref = $stmt->fetch();
        if (!$ref) {
            throw new \RuntimeException('Referencia no encontrada');
        }
        if ($ref['status'] === 'COMPLETED') {
            throw new \RuntimeException('La referencia ya fue creada en SAP');
        }
        if ($ref['status'] === 'SKIPPED') {
            throw new \RuntimeException('La referencia no tiene líneas recibidas (SKIPPED)');
        }

        // Líneas recibidas
        $stmt = $db->prepare(
            "SELECT * FROM reception_agenda_lines
             WHERE agenda_id = ? AND ref_id = ? AND received_qty > 0"
        );
        $stmt->execute([$ref['agenda_id'], $ref['ref_id']]);
        $lines = $stmt->fetchAll();
        if (empty($lines)) {
            throw new \RuntimeException('No hay líneas con recepción registrada');
        }

        // Construir payload con BPLId
        $sapData = [
            'CardCode' => $ref['supplier_code'],
            'DocDate'  => date('Y-m-d'),
            'BPL_IDAssignedToInvoice' => isset($ref['bpl_id']) ? (int)$ref['bpl_id'] : null,
            'DocumentLines' => [],
        ];

        foreach ($lines as $line) {
            $sapLine = [
                'ItemCode'  => $line['item_code'],
                'Quantity'  => (float) $line['received_qty'],
                'BaseType'  => 22,
                'BaseEntry' => (int) $ref['sap_doc_entry'],
                'BaseLine'  => (int) $line['sap_line_num'],
            ];
            if (!empty($line['batch_number'])) {
                $stmtScans = $db->prepare(
                    "SELECT batch_number, SUM(scanned_qty) AS qty, expiry_date, supplier_lot
                     FROM reception_scans
                     WHERE agenda_id = ? AND line_id = ? AND batch_number IS NOT NULL
                     GROUP BY batch_number, expiry_date, supplier_lot"
                );
                $stmtScans->execute([$ref['agenda_id'], $line['id']]);
                $sapLine['BatchNumbers'] = [];
                foreach ($stmtScans->fetchAll() as $sb) {
                    $be = ['BatchNumber' => $sb['batch_number'], 'Quantity' => (float)$sb['qty']];
                    if (!empty($sb['expiry_date'])) $be['ExpiryDate'] = $sb['expiry_date'];
                    if (!empty($sb['supplier_lot'])) $be['ManufacturerSerialNumber'] = $sb['supplier_lot'];
                    $sapLine['BatchNumbers'][] = $be;
                }
            }
            $sapData['DocumentLines'][] = $sapLine;
        }

        try {
            $sap = new SapServiceLayerClient();
            $result = $sap->post('/PurchaseDeliveryNotes', $sapData);

            if (!isset($result['DocEntry'])) {
                throw new \RuntimeException('SAP no devolvió DocEntry: ' . json_encode($result));
            }

            $db->prepare("UPDATE reception_agenda_refs SET status = 'COMPLETED' WHERE id = ?")
               ->execute([$ref['ref_id']]);

            $db->prepare(
                "INSERT INTO sap_sync_log (sync_type, direction, sap_doc_entry, sap_doc_type, wms_reference_id, status)
                 VALUES ('DOC_RECEPTION_AGENDA','WMS_TO_SAP', ?, 'PurchaseDeliveryNote', ?, 'SUCCESS')"
            )->execute([$result['DocEntry'], $ref['agenda_id']]);

            AuditService::log('reception_agenda_refs', $ref['ref_id'], 'AGENDA_REF_RETRY_SAP', [
                'status_prev' => $ref['status'],
            ], [
                'agenda_number' => $ref['agenda_number'],
                'sap_doc_entry' => $result['DocEntry'],
                'sap_doc_num'   => $result['DocNum'] ?? null,
            ], $userId);

            return [
                'ref_id'        => $ref['ref_id'],
                'agenda_number' => $ref['agenda_number'],
                'status'        => 'SUCCESS',
                'sap_doc_entry' => $result['DocEntry'],
                'sap_doc_num'   => $result['DocNum'] ?? null,
            ];
        } catch (\Throwable $e) {
            $db->prepare(
                "INSERT INTO sap_sync_log (sync_type, direction, sap_doc_type, wms_reference_id, status, error_message)
                 VALUES ('DOC_RECEPTION_AGENDA','WMS_TO_SAP','PurchaseDeliveryNote', ?, 'ERROR', ?)"
            )->execute([$ref['agenda_id'], $e->getMessage()]);
            throw new \RuntimeException('SAP rechazó: ' . $e->getMessage());
        }
    }

    /**
     * Verifica si la agenda permite productos inesperados.
     * OC importacion/plaza = si, OT produccion = no.
     */
    public function allowUnexpectedProducts(int $agendaId): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare(
            "SELECT ref_type FROM reception_agenda_refs WHERE agenda_id = ? LIMIT 1"
        );
        $stmt->execute([$agendaId]);
        $ref = $stmt->fetch();

        if (!$ref) {
            return true; // Sin referencias, permitir por defecto
        }

        // OT produccion no permite productos inesperados
        return $ref['ref_type'] !== 'OT_PRODUCCION';
    }

    /**
     * Crea entradas de stock en el WMS a partir de las lineas recibidas.
     */
    private function createStockEntries(int $agendaId, int $warehouseId, int $userId): void
    {
        $db = Database::getConnection();

        // Obtener bin de recepcion
        $receivingBins = $this->binRepo->getReceivingBins($warehouseId);
        if (empty($receivingBins)) {
            throw new \RuntimeException('No hay bin RECEIVING configurado en este almacen');
        }
        $receivingBinId = $receivingBins[0]['id'];

        $config = require __DIR__ . '/../config/app.php';
        $initialStatus = $config['wms']['qa_on_receipt'] ? 'QA' : 'AVAILABLE';

        // Agrupar escaneos por item_code + batch_number
        $stmt = $db->prepare(
            "SELECT s.item_code, s.batch_number, s.expiry_date,
                    SUM(s.scanned_qty) AS total_qty,
                    COALESCE(s.bin_id, ?) AS target_bin_id
             FROM reception_scans s
             WHERE s.agenda_id = ?
             GROUP BY s.item_code, s.batch_number, s.expiry_date, s.bin_id"
        );
        $stmt->execute([$receivingBinId, $agendaId]);
        $scanGroups = $stmt->fetchAll();

        foreach ($scanGroups as $group) {
            // Resolver el código contra el catálogo: item_code / sap_item_code / barcode
            $item = $this->itemRepo->findByCode($group['item_code']);
            if (!$item) {
                $stmtR = $db->prepare(
                    "SELECT * FROM items
                     WHERE barcode = ? OR sap_item_code = ?
                     LIMIT 1"
                );
                $stmtR->execute([$group['item_code'], $group['item_code']]);
                $item = $stmtR->fetch() ?: null;
            }
            if (!$item) {
                continue; // Item realmente no registrado en WMS
            }

            $batchId = null;
            if (!empty($group['batch_number'])) {
                $batch = $this->batchRepo->findOrCreate(
                    $item['id'],
                    $group['batch_number'],
                    $group['expiry_date'],
                    $userId
                );
                $batchId = $batch['id'];
            }

            $binId = (int) $group['target_bin_id'];
            $qty   = (float) $group['total_qty'];

            // Crear o actualizar posicion de stock
            $stockId = $this->stockRepo->findOrCreatePosition(
                $warehouseId,
                $binId,
                $item['id'],
                $batchId,
                $initialStatus,
                $item['uom'] ?? 'UN'
            );
            $this->stockRepo->addQuantity($stockId, $qty);

            // Registrar movimiento
            $this->movementRepo->logMovement([
                'warehouse_id'   => $warehouseId,
                'movement_type'  => 'RECEIPT',
                'item_id'        => $item['id'],
                'batch_id'       => $batchId,
                'to_bin_id'      => $binId,
                'to_status'      => $initialStatus,
                'quantity'       => $qty,
                'uom'            => $item['uom'] ?? 'UN',
                'reference_type' => 'RECEPTION_AGENDA',
                'reference_id'   => $agendaId,
                'created_by'     => $userId,
            ]);
        }
    }
}