<?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\ReceptionPdfService;
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;
    }

    /**
     * Importa una Solicitud de Traslado SAP (OWTQ / InventoryTransferRequest) creando
     * agenda en estado DRAFT, asociando la ST y registrando sus lineas. Espejo de
     * importFromSapPurchaseOrder pero adaptado a la entidad SAP de transferencias.
     */
    public function importFromSapStockTransferRequest(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');
        }

        $agenda = $this->createAgenda([
            'warehouse_id' => $data['warehouse_id'],
            'planned_date' => $data['planned_date'] ?? date('Y-m-d'),
            'notes'        => $data['notes'] ?? ('Auto-creada desde ST SAP #' . ($data['sap_doc_num'] ?? $data['sap_doc_entry'])),
        ], $userId);
        $agendaId = (int) $agenda['id'];

        try {
            $this->addReferenceFromTransferRequest($agendaId, [
                'sap_doc_entry' => $data['sap_doc_entry'] ?? null,
                'sap_doc_num'   => $data['sap_doc_num'] ?? null,
            ], $userId);
        } catch (\Throwable $e) {
            throw new \RuntimeException('Agenda creada pero fallo asociar la ST: ' . $e->getMessage());
        }

        // Auto-asignacion (mismo algoritmo que OC)
        $role = strtolower(trim((string)$userRole));
        $assignTo = null;
        $assignReason = null;
        if ($role === 'op_recepcion') {
            $assignTo = $userId;
            $assignReason = 'Auto-asignacion: importador es op_recepcion';
        } else {
            $db = Database::getConnection();
            $stmt = $db->prepare(
                "SELECT u.id, u.username,
                        (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();
            $c = $stmt->fetch();
            if ($c) {
                $assignTo = (int) $c['id'];
                $assignReason = 'Auto-asignacion: op_recepcion online con menor carga (' . $c['username'] . ', ' . $c['active_load'] . ' agendas activas)';
            }
        }
        if ($assignTo) {
            $this->assignAgenda($agendaId, $assignTo, $userId, $assignReason);
        }

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

    /**
     * Lotes del ítem en el almacén de origen de la ST: lote + vencimiento + lote proveedor
     * (OBTN.MnfSerial), ordenados FEFO. Lee HANA con el cliente nativo `hdbsql` (estable),
     * NO con PDO/ODBC (que segfaultea el worker de Apache). Devuelve [] ante cualquier error
     * (no rompe el import de la ST).
     */
    private function stSourceBatches(string $itemCode, string $fromWh): array
    {
        if ($itemCode === '' || $fromWh === '') return [];

        $cfg  = require __DIR__ . '/../config/app.php';
        $h    = $cfg['hana'] ?? [];
        $sch  = $cfg['sap']['company_db'] ?? ($h['schema'] ?? 'TEST2805'); // base viva
        $node = ($h['host'] ?? '200.10.10.151') . ':' . ($h['port'] ?? '30015');
        $user = $h['username'] ?? 'SAPINST';
        $pass = $h['password'] ?? 'CFR!Art205';
        $hdb  = '/usr/sap/hdbclient';

        $lit = static fn(string $v): string => "'" . str_replace("'", "''", $v) . "'";
        $run = function (string $sql) use ($hdb, $node, $user, $pass): array {
            $cmd = 'LD_LIBRARY_PATH=' . escapeshellarg($hdb) . ' ' . escapeshellarg($hdb . '/hdbsql')
                 . ' -n ' . escapeshellarg($node) . ' -u ' . escapeshellarg($user)
                 . ' -p ' . escapeshellarg($pass) . ' -x -a -C -F "|" ' . escapeshellarg($sql) . ' 2>/dev/null';
            $lines = [];
            @exec($cmd, $lines);
            $rows = [];
            foreach ($lines as $ln) {
                $ln = trim($ln);
                if ($ln === '' || $ln[0] !== '|') continue; // saltar mensajes/avisos
                $rows[] = explode('|', trim($ln, '|'));
            }
            return $rows;
        };

        // Stock por lote en el almacén de origen (OIBT)
        $stockRows = $run('SELECT "BatchNum","Quantity" FROM "' . $sch . '"."OIBT" '
            . 'WHERE "ItemCode"=' . $lit($itemCode) . ' AND "WhsCode"=' . $lit($fromWh) . ' AND "Quantity">0');
        if (!$stockRows) return [];

        // Maestro de lotes (OBTN): vencimiento + lote proveedor (MnfSerial)
        $map = [];
        foreach ($run('SELECT "DistNumber","ExpDate","MnfSerial" FROM "' . $sch . '"."OBTN" WHERE "ItemCode"=' . $lit($itemCode)) as $m) {
            if (count($m) < 3) continue;
            $exp = trim($m[1]); $mnf = trim($m[2]);
            $map[trim($m[0])] = [
                'exp' => ($exp === '' || $exp === '?') ? null : substr($exp, 0, 10),
                'mnf' => ($mnf === '' || $mnf === '?') ? null : $mnf,
            ];
        }

        $out = [];
        foreach ($stockRows as $r) {
            if (count($r) < 2) continue;
            $b  = trim($r[0]);
            $mm = $map[$b] ?? ['exp' => null, 'mnf' => null];
            $out[] = ['batch' => $b, 'exp' => $mm['exp'], 'mnf' => $mm['mnf'], 'qty' => (float) trim($r[1])];
        }
        // FEFO: vencimiento ascendente, nulos al final.
        usort($out, static fn($a, $b) => strcmp($a['exp'] ?? '9999-99-99', $b['exp'] ?? '9999-99-99'));
        return $out;
    }

    /**
     * Asocia una Solicitud de Traslado SAP (OWTQ) a una agenda DRAFT.
     * Lee del Service Layer: /InventoryTransferRequests({docEntry}) y mapea StockTransferLines.
     */
    public function addReferenceFromTransferRequest(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;
        $sap = new SapServiceLayerClient();

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

        $tr = $sap->get("/InventoryTransferRequests({$sapDocEntry})");
        if (empty($tr['DocEntry'])) {
            throw new \RuntimeException("No se pudo obtener la ST #{$sapDocEntry} de SAP");
        }

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

        $db->beginTransaction();
        try {
            $fromWh = $tr['FromWarehouse'] ?? '';
            $toWh   = $tr['ToWarehouse'] ?? '';
            $supplierLabel = trim('Traslado ' . $fromWh . ' → ' . $toWh);

            $bplId = isset($tr['BPLID']) ? (int)$tr['BPLID'] : null;

            $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 (?, 'InventoryTransferRequest', ?, ?, 'OT_PRODUCCION', ?, ?, ?, 'PENDING')"
            );
            $stmtRef->execute([
                $agendaId,
                $tr['DocEntry'],
                $tr['DocNum'],
                $fromWh ?: null,
                $supplierLabel ?: null,
                $bplId,
            ]);
            $refId = (int) $db->lastInsertId();

            $lines = $tr['StockTransferLines'] ?? [];
            if ($lines) {
                $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,
                      batch_number, expiry_date, supplier_lot, sap_line_num, status)
                     VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, ?, ?, ?, ?, 'PENDING')"
                );
                $lineNum = 0;
                $srcCache = [];
                foreach ($lines as $i => $l) {
                    $itemCode = $l['ItemCode'] ?? '';
                    $itemName = $l['ItemDescription'] ?? '';
                    $local = $this->itemRepo->findByCode($itemCode);
                    $itemId = $local ? $local['id'] : null;
                    $sapLineNum = $l['LineNum'] ?? $i;
                    $reqQty = (float) ($l['Quantity'] ?? 0);

                    // En una ST se trasladan lotes que YA existen en el almacén de origen.
                    // Traemos del stock de SAP (almacén origen) el lote + vencimiento +
                    // LOTE PROVEEDOR real (OBTN.MnfSerial), FEFO, para precargar la línea y
                    // que en la RF el operador solo confirme la cantidad. (Solo para ST.)
                    if (!array_key_exists($itemCode, $srcCache)) {
                        $srcCache[$itemCode] = $this->stSourceBatches($itemCode, (string) $fromWh);
                    }
                    $src = $srcCache[$itemCode];
                    $srcMap = [];
                    foreach ($src as $s) { $srcMap[(string) $s['batch']] = $s; }

                    $batches = $l['BatchNumbers'] ?? [];
                    if ($batches) {
                        // La ST ya trae lotes asignados: usarlos, enriqueciendo lote proveedor
                        // (MnfSerial) y vencimiento desde el maestro de lotes de origen.
                        foreach ($batches as $b) {
                            $batchNum = isset($b['BatchNumber']) ? (string) $b['BatchNumber'] : null;
                            $m        = ($batchNum !== null && isset($srcMap[$batchNum])) ? $srcMap[$batchNum] : null;
                            $exp      = !empty($b['ExpiryDate']) ? substr((string) $b['ExpiryDate'], 0, 10) : ($m['exp'] ?? null);
                            $sup      = $m['mnf'] ?? $batchNum;  // lote proveedor real; si no hay, cae al lote
                            $qty      = (float) ($b['Quantity'] ?? 0);
                            $stmtLine->execute([
                                $agendaId, $refId, ++$lineNum, $itemId, $itemCode, $itemName,
                                $qty, $batchNum, $exp, $sup, $sapLineNum
                            ]);
                        }
                    } elseif ($src) {
                        // La ST no trae lotes: traerlos del stock del almacén de origen (FEFO),
                        // repartiendo la cantidad solicitada entre los lotes disponibles.
                        $remaining = $reqQty;
                        foreach ($src as $s) {
                            if ($remaining <= 0) break;
                            $take = min($remaining, (float) $s['qty']);
                            if ($take <= 0) continue;
                            $sup = !empty($s['mnf']) ? $s['mnf'] : $s['batch'];
                            $stmtLine->execute([
                                $agendaId, $refId, ++$lineNum, $itemId, $itemCode, $itemName,
                                $take, $s['batch'], $s['exp'], $sup, $sapLineNum
                            ]);
                            $remaining -= $take;
                        }
                        if ($remaining > 0.0000001) {
                            // Lo que no se cubre con stock en origen queda sin lote (se carga en RF).
                            $stmtLine->execute([
                                $agendaId, $refId, ++$lineNum, $itemId, $itemCode, $itemName,
                                $remaining, null, null, null, $sapLineNum
                            ]);
                        }
                    } else {
                        // Sin lotes en la ST ni stock por lote en origen: línea sin lote.
                        $stmtLine->execute([
                            $agendaId, $refId, ++$lineNum, $itemId, $itemCode, $itemName,
                            $reqQty, null, null, null, $sapLineNum
                        ]);
                    }
                }
            }

            $db->commit();

            AuditService::log('reception_agenda_refs', $refId, 'AGENDA_ADD_REF_ST', null, [
                'agenda_id'     => $agendaId,
                'sap_doc_entry' => $sapDocEntry,
                'sap_doc_num'   => $tr['DocNum'] ?? null,
                'sap_doc_type'  => 'InventoryTransferRequest',
                'from_warehouse'=> $fromWh,
                'to_warehouse'  => $toWh,
                'lines_added'   => count($lines),
            ], $userId);

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

    /**
     * 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');
        }

        // Dispatch por tipo de refs existentes: una agenda hereda el "tipo SAP"
        // de su primera ref. Si ya tiene una InventoryTransferRequest, el nuevo
        // ref tambien debe ser ST (no se permite mezclar OC y ST en una agenda).
        $db = Database::getConnection();
        $stmtType = $db->prepare(
            "SELECT sap_doc_type FROM reception_agenda_refs WHERE agenda_id = ? ORDER BY id ASC LIMIT 1"
        );
        $stmtType->execute([$agendaId]);
        $existingType = $stmtType->fetchColumn();
        if ($existingType === 'InventoryTransferRequest') {
            return $this->addReferenceFromTransferRequest($agendaId, $data, $userId);
        }

        $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;
        $containerId = isset($data['container_id']) ? (int)$data['container_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');
        }

        // Compliance GxP: vencimiento y lote de proveedor son obligatorios al recepcionar.
        // El lote interno (batch_number) se valida más abajo: para familias con código
        // automatizado (MP 112 / Insumos 124) se genera; para el resto lo tipea el operador.
        if (trim((string) $expiryDate) === '') {
            throw new \RuntimeException('La fecha de vencimiento es obligatoria para registrar la recepción');
        }
        if (trim((string) $supplierLot) === '') {
            throw new \RuntimeException('El lote proveedor es obligatorio para registrar la recepción');
        }

        $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.");
                }
            }

            // Código de Lote Automatizado (familias MP 112 / Insumos 124): el lote interno
            // se genera o se reusa (mismo item + lote de proveedor ya escaneado en esta agenda).
            // El operador NO tipea el lote interno; el lote del proveedor va en supplier_lot.
            if ($itemId) {
                $autoLot = \WMS\Services\LotCodeService::assign(
                    $db, $itemId, $agendaId, $resolvedCode !== '' ? $resolvedCode : $itemCode, $supplierLot
                );
                if ($autoLot !== null) { $batchNumber = $autoLot; }
            }
            // GxP: el lote interno es obligatorio (autogenerado para 112/124, manual para el resto).
            if (trim((string) $batchNumber) === '') {
                throw new \RuntimeException('El lote es obligatorio para registrar la recepción');
            }

            // El guardrail de lote-duplicado aplica SOLO a recepciones de OC (PurchaseDeliveryNote):
            // SAP rechaza crear un PDN con un lote ya consumido. En una Solicitud de Traslado (ST)
            // se mueve un lote EXISTENTE en SAP -> reusar el mismo lote es válido y NO se bloquea.
            $isTransferAgenda = (bool) $db->query(
                "SELECT 1 FROM reception_agenda_refs WHERE agenda_id = " . (int) $agendaId
                . " AND sap_doc_type = 'InventoryTransferRequest' LIMIT 1"
            )->fetchColumn();

            // Guardrail: detectar batch_number ya usado en otra agenda CERRADA para el mismo item.
            // SAP rechaza la creación del PDN si el batch ya fue consumido. Bloquear acá con
            // mensaje claro permite al operador renombrar el lote (ej: "123-AG5") antes de cerrar.
            // Se puede saltar enviando force_reuse=1 si físicamente es el mismo lote.
            if ($batchNumber && $itemId && empty($data['force_reuse']) && !$isTransferAgenda) {
                $stmtDup = $db->prepare(
                    "SELECT a.id, a.agenda_number FROM reception_agenda_lines rl
                     JOIN reception_agendas a ON a.id = rl.agenda_id
                     WHERE rl.item_id = ?
                       AND rl.batch_number = ?
                       AND rl.agenda_id != ?
                       AND a.status IN ('CLOSED','OPEN','IN_PROGRESS')
                     LIMIT 1"
                );
                $stmtDup->execute([$itemId, $batchNumber, $agendaId]);
                $dup = $stmtDup->fetch();
                if ($dup) {
                    throw new \RuntimeException(
                        "El lote '{$batchNumber}' ya fue usado en la agenda " .
                        ($dup['agenda_number'] ?? ('#' . $dup['id'])) .
                        " para el mismo item. SAP va a rechazar el cierre por duplicación. " .
                        "Solución: usá un sufijo (ej: '{$batchNumber}-B') si es OTRO envío del proveedor. " .
                        "Si físicamente es el mismo lote, reenviar con force_reuse=1."
                    );
                }
            }

            // Validacion shelf life minimo (compliance GxP): si el item define
            // min_remaining_shelf_life_days, no admitir lotes cuyo vto - hoy sea menor.
            // Override: enviar 'force_short_shelf' = 1 con razon documentada.
            if ($itemId && !empty($expiryDate) && empty($data['force_short_shelf'])) {
                $stmtSL = $db->prepare("SELECT min_remaining_shelf_life_days FROM items WHERE id = ? LIMIT 1");
                $stmtSL->execute([$itemId]);
                $minDays = $stmtSL->fetchColumn();
                if ($minDays !== false && $minDays !== null && (int)$minDays > 0) {
                    $today  = new \DateTime('today');
                    try {
                        $expDt = new \DateTime((string)$expiryDate);
                    } catch (\Throwable $e) {
                        throw new \RuntimeException("Fecha de vencimiento invalida: '{$expiryDate}'");
                    }
                    $daysLeft = (int)$today->diff($expDt)->format('%r%a');
                    if ($daysLeft < (int)$minDays) {
                        throw new \RuntimeException(
                            "Shelf life insuficiente: el item requiere al menos " . (int)$minDays .
                            " dias de vida util, pero el lote vence en {$daysLeft} dias " .
                            "({$expiryDate}). Para forzar la recepcion enviar force_short_shelf=1 con motivo en notes."
                        );
                    }
                }
            }

            // 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;

                // Validación: no permitir recibir más de lo esperado (a menos que se fuerce explícitamente).
                if ($newReceivedQty > $expectedQty && empty($data['allow_over'])) {
                    $remaining = $expectedQty - $prevReceived;
                    throw new \RuntimeException(
                        'Cantidad excede lo esperado: pediste ' . $scannedQty .
                        ', pero el saldo pendiente del item ' . ($matchedLine['item_code'] ?? '') .
                        ' es ' . number_format(max(0, $remaining), 0, '.', '') .
                        ' (esperado ' . number_format($expectedQty, 0, '.', '') .
                        ', ya recibido ' . number_format($prevReceived, 0, '.', '') . ').'
                    );
                }

                // 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, container_id)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?)"
            )->execute([
                $agendaId,
                $lineId,
                $itemCode !== '' ? $itemCode : ($matchedLine['item_code'] ?? ''),
                $batchNumber,
                $expiryDate,
                $supplierLot,
                $scannedQty,
                $userId,
                $binId,
                $containerId,
            ]);

            // Si hay contenedor activo, replicar la línea en container_lines (UPSERT por item+batch)
            if ($containerId && $itemId) {
                $batchIdForCl = null;
                if (!empty($batchNumber)) {
                    // find-or-create: si el batch no existe, lo creamos ahora para que la
                    // validación BPA DINAVISA pueda comparar por id (no quedar todo en NULL).
                    $stmtB = $db->prepare("SELECT id, expiry_date FROM batches WHERE item_id = ? AND batch_number = ? LIMIT 1");
                    $stmtB->execute([$itemId, $batchNumber]);
                    $br = $stmtB->fetch();
                    if ($br) {
                        // BPA DINAVISA: un mismo lote NO puede tener distinto vencimiento
                        // (un batch_number identifica unívocamente un vto del fabricante).
                        if (!empty($expiryDate) && !empty($br['expiry_date'])) {
                            $existingExp = substr((string)$br['expiry_date'], 0, 10);
                            $newExp      = substr((string)$expiryDate, 0, 10);
                            if ($existingExp !== $newExp && $existingExp !== '9999-12-31') {
                                throw new \RuntimeException(
                                    'BPA DINAVISA: el lote ' . $batchNumber . ' ya está registrado con vto ' . $existingExp .
                                    '. No se permite usar el mismo lote con vto distinto (' . $newExp . '). ' .
                                    'Si es otro envío del proveedor, usá un sufijo (ej: ' . $batchNumber . '-B).'
                                );
                            }
                            // Si el batch tenía 9999-12-31 (placeholder) y ahora viene fecha real, actualizamos
                            if ($existingExp === '9999-12-31' && $newExp !== '9999-12-31') {
                                $db->prepare("UPDATE batches SET expiry_date = ? WHERE id = ?")
                                   ->execute([$newExp, (int)$br['id']]);
                            }
                        }
                        $batchIdForCl = (int)$br['id'];
                    } else {
                        $insB = $db->prepare(
                            "INSERT INTO batches (item_id, batch_number, expiry_date, supplier_batch, status)
                             VALUES (?, ?, ?, ?, 'QA')"
                        );
                        $insB->execute([
                            $itemId,
                            $batchNumber,
                            !empty($expiryDate) ? $expiryDate : '9999-12-31',
                            !empty($supplierLot) ? $supplierLot : null,
                        ]);
                        $batchIdForCl = (int) $db->lastInsertId();
                    }
                }

                // BPA DINAVISA: 1 LPN = 1 producto + 1 lote + 1 vencimiento + 1 estado QA
                // Validar homogeneidad antes de aceptar la línea.
                $config = require __DIR__ . '/../config/app.php';
                $newStockStatus = ($config['wms']['qa_on_receipt'] ?? true) ? 'QA' : 'AVAILABLE';

                $stmtH = $db->prepare(
                    "SELECT cl.item_id, cl.batch_id, cl.stock_status, i.item_code, b.batch_number, b.expiry_date
                     FROM container_lines cl
                     LEFT JOIN items i ON i.id = cl.item_id
                     LEFT JOIN batches b ON b.id = cl.batch_id
                     WHERE cl.container_id = ? AND cl.quantity > 0 LIMIT 1"
                );
                $stmtH->execute([$containerId]);
                $existingHead = $stmtH->fetch();
                if ($existingHead) {
                    if ((int)$existingHead['item_id'] !== (int)$itemId) {
                        throw new \RuntimeException(
                            'BPA DINAVISA: el LPN ya contiene el producto ' . ($existingHead['item_code'] ?? ('#'.$existingHead['item_id'])) .
                            '. No se permite mezclar productos en el mismo contenedor (1 LPN = 1 producto + 1 lote + 1 vto).'
                        );
                    }
                    $existingBatchId = $existingHead['batch_id'] !== null ? (int)$existingHead['batch_id'] : null;
                    if ($existingBatchId !== $batchIdForCl) {
                        $existingBatch = $existingHead['batch_number'] ?? '(sin lote)';
                        $newBatch = $batchNumber ?: '(sin lote)';
                        throw new \RuntimeException(
                            'BPA DINAVISA: el LPN ya contiene el lote ' . $existingBatch .
                            '. No se permite mezclar lotes/vencimientos distintos en el mismo contenedor (lote escaneado: ' . $newBatch . ').'
                        );
                    }
                    if (!empty($existingHead['stock_status']) && $existingHead['stock_status'] !== $newStockStatus) {
                        throw new \RuntimeException(
                            'BPA DINAVISA: el LPN ya contiene stock con estado ' . $existingHead['stock_status'] .
                            '. No se permite mezclar estados de calidad en el mismo contenedor (estado escaneado: ' . $newStockStatus . ').'
                        );
                    }
                }

                $stmtCl = $db->prepare(
                    "SELECT id, quantity FROM container_lines
                     WHERE container_id = ? AND item_id = ? AND (batch_id <=> ?)
                     LIMIT 1"
                );
                $stmtCl->execute([$containerId, $itemId, $batchIdForCl]);
                $existing = $stmtCl->fetch();
                if ($existing) {
                    $db->prepare("UPDATE container_lines SET quantity = quantity + ?, stock_status = COALESCE(stock_status, ?) WHERE id = ?")
                       ->execute([$scannedQty, $newStockStatus, $existing['id']]);
                } else {
                    $stmtNext = $db->prepare("SELECT COALESCE(MAX(line_num), 0) + 1 FROM container_lines WHERE container_id = ?");
                    $stmtNext->execute([$containerId]);
                    $nextLine = (int)$stmtNext->fetchColumn();
                    $db->prepare(
                        "INSERT INTO container_lines (container_id, line_num, item_id, batch_id, stock_status, quantity, uom)
                         VALUES (?, ?, ?, ?, ?, ?, ?)"
                    )->execute([$containerId, $nextLine, $itemId, $batchIdForCl, $newStockStatus, $scannedQty, 'UN']);
                }
            }
            $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 {
            // Almacén SAP de recepción del WMS (p.ej. CENMRA). La entrada de mercadería
            // (PurchaseDeliveryNote) debe ingresar al almacén físico donde se recibe, NO
            // al almacén que la OC trae heredado en sus líneas (históricamente CEN).
            $agendaWhCode = null;
            $whStmt = $db->prepare("SELECT sap_warehouse_code FROM warehouses WHERE id = ?");
            $whStmt->execute([(int) $agenda['warehouse_id']]);
            $agendaWhCode = $whStmt->fetchColumn() ?: null;

            // 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.*, i.item_group AS item_group_code, 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
                     LEFT JOIN items i ON i.id = l.item_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;
                }

                // Ramificacion por tipo de doc SAP: una OWTQ se materializa como
                // StockTransfer (OWTR), no como PurchaseDeliveryNote.
                if (($ref['sap_doc_type'] ?? '') === 'InventoryTransferRequest') {
                    $this->closeRefAsStockTransfer($agendaId, $agenda, $ref, $lines, $db, $sapResults, $userId);
                    continue;
                }

                // Construir payload de PurchaseDeliveryNotes para SAP
                $sapData = [
                    'CardCode'      => $ref['supplier_code'],
                    'DocDate'       => date('Y-m-d'),
                    'DocumentLines' => [],
                ];
                // Solo enviar BPL si es > 0 (single-branch DBs no usan BPL)
                if (!empty($ref['bpl_id']) && (int)$ref['bpl_id'] > 0) {
                    $sapData['BPL_IDAssignedToInvoice'] = (int)$ref['bpl_id'];
                }

                // Heredar moneda del header de la OC (que matchea el BP).
                // Las lineas mantienen su Currency original via BaseType/BaseEntry/BaseLine.
                try {
                    $sapClient = new SapServiceLayerClient();
                    $oc = $sapClient->get("/PurchaseOrders(" . (int)$ref['sap_doc_entry'] . ")?\$select=DocCurrency,DocRate");
                    if (!empty($oc['DocCurrency'])) {
                        $sapData['DocCurrency'] = $oc['DocCurrency'];
                        if (!empty($oc['DocRate']) && (float)$oc['DocRate'] > 0) {
                            $sapData['DocRate'] = (float)$oc['DocRate'];
                        }
                    }
                } catch (\Throwable $ce) {
                    error_log('[AgendaService] No se pudo leer DocCurrency de la OC: ' . $ce->getMessage());
                }

                $cfg = require __DIR__ . '/../config/app.php';
                $defaultCC = $cfg['sap']['default_costing_codes'] ?? [];
                $cc4ByGroup = $cfg['sap']['costing_code4_by_group'] ?? [];
                $cc4BySupplier = $cfg['sap']['costing_code4_by_supplier'] ?? [];
                $supplierCC4 = $cc4BySupplier[$ref['supplier_code']] ?? null;
                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'],
                    ];
                    // Forzar el almacén de recepción del WMS (CENMRA) en la entrada.
                    if (!empty($agendaWhCode)) {
                        $sapLine['WarehouseCode'] = $agendaWhCode;
                    }
                    foreach ($defaultCC as $ccKey => $ccVal) {
                        if ($ccVal !== '' && $ccVal !== null) $sapLine[$ccKey] = $ccVal;
                    }
                    // Override CostingCode4 segun ItemGroup SAP del item
                    $ig = (string)($line['item_group_code'] ?? '');
                    if ($ig !== '' && isset($cc4ByGroup[$ig])) {
                        $sapLine['CostingCode4'] = $cc4ByGroup[$ig];
                    }
                    // Override por proveedor (mayor prioridad: marca dedicada gana sobre grupo generico)
                    if ($supplierCC4 !== null) {
                        $sapLine['CostingCode4'] = $supplierCC4;
                    }

                    // 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'])) {
                        // Calcular diferencia OC vs recepcion para esta referencia.
                        // Si total recibido < total esperado -> ref status PARTIAL + audit para Compras.
                        $stmtDiff = $db->prepare(
                            "SELECT COALESCE(SUM(expected_qty),0) AS te,
                                    COALESCE(SUM(received_qty),0) AS tr,
                                    COUNT(*) AS lines_total,
                                    SUM(CASE WHEN received_qty < expected_qty THEN 1 ELSE 0 END) AS lines_short,
                                    COALESCE(SUM(GREATEST(expected_qty - received_qty, 0)),0) AS qty_short
                             FROM reception_agenda_lines
                             WHERE agenda_id = ? AND ref_id = ?"
                        );
                        $stmtDiff->execute([$agendaId, $ref['ref_id']]);
                        $diff = $stmtDiff->fetch() ?: ['te'=>0,'tr'=>0,'lines_total'=>0,'lines_short'=>0,'qty_short'=>0];
                        $refStatus = ((float)$diff['qty_short'] > 0) ? 'PARTIAL' : 'COMPLETED';

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

                        if ($refStatus === 'PARTIAL') {
                            AuditService::log('reception_agenda_refs', $ref['ref_id'], 'AGENDA_PARTIAL_RECEIPT', null, [
                                'agenda_id'       => $agendaId,
                                'sap_doc_entry'   => $ref['sap_doc_entry'] ?? null,
                                'sap_doc_num'     => $ref['sap_doc_num'] ?? null,
                                'supplier_code'   => $ref['supplier_code'] ?? null,
                                'pdn_doc_entry'   => $result['DocEntry'],
                                'pdn_doc_num'     => $result['DocNum'] ?? null,
                                'lines_total'     => (int)$diff['lines_total'],
                                'lines_short'     => (int)$diff['lines_short'],
                                'qty_expected'    => (float)$diff['te'],
                                'qty_received'    => (float)$diff['tr'],
                                'qty_short'       => (float)$diff['qty_short'],
                            ], $userId);
                        }

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

                        // Bloquear lotes en SAP (refuerzo explicito por compliance)
                        $this->lockBatchesInSap($sapData, $sap, $agendaId);

                        // Generar Acta PDF + adjuntar al PDN en SAP (compliance DINAVISA)
                        $this->attachActaToPdn($agendaId, (int)$result['DocEntry'], $sap, $userId);
                    }

                    // 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);

            // Generar tareas de PUTAWAY auto-asignadas a op_almacen online
            // (preserva el stock_status actual: si está en QA, la tarea es putaway en cuarentena)
            $putawayTasks = $this->createPutawayTasksFromAgenda($agendaId, (int) $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;
        }
    }

    /**
     * Cierra una ref de tipo InventoryTransferRequest (OWTQ): construye y postea
     * un /StockTransfers (OWTR) en SAP, replicando la trazabilidad (BaseEntry → ITR)
     * y el linaje de lotes. Actualiza el estado de la ref (COMPLETED/PARTIAL/ERROR)
     * y agrega entrada en sap_sync_log + sap_results para que el caller pueda
     * decidir el estado final de la agenda.
     */
    private function closeRefAsStockTransfer(int $agendaId, array $agenda, array $ref, array $lines, \PDO $db, array &$sapResults, int $userId): void
    {
        try {
            $sap = new SapServiceLayerClient();

            // Traer la ITR para FromWarehouse/ToWarehouse y mapear LineNum por ItemCode.
            $itr = $sap->get("/InventoryTransferRequests(" . (int)$ref['sap_doc_entry'] . ")");
            $fromWh = (string)($itr['FromWarehouse'] ?? '');
            $toWh   = (string)($itr['ToWarehouse']   ?? '');
            $itrMap = [];
            foreach ($itr['StockTransferLines'] ?? [] as $il) {
                $itrMap[(string)($il['ItemCode'] ?? '')] = $il;
            }

            $cfg = require __DIR__ . '/../config/app.php';
            $defaultCC = $cfg['sap']['default_costing_codes'] ?? [];

            $sapData = [
                'DocDate'            => date('Y-m-d'),
                'FromWarehouse'      => $fromWh,
                'ToWarehouse'        => $toWh,
                'JournalMemo'        => 'WMS Agenda ' . ($agenda['agenda_number'] ?? $agendaId),
                'StockTransferLines' => [],
            ];

            foreach ($lines as $line) {
                $itrL = $itrMap[(string)$line['item_code']] ?? null;
                $sapLine = [
                    'ItemCode'           => $line['item_code'],
                    'Quantity'           => (float) $line['received_qty'],
                    'BaseType'           => 1250000001, // InventoryTransferRequest
                    'BaseEntry'          => (int) $ref['sap_doc_entry'],
                    'BaseLine'           => $itrL ? (int)$itrL['LineNum'] : (int)$line['sap_line_num'],
                    'FromWarehouseCode'  => (string)($itrL['FromWarehouseCode'] ?? $fromWh),
                    'WarehouseCode'      => (string)($itrL['WarehouseCode'] ?? $toWh),
                ];
                // CostingCodes NO se setean en /StockTransferLines: SAP los rechaza
                // (solo aplican a marketing docs como PDN/Invoice). El ITR original
                // ya tiene su asignacion y SAP la conserva.

                // Lotes: replicar comportamiento PDN
                if (!empty($line['batch_number'])) {
                    $sapLine['BatchNumbers'] = [];
                    $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']]);
                    foreach ($stmtScans->fetchAll() 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['StockTransferLines'][] = $sapLine;
            }

            $result = $sap->post('/StockTransfers', $sapData);

            if (!empty($result['DocEntry'])) {
                // Calcular si fue parcial
                $stmtDiff = $db->prepare(
                    "SELECT COALESCE(SUM(expected_qty),0) AS te,
                            COALESCE(SUM(received_qty),0) AS tr,
                            COUNT(*) AS lines_total,
                            SUM(CASE WHEN received_qty < expected_qty THEN 1 ELSE 0 END) AS lines_short,
                            COALESCE(SUM(GREATEST(expected_qty - received_qty, 0)),0) AS qty_short
                     FROM reception_agenda_lines
                     WHERE agenda_id = ? AND ref_id = ?"
                );
                $stmtDiff->execute([$agendaId, $ref['ref_id']]);
                $diff = $stmtDiff->fetch() ?: ['te'=>0,'tr'=>0,'lines_total'=>0,'lines_short'=>0,'qty_short'=>0];
                $refStatus = ((float)$diff['qty_short'] > 0) ? 'PARTIAL' : 'COMPLETED';

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

                if ($refStatus === 'PARTIAL') {
                    AuditService::log('reception_agenda_refs', $ref['ref_id'], 'AGENDA_PARTIAL_RECEIPT', null, [
                        'agenda_id'       => $agendaId,
                        'sap_doc_entry'   => $ref['sap_doc_entry'] ?? null,
                        'sap_doc_num'     => $ref['sap_doc_num']   ?? null,
                        'supplier_code'   => $ref['supplier_code'] ?? null,
                        'st_doc_entry'    => $result['DocEntry'],
                        'st_doc_num'      => $result['DocNum'] ?? null,
                        'lines_total'     => (int)$diff['lines_total'],
                        'lines_short'     => (int)$diff['lines_short'],
                        'qty_expected'    => (float)$diff['te'],
                        'qty_received'    => (float)$diff['tr'],
                        'qty_short'       => (float)$diff['qty_short'],
                    ], $userId);
                }

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

                // lockBatchesInSap espera 'DocumentLines', lo adaptamos sin tocar el helper.
                $this->lockBatchesInSap(['DocumentLines' => $sapData['StockTransferLines']], $sap, $agendaId);
                $this->attachActaToSapDoc($agendaId, (int)$result['DocEntry'], '/StockTransfers', $sap, $userId);
            }

            $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,
                'StockTransfer',
                $agendaId,
                'SUCCESS',
            ]);
        } catch (\Throwable $e) {
            $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',
                'StockTransfer',
                $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(),
            ];
        }
    }

    /**
     * Variante de retrySapForRef para refs de tipo InventoryTransferRequest.
     * Reusa la logica de closeRefAsStockTransfer pero adaptada al retry context
     * (no recibe sapResults por referencia; devuelve el dict que retrySapForRef
     * espera).
     */
    private function retryRefAsStockTransfer(array $ref, array $lines, \PDO $db, int $userId): array
    {
        try {
            $sap = new SapServiceLayerClient();

            $itr = $sap->get("/InventoryTransferRequests(" . (int)$ref['sap_doc_entry'] . ")");
            $fromWh = (string)($itr['FromWarehouse'] ?? '');
            $toWh   = (string)($itr['ToWarehouse']   ?? '');
            $itrMap = [];
            foreach ($itr['StockTransferLines'] ?? [] as $il) {
                $itrMap[(string)($il['ItemCode'] ?? '')] = $il;
            }

            $sapData = [
                'DocDate'            => date('Y-m-d'),
                'FromWarehouse'      => $fromWh,
                'ToWarehouse'        => $toWh,
                'JournalMemo'        => 'WMS Agenda ' . ($ref['agenda_number'] ?? $ref['agenda_id']) . ' (retry)',
                'StockTransferLines' => [],
            ];

            foreach ($lines as $line) {
                $itrL = $itrMap[(string)$line['item_code']] ?? null;
                $sapLine = [
                    'ItemCode'           => $line['item_code'],
                    'Quantity'           => (float) $line['received_qty'],
                    'BaseType'           => 1250000001,
                    'BaseEntry'          => (int) $ref['sap_doc_entry'],
                    'BaseLine'           => $itrL ? (int)$itrL['LineNum'] : (int)$line['sap_line_num'],
                    'FromWarehouseCode'  => (string)($itrL['FromWarehouseCode'] ?? $fromWh),
                    'WarehouseCode'      => (string)($itrL['WarehouseCode'] ?? $toWh),
                ];
                if (!empty($line['batch_number'])) {
                    $sapLine['BatchNumbers'] = [];
                    $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']]);
                    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['StockTransferLines'][] = $sapLine;
            }

            $result = $sap->post('/StockTransfers', $sapData);
            if (!isset($result['DocEntry'])) {
                throw new \RuntimeException('SAP no devolvio DocEntry: ' . json_encode($result));
            }

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

            $this->lockBatchesInSap(['DocumentLines' => $sapData['StockTransferLines']], $sap, (int)$ref['agenda_id']);
            $this->attachActaToSapDoc((int)$ref['agenda_id'], (int)$result['DocEntry'], '/StockTransfers', $sap, $userId);

            $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', ?, 'StockTransfer', ?, '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,
                'sap_doc_type'  => 'StockTransfer',
            ], $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,
                'sap_doc_type'  => 'StockTransfer',
            ];
        } 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','StockTransfer', ?, 'ERROR', ?)"
            )->execute([$ref['agenda_id'], $e->getMessage()]);
            throw new \RuntimeException('SAP rechazo (StockTransfer): ' . $e->getMessage());
        }
    }

    /**
     * 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, r.sap_doc_type,
                    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 l.*, i.item_group AS item_group_code
             FROM reception_agenda_lines l
             LEFT JOIN items i ON i.id = l.item_id
             WHERE l.agenda_id = ? AND l.ref_id = ? AND l.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');
        }

        // Ramificacion por tipo SAP: una OWTQ → /StockTransfers, no /PurchaseDeliveryNotes.
        if (($ref['sap_doc_type'] ?? '') === 'InventoryTransferRequest') {
            return $this->retryRefAsStockTransfer($ref, $lines, $db, $userId);
        }

        // Construir payload con BPLId
        $sapData = [
            'CardCode' => $ref['supplier_code'],
            'DocDate'  => date('Y-m-d'),
            'DocumentLines' => [],
        ];
        // Solo enviar BPL si es > 0 (single-branch DBs no usan BPL)
        if (!empty($ref['bpl_id']) && (int)$ref['bpl_id'] > 0) {
            $sapData['BPL_IDAssignedToInvoice'] = (int)$ref['bpl_id'];
        }

        // Heredar moneda del header de la OC (que matchea el BP).
        // Las lineas mantienen su Currency original via BaseType/BaseEntry/BaseLine.
        try {
            $sapClient = new SapServiceLayerClient();
            $oc = $sapClient->get("/PurchaseOrders(" . (int)$ref['sap_doc_entry'] . ")?\$select=DocCurrency,DocRate");
            if (!empty($oc['DocCurrency'])) {
                $sapData['DocCurrency'] = $oc['DocCurrency'];
                if (!empty($oc['DocRate']) && (float)$oc['DocRate'] > 0) {
                    $sapData['DocRate'] = (float)$oc['DocRate'];
                }
            }
        } catch (\Throwable $ce) {
            error_log('[AgendaService] No se pudo leer DocCurrency de la OC: ' . $ce->getMessage());
        }

        $cfg = require __DIR__ . '/../config/app.php';
        $defaultCC = $cfg['sap']['default_costing_codes'] ?? [];
        $cc4ByGroup = $cfg['sap']['costing_code4_by_group'] ?? [];
        $cc4BySupplier = $cfg['sap']['costing_code4_by_supplier'] ?? [];
        $supplierCC4 = $cc4BySupplier[$ref['supplier_code']] ?? null;

        // Almacén SAP de recepción del WMS (CENMRA): forzar entrada física en lugar de
        // heredar el almacén de la línea de la OC. $ref trae a.warehouse_id del JOIN.
        $agendaWhCode = null;
        $whStmt = $db->prepare("SELECT sap_warehouse_code FROM warehouses WHERE id = ?");
        $whStmt->execute([(int) ($ref['warehouse_id'] ?? 0)]);
        $agendaWhCode = $whStmt->fetchColumn() ?: null;

        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($agendaWhCode)) {
                $sapLine['WarehouseCode'] = $agendaWhCode;
            }
            foreach ($defaultCC as $ccKey => $ccVal) {
                if ($ccVal !== '' && $ccVal !== null) $sapLine[$ccKey] = $ccVal;
            }
            // Override CostingCode4 segun ItemGroup SAP del item
            $ig = (string)($line['item_group_code'] ?? '');
            if ($ig !== '' && isset($cc4ByGroup[$ig])) {
                $sapLine['CostingCode4'] = $cc4ByGroup[$ig];
            }
            // Override por proveedor (mayor prioridad)
            if ($supplierCC4 !== null) {
                $sapLine['CostingCode4'] = $supplierCC4;
            }
            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']]);

            // Bloquear lotes en SAP (refuerzo explicito por compliance)
            $this->lockBatchesInSap($sapData, $sap, (int)$ref['agenda_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());
        }
    }

    /**
     * Bloquea en SAP los lotes recien creados por la PDN.
     * Refuerzo explicito: aunque SAP por config los cree ya en bdsStatus_Locked, hacemos PATCH
     * para garantizar el bloqueo independientemente del setting global.
     * Errores se registran en sap_sync_log como warnings, NO revierten la PDN.
     */
    private function lockBatchesInSap(array $sapData, SapServiceLayerClient $sap, int $agendaId): void
    {
        $db = Database::getConnection();
        $locked = 0; $alreadyLocked = 0; $errors = [];
        foreach ($sapData['DocumentLines'] ?? [] as $line) {
            $itemCode = $line['ItemCode'] ?? null;
            $batches  = $line['BatchNumbers'] ?? [];
            if (!$itemCode || !$batches) continue;
            foreach ($batches as $b) {
                $batchNum = $b['BatchNumber'] ?? null;
                if (!$batchNum) continue;
                try {
                    $ic = str_replace("'", "''", (string)$itemCode);
                    $bn = str_replace("'", "''", (string)$batchNum);
                    $r  = $sap->get("/BatchNumberDetails?\$filter=ItemCode eq '{$ic}' and Batch eq '{$bn}'&\$top=1&\$select=DocEntry,Status");
                    $row = $r['value'][0] ?? null;
                    if (!$row) { $errors[] = "no encontrado: {$itemCode}/{$batchNum}"; continue; }
                    if (($row['Status'] ?? '') === 'bdsStatus_Locked') { $alreadyLocked++; continue; }
                    $sap->patch("/BatchNumberDetails({$row['DocEntry']})", ['Status' => 'bdsStatus_Locked']);
                    $locked++;
                } catch (\Throwable $e) {
                    $errors[] = "{$itemCode}/{$batchNum}: " . $e->getMessage();
                }
            }
        }
        $logMsg = "locked={$locked} already_locked={$alreadyLocked} errors=" . count($errors);
        if ($errors) $logMsg .= " details: " . substr(implode(' | ', $errors), 0, 500);
        try {
            $db->prepare(
                "INSERT INTO sap_sync_log (sync_type, direction, sap_doc_type, wms_reference_id, status, error_message)
                 VALUES ('BATCH_LOCK','WMS_TO_SAP','BatchNumberDetails', ?, ?, ?)"
            )->execute([$agendaId, $errors ? 'WARN' : 'SUCCESS', $logMsg]);
        } catch (\Throwable $e) {
            error_log('[AgendaService::lockBatchesInSap] log fail: ' . $e->getMessage());
        }
    }

    /**
     * Genera el Acta de recepción en PDF y la adjunta al PDN en SAP via /Attachments2.
     * Errores NO revierten la PDN: se loguean como WARN en sap_sync_log.
     */
    private function attachActaToPdn(int $agendaId, int $pdnDocEntry, SapServiceLayerClient $sap, int $userId): void
    {
        // Backward-compat: PDN es el target por defecto.
        $this->attachActaToSapDoc($agendaId, $pdnDocEntry, '/PurchaseDeliveryNotes', $sap, $userId);
    }

    /**
     * Variante generalizada: el target puede ser /PurchaseDeliveryNotes (OPDN)
     * o /StockTransfers (OWTR). Mantiene el comportamiento de logging y la
     * idea de que un fallo de attach no revierte la PDN/ST creada.
     */
    private function attachActaToSapDoc(int $agendaId, int $docEntry, string $sapEndpoint, SapServiceLayerClient $sap, int $userId): void
    {
        $db = Database::getConnection();
        $endpointTag = ltrim($sapEndpoint, '/'); // 'PurchaseDeliveryNotes' o 'StockTransfers'
        try {
            $svc = new ReceptionPdfService();
            $pdfPath = $svc->generate($agendaId, $userId);
            $fileSize = file_exists($pdfPath) ? filesize($pdfPath) : 0;
            try {
                $absEntry = $sap->uploadAttachment($pdfPath, basename($pdfPath));
                $sap->patch("{$sapEndpoint}({$docEntry})", ['AttachmentEntry' => $absEntry]);
                $db->prepare(
                    "INSERT INTO sap_sync_log (sync_type, direction, sap_doc_entry, sap_doc_type, wms_reference_id, status, error_message)
                     VALUES ('BATCH','WMS_TO_SAP', ?, 'Acta_PDF', ?, 'SUCCESS', ?)"
                )->execute([$docEntry, $agendaId, "endpoint={$endpointTag} acta={$pdfPath} attachment_entry={$absEntry} size={$fileSize}"]);
            } catch (\Throwable $eAttach) {
                $db->prepare(
                    "INSERT INTO sap_sync_log (sync_type, direction, sap_doc_entry, sap_doc_type, wms_reference_id, status, error_message)
                     VALUES ('BATCH','WMS_TO_SAP', ?, 'Acta_PDF', ?, 'WARN', ?)"
                )->execute([$docEntry, $agendaId, "endpoint={$endpointTag} acta_local_ok={$pdfPath} sap_attach_fail=" . $eAttach->getMessage()]);
            }
        } catch (\Throwable $e) {
            $db->prepare(
                "INSERT INTO sap_sync_log (sync_type, direction, sap_doc_type, wms_reference_id, status, error_message)
                 VALUES ('BATCH','WMS_TO_SAP','Acta_PDF', ?, 'ERROR', ?)"
            )->execute([$agendaId, "endpoint={$endpointTag} acta_gen_fail=" . $e->getMessage()]);
        }
    }

    /**
     * Reconcilia el stock de una agenda ya cerrada (idempotente).
     * Útil cuando una agenda se cerró antes de un fix y el stock no se generó
     * (o quedó parcial). Verifica si ya hay movimientos de la agenda y aborta
     * para no duplicar.
     */
    public function reconcileStock(int $agendaId, int $userId): array
    {
        $agenda = $this->agendaRepo->findById($agendaId);
        if (!$agenda) {
            throw new \RuntimeException('Agenda no encontrada');
        }
        if ($agenda['status'] !== 'CLOSED') {
            throw new \RuntimeException('Solo se reconcilia stock de agendas CERRADAS (estado actual: ' . $agenda['status'] . ')');
        }

        $db = Database::getConnection();

        // Idempotencia: si ya hay movimientos generados por esta agenda, abortar
        $stmt = $db->prepare(
            "SELECT COUNT(*) FROM stock_movements
             WHERE reference_type IN ('RECEPTION_AGENDA','AGENDA') AND reference_id = ?"
        );
        $stmt->execute([$agendaId]);
        $existing = (int) $stmt->fetchColumn();
        if ($existing > 0) {
            throw new \RuntimeException("Ya existen {$existing} movimientos de stock para esta agenda. Reconciliación abortada.");
        }

        // Verificar que tiene escaneos para reconciliar
        $stmt = $db->prepare("SELECT COUNT(*) FROM reception_scans WHERE agenda_id = ?");
        $stmt->execute([$agendaId]);
        $scanCount = (int) $stmt->fetchColumn();
        if ($scanCount === 0) {
            throw new \RuntimeException('La agenda no tiene escaneos registrados.');
        }

        $beforeStock = (int) $db->query("SELECT COUNT(*) FROM stock")->fetchColumn();

        $db->beginTransaction();
        try {
            $this->createStockEntries($agendaId, (int) $agenda['warehouse_id'], $userId);
            $db->commit();
        } catch (\Throwable $e) {
            $db->rollBack();
            throw new \RuntimeException('Error reconciliando stock: ' . $e->getMessage());
        }

        $afterStock = (int) $db->query("SELECT COUNT(*) FROM stock")->fetchColumn();
        $created = $afterStock - $beforeStock;

        AuditService::log('reception_agendas', $agendaId, 'AGENDA_RECONCILE_STOCK', null, [
            'agenda_number'   => $agenda['agenda_number'] ?? null,
            'scans_processed' => $scanCount,
            'stock_records_created' => $created,
        ], $userId);

        return [
            'agenda_id'             => $agendaId,
            'agenda_number'         => $agenda['agenda_number'] ?? null,
            'scans_processed'       => $scanCount,
            'stock_records_created' => $created,
        ];
    }

    /**
     * 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,
            ]);

            // Auditoría DINAVISA: si el batch entra a cuarentena, registrar evento explícito
            // sobre el batch para que el botón 📜 del módulo QA muestre el ingreso a QA.
            if ($batchId && $initialStatus === 'QA') {
                AuditService::log('batches', (int)$batchId, 'BATCH_INTO_QA', null, [
                    'agenda_id'    => $agendaId,
                    'item_id'      => (int)$item['id'],
                    'item_code'    => $item['item_code'] ?? null,
                    'batch_number' => $group['batch_number'] ?? null,
                    'expiry_date'  => $group['expiry_date'] ?? null,
                    'warehouse_id' => $warehouseId,
                    'bin_id'       => $binId,
                    'quantity'     => $qty,
                    'reason'       => 'Recepción de mercadería — cuarentena automática',
                ], $userId);
            } elseif ($batchId) {
                AuditService::log('batches', (int)$batchId, 'BATCH_RECEIVED', null, [
                    'agenda_id'    => $agendaId,
                    'item_code'    => $item['item_code'] ?? null,
                    'batch_number' => $group['batch_number'] ?? null,
                    'expiry_date'  => $group['expiry_date'] ?? null,
                    'warehouse_id' => $warehouseId,
                    'bin_id'       => $binId,
                    'quantity'     => $qty,
                    'initial_status' => $initialStatus,
                ], $userId);
            }
        }
    }

    /**
     * Tras cerrar una agenda, genera tareas de PUTAWAY para todo el stock recién
     * creado en bins RECEIVING. Auto-asigna a un op_almacen online.
     * Preserva el status real (AVAILABLE / QA / BLOCKED).
     */
    private function createPutawayTasksFromAgenda(int $agendaId, int $warehouseId, int $userId): array
    {
        $db = Database::getConnection();

        $stmt = $db->prepare(
            "SELECT DISTINCT s.warehouse_id, s.bin_id, s.item_id, s.batch_id, s.quantity, s.uom, s.stock_status
             FROM stock s
             JOIN bins b ON b.id = s.bin_id
             JOIN stock_movements m ON m.warehouse_id = s.warehouse_id
                                    AND m.to_bin_id  = s.bin_id
                                    AND m.item_id    = s.item_id
                                    AND (m.batch_id <=> s.batch_id)
                                    AND m.reference_type IN ('RECEPTION_AGENDA','AGENDA')
                                    AND m.reference_id = ?
             WHERE s.warehouse_id = ?
               AND s.quantity > 0
               AND b.bin_type = 'RECEIVING'"
        );
        $stmt->execute([$agendaId, $warehouseId]);
        $rows = $stmt->fetchAll();

        $tasks = [];
        foreach ($rows as $sp) {
            // Skip si ya hay tarea pendiente para mismo batch+bin
            $chk = $db->prepare(
                "SELECT id FROM warehouse_tasks
                 WHERE task_type='PUTAWAY' AND batch_id=? AND from_bin_id=?
                   AND status IN ('PENDING','ASSIGNED','IN_PROGRESS') LIMIT 1"
            );
            $chk->execute([$sp['batch_id'], $sp['bin_id']]);
            if ($chk->fetch()) continue;

            $assignee = $this->pickOnlineOpAlmacen($db, $warehouseId);
            $isQA = ($sp['stock_status'] === 'QA');
            $refType = $isQA ? 'QA_PUTAWAY_HOLD' : 'GOODS_RECEIPT';
            $note    = $isQA
                ? 'Putaway en cuarentena (auto agenda): stock con status QA hasta liberación'
                : 'Putaway auto generado al cerrar agenda';

            $ins = $db->prepare(
                "INSERT INTO warehouse_tasks
                 (warehouse_id, task_type, priority, item_id, batch_id, from_bin_id, quantity, uom,
                  reference_type, reference_id, status, assigned_to, notes, created_by)
                 VALUES (?, 'PUTAWAY', 2, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            $ins->execute([
                $warehouseId, $sp['item_id'], $sp['batch_id'],
                $sp['bin_id'], $sp['quantity'], $sp['uom'],
                $refType, $agendaId,
                $assignee ? 'ASSIGNED' : 'PENDING',
                $assignee, $note, $userId,
            ]);
            $tasks[] = [
                'task_id'     => (int) $db->lastInsertId(),
                'assigned_to' => $assignee,
                'from_bin_id' => (int) $sp['bin_id'],
                'quantity'    => (float) $sp['quantity'],
                'status'      => $sp['stock_status'],
            ];
        }
        return $tasks;
    }

    /**
     * Selecciona op_almacen online (last_activity_at <= 5 min) con menor carga.
     * Fallback: cualquier op_almacen activo si no hay nadie online.
     */
    private function pickOnlineOpAlmacen(\PDO $db, int $warehouseId): ?int
    {
        $sql = "SELECT u.id, COALESCE(SUM(CASE WHEN t.status IN ('PENDING','ASSIGNED','IN_PROGRESS') THEN 1 ELSE 0 END), 0) AS load_active
                FROM users u
                LEFT JOIN warehouse_tasks t ON t.assigned_to = u.id
                WHERE u.role = 'op_almacen' AND u.is_active = 1
                  AND u.last_activity_at >= (NOW() - INTERVAL 5 MINUTE)
                  AND (u.warehouse_id = ? OR u.warehouse_id IS NULL)
                GROUP BY u.id
                ORDER BY load_active ASC, u.last_activity_at DESC, u.id ASC LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->execute([$warehouseId]);
        $row = $stmt->fetch();
        if ($row) return (int) $row['id'];

        $stmt = $db->prepare(
            "SELECT u.id FROM users u
             WHERE u.role = 'op_almacen' AND u.is_active = 1
               AND (u.warehouse_id = ? OR u.warehouse_id IS NULL)
             ORDER BY u.id ASC LIMIT 1"
        );
        $stmt->execute([$warehouseId]);
        $row = $stmt->fetch();
        return $row ? (int) $row['id'] : null;
    }
}