<?php

namespace WMS\Services;

use WMS\Core\Database;

class ReportService
{
    public function stockReport(?string $warehouseId = null, ?string $status = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT i.item_code AS 'Codigo', i.item_name AS 'Producto',
                       bt.batch_number AS 'Lote', bt.expiry_date AS 'Vencimiento',
                       s.quantity AS 'Cantidad', s.reserved_qty AS 'Reservado',
                       (s.quantity - s.reserved_qty) AS 'Disponible',
                       s.stock_status AS 'Estado', s.uom AS 'UOM',
                       w.code AS 'Almacen', b.code AS 'Ubicacion'
                FROM stock s
                LEFT JOIN items i ON s.item_id = i.id
                LEFT JOIN batches bt ON s.batch_id = bt.id
                LEFT JOIN warehouses w ON s.warehouse_id = w.id
                LEFT JOIN bins b ON s.bin_id = b.id
                WHERE s.quantity > 0";
        $params = [];
        if ($warehouseId) { $sql .= " AND s.warehouse_id = ?"; $params[] = $warehouseId; }
        if ($status) { $sql .= " AND s.stock_status = ?"; $params[] = $status; }
        $sql .= " ORDER BY i.item_code, bt.expiry_date";
        return $this->toCsv($db, $sql, $params);
    }

    public function movementsReport(?string $from = null, ?string $to = null, ?string $type = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT sm.created_at AS 'Fecha',
                       sm.movement_type AS 'Tipo Movimiento',
                       i.item_code AS 'Codigo', i.item_name AS 'Producto',
                       bt.batch_number AS 'Lote',
                       sm.quantity AS 'Cantidad',
                       fb.code AS 'Desde', tb.code AS 'Hacia',
                       sm.from_status AS 'Estado Origen', sm.to_status AS 'Estado Destino',
                       sm.reference_type AS 'Ref. Tipo', sm.reference_id AS 'Ref. ID',
                       u.full_name AS 'Operario'
                FROM stock_movements sm
                LEFT JOIN items i ON sm.item_id = i.id
                LEFT JOIN batches bt ON sm.batch_id = bt.id
                LEFT JOIN bins fb ON sm.from_bin_id = fb.id
                LEFT JOIN bins tb ON sm.to_bin_id = tb.id
                LEFT JOIN users u ON sm.created_by = u.id
                WHERE 1=1";
        $params = [];
        if ($from) { $sql .= " AND sm.created_at >= ?"; $params[] = $from; }
        if ($to) { $sql .= " AND sm.created_at <= ?"; $params[] = $to . ' 23:59:59'; }
        if ($type) { $sql .= " AND sm.movement_type = ?"; $params[] = $type; }
        $sql .= " ORDER BY sm.created_at DESC LIMIT 5000";
        return $this->toCsv($db, $sql, $params);
    }

    public function recallsReport(?string $status = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT r.recall_number AS 'Nro Recall',
                       r.item_code AS 'Codigo', r.item_name AS 'Producto',
                       r.batch_number AS 'Lote',
                       r.reason AS 'Motivo',
                       r.total_stock_found AS 'Stock Encontrado',
                       r.total_customers_affected AS 'Clientes Afectados',
                       r.status AS 'Estado',
                       u.full_name AS 'Iniciado Por',
                       r.created_at AS 'Fecha Inicio',
                       r.completed_at AS 'Fecha Cierre'
                FROM recalls r
                LEFT JOIN users u ON r.initiated_by = u.id
                WHERE 1=1";
        $params = [];
        if ($status) { $sql .= " AND r.status = ?"; $params[] = $status; }
        $sql .= " ORDER BY r.created_at DESC";

        $csv = $this->toCsv($db, $sql, $params);

        // Add recall results detail
        $recalls = $db->query("SELECT id, recall_number FROM recalls ORDER BY created_at DESC")->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($recalls as $rc) {
            $results = $db->prepare("SELECT * FROM recall_results WHERE recall_id = ?");
            $results->execute([$rc['id']]);
            $rows = $results->fetchAll(\PDO::FETCH_ASSOC);
            if ($rows) {
                $csv .= "\n\nDetalle Recall " . $rc['recall_number'] . "\n";
                $csv .= "Tipo;Almacen;Ubicacion;Cantidad;Estado;Cliente;Despacho;Fecha\n";
                foreach ($rows as $rr) {
                    $csv .= implode(';', [
                        $rr['result_type'], $rr['warehouse_code'] ?? '', $rr['bin_code'] ?? '',
                        $rr['quantity'] ?? '', $rr['stock_status'] ?? '',
                        $rr['customer_name'] ?? '', $rr['dispatch_number'] ?? '',
                        $rr['movement_date'] ?? $rr['dispatch_date'] ?? $rr['created_at']
                    ]) . "\n";
                }
            }
        }
        return $csv;
    }

    public function expiringReport(int $days = 90): string
    {
        $db = Database::getConnection();
        $sql = "SELECT i.item_code AS 'Codigo', i.item_name AS 'Producto',
                       bt.batch_number AS 'Lote', bt.expiry_date AS 'Vencimiento',
                       DATEDIFF(bt.expiry_date, CURDATE()) AS 'Dias Restantes',
                       s.quantity AS 'Cantidad', s.stock_status AS 'Estado',
                       w.code AS 'Almacen', b.code AS 'Ubicacion'
                FROM stock s
                JOIN batches bt ON s.batch_id = bt.id
                JOIN items i ON s.item_id = i.id
                LEFT JOIN warehouses w ON s.warehouse_id = w.id
                LEFT JOIN bins b ON s.bin_id = b.id
                WHERE bt.expiry_date IS NOT NULL
                  AND bt.expiry_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
                  AND s.quantity > 0
                ORDER BY bt.expiry_date ASC";
        return $this->toCsv($db, $sql, [$days]);
    }

    public function temperatureReport(?string $zoneId = null, ?string $from = null, ?string $to = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT tz.zone_name AS 'Zona',
                       tr.temperature AS 'Temperatura °C',
                       tr.humidity AS 'Humedad %',
                       tz.min_temp AS 'Min Permitido',
                       tz.max_temp AS 'Max Permitido',
                       tr.status AS 'Estado',
                       tr.reading_source AS 'Fuente',
                       u.full_name AS 'Registrado Por',
                       tr.recorded_at AS 'Fecha',
                       tr.notes AS 'Notas'
                FROM temperature_readings tr
                LEFT JOIN temperature_zones tz ON tr.zone_id = tz.id
                LEFT JOIN users u ON tr.recorded_by = u.id
                WHERE 1=1";
        $params = [];
        if ($zoneId) { $sql .= " AND tr.zone_id = ?"; $params[] = $zoneId; }
        if ($from) { $sql .= " AND tr.recorded_at >= ?"; $params[] = $from; }
        if ($to) { $sql .= " AND tr.recorded_at <= ?"; $params[] = $to . ' 23:59:59'; }
        $sql .= " ORDER BY tr.recorded_at DESC LIMIT 5000";
        return $this->toCsv($db, $sql, $params);
    }

    public function inventoryReport(?string $warehouseId = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT w.code AS 'Almacen', w.name AS 'Nombre Almacen',
                       b.code AS 'Ubicacion',
                       i.item_code AS 'Codigo', i.item_name AS 'Producto',
                       i.uom AS 'UOM',
                       bt.batch_number AS 'Lote', bt.expiry_date AS 'Vencimiento',
                       DATEDIFF(bt.expiry_date, CURDATE()) AS 'Dias Venc.',
                       s.quantity AS 'Cantidad', s.reserved_qty AS 'Reservado',
                       (s.quantity - s.reserved_qty) AS 'Disponible',
                       s.stock_status AS 'Estado'
                FROM stock s
                JOIN items i ON s.item_id = i.id
                LEFT JOIN batches bt ON s.batch_id = bt.id
                JOIN warehouses w ON s.warehouse_id = w.id
                LEFT JOIN bins b ON s.bin_id = b.id
                WHERE s.quantity > 0";
        $params = [];
        if ($warehouseId) { $sql .= " AND s.warehouse_id = ?"; $params[] = $warehouseId; }
        $sql .= " ORDER BY w.code, b.code, i.item_code";
        return $this->toCsv($db, $sql, $params);
    }

    private function toCsv(\PDO $db, string $sql, array $params = []): string
    {
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        if (empty($rows)) return "Sin datos\n";

        $csv = implode(';', array_keys($rows[0])) . "\n";
        foreach ($rows as $row) {
            $csv .= implode(';', array_map(function ($v) {
                if ($v === null) return '';
                $v = str_replace(['"', ';', "\n", "\r"], ['""', ',', ' ', ''], (string)$v);
                return $v;
            }, array_values($row))) . "\n";
        }
        return $csv;
    }

    public function auditTrailReport(?string $from = null, ?string $to = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT sm.created_at AS 'Fecha y Hora',
                       sm.movement_type AS 'Tipo',
                       i.item_code AS 'Codigo Producto', i.item_name AS 'Producto',
                       bt.batch_number AS 'Lote', bt.expiry_date AS 'Vencimiento',
                       sm.quantity AS 'Cantidad',
                       fb.code AS 'Ubicacion Origen', tb.code AS 'Ubicacion Destino',
                       sm.from_status AS 'Estado Anterior', sm.to_status AS 'Estado Nuevo',
                       sm.reference_type AS 'Tipo Referencia', sm.reference_id AS 'ID Referencia',
                       u.full_name AS 'Usuario', u.username AS 'Login'
                FROM stock_movements sm
                LEFT JOIN items i ON sm.item_id = i.id
                LEFT JOIN batches bt ON sm.batch_id = bt.id
                LEFT JOIN bins fb ON sm.from_bin_id = fb.id
                LEFT JOIN bins tb ON sm.to_bin_id = tb.id
                LEFT JOIN users u ON sm.created_by = u.id
                WHERE 1=1";
        $params = [];
        if ($from) { $sql .= " AND sm.created_at >= ?"; $params[] = $from; }
        if ($to) { $sql .= " AND sm.created_at <= ?"; $params[] = $to . ' 23:59:59'; }
        $sql .= " ORDER BY sm.created_at DESC LIMIT 10000";
        return $this->toCsv($db, $sql, $params);
    }

    public function batchTraceReport(string $batchNumber): string
    {
        $db = Database::getConnection();

        $csv = "REPORTE DE TRAZABILIDAD DE LOTE\n";
        $csv .= "Lote: {$batchNumber}\n";
        $csv .= "Fecha generacion: " . date('Y-m-d H:i:s') . "\n\n";

        // Batch info
        $csv .= "INFORMACION DEL LOTE\n";
        $csv .= "Lote;Producto;Codigo;Vencimiento;Estado;Proveedor\n";
        $stmt = $db->prepare(
            "SELECT bt.batch_number, i.item_name, i.item_code, bt.expiry_date, bt.status, bt.supplier_batch
             FROM batches bt JOIN items i ON bt.item_id = i.id WHERE bt.batch_number = ?"
        );
        $stmt->execute([$batchNumber]);
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $r) {
            $csv .= implode(';', array_values($r)) . "\n";
        }

        // Current stock
        $csv .= "\nSTOCK ACTUAL\n";
        $csv .= "Almacen;Ubicacion;Cantidad;Estado\n";
        $stmt2 = $db->prepare(
            "SELECT w.code, b.code, s.quantity, s.stock_status
             FROM stock s
             JOIN batches bt ON s.batch_id = bt.id
             LEFT JOIN warehouses w ON s.warehouse_id = w.id
             LEFT JOIN bins b ON s.bin_id = b.id
             WHERE bt.batch_number = ? AND s.quantity > 0"
        );
        $stmt2->execute([$batchNumber]);
        foreach ($stmt2->fetchAll(\PDO::FETCH_ASSOC) as $r) {
            $csv .= implode(';', array_values($r)) . "\n";
        }

        // All movements
        $csv .= "\nHISTORIAL DE MOVIMIENTOS\n";
        $csv .= "Fecha;Tipo;Cantidad;Desde;Hacia;Estado Ant.;Estado Nuevo;Usuario\n";
        $stmt3 = $db->prepare(
            "SELECT sm.created_at, sm.movement_type, sm.quantity,
                    fb.code, tb.code, sm.from_status, sm.to_status, u.full_name
             FROM stock_movements sm
             JOIN batches bt ON sm.batch_id = bt.id
             LEFT JOIN bins fb ON sm.from_bin_id = fb.id
             LEFT JOIN bins tb ON sm.to_bin_id = tb.id
             LEFT JOIN users u ON sm.created_by = u.id
             WHERE bt.batch_number = ? ORDER BY sm.created_at"
        );
        $stmt3->execute([$batchNumber]);
        foreach ($stmt3->fetchAll(\PDO::FETCH_ASSOC) as $r) {
            $csv .= implode(';', array_map(fn($v) => $v ?? '', array_values($r))) . "\n";
        }

        // Dispatches/customers
        $csv .= "\nDESPACHOS Y CLIENTES\n";
        $csv .= "Despacho;Camion;Cliente;Fecha\n";
        try {
            $stmt4 = $db->prepare(
                "SELECT d.dispatch_number, d.truck_plate, pl.customer_name, d.dispatched_at
                 FROM dispatches d
                 JOIN dispatch_lines dl ON d.id = dl.dispatch_id
                 JOIN packing_orders po ON dl.packing_order_id = po.id
                 JOIN packing_lines pkl ON po.id = pkl.packing_order_id
                 JOIN batches bt ON pkl.batch_id = bt.id
                 WHERE bt.batch_number = ? GROUP BY d.id"
            );
            $stmt4->execute([$batchNumber]);
            foreach ($stmt4->fetchAll(\PDO::FETCH_ASSOC) as $r) {
                $csv .= implode(';', array_map(fn($v) => $v ?? '', array_values($r))) . "\n";
            }
        } catch (\Exception $e) {}

        return $csv;
    }

    public function accessLogReport(?string $from = null, ?string $to = null): string
    {
        $db = Database::getConnection();
        $sql = "SELECT created_at AS 'Fecha y Hora',
                       username AS 'Usuario', full_name AS 'Nombre Completo',
                       action AS 'Accion',
                       ip_address AS 'Direccion IP',
                       user_agent AS 'Navegador/Dispositivo'
                FROM access_log WHERE 1=1";
        $params = [];
        if ($from) { $sql .= " AND created_at >= ?"; $params[] = $from; }
        if ($to) { $sql .= " AND created_at <= ?"; $params[] = $to . ' 23:59:59'; }
        $sql .= " ORDER BY created_at DESC LIMIT 5000";
        return $this->toCsv($db, $sql, $params);
    }


    private function queryToArray(\PDO $db, string $sql, array $params = []): array
    {
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function stockData(?string $warehouseId = null, ?string $status = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT i.item_code, i.item_name, bt.batch_number, bt.expiry_date,
                       s.quantity, s.reserved_qty, (s.quantity - s.reserved_qty) as disponible,
                       s.stock_status, s.uom, w.code as almacen, b.code as ubicacion
                FROM stock s LEFT JOIN items i ON s.item_id = i.id LEFT JOIN batches bt ON s.batch_id = bt.id
                LEFT JOIN warehouses w ON s.warehouse_id = w.id LEFT JOIN bins b ON s.bin_id = b.id WHERE s.quantity > 0";
        $p = [];
        if ($warehouseId) { $sql .= " AND s.warehouse_id = ?"; $p[] = $warehouseId; }
        if ($status) { $sql .= " AND s.stock_status = ?"; $p[] = $status; }
        $sql .= " ORDER BY i.item_code";
        return ['headers'=>['Codigo','Producto','Lote','Vencimiento','Cantidad','Reservado','Disponible','Estado','UOM','Almacen','Ubicacion'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function movementsData(?string $from = null, ?string $to = null, ?string $type = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT sm.created_at, sm.movement_type, i.item_code, i.item_name, bt.batch_number,
                       sm.quantity, fb.code as desde, tb.code as hacia, sm.from_status, sm.to_status, u.full_name as operario
                FROM stock_movements sm LEFT JOIN items i ON sm.item_id = i.id LEFT JOIN batches bt ON sm.batch_id = bt.id
                LEFT JOIN bins fb ON sm.from_bin_id = fb.id LEFT JOIN bins tb ON sm.to_bin_id = tb.id
                LEFT JOIN users u ON sm.created_by = u.id WHERE 1=1";
        $p = [];
        if ($from) { $sql .= " AND sm.created_at >= ?"; $p[] = $from; }
        if ($to) { $sql .= " AND sm.created_at <= ?"; $p[] = $to . ' 23:59:59'; }
        if ($type) { $sql .= " AND sm.movement_type = ?"; $p[] = $type; }
        $sql .= " ORDER BY sm.created_at DESC LIMIT 5000";
        return ['headers'=>['Fecha','Tipo','Codigo','Producto','Lote','Cantidad','Desde','Hacia','Estado Ant.','Estado Nuevo','Operario'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function recallsData(?string $status = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT r.recall_number, r.item_code, r.item_name, r.batch_number, r.reason,
                       r.total_stock_found, r.total_customers_affected, r.status, u.full_name as iniciado_por, r.created_at, r.completed_at
                FROM recalls r LEFT JOIN users u ON r.initiated_by = u.id WHERE 1=1";
        $p = [];
        if ($status) { $sql .= " AND r.status = ?"; $p[] = $status; }
        $sql .= " ORDER BY r.created_at DESC";
        return ['headers'=>['Nro Recall','Codigo','Producto','Lote','Motivo','Stock','Clientes','Estado','Iniciado Por','Fecha','Cierre'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function expiringData(int $days = 90): array
    {
        $db = Database::getConnection();
        $sql = "SELECT i.item_code, i.item_name, bt.batch_number, bt.expiry_date, DATEDIFF(bt.expiry_date, CURDATE()) as dias,
                       s.quantity, s.stock_status, w.code as almacen, b.code as ubicacion
                FROM stock s JOIN batches bt ON s.batch_id = bt.id JOIN items i ON s.item_id = i.id
                LEFT JOIN warehouses w ON s.warehouse_id = w.id LEFT JOIN bins b ON s.bin_id = b.id
                WHERE bt.expiry_date IS NOT NULL AND bt.expiry_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
                AND bt.expiry_date >= CURDATE() AND s.quantity > 0 ORDER BY bt.expiry_date";
        return ['headers'=>['Codigo','Producto','Lote','Vencimiento','Dias','Cantidad','Estado','Almacen','Ubicacion'], 'rows'=>$this->queryToArray($db, $sql, [$days])];
    }

    public function temperatureData(?string $zoneId = null, ?string $from = null, ?string $to = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT tz.zone_name, tr.temperature, tr.humidity, tz.min_temp, tz.max_temp, tr.status,
                       tr.reading_source, u.full_name as registrado_por, tr.recorded_at
                FROM temperature_readings tr LEFT JOIN temperature_zones tz ON tr.zone_id = tz.id
                LEFT JOIN users u ON tr.recorded_by = u.id WHERE 1=1";
        $p = [];
        if ($zoneId) { $sql .= " AND tr.zone_id = ?"; $p[] = $zoneId; }
        if ($from) { $sql .= " AND tr.recorded_at >= ?"; $p[] = $from; }
        if ($to) { $sql .= " AND tr.recorded_at <= ?"; $p[] = $to . ' 23:59:59'; }
        $sql .= " ORDER BY tr.recorded_at DESC LIMIT 5000";
        return ['headers'=>['Zona','Temp °C','Humedad %','Min','Max','Estado','Fuente','Registrado Por','Fecha'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function inventoryData(?string $warehouseId = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT w.code as almacen, w.name as nombre, b.code as ubicacion, i.item_code, i.item_name, i.uom,
                       bt.batch_number, bt.expiry_date, DATEDIFF(bt.expiry_date, CURDATE()) as dias,
                       s.quantity, s.reserved_qty, (s.quantity - s.reserved_qty) as disponible, s.stock_status
                FROM stock s JOIN items i ON s.item_id = i.id LEFT JOIN batches bt ON s.batch_id = bt.id
                JOIN warehouses w ON s.warehouse_id = w.id LEFT JOIN bins b ON s.bin_id = b.id WHERE s.quantity > 0";
        $p = [];
        if ($warehouseId) { $sql .= " AND s.warehouse_id = ?"; $p[] = $warehouseId; }
        $sql .= " ORDER BY w.code, b.code, i.item_code";
        return ['headers'=>['Almacen','Nombre','Ubicacion','Codigo','Producto','UOM','Lote','Vencimiento','Dias','Cantidad','Reservado','Disponible','Estado'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function auditTrailData(?string $from = null, ?string $to = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT sm.created_at, sm.movement_type, i.item_code, i.item_name, bt.batch_number, bt.expiry_date,
                       sm.quantity, fb.code as desde, tb.code as hacia, sm.from_status, sm.to_status, u.full_name as usuario
                FROM stock_movements sm LEFT JOIN items i ON sm.item_id = i.id LEFT JOIN batches bt ON sm.batch_id = bt.id
                LEFT JOIN bins fb ON sm.from_bin_id = fb.id LEFT JOIN bins tb ON sm.to_bin_id = tb.id
                LEFT JOIN users u ON sm.created_by = u.id WHERE 1=1";
        $p = [];
        if ($from) { $sql .= " AND sm.created_at >= ?"; $p[] = $from; }
        if ($to) { $sql .= " AND sm.created_at <= ?"; $p[] = $to . ' 23:59:59'; }
        $sql .= " ORDER BY sm.created_at DESC LIMIT 10000";
        return ['headers'=>['Fecha','Tipo','Codigo','Producto','Lote','Vencimiento','Cantidad','Desde','Hacia','Est.Ant.','Est.Nuevo','Usuario'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function accessLogData(?string $from = null, ?string $to = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT created_at, username, full_name, action, ip_address, user_agent FROM access_log WHERE 1=1";
        $p = [];
        if ($from) { $sql .= " AND created_at >= ?"; $p[] = $from; }
        if ($to) { $sql .= " AND created_at <= ?"; $p[] = $to . ' 23:59:59'; }
        $sql .= " ORDER BY created_at DESC LIMIT 5000";
        return ['headers'=>['Fecha','Usuario','Nombre','Accion','IP','Navegador'], 'rows'=>$this->queryToArray($db, $sql, $p)];
    }

    public function batchTraceData(string $batchNumber): array
    {
        $db = Database::getConnection();
        $result = ['batch' => $batchNumber, 'info' => [], 'stock' => [], 'movements' => [], 'dispatches' => []];
        $stmt = $db->prepare("SELECT bt.batch_number, i.item_name, i.item_code, bt.expiry_date, bt.status, bt.supplier_batch
                              FROM batches bt JOIN items i ON bt.item_id = i.id WHERE bt.batch_number = ?");
        $stmt->execute([$batchNumber]);
        $result['info'] = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $stmt2 = $db->prepare("SELECT w.code as almacen, b.code as ubicacion, s.quantity, s.stock_status
                               FROM stock s JOIN batches bt ON s.batch_id = bt.id LEFT JOIN warehouses w ON s.warehouse_id = w.id
                               LEFT JOIN bins b ON s.bin_id = b.id WHERE bt.batch_number = ? AND s.quantity > 0");
        $stmt2->execute([$batchNumber]);
        $result['stock'] = $stmt2->fetchAll(\PDO::FETCH_ASSOC);

        $stmt3 = $db->prepare("SELECT sm.created_at, sm.movement_type, sm.quantity, fb.code as desde, tb.code as hacia, u.full_name as operario
                               FROM stock_movements sm JOIN batches bt ON sm.batch_id = bt.id
                               LEFT JOIN bins fb ON sm.from_bin_id = fb.id LEFT JOIN bins tb ON sm.to_bin_id = tb.id
                               LEFT JOIN users u ON sm.created_by = u.id WHERE bt.batch_number = ? ORDER BY sm.created_at");
        $stmt3->execute([$batchNumber]);
        $result['movements'] = $stmt3->fetchAll(\PDO::FETCH_ASSOC);

        return ['headers'=>['Trazabilidad completa del lote: '.$batchNumber], 'trace'=>$result];
    }

    public function transferRequestsData(?string $status = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT r.sap_doc_num AS 'ST SAP', a.agenda_number AS 'Agenda', w.name AS 'Destino',
                       r.supplier_name AS 'Descripcion', DATE_FORMAT(r.created_at,'%Y-%m-%d') AS 'Importada',
                       a.status AS 'Estado Agenda', r.status AS 'Estado ST',
                       COUNT(l.id) AS 'Items',
                       ROUND(COALESCE(SUM(l.expected_qty),0),3) AS 'Esperado',
                       ROUND(COALESCE(SUM(l.received_qty),0),3) AS 'Recibido'
                FROM reception_agenda_refs r
                JOIN reception_agendas a ON a.id = r.agenda_id
                LEFT JOIN warehouses w ON w.id = a.warehouse_id
                LEFT JOIN reception_agenda_lines l ON l.agenda_id = a.id
                WHERE r.sap_doc_type = 'InventoryTransferRequest'";
        $p = [];
        if ($status) { $sql .= " AND r.status = ?"; $p[] = $status; }
        $sql .= " GROUP BY r.id, r.sap_doc_num, a.agenda_number, w.name, r.supplier_name, r.created_at, a.status, r.status
                  ORDER BY r.sap_doc_num+0 DESC, r.id DESC";
        return ['headers'=>['ST SAP','Agenda','Destino','Descripcion','Importada','Estado Agenda','Estado ST','Items','Esperado','Recibido'],
                'rows'=>$this->queryToArray($db, $sql, $p)];
    }
}