<?php
/**
 * GET /api/inventario/productos/exportar_real_time.php
 * Exporta el inventario en tiempo real a formato Excel (.xls / compatible con openxml)
 */
ini_set('display_errors', 0);
error_reporting(E_ALL);

require_once '../../config/database.php';
require_once '../../includes/helpers.php';

$pdo = getDBConnection();

try {
    $query = isset($_GET['query']) ? trim($_GET['query']) : '';
    $sucursal_id = isset($_GET['sucursal_id']) ? (int)$_GET['sucursal_id'] : 0; // 0 = Todas
    $solo_existencias = isset($_GET['solo_existencias']) ? (int)$_GET['solo_existencias'] : 1;
    $facturado = isset($_GET['facturado']) ? (int)$_GET['facturado'] : 1; // 1 = Con Factura, 0 = Sin Factura

    // 1. Obtener nombre de la sucursal activa
    $sucursal_nombre = "Todas las Sucursales";
    if ($sucursal_id > 0) {
        $stmtS = $pdo->prepare("SELECT nombre FROM sucursales WHERE id = ?");
        $stmtS->execute([$sucursal_id]);
        $sucursal_nombre = $stmtS->fetchColumn() ?: "Sucursal ID " . $sucursal_id;
    }

    // 2. Construir filtros de búsqueda
    $whereConditions = ["p.estado = 1"];
    $params = [];

    if (!empty($query)) {
        $whereConditions[] = "(p.CodigoInterno LIKE :query1 OR p.CodigoBarra LIKE :query2 OR p.NombreComercial LIKE :query3 OR p.NombreGenerico LIKE :query4 OR p.laboratorio LIKE :query5)";
        $params[':query1'] = "%{$query}%";
        $params[':query2'] = "%{$query}%";
        $params[':query3'] = "%{$query}%";
        $params[':query4'] = "%{$query}%";
        $params[':query5'] = "%{$query}%";
    }
    $whereClause = implode(" AND ", $whereConditions);

    // 3. Ejecutar consulta de datos sin límites de paginación
    $products = [];
    if ($sucursal_id === 0) {
        // Vista Consolidada
        $sql = "
            SELECT 
                p.id,
                COALESCE(p.CodigoInterno, '') as codigo,
                COALESCE(p.CodigoBarra, '') as codigo_barra,
                COALESCE(p.NombreComercial, 'Sin Nombre') as nombre,
                COALESCE(p.NombreGenerico, '') as nombre_generico,
                COALESCE(p.laboratorio, 'Genérico') as laboratorio,
                COALESCE(pcp.costo_promedio_unidad_base, 0) as costo_promedio,
                
                COALESCE(s1.stock_facturado, 0) as stock_f_1,
                COALESCE(s1.stock_no_facturado, 0) as stock_nf_1,
                s1.last_updated as last_updated_suc_1,
                
                COALESCE(s2.stock_facturado, 0) as stock_f_2,
                COALESCE(s2.stock_no_facturado, 0) as stock_nf_2,
                s2.last_updated as last_updated_suc_2,
                
                COALESCE(s3.stock_facturado, 0) as stock_f_3,
                COALESCE(s3.stock_no_facturado, 0) as stock_nf_3,
                s3.last_updated as last_updated_suc_3,
                
                COALESCE(s4.stock_facturado, 0) as stock_f_4,
                COALESCE(s4.stock_no_facturado, 0) as stock_nf_4,
                s4.last_updated as last_updated_suc_4,
                
                COALESCE(s5.stock_facturado, 0) as stock_f_5,
                COALESCE(s5.stock_no_facturado, 0) as stock_nf_5,
                s5.last_updated as last_updated_suc_5
            FROM productos p
            LEFT JOIN productos_costo_promedio pcp ON p.id = pcp.producto_id
            LEFT JOIN inventario_sucursal s1 ON p.id = s1.producto_id AND s1.sucursal_id = 1
            LEFT JOIN inventario_sucursal s2 ON p.id = s2.producto_id AND s2.sucursal_id = 2
            LEFT JOIN inventario_sucursal s3 ON p.id = s3.producto_id AND s3.sucursal_id = 3
            LEFT JOIN inventario_sucursal s4 ON p.id = s4.producto_id AND s4.sucursal_id = 4
            LEFT JOIN inventario_sucursal s5 ON p.id = s5.producto_id AND s5.sucursal_id = 5
            WHERE {$whereClause}
        ";

        if ($facturado === 1) {
            if ($solo_existencias === 1) {
                $sql .= " AND (COALESCE(s1.stock_facturado, 0) + COALESCE(s2.stock_facturado, 0) + COALESCE(s3.stock_facturado, 0) + COALESCE(s4.stock_facturado, 0) + COALESCE(s5.stock_facturado, 0)) > 0";
            }
        } else {
            if ($solo_existencias === 1) {
                $sql .= " AND (COALESCE(s1.stock_no_facturado, 0) + COALESCE(s2.stock_no_facturado, 0) + COALESCE(s3.stock_no_facturado, 0) + COALESCE(s4.stock_no_facturado, 0) + COALESCE(s5.stock_no_facturado, 0)) > 0";
            }
        }

        $sql .= " ORDER BY p.NombreComercial ASC";

        $stmt = $pdo->prepare($sql);
        foreach ($params as $key => $val) {
            $stmt->bindValue($key, $val, PDO::PARAM_STR);
        }
        $stmt->execute();
        $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Buscar vencimientos si hay productos (para vista consolidada)
        $productIds = array_column($products, 'id');
        $expirations = [];
        if (!empty($productIds)) {
            $inClause = implode(',', array_fill(0, count($productIds), '?'));
            $sqlExp = "
                SELECT 
                    cd.producto_id, 
                    cd.fecha_vencimiento,
                    SUM(cd.cantidad_unidades_base) as cantidad,
                    suc.nombre as sucursal_nombre
                FROM compras_detalle cd
                JOIN compras c ON cd.compra_id = c.id
                JOIN inventario_movimientos m ON m.referencia_id = c.id AND m.producto_id = cd.producto_id
                JOIN sucursales suc ON m.sucursal_id = suc.id
                WHERE cd.producto_id IN ($inClause)
                  AND cd.fecha_vencimiento IS NOT NULL 
                  AND cd.fecha_vencimiento != '0000-00-00'
                  AND cd.facturado = ?
                GROUP BY cd.producto_id, cd.fecha_vencimiento, suc.nombre
                ORDER BY cd.fecha_vencimiento ASC
            ";
            $stmtExp = $pdo->prepare($sqlExp);
            $paramsExp = array_merge($productIds, [$facturado]);
            $stmtExp->execute($paramsExp);
            while ($rowExp = $stmtExp->fetch(PDO::FETCH_ASSOC)) {
                $shortBranch = explode(' ', $rowExp['sucursal_nombre'])[0];
                $expirations[$rowExp['producto_id']][] = $shortBranch . ": " . date('d/m/Y', strtotime($rowExp['fecha_vencimiento'])) . " (" . $rowExp['cantidad'] . " u)";
            }
        }

        // Mapear campos para Excel
        foreach ($products as &$prod) {
            $prodId = (int)$prod['id'];
            if ($facturado === 1) {
                $prod['stock_1'] = (int)$prod['stock_f_1'];
                $prod['stock_2'] = (int)$prod['stock_f_2'];
                $prod['stock_3'] = (int)$prod['stock_f_3'];
                $prod['stock_4'] = (int)$prod['stock_f_4'];
                $prod['stock_5'] = (int)$prod['stock_f_5'];
            } else {
                $prod['stock_1'] = (int)$prod['stock_nf_1'];
                $prod['stock_2'] = (int)$prod['stock_nf_2'];
                $prod['stock_3'] = (int)$prod['stock_nf_3'];
                $prod['stock_4'] = (int)$prod['stock_nf_4'];
                $prod['stock_5'] = (int)$prod['stock_nf_5'];
            }
            $prod['stock_total'] = $prod['stock_1'] + $prod['stock_2'] + $prod['stock_3'] + $prod['stock_4'] + $prod['stock_5'];
            $prod['costo'] = (float)$prod['costo_promedio'];
            $prod['valorizado'] = $prod['stock_total'] * $prod['costo'];

            $dates = array_filter([
                $prod['last_updated_suc_1'], $prod['last_updated_suc_2'], $prod['last_updated_suc_3'], $prod['last_updated_suc_4'], $prod['last_updated_suc_5']
            ], function($d) {
                return !empty($d) && $d !== '0000-00-00 00:00:00';
            });
            $prod['fecha_registro'] = !empty($dates) ? max($dates) : null;
            $prod['vencimientos_label'] = isset($expirations[$prodId]) ? implode(', ', $expirations[$prodId]) : 'Sin vencimientos';
        }

    } else {
        // Vista por Sucursal Específica
        $sql = "
            SELECT 
                p.id,
                COALESCE(p.CodigoInterno, '') as codigo,
                COALESCE(p.CodigoBarra, '') as codigo_barra,
                COALESCE(p.NombreComercial, 'Sin Nombre') as nombre,
                COALESCE(p.NombreGenerico, '') as nombre_generico,
                COALESCE(p.laboratorio, 'Genérico') as laboratorio,
                COALESCE(s.costo_promedio_local, pcp.costo_promedio_unidad_base, 0) as costo_promedio,
                COALESCE(s.stock_facturado, 0) as stock_facturado,
                COALESCE(s.stock_no_facturado, 0) as stock_no_facturado,
                s.last_updated as fecha_registro
            FROM productos p
            LEFT JOIN productos_costo_promedio pcp ON p.id = pcp.producto_id
            LEFT JOIN inventario_sucursal s ON p.id = s.producto_id AND s.sucursal_id = :sucursal_id
            WHERE {$whereClause}
        ";

        if ($facturado === 1) {
            if ($solo_existencias === 1) {
                $sql .= " AND COALESCE(s.stock_facturado, 0) > 0";
            }
        } else {
            if ($solo_existencias === 1) {
                $sql .= " AND COALESCE(s.stock_no_facturado, 0) > 0";
            }
        }

        $sql .= " ORDER BY p.NombreComercial ASC";

        $stmt = $pdo->prepare($sql);
        $stmt->bindValue(':sucursal_id', $sucursal_id, PDO::PARAM_INT);
        foreach ($params as $key => $val) {
            $stmt->bindValue($key, $val, PDO::PARAM_STR);
        }
        $stmt->execute();
        $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Buscar vencimientos si hay productos
        $productIds = array_column($products, 'id');
        $expirations = [];
        if (!empty($productIds)) {
            $inClause = implode(',', array_fill(0, count($productIds), '?'));
            $sqlExp = "
                SELECT 
                    cd.producto_id, 
                    cd.fecha_vencimiento,
                    SUM(cd.cantidad_unidades_base) as cantidad
                FROM compras_detalle cd
                JOIN compras c ON cd.compra_id = c.id
                JOIN inventario_movimientos m ON m.referencia_id = c.id AND m.producto_id = cd.producto_id
                WHERE m.sucursal_id = ? 
                  AND cd.producto_id IN ($inClause)
                  AND cd.fecha_vencimiento IS NOT NULL 
                  AND cd.fecha_vencimiento != '0000-00-00'
                  AND cd.facturado = ?
                GROUP BY cd.producto_id, cd.fecha_vencimiento
                ORDER BY cd.fecha_vencimiento ASC
            ";
            $stmtExp = $pdo->prepare($sqlExp);
            $paramsExp = array_merge([$sucursal_id], $productIds, [$facturado]);
            $stmtExp->execute($paramsExp);
            while ($rowExp = $stmtExp->fetch(PDO::FETCH_ASSOC)) {
                $expirations[$rowExp['producto_id']][] = date('d/m/Y', strtotime($rowExp['fecha_vencimiento'])) . " (" . $rowExp['cantidad'] . " u)";
            }
        }

        foreach ($products as &$prod) {
            $prodId = (int)$prod['id'];
            $prod['stock_total'] = $facturado === 1 ? (int)$prod['stock_facturado'] : (int)$prod['stock_no_facturado'];
            $prod['costo'] = (float)$prod['costo_promedio'];
            $prod['valorizado'] = $prod['stock_total'] * $prod['costo'];
            $prod['vencimientos_label'] = isset($expirations[$prodId]) ? implode(', ', $expirations[$prodId]) : 'Sin vencimientos';
        }
    }

    // 4. Configurar headers de descarga para Excel (.xls compatible)
    $tipo_reporte = ($facturado === 1) ? "Facturado" : "Sin_Factura";
    $filename = "Inventario_" . $tipo_reporte . "_" . str_replace(' ', '_', $sucursal_nombre) . "_" . date('Ymd_His') . ".xls";

    header("Content-Type: application/vnd.ms-excel; charset=utf-8");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Pragma: no-cache");
    header("Expires: 0");

    // Escribir contenido HTML compatible con Excel
    echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
    echo '<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style>';
    echo 'table { border-collapse: collapse; }';
    echo 'th { background-color: #0f172a; color: #ffffff; font-weight: bold; border: 1px solid #cbd5e1; padding: 6px; }';
    echo 'td { border: 1px solid #cbd5e1; padding: 6px; }';
    echo '.header-title { background-color: #4f46e5; color: #ffffff; font-size: 16px; font-weight: bold; text-align: center; }';
    echo '</style></head><body>';
    echo '<table>';

    if ($sucursal_id === 0) {
        echo '<tr><th colspan="14" class="header-title">INVENTARIO REAL-TIME (' . strtoupper($tipo_reporte) . ') - CONSOLIDADO</th></tr>';
        echo '<tr><th colspan="14" style="background-color: #1e293b; color: #ffffff; font-weight: bold;">Sucursales: Bodega, Sanfe, Terminal, Sector 8, Centro | Exportado: ' . date('d/m/Y H:i') . '</th></tr>';
        echo '<tr>
                <th>Código</th>
                <th>Nombre del Producto</th>
                <th>Genérico</th>
                <th>Laboratorio</th>
                <th>Bodega (S1)</th>
                <th>Sanfe (S2)</th>
                <th>Terminal (S3)</th>
                <th>Sector 8 (S4)</th>
                <th>Centro (S5)</th>
                <th>Stock Total</th>
                <th>Costo Promedio (Q)</th>
                <th>Valorizado (Q)</th>
                <th>Última Act.</th>
                <th>Próximos Vencimientos</th>
              </tr>';

        foreach ($products as $row) {
            $actStr = $row['fecha_registro'] ? date('d/m/Y H:i', strtotime($row['fecha_registro'])) : '-';
            echo "<tr>
                    <td style=\"mso-number-format:'\@';\">" . htmlspecialchars($row['codigo']) . "</td>
                    <td>" . htmlspecialchars($row['nombre']) . "</td>
                    <td>" . htmlspecialchars($row['nombre_generico']) . "</td>
                    <td>" . htmlspecialchars($row['laboratorio']) . "</td>
                    <td align=\"right\">" . $row['stock_1'] . "</td>
                    <td align=\"right\">" . $row['stock_2'] . "</td>
                    <td align=\"right\">" . $row['stock_3'] . "</td>
                    <td align=\"right\">" . $row['stock_4'] . "</td>
                    <td align=\"right\">" . $row['stock_5'] . "</td>
                    <td align=\"right\" style=\"font-weight: bold;\">" . $row['stock_total'] . "</td>
                    <td align=\"right\">" . number_format($row['costo'], 4, '.', '') . "</td>
                    <td align=\"right\" style=\"font-weight: bold;\">" . number_format($row['valorizado'], 2, '.', '') . "</td>
                    <td align=\"center\">" . $actStr . "</td>
                    <td>" . htmlspecialchars($row['vencimientos_label']) . "</td>
                  </tr>";
        }
    } else {
        echo '<tr><th colspan="9" class="header-title">INVENTARIO REAL-TIME (' . strtoupper($tipo_reporte) . ') - ' . strtoupper($sucursal_nombre) . '</th></tr>';
        echo '<tr><th colspan="9" style="background-color: #1e293b; color: #ffffff; font-weight: bold;">Exportado: ' . date('d/m/Y H:i') . '</th></tr>';
        echo '<tr>
                <th>Código</th>
                <th>Nombre del Producto</th>
                <th>Genérico</th>
                <th>Laboratorio</th>
                <th>Stock Local</th>
                <th>Costo Local (Q)</th>
                <th>Valorizado Local (Q)</th>
                <th>Última Act.</th>
                <th>Próximos Vencimientos</th>
              </tr>';

        foreach ($products as $row) {
            $actStr = $row['fecha_registro'] ? date('d/m/Y H:i', strtotime($row['fecha_registro'])) : '-';
            echo "<tr>
                    <td style=\"mso-number-format:'\@';\">" . htmlspecialchars($row['codigo']) . "</td>
                    <td>" . htmlspecialchars($row['nombre']) . "</td>
                    <td>" . htmlspecialchars($row['nombre_generico']) . "</td>
                    <td>" . htmlspecialchars($row['laboratorio']) . "</td>
                    <td align=\"right\" style=\"font-weight: bold;\">" . $row['stock_total'] . "</td>
                    <td align=\"right\">" . number_format($row['costo'], 4, '.', '') . "</td>
                    <td align=\"right\" style=\"font-weight: bold;\">" . number_format($row['valorizado'], 2, '.', '') . "</td>
                    <td align=\"center\">" . $actStr . "</td>
                    <td>" . htmlspecialchars($row['vencimientos_label']) . "</td>
                  </tr>";
        }
    }

    echo '</table></body></html>';

} catch (Exception $e) {
    die("Error al generar el reporte: " . $e->getMessage());
}
