<?php
require '../vendor/autoload.php';
require_once '../db_connection.php'; 

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

// --- 1. Obtener Filtros de la URL ---
$searchTerm = isset($_GET['term']) ? trim($_GET['term']) : '';
$laboratoryFilter = isset($_GET['laboratory']) ? trim($_GET['laboratory']) : '';
$branchFilter = isset($_GET['branch']) ? (int)$_GET['branch'] : 0;
$presentationFilter = isset($_GET['presentation']) ? trim($_GET['presentation']) : '';

// --- 2. Construir la Consulta a la Base de Datos ---
$whereClauses = [];
$params = [];
$types = '';

if (!empty($searchTerm)) {
    $likeTerm = "%{$searchTerm}%";
    $whereClauses[] = "(p.CodigoInterno LIKE ? OR p.NombreComercial LIKE ? OR p.NombreGenerico LIKE ?)";
    array_push($params, $likeTerm, $likeTerm, $likeTerm);
    $types .= 'sss';
}
if (!empty($laboratoryFilter)) {
    $whereClauses[] = "p.laboratorio LIKE ?";
    $params[] = "%{$laboratoryFilter}%";
    $types .= 's';
}
if ($branchFilter > 0) {
    $whereClauses[] = "inv.sucursal_id = ?";
    $params[] = $branchFilter;
    $types .= 'i';
}
if (!empty($presentationFilter)) {
    $whereClauses[] = "inv.presentacion LIKE ?";
    $params[] = "%{$presentationFilter}%";
    $types .= 's';
}

$whereSql = "";
if (!empty($whereClauses)) {
    $whereSql = "WHERE " . implode(' AND ', $whereClauses);
}

// Consulta principal que agrupa el inventario por producto
$query = "
    SELECT 
        p.CodigoInterno,
        p.NombreComercial,
        p.laboratorio,
        p.CostoUnidad, -- <<<<<< 1. CAMBIO: Añadida la columna de costo
        GROUP_CONCAT(DISTINCT inv.presentacion SEPARATOR ', ') as Presentaciones,
        SUM(CASE WHEN inv.sucursal_id = 3 THEN inv.stock_unidad_base ELSE 0 END) as 'SANFE',
        SUM(CASE WHEN inv.sucursal_id = 5 THEN inv.stock_unidad_base ELSE 0 END) as 'CHIQUI',
        SUM(CASE WHEN inv.sucursal_id = 2 THEN inv.stock_unidad_base ELSE 0 END) as 'CENTRO',
        SUM(CASE WHEN inv.sucursal_id = 4 THEN inv.stock_unidad_base ELSE 0 END) as 'ZONA1'
    FROM productos p
    LEFT JOIN inventario inv ON p.id = inv.lote_id
    {$whereSql}
    GROUP BY p.id, p.CodigoInterno, p.NombreComercial, p.laboratorio, p.CostoUnidad -- <<<<<< 2. CAMBIO: Añadido al GROUP BY
    HAVING SUM(inv.stock_unidad_base) > 0
    ORDER BY p.NombreComercial ASC
";

$stmt = $conn->prepare($query);

if (!empty($types)) {
    $bind_names = [];
    $bind_names[] = $types;
    for ($i = 0; $i < count($params); $i++) {
        $bind_names[] = &$params[$i];
    }
    call_user_func_array([$stmt, 'bind_param'], $bind_names);
}

$stmt->execute();
$result = $stmt->get_result();

// --- 3. Crear el Archivo Excel ---
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Reporte de Inventario');

// Encabezados de la tabla
$headers = [
    'Código Producto', 
    'Nombre Comercial', 
    'Laboratorio', 
    'Costo Unidad', // <<<<<< 3. CAMBIO: Nuevo encabezado
    'Presentaciones',
    'Stock SANFE',
    'Stock CHIQUI',
    'Stock CENTRO',
    'Stock ZONA1'
];
$sheet->fromArray($headers, NULL, 'A1');

// Estilo para los encabezados
$headerStyle = [
    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4F81BD']],
    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
];
$sheet->getStyle('A1:I1')->applyFromArray($headerStyle); // Rango ajustado a 'I'

// Llenar los datos del inventario
$rowNum = 2;
while ($row = $result->fetch_assoc()) {
    $sheet->setCellValue('A' . $rowNum, $row['CodigoInterno']);
    $sheet->setCellValue('B' . $rowNum, $row['NombreComercial']);
    $sheet->setCellValue('C' . $rowNum, $row['laboratorio']);
    $sheet->setCellValue('D' . $rowNum, $row['CostoUnidad']); // <<<<<< 4. CAMBIO: Añadida la celda de costo
    $sheet->setCellValue('E' . $rowNum, $row['Presentaciones']);
    $sheet->setCellValue('F' . $rowNum, $row['SANFE']);
    $sheet->setCellValue('G' . $rowNum, $row['CHIQUI']);
    $sheet->setCellValue('H' . $rowNum, $row['CENTRO']);
    $sheet->setCellValue('I' . $rowNum, $row['ZONA1']);
    $rowNum++;
}

// Formato de moneda para la columna de costo
$sheet->getStyle('D2:D'.($rowNum-1))->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);


// Ajustar el ancho de las columnas automáticamente
foreach (range('A', 'I') as $columnID) { // Rango ajustado a 'I'
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

// --- 4. Enviar el Archivo al Navegador ---
$writer = new Xlsx($spreadsheet);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Reporte_Inventario_'.date('Y-m-d').'.xlsx"');
header('Cache-Control: max-age=0');

$writer->save('php://output');

$stmt->close();
$conn->close();
exit();