<?php
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=reporte_inventario_' . date('Ymd_His') . '.csv');
require_once '../db_connection.php';

// Get filter parameters
$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']) : '';

$whereClauses = [];
$params = [];
$types = '';

// Search by product code, commercial name, generic name
if (!empty($searchTerm)) {
    $whereClauses[] = "(p.CodigoInterno LIKE ? OR p.NombreComercial LIKE ? OR p.NombreGenerico LIKE ?)";
    $params[] = "%{$searchTerm}%";
    $params[] = "%{$searchTerm}%";
    $params[] = "%{$searchTerm}%";
    $types .= 'sss';
}

// Filter by laboratory
if (!empty($laboratoryFilter)) {
    $whereClauses[] = "p.laboratorio LIKE ?";
    $params[] = "%{$laboratoryFilter}%";
    $types .= 's';
}

// Filter by branch (this will filter products that have inventory in the specified branch)
if ($branchFilter > 0) {
    $whereClauses[] = "i.sucursal_id = ?";
    $params[] = $branchFilter;
    $types .= 'i';
}

// Filter by presentation (this will filter products that have inventory with the specified presentation)
if (!empty($presentationFilter)) {
    $whereClauses[] = "i.presentacion LIKE ?";
    $params[] = "%{$presentationFilter}%";
    $types .= 's';
}

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

// Main query to get inventory report data in "Conteo" structure
$query = "SELECT 
            p.id, p.CodigoInterno, p.NombreComercial, p.laboratorio,
            GROUP_CONCAT(CONCAT(i.sucursal_id, ':', i.stock_unidad_base, ':', i.presentacion, ':', i.last_updated) SEPARATOR '|') as inventory_data
          FROM productos p
          JOIN inventario i ON p.id = i.lote_id
          $whereSql
          GROUP BY p.id
          ORDER BY p.NombreComercial ASC"; // No LIMIT/OFFSET for export

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

if (!empty($params)) {
    $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();

$output = fopen('php://output', 'w');

// Output CSV headers
$branchOrder = [3, 5, 2, 4]; // Defined order of branches
$branchNames = []; // To store branch ID to Name mapping
// Get branch names for headers
$stmt_branches = $conn->prepare("SELECT id, nombre FROM sucursales ORDER BY FIELD(id, 3, 5, 2, 4)");
$stmt_branches->execute();
$result_branches = $stmt_branches->get_result();
while ($row = $result_branches->fetch_assoc()) {
    $branchNames[$row['id']] = $row['nombre'];
}
$stmt_branches->close();

$csvHeaders = ['Código Producto', 'Nombre Comercial', 'Laboratorio', 'Presentación'];
foreach ($branchOrder as $branchId) {
    $csvHeaders[] = 'Cantidad en ' . ($branchNames[$branchId] ?? "Sucursal $branchId");
}
$csvHeaders[] = 'Última Actualización';
fputcsv($output, $csvHeaders);

// Output data rows
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $product = [
            'id' => $row['id'],
            'CodigoInterno' => $row['CodigoInterno'],
            'NombreComercial' => $row['NombreComercial'],
            'laboratorio' => $row['laboratorio'],
            'inventory' => []
        ];

        // Parse inventory data
        if (!empty($row['inventory_data'])) {
            $inventory_items = explode('|', $row['inventory_data']);
            foreach ($inventory_items as $item) {
                list($branch_id, $quantity, $presentation, $last_updated) = explode(':', $item);
                $product['inventory'][(int)$branch_id] = [
                    'quantity' => (int)$quantity,
                    'presentation' => $presentation,
                    'last_updated' => $last_updated
                ];
            }
        }

        $csvRow = [
            $product['CodigoInterno'],
            $product['NombreComercial'],
            $product['laboratorio'],
            '' // Placeholder for product presentation
        ];

        // Find product presentation (from any branch)
        $productPresentation = '';
        foreach ($branchOrder as $branchId) {
            if ($product['inventory'][$branchId] && $product['inventory'][$branchId]['presentation']) {
                $productPresentation = $product['inventory'][$branchId]['presentation'];
                break;
            }
        }
        $csvRow[3] = $productPresentation; // Set product presentation

        // Add quantities for each branch
        foreach ($branchOrder as $branchId) {
            $quantity = $product['inventory'][$branchId]['quantity'] ?? 0;
            $csvRow[] = $quantity;
        }

        // Find latest update timestamp
        $latestUpdate = '';
        foreach ($branchOrder as $branchId) {
            if ($product['inventory'][$branchId] && $product['inventory'][$branchId]['last_updated']) {
                $currentLastUpdated = $product['inventory'][$branchId]['last_updated'];
                if ($latestUpdate === '' || $currentLastUpdated > $latestUpdate) {
                    $latestUpdate = $currentLastUpdated;
                }
            }
        }
        $csvRow[] = $latestUpdate;

        fputcsv($output, $csvRow);
    }
}

fclose($output);
$stmt->close();
$conn->close();
?>