<?php
    session_start(); // ① NECESARIO para usar $_SESSION

    require __DIR__ . '/../../../vendor/autoload.php';
    require_once __DIR__ . '/../../database/Conexion_SQL_Server.php';

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Style\Border;
    use PhpOffice\PhpSpreadsheet\Style\Alignment;
    use PhpOffice\PhpSpreadsheet\Style\Fill;
    use PhpOffice\PhpSpreadsheet\Cell\DataType;
    use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
    use PhpOffice\PhpSpreadsheet\Style\Conditional;

    // Helper: guardar mensaje y volver a la lista (URL limpia)
    function flash_and_back(string $type, string $text): void {
        $_SESSION['flash_export'] = ['type' => $type, 'text' => $text]; // success|warning|danger|info
        $back = $_SERVER['HTTP_REFERER'] ?? '/Proyecto_Guardiana/frontend/app_Recepcion_Listado.php';
        header('Location: '.$back);
        exit;
    }

    $sql = "
        SELECT 
            ID_ORDER, FECHA_REGISTRO, NOMBRE_COMPLETO, TELEFONO_CLIENTE,
            CANTIDAD_ADULTOS, [CANTIDAD_NIÑOS] AS CANTIDAD_NINOS,
            ESTADO_REGISTRO, FECHA_RECEPCION, OBSERVACION
        FROM REGISTRO_LISTA
        ORDER BY FECHA_REGISTRO DESC
    ";
    $stmt = sqlsrv_query($conn, $sql);
    if ($stmt === false) {
        flash_and_back('danger', 'Error SQL al consultar la base de datos.');
    }

    // ======== Tu lógica de Excel (igual a la que ya tienes) ========
    $ss = new Spreadsheet();
    $ss->getDefaultStyle()->getFont()->setName('Calibri')->setSize(11);
    /** @var Worksheet $sheet */
    $sheet = $ss->getActiveSheet();
    $sheet->setTitle('Lista de Espera LG');

    // Título
    $sheet->mergeCells('A1:I1');
    $sheet->setCellValue('A1', 'Lista de Espera – Exportación');
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $sheet->getRowDimension(1)->setRowHeight(28);

    // Encabezados
    $headers = ['ID','FECHA REGISTRO.','NOMBRE COMPLETO','TELEFONO','CANTIDAD ADULTOS','CANTIDAD NIÑOS','ESTADO','FECHA RECEPCION','OBSERVACION CLIENTE'];
    $sheet->fromArray($headers, null, 'A3');
    $sheet->getStyle('A3:I3')->applyFromArray([
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '2563EB']],
    ]);
    $sheet->getRowDimension(3)->setRowHeight(22);

    // Datos
    $row = 4;
    $rowsCount = 0;
    while ($r = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $rowsCount++;
        $id       = (int)($r['ID_ORDER'] ?? 0);
        $fr       = $r['FECHA_REGISTRO'] ?? null;
        $frc      = $r['FECHA_RECEPCION'] ?? null;
        $fechaReg = ($fr  instanceof DateTime) ? $fr->format('Y-m-d H:i')  : (string)$fr;
        $fechaRec = ($frc instanceof DateTime) ? $frc->format('Y-m-d H:i') : (string)$frc;
        $telefono = isset($r['TELEFONO_CLIENTE']) ? (string)$r['TELEFONO_CLIENTE'] : '';
        $estado   = (string)($r['ESTADO_REGISTRO'] ?? '');

        $sheet->setCellValue("A{$row}", $id);
        $sheet->setCellValue("B{$row}", $fechaReg);
        $sheet->setCellValue("C{$row}", (string)$r['NOMBRE_COMPLETO']);
        $sheet->setCellValueExplicit("D{$row}", $telefono, DataType::TYPE_STRING);
        $sheet->setCellValue("E{$row}", (int)$r['CANTIDAD_ADULTOS']);
        $sheet->setCellValue("F{$row}", (int)$r['CANTIDAD_NINOS']);
        $sheet->setCellValue("G{$row}", $estado);
        $sheet->setCellValue("H{$row}", $fechaRec);
        $sheet->setCellValue("I{$row}", (string)($r['OBSERVACION'] ?? ''));
        $row++;
    }
    $lastRow = $row - 1;

    // Si no hay filas
    if ($rowsCount === 0) {
        sqlsrv_free_stmt($stmt);
        flash_and_back('warning', 'No hay registros para exportar.');
    }

    // Formatos/bordes/tamaño/etc.
    $sheet->getStyle("B4:B{$lastRow}")->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm');
    $sheet->getStyle("H4:H{$lastRow}")->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm');
    $sheet->getStyle("E4:F{$lastRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("A4:A{$lastRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("I4:I{$lastRow}")->getAlignment()->setWrapText(true);
    $sheet->getStyle("A3:I{$lastRow}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    foreach (range('A','I') as $col) { $sheet->getColumnDimension($col)->setAutoSize(true); }
    $sheet->getColumnDimension('I')->setWidth(45);
    for ($r = 4; $r <= $lastRow; $r++) {
        if ($r % 2 === 0) {
            $sheet->getStyle("A{$r}:I{$r}")
                ->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F8FAFC');
        }
    }
    $sheet->setAutoFilter("A3:I3");
    $sheet->freezePane('A4');

    // Condicional por estado
    $condLLamado = new Conditional();
    $condLLamado->setConditionType(Conditional::CONDITION_CONTAINSTEXT)
                ->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT)
                ->setText('LLAMADO')
                ->getStyle()->getFont()->getColor()->setRGB('166534');
    $condLLamado->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('DCFCE7');
    $condCancel = new Conditional();
    $condCancel->setConditionType(Conditional::CONDITION_CONTAINSTEXT)
            ->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT)
            ->setText('CANCELADO')
            ->getStyle()->getFont()->getColor()->setRGB('991B1B');
    $condCancel->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FEE2E2');
    $condEspera = new Conditional();
    $condEspera->setConditionType(Conditional::CONDITION_CONTAINSTEXT)
            ->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT)
            ->setText('EN ESPERA')
            ->getStyle()->getFont()->getColor()->setRGB('92400E');
    $condEspera->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FEF3C7');
    $conditionalStyles = $sheet->getStyle("G4:G{$lastRow}")->getConditionalStyles();
    $conditionalStyles[] = $condLLamado;
    $conditionalStyles[] = $condCancel;
    $conditionalStyles[] = $condEspera;
    $sheet->getStyle("G4:G{$lastRow}")->setConditionalStyles($conditionalStyles);

    // Guardar archivo
    $fullPath = "E:/Archivo_Recepcion.xlsx";
    try {
        $writer = new Xlsx($ss);
        $writer->save($fullPath);
    } catch (\Throwable $e) {
        sqlsrv_free_stmt($stmt);
        flash_and_back('danger', 'No se pudo guardar el archivo o esta abierto el archivo.');
    }

    sqlsrv_free_stmt($stmt);
    // Éxito
    flash_and_back('success', 'Exportación Correcta');
?>