<?php

include "../config/conexion.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

if (isset($_POST['fecha_inicio'], $_POST['fecha_fin'])) {
    $inicio = $_POST['fecha_inicio'];
    $fin    = $_POST['fecha_fin'];

    $sql = "
        SELECT
            rf.idregistro, rf.carnet, rf.nombre, rf.apellidos,
            rf.carrera, rf.grado, rf.seccion, rf.falta,
            rf.bimestre, rf.maestro, rf.fecha, rf.observaciones,
            u.nombre AS usuario
        FROM registro_falta rf
        INNER JOIN usuarios u ON rf.idusuario = u.id
        WHERE rf.fecha BETWEEN ? AND ?
    ";

    if ($stmt = $conexion->prepare($sql)) {
        $stmt->bind_param("ss", $inicio, $fin);
        $stmt->execute();
        $stmt->store_result();

        $meta = $stmt->result_metadata();
        $fields = [];
        while ($field = $meta->fetch_field()) {
            $fields[] = $field->name;
        }

        // Preparar variables para bind_result dinámico
        $row = [];
        $row_refs = [];
        foreach ($fields as $field) {
            $row[$field] = null;
            $row_refs[] = &$row[$field];
        }

        call_user_func_array([$stmt, 'bind_result'], $row_refs);

        // Crear hoja de cálculo
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Escribir encabezados (fila 1)
        foreach ($fields as $i => $field) {
            $colLetter = Coordinate::stringFromColumnIndex($i + 1);
            $sheet->setCellValue($colLetter . '1', $field);
        }

        // Escribir datos (desde fila 2)
        $rowIndex = 2;
        while ($stmt->fetch()) {
            foreach ($fields as $i => $field) {
                $colLetter = Coordinate::stringFromColumnIndex($i + 1);
                $sheet->setCellValue($colLetter . $rowIndex, $row[$field]);
            }
            $rowIndex++;
        }

        // Enviar headers para descargar CSV
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="faltas_' . date('Ymd_His') . '.csv"');
        header('Cache-Control: max-age=0');

        $writer = new Csv($spreadsheet);
        $writer->setDelimiter(';'); // punto y coma para Excel en español
        $writer->setEnclosure('"');
        $writer->setLineEnding("\r\n");
        $writer->save('php://output');
        exit();
    } else {
        die("Error en la consulta: " . $conexion->error);
    }
} else {
    echo "Fechas no definidas correctamente.";
}