<?php
/**
 * exportar_quorum.php
 * Descarga la lista de delegados como archivo .xlsx
 * Columnas: Nombre, Cédula, Poder
 * Nombre del archivo: quorum_{zona}_{fecha_hora}.xlsx
 */

require_once __DIR__ . '/../env_loader.php';
require_once __DIR__ . '/../app/models/conexion.php';

use app\models\conexion;

// ── Conexión y datos ─────────────────────────────────────────────────
$conn = new conexion();
$db   = $conn->DB();
$db->exec("SET NAMES utf8");

$stmt = $db->query(
    "SELECT name AS Nombre, username AS Cedula, fecha_ingreso AS FechaIngreso, zona
     FROM users
     WHERE activo = 1 AND quorum = 1
     ORDER BY name ASC"
);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Zona para el nombre del archivo (primera zona encontrada, siempre FONDOENERGIA)
$zona      = !empty($rows) ? $rows[0]['zona'] : 'SIN_ZONA';
$zonaLimpia = preg_replace('/[^A-Za-z0-9_\-]/', '_', $zona);
$fechaHora  = date('Y-m-d_H-i-s');
$filename   = "quorum_{$zonaLimpia}_{$fechaHora}.xlsx";

// ── Generador XLSX puro (sin dependencias externas) ──────────────────
// Construye el archivo .xlsx usando ZipArchive + XML (OOXML)

function xlsxEscape($v) {
    return htmlspecialchars((string)$v, ENT_XML1, 'UTF-8');
}

function buildXlsx(array $headers, array $data): string
{
    // ── Shared strings ───────────────────────────────────────────────
    $strings = [];
    $addStr  = function($v) use (&$strings) {
        $v = (string)$v;
        $k = array_search($v, $strings, true);
        if ($k === false) { $strings[] = $v; $k = count($strings) - 1; }
        return $k;
    };

    // Encabezados
    $hIdx = [];
    foreach ($headers as $h) $hIdx[] = $addStr($h);

    // Filas: strings van a shared strings; números quedan inline
    $cellRows = [];
    foreach ($data as $row) {
        $cells = [];
        $values = array_values($row);
        foreach ($values as $val) {
            if (is_numeric($val) && $val !== '') {
                $cells[] = ['n', $val];
            } else {
                $cells[] = ['s', $addStr($val)];
            }
        }
        $cellRows[] = $cells;
    }

    // ── sheet1.xml ───────────────────────────────────────────────────
    $cols  = count($headers);
    $alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    $sheetXml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
    $sheetXml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
    $sheetXml .= '<sheetData>';

    // Fila encabezado (row 1) — estilo 1 = bold
    $sheetXml .= '<row r="1">';
    foreach ($hIdx as $ci => $si) {
        $col = $alpha[$ci];
        $sheetXml .= "<c r=\"{$col}1\" t=\"s\" s=\"1\"><v>{$si}</v></c>";
    }
    $sheetXml .= '</row>';

    // Filas de datos
    foreach ($cellRows as $ri => $cells) {
        $rowNum = $ri + 2;
        $sheetXml .= "<row r=\"{$rowNum}\">";
        foreach ($cells as $ci => $cell) {
            $col = $alpha[$ci];
            [$type, $val] = $cell;
            if ($type === 's') {
                $sheetXml .= "<c r=\"{$col}{$rowNum}\" t=\"s\"><v>" . xlsxEscape($val) . "</v></c>";
            } else {
                $sheetXml .= "<c r=\"{$col}{$rowNum}\"><v>" . xlsxEscape($val) . "</v></c>";
            }
        }
        $sheetXml .= '</row>';
    }
    $sheetXml .= '</sheetData></worksheet>';

    // ── sharedStrings.xml ────────────────────────────────────────────
    $count   = count($strings);
    $ssXml   = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
    $ssXml  .= "<sst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\""
             . " count=\"{$count}\" uniqueCount=\"{$count}\">";
    foreach ($strings as $s) {
        $ssXml .= '<si><t xml:space="preserve">' . xlsxEscape($s) . '</t></si>';
    }
    $ssXml .= '</sst>';

    // ── styles.xml — estilo 1 = bold para encabezados ────────────────
    $stylesXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        . '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        . '<fonts count="2">'
        .   '<font><sz val="11"/><name val="Arial"/></font>'
        .   '<font><b/><sz val="11"/><name val="Arial"/></font>'
        . '</fonts>'
        . '<fills count="2">'
        .   '<fill><patternFill patternType="none"/></fill>'
        .   '<fill><patternFill patternType="gray125"/></fill>'
        . '</fills>'
        . '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>'
        . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
        . '<cellXfs count="2">'
        .   '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
        .   '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0"/>'
        . '</cellXfs>'
        . '</styleSheet>';

    // ── workbook.xml ─────────────────────────────────────────────────
    $wbXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        . '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'
        . ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
        . '<sheets><sheet name="Quorum" sheetId="1" r:id="rId1"/></sheets>'
        . '</workbook>';

    // ── [Content_Types].xml ──────────────────────────────────────────
    $ctXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        . '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
        . '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
        . '<Default Extension="xml"  ContentType="application/xml"/>'
        . '<Override PartName="/xl/workbook.xml"'
        .   ' ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
        . '<Override PartName="/xl/worksheets/sheet1.xml"'
        .   ' ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
        . '<Override PartName="/xl/sharedStrings.xml"'
        .   ' ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>'
        . '<Override PartName="/xl/styles.xml"'
        .   ' ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
        . '</Types>';

    // ── _rels/.rels ──────────────────────────────────────────────────
    $relsXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
        . '</Relationships>';

    // ── xl/_rels/workbook.xml.rels ───────────────────────────────────
    $wbRelsXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"           Target="worksheets/sheet1.xml"/>'
        . '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"       Target="sharedStrings.xml"/>'
        . '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"              Target="styles.xml"/>'
        . '</Relationships>';

    // ── Empaquetar con ZipArchive en memoria ─────────────────────────
    $tmpFile = tempnam(sys_get_temp_dir(), 'bv_quorum_') . '.xlsx';

    $zip = new ZipArchive();
    $zip->open($tmpFile, ZipArchive::CREATE | ZipArchive::OVERWRITE);

    $zip->addFromString('[Content_Types].xml',            $ctXml);
    $zip->addFromString('_rels/.rels',                    $relsXml);
    $zip->addFromString('xl/workbook.xml',                $wbXml);
    $zip->addFromString('xl/_rels/workbook.xml.rels',     $wbRelsXml);
    $zip->addFromString('xl/worksheets/sheet1.xml',       $sheetXml);
    $zip->addFromString('xl/sharedStrings.xml',           $ssXml);
    $zip->addFromString('xl/styles.xml',                  $stylesXml);

    $zip->close();

    $content = file_get_contents($tmpFile);
    unlink($tmpFile);
    return $content;
}

// ── Preparar datos para el Excel ─────────────────────────────────────
$headers = ['#', 'Nombre', 'Cédula', 'Fecha Ingreso'];
$data    = array_map(fn($r, $i) => [
    'Num'    => $i + 1,
    'Nombre' => $r['Nombre'],
    'Cedula' => $r['Cedula'],
    'Fecha'  => $r['FechaIngreso'] ?? '',
], array_values($rows), array_keys($rows));

$xlsxContent = buildXlsx($headers, $data);

// ── Enviar al navegador ───────────────────────────────────────────────
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . strlen($xlsxContent));
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');

echo $xlsxContent;
exit;
