<?php
/**
 * XLSXWriter - Generador minimo de archivos XLSX con soporte de fechas reales
 * Crea archivos XLSX validos usando ZipArchive y XML
 * Las celdas de fecha se guardan como seriales de Excel con formato fecha
 */

namespace dokuwiki\plugin\beztaxcalendar\lib;

class XLSXWriter {

    protected $sheets = [];
    protected $currentSheet = null;

    /**
     * Agregar una hoja
     */
    public function addSheet($name) {
        $this->sheets[$name] = ['rows' => []];
        $this->currentSheet = $name;
    }

    /**
     * Agregar fila de datos
     * Cada celda puede ser: string, numero, o ['date' => 'Y-m-d'] para fecha
     */
    public function addRow($cells) {
        if (!$this->currentSheet) $this->addSheet('Sheet1');
        $this->sheets[$this->currentSheet]['rows'][] = $cells;
    }

    /**
     * Convertir fecha Y-m-d a serial de Excel
     */
    protected function dateToSerial($dateStr) {
        $dt = new \DateTime($dateStr);
        $base = new \DateTime('1899-12-30');
        $diff = $base->diff($dt);
        return $diff->days;
    }

    /**
     * Escapar texto para XML
     */
    protected function esc($str) {
        return htmlspecialchars((string)$str, ENT_XML1 | ENT_QUOTES, 'UTF-8');
    }

    /**
     * Convertir indice de columna a letra Excel (0=A, 1=B, ..., 25=Z, 26=AA)
     */
    protected function colLetter($idx) {
        $letter = '';
        $idx++;
        while ($idx > 0) {
            $idx--;
            $letter = chr(65 + ($idx % 26)) . $letter;
            $idx = intdiv($idx, 26);
        }
        return $letter;
    }

    /**
     * Generar y guardar archivo XLSX
     */
    public function save($filePath) {
        $zip = new \ZipArchive();
        if ($zip->open($filePath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
            return false;
        }

        // [Content_Types].xml
        $zip->addFromString('[Content_Types].xml', $this->buildContentTypes());

        // _rels/.rels
        $zip->addFromString('_rels/.rels', $this->buildRels());

        // xl/workbook.xml
        $zip->addFromString('xl/workbook.xml', $this->buildWorkbook());

        // xl/_rels/workbook.xml.rels
        $zip->addFromString('xl/_rels/workbook.xml.rels', $this->buildWorkbookRels());

        // xl/styles.xml - con formato de fecha
        $zip->addFromString('xl/styles.xml', $this->buildStyles());

        // xl/sharedStrings.xml
        $strings = $this->collectStrings();
        $zip->addFromString('xl/sharedStrings.xml', $this->buildSharedStrings($strings));

        // Hojas
        $sheetIdx = 1;
        foreach ($this->sheets as $name => $sheet) {
            $zip->addFromString("xl/worksheets/sheet{$sheetIdx}.xml", $this->buildSheet($sheet, $strings));
            $sheetIdx++;
        }

        $zip->close();
        return true;
    }

    /**
     * Generar y retornar contenido como string
     */
    public function output() {
        $tmpFile = tempnam(sys_get_temp_dir(), 'xlsx_');
        $this->save($tmpFile);
        $content = file_get_contents($tmpFile);
        unlink($tmpFile);
        return $content;
    }

    protected function collectStrings() {
        $strings = [];
        $index = 0;
        foreach ($this->sheets as $sheet) {
            foreach ($sheet['rows'] as $row) {
                foreach ($row as $cell) {
                    // Incluir todo lo que sea string (incluyendo '01', '02', etc.)
                    // Excluir arrays (fechas) y numeros puros (int/float)
                    if (is_string($cell)) {
                        if (!isset($strings[$cell])) {
                            $strings[$cell] = $index++;
                        }
                    }
                }
            }
        }
        return $strings;
    }

    protected function buildContentTypes() {
        $parts = '';
        $idx = 1;
        foreach ($this->sheets as $name => $sheet) {
            $parts .= '<Override PartName="/xl/worksheets/sheet' . $idx . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
            $idx++;
        }
        return '<?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/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
' . $parts . '
</Types>';
    }

    protected function buildRels() {
        return '<?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>';
    }

    protected function buildWorkbook() {
        $sheets = '';
        $idx = 1;
        foreach ($this->sheets as $name => $sheet) {
            $sheets .= '<sheet name="' . $this->esc($name) . '" sheetId="' . $idx . '" r:id="rId' . ($idx + 2) . '"/>';
            $idx++;
        }
        return '<?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>' . $sheets . '</sheets>
</workbook>';
    }

    protected function buildWorkbookRels() {
        $rels = '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>';
        $rels .= '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>';
        $idx = 1;
        foreach ($this->sheets as $name => $sheet) {
            $rId = $idx + 2;
            $rels .= '<Relationship Id="rId' . $rId . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . $idx . '.xml"/>';
            $idx++;
        }
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
' . $rels . '
</Relationships>';
    }

    protected function buildStyles() {
        // numFmtId 164 = formato personalizado de fecha yyyy-mm-dd
        // cellXfs[0] = General (texto/numero)
        // cellXfs[1] = Fecha (numFmtId=164)
        // cellXfs[2] = Header (negrita)
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="1">
<numFmt numFmtId="164" formatCode="yyyy-mm-dd"/>
</numFmts>
<fonts count="2">
<font><sz val="11"/><name val="Calibri"/></font>
<font><b/><sz val="11"/><name val="Calibri"/></font>
</fonts>
<fills count="3">
<fill><patternFill patternType="none"/></fill>
<fill><patternFill patternType="gray125"/></fill>
<fill><patternFill patternType="solid"><fgColor rgb="FFD9E1F2"/></patternFill></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="3">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/>
</cellXfs>
</styleSheet>';
    }

    protected function buildSharedStrings($strings) {
        $count = count($strings);
        $items = '';
        // Ordenar por indice
        $sorted = array_flip($strings);
        ksort($sorted);
        foreach ($sorted as $idx => $str) {
            $items .= '<si><t>' . $this->esc($str) . '</t></si>';
        }
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' . $count . '" uniqueCount="' . $count . '">
' . $items . '
</sst>';
    }

    protected function buildSheet($sheet, $strings) {
        $rowsXml = '';
        $rowNum = 1;
        foreach ($sheet['rows'] as $rowIdx => $row) {
            $cellsXml = '';
            foreach ($row as $colIdx => $cell) {
                $ref = $this->colLetter($colIdx) . $rowNum;

                if (is_array($cell) && isset($cell['date'])) {
                    // Celda de fecha: valor serial, estilo 1 (formato fecha)
                    $serial = $this->dateToSerial($cell['date']);
                    $cellsXml .= '<c r="' . $ref . '" s="1"><v>' . $serial . '</v></c>';
                } elseif (is_numeric($cell) && !is_string($cell)) {
                    $cellsXml .= '<c r="' . $ref . '"><v>' . $cell . '</v></c>';
                } else {
                    // String → shared string
                    $strIdx = isset($strings[$cell]) ? $strings[$cell] : 0;
                    // Header row (primera fila) con estilo 2 (negrita+fondo)
                    $style = ($rowIdx === 0) ? ' s="2"' : '';
                    $cellsXml .= '<c r="' . $ref . '" t="s"' . $style . '><v>' . $strIdx . '</v></c>';
                }
            }
            $rowsXml .= '<row r="' . $rowNum . '">' . $cellsXml . '</row>';
            $rowNum++;
        }

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