<?php
/**
 * Pure-PHP multi-sheet XLSX writer. No Composer.
 *
 * Cell type inference:
 *   - numeric value -> number
 *   - DateTime or 'YYYY-MM-DD'[ HH:MM:SS] string -> date (style 4)
 *   - everything else -> string (via sharedStrings)
 *
 * Style IDs (matches HTML dashboard color palette):
 *    0 = default
 *    1 = (reserved)
 *    2 = bold-white-on-blue   (#206BC4) — HTML primary header
 *    3 = bold-white-on-slate  (#475569) — HTML legacy table header
 *    4 = date YYYY-MM-DD
 *    5 = total row (bold, amber #FEF3C7 background, dark text)
 *    6 = section title (bold, slate #334155 bg, white text, 13pt)
 *    7 = currency $#,##0.00
 *    8 = currency total row (bold + amber bg + currency format)
 *    9 = good (green tint #DCFCE7)
 *   10 = warning (amber tint #FEF3C7)
 *   11 = bad (red tint #FEE2E2)
 *   12 = subtle header gray (#F1F5F9)
 *   13 = bold default (no fill)
 *   14 = report KPI card title (bold purple-blue #4F46E5)
 *   15 = right-aligned number
 *   16 = pct format
 *
 * Usage:
 *   $w = new XlsxWriter();
 *   $w->addSheet('Dashboard', $rows, true);
 *   // Per-cell styles via 2-tuple: [value, styleId]
 *   $w->save('/tmp/out.xlsx');
 */
class XlsxWriter
{
    /** @var array<int, array{name:string, rows:array, header_bold:bool, freeze:bool, col_widths:?array, merges:array}> */
    private array $sheets = [];
    /** @var array<string, int> */
    private array $sharedStrings = [];
    private int $sharedCount = 0;
    /** @var array<int, string> */
    private array $sharedList = [];

    public function addSheet(string $name, array $rows, bool $headerBold = true, bool $freeze = true, ?array $colWidths = null, array $merges = []): void
    {
        $clean = preg_replace('/[\\\\\/\?\*\[\]:]/', '_', $name);
        $clean = mb_substr($clean, 0, 31);
        $this->sheets[] = [
            'name' => $clean, 'rows' => $rows,
            'header_bold' => $headerBold, 'freeze' => $freeze,
            'col_widths' => $colWidths, 'merges' => $merges,
        ];
    }

    public function save(string $path): void
    {
        $zip = new ZipArchive();
        if ($zip->open($path, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
            throw new RuntimeException("Cannot create xlsx at $path");
        }

        $sheetXmls = [];
        foreach ($this->sheets as $i => $sheet) {
            $sheetXmls[$i] = $this->buildSheetXml($sheet);
        }

        $zip->addFromString('[Content_Types].xml', $this->contentTypesXml());
        $zip->addFromString('_rels/.rels', $this->rootRelsXml());
        $zip->addFromString('xl/workbook.xml', $this->workbookXml());
        $zip->addFromString('xl/_rels/workbook.xml.rels', $this->workbookRelsXml());
        $zip->addFromString('xl/styles.xml', $this->stylesXml());
        $zip->addFromString('xl/sharedStrings.xml', $this->sharedStringsXml());
        foreach ($sheetXmls as $i => $xml) {
            $zip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $xml);
        }
        $zip->close();
    }

    /** Output directly to PHP stream (HTTP download path). */
    public function output(): string
    {
        $tmp = tempnam(sys_get_temp_dir(), 'xlsx_');
        $this->save($tmp);
        $bin = file_get_contents($tmp);
        @unlink($tmp);
        return $bin;
    }

    /* ---------- per-sheet XML ---------- */
    private function buildSheetXml(array $sheet): string
    {
        $rowsXml = '';
        $maxCol = 0;
        foreach ($sheet['rows'] as $r => $row) {
            $cellsXml = '';
            $colN = 0;
            foreach ($row as $cell) {
                $colN++;
                // Support 2-tuple [value, styleId]
                if (is_array($cell) && count($cell) === 2 && array_keys($cell) === [0, 1]) {
                    [$val, $styleOverride] = $cell;
                } else {
                    $val = $cell;
                    $styleOverride = null;
                }

                $ref = $this->cellRef($colN, $r + 1);
                if ($val === null || $val === '') continue;

                $defaultStyle = ($r === 0 && $sheet['header_bold']) ? 2 : 0;
                $styleId = $styleOverride !== null ? $styleOverride : $defaultStyle;

                if (is_int($val) || is_float($val) || (is_numeric($val) && !is_string($val))) {
                    $cellsXml .= '<c r="' . $ref . '" s="' . $styleId . '"><v>' . $this->numFmt($val) . '</v></c>';
                } elseif ($val instanceof DateTimeInterface
                          || (is_string($val) && preg_match('/^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}:\d{2})?$/', $val))) {
                    $serial = $this->dateSerial($val);
                    // If styleOverride supplied, honor it (could be a custom date style); else style 4
                    $cellsXml .= '<c r="' . $ref . '" s="' . ($styleOverride ?? 4) . '"><v>' . $serial . '</v></c>';
                } else {
                    $idx = $this->sharedString((string) $val);
                    $cellsXml .= '<c r="' . $ref . '" s="' . $styleId . '" t="s"><v>' . $idx . '</v></c>';
                }
            }
            if ($colN > $maxCol) $maxCol = $colN;
            $rowsXml .= '<row r="' . ($r + 1) . '">' . $cellsXml . '</row>';
        }

        $colWidths = '';
        if ($maxCol > 0) {
            if ($sheet['col_widths']) {
                $colXml = '';
                foreach ($sheet['col_widths'] as $idx => $w) {
                    $col = $idx + 1;
                    $colXml .= '<col min="' . $col . '" max="' . $col . '" width="' . (float)$w . '" customWidth="1"/>';
                }
                $colWidths = '<cols>' . $colXml . '</cols>';
            } else {
                $colWidths = '<cols><col min="1" max="' . $maxCol . '" width="18" customWidth="1"/></cols>';
            }
        }
        $freeze = $sheet['freeze']
            ? '<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
            : '';

        $mergeXml = '';
        if (!empty($sheet['merges'])) {
            $items = '';
            foreach ($sheet['merges'] as $ref) $items .= '<mergeCell ref="' . htmlspecialchars($ref) . '"/>';
            $mergeXml = '<mergeCells count="' . count($sheet['merges']) . '">' . $items . '</mergeCells>';
        }

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

    private function cellRef(int $col, int $row): string
    {
        $name = '';
        while ($col > 0) {
            $rem = ($col - 1) % 26;
            $name = chr(65 + $rem) . $name;
            $col = (int) (($col - $rem - 1) / 26);
        }
        return $name . $row;
    }

    private function numFmt(float|int|string $v): string
    {
        if (is_int($v)) return (string) $v;
        $f = (float) $v;
        if ((int) $f == $f) return (string) (int) $f;
        return rtrim(rtrim(sprintf('%.6F', $f), '0'), '.');
    }

    private function dateSerial(string|DateTimeInterface $v): float
    {
        if ($v instanceof DateTimeInterface) {
            $unix = $v->getTimestamp();
        } else {
            $unix = strtotime($v);
            if ($unix === false) return 0;
        }
        $serial = ($unix - (-2208988800)) / 86400 + 1;
        if ($serial >= 60) $serial += 1;
        return round($serial, 6);
    }

    private function sharedString(string $s): int
    {
        if (!isset($this->sharedStrings[$s])) {
            $this->sharedStrings[$s] = $this->sharedCount++;
            $this->sharedList[] = $s;
        }
        return $this->sharedStrings[$s];
    }

    /* ---------- package XML parts ---------- */
    private function contentTypesXml(): string
    {
        $sheetOverrides = '';
        foreach ($this->sheets as $i => $_) {
            $sheetOverrides .= '<Override PartName="/xl/worksheets/sheet' . ($i + 1) . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
        }
        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"/>'
            . $sheetOverrides
            . '</Types>';
    }

    private function rootRelsXml(): string
    {
        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>';
    }

    private function workbookXml(): string
    {
        $sheetsXml = '';
        foreach ($this->sheets as $i => $sheet) {
            $sheetsXml .= '<sheet name="' . htmlspecialchars($sheet['name']) . '" sheetId="' . ($i + 1) . '" r:id="rId' . ($i + 2) . '"/>';
        }
        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>' . $sheetsXml . '</sheets>'
            . '</workbook>';
    }

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

    private function stylesXml(): string
    {
        // numFmts:  164 = yyyy-mm-dd,  165 = $#,##0.00,  166 = 0.0%,  167 = #,##0
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'

            . '<numFmts count="4">'
            .   '<numFmt numFmtId="164" formatCode="yyyy-mm-dd"/>'
            .   '<numFmt numFmtId="165" formatCode="&quot;$&quot;#,##0.00"/>'
            .   '<numFmt numFmtId="166" formatCode="0.0%"/>'
            .   '<numFmt numFmtId="167" formatCode="#,##0"/>'
            . '</numFmts>'

            . '<fonts count="6">'
            .   '<font><sz val="11"/><name val="Calibri"/><color rgb="FF1A2332"/></font>'                /* 0 default */
            .   '<font><b/><sz val="11"/><name val="Calibri"/><color rgb="FFFFFFFF"/></font>'             /* 1 white bold */
            .   '<font><b/><sz val="11"/><name val="Calibri"/><color rgb="FF1E293B"/></font>'             /* 2 dark bold */
            .   '<font><b/><sz val="13"/><name val="Calibri"/><color rgb="FFFFFFFF"/></font>'             /* 3 section title */
            .   '<font><b/><sz val="11"/><name val="Calibri"/><color rgb="FF4F46E5"/></font>'             /* 4 indigo bold */
            .   '<font><sz val="11"/><name val="Calibri"/><color rgb="FF475569"/></font>'                 /* 5 slate */
            . '</fonts>'

            . '<fills count="9">'
            .   '<fill><patternFill patternType="none"/></fill>'                                                                                       /* 0 none */
            .   '<fill><patternFill patternType="gray125"/></fill>'                                                                                    /* 1 reserved */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FF206BC4"/><bgColor indexed="64"/></patternFill></fill>'                          /* 2 primary blue */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FF475569"/><bgColor indexed="64"/></patternFill></fill>'                          /* 3 slate header */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FFFEF3C7"/><bgColor indexed="64"/></patternFill></fill>'                          /* 4 amber total */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FFDCFCE7"/><bgColor indexed="64"/></patternFill></fill>'                          /* 5 good green */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FFFEE2E2"/><bgColor indexed="64"/></patternFill></fill>'                          /* 6 bad red */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FF334155"/><bgColor indexed="64"/></patternFill></fill>'                          /* 7 section title */
            .   '<fill><patternFill patternType="solid"><fgColor rgb="FFF1F5F9"/><bgColor indexed="64"/></patternFill></fill>'                          /* 8 subtle gray */
            . '</fills>'

            . '<borders count="2">'
            .   '<border/>'
            .   '<border><left style="thin"><color rgb="FFCBD5E1"/></left><right style="thin"><color rgb="FFCBD5E1"/></right>'
            .   '<top style="thin"><color rgb="FFCBD5E1"/></top><bottom style="thin"><color rgb="FFCBD5E1"/></bottom></border>'
            . '</borders>'

            . '<cellStyleXfs count="1"><xf/></cellStyleXfs>'

            . '<cellXfs count="17">'
            /* 0 default */
            .   '<xf fontId="0" fillId="0" borderId="0" xfId="0"/>'
            /* 1 reserved */
            .   '<xf fontId="0" fillId="0" borderId="0" xfId="0"/>'
            /* 2 bold-white-on-blue (primary header) */
            .   '<xf fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            /* 3 bold-white-on-slate (legacy header) */
            .   '<xf fontId="1" fillId="3" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            /* 4 date YYYY-MM-DD */
            .   '<xf fontId="0" fillId="0" borderId="1" xfId="0" numFmtId="164" applyNumberFormat="1" applyBorder="1"/>'
            /* 5 total row (bold dark on amber) */
            .   '<xf fontId="2" fillId="4" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/>'
            /* 6 section title (white bold on slate, larger) */
            .   '<xf fontId="3" fillId="7" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            /* 7 currency */
            .   '<xf fontId="0" fillId="0" borderId="1" xfId="0" numFmtId="165" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right"/></xf>'
            /* 8 currency total */
            .   '<xf fontId="2" fillId="4" borderId="1" xfId="0" numFmtId="165" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right"/></xf>'
            /* 9 good */
            .   '<xf fontId="0" fillId="5" borderId="1" xfId="0" applyFill="1" applyBorder="1"/>'
            /* 10 warning */
            .   '<xf fontId="0" fillId="4" borderId="1" xfId="0" applyFill="1" applyBorder="1"/>'
            /* 11 bad */
            .   '<xf fontId="0" fillId="6" borderId="1" xfId="0" applyFill="1" applyBorder="1"/>'
            /* 12 subtle */
            .   '<xf fontId="5" fillId="8" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/>'
            /* 13 bold default */
            .   '<xf fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1"/>'
            /* 14 KPI card title */
            .   '<xf fontId="4" fillId="0" borderId="0" xfId="0" applyFont="1"/>'
            /* 15 right-aligned number */
            .   '<xf fontId="0" fillId="0" borderId="1" xfId="0" numFmtId="167" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right"/></xf>'
            /* 16 pct */
            .   '<xf fontId="0" fillId="0" borderId="1" xfId="0" numFmtId="166" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right"/></xf>'
            . '</cellXfs>'

            . '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
            . '</styleSheet>';
    }

    private function sharedStringsXml(): string
    {
        $items = '';
        foreach ($this->sharedList as $s) {
            $items .= '<si><t xml:space="preserve">' . htmlspecialchars($s, ENT_XML1) . '</t></si>';
        }
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' . $this->sharedCount . '" uniqueCount="' . count($this->sharedList) . '">'
            . $items
            . '</sst>';
    }
}

/* ============================================================================
 *  XlsxStyle — short helper for tuple cell style IDs
 * ============================================================================ */
final class XS {
    const HEADER_BLUE  = 2;
    const HEADER_SLATE = 3;
    const DATE         = 4;
    const TOTAL        = 5;
    const SECTION      = 6;
    const CURRENCY     = 7;
    const CURRENCY_TOT = 8;
    const GOOD         = 9;
    const WARN         = 10;
    const BAD          = 11;
    const SUBTLE       = 12;
    const BOLD         = 13;
    const KPI          = 14;
    const NUM          = 15;
    const PCT          = 16;
}
