<?php

/**
 * Minimal XLSX writer — real Excel files with no composer dependency.
 *
 * Writes the handful of OOXML parts Excel needs, using inline strings so no
 * shared-string table has to be maintained. Styles are a fixed palette defined
 * once in styles.xml and referenced by index, which keeps the sheet XML small.
 */
final class Xlsx
{
    // Style indexes, matching the order written in styles.xml below.
    public const S_DEFAULT = 0;
    public const S_TITLE   = 1;
    public const S_SUBTITLE= 2;
    public const S_HEADER  = 3;
    public const S_TEXT    = 4;
    public const S_TEXT_ALT= 5;
    public const S_DATE    = 6;
    public const S_DATE_ALT= 7;
    public const S_NUM     = 8;
    public const S_NUM_ALT = 9;
    public const S_LABEL   = 10;
    public const S_BIG     = 11;

    /** @var array<int,array{name:string,rows:array,cols:array}> */
    private array $sheets = [];

    public function addSheet(string $name, array $rows, array $colWidths = [],
                            int $freezeRow = 0, ?string $autofilter = null): void
    {
        $this->sheets[] = [
            'name' => $name, 'rows' => $rows, 'cols' => $colWidths,
            'freeze' => $freezeRow, 'autofilter' => $autofilter,
        ];
    }

    /** Column letter for a zero-based index: 0 -> A, 26 -> AA. */
    public static function col(int $i): string
    {
        return self::colName($i);
    }

    /** Excel stores dates as days since 1900; 25569 bridges that to the unix epoch. */
    public static function dateValue(?string $datetime): ?float
    {
        if (!$datetime) {
            return null;
        }
        $ts = strtotime($datetime);
        if ($ts === false) {
            return null;
        }
        return ($ts + date('Z', $ts)) / 86400 + 25569;
    }

    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);
        }

        $n = count($this->sheets);
        $contentTypes = '<?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"/>';
        for ($i = 1; $i <= $n; $i++) {
            $contentTypes .= '<Override PartName="/xl/worksheets/sheet' . $i
                . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
        }
        $zip->addFromString('[Content_Types].xml', $contentTypes . '</Types>');

        $zip->addFromString('_rels/.rels',
            '<?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>');

        $wb   = '<?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>';
        $rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
              . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
        foreach ($this->sheets as $i => $sheet) {
            $id    = $i + 1;
            $wb   .= '<sheet name="' . self::esc($sheet['name']) . '" sheetId="' . $id . '" r:id="rId' . $id . '"/>';
            $rels .= '<Relationship Id="rId' . $id . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"'
                   . ' Target="worksheets/sheet' . $id . '.xml"/>';
            $zip->addFromString('xl/worksheets/sheet' . $id . '.xml', $this->sheetXml($sheet));
        }
        $rels .= '<Relationship Id="rId' . ($n + 1) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>';

        $zip->addFromString('xl/workbook.xml', $wb . '</sheets></workbook>');
        $zip->addFromString('xl/_rels/workbook.xml.rels', $rels . '</Relationships>');
        $zip->addFromString('xl/styles.xml', $this->stylesXml());
        $zip->close();
    }

    private function sheetXml(array $sheet): string
    {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
             . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
             // rightToLeft puts column A on the right, which is what a Hebrew reader expects
             . '<sheetViews><sheetView rightToLeft="1" workbookViewId="0" tabSelected="1">';
        if (!empty($sheet['freeze'])) {
            $xml .= '<pane ySplit="' . $sheet['freeze'] . '" topLeftCell="A' . ($sheet['freeze'] + 1)
                  . '" activePane="bottomLeft" state="frozen"/>';
        }
        $xml .= '</sheetView></sheetViews><sheetFormatPr defaultRowHeight="16"/>';

        if ($sheet['cols']) {
            $xml .= '<cols>';
            foreach ($sheet['cols'] as $i => $w) {
                $xml .= '<col min="' . ($i + 1) . '" max="' . ($i + 1) . '" width="' . $w . '" customWidth="1"/>';
            }
            $xml .= '</cols>';
        }

        $xml .= '<sheetData>';
        foreach ($sheet['rows'] as $r => $row) {
            $rowNum = $r + 1;
            $height = $row['height'] ?? null;
            $xml   .= '<row r="' . $rowNum . '"' . ($height ? ' ht="' . $height . '" customHeight="1"' : '') . '>';
            foreach (($row['cells'] ?? $row) as $c => $cell) {
                $ref   = self::colName($c) . $rowNum;
                $style = is_array($cell) ? ($cell['s'] ?? self::S_TEXT) : self::S_TEXT;
                $value = is_array($cell) ? ($cell['v'] ?? '') : $cell;
                $type  = is_array($cell) ? ($cell['t'] ?? 'str') : 'str';

                if ($value === null || $value === '') {
                    $xml .= '<c r="' . $ref . '" s="' . $style . '"/>';
                } elseif ($type === 'n' || $type === 'd') {
                    $xml .= '<c r="' . $ref . '" s="' . $style . '"><v>' . $value . '</v></c>';
                } else {
                    $xml .= '<c r="' . $ref . '" s="' . $style . '" t="inlineStr"><is><t xml:space="preserve">'
                          . self::esc((string) $value) . '</t></is></c>';
                }
            }
            $xml .= '</row>';
        }
        $xml .= '</sheetData>';

        if (!empty($sheet['autofilter'])) {
            $xml .= '<autoFilter ref="' . $sheet['autofilter'] . '"/>';
        }
        return $xml . '</worksheet>';
    }

    private function stylesXml(): string
    {
        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="dd/mm/yyyy hh:mm"/></numFmts>'
        . '<fonts count="6">'
          . '<font><sz val="11"/><name val="Arial"/></font>'                                    // 0 body
          . '<font><b/><sz val="16"/><color rgb="FFFFFFFF"/><name val="Arial"/></font>'          // 1 title
          . '<font><sz val="10"/><color rgb="FF6B6B6B"/><name val="Arial"/></font>'              // 2 subtitle
          . '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Arial"/></font>'          // 3 header
          . '<font><b/><sz val="11"/><name val="Arial"/></font>'                                 // 4 label
          . '<font><b/><sz val="20"/><color rgb="FFD65622"/><name val="Arial"/></font>'          // 5 big number
        . '</fonts>'
        . '<fills count="5">'
          . '<fill><patternFill patternType="none"/></fill>'
          . '<fill><patternFill patternType="gray125"/></fill>'
          . '<fill><patternFill patternType="solid"><fgColor rgb="FFD65622"/><bgColor indexed="64"/></patternFill></fill>'  // 2 brand
          . '<fill><patternFill patternType="solid"><fgColor rgb="FF3B3B3B"/><bgColor indexed="64"/></patternFill></fill>'  // 3 header grey
          . '<fill><patternFill patternType="solid"><fgColor rgb="FFF7F5F2"/><bgColor indexed="64"/></patternFill></fill>'  // 4 zebra
        . '</fills>'
        . '<borders count="2">'
          . '<border><left/><right/><top/><bottom/><diagonal/></border>'
          . '<border><left style="thin"><color rgb="FFE0DDD4"/></left><right style="thin"><color rgb="FFE0DDD4"/></right>'
            . '<top style="thin"><color rgb="FFE0DDD4"/></top><bottom style="thin"><color rgb="FFE0DDD4"/></bottom><diagonal/></border>'
        . '</borders>'
        . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
        . '<cellXfs count="12">'
          . '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'                                                        // 0 default
          . '<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1">'
            . '<alignment vertical="center" indent="1"/></xf>'                                                                      // 1 title
          . '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1" applyAlignment="1">'
            . '<alignment vertical="center" indent="1"/></xf>'                                                                      // 2 subtitle
          . '<xf numFmtId="0" fontId="3" fillId="3" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">'
            . '<alignment horizontal="center" vertical="center" wrapText="1"/></xf>'                                                // 3 header
          . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1" applyAlignment="1">'
            . '<alignment vertical="top" wrapText="1" readingOrder="2"/></xf>'                                                      // 4 text
          . '<xf numFmtId="0" fontId="0" fillId="4" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1">'
            . '<alignment vertical="top" wrapText="1" readingOrder="2"/></xf>'                                                      // 5 text alt
          . '<xf numFmtId="164" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1">'
            . '<alignment horizontal="center" vertical="top"/></xf>'                                                                // 6 date
          . '<xf numFmtId="164" fontId="0" fillId="4" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1">'
            . '<alignment horizontal="center" vertical="top"/></xf>'                                                                // 7 date alt
          . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1" applyAlignment="1">'
            . '<alignment horizontal="center" vertical="top"/></xf>'                                                                // 8 num
          . '<xf numFmtId="0" fontId="0" fillId="4" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1">'
            . '<alignment horizontal="center" vertical="top"/></xf>'                                                                // 9 num alt
          . '<xf numFmtId="0" fontId="4" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1">'
            . '<alignment vertical="center" readingOrder="2"/></xf>'                                                                // 10 label
          . '<xf numFmtId="0" fontId="5" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1">'
            . '<alignment horizontal="center" vertical="center"/></xf>'                                                             // 11 big
        . '</cellXfs>'
        . '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
        . '</styleSheet>';
    }

    private static function colName(int $i): string
    {
        $name = '';
        for ($n = $i + 1; $n > 0; $n = intdiv($n - 1, 26)) {
            $name = chr(65 + ($n - 1) % 26) . $name;
        }
        return $name;
    }

    private static function esc(string $s): string
    {
        // Control characters are not legal in XML and Excel refuses the file.
        $s = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F]/u', '', $s);
        return htmlspecialchars((string) $s, ENT_QUOTES | ENT_XML1, 'UTF-8');
    }
}
