<?php
/**
 * SimpleXlsx — minimal multi-sheet .xlsx (Office Open XML) writer.
 *
 * No Composer / PhpSpreadsheet: builds a real .xlsx with PHP's built-in ZipArchive.
 * Each worksheet is streamed to a temp file row-by-row, so even a few hundred-thousand
 * rows of "raw data" stay memory-flat.  The header row is bold + frozen and an
 * AutoFilter is applied across it so the ops team can filter immediately in Excel.
 *
 * Usage:
 *   $x = new SimpleXlsx();
 *   $x->addSheet('Summary', $headers, $rows);                 // $rows = array of arrays; cell type auto-detected (int/float => number)
 *   $x->addSheetFromStmt('Raw Data', $headers, $stmt, $num);  // streams a PDOStatement (FETCH_NUM); $num = numeric column indexes
 *   $x->download('report.xlsx');                              // sends headers + body, cleans temp files, exit
 */
class SimpleXlsx
{
    private array $sheets   = [];   // [['name'=>, 'file'=>], ...]
    private array $tmpFiles = [];
    private string $tmpDir;

    public function __construct(?string $tmpDir = null)
    {
        $cand = $tmpDir ?: (__DIR__ . '/../storage/tmp');
        if (!is_dir($cand)) @mkdir($cand, 0775, true);
        $this->tmpDir = (is_dir($cand) && is_writable($cand)) ? $cand : sys_get_temp_dir();
    }

    /** 0-based column index -> spreadsheet letter (0=A, 25=Z, 26=AA, ...). */
    private function colLetter(int $i): string
    {
        $s = '';
        for ($n = $i + 1; $n > 0; $n = intdiv($n - 1, 26)) $s = chr(65 + ($n - 1) % 26) . $s;
        return $s;
    }

    private function esc(string $s): string
    {
        // Drop XML-illegal control characters, then escape entities.
        $s = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F]/u', '', $s);
        return htmlspecialchars((string) $s, ENT_QUOTES | ENT_XML1, 'UTF-8');
    }

    private function safeName(string $name): string
    {
        $name = trim(preg_replace('/[\[\]:\*\?\/\\\\]/', ' ', $name));
        return mb_substr($name === '' ? 'Sheet' : $name, 0, 31);
    }

    /** Build one cell.  $mode: 'auto' (number iff PHP int/float) | 'num' (number iff numeric) | 'text'. */
    private function cell(int $col, int $row, $v, string $mode): string
    {
        $ref = $this->colLetter($col) . $row;
        $isNum = ($mode === 'auto' && (is_int($v) || is_float($v)))
              || ($mode === 'num'  && $v !== '' && $v !== null && is_numeric($v));
        if ($isNum) return '<c r="' . $ref . '"><v>' . (0 + $v) . '</v></c>';
        $v = ($v === null) ? '' : (string) $v;
        if ($v === '') return '<c r="' . $ref . '"/>';
        return '<c r="' . $ref . '" t="inlineStr"><is><t xml:space="preserve">' . $this->esc($v) . '</t></is></c>';
    }

    /** @return array{0:resource,1:string,2:int} [$fh, $tmpPath, $colCount] */
    private function beginSheet(string $name, array $headers): array
    {
        $tmp = tempnam($this->tmpDir, 'xlsx_s');
        $this->tmpFiles[] = $tmp;
        $fh = fopen($tmp, 'wb');
        fwrite($fh, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
            . '<sheetData><row r="1">');
        $headers = array_values($headers);
        foreach ($headers as $i => $h) {
            fwrite($fh, '<c r="' . $this->colLetter($i) . '1" t="inlineStr" s="1"><is><t xml:space="preserve">' . $this->esc((string) $h) . '</t></is></c>');
        }
        fwrite($fh, '</row>');
        return [$fh, $tmp, count($headers)];
    }

    private function endSheet($fh, string $name, string $tmp, int $colCount, int $lastRow): void
    {
        $lastCol = $this->colLetter(max(0, $colCount - 1));
        fwrite($fh, '</sheetData><autoFilter ref="A1:' . $lastCol . max(1, $lastRow) . '"/></worksheet>');
        fclose($fh);
        $this->sheets[] = ['name' => $this->safeName($name), 'file' => $tmp];
    }

    public function addSheet(string $name, array $headers, array $rows): void
    {
        [$fh, $tmp, $cols] = $this->beginSheet($name, $headers);
        $r = 1;
        foreach ($rows as $row) {
            $r++;
            $buf = '<row r="' . $r . '">';
            $i = 0;
            foreach (array_values($row) as $v) { $buf .= $this->cell($i++, $r, $v, 'auto'); }
            fwrite($fh, $buf . '</row>');
        }
        $this->endSheet($fh, $name, $tmp, $cols, $r);
    }

    public function addSheetFromStmt(string $name, array $headers, PDOStatement $stmt, array $numericCols = []): void
    {
        $numeric = array_fill_keys($numericCols, true);
        [$fh, $tmp, $cols] = $this->beginSheet($name, $headers);
        $r = 1;
        while (($row = $stmt->fetch(PDO::FETCH_NUM)) !== false) {
            $r++;
            $buf = '<row r="' . $r . '">';
            foreach ($row as $i => $v) { $buf .= $this->cell($i, $r, $v, isset($numeric[$i]) ? 'num' : 'text'); }
            fwrite($fh, $buf . '</row>');
        }
        $this->endSheet($fh, $name, $tmp, $cols, $r);
    }

    /** Build the .xlsx package into a temp file and return its path. */
    private function build(): string
    {
        $zipPath = tempnam($this->tmpDir, 'xlsx_z');
        $this->tmpFiles[] = $zipPath;
        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
            throw new RuntimeException('Cannot create the Excel archive.');
        }
        $n = count($this->sheets);

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

        $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>';
        foreach ($this->sheets as $i => $s) {
            $sid = $i + 1;
            $wb .= '<sheet name="' . $this->esc($s['name']) . '" sheetId="' . $sid . '" r:id="rId' . $sid . '"/>';
        }
        $wb .= '</sheets></workbook>';
        $zip->addFromString('xl/workbook.xml', $wb);

        $rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
              . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
        foreach ($this->sheets as $i => $s) {
            $sid = $i + 1;
            $rels .= '<Relationship Id="rId' . $sid . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . $sid . '.xml"/>';
        }
        $rels .= '<Relationship Id="rId' . ($n + 1) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
               . '</Relationships>';
        $zip->addFromString('xl/_rels/workbook.xml.rels', $rels);

        $zip->addFromString('xl/styles.xml',
            '<?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="Calibri"/></font><font><b/><sz val="11"/><name val="Calibri"/></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" applyFont="1"/></cellXfs>'
            . '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
            . '</styleSheet>');

        foreach ($this->sheets as $i => $s) {
            $zip->addFile($s['file'], 'xl/worksheets/sheet' . ($i + 1) . '.xml');
        }
        $zip->close();   // ZipArchive reads the worksheet temp files here
        return $zipPath;
    }

    /** Write the workbook to a path on disk (does not exit). */
    public function save(string $path): void
    {
        copy($this->build(), $path);
        $this->cleanup();
    }

    /** Stream the workbook to the browser as a download, then exit. */
    public function download(string $filename): void
    {
        $zipPath = $this->build();
        while (ob_get_level() > 0) ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . filesize($zipPath));
        header('Cache-Control: max-age=0, no-cache');
        readfile($zipPath);
        $this->cleanup();
        exit;
    }

    private function cleanup(): void
    {
        foreach ($this->tmpFiles as $f) @unlink($f);
        $this->tmpFiles = [];
    }

    public function __destruct() { $this->cleanup(); }
}
