<?php
/**
 * Minimal pure-PHP XLSX reader. No external dependencies — uses
 * ZipArchive (bundled with PHP) and SimpleXML.
 *
 *   $reader = new XlsxReader('/path/file.xlsx');
 *   foreach ($reader->rows(0) as $row) { ... }   // first sheet, array of cell values
 *   $reader->close();
 *
 * Handles: shared strings, inline strings, numbers, booleans, dates
 * (Excel serial -> DateTime). Skips formulas (returns cached value).
 *
 * Tested against the operations team's Sample Report.xlsx (~5300 rows).
 */
class XlsxReader
{
    private ZipArchive $zip;
    private array $sharedStrings = [];
    private array $sheets = [];   // [['name'=>..,'path'=>..], ...]
    private array $dateStyleIds = [];
    private array $styles = [];
    private const EXCEL_EPOCH = -2208988800; // 1900-01-01 UTC

    public function __construct(string $path)
    {
        $this->zip = new ZipArchive();
        if ($this->zip->open($path) !== true) {
            throw new RuntimeException("Cannot open xlsx file: $path");
        }
        $this->loadWorkbook();
        $this->loadSharedStrings();
        $this->loadStyles();
    }

    public function close(): void { $this->zip->close(); }

    /** @return array<int,array{name:string,path:string}> */
    public function sheets(): array { return $this->sheets; }

    private function loadWorkbook(): void
    {
        $rels = $this->loadXml('xl/_rels/workbook.xml.rels');
        $rmap = [];
        foreach ($rels->Relationship as $r) {
            $rmap[(string) $r['Id']] = (string) $r['Target'];
        }
        $wb = $this->loadXml('xl/workbook.xml');
        $ns = $wb->getNamespaces(true);
        foreach ($wb->sheets->sheet as $sheet) {
            $rid = (string) $sheet->attributes($ns['r'])->id;
            $target = $rmap[$rid] ?? null;
            if ($target) {
                $path = (str_starts_with($target, '/')) ? ltrim($target, '/') : 'xl/' . $target;
                $this->sheets[] = ['name' => (string) $sheet['name'], 'path' => $path];
            }
        }
    }

    private function loadSharedStrings(): void
    {
        if ($this->zip->locateName('xl/sharedStrings.xml') === false) return;
        $xml = $this->loadXml('xl/sharedStrings.xml');
        if (!$xml) return;
        foreach ($xml->si as $si) {
            // Either single <t> or rich text composed of <r><t>
            if (isset($si->t)) {
                $this->sharedStrings[] = (string) $si->t;
            } else {
                $combined = '';
                foreach ($si->r as $r) {
                    if (isset($r->t)) $combined .= (string) $r->t;
                }
                $this->sharedStrings[] = $combined;
            }
        }
    }

    private function loadStyles(): void
    {
        if ($this->zip->locateName('xl/styles.xml') === false) return;
        $xml = $this->loadXml('xl/styles.xml');
        if (!$xml) return;

        // Built-in date format IDs: 14-22, 27-36, 45-47, 50-58
        $builtinDate = array_merge(range(14,22), range(27,36), range(45,47), range(50,58));
        $customDates = [];
        if (isset($xml->numFmts)) {
            foreach ($xml->numFmts->numFmt as $nf) {
                $code = (string) $nf['formatCode'];
                $id   = (int) $nf['numFmtId'];
                if (preg_match('/[ymdhsYMDHS]/', $code) && !preg_match('/(General|".*?")/i', $code)) {
                    $customDates[] = $id;
                }
            }
        }
        $dateFmtIds = array_merge($builtinDate, $customDates);

        if (isset($xml->cellXfs)) {
            $i = 0;
            foreach ($xml->cellXfs->xf as $xf) {
                $fmtId = (int) $xf['numFmtId'];
                if (in_array($fmtId, $dateFmtIds, true)) {
                    $this->dateStyleIds[$i] = true;
                }
                $i++;
            }
        }
    }

    private function loadXml(string $entry): ?SimpleXMLElement
    {
        $idx = $this->zip->locateName($entry);
        if ($idx === false) return null;
        $data = $this->zip->getFromIndex($idx);
        if ($data === false) return null;
        return simplexml_load_string($data);
    }

    /**
     * Yields rows as numerically-indexed arrays of cell values.
     * Empty cells in the middle of a row are filled with '' to keep column alignment.
     */
    public function rows(int $sheetIndex = 0): Generator
    {
        if (!isset($this->sheets[$sheetIndex])) {
            throw new InvalidArgumentException("Sheet $sheetIndex does not exist.");
        }
        $sheet = $this->sheets[$sheetIndex];
        $xml = $this->loadXml($sheet['path']);
        if (!$xml) return;

        foreach ($xml->sheetData->row as $rowEl) {
            $rowOut = [];
            $maxCol = 0;
            foreach ($rowEl->c as $c) {
                $ref     = (string) $c['r'];
                $colIdx  = $this->columnIndex($ref);
                $type    = (string) ($c['t'] ?? '');
                $styleId = isset($c['s']) ? (int) $c['s'] : -1;
                $rawVal  = isset($c->v) ? (string) $c->v : '';
                $isVal   = isset($c->is) ? (string) ($c->is->t ?? '') : null;

                if ($type === 's') {
                    $value = $this->sharedStrings[(int) $rawVal] ?? '';
                } elseif ($type === 'b') {
                    $value = $rawVal === '1' ? true : false;
                } elseif ($type === 'inlineStr' && $isVal !== null) {
                    $value = $isVal;
                } elseif ($type === 'str') {
                    $value = $rawVal;
                } elseif ($type === 'e') {
                    $value = '#ERROR';
                } else {
                    // Number — could be a date if style flags it
                    if ($rawVal === '') {
                        $value = '';
                    } elseif (isset($this->dateStyleIds[$styleId])) {
                        $value = $this->excelSerialToDateString((float) $rawVal);
                    } elseif (str_contains($rawVal, '.') || str_contains($rawVal, 'E')) {
                        $value = (float) $rawVal;
                    } else {
                        $value = (int) $rawVal;
                    }
                }

                $rowOut[$colIdx] = $value;
                if ($colIdx > $maxCol) $maxCol = $colIdx;
            }
            // Pad missing cells so caller can index 0..maxCol consistently
            $padded = [];
            for ($i = 0; $i <= $maxCol; $i++) {
                $padded[$i] = $rowOut[$i] ?? '';
            }
            yield $padded;
        }
    }

    private function columnIndex(string $cellRef): int
    {
        // 'A1' -> 0, 'B1' -> 1, 'AA1' -> 26
        $letters = preg_replace('/[0-9]/', '', $cellRef);
        $n = 0;
        for ($i = 0, $len = strlen($letters); $i < $len; $i++) {
            $n = $n * 26 + (ord($letters[$i]) - 64);
        }
        return $n - 1;
    }

    private function excelSerialToDateString(float $serial): string
    {
        // Excel's epoch is 1900-01-00; it treats 1900 as a leap year (it isn't), so we adjust.
        $unix = self::EXCEL_EPOCH + ($serial - 1) * 86400;
        if ($serial >= 60) $unix -= 86400;
        $hasTime = ($serial - floor($serial)) > 1e-9;
        $fmt = $hasTime ? 'Y-m-d H:i:s' : 'Y-m-d';
        return gmdate($fmt, (int) round($unix));
    }
}
