<?php
/**
 * Minimal XLSX reader for Chess Podium bulk import.
 * Uses PHP ZipArchive + SimpleXML only. No external dependencies.
 *
 * @package Chess_Podium
 */

if (!defined('ABSPATH')) {
    exit;
}

final class ChessPodium_XlsxReader
{
    private const NS_MAIN = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';

    /** @var array<string> */
    private $sharedStrings = [];

    /** @var array<array<string|int|float>> */
    private $rows = [];

    /** @var string|null */
    private $error = null;

    /** @var string|null Last parse error (static for error retrieval after read) */
    private static $lastError = null;

    /**
     * Parse xlsx file and return rows (array of arrays).
     *
     * @param string $filePath Path to .xlsx file
     * @return array<array<string|int|float>>|false Rows or false on error
     */
    public static function read(string $filePath)
    {
        self::$lastError = null;
        $reader = new self();
        if ($reader->parse($filePath)) {
            return $reader->rows;
        }
        self::$lastError = $reader->error;
        return false;
    }

    public static function getLastError(): ?string
    {
        return self::$lastError;
    }

    private function parse(string $filePath): bool
    {
        $this->error = null;
        $this->rows = [];
        $this->sharedStrings = [];

        if (!extension_loaded('zip') || !class_exists('ZipArchive')) {
            $this->error = 'PHP Zip extension required';
            return false;
        }

        $zip = new ZipArchive();
        if ($zip->open($filePath, ZipArchive::RDONLY) !== true) {
            $this->error = 'Cannot open xlsx file';
            return false;
        }

        $sharedXml = $zip->getFromName('xl/sharedStrings.xml') ?: $zip->getFromName('xl/SharedStrings.xml');
        if ($sharedXml !== false) {
            $this->parseSharedStrings($sharedXml);
        }

        $sheetNames = ['xl/worksheets/sheet1.xml', 'xl/worksheets/sheet2.xml', 'xl/worksheets/sheet.xml'];
        $sheetXml = false;
        foreach ($sheetNames as $name) {
            $sheetXml = $zip->getFromName($name);
            if ($sheetXml !== false) {
                break;
            }
        }
        if ($sheetXml === false) {
            $entries = [];
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $entries[] = $zip->getNameIndex($i);
            }
            $sheetXml = false;
            foreach ($entries as $entry) {
                if (preg_match('#xl/worksheets/sheet\d*\.xml$#', $entry)) {
                    $sheetXml = $zip->getFromName($entry);
                    break;
                }
            }
        }
        $zip->close();

        if ($sheetXml === false) {
            $this->error = 'No worksheet found in xlsx';
            return false;
        }

        $this->parseSheet($sheetXml);
        return true;
    }

    private function parseSharedStrings(string $xml): void
    {
        $doc = @simplexml_load_string($xml);
        if (!$doc) {
            return;
        }
        $ns = self::NS_MAIN;
        $sst = $doc->getName() === 'sst' ? $doc : ($doc->children($ns)->sst ?? $doc->sst ?? null);
        if (!$sst) {
            return;
        }
        $siList = $sst->children($ns)->si ?? $sst->si ?? [];
        foreach ($siList as $si) {
            if (isset($si->t)) {
                $this->sharedStrings[] = (string) $si->t;
            } elseif (isset($si->r)) {
                $parts = [];
                foreach ($si->r as $r) {
                    $parts[] = isset($r->t) ? (string) $r->t : '';
                }
                $this->sharedStrings[] = implode('', $parts);
            } else {
                $this->sharedStrings[] = '';
            }
        }
    }

    private function parseSheet(string $xml): void
    {
        $doc = @simplexml_load_string($xml);
        if (!$doc) {
            return;
        }
        $ns = self::NS_MAIN;
        $sheetData = $doc->children($ns)->sheetData ?? $doc->sheetData ?? null;
        if (!$sheetData) {
            return;
        }
        $rows = $sheetData->children($ns)->row ?? $sheetData->row ?? [];
        $cellMap = [];
        $maxRow = 0;
        $maxCol = 0;

        foreach ($rows as $row) {
            $attrs = $row->attributes();
            $rowIdx = (int) ($attrs['r'] ?? 0);
            if ($rowIdx > $maxRow) {
                $maxRow = $rowIdx;
            }
            $cells = $row->children($ns)->c ?? $row->c ?? [];
            foreach ($cells as $c) {
                $cAttrs = $c->attributes();
                $ref = (string) ($cAttrs['r'] ?? '');
                if (!preg_match('/^([A-Z]+)(\d+)$/i', $ref, $m)) {
                    continue;
                }
                $col = $this->colToIndex(strtoupper($m[1]));
                $r = (int) $m[2];
                $val = $this->getCellValue($c);
                $cellMap[$r][$col] = $val;
                if ($col > $maxCol) {
                    $maxCol = $col;
                }
            }
        }

        for ($r = 1; $r <= $maxRow; $r++) {
            $rowData = [];
            for ($col = 0; $col <= $maxCol; $col++) {
                $rowData[] = $cellMap[$r][$col] ?? '';
            }
            $this->rows[] = $rowData;
        }
    }

    private function colToIndex(string $col): int
    {
        $idx = 0;
        $len = strlen($col);
        for ($i = 0; $i < $len; $i++) {
            $idx = $idx * 26 + (ord($col[$i]) - 64);
        }
        return $idx - 1;
    }

    /**
     * @param SimpleXMLElement $c
     * @return string|int|float
     */
    private function getCellValue($c)
    {
        $attrs = $c->attributes();
        $type = (string) ($attrs['t'] ?? '');
        $vEl = $c->children(self::NS_MAIN)->v ?? $c->v ?? null;
        $v = $vEl !== null ? (string) $vEl : '';
        if ($v === '') {
            if ($type === 'inlineStr') {
                $is = $c->children(self::NS_MAIN)->is ?? $c->is ?? null;
                if ($is !== null) {
                    $t = $is->children(self::NS_MAIN)->t ?? $is->t ?? null;
                    return $t !== null ? (string) $t : '';
                }
            }
            return '';
        }
        if ($type === 's') {
            $idx = (int) $v;
            return $this->sharedStrings[$idx] ?? '';
        }
        if ($type === 'n' || is_numeric($v)) {
            return strpos($v, '.') !== false ? (float) $v : (int) $v;
        }
        return $v;
    }
}
