<?php

declare(strict_types=1);

namespace PpPhp\Core\DataOps\Readers;

use PpPhp\Core\DataOps\ImportReader;

/**
 * A REAL .xlsx reader (OOXML), using ext/zip + SimpleXML only.
 *
 * Reads the first worksheet, resolving both shared strings and inline strings.
 * Gaps are honoured via each cell's r="B3" reference, so blank cells do not
 * shift later columns out of alignment — the bug you get from naive parsers.
 *
 * Cannot accept pasted text (a workbook is a binary ZIP), so supportsPaste() is
 * false and the wizard offers it on the Upload tab only.
 */
final class XlsxReader implements ImportReader
{
    public static function id(): string { return 'xlsx'; }
    public static function label(): string { return 'Excel Workbook'; }
    public static function extensions(): array { return ['xlsx']; }
    public static function supportsPaste(): bool { return false; }

    public function read(string $path, array $opts): array
    {
        $zip = new \ZipArchive();
        if ($zip->open($path) !== true) return [[], []];

        $shared = self::sharedStrings($zip);
        $sheet  = self::firstSheetXml($zip);
        $zip->close();
        if ($sheet === null) return [[], []];

        $prev = libxml_use_internal_errors(true);
        $xml = simplexml_load_string($sheet);
        libxml_clear_errors();
        libxml_use_internal_errors($prev);
        if ($xml === false || !isset($xml->sheetData)) return [[], []];

        $rows = [];
        foreach ($xml->sheetData->row as $row) {
            $cells = [];
            $max = -1;
            foreach ($row->c as $c) {
                $ref  = (string) $c['r'];
                $idx  = self::colIndex($ref);
                $type = (string) $c['t'];
                if ($type === 'inlineStr') {
                    $val = isset($c->is) ? trim((string) $c->is->t) : '';
                } elseif ($type === 's') {
                    $val = $shared[(int) $c->v] ?? '';
                } else {
                    $val = isset($c->v) ? (string) $c->v : '';
                }
                $cells[$idx] = $val;
                if ($idx > $max) $max = $idx;
            }
            if ($max < 0) continue;
            $line = [];
            for ($i = 0; $i <= $max; $i++) $line[] = (string) ($cells[$i] ?? '');
            if (implode('', $line) === '') continue;   // fully blank row
            $rows[] = $line;
        }

        $header = [];
        if (!empty($opts['hasHeader']) && $rows) {
            $header = array_map(static fn($c) => trim((string) $c), array_shift($rows));
        } elseif ($rows) {
            $header = array_map(static fn($i) => 'Column ' . ($i + 1), array_keys($rows[0]));
        }
        return [$header, $rows];
    }

    /** @return list<string> */
    private static function sharedStrings(\ZipArchive $zip): array
    {
        $raw = $zip->getFromName('xl/sharedStrings.xml');
        if ($raw === false) return [];
        $prev = libxml_use_internal_errors(true);
        $xml = simplexml_load_string($raw);
        libxml_clear_errors();
        libxml_use_internal_errors($prev);
        if ($xml === false) return [];

        $out = [];
        foreach ($xml->si as $si) {
            // A string can be split across runs (<r><t>..</t></r>) — join them.
            $out[] = isset($si->t) && count($si->r) === 0
                ? (string) $si->t
                : implode('', array_map(static fn($r) => (string) $r->t, iterator_to_array($si->r, false)));
        }
        return $out;
    }

    /** Resolve the workbook's first sheet part (never assume sheet1.xml). */
    private static function firstSheetXml(\ZipArchive $zip): ?string
    {
        foreach (['xl/worksheets/sheet1.xml', 'xl/worksheets/sheet.xml'] as $guess) {
            $raw = $zip->getFromName($guess);
            if ($raw !== false) return $raw;
        }
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $name = (string) $zip->getNameIndex($i);
            if (str_starts_with($name, 'xl/worksheets/') && str_ends_with($name, '.xml')) {
                $raw = $zip->getFromName($name);
                if ($raw !== false) return $raw;
            }
        }
        return null;
    }

    /** "B3" → 1 (zero-based column index). */
    private static function colIndex(string $ref): int
    {
        if (!preg_match('/^([A-Z]+)/', strtoupper($ref), $m)) return 0;
        $n = 0;
        foreach (str_split($m[1]) as $ch) $n = $n * 26 + (ord($ch) - 64);
        return $n - 1;
    }
}
