<?php
namespace App\Services;

use App\Core\Dates;

/**
 * Pembaca berkas XLSX tanpa dependensi eksternal (ZipArchive + XMLReader).
 *
 * Kemampuan:
 *  - membaca daftar worksheet
 *  - shared strings & inline strings
 *  - nilai hasil rumus yang di-cache (<v>) dan teks rumusnya (<f>)
 *  - deteksi sel bertipe tanggal dari number format
 *  - nilai error Excel (#REF!, #VALUE!, dsb.)
 *  - membaca sebagian baris (untuk preview) tanpa memuat seluruh berkas
 *  - fallback CSV
 *
 * Bila PhpSpreadsheet terpasang lewat Composer, kelas ini tetap dipakai karena
 * lebih hemat memori untuk berkas besar; PhpSpreadsheet dipakai sebagai cadangan
 * bila berkas gagal dibaca (mis. XLSX tidak standar).
 */
class ExcelReader
{
    private string $file;
    private array $sheets = [];
    private array $sharedStrings = [];
    private array $dateStyles = [];
    private bool $loaded = false;
    private bool $isCsv = false;
    /** Peta rumus bersama (shared formula) per worksheet: si => [f, row, col]. */
    private array $sharedFormulas = [];
    public array $warnings = [];

    /** Kode format bawaan Excel yang merupakan tanggal/waktu. */
    private const BUILTIN_DATE_FORMATS = [14,15,16,17,18,19,20,21,22,27,28,29,30,31,32,33,34,35,36,45,46,47,50,51,52,53,54,55,56,57,58];

    public function __construct(string $file)
    {
        if (!is_file($file)) throw new \RuntimeException('Berkas tidak ditemukan: ' . basename($file));
        $this->file = $file;
        $ext = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        $this->isCsv = in_array($ext, ['csv', 'txt', 'tsv'], true);
    }

    public function isCsv(): bool { return $this->isCsv; }

    /** Memuat metadata: daftar sheet, shared strings, style tanggal. */
    public function load(): void
    {
        if ($this->loaded) return;
        if ($this->isCsv) { $this->sheets = [['name' => 'CSV', 'path' => '', 'rid' => '']]; $this->loaded = true; return; }

        if (!class_exists('\ZipArchive')) {
            throw new \RuntimeException('Ekstensi PHP "zip" tidak tersedia di server. Aktifkan ekstensi zip atau unggah berkas dalam format CSV.');
        }
        $zip = new \ZipArchive();
        if ($zip->open($this->file) !== true) {
            throw new \RuntimeException('Berkas Excel tidak dapat dibuka atau rusak.');
        }

        // Peta rId -> path worksheet
        $relMap = [];
        $rels = $zip->getFromName('xl/_rels/workbook.xml.rels');
        if ($rels) {
            $x = @simplexml_load_string($rels);
            if ($x) foreach ($x->Relationship as $r) {
                $relMap[(string)$r['Id']] = ltrim(str_replace('/xl/', '', '/xl/' . ltrim((string)$r['Target'], '/')), '/');
            }
        }

        $wb = $zip->getFromName('xl/workbook.xml');
        if ($wb) {
            $x = @simplexml_load_string($wb);
            if ($x && isset($x->sheets->sheet)) {
                $i = 0;
                foreach ($x->sheets->sheet as $s) {
                    $i++;
                    $rid = '';
                    foreach ($s->attributes('r', true) as $k => $v) if ($k === 'id') $rid = (string)$v;
                    $path = $relMap[$rid] ?? "worksheets/sheet{$i}.xml";
                    $this->sheets[] = [
                        'name'  => (string)$s['name'],
                        'path'  => 'xl/' . ltrim($path, '/'),
                        'state' => (string)($s['state'] ?? 'visible'),
                        'index' => $i,
                    ];
                }
            }
        }
        if (!$this->sheets) {
            for ($i = 1; $i <= 30; $i++) {
                if ($zip->locateName("xl/worksheets/sheet$i.xml") !== false) {
                    $this->sheets[] = ['name' => "Sheet$i", 'path' => "xl/worksheets/sheet$i.xml", 'state' => 'visible', 'index' => $i];
                }
            }
        }

        // Shared strings (streaming agar hemat memori)
        $ssIdx = $zip->locateName('xl/sharedStrings.xml');
        if ($ssIdx !== false) {
            $stream = $zip->getStream('xl/sharedStrings.xml');
            if ($stream) {
                $buf = stream_get_contents($stream);
                fclose($stream);
                $this->sharedStrings = $this->parseSharedStrings((string)$buf);
            }
        }

        // Style: cellXfs -> numFmtId ; numFmts kustom
        $styles = $zip->getFromName('xl/styles.xml');
        if ($styles) $this->dateStyles = $this->parseDateStyles($styles);

        $zip->close();
        $this->loaded = true;
    }

    private function parseSharedStrings(string $xml): array
    {
        $out = [];
        $r = new \XMLReader();
        if (!@$r->XML($xml, 'UTF-8', LIBXML_NOENT | LIBXML_NONET)) return $out;

        // Maju ke elemen <si> pertama
        while ($r->read()) {
            if ($r->nodeType === \XMLReader::ELEMENT && $r->localName === 'si') break;
        }
        // PENTING: hanya memakai next() di dalam loop — memanggil read() lagi akan
        // melewati satu elemen (bug yang menggeser seluruh indeks shared string).
        while ($r->nodeType === \XMLReader::ELEMENT && $r->localName === 'si') {
            $s = @simplexml_load_string($r->readOuterXml());
            $txt = '';
            if ($s !== false) {
                if (isset($s->t)) {
                    $txt = (string)$s->t;
                } elseif (isset($s->r)) {
                    foreach ($s->r as $run) $txt .= (string)$run->t;
                }
            }
            $out[] = $txt;
            if (!$r->next('si')) break;
        }
        $r->close();
        return $out;
    }

    private function parseDateStyles(string $xml): array
    {
        $isDate = [];
        $x = @simplexml_load_string($xml);
        if (!$x) return $isDate;

        $customDate = [];
        if (isset($x->numFmts->numFmt)) {
            foreach ($x->numFmts->numFmt as $nf) {
                $id = (int)$nf['numFmtId'];
                $code = (string)$nf['formatCode'];
                $stripped = preg_replace('/\[[^\]]*\]|"[^"]*"/', '', $code) ?? $code;
                if (preg_match('/[dmyhs]/i', $stripped) && !preg_match('/^[0#,.\s%eE+\-()_@*\\\\]*$/', $stripped)) {
                    $customDate[$id] = true;
                }
            }
        }
        if (isset($x->cellXfs->xf)) {
            $i = 0;
            foreach ($x->cellXfs->xf as $xf) {
                $numFmtId = (int)($xf['numFmtId'] ?? 0);
                if (in_array($numFmtId, self::BUILTIN_DATE_FORMATS, true) || isset($customDate[$numFmtId])) {
                    $isDate[$i] = true;
                }
                $i++;
            }
        }
        return $isDate;
    }

    /** @return array<int,array{name:string,index:int,state:string}> */
    public function sheetNames(): array
    {
        $this->load();
        return array_map(fn($s) => ['name' => $s['name'], 'index' => $s['index'], 'state' => $s['state']], $this->sheets);
    }

    private function sheetPath(?string $sheetName): string
    {
        $this->load();
        if (!$this->sheets) throw new \RuntimeException('Berkas tidak memuat worksheet.');
        if ($sheetName === null || $sheetName === '') return $this->sheets[0]['path'];
        foreach ($this->sheets as $s) if ($s['name'] === $sheetName) return $s['path'];
        foreach ($this->sheets as $s) if (strcasecmp($s['name'], $sheetName) === 0) return $s['path'];
        throw new \RuntimeException('Worksheet "' . $sheetName . '" tidak ditemukan dalam berkas.');
    }

    /**
     * Membaca baris dari worksheet.
     *
     * @param string|null $sheetName
     * @param int         $maxRows   0 = tanpa batas
     * @param int         $startRow  baris awal (1-based)
     * @return array<int,array<string,array{v:mixed,f:?string,t:string,err:?string}>>
     *         Kunci luar = nomor baris; kunci dalam = huruf kolom.
     */
    public function rows(?string $sheetName = null, int $maxRows = 0, int $startRow = 1): array
    {
        if ($this->isCsv) return $this->csvRows($maxRows, $startRow);

        $path = $this->sheetPath($sheetName);
        $zip = new \ZipArchive();
        if ($zip->open($this->file) !== true) throw new \RuntimeException('Berkas Excel tidak dapat dibuka.');
        $stream = $zip->getStream($path);
        if (!$stream) { $zip->close(); throw new \RuntimeException('Worksheet tidak dapat dibaca.'); }
        $xml = stream_get_contents($stream);
        fclose($stream);
        $zip->close();

        $out = [];
        $this->sharedFormulas = [];
        $r = new \XMLReader();
        if (!@$r->XML((string)$xml, 'UTF-8', LIBXML_NOENT | LIBXML_NONET)) {
            throw new \RuntimeException('Struktur worksheet tidak dapat diproses.');
        }
        // Bila hanya sebagian baris dibaca, rumus bersama tetap perlu dipetakan lebih dulu
        if ($startRow > 1 || $maxRows > 0) {
            $this->prescanSharedFormulas((string)$xml);
        }
        $count = 0;
        // Maju ke elemen <row> pertama
        while ($r->read()) {
            if ($r->nodeType === \XMLReader::ELEMENT && $r->localName === 'row') break;
        }
        // Hanya next() di dalam loop (lihat catatan pada parseSharedStrings).
        while ($r->nodeType === \XMLReader::ELEMENT && $r->localName === 'row') {
            $rowNum = (int)($r->getAttribute('r') ?: 0);
            if ($rowNum > 0 && $rowNum < $startRow) {
                if (!$r->next('row')) break;
                continue;
            }
            $cells = $this->parseRow($r->readOuterXml(), $rowNum);
            if ($rowNum <= 0) $rowNum = count($out) + $startRow;
            $out[$rowNum] = $cells;
            $count++;
            if ($maxRows > 0 && $count >= $maxRows) break;
            if (!$r->next('row')) break;
        }
        $r->close();
        return $out;
    }

    private function parseRow(string $xmlRow, int $rowNum): array
    {
        $s = @simplexml_load_string($xmlRow);
        if ($s === false) return [];
        $cells = [];
        foreach ($s->c as $c) {
            $ref = (string)$c['r'];
            $col = preg_replace('/\d+/', '', $ref) ?: '';
            if ($col === '') continue;
            $type = (string)($c['t'] ?? 'n');
            $styleIdx = isset($c['s']) ? (int)$c['s'] : -1;
            $formula = $this->resolveFormula($c, $ref);
            $raw = isset($c->v) ? (string)$c->v : null;
            $err = null;
            $val = null;

            if ($type === 's') {
                $i = (int)$raw;
                $val = $this->sharedStrings[$i] ?? '';
            } elseif ($type === 'inlineStr') {
                $val = isset($c->is->t) ? (string)$c->is->t : $this->inlineRuns($c);
            } elseif ($type === 'str') {
                $val = $raw;
            } elseif ($type === 'e') {
                $err = $raw; $val = $raw;
            } elseif ($type === 'b') {
                $val = ((int)$raw) === 1;
            } else {
                if ($raw === null || $raw === '') { $val = null; }
                else {
                    $num = (float)$raw;
                    if ($styleIdx >= 0 && isset($this->dateStyles[$styleIdx]) && $num > 0 && $num < 500000) {
                        $d = Dates::fromExcelSerial($num);
                        $val = $d ?: $num;
                        if ($d) $type = 'd';
                    } else {
                        $val = (floor($num) == $num && abs($num) < PHP_INT_MAX) ? (int)$num : $num;
                    }
                }
            }
            $cells[$col] = ['v' => $val, 'f' => $formula, 't' => $type, 'err' => $err];
        }
        return $cells;
    }

    private function inlineRuns(\SimpleXMLElement $c): string
    {
        $t = '';
        if (isset($c->is->r)) foreach ($c->is->r as $run) $t .= (string)$run->t;
        return $t;
    }

    // ------------------------------------------------------------------
    // RUMUS BERSAMA (SHARED FORMULA)
    // ------------------------------------------------------------------
    // Excel menyimpan rumus yang diulang ke bawah hanya sekali:
    //   <f t="shared" ref="AD2:AD484" si="7">IF(AND(AB2>=46,AB2<=90),I2,0)</f>
    // lalu sel berikutnya hanya <f t="shared" si="7"/>.
    // Tanpa ekspansi, deteksi kerusakan rumus (DQ-01) hanya menemukan sel induk.

    /**
     * Mengembalikan teks rumus lengkap sebuah sel, termasuk hasil ekspansi
     * rumus bersama dengan penerjemahan referensi relatif.
     */
    private function resolveFormula(\SimpleXMLElement $c, string $ref): ?string
    {
        if (!isset($c->f)) return null;
        $f    = $c->f;
        $text = trim((string)$f);
        $ftype = (string)($f['t'] ?? '');
        $si    = isset($f['si']) ? (string)$f['si'] : null;

        [$row, $col] = self::refToRowCol($ref);

        if ($ftype === 'shared' && $si !== null) {
            if ($text !== '') {
                // Sel induk: simpan sebagai acuan
                $this->sharedFormulas[$si] = ['f' => $text, 'row' => $row, 'col' => $col];
                return '=' . $text;
            }
            $m = $this->sharedFormulas[$si] ?? null;
            if (!$m) return null;
            return '=' . self::translateFormula($m['f'], $row - $m['row'], $col - $m['col']);
        }
        return $text === '' ? null : '=' . $text;
    }

    /** Memindai seluruh worksheet untuk mengumpulkan rumus induk sebelum pembacaan sebagian. */
    private function prescanSharedFormulas(string $xml): void
    {
        if (!preg_match_all('/<c\s+r="([A-Z]+\d+)"[^>]*>\s*<f[^>]*t="shared"[^>]*si="(\d+)"[^>]*>([^<]+)<\/f>/i', $xml, $m, PREG_SET_ORDER)) {
            return;
        }
        foreach ($m as $g) {
            [$row, $col] = self::refToRowCol($g[1]);
            $this->sharedFormulas[$g[2]] = ['f' => html_entity_decode($g[3], ENT_QUOTES | ENT_HTML5, 'UTF-8'), 'row' => $row, 'col' => $col];
        }
    }

    /** @return array{0:int,1:int} [baris, indeks kolom] */
    public static function refToRowCol(string $ref): array
    {
        if (!preg_match('/^\$?([A-Z]+)\$?(\d+)$/i', $ref, $m)) return [0, 0];
        return [(int)$m[2], self::colIndex($m[1])];
    }

    /**
     * Menggeser referensi relatif dalam rumus sebesar (dRow, dCol).
     * Bagian di dalam tanda kutip ganda tidak diterjemahkan.
     */
    public static function translateFormula(string $f, int $dRow, int $dCol): string
    {
        if ($dRow === 0 && $dCol === 0) return $f;
        // Pisahkan literal string agar tidak ikut diterjemahkan
        $parts = preg_split('/("(?:[^"]|"")*")/', $f, -1, PREG_SPLIT_DELIM_CAPTURE) ?: [$f];
        foreach ($parts as $i => $part) {
            if ($part !== '' && $part[0] === '"') continue;
            $parts[$i] = preg_replace_callback(
                '/(?<![A-Za-z0-9_$!])(\$?)([A-Z]{1,3})(\$?)(\d{1,7})(?![0-9(])/',
                function (array $m) use ($dRow, $dCol): string {
                    $absCol = $m[1] === '$';
                    $absRow = $m[3] === '$';
                    $col = self::colIndex($m[2]);
                    $row = (int)$m[4];
                    if (!$absCol) $col += $dCol;
                    if (!$absRow) $row += $dRow;
                    if ($col < 1 || $row < 1) return '#REF!';
                    return ($absCol ? '$' : '') . self::colLetter($col) . ($absRow ? '$' : '') . $row;
                },
                $part
            ) ?? $part;
        }
        return implode('', $parts);
    }

    private function csvRows(int $maxRows, int $startRow): array
    {
        $out = [];
        $fh = fopen($this->file, 'r');
        if (!$fh) throw new \RuntimeException('Berkas CSV tidak dapat dibuka.');
        // Deteksi BOM & pemisah
        $first = fgets($fh);
        if ($first === false) { fclose($fh); return []; }
        $first = preg_replace('/^\xEF\xBB\xBF/', '', $first);
        $sep = ',';
        foreach ([';', "\t", '|'] as $cand) {
            if (substr_count($first, $cand) > substr_count($first, $sep)) $sep = $cand;
        }
        rewind($fh);
        $n = 0; $rowNum = 0;
        while (($line = fgetcsv($fh, 0, $sep)) !== false) {
            $rowNum++;
            if ($rowNum < $startRow) continue;
            $cells = [];
            foreach ($line as $i => $v) {
                $col = self::colLetter($i + 1);
                $v = is_string($v) ? preg_replace('/^\xEF\xBB\xBF/', '', $v) : $v;
                $cells[$col] = ['v' => ($v === '' ? null : $v), 'f' => null, 't' => 'str', 'err' => null];
            }
            $out[$rowNum] = $cells;
            $n++;
            if ($maxRows > 0 && $n >= $maxRows) break;
        }
        fclose($fh);
        return $out;
    }

    /** Header (baris pertama non-kosong) -> [kolom => judul]. */
    public function headers(?string $sheetName = null, int $headerRow = 1): array
    {
        $rows = $this->rows($sheetName, 5, 1);
        $target = $rows[$headerRow] ?? null;
        if (!$target) {
            foreach ($rows as $n => $cells) {
                $nonEmpty = array_filter($cells, fn($c) => $c['v'] !== null && trim((string)$c['v']) !== '');
                if (count($nonEmpty) >= 3) { $target = $cells; break; }
            }
        }
        if (!$target) return [];
        $out = [];
        foreach ($target as $col => $c) {
            $t = trim((string)($c['v'] ?? ''));
            if ($t !== '') $out[$col] = $t;
        }
        return $out;
    }

    /** Perkiraan jumlah baris berisi data. */
    public function countRows(?string $sheetName = null): int
    {
        $rows = $this->rows($sheetName, 0, 1);
        $last = 0;
        foreach ($rows as $n => $cells) {
            foreach ($cells as $c) {
                if ($c['v'] !== null && trim((string)$c['v']) !== '') { $last = $n; break; }
            }
        }
        return max(0, $last - 1);
    }

    public static function colLetter(int $n): string
    {
        $s = '';
        while ($n > 0) { $m = ($n - 1) % 26; $s = chr(65 + $m) . $s; $n = (int)(($n - $m - 1) / 26); }
        return $s ?: 'A';
    }

    public static function colIndex(string $letters): int
    {
        $n = 0;
        $letters = strtoupper(preg_replace('/[^A-Z]/i', '', $letters) ?? '');
        for ($i = 0, $len = strlen($letters); $i < $len; $i++) $n = $n * 26 + (ord($letters[$i]) - 64);
        return $n;
    }
}
