<?php
namespace App\Services;

use App\Core\Dates;
use App\Core\Settings;

/**
 * Penulis berkas XLSX tanpa dependensi eksternal (ZipArchive + XML).
 *
 * Fitur yang dipakai laporan:
 *  - header tebal berlatar warna + freeze pane + autofilter
 *  - format mata uang Indonesia (Rp #.##0) dan tanggal DD-MM-YYYY
 *  - kolom teks dipaksa bertipe string (nomor faktur pajak tidak menjadi
 *    notasi ilmiah dan nol di depan tetap utuh)
 *  - baris total dengan gaya tersendiri
 *  - lebar kolom otomatis
 */
class ExcelWriter
{
    private array $sheets = [];
    private array $sharedStrings = [];
    private array $sharedIndex = [];
    private string $title = 'Laporan';

    /** Indeks style yang dipakai (harus selaras dengan styles.xml di bawah). */
    private const S_DEFAULT = 0;
    private const S_HEADER  = 1;
    private const S_TEXT    = 2;
    private const S_MONEY   = 3;
    private const S_DATE    = 4;
    private const S_INT     = 5;
    private const S_TOTAL_T = 6;
    private const S_TOTAL_M = 7;
    private const S_TITLE   = 8;
    private const S_SUB     = 9;
    private const S_DEC     = 10;
    private const S_PCT     = 11;
    private const S_TOTAL_I = 12;
    private const S_CENTER  = 13;

    public function setTitle(string $t): void { $this->title = $t; }

    /**
     * Menambahkan worksheet.
     *
     * @param string $name    nama sheet (maks 31 karakter, karakter ilegal dibersihkan)
     * @param array  $columns [['label'=>..,'key'=>..,'type'=>'text|money|date|int|dec|pct','width'=>int], ...]
     * @param array  $rows    array asosiatif atau array numerik sesuai urutan kolom
     * @param array  $opts    ['judul'=>string, 'subjudul'=>array<string>, 'total'=>array, 'autofilter'=>bool, 'freeze'=>bool]
     */
    public function addSheet(string $name, array $columns, array $rows, array $opts = []): void
    {
        $this->sheets[] = [
            'name'    => $this->cleanSheetName($name),
            'columns' => $columns,
            'rows'    => $rows,
            'opts'    => $opts,
        ];
    }

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

    private function si(string $s): int
    {
        $k = md5($s);
        if (isset($this->sharedIndex[$k])) return $this->sharedIndex[$k];
        $i = count($this->sharedStrings);
        $this->sharedStrings[] = $s;
        $this->sharedIndex[$k] = $i;
        return $i;
    }

    /** Menulis berkas XLSX ke path tujuan. */
    public function save(string $path): string
    {
        if (!class_exists('\ZipArchive')) {
            throw new \RuntimeException('Ekstensi PHP "zip" tidak tersedia. Gunakan export CSV sebagai alternatif.');
        }
        $dir = dirname($path);
        if (!is_dir($dir)) @mkdir($dir, 0775, true);
        @unlink($path);

        $zip = new \ZipArchive();
        if ($zip->open($path, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
            throw new \RuntimeException('Berkas Excel tidak dapat dibuat di ' . $dir);
        }

        // Bangun XML worksheet lebih dulu agar shared strings terkumpul
        $sheetXml = [];
        foreach ($this->sheets as $i => $s) $sheetXml[$i] = $this->buildSheetXml($s);

        $zip->addFromString('[Content_Types].xml', $this->contentTypes());
        $zip->addFromString('_rels/.rels', $this->rootRels());
        $zip->addFromString('docProps/app.xml', $this->appProps());
        $zip->addFromString('docProps/core.xml', $this->coreProps());
        $zip->addFromString('xl/workbook.xml', $this->workbookXml());
        $zip->addFromString('xl/_rels/workbook.xml.rels', $this->workbookRels());
        $zip->addFromString('xl/styles.xml', $this->stylesXml());
        $zip->addFromString('xl/sharedStrings.xml', $this->sharedStringsXml());
        foreach ($sheetXml as $i => $xml) $zip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $xml);
        $zip->close();
        return $path;
    }

    public function output(): string
    {
        $tmp = rtrim((string)(\App\Core\Config::get('paths.tmp') ?: sys_get_temp_dir()), '/') . '/xlsx_' . bin2hex(random_bytes(6)) . '.xlsx';
        $this->save($tmp);
        $data = (string)file_get_contents($tmp);
        @unlink($tmp);
        return $data;
    }

    // ------------------------------------------------------------------
    private function buildSheetXml(array $s): string
    {
        $cols = $s['columns'];
        $opts = $s['opts'];
        $nCols = count($cols);
        $lastCol = ExcelReader::colLetter(max(1, $nCols));

        $rowsXml = '';
        $rowNum = 0;

        // Judul & subjudul
        if (!empty($opts['judul'])) {
            $rowNum++;
            $rowsXml .= '<row r="' . $rowNum . '" ht="20" customHeight="1">'
                      . $this->cellStr('A' . $rowNum, (string)$opts['judul'], self::S_TITLE) . '</row>';
        }
        foreach ((array)($opts['subjudul'] ?? []) as $sub) {
            $rowNum++;
            $rowsXml .= '<row r="' . $rowNum . '">' . $this->cellStr('A' . $rowNum, (string)$sub, self::S_SUB) . '</row>';
        }
        if ($rowNum > 0) { $rowNum++; } // baris kosong pemisah

        // Header
        $rowNum++;
        $headerRow = $rowNum;
        $rowsXml .= '<row r="' . $rowNum . '" ht="26" customHeight="1">';
        foreach ($cols as $i => $c) {
            $ref = ExcelReader::colLetter($i + 1) . $rowNum;
            $rowsXml .= $this->cellStr($ref, (string)($c['label'] ?? ''), self::S_HEADER);
        }
        $rowsXml .= '</row>';

        // Data
        foreach ($s['rows'] as $r) {
            $rowNum++;
            $rowsXml .= '<row r="' . $rowNum . '">';
            foreach ($cols as $i => $c) {
                $key = $c['key'] ?? $i;
                $val = is_array($r) ? ($r[$key] ?? null) : null;
                $rowsXml .= $this->cellAuto(ExcelReader::colLetter($i + 1) . $rowNum, $val, (string)($c['type'] ?? 'text'));
            }
            $rowsXml .= '</row>';
        }

        // Baris total
        if (!empty($opts['total']) && is_array($opts['total'])) {
            $rowNum++;
            $rowsXml .= '<row r="' . $rowNum . '">';
            foreach ($cols as $i => $c) {
                $key = $c['key'] ?? $i;
                $ref = ExcelReader::colLetter($i + 1) . $rowNum;
                $type = (string)($c['type'] ?? 'text');
                if (array_key_exists($key, $opts['total'])) {
                    $v = $opts['total'][$key];
                    if (in_array($type, ['money', 'dec', 'pct'], true)) {
                        $rowsXml .= $this->cellNum($ref, (float)$v, self::S_TOTAL_M);
                    } elseif ($type === 'int') {
                        $rowsXml .= $this->cellNum($ref, (float)$v, self::S_TOTAL_I);
                    } else {
                        $rowsXml .= $this->cellStr($ref, (string)$v, self::S_TOTAL_T);
                    }
                } else {
                    $rowsXml .= $this->cellStr($ref, $i === 0 ? 'TOTAL' : '', self::S_TOTAL_T);
                }
            }
            $rowsXml .= '</row>';
        }

        // Lebar kolom
        $colsXml = '<cols>';
        foreach ($cols as $i => $c) {
            $w = (int)($c['width'] ?? 0);
            if ($w <= 0) {
                $w = match ((string)($c['type'] ?? 'text')) {
                    'money' => 18, 'date' => 13, 'int' => 10, 'dec' => 13, 'pct' => 11, default => 24,
                };
            }
            $colsXml .= '<col min="' . ($i + 1) . '" max="' . ($i + 1) . '" width="' . $w . '" customWidth="1"/>';
        }
        $colsXml .= '</cols>';

        $freeze = '';
        if (($opts['freeze'] ?? true) && $nCols > 0) {
            $topLeft = 'A' . ($headerRow + 1);
            $freeze = '<sheetViews><sheetView workbookViewId="0" showGridLines="1">'
                    . '<pane ySplit="' . $headerRow . '" topLeftCell="' . $topLeft . '" activePane="bottomLeft" state="frozen"/>'
                    . '</sheetView></sheetViews>';
        } else {
            $freeze = '<sheetViews><sheetView workbookViewId="0"/></sheetViews>';
        }

        $autofilter = '';
        if (($opts['autofilter'] ?? true) && $nCols > 0 && count($s['rows']) > 0) {
            $lastDataRow = $headerRow + count($s['rows']);
            $autofilter = '<autoFilter ref="A' . $headerRow . ':' . $lastCol . $lastDataRow . '"/>';
        }

        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
             . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
             . $freeze . $colsXml
             . '<sheetData>' . $rowsXml . '</sheetData>'
             . $autofilter
             . '<pageMargins left="0.4" right="0.4" top="0.6" bottom="0.6" header="0.3" footer="0.3"/>'
             . '</worksheet>';
    }

    private function cellStr(string $ref, string $v, int $style = self::S_TEXT): string
    {
        if ($v === '') return '<c r="' . $ref . '" s="' . $style . '"/>';
        return '<c r="' . $ref . '" s="' . $style . '" t="s"><v>' . $this->si($v) . '</v></c>';
    }

    private function cellNum(string $ref, float $v, int $style): string
    {
        return '<c r="' . $ref . '" s="' . $style . '"><v>' . self::numStr($v) . '</v></c>';
    }

    private function cellAuto(string $ref, $v, string $type): string
    {
        if ($v === null || $v === '') {
            $s = match ($type) { 'money' => self::S_MONEY, 'date' => self::S_DATE, 'int' => self::S_INT,
                                 'dec' => self::S_DEC, 'pct' => self::S_PCT, default => self::S_TEXT };
            return '<c r="' . $ref . '" s="' . $s . '"/>';
        }
        switch ($type) {
            case 'money':
                return $this->cellNum($ref, (float)$v, self::S_MONEY);
            case 'int':
                return $this->cellNum($ref, (float)$v, self::S_INT);
            case 'dec':
                return $this->cellNum($ref, (float)$v, self::S_DEC);
            case 'pct':
                return $this->cellNum($ref, (float)$v / 100, self::S_PCT);
            case 'date':
                $serial = Dates::toExcelSerial(substr((string)$v, 0, 10));
                if ($serial === null) return $this->cellStr($ref, (string)$v, self::S_TEXT);
                return $this->cellNum($ref, $serial, self::S_DATE);
            case 'center':
                return $this->cellStr($ref, (string)$v, self::S_CENTER);
            default:
                // Selalu sebagai teks: menjaga nol di depan & mencegah notasi ilmiah
                return $this->cellStr($ref, (string)$v, self::S_TEXT);
        }
    }

    private static function numStr(float $v): string
    {
        if ($v == (int)$v && abs($v) < 1e15) return (string)(int)$v;
        return rtrim(rtrim(number_format($v, 6, '.', ''), '0'), '.');
    }

    private static function esc(string $s): string
    {
        $s = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F]/u', '', $s) ?? $s;
        return htmlspecialchars($s, ENT_QUOTES | ENT_XML1, 'UTF-8');
    }

    // ------------------------------------------------------------------ bagian tetap
    private function contentTypes(): string
    {
        $x = '<?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"/>'
           . '<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>'
           . '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>'
           . '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
        foreach ($this->sheets as $i => $s) {
            $x .= '<Override PartName="/xl/worksheets/sheet' . ($i + 1) . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
        }
        return $x . '</Types>';
    }

    private function rootRels(): string
    {
        return '<?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"/>'
             . '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>'
             . '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>'
             . '</Relationships>';
    }

    private function workbookXml(): string
    {
        $x = '<?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) {
            $x .= '<sheet name="' . self::esc($s['name']) . '" sheetId="' . ($i + 1) . '" r:id="rId' . ($i + 1) . '"/>';
        }
        return $x . '</sheets></workbook>';
    }

    private function workbookRels(): string
    {
        $x = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
           . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
        $n = count($this->sheets);
        foreach ($this->sheets as $i => $s) {
            $x .= '<Relationship Id="rId' . ($i + 1) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . ($i + 1) . '.xml"/>';
        }
        $x .= '<Relationship Id="rId' . ($n + 1) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>';
        $x .= '<Relationship Id="rId' . ($n + 2) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>';
        return $x . '</Relationships>';
    }

    private function sharedStringsXml(): string
    {
        $x = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
           . '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="'
           . count($this->sharedStrings) . '" uniqueCount="' . count($this->sharedStrings) . '">';
        foreach ($this->sharedStrings as $s) {
            $x .= '<si><t xml:space="preserve">' . self::esc($s) . '</t></si>';
        }
        return $x . '</sst>';
    }

    private function stylesXml(): string
    {
        $sym = Settings::str('umum.simbol_mata_uang', 'Rp');
        // Format Indonesia: pemisah ribuan titik, desimal koma -> dicapai lewat
        // format kode standar; aplikasi Excel akan mengikuti locale sistem pengguna.
        $money = '&quot;' . self::esc($sym) . '&quot;\ #,##0;[Red]\(&quot;' . self::esc($sym) . '&quot;\ #,##0\)';
        $dec   = '#,##0.00;[Red]\(#,##0.00\)';
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        . '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        . '<numFmts count="3">'
        .   '<numFmt numFmtId="164" formatCode="' . $money . '"/>'
        .   '<numFmt numFmtId="165" formatCode="DD\-MM\-YYYY"/>'
        .   '<numFmt numFmtId="166" formatCode="' . $dec . '"/>'
        . '</numFmts>'
        . '<fonts count="6">'
        .   '<font><sz val="10"/><name val="Calibri"/></font>'
        .   '<font><b/><sz val="10"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>'
        .   '<font><b/><sz val="10"/><name val="Calibri"/></font>'
        .   '<font><b/><sz val="14"/><color rgb="FF1F3864"/><name val="Calibri"/></font>'
        .   '<font><sz val="9"/><color rgb="FF595959"/><name val="Calibri"/></font>'
        .   '<font><b/><sz val="10"/><color rgb="FF1F3864"/><name val="Calibri"/></font>'
        . '</fonts>'
        . '<fills count="4">'
        .   '<fill><patternFill patternType="none"/></fill>'
        .   '<fill><patternFill patternType="gray125"/></fill>'
        .   '<fill><patternFill patternType="solid"><fgColor rgb="FF1F3864"/><bgColor indexed="64"/></patternFill></fill>'
        .   '<fill><patternFill patternType="solid"><fgColor rgb="FFDCE6F1"/><bgColor indexed="64"/></patternFill></fill>'
        . '</fills>'
        . '<borders count="3">'
        .   '<border><left/><right/><top/><bottom/><diagonal/></border>'
        .   '<border><left style="thin"><color rgb="FFBFBFBF"/></left><right style="thin"><color rgb="FFBFBFBF"/></right>'
        .     '<top style="thin"><color rgb="FFBFBFBF"/></top><bottom style="thin"><color rgb="FFBFBFBF"/></bottom><diagonal/></border>'
        .   '<border><left style="thin"><color rgb="FF1F3864"/></left><right style="thin"><color rgb="FF1F3864"/></right>'
        .     '<top style="double"><color rgb="FF1F3864"/></top><bottom style="thin"><color rgb="FF1F3864"/></bottom><diagonal/></border>'
        . '</borders>'
        . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
        . '<cellXfs count="14">'
        .   '<xf numFmtId="0"   fontId="0" fillId="0" borderId="0" xfId="0"/>'                                                                    // 0 default
        .   '<xf numFmtId="0"   fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>' // 1 header
        .   '<xf numFmtId="49"  fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment vertical="center"/></xf>' // 2 teks
        .   '<xf numFmtId="164" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'                               // 3 uang
        .   '<xf numFmtId="165" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center"/></xf>' // 4 tanggal
        .   '<xf numFmtId="3"   fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'                               // 5 bilangan bulat
        .   '<xf numFmtId="0"   fontId="2" fillId="3" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/>'                         // 6 total teks
        .   '<xf numFmtId="164" fontId="2" fillId="3" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1"/>'   // 7 total uang
        .   '<xf numFmtId="0"   fontId="3" fillId="0" borderId="0" xfId="0" applyFont="1"/>'                                                       // 8 judul
        .   '<xf numFmtId="0"   fontId="4" fillId="0" borderId="0" xfId="0" applyFont="1"/>'                                                       // 9 subjudul
        .   '<xf numFmtId="166" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'                               // 10 desimal
        .   '<xf numFmtId="10"  fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"/>'                               // 11 persen
        .   '<xf numFmtId="3"   fontId="2" fillId="3" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1"/>'   // 12 total bulat
        .   '<xf numFmtId="49"  fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center"/></xf>' // 13 teks tengah
        . '</cellXfs>'
        . '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
        . '</styleSheet>';
    }

    private function coreProps(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
             . '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties"'
             . ' xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/"'
             . ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
             . '<dc:title>' . self::esc($this->title) . '</dc:title>'
             . '<dc:creator>' . self::esc(Settings::str('umum.nama_aplikasi', 'SIPIUTANG')) . '</dc:creator>'
             . '<cp:lastModifiedBy>' . self::esc(Settings::str('umum.nama_perusahaan', '')) . '</cp:lastModifiedBy>'
             . '<dcterms:created xsi:type="dcterms:W3CDTF">' . date('c') . '</dcterms:created>'
             . '<dcterms:modified xsi:type="dcterms:W3CDTF">' . date('c') . '</dcterms:modified>'
             . '</cp:coreProperties>';
    }

    private function appProps(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
             . '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">'
             . '<Application>SIPIUTANG</Application><Company>'
             . self::esc(Settings::str('umum.nama_perusahaan', '')) . '</Company></Properties>';
    }
}
