<?php

namespace App\Libraries;

use RuntimeException;
use SimpleXMLElement;
use ZipArchive;

class BarangImportXlsx
{
    public const HEADERS = [
        'kode_barang'      => 'Kode Barang',
        'barcode'          => 'Barcode',
        'nama_barang'      => 'Nama Barang',
        'kategori'         => 'Kategori',
        'satuan'           => 'Satuan',
        'stok_awal'        => 'Stok Awal',
        'stok_minimum'     => 'Stok Minimum',
        'lokasi_barang'    => 'Lokasi Barang',
        'harga_beli'       => 'Harga Beli',
        'harga_jual'       => 'Harga Jual',
        'deskripsi_barang' => 'Deskripsi Barang',
    ];

    public function template(array $kategori): string
    {
        $filePath = tempnam(sys_get_temp_dir(), 'barang-template-');

        if ($filePath === false) {
            throw new RuntimeException('Gagal membuat file template sementara.');
        }

        $zip = new ZipArchive();

        if ($zip->open($filePath, ZipArchive::OVERWRITE) !== true) {
            throw new RuntimeException('Gagal membuat template XLSX.');
        }

        $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->workbook());
        $zip->addFromString('xl/_rels/workbook.xml.rels', $this->workbookRels());
        $zip->addFromString('xl/styles.xml', $this->styles());
        $zip->addFromString('xl/worksheets/sheet1.xml', $this->barangSheet($kategori));
        $zip->addFromString('xl/worksheets/sheet2.xml', $this->kategoriSheet($kategori));
        $zip->close();

        $content = file_get_contents($filePath);
        unlink($filePath);

        if ($content === false) {
            throw new RuntimeException('Gagal membaca template XLSX.');
        }

        return $content;
    }

    public function read(string $filePath): array
    {
        $zip = new ZipArchive();

        if ($zip->open($filePath) !== true) {
            throw new RuntimeException('File XLSX tidak dapat dibuka.');
        }

        $sharedStrings = $this->readSharedStrings($zip);
        $sheetPath = $this->firstSheetPath($zip);
        $sheetXml = $zip->getFromName($sheetPath);
        $zip->close();

        if ($sheetXml === false) {
            throw new RuntimeException('Sheet data barang tidak ditemukan.');
        }

        $xml = simplexml_load_string($sheetXml);

        if (! $xml instanceof SimpleXMLElement) {
            throw new RuntimeException('Sheet data barang tidak valid.');
        }

        $rows = [];
        $headerMap = [];

        foreach ($xml->sheetData->row as $row) {
            $rowIndex = (int) $row['r'];
            $cells = [];

            foreach ($row->c as $cell) {
                $reference = (string) $cell['r'];
                $column = preg_replace('/\d+/', '', $reference);
                $cells[$this->columnIndex($column)] = trim($this->cellValue($cell, $sharedStrings));
            }

            if ($rowIndex === 1) {
                foreach ($cells as $index => $header) {
                    $key = $this->headerKey($header);

                    if ($key !== null) {
                        $headerMap[$index] = $key;
                    }
                }

                continue;
            }

            $data = [];

            foreach ($headerMap as $index => $key) {
                $data[$key] = $cells[$index] ?? '';
            }

            if (implode('', array_map('trim', $data)) === '') {
                continue;
            }

            $data['_row'] = $rowIndex;
            $rows[] = $data;
        }

        if ($headerMap === []) {
            throw new RuntimeException('Header template tidak sesuai. Download ulang format import barang.');
        }

        return $rows;
    }

    private function readSharedStrings(ZipArchive $zip): array
    {
        $xmlContent = $zip->getFromName('xl/sharedStrings.xml');

        if ($xmlContent === false) {
            return [];
        }

        $xml = simplexml_load_string($xmlContent);

        if (! $xml instanceof SimpleXMLElement) {
            return [];
        }

        $strings = [];

        foreach ($xml->si as $item) {
            if (isset($item->t)) {
                $strings[] = (string) $item->t;
                continue;
            }

            $text = '';

            foreach ($item->r as $run) {
                $text .= (string) $run->t;
            }

            $strings[] = $text;
        }

        return $strings;
    }

    private function firstSheetPath(ZipArchive $zip): string
    {
        $workbook = $zip->getFromName('xl/workbook.xml');
        $rels = $zip->getFromName('xl/_rels/workbook.xml.rels');

        if ($workbook === false || $rels === false) {
            return 'xl/worksheets/sheet1.xml';
        }

        $workbookXml = simplexml_load_string($workbook);
        $relsXml = simplexml_load_string($rels);

        if (! $workbookXml instanceof SimpleXMLElement || ! $relsXml instanceof SimpleXMLElement) {
            return 'xl/worksheets/sheet1.xml';
        }

        $workbookXml->registerXPathNamespace('main', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $workbookXml->registerXPathNamespace('r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
        $sheets = $workbookXml->xpath('//main:sheets/main:sheet');

        if (! $sheets || ! isset($sheets[0])) {
            return 'xl/worksheets/sheet1.xml';
        }

        $attributes = $sheets[0]->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
        $relationshipId = (string) $attributes['id'];

        foreach ($relsXml->Relationship as $relationship) {
            if ((string) $relationship['Id'] === $relationshipId) {
                $target = (string) $relationship['Target'];

                return str_starts_with($target, '/')
                    ? ltrim($target, '/')
                    : 'xl/' . ltrim($target, '/');
            }
        }

        return 'xl/worksheets/sheet1.xml';
    }

    private function cellValue(SimpleXMLElement $cell, array $sharedStrings): string
    {
        $type = (string) $cell['t'];

        if ($type === 's') {
            $index = (int) $cell->v;

            return $sharedStrings[$index] ?? '';
        }

        if ($type === 'inlineStr') {
            return (string) $cell->is->t;
        }

        return isset($cell->v) ? (string) $cell->v : '';
    }

    private function headerKey(string $header): ?string
    {
        $normalized = strtolower(trim($header));

        foreach (self::HEADERS as $key => $label) {
            if ($normalized === strtolower($label)) {
                return $key;
            }
        }

        return null;
    }

    private function columnIndex(string $letters): int
    {
        $index = 0;

        foreach (str_split($letters) as $letter) {
            $index = ($index * 26) + (ord(strtoupper($letter)) - 64);
        }

        return $index;
    }

    private function barangSheet(array $kategori): string
    {
        $headers = array_values(self::HEADERS);
        $headerCells = '';

        foreach ($headers as $index => $header) {
            $column = $this->columnName($index + 1);
            $headerCells .= $this->inlineCell($column . '1', $header, 1);
        }

        $sample = [
            'BRGCONTOH001',
            '899000000001',
            'Keyboard Logitech K120',
            $kategori[0]['nama_kategori'] ?? '',
            'unit',
            '10',
            '2',
            'Rak A1',
            '85000',
            '100000',
            'Contoh data barang. Ubah atau hapus baris ini sebelum import.',
        ];
        $sampleCells = '';

        foreach ($sample as $index => $value) {
            $column = $this->columnName($index + 1);
            $sampleCells .= $this->inlineCell($column . '2', $value);
        }

        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
            . '<dimension ref="A1:K200"/>'
            . '<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
            . '<cols>'
            . '<col min="1" max="1" width="18" customWidth="1"/>'
            . '<col min="2" max="2" width="20" customWidth="1"/>'
            . '<col min="3" max="3" width="32" customWidth="1"/>'
            . '<col min="4" max="4" width="28" customWidth="1"/>'
            . '<col min="5" max="7" width="16" customWidth="1"/>'
            . '<col min="8" max="8" width="24" customWidth="1"/>'
            . '<col min="9" max="10" width="16" customWidth="1"/>'
            . '<col min="11" max="11" width="34" customWidth="1"/>'
            . '</cols>'
            . '<sheetData><row r="1">' . $headerCells . '</row><row r="2">' . $sampleCells . '</row></sheetData>'
            . '</worksheet>';
    }

    private function kategoriSheet(array $kategori): string
    {
        $rows = '<row r="1">' . $this->inlineCell('A1', 'Daftar Kategori', 1) . '</row>';

        foreach (array_values($kategori) as $index => $item) {
            $row = $index + 2;
            $rows .= '<row r="' . $row . '">' . $this->inlineCell('A' . $row, $item['nama_kategori'] ?? '') . '</row>';
        }

        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<dimension ref="A1:A' . (count($kategori) + 1) . '"/>'
            . '<cols><col min="1" max="1" width="36" customWidth="1"/></cols>'
            . '<sheetData>' . $rows . '</sheetData>'
            . '</worksheet>';
    }

    private function inlineCell(string $reference, string $value, int $style = 0): string
    {
        return '<c r="' . $reference . '" t="inlineStr" s="' . $style . '"><is><t>' . $this->xml($value) . '</t></is></c>';
    }

    private function columnName(int $index): string
    {
        $name = '';

        while ($index > 0) {
            $index--;
            $name = chr(65 + ($index % 26)) . $name;
            $index = intdiv($index, 26);
        }

        return $name;
    }

    private function contentTypes(): string
    {
        return '<?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="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>'
            . '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+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/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            . '<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            . '</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 workbook(): string
    {
        return '<?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>'
            . '<sheet name="Barang" sheetId="1" r:id="rId1"/>'
            . '<sheet name="Kategori" sheetId="2" r:id="rId2"/>'
            . '</sheets>'
            . '</workbook>';
    }

    private function workbookRels(): 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/worksheet" Target="worksheets/sheet1.xml"/>'
            . '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>'
            . '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
            . '</Relationships>';
    }

    private function styles(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<fonts count="2"><font><sz val="11"/><name val="Calibri"/></font><font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font></fonts>'
            . '<fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FF111827"/><bgColor indexed="64"/></patternFill></fill></fills>'
            . '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>'
            . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
            . '<cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/></cellXfs>'
            . '</styleSheet>';
    }

    private function appProps(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>Nusa POS</Application></Properties>';
    }

    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:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
            . '<dc:title>Format Import Barang</dc:title><dc:creator>Nusa POS</dc:creator>'
            . '<dcterms:created xsi:type="dcterms:W3CDTF">' . date('c') . '</dcterms:created>'
            . '</cp:coreProperties>';
    }

    private function xml(string $value): string
    {
        return htmlspecialchars($value, ENT_XML1 | ENT_COMPAT, 'UTF-8');
    }
}
