<?php

declare(strict_types=1);

namespace App\Services\Import;

use RuntimeException;
use ZipArchive;

final class SimpleXlsx
{
    /** @param array<int,array<int,string>> $rows */
    public static function output(string $filename, array $rows): void
    {
        if (!class_exists(ZipArchive::class)) {
            self::outputCsv(preg_replace('/\.xlsx$/i', '.csv', $filename) ?: 'template.csv', $rows);
        }
        $tmp = tempnam(sys_get_temp_dir(), 'xlsx_');
        if ($tmp === false) {
            throw new RuntimeException('تعذر إنشاء ملف مؤقت.');
        }
        @unlink($tmp);
        $zip = new ZipArchive();
        if ($zip->open($tmp, ZipArchive::CREATE) !== true) {
            throw new RuntimeException('ZipArchive غير متاح لإنشاء ملف Excel.');
        }

        $zip->addFromString('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8"?>
<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/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
</Types>');
        $zip->addFromString('_rels/.rels', '<?xml version="1.0" encoding="UTF-8"?>
<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"/></Relationships>');
        $zip->addFromString('xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8"?>
<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"/></Relationships>');
        $zip->addFromString('xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Template" sheetId="1" r:id="rId1"/></sheets></workbook>');

        $sheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>';
        foreach ($rows as $rIdx => $row) {
            $rowNo = $rIdx + 1;
            $sheet .= '<row r="' . $rowNo . '">';
            foreach ($row as $cIdx => $value) {
                $cellRef = self::colName($cIdx + 1) . $rowNo;
                $v = htmlspecialchars((string)$value, ENT_XML1 | ENT_COMPAT, 'UTF-8');
                $sheet .= '<c r="' . $cellRef . '" t="inlineStr"><is><t>' . $v . '</t></is></c>';
            }
            $sheet .= '</row>';
        }
        $sheet .= '</sheetData></worksheet>';
        $zip->addFromString('xl/worksheets/sheet1.xml', $sheet);
        $zip->close();

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . addslashes($filename) . '"');
        header('Content-Length: ' . filesize($tmp));
        readfile($tmp);
        @unlink($tmp);
        exit;
    }

    /** @return array<int,array<int,string>> */
    public static function read(string $path, string $originalName = ''): array
    {
        $extSource = $originalName !== '' ? $originalName : $path;
        $ext = strtolower(pathinfo($extSource, PATHINFO_EXTENSION));
        if ($ext === 'csv') {
            return self::readCsv($path);
        }

        if (!class_exists(ZipArchive::class)) {
            throw new RuntimeException('امتداد ZipArchive غير مفعل على السيرفر. استخدم CSV أو فعّل ext-zip لقراءة ملفات XLSX.');
        }
        $zip = new ZipArchive();
        if ($zip->open($path) !== true) {
            throw new RuntimeException('تعذر فتح ملف Excel.');
        }
        $shared = [];
        $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
        if (is_string($sharedXml) && $sharedXml !== '') {
            $sx = simplexml_load_string($sharedXml);
            if ($sx !== false) {
                foreach ($sx->si as $si) {
                    if (isset($si->t)) {
                        $shared[] = (string)$si->t;
                    } else {
                        $parts = [];
                        foreach ($si->r as $r) { $parts[] = (string)$r->t; }
                        $shared[] = implode('', $parts);
                    }
                }
            }
        }
        $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
        $zip->close();
        if (!is_string($sheetXml) || $sheetXml === '') {
            throw new RuntimeException('ملف Excel لا يحتوي على sheet1.');
        }
        $sx = simplexml_load_string($sheetXml);
        if ($sx === false) {
            throw new RuntimeException('تعذر قراءة بيانات Excel.');
        }
        $rows = [];
        foreach ($sx->sheetData->row as $row) {
            $rowValues = [];
            foreach ($row->c as $cell) {
                $ref = (string)($cell['r'] ?? '');
                $colIndex = self::colIndex(preg_replace('/\d+/', '', $ref) ?: 'A') - 1;
                while (count($rowValues) < $colIndex) { $rowValues[] = ''; }
                $type = (string)($cell['t'] ?? '');
                $value = '';
                if ($type === 's') {
                    $idx = (int)($cell->v ?? 0);
                    $value = $shared[$idx] ?? '';
                } elseif ($type === 'inlineStr') {
                    $value = (string)($cell->is->t ?? '');
                } else {
                    $value = (string)($cell->v ?? '');
                }
                $rowValues[$colIndex] = trim($value);
            }
            $rows[] = $rowValues;
        }
        return $rows;
    }

    /** @param array<int,array<int,string>> $rows */
    private static function outputCsv(string $filename, array $rows): void
    {
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . addslashes($filename) . '"');
        echo "\xEF\xBB\xBF";
        $out = fopen('php://output', 'wb');
        foreach ($rows as $row) {
            fputcsv($out, $row);
        }
        fclose($out);
        exit;
    }

    /** @return array<int,array<int,string>> */
    private static function readCsv(string $path): array
    {
        $rows = [];
        $handle = fopen($path, 'rb');
        if (!$handle) { return []; }
        $firstLine = fgets($handle);
        if ($firstLine === false) {
            fclose($handle);
            return [];
        }
        $delimiter = substr_count($firstLine, ';') > substr_count($firstLine, ',') ? ';' : ',';
        rewind($handle);
        while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
            $clean = array_map(static function ($v): string {
                $value = trim((string)$v);
                return preg_replace('/^\xEF\xBB\xBF/', '', $value) ?? $value;
            }, $row);
            $rows[] = $clean;
        }
        fclose($handle);
        return $rows;
    }

    private static function colName(int $num): string
    {
        $name = '';
        while ($num > 0) {
            $num--;
            $name = chr(65 + ($num % 26)) . $name;
            $num = intdiv($num, 26);
        }
        return $name;
    }

    private static function colIndex(string $letters): int
    {
        $n = 0;
        foreach (str_split(strtoupper($letters)) as $ch) {
            $n = $n * 26 + (ord($ch) - 64);
        }
        return max(1, $n);
    }
}
