<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\ZipWriter;

/**
 * XlsxExportService
 *
 * Generates a real .xlsx file (not the older HTML/XML "Excel will open
 * this" trick, which throws a format-mismatch warning in modern
 * Excel) using the minimum valid OOXML package: content types, root
 * relationships, one workbook with one sheet, and inline-string cells
 * (skipping a sharedStrings.xml part entirely — valid per the OOXML
 * spec and simpler for report-sized exports that don't need string
 * de-duplication). Verified independently against Python's openpyxl
 * during development, not just self-consistency.
 */
final class XlsxExportService
{
    /** @param array<int,array<string,mixed>> $rows @param array<string,string> $columns machine_key => Human Label */
    public function toXlsx(array $rows, array $columns, string $sheetName = 'Report'): string
    {
        $sheetName = substr(preg_replace('/[\\\\\/?*\[\]:]/', ' ', $sheetName) ?? 'Report', 0, 31) ?: 'Report';

        $zip = new ZipWriter();
        $zip->addFile('[Content_Types].xml', $this->contentTypesXml());
        $zip->addFile('_rels/.rels', $this->rootRelsXml());
        $zip->addFile('xl/workbook.xml', $this->workbookXml($sheetName));
        $zip->addFile('xl/_rels/workbook.xml.rels', $this->workbookRelsXml());
        $zip->addFile('xl/styles.xml', $this->stylesXml());
        $zip->addFile('xl/worksheets/sheet1.xml', $this->sheetXml($rows, $columns));

        return $zip->toBinary();
    }

    private function contentTypesXml(): 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="/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"/>'
            . '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
            . '</Types>';
    }

    private function rootRelsXml(): 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"/>'
            . '</Relationships>';
    }

    private function workbookXml(string $sheetName): string
    {
        $escaped = htmlspecialchars($sheetName, ENT_XML1 | ENT_QUOTES, 'UTF-8');

        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="' . $escaped . '" sheetId="1" r:id="rId1"/></sheets>'
            . '</workbook>';
    }

    private function workbookRelsXml(): 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/styles" Target="styles.xml"/>'
            . '</Relationships>';
    }

    /** Two cell formats: 0 = normal, 1 = bold header (white text, brand-blue fill). */
    private function stylesXml(): 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><sz val="11"/><name val="Calibri"/><b/><color rgb="FFFFFFFF"/></font></fonts>'
            . '<fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF174890"/><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 sheetXml(array $rows, array $columns): string
    {
        $colLetters = $this->columnLetters(count($columns));
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<cols>' . implode('', array_map(fn ($l) => "<col min=\"" . ($this->colIndex($l) + 1) . "\" max=\"" . ($this->colIndex($l) + 1) . "\" width=\"22\" customWidth=\"1\"/>", $colLetters)) . '</cols>'
            . '<sheetData>';

        $xml .= '<row r="1">';
        $c = 0;
        foreach ($columns as $label) {
            $xml .= $this->cellXml($colLetters[$c] . '1', (string) $label, 1);
            $c++;
        }
        $xml .= '</row>';

        $rowNum = 2;
        foreach ($rows as $row) {
            $xml .= '<row r="' . $rowNum . '">';
            $c = 0;
            foreach (array_keys($columns) as $key) {
                $xml .= $this->cellXml($colLetters[$c] . $rowNum, (string) ($row[$key] ?? ''), 0);
                $c++;
            }
            $xml .= '</row>';
            $rowNum++;
        }

        $xml .= '</sheetData></worksheet>';

        return $xml;
    }

    private function cellXml(string $ref, string $value, int $styleIndex): string
    {
        $escaped = htmlspecialchars($value, ENT_XML1 | ENT_QUOTES, 'UTF-8');
        $styleAttr = $styleIndex > 0 ? " s=\"{$styleIndex}\"" : '';

        if ($value !== '' && is_numeric($value) && !preg_match('/^0\d/', $value)) {
            return "<c r=\"{$ref}\"{$styleAttr}><v>{$escaped}</v></c>";
        }

        return "<c r=\"{$ref}\" t=\"inlineStr\"{$styleAttr}><is><t xml:space=\"preserve\">{$escaped}</t></is></c>";
    }

    /** @return array<int,string> Excel column letters A, B, ..., Z, AA, AB, ... for $count columns. */
    private function columnLetters(int $count): array
    {
        $letters = [];
        for ($i = 0; $i < $count; $i++) {
            $n = $i;
            $letter = '';
            do {
                $letter = chr(65 + ($n % 26)) . $letter;
                $n = intdiv($n, 26) - 1;
            } while ($n >= 0);
            $letters[] = $letter;
        }

        return $letters;
    }

    private function colIndex(string $letter): int
    {
        $index = 0;
        foreach (str_split($letter) as $char) {
            $index = $index * 26 + (ord($char) - 64);
        }

        return $index - 1;
    }
}
