<?php
namespace App\Services;

/**
 * Writes a genuine single-sheet .xlsx file (not a renamed CSV) using PHP's
 * built-in ZipArchive — an XLSX is just a zip of a handful of small XML
 * parts, so no library is needed. Falls back to null (caller serves CSV
 * instead) on the rare host without ext-zip enabled.
 */
class SimpleXlsx
{
    public static function isAvailable(): bool
    {
        return class_exists(\ZipArchive::class);
    }

    /**
     * @param array<int,string> $headers
     * @param array<int,array<int,mixed>> $rows
     * @return string|null raw .xlsx bytes, or null if ext-zip is unavailable
     */
    public static function build(array $headers, array $rows, string $sheetName = 'Report'): ?string
    {
        if (!self::isAvailable()) return null;

        $tmpPath = tempnam(sys_get_temp_dir(), 'xlsx');
        $zip = new \ZipArchive();
        $zip->open($tmpPath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);

        $zip->addEmptyDir('_rels');
        $zip->addEmptyDir('xl');
        $zip->addEmptyDir('xl/_rels');
        $zip->addEmptyDir('xl/worksheets');

        $zip->addFromString('[Content_Types].xml',
            '<?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>');

        $zip->addFromString('_rels/.rels',
            '<?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>');

        $zip->addFromString('xl/_rels/workbook.xml.rels',
            '<?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>');

        $zip->addFromString('xl/styles.xml',
            '<?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"/><name val="Calibri"/></font></fonts>' .
            '<fills count="1"><fill><patternFill patternType="none"/></fill></fills>' .
            '<borders count="1"><border/></borders>' .
            '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0"/></cellStyleXfs>' .
            '<cellXfs count="2"><xf numFmtId="0" fontId="0" xfId="0"/><xf numFmtId="0" fontId="1" xfId="0" applyFont="1"/></cellXfs>' .
            '</styleSheet>');

        $zip->addFromString('xl/workbook.xml',
            '<?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="' . self::escapeXml(mb_substr($sheetName, 0, 31)) . '" sheetId="1" r:id="rId1"/></sheets>' .
            '</workbook>');

        $zip->addFromString('xl/worksheets/sheet1.xml', self::buildSheet($headers, $rows));

        $zip->close();
        $bytes = file_get_contents($tmpPath);
        unlink($tmpPath);
        return $bytes === false ? null : $bytes;
    }

    private static function buildSheet(array $headers, array $rows): string
    {
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
        $xml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>';

        $xml .= self::buildRow(1, $headers, true);
        foreach ($rows as $i => $row) {
            $xml .= self::buildRow($i + 2, $row, false);
        }

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

    private static function buildRow(int $rowNum, array $cells, bool $bold): string
    {
        $xml = "<row r=\"{$rowNum}\">";
        foreach (array_values($cells) as $colIdx => $value) {
            $ref = self::colLetter($colIdx) . $rowNum;
            if (is_numeric($value) && $value !== '' && !preg_match('/^0[0-9]/', (string)$value)) {
                $xml .= "<c r=\"{$ref}\"><v>" . self::escapeXml((string)$value) . '</v></c>';
            } else {
                $text = self::escapeXml((string)$value);
                $style = $bold ? ' s="1"' : '';
                $xml .= "<c r=\"{$ref}\" t=\"inlineStr\"{$style}><is><t xml:space=\"preserve\">{$text}</t></is></c>";
            }
        }
        return $xml . '</row>';
    }

    private static function colLetter(int $index): string
    {
        $letter = '';
        $index++;
        while ($index > 0) {
            $rem = ($index - 1) % 26;
            $letter = chr(65 + $rem) . $letter;
            $index = intdiv($index - 1, 26);
        }
        return $letter;
    }

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