<?php

namespace App\Core;

/**
 * Minimal but genuine .xlsx writer. Produces real OOXML (a zip of XML parts)
 * that Excel/Sheets can open natively.
 */
class XlsxWriter
{
    public static function write(array $headers, array $rows, string $sheetName = 'Sheet1'): string
    {
        if (!class_exists('\ZipArchive')) {
            throw new \RuntimeException('Excel export requires the PHP "zip" extension.');
        }

        // Clean sheet name — no special chars allowed in XML attribute
        $sheetName = preg_replace('/[\\\\\/\?\*\[\]:\'"]/', '', $sheetName);
        $sheetName = mb_substr($sheetName ?: 'Sheet1', 0, 31);

        $tmp = tempnam(sys_get_temp_dir(), 'xlsx_');
        $zip = new \ZipArchive();
        if ($zip->open($tmp, \ZipArchive::OVERWRITE) !== true) {
            throw new \RuntimeException('Could not create temporary Excel file.');
        }

        $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/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+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" 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/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::xmlAttr($sheetName) . '" sheetId="1" r:id="rId1"/></sheets>' .
            '</workbook>'
        );

        $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="10"/><name val="Calibri"/></font>' .
              '<font><sz val="10"/><b/><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="FF2F3B63"/><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">' .
                '<alignment vertical="center"/>' .
              '</xf>' .
            '</cellXfs>' .
            '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>' .
            '</styleSheet>'
        );

        $colCount  = max(1, count($headers));
        $colWidths = self::columnWidths($headers, $rows);
        $colsXml   = '<cols>';
        foreach ($colWidths as $i => $w) {
            $colsXml .= '<col min="' . ($i + 1) . '" max="' . ($i + 1) . '" width="' . $w . '" customWidth="1"/>';
        }
        $colsXml .= '</cols>';

        $lastCol  = self::colLetter($colCount);
        $lastRow  = 1 + count($rows);

        $sheetXml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
        $sheetXml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
        $sheetXml .= '<sheetViews><sheetView workbookViewId="0">';
        $sheetXml .= '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>';
        $sheetXml .= '</sheetView></sheetViews>';
        $sheetXml .= $colsXml;
        $sheetXml .= '<sheetData>';
        $sheetXml .= self::rowXml(1, $headers, 1);
        $r = 2;
        foreach ($rows as $row) {
            $sheetXml .= self::rowXml($r, array_values($row), 0);
            $r++;
        }
        $sheetXml .= '</sheetData>';
        $sheetXml .= '<autoFilter ref="A1:' . $lastCol . '1"/>';
        $sheetXml .= '</worksheet>';

        $zip->addFromString('xl/worksheets/sheet1.xml', $sheetXml);
        $zip->close();

        $contents = file_get_contents($tmp);
        @unlink($tmp);
        return $contents;
    }

    /**
     * Escape a string for use inside XML element text content.
     * Handles all 5 XML special characters AND removes control characters
     * that are illegal in XML 1.0 (which would corrupt the file).
     */
    private static function xmlText(string $val): string
    {
        // Strip XML 1.0 illegal control characters (keep tab, LF, CR)
        $val = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', (string)$val);
        return htmlspecialchars($val, ENT_XML1 | ENT_QUOTES, 'UTF-8');
    }

    /** Escape a string for use in an XML attribute value. */
    private static function xmlAttr(string $val): string
    {
        return htmlspecialchars($val, ENT_XML1 | ENT_QUOTES, 'UTF-8');
    }

    private static function columnWidths(array $headers, array $rows): array
    {
        $widths = [];
        foreach ($headers as $i => $h) {
            $max = mb_strlen((string)$h);
            foreach ($rows as $row) {
                $vals = array_values($row);
                if (isset($vals[$i])) {
                    $max = max($max, mb_strlen((string)$vals[$i]));
                }
            }
            $widths[$i] = min(max((int)$max + 2, 10), 50);
        }
        return $widths;
    }

    private static function rowXml(int $rowNum, array $cells, int $styleId = 0): string
    {
        $xml = '<row r="' . $rowNum . '">';
        foreach ($cells as $i => $val) {
            $col       = self::colLetter($i + 1);
            $ref       = $col . $rowNum;
            $safe      = self::xmlText((string)($val ?? ''));
            $styleAttr = $styleId ? ' s="' . $styleId . '"' : '';
            $xml .= '<c r="' . $ref . '"' . $styleAttr . ' t="inlineStr"><is><t xml:space="preserve">' . $safe . '</t></is></c>';
        }
        return $xml . '</row>';
    }

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