<?php

declare(strict_types=1);

namespace PpPhp\Core\DataOps\Formats;

use PpPhp\Core\DataOps\ExportFormat;

/**
 * A REAL .xlsx workbook (OOXML), written with ext/zip only — no dependency.
 *
 * The older ExcelFormat streams a styled HTML table as .xls, which Excel opens
 * but which is not a spreadsheet file: it cannot be read back by XlsxReader and
 * newer Excel builds warn about the extension mismatch. This writes a genuine
 * package, so an export → import round-trip works.
 *
 * Cells use inline strings (t="inlineStr"), which avoids a shared-string table
 * entirely; numeric-looking values are written as numbers so Excel sums them.
 */
final class XlsxFormat implements ExportFormat
{
    public static function id(): string { return 'xlsx'; }
    public static function label(): string { return 'Excel Workbook'; }
    public static function icon(): array { return ['bi-file-earmark-spreadsheet-fill', 'var(--pp-fmt-excel,#217346)']; }
    public static function extension(): string { return 'xlsx'; }
    public static function contentType(): string { return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; }

    public function write(array $rows, array $columns, array $meta): void
    {
        $sheet = self::sheetXml($rows, $columns, ($meta['includeHeaders'] ?? true) !== false);

        // ZipArchive needs a real file; stream it out and clean up.
        $tmp = tempnam(sys_get_temp_dir(), 'ppxlsx');
        $zip = new \ZipArchive();
        if ($zip->open($tmp, \ZipArchive::OVERWRITE) !== true) {
            @unlink($tmp);
            http_response_code(500);
            exit('Could not create the workbook.');
        }
        $zip->addFromString('[Content_Types].xml', self::CONTENT_TYPES);
        $zip->addFromString('_rels/.rels', self::ROOT_RELS);
        $zip->addFromString('xl/workbook.xml', self::WORKBOOK);
        $zip->addFromString('xl/_rels/workbook.xml.rels', self::WORKBOOK_RELS);
        $zip->addFromString('xl/styles.xml', self::STYLES);
        $zip->addFromString('xl/worksheets/sheet1.xml', $sheet);
        $zip->close();

        header('Content-Length: ' . (string) filesize($tmp));
        readfile($tmp);
        @unlink($tmp);
    }

    /** @param array<string,string> $columns */
    private static function sheetXml(array $rows, array $columns, bool $withHeader): string
    {
        $keys = array_keys($columns);
        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>';

        $r = 0;
        if ($withHeader) {
            $r++;
            $xml .= '<row r="1">';
            foreach (array_values($columns) as $i => $label) {
                $xml .= self::cell(self::colRef($i) . '1', (string) $label, true);
            }
            $xml .= '</row>';
        }
        foreach ($rows as $row) {
            $r++;
            $xml .= '<row r="' . $r . '">';
            foreach ($keys as $i => $k) {
                $xml .= self::cell(self::colRef($i) . $r, (string) ($row[$k] ?? ''), false);
            }
            $xml .= '</row>';
        }
        return $xml . '</sheetData></worksheet>';
    }

    private static function cell(string $ref, string $value, bool $bold): string
    {
        $style = $bold ? ' s="1"' : '';
        // Write plain integers/decimals as numbers; anything else as text. Values
        // with leading zeros stay text so identifiers are not mangled.
        if ($value !== '' && preg_match('/^-?(0|[1-9]\d*)(\.\d+)?$/', $value)) {
            return '<c r="' . $ref . '"' . $style . '><v>' . $value . '</v></c>';
        }
        if ($value === '') return '<c r="' . $ref . '"' . $style . '/>';
        return '<c r="' . $ref . '"' . $style . ' t="inlineStr"><is><t xml:space="preserve">'
            . htmlspecialchars($value, ENT_XML1 | ENT_QUOTES, 'UTF-8') . '</t></is></c>';
    }

    /** 0 → A, 25 → Z, 26 → AA … */
    private static function colRef(int $i): string
    {
        $s = '';
        for ($n = $i + 1; $n > 0; $n = intdiv($n - 1, 26)) {
            $s = chr(65 + (($n - 1) % 26)) . $s;
        }
        return $s;
    }

    private const CONTENT_TYPES = '<?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 const ROOT_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>';

    private const WORKBOOK = '<?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="Data" sheetId="1" r:id="rId1"/></sheets></workbook>';

    private const WORKBOOK_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>';

    /** Two cell formats: 0 = normal, 1 = bold (used for the header row). */
    private const STYLES = '<?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/></cellStyleXfs>'
        . '<cellXfs count="2"><xf xfId="0"/><xf xfId="0" fontId="1" applyFont="1"/></cellXfs>'
        . '</styleSheet>';
}
