<?php
namespace App\Core;

/**
 * Lecteur XLSX léger — utilise ZipArchive + SimpleXML/DOM (extensions PHP standard).
 * Lit la première feuille du classeur et retourne un tableau 2D de chaînes.
 */
class XlsxReader
{
    /**
     * @return string[][] Tableau de lignes, chaque ligne étant un tableau de cellules.
     */
    public static function readSheet(string $filePath, ?string $preferredSheet = null): array
    {
        if (!extension_loaded('zip')) {
            throw new \RuntimeException('L\'extension ZipArchive est requise pour lire les fichiers XLSX.');
        }

        $zip = new \ZipArchive();
        if ($zip->open($filePath) !== true) {
            throw new \RuntimeException('Impossible d\'ouvrir le fichier XLSX : ' . basename($filePath));
        }

        // 1. Shared strings (strings.xml)
        $sharedStrings = [];
        $ssContent = $zip->getFromName('xl/sharedStrings.xml');
        if ($ssContent !== false) {
            $sharedStrings = self::parseSharedStrings($ssContent);
        }

        // 2. Feuille de calcul demandée (ou RACCORDES si présente)
        $worksheetPath = self::resolveWorksheetPath($zip, $preferredSheet);
        $wsContent = $worksheetPath !== null ? $zip->getFromName($worksheetPath) : false;
        $zip->close();

        if ($wsContent === false) {
            return [];
        }

        return self::parseWorksheet($wsContent, $sharedStrings);
    }

    private static function resolveWorksheetPath(\ZipArchive $zip, ?string $preferredSheet = null): ?string
    {
        $workbookContent = $zip->getFromName('xl/workbook.xml');
        $relsContent = $zip->getFromName('xl/_rels/workbook.xml.rels');

        if ($workbookContent === false || $relsContent === false) {
            return 'xl/worksheets/sheet1.xml';
        }

        $workbookContent = preg_replace('/\sxmlns[^\"]*"[^\"]*"/i', '', $workbookContent) ?? $workbookContent;
        $relsContent = preg_replace('/\sxmlns[^\"]*"[^\"]*"/i', '', $relsContent) ?? $relsContent;

        $workbookDom = new \DOMDocument();
        $relsDom = new \DOMDocument();

        @$workbookDom->loadXML($workbookContent);
        @$relsDom->loadXML($relsContent);

        $relationshipTargets = [];
        foreach ($relsDom->getElementsByTagName('Relationship') as $relationshipNode) {
            $relationshipId = (string)$relationshipNode->getAttribute('Id');
            $target = str_replace('\\', '/', (string)$relationshipNode->getAttribute('Target'));
            if ($relationshipId !== '' && $target !== '') {
                if (!str_starts_with($target, 'xl/')) {
                    $target = 'xl/' . ltrim($target, '/');
                }
                $relationshipTargets[$relationshipId] = $target;
            }
        }

        $sheets = [];
        foreach ($workbookDom->getElementsByTagName('sheet') as $sheetNode) {
            $sheetName = trim((string)$sheetNode->getAttribute('name'));
            $relationshipId = trim((string)($sheetNode->getAttribute('r:id') ?: $sheetNode->getAttribute('id')));
            $target = $relationshipTargets[$relationshipId] ?? null;
            if ($sheetName !== '' && $target !== null) {
                $sheets[] = ['name' => $sheetName, 'target' => $target];
            }
        }

        if ($sheets === []) {
            return 'xl/worksheets/sheet1.xml';
        }

        $sheetNamesToTry = array_values(array_filter([
            $preferredSheet,
            'RACCORDES',
        ], static fn(?string $name): bool => trim((string)$name) !== ''));

        foreach ($sheetNamesToTry as $sheetNameToTry) {
            foreach ($sheets as $sheet) {
                if (strcasecmp($sheet['name'], $sheetNameToTry) === 0) {
                    return $sheet['target'];
                }
            }
        }

        return $sheets[0]['target'];
    }

    // ---------------------------------------------------------------
    // Parsing shared strings
    // ---------------------------------------------------------------
    private static function parseSharedStrings(string $xmlContent): array
    {
        $strings = [];
        // Supprime les déclarations de namespace pour simplifier la lecture
        $xmlContent = preg_replace('/\sxmlns[^"]*"[^"]*"/i', '', $xmlContent) ?? $xmlContent;

        $dom = new \DOMDocument();
        @$dom->loadXML($xmlContent);

        $sis = $dom->getElementsByTagName('si');
        foreach ($sis as $si) {
            $tNodes = $si->getElementsByTagName('t');
            $text = '';
            foreach ($tNodes as $t) {
                // Préserver les espaces si xml:space="preserve"
                $text .= $t->nodeValue;
            }
            $strings[] = $text;
        }
        return $strings;
    }

    // ---------------------------------------------------------------
    // Conversion de référence de colonne (A, B, ..., Z, AA, ...) en index 0-based
    // ---------------------------------------------------------------
    private static function colIndex(string $ref): int
    {
        preg_match('/^([A-Z]+)/i', strtoupper($ref), $m);
        $col = $m[1] ?? 'A';
        $idx = 0;
        $len = strlen($col);
        for ($i = 0; $i < $len; $i++) {
            $idx = $idx * 26 + (ord($col[$i]) - 64);
        }
        return $idx - 1; // 0-based
    }

    // ---------------------------------------------------------------
    // Parsing de la feuille de calcul
    // ---------------------------------------------------------------
    private static function parseWorksheet(string $xmlContent, array $sharedStrings): array
    {
        $xmlContent = preg_replace('/\sxmlns[^"]*"[^"]*"/i', '', $xmlContent) ?? $xmlContent;

        $dom = new \DOMDocument();
        @$dom->loadXML($xmlContent);

        $rows = [];
        $rowNodes = $dom->getElementsByTagName('row');

        foreach ($rowNodes as $rowNode) {
            $rowAttr = (int)($rowNode->getAttribute('r') ?: 0);
            $rowIdx  = $rowAttr > 0 ? $rowAttr - 1 : count($rows);

            // Garantir l'existence de la ligne
            while (count($rows) <= $rowIdx) {
                $rows[] = [];
            }

            $cellNodes = $rowNode->getElementsByTagName('c');
            foreach ($cellNodes as $cell) {
                $ref    = $cell->getAttribute('r');
                $colIdx = $ref !== '' ? self::colIndex($ref) : 0;
                $type   = $cell->getAttribute('t');

                // Valeur brute
                $vNodes = $cell->getElementsByTagName('v');
                $rawVal = $vNodes->length > 0 ? (string)$vNodes->item(0)->nodeValue : '';

                if ($type === 's') {
                    // Shared string
                    $rawVal = $sharedStrings[(int)$rawVal] ?? '';
                } elseif ($type === 'inlineStr') {
                    $isNodes = $cell->getElementsByTagName('is');
                    if ($isNodes->length > 0) {
                        $tNodes = $isNodes->item(0)->getElementsByTagName('t');
                        $text   = '';
                        foreach ($tNodes as $t) {
                            $text .= $t->nodeValue;
                        }
                        $rawVal = $text;
                    }
                }
                // Type 'b' (boolean), vide, numérique → garder tel quel

                // Élargir le tableau si nécessaire
                while (count($rows[$rowIdx]) <= $colIdx) {
                    $rows[$rowIdx][] = '';
                }
                $rows[$rowIdx][$colIdx] = trim((string)$rawVal);
            }
        }

        return $rows;
    }

    // ---------------------------------------------------------------
    // Utilitaire : mappe les en-têtes de la 1ère ligne sur les colonnes
    // Retourne un tableau associatif par ligne
    // ---------------------------------------------------------------
    public static function toAssoc(array $rows): array
    {
        if (count($rows) < 2) {
            return [];
        }
        $headers = array_map('trim', $rows[0]);
        $result  = [];
        for ($i = 1; $i < count($rows); $i++) {
            $row  = $rows[$i];
            $item = [];
            foreach ($headers as $col => $header) {
                $item[$header] = $row[$col] ?? '';
            }
            $result[] = $item;
        }
        return $result;
    }
}