<?php

declare(strict_types=1);

namespace App\Services;

use RuntimeException;
use ZipArchive;

final class XlsxReader
{
    /**
     * Lit la première feuille d'un fichier XLSX et retourne un tableau de lignes.
     * Cette classe évite une dépendance obligatoire à PhpSpreadsheet pour l'import simple.
     */
    public function read(string $filePath): array
    {
        if (!class_exists(ZipArchive::class)) {
            throw new RuntimeException('L\'extension PHP ZipArchive est nécessaire pour lire les fichiers XLSX.');
        }

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

        $sharedStrings = $this->readSharedStrings($zip);
        $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
        if ($sheetXml === false) {
            $zip->close();
            throw new RuntimeException('La première feuille du fichier XLSX est introuvable.');
        }

        $xml = simplexml_load_string($sheetXml);
        if ($xml === false) {
            $zip->close();
            throw new RuntimeException('La feuille Excel est illisible.');
        }

        $rows = [];
        foreach ($xml->sheetData->row as $row) {
            $line = [];
            foreach ($row->c as $cell) {
                $attributes = $cell->attributes();
                $reference = (string) ($attributes['r'] ?? '');
                $type = (string) ($attributes['t'] ?? '');
                $columnIndex = $this->columnIndex($reference);
                $value = $this->cellValue($cell, $type, $sharedStrings);
                $line[$columnIndex] = $value;
            }

            if (!empty(array_filter($line, static fn ($value) => trim((string) $value) !== ''))) {
                ksort($line);
                $rows[] = $line;
            }
        }

        $zip->close();
        return $rows;
    }

    private function readSharedStrings(ZipArchive $zip): array
    {
        $content = $zip->getFromName('xl/sharedStrings.xml');
        if ($content === false) {
            return [];
        }

        $xml = simplexml_load_string($content);
        if ($xml === false) {
            return [];
        }

        $strings = [];
        foreach ($xml->si as $si) {
            if (isset($si->t)) {
                $strings[] = (string) $si->t;
                continue;
            }

            $text = '';
            if (isset($si->r)) {
                foreach ($si->r as $run) {
                    $text .= (string) $run->t;
                }
            }
            $strings[] = $text;
        }

        return $strings;
    }

    private function cellValue(\SimpleXMLElement $cell, string $type, array $sharedStrings): string
    {
        if ($type === 's') {
            $index = (int) ($cell->v ?? 0);
            return trim((string) ($sharedStrings[$index] ?? ''));
        }

        if ($type === 'inlineStr' && isset($cell->is->t)) {
            return trim((string) $cell->is->t);
        }

        return trim((string) ($cell->v ?? ''));
    }

    private function columnIndex(string $cellReference): int
    {
        preg_match('/^[A-Z]+/i', $cellReference, $matches);
        $letters = strtoupper($matches[0] ?? 'A');
        $index = 0;

        for ($i = 0; $i < strlen($letters); $i++) {
            $index = $index * 26 + (ord($letters[$i]) - 64);
        }

        return $index - 1;
    }
}
