<?php
declare(strict_types=1);

final class TabularReader
{
    public static function read(string $filePath): array
    {
        if (!is_file($filePath)) {
            throw new RuntimeException('Import file was not found.');
        }

        $extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
        return match ($extension) {
            'csv', 'txt' => self::readCsv($filePath),
            'xlsx' => self::readXlsx($filePath),
            default => throw new RuntimeException('Only CSV and XLSX files are supported.'),
        };
    }

    public static function readWorksheet(string $filePath, string $worksheetName, array $requiredHeaders = []): array
    {
        if (!is_file($filePath)) {
            throw new RuntimeException('Import file was not found.');
        }

        $extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
        return match ($extension) {
            'csv', 'txt' => self::readCsv($filePath, $requiredHeaders),
            'xlsx' => self::readXlsx($filePath, $worksheetName, $requiredHeaders),
            default => throw new RuntimeException('Only CSV and XLSX files are supported.'),
        };
    }

    public static function normalizeHeader(string $value): string
    {
        $value = trim($value);
        if ($value === '') {
            return '';
        }

        $value = preg_replace('/[^\p{L}\p{N}]+/u', '_', $value) ?? $value;
        $value = trim($value, '_');
        $value = strtolower($value);
        return $value;
    }

    private static function readCsv(string $filePath, array $requiredHeaders = []): array
    {
        $handle = fopen($filePath, 'rb');
        if ($handle === false) {
            throw new RuntimeException('Unable to open CSV file.');
        }

        try {
            $firstLine = '';
            while (($firstLine = fgets($handle)) !== false) {
                if (trim($firstLine) !== '') {
                    break;
                }
            }

            if ($firstLine === '' || $firstLine === false) {
                throw new RuntimeException('CSV file is empty.');
            }

            $delimiter = self::detectDelimiter($firstLine);
            rewind($handle);

            $rows = [];
            while (($cells = fgetcsv($handle, 0, $delimiter)) !== false) {
                if ($cells === [null] || $cells === false) {
                    continue;
                }
                $rows[] = array_map(static fn (mixed $v): string => trim((string) $v), $cells);
            }
        } finally {
            fclose($handle);
        }

        return self::rowsToStructured($rows, $requiredHeaders);
    }

    private static function detectDelimiter(string $line): string
    {
        $candidates = [
            ',' => substr_count($line, ','),
            ';' => substr_count($line, ';'),
            "\t" => substr_count($line, "\t"),
            '|' => substr_count($line, '|'),
        ];
        arsort($candidates);
        $delimiter = array_key_first($candidates);
        return is_string($delimiter) ? $delimiter : ',';
    }

    private static function readXlsx(string $filePath, ?string $preferredWorksheetName = null, array $requiredHeaders = []): array
    {
        $zip = new ZipArchive();
        $opened = $zip->open($filePath);
        if ($opened !== true) {
            throw new RuntimeException('Unable to open XLSX file.');
        }

        try {
            $sharedStrings = self::readSharedStrings($zip);
            $sheetPath = self::worksheetPath($zip, $preferredWorksheetName);
            if ($sheetPath === null) {
                throw new RuntimeException('Workbook does not contain a readable worksheet.');
            }

            $xml = self::readXmlEntry($zip, $sheetPath);
            $rows = self::readWorksheetRows($xml, $sharedStrings);
            return self::rowsToStructured($rows, $requiredHeaders);
        } finally {
            $zip->close();
        }
    }

    private static function rowsToStructured(array $rows, array $requiredHeaders = []): array
    {
        $headerIndex = null;
        $headers = [];
        $requiredHeaderMap = [];
        foreach ($requiredHeaders as $requiredHeader) {
            $requiredKey = self::normalizeHeader((string) $requiredHeader);
            if ($requiredKey !== '') {
                $requiredHeaderMap[$requiredKey] = true;
            }
        }

        if ($requiredHeaderMap) {
            foreach ($rows as $index => $cells) {
                $keys = [];
                foreach ((array) $cells as $cell) {
                    $key = self::normalizeHeader((string) $cell);
                    if ($key !== '') {
                        $keys[$key] = true;
                    }
                }
                $matches = count(array_intersect_key($keys, $requiredHeaderMap));
                if ($matches >= min(2, count($requiredHeaderMap))) {
                    $headerIndex = $index;
                    $headers = array_map(static fn (mixed $v): string => trim((string) $v), $cells);
                    break;
                }
            }
        }

        if ($headerIndex === null) {
            foreach ($rows as $index => $cells) {
                $joined = trim(implode('', array_map('strval', $cells)));
                if ($joined === '') {
                    continue;
                }
                $headerIndex = $index;
                $headers = array_map(static fn (mixed $v): string => trim((string) $v), $cells);
                break;
            }
        }

        if ($headerIndex === null || !$headers) {
            throw new RuntimeException('Could not detect a header row in the file.');
        }

        $normalized = [];
        $used = [];
        foreach ($headers as $i => $header) {
            $key = self::normalizeHeader($header);
            if ($key === '') {
                $key = 'column_' . ($i + 1);
            }
            $base = $key;
            $suffix = 2;
            while (isset($used[$key])) {
                $key = $base . '_' . $suffix;
                $suffix++;
            }
            $used[$key] = true;
            $normalized[$i] = $key;
        }

        $structuredRows = [];
        foreach ($rows as $index => $cells) {
            if ($index <= $headerIndex) {
                continue;
            }

            $row = [];
            $hasValue = false;
            foreach ($normalized as $colIndex => $fieldKey) {
                $value = isset($cells[$colIndex]) ? trim((string) $cells[$colIndex]) : '';
                if ($value !== '') {
                    $hasValue = true;
                }
                $row[$fieldKey] = $value;
            }

            if ($hasValue) {
                $structuredRows[] = $row;
            }
        }

        return [
            'header_row_index' => $headerIndex + 1,
            'headers' => array_values($headers),
            'normalized_headers' => array_values($normalized),
            'rows' => $structuredRows,
        ];
    }

    private static function readXmlEntry(ZipArchive $zip, string $entry): string
    {
        $xml = $zip->getFromName($entry);
        if ($xml === false) {
            throw new RuntimeException(sprintf('Unable to read workbook entry: %s', $entry));
        }

        return $xml;
    }

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

        $xml = self::readXmlEntry($zip, 'xl/sharedStrings.xml');
        $dom = new DOMDocument();
        if (!$dom->loadXML($xml, LIBXML_NOCDATA | LIBXML_NONET)) {
            throw new RuntimeException('Unable to parse shared strings.');
        }

        $xpath = new DOMXPath($dom);
        $xpath->registerNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $strings = [];

        foreach ($xpath->query('//x:si') as $sharedString) {
            $text = '';
            foreach ($xpath->query('.//x:t', $sharedString) as $textNode) {
                $text .= $textNode->textContent;
            }
            $strings[] = $text;
        }

        return $strings;
    }

    private static function firstWorksheetPath(ZipArchive $zip): ?string
    {
        return self::worksheetPath($zip, null);
    }

    private static function worksheetPath(ZipArchive $zip, ?string $preferredWorksheetName): ?string
    {
        $workbookXml = self::readXmlEntry($zip, 'xl/workbook.xml');
        $relsXml = self::readXmlEntry($zip, 'xl/_rels/workbook.xml.rels');

        $relsDom = new DOMDocument();
        if (!$relsDom->loadXML($relsXml, LIBXML_NOCDATA | LIBXML_NONET)) {
            return null;
        }

        $relsXpath = new DOMXPath($relsDom);
        $relsXpath->registerNamespace('r', 'http://schemas.openxmlformats.org/package/2006/relationships');
        $relationships = [];
        foreach ($relsXpath->query('//r:Relationship') as $relationshipNode) {
            if (!$relationshipNode instanceof DOMElement) {
                continue;
            }
            $relationshipId = (string) $relationshipNode->getAttribute('Id');
            $target = self::normalizeWorksheetTarget((string) $relationshipNode->getAttribute('Target'));
            if ($relationshipId !== '' && $target !== '') {
                $relationships[$relationshipId] = $target;
            }
        }
        if (!$relationships) {
            return null;
        }

        $workbookDom = new DOMDocument();
        if (!$workbookDom->loadXML($workbookXml, LIBXML_NOCDATA | LIBXML_NONET)) {
            return null;
        }

        $workbookXpath = new DOMXPath($workbookDom);
        $workbookXpath->registerNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $workbookXpath->registerNamespace('r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
        $preferredKey = $preferredWorksheetName !== null ? self::normalizeHeader($preferredWorksheetName) : '';
        $firstPath = null;
        foreach ($workbookXpath->query('//x:sheets/x:sheet') as $sheetNode) {
            if (!$sheetNode instanceof DOMElement) {
                continue;
            }
            $relationshipId = (string) $sheetNode->getAttributeNS('http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'id');
            if ($relationshipId === '') {
                $relationshipId = (string) $sheetNode->getAttribute('r:id');
            }
            $target = $relationships[$relationshipId] ?? '';
            if ($target === '') {
                continue;
            }
            $firstPath ??= $target;
            $sheetName = (string) $sheetNode->getAttribute('name');
            if ($preferredKey !== '' && self::normalizeHeader($sheetName) === $preferredKey) {
                return $target;
            }
        }

        return $firstPath;
    }

    private static function normalizeWorksheetTarget(string $target): string
    {
        $target = trim($target);
        if ($target === '') {
            return '';
        }

        $target = ltrim($target, '/');
        if (!str_starts_with($target, 'xl/')) {
            $target = 'xl/' . ltrim($target, '/');
        }
        return $target;
    }

    private static function readWorksheetRows(string $xml, array $sharedStrings): array
    {
        $dom = new DOMDocument();
        if (!$dom->loadXML($xml, LIBXML_NOCDATA | LIBXML_NONET)) {
            throw new RuntimeException('Unable to parse worksheet XML.');
        }

        $xpath = new DOMXPath($dom);
        $xpath->registerNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $rows = [];

        foreach ($xpath->query('//x:sheetData/x:row') as $rowNode) {
            if (!$rowNode instanceof DOMElement) {
                continue;
            }

            $row = [];
            foreach ($xpath->query('./x:c', $rowNode) as $cellNode) {
                if (!$cellNode instanceof DOMElement) {
                    continue;
                }

                $ref = (string) $cellNode->getAttribute('r');
                if ($ref === '') {
                    continue;
                }

                $columnIndex = self::columnIndexFromRef($ref) - 1;
                $row[$columnIndex] = self::cellValue($xpath, $cellNode, $sharedStrings);
            }

            if ($row) {
                ksort($row);
                $rows[] = $row;
            } else {
                $rows[] = [];
            }
        }

        return $rows;
    }

    private static function cellValue(DOMXPath $xpath, DOMElement $cell, array $sharedStrings): string
    {
        $type = $cell->getAttribute('t');
        if ($type === 's') {
            $node = $xpath->query('./x:v', $cell)->item(0);
            $index = $node ? (int) $node->textContent : 0;
            return (string) ($sharedStrings[$index] ?? '');
        }

        if ($type === 'inlineStr') {
            $node = $xpath->query('./x:is/x:t', $cell)->item(0);
            return $node ? (string) $node->textContent : '';
        }

        if ($type === 'b') {
            $node = $xpath->query('./x:v', $cell)->item(0);
            return ($node && $node->textContent === '1') ? '1' : '0';
        }

        $node = $xpath->query('./x:v', $cell)->item(0);
        return $node ? (string) $node->textContent : '';
    }

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

        foreach (str_split($letters) as $letter) {
            $index = ($index * 26) + (ord($letter) - 64);
        }

        return $index;
    }
}
