<?php

/** Lightweight CSV/XLSX reader for identifier/detail maintenance uploads. */
final class SpreadsheetUploadReader {
    private const MAX_ROWS = 5001; // header + 5,000 data rows
    private const MAX_COLUMNS = 50;

    public static function read($path, $originalName): array {
        $extension = strtolower(pathinfo((string)$originalName, PATHINFO_EXTENSION));
        if ($extension === 'csv') {
            return self::readCsv($path);
        }
        if ($extension === 'xlsx') {
            return self::readXlsx($path);
        }
        throw new RuntimeException('Upload a .xlsx or .csv file. The older .xls format is not supported.');
    }

    private static function readCsv($path): array {
        $handle = fopen($path, 'rb');
        if (!$handle) {
            throw new RuntimeException('Unable to read the uploaded CSV file.');
        }

        $matrix = [];
        try {
            while (($row = fgetcsv($handle)) !== false) {
                $matrix[] = array_slice($row, 0, self::MAX_COLUMNS);
                if (count($matrix) > self::MAX_ROWS) {
                    throw new RuntimeException('The upload exceeds the 5,000-row limit.');
                }
            }
        } finally {
            fclose($handle);
        }
        return self::toRecords($matrix);
    }

    private static function readXlsx($path): array {
        if (!class_exists('ZipArchive')) {
            throw new RuntimeException('This server cannot read .xlsx files because the PHP ZIP extension is unavailable. Upload CSV instead.');
        }

        $zip = new ZipArchive();
        if ($zip->open($path) !== true) {
            throw new RuntimeException('The uploaded Excel workbook is invalid or damaged.');
        }

        try {
            $sheetPath = self::firstWorksheetPath($zip);
            $sheetXml = $zip->getFromName($sheetPath);
            if ($sheetXml === false) {
                throw new RuntimeException('The first worksheet could not be read.');
            }
            $sharedStrings = self::sharedStrings($zip);
            $document = self::xmlDocument($sheetXml, 'worksheet');
            $xpath = new DOMXPath($document);
            $matrix = [];

            foreach ($xpath->query('//*[local-name()="sheetData"]/*[local-name()="row"]') as $rowNode) {
                $rowNumber = max(1, (int)$rowNode->getAttribute('r'));
                if ($rowNumber > self::MAX_ROWS) {
                    throw new RuntimeException('The upload exceeds the 5,000-row limit.');
                }
                $values = [];
                $nextColumn = 0;
                foreach ($xpath->query('./*[local-name()="c"]', $rowNode) as $cell) {
                    $reference = $cell->getAttribute('r');
                    $column = preg_match('/^([A-Z]+)/i', $reference, $match)
                        ? self::columnIndex($match[1])
                        : $nextColumn;
                    $nextColumn = $column + 1;
                    if ($column >= self::MAX_COLUMNS) {
                        continue;
                    }
                    $values[$column] = self::cellValue($xpath, $cell, $sharedStrings);
                }
                if ($values) {
                    $lastColumn = max(array_keys($values));
                    $matrix[$rowNumber - 1] = [];
                    for ($column = 0; $column <= $lastColumn; $column++) {
                        $matrix[$rowNumber - 1][$column] = $values[$column] ?? '';
                    }
                }
            }

            ksort($matrix);
            return self::toRecords(array_values($matrix));
        } finally {
            $zip->close();
        }
    }

    private static function firstWorksheetPath(ZipArchive $zip): string {
        $workbookXml = $zip->getFromName('xl/workbook.xml');
        $relationsXml = $zip->getFromName('xl/_rels/workbook.xml.rels');
        if ($workbookXml === false || $relationsXml === false) {
            return 'xl/worksheets/sheet1.xml';
        }

        $workbook = self::xmlDocument($workbookXml, 'workbook');
        $workbookXpath = new DOMXPath($workbook);
        $sheet = $workbookXpath->query('//*[local-name()="sheets"]/*[local-name()="sheet"]')->item(0);
        if (!$sheet) {
            throw new RuntimeException('The workbook has no worksheets.');
        }
        $relationshipId = $sheet->getAttributeNS(
            'http://schemas.openxmlformats.org/officeDocument/2006/relationships',
            'id'
        );

        $relations = self::xmlDocument($relationsXml, 'workbook relationships');
        $relationsXpath = new DOMXPath($relations);
        foreach ($relationsXpath->query('//*[local-name()="Relationship"]') as $relation) {
            if ($relation->getAttribute('Id') !== $relationshipId) {
                continue;
            }
            $target = str_replace('\\', '/', $relation->getAttribute('Target'));
            $target = preg_replace('#^(\.\./)+#', '', $target);
            if (strpos($target, 'worksheets/') === 0) {
                return 'xl/' . $target;
            }
            if (strpos($target, 'xl/') === 0) {
                return $target;
            }
        }
        return 'xl/worksheets/sheet1.xml';
    }

    private static function sharedStrings(ZipArchive $zip): array {
        $xml = $zip->getFromName('xl/sharedStrings.xml');
        if ($xml === false) {
            return [];
        }
        $document = self::xmlDocument($xml, 'shared strings');
        $xpath = new DOMXPath($document);
        $strings = [];
        foreach ($xpath->query('//*[local-name()="si"]') as $item) {
            $text = '';
            foreach ($xpath->query('.//*[local-name()="t"]', $item) as $textNode) {
                $text .= $textNode->textContent;
            }
            $strings[] = $text;
        }
        return $strings;
    }

    private static function cellValue(DOMXPath $xpath, DOMElement $cell, array $sharedStrings): string {
        $type = $cell->getAttribute('t');
        if ($type === 'inlineStr') {
            $value = '';
            foreach ($xpath->query('.//*[local-name()="t"]', $cell) as $textNode) {
                $value .= $textNode->textContent;
            }
            return $value;
        }

        $valueNode = $xpath->query('./*[local-name()="v"]', $cell)->item(0);
        $value = $valueNode ? $valueNode->textContent : '';
        if ($type === 's') {
            return $sharedStrings[(int)$value] ?? '';
        }
        if ($type === 'b') {
            return $value === '1' ? '1' : '0';
        }
        return $value;
    }

    private static function xmlDocument($xml, $label): DOMDocument {
        $previous = libxml_use_internal_errors(true);
        $document = new DOMDocument();
        $loaded = $document->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT);
        libxml_clear_errors();
        libxml_use_internal_errors($previous);
        if (!$loaded) {
            throw new RuntimeException('Invalid XML in the Excel ' . $label . '.');
        }
        return $document;
    }

    private static function columnIndex($letters): int {
        $index = 0;
        foreach (str_split(strtoupper($letters)) as $letter) {
            $index = ($index * 26) + (ord($letter) - 64);
        }
        return max(0, $index - 1);
    }

    private static function toRecords(array $matrix): array {
        if (!$matrix) {
            throw new RuntimeException('The uploaded spreadsheet is empty.');
        }

        $headerRow = array_shift($matrix);
        $headers = [];
        foreach ($headerRow as $index => $header) {
            $header = preg_replace('/^\xEF\xBB\xBF/', '', trim((string)$header));
            $header = strtolower(preg_replace('/[^a-zA-Z0-9]+/', '_', $header));
            $headers[$index] = trim($header, '_');
        }

        $records = [];
        foreach ($matrix as $offset => $row) {
            if (!array_filter($row, static function ($value) { return trim((string)$value) !== ''; })) {
                continue;
            }
            $record = ['_row' => $offset + 2];
            foreach ($headers as $index => $header) {
                if ($header !== '') {
                    $record[$header] = isset($row[$index]) ? trim((string)$row[$index]) : '';
                }
            }
            $records[] = $record;
        }
        return $records;
    }
}
