<?php

namespace App\Support;

/**
 * Read headers and rows from CSV or Excel (xlsx, xls) for import.
 * Returns data in the same shape Filament expects: first row = headers, rows = associative arrays.
 *
 * .xlsx is read natively (no extra package). Legacy .xls requires: composer require phpoffice/phpspreadsheet
 */
class ExcelImportHelper
{
    /**
     * Return true if the file is Excel by extension or mime.
     */
    public static function isExcel(string $pathOrExtension, ?string $mime = null): bool
    {
        $ext = strtolower(pathinfo($pathOrExtension, PATHINFO_EXTENSION));
        if (in_array($ext, ['xlsx', 'xls'], true)) {
            return true;
        }
        if ($mime && in_array(strtolower($mime), [
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
        ], true)) {
            return true;
        }
        return false;
    }

    /**
     * Get headers (first row) and all data rows from a file.
     * For CSV: uses league/csv style - pass a stream or path; we use file path and detect format.
     *
     * @return array{0: array<int, string>, 1: array<int, array<string, mixed>>}
     */
    public static function readHeadersAndRows(string $path, int $headerRowIndex = 0): array
    {
        $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
        if (in_array($ext, ['xlsx', 'xls'], true)) {
            return self::readExcelHeadersAndRows($path, $headerRowIndex);
        }
        return self::readCsvHeadersAndRows($path, $headerRowIndex);
    }

    /**
     * Read from Excel file. Uses built-in xlsx reader when PhpSpreadsheet is not installed;
     * for .xls (legacy) requires PhpSpreadsheet.
     *
     * @return array{0: array<int, string>, 1: array<int, array<string, mixed>>}
     */
    public static function readExcelHeadersAndRows(string $path, int $headerRowIndex = 0): array
    {
        $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));

        if ($ext === 'xls') {
            if (! class_exists(\PhpOffice\PhpSpreadsheet\IOFactory::class)) {
                throw new \RuntimeException(
                    'Legacy .xls files require PhpSpreadsheet. Use .xlsx or CSV, or run: composer require phpoffice/phpspreadsheet'
                );
            }
            return self::readExcelViaPhpSpreadsheet($path, $headerRowIndex);
        }

        if ($ext === 'xlsx') {
            if (class_exists(\PhpOffice\PhpSpreadsheet\IOFactory::class)) {
                return self::readExcelViaPhpSpreadsheet($path, $headerRowIndex);
            }
            return self::readXlsxNative($path, $headerRowIndex);
        }

        return [[], []];
    }

    /**
     * Read xlsx using PhpSpreadsheet (when available).
     *
     * @return array{0: array<int, string>, 1: array<int, array<string, mixed>>}
     */
    protected static function readExcelViaPhpSpreadsheet(string $path, int $headerRowIndex): array
    {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray(null, true, true, true);

        if (empty($rows)) {
            return [[], []];
        }

        $excelDate = \PhpOffice\PhpSpreadsheet\Shared\Date::class;

        $headerRow = array_values($rows[$headerRowIndex + 1] ?? $rows[1] ?? []);
        $headers = array_map(function ($cell) use ($excelDate) {
            $v = is_numeric($cell) && $cell > 0 ? $excelDate::excelToDateTimeObject($cell)->format('Y-m-d') : (string) $cell;
            return trim($v);
        }, $headerRow);

        $dataRows = [];
        $startRow = $headerRowIndex + 2;
        foreach (array_slice($rows, $startRow - 1, null, true) as $row) {
            $row = array_values($row);
            $assoc = [];
            foreach ($headers as $i => $header) {
                $value = $row[$i] ?? '';
                if (is_numeric($value) && $value > 0 && $header !== '' && self::looksLikeDateColumn($header)) {
                    try {
                        $value = $excelDate::excelToDateTimeObject($value)->format('Y-m-d H:i:s');
                    } catch (\Throwable $e) {
                    }
                } elseif (is_numeric($value)) {
                    $value = (string) $value;
                } else {
                    $value = trim((string) $value);
                }
                $assoc[$header] = $value;
            }
            if (array_filter($assoc, fn ($v) => $v !== '' && $v !== null)) {
                $dataRows[] = $assoc;
            }
        }

        return [$headers, $dataRows];
    }

    /**
     * Read .xlsx without PhpSpreadsheet (ZIP + XML). Supports first sheet only.
     *
     * @return array{0: array<int, string>, 1: array<int, array<string, mixed>>}
     */
    protected static function readXlsxNative(string $path, int $headerRowIndex): array
    {
        $zip = new \ZipArchive;
        if ($zip->open($path, \ZipArchive::RDONLY) !== true) {
            return [[], []];
        }

        $sharedStrings = [];
        $stringsXml = $zip->getFromName('xl/sharedStrings.xml');
        if ($stringsXml !== false) {
            $xml = @simplexml_load_string($stringsXml, 'SimpleXMLElement', 0, 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
            if ($xml !== false) {
                foreach ($xml->si ?? [] as $si) {
                    $parts = [];
                    if (isset($si->t)) {
                        $parts[] = (string) $si->t;
                    }
                    foreach ($si->r ?? [] as $r) {
                        $parts[] = (string) $r->t;
                    }
                    $sharedStrings[] = implode('', $parts);
                }
            }
        }

        $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
        $zip->close();

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

        $rows = self::parseSheetXmlToRows($sheetXml, $sharedStrings);
        if (empty($rows)) {
            return [[], []];
        }

        $headerRow = $rows[$headerRowIndex] ?? $rows[0];
        $headers = array_map(fn ($v) => trim((string) $v), $headerRow);

        $dataRows = [];
        $start = $headerRowIndex + 1;
        foreach (array_slice($rows, $start) as $row) {
            $assoc = [];
            foreach ($headers as $i => $header) {
                $value = $row[$i] ?? '';
                $value = is_numeric($value) ? (string) $value : trim((string) $value);
                $assoc[$header] = $value;
            }
            if (array_filter($assoc, fn ($v) => $v !== '' && $v !== null)) {
                $dataRows[] = $assoc;
            }
        }

        return [$headers, $dataRows];
    }

    /**
     * Parse xl/worksheets/sheet1.xml into array of rows (0-indexed), each row = array of cell values.
     */
    protected static function parseSheetXmlToRows(string $sheetXml, array $sharedStrings): array
    {
        $ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
        $xml = @simplexml_load_string($sheetXml, 'SimpleXMLElement', 0, $ns);
        if ($xml === false) {
            return [];
        }

        $xml->registerXPathNamespace('s', $ns);
        $rowEls = $xml->xpath('//s:row');
        if ($rowEls === false) {
            return [];
        }

        $rows = [];
        foreach ($rowEls as $rowEl) {
            $rowIndex = (int) ($rowEl['r'] ?? 0) - 1;
            $cells = [];
            foreach ($rowEl->c ?? [] as $c) {
                $ref = (string) ($c['r'] ?? '');
                $type = (string) ($c['t'] ?? '');
                $colIndex = self::columnRefToIndex($ref);
                $v = $c->v !== null ? (string) $c->v : '';
                if ($type === 's' && $v !== '' && isset($sharedStrings[(int) $v])) {
                    $v = $sharedStrings[(int) $v];
                }
                $cells[$colIndex] = $v;
            }
            if ($cells !== []) {
                $rows[$rowIndex] = $cells;
            }
        }

        ksort($rows, SORT_NUMERIC);
        $maxRow = $rows ? max(array_keys($rows)) : 0;
        $maxCol = 0;
        foreach ($rows as $cells) {
            foreach (array_keys($cells) as $c) {
                if ($c > $maxCol) {
                    $maxCol = $c;
                }
            }
        }

        $out = [];
        for ($r = 0; $r <= $maxRow; $r++) {
            $line = [];
            $rowCells = $rows[$r] ?? [];
            for ($c = 0; $c <= $maxCol; $c++) {
                $line[$c] = $rowCells[$c] ?? '';
            }
            $out[] = $line;
        }

        return $out;
    }

    protected static function columnRefToIndex(string $ref): int
    {
        $col = preg_replace('/[0-9]+/', '', $ref);
        $index = 0;
        $len = strlen($col);
        for ($i = 0; $i < $len; $i++) {
            $index = $index * 26 + (ord($col[$i]) - ord('A') + 1);
        }
        return $index - 1;
    }

    protected static function looksLikeDateColumn(string $header): bool
    {
        $h = strtolower($header);
        return str_contains($h, 'date') || str_contains($h, 'time') || str_contains($h, 'created') || str_contains($h, 'updated');
    }

    /**
     * Read from CSV file (using native fgetcsv for consistency with Filament).
     *
     * @return array{0: array<int, string>, 1: array<int, array<string, mixed>>}
     */
    public static function readCsvHeadersAndRows(string $path, int $headerRowIndex = 0): array
    {
        $handle = fopen($path, 'r');
        if (!$handle) {
            return [[], []];
        }

        $delimiters = [',', ';', "\t", '|'];
        $firstLine = fgets($handle);
        rewind($handle);
        $delimiter = ',';
        foreach ($delimiters as $d) {
            if (substr_count($firstLine, $d) > 0) {
                $delimiter = $d;
                break;
            }
        }

        $rowNum = 0;
        $headers = [];
        $dataRows = [];
        while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
            if ($rowNum === $headerRowIndex) {
                $headers = array_map('trim', $row);
                $rowNum++;
                continue;
            }
            if ($rowNum < $headerRowIndex) {
                $rowNum++;
                continue;
            }
            $assoc = [];
            foreach ($headers as $i => $header) {
                $assoc[$header] = isset($row[$i]) ? trim((string) $row[$i]) : '';
            }
            if (array_filter($assoc, fn ($v) => $v !== '')) {
                $dataRows[] = $assoc;
            }
            $rowNum++;
        }
        fclose($handle);
        return [$headers, $dataRows];
    }
}
