<?php

namespace App\Support;

use App\Models\Disk;
use Illuminate\Http\Response as HttpResponse;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use RuntimeException;
use Symfony\Component\HttpFoundation\StreamedResponse;
use ZipArchive;

class DiskPreviewBuilder
{
    private const PREVIEW_SUPPORT_MESSAGE = 'Built-in preview supports PDF, DOC, DOCX, XLSX, XLS and CSV.';

    /**
     * @var array<int, string>
     */
    private const WORD_EXTENSIONS = ['docx', 'doc'];

    /**
     * @var array<int, string>
     */
    private const EXCEL_EXTENSIONS = ['xlsx', 'xls', 'csv'];

    /**
     * @var array<int, string>
     */
    private const PREVIEWABLE_EXTENSIONS = ['pdf', 'docx', 'doc', 'xlsx', 'xls', 'csv'];

    private const PREVIEW_MAX_ROWS = 300;

    private const PREVIEW_MAX_COLUMNS = 40;

    public function canPreviewInCrm(Disk $disk): bool
    {
        $extension = strtolower(trim((string) $disk->extension));

        return in_array($extension, self::PREVIEWABLE_EXTENSIONS, true);
    }

    /**
     * @return array{kind:string,title:string,html:\Illuminate\Support\HtmlString|null,message:string|null}
     */
    public function buildPreviewPayload(Disk $disk): array
    {
        $extension = strtolower(trim((string) $disk->extension));

        if ($extension === 'pdf') {
            return [
                'kind' => 'pdf',
                'title' => 'PDF preview',
                'html' => null,
                'message' => null,
            ];
        }

        if (in_array($extension, self::WORD_EXTENSIONS, true)) {
            return [
                'kind' => 'word',
                'title' => 'Word preview',
                'html' => null,
                'message' => null,
            ];
        }

        if (in_array($extension, self::EXCEL_EXTENSIONS, true)) {
            return [
                'kind' => 'excel',
                'title' => 'Spreadsheet preview',
                'html' => null,
                'message' => null,
            ];
        }

        return [
            'kind' => 'unsupported',
            'title' => 'Preview unavailable',
            'html' => null,
            'message' => self::PREVIEW_SUPPORT_MESSAGE,
        ];
    }

    public function buildWordPreviewSourceResponse(Disk $disk): HttpResponse|StreamedResponse
    {
        $extension = strtolower(trim((string) $disk->extension));
        $filename = $this->wordPreviewFilename($disk);
        $docxMime = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';

        if ($extension === 'docx') {
            return Storage::disk('public')->response(
                $disk->storage_path,
                $filename,
                [
                    'Content-Type' => $docxMime,
                    'Content-Disposition' => sprintf('inline; filename="%s"', str_replace('"', '', $filename)),
                    'X-Content-Type-Options' => 'nosniff',
                ]
            );
        }

        if ($extension !== 'doc') {
            throw new RuntimeException('Word preview is available only for DOC and DOCX files.');
        }

        $sourcePath = Storage::disk('public')->path($disk->storage_path);
        $tempDirectory = $this->createTemporaryDirectory('crm-doc-preview-');

        try {
            $docxPath = $this->convertDocToDocx($sourcePath, $tempDirectory);
            $content = @file_get_contents($docxPath);
            if ($content === false) {
                throw new RuntimeException('Unable to read converted DOCX file.');
            }
        } finally {
            $this->deleteDirectory($tempDirectory);
        }

        return response($content, 200, [
            'Content-Type' => $docxMime,
            'Content-Disposition' => sprintf('inline; filename="%s"', str_replace('"', '', $filename)),
            'X-Content-Type-Options' => 'nosniff',
        ]);
    }

    public function buildSpreadsheetPreviewSourceResponse(Disk $disk): HttpResponse|StreamedResponse
    {
        $extension = strtolower(trim((string) $disk->extension));
        $filename = $this->spreadsheetPreviewFilename($disk, $extension);
        $xlsxMime = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        $csvMime = 'text/csv; charset=UTF-8';

        if ($extension === 'xlsx') {
            return Storage::disk('public')->response(
                $disk->storage_path,
                $filename,
                [
                    'Content-Type' => $xlsxMime,
                    'Content-Disposition' => sprintf('inline; filename="%s"', str_replace('"', '', $filename)),
                    'X-Content-Type-Options' => 'nosniff',
                ]
            );
        }

        if ($extension === 'csv') {
            return Storage::disk('public')->response(
                $disk->storage_path,
                $filename,
                [
                    'Content-Type' => $csvMime,
                    'Content-Disposition' => sprintf('inline; filename="%s"', str_replace('"', '', $filename)),
                    'X-Content-Type-Options' => 'nosniff',
                ]
            );
        }

        if ($extension !== 'xls') {
            throw new RuntimeException('Spreadsheet preview is available only for XLSX, XLS and CSV files.');
        }

        $sourcePath = Storage::disk('public')->path($disk->storage_path);
        $tempDirectory = $this->createTemporaryDirectory('crm-xls-preview-');

        try {
            $xlsxPath = $this->convertXlsToXlsx($sourcePath, $tempDirectory);
            $content = @file_get_contents($xlsxPath);
            if ($content === false) {
                throw new RuntimeException('Unable to read converted XLSX file.');
            }
        } finally {
            $this->deleteDirectory($tempDirectory);
        }

        return response($content, 200, [
            'Content-Type' => $xlsxMime,
            'Content-Disposition' => sprintf('inline; filename="%s"', str_replace('"', '', $filename)),
            'X-Content-Type-Options' => 'nosniff',
        ]);
    }

    private function wordPreviewFilename(Disk $disk): string
    {
        $baseName = trim((string) $disk->name);
        if ($baseName === '') {
            $baseName = trim((string) pathinfo((string) $disk->original_name, PATHINFO_FILENAME));
        }

        if ($baseName === '') {
            $baseName = 'document';
        }

        if (! str_ends_with(strtolower($baseName), '.docx')) {
            $baseName .= '.docx';
        }

        return $baseName;
    }

    private function spreadsheetPreviewFilename(Disk $disk, string $sourceExtension): string
    {
        $baseName = trim((string) $disk->name);
        if ($baseName === '') {
            $baseName = trim((string) pathinfo((string) $disk->original_name, PATHINFO_FILENAME));
        }

        if ($baseName === '') {
            $baseName = 'spreadsheet';
        }

        if ($sourceExtension === 'csv') {
            if (! str_ends_with(strtolower($baseName), '.csv')) {
                $baseName .= '.csv';
            }

            return $baseName;
        }

        if (! str_ends_with(strtolower($baseName), '.xlsx')) {
            $baseName .= '.xlsx';
        }

        return $baseName;
    }

    private function renderDocxHtml(Disk $disk): string
    {
        $path = Storage::disk('public')->path($disk->storage_path);

        return $this->renderDocxHtmlFromPath($path);
    }

    private function renderDocHtml(Disk $disk): string
    {
        $sourcePath = Storage::disk('public')->path($disk->storage_path);
        $tempDirectory = $this->createTemporaryDirectory('crm-doc-preview-');

        try {
            $docxPath = $this->convertDocToDocx($sourcePath, $tempDirectory);

            return $this->renderDocxHtmlFromPath($docxPath);
        } finally {
            $this->deleteDirectory($tempDirectory);
        }
    }

    private function renderDocxHtmlFromPath(string $path): string
    {
        $zip = new ZipArchive();
        if ($zip->open($path) !== true) {
            throw new RuntimeException('Unable to open DOCX file for preview.');
        }

        try {
            $documentXml = $zip->getFromName('word/document.xml');
            if (! is_string($documentXml) || trim($documentXml) === '') {
                throw new RuntimeException('DOCX content is invalid.');
            }
        } finally {
            $zip->close();
        }

        $xml = @simplexml_load_string($documentXml);
        if ($xml === false) {
            throw new RuntimeException('DOCX XML parsing failed.');
        }

        $xml->registerXPathNamespace('w', 'http://schemas.openxmlformats.org/wordprocessingml/2006/main');
        $paragraphs = $xml->xpath('//w:body/w:p');
        if (! is_array($paragraphs)) {
            $paragraphs = [];
        }

        $lines = [];
        foreach (array_slice($paragraphs, 0, 1000) as $paragraph) {
            if (! $paragraph instanceof \SimpleXMLElement) {
                continue;
            }

            $textNodes = $paragraph->xpath('.//w:t');
            if (! is_array($textNodes) || $textNodes === []) {
                continue;
            }

            $line = '';
            foreach ($textNodes as $node) {
                $line .= (string) $node;
            }

            $line = trim($line);
            if ($line === '') {
                continue;
            }

            $lines[] = $line;
        }

        if ($lines === []) {
            return '<p class="text-sm text-slate-500">No readable text found in DOCX.</p>';
        }

        $html = '';
        foreach ($lines as $line) {
            $html .= '<p class="mb-3 text-sm leading-6 text-slate-800">'.e($line).'</p>';
        }

        return $html;
    }

    private function convertDocToDocx(string $sourcePath, string $outputDirectory): string
    {
        return $this->convertWithLibreOffice(
            $sourcePath,
            $outputDirectory,
            'docx',
            'DOC preview requires LibreOffice (soffice) on server.',
            'Unable to convert DOC file for preview.'
        );
    }

    private function convertXlsToXlsx(string $sourcePath, string $outputDirectory): string
    {
        return $this->convertWithLibreOffice(
            $sourcePath,
            $outputDirectory,
            'xlsx',
            'XLS preview requires LibreOffice (soffice) on server.',
            'Unable to convert XLS file for preview.'
        );
    }

    private function convertWithLibreOffice(
        string $sourcePath,
        string $outputDirectory,
        string $targetExtension,
        string $missingConverterMessage,
        string $conversionFailedMessage
    ): string
    {
        $converter = $this->resolveOfficeConverterBinary();
        if ($converter === null) {
            throw new RuntimeException($missingConverterMessage);
        }

        $command = sprintf(
            '%s --headless --convert-to %s --outdir %s %s 2>&1',
            escapeshellarg($converter),
            escapeshellarg($targetExtension),
            escapeshellarg($outputDirectory),
            escapeshellarg($sourcePath)
        );

        $output = [];
        $exitCode = 0;
        @exec($command, $output, $exitCode);

        if ($exitCode !== 0) {
            throw new RuntimeException($conversionFailedMessage);
        }

        $expectedPath = rtrim($outputDirectory, DIRECTORY_SEPARATOR)
            .DIRECTORY_SEPARATOR
            .pathinfo($sourcePath, PATHINFO_FILENAME)
            .'.'.$targetExtension;

        if (is_file($expectedPath)) {
            return $expectedPath;
        }

        $matches = glob(rtrim($outputDirectory, DIRECTORY_SEPARATOR).DIRECTORY_SEPARATOR.'*.'.$targetExtension) ?: [];
        if (isset($matches[0]) && is_file($matches[0])) {
            return $matches[0];
        }

        throw new RuntimeException('Converted '.$targetExtension.' file was not created.');
    }

    private function resolveOfficeConverterBinary(): ?string
    {
        $officeConverterPath = trim((string) env('OFFICE_PREVIEW_CONVERTER_PATH', ''));
        if ($officeConverterPath !== '') {
            return $officeConverterPath;
        }

        $configuredBinary = trim((string) env('DOC_PREVIEW_CONVERTER_PATH', ''));
        if ($configuredBinary !== '') {
            return $configuredBinary;
        }

        foreach (['soffice', 'libreoffice'] as $candidate) {
            $output = [];
            $exitCode = 0;
            @exec('command -v '.escapeshellarg($candidate).' 2>/dev/null', $output, $exitCode);

            if ($exitCode === 0 && isset($output[0]) && trim((string) $output[0]) !== '') {
                return trim((string) $output[0]);
            }
        }

        return null;
    }

    private function createTemporaryDirectory(string $prefix): string
    {
        $base = rtrim(sys_get_temp_dir(), DIRECTORY_SEPARATOR).DIRECTORY_SEPARATOR.$prefix.Str::random(12);
        if (! @mkdir($base, 0700, true) && ! is_dir($base)) {
            throw new RuntimeException('Unable to create temporary preview directory.');
        }

        return $base;
    }

    private function deleteDirectory(string $directory): void
    {
        $directory = rtrim($directory, DIRECTORY_SEPARATOR);
        if ($directory === '' || ! is_dir($directory)) {
            return;
        }

        $items = scandir($directory);
        if (! is_array($items)) {
            @rmdir($directory);

            return;
        }

        foreach ($items as $item) {
            if ($item === '.' || $item === '..') {
                continue;
            }

            $itemPath = $directory.DIRECTORY_SEPARATOR.$item;
            if (is_dir($itemPath)) {
                $this->deleteDirectory($itemPath);
            } else {
                @unlink($itemPath);
            }
        }

        @rmdir($directory);
    }

    private function renderCsvHtml(Disk $disk): string
    {
        $path = Storage::disk('public')->path($disk->storage_path);
        $raw = @file_get_contents($path);
        if ($raw === false) {
            throw new RuntimeException('Unable to read CSV file.');
        }

        $rows = preg_split('/\r\n|\r|\n/', $raw) ?: [];
        $rows = array_values(array_filter($rows, static fn (string $line): bool => trim($line) !== ''));
        $totalRows = count($rows);
        $rows = array_slice($rows, 0, self::PREVIEW_MAX_ROWS);

        if ($rows === []) {
            return '<p class="text-sm text-slate-500">CSV is empty.</p>';
        }

        $tableRows = [];
        foreach ($rows as $row) {
            $tableRows[] = str_getcsv($row);
        }

        return $this->renderTableHtml([
            'CSV' => [
                'rows' => $tableRows,
                'total_rows' => $totalRows,
            ],
        ]);
    }

    private function renderXlsxHtml(Disk $disk): string
    {
        $path = Storage::disk('public')->path($disk->storage_path);
        $zip = new ZipArchive();
        if ($zip->open($path) !== true) {
            throw new RuntimeException('Unable to open XLSX file for preview.');
        }

        try {
            $sharedStrings = $this->readSharedStrings($zip);
            $sheetFiles = $this->readSheetTargets($zip);
            if ($sheetFiles === []) {
                throw new RuntimeException('XLSX has no worksheets.');
            }

            $sheets = [];
            foreach (array_slice($sheetFiles, 0, 5, true) as $sheetName => $sheetTarget) {
                $sheetXml = $zip->getFromName($sheetTarget);
                if (! is_string($sheetXml) || trim($sheetXml) === '') {
                    continue;
                }

                $sheetData = $this->parseSheetRows($sheetXml, $sharedStrings);
                if (($sheetData['rows'] ?? []) !== []) {
                    $sheets[$sheetName] = $sheetData;
                }
            }
        } finally {
            $zip->close();
        }

        if ($sheets === []) {
            return '<p class="text-sm text-slate-500">No readable cells found in XLSX.</p>';
        }

        return $this->renderTableHtml($sheets);
    }

    /**
     * @return array<int, string>
     */
    private function readSharedStrings(ZipArchive $zip): array
    {
        $xml = $zip->getFromName('xl/sharedStrings.xml');
        if (! is_string($xml) || trim($xml) === '') {
            return [];
        }

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

        $document->registerXPathNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $nodes = $document->xpath('//x:si');
        if (! is_array($nodes) || $nodes === []) {
            return [];
        }

        $strings = [];
        foreach ($nodes as $node) {
            if (! $node instanceof \SimpleXMLElement) {
                continue;
            }

            $textNodes = $node->xpath('.//*[local-name()="t"]');
            $text = '';
            if (is_array($textNodes)) {
                foreach ($textNodes as $textNode) {
                    $text .= (string) $textNode;
                }
            }
            $strings[] = $text;
        }

        return $strings;
    }

    /**
     * @return array<string, string>
     */
    private function readSheetTargets(ZipArchive $zip): array
    {
        $workbookXml = $zip->getFromName('xl/workbook.xml');
        $relsXml = $zip->getFromName('xl/_rels/workbook.xml.rels');
        if (! is_string($workbookXml) || ! is_string($relsXml)) {
            return [];
        }

        $workbook = @simplexml_load_string($workbookXml);
        $relationships = @simplexml_load_string($relsXml);
        if ($workbook === false || $relationships === false) {
            return [];
        }

        $workbook->registerXPathNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $workbook->registerXPathNamespace('r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
        $relationships->registerXPathNamespace('r', 'http://schemas.openxmlformats.org/package/2006/relationships');

        $relationshipMap = [];
        $relationshipNodes = $relationships->xpath('//r:Relationship');
        if (is_array($relationshipNodes)) {
            foreach ($relationshipNodes as $relationshipNode) {
                if (! $relationshipNode instanceof \SimpleXMLElement) {
                    continue;
                }

                $id = (string) ($relationshipNode['Id'] ?? '');
                $target = (string) ($relationshipNode['Target'] ?? '');
                if ($id === '' || $target === '') {
                    continue;
                }

                $target = ltrim(str_replace('\\', '/', $target), '/');
                if (! str_starts_with($target, 'worksheets/')) {
                    continue;
                }

                $relationshipMap[$id] = 'xl/'.$target;
            }
        }

        $sheetTargets = [];
        $sheetNodes = $workbook->xpath('//x:sheets/x:sheet');
        if (is_array($sheetNodes)) {
            foreach ($sheetNodes as $sheetNode) {
                if (! $sheetNode instanceof \SimpleXMLElement) {
                    continue;
                }

                $sheetName = trim((string) ($sheetNode['name'] ?? ''));
                $relationshipId = trim((string) $sheetNode->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships')->id);
                if ($sheetName === '' || $relationshipId === '' || ! isset($relationshipMap[$relationshipId])) {
                    continue;
                }

                $sheetTargets[$sheetName] = $relationshipMap[$relationshipId];
            }
        }

        return $sheetTargets;
    }

    /**
     * @param  array<int, string>  $sharedStrings
     * @return array{rows:array<int, array<int, string>>, total_rows:int}
     */
    private function parseSheetRows(string $sheetXml, array $sharedStrings): array
    {
        $sheet = @simplexml_load_string($sheetXml);
        if ($sheet === false) {
            return [
                'rows' => [],
                'total_rows' => 0,
            ];
        }

        $rowNodes = $sheet->xpath('//*[local-name()="sheetData"]/*[local-name()="row"]');
        if (! is_array($rowNodes) || $rowNodes === []) {
            return [
                'rows' => [],
                'total_rows' => 0,
            ];
        }

        $totalRows = count($rowNodes);
        $rows = [];
        foreach (array_slice($rowNodes, 0, self::PREVIEW_MAX_ROWS) as $rowNode) {
            if (! $rowNode instanceof \SimpleXMLElement) {
                continue;
            }

            $cells = [];
            $cellNodes = $rowNode->xpath('./*[local-name()="c"]');
            if (! is_array($cellNodes)) {
                $cellNodes = [];
            }

            foreach ($cellNodes as $cellNode) {
                if (! $cellNode instanceof \SimpleXMLElement) {
                    continue;
                }

                $cellReference = strtoupper((string) ($cellNode['r'] ?? ''));
                $columnIndex = $cellReference !== '' ? $this->columnIndexFromCellReference($cellReference) : null;
                if ($columnIndex === null) {
                    $columnIndex = count($cells);
                }

                $type = (string) ($cellNode['t'] ?? '');
                $value = '';

                if ($type === 'inlineStr') {
                    $inlineTextNodes = $cellNode->xpath('./*[local-name()="is"]/*[local-name()="t"]');
                    if (is_array($inlineTextNodes)) {
                        foreach ($inlineTextNodes as $inlineTextNode) {
                            $value .= (string) $inlineTextNode;
                        }
                    }
                } else {
                    $rawValueNodes = $cellNode->xpath('./*[local-name()="v"]');
                    $rawValue = (is_array($rawValueNodes) && isset($rawValueNodes[0])) ? (string) $rawValueNodes[0] : '';

                    if ($type === 's' && $rawValue !== '' && ctype_digit($rawValue)) {
                        $value = $sharedStrings[(int) $rawValue] ?? '';
                    } elseif ($type === 'b') {
                        $value = $rawValue === '1' ? 'TRUE' : 'FALSE';
                    } else {
                        $value = $rawValue;
                    }
                }

                $cells[$columnIndex] = trim($value);
            }

            if ($cells === []) {
                continue;
            }

            ksort($cells);
            $rows[] = $cells;
        }

        return [
            'rows' => $rows,
            'total_rows' => $totalRows,
        ];
    }

    private function columnIndexFromCellReference(string $cellReference): ?int
    {
        if (! preg_match('/^[A-Z]+/', $cellReference, $matches)) {
            return null;
        }

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

        return max(0, $index - 1);
    }

    /**
     * @param  array<string, array{rows:array<int, array<int, string>>, total_rows:int}>  $sheets
     */
    private function renderTableHtml(array $sheets): string
    {
        $html = $this->excelTableStyles();

        foreach ($sheets as $sheetName => $sheetData) {
            $rows = is_array($sheetData['rows'] ?? null) ? $sheetData['rows'] : [];
            $rows = array_slice($rows, 0, self::PREVIEW_MAX_ROWS);
            $totalRows = max(count($rows), (int) ($sheetData['total_rows'] ?? count($rows)));
            $totalColumnIndex = $this->detectMaxColumnIndex($rows, 1000);
            $maxColumnIndex = $this->detectMaxColumnIndex($rows, self::PREVIEW_MAX_COLUMNS);
            if ($maxColumnIndex < 0) {
                continue;
            }

            $visibleRows = count($rows);
            $visibleColumns = $maxColumnIndex + 1;
            $totalColumns = max($visibleColumns, $totalColumnIndex + 1);
            $rowsTruncated = $totalRows > $visibleRows;
            $columnsTruncated = $totalColumns > $visibleColumns;

            $html .= '<section class="crm-excel-sheet">';
            $html .= '<header class="crm-excel-title-row">';
            $html .= '<h3 class="crm-excel-title">'.e($sheetName).'</h3>';
            $html .= '<div class="crm-excel-meta">';
            $html .= '<span class="crm-excel-meta-pill">'.e(__('Rows')).': '.e((string) $visibleRows).' / '.e((string) $totalRows).'</span>';
            $html .= '<span class="crm-excel-meta-pill">'.e(__('Columns')).': '.e((string) $visibleColumns).' / '.e((string) $totalColumns).'</span>';
            if ($rowsTruncated || $columnsTruncated) {
                $html .= '<span class="crm-excel-meta-note">'.e(__('Preview is limited to :rows rows and :columns columns.', [
                    'rows' => self::PREVIEW_MAX_ROWS,
                    'columns' => self::PREVIEW_MAX_COLUMNS,
                ])).'</span>';
            }
            $html .= '</div>';
            $html .= '</header>';
            $html .= '<div class="crm-excel-wrap">';
            $html .= '<table class="crm-excel-table">';
            $html .= '<thead><tr>';
            $html .= '<th class="crm-excel-corner" aria-hidden="true"></th>';
            for ($columnIndex = 0; $columnIndex <= $maxColumnIndex; $columnIndex++) {
                $html .= '<th class="crm-excel-col-head">'.e($this->columnNameFromIndex($columnIndex)).'</th>';
            }
            $html .= '</tr></thead>';
            $html .= '<tbody>';

            foreach ($rows as $rowIndex => $row) {
                if (! is_array($row)) {
                    continue;
                }

                $html .= '<tr>';
                $html .= '<th class="crm-excel-row-head">'.($rowIndex + 1).'</th>';

                for ($columnIndex = 0; $columnIndex <= $maxColumnIndex; $columnIndex++) {
                    $value = trim((string) ($row[$columnIndex] ?? ''));
                    $classes = $this->excelCellClasses($value, $rowIndex === 0);
                    $displayValue = $value === '' ? '&nbsp;' : e($value);
                    $title = $value === '' ? '' : ' title="'.e($value).'"';

                    $html .= '<td class="'.$classes.'"'.$title.'>'.$displayValue.'</td>';
                }
                $html .= '</tr>';
            }

            $html .= '</tbody>';
            $html .= '</table>';
            $html .= '</div>';
            $html .= '</section>';
        }

        return $html;
    }

    /**
     * @param  array<int, array<int, string>>  $rows
     */
    private function detectMaxColumnIndex(array $rows, int $columnLimit = 40): int
    {
        $maxColumnIndex = -1;

        foreach (array_slice($rows, 0, 300) as $row) {
            if (! is_array($row) || $row === []) {
                continue;
            }

            $rowKeys = array_map('intval', array_keys($row));
            $rowMax = max($rowKeys);
            if ($rowMax > $maxColumnIndex) {
                $maxColumnIndex = $rowMax;
            }
        }

        if ($maxColumnIndex < 0) {
            return -1;
        }

        return min($maxColumnIndex, max(0, $columnLimit - 1));
    }

    private function columnNameFromIndex(int $index): string
    {
        if ($index < 0) {
            return '';
        }

        $name = '';
        $index++;

        while ($index > 0) {
            $remainder = ($index - 1) % 26;
            $name = chr(65 + $remainder).$name;
            $index = (int) floor(($index - 1) / 26);
        }

        return $name;
    }

    private function excelCellClasses(string $value, bool $isHeaderRow): string
    {
        $classes = ['crm-excel-cell'];

        if ($isHeaderRow) {
            $classes[] = 'crm-excel-cell-header';
        }

        if ($value === '') {
            $classes[] = 'crm-excel-cell-empty';

            return implode(' ', $classes);
        }

        if (preg_match('/^-?\d+(?:[.,]\d+)?\s*%$/', $value) === 1) {
            $classes[] = 'crm-excel-cell-percent';
            $classes[] = 'crm-excel-cell-number';

            return implode(' ', $classes);
        }

        if ($this->looksLikeDate($value)) {
            $classes[] = 'crm-excel-cell-date';
            $classes[] = 'crm-excel-cell-center';

            return implode(' ', $classes);
        }

        $numericValue = $this->normalizeNumericValue($value);
        if ($numericValue !== null) {
            $classes[] = 'crm-excel-cell-number';
            if ($numericValue > 0) {
                $classes[] = 'crm-excel-cell-positive';
            } elseif ($numericValue < 0) {
                $classes[] = 'crm-excel-cell-negative';
            } else {
                $classes[] = 'crm-excel-cell-zero';
            }

            return implode(' ', $classes);
        }

        if (strcasecmp($value, 'true') === 0 || strcasecmp($value, 'false') === 0) {
            $classes[] = 'crm-excel-cell-bool';
            $classes[] = 'crm-excel-cell-center';
        }

        if ($this->looksLikeUrl($value)) {
            $classes[] = 'crm-excel-cell-link';
        }

        if (strlen($value) >= 80) {
            $classes[] = 'crm-excel-cell-long';
        }

        return implode(' ', $classes);
    }

    private function normalizeNumericValue(string $value): ?float
    {
        $value = preg_replace('/[^\d\.\,\-]+/', '', $value) ?? '';
        if ($value === '' || $value === '-' || $value === '.' || $value === ',') {
            return null;
        }

        if (str_contains($value, ',') && str_contains($value, '.')) {
            $lastComma = strrpos($value, ',');
            $lastDot = strrpos($value, '.');

            if ($lastComma !== false && $lastDot !== false && $lastComma > $lastDot) {
                $value = str_replace('.', '', $value);
                $value = str_replace(',', '.', $value);
            } else {
                $value = str_replace(',', '', $value);
            }
        } elseif (str_contains($value, ',')) {
            if (substr_count($value, ',') > 1) {
                $value = str_replace(',', '', $value);
            } else {
                $value = str_replace(',', '.', $value);
            }
        }

        if (! is_numeric($value)) {
            return null;
        }

        return (float) $value;
    }

    private function looksLikeDate(string $value): bool
    {
        return preg_match('/^\d{4}[.\-\/]\d{1,2}[.\-\/]\d{1,2}$/', $value) === 1
            || preg_match('/^\d{1,2}[.\-\/]\d{1,2}[.\-\/]\d{2,4}$/', $value) === 1;
    }

    private function looksLikeUrl(string $value): bool
    {
        return preg_match('#^https?://[^\s]+$#i', $value) === 1;
    }

    private function excelTableStyles(): string
    {
        return <<<'HTML'
<style>
    .crm-excel-sheet {
        margin-bottom: 1.25rem;
        border: 1px solid #dbe4f0;
        border-radius: 0.75rem;
        background: #f8fafc;
        padding: 0.5rem;
    }
    .crm-excel-title-row {
        display: flex;
        align-items: center;
        justify-content: space-between;
        gap: 0.75rem;
        margin: 0 0 0.55rem;
        flex-wrap: wrap;
    }
    .crm-excel-title {
        margin: 0;
        font-size: 0.8125rem;
        font-weight: 700;
        color: #1f2937;
        letter-spacing: 0.02em;
    }
    .crm-excel-meta {
        display: inline-flex;
        gap: 0.4rem;
        align-items: center;
        flex-wrap: wrap;
    }
    .crm-excel-meta-pill {
        display: inline-flex;
        align-items: center;
        border-radius: 9999px;
        border: 1px solid #dbe4f0;
        background: #ffffff;
        color: #334155;
        padding: 0.2rem 0.55rem;
        font-size: 0.69rem;
        font-weight: 600;
        letter-spacing: 0.02em;
    }
    .crm-excel-meta-note {
        color: #64748b;
        font-size: 0.69rem;
    }
    .crm-excel-wrap {
        overflow: auto;
        max-height: min(74vh, 52rem);
        border: 1px solid #cbd5e1;
        border-radius: 0.625rem;
        background: #ffffff;
        box-shadow: inset 0 1px 0 rgba(255, 255, 255, 0.8), 0 1px 2px rgba(15, 23, 42, 0.05);
    }
    .crm-excel-table {
        border-collapse: separate;
        border-spacing: 0;
        min-width: max-content;
        width: max-content;
        font-family: "Segoe UI", "Helvetica Neue", Arial, sans-serif;
        font-size: 0.8125rem;
        color: #0f172a;
    }
    .crm-excel-corner,
    .crm-excel-col-head,
    .crm-excel-row-head,
    .crm-excel-cell {
        border-right: 1px solid #dbe4f0;
        border-bottom: 1px solid #dbe4f0;
    }
    .crm-excel-corner {
        position: sticky;
        top: 0;
        left: 0;
        z-index: 3;
        min-width: 2.8rem;
        width: 2.8rem;
        background: linear-gradient(180deg, #eef3fb 0%, #e4eaf5 100%);
        box-shadow: 1px 0 0 #dbe4f0, 0 1px 0 #dbe4f0;
    }
    .crm-excel-col-head {
        position: sticky;
        top: 0;
        z-index: 2;
        min-width: 9rem;
        padding: 0.45rem 0.55rem;
        text-align: center;
        font-weight: 700;
        color: #334155;
        background: linear-gradient(180deg, #eef3fb 0%, #e4eaf5 100%);
        text-transform: uppercase;
        box-shadow: inset 0 -1px 0 #dbe4f0;
    }
    .crm-excel-row-head {
        position: sticky;
        left: 0;
        z-index: 1;
        min-width: 2.8rem;
        width: 2.8rem;
        padding: 0.4rem 0.5rem;
        text-align: center;
        font-weight: 600;
        color: #64748b;
        background: #f8fafc;
        box-shadow: inset -1px 0 0 #dbe4f0;
    }
    .crm-excel-cell {
        min-width: 9rem;
        max-width: 20rem;
        padding: 0.42rem 0.55rem;
        background: #ffffff;
        line-height: 1.35;
        white-space: pre-wrap;
        overflow-wrap: anywhere;
        vertical-align: top;
    }
    .crm-excel-cell-empty { background: #ffffff; }
    .crm-excel-cell-number {
        text-align: right;
        font-variant-numeric: tabular-nums;
    }
    .crm-excel-cell-center { text-align: center; }
    .crm-excel-cell-header {
        font-weight: 600;
        background: #f8fafc;
        color: #0f172a;
    }
    .crm-excel-cell-positive {
        background: linear-gradient(90deg, #ecfdf3 0%, #f7fff9 100%);
        color: #166534;
        font-variant-numeric: tabular-nums;
    }
    .crm-excel-cell-negative {
        background: linear-gradient(90deg, #fef2f2 0%, #fff7f7 100%);
        color: #b91c1c;
        font-variant-numeric: tabular-nums;
    }
    .crm-excel-cell-zero {
        background: #f8fafc;
        color: #334155;
        font-variant-numeric: tabular-nums;
    }
    .crm-excel-cell-percent {
        background: linear-gradient(90deg, #eff6ff 0%, #f8fbff 100%);
        color: #1d4ed8;
        font-variant-numeric: tabular-nums;
    }
    .crm-excel-cell-date {
        background: linear-gradient(90deg, #fffbeb 0%, #fffef3 100%);
        color: #92400e;
        font-variant-numeric: tabular-nums;
    }
    .crm-excel-cell-bool {
        background: #f1f5f9;
        color: #0f172a;
        font-weight: 600;
        text-transform: uppercase;
        letter-spacing: 0.02em;
    }
    .crm-excel-cell-link {
        color: #1d4ed8;
        text-decoration: underline;
        text-underline-offset: 2px;
    }
    .crm-excel-cell-long { line-height: 1.45; }
    .crm-excel-table tbody tr:nth-child(even) .crm-excel-cell:not(.crm-excel-cell-positive):not(.crm-excel-cell-negative):not(.crm-excel-cell-percent):not(.crm-excel-cell-date):not(.crm-excel-cell-header):not(.crm-excel-cell-bool) {
        background: #fcfdff;
    }
    .crm-excel-table tbody tr:hover .crm-excel-cell:not(.crm-excel-cell-positive):not(.crm-excel-cell-negative):not(.crm-excel-cell-percent):not(.crm-excel-cell-date):not(.crm-excel-cell-header):not(.crm-excel-cell-bool) {
        background: #f8fbff;
    }
</style>
HTML;
    }
}
