<?php

declare(strict_types=1);

namespace CpCreation\VitiCore\User\Import;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

final class ClientImportAnalyzer
{
    private const MAX_COLUMNS = 200;
    private const MAX_DATA_ROWS = 10000;
    private const HEADER_SCAN_ROWS = 10;

    public function analyze(string $filePath): ClientImportAnalysis
    {
        if (!is_file($filePath) || !is_readable($filePath)) {
            throw new ClientImportException('admin_client_import.error.unreadable_file');
        }

        try {
            $reader = IOFactory::createReaderForFile($filePath);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath);
        } catch (\Throwable $exception) {
            throw new ClientImportException('admin_client_import.error.invalid_file', previous: $exception);
        }

        try {
            $sheet = $spreadsheet->getSheet(0);
            $highestRow = $sheet->getHighestDataRow();
            $highestColumnIndex = Coordinate::columnIndexFromString($sheet->getHighestDataColumn());

            if ($highestColumnIndex > self::MAX_COLUMNS) {
                throw new ClientImportException('admin_client_import.error.too_many_columns');
            }

            [$headerLine, $emailColumnIndex, $groupColumnIndex] = $this->detectColumns(
                $sheet,
                min($highestRow, self::HEADER_SCAN_ROWS),
                $highestColumnIndex,
            );

            if ($highestRow - $headerLine > self::MAX_DATA_ROWS) {
                throw new ClientImportException('admin_client_import.error.too_many_rows');
            }

            $sourceGroups = [];
            $rows = [];
            $totalRows = 0;
            $invalidEmailRows = 0;
            $missingGroupRows = 0;

            for ($line = $headerLine + 1; $line <= $highestRow; ++$line) {
                $email = $this->normalizeEmail($this->cellValue($sheet, $emailColumnIndex, $line));
                $sourceGroup = $this->normalizeCellValue($this->cellValue($sheet, $groupColumnIndex, $line));

                if ('' === $email && '' === $sourceGroup) {
                    continue;
                }

                ++$totalRows;

                if ('' === $sourceGroup) {
                    ++$missingGroupRows;

                    continue;
                }

                $sourceGroups[$sourceGroup] = true;

                if (!$this->isValidEmail($email)) {
                    ++$invalidEmailRows;

                    continue;
                }

                $rows[] = new ClientImportRow($line, $email, $sourceGroup);
            }

            if ([] === $rows) {
                throw new ClientImportException('admin_client_import.error.no_importable_rows');
            }

            $groupNames = array_keys($sourceGroups);
            natcasesort($groupNames);

            return new ClientImportAnalysis(
                $headerLine,
                Coordinate::stringFromColumnIndex($emailColumnIndex),
                $this->normalizeCellValue($this->cellValue($sheet, $emailColumnIndex, $headerLine)),
                Coordinate::stringFromColumnIndex($groupColumnIndex),
                $this->normalizeCellValue($this->cellValue($sheet, $groupColumnIndex, $headerLine)),
                $rows,
                array_values($groupNames),
                $totalRows,
                $invalidEmailRows,
                $missingGroupRows,
            );
        } finally {
            $spreadsheet->disconnectWorksheets();
        }
    }

    /**
     * @return array{int, int, int}
     */
    private function detectColumns(Worksheet $sheet, int $rowsToScan, int $highestColumnIndex): array
    {
        $bestMatch = null;
        $bestScore = -1;

        for ($line = 1; $line <= $rowsToScan; ++$line) {
            $emailColumn = null;
            $emailScore = 0;
            $groupColumn = null;
            $groupScore = 0;

            for ($column = 1; $column <= $highestColumnIndex; ++$column) {
                $header = $this->normalizeHeader($this->cellValue($sheet, $column, $line));
                $candidateEmailScore = $this->emailHeaderScore($header);
                $candidateGroupScore = $this->groupHeaderScore($header);

                if ($candidateEmailScore > $emailScore) {
                    $emailColumn = $column;
                    $emailScore = $candidateEmailScore;
                }

                if ($candidateGroupScore > $groupScore) {
                    $groupColumn = $column;
                    $groupScore = $candidateGroupScore;
                }
            }

            if (null === $emailColumn || null === $groupColumn || $emailColumn === $groupColumn) {
                continue;
            }

            $score = $emailScore + $groupScore;
            if ($score > $bestScore) {
                $bestMatch = [$line, $emailColumn, $groupColumn];
                $bestScore = $score;
            }
        }

        if (null === $bestMatch) {
            throw new ClientImportException('admin_client_import.error.columns_not_detected');
        }

        return $bestMatch;
    }

    private function emailHeaderScore(string $header): int
    {
        return match ($header) {
            'email', 'e mail', 'courriel', 'mail' => 4,
            'adresse email', 'adresse e mail', 'adresse courriel', 'email client', 'courriel client' => 3,
            default => str_contains($header, 'email') || str_contains($header, 'courriel') ? 1 : 0,
        };
    }

    private function groupHeaderScore(string $header): int
    {
        return match ($header) {
            'groupe', 'group' => 4,
            'groupe client', 'groupe partenaire', 'groupe de prix', 'customer group', 'price group' => 3,
            default => str_contains($header, 'groupe') || str_contains($header, 'group') ? 1 : 0,
        };
    }

    private function normalizeHeader(string $value): string
    {
        $value = mb_strtolower($this->normalizeCellValue($value));
        $normalized = \Normalizer::normalize($value, \Normalizer::FORM_D);

        if (false !== $normalized) {
            $value = preg_replace('/\p{Mn}+/u', '', $normalized) ?? $value;
        }

        $value = preg_replace('/[^a-z0-9]+/u', ' ', $value) ?? $value;

        return trim(preg_replace('/\s+/u', ' ', $value) ?? $value);
    }

    private function normalizeEmail(string $value): string
    {
        return mb_strtolower($this->normalizeCellValue($value));
    }

    private function normalizeCellValue(string $value): string
    {
        return trim($value, " \t\n\r\0\x0B\xEF\xBB\xBF");
    }

    private function isValidEmail(string $email): bool
    {
        return '' !== $email
            && mb_strlen($email) <= 60
            && false !== filter_var($email, FILTER_VALIDATE_EMAIL);
    }

    private function cellValue(Worksheet $sheet, int $column, int $line): string
    {
        $value = $sheet->getCell(Coordinate::stringFromColumnIndex($column) . $line)->getValue();

        if ($value instanceof RichText) {
            return $value->getPlainText();
        }

        if (is_scalar($value) || $value instanceof \Stringable) {
            return (string) $value;
        }

        return '';
    }
}
