<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class CartaServicosImportSeeder extends Seeder
{
    private const CSV_SERVICOS_PATH = 'temp/carta-de-servicos/CARTAS.csv';

    private const CSV_TIPOS_PATH = 'temp/carta-de-servicos/TIPOS.csv';

    private const BATCH_SIZE = 300;

    private const SECRETARIA_MIN_SCORE = 35;

    public function run(): void
    {
        $this->command->info('===========================================');
        $this->command->info('Importando Carta de Servicos (TIPOS + SERVICOS)...');
        $this->command->info('===========================================');

        $this->validateFiles();

        DB::beginTransaction();

        try {
            $this->command->info('Limpando tabelas de destino...');
            DB::table('carta_servicos')->delete();
            DB::table('carta_servicos_tipos')->delete();

            $importedTipos = $this->importTipos();

            $validCategoriaIds = DB::table('carta_servicos_tipos')
                ->pluck('id_tipo')
                ->map(static fn ($id): int => (int) $id)
                ->all();
            $validCategoriaIds = array_fill_keys($validCategoriaIds, true);

            $rows = iterator_to_array($this->readCsv(self::CSV_SERVICOS_PATH), false);
            $legacySecretariaMap = $this->buildLegacySecretariaMap($rows);

            $batch = [];
            $imported = 0;
            $now = now();

            foreach ($rows as $row) {
                $titulo = $this->nullableString($row['cds_titulo'] ?? null);
                if ($titulo === null) {
                    continue;
                }

                $batch[] = [
                    'cds_secretaria' => $this->resolveSecretariaUuid($row, $legacySecretariaMap),
                    'categoria_principal_id' => $this->extractCategoriaPrincipalId($row['cds_tipo'] ?? null, $validCategoriaIds),
                    'cds_setor' => $this->nullableString($row['cds_setor'] ?? null),
                    'cds_entrada' => $this->nullableString($row['cds_entrada'] ?? null),
                    'cds_titulo' => Str::limit($titulo, 255, ''),
                    'cds_tipo' => $this->nullableString($row['cds_tipo'] ?? null),
                    'cds_descricao' => $this->nullableString($row['cds_descricao'] ?? null),
                    'cds_telefone' => Str::limit((string) ($this->nullableString($row['cds_telefone'] ?? null) ?? ''), 120, '') ?: null,
                    'cds_email' => $this->normalizeEmailForStorage($row['cds_email'] ?? null),
                    'cds_quem' => $this->nullableString($row['cds_quem'] ?? null),
                    'cds_quando' => $this->nullableString($row['cds_quando'] ?? null),
                    'cds_atendimento' => $this->nullableString($row['cds_atendimento'] ?? null),
                    'cds_requisitos' => $this->nullableString($row['cds_requisitos'] ?? null),
                    'cds_prazo' => $this->nullableString($row['cds_prazo'] ?? null),
                    'cds_taxas' => $this->nullableString($row['cds_taxas'] ?? null),
                    'cds_legislacao' => $this->nullableString($row['cds_legislacao'] ?? null),
                    'cds_contato' => $this->nullableString($row['cds_contato'] ?? null),
                    'cds_etapas' => $this->nullableString($row['cds_etapas'] ?? null),
                    'cds_obs' => $this->nullableString($row['cds_obs'] ?? null),
                    'cds_solicitacao' => $this->normalizeUrlForStorage($row['cds_solicitacao'] ?? null, 500),
                    'cds_status' => $this->normalizeStatus($row['cds_status'] ?? null),
                    'cds_mapa' => $this->normalizeUrlForStorage($row['cds_mapa'] ?? null, 1000),
                    'created_at' => $now,
                    'updated_at' => $now,
                ];

                if (count($batch) >= self::BATCH_SIZE) {
                    DB::table('carta_servicos')->insert($batch);
                    $imported += count($batch);
                    $batch = [];
                }
            }

            if ($batch !== []) {
                DB::table('carta_servicos')->insert($batch);
                $imported += count($batch);
            }

            DB::commit();
            $mappedSecretarias = count(array_filter($legacySecretariaMap));
            $this->command->info("Tipos importados: {$importedTipos}");
            $this->command->info("Mapeamento de secretarias legado aplicado: {$mappedSecretarias}");
            $this->command->info("Importacao finalizada. Registros inseridos: {$imported}");
        } catch (\Throwable $e) {
            DB::rollBack();
            $this->command->error('Falha na importacao da Carta de Servicos: ' . $e->getMessage());
            throw $e;
        }
    }

    private function validateFiles(): void
    {
        foreach ([self::CSV_TIPOS_PATH, self::CSV_SERVICOS_PATH] as $file) {
            if (!file_exists(base_path($file))) {
                throw new \RuntimeException('Arquivo CSV nao encontrado em: ' . $file);
            }
        }
    }

    private function importTipos(): int
    {
        $rows = [];
        $now = now();

        foreach ($this->readCsv(self::CSV_TIPOS_PATH) as $row) {
            $id = (int) preg_replace('/\D+/', '', (string) ($row['id_tipo'] ?? '0'));
            if ($id <= 0) {
                continue;
            }

            $descricao = $this->nullableString($row['tip_descricao'] ?? null);
            if ($descricao === null) {
                continue;
            }

            $status = strtoupper(trim((string) ($row['tip_status'] ?? 'A')));
            if (!in_array($status, ['A', 'I'], true)) {
                $status = 'A';
            }

            $rows[] = [
                'id_tipo' => $id,
                'tip_descricao' => Str::limit($descricao, 255, ''),
                'tip_class' => Str::limit((string) ($this->nullableString($row['tip_class'] ?? null) ?? 'fa-circle'), 100, ''),
                'tip_status' => $status,
                'created_at' => $now,
                'updated_at' => $now,
            ];
        }

        if ($rows === []) {
            return 0;
        }

        DB::table('carta_servicos_tipos')->insert($rows);
        return count($rows);
    }

    private function readCsv(string $relativePath): \Generator
    {
        $path = base_path($relativePath);
        $handle = fopen($path, 'r');

        if ($handle === false) {
            throw new \RuntimeException('Nao foi possivel abrir o arquivo: ' . $relativePath);
        }

        try {
            $header = fgetcsv($handle, separator: ';');
            if ($header === false) {
                return;
            }

            $header = array_map(function ($value) {
                $normalized = $this->normalizeCsvText((string) $value);
                $normalized = preg_replace('/^\xEF\xBB\xBF/', '', $normalized);
                return trim((string) $normalized, " \t\n\r\0\x0B\"");
            }, $header);

            while (($row = fgetcsv($handle, separator: ';')) !== false) {
                if ($this->isEmptyRow($row)) {
                    continue;
                }

                $normalizedRow = [];
                foreach ($header as $index => $column) {
                    $normalizedRow[$column] = isset($row[$index])
                        ? trim($this->normalizeCsvText((string) $row[$index]), " \t\n\r\0\x0B\"")
                        : null;
                }

                yield $normalizedRow;
            }
        } finally {
            fclose($handle);
        }
    }

    private function extractCategoriaPrincipalId(?string $rawTipo, array $validCategoriaIds): ?int
    {
        if ($rawTipo === null || trim($rawTipo) === '') {
            return null;
        }

        preg_match_all('/\d+/', $rawTipo, $matches);
        foreach ($matches[0] as $possibleId) {
            $id = (int) $possibleId;
            if (isset($validCategoriaIds[$id])) {
                return $id;
            }
        }

        return null;
    }

    private function normalizeStatus(?string $value): string
    {
        $status = trim((string) $value);
        if (!in_array($status, ['1', '2', '3'], true)) {
            return '3';
        }

        return $status;
    }

    private function nullableUuid(?string $value): ?string
    {
        $value = trim((string) $value);
        return Str::isUuid($value) ? $value : null;
    }

    private function nullableString(?string $value): ?string
    {
        $value = trim((string) $value);
        return $value !== '' ? $value : null;
    }

    private function isEmptyRow(array $row): bool
    {
        foreach ($row as $value) {
            if (trim((string) $value, " \t\n\r\0\x0B\"") !== '') {
                return false;
            }
        }

        return true;
    }

    private function normalizeCsvText(string $value): string
    {
        if ($value === '') {
            return '';
        }

        if (mb_check_encoding($value, 'UTF-8')) {
            return $value;
        }

        $converted = @mb_convert_encoding($value, 'UTF-8', 'Windows-1252,ISO-8859-1,UTF-8');
        return $converted !== false ? $converted : utf8_encode($value);
    }

    private function normalizeEmailForStorage(?string $value): ?string
    {
        $value = $this->nullableString($value);
        if ($value === null) {
            return null;
        }

        $emails = $this->extractEmails($value);
        if ($emails === []) {
            return null;
        }

        return Str::limit($emails[0], 255, '');
    }

    private function normalizeUrlForStorage(?string $value, int $maxLength): ?string
    {
        $url = $this->nullableString($value);
        if ($url === null) {
            return null;
        }

        $url = trim($url);
        if (!preg_match('/^https?:\/\//i', $url)) {
            return null;
        }

        if (!filter_var($url, FILTER_VALIDATE_URL)) {
            return null;
        }

        return Str::limit($url, $maxLength, '');
    }

    private function resolveSecretariaUuid(array $row, array $legacySecretariaMap): ?string
    {
        $currentValue = $this->nullableUuid($row['cds_secretaria'] ?? null);
        if ($currentValue !== null) {
            return $currentValue;
        }

        $legacyId = preg_replace('/\D+/', '', (string) ($row['cds_secretaria'] ?? ''));
        if ($legacyId === '') {
            return null;
        }

        return $legacySecretariaMap[$legacyId] ?? null;
    }

    private function buildLegacySecretariaMap(array $rows): array
    {
        $structures = DB::table('structures')
            ->select(['id', 'name', 'abbreviation', 'slug', 'email1', 'email2', 'email3'])
            ->get()
            ->map(function ($item) {
                $emails = array_values(array_filter([
                    $this->normalizeEmail($item->email1),
                    $this->normalizeEmail($item->email2),
                    $this->normalizeEmail($item->email3),
                ]));

                return [
                    'id' => (string) $item->id,
                    'tokens' => $this->extractSearchTokens(
                        implode(' ', [
                            (string) $item->name,
                            (string) $item->abbreviation,
                            (string) $item->slug,
                            implode(' ', $emails),
                        ])
                    ),
                    'emails' => $emails,
                ];
            })
            ->all();

        $scoreByLegacy = [];

        foreach ($rows as $row) {
            $legacyId = preg_replace('/\D+/', '', (string) ($row['cds_secretaria'] ?? ''));
            if ($legacyId === '') {
                continue;
            }

            $sourceText = implode(' ', [
                (string) ($row['cds_titulo'] ?? ''),
                (string) ($row['cds_entrada'] ?? ''),
                (string) ($row['cds_email'] ?? ''),
            ]);
            $sourceTokens = array_fill_keys($this->extractSearchTokens($sourceText), true);
            $sourceEmails = $this->extractEmails((string) ($row['cds_email'] ?? ''));

            foreach ($structures as $structure) {
                $score = 0;

                foreach ($sourceEmails as $email) {
                    if (in_array($email, $structure['emails'], true)) {
                        $score += 120;
                    } elseif ($email !== '' && str_contains(implode(' ', $structure['emails']), $email)) {
                        $score += 80;
                    }
                }

                foreach ($structure['tokens'] as $token) {
                    if (isset($sourceTokens[$token])) {
                        $score += strlen($token) >= 8 ? 9 : 5;
                    }
                }

                if ($score > 0) {
                    $scoreByLegacy[$legacyId][$structure['id']] = ($scoreByLegacy[$legacyId][$structure['id']] ?? 0) + $score;
                }
            }
        }

        $resolved = [];
        foreach ($scoreByLegacy as $legacyId => $scoreByStructure) {
            arsort($scoreByStructure);
            $bestStructureId = array_key_first($scoreByStructure);
            $bestScore = $scoreByStructure[$bestStructureId] ?? 0;

            if ($bestScore >= self::SECRETARIA_MIN_SCORE) {
                $resolved[$legacyId] = $bestStructureId;
            }
        }

        return $resolved;
    }

    private function normalizeEmail(?string $value): string
    {
        return strtolower(trim((string) $value));
    }

    private function extractEmails(string $value): array
    {
        preg_match_all('/[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}/i', strtolower($value), $matches);
        return array_values(array_unique($matches[0] ?? []));
    }

    private function extractSearchTokens(string $value): array
    {
        $value = Str::ascii(mb_strtolower($value));
        $parts = preg_split('/[^a-z0-9]+/', $value) ?: [];

        $tokens = [];
        foreach ($parts as $part) {
            $part = trim($part);
            if (strlen($part) >= 4) {
                $tokens[] = $part;
            }
        }

        return array_values(array_unique($tokens));
    }
}