<?php

namespace Database\Seeders;

use Database\Seeders\Concerns\ReadsObservatorioOds;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use RuntimeException;

/**
 * Popula o banco "observaangra" (conexão mysql_observatorio) a partir dos
 * arquivos .ods exportados do antigo sistema.
 *
 * Executa: php artisan db:seed --class=Database\\Seeders\\ObservatorioSeeder
 * É idempotente: limpa e recarrega apenas os dados de indicadores.
 */
class ObservatorioSeeder extends Seeder
{
    use ReadsObservatorioOds;

    private const CONNECTION = 'mysql_observatorio';

    /** Quantos indicadores marcar como destaque na home (como no site antigo). */
    private const QTD_DESTAQUES = 4;

    /** Mapeia seções por ID numérico existente em OUTROS.ods. */
    private const ID_LOOKUP_MAP = [
        'DIRETRIZES' => 'observa_diretrizes',
        'EIXOS' => 'observa_eixos',
        'PROGRAMAS' => 'observa_programas',
    ];

    /** Nomes de órgãos legados que não aparecem no mapeamento estrutural atual. */
    private const ORGAOS_LEGADOS = [
        32 => 'Planejamento e Parcerias',
        63 => 'Secretaria-Executiva de Recursos Humanos',
        558 => 'Fundação Hospital Municipal da Japuíba - FHMJ',
        669 => 'Instituto de Previdência Social de Angra dos Reis - AngraPrev',
    ];

    public function run(): void
    {
        $dataDir = database_path('seeders/data/observatorio');
        $outros = $this->readOds($dataDir . DIRECTORY_SEPARATOR . 'OUTROS.ods');
        $indicadores = $this->readOds($dataDir . DIRECTORY_SEPARATOR . 'OBSERVA_INDICADORES.ods');

        $db = DB::connection(self::CONNECTION);
        $now = now();
        $indicadorColumns = array_flip(Schema::connection(self::CONNECTION)->getColumnListing('observa_indicadores'));

        Schema::connection(self::CONNECTION)->disableForeignKeyConstraints();

        foreach ([
            'observa_indicadores', 'observa_temas',
            'observa_bases', 'observa_diretrizes', 'observa_eixos', 'observa_fontes',
            'observa_orgaos', 'observa_periodicidades', 'observa_programas',
        ] as $table) {
            $db->table($table)->truncate();
        }

        $sections = $this->splitSections($outros['Planilha1'] ?? []);
        $validIds = [];

        foreach (self::ID_LOOKUP_MAP as $section => $table) {
            $rows = [];
            foreach ($sections[$section] ?? [] as $row) {
                $id = (int) ($row[0] ?? 0);
                if ($id <= 0) {
                    continue;
                }
                $rows[] = ['id' => $id, 'descricao' => trim((string) ($row[1] ?? '')), 'created_at' => $now, 'updated_at' => $now];
                $validIds[$table][$id] = true;
            }
            if ($rows) {
                $db->table($table)->insert($rows);
            }
            $this->command?->info(sprintf('  %-22s %4d registros', $table, count($rows)));
        }

        // Temas: a descrição da planilha vira o "nome"; cor/ícone ficam para o admin.
        $temas = [];
        foreach ($sections['TEMAS'] ?? [] as $row) {
            $id = (int) ($row[0] ?? 0);
            if ($id <= 0) {
                continue;
            }
            $temas[] = [
                'id' => $id,
                'nome' => trim((string) ($row[1] ?? '')),
                'descricao' => null,
                'cor' => null,
                'icone' => null,
                'status' => 'ATIVADO',
                'created_at' => $now,
                'updated_at' => $now,
            ];
            $validIds['observa_temas'][$id] = true;
        }
        if ($temas) {
            $db->table('observa_temas')->insert($temas);
        }
        $this->command?->info(sprintf('  %-22s %4d registros', 'observa_temas', count($temas)));

        $sheet = $indicadores['Planilha1'] ?? [];
        $col = $this->headerColumns(array_shift($sheet) ?? []);

        $textLookups = [
            'base_id' => $this->seedTextLookup($db, 'observa_bases', $sections['BASES'] ?? [], $sheet, $col, 'indi_base_geografica', $now, $validIds),
            'fonte_id' => $this->seedTextLookup($db, 'observa_fontes', $sections['FONTES'] ?? [], $sheet, $col, 'indi_fontes', $now, $validIds),
            'periodicidade_id' => $this->seedTextLookup($db, 'observa_periodicidades', $sections['PERIODICIDADE'] ?? [], $sheet, $col, 'indi_periodicidade', $now, $validIds),
        ];

        $this->seedOrgaos($db, $sections['ORGAOS'] ?? [], $sheet, $col, $now, $validIds);

        $idFkColumns = [
            'tema_id' => ['indi_id_tema', 'observa_temas'],
            'diretriz_id' => ['indi_id_diretriz', 'observa_diretrizes'],
            'eixo_id' => ['indi_id_eixo', 'observa_eixos'],
            'orgao_id' => ['indi_id_orgao', 'observa_orgaos'],
            'programa_id' => ['indi_id_programa', 'observa_programas'],
        ];

        $registros = [];
        foreach ($sheet as $row) {
            $id = (int) ($this->cell($row, $col, 'id_indicador'));
            if ($id <= 0) {
                continue;
            }

            $notaTecnica = $this->nullable($this->cell($row, $col, 'indi_nota_tecnica'));
            $observacao = $this->nullable($this->cell($row, $col, 'indi_observacao'));

            $record = [
                'id' => $id,
                'nome' => trim((string) $this->cell($row, $col, 'indi_nome')),
                'observatorio' => $this->toBool($this->cell($row, $col, 'indi_observatorio')),
                'ppa' => $this->toBool($this->cell($row, $col, 'indi_ppa')),
                'memoria_calculo' => $observacao ?: $notaTecnica,
                'proxima_apuracao' => null,
                'planilha_link' => $this->nullable($this->cell($row, $col, 'indi_planilha')),
                'grafico_link_1' => $this->normalizeEmbed($this->cell($row, $col, 'indi_grafico1')),
                'grafico_link_2' => $this->normalizeEmbed($this->cell($row, $col, 'indi_grafico2')),
                'grafico_link_3' => $this->normalizeEmbed($this->cell($row, $col, 'indi_grafico3')),
                'grafico_link_4' => $this->normalizeEmbed($this->cell($row, $col, 'indi_grafico4')),
                'grafico_link_5' => $this->normalizeEmbed($this->cell($row, $col, 'indi_grafico5')),
                'grafico_embed' => null,
                'destaque' => false,
                'status' => 'ATIVADO',
                'created_at' => $now,
                'updated_at' => $now,
            ];

            foreach ([
                'responsavel' => $this->nullable($this->cell($row, $col, 'indi_responsavel')),
                'nota_tecnica' => $notaTecnica,
                'observacao' => $observacao,
            ] as $field => $value) {
                if (isset($indicadorColumns[$field])) {
                    $record[$field] = $value;
                }
            }

            foreach ($idFkColumns as $field => [$source, $lookupTable]) {
                $value = (int) $this->cell($row, $col, $source);
                $record[$field] = ($value > 0 && isset($validIds[$lookupTable][$value])) ? $value : null;
            }

            foreach ($textLookups as $field => $lookup) {
                $record[$field] = $lookup[$this->descriptionKey($this->cell($row, $col, $this->sourceForTextField($field)))] ?? null;
            }

            $registros[] = $record;
        }

        $destacados = 0;
        foreach ($registros as &$registro) {
            if ($destacados >= self::QTD_DESTAQUES) {
                break;
            }
            if ($registro['observatorio']) {
                $registro['destaque'] = true;
                $destacados++;
            }
        }
        unset($registro);

        foreach (array_chunk($registros, 100) as $chunk) {
            $db->table('observa_indicadores')->insert($chunk);
        }
        $this->command?->info(sprintf('  %-22s %4d registros', 'observa_indicadores', count($registros)));

        Schema::connection(self::CONNECTION)->enableForeignKeyConstraints();
    }

    /**
     * Divide as linhas de OUTROS.ods em seções (cada bloco começa com um
     * título em uma única célula, seguido de "id/descricao" e dos dados).
     */
    private function splitSections(array $rows): array
    {
        $sections = [];
        $current = null;

        foreach ($rows as $row) {
            $nonEmpty = array_values(array_filter($row, fn ($c) => trim((string) $c) !== ''));

            if (count($nonEmpty) === 1) {
                $current = strtoupper(trim($nonEmpty[0]));
                $sections[$current] = [];
                continue;
            }

            if ($current === null || count($nonEmpty) < 2) {
                continue;
            }

            // Pula a linha de cabeçalho "id / descricao".
            if (strtolower(trim($row[0])) === 'id') {
                continue;
            }

            if (is_numeric(trim($row[0]))) {
                $sections[$current][] = $row;
            }
        }

        return $sections;
    }

    /**
     * @return array<string, int>
     */
    private function headerColumns(array $header): array
    {
        $col = array_flip(array_map('trim', $header));
        $required = [
            'id_indicador', 'indi_nome', 'indi_id_tema', 'indi_id_diretriz', 'indi_id_eixo',
            'indi_id_programa', 'indi_id_orgao', 'indi_observatorio', 'indi_ppa',
            'indi_base_geografica', 'indi_periodicidade', 'indi_nota_tecnica', 'indi_fontes',
            'indi_observacao', 'indi_planilha', 'indi_grafico1', 'indi_grafico2',
            'indi_grafico3', 'indi_grafico4', 'indi_grafico5',
        ];

        foreach ($required as $column) {
            if (!array_key_exists($column, $col)) {
                throw new RuntimeException("Coluna obrigatória ausente em OBSERVA_INDICADORES.ods: {$column}");
            }
        }

        return $col;
    }

    /**
     * Popula domínios textuais usando os valores reais da planilha correta.
     *
     * @return array<string, int>
     */
    private function seedTextLookup($db, string $table, array $legacyRows, array $sheet, array $col, string $source, $now, array &$validIds): array
    {
        $rows = [];
        $byDescription = [];
        $maxId = 0;

        foreach ($legacyRows as $legacyRow) {
            $id = (int) ($legacyRow[0] ?? 0);
            $descricao = trim((string) ($legacyRow[1] ?? ''));
            if ($id <= 0 || $descricao === '') {
                continue;
            }

            $rows[$id] = ['id' => $id, 'descricao' => $descricao, 'created_at' => $now, 'updated_at' => $now];
            $byDescription[$this->descriptionKey($descricao)] = $id;
            $validIds[$table][$id] = true;
            $maxId = max($maxId, $id);
        }

        foreach ($sheet as $row) {
            if ((int) ($this->cell($row, $col, 'id_indicador')) <= 0) {
                continue;
            }

            $descricao = $this->nullable($this->cell($row, $col, $source));
            if ($descricao === null) {
                continue;
            }

            $key = $this->descriptionKey($descricao);
            if (isset($byDescription[$key])) {
                continue;
            }

            $id = ++$maxId;
            $rows[$id] = ['id' => $id, 'descricao' => $descricao, 'created_at' => $now, 'updated_at' => $now];
            $byDescription[$key] = $id;
            $validIds[$table][$id] = true;
        }

        ksort($rows);
        if ($rows) {
            $db->table($table)->insert(array_values($rows));
        }

        $this->command?->info(sprintf('  %-22s %4d registros', $table, count($rows)));

        return $byDescription;
    }

    private function seedOrgaos($db, array $legacyRows, array $sheet, array $col, $now, array &$validIds): void
    {
        $legacyDescriptions = [];
        foreach ($legacyRows as $legacyRow) {
            $id = (int) ($legacyRow[0] ?? 0);
            if ($id > 0) {
                $legacyDescriptions[$id] = trim((string) ($legacyRow[1] ?? ''));
            }
        }

        $ids = [];
        foreach ($sheet as $row) {
            $id = (int) $this->cell($row, $col, 'indi_id_orgao');
            if ($id > 0) {
                $ids[$id] = true;
            }
        }
        ksort($ids);

        $rows = [];
        foreach (array_keys($ids) as $id) {
            $rows[] = [
                'id' => $id,
                'descricao' => $this->orgaoDescricao($id, $legacyDescriptions),
                'created_at' => $now,
                'updated_at' => $now,
            ];
            $validIds['observa_orgaos'][$id] = true;
        }

        if ($rows) {
            $db->table('observa_orgaos')->insert($rows);
        }

        $this->command?->info(sprintf('  %-22s %4d registros', 'observa_orgaos', count($rows)));
    }

    private function orgaoDescricao(int $id, array $legacyDescriptions): string
    {
        if (!empty($legacyDescriptions[$id])) {
            return $legacyDescriptions[$id];
        }

        if (isset(self::ORGAOS_LEGADOS[$id])) {
            return self::ORGAOS_LEGADOS[$id];
        }

        $structureId = config("legacy_orgaos_mapping.orgao_mapping.{$id}");
        if ($structureId) {
            $structure = DB::table('structures')
                ->where('id', $structureId)
                ->first(['name', 'abbreviation']);

            if ($structure) {
                return $structure->abbreviation
                    ? "{$structure->abbreviation} - {$structure->name}"
                    : $structure->name;
            }
        }

        return "Órgão legado {$id}";
    }

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

        $trimmed = trim($value);
        if (stripos($trimmed, '<iframe') === 0) {
            return $trimmed;
        }

        if (stripos($trimmed, 'src=') !== false) {
            return "<iframe {$trimmed}></iframe>";
        }

        return $trimmed;
    }

    private function descriptionKey(string $value): string
    {
        return mb_strtolower(preg_replace('/\s+/', ' ', trim($value)));
    }

    private function sourceForTextField(string $field): string
    {
        return [
            'base_id' => 'indi_base_geografica',
            'fonte_id' => 'indi_fontes',
            'periodicidade_id' => 'indi_periodicidade',
        ][$field];
    }
}