<?php

declare(strict_types=1);

namespace App\Controllers;

use App\Core\Database;
use App\Core\Env;
use App\Core\Auth;
use App\Core\Request;
use App\Core\Response;
use Throwable;

final class MapaController
{
    private array $schemaCache = [];

    public function config(Request $request): void
    {
        $ctx = $this->municipioContext($request);
        $slug = $ctx['slug'];
        $nome = $this->municipioEnv($ctx, 'NOME', $ctx['nome'] ?: ($slug === 'jequie' ? 'Jequié' : ucfirst(str_replace('_', ' ', $slug))));

        $workspace = $this->municipioEnv($ctx, 'GEOSERVER_WORKSPACE', Env::get('GEOSERVER_WORKSPACE', 'sisprom')) ?: 'sisprom';
        $geoserverUrl = rtrim((string)$this->municipioEnv($ctx, 'GEOSERVER_URL', Env::get('GEOSERVER_URL', 'https://sisprom-br.com.br:8443/geoserver')), '/');

        Response::json([
            'ok' => true,
            'municipio' => $slug,
            'municipio_id' => $ctx['id'],
            'schema_name' => $ctx['schema_name'],
            'configWarnings' => $this->layerWarnings($ctx),
            'nome' => $nome,
            'centerLonLat' => [
                (float)$this->municipioEnv($ctx, 'CENTER_LON', Env::get('MAPA_CENTER_LON', '-40.0786')),
                (float)$this->municipioEnv($ctx, 'CENTER_LAT', Env::get('MAPA_CENTER_LAT', '-13.8607')),
            ],
            'initialZoom' => (int)$this->municipioEnv($ctx, 'ZOOM', Env::get('MAPA_INITIAL_ZOOM', '14')),
            'geoserver' => [
                'url' => $geoserverUrl,
                'workspace' => $workspace,
            ],
            'layers' => [
                'ortofoto' => [
                    'type' => 'WMS',
                    'name' => $this->layerEnv($ctx, 'LAYER_ORTOFOTO', 'sisprom:jequie_full_gsd07cm'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_ORTOFOTO_TITLE', 'Ortofoto'),
                    'format' => $this->municipioEnv($ctx, 'LAYER_ORTOFOTO_FORMAT', Env::get('LAYER_ORTOFOTO_FORMAT', 'image/jpeg')),
                    'visible' => true,
                ],
                'lotes' => [
                    'type' => 'WFS',
                    'name' => $this->layerEnv($ctx, 'LAYER_LOTES', 'sisprom:Jequie_2025_postgis'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_LOTES_TITLE', 'Parcelamento Urbano (WFS)'),
                    'visible' => true,
                ],
                'construcoes' => [
                    'type' => 'WFS',
                    'name' => $this->layerEnv($ctx, 'LAYER_CONSTRUCOES', 'sisprom:sisprommap_ctm_construcoes'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_CONSTRUCOES_TITLE', 'Construções'),
                    'visible' => false,
                ],
                'componentes' => [
                    'type' => 'WFS',
                    'name' => $this->layerEnv($ctx, 'LAYER_COMPONENTES', 'sisprom:ctm_componentes_2026'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_COMPONENTES_TITLE', 'Componentes'),
                    'visible' => false,
                ],
                'ruas' => [
                    'type' => 'WMS',
                    'name' => $this->layerEnv($ctx, 'LAYER_RUAS', 'sisprom:ruas_jequie_2023_wgs84'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_RUAS_TITLE', 'Ruas'),
                    'visible' => false,
                ],
                'intersecao' => [
                    'type' => 'WMS',
                    'name' => $this->layerEnv($ctx, 'LAYER_INTERSECAO', 'sisprom:sigef_intersecao'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_INTERSECAO_TITLE', 'Áreas Rurais'),
                    'visible' => false,
                ],
                'perimetro' => [
                    'type' => 'WMS',
                    'name' => $this->layerEnv($ctx, 'LAYER_PERIMETRO', 'sisprom:perimentro_urbano'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_PERIMETRO_TITLE', 'Perímetro Urbano'),
                    'visible' => false,
                ],
                'pavimentos' => [
                    'type' => 'WMS',
                    'name' => $this->layerEnv($ctx, 'LAYER_PAVIMENTOS', 'sisprom:imoveis_jequie'),
                    'title' => $this->municipioEnv($ctx, 'LAYER_PAVIMENTOS_TITLE', 'Imóveis por Pavimentos'),
                    'attribute' => $this->layerEnv($ctx, 'LAYER_PAVIMENTOS_ATTRIBUTE', 'pavimentos'),
                    'values' => array_values(array_filter(array_map('trim', explode(',', (string)$this->layerEnv($ctx, 'LAYER_PAVIMENTOS_VALUES', 'P-0,P-1,P-2,P-3'))))),
                    'visible' => false,
                ],
            ],
            'fotos360' => [
                'schema' => $this->municipioEnv($ctx, 'FOTOS360_DB_SCHEMA', $ctx['schema_name'] ?: 'mun_' . $slug),
                'table' => $this->municipioEnv($ctx, 'FOTOS360_DB_TABLE', 'fotos360'),
                'listUrl' => '/api/v1/mapa/fotos360?municipio=' . urlencode($slug),
                'uploadUrl' => '/api/v1/mapa/fotos360/upload?municipio=' . urlencode($slug),
            ],
            'consulta' => [
                'provider' => $this->municipioEnv($ctx, 'CADASTRO_API_PROVIDER', Env::get('CADASTRO_API_PROVIDER', 'LOCAL_DB')),
                'url' => '/api/v1/mapa/consulta-el?municipio=' . urlencode($slug),
            ],
            'export' => [
                'shpUrl' => '/api/v1/mapa/export/shp?municipio=' . urlencode($slug),
            ],
        ]);
    }

    public function fotos360(Request $request): void
    {
        try {
            $ctx = $this->municipioContext($request);

            $payload = $this->readFotos360FromDatabase($ctx);

            if (is_array($payload)) {
                Response::json($payload);
                return;
            }

            $legacy = $this->readFotos360LegacyEndpoint($ctx);
            if ($legacy !== null) {
                Response::json($legacy);
                return;
            }

            $index = $this->readFotos360Index();

            if (in_array(strtolower((string) Env::get('APP_DEBUG', 'false')), ['1', 'true', 'yes', 'on'], true)) {
                $index['_debug'] = [
                    'source' => 'index',
                    'message' => 'Banco retornou null; usando índice local.',
                    'municipio' => $ctx['slug'] ?? null,
                    'schema_name' => $ctx['schema_name'] ?? null,
                ];
            }

            Response::json($index);
        } catch (Throwable $e) {
            $this->logError('mapa-fotos360', $e);

            $payload = [
                'type' => 'FeatureCollection',
                'features' => [],
            ];

            if (in_array(strtolower((string) Env::get('APP_DEBUG', 'false')), ['1', 'true', 'yes', 'on'], true)) {
                $payload['_debug'] = [
                    'source' => 'exception',
                    'message' => $e->getMessage(),
                    'file' => $e->getFile(),
                    'line' => $e->getLine(),
                ];
            }

            Response::json($payload);
        }
    }

    public function upload360(Request $request): void
    {
        try {
            if (empty($_FILES['file'])) {
                Response::error('Arquivo 360º não enviado no campo file.', 400);
                return;
            }
            $file = $_FILES['file'];
            if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
                Response::error('Falha no upload do arquivo 360º.', 400, $file['error'] ?? null);
                return;
            }
            $lat = (float)($_POST['lat'] ?? $_POST['latitude'] ?? 0);
            $lon = (float)($_POST['lon'] ?? $_POST['lng'] ?? $_POST['longitude'] ?? 0);
            if (!$lat || !$lon) {
                Response::error('Latitude/longitude inválidas para foto 360º.', 400);
                return;
            }
            $id = $this->uuid();
            $ext = strtolower(pathinfo((string)($file['name'] ?? 'foto360.jpg'), PATHINFO_EXTENSION));
            if (!in_array($ext, ['jpg','jpeg','png'], true)) $ext = 'jpg';
            $dir = dirname(__DIR__, 2) . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . 'fotos360';
            if (!is_dir($dir)) @mkdir($dir, 0777, true);
            $filename = $id . '.' . $ext;
            $target = $dir . DIRECTORY_SEPARATOR . $filename;
            if (!move_uploaded_file((string)$file['tmp_name'], $target)) {
                Response::error('Não foi possível salvar o arquivo 360º.', 500);
                return;
            }
            $base = rtrim((string)Env::get('PUBLIC_BASE_URL', 'http://127.0.0.1:8081'), '/');
            $fileUrl = $base . '/api/v1/storage/public/fotos360/' . rawurlencode($filename);
            $feature = [
                'type' => 'Feature',
                'geometry' => ['type' => 'Point', 'coordinates' => [$lon, $lat]],
                'properties' => [
                    'id' => $id,
                    'descricao' => $_POST['descricao'] ?? 'Foto 360º',
                    'heading_deg' => isset($_POST['heading']) ? (float)$_POST['heading'] : null,
                    'capture_at' => $_POST['capture_at'] ?? date(DATE_ATOM),
                    'file_url' => $fileUrl,
                ],
            ];
            $index = $this->readFotos360Index();
            $index['features'][] = $feature;
            $this->writeFotos360Index($index);
            Response::json($feature);
        } catch (Throwable $e) {
            $this->logError('mapa-upload360', $e);
            Response::error('Erro ao enviar foto 360º', 500, Env::bool('APP_DEBUG') ? $e->getMessage() : null);
        }
    }

    public function health(Request $request): void
    {
        Response::json([
            'ok' => true,
            'service' => 'mapa-php',
            'routes' => [
                '/api/v1/mapa/config',
                '/api/v1/mapa/fotos360',
                '/api/v1/mapa/fotos360/upload',
                '/api/v1/mapa/consulta-el?inscricao=01043630188001',
                '/api/v1/mapa/export/shp',
            ],
        ]);
    }

    public function tokenStatus(Request $request): void
    {
        $ctx = $this->municipioContext($request);
        $cache = $this->readGpiTokenCache($ctx);
        $createdAt = (int)($cache['created_at'] ?? 0);
        $ttl = (int)$this->municipioEnv($ctx, 'GPI_TOKEN_TTL', Env::get('GPI_TOKEN_TTL', '900'));
        Response::json([
            'ok' => true,
            'municipio' => $ctx['slug'],
            'has_token' => !empty($cache['token']),
            'token_age_seconds' => $createdAt ? max(0, time() - $createdAt) : null,
            'token_cache_valid' => !empty($cache['token']) && $createdAt && (time() - $createdAt) < $ttl,
        ]);
    }

    public function tokenRefresh(Request $request): void
    {
        try {
            $ctx = $this->municipioContext($request);
            $token = $this->requestGpiToken($ctx, true);
            Response::json([
                'ok' => true,
                'message' => 'Token EL/GPI renovado com sucesso.',
                'token_length' => strlen($token),
            ]);
        } catch (Throwable $e) {
            $this->logError('mapa-token-refresh', $e);
            Response::error('Erro ao renovar token EL/GPI.', 500, Env::bool('APP_DEBUG') ? $e->getMessage() : null);
        }
    }

    public function consultaEl(Request $request): void
    {
        try {
            $ctx = $this->municipioContext($request);
            $body = is_array($request->body) ? $request->body : [];
            $inscricao = trim((string)(
                $request->query['inscricao']
                ?? $request->query['inscricao_municipal']
                ?? $body['inscricao']
                ?? $body['inscricao_municipal']
                ?? ''
            ));

            if ($inscricao === '') {
                Response::error('Inscrição não informada.', 400);
                return;
            }

            $proxyUrl = trim((string)$this->municipioEnv($ctx, 'GPI_PROXY_URL', Env::get('GPI_PROXY_URL', '')));
            $internalKey = trim((string)$this->municipioEnv($ctx, 'INTERNAL_API_KEY', Env::get('INTERNAL_API_KEY', '')));

            if ($proxyUrl !== '' && $internalKey !== '') {
                $payload = $this->consultaElViaProxy($proxyUrl, $internalKey, $inscricao);
                Response::json([
                    'ok' => true,
                    'fonte' => 'GPI/EL',
                    'modo' => 'proxy',
                    'inscricao' => $inscricao,
                    'data' => $payload,
                ]);
                return;
            }

            if (trim((string)$this->municipioEnv($ctx, 'GPI_API_BASE_URL', $this->municipioEnv($ctx, 'GPI_API_BASE', Env::get('GPI_API_BASE_URL', '')))) !== '') {
                $payload = $this->consultaElViaApiDireta($ctx, $inscricao);
                Response::json([
                    'ok' => true,
                    'fonte' => 'GPI/EL',
                    'modo' => 'api-direta',
                    'inscricao' => $inscricao,
                    'data' => $payload,
                ]);
                return;
            }

            // Fallback local para não quebrar a Consulta BCI quando a API EL/GPI não estiver configurada.
            // Em ambiente local, a consulta tenta buscar os dados diretamente no PostgreSQL/PostGIS
            // usando a tabela configurada em BCI_DB_SCHEMA/BCI_DB_TABLE ou a tabela do LAYER_LOTES.
            $localPayload = $this->consultaBciLocal($ctx, $inscricao);
            Response::json([
                'ok' => true,
                'fonte' => $localPayload['fonte'] ?? 'local',
                'modo' => $localPayload['modo'] ?? 'local-db',
                'inscricao' => $inscricao,
                'message' => $localPayload['message'] ?? 'Consulta BCI local executada.',
                'data' => $localPayload['data'] ?? ['inscricao_municipal' => $inscricao, 'inscricao' => $inscricao],
            ]);
            return;
        } catch (Throwable $e) {
            $this->logError('mapa-consulta-el', $e);
            Response::error('Erro ao consultar EL/GPI.', 500, Env::bool('APP_DEBUG') ? $e->getMessage() : null);
        }
    }

    private function consultaBciLocal(array $ctx, string $inscricao): array
    {
        if (!$this->municipioBool($ctx, 'BCI_LOCAL_ENABLED', Env::bool('BCI_LOCAL_ENABLED', true))) {
            return [
                'fonte' => 'local',
                'modo' => 'local-disabled',
                'message' => 'Consulta BCI local desativada para este município.',
                'data' => ['inscricao_municipal' => $inscricao, 'inscricao' => $inscricao],
            ];
        }

        $slug = $this->slug((string)($ctx['slug'] ?? Env::get('MAPA_MUNICIPIO_PADRAO', 'jequie')));
        $defaultSlug = $this->slug((string)Env::get('MAPA_MUNICIPIO_PADRAO', 'jequie'));
        $isDefaultMunicipio = $slug === $defaultSlug;

        /**
         * Regra importante para SaaS multi-município:
         * - A consulta BCI deve usar exclusivamente o schema/tabela do município ativo.
         * - Não deve cair em mun_jequie/jequie_2025 quando o município ativo for Ipiaú, Brumado,
         *   Castro Alves ou outro município.
         */
        $schemaCandidates = [];
        foreach ([
            (string)$this->municipioEnv($ctx, 'BCI_DB_SCHEMA', ''),
            (string)$this->municipioEnv($ctx, 'MAPA_DB_SCHEMA', ''),
            (string)$this->municipioEnv($ctx, 'MUNICIPIO_DB_SCHEMA', ''),
            (string)($ctx['schema_name'] ?? ''),
            'mun_' . $slug,
        ] as $schema) {
            $schema = trim($schema);
            if ($schema !== '') $schemaCandidates[] = $schema;
        }

        // Só o município padrão pode usar fallback global antigo.
        if ($isDefaultMunicipio) {
            foreach (['mun_jequie', (string)Env::get('DB_SCHEMA', 'gestao_sisprom')] as $schema) {
                $schema = trim($schema);
                if ($schema !== '') $schemaCandidates[] = $schema;
            }
        }

        $schemaCandidates = array_values(array_unique(array_filter($schemaCandidates)));

        $layerTable = $this->layerNameToTable((string)$this->municipioEnv($ctx, 'LAYER_LOTES', ''));
        $tableEnv = (string)$this->municipioEnv($ctx, 'BCI_DB_TABLE', '');
        $tablesEnv = (string)$this->municipioEnv($ctx, 'BCI_DB_TABLES', '');

        $tableCandidates = [];
        foreach (explode(',', $tablesEnv) as $t) {
            $t = trim($t);
            if ($t !== '') $tableCandidates[] = $t;
        }

        foreach ([
            $tableEnv,
            $layerTable,
            strtolower($layerTable),
            $slug . '_2025',
            $slug . '_2026',
            $slug . '_lotes',
            'cadastro_2026_postgis',
            'sisprommap_ctm_lotes',
        ] as $t) {
            $t = trim((string)$t);
            if ($t !== '') $tableCandidates[] = $t;
        }

        // Fallbacks históricos somente para Jequié/município padrão.
        if ($isDefaultMunicipio || $slug === 'jequie') {
            foreach (['jequie_2025', 'Jequie_2025_postgis', 'jequie_2025_postgis'] as $t) {
                $tableCandidates[] = $t;
            }
        }

        $tableCandidates = array_values(array_unique(array_filter($tableCandidates)));

        $columnEnv = trim((string)$this->municipioEnv($ctx, 'BCI_INSCRICAO_COLUMN', Env::get('BCI_INSCRICAO_COLUMN', '')));
        $columnCandidates = array_values(array_unique(array_filter(array_merge(
            $columnEnv !== '' ? [$columnEnv] : [],
            [
                'inscricao_municipal',
                'inscricao_imobiliaria',
                'inscricao',
                'cadastro_municipal',
                'cadastro',
                'sequencial_contribuinte',
                'sequencial',
                'inscricao_reduzida',
            ]
        ))));

        $debugAttempts = [];

        foreach ($schemaCandidates as $schema) {
            $schema = preg_replace('/[^A-Za-z0-9_]/', '', $schema) ?: '';
            if ($schema === '') continue;

            foreach ($tableCandidates as $table) {
                $table = preg_replace('/[^A-Za-z0-9_]/', '', $table) ?: '';
                if ($table === '') continue;

                $debugAttempts[] = $schema . '.' . $table;

                if (!$this->tableExists($schema, $table)) continue;

                $cols = $this->columns($schema, $table);
                $searchCols = array_values(array_intersect($columnCandidates, $cols));
                if (!$searchCols) continue;

                foreach ($searchCols as $col) {
                    $sql = 'SELECT * FROM ' . $this->qn($schema, $table) . ' WHERE CAST(' . $this->qi($col) . ' AS text) = :inscricao LIMIT 1';
                    $row = Database::fetchOne($sql, ['inscricao' => $inscricao]);

                    if (!$row && preg_match('/^0+\d+$/', $inscricao)) {
                        $row = Database::fetchOne($sql, ['inscricao' => ltrim($inscricao, '0')]);
                    }

                    if ($row) {
                        return [
                            'fonte' => 'PostgreSQL/PostGIS',
                            'modo' => 'local-db',
                            'municipio' => $slug,
                            'municipio_id' => $ctx['id'] ?? null,
                            'schema' => $schema,
                            'table' => $table,
                            'message' => 'Consulta BCI local encontrada no banco do município ativo.',
                            'data' => $this->normalizeBciRow($row, $inscricao, $schema, $table, $col),
                        ];
                    }
                }
            }
        }

        return [
            'fonte' => 'local',
            'modo' => 'local-empty',
            'municipio' => $slug,
            'municipio_id' => $ctx['id'] ?? null,
            'message' => 'Nenhum registro local foi encontrado para esta inscrição no banco do município ativo.',
            'data' => [
                'inscricao_municipal' => $inscricao,
                'inscricao' => $inscricao,
                'imobiliario' => ['inscricao_municipal' => $inscricao],
                'boletim' => [],
                '_debug' => [
                    'municipio' => $slug,
                    'schema_candidates' => $schemaCandidates,
                    'table_candidates' => $tableCandidates,
                    'column_candidates' => $columnCandidates,
                    'attempted_tables' => array_values(array_unique($debugAttempts)),
                ],
            ],
        ];
    }

    private function normalizeBciRow(array $row, string $inscricao, string $schema, string $table, string $matchedColumn): array
    {
        unset($row['geom'], $row['geometry'], $row['the_geom']);

        $get = function (array $keys, mixed $fallback = null) use ($row): mixed {
            foreach ($keys as $key) {
                if (array_key_exists($key, $row) && $row[$key] !== null && $row[$key] !== '') return $row[$key];
            }
            return $fallback;
        };

        $data = $row;
        $data['inscricao'] = $get(['inscricao', 'inscricao_municipal', 'inscricao_imobiliaria', 'cadastro_municipal'], $inscricao);
        $data['inscricao_municipal'] = $get(['inscricao_municipal', 'inscricao', 'inscricao_imobiliaria', 'cadastro_municipal'], $inscricao);
        $data['codigo'] = $get(['codigo', 'inscricao_municipal', 'inscricao', 'cadastro_municipal'], $inscricao);
        $data['sequencial_contribuinte'] = $get(['sequencial_contribuinte', 'sequencial', 'inscricao_reduzida'], null);

        $data['pessoa'] = [
            'nome' => $get(['proprietario', 'nome_proprietario', 'contribuinte', 'nome_contribuinte', 'nome', 'pessoa_nome'], null),
            'cpf_cnpj' => $get(['cpf_cnpj', 'cpf', 'cnpj', 'documento'], null),
        ];

        $data['contribuinte'] = $data['pessoa'];
        $data['proprietario'] = $data['pessoa'];

        $data['logradouro'] = [
            'nome' => $get(['logradouro', 'nome_logradouro', 'endereco', 'rua'], null),
        ];

        $data['endereco'] = [
            'logradouro' => $get(['logradouro', 'nome_logradouro', 'endereco', 'rua'], null),
            'numero' => $get(['numero', 'numero_lot', 'num', 'nro'], null),
            'bairro' => $get(['bairro', 'nome_bairro'], null),
            'cep' => $get(['cep'], null),
        ];

        $data['imobiliario'] = [
            'inscricao_municipal' => $data['inscricao_municipal'],
            'distrito' => $get(['distrito'], null),
            'setor' => $get(['setor', 'setor_id'], null),
            'quadra' => $get(['quadra'], null),
            'lote' => $get(['lote'], null),
            'face' => $get(['face'], null),
            'unidade' => $get(['unidade'], null),
            'matricula' => $get(['matricula'], null),
            'boletim' => [],
        ];

        $data['_debug'] = [
            'schema' => $schema,
            'table' => $table,
            'matched_column' => $matchedColumn,
        ];

        return $data;
    }

    private function layerNameToTable(string $layer): string
    {
        $layer = trim($layer);
        if ($layer === '') return '';
        if (str_contains($layer, ':')) {
            $parts = explode(':', $layer, 2);
            return trim($parts[1] ?? '');
        }
        return $layer;
    }

    private function consultaElViaProxy(string $proxyUrl, string $internalKey, string $inscricao): mixed
    {
        $timeout = Env::int('GPI_PROXY_TIMEOUT', 20);
        $params = http_build_query([
            'operacao' => 'consulta',
            'inscricao' => $inscricao,
            'key' => $internalKey,
        ]);
        $url = $proxyUrl . (str_contains($proxyUrl, '?') ? '&' : '?') . $params;
        $result = $this->httpRequestJson('GET', $url, null, ['Accept: application/json'], $timeout);
        if (($result['status'] ?? 0) < 200 || ($result['status'] ?? 0) >= 300) {
            throw new \RuntimeException('Proxy EL/GPI retornou HTTP ' . ($result['status'] ?? 0) . ': ' . substr((string)($result['raw'] ?? ''), 0, 500));
        }
        if (!is_array($result['json']) && !is_object($result['json'])) {
            throw new \RuntimeException('Proxy EL/GPI não retornou JSON válido. Retorno: ' . substr((string)($result['raw'] ?? ''), 0, 500));
        }
        return $result['json'];
    }

    private function consultaElViaApiDireta(array $ctx, string $inscricao): mixed
    {
        $token = $this->requestGpiToken($ctx, false);
        $result = $this->postConsultaInscricao($ctx, $inscricao, $token);
        $status = (int)($result['status'] ?? 0);

        if (in_array($status, [401, 403], true)) {
            $token = $this->requestGpiToken($ctx, true);
            $result = $this->postConsultaInscricao($ctx, $inscricao, $token);
            $status = (int)($result['status'] ?? 0);
        }

        if ($status < 200 || $status >= 300) {
            throw new \RuntimeException('API EL/GPI retornou HTTP ' . $status . ': ' . substr((string)($result['raw'] ?? ''), 0, 800));
        }
        if (!is_array($result['json']) && !is_object($result['json'])) {
            throw new \RuntimeException('API EL/GPI não retornou JSON válido. Retorno: ' . substr((string)($result['raw'] ?? ''), 0, 500));
        }
        return $result['json'];
    }

    private function postConsultaInscricao(array $ctx, string $inscricao, string $token): array
    {
        $base = rtrim((string)$this->municipioEnv($ctx, 'GPI_API_BASE_URL', $this->municipioEnv($ctx, 'GPI_API_BASE', Env::get('GPI_API_BASE_URL', ''))), '/');
        $timeout = (int)$this->municipioEnv($ctx, 'GPI_API_TIMEOUT', Env::get('GPI_API_TIMEOUT', '20'));
        $url = $base . '/bci/ac/consulta/inscricao';
        return $this->httpRequestJson('POST', $url, ['inscricao_municipal' => $inscricao], [
            'Accept: application/json',
            'Content-Type: application/json',
            'Authorization: Bearer ' . $token,
        ], $timeout);
    }

    private function requestGpiToken(array $ctx, bool $force = false): string
    {
        $ttl = (int)$this->municipioEnv($ctx, 'GPI_TOKEN_TTL', Env::get('GPI_TOKEN_TTL', '900'));
        $cache = $this->readGpiTokenCache($ctx);
        if (!$force && !empty($cache['token']) && !empty($cache['created_at']) && (time() - (int)$cache['created_at']) < $ttl) {
            return (string)$cache['token'];
        }

        $base = rtrim((string)$this->municipioEnv($ctx, 'GPI_API_BASE_URL', $this->municipioEnv($ctx, 'GPI_API_BASE', Env::get('GPI_API_BASE_URL', ''))), '/');
        $usuario = (string)$this->municipioEnv($ctx, 'GPI_API_USER', Env::get('GPI_API_USER', ''));
        $senha = (string)$this->municipioEnv($ctx, 'GPI_API_PASS', Env::get('GPI_API_PASS', ''));
        $timeout = (int)$this->municipioEnv($ctx, 'GPI_API_TIMEOUT', Env::get('GPI_API_TIMEOUT', '20'));

        if ($base === '') throw new \RuntimeException('GPI_API_BASE_URL não configurado.');
        if ($usuario === '') throw new \RuntimeException('GPI_API_USER não configurado.');
        if ($senha === '') throw new \RuntimeException('GPI_API_PASS não configurado.');

        $result = $this->httpRequestJson('POST', $base . '/auth', ['usuario' => $usuario, 'senha' => $senha], [
            'Accept: application/json',
            'Content-Type: application/json',
        ], $timeout);

        $status = (int)($result['status'] ?? 0);
        if ($status < 200 || $status >= 300) {
            throw new \RuntimeException('Falha ao autenticar na API EL/GPI. HTTP ' . $status . ': ' . substr((string)($result['raw'] ?? ''), 0, 800));
        }

        $token = $this->extractToken($result['json'] ?? null);
        if ($token === '') {
            throw new \RuntimeException('Token não encontrado na resposta da API EL/GPI.');
        }

        $this->writeGpiTokenCache($ctx, ['token' => $token, 'created_at' => time()]);
        return $token;
    }

    private function extractToken(mixed $payload): string
    {
        if (!is_array($payload)) return '';
        foreach (['token', 'access_token', 'jwt', 'id_token'] as $key) {
            if (!empty($payload[$key]) && is_string($payload[$key])) return trim($payload[$key]);
        }
        if (isset($payload['data']) && is_array($payload['data'])) {
            foreach (['token', 'access_token', 'jwt', 'id_token'] as $key) {
                if (!empty($payload['data'][$key]) && is_string($payload['data'][$key])) return trim($payload['data'][$key]);
            }
        }
        return '';
    }

    private function httpRequestJson(string $method, string $url, ?array $payload = null, array $headers = [], int $timeout = 20): array
    {
        $method = strtoupper($method);
        $body = $payload === null ? null : json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        if (function_exists('curl_init')) {
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_HEADER => true,
                CURLOPT_CUSTOMREQUEST => $method,
                CURLOPT_TIMEOUT => $timeout,
                CURLOPT_CONNECTTIMEOUT => min(10, $timeout),
                CURLOPT_HTTPHEADER => $headers,
                CURLOPT_SSL_VERIFYPEER => false,
                CURLOPT_SSL_VERIFYHOST => false,
            ]);
            if ($body !== null) curl_setopt($ch, CURLOPT_POSTFIELDS, $body);
            $rawResponse = curl_exec($ch);
            if ($rawResponse === false) {
                $err = curl_error($ch);
                curl_close($ch);
                throw new \RuntimeException('Erro de comunicação HTTP: ' . $err);
            }
            $status = (int)curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
            $headerSize = (int)curl_getinfo($ch, CURLINFO_HEADER_SIZE);
            curl_close($ch);
            $raw = substr((string)$rawResponse, $headerSize);
        } else {
            $headerText = implode("\r\n", $headers);
            $context = stream_context_create([
                'http' => [
                    'method' => $method,
                    'header' => $headerText,
                    'content' => $body ?? '',
                    'timeout' => $timeout,
                    'ignore_errors' => true,
                ],
                'ssl' => [
                    'verify_peer' => false,
                    'verify_peer_name' => false,
                ],
            ]);
            $raw = @file_get_contents($url, false, $context);
            if ($raw === false) throw new \RuntimeException('Erro de comunicação HTTP ao acessar ' . $url);
            $status = 200;
            if (isset($http_response_header[0]) && preg_match('/\s(\d{3})\s/', $http_response_header[0], $m)) {
                $status = (int)$m[1];
            }
        }

        $json = null;
        if (is_string($raw) && trim($raw) !== '') {
            $decoded = json_decode($raw, true);
            if (json_last_error() === JSON_ERROR_NONE) $json = $decoded;
        }
        return ['status' => $status, 'raw' => (string)$raw, 'json' => $json];
    }

    private function gpiCachePath(array $ctx): string
    {
        $slug = preg_replace('/[^A-Za-z0-9_]/', '_', (string)($ctx['slug'] ?? 'default')) ?: 'default';
        return dirname(__DIR__, 2) . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'cache' . DIRECTORY_SEPARATOR . 'gpi_token_' . $slug . '.json';
    }

    private function readGpiTokenCache(array $ctx): array
    {
        $file = $this->gpiCachePath($ctx);
        if (!is_file($file)) return [];
        $json = json_decode((string)@file_get_contents($file), true);
        return is_array($json) ? $json : [];
    }

    private function writeGpiTokenCache(array $ctx, array $data): void
    {
        $file = $this->gpiCachePath($ctx);
        $dir = dirname($file);
        if (!is_dir($dir)) @mkdir($dir, 0777, true);
        @file_put_contents($file, json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
    }

    public function exportShp(Request $request): void
    {
        try {
            $payload = is_array($request->body) ? $request->body : [];
            if (!$payload) {
                Response::error('GeoJSON não enviado.', 400);
                return;
            }
            $filename = $this->safeFilename((string)($request->query['filename'] ?? 'sisprom_export'));
            $features = $this->normalizeGeoJson($payload);
            $zipBytes = $this->createGeoPackageZip($features, $filename, $payload);
            http_response_code(200);
            header('Content-Type: application/zip');
            header('Content-Disposition: attachment; filename="' . $filename . '.zip"');
            header('Content-Length: ' . strlen($zipBytes));
            echo $zipBytes;
        } catch (Throwable $e) {
            $this->logError('mapa-export-shp', $e);
            Response::error('Erro ao exportar feição', 500, Env::bool('APP_DEBUG') ? $e->getMessage() : null);
        }
    }

    /**
     * Busca Fotos 360º diretamente no PostgreSQL/PostGIS.
     * Esta versão segue exatamente o padrão que funcionava no FastAPI:
     * id, lat, lon, descricao, heading_deg, capture_at, file_url.
     */
    private function readFotos360FromDatabase(array $ctx): ?array
    {
        $useDatabase = $this->municipioBool(
            $ctx,
            'FOTOS360_USE_DATABASE',
            in_array(strtolower((string) Env::get('FOTOS360_USE_DATABASE', 'true')), ['1', 'true', 'yes', 'on'], true)
        );

        if (!$useDatabase) {
            return $this->emptyFotos360Debug('database-disabled', $ctx, null, null, 'FOTOS360_USE_DATABASE está false.');
        }

        $schema = preg_replace(
            '/[^A-Za-z0-9_]/',
            '',
            (string) $this->municipioEnv(
                $ctx,
                'FOTOS360_DB_SCHEMA',
                Env::get('FOTOS360_DB_SCHEMA', $ctx['schema_name'] ?: 'mun_' . $ctx['slug'])
            )
        ) ?: ($ctx['schema_name'] ?: 'mun_' . $ctx['slug']);

        $table = preg_replace(
            '/[^A-Za-z0-9_]/',
            '',
            (string) $this->municipioEnv(
                $ctx,
                'FOTOS360_DB_TABLE',
                Env::get('FOTOS360_DB_TABLE', 'fotos360')
            )
        ) ?: 'fotos360';

        if (!$this->tableExists($schema, $table)) {
            return $this->emptyFotos360Debug('table-not-found', $ctx, $schema, $table, 'Tabela não encontrada no banco.');
        }

        $cols = $this->columns($schema, $table);
        $required = ['id', 'lat', 'lon', 'descricao', 'heading_deg', 'capture_at', 'file_url'];
        $missing = array_values(array_filter($required, fn($col) => !in_array($col, $cols, true)));

        // Query principal igual à do FastAPI antigo.
        if (empty($missing)) {
            $sql = "
                SELECT
                    id,
                    lat,
                    lon,
                    descricao,
                    heading_deg,
                    capture_at,
                    file_url
                FROM {$this->qn($schema, $table)}
                WHERE lat IS NOT NULL
                  AND lon IS NOT NULL
                  AND file_url IS NOT NULL
                  AND file_url <> ''
                ORDER BY id DESC
                LIMIT 1000
            ";

            $rows = Database::fetchAll($sql);

            return $this->rowsToFotos360FeatureCollection($rows, [
                'source' => 'database',
                'mode' => 'fastapi-compatible',
                'schema' => $schema,
                'table' => $table,
                'columns' => $cols,
                'query' => 'id,lat,lon,descricao,heading_deg,capture_at,file_url',
                'sql_rows' => count($rows),
            ]);
        }

        // Fallback apenas se a tabela não tiver exatamente as colunas do FastAPI.
        $geomExpr = in_array('geom', $cols, true)
            ? 'ST_AsGeoJSON(geom)::json AS geometry_json'
            : 'NULL AS geometry_json';

        $order = in_array('id', $cols, true) ? 'ORDER BY id DESC' : '';
        $rows = Database::fetchAll("SELECT *, {$geomExpr} FROM {$this->qn($schema, $table)} {$order} LIMIT 1000");

        return $this->rowsToFotos360FeatureCollection($rows, [
            'source' => 'database',
            'mode' => 'compat-fallback',
            'schema' => $schema,
            'table' => $table,
            'columns' => $cols,
            'missing_fastapi_columns' => $missing,
            'sql_rows' => count($rows),
        ]);
    }

    private function rowsToFotos360FeatureCollection(array $rows, array $debug = []): array
    {
        $features = [];

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

            $geom = $row['geometry_json'] ?? null;

            if (is_string($geom)) {
                $geom = json_decode($geom, true);
            }

            $lon = $this->firstNumeric($row, ['lon', 'lng', 'longitude', 'long', 'x']);
            $lat = $this->firstNumeric($row, ['lat', 'latitude', 'y']);

            if (!$geom && $lon !== null && $lat !== null) {
                $geom = [
                    'type' => 'Point',
                    'coordinates' => [$lon, $lat],
                ];
            }

            if (!$geom) {
                continue;
            }

            $fileUrl = (string) $this->firstValue($row, [
                'file_url',
                'url',
                'path',
                'imagem',
                'image',
                'arquivo',
                'arquivo_url',
                'foto_url',
                'photo_url',
                'image_url',
            ], '');

            $fileUrl = $this->absoluteFileUrl($fileUrl);

            if ($fileUrl === '') {
                continue;
            }

            $descricao = (string) $this->firstValue($row, [
                'descricao',
                'description',
                'titulo',
                'title',
                'legenda',
                'nome',
            ], 'Foto 360º');

            $heading = $this->firstValue($row, [
                'heading_deg',
                'heading',
                'heading_degrees',
                'azimuth',
            ], null);

            $captureAt = $this->firstValue($row, [
                'capture_at',
                'data_captura',
                'created_at',
                'dt_captura',
            ], null);

            unset($row['geom'], $row['geometry_json']);

            $row['descricao'] = $descricao;
            $row['heading_deg'] = $heading;
            $row['capture_at'] = $captureAt;
            $row['file_url'] = $fileUrl;

            $features[] = [
                'type' => 'Feature',
                'geometry' => $geom,
                'properties' => $row,
            ];
        }

        $payload = [
            'type' => 'FeatureCollection',
            'features' => $features,
        ];

        if (in_array(strtolower((string) Env::get('APP_DEBUG', 'false')), ['1', 'true', 'yes', 'on'], true)) {
            $payload['_debug'] = $debug + [
                'features' => count($features),
            ];
        }

        return $payload;
    }

    private function emptyFotos360Debug(string $mode, array $ctx, ?string $schema, ?string $table, string $message): array
    {
        $payload = [
            'type' => 'FeatureCollection',
            'features' => [],
        ];

        if (in_array(strtolower((string) Env::get('APP_DEBUG', 'false')), ['1', 'true', 'yes', 'on'], true)) {
            $payload['_debug'] = [
                'source' => 'database',
                'mode' => $mode,
                'message' => $message,
                'municipio' => $ctx['slug'] ?? null,
                'schema' => $schema,
                'table' => $table,
                'schema_name' => $ctx['schema_name'] ?? null,
            ];
        }

        return $payload;
    }

    private function firstValue(array $row, array $keys, mixed $default = null): mixed
    {
        foreach ($keys as $key) {
            if (array_key_exists($key, $row) && $row[$key] !== null && $row[$key] !== '') {
                return $row[$key];
            }
        }

        return $default;
    }

    private function firstNumeric(array $row, array $keys): ?float
    {
        foreach ($keys as $key) {
            if (!array_key_exists($key, $row)) {
                continue;
            }

            $value = $row[$key];

            if ($value === null || $value === '') {
                continue;
            }

            if (is_string($value)) {
                $value = str_replace(',', '.', trim($value));
            }

            if (is_numeric($value)) {
                return (float) $value;
            }
        }

        return null;
    }

    private function readFotos360Index(): array
    {
        $file = $this->fotosIndexPath();
        if (is_file($file)) {
            $json = json_decode((string)@file_get_contents($file), true);
            if (is_array($json) && ($json['type'] ?? '') === 'FeatureCollection') return $json;
        }
        return ['type' => 'FeatureCollection', 'features' => []];
    }

    private function writeFotos360Index(array $data): void
    {
        $file = $this->fotosIndexPath();
        $dir = dirname($file);
        if (!is_dir($dir)) @mkdir($dir, 0777, true);
        @file_put_contents($file, json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT));
    }

    private function fotosIndexPath(): string
    {
        return dirname(__DIR__, 2) . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . 'fotos360' . DIRECTORY_SEPARATOR . 'fotos360_index.json';
    }

    private function absoluteFileUrl(string $url): string
    {
        $url = trim($url);

        if ($url === '' || preg_match('#^(https?://|data:|blob:)#i', $url)) {
            return $url;
        }

        $prefix = rtrim((string) Env::get('FOTOS360_PUBLIC_URL_PREFIX', ''), '/');

        if ($prefix === '') {
            $prefix = rtrim((string) Env::get('PUBLIC_BASE_URL', 'http://127.0.0.1:8081'), '/');
        }

        return $prefix . '/' . ltrim($url, '/');
    }

    private function normalizeGeoJson(array $payload): array
    {
        if (($payload['type'] ?? '') === 'FeatureCollection') return array_values(array_filter($payload['features'] ?? [], 'is_array'));
        if (($payload['type'] ?? '') === 'Feature') return [$payload];
        return [['type' => 'Feature', 'properties' => [], 'geometry' => $payload]];
    }

    private function createGeoPackageZip(array $features, string $filename, array $original): string
    {
        $tmp = tempnam(sys_get_temp_dir(), 'sisprom_zip_');
        $zip = new \ZipArchive();
        if ($zip->open($tmp, \ZipArchive::OVERWRITE) !== true) throw new \RuntimeException('ZipArchive indisponível no PHP.');
        $fc = ['type' => 'FeatureCollection', 'features' => $features];
        $zip->addFromString($filename . '.geojson', json_encode($fc, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT));
        $zip->addFromString($filename . '.csv', $this->featuresToCsv($features));
        $zip->addFromString($filename . '.prj', 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433],AUTHORITY["EPSG","4326"]]');
        $zip->addFromString('LEIA-ME.txt', "Exportação gerada pelo SISPROM. Este ZIP contém GeoJSON e CSV. Para SHP nativo, ative ogr2ogr/GDAL no servidor PHP.\n");
        $zip->close();
        $bytes = (string)file_get_contents($tmp);
        @unlink($tmp);
        return $bytes;
    }

    private function featuresToCsv(array $features): string
    {
        $keys = ['sisprom_id'];
        foreach ($features as $f) foreach (($f['properties'] ?? []) as $k => $_v) if (!in_array((string)$k, $keys, true)) $keys[] = (string)$k;
        $lines = [implode(';', $keys)];
        foreach ($features as $i => $f) {
            $props = $f['properties'] ?? [];
            $row = [];
            foreach ($keys as $k) {
                $v = $k === 'sisprom_id' ? (string)($i + 1) : ($props[$k] ?? '');
                if (is_array($v) || is_object($v)) $v = json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
                $row[] = '"' . str_replace('"', '""', (string)$v) . '"';
            }
            $lines[] = implode(';', $row);
        }
        return implode("\n", $lines);
    }


    private function municipioContext(Request $request): array
    {
        $raw = trim((string)(
            $request->query['municipio']
            ?? $request->query['municipio_id']
            ?? $request->header('x-municipality-id')
            ?? Auth::tokenMunicipality($request)
            ?? Env::get('MAPA_MUNICIPIO_PADRAO', 'jequie')
        ));

        if ($raw === '') {
            $raw = (string)Env::get('MAPA_MUNICIPIO_PADRAO', 'jequie');
        }

        $row = $this->findMunicipio($raw);

        $nome = $row['nome'] ?? null;
        $slugFromDb = $row['slug'] ?? null;
        $schema = $row['schema_name'] ?? null;
        $id = $row['id'] ?? null;

        $rawKey = $this->slug($raw);
        $dbSlugKey = $slugFromDb ? $this->slug((string)$slugFromDb) : '';
        $nameKey = $nome ? $this->slug((string)$nome) : '';
        $schemaKey = $schema ? $this->keyFromSchema((string)$schema) : '';

        // Regra principal:
        // 1) se a URL pediu explicitamente municipio=jequie/ipiau/brumado e existe bloco no .env,
        //    esse valor tem prioridade sobre qualquer slug errado vindo do banco.
        // 2) se veio ID do município, resolve pelo nome/schema/slug do banco.
        // 3) nunca transformar Jequié em "municipio_exemplo" nem Ipiaú em "ipia_u".
        $key = '';
        foreach ([$rawKey, $nameKey, $schemaKey, $dbSlugKey] as $candidate) {
            if ($candidate !== '' && $this->hasMunicipioEnvBlock($candidate)) {
                $key = $candidate;
                break;
            }
        }

        if ($key === '') {
            if ($nameKey !== '' && !in_array($nameKey, ['municipio_exemplo', 'municipio_teste', 'cidade_exemplo'], true)) {
                $key = $nameKey;
            } elseif ($schemaKey !== '') {
                $key = $schemaKey;
            } elseif ($rawKey !== '') {
                $key = $rawKey;
            } elseif ($dbSlugKey !== '') {
                $key = $dbSlugKey;
            } else {
                $key = $this->slug((string)Env::get('MAPA_MUNICIPIO_PADRAO', 'jequie'));
            }
        }

        $nomeFinal = $nome ?: $this->municipioDisplayName($key);
        $schemaFinal = $schema ?: (string)$this->municipioEnvByKey($key, 'BCI_DB_SCHEMA', 'mun_' . $key);

        return [
            'raw' => $raw,
            'id' => $id,
            'nome' => $nomeFinal,
            'slug' => $key,
            'key' => $key,
            'key_upper' => strtoupper(str_replace('-', '_', $key)),
            'schema_name' => $schemaFinal ?: null,
        ];
    }

    private function findMunicipio(string $value): ?array
    {
        $v = trim($value);
        if ($v === '') return null;

        $slug = $this->slug($v);
        $slugHyphen = str_replace('_', '-', $slug);

        try {
            $schema = Env::get('DB_SCHEMA', 'gestao_sisprom') ?: 'gestao_sisprom';

            if ($this->tableExists((string)$schema, 'municipios')) {
                $table = $this->qn((string)$schema, 'municipios');
                $sql = "SELECT id, nome, uf, slug, codigo, codigo_ibge, schema_name
                        FROM {$table}
                        WHERE id = :value
                           OR lower(coalesce(slug, '')) IN (:slug, :slug_hyphen)
                           OR lower(coalesce(codigo, '')) IN (:slug, :slug_hyphen)
                           OR lower(coalesce(codigo_ibge, '')) = :slug
                           OR lower(regexp_replace(translate(nome,
                              'ÁÀÂÃÄáàâãäÉÈÊËéèêëÍÌÎÏíìîïÓÒÔÕÖóòôõöÚÙÛÜúùûüÇç',
                              'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUUuuuuCc'), '[^a-zA-Z0-9]+', '_', 'g')) = :slug
                        ORDER BY
                          CASE
                            WHEN lower(coalesce(slug, '')) IN (:slug, :slug_hyphen) THEN 1
                            WHEN lower(regexp_replace(translate(nome,
                              'ÁÀÂÃÄáàâãäÉÈÊËéèêëÍÌÎÏíìîïÓÒÔÕÖóòôõöÚÙÛÜúùûüÇç',
                              'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUUuuuuCc'), '[^a-zA-Z0-9]+', '_', 'g')) = :slug THEN 2
                            WHEN id = :value THEN 3
                            ELSE 9
                          END
                        LIMIT 1";
                $row = Database::fetchOne($sql, ['value' => $v, 'slug' => $slug, 'slug_hyphen' => $slugHyphen]);
                if ($row) return $row;
            }

            if ($this->tableExists((string)$schema, 'municipalities')) {
                $table = $this->qn((string)$schema, 'municipalities');
                $sql = "SELECT
                            id,
                            name AS nome,
                            NULL::varchar AS uf,
                            slug,
                            code AS codigo,
                            NULL::varchar AS codigo_ibge,
                            schema_name
                        FROM {$table}
                        WHERE id = :value
                           OR lower(coalesce(slug, '')) IN (:slug, :slug_hyphen)
                           OR lower(coalesce(code, '')) IN (:slug, :slug_hyphen)
                           OR lower(regexp_replace(translate(name,
                              'ÁÀÂÃÄáàâãäÉÈÊËéèêëÍÌÎÏíìîïÓÒÔÕÖóòôõöÚÙÛÜúùûüÇç',
                              'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUUuuuuCc'), '[^a-zA-Z0-9]+', '_', 'g')) = :slug
                        ORDER BY
                          CASE
                            WHEN lower(coalesce(slug, '')) IN (:slug, :slug_hyphen) THEN 1
                            WHEN lower(regexp_replace(translate(name,
                              'ÁÀÂÃÄáàâãäÉÈÊËéèêëÍÌÎÏíìîïÓÒÔÕÖóòôõöÚÙÛÜúùûüÇç',
                              'AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUUuuuuCc'), '[^a-zA-Z0-9]+', '_', 'g')) = :slug THEN 2
                            WHEN id = :value THEN 3
                            ELSE 9
                          END
                        LIMIT 1";
                $row = Database::fetchOne($sql, ['value' => $v, 'slug' => $slug, 'slug_hyphen' => $slugHyphen]);
                if ($row) return $row;
            }
        } catch (Throwable $e) {
            $this->logError('mapa-find-municipio', $e);
        }

        return null;
    }

    private function municipioEnv(array $ctx, string $suffix, ?string $default = null): ?string
    {
        return $this->municipioEnvByKey((string)($ctx['key'] ?? ''), $suffix, $default);
    }

    private function municipioEnvByKey(string $key, string $suffix, ?string $default = null): ?string
    {
        $envKey = strtoupper(str_replace('-', '_', $this->slug($key)));
        if ($envKey !== '') {
            $specific = Env::get('MUNICIPIO_' . $envKey . '_' . $suffix, null);
            if ($specific !== null && $specific !== '') return $specific;
        }
        return Env::get($suffix, $default);
    }

    private function layerEnv(array $ctx, string $suffix, string $defaultForDefaultMunicipio = ''): string
    {
        $key = (string)($ctx['key'] ?? '');
        $envKey = strtoupper(str_replace('-', '_', $this->slug($key)));
        $defaultSlug = $this->slug((string)Env::get('MAPA_MUNICIPIO_PADRAO', 'jequie'));
        $currentSlug = $this->slug((string)($ctx['slug'] ?? ''));

        if ($envKey !== '') {
            $specific = Env::get('MUNICIPIO_' . $envKey . '_' . $suffix, null);
            if ($specific !== null) {
                return trim((string)$specific);
            }
        }

        if ($currentSlug !== '' && $currentSlug !== $defaultSlug) {
            return '';
        }

        return trim((string)Env::get($suffix, $defaultForDefaultMunicipio));
    }

    private function layerWarnings(array $ctx): array
    {
        $required = ['LAYER_ORTOFOTO', 'LAYER_LOTES'];
        $warnings = [];
        foreach ($required as $suffix) {
            if ($this->layerEnv($ctx, $suffix, '') === '') {
                $warnings[] = 'Camada obrigatória não configurada para o município: ' . $suffix;
            }
        }
        return $warnings;
    }

    private function hasMunicipioEnvBlock(string $key): bool
    {
        $envKey = strtoupper(str_replace('-', '_', $this->slug($key)));
        if ($envKey === '') return false;
        $checks = ['NOME', 'SLUG', 'LAYER_ORTOFOTO', 'LAYER_LOTES', 'GEOSERVER_URL', 'BCI_DB_SCHEMA'];
        foreach ($checks as $suffix) {
            $value = Env::get('MUNICIPIO_' . $envKey . '_' . $suffix, null);
            if ($value !== null && $value !== '') return true;
        }
        return false;
    }

    private function keyFromSchema(string $schema): string
    {
        $schema = strtolower(trim($schema));
        $schema = preg_replace('/^(mun_|geo_|schema_)/', '', $schema) ?: $schema;
        return $this->slug($schema);
    }

    private function municipioDisplayName(string $key): string
    {
        $names = [
            'jequie' => 'Jequié',
            'ipiau' => 'Ipiaú',
            'brumado' => 'Brumado',
            'castro_alves' => 'Castro Alves',
            'castro-alves' => 'Castro Alves',
        ];
        return $names[$key] ?? ucwords(str_replace(['_', '-'], ' ', $key));
    }

    private function municipioBool(array $ctx, string $suffix, bool $default = false): bool
    {
        $value = $this->municipioEnv($ctx, $suffix, $default ? 'true' : 'false');
        return in_array(strtolower((string)$value), ['1', 'true', 'yes', 'on'], true);
    }

    private function safeFilename(string $v): string { $v = preg_replace('/[^A-Za-z0-9_\-]+/', '_', trim($v)) ?: 'sisprom_export'; return substr(trim($v, '_'), 0, 90) ?: 'sisprom_export'; }
    private function slug(string $v): string
    {
        $v = trim($v);
        if ($v === '') return 'jequie';

        $from = ['Á','À','Â','Ã','Ä','á','à','â','ã','ä','É','È','Ê','Ë','é','è','ê','ë','Í','Ì','Î','Ï','í','ì','î','ï','Ó','Ò','Ô','Õ','Ö','ó','ò','ô','õ','ö','Ú','Ù','Û','Ü','ú','ù','û','ü','Ç','ç','Ñ','ñ'];
        $to   = ['A','A','A','A','A','a','a','a','a','a','E','E','E','E','e','e','e','e','I','I','I','I','i','i','i','i','O','O','O','O','O','o','o','o','o','o','U','U','U','U','u','u','u','u','C','c','N','n'];
        $v = str_replace($from, $to, $v);
        $ascii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $v);
        if ($ascii !== false && $ascii !== '') $v = $ascii;

        $v = strtolower((string)preg_replace('/[^A-Za-z0-9]+/', '_', $v));
        $v = trim((string)preg_replace('/_+/', '_', $v), '_');

        // Correções para slugs gerados incorretamente por bases antigas/encoding.
        $aliases = [
            'ipia_u' => 'ipiau',
            'ipia' => 'ipiau',
            'jequi' => 'jequie',
            'jequie_ba' => 'jequie',
            'municipio_exemplo' => 'jequie',
        ];

        return $aliases[$v] ?? ($v ?: 'jequie');
    }
    private function uuid(): string { $d = random_bytes(16); $d[6] = chr((ord($d[6]) & 0x0f) | 0x40); $d[8] = chr((ord($d[8]) & 0x3f) | 0x80); return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($d), 4)); }
    private function tableExists(string $schema, string $table): bool { return (bool)Database::fetchOne('SELECT 1 FROM information_schema.tables WHERE table_schema=:s AND table_name=:t LIMIT 1', ['s'=>$schema,'t'=>$table]); }
    private function columns(string $schema, string $table): array { $k="{$schema}.{$table}"; if(isset($this->schemaCache[$k]))return $this->schemaCache[$k]; $rows=Database::fetchAll('SELECT column_name FROM information_schema.columns WHERE table_schema=:s AND table_name=:t', ['s'=>$schema,'t'=>$table]); return $this->schemaCache[$k]=array_map(fn($r)=>(string)$r['column_name'],$rows); }
    private function qi(string $v): string { return '"' . str_replace('"','""', preg_replace('/[^A-Za-z0-9_]/','',$v)) . '"'; }
    private function qn(string $s, string $t): string { return $this->qi($s) . '.' . $this->qi($t); }
    private function logError(string $ctx, Throwable $e): void { $dir=dirname(__DIR__,2).DIRECTORY_SEPARATOR.'storage'.DIRECTORY_SEPARATOR.'logs'; if(!is_dir($dir))@mkdir($dir,0777,true); @file_put_contents($dir.DIRECTORY_SEPARATOR.'api-error.log','['.date(DATE_ATOM)."] {$ctx}: ".$e->getMessage().' in '.$e->getFile().':'.$e->getLine().PHP_EOL,FILE_APPEND); }
}
