<?php

declare(strict_types=1);

namespace App\Controllers;

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

final class BiController
{
    public function indicadores(Request $request): void
    {
        $user = Auth::requireUser($request);
        $municipalityId = Auth::tokenMunicipality($request) ?: ($request->header('x-municipality-id') ?: null);
        $isSuperAdmin = $this->isSuperAdmin($user['id'] ?? '');

        if (!$isSuperAdmin && !$municipalityId) {
            Response::json($this->emptyResponse(false, null));
            return;
        }

        try {
            $scope = $isSuperAdmin ? 'global' : 'municipal';
            $kpis = $this->kpis($isSuperAdmin, $municipalityId);
            $charts = $this->charts($isSuperAdmin, $municipalityId);

            Response::json([
                'scope' => $scope,
                'is_super_admin' => $isSuperAdmin,
                'municipality_id' => $isSuperAdmin ? null : $municipalityId,
                'generated_at' => date(DATE_ATOM),
                'kpis' => $kpis,
                'charts' => $charts,
            ]);
        } catch (Throwable $e) {
            $this->logError('bi-indicadores', $e);
            Response::json([
                'message' => 'Erro ao carregar BI de indicadores',
                'details' => $e->getMessage(),
                'hint' => 'Verifique storage/logs/api-error.log. Esta versão remove parâmetros PDO extras e usa parâmetros separados para a consulta fiscal e cadastral.',
            ], 500);
        }
    }

    /**
     * BI Fiscal/IPTU - resumo.
     *
     * Esta implementação replica a regra dos arquivos Adianti:
     * - calculos_dashboard_resumo.php
     * - calculos_debito_mapa.php
     *
     * Diferença necessária: usa por padrão o schema real mun_jequie.
     */
    public function iptuResumo(Request $request): void
    {
        try {
            $user = Auth::requireUser($request);
            $ctx = $this->biMunicipioContext($request);
            $this->assertCanUseMunicipio($user, $ctx);
            $cfg = $this->iptuConfig($request, $ctx);
            if (empty($cfg['valid'])) {
                Response::json($this->emptyIptuResumo($cfg, 'BI/IPTU sem tabelas configuradas para o município ativo.'));
                return;
            }
            $pdo = Database::pdo();

            $ano = (int)($request->query['ano'] ?? 2025);
            $agrupamento = (string)($request->query['agrupamento'] ?? '1');

            $limit = (int)($request->query['limit'] ?? 8);
            if ($limit <= 0) $limit = 8;
            if ($limit > 500) $limit = 500;

            $rawSetor = trim((string)($request->query['setor'] ?? ''));
            if ($rawSetor === 'ALL') $rawSetor = '';
            $setorTexto = null;
            if ($rawSetor !== '') {
                if (preg_match('/(\d+)/', $rawSetor, $m)) {
                    $n = (int)$m[1];
                    if ($n > 0) {
                        $setorTexto = sprintf('SETOR %02d', $n);
                    }
                }
                if ($setorTexto === null) {
                    $setorTexto = $rawSetor;
                }
            }

            $bairro = trim((string)($request->query['bairro'] ?? ''));
            if ($bairro === '' || $bairro === 'ALL') $bairro = null;

            $minDebito = (float)str_replace(',', '.', (string)($request->query['min_debito'] ?? '0'));
            if ($minDebito < 0) $minDebito = 0.0;

            $situacao = strtoupper(trim((string)($request->query['situacao'] ?? 'ALL')));
            if (!in_array($situacao, ['ALL', 'DEBITO', 'PAGO'], true)) {
                $situacao = 'ALL';
            }

            $calc = $cfg['calc_full'];
            $parc = $cfg['parcela_full'];
            $geo  = $cfg['geo_full'];
            $gIns = $cfg['geo_col_inscricao_q'];
            $gSet = $cfg['geo_col_setor_q'];
            $gBai = $cfg['geo_col_bairro_q'];
            $gNom = $cfg['geo_col_nome_q'];
            $gDoc = $cfg['geo_col_documento_q'];
            $gLog = $cfg['geo_col_logradouro_q'];
            $gNum = $cfg['geo_col_numero_q'];
            $join = $this->iptuJoinCondition('g', 'cc', $cfg);

            $cards = [
                'total_calculos'        => 0,
                'total_debito'          => 0.0,
                'total_pago'            => 0.0,
                'total_lancado'         => 0.0,
                'inscricoes_debito'     => 0,
                'inscricoes_alto_risco' => 0,
                'imo_total_cadastrados' => 0,
                'imo_com_calculo'       => 0,
            ];

            // 1) Total cálculos - sem JOIN direto com GEO para não duplicar registros.
            $sql = "
                SELECT COUNT(*) AS total
                FROM {$calc} cc
                WHERE cc.ano = :ano
                  AND cc.codigo_agrupamento = :agr
            ";
            if ($setorTexto !== null || $bairro !== null) {
                $sql .= "
                  AND EXISTS (
                      SELECT 1
                      FROM {$geo} g
                      WHERE {$join}
                ";
                if ($setorTexto !== null) $sql .= " AND g.{$gSet} = :setor";
                if ($bairro !== null) $sql .= " AND g.{$gBai} = :bairro";
                $sql .= "
                  )
                ";
            }
            $cards['total_calculos'] = (int)($this->iptuFetchValue($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)) ?? 0);

            // 2) Total débito - parcelas <> 0 em Débito/Debito.
            // Calculado SEM JOIN direto com a tabela GEO para evitar duplicidade quando
            // houver mais de uma geometria para o mesmo sequencial_contribuinte.
            $sql = "
                SELECT COALESCE(SUM(cp.valor), 0) AS total
                FROM {$parc} cp
                WHERE cp.situacao IN ('Débito', 'Debito')
                  AND cp.parcela <> 0
                  AND EXISTS (
                      SELECT 1
                      FROM {$calc} cc
                      WHERE cc.id = cp.calculo_id
                        AND cc.ano = :ano
                        AND cc.codigo_agrupamento = :agr
            ";
            if ($setorTexto !== null || $bairro !== null) {
                $sql .= "
                        AND EXISTS (
                            SELECT 1
                            FROM {$geo} g
                            WHERE {$join}
                ";
                if ($setorTexto !== null) $sql .= " AND g.{$gSet} = :setor";
                if ($bairro !== null) $sql .= " AND g.{$gBai} = :bairro";
                $sql .= "
                        )
                ";
            }
            $sql .= "
                  )
            ";
            $cards['total_debito'] = (float)($this->iptuFetchValue($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)) ?? 0);

            // 3) Total pago - regra confirmada: soma TUDO que está com situacao = 'Pago'.
            // Importante: calculado com EXISTS, sem JOIN direto com GEO, para não duplicar valores
            // quando houver mais de uma feição no mapa para o mesmo sequencial_contribuinte.
            $sql = "
                SELECT COALESCE(SUM(cp.valor), 0) AS total
                FROM {$parc} cp
                WHERE cp.situacao = 'Pago'
                  AND EXISTS (
                      SELECT 1
                      FROM {$calc} cc
                      WHERE cc.id = cp.calculo_id
                        AND cc.ano = :ano
                        AND cc.codigo_agrupamento = :agr
                  ";
            if ($setorTexto !== null || $bairro !== null) {
                $sql .= "
                        AND EXISTS (
                            SELECT 1
                            FROM {$geo} g
                            WHERE {$join}
                ";
                if ($setorTexto !== null) $sql .= " AND g.{$gSet} = :setor";
                if ($bairro !== null) $sql .= " AND g.{$gBai} = :bairro";
                $sql .= "
                        )
                ";
            }
            $sql .= "
                  )
            ";
            $cards['total_pago'] = (float)($this->iptuFetchValue($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)) ?? 0);

            // No Adianti, total lançado é total_debito + total_pago.
            $cards['total_lancado'] = (float)$cards['total_debito'] + (float)$cards['total_pago'];

            // 4) Inscrições com débito - contagem distinta, sem multiplicação por geometrias.
            $sql = "
                SELECT COUNT(DISTINCT cc.inscricao_reduzida) AS total
                FROM {$parc} cp
                INNER JOIN {$calc} cc ON cc.id = cp.calculo_id
                WHERE cc.ano = :ano
                  AND cc.codigo_agrupamento = :agr
                  AND cp.situacao IN ('Débito', 'Debito')
                  AND cp.parcela <> 0
            ";
            if ($setorTexto !== null || $bairro !== null) {
                $sql .= "
                  AND EXISTS (
                      SELECT 1
                      FROM {$geo} g
                      WHERE {$join}
                ";
                if ($setorTexto !== null) $sql .= " AND g.{$gSet} = :setor";
                if ($bairro !== null) $sql .= " AND g.{$gBai} = :bairro";
                $sql .= "
                  )
                ";
            }
            $cards['inscricoes_debito'] = (int)($this->iptuFetchValue($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)) ?? 0);

            // 4.1) Inscrições de alto risco - independente do Ranking/Top N.
            // Conta todas as inscrições cujo somatório das parcelas em débito
            // sem parcela 0 ultrapassa R$ 25.000,00.
            $sql = "
                SELECT COUNT(*) AS total
                FROM (
                    SELECT cc.inscricao_reduzida, COALESCE(SUM(cp.valor), 0) AS total_debito
                    FROM {$parc} cp
                    INNER JOIN {$calc} cc ON cc.id = cp.calculo_id
                    WHERE cc.ano = :ano
                      AND cc.codigo_agrupamento = :agr
                      AND cp.situacao IN ('Débito', 'Debito')
                      AND cp.parcela <> 0
            ";
            if ($setorTexto !== null || $bairro !== null) {
                $sql .= "
                      AND EXISTS (
                          SELECT 1
                          FROM {$geo} g
                          WHERE {$join}
                ";
                if ($setorTexto !== null) $sql .= " AND g.{$gSet} = :setor";
                if ($bairro !== null) $sql .= " AND g.{$gBai} = :bairro";
                $sql .= "
                      )
                ";
            }
            $sql .= "
                    GROUP BY cc.inscricao_reduzida
                    HAVING COALESCE(SUM(cp.valor), 0) > 25000
                ) alto_risco
            ";
            $cards['inscricoes_alto_risco'] = (int)($this->iptuFetchValue($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)) ?? 0);

            // 5) Total imóveis no GEO.
            $sql = "SELECT COUNT(DISTINCT g.{$gIns}) AS total FROM {$geo} g WHERE 1=1";
            $paramsGeo = [];
            if ($setorTexto !== null) { $sql .= " AND g.{$gSet} = :setor"; $paramsGeo['setor'] = $setorTexto; }
            if ($bairro !== null) { $sql .= " AND g.{$gBai} = :bairro"; $paramsGeo['bairro'] = $bairro; }
            $cards['imo_total_cadastrados'] = (int)($this->iptuFetchValue($pdo, $sql, $paramsGeo) ?? 0);

            // 6) Imóveis com cálculo - usa EXISTS para evitar duplicidade por múltiplas geometrias.
            $sql = "
                SELECT COUNT(DISTINCT g.{$gIns}) AS total
                FROM {$geo} g
                WHERE EXISTS (
                    SELECT 1
                    FROM {$calc} cc
                    WHERE {$join}
                      AND cc.ano = :ano
                      AND cc.codigo_agrupamento = :agr
                )
            ";
            if ($setorTexto !== null) $sql .= " AND g.{$gSet} = :setor";
            if ($bairro !== null) $sql .= " AND g.{$gBai} = :bairro";
            $cards['imo_com_calculo'] = (int)($this->iptuFetchValue($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)) ?? 0);

            // 7) Comparativo ano anterior.
            $cardsPrev = null;
            $anoAnterior = $ano - 1;
            if ($anoAnterior >= 2000) {
                $cardsPrev = [
                    'ano' => $anoAnterior,
                    'total_debito' => 0.0,
                    'total_pago' => 0.0,
                    'total_lancado' => 0.0,
                ];

                $sqlDebPrev = "
                    SELECT COALESCE(SUM(cp.valor), 0) AS total
                    FROM {$parc} cp
                    WHERE cp.situacao IN ('Débito', 'Debito')
                      AND cp.parcela <> 0
                      AND EXISTS (
                          SELECT 1
                          FROM {$calc} cc
                          WHERE cc.id = cp.calculo_id
                            AND cc.ano = :ano
                            AND cc.codigo_agrupamento = :agr
                ";
                if ($setorTexto !== null || $bairro !== null) {
                    $sqlDebPrev .= "
                            AND EXISTS (
                                SELECT 1
                                FROM {$geo} g
                                WHERE {$join}
                    ";
                    if ($setorTexto !== null) $sqlDebPrev .= " AND g.{$gSet} = :setor";
                    if ($bairro !== null) $sqlDebPrev .= " AND g.{$gBai} = :bairro";
                    $sqlDebPrev .= "
                            )
                    ";
                }
                $sqlDebPrev .= "
                      )
                ";
                $cardsPrev['total_debito'] = (float)($this->iptuFetchValue($pdo, $sqlDebPrev, $this->iptuParams($anoAnterior, $agrupamento, $setorTexto, $bairro)) ?? 0);

                $sqlPagPrev = "
                    SELECT COALESCE(SUM(cp.valor), 0) AS total
                    FROM {$parc} cp
                    WHERE cp.situacao = 'Pago'
                      AND EXISTS (
                          SELECT 1
                          FROM {$calc} cc
                          WHERE cc.id = cp.calculo_id
                            AND cc.ano = :ano
                            AND cc.codigo_agrupamento = :agr
                      ";
                if ($setorTexto !== null || $bairro !== null) {
                    $sqlPagPrev .= "
                            AND EXISTS (
                                SELECT 1
                                FROM {$geo} g
                                WHERE {$join}
                    ";
                    if ($setorTexto !== null) $sqlPagPrev .= " AND g.{$gSet} = :setor";
                    if ($bairro !== null) $sqlPagPrev .= " AND g.{$gBai} = :bairro";
                    $sqlPagPrev .= "
                            )
                    ";
                }
                $sqlPagPrev .= "
                      )
                ";
                $cardsPrev['total_pago'] = (float)($this->iptuFetchValue($pdo, $sqlPagPrev, $this->iptuParams($anoAnterior, $agrupamento, $setorTexto, $bairro)) ?? 0);
                $cardsPrev['total_lancado'] = (float)$cardsPrev['total_debito'] + (float)$cardsPrev['total_pago'];
            }

            // 8) Ranking Top N.
            // Primeiro agrega as parcelas por cálculo/inscrição; depois cruza com uma visão GEO
            // deduplicada por sequencial. Isso evita multiplicar valores por quantidade de geometrias.
            $sql = "
                WITH deb AS (
                    SELECT
                        cc.inscricao_municipal,
                        cc.inscricao_reduzida,
                        cc.ano,
                        COUNT(cp.id) AS qtd_parcelas,
                        COALESCE(SUM(cp.valor), 0) AS total_debito
                    FROM {$parc} cp
                    INNER JOIN {$calc} cc ON cc.id = cp.calculo_id
                    WHERE cc.ano = :ano
                      AND cc.codigo_agrupamento = :agr
                      AND cp.situacao IN ('Débito', 'Debito')
                      AND cp.parcela <> 0
                    GROUP BY cc.inscricao_municipal, cc.inscricao_reduzida, cc.ano
                ), geo_one AS (
                    SELECT
                        NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint AS seq_norm,
                        MAX(g.{$gNom}) AS contribuinte,
                        MAX(g.{$gDoc}) AS documento,
                        MAX(g.{$gLog}) AS logradouro,
                        MAX(g.{$gNum}) AS numero,
                        MAX(g.{$gBai}) AS bairro,
                        MAX(g.{$gSet}) AS setor
                    FROM {$geo} g
                    WHERE NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '') IS NOT NULL
                    GROUP BY NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint
                )
                SELECT
                    deb.inscricao_municipal,
                    deb.inscricao_reduzida,
                    deb.ano,
                    geo_one.contribuinte,
                    geo_one.documento,
                    geo_one.logradouro,
                    geo_one.numero,
                    geo_one.bairro,
                    geo_one.setor,
                    deb.qtd_parcelas,
                    deb.total_debito
                FROM deb
                LEFT JOIN geo_one ON geo_one.seq_norm = deb.inscricao_reduzida::bigint
                WHERE deb.total_debito > 0
            ";
            if ($setorTexto !== null) $sql .= " AND geo_one.setor = :setor";
            if ($bairro !== null) $sql .= " AND geo_one.bairro = :bairro";
            if ($minDebito > 0) $sql .= " AND deb.total_debito >= :min_debito";
            $sql .= " ORDER BY deb.total_debito DESC LIMIT :limit";

            $params = $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro);
            if ($minDebito > 0) $params['min_debito'] = $minDebito;
            $params['limit'] = $limit;
            $stmt = $pdo->prepare($sql);
            foreach ($params as $k => $v) {
                $stmt->bindValue(':' . $k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
            }
            $stmt->execute();
            $datagrid = [];
            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                $datagrid[] = [
                    'inscricao_municipal' => $row['inscricao_municipal'] ?? null,
                    'inscricao_reduzida'  => $row['inscricao_reduzida'] ?? null,
                    'ano'                 => isset($row['ano']) ? (int)$row['ano'] : null,
                    'contribuinte'        => $row['contribuinte'] ?? null,
                    'documento'           => $row['documento'] ?? null,
                    'logradouro'          => $row['logradouro'] ?? null,
                    'numero'              => $row['numero'] ?? null,
                    'bairro'              => $row['bairro'] ?? null,
                    'setor'               => $row['setor'] ?? null,
                    'qtd_parcelas'        => isset($row['qtd_parcelas']) ? (int)$row['qtd_parcelas'] : 0,
                    'total_debito'        => isset($row['total_debito']) ? (float)$row['total_debito'] : 0.0,
                ];
            }

            // Se filtro PAGO for usado, o ranking de débito deve ficar vazio.
            if ($situacao === 'PAGO') {
                $datagrid = [];
            }

            // 9) Débito por setor.
            // A soma parte da tabela fiscal e cruza com GEO deduplicado: 1 sequencial = 1 setor.
            $sql = "
                WITH deb AS (
                    SELECT cc.inscricao_reduzida, COALESCE(SUM(cp.valor), 0) AS total_debito
                    FROM {$parc} cp
                    INNER JOIN {$calc} cc ON cc.id = cp.calculo_id
                    WHERE cc.ano = :ano
                      AND cc.codigo_agrupamento = :agr
                      AND cp.situacao IN ('Débito', 'Debito')
                      AND cp.parcela <> 0
                    GROUP BY cc.inscricao_reduzida
                ), geo_one AS (
                    SELECT
                        NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint AS seq_norm,
                        MAX(NULLIF(g.{$gSet}, '')) AS setor,
                        MAX(NULLIF(g.{$gBai}, '')) AS bairro
                    FROM {$geo} g
                    WHERE NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '') IS NOT NULL
                    GROUP BY NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint
                )
                SELECT geo_one.setor, COALESCE(SUM(deb.total_debito), 0) AS total_debito
                FROM deb
                INNER JOIN geo_one ON geo_one.seq_norm = deb.inscricao_reduzida::bigint
                WHERE geo_one.setor IS NOT NULL
            ";
            if ($setorTexto !== null) $sql .= " AND geo_one.setor = :setor";
            if ($bairro !== null) $sql .= " AND geo_one.bairro = :bairro";
            $sql .= " GROUP BY geo_one.setor ORDER BY geo_one.setor";
            $porSetor = array_map(fn($r) => ['setor' => $r['setor'] ?? null, 'total_debito' => (float)($r['total_debito'] ?? 0)], $this->iptuFetchAll($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)));

            // 10) Débito por bairro.
            // Mesmo princípio: agrega primeiro no fiscal e depois junta em GEO deduplicado.
            $sql = "
                WITH deb AS (
                    SELECT cc.inscricao_reduzida, COALESCE(SUM(cp.valor), 0) AS total_debito
                    FROM {$parc} cp
                    INNER JOIN {$calc} cc ON cc.id = cp.calculo_id
                    WHERE cc.ano = :ano
                      AND cc.codigo_agrupamento = :agr
                      AND cp.situacao IN ('Débito', 'Debito')
                      AND cp.parcela <> 0
                    GROUP BY cc.inscricao_reduzida
                ), geo_one AS (
                    SELECT
                        NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint AS seq_norm,
                        COALESCE(MAX(NULLIF(g.{$gBai}, '')), 'Sem bairro') AS bairro,
                        MAX(NULLIF(g.{$gSet}, '')) AS setor
                    FROM {$geo} g
                    WHERE NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '') IS NOT NULL
                    GROUP BY NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint
                )
                SELECT geo_one.bairro, COALESCE(SUM(deb.total_debito), 0) AS total_debito
                FROM deb
                INNER JOIN geo_one ON geo_one.seq_norm = deb.inscricao_reduzida::bigint
                WHERE 1=1
            ";
            if ($setorTexto !== null) $sql .= " AND geo_one.setor = :setor";
            if ($bairro !== null) $sql .= " AND geo_one.bairro = :bairro";
            $sql .= " GROUP BY geo_one.bairro ORDER BY total_debito DESC";
            $porBairroDebito = array_map(fn($r) => ['bairro' => $r['bairro'] ?? null, 'total_debito' => (float)($r['total_debito'] ?? 0)], $this->iptuFetchAll($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)));

            // 11) Pago por bairro.
            // Total pago = tudo que está com situação Pago, sem filtro de parcela e sem duplicar por GEO.
            $sql = "
                WITH pag AS (
                    SELECT cc.inscricao_reduzida, COALESCE(SUM(cp.valor), 0) AS total_pago
                    FROM {$parc} cp
                    INNER JOIN {$calc} cc ON cc.id = cp.calculo_id
                    WHERE cc.ano = :ano
                      AND cc.codigo_agrupamento = :agr
                      AND cp.situacao = 'Pago'
                    GROUP BY cc.inscricao_reduzida
                ), geo_one AS (
                    SELECT
                        NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint AS seq_norm,
                        COALESCE(MAX(NULLIF(g.{$gBai}, '')), 'Sem bairro') AS bairro,
                        MAX(NULLIF(g.{$gSet}, '')) AS setor
                    FROM {$geo} g
                    WHERE NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '') IS NOT NULL
                    GROUP BY NULLIF(regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g'), '')::bigint
                )
                SELECT geo_one.bairro, COALESCE(SUM(pag.total_pago), 0) AS total_pago
                FROM pag
                INNER JOIN geo_one ON geo_one.seq_norm = pag.inscricao_reduzida::bigint
                WHERE 1=1
            ";
            if ($setorTexto !== null) $sql .= " AND geo_one.setor = :setor";
            if ($bairro !== null) $sql .= " AND geo_one.bairro = :bairro";
            $sql .= " GROUP BY geo_one.bairro ORDER BY total_pago DESC";
            $porBairroPago = array_map(fn($r) => ['bairro' => $r['bairro'] ?? null, 'total_pago' => (float)($r['total_pago'] ?? 0)], $this->iptuFetchAll($pdo, $sql, $this->iptuParams($ano, $agrupamento, $setorTexto, $bairro)));

            // 12) Lista de setores.
            $sql = "
                SELECT DISTINCT g.{$gSet} AS setor
                FROM {$geo} g
                WHERE g.{$gSet} IS NOT NULL
                  AND g.{$gSet} <> ''
            ";
            $params = [];
            if ($bairro !== null) { $sql .= " AND g.{$gBai} = :bairro"; $params['bairro'] = $bairro; }
            $sql .= " ORDER BY g.{$gSet}";
            $setores = array_map(fn($r) => ['setor' => $r['setor'] ?? null], $this->iptuFetchAll($pdo, $sql, $params));

            Response::json([
                'success'           => true,
                'municipio'         => $cfg['municipio'] ?? null,
                'config'            => [
                    'valid' => (bool)($cfg['valid'] ?? false),
                    'warnings' => $cfg['warnings'] ?? [],
                    'geo_schema' => $cfg['geo_schema'] ?? null,
                    'geo_table' => $cfg['geo_table'] ?? null,
                ],
                'cached'            => false,
                'cache_ttl_seconds' => 0,
                'cards'             => $cards,
                'cards_prev'        => $cardsPrev,
                'datagrid'          => $datagrid,
                'por_setor'         => $porSetor,
                'por_bairro_debito' => $porBairroDebito,
                'por_bairro_pago'   => $porBairroPago,
                'setores'           => $setores,
            ]);
        } catch (Throwable $e) {
            $this->logError('bi-iptu-resumo', $e);
            Response::json([
                'success' => false,
                'message' => 'Erro ao gerar resumo de cálculos',
                'error'   => $e->getMessage(),
            ], 500);
        }
    }

    /**
     * BI Fiscal/IPTU - status do mapa.
     * Saída deliberadamente igual ao calculos_debito_mapa.php: objeto indexado pela inscrição reduzida.
     */
    public function iptuStatus(Request $request): void
    {
        try {
            $user = Auth::requireUser($request);
            $ctx = $this->biMunicipioContext($request);
            $this->assertCanUseMunicipio($user, $ctx);
            $cfg = $this->iptuConfig($request, $ctx);
            if (empty($cfg['valid'])) {
                Response::json([]);
                return;
            }

            $pdo = Database::pdo();
            $ano = (int)($request->query['ano'] ?? 2025);
            if ($ano <= 0) $ano = 2025;
            $agrupamento = (string)($request->query['agrupamento'] ?? '1');

            $saida = [];

            $addStatusItem = function (array $row) use (&$saida): void {
                $inscReduzidaRaw = $row['inscricao_reduzida'] ?? null;
                if ($inscReduzidaRaw === null || $inscReduzidaRaw === '') return;

                $digitsReduced = preg_replace('/\D+/', '', (string)$inscReduzidaRaw) ?: '';
                $digitsReducedNoZeros = ltrim($digitsReduced, '0') ?: $digitsReduced;
                if ($digitsReducedNoZeros === '') return;

                $valorDebito = (float)($row['valor_debito'] ?? $row['total_debito'] ?? 0);
                $valorPago = (float)($row['valor_pago'] ?? $row['total_pago'] ?? 0);
                $situacaoRaw = (string)($row['situacao_fiscal'] ?? $row['status'] ?? '');
                $situacaoNorm = $this->normalizeSituacaoFiscal($situacaoRaw);

                if ($valorDebito > 0 || $situacaoNorm === 'DEBITO') {
                    $status = 'Débito';
                } elseif ($valorPago > 0 || $situacaoNorm === 'PAGO') {
                    $status = 'Pago';
                } else {
                    return;
                }

                $item = [
                    'status' => $status,
                    'total_debito' => round($valorDebito, 2),
                    'inscricao_reduzida' => $digitsReducedNoZeros,
                ];

                $keys = [];
                $keys[] = $digitsReducedNoZeros;
                $keys[] = str_pad($digitsReducedNoZeros, 7, '0', STR_PAD_LEFT);
                if ($digitsReduced !== '') $keys[] = $digitsReduced;

                $inscMunicipal = preg_replace('/\D+/', '', (string)($row['inscricao_municipal'] ?? '')) ?: '';
                if ($inscMunicipal !== '') {
                    // Também publica a inscrição completa e seus 7 primeiros dígitos.
                    // Isso corrige o mapa quando a feição WFS traz "inscricao" em vez de "sequencial_contribuinte".
                    $keys[] = $inscMunicipal;
                    if (strlen($inscMunicipal) >= 7) {
                        $first7 = substr($inscMunicipal, 0, 7);
                        $first7NoZeros = ltrim($first7, '0') ?: $first7;
                        $keys[] = $first7;
                        $keys[] = $first7NoZeros;
                        $keys[] = str_pad($first7NoZeros, 7, '0', STR_PAD_LEFT);
                    }
                }

                foreach (array_values(array_unique(array_filter($keys))) as $key) {
                    $saida[(string)$key] = $item;
                }
            };

            // Caminho rápido: tabela-resumo pré-calculada, quando existir.
            $summaryTable = 'bi_iptu_resumo_imovel';
            if ($this->tableExistsInSchema((string)$cfg['calc_schema'], $summaryTable)) {
                $summaryFull = $this->qn((string)$cfg['calc_schema'], $summaryTable);
                $stmt = $pdo->prepare("
                    SELECT
                        inscricao_reduzida,
                        inscricao_municipal,
                        ano,
                        codigo_agrupamento,
                        valor_pago,
                        valor_debito,
                        situacao_fiscal
                    FROM {$summaryFull}
                    WHERE ano = :ano
                      AND CAST(codigo_agrupamento AS text) = :agr
                      AND (COALESCE(valor_debito, 0) > 0 OR COALESCE(valor_pago, 0) > 0)
                ");
                $stmt->execute(['ano' => $ano, 'agr' => $agrupamento]);
                while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                    $addStatusItem($row);
                }

                Response::json($saida);
                return;
            }

            // Fallback robusto: calcula status direto nas tabelas de cálculo/parcela.
            $calc = $cfg['calc_full'];
            $parc = $cfg['parcela_full'];

            $sql = "
                WITH base AS (
                    SELECT
                        c.inscricao_reduzida,
                        MAX(CAST(c.inscricao_municipal AS text)) AS inscricao_municipal,
                        p.parcela,
                        COALESCE(p.valor, 0)::numeric(14,2) AS valor,
                        CASE
                            WHEN translate(upper(trim(COALESCE(p.situacao, ''))), 'ÁÀÂÃÉÊÍÓÔÕÚÇ', 'AAAAEEIOOOUC') IN ('DEBITO', 'EM DEBITO', 'ABERTO') THEN 'DEBITO'
                            WHEN translate(upper(trim(COALESCE(p.situacao, ''))), 'ÁÀÂÃÉÊÍÓÔÕÚÇ', 'AAAAEEIOOOUC') IN ('PAGO', 'QUITADO', 'BAIXADO') THEN 'PAGO'
                            ELSE translate(upper(trim(COALESCE(p.situacao, ''))), 'ÁÀÂÃÉÊÍÓÔÕÚÇ', 'AAAAEEIOOOUC')
                        END AS situacao_norm
                    FROM {$calc} c
                    JOIN {$parc} p ON p.calculo_id = c.id
                    WHERE c.ano = :ano
                      AND CAST(c.codigo_agrupamento AS text) = :agr
                    GROUP BY c.inscricao_reduzida, p.id, p.parcela, p.valor, p.situacao
                ), marcada AS (
                    SELECT
                        base.*,
                        MAX(CASE WHEN COALESCE(parcela, 0) <> 0 THEN 1 ELSE 0 END)
                            OVER (PARTITION BY inscricao_reduzida) AS tem_parcelas_reais
                    FROM base
                )
                SELECT
                    inscricao_reduzida,
                    MAX(inscricao_municipal) AS inscricao_municipal,
                    COALESCE(SUM(CASE
                        WHEN tem_parcelas_reais = 1 AND parcela <> 0 AND situacao_norm = 'DEBITO' THEN valor
                        WHEN tem_parcelas_reais = 0 AND parcela = 0 AND situacao_norm = 'DEBITO' THEN valor
                        ELSE 0
                    END), 0) AS valor_debito,
                    COALESCE(SUM(CASE
                        WHEN tem_parcelas_reais = 1 AND parcela <> 0 AND situacao_norm = 'PAGO' THEN valor
                        WHEN tem_parcelas_reais = 0 AND parcela = 0 AND situacao_norm = 'PAGO' THEN valor
                        ELSE 0
                    END), 0) AS valor_pago
                FROM marcada
                GROUP BY inscricao_reduzida
            ";

            $stmt = $pdo->prepare($sql);
            $stmt->execute(['ano' => $ano, 'agr' => $agrupamento]);
            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                $row['situacao_fiscal'] = ((float)($row['valor_debito'] ?? 0) > 0) ? 'DEBITO' : (((float)($row['valor_pago'] ?? 0) > 0) ? 'PAGO' : 'SEM_INFORMACAO');
                $addStatusItem($row);
            }

            Response::json($saida);
        } catch (Throwable $e) {
            $this->logError('bi-iptu-status', $e);
            Response::json([
                'error'   => 'Erro ao consultar status para mapa',
                'message' => $e->getMessage(),
            ], 500);
        }
    }

    public function iptuMapa(Request $request): void
    {
        $this->iptuStatus($request);
    }


    /**
     * BI Fiscal/IPTU - consulta fiscal/cadastral por código do contribuinte.
     *
     * Regra oficial de integração:
     * - Mapa: mun_jequie.jequie_2025.sequencial_contribuinte
     * - BI:   mun_jequie.calculo_contribuinte.inscricao_reduzida
     * - Parcelas: mun_jequie.calculo_contribuinte.id = mun_jequie.calculo_parcela.calculo_id
     *
     * Observação importante:
     * A tabela calculo_contribuinte deste banco não possui coluna calculo_id;
     * o identificador usado pelo BI é calculo_contribuinte.id, exatamente como
     * já é usado nos endpoints /resumo e /status.
     */
    public function iptuImovel(Request $request): void
    {
        try {
            $user = Auth::requireUser($request);
            $ctx = $this->biMunicipioContext($request);
            $this->assertCanUseMunicipio($user, $ctx);
            $cfg = $this->iptuConfig($request, $ctx);

            if (empty($cfg['valid'])) {
                Response::json([
                    'success' => false,
                    'message' => 'BI/IPTU sem tabelas configuradas para o município ativo.',
                    'warnings' => $cfg['warnings'] ?? [],
                    'municipio' => $ctx,
                ]);
                return;
            }

            $ano = (int)($request->query['ano'] ?? date('Y'));
            if ($ano <= 0) $ano = 2025;
            $agrupamento = (string)($request->query['agrupamento'] ?? '1');

            $rawCodigo = trim((string)(
                $request->query['codigo_contribuinte']
                ?? $request->query['sequencial_contribuinte']
                ?? $request->query['inscricao_reduzida']
                ?? ''
            ));

            // Usado somente como fallback de resolução do código correto.
            // Não transforma inscrição completa em chave BI por substring.
            $rawInscricaoMunicipal = trim((string)(
                $request->query['inscricao_municipal']
                ?? $request->query['inscricao']
                ?? $request->query['inscricao_imobiliaria']
                ?? ''
            ));

            $calc = $cfg['calc_full'];
            $parc = $cfg['parcela_full'];
            $geo  = $cfg['geo_full'];

            $codigoContribuinte = $this->normalizeDigits($rawCodigo);
            $resolucaoOrigem = $codigoContribuinte !== '' ? 'parametro_codigo_contribuinte' : null;

            // Se o WFS não trouxe sequencial_contribuinte, resolve pelo cadastro/BI usando inscrição municipal.
            if ($codigoContribuinte === '' && $rawInscricaoMunicipal !== '') {
                $resolved = $this->resolveCodigoContribuintePorInscricaoMunicipal(
                    $rawInscricaoMunicipal,
                    $ano,
                    $agrupamento,
                    $cfg
                );
                $codigoContribuinte = $resolved['codigo'] ?? '';
                $resolucaoOrigem = $resolved['origem'] ?? null;
            }

            if ($codigoContribuinte === '' || !ctype_digit($codigoContribuinte) || (float)$codigoContribuinte > 2147483647) {
                Response::json([
                    'success' => false,
                    'message' => 'Não foi possível identificar o código do contribuinte para consultar o BI.',
                    'codigo_contribuinte_recebido' => $rawCodigo,
                    'inscricao_municipal_recebida' => $rawInscricaoMunicipal,
                    'inscricao_municipal_normalizada_sem_remover_zero' => $this->normalizeDigitsKeepZeros($rawInscricaoMunicipal),
                    'regra' => [
                        'mapa' => 'jequie_2025.sequencial_contribuinte',
                        'bi' => 'calculo_contribuinte.inscricao_reduzida',
                    ],
                ], 422);
                return;
            }

            $pdo = Database::pdo();

            // Modelo igual ao BI já existente:
            // 1) jequie_2025.sequencial_contribuinte = calculo_contribuinte.inscricao_reduzida
            // 2) calculo_contribuinte.id = calculo_parcela.calculo_id
            // 3) Lançado = valor da parcela 0
            // 4) Débito = soma das parcelas <> 0 com situação Débito/Debito/DEBITO
            // 5) Pago = soma das parcelas <> 0 com situação Pago/PAGO
            $sqlFiscal = "
                SELECT
                    c.id AS calculo_id,
                    c.inscricao_municipal,
                    c.inscricao_reduzida,
                    c.ano,
                    c.codigo_agrupamento,
                    c.abreviatura_agrupamento,
                    p.id AS parcela_id,
                    p.parcela,
                    p.situacao,
                    p.valor,
                    p.data_vencimento,
                    CASE
                        WHEN UPPER(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(CAST(p.situacao AS text), '')), 'É', 'E'), 'Ê', 'E'), 'Á', 'A'))
                             IN ('DEBITO', 'EM DEBITO', 'ABERTO') THEN 'DEBITO'
                        WHEN UPPER(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(CAST(p.situacao AS text), '')), 'É', 'E'), 'Ê', 'E'), 'Á', 'A'))
                             IN ('PAGO', 'QUITADO', 'BAIXADO') THEN 'PAGO'
                        ELSE UPPER(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(CAST(p.situacao AS text), '')), 'É', 'E'), 'Ê', 'E'), 'Á', 'A'))
                    END AS situacao_norm_sql
                FROM {$calc} c
                LEFT JOIN {$parc} p ON p.calculo_id = c.id
                WHERE c.ano = :ano
                  AND CAST(c.codigo_agrupamento AS text) = :agr
                  AND regexp_replace(CAST(c.inscricao_reduzida AS text), '[^0-9]', '', 'g') = :codigo
                ORDER BY c.id, p.parcela NULLS LAST, p.id
            ";

            $rows = $this->iptuFetchAll($pdo, $sqlFiscal, [
                'ano' => $ano,
                'agr' => $agrupamento,
                'codigo' => $codigoContribuinte,
            ]);

            $totalLancado = 0.0;
            $totalPago = 0.0;
            $totalDebito = 0.0;
            $parcelas = [];
            $calculos = [];
            $situacoes = [];
            $inscricaoMunicipalBi = null;

            foreach ($rows as $row) {
                $calculoId = (string)($row['calculo_id'] ?? '');
                if ($calculoId !== '') $calculos[$calculoId] = true;
                if (!$inscricaoMunicipalBi && !empty($row['inscricao_municipal'])) {
                    $inscricaoMunicipalBi = (string)$row['inscricao_municipal'];
                }

                $parcela = isset($row['parcela']) ? (int)$row['parcela'] : null;
                $situacaoRaw = trim((string)($row['situacao'] ?? ''));
                $situacaoNorm = trim((string)($row['situacao_norm_sql'] ?? ''));
                if ($situacaoNorm === '') $situacaoNorm = $this->normalizeSituacaoFiscal($situacaoRaw);
                $valor = (float)($row['valor'] ?? 0);
                if ($situacaoNorm !== '') $situacoes[$situacaoNorm] = true;

                // BASE DO BI: parcela 0 é o valor lançado, independente de estar Pago ou Débito.
               // LANÇADO: soma de todas as parcelas 0.
                if ((int)$parcela === 0) {
                    $totalLancado += $valor;
                }

                // PAGO: soma somente parcelas reais, sem parcela 0.
                if ($situacaoNorm === 'PAGO' && (int)$parcela !== 0) {
                    $totalPago += $valor;
                }

                // DÉBITO: soma somente parcelas reais em Débito, sem parcela 0.
                // Ex.: parcelas 1 a 9, 3 a 9, etc.
                if ($situacaoNorm === 'DEBITO' && (int)$parcela !== 0) {
                    $totalDebito += $valor;
                }

                if ($row['parcela_id'] !== null) {
                    $parcelas[] = [
                        'id' => $row['parcela_id'],
                        'calculo_id' => $row['calculo_id'],
                        'parcela' => $parcela,
                        'situacao' => $situacaoRaw,
                        'situacao_normalizada' => $situacaoNorm,
                        'valor' => $valor,
                        'data_vencimento' => $row['data_vencimento'] ?? null,
                    ];
                }
            }

            $situacaoFiscal = 'SEM_INFORMACAO';
            if ($totalDebito > 0) {
                $situacaoFiscal = 'DEBITO';
            } elseif ($totalPago > 0 || isset($situacoes['PAGO'])) {
                $situacaoFiscal = 'PAGO';
            } elseif (count($rows) > 0) {
                $situacaoFiscal = 'SEM_DEBITO';
            }

            $cadastro = $this->fetchCadastroPorCodigoContribuinte(
                $codigoContribuinte,
                $inscricaoMunicipalBi ?: $rawInscricaoMunicipal,
                $cfg
            );

            Response::json([
                'success' => true,
                'codigo_contribuinte_consultado' => $codigoContribuinte,
                'inscricao_reduzida_usada' => $codigoContribuinte,
                'inscricao_municipal_consultada' => $rawInscricaoMunicipal ?: $inscricaoMunicipalBi,
                'origem_codigo_contribuinte' => $resolucaoOrigem,
                'calculo_id_coluna_usada' => 'id',
                'chave_integracao' => [
                    'mapa' => 'jequie_2025.sequencial_contribuinte',
                    'bi' => 'calculo_contribuinte.inscricao_reduzida',
                    'parcelas' => 'calculo_contribuinte.id = calculo_parcela.calculo_id',
                    'valor' => $codigoContribuinte,
                ],
                'municipio' => $ctx,
                'ano' => $ano,
                'agrupamento' => $agrupamento,
                'situacao_fiscal' => $situacaoFiscal,
                'resumo' => [
                    'valor_lancado' => round($totalLancado, 2),
                    'valor_pago' => round($totalPago, 2),
                    'valor_debito' => round($totalDebito, 2),
                    'qtd_calculos' => count($calculos),
                    'qtd_parcelas' => count($parcelas),
                    'qtd_parcelas_pagas' => count(array_filter($parcelas, static fn($p) => ($p['situacao_normalizada'] ?? '') === 'PAGO')),
                    'qtd_parcelas_debito' => count(array_filter($parcelas, static fn($p) => ($p['situacao_normalizada'] ?? '') === 'DEBITO' && (int)($p['parcela'] ?? 0) !== 0)),
                ],
                'cadastro' => $cadastro,
                'parcelas' => $parcelas,
                'has_fiscal_data' => count($rows) > 0,
                'warnings' => $cfg['warnings'] ?? [],
            ]);
        } catch (Throwable $e) {
            $this->logError('bi-iptu-imovel', $e);
            Response::json([
                'success' => false,
                'message' => 'Erro ao consultar dados fiscais do imóvel.',
                'details' => $e->getMessage(),
                'hint' => 'Modelo BI: lançado=parcela 0; débito=parcelas <> 0 em DEBITO; calculo_contribuinte.id = calculo_parcela.calculo_id.',
            ], 500);
        }
    }

    private function normalizeDigits(mixed $value): string
    {
        $digits = preg_replace('/\D+/', '', (string)($value ?? ''));
        $digits = ltrim((string)$digits, '0');
        return $digits === '' ? '' : $digits;
    }

    private function normalizeDigitsKeepZeros(mixed $value): string
    {
        $digits = preg_replace('/\D+/', '', (string)($value ?? ''));
        return (string)($digits ?? '');
    }

    private function resolveCodigoContribuintePorInscricaoMunicipal(string $inscricaoMunicipal, int $ano, string $agrupamento, array $cfg): array
    {
        // Inscrição municipal é a inscrição completa do imóvel e pode começar com zero.
        // Por isso, aqui NÃO removemos zeros à esquerda.
        $inscricaoDigits = $this->normalizeDigitsKeepZeros($inscricaoMunicipal);
        if ($inscricaoDigits === '') return ['codigo' => '', 'origem' => null];

        $calc = $cfg['calc_full'];
        $geo = $cfg['geo_full'];

        // 1) Modelo do BI: calculo_contribuinte.inscricao_municipal guarda a inscrição completa
        // e calculo_contribuinte.inscricao_reduzida guarda o código do contribuinte.
        // Primeiro tenta no ano/agrupamento selecionados.
        $row = Database::fetchOne("
            SELECT regexp_replace(CAST(c.inscricao_reduzida AS text), '[^0-9]', '', 'g') AS codigo
            FROM {$calc} c
            WHERE c.ano = :ano
              AND CAST(c.codigo_agrupamento AS text) = :agr
              AND regexp_replace(CAST(c.inscricao_municipal AS text), '[^0-9]', '', 'g') = :inscricao
            ORDER BY c.id
            LIMIT 1
        ", [
            'ano' => $ano,
            'agr' => $agrupamento,
            'inscricao' => $inscricaoDigits,
        ]);
        if ($row && !empty($row['codigo'])) {
            return ['codigo' => $this->normalizeDigits($row['codigo']), 'origem' => 'calculo_contribuinte.inscricao_municipal_ano'];
        }

        // 2) Se não houver lançamento no ano selecionado, resolve pelo próprio BI em qualquer ano.
        // Isso permite descobrir o código do contribuinte e depois consultar parcelas do ano escolhido.
        $row = Database::fetchOne("
            SELECT regexp_replace(CAST(c.inscricao_reduzida AS text), '[^0-9]', '', 'g') AS codigo
            FROM {$calc} c
            WHERE regexp_replace(CAST(c.inscricao_municipal AS text), '[^0-9]', '', 'g') = :inscricao
            ORDER BY c.ano DESC NULLS LAST, c.id DESC
            LIMIT 1
        ", [
            'inscricao' => $inscricaoDigits,
        ]);
        if ($row && !empty($row['codigo'])) {
            return ['codigo' => $this->normalizeDigits($row['codigo']), 'origem' => 'calculo_contribuinte.inscricao_municipal_qualquer_ano'];
        }

        // 3) Fallback pelo GEO: jequie_2025.inscricao -> jequie_2025.sequencial_contribuinte.
        // A coluna configurada BI_IPTU_GEO_COL_INSCRICAO deve ser sequencial_contribuinte.
        if ($this->columnExistsInSchema($cfg['geo_schema'], $cfg['geo_table'], 'inscricao')) {
            $row = Database::fetchOne("
                SELECT regexp_replace(CAST(g.{$cfg['geo_col_inscricao_q']} AS text), '[^0-9]', '', 'g') AS codigo
                FROM {$geo} g
                WHERE regexp_replace(CAST(g." . $this->qi('inscricao') . " AS text), '[^0-9]', '', 'g') = :inscricao
                LIMIT 1
            ", ['inscricao' => $inscricaoDigits]);
            if ($row && !empty($row['codigo'])) {
                return ['codigo' => $this->normalizeDigits($row['codigo']), 'origem' => 'jequie_2025.inscricao_para_sequencial_contribuinte'];
            }
        }

        return ['codigo' => '', 'origem' => null];
    }

    private function fetchCadastroPorCodigoContribuinte(string $codigoContribuinte, ?string $inscricaoMunicipal, array $cfg): ?array
    {
        $geo = $cfg['geo_full'];
        $gIns = $cfg['geo_col_inscricao_q'];
        $gSet = $cfg['geo_col_setor_q'];
        $gBai = $cfg['geo_col_bairro_q'];
        $gNom = $cfg['geo_col_nome_q'];
        $gDoc = $cfg['geo_col_documento_q'];
        $gLog = $cfg['geo_col_logradouro_q'];
        $gNum = $cfg['geo_col_numero_q'];

        $selectInscricaoVisual = $this->columnExistsInSchema($cfg['geo_schema'], $cfg['geo_table'], 'inscricao')
            ? 'CAST(g.' . $this->qi('inscricao') . ' AS text)'
            : 'NULL';

        $sql = "
            SELECT
                CAST(g.{$gIns} AS text) AS sequencial_contribuinte,
                CAST(g.{$gIns} AS text) AS codigo_contribuinte,
                CAST(g.{$gIns} AS text) AS inscricao_reduzida,
                {$selectInscricaoVisual} AS inscricao,
                CAST(g.{$gSet} AS text) AS setor,
                CAST(g.{$gBai} AS text) AS bairro,
                CAST(g.{$gNom} AS text) AS contribuinte,
                CAST(g.{$gDoc} AS text) AS documento,
                CAST(g.{$gLog} AS text) AS logradouro,
                CAST(g.{$gNum} AS text) AS numero
            FROM {$geo} g
            WHERE regexp_replace(CAST(g.{$gIns} AS text), '[^0-9]', '', 'g') = :codigo
        ";
        $params = ['codigo' => $codigoContribuinte];

        if ($inscricaoMunicipal && $this->columnExistsInSchema($cfg['geo_schema'], $cfg['geo_table'], 'inscricao')) {
            $sql .= " OR regexp_replace(CAST(g." . $this->qi('inscricao') . " AS text), '[^0-9]', '', 'g') = :inscricao";
            $params['inscricao'] = $this->normalizeDigits($inscricaoMunicipal);
        }

        $sql .= " LIMIT 1";

        return Database::fetchOne($sql, $params) ?: null;
    }

    private function normalizeSituacaoFiscal(string $situacao): string
    {
        $s = strtoupper(trim($situacao));
        $s = str_replace(['É', 'Ê'], 'E', $s);
        if (in_array($s, ['PAGO', 'QUITADO', 'BAIXADO'], true)) return 'PAGO';
        if (in_array($s, ['DEBITO', 'DÉBITO', 'ABERTO', 'EM DEBITO', 'EM DÉBITO'], true)) return 'DEBITO';
        return $s;
    }


    public function clearCache(Request $request): void
    {
        Response::json([
            'success' => true,
            'message' => 'Cache do BI/IPTU limpo. Esta versão usa cálculo direto no modelo Adianti.',
        ]);
    }

    public function iptuConfigEndpoint(Request $request): void
    {
        try {
            $user = Auth::requireUser($request);
            $ctx = $this->biMunicipioContext($request);
            $this->assertCanUseMunicipio($user, $ctx);
            $cfg = $this->iptuConfig($request, $ctx);

            Response::json([
                'success' => true,
                'municipio' => $cfg['municipio'] ?? null,
                'config' => [
                    'valid' => (bool)($cfg['valid'] ?? false),
                    'warnings' => $cfg['warnings'] ?? [],
                    'geo_schema' => $cfg['geo_schema'] ?? null,
                    'geo_table' => $cfg['geo_table'] ?? null,
                ],
                'municipio' => $ctx,
                'config' => [
                    'valid' => (bool)($cfg['valid'] ?? false),
                    'warnings' => $cfg['warnings'] ?? [],
                    'calc_schema' => $cfg['calc_schema'] ?? null,
                    'calc_table' => $cfg['contrib_table'] ?? null,
                    'parcela_table' => $cfg['parcela_table'] ?? null,
                    'geo_schema' => $cfg['geo_schema'] ?? null,
                    'geo_table' => $cfg['geo_table'] ?? null,
                    'geoserver_url' => $cfg['geoserver_url'] ?? null,
                    'wms_url' => $cfg['wms_url'] ?? null,
                    'wfs_typenames' => $cfg['wfs_typenames'] ?? [],
                    'ortho_layer' => $cfg['ortho_layer'] ?? null,
                    'default_center' => $cfg['default_center'] ?? [-40.083, -13.85],
                    'default_zoom' => $cfg['default_zoom'] ?? 13,
                    'geo_columns' => [
                        'inscricao' => $cfg['geo_col_inscricao'] ?? null,
                        'setor' => $cfg['geo_col_setor'] ?? null,
                        'bairro' => $cfg['geo_col_bairro'] ?? null,
                        'nome' => $cfg['geo_col_nome'] ?? null,
                        'documento' => $cfg['geo_col_documento'] ?? null,
                        'logradouro' => $cfg['geo_col_logradouro'] ?? null,
                        'numero' => $cfg['geo_col_numero'] ?? null,
                    ],
                ],
            ]);
        } catch (Throwable $e) {
            $this->logError('bi-iptu-config', $e);
            Response::json([
                'success' => false,
                'message' => 'Erro ao carregar configuração do BI/IPTU',
                'details' => $e->getMessage(),
                'hint' => 'Verifique storage/logs/api-error.log. Esta versão remove parâmetros PDO extras e usa parâmetros separados para a consulta fiscal e cadastral.',
            ], 500);
        }
    }

    private function iptuConfig(Request $request, ?array $ctx = null): array
    {
        $ctx = $ctx ?: $this->biMunicipioContext($request);
        $key = (string)($ctx['key'] ?? '');
        $schemaDefault = (string)($ctx['schema_name'] ?? ('mun_' . ($key ?: 'jequie')));

        $calcSchema = $this->biMunicipioEnv($ctx, 'BI_IPTU_CALC_SCHEMA', $schemaDefault);
        $contribTable = $this->biMunicipioEnv($ctx, 'BI_IPTU_CONTRIB_TABLE', 'calculo_contribuinte');
        $parcelaTable = $this->biMunicipioEnv($ctx, 'BI_IPTU_PARCELA_TABLE', 'calculo_parcela');
        $geoSchema = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_SCHEMA', $schemaDefault);
        $geoTable = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_TABLE', $this->defaultGeoTable($ctx));

        $geoColInscricao = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_INSCRICAO', 'sequencial_contribuinte');
        $geoColSetor = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_SETOR', 'setor');
        $geoColBairro = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_BAIRRO', 'bairro');
        $geoColNome = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_NOME', 'name');
        $geoColDocumento = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_DOCUMENTO', 'numero_doc');
        $geoColLogradouro = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_LOGRADOURO', 'logradouro');
        $geoColNumero = $this->biMunicipioEnv($ctx, 'BI_IPTU_GEO_COL_NUMERO', 'numero_lot');

        $geoserverUrl = rtrim((string)$this->biMunicipioEnv($ctx, 'GEOSERVER_URL', 'https://sisprom-br.com.br:8443/geoserver/sisprom'), '/');
        $wfsUrl = $this->biMunicipioEnv($ctx, 'BI_IPTU_WFS_URL', $geoserverUrl . '/wfs');
        $wmsUrl = $this->biMunicipioEnv($ctx, 'BI_IPTU_WMS_URL', $geoserverUrl . '/wms');
        $orthoLayer = $this->biMunicipioEnv($ctx, 'BI_IPTU_ORTHO_LAYER', (string)$this->biMunicipioEnv($ctx, 'LAYER_ORTOFOTO', ''));
        $wfsTypeNamesRaw = $this->biMunicipioEnv($ctx, 'BI_IPTU_WFS_TYPENAMES', (string)$this->biMunicipioEnv($ctx, 'LAYER_LOTES', ''));
        $wfsTypeNames = $this->splitCsv($wfsTypeNamesRaw);
        if (!$wfsTypeNames) {
            $wfsTypeNames = ['sisprom:' . $geoTable];
        }

        $centerRaw = (string)$this->biMunicipioEnv($ctx, 'BI_IPTU_CENTER', $this->municipioCenterFallback($ctx));
        $center = $this->parseCenter($centerRaw);
        $zoom = (int)$this->biMunicipioEnv($ctx, 'BI_IPTU_ZOOM', (string)$this->biMunicipioEnv($ctx, 'ZOOM', '13'));
        if ($zoom <= 0) $zoom = 13;

        $warnings = [];
        $valid = true;
        foreach ([
            [$calcSchema, $contribTable, 'Tabela de cálculo/contribuinte'],
            [$calcSchema, $parcelaTable, 'Tabela de parcelas'],
            [$geoSchema, $geoTable, 'Tabela geográfica/cadastral'],
        ] as [$schema, $table, $label]) {
            if (!$this->tableExistsInSchema((string)$schema, (string)$table)) {
                $valid = false;
                $warnings[] = $label . ' não encontrada: ' . $schema . '.' . $table;
            }
        }

        return [
            'valid' => $valid,
            'warnings' => $warnings,
            'municipio' => $ctx,
            'calc_schema' => $calcSchema,
            'contrib_table' => $contribTable,
            'parcela_table' => $parcelaTable,
            'geo_schema' => $geoSchema,
            'geo_table' => $geoTable,
            'calc_full' => $this->qn($calcSchema, $contribTable),
            'parcela_full' => $this->qn($calcSchema, $parcelaTable),
            'geo_full' => $this->qn($geoSchema, $geoTable),
            'geo_col_inscricao' => $geoColInscricao,
            'geo_col_inscricao_q' => $this->qi($geoColInscricao),
            'geo_col_setor' => $geoColSetor,
            'geo_col_setor_q' => $this->qi($geoColSetor),
            'geo_col_bairro' => $geoColBairro,
            'geo_col_bairro_q' => $this->qi($geoColBairro),
            'geo_col_nome' => $geoColNome,
            'geo_col_nome_q' => $this->qi($geoColNome),
            'geo_col_documento' => $geoColDocumento,
            'geo_col_documento_q' => $this->qi($geoColDocumento),
            'geo_col_logradouro' => $geoColLogradouro,
            'geo_col_logradouro_q' => $this->qi($geoColLogradouro),
            'geo_col_numero' => $geoColNumero,
            'geo_col_numero_q' => $this->qi($geoColNumero),
            'geoserver_url' => $geoserverUrl,
            'wfs_url' => $wfsUrl,
            'wms_url' => $wmsUrl,
            'wfs_typenames' => $wfsTypeNames,
            'ortho_layer' => $orthoLayer,
            'default_center' => $center,
            'default_zoom' => $zoom,
        ];
    }

    private function iptuJoinCondition(string $geoAlias, string $calcAlias, array $cfg): string
    {
        $col = $cfg['geo_col_inscricao_q'];

        // Mesmo vínculo do Adianti, com tolerância para sequencial no WFS vir com zero à esquerda.
        return "NULLIF(regexp_replace({$geoAlias}.{$col}::text, '[^0-9]', '', 'g'), '')::bigint = {$calcAlias}.inscricao_reduzida::bigint";
    }

    private function iptuParams(int $ano, string $agrupamento, ?string $setorTexto, ?string $bairro): array
    {
        $params = ['ano' => $ano, 'agr' => $agrupamento];
        if ($setorTexto !== null) $params['setor'] = $setorTexto;
        if ($bairro !== null) $params['bairro'] = $bairro;
        return $params;
    }


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

        if ($raw === '') {
            Response::json([
                'success' => false,
                'message' => 'Selecione um município para carregar o BI Fiscal.',
            ], 400);
            exit;
        }

        $row = $this->findMunicipioForBi($raw);
        if (!$row) {
            Response::json([
                'success' => false,
                'message' => 'Município não encontrado para o BI Fiscal.',
                'municipio' => $raw,
            ], 404);
            exit;
        }

        $nome = (string)($row['nome'] ?? '');
        $slug = $this->slug((string)($row['slug'] ?? $row['codigo'] ?? $nome ?: $raw));
        $schema = trim((string)($row['schema_name'] ?? ''));
        if ($schema === '') $schema = 'mun_' . $slug;

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

    private function assertCanUseMunicipio(array $user, array $ctx): void
    {
        $userId = (string)($user['id'] ?? '');
        $municipioId = (string)($ctx['id'] ?? '');
        if ($userId === '' || $municipioId === '') {
            Response::error('Acesso municipal inválido.', 403);
            exit;
        }

        if ($this->isSuperAdmin($userId)) return;

        $sql = "SELECT 1 FROM " . $this->qi('usuario_municipios') . " WHERE user_id = :u AND municipio_id = :m AND ativo IS TRUE LIMIT 1";
        $row = Database::fetchOne($sql, ['u' => $userId, 'm' => $municipioId]);
        if (!$row) {
            Response::error('Usuário sem vínculo ativo com o município selecionado.', 403);
            exit;
        }
    }

    private function findMunicipioForBi(string $value): ?array
    {
        $v = trim($value);
        if ($v === '') return null;
        $slug = $this->slug($v);
        $slugHyphen = str_replace('_', '-', $slug);
        $schema = Env::get('DB_SCHEMA', 'gestao_sisprom') ?: 'gestao_sisprom';

        foreach (['municipios', 'municipalities'] as $table) {
            if (!$this->tableExistsInSchema((string)$schema, $table)) continue;
            $full = $this->qn((string)$schema, $table);
            if ($table === 'municipios') {
                $sql = "SELECT id, nome, uf, slug, codigo, codigo_ibge, schema_name
                        FROM {$full}
                        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
                        LIMIT 1";
            } else {
                $sql = "SELECT id, name AS nome, NULL::varchar AS uf, slug, code AS codigo, NULL::varchar AS codigo_ibge, schema_name
                        FROM {$full}
                        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
                        LIMIT 1";
            }
            $row = Database::fetchOne($sql, ['value' => $v, 'slug' => $slug, 'slug_hyphen' => $slugHyphen]);
            if ($row) return $row;
        }
        return null;
    }

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

        // Segurança multi-município: configurações BI_IPTU nunca devem herdar valores globais
        // de outro município. Se não existir configuração específica, usa apenas o default seguro.
        if (str_starts_with($suffix, 'BI_IPTU_')) {
            return $default;
        }

        return Env::get($suffix, $default);
    }

    private function defaultGeoTable(array $ctx): string
    {
        $slug = (string)($ctx['key'] ?? 'jequie');
        $year = date('Y');
        foreach ([$slug . '_' . $year, $slug . '_2025', 'cadastro_' . $year . '_postgis', 'cadastro_2026_postgis', 'jequie_2025'] as $candidate) {
            $candidate = preg_replace('/[^A-Za-z0-9_]/', '_', $candidate) ?: '';
            if ($candidate !== '') return $candidate;
        }
        return $slug . '_2025';
    }

    private function splitCsv(?string $value): array
    {
        $items = [];
        foreach (explode(',', (string)$value) as $item) {
            $item = trim($item);
            if ($item !== '') $items[] = $item;
        }
        return array_values(array_unique($items));
    }


    private function municipioCenterFallback(array $ctx): string
    {
        $lon = $this->biMunicipioEnv($ctx, 'CENTER_LON', null);
        $lat = $this->biMunicipioEnv($ctx, 'CENTER_LAT', null);
        if ($lon !== null && $lat !== null && is_numeric($lon) && is_numeric($lat)) {
            return $lon . ',' . $lat;
        }
        return '-40.083,-13.85';
    }

    private function parseCenter(string $value): array
    {
        $parts = array_map('trim', explode(',', $value));
        if (count($parts) >= 2 && is_numeric($parts[0]) && is_numeric($parts[1])) {
            return [(float)$parts[0], (float)$parts[1]];
        }
        return [-40.083, -13.85];
    }

    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','II','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), '_');
        $aliases = ['ipia_u' => 'ipiau', 'ipia' => 'ipiau', 'jequi' => 'jequie', 'jequie_ba' => 'jequie'];
        return $aliases[$v] ?? ($v ?: 'jequie');
    }

    private function columnExistsInSchema(string $schema, string $table, string $column): bool
    {
        return (bool)Database::fetchOne(
            'SELECT 1 FROM information_schema.columns WHERE table_schema = :s AND table_name = :t AND column_name = :c LIMIT 1',
            ['s' => $schema, 't' => $table, 'c' => $column]
        );
    }

    private function tableExistsInSchema(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 emptyIptuResumo(array $cfg, string $message = 'Sem dados para o município ativo.'): array
    {
        return [
            'success' => true,
            'message' => $message,
            'municipio' => $cfg['municipio'] ?? null,
            'config' => [
                'valid' => (bool)($cfg['valid'] ?? false),
                'warnings' => $cfg['warnings'] ?? [],
            ],
            'cards' => [
                'total_calculos' => 0,
                'total_debito' => 0,
                'total_pago' => 0,
                'total_lancado' => 0,
                'inscricoes_debito' => 0,
                'inscricoes_alto_risco' => 0,
                'imo_total_cadastrados' => 0,
                'imo_com_calculo' => 0,
            ],
            'cards_prev' => [],
            'ranking' => [],
            'por_setor' => [],
            'por_bairro_debito' => [],
            'por_bairro_pago' => [],
            'filtros' => [
                'setores' => [],
                'bairros' => [],
            ],
        ];
    }

    private function iptuFetchValue(\PDO $pdo, string $sql, array $params = []): mixed
    {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$row) return null;
        return reset($row);
    }

    private function iptuFetchAll(\PDO $pdo, string $sql, array $params = []): array
    {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function qn(string $schema, string $table): string
    {
        return $this->qi($schema) . '.' . $this->qi($table);
    }


    private function kpis(bool $isSuperAdmin, ?string $municipalityId): array
    {
        $whereMunicipio = (!$isSuperAdmin && $municipalityId) ? ' WHERE municipio_id = :m' : '';
        $bindM = (!$isSuperAdmin && $municipalityId) ? ['m' => $municipalityId] : [];

        return [
            'municipios_total' => $isSuperAdmin ? $this->count('municipios') : 1,
            'municipios_ativos' => $isSuperAdmin ? $this->countWhenColumn('municipios', 'ativo', 'ativo IS TRUE') : 1,
            'municipios_bloqueados' => $isSuperAdmin ? $this->countWhenColumn('municipios', 'bloqueado', 'bloqueado IS TRUE') : 0,
            'usuarios_total' => $this->countScoped('profiles', $isSuperAdmin, $municipalityId),
            'usuarios_ativos' => $this->hasColumn('profiles', 'bloqueado') ? $this->countScoped('profiles', $isSuperAdmin, $municipalityId, 'bloqueado IS NOT TRUE') : $this->countScoped('profiles', $isSuperAdmin, $municipalityId),
            'usuarios_bloqueados' => $this->hasColumn('profiles', 'bloqueado') ? $this->countScoped('profiles', $isSuperAdmin, $municipalityId, 'bloqueado IS TRUE') : 0,
            'secretarias' => $this->countScoped('secretarias', $isSuperAdmin, $municipalityId),
            'departamentos' => $this->countScoped('departamentos', $isSuperAdmin, $municipalityId),
            'setores' => $this->countScoped('setores', $isSuperAdmin, $municipalityId),
            'grupos' => $this->countScoped('grupos_trabalho', $isSuperAdmin, $municipalityId),
            'perfis_customizados' => $this->countScoped('perfis_customizados', $isSuperAdmin, $municipalityId),
            'modulos_ativos' => $this->hasColumn('municipio_modulos', 'ativo') ? $this->countScoped('municipio_modulos', $isSuperAdmin, $municipalityId, 'ativo IS TRUE') : $this->countScoped('municipio_modulos', $isSuperAdmin, $municipalityId),
            'auditoria_total' => $this->countScoped('auditoria', $isSuperAdmin, $municipalityId),
            'auditoria_hoje' => $this->hasColumn('auditoria', 'created_at') ? $this->countScoped('auditoria', $isSuperAdmin, $municipalityId, "created_at >= date_trunc('day', now())") : 0,
            'logins_hoje' => $this->hasColumn('sessoes_login', 'iniciada_em') ? $this->countScoped('sessoes_login', $isSuperAdmin, $municipalityId, "iniciada_em >= date_trunc('day', now())") : 0,
            'tentativas_falhas_24h' => $this->countTentativasFalhas($isSuperAdmin, $municipalityId),
            'imoveis_ctm' => $this->countScoped('ctm_lotes', $isSuperAdmin, $municipalityId),
        ];
    }

    private function charts(bool $isSuperAdmin, ?string $municipalityId): array
    {
        return [
            'estrutura' => [
                ['label' => 'Secretarias', 'total' => $this->countScoped('secretarias', $isSuperAdmin, $municipalityId)],
                ['label' => 'Departamentos', 'total' => $this->countScoped('departamentos', $isSuperAdmin, $municipalityId)],
                ['label' => 'Setores', 'total' => $this->countScoped('setores', $isSuperAdmin, $municipalityId)],
                ['label' => 'Grupos', 'total' => $this->countScoped('grupos_trabalho', $isSuperAdmin, $municipalityId)],
            ],
            'usuarios_por_municipio' => $this->usuariosPorMunicipio($isSuperAdmin, $municipalityId),
            'auditoria_por_acao' => $this->groupCount('auditoria', 'acao', $isSuperAdmin, $municipalityId, 12),
            'logins_7_dias' => $this->series7Dias('sessoes_login', 'iniciada_em', $isSuperAdmin, $municipalityId),
            'auditoria_7_dias' => $this->series7Dias('auditoria', 'created_at', $isSuperAdmin, $municipalityId),
            'modulos_por_municipio' => $this->modulosPorMunicipio($isSuperAdmin, $municipalityId),
            'lotes_por_bairro' => $this->lotesPorBairro($isSuperAdmin, $municipalityId),
        ];
    }

    private function isSuperAdmin(string $userId): bool
    {
        if ($userId === '') return false;
        if (!$this->tableExists('user_roles')) return false;
        $row = Database::fetchOne("SELECT 1 FROM user_roles WHERE user_id = :id AND role = 'super_admin' LIMIT 1", ['id' => $userId]);
        return (bool)$row;
    }

    private function count(string $table, ?string $extraWhere = null, array $params = []): int
    {
        if (!$this->tableExists($table)) return 0;
        $sql = 'SELECT count(*) AS total FROM ' . $this->qi($table);
        if ($extraWhere) $sql .= ' WHERE ' . $extraWhere;
        return (int)(Database::fetchOne($sql, $params)['total'] ?? 0);
    }

    private function countWhenColumn(string $table, string $column, string $where): int
    {
        if (!$this->tableExists($table) || !$this->hasColumn($table, $column)) return 0;
        return $this->count($table, $where);
    }

    private function countScoped(string $table, bool $isSuperAdmin, ?string $municipalityId, ?string $extraWhere = null): int
    {
        if (!$this->tableExists($table)) return 0;
        $params = [];
        $where = [];
        if (!$isSuperAdmin && $municipalityId && $this->hasColumn($table, 'municipio_id')) {
            $where[] = 'municipio_id = :m';
            $params['m'] = $municipalityId;
        }
        if ($extraWhere) $where[] = $extraWhere;
        $sql = 'SELECT count(*) AS total FROM ' . $this->qi($table) . ($where ? ' WHERE ' . implode(' AND ', $where) : '');
        return (int)(Database::fetchOne($sql, $params)['total'] ?? 0);
    }

    private function countTentativasFalhas(bool $isSuperAdmin, ?string $municipalityId): int
    {
        if (!$this->tableExists('tentativas_login')) return 0;
        $params = [];
        $where = ["created_at >= now() - interval '24 hours'"];
        if ($this->hasColumn('tentativas_login', 'sucesso')) {
            $where[] = 'sucesso IS NOT TRUE';
        }
        if (!$isSuperAdmin && $municipalityId && $this->hasColumn('tentativas_login', 'municipio_id')) {
            $where[] = 'municipio_id = :m';
            $params['m'] = $municipalityId;
        }
        $sql = 'SELECT count(*) AS total FROM tentativas_login WHERE ' . implode(' AND ', $where);
        return (int)(Database::fetchOne($sql, $params)['total'] ?? 0);
    }

    private function usuariosPorMunicipio(bool $isSuperAdmin, ?string $municipalityId): array
    {
        if (!$this->tableExists('profiles')) return [];

        $hasMunicipioId = $this->hasColumn('profiles', 'municipio_id');
        $hasPerfil = $this->hasColumn('profiles', 'perfil');
        $hasRole = $this->hasColumn('profiles', 'role');
        $labelExpr = $hasPerfil && $hasRole
            ? "COALESCE(perfil::text, role::text, 'Usuários')"
            : ($hasPerfil ? "COALESCE(perfil::text, 'Usuários')" : ($hasRole ? "COALESCE(role::text, 'Usuários')" : "'Usuários'"));

        if ($isSuperAdmin && $this->tableExists('municipios') && $hasMunicipioId && $this->hasColumn('municipios', 'id') && $this->hasColumn('municipios', 'nome')) {
            return Database::fetchAll("SELECT COALESCE(m.nome, 'Sem município') AS label, count(p.*)::int AS total
                FROM profiles p
                LEFT JOIN municipios m ON m.id = p.municipio_id
                GROUP BY COALESCE(m.nome, 'Sem município')
                ORDER BY total DESC, label ASC
                LIMIT 12");
        }

        $where = (!$isSuperAdmin && $municipalityId && $hasMunicipioId) ? 'WHERE municipio_id = :m' : '';
        return Database::fetchAll("SELECT {$labelExpr} AS label, count(*)::int AS total FROM profiles {$where} GROUP BY 1 ORDER BY total DESC LIMIT 12", $where ? ['m' => $municipalityId] : []);
    }

    private function groupCount(string $table, string $column, bool $isSuperAdmin, ?string $municipalityId, int $limit = 12): array
    {
        if (!$this->tableExists($table) || !$this->hasColumn($table, $column)) return [];
        $params = [];
        $where = [];
        if (!$isSuperAdmin && $municipalityId && $this->hasColumn($table, 'municipio_id')) {
            $where[] = 'municipio_id = :m';
            $params['m'] = $municipalityId;
        }
        $sql = 'SELECT COALESCE(' . $this->qi($column) . "::text, 'Não informado') AS label, count(*)::int AS total FROM " . $this->qi($table) .
            ($where ? ' WHERE ' . implode(' AND ', $where) : '') .
            ' GROUP BY 1 ORDER BY total DESC, label ASC LIMIT ' . max(1, $limit);
        return Database::fetchAll($sql, $params);
    }

    private function series7Dias(string $table, string $dateColumn, bool $isSuperAdmin, ?string $municipalityId): array
    {
        if (!$this->tableExists($table) || !$this->hasColumn($table, $dateColumn)) return [];
        $params = [];
        $where = [$this->qi($dateColumn) . " >= current_date - interval '6 days'"];
        if (!$isSuperAdmin && $municipalityId && $this->hasColumn($table, 'municipio_id')) {
            $where[] = 'municipio_id = :m';
            $params['m'] = $municipalityId;
        }
        $sql = "SELECT to_char(d::date, 'DD/MM') AS label, COALESCE(x.total, 0)::int AS total
            FROM generate_series(current_date - interval '6 days', current_date, interval '1 day') d
            LEFT JOIN (
                SELECT date_trunc('day', {$this->qi($dateColumn)})::date AS dia, count(*)::int AS total
                FROM {$this->qi($table)}
                WHERE " . implode(' AND ', $where) . "
                GROUP BY 1
            ) x ON x.dia = d::date
            ORDER BY d::date";
        return Database::fetchAll($sql, $params);
    }

    private function modulosPorMunicipio(bool $isSuperAdmin, ?string $municipalityId): array
    {
        if (!$this->tableExists('municipio_modulos')) return [];
        $params = [];
        $where = [];
        if ($this->hasColumn('municipio_modulos', 'ativo')) {
            $where[] = 'mm.ativo IS TRUE';
        }
        if (!$isSuperAdmin && $municipalityId && $this->hasColumn('municipio_modulos', 'municipio_id')) {
            $where[] = 'mm.municipio_id = :m';
            $params['m'] = $municipalityId;
        }
        $whereSql = $where ? ' WHERE ' . implode(' AND ', $where) : '';
        if ($this->tableExists('municipios') && $this->hasColumn('municipio_modulos', 'municipio_id') && $this->hasColumn('municipios', 'id') && $this->hasColumn('municipios', 'nome')) {
            $sql = "SELECT COALESCE(m.nome, mm.municipio_id::text) AS label, count(*)::int AS total
                FROM municipio_modulos mm
                LEFT JOIN municipios m ON m.id = mm.municipio_id
                {$whereSql}
                GROUP BY 1 ORDER BY total DESC, label ASC LIMIT 12";
            return Database::fetchAll($sql, $params);
        }
        if ($this->hasColumn('municipio_modulos', 'municipio_id')) {
            return $this->groupCount('municipio_modulos', 'municipio_id', $isSuperAdmin, $municipalityId, 12);
        }
        return [['label' => 'Módulos', 'total' => $this->countScoped('municipio_modulos', $isSuperAdmin, $municipalityId)]];
    }

    private function lotesPorBairro(bool $isSuperAdmin, ?string $municipalityId): array
    {
        if (!$this->tableExists('ctm_lotes')) return [];
        $bairroCol = $this->hasColumn('ctm_lotes', 'bairro') ? 'bairro' : ($this->hasColumn('ctm_lotes', 'nome_bairro') ? 'nome_bairro' : null);
        if (!$bairroCol) return [];
        $params = [];
        $where = [];
        if (!$isSuperAdmin && $municipalityId && $this->hasColumn('ctm_lotes', 'municipio_id')) {
            $where[] = 'municipio_id = :m';
            $params['m'] = $municipalityId;
        }
        $sql = 'SELECT COALESCE(' . $this->qi($bairroCol) . "::text, 'Não informado') AS label, count(*)::int AS total FROM ctm_lotes" .
            ($where ? ' WHERE ' . implode(' AND ', $where) : '') .
            ' GROUP BY 1 ORDER BY total DESC, label ASC LIMIT 12';
        return Database::fetchAll($sql, $params);
    }

    private function emptyResponse(bool $isSuperAdmin, ?string $municipalityId): array
    {
        return [
            'scope' => $isSuperAdmin ? 'global' : 'municipal',
            'is_super_admin' => $isSuperAdmin,
            'municipality_id' => $municipalityId,
            'generated_at' => date(DATE_ATOM),
            'kpis' => [
                'municipios_total' => 0, 'municipios_ativos' => 0, 'municipios_bloqueados' => 0,
                'usuarios_total' => 0, 'usuarios_ativos' => 0, 'usuarios_bloqueados' => 0,
                'secretarias' => 0, 'departamentos' => 0, 'setores' => 0, 'grupos' => 0,
                'perfis_customizados' => 0, 'modulos_ativos' => 0, 'auditoria_total' => 0,
                'auditoria_hoje' => 0, 'logins_hoje' => 0, 'tentativas_falhas_24h' => 0, 'imoveis_ctm' => 0,
            ],
            'charts' => [
                'estrutura' => [], 'usuarios_por_municipio' => [], 'auditoria_por_acao' => [],
                'logins_7_dias' => [], 'auditoria_7_dias' => [], 'modulos_por_municipio' => [], 'lotes_por_bairro' => [],
            ],
        ];
    }

    private function tableExists(string $table): bool
    {
        $row = Database::fetchOne('SELECT 1 FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = :table LIMIT 1', ['table' => $table]);
        return (bool)$row;
    }

    private function hasColumn(string $table, string $column): bool
    {
        if (!$this->tableExists($table)) return false;
        static $cache = [];
        if (!isset($cache[$table])) {
            $rows = Database::fetchAll('SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = :table', ['table' => $table]);
            $cache[$table] = array_column($rows, 'column_name');
        }
        return in_array($column, $cache[$table], true);
    }

    private function qi(string $id): string
    {
        return '"' . str_replace('"', '""', $id) . '"';
    }

    private function logError(string $context, Throwable $e): void
    {
        $root = dirname(__DIR__, 2);
        $dir = $root . 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) . "] {$context}: " . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine() . PHP_EOL, FILE_APPEND);
    }
}
