-- ---------------------------------------------------------------------------
-- O irmao no PostgreSQL de `scripts/recalcular-pontuacao-no-corte.sql` (07/11/2026, a fatia 106 da fila dos debitos de
-- pe, RECALCULO-DA-PONTUACAO-NO-CORTE; o D284 e o D312; as respostas 230 (a) e 123 (a) do dono): a linha de saldo
-- zerada de cada par (cliente, unidade) com pontos `ativa` e sem saldo, e depois a pontuacao de todas as linhas de saldo
-- recalculada pela soma das linhas `ativa`, com a contagem antes, as listas do que muda e a conferencia. As mesmas
-- regras, o mesmo porque e o mesmo caminho de volta: leia o cabecalho do irmao.
--
--   psql -v ON_ERROR_STOP=1 -f postgres/recalcular-pontuacao-no-corte.sql | tee /root/recalcular-pontuacao-pg-<data>.txt
--   docker exec -i franquiabv-postgres-1 sh -c 'psql -U "$POSTGRES_USER" -d <banco descartavel> -v ON_ERROR_STOP=1' \
--     < postgres/recalcular-pontuacao-no-corte.sql
--
-- ONDE RODA: no 7d de `docs/procedimento-de-corte.md`, com a aplicacao nova apontada e antes de liberar. COM A ETAPA 1
-- FEITA, o MySQL ja foi recalculado no passo 7 de la, e a carga (`migrar-dados.sh`) trouxe o numero recalculado: este
-- roteiro conta e acerta so o que o intervalo entre as duas etapas deixou (o codigo novo ja grava a coluna e a linha na
-- mesma unidade, e o esperado e pouco ou nada). No CORTE DE UMA VEZ SO, e ele que recalcula todos. NA COPIA
-- LOCAL NAO RODA: so num banco descartavel (a 123 (a): "so vale para o corte").
--
-- O INQUILINO: o par e (inquilino, cliente, unidade), e a linha criada leva o `inquilino_id` da linha do historico. As
-- tabelas tem RLS forcado. COMO O DONO SUPERUSUARIO -- o `psql` do 7d --, o RLS nao vale e o `SET` abaixo nao filtra: o
-- roteiro conta e acerta TODO inquilino (no corte, so o 1). Como o `bvip_app`, com o `SET`, so o inquilino 1; sem o
-- `SET`, para no primeiro SELECT sem mudar nada. A CHAVE DO INQUILINO em cada par (o `NOT EXISTS` e o `JOIN` da soma)
-- nao muda o resultado com o dado coerente, com um inquilino ou com mais: `clientes.id` e `unidades.id` sao chave
-- primaria sozinhos, e o mesmo (cliente, unidade) nao existe em dois inquilinos. Ela fica porque o par e o do esquema
-- (os indices sao `(inquilino_id, cliente)` e `(inquilino_id, unidade)`) e porque uma linha incoerente -- o historico
-- com um inquilino e o cliente com outro -- nao leva a soma de um inquilino ao saldo do outro.
--
-- AS LINHAS CRIADAS saem da propria escrita (`RETURNING`): o id, o cliente e a unidade -- o caminho de volta. O `now()`
-- e o da transacao, o mesmo instante em todas.
--
-- IDEMPOTENTE: a segunda rodada conta zero, cria nada e muda nada. A conferencia no fim repete a contagem de antes:
-- as linhas de saldo sao as de antes mais as criadas, e todo o resto zero. SO A COLUNA
-- `pontuacao`, e as linhas novas; `valor` e `valor_pf` nao sao tocados. Nenhum DELETE, nenhuma coluna nova.
-- ---------------------------------------------------------------------------

SET app.inquilino_id = '1';

-- 0. AS ESTATISTICAS DAS DUAS TABELAS, ANTES DE TUDO (medido em 07/11/2026, num banco descartavel com as duas tabelas
--    da copia recem-carregadas): sem elas o planejador estima 2.460 linhas `ativa` (sao 225.209) e procura o saldo de
--    cada uma pelo indice da unidade -- a contagem de antes levou 61 s; com elas, menos de 1 s. A carga do passo 4
--    deixa as tabelas assim ate o autovacuum passar. So le e grava estatistica: nenhuma linha muda.
ANALYZE clientes_creditos;
ANALYZE pontuacoes_historicos;

-- 1. A contagem, antes.
SELECT (SELECT COUNT(*) FROM clientes_creditos) AS linhas_de_saldo,
       (SELECT COUNT(*) FROM (SELECT 1 FROM pontuacoes_historicos h
                               WHERE h.status = 'ativa'
                                 AND NOT EXISTS (SELECT 1 FROM clientes_creditos x
                                                  WHERE x.inquilino_id = h.inquilino_id AND x.cliente = h.cliente AND x.unidade = h.unidade)
                               GROUP BY h.inquilino_id, h.cliente, h.unidade) p) AS pares_sem_saldo,
       (SELECT COALESCE(SUM(h.total), 0) FROM pontuacoes_historicos h
         WHERE h.status = 'ativa'
           AND NOT EXISTS (SELECT 1 FROM clientes_creditos x
                            WHERE x.inquilino_id = h.inquilino_id AND x.cliente = h.cliente AND x.unidade = h.unidade)) AS pontos_sem_saldo,
       COUNT(*) AS divergentes,
       COUNT(*) FILTER (WHERE COALESCE(c.pontuacao, 0) > COALESCE(s.soma, 0)) AS acima,
       COUNT(*) FILTER (WHERE COALESCE(c.pontuacao, 0) < COALESCE(s.soma, 0)) AS abaixo,
       COUNT(*) FILTER (WHERE c.pontuacao IS NULL) AS nulos,
       COALESCE(SUM(COALESCE(c.pontuacao, 0) - COALESCE(s.soma, 0)), 0) AS liquido
  FROM clientes_creditos c
  LEFT JOIN (SELECT h.inquilino_id, h.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.inquilino_id, h.cliente, h.unidade) s
         ON s.inquilino_id = c.inquilino_id AND s.cliente = c.cliente AND s.unidade = c.unidade
 WHERE c.pontuacao IS DISTINCT FROM COALESCE(s.soma, 0);

-- 2. A lista dos pares sem saldo, com os pontos `ativa` de cada um.
SELECT h.cliente, h.unidade, SUM(h.total) AS pontos_ativos
  FROM pontuacoes_historicos h
 WHERE h.status = 'ativa'
   AND NOT EXISTS (SELECT 1 FROM clientes_creditos x
                    WHERE x.inquilino_id = h.inquilino_id AND x.cliente = h.cliente AND x.unidade = h.unidade)
 GROUP BY h.inquilino_id, h.cliente, h.unidade
 ORDER BY h.cliente, h.unidade;

BEGIN;

-- 3. A 230 (a): a linha de saldo zerada de cada par sem saldo, e a lista das criadas (o caminho de volta).
WITH criadas AS (
    INSERT INTO clientes_creditos (inquilino_id, unidade, cliente, valor, valor_pf, pontuacao, pontuacao_validade, data_criacao)
    SELECT h.inquilino_id, h.unidade, h.cliente, 0, 0, 0, CURRENT_DATE, now()
      FROM pontuacoes_historicos h
     WHERE h.status = 'ativa'
       AND NOT EXISTS (SELECT 1 FROM clientes_creditos x
                        WHERE x.inquilino_id = h.inquilino_id AND x.cliente = h.cliente AND x.unidade = h.unidade)
     GROUP BY h.inquilino_id, h.unidade, h.cliente
 RETURNING id, cliente, unidade
)
SELECT id, cliente, unidade FROM criadas ORDER BY id;

-- 4. A lista do que muda, com o numero de antes (o caminho de volta): tambem as linhas que o passo 3 criou.
SELECT c.id, c.cliente, c.unidade, c.pontuacao AS pontuacao_de_antes, COALESCE(s.soma, 0) AS pontuacao_nova
  FROM clientes_creditos c
  LEFT JOIN (SELECT h.inquilino_id, h.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.inquilino_id, h.cliente, h.unidade) s
         ON s.inquilino_id = c.inquilino_id AND s.cliente = c.cliente AND s.unidade = c.unidade
 WHERE c.pontuacao IS DISTINCT FROM COALESCE(s.soma, 0)
 ORDER BY c.id;

-- 5. A 123 (a): a soma das linhas `ativa` em toda linha de saldo que difere dela.
WITH alvo AS (
    SELECT c.id, COALESCE(s.soma, 0) AS nova
      FROM clientes_creditos c
      LEFT JOIN (SELECT h.inquilino_id, h.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
                  WHERE h.status = 'ativa' GROUP BY h.inquilino_id, h.cliente, h.unidade) s
             ON s.inquilino_id = c.inquilino_id AND s.cliente = c.cliente AND s.unidade = c.unidade
     WHERE c.pontuacao IS DISTINCT FROM COALESCE(s.soma, 0)
), feitos AS (
    UPDATE clientes_creditos c
       SET pontuacao = a.nova
      FROM alvo a
     WHERE c.id = a.id
 RETURNING c.id
)
SELECT COUNT(*) AS recalculados_agora FROM feitos;

COMMIT;

-- 6. A conferencia: A MESMA CONTAGEM DO PASSO 1, depois. O esperado: as linhas de saldo sao as de antes mais as
--    criadas no passo 3, e todo o resto zero; outro numero e PARE (leia o irmao).
SELECT (SELECT COUNT(*) FROM clientes_creditos) AS linhas_de_saldo_depois,
       (SELECT COUNT(*) FROM (SELECT 1 FROM pontuacoes_historicos h
                               WHERE h.status = 'ativa'
                                 AND NOT EXISTS (SELECT 1 FROM clientes_creditos x
                                                  WHERE x.inquilino_id = h.inquilino_id AND x.cliente = h.cliente AND x.unidade = h.unidade)
                               GROUP BY h.inquilino_id, h.cliente, h.unidade) p) AS pares_sem_saldo_depois,
       (SELECT COALESCE(SUM(h.total), 0) FROM pontuacoes_historicos h
         WHERE h.status = 'ativa'
           AND NOT EXISTS (SELECT 1 FROM clientes_creditos x
                            WHERE x.inquilino_id = h.inquilino_id AND x.cliente = h.cliente AND x.unidade = h.unidade)) AS pontos_sem_saldo_depois,
       COUNT(*) AS divergentes_depois,
       COUNT(*) FILTER (WHERE COALESCE(c.pontuacao, 0) > COALESCE(s.soma, 0)) AS acima_depois,
       COUNT(*) FILTER (WHERE COALESCE(c.pontuacao, 0) < COALESCE(s.soma, 0)) AS abaixo_depois,
       COUNT(*) FILTER (WHERE c.pontuacao IS NULL) AS nulos_depois,
       COALESCE(SUM(COALESCE(c.pontuacao, 0) - COALESCE(s.soma, 0)), 0) AS liquido_depois
  FROM clientes_creditos c
  LEFT JOIN (SELECT h.inquilino_id, h.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.inquilino_id, h.cliente, h.unidade) s
         ON s.inquilino_id = c.inquilino_id AND s.cliente = c.cliente AND s.unidade = c.unidade
 WHERE c.pontuacao IS DISTINCT FROM COALESCE(s.soma, 0);