-- ---------------------------------------------------------------------------
-- A PONTUACAO DE TODOS RECALCULADA PELO HISTORICO, UMA VEZ, NO CORTE (07/11/2026, a fatia 106 da fila dos debitos de
-- pe, RECALCULO-DA-PONTUACAO-NO-CORTE; o D284 e o D312). Duas respostas do dono, nesta ordem:
--
--   a 230 (a), "Acertar no corte, junto com o recalculo da 123": antes de recalcular todos, o passo cria o saldo
--   zerado onde falta, e o recalculo poe os pontos la; "so acrescenta linhas, no mesmo passo, contadas antes e depois";
--   a 123 (a), "Recalcular todos no corte": quem tem pontos a mais na coluna perde o excesso, que o resgate ja nao
--   pagava, e todo saldo passa a bater com o historico.
--
--   mysql franquia_producao < /var/www/franquiabv.com.br/public_html/scripts/recalcular-pontuacao-no-corte.sql | tee /root/recalcular-pontuacao-<data>.txt; echo "o mysql saiu com ${PIPESTATUS[0]}"
--   docker exec -i franquiabv-mysql-1 mysql -uroot -proot <banco descartavel> < scripts/recalcular-pontuacao-no-corte.sql
--
-- POR QUE. A coluna `clientes_creditos.pontuacao` e o saldo de pontos do cliente em cada unidade, e o historico
-- (`pontuacoes_historicos`) e de onde ela sai: `ClienteService::atualizarPontuacao` grava em cada linha de saldo do
-- cliente a soma das linhas `ativa` da mesma unidade (o `selecionarPontuacaoValidadePorClienteUnidade`, zero sem
-- nenhuma). O codigo antigo deixava a coluna torta de dois jeitos, ja consertados no codigo novo: o cancelamento
-- tirava REAIS, e nao os pontos (o D284, consertado em 01/10/2026); e os 10 pontos da indicacao iam para a coluna da
-- unidade do cadastro de quem indicou, com a linha do historico na unidade da venda (o D312, a 92 (b), 15/10/2026). O
-- saldo torto so se acerta no proximo recalculo de cada cliente; este roteiro faz o de todos de uma vez.
--
-- A 230 (a) VEM PRIMEIRO, e e o que impede o recalculo de tirar ponto de alguem: quem tem linha `ativa` numa unidade
-- onde nao tem linha de saldo nao tem onde o recalculo por os pontos dela -- o `atualizarPontuacao` so percorre as
-- linhas de saldo que existem. O passo 1 cria a linha de saldo ZERADA (cliente, unidade) de cada par assim, como o
-- codigo novo a cria (`new ClienteCredito`: valor e valor_pf zero, pontuacao zero, `pontuacao_validade` no dia), e o
-- passo 2 a enche com a soma. Vale para todo tipo de ponto, e nao so a indicacao (a letra da 230: "na unidade de cada
-- registro de pontos ainda valido que nao tem saldo la"). O total da pessoa nao muda: os 10 saem da unidade do
-- cadastro e entram na da compra. Na copia, 1 par, de 10 pontos (o lancamento de indicacao de 31/07/2026 do D312).
--
-- O RECALCULO E A MESMA CONTA DO `atualizarPontuacao`, para TODAS as linhas de saldo: a soma de `total` das linhas de
-- `status` 'ativa' do mesmo (cliente, unidade), ou zero. As linhas 'resgatada', 'inativa' e 'expirada' nao contam. O
-- PAR GEMEO (o D72: tres pares com duas linhas de saldo na copia) recebe a soma nas duas, como o `atualizarPontuacao`
-- faz; o passo 1 nao cria a terceira. A pontuacao NULA (zero na copia) vai a zero, como o `(int)` do servico.
--
-- SO A COLUNA `pontuacao`, e as linhas novas do passo 1. NADA DE `valor` NEM DE `valor_pf`: o credito em dinheiro --
-- tambem o dos pontos de venda cancelada que ja viraram credito -- fica com o cliente (a 123 (a), "pela mesma logica da
-- sua 68"). Sem DELETE, sem coluna nova; nenhuma linha do historico e tocada.
--
-- ONDE RODA: no passo 7 da etapa 1 (`docs/procedimento-de-corte-etapa-1.md`), depois da troca do codigo e logo depois
-- do consumo dos clientes, com a aplicacao em manutencao: ninguem vende, cancela nem resgata no meio, e o codigo novo no
-- lugar ja nao deixa a coluna torta. NAO no laco do passo 0: muda dado, e o passo 0 e so esquema (a 83 (a)). Na etapa 2
-- a carga leva o numero recalculado, e o irmao `postgres/recalcular-pontuacao-no-corte.sql` (no 7d de
-- `docs/procedimento-de-corte.md`) conta o que o intervalo entre as duas deixou. NA COPIA LOCAL NAO RODA: so
-- num banco descartavel, para ensaiar (a 123 (a): "so vale para o corte").
--
-- A CONTAGEM VEM ANTES, para quem opera ler o numero da producao: as linhas de saldo; os pares (cliente, unidade) com
-- pontos `ativa` e sem saldo, e os pontos deles; e, das linhas de saldo que existem, quantas diferem do historico,
-- quantas estao acima e abaixo, quantas nulas, e o liquido (a coluna menos o historico: positivo e o excesso que sai).
-- Na copia (07/11/2026, so SELECT): 1 par sem saldo, de 10 pontos; das 256.987 linhas de saldo, no MySQL de
-- desenvolvimento 7.793 diferem (7.789 acima, 4 abaixo, +790.297) -- quase todas pares com linhas que expiraram no
-- proprio ambiente, depois do retrato (o D284) --, e no PostgreSQL da copia, que nenhuma rotina tocou, 28 (22 acima, 6
-- abaixo, +5.092). NAO E PONTO DE PARADA: a resposta e recalcular todos.
--
-- AS LISTAS, ANTES DE CADA ESCRITA: os pares sem saldo, com os pontos; as linhas de saldo criadas (o id, o cliente e a
-- unidade); e cada linha de saldo que muda, com o numero de antes e o novo. So ids e numeros: nenhum nome, telefone nem
-- e-mail. O `tee` da linha de cima as guarda: e o caminho de volta exato.
--
-- IDEMPOTENTE: depois do passo 2 toda linha de saldo e a soma, e todo par com ponto `ativa` tem a sua; 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 (pares sem saldo, pontos, divergentes, acima, abaixo, nulos, liquido) zero.
--
-- PARA DESFAZER, pela lista guardada: `UPDATE clientes_creditos SET pontuacao = <o de antes> WHERE id = <id>;` para
-- cada linha da lista do que muda, e `DELETE FROM clientes_creditos WHERE id = <id>;` para cada linha criada (zerada
-- no nascimento; so se ninguem a usou depois). Numa volta ao codigo antigo, o numero recalculado FICA: o antigo le a
-- mesma coluna e recalcula pela mesma soma.
-- ---------------------------------------------------------------------------

-- 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.cliente = h.cliente AND x.unidade = h.unidade)
                               GROUP BY 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.cliente = h.cliente AND x.unidade = h.unidade)) AS pontos_sem_saldo,
       COUNT(*) AS divergentes,
       COALESCE(SUM(CASE WHEN COALESCE(c.pontuacao, 0) > COALESCE(s.soma, 0) THEN 1 ELSE 0 END), 0) AS acima,
       COALESCE(SUM(CASE WHEN COALESCE(c.pontuacao, 0) < COALESCE(s.soma, 0) THEN 1 ELSE 0 END), 0) AS abaixo,
       COALESCE(SUM(CASE WHEN c.pontuacao IS NULL THEN 1 ELSE 0 END), 0) AS nulos,
       COALESCE(SUM(COALESCE(c.pontuacao, 0) - COALESCE(s.soma, 0)), 0) AS liquido
  FROM clientes_creditos c
  LEFT JOIN (SELECT h.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.cliente, h.unidade) s ON s.cliente = c.cliente AND s.unidade = c.unidade
 WHERE NOT (c.pontuacao <=> 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.cliente = h.cliente AND x.unidade = h.unidade)
 GROUP BY h.cliente, h.unidade
 ORDER BY h.cliente, h.unidade;

START TRANSACTION;

-- O MAIOR id DE ANTES, para listar as linhas que o passo 3 cria (id acima dele). NAO E ANCORA DE JANELA (o
-- `conferir-ancora-de-medicao.php`, o D167): nao mede nada, so separa o que nasce agora, e o AUTO_INCREMENT da
-- linha nova passa de qualquer id que exista -- tambem o de uma linha de fixture (90000000 em diante), num banco
-- descartavel montado da copia. O alvo do roteiro e a producao, que nao tem fixture.
SET @ultimo_id = (SELECT COALESCE(MAX(id), 0) FROM clientes_creditos);

-- 3. A 230 (a): a linha de saldo zerada de cada par sem saldo, e a lista das criadas (o caminho de volta).
INSERT INTO clientes_creditos (unidade, cliente, valor, valor_pf, pontuacao, pontuacao_validade, data_criacao)
SELECT 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.cliente = h.cliente AND x.unidade = h.unidade)
 GROUP BY h.unidade, h.cliente;

SELECT id, cliente, unidade FROM clientes_creditos WHERE id > @ultimo_id 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.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.cliente, h.unidade) s ON s.cliente = c.cliente AND s.unidade = c.unidade
 WHERE NOT (c.pontuacao <=> 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.
UPDATE clientes_creditos c
  LEFT JOIN (SELECT h.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.cliente, h.unidade) s ON s.cliente = c.cliente AND s.unidade = c.unidade
   SET c.pontuacao = COALESCE(s.soma, 0)
 WHERE NOT (c.pontuacao <=> COALESCE(s.soma, 0));

SELECT ROW_COUNT() AS recalculados_agora;

COMMIT;

-- 6. A conferencia: A MESMA CONTAGEM DO PASSO 1, depois (o bloco da fatia: "contagem antes e depois (divergentes,
--    acima, abaixo, liquido)"). 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 -- e a mesma coluna do passo 1 diz quantas ficaram acima, abaixo, nulas e o
--    liquido, sem precisar de outra consulta na janela.
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.cliente = h.cliente AND x.unidade = h.unidade)
                               GROUP BY 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.cliente = h.cliente AND x.unidade = h.unidade)) AS pontos_sem_saldo_depois,
       COUNT(*) AS divergentes_depois,
       COALESCE(SUM(CASE WHEN COALESCE(c.pontuacao, 0) > COALESCE(s.soma, 0) THEN 1 ELSE 0 END), 0) AS acima_depois,
       COALESCE(SUM(CASE WHEN COALESCE(c.pontuacao, 0) < COALESCE(s.soma, 0) THEN 1 ELSE 0 END), 0) AS abaixo_depois,
       COALESCE(SUM(CASE WHEN c.pontuacao IS NULL THEN 1 ELSE 0 END), 0) 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.cliente, h.unidade, SUM(h.total) AS soma FROM pontuacoes_historicos h
              WHERE h.status = 'ativa' GROUP BY h.cliente, h.unidade) s ON s.cliente = c.cliente AND s.unidade = c.unidade
 WHERE NOT (c.pontuacao <=> COALESCE(s.soma, 0));