-- Mede o TEXTO das campanhas de Marketing > Notificacoes na PRODUCAO, so com numeros.
--
-- Da pasta do repositorio, na maquina de quem roda (nada vai para o servidor, e
-- a saida fica aqui -- a forma do `docs/procedimento-de-corte-etapa-1.md`, "O que
-- se le na producao"):
--
--     ssh root@<a producao> 'mysql --default-character-set=utf8mb4 -t franquia_producao' < scripts/medir-texto-das-campanhas.sql > texto-das-campanhas.txt
--
-- O ARQUIVO E SO ASCII, e a primeira linha fixa a conversa em utf8mb4 (40b, a
-- verificacao, 25/10/2026): com o cliente em latin1 -- o padrao de um terminal
-- sem locale UTF-8 --, o intervalo das letras acentuadas da secao 5, em UTF-8, chegava
-- ao servidor como outro intervalo, invertido, e o roteiro parava ali com o
-- ERROR 3697, sem as secoes 5 a 8. Os acentos das expressoes estao em escape
-- (`\\x{00C0}`), e a ULTIMA linha da saida e "=== FIM ===": sem ela, o roteiro
-- parou no meio, e o erro foi so para a tela, e nao para o arquivo.
--
-- SO LE. Nenhum INSERT, UPDATE, DELETE nem DDL: a sessao abre em
-- `SET SESSION TRANSACTION READ ONLY` e termina em `ROLLBACK`, e qualquer
-- escrita que alguem acrescente aqui por engano para no "Cannot execute
-- statement in a READ ONLY transaction". NENHUM TEXTO, TITULO, LINK, NOME NEM
-- TELEFONE sai: so contagens, tamanhos e medias, agrupados por canal e por
-- ano. Pode rodar na producao (de preferencia numa das replicas) sem alterar
-- nada. Leva segundos: `notificacoes` tem poucos milhares de linhas, e nenhuma
-- consulta le `notificacoes_clientes`.
--
-- POR QUE ISTO EXISTE (25/10/2026, a resposta 259 (a) do dono; o D297 e o
-- desenho `docs/f3-telas-restantes/tela-notificacoes.md`, secao 4, pergunta 6).
-- A tela nova das campanhas conta os caracteres do texto e mostra a mensagem
-- como chega ao cliente. Falta saber o que as unidades escrevem: o tamanho, se
-- usam as quatro etiquetas que a campanha troca, se escrevem alguma que nao
-- existe -- que chega ao cliente escrita assim, com os sinais -- e se usam
-- emoji. A COPIA LOCAL NAO DIZ: o anonimizador troca o texto por um fixo
-- (`scripts/anonimizar.sql.modelo`), e os textos da copia tem todos 20
-- caracteres. Rodado no `franquiabv_dev` em 25/10/2026, este roteiro devolve
-- numeros que NAO VALEM para a decisao (o resultado da copia esta no desenho,
-- so para provar que ele roda). Com os numeros da producao, o teto do texto e
-- a regra da etiqueta desconhecida (avisar ou recusar) sao propostos ao dono.
--
-- AS ETIQUETAS. A campanha troca so quatro (`TagProcessorService::processarTags`
-- sem contexto, `module/ISMarketing/src/Service/TagProcessorService.php`):
-- #CLIENTE_NOME#, #NOME_BARBEIRO_ULTIMO_SERVICO#, #DATA_ULTIMO_SERVICO# e
-- #PONTUACAO_ATUAL#; a campanha para colaboradores, so a primeira. As do
-- contexto (`processarTagsContexto`, a lista inteira: #LINK#, #CODIGO# e o
-- mesmo com o O acentuado, #COLABORADOR_NOME#, #PONTOS#, #VALOR_REAIS#,
-- #VENDA_ID#, #PORCENTAGEM#, #PRODUTO_NOME#, #DATA#, e #HORARIO# com e sem o
-- A acentuado) sao de outras mensagens: na campanha chegam ao cliente como
-- estao escritas. A troca e `str_replace`, e por
-- isso a caixa conta: `#cliente_nome#` nao e trocada. As comparacoes abaixo
-- usam o modo 'c' do REGEXP_LIKE pelo mesmo motivo: a coluna e
-- `utf8mb4_unicode_ci`, que ignora a caixa, e o `LIKE BINARY` NAO a respeita
-- aqui (medido em 25/10/2026: `'#cliente_nome#' LIKE BINARY '%#CLIENTE_NOME#%'`
-- deu 1 no MySQL 8.4 da copia).
--
-- O EMOJI. O caractere fora do plano basico (U+10000 em diante: quase todo
-- emoji) e contado a parte dos simbolos do plano basico mais usados como emoji
-- (U+2600 a U+27BF: o sol, o telefone, o check, a estrela). A coluna e
-- `utf8mb4` (`mysql.sql`, `notificacoes.conteudo`), entao os dois chegam
-- inteiros ao banco.
--
-- O `id < 90000000` tira as fixtures de teste (na producao, nenhuma).

SET NAMES utf8mb4;
SET SESSION TRANSACTION READ ONLY;
START TRANSACTION;

SELECT '=== 1. o universo: campanhas por canal e ano ===' AS '';

SELECT tipo                                   AS canal,
       YEAR(data_criacao)                     AS ano,
       COUNT(*)                               AS campanhas,
       SUM(conteudo IS NULL OR conteudo = '') AS sem_texto
  FROM notificacoes
 WHERE id < 90000000
 GROUP BY tipo, YEAR(data_criacao)
 ORDER BY tipo, ano;

SELECT '=== 2. o tamanho do texto (caracteres), por canal e ano ===' AS '';

SELECT tipo                                          AS canal,
       YEAR(data_criacao)                            AS ano,
       MAX(CHAR_LENGTH(conteudo))                    AS maior,
       ROUND(AVG(CHAR_LENGTH(conteudo)))             AS medio,
       SUM(CHAR_LENGTH(conteudo) <= 160)             AS ate_160,
       SUM(CHAR_LENGTH(conteudo) BETWEEN 161 AND 500)  AS de_161_a_500,
       SUM(CHAR_LENGTH(conteudo) BETWEEN 501 AND 1000) AS de_501_a_1000,
       SUM(CHAR_LENGTH(conteudo) BETWEEN 1001 AND 2000) AS de_1001_a_2000,
       SUM(CHAR_LENGTH(conteudo) > 2000)             AS acima_de_2000
  FROM notificacoes
 WHERE id < 90000000 AND conteudo IS NOT NULL AND conteudo <> ''
 GROUP BY tipo, YEAR(data_criacao)
 ORDER BY tipo, ano;

SELECT '=== 3. o tamanho do texto: os percentis do WhatsApp de 2025 e 2026 ===' AS '';

-- O 50o, o 90o e o 99o, pela posicao na fila ordenada (sem texto nenhum).
SELECT ano,
       MAX(CASE WHEN pos <= CEIL(total * 0.50) THEN tam END) AS p50,
       MAX(CASE WHEN pos <= CEIL(total * 0.90) THEN tam END) AS p90,
       MAX(CASE WHEN pos <= CEIL(total * 0.99) THEN tam END) AS p99,
       MAX(total)                                            AS campanhas
  FROM (SELECT YEAR(data_criacao) AS ano,
               CHAR_LENGTH(conteudo) AS tam,
               ROW_NUMBER() OVER (PARTITION BY YEAR(data_criacao) ORDER BY CHAR_LENGTH(conteudo)) AS pos,
               COUNT(*) OVER (PARTITION BY YEAR(data_criacao)) AS total
          FROM notificacoes
         WHERE id < 90000000 AND tipo = 'whatsapp' AND conteudo IS NOT NULL AND conteudo <> ''
           AND YEAR(data_criacao) IN (2025, 2026)) t
 GROUP BY ano
 ORDER BY ano;

SELECT '=== 4. as quatro etiquetas que a campanha troca: quantas campanhas usam cada uma ===' AS '';

SELECT tipo                                                              AS canal,
       destinatario                                                      AS para,
       COUNT(*)                                                          AS campanhas,
       SUM(REGEXP_LIKE(conteudo, '#CLIENTE_NOME#', 'c'))           AS cliente_nome,
       SUM(REGEXP_LIKE(conteudo, '#NOME_BARBEIRO_ULTIMO_SERVICO#', 'c')) AS nome_barbeiro,
       SUM(REGEXP_LIKE(conteudo, '#DATA_ULTIMO_SERVICO#', 'c'))    AS data_ultimo_servico,
       SUM(REGEXP_LIKE(conteudo, '#PONTUACAO_ATUAL#', 'c'))        AS pontuacao_atual,
       SUM(REGEXP_LIKE(conteudo, '#(CLIENTE_NOME|NOME_BARBEIRO_ULTIMO_SERVICO|DATA_ULTIMO_SERVICO|PONTUACAO_ATUAL)#', 'c')) AS alguma_delas
  FROM notificacoes
 WHERE id < 90000000 AND conteudo IS NOT NULL
 GROUP BY tipo, destinatario
 ORDER BY tipo, destinatario;

SELECT '=== 5. a etiqueta que a campanha NAO troca (chega ao cliente escrita assim) ===' AS '';

-- `resto`: o texto sem as quatro conhecidas. Uma `#PALAVRA#` que sobra e uma
-- etiqueta que a campanha nao troca. Separadas: as do contexto de outras
-- mensagens (#LINK# e as outras), as conhecidas escritas noutra caixa
-- (#cliente_nome#) e o resto. So contagens.
SELECT canal, para, ano,
       COUNT(*)                                                          AS campanhas,
       SUM(REGEXP_LIKE(resto, '#[A-Za-z\\x{00C0}-\\x{00FF}0-9_]+#', 'c')) AS com_etiqueta_que_nao_troca,
       SUM(REGEXP_LIKE(resto, '#(LINK|CODIGO|C\\x{00D3}DIGO|COLABORADOR_NOME|PONTOS|VALOR_REAIS|VENDA_ID|PORCENTAGEM|PRODUTO_NOME|DATA|HORARIO|HOR\\x{00C1}RIO)#', 'c')) AS do_contexto_de_outras,
       SUM(REGEXP_LIKE(resto, '#(CLIENTE_NOME|NOME_BARBEIRO_ULTIMO_SERVICO|DATA_ULTIMO_SERVICO|PONTUACAO_ATUAL)#', 'i') AND NOT REGEXP_LIKE(resto, '#(CLIENTE_NOME|NOME_BARBEIRO_ULTIMO_SERVICO|DATA_ULTIMO_SERVICO|PONTUACAO_ATUAL)#', 'c')) AS conhecida_noutra_caixa
  FROM (SELECT tipo AS canal, destinatario AS para, YEAR(data_criacao) AS ano,
               CASE WHEN destinatario = 'cliente'
                    THEN REGEXP_REPLACE(conteudo, '#(CLIENTE_NOME|NOME_BARBEIRO_ULTIMO_SERVICO|DATA_ULTIMO_SERVICO|PONTUACAO_ATUAL)#', '', 1, 0, 'c')
                    ELSE REGEXP_REPLACE(conteudo, '#CLIENTE_NOME#', '', 1, 0, 'c') END AS resto
          FROM notificacoes
         WHERE id < 90000000 AND conteudo IS NOT NULL AND YEAR(data_criacao) >= 2025) t
 GROUP BY canal, para, ano
 ORDER BY canal, para, ano;

SELECT '=== 6. emoji, quebra de linha e endereco escrito no texto ===' AS '';

SELECT tipo                                                   AS canal,
       YEAR(data_criacao)                                     AS ano,
       COUNT(*)                                               AS campanhas,
       SUM(conteudo REGEXP '[\\x{10000}-\\x{10FFFF}]')        AS com_emoji,
       SUM(conteudo REGEXP '[\\x{2600}-\\x{27BF}]')           AS com_simbolo,
       SUM(conteudo LIKE '%\n%')                              AS com_quebra_de_linha,
       SUM(conteudo LIKE '%http%' OR conteudo LIKE '%www.%')  AS com_endereco_no_texto
  FROM notificacoes
 WHERE id < 90000000 AND conteudo IS NOT NULL AND YEAR(data_criacao) >= 2025
 GROUP BY tipo, YEAR(data_criacao)
 ORDER BY tipo, ano;

SELECT '=== 7. o titulo (so a equipe ve; o WhatsApp nao o manda) ===' AS '';

SELECT tipo                             AS canal,
       MAX(CHAR_LENGTH(titulo))         AS maior,
       ROUND(AVG(CHAR_LENGTH(titulo)))  AS medio,
       SUM(titulo REGEXP '[\\x{10000}-\\x{10FFFF}]') AS com_emoji
  FROM notificacoes
 WHERE id < 90000000 AND YEAR(data_criacao) >= 2025
 GROUP BY tipo
 ORDER BY tipo;

SELECT '=== 8. o que esta na fila agora (a resposta 254 (c): o push agendado antes do corte) ===' AS '';

-- Nao e texto, e serve ao corte: a campanha de push que ja estava agendada
-- continua saindo pela rotina, e a tela nova a mostra so para cancelar; a
-- atrasada ha mais de 3 horas deixa de sair (a resposta 262 (b)). O
-- `passaram_3h_da_hora` e um TETO: a rotina conta o prazo a partir das 9h na
-- campanha marcada fora do horario das campanhas (o desenho, secao 2), e esta
-- conta, da hora marcada.
SELECT tipo                                       AS canal,
       status                                     AS situacao,
       COUNT(*)                                   AS campanhas,
       SUM(data > NOW())                          AS com_a_hora_por_vir,
       SUM(data <= NOW() - INTERVAL 3 HOUR)       AS passaram_3h_da_hora,
       MAX(total_clientes)                        AS maior_lista
  FROM notificacoes
 WHERE id < 90000000 AND status IN ('agendado', 'aguardando', 'enviando')
 GROUP BY tipo, status
 ORDER BY tipo, status;

SELECT '=== FIM: o roteiro rodou inteiro ===' AS '';

ROLLBACK;