-- ---------------------------------------------------------------------------
-- De onde vêm as 3.473 pessoas que não recebem parabéns no ano.
--
-- A hipótese a testar: a perda quase toda vem de POUCOS números-lixo com muitos
-- clientes, e não de famílias que dividem telefone.
--
-- O raciocínio: duas pessoas só se anulam se tiverem o MESMO dia e mês de
-- nascimento. Num telefone com 2 clientes isso é ~1/366. Num telefone com 354
-- clientes, é praticamente certo o ano inteiro.
--
-- Se a hipótese estiver certa, a decisão muda de natureza: não é escolher qual
-- cliente representa o telefone, é limpar o D32.
--
-- RODE NA RÉPLICA DO BVIP. SÓ LEITURA.
--
--   mysql -h 127.0.0.1 -P 3306 -u franquiabv -p franquia_producao \
--     < scripts/medir-perda-aniversario.sql
-- ---------------------------------------------------------------------------

SELECT '=== 1. perda por telefone, do pior para o melhor ===' AS '';

SELECT telefone, clientes, unidades, perdidos
  FROM (
    SELECT telefone_sem_mascara    AS telefone,
           COUNT(*)                AS clientes,
           COUNT(DISTINCT unidade) AS unidades,
           -- Cada aniversário repetido no mesmo telefone é uma pessoa a menos
           -- recebendo: a consulta manda UMA mensagem por telefone por dia.
           COUNT(*) - COUNT(DISTINCT CONCAT(DAY(data_nascimento), '-', MONTH(data_nascimento))) AS perdidos
      FROM clientes
     WHERE status = 1 AND pais = 'Brasil' AND data_nascimento IS NOT NULL
       AND telefone_sem_mascara IS NOT NULL AND telefone_sem_mascara <> ''
     GROUP BY telefone_sem_mascara
  ) x
 WHERE perdidos > 0
 ORDER BY perdidos DESC
 LIMIT 15;

SELECT '=== 2. a conta dividida: numeros grandes contra o resto ===' AS '';

SELECT SUM(CASE WHEN clientes >= 10 THEN perdidos ELSE 0 END) AS perda_em_telefones_com_10_ou_mais,
       SUM(CASE WHEN clientes <  10 THEN perdidos ELSE 0 END) AS perda_no_resto,
       SUM(perdidos)                                          AS perda_total,
       SUM(clientes >= 10)                                    AS quantos_telefones_grandes
  FROM (
    SELECT COUNT(*) AS clientes,
           COUNT(*) - COUNT(DISTINCT CONCAT(DAY(data_nascimento), '-', MONTH(data_nascimento))) AS perdidos
      FROM clientes
     WHERE status = 1 AND pais = 'Brasil' AND data_nascimento IS NOT NULL
       AND telefone_sem_mascara IS NOT NULL AND telefone_sem_mascara <> ''
     GROUP BY telefone_sem_mascara
  ) y;

SELECT '=== 3. quantos telefones sao invalidos pela forma (D32) ===' AS '';

-- DDI(2) + DDD(2) = 4 dígitos, então o assinante começa na posição 5.
-- É a mesma regra de postgres/funcoes-telefone.sql: assinante todo repetido não
-- é telefone, é preenchimento de campo obrigatório.
SELECT SUM(CHAR_LENGTH(t) < 12)                                      AS curto_demais,
       SUM(CHAR_LENGTH(t) >= 12 AND SUBSTRING(t, 5) REGEXP '^(.)\\1*$') AS assinante_todo_repetido,
       COUNT(*)                                                      AS telefones_distintos,
       SUM(clientes)                                                 AS clientes_afetados
  FROM (
    SELECT REPLACE(telefone_sem_mascara, '+', '') AS t, COUNT(*) AS clientes
      FROM clientes
     WHERE status = 1 AND telefone_sem_mascara IS NOT NULL AND telefone_sem_mascara <> ''
     GROUP BY t
  ) w
 WHERE CHAR_LENGTH(t) < 12
    OR SUBSTRING(t, 5) REGEXP '^(.)\\1*$';