-- ---------------------------------------------------------------------------
-- Quantos clientes compartilham telefone, e o que a deduplicacao esconde.
--
-- RODE NA REPLICA DO BVIP, nao na homologacao: a anonimizacao deriva o telefone
-- de MOD(id, 10000), entao na base anonimizada os numeros colidem por
-- construcao e qualquer medicao seria ficcao.
--
--   mysql -h 127.0.0.1 -P 3306 -u franquiabv -p franquia_producao < este_arquivo
--
-- SO LEITURA. Nenhum comando altera dado.
-- ---------------------------------------------------------------------------

SELECT '=== 1. telefones compartilhados entre clientes ativos ===' AS '';

SELECT COUNT(*)                                   AS telefones_com_mais_de_um_cliente,
       SUM(clientes)                              AS clientes_envolvidos,
       SUM(unidades > 1)                          AS telefones_em_MAIS_DE_UMA_UNIDADE,
       MAX(clientes)                              AS pior_caso
  FROM (
    SELECT telefone_sem_mascara,
           COUNT(*)                  AS clientes,
           COUNT(DISTINCT unidade)   AS unidades
      FROM clientes
     WHERE status = 1
       AND telefone_sem_mascara IS NOT NULL
       AND telefone_sem_mascara <> ''
     GROUP BY telefone_sem_mascara
    HAVING COUNT(*) > 1
  ) x;

SELECT '=== 2. os numeros mais repetidos (D32: telefone de balcao?) ===' AS '';

SELECT telefone_sem_mascara AS telefone,
       COUNT(*)                AS clientes,
       COUNT(DISTINCT unidade) AS unidades
  FROM clientes
 WHERE status = 1
   AND telefone_sem_mascara IS NOT NULL
   AND telefone_sem_mascara <> ''
 GROUP BY telefone_sem_mascara
 ORDER BY clientes DESC
 LIMIT 10;

SELECT '=== 3. quantos aniversariantes um dia tipico perde ===' AS '';

-- Compara quantos clientes fazem aniversario hoje com quantas MENSAGENS a
-- consulta atual dispararia. A diferenca e gente que nao recebe.
SELECT COUNT(*)                                  AS aniversariantes_hoje,
       COUNT(DISTINCT telefone_sem_mascara)      AS mensagens_enviadas,
       COUNT(*) - COUNT(DISTINCT telefone_sem_mascara) AS NAO_RECEBEM
  FROM clientes
 WHERE status = 1
   AND pais = 'Brasil'
   AND data_nascimento IS NOT NULL
   AND DAY(data_nascimento)   = DAY(CURDATE())
   AND MONTH(data_nascimento) = MONTH(CURDATE());

SELECT '=== 4. no ano inteiro ===' AS '';

SELECT SUM(perdidos) AS pessoas_que_nao_recebem_no_ano
  FROM (
    SELECT DAY(data_nascimento) d, MONTH(data_nascimento) m,
           COUNT(*) - COUNT(DISTINCT telefone_sem_mascara) AS perdidos
      FROM clientes
     WHERE status = 1 AND pais = 'Brasil' AND data_nascimento IS NOT NULL
     GROUP BY d, m
  ) y;