-- ---------------------------------------------------------------------------
-- As 3.434 "perdas" sao pessoas diferentes, ou a MESMA pessoa cadastrada duas
-- vezes?
--
-- A medicao anterior derrubou a hipotese do numero-lixo: 82% da perda vem de
-- telefones com menos de 10 clientes. Mas resta uma leitura melhor.
--
-- Duas pessoas so se anulam se tiverem o MESMO dia e mes de nascimento. Numa
-- familia isso e raro. Ja a MESMA PESSOA cadastrada duas vezes -- uma por
-- unidade, que e o D31 -- tem o mesmo telefone E a mesma data, sempre.
--
-- Se a maioria das colisoes for a mesma pessoa, entao a deduplicacao esta
-- funcionando exatamente como deveria, e nao ha perda nenhuma: ela existe para
-- impedir que a pessoa receba duas mensagens.
--
-- RODE NA REPLICA DO BVIP. SO LEITURA.
--
--   mysql -h 127.0.0.1 -P 3306 -u franquiabv -p franquia_producao \
--     < scripts/medir-perda-aniversario-2.sql
-- ---------------------------------------------------------------------------

SELECT '=== 1. quem colide tem o mesmo nome? ===' AS '';

SELECT SUM(CASE WHEN nomes = 1 THEN perdidos ELSE 0 END) AS perda_MESMO_NOME_mesma_pessoa,
       SUM(CASE WHEN nomes > 1 THEN perdidos ELSE 0 END) AS perda_NOMES_DIFERENTES,
       SUM(perdidos)                                     AS perda_total,
       SUM(CASE WHEN nomes = 1 THEN 1 ELSE 0 END)        AS grupos_mesma_pessoa,
       SUM(CASE WHEN nomes > 1 THEN 1 ELSE 0 END)        AS grupos_pessoas_distintas
  FROM (
    SELECT COUNT(*) - 1                              AS perdidos,
           COUNT(DISTINCT LOWER(TRIM(nome)))         AS nomes
      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, DAY(data_nascimento), MONTH(data_nascimento)
    HAVING COUNT(*) > 1
  ) x;

SELECT '=== 2. e o ANO tambem bate? (sinal mais forte) ===' AS '';

SELECT SUM(CASE WHEN datas = 1 AND nomes = 1 THEN perdidos ELSE 0 END) AS mesma_data_e_mesmo_nome,
       SUM(CASE WHEN datas = 1 AND nomes > 1 THEN perdidos ELSE 0 END) AS mesma_data_nome_diferente,
       SUM(CASE WHEN datas > 1                THEN perdidos ELSE 0 END) AS anos_diferentes,
       SUM(perdidos)                                                    AS perda_total
  FROM (
    SELECT COUNT(*) - 1                        AS perdidos,
           COUNT(DISTINCT data_nascimento)     AS datas,
           COUNT(DISTINCT LOWER(TRIM(nome)))   AS nomes
      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, DAY(data_nascimento), MONTH(data_nascimento)
    HAVING COUNT(*) > 1
  ) y;

SELECT '=== 3. dez exemplos, para olhar com os proprios olhos ===' AS '';

SELECT c.telefone_sem_mascara AS telefone,
       c.nome,
       c.data_nascimento,
       c.unidade,
       c.id
  FROM clientes c
  JOIN (
    SELECT telefone_sem_mascara AS t, DAY(data_nascimento) d, MONTH(data_nascimento) m
      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, d, m
    HAVING COUNT(*) > 1 AND COUNT(DISTINCT unidade) > 1
     LIMIT 5
  ) g
    ON g.t = c.telefone_sem_mascara
   AND g.d = DAY(c.data_nascimento)
   AND g.m = MONTH(c.data_nascimento)
 WHERE c.status = 1
 ORDER BY c.telefone_sem_mascara, c.id;

SELECT '=== 4. quantas datas de nascimento sao repetidas demais? ===' AS '';

-- Se um punhado de datas concentrar muita gente, e preenchimento automatico,
-- e nao aniversario de verdade.
SELECT DATE_FORMAT(data_nascimento, '%d/%m') AS dia_mes,
       COUNT(*)                              AS clientes
  FROM clientes
 WHERE status = 1 AND pais = 'Brasil' AND data_nascimento IS NOT NULL
 GROUP BY dia_mes
 ORDER BY clientes DESC
 LIMIT 8;