-- ============================================================
-- Migração: colunas de leituras (email_leituras / celular_leituras)
-- Data   : 2026-06-15
-- Requer : MySQL 8.0+ (ADD COLUMN IF NOT EXISTS)
-- Seguro : idempotente — pode ser executado mais de uma vez
-- ============================================================

-- 1. Estrutura -----------------------------------------------

ALTER TABLE contatos
    ADD COLUMN IF NOT EXISTS email_leituras   TINYINT NOT NULL DEFAULT 0
        AFTER email_engajamento,
    ADD COLUMN IF NOT EXISTS celular_leituras TINYINT NOT NULL DEFAULT 0
        AFTER celular_engajamento;


-- 2. Recalculo das leituras a partir do historico_zenvia ------
--    READ / OPENED / CLICKED / REPLIED  →  qualidade (lido/clicado/respondido)
--    Teto 3 para manter a escala 0-3 = 0-100 %.

UPDATE contatos c SET
    c.email_leituras = LEAST(3, COALESCE((
        SELECT COUNT(*) FROM historico_zenvia h
        WHERE h.contact_id = c.id
          AND h.channel = 'EMAIL'
          AND h.status IN ('READ','OPENED','CLICKED','REPLIED')
    ), 0)),
    c.celular_leituras = LEAST(3, COALESCE((
        SELECT COUNT(*) FROM historico_zenvia h
        WHERE h.contact_id = c.id
          AND h.channel <> 'EMAIL' AND h.channel IS NOT NULL
          AND h.status IN ('READ','REPLIED','CLICKED')
    ), 0));


-- 3. Recalculo do engajamento com a nova formula --------------
--    engajamento = GREATEST(leituras, entregas_cap3)
--    Garante que quem leu sempre fica >= quem so recebeu.

UPDATE contatos c SET
    c.email_engajamento = GREATEST(
        c.email_leituras,
        LEAST(3, COALESCE((
            SELECT COUNT(*) FROM historico_zenvia h
            WHERE h.contact_id = c.id
              AND h.channel = 'EMAIL'
              AND h.status = 'DELIVERED'
        ), 0))
    ),
    c.celular_engajamento = GREATEST(
        c.celular_leituras,
        LEAST(3, COALESCE((
            SELECT COUNT(*) FROM historico_zenvia h
            WHERE h.contact_id = c.id
              AND h.channel <> 'EMAIL' AND h.channel IS NOT NULL
              AND h.status = 'DELIVERED'
        ), 0))
    );


-- 4. Verificacao pos-migracao ---------------------------------
--    Confira os totais antes de liberar o deploy.

SELECT
    COUNT(*)                                AS total_contatos,
    SUM(email_leituras   > 0)              AS com_leituras_email,
    SUM(celular_leituras > 0)              AS com_leituras_cel,
    SUM(email_engajamento > 0)             AS com_engaj_email,
    SUM(celular_engajamento > 0)           AS com_engaj_cel,
    -- Sanidade: engajamento nunca pode ser menor que leituras
    SUM(email_engajamento < email_leituras)     AS ERRO_email_engaj_menor_leituras,
    SUM(celular_engajamento < celular_leituras) AS ERRO_cel_engaj_menor_leituras
FROM contatos;
-- Colunas ERRO_* devem retornar 0. Qualquer valor > 0 indica problema.