-- CDP Edge — Migração v6: WhatsApp CTWA Tracking
-- Aplica com: wrangler d1 execute cdp-edge-db --file=server-edge-tracker/migrate-v6.sql

-- Tabela de contatos recebidos via Click to WhatsApp (CTWA)
-- Cada linha = uma primeira mensagem de um usuário que veio de anúncio Meta
CREATE TABLE IF NOT EXISTS whatsapp_contacts (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
  phone_hash    TEXT    NOT NULL,           -- SHA256(phone) — enviado ao Meta CAPI como "ph"
  phone_raw     TEXT    NOT NULL,           -- número normalizado (ex: 5511999998888)
  wamid         TEXT    UNIQUE,             -- ID único da mensagem no WhatsApp (deduplicação)
  ctwa_clid     TEXT,                       -- Click ID do anúncio CTWA (não hasheado — vai direto para CAPI)
  ad_id         TEXT,                       -- ID do anúncio que gerou o clique
  source_url    TEXT,                       -- URL do anúncio (Facebook/Instagram)
  headline      TEXT,                       -- Título do anúncio
  capi_sent     INTEGER NOT NULL DEFAULT 0, -- 0 = pendente | 1 = evento Contact enviado à Meta CAPI
  capi_event_id TEXT,                       -- event_id gerado para deduplicação no CAPI
  message_body  TEXT                        -- texto da primeira mensagem (útil para qualificação)
);

CREATE INDEX IF NOT EXISTS idx_wa_contacts_phone_hash ON whatsapp_contacts(phone_hash);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_wamid      ON whatsapp_contacts(wamid);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_ctwa_clid  ON whatsapp_contacts(ctwa_clid);
CREATE INDEX IF NOT EXISTS idx_wa_contacts_created    ON whatsapp_contacts(created_at);
