-- migrate-v4.sql — Tabela de histórico de mensagens (WhatsApp + Email)
-- Executar: wrangler d1 execute cdp-edge-db --file=migrate-v4.sql

CREATE TABLE IF NOT EXISTS messaging_history (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  sent_at    TEXT    NOT NULL DEFAULT (datetime('now')),
  lead_id    INTEGER NOT NULL,
  channel    TEXT    NOT NULL,  -- 'whatsapp' | 'email'
  recipient  TEXT    NOT NULL,  -- telefone E.164 ou endereço de email
  subject    TEXT,              -- assunto (só email)
  content    TEXT    NOT NULL,  -- corpo da mensagem
  status     TEXT    NOT NULL,  -- 'sent' | 'failed'
  meta       TEXT               -- message_id da API ou erro em JSON
);

CREATE INDEX IF NOT EXISTS idx_msg_history_lead    ON messaging_history(lead_id);
CREATE INDEX IF NOT EXISTS idx_msg_history_sent_at ON messaging_history(sent_at);
CREATE INDEX IF NOT EXISTS idx_msg_history_channel ON messaging_history(channel);
