-- CDP Edge — Schema Sales Engine (Fase 7)
-- ROAS Feedback Loop + Nurture Engine + Lookalike Dinâmico
-- Executar: wrangler d1 execute cdp-edge-db --file=schema-sales-engine.sql --remote

-- ── ROAS Reports — histórico de performance por campanha ──────────────────────
CREATE TABLE IF NOT EXISTS roas_reports (
  id                 INTEGER PRIMARY KEY AUTOINCREMENT,
  utm_source         TEXT    NOT NULL,
  utm_campaign       TEXT    NOT NULL,
  utm_content        TEXT    NOT NULL DEFAULT 'unknown', -- origem: quiz_*, video_*, landing_*, ctwa_*
  period_days        INTEGER NOT NULL DEFAULT 30,
  total_leads        INTEGER NOT NULL DEFAULT 0,
  confirmed_buyers   INTEGER NOT NULL DEFAULT 0,
  conversion_rate    REAL    NOT NULL DEFAULT 0,     -- 0.0–1.0
  total_revenue      REAL    NOT NULL DEFAULT 0,     -- BRL
  revenue_per_lead   REAL    NOT NULL DEFAULT 0,
  ltv_accuracy       REAL    NOT NULL DEFAULT 0,     -- % leads High LTV que realmente compraram
  top_qualification  TEXT,                           -- qualificação quiz dominante
  bid_recommendation TEXT    NOT NULL DEFAULT 'maintain', -- increase|maintain|decrease|pause
  created_at         TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- Migração para instâncias existentes (idempotente via IF NOT EXISTS não funciona em ALTER TABLE —
-- execute manualmente se a tabela já existir):
-- ALTER TABLE roas_reports ADD COLUMN utm_content TEXT NOT NULL DEFAULT 'unknown';

CREATE INDEX IF NOT EXISTS idx_roas_source_campaign ON roas_reports(utm_source, utm_campaign);
CREATE INDEX IF NOT EXISTS idx_roas_content         ON roas_reports(utm_content);
CREATE INDEX IF NOT EXISTS idx_roas_created_at      ON roas_reports(created_at);
CREATE INDEX IF NOT EXISTS idx_roas_bid_rec         ON roas_reports(bid_recommendation);

-- VIEW: últimos relatórios por campanha com evolução
CREATE VIEW IF NOT EXISTS v_roas_latest AS
SELECT
  utm_source,
  utm_campaign,
  conversion_rate,
  total_revenue,
  revenue_per_lead,
  ltv_accuracy,
  top_qualification,
  bid_recommendation,
  created_at
FROM roas_reports r1
WHERE created_at = (
  SELECT MAX(r2.created_at)
  FROM roas_reports r2
  WHERE r2.utm_source = r1.utm_source
    AND r2.utm_campaign = r1.utm_campaign
)
ORDER BY total_revenue DESC;

-- ── Nurture Sequences — filas de mensagens por qualificação ──────────────────
CREATE TABLE IF NOT EXISTS nurture_sequences (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id        TEXT,                     -- _cdp_uid
  qualification  TEXT    NOT NULL,         -- interessado | curioso
  delay_days     INTEGER NOT NULL,         -- D+N após o quiz
  channel        TEXT    NOT NULL,         -- whatsapp | email
  message        TEXT    NOT NULL,         -- mensagem interpolada
  subject        TEXT,                     -- assunto (email only)
  send_at        TEXT    NOT NULL,         -- datetime de envio agendado
  status         TEXT    NOT NULL DEFAULT 'pending',  -- pending | sent | failed | cancelled
  sent_at        TEXT,                     -- quando foi realmente enviado
  quiz_name      TEXT,                     -- nome do quiz (para interpolação)
  phone          TEXT,                     -- telefone do lead
  email          TEXT,                     -- email do lead
  first_name     TEXT,                     -- nome (para personalização)
  created_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  updated_at     TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_nurture_user_id   ON nurture_sequences(user_id);
CREATE INDEX IF NOT EXISTS idx_nurture_status    ON nurture_sequences(status);
CREATE INDEX IF NOT EXISTS idx_nurture_send_at   ON nurture_sequences(send_at);
CREATE INDEX IF NOT EXISTS idx_nurture_qual      ON nurture_sequences(qualification);

-- VIEW: fila de envio pendente
CREATE VIEW IF NOT EXISTS v_nurture_pending AS
SELECT
  id, user_id, qualification, delay_days, channel,
  message, send_at, phone, email, first_name, quiz_name
FROM nurture_sequences
WHERE status = 'pending'
  AND send_at <= datetime('now')
ORDER BY send_at ASC;

-- VIEW: taxa de envio por qualificação
CREATE VIEW IF NOT EXISTS v_nurture_stats AS
SELECT
  qualification,
  channel,
  COUNT(*)                                       AS total,
  COUNT(CASE WHEN status = 'sent'    THEN 1 END) AS sent,
  COUNT(CASE WHEN status = 'failed'  THEN 1 END) AS failed,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
  ROUND(COUNT(CASE WHEN status = 'sent' THEN 1 END) * 100.0 / COUNT(*), 1) AS delivery_rate_pct
FROM nurture_sequences
GROUP BY qualification, channel;

-- ── Lookalike Seeds — histórico de audiences enviadas ao Meta ─────────────────
CREATE TABLE IF NOT EXISTS lookalike_seeds (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  audience_id     TEXT    NOT NULL,    -- META_AUDIENCE_ID
  seed_type       TEXT    NOT NULL,    -- 'buyer_confirmed' | 'high_intent' | 'quiz_comprador'
  profiles_sent   INTEGER NOT NULL DEFAULT 0,
  profiles_received INTEGER,           -- confirmado pela API Meta
  period_days     INTEGER NOT NULL DEFAULT 30,
  created_at      TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_lookalike_seed_type  ON lookalike_seeds(seed_type);
CREATE INDEX IF NOT EXISTS idx_lookalike_created_at ON lookalike_seeds(created_at);
