-- CDP Edge — Migration v7
-- Tabelas para: LTV Model (regressão logística) + Match Quality Log
-- Idempotente: todos os CREATE usam IF NOT EXISTS

-- ── LTV Model Weights ─────────────────────────────────────────────────────────
-- Armazena os pesos treinados pelo cron semanal de regressão logística.
-- O Worker carrega via KV (TTL 7 dias) — query D1 apenas se KV miss.
CREATE TABLE IF NOT EXISTS ltv_model_weights (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  trained_at   TEXT    NOT NULL DEFAULT (datetime('now')),
  version      INTEGER NOT NULL DEFAULT 1,
  is_active    INTEGER NOT NULL DEFAULT 0,
  sample_size  INTEGER NOT NULL DEFAULT 0,
  positive_rate REAL,           -- % de samples com label=1 (compra)
  accuracy     REAL,            -- acurácia no conjunto de treino
  weights_json TEXT    NOT NULL  -- JSON: {"bias": n, "features": {"utm_fb": w, ...}}
);

CREATE INDEX IF NOT EXISTS idx_ltv_weights_active  ON ltv_model_weights(is_active);
CREATE INDEX IF NOT EXISTS idx_ltv_weights_trained ON ltv_model_weights(trained_at DESC);

-- ── Match Quality Log ─────────────────────────────────────────────────────────
-- Registra a qualidade dos dados enviados ao Meta CAPI por evento.
-- Sem PII — apenas flags booleanos.
CREATE TABLE IF NOT EXISTS match_quality_log (
  id                   INTEGER PRIMARY KEY AUTOINCREMENT,
  logged_at            TEXT    NOT NULL DEFAULT (datetime('now')),
  event_name           TEXT    NOT NULL,
  has_email            INTEGER NOT NULL DEFAULT 0,
  has_phone            INTEGER NOT NULL DEFAULT 0,
  has_fbp              INTEGER NOT NULL DEFAULT 0,
  has_fbc              INTEGER NOT NULL DEFAULT 0,
  has_external_id      INTEGER NOT NULL DEFAULT 0,
  was_email_recovered  INTEGER NOT NULL DEFAULT 0,  -- recuperado do Identity Graph
  was_utm_restored     INTEGER NOT NULL DEFAULT 0   -- via UTM Resurrection
);

CREATE INDEX IF NOT EXISTS idx_mq_logged ON match_quality_log(logged_at DESC);
CREATE INDEX IF NOT EXISTS idx_mq_event  ON match_quality_log(event_name, logged_at DESC);

-- ── View: Match Quality Dashboard (últimas 24h) ───────────────────────────────
CREATE VIEW IF NOT EXISTS v_match_quality_24h AS
SELECT
  COUNT(*)                                                          AS total_events,
  ROUND(AVG(has_email)       * 100, 1)                             AS email_rate_pct,
  ROUND(AVG(has_phone)       * 100, 1)                             AS phone_rate_pct,
  ROUND(AVG(has_fbp)         * 100, 1)                             AS fbp_rate_pct,
  ROUND(AVG(has_fbc)         * 100, 1)                             AS fbc_rate_pct,
  ROUND(AVG(has_external_id) * 100, 1)                             AS ext_id_rate_pct,
  ROUND(AVG(was_email_recovered) * 100, 1)                         AS email_recovered_pct,
  ROUND(AVG(was_utm_restored) * 100, 1)                            AS utm_restored_pct,
  -- Score composto: email(40%) + fbp(30%) + phone(20%) + fbc(10%)
  ROUND((AVG(has_email)*0.4 + AVG(has_fbp)*0.3 + AVG(has_phone)*0.2 + AVG(has_fbc)*0.1) * 100, 1) AS composite_score_pct
FROM match_quality_log
WHERE logged_at >= datetime('now', '-24 hours');

-- ── ALTER: colunas de auditoria em ltv_ab_tests ───────────────────────────────
-- Adiciona auto_decided_at se ainda não existir (SQLite não tem IF NOT EXISTS em ALTER)
-- Protegido por trigger-style: INSERT OR IGNORE não se aplica a ALTER, então usamos
-- um approach de criação de tabela auxiliar que faz nada se a coluna já existir.
-- A abordagem mais segura no Cloudflare D1 é tentar e ignorar o erro no app.
-- Aqui deixamos comentado — o worker.js já lida com campos opcionais:
-- ALTER TABLE ltv_ab_tests ADD COLUMN auto_decided_at TEXT;
-- ALTER TABLE ltv_ab_tests ADD COLUMN auto_decided_reason TEXT;
