-- Schema A/B Testing de Prompts LTV — CDP Edge Quantum Tier
-- Versão: 1.0
-- Data: 9 de Abril de 2026
-- Fase 3 Enterprise-Level

-- TABELA: Experimentos A/B
CREATE TABLE IF NOT EXISTS ltv_ab_tests (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT NOT NULL,
  description   TEXT,
  status        TEXT NOT NULL DEFAULT 'draft',  -- 'draft', 'running', 'paused', 'completed'
  winner_id     INTEGER,                         -- FK para ltv_ab_variations.id
  started_at    TEXT,
  completed_at  TEXT,
  created_at    TEXT NOT NULL DEFAULT (datetime('now')),
  min_sample    INTEGER NOT NULL DEFAULT 100,    -- Mínimo de assignments para declarar vencedor
  confidence_threshold REAL NOT NULL DEFAULT 0.90  -- Nível de confiança estatística (90%)
);

-- TABELA: Variações de Prompt por Experimento
CREATE TABLE IF NOT EXISTS ltv_ab_variations (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  test_id       INTEGER NOT NULL REFERENCES ltv_ab_tests(id),
  name          TEXT NOT NULL,                   -- Ex: 'Variação A — Foco em Engajamento'
  system_prompt TEXT NOT NULL,                   -- O prompt system enviado ao Workers AI
  weight        REAL NOT NULL DEFAULT 0.5,       -- Peso de distribuição (0-1, soma deve = 1)
  is_control    INTEGER NOT NULL DEFAULT 0,      -- 1 = variação controle (baseline)

  -- Métricas acumuladas
  total_assigned    INTEGER NOT NULL DEFAULT 0,
  total_purchases   INTEGER NOT NULL DEFAULT 0,
  sum_predicted_ltv REAL NOT NULL DEFAULT 0,
  sum_real_revenue  REAL NOT NULL DEFAULT 0,

  -- Métricas calculadas (atualizadas por trigger ou batch)
  avg_predicted_ltv REAL,
  avg_real_revenue  REAL,
  conversion_rate   REAL,
  accuracy_score    REAL,  -- Quão próximo do valor real (1 - |predicted-real|/real)

  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- TABELA: Assignments — qual variação foi usada para qual lead
CREATE TABLE IF NOT EXISTS ltv_ab_assignments (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  test_id       INTEGER NOT NULL REFERENCES ltv_ab_tests(id),
  variation_id  INTEGER NOT NULL REFERENCES ltv_ab_variations(id),
  user_id       TEXT NOT NULL,
  email_hash    TEXT,                    -- SHA256 do email (indexação sem PII)
  predicted_ltv REAL,
  predicted_class TEXT,
  assigned_at   TEXT NOT NULL DEFAULT (datetime('now')),

  -- Preenchido quando purchase chega via webhook
  converted     INTEGER NOT NULL DEFAULT 0,
  real_revenue  REAL,
  converted_at  TEXT
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_ab_tests_status       ON ltv_ab_tests(status);
CREATE INDEX IF NOT EXISTS idx_ab_variations_test    ON ltv_ab_variations(test_id);
CREATE INDEX IF NOT EXISTS idx_ab_assignments_test   ON ltv_ab_assignments(test_id);
CREATE INDEX IF NOT EXISTS idx_ab_assignments_user   ON ltv_ab_assignments(user_id);
CREATE INDEX IF NOT EXISTS idx_ab_assignments_email  ON ltv_ab_assignments(email_hash);
CREATE INDEX IF NOT EXISTS idx_ab_assignments_conv   ON ltv_ab_assignments(converted);

-- VIEW: Resumo de performance por variação
CREATE VIEW IF NOT EXISTS v_ab_test_performance AS
SELECT
  t.id            AS test_id,
  t.name          AS test_name,
  t.status,
  v.id            AS variation_id,
  v.name          AS variation_name,
  v.is_control,
  v.weight,
  v.total_assigned,
  v.total_purchases,
  CASE WHEN v.total_assigned > 0
    THEN ROUND(CAST(v.total_purchases AS REAL) / v.total_assigned * 100, 2)
    ELSE 0
  END AS conversion_rate_pct,
  CASE WHEN v.total_assigned > 0
    THEN ROUND(v.sum_predicted_ltv / v.total_assigned, 2)
    ELSE 0
  END AS avg_predicted_ltv,
  CASE WHEN v.total_purchases > 0
    THEN ROUND(v.sum_real_revenue / v.total_purchases, 2)
    ELSE 0
  END AS avg_real_revenue,
  v.accuracy_score,
  t.winner_id = v.id AS is_winner
FROM ltv_ab_tests t
JOIN ltv_ab_variations v ON v.test_id = t.id
ORDER BY t.id, v.id;
