-- CDP Edge — Schema Quiz Sessions (Fase 6 v2)
-- Análise Dimensional Automática por Workers AI
-- Executar: wrangler d1 execute cdp-edge-db --file=schema-quiz.sql --remote

CREATE TABLE IF NOT EXISTS quiz_sessions (
  id                 INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id            TEXT,                   -- _cdp_uid (FK lógica → user_profiles)
  quiz_name          TEXT,                   -- nome do quiz (ex: "Diagnóstico Imóvel")
  answers_json       TEXT    NOT NULL,       -- JSON: [{question, answer, step}]
  qualification      TEXT    NOT NULL,       -- comprador | interessado | curioso | perdido
  intent_score       REAL    NOT NULL,       -- 0.0–1.0 (propagado ao pipeline CDP)
  weighted_score     REAL    NOT NULL,       -- score ponderado bruto Σ(score×weight)/Σ(weight)
  confidence         REAL    NOT NULL DEFAULT 0.7,
  reason             TEXT,                   -- frase explicativa em português (audit)
  dominant_dimension TEXT,                   -- dimensão com maior impacto: budget|urgency|etc.
  dimensions_json    TEXT,                   -- JSON: [{step, dimension, score, weight, signal}]
  source             TEXT    DEFAULT 'ai',   -- ai | heuristic
  created_at         TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_quiz_user_id       ON quiz_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_quiz_qualification ON quiz_sessions(qualification);
CREATE INDEX IF NOT EXISTS idx_quiz_created_at    ON quiz_sessions(created_at);
CREATE INDEX IF NOT EXISTS idx_quiz_dominant_dim  ON quiz_sessions(dominant_dimension);

-- VIEW: distribuição de qualificações por quiz + score médio
CREATE VIEW IF NOT EXISTS v_quiz_qualification_summary AS
SELECT
  quiz_name,
  qualification,
  COUNT(*)                                          AS total,
  ROUND(AVG(intent_score)   * 100, 1)              AS avg_intent_pct,
  ROUND(AVG(weighted_score) * 100, 1)              AS avg_weighted_pct,
  ROUND(AVG(confidence)     * 100, 1)              AS avg_confidence_pct,
  COUNT(CASE WHEN source = 'ai' THEN 1 END)        AS ai_scored,
  COUNT(CASE WHEN source = 'heuristic' THEN 1 END) AS heuristic_scored
FROM quiz_sessions
GROUP BY quiz_name, qualification
ORDER BY quiz_name, avg_intent_pct DESC;

-- VIEW: qual dimensão mais penaliza/beneficia cada quiz
CREATE VIEW IF NOT EXISTS v_quiz_dimension_impact AS
SELECT
  quiz_name,
  dominant_dimension,
  COUNT(*)                             AS total_sessions,
  ROUND(AVG(weighted_score) * 100, 1) AS avg_weighted_pct,
  qualification
FROM quiz_sessions
WHERE dominant_dimension IS NOT NULL
GROUP BY quiz_name, dominant_dimension, qualification
ORDER BY quiz_name, total_sessions DESC;
