-- The Small Business MBA. Quizzes.
-- One quiz per lesson (a lesson is a course module). Passing (>= pass_pct)
-- marks the lesson complete via lesson_progress and unlocks the next module.
-- Questions are stored as a JSON array: [{ q, options:[...], correct: <index>, explanation }].

CREATE TABLE IF NOT EXISTS quizzes (
  id             TEXT PRIMARY KEY,
  lesson_id      TEXT NOT NULL UNIQUE REFERENCES lessons(id) ON DELETE CASCADE,
  pass_pct       INTEGER NOT NULL DEFAULT 80,
  question_count INTEGER NOT NULL DEFAULT 0,
  questions      TEXT NOT NULL,            -- JSON array of questions
  created_at     INTEGER NOT NULL,
  updated_at     INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_quizzes_lesson ON quizzes(lesson_id);

-- One row per quiz submission. Latest passing attempt is what gates advancement.
CREATE TABLE IF NOT EXISTS quiz_attempts (
  id         TEXT PRIMARY KEY,
  user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  lesson_id  TEXT NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  score      INTEGER NOT NULL,             -- number correct
  total      INTEGER NOT NULL,             -- number of questions
  passed     INTEGER NOT NULL DEFAULT 0,   -- 0/1
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_quiz_attempts_user ON quiz_attempts(user_id);
CREATE INDEX IF NOT EXISTS idx_quiz_attempts_lesson ON quiz_attempts(lesson_id);
