PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS organizations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  slug TEXT UNIQUE,
  settings TEXT,
  created_at TEXT, updated_at TEXT
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id INTEGER NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name TEXT,
  role TEXT NOT NULL DEFAULT 'candidate',
  locale TEXT,
  twofa_enabled INTEGER DEFAULT 0,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (org_id) REFERENCES organizations(id)
);

CREATE TABLE IF NOT EXISTS candidates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER UNIQUE,
  reg_no TEXT,
  extra_fields TEXT,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS question_banks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id INTEGER NULL,
  title TEXT NOT NULL,
  tags TEXT,
  created_at TEXT, updated_at TEXT
);

CREATE TABLE IF NOT EXISTS questions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  bank_id INTEGER NOT NULL,
  type TEXT NOT NULL,
  text TEXT NOT NULL,
  media TEXT NULL,
  options_json TEXT,
  answer_key TEXT,
  difficulty INTEGER DEFAULT 3,
  tags TEXT,
  version INTEGER DEFAULT 1,
  explanation TEXT,
  numeric_tolerance REAL DEFAULT 0,
  marks REAL DEFAULT 1,
  negative_marks REAL DEFAULT 0,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (bank_id) REFERENCES question_banks(id)
);

CREATE TABLE IF NOT EXISTS tests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id INTEGER NULL,
  title TEXT NOT NULL,
  slug TEXT,
  description TEXT,
  settings_json TEXT,
  schedule_start TEXT,
  schedule_end TEXT,
  duration_minutes INTEGER DEFAULT 30,
  created_at TEXT, updated_at TEXT
);

CREATE TABLE IF NOT EXISTS test_sections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  test_id INTEGER NOT NULL,
  title TEXT,
  time_limit INTEGER NULL,
  pick_count INTEGER NULL,
  negative_mark REAL DEFAULT 0,
  weight REAL DEFAULT 1,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (test_id) REFERENCES tests(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS test_section_questions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  section_id INTEGER NOT NULL,
  question_id INTEGER NOT NULL,
  "order" INTEGER,
  weight REAL DEFAULT 1,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (section_id) REFERENCES test_sections(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id)
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  test_id INTEGER NOT NULL,
  candidate_id INTEGER NOT NULL,
  status TEXT DEFAULT 'enrolled',
  created_at TEXT, updated_at TEXT
);

CREATE TABLE IF NOT EXISTS attempts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  enrollment_id TEXT NOT NULL, -- using string in MVP
  test_id INTEGER NULL,
  started_at TEXT,
  submitted_at TEXT,
  score REAL DEFAULT 0,
  status TEXT DEFAULT 'in_progress',
  proctor_flags_json TEXT,
  created_at TEXT, updated_at TEXT
);

CREATE TABLE IF NOT EXISTS responses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  attempt_id INTEGER NOT NULL,
  question_id INTEGER NOT NULL,
  response_json TEXT,
  score REAL NULL,
  time_spent_sec INTEGER NULL,
  flagged INTEGER DEFAULT 0,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (attempt_id) REFERENCES attempts(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id)
);

CREATE TABLE IF NOT EXISTS certificate_templates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id INTEGER NULL,
  name TEXT,
  schema_json TEXT,
  background_url TEXT,
  created_at TEXT, updated_at TEXT
);

CREATE TABLE IF NOT EXISTS certificates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  attempt_id INTEGER NOT NULL,
  template_id INTEGER NULL,
  issued_at TEXT DEFAULT (datetime('now')),
  serial TEXT UNIQUE,
  verification_code TEXT,
  pdf_url TEXT,
  created_at TEXT, updated_at TEXT,
  FOREIGN KEY (attempt_id) REFERENCES attempts(id)
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id INTEGER NULL,
  actor_id INTEGER,
  action TEXT,
  entity TEXT,
  entity_id TEXT,
  ip TEXT,
  user_agent TEXT,
  created_at TEXT, updated_at TEXT
);
