-- Qudurat AI - M6 Tests & Mock Exams
-- Run AFTER M1..M5
USE qudurat_ai;

CREATE TABLE tests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  test_type ENUM('skill','section','short','daily','weekly','comprehensive','mock') NOT NULL,
  section_id BIGINT UNSIGNED NULL,
  duration_minutes SMALLINT UNSIGNED NULL,
  question_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  instructions TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  published_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_test_type (test_type),
  INDEX idx_test_status (status),
  CONSTRAINT fk_tests_section FOREIGN KEY (section_id) REFERENCES sections(id) ON DELETE SET NULL,
  CONSTRAINT fk_tests_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE test_questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  test_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  weight DECIMAL(6,3) NOT NULL DEFAULT 1.000,
  section_label VARCHAR(100) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_test_question (test_id, question_id),
  INDEX idx_test_question_order (test_id, sort_order),
  CONSTRAINT fk_test_questions_test FOREIGN KEY (test_id) REFERENCES tests(id) ON DELETE CASCADE,
  CONSTRAINT fk_test_questions_question FOREIGN KEY (question_id) REFERENCES questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE test_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  test_id BIGINT UNSIGNED NOT NULL,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active','submitted','expired','abandoned') NOT NULL DEFAULT 'active',
  started_at TIMESTAMP NOT NULL,
  submitted_at TIMESTAMP NULL,
  expires_at TIMESTAMP NULL,
  current_question_index SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  answered_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  correct_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  score_percent DECIMAL(5,2) NULL,
  total_time_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  analysis_json JSON NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_test_session_student (student_profile_id, status),
  INDEX idx_test_session_test (test_id, status),
  CONSTRAINT fk_test_session_test FOREIGN KEY (test_id) REFERENCES tests(id) ON DELETE CASCADE,
  CONSTRAINT fk_test_session_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE test_answers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  test_session_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  selected_choice_id BIGINT UNSIGNED NULL,
  is_correct TINYINT(1) NULL,
  response_time_ms INT UNSIGNED NOT NULL DEFAULT 0,
  is_marked_for_review TINYINT(1) NOT NULL DEFAULT 0,
  answered_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_test_session_question (test_session_id, question_id),
  INDEX idx_test_answer_correct (test_session_id, is_correct),
  CONSTRAINT fk_test_answer_session FOREIGN KEY (test_session_id) REFERENCES test_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_test_answer_question FOREIGN KEY (question_id) REFERENCES questions(id),
  CONSTRAINT fk_test_answer_choice FOREIGN KEY (selected_choice_id) REFERENCES question_choices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;