-- Qudurat AI - M3 Student Training Engine
-- Run AFTER M1_SCHEMA.sql and M2_QUESTION_BANK_SCHEMA.sql
USE qudurat_ai;

CREATE TABLE training_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  session_type ENUM('daily','remedial','skill','section','free') NOT NULL DEFAULT 'daily',
  target_skill_id BIGINT UNSIGNED NULL,
  started_at TIMESTAMP NOT NULL,
  ended_at TIMESTAMP NULL,
  status ENUM('active','completed','abandoned') NOT NULL DEFAULT 'active',
  questions_answered INT UNSIGNED NOT NULL DEFAULT 0,
  correct_answers INT UNSIGNED NOT NULL DEFAULT 0,
  total_active_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_training_student_status (student_profile_id, status),
  CONSTRAINT fk_training_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_training_skill FOREIGN KEY (target_skill_id) REFERENCES skills(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_skill_mastery (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  skill_id BIGINT UNSIGNED NOT NULL,
  mastery_score DECIMAL(5,2) NOT NULL DEFAULT 50.00,
  attempts_count INT UNSIGNED NOT NULL DEFAULT 0,
  correct_count INT UNSIGNED NOT NULL DEFAULT 0,
  consecutive_correct SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  consecutive_wrong SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  average_response_time_ms INT UNSIGNED NULL,
  last_practiced_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_skill_mastery (student_profile_id, skill_id),
  INDEX idx_student_mastery_score (student_profile_id, mastery_score),
  CONSTRAINT fk_mastery_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_mastery_skill FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_question_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  training_session_id BIGINT UNSIGNED NOT NULL,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  selected_choice_id BIGINT UNSIGNED NULL,
  is_correct TINYINT(1) NOT NULL,
  response_time_ms INT UNSIGNED NOT NULL,
  difficulty_at_attempt ENUM('easy','medium','hard','advanced') NOT NULL,
  mastery_before DECIMAL(5,2) NOT NULL,
  mastery_after DECIMAL(5,2) NOT NULL,
  answered_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_attempt_student_question (student_profile_id, question_id),
  INDEX idx_attempt_student_date (student_profile_id, answered_at),
  INDEX idx_attempt_question_correct (question_id, is_correct),
  CONSTRAINT fk_attempt_session FOREIGN KEY (training_session_id) REFERENCES training_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_attempt_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_attempt_question FOREIGN KEY (question_id) REFERENCES questions(id),
  CONSTRAINT fk_attempt_choice FOREIGN KEY (selected_choice_id) REFERENCES question_choices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE mastery_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  skill_id BIGINT UNSIGNED NOT NULL,
  attempt_id BIGINT UNSIGNED NULL,
  score_before DECIMAL(5,2) NOT NULL,
  attempt_score DECIMAL(5,2) NOT NULL,
  score_after DECIMAL(5,2) NOT NULL,
  calculation_json JSON NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_mastery_history (student_profile_id, skill_id, created_at),
  CONSTRAINT fk_mastery_event_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_mastery_event_skill FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
  CONSTRAINT fk_mastery_event_attempt FOREIGN KEY (attempt_id) REFERENCES student_question_attempts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_error_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  skill_id BIGINT UNSIGNED NOT NULL,
  attempt_id BIGINT UNSIGNED NULL,
  error_type ENUM('understanding','rule','calculation','inference','speed','prerequisite','unknown') NOT NULL DEFAULT 'unknown',
  classification_source ENUM('rule','ai','reviewer') NOT NULL DEFAULT 'rule',
  confidence DECIMAL(5,4) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_student_errors (student_profile_id, skill_id, error_type),
  CONSTRAINT fk_error_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_error_question FOREIGN KEY (question_id) REFERENCES questions(id),
  CONSTRAINT fk_error_skill FOREIGN KEY (skill_id) REFERENCES skills(id),
  CONSTRAINT fk_error_attempt FOREIGN KEY (attempt_id) REFERENCES student_question_attempts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;