-- Qudurat AI - M4 Adaptive Engine v2
-- Run AFTER M1, M2, M3
USE qudurat_ai;

CREATE TABLE skill_prerequisites (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  skill_id BIGINT UNSIGNED NOT NULL,
  prerequisite_skill_id BIGINT UNSIGNED NOT NULL,
  minimum_mastery_required DECIMAL(5,2) NOT NULL DEFAULT 60.00,
  priority_weight DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_skill_prerequisite (skill_id, prerequisite_skill_id),
  CONSTRAINT fk_skill_prereq_skill FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE,
  CONSTRAINT fk_skill_prereq_required FOREIGN KEY (prerequisite_skill_id) REFERENCES skills(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE question_exposure_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  training_session_id BIGINT UNSIGNED NULL,
  exposure_type ENUM('shown','answered','remedial') NOT NULL DEFAULT 'shown',
  shown_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_question_exposure (student_profile_id, question_id, shown_at),
  CONSTRAINT fk_exposure_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_exposure_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
  CONSTRAINT fk_exposure_session FOREIGN KEY (training_session_id) REFERENCES training_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE adaptive_recommendations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  training_session_id BIGINT UNSIGNED NULL,
  recommended_skill_id BIGINT UNSIGNED NULL,
  recommended_question_id BIGINT UNSIGNED NULL,
  recommended_difficulty ENUM('easy','medium','hard','advanced') NULL,
  mode ENUM('normal','remedial','prerequisite','speed') NOT NULL DEFAULT 'normal',
  priority_score DECIMAL(8,4) NOT NULL DEFAULT 0,
  reason_code VARCHAR(100) NULL,
  reason_json JSON NULL,
  consumed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_adaptive_student (student_profile_id, created_at),
  INDEX idx_adaptive_reason (reason_code),
  CONSTRAINT fk_adaptive_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_adaptive_session FOREIGN KEY (training_session_id) REFERENCES training_sessions(id) ON DELETE SET NULL,
  CONSTRAINT fk_adaptive_skill FOREIGN KEY (recommended_skill_id) REFERENCES skills(id) ON DELETE SET NULL,
  CONSTRAINT fk_adaptive_question FOREIGN KEY (recommended_question_id) REFERENCES questions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;