-- Qudurat AI - M7 AI Tutor
-- Run AFTER M1..M6
USE qudurat_ai;

CREATE TABLE ai_tutor_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NULL,
  training_session_id BIGINT UNSIGNED NULL,
  test_session_id BIGINT UNSIGNED NULL,
  status ENUM('active','completed','closed') NOT NULL DEFAULT 'active',
  mode ENUM('hint','explain','error_analysis','concept','similar_example','general') NOT NULL DEFAULT 'general',
  started_at TIMESTAMP NOT NULL,
  ended_at TIMESTAMP NULL,
  last_activity_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ai_tutor_student_status (student_profile_id, status),
  INDEX idx_ai_tutor_question_student (question_id, student_profile_id),
  CONSTRAINT fk_ai_tutor_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_ai_tutor_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE SET NULL,
  CONSTRAINT fk_ai_tutor_training_session FOREIGN KEY (training_session_id) REFERENCES training_sessions(id) ON DELETE SET NULL,
  CONSTRAINT fk_ai_tutor_test_session FOREIGN KEY (test_session_id) REFERENCES test_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_tutor_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ai_tutor_session_id BIGINT UNSIGNED NOT NULL,
  role ENUM('student','assistant','system') NOT NULL,
  message_type ENUM('question','hint','explanation','error_analysis','concept','similar_example','general') NOT NULL DEFAULT 'general',
  content LONGTEXT NOT NULL,
  model VARCHAR(100) NULL,
  prompt_tokens INT UNSIGNED NULL,
  output_tokens INT UNSIGNED NULL,
  latency_ms INT UNSIGNED NULL,
  context_json JSON NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ai_tutor_message_history (ai_tutor_session_id, created_at),
  INDEX idx_ai_tutor_message_role (role),
  CONSTRAINT fk_ai_tutor_message_session FOREIGN KEY (ai_tutor_session_id) REFERENCES ai_tutor_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_tutor_feedback (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ai_tutor_message_id BIGINT UNSIGNED NOT NULL,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  rating ENUM('helpful','not_helpful') NOT NULL,
  feedback_type VARCHAR(100) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ai_message_student_feedback (ai_tutor_message_id, student_profile_id),
  CONSTRAINT fk_ai_feedback_message FOREIGN KEY (ai_tutor_message_id) REFERENCES ai_tutor_messages(id) ON DELETE CASCADE,
  CONSTRAINT fk_ai_feedback_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;