-- Lesson Content Table
CREATE TABLE IF NOT EXISTS lesson_content (
  id INT PRIMARY KEY AUTO_INCREMENT,
  task_id INT NOT NULL,
  content TEXT NOT NULL,
  content_type ENUM('markdown', 'html') DEFAULT 'markdown',
  reading_time_minutes INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  UNIQUE KEY unique_task_content (task_id)
);

-- Lesson Questions Table
CREATE TABLE IF NOT EXISTS lesson_questions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  task_id INT NOT NULL,
  question_text TEXT NOT NULL,
  question_order INT NOT NULL,
  correct_answer TEXT NOT NULL,
  case_sensitive BOOLEAN DEFAULT FALSE,
  hint TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  INDEX idx_task_order (task_id, question_order)
);

-- User Lesson Answers Table
CREATE TABLE IF NOT EXISTS user_lesson_answers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  question_id INT NOT NULL,
  submitted_answer TEXT,
  is_correct BOOLEAN,
  submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES lesson_questions(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_question (user_id, question_id)
);

-- User Lesson Grades Table
CREATE TABLE IF NOT EXISTS user_lesson_grades (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  task_id INT NOT NULL,
  score DECIMAL(5,2) NOT NULL,
  total_questions INT NOT NULL,
  correct_answers INT NOT NULL,
  passed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_task_grade (user_id, task_id)
);
