-- Improved LMS schema (v2)
-- Normalized, with PKs, FKs, timestamps, indexes, and key feature fields
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Users table: students, teachers, admins in one table with roles mapping
CREATE TABLE IF NOT EXISTS `user_account` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NULL,
  `full_name` VARCHAR(255) NULL,
  `phone` VARCHAR(32) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `metadata` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `role` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user_role` (
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  CONSTRAINT `ur_fk_user` FOREIGN KEY (`user_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE,
  CONSTRAINT `ur_fk_role` FOREIGN KEY (`role_id`) REFERENCES `role`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Courses and tagging/categories
CREATE TABLE IF NOT EXISTS `course` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(500) NOT NULL,
  `slug` VARCHAR(500) NOT NULL UNIQUE,
  `short_description` VARCHAR(1000) NULL,
  `long_description` TEXT NULL,
  `price_cents` INT NOT NULL DEFAULT 0,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'INR',
  `is_published` TINYINT(1) NOT NULL DEFAULT 0,
  `is_demo_available` TINYINT(1) NOT NULL DEFAULT 0,
  `created_by` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `course_fk_created_by` FOREIGN KEY (`created_by`) REFERENCES `user_account`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `tag` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `course_tag` (
  `course_id` INT NOT NULL,
  `tag_id` INT NOT NULL,
  PRIMARY KEY (`course_id`,`tag_id`),
  CONSTRAINT `ct_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE,
  CONSTRAINT `ct_fk_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Course <-> teacher many-to-many
CREATE TABLE IF NOT EXISTS `course_teacher` (
  `course_id` INT NOT NULL,
  `teacher_id` INT NOT NULL,
  `role` VARCHAR(100) DEFAULT 'instructor',
  PRIMARY KEY (`course_id`,`teacher_id`),
  CONSTRAINT `cteach_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE,
  CONSTRAINT `cteach_fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Subjects and batches
CREATE TABLE IF NOT EXISTS `subject` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `board` VARCHAR(100) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `batch` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `course_id` INT NULL,
  `start_date` DATE NULL,
  `end_date` DATE NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `batch_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `batch_teacher` (
  `batch_id` INT NOT NULL,
  `teacher_id` INT NOT NULL,
  PRIMARY KEY (`batch_id`,`teacher_id`),
  CONSTRAINT `bt_fk_batch` FOREIGN KEY (`batch_id`) REFERENCES `batch`(`id`) ON DELETE CASCADE,
  CONSTRAINT `bt_fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `batch_student` (
  `batch_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `enrolled_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`batch_id`,`student_id`),
  CONSTRAINT `bs_fk_batch` FOREIGN KEY (`batch_id`) REFERENCES `batch`(`id`) ON DELETE CASCADE,
  CONSTRAINT `bs_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Modules and lessons
CREATE TABLE IF NOT EXISTS `module` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `module_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `lesson` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `module_id` INT NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `description` TEXT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `video_key` VARCHAR(1000) NULL, -- storage key for signed URL
  `video_url` VARCHAR(1000) NULL,
  `notes_key` VARCHAR(1000) NULL,
  `availability_date` DATE NULL,
  `drip_days_after_enrolment` INT NULL,
  `lock_until_quiz_id` INT NULL,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `required_watch_percent` TINYINT(3) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `lesson_fk_module` FOREIGN KEY (`module_id`) REFERENCES `module`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Enrolments and payments
CREATE TABLE IF NOT EXISTS `enrolment` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `batch_id` INT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'active',
  `enrolled_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  `expires_at` TIMESTAMP NULL,
  CONSTRAINT `enrol_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE,
  CONSTRAINT `enrol_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE,
  CONSTRAINT `enrol_fk_batch` FOREIGN KEY (`batch_id`) REFERENCES `batch`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `payment` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_reference` VARCHAR(255) NULL,
  `student_id` INT NOT NULL,
  `course_id` INT NULL,
  `amount_cents` INT NOT NULL,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'INR',
  `gateway` VARCHAR(100) NULL,
  `gateway_txn_id` VARCHAR(255) NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
  `metadata` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `payment_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE,
  CONSTRAINT `payment_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Student progress per lesson
CREATE TABLE IF NOT EXISTS `student_lesson_progress` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `lesson_id` INT NOT NULL,
  `progress_percent` TINYINT(3) NOT NULL DEFAULT 0,
  `watch_time_seconds` INT NOT NULL DEFAULT 0,
  `completed_at` TIMESTAMP NULL,
  `last_watched_at` TIMESTAMP NULL,
  CONSTRAINT `slp_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE,
  CONSTRAINT `slp_fk_lesson` FOREIGN KEY (`lesson_id`) REFERENCES `lesson`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `slp_unique` (`student_id`,`lesson_id`)
) ENGINE=InnoDB;

-- Quizzes
CREATE TABLE IF NOT EXISTS `quiz` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `min_pass_score` INT NOT NULL DEFAULT 0,
  `is_pass_required` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `quiz_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `quiz_question` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `quiz_id` INT NOT NULL,
  `question_text` TEXT NOT NULL,
  `question_type` VARCHAR(50) NOT NULL DEFAULT 'mcq',
  `points` INT NOT NULL DEFAULT 1,
  CONSTRAINT `qq_fk_quiz` FOREIGN KEY (`quiz_id`) REFERENCES `quiz`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `quiz_answer` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `question_id` INT NOT NULL,
  `answer_text` TEXT NOT NULL,
  `is_correct` TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT `qa_fk_question` FOREIGN KEY (`question_id`) REFERENCES `quiz_question`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `quiz_attempt` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `quiz_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `attempted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `score` INT NOT NULL DEFAULT 0,
  `metadata` JSON NULL,
  CONSTRAINT `qat_fk_quiz` FOREIGN KEY (`quiz_id`) REFERENCES `quiz`(`id`) ON DELETE CASCADE,
  CONSTRAINT `qat_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Assignments
CREATE TABLE IF NOT EXISTS `assignment` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `description` TEXT NULL,
  `due_at` TIMESTAMP NULL,
  `allow_late` TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT `assign_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `assignment_submission` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `assignment_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `submitted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `file_key` VARCHAR(1000) NULL,
  `grade` DECIMAL(5,2) NULL,
  `feedback` TEXT NULL,
  CONSTRAINT `asub_fk_assignment` FOREIGN KEY (`assignment_id`) REFERENCES `assignment`(`id`) ON DELETE CASCADE,
  CONSTRAINT `asub_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Doubts/helpdesk
CREATE TABLE IF NOT EXISTS `doubt` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `course_id` INT NULL,
  `title` VARCHAR(500) NULL,
  `body` TEXT NOT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'open',
  `assigned_teacher_id` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` TIMESTAMP NULL,
  CONSTRAINT `doubt_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE,
  CONSTRAINT `doubt_fk_teacher` FOREIGN KEY (`assigned_teacher_id`) REFERENCES `user_account`(`id`) ON DELETE SET NULL,
  CONSTRAINT `doubt_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `doubt_message` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `doubt_id` INT NOT NULL,
  `sender_user_id` INT NOT NULL,
  `body` TEXT NULL,
  `attachment_key` VARCHAR(1000) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `dmsg_fk_doubt` FOREIGN KEY (`doubt_id`) REFERENCES `doubt`(`id`) ON DELETE CASCADE,
  CONSTRAINT `dmsg_fk_user` FOREIGN KEY (`sender_user_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Live classes
CREATE TABLE IF NOT EXISTS `live_class` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `course_id` INT NOT NULL,
  `title` VARCHAR(500) NULL,
  `provider` VARCHAR(100) NULL,
  `provider_room_id` VARCHAR(255) NULL,
  `start_at` TIMESTAMP NULL,
  `end_at` TIMESTAMP NULL,
  `recording_key` VARCHAR(1000) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `lc_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `live_class_attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `live_class_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `joined_at` TIMESTAMP NULL,
  `left_at` TIMESTAMP NULL,
  CONSTRAINT `lca_fk_lc` FOREIGN KEY (`live_class_id`) REFERENCES `live_class`(`id`) ON DELETE CASCADE,
  CONSTRAINT `lca_fk_student` FOREIGN KEY (`student_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Devices & sessions
CREATE TABLE IF NOT EXISTS `device` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `device_id` VARCHAR(255) NOT NULL,
  `device_info` VARCHAR(1000) NULL,
  `last_seen_at` TIMESTAMP NULL,
  CONSTRAINT `device_fk_user` FOREIGN KEY (`user_id`) REFERENCES `user_account`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `user_device_unique` (`user_id`,`device_id`)
) ENGINE=InnoDB;

-- Audit logs
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `actor_user_id` INT NULL,
  `action` VARCHAR(255) NOT NULL,
  `object_type` VARCHAR(100) NULL,
  `object_id` VARCHAR(255) NULL,
  `metadata` JSON NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `audit_fk_user` FOREIGN KEY (`actor_user_id`) REFERENCES `user_account`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Simple indexes to support common queries
CREATE INDEX IF NOT EXISTS `idx_course_price` ON `course` (`price_cents`);
CREATE INDEX IF NOT EXISTS `idx_enrol_student_course` ON `enrolment` (`student_id`,`course_id`);
CREATE INDEX IF NOT EXISTS `idx_payment_student` ON `payment` (`student_id`,`status`);

-- End of schema v2