-- ============================================
-- Training System Refactor: Courses → Modules → Lessons
-- ============================================
-- This migration creates a hierarchical training structure:
-- Course → Modules → Lessons (videos) + Quizzes
-- ============================================

-- ============================================
-- 1. TrainingCourses (Top Level)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingCourses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL COMMENT 'Course title, e.g., "Trenching & Excavation Safety"',
  `description` TEXT NULL COMMENT 'Course overview and objectives',
  `category` VARCHAR(100) NULL COMMENT 'e.g., "Construction Safety", "Electrical Safety"',
  `standard` VARCHAR(100) NULL COMMENT 'e.g., "OSHA 1926 Subpart P", "ISO 45001"',
  `level` ENUM('foundation', 'intermediate', 'advanced') DEFAULT 'foundation' COMMENT 'Difficulty level',
  `thumbnailUrl` TEXT NULL COMMENT 'Course thumbnail image',
  `estimatedDuration` INT NULL COMMENT 'Total estimated minutes',
  `isActive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Published/unpublished',
  `order` INT NOT NULL DEFAULT 0 COMMENT 'Display order',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_courses_category` (`category`),
  KEY `idx_courses_level` (`level`),
  KEY `idx_courses_active` (`isActive`),
  KEY `idx_courses_order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Training courses (top level container)';

-- ============================================
-- 2. TrainingModules (Sections within a course)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingModules` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `TrainingCourseId` INT NOT NULL COMMENT 'Parent course',
  `title` VARCHAR(255) NOT NULL COMMENT 'Module title, e.g., "Introduction to Trenching Safety"',
  `description` TEXT NULL COMMENT 'Module overview',
  `order` INT NOT NULL DEFAULT 0 COMMENT 'Order within course',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_modules_course` (`TrainingCourseId`),
  KEY `idx_modules_order` (`order`),
  CONSTRAINT `TrainingModules_ibfk_course` FOREIGN KEY (`TrainingCourseId`) REFERENCES `TrainingCourses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Modules within a training course';

-- ============================================
-- 3. TrainingLessons (Video lessons within modules)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingLessons` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `TrainingModuleId` INT NOT NULL COMMENT 'Parent module',
  `title` VARCHAR(255) NOT NULL COMMENT 'Lesson title',
  `description` TEXT NULL COMMENT 'Lesson content description',
  `videoFileName` VARCHAR(255) NULL COMMENT 'Uploaded video file (MP4/WebM)',
  `youtubeId` VARCHAR(255) NULL COMMENT 'Legacy YouTube video ID',
  `duration` INT NULL COMMENT 'Video duration in minutes',
  `watchUnlockPercent` INT NOT NULL DEFAULT 80 COMMENT 'Percentage to watch before unlocking quiz',
  `order` INT NOT NULL DEFAULT 0 COMMENT 'Order within module',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_lessons_module` (`TrainingModuleId`),
  KEY `idx_lessons_order` (`order`),
  CONSTRAINT `TrainingLessons_ibfk_module` FOREIGN KEY (`TrainingModuleId`) REFERENCES `TrainingModules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Video lessons within training modules';

-- ============================================
-- 4. TrainingQuizzes (Quizzes per module)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingQuizzes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `TrainingModuleId` INT NOT NULL COMMENT 'Parent module',
  `question` TEXT NOT NULL COMMENT 'Quiz question',
  `options` JSON NOT NULL COMMENT 'Array of answer options',
  `correctAnswer` INT NOT NULL COMMENT 'Index of correct option (0-based)',
  `explanation` TEXT NULL COMMENT 'Optional explanation for the answer',
  `order` INT NOT NULL DEFAULT 0 COMMENT 'Question order within module',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_quizzes_module` (`TrainingModuleId`),
  KEY `idx_quizzes_order` (`order`),
  CONSTRAINT `TrainingQuizzes_ibfk_module` FOREIGN KEY (`TrainingModuleId`) REFERENCES `TrainingModules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Quiz questions for training modules';

-- ============================================
-- 5. TrainingCourseEnrollments (User progress per course)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingCourseEnrollments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `UserId` INT NOT NULL,
  `TrainingCourseId` INT NOT NULL,
  `status` ENUM('not-started', 'in-progress', 'completed', 'failed') NOT NULL DEFAULT 'not-started',
  `enrolledAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completedAt` DATETIME NULL,
  `certificateId` VARCHAR(255) NULL COMMENT 'Certificate ID if completed',
  `overallScore` INT NULL COMMENT 'Average score across all modules',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_course_enrollment` (`UserId`, `TrainingCourseId`),
  KEY `idx_course_enrollments_user` (`UserId`),
  KEY `idx_course_enrollments_course` (`TrainingCourseId`),
  KEY `idx_course_enrollments_status` (`status`),
  CONSTRAINT `TrainingCourseEnrollments_ibfk_user` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TrainingCourseEnrollments_ibfk_course` FOREIGN KEY (`TrainingCourseId`) REFERENCES `TrainingCourses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User enrollment and progress per course';

-- ============================================
-- 6. TrainingModuleProgress (User progress per module)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingModuleProgress` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `UserId` INT NOT NULL,
  `TrainingModuleId` INT NOT NULL,
  `TrainingCourseEnrollmentId` INT NOT NULL COMMENT 'Link to course enrollment',
  `status` ENUM('not-started', 'in-progress', 'completed', 'failed') NOT NULL DEFAULT 'not-started',
  `quizScore` INT NULL COMMENT 'Quiz score percentage (0-100)',
  `quizAnswers` JSON NULL COMMENT 'User quiz answers',
  `completedAt` DATETIME NULL,
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_module_progress` (`UserId`, `TrainingModuleId`),
  KEY `idx_module_progress_user` (`UserId`),
  KEY `idx_module_progress_module` (`TrainingModuleId`),
  KEY `idx_module_progress_enrollment` (`TrainingCourseEnrollmentId`),
  KEY `idx_module_progress_status` (`status`),
  CONSTRAINT `TrainingModuleProgress_ibfk_user` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TrainingModuleProgress_ibfk_module` FOREIGN KEY (`TrainingModuleId`) REFERENCES `TrainingModules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TrainingModuleProgress_ibfk_enrollment` FOREIGN KEY (`TrainingCourseEnrollmentId`) REFERENCES `TrainingCourseEnrollments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User progress per module (quiz completion)';

-- ============================================
-- 7. TrainingLessonProgress (User watch progress per lesson)
-- ============================================
CREATE TABLE IF NOT EXISTS `TrainingLessonProgress` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `UserId` INT NOT NULL,
  `TrainingLessonId` INT NOT NULL,
  `watchProgressPercent` FLOAT NOT NULL DEFAULT 0 COMMENT 'Watch progress 0-100',
  `completed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Watched >= watchUnlockPercent',
  `lastWatchedAt` DATETIME NULL,
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_lesson_progress` (`UserId`, `TrainingLessonId`),
  KEY `idx_lesson_progress_user` (`UserId`),
  KEY `idx_lesson_progress_lesson` (`TrainingLessonId`),
  CONSTRAINT `TrainingLessonProgress_ibfk_user` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TrainingLessonProgress_ibfk_lesson` FOREIGN KEY (`TrainingLessonId`) REFERENCES `TrainingLessons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User watch progress per lesson';

-- ============================================
-- VERIFICATION
-- ============================================
SELECT '✅ Training system tables created successfully!' AS status;
SELECT 'Structure: Course → Modules → Lessons + Quizzes' AS info;
