-- =========================================================
-- SIMDATADIK - Schema E-Learning (Tahap E1)
-- Database: MySQL / MariaDB
-- Catatan:
-- 1. File ini hanya MENAMBAHKAN tabel e-learning baru.
-- 2. Tabel global `notifications` SUDAH ADA di schema lama,
--    sehingga TIDAK dibuat ulang di file ini agar aman dieksekusi.
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- 1. elearning_courses
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_courses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `school_id` BIGINT UNSIGNED NOT NULL,
  `teacher_id` BIGINT UNSIGNED NOT NULL,
  `academic_year_id` BIGINT UNSIGNED DEFAULT NULL,
  `class_group_id` BIGINT UNSIGNED DEFAULT NULL,
  `subject_id` BIGINT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `level_label` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `start_at` DATETIME DEFAULT NULL,
  `end_at` DATETIME DEFAULT NULL,
  `cover_image` VARCHAR(255) DEFAULT NULL,
  `is_auto_enroll` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ec_school` (`school_id`),
  KEY `idx_ec_teacher` (`teacher_id`),
  KEY `idx_ec_academic_year` (`academic_year_id`),
  KEY `idx_ec_class_group` (`class_group_id`),
  KEY `idx_ec_subject` (`subject_id`),
  KEY `idx_ec_status` (`status`),
  CONSTRAINT `fk_ec_school`
    FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ec_teacher`
    FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ec_academic_year`
    FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_ec_class_group`
    FOREIGN KEY (`class_group_id`) REFERENCES `class_groups` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_ec_subject`
    FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 2. elearning_modules
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_modules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `order_index` INT UNSIGNED NOT NULL DEFAULT 1,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_em_course` (`course_id`),
  KEY `idx_em_course_order` (`course_id`, `order_index`),
  CONSTRAINT `fk_em_course`
    FOREIGN KEY (`course_id`) REFERENCES `elearning_courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 3. elearning_lessons
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_lessons` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `module_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `content_type` ENUM('text','file','link','video') NOT NULL DEFAULT 'text',
  `content_text` LONGTEXT DEFAULT NULL,
  `content_file` VARCHAR(255) DEFAULT NULL,
  `content_url` VARCHAR(255) DEFAULT NULL,
  `duration_minutes` INT UNSIGNED DEFAULT NULL,
  `order_index` INT UNSIGNED NOT NULL DEFAULT 1,
  `is_preview` TINYINT(1) NOT NULL DEFAULT 0,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_el_module` (`module_id`),
  KEY `idx_el_module_order` (`module_id`, `order_index`),
  KEY `idx_el_type` (`content_type`),
  CONSTRAINT `fk_el_module`
    FOREIGN KEY (`module_id`) REFERENCES `elearning_modules` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 4. elearning_tasks
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_tasks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` BIGINT UNSIGNED NOT NULL,
  `module_id` BIGINT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `instruction` LONGTEXT DEFAULT NULL,
  `max_score` DECIMAL(8,2) NOT NULL DEFAULT 100.00,
  `start_at` DATETIME DEFAULT NULL,
  `due_at` DATETIME DEFAULT NULL,
  `allow_late_submission` TINYINT(1) NOT NULL DEFAULT 0,
  `is_required` TINYINT(1) NOT NULL DEFAULT 1,
  `status` ENUM('draft','published','closed','archived') NOT NULL DEFAULT 'draft',
  `attachment_file` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_et_course` (`course_id`),
  KEY `idx_et_module` (`module_id`),
  KEY `idx_et_status` (`status`),
  KEY `idx_et_due_at` (`due_at`),
  CONSTRAINT `fk_et_course`
    FOREIGN KEY (`course_id`) REFERENCES `elearning_courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_et_module`
    FOREIGN KEY (`module_id`) REFERENCES `elearning_modules` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5. elearning_task_submissions
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_task_submissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id` BIGINT UNSIGNED NOT NULL,
  `student_id` BIGINT UNSIGNED NOT NULL,
  `answer_text` LONGTEXT DEFAULT NULL,
  `answer_file` VARCHAR(255) DEFAULT NULL,
  `submitted_at` DATETIME DEFAULT NULL,
  `score` DECIMAL(8,2) DEFAULT NULL,
  `feedback` TEXT DEFAULT NULL,
  `graded_by` BIGINT UNSIGNED DEFAULT NULL,
  `graded_at` DATETIME DEFAULT NULL,
  `status` ENUM('draft','submitted','graded','returned') NOT NULL DEFAULT 'submitted',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ets_task_student` (`task_id`, `student_id`),
  KEY `idx_ets_student` (`student_id`),
  KEY `idx_ets_graded_by` (`graded_by`),
  KEY `idx_ets_status` (`status`),
  CONSTRAINT `fk_ets_task`
    FOREIGN KEY (`task_id`) REFERENCES `elearning_tasks` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ets_student`
    FOREIGN KEY (`student_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ets_graded_by`
    FOREIGN KEY (`graded_by`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 6. elearning_enrollments
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_enrollments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `enrollment_source` ENUM('auto_class_group','manual','imported') NOT NULL DEFAULT 'auto_class_group',
  `enrolled_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ee_course_user` (`course_id`, `user_id`),
  KEY `idx_ee_user` (`user_id`),
  CONSTRAINT `fk_ee_course`
    FOREIGN KEY (`course_id`) REFERENCES `elearning_courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ee_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 7. elearning_activity_logs
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_activity_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `course_id` BIGINT UNSIGNED NOT NULL,
  `module_id` BIGINT UNSIGNED DEFAULT NULL,
  `lesson_id` BIGINT UNSIGNED DEFAULT NULL,
  `task_id` BIGINT UNSIGNED DEFAULT NULL,
  `activity_type` ENUM(
    'view_course',
    'view_module',
    'view_lesson',
    'complete_lesson',
    'download_material',
    'submit_task',
    'update_submission',
    'grade_task',
    'open_feed',
    'login_lms'
  ) NOT NULL,
  `activity_note` VARCHAR(255) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_eal_user` (`user_id`),
  KEY `idx_eal_course` (`course_id`),
  KEY `idx_eal_module` (`module_id`),
  KEY `idx_eal_lesson` (`lesson_id`),
  KEY `idx_eal_task` (`task_id`),
  KEY `idx_eal_type` (`activity_type`),
  KEY `idx_eal_created_at` (`created_at`),
  CONSTRAINT `fk_eal_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_eal_course`
    FOREIGN KEY (`course_id`) REFERENCES `elearning_courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_eal_module`
    FOREIGN KEY (`module_id`) REFERENCES `elearning_modules` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_eal_lesson`
    FOREIGN KEY (`lesson_id`) REFERENCES `elearning_lessons` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_eal_task`
    FOREIGN KEY (`task_id`) REFERENCES `elearning_tasks` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 8. elearning_feeds
-- =========================================================
CREATE TABLE IF NOT EXISTS `elearning_feeds` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `school_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `course_id` BIGINT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `category` ENUM('artikel','materi','event','lomba') NOT NULL DEFAULT 'artikel',
  `visibility` ENUM('public_school','course_only') NOT NULL DEFAULT 'public_school',
  `cover_image` VARCHAR(255) DEFAULT NULL,
  `attachment_file` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('draft','published','archived') NOT NULL DEFAULT 'published',
  `published_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ef_school` (`school_id`),
  KEY `idx_ef_user` (`user_id`),
  KEY `idx_ef_course` (`course_id`),
  KEY `idx_ef_category` (`category`),
  KEY `idx_ef_visibility` (`visibility`),
  KEY `idx_ef_status` (`status`),
  CONSTRAINT `fk_ef_school`
    FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ef_user`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ef_course`
    FOREIGN KEY (`course_id`) REFERENCES `elearning_courses` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 9. Catatan untuk notifications
-- =========================================================
-- Tabel `notifications` SUDAH ADA pada schema lama SIMDATADIK.
-- Struktur lama:
--   id, user_id, title, body, link_url, is_read, read_at, created_at
-- Karena sudah tersedia, file ini sengaja tidak membuat ulang tabel tersebut
-- agar tidak terjadi konflik "table already exists" saat import.

SET FOREIGN_KEY_CHECKS = 1;