<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class InitialSchema extends Migration
{
    public function up()
    {
        // Disable foreign key checks to prevent ordering issues
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0;');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `academic_periods` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `academic_year_id` bigint(20) unsigned NOT NULL,
              `semester` enum(\'Ganjil\',\'Genap\') NOT NULL,
              `is_active` tinyint(1) NOT NULL DEFAULT 0,
              `start_date` date DEFAULT NULL,
              `end_date` date DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `academic_periods_academic_year_id_foreign` (`academic_year_id`),
              CONSTRAINT `academic_periods_academic_year_id_foreign` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `academic_years` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(20) NOT NULL,
              `start_date` date DEFAULT NULL,
              `end_date` date DEFAULT NULL,
              `is_active` tinyint(1) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `activity_logs` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `user_id` bigint(20) unsigned DEFAULT NULL,
              `action` varchar(50) NOT NULL,
              `target` varchar(100) NOT NULL,
              `record_id` int(11) DEFAULT NULL,
              `description` text NOT NULL,
              `ip_address` varchar(45) NOT NULL,
              `user_agent` varchar(255) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `activity_logs_user_id_foreign` (`user_id`),
              CONSTRAINT `activity_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `ai_kb_cp` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `subject_id` bigint(20) unsigned DEFAULT NULL,
              `academic_year_id` bigint(20) unsigned DEFAULT NULL,
              `user_id` bigint(20) unsigned DEFAULT NULL,
              `subject_name` varchar(100) NOT NULL,
              `phase` varchar(10) NOT NULL,
              `content` text NOT NULL,
              `elements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`elements`)),
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `ai_kb_cp_subject_id_foreign` (`subject_id`),
              KEY `idx_ai_kb_cp_academic_year_id` (`academic_year_id`),
              KEY `idx_ai_kb_cp_user_id` (`user_id`),
              CONSTRAINT `ai_kb_cp_academic_year_id_foreign` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
              CONSTRAINT `ai_kb_cp_subject_id_foreign` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
              CONSTRAINT `ai_kb_cp_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `ai_kb_tp` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `academic_year_id` int(11) unsigned NOT NULL,
              `user_id` int(11) unsigned NOT NULL,
              `subject_id` varchar(50) DEFAULT NULL,
              `subject_name` varchar(150) NOT NULL,
              `grade_id` int(11) unsigned NOT NULL,
              `grade_name` varchar(50) NOT NULL,
              `chapters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`chapters`)),
              `dpl` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`dpl`)),
              `panca_cinta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`panca_cinta`)),
              `tps` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tps`)),
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `asc_classes` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `import_id` int(11) unsigned NOT NULL,
              `asc_id` varchar(50) NOT NULL COMMENT \'ID asli dari XML aSc\',
              `name` varchar(50) NOT NULL COMMENT \'Misal: 7A, 8B, 9A1\',
              `short_name` varchar(20) NOT NULL,
              `grade_level` tinyint(2) DEFAULT NULL COMMENT \'7, 8, atau 9 — diambil dari prefix nama kelas\',
              `classroom_id` int(11) unsigned DEFAULT NULL COMMENT \'FK ke classrooms (nanti)\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `import_id` (`import_id`),
              KEY `asc_id` (`asc_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `asc_imports` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(100) NOT NULL COMMENT \'Misal: Ganjil 2024/2025\',
              `academic_year` varchar(20) NOT NULL COMMENT \'Misal: 2024/2025\',
              `semester` enum(\'Ganjil\',\'Genap\') NOT NULL DEFAULT \'Ganjil\',
              `filename` varchar(255) NOT NULL COMMENT \'Nama file XML yang diupload\',
              `is_active` tinyint(1) NOT NULL DEFAULT 0 COMMENT \'1 = jadwal aktif yang ditampilkan\',
              `total_schedules` int(11) NOT NULL DEFAULT 0 COMMENT \'Total slot jadwal hasil import\',
              `imported_by` int(11) unsigned DEFAULT NULL COMMENT \'user_id yang melakukan import\',
              `notes` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `is_active` (`is_active`)
            ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `asc_periods` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `import_id` int(11) unsigned NOT NULL,
              `period_number` tinyint(2) NOT NULL COMMENT \'Jam ke- (1, 2, 3, ...)\',
              `name` varchar(20) NOT NULL COMMENT \'Nama periode dari XML\',
              `start_time` varchar(10) NOT NULL COMMENT \'Misal: 07:00\',
              `end_time` varchar(10) NOT NULL COMMENT \'Misal: 07:40\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `import_id` (`import_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `asc_schedules` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `import_id` int(11) unsigned NOT NULL,
              `class_asc_id` varchar(50) NOT NULL,
              `class_name` varchar(50) NOT NULL COMMENT \'Misal: 7A\',
              `grade_level` tinyint(2) DEFAULT NULL COMMENT \'7, 8, atau 9\',
              `subject_asc_id` varchar(50) NOT NULL,
              `subject_name` varchar(150) NOT NULL,
              `subject_code` varchar(20) NOT NULL COMMENT \'Kode singkat: MAT, BIG, IPA\',
              `teacher_asc_id` varchar(50) NOT NULL,
              `teacher_name` varchar(150) NOT NULL,
              `teacher_code` varchar(10) NOT NULL COMMENT \'Short code guru: 01, 02, 32\',
              `teacher_color` varchar(10) NOT NULL DEFAULT \'#CCCCCC\' COMMENT \'Warna unik guru dari XML\',
              `day_number` tinyint(1) NOT NULL COMMENT \'1=Senin, 2=Selasa, ..., 6=Sabtu\',
              `day_name` varchar(10) NOT NULL COMMENT \'Senin, Selasa, ..., Sabtu\',
              `period_number` tinyint(2) NOT NULL COMMENT \'Jam ke-1, ke-2, dst\',
              `start_time` varchar(10) NOT NULL COMMENT \'Misal: 07:00\',
              `end_time` varchar(10) NOT NULL COMMENT \'Misal: 07:40\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `import_id` (`import_id`),
              KEY `class_asc_id` (`class_asc_id`),
              KEY `teacher_asc_id` (`teacher_asc_id`),
              KEY `idx_schedule_lookup` (`import_id`,`day_number`,`period_number`),
              KEY `idx_teacher_schedule` (`import_id`,`teacher_asc_id`,`day_number`),
              KEY `idx_class_schedule` (`import_id`,`class_asc_id`,`day_number`)
            ) ENGINE=InnoDB AUTO_INCREMENT=3097 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `asc_subjects` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `import_id` int(11) unsigned NOT NULL,
              `asc_id` varchar(50) NOT NULL COMMENT \'ID asli dari XML aSc\',
              `name` varchar(150) NOT NULL,
              `short_code` varchar(20) NOT NULL COMMENT \'Kode singkat, misal: MAT, BIG, IPA\',
              `school_subject_id` int(11) unsigned DEFAULT NULL COMMENT \'FK ke school_subjects (nanti)\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `import_id` (`import_id`),
              KEY `asc_id` (`asc_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `asc_teachers` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `import_id` int(11) unsigned NOT NULL,
              `asc_id` varchar(50) NOT NULL COMMENT \'ID asli dari XML aSc\',
              `name` varchar(150) NOT NULL,
              `short_code` varchar(10) NOT NULL COMMENT \'Kode guru, misal: 01, 02, 32\',
              `gender` enum(\'M\',\'F\') NOT NULL DEFAULT \'M\',
              `color` varchar(10) NOT NULL DEFAULT \'#CCCCCC\' COMMENT \'Warna unik guru dari XML, misal: #FF0000\',
              `teacher_id` int(11) unsigned DEFAULT NULL COMMENT \'FK ke teachers (nanti)\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `import_id` (`import_id`),
              KEY `asc_id` (`asc_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `assessment_class_settings` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `assessment_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `subject_id` bigint(20) unsigned NOT NULL,
              `teacher_id` bigint(20) unsigned DEFAULT NULL,
              `kktp` tinyint(4) NOT NULL DEFAULT 75,
              `bobot_pg` tinyint(4) NOT NULL DEFAULT 0,
              `bobot_bs` tinyint(4) NOT NULL DEFAULT 0,
              `bobot_isian` tinyint(4) NOT NULL DEFAULT 0,
              `bobot_essay` tinyint(4) NOT NULL DEFAULT 0,
              `kunci_pg` text DEFAULT NULL,
              `kunci_bs` text DEFAULT NULL,
              `bobot_filled` tinyint(1) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `assessment_class_settings_assessment_classroom_subject` (`assessment_id`,`classroom_id`,`subject_id`),
              KEY `assessment_class_settings_classroom_id_foreign` (`classroom_id`),
              KEY `assessment_class_settings_teacher_id_foreign` (`teacher_id`),
              KEY `fk_acs_subject_id` (`subject_id`),
              CONSTRAINT `assessment_class_settings_assessment_id_foreign` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessment_class_settings_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessment_class_settings_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
              CONSTRAINT `fk_acs_subject_id` FOREIGN KEY (`subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `assessment_detail_scores` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `score_id` bigint(20) unsigned NOT NULL,
              `jenis` enum(\'isian\',\'essay\') NOT NULL,
              `nomor_soal` smallint(5) unsigned NOT NULL,
              `skor` decimal(5,2) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `score_id_jenis_nomor_soal` (`score_id`,`jenis`,`nomor_soal`),
              CONSTRAINT `assessment_detail_scores_score_id_foreign` FOREIGN KEY (`score_id`) REFERENCES `assessment_scores` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=480 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `assessment_scores` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `assessment_id` bigint(20) unsigned NOT NULL,
              `student_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned DEFAULT NULL,
              `subject_id` bigint(20) unsigned NOT NULL,
              `jawaban_pg` text DEFAULT NULL,
              `jawaban_bs` text DEFAULT NULL,
              `skor_pg` decimal(5,2) DEFAULT NULL,
              `skor_bs` decimal(5,2) DEFAULT NULL,
              `skor_isian` decimal(5,2) DEFAULT NULL,
              `skor_essay` decimal(5,2) DEFAULT NULL,
              `nilai_akhir` decimal(5,2) unsigned DEFAULT NULL,
              `notif_pg` varchar(50) DEFAULT NULL,
              `notif_bs` varchar(50) DEFAULT NULL,
              `multiple_choice_score` tinyint(4) DEFAULT NULL,
              `essay_score` tinyint(4) DEFAULT NULL,
              `final_score` tinyint(4) DEFAULT NULL,
              `answers` text DEFAULT NULL,
              `is_complete` tinyint(4) NOT NULL DEFAULT 0,
              `graded_by` bigint(20) unsigned DEFAULT NULL,
              `graded_at` datetime DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `assessment_scores_assessment_student_subject` (`assessment_id`,`student_id`,`subject_id`),
              KEY `assessment_scores_student_id_foreign` (`student_id`),
              KEY `assessment_scores_graded_by_foreign` (`graded_by`),
              KEY `assessment_scores_classroom_id_foreign` (`classroom_id`),
              KEY `fk_aso_subject_id` (`subject_id`),
              CONSTRAINT `assessment_scores_assessment_id_foreign` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessment_scores_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE SET NULL,
              CONSTRAINT `assessment_scores_graded_by_foreign` FOREIGN KEY (`graded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
              CONSTRAINT `assessment_scores_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_aso_subject_id` FOREIGN KEY (`subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `assessment_types` (
              `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(50) NOT NULL,
              `code` varchar(10) NOT NULL,
              `created_by` tinyint(1) NOT NULL DEFAULT 1,
              `is_active` tinyint(1) NOT NULL DEFAULT 1,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `assessments` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `assessment_type_id` tinyint(3) unsigned NOT NULL,
              `name` varchar(100) NOT NULL,
              `description` text DEFAULT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `subject_id` bigint(20) unsigned DEFAULT NULL,
              `classroom_id` bigint(20) unsigned DEFAULT NULL,
              `grade_id` bigint(20) unsigned DEFAULT NULL,
              `has_pg` tinyint(1) DEFAULT 0,
              `has_bs` tinyint(1) DEFAULT 0,
              `has_isian` tinyint(1) DEFAULT 0,
              `has_essay` tinyint(1) DEFAULT 0,
              `bobot_by_admin` tinyint(1) NOT NULL DEFAULT 0,
              `admin_bobot_pg` int(11) NOT NULL DEFAULT 0,
              `admin_bobot_bs` int(11) NOT NULL DEFAULT 0,
              `admin_bobot_isian` int(11) NOT NULL DEFAULT 0,
              `admin_bobot_essay` int(11) NOT NULL DEFAULT 0,
              `isian_mode` enum(\'total\',\'per_nomor\') DEFAULT \'total\',
              `isian_total_soal` smallint(6) DEFAULT 0,
              `essay_mode` enum(\'total\',\'per_nomor\') DEFAULT \'total\',
              `essay_total_soal` smallint(6) DEFAULT 0,
              `teacher_id` bigint(20) unsigned DEFAULT NULL,
              `start_date` date DEFAULT NULL,
              `end_date` date DEFAULT NULL,
              `is_open` tinyint(1) NOT NULL DEFAULT 1,
              `is_visible` tinyint(1) NOT NULL DEFAULT 1,
              `created_by` bigint(20) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `assessments_assessment_type_id_foreign` (`assessment_type_id`),
              KEY `assessments_academic_period_id_foreign` (`academic_period_id`),
              KEY `assessments_subject_id_foreign` (`subject_id`),
              KEY `assessments_classroom_id_foreign` (`classroom_id`),
              KEY `assessments_teacher_id_foreign` (`teacher_id`),
              KEY `assessments_created_by_foreign` (`created_by`),
              CONSTRAINT `assessments_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessments_assessment_type_id_foreign` FOREIGN KEY (`assessment_type_id`) REFERENCES `assessment_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessments_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessments_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessments_subject_id_foreign` FOREIGN KEY (`subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `assessments_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `attendances` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `student_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `date` date NOT NULL,
              `status` enum(\'Sakit\',\'Izin\',\'Alpha\') NOT NULL,
              `recorded_by` bigint(20) unsigned DEFAULT NULL,
              `source` enum(\'manual\',\'qr_scan\') NOT NULL DEFAULT \'manual\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `student_id_date` (`student_id`,`date`),
              UNIQUE KEY `unique_attendance` (`student_id`,`date`,`academic_period_id`),
              KEY `attendances_classroom_id_foreign` (`classroom_id`),
              KEY `attendances_academic_period_id_foreign` (`academic_period_id`),
              KEY `attendances_recorded_by_foreign` (`recorded_by`),
              CONSTRAINT `attendances_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE,
              CONSTRAINT `attendances_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE,
              CONSTRAINT `attendances_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
              CONSTRAINT `attendances_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `bk_assignments` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `teacher_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `classroom_id_academic_period_id` (`classroom_id`,`academic_period_id`),
              KEY `bk_assignments_teacher_id_foreign` (`teacher_id`),
              KEY `bk_assignments_academic_period_id_foreign` (`academic_period_id`),
              CONSTRAINT `bk_assignments_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `bk_assignments_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `bk_assignments_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `calendar_categories` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(100) NOT NULL,
              `code` varchar(10) NOT NULL COMMENT \'Kode singkat ditampilkan di grid, misal: LU, PTS, PAS\',
              `color` varchar(10) NOT NULL DEFAULT \'#CCCCCC\' COMMENT \'Warna kategori (hex)\',
              `is_libur` tinyint(1) NOT NULL DEFAULT 0 COMMENT \'1 = dihitung sebagai hari libur (mengurangi hari efektif)\',
              `sort_order` int(11) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `calendar_events` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `academic_year_id` bigint(20) unsigned NOT NULL COMMENT \'FK ke academic_years\',
              `academic_period_id` bigint(20) unsigned DEFAULT NULL COMMENT \'FK ke academic_periods (opsional, filter semester)\',
              `category_id` bigint(20) unsigned NOT NULL COMMENT \'FK ke calendar_categories\',
              `title` varchar(200) NOT NULL,
              `start_date` date NOT NULL,
              `end_date` date NOT NULL,
              `description` text DEFAULT NULL,
              `created_by` bigint(20) unsigned DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `calendar_events_academic_year_id_foreign` (`academic_year_id`),
              KEY `calendar_events_academic_period_id_foreign` (`academic_period_id`),
              KEY `calendar_events_category_id_foreign` (`category_id`),
              KEY `idx_event_dates` (`start_date`,`end_date`),
              CONSTRAINT `calendar_events_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
              CONSTRAINT `calendar_events_academic_year_id_foreign` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `calendar_events_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `calendar_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `calendar_settings` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `key` varchar(100) NOT NULL,
              `value` text DEFAULT NULL,
              `label` varchar(200) DEFAULT NULL COMMENT \'Label deskriptif untuk UI\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `calendar_settings_key_unique` (`key`)
            ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `classrooms` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(50) NOT NULL,
              `grade_id` bigint(20) unsigned NOT NULL,
              `major_id` bigint(20) unsigned DEFAULT NULL,
              `capacity` tinyint(4) NOT NULL DEFAULT 30,
              `academic_year_id` bigint(20) unsigned DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `classrooms_grade_id_foreign` (`grade_id`),
              KEY `idx_classrooms_academic_year_id` (`academic_year_id`),
              CONSTRAINT `classrooms_grade_id_foreign` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_event_teachers` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `exam_event_id` int(11) unsigned NOT NULL,
              `teacher_id` int(11) unsigned NOT NULL,
              `role` varchar(20) NOT NULL,
              `max_days` int(11) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `exam_event_id_teacher_id` (`exam_event_id`,`teacher_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=160 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_events` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `name` varchar(100) NOT NULL,
              `start_date` date NOT NULL,
              `end_date` date NOT NULL,
              `grade_levels` varchar(50) NOT NULL,
              `is_locked` tinyint(1) NOT NULL DEFAULT 0,
              `proctors_per_room` int(11) NOT NULL DEFAULT 1,
              `created_by` bigint(20) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              `pairing_plan` text DEFAULT NULL,
              `event_code` varchar(20) DEFAULT NULL,
              `proctor_rules` text DEFAULT NULL,
              `student_rules` text DEFAULT NULL,
              `chairman_name` varchar(150) DEFAULT NULL,
              `chairman_nip` varchar(50) DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_participants` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `exam_event_id` int(11) unsigned NOT NULL,
              `exam_room_id` int(11) unsigned NOT NULL,
              `student_id` int(11) unsigned NOT NULL,
              `exam_number` varchar(50) NOT NULL,
              `seat_number` int(11) NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `exam_event_id_student_id` (`exam_event_id`,`student_id`),
              UNIQUE KEY `exam_event_id_exam_number` (`exam_event_id`,`exam_number`),
              UNIQUE KEY `exam_room_id_seat_number` (`exam_room_id`,`seat_number`)
            ) ENGINE=InnoDB AUTO_INCREMENT=5897 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_room_rules` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `exam_room_id` int(11) unsigned NOT NULL,
              `classroom_id` int(11) unsigned NOT NULL,
              `start_absen` int(11) NOT NULL,
              `end_absen` int(11) NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_rooms` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `exam_event_id` int(11) unsigned NOT NULL,
              `name` varchar(50) NOT NULL,
              `capacity` int(11) NOT NULL DEFAULT 20,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_schedule_proctors` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `exam_event_id` int(11) unsigned NOT NULL,
              `exam_schedule_id` int(11) unsigned NOT NULL,
              `subject_name` varchar(150) NOT NULL,
              `exam_room_id` int(11) unsigned NOT NULL,
              `teacher_id_1` int(11) unsigned DEFAULT NULL,
              `teacher_id_2` int(11) unsigned DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `exam_schedule_id_subject_name_exam_room_id` (`exam_schedule_id`,`subject_name`,`exam_room_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=897 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `exam_schedules` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `exam_event_id` int(11) unsigned NOT NULL,
              `exam_date` date NOT NULL,
              `exam_day` varchar(20) NOT NULL,
              `subject_list` text NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `grades` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(20) NOT NULL,
              `order` tinyint(4) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `graduation_settings` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `academic_year_id` bigint(20) unsigned NOT NULL,
              `final_grade_id` bigint(20) unsigned DEFAULT NULL,
              `announcement_datetime` datetime DEFAULT NULL,
              `is_active` tinyint(1) NOT NULL DEFAULT 0,
              `allow_skl_download` tinyint(1) NOT NULL DEFAULT 0,
              `custom_message` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `academic_year_id` (`academic_year_id`),
              KEY `graduation_settings_ibfk_2` (`final_grade_id`),
              CONSTRAINT `graduation_settings_academic_year_id_foreign` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `graduation_settings_final_grade_id_foreign` FOREIGN KEY (`final_grade_id`) REFERENCES `grades` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
              CONSTRAINT `graduation_settings_ibfk_1` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `graduation_settings_ibfk_2` FOREIGN KEY (`final_grade_id`) REFERENCES `grades` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `homeroom_assignments` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `teacher_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `classroom_id_academic_period_id` (`classroom_id`,`academic_period_id`),
              KEY `homeroom_assignments_teacher_id_foreign` (`teacher_id`),
              KEY `homeroom_assignments_academic_period_id_foreign` (`academic_period_id`),
              CONSTRAINT `homeroom_assignments_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `homeroom_assignments_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `homeroom_assignments_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `journal_attendances` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `journal_id` bigint(20) unsigned NOT NULL,
              `student_id` bigint(20) unsigned NOT NULL,
              `status` enum(\'Sakit\',\'Izin\',\'Alpha\') NOT NULL,
              `created_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `journal_id_student_id` (`journal_id`,`student_id`),
              KEY `ja_ibfk_2` (`student_id`),
              CONSTRAINT `ja_ibfk_1` FOREIGN KEY (`journal_id`) REFERENCES `teaching_journals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `ja_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `journal_attendances_journal_id_foreign` FOREIGN KEY (`journal_id`) REFERENCES `teaching_journals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `journal_attendances_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_kktps` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `tp_id` bigint(20) unsigned NOT NULL,
              `indicators` text DEFAULT NULL,
              `rubric_bimbingan` text DEFAULT NULL,
              `rubric_cukup` text DEFAULT NULL,
              `rubric_baik` text DEFAULT NULL,
              `rubric_sangat_baik` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_kktps_learning_device_id_foreign` (`learning_device_id`),
              KEY `learning_device_kktps_tp_id_foreign` (`tp_id`),
              CONSTRAINT `learning_device_kktps_learning_device_id_foreign` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_device_kktps_tp_id_foreign` FOREIGN KEY (`tp_id`) REFERENCES `learning_device_tps` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_moduls` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `tp_id` bigint(20) unsigned DEFAULT NULL,
              `chapter_index` int(11) unsigned DEFAULT 0,
              `topic` varchar(255) NOT NULL,
              `kompetensi_awal` text DEFAULT NULL,
              `sarana_prasarana` text DEFAULT NULL,
              `target_peserta_didik` text DEFAULT NULL,
              `model_pembelajaran` text DEFAULT NULL,
              `pemahaman_bermakna` text DEFAULT NULL,
              `pertanyaan_pemantik` text DEFAULT NULL,
              `materi_esensial` text DEFAULT NULL,
              `skenario_pembelajaran` text DEFAULT NULL,
              `asesmen_diagnostik` text DEFAULT NULL,
              `asesmen_formatif` text DEFAULT NULL,
              `asesmen_sumatif` text DEFAULT NULL,
              `pengayaan` text DEFAULT NULL,
              `remedial` text DEFAULT NULL,
              `lkpd` text DEFAULT NULL,
              `glosarium` text DEFAULT NULL,
              `daftar_pustaka` text DEFAULT NULL,
              `jumlah_pertemuan` int(11) unsigned DEFAULT 0,
              `field_status` text DEFAULT NULL,
              `identity` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`identity`)),
              `core_components` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`core_components`)),
              `differentiation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`differentiation`)),
              `assessment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`assessment`)),
              `follow_up` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`follow_up`)),
              `reflection` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`reflection`)),
              `character_integration` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`character_integration`)),
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_moduls_learning_device_id_foreign` (`learning_device_id`),
              KEY `learning_device_moduls_tp_id_foreign` (`tp_id`),
              CONSTRAINT `learning_device_moduls_learning_device_id_foreign` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_device_moduls_tp_id_foreign` FOREIGN KEY (`tp_id`) REFERENCES `learning_device_tps` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_pekan_efektif` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `content_json` longtext NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_pekan_efektif_learning_device_id_foreign` (`learning_device_id`),
              CONSTRAINT `learning_device_pekan_efektif_learning_device_id_foreign` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_promes` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `tp_id` bigint(20) unsigned NOT NULL,
              `month` tinyint(2) NOT NULL,
              `week_number` tinyint(1) NOT NULL,
              `allocated_hours` int(5) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_promes_learning_device_id_foreign` (`learning_device_id`),
              KEY `learning_device_promes_tp_id_foreign` (`tp_id`),
              CONSTRAINT `learning_device_promes_learning_device_id_foreign` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_device_promes_tp_id_foreign` FOREIGN KEY (`tp_id`) REFERENCES `learning_device_tps` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_prota` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `tp_id` bigint(20) unsigned NOT NULL,
              `semester` tinyint(1) NOT NULL DEFAULT 1,
              `allocated_weeks` int(5) NOT NULL DEFAULT 0,
              `allocated_hours` int(5) NOT NULL DEFAULT 0,
              `notes` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_prota_learning_device_id_foreign` (`learning_device_id`),
              KEY `learning_device_prota_tp_id_foreign` (`tp_id`),
              CONSTRAINT `learning_device_prota_learning_device_id_foreign` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_device_prota_tp_id_foreign` FOREIGN KEY (`tp_id`) REFERENCES `learning_device_tps` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_rpe` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `content_json` longtext DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_id` (`learning_device_id`),
              CONSTRAINT `learning_device_rpe_ibfk_1` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_device_tps` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `learning_device_id` bigint(20) unsigned NOT NULL,
              `code` varchar(20) NOT NULL,
              `description` text NOT NULL,
              `allocated_hours` int(5) NOT NULL DEFAULT 0,
              `semester` tinyint(1) NOT NULL DEFAULT 1,
              `sort_order` int(5) NOT NULL DEFAULT 0,
              `chapter` varchar(255) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_device_tps_learning_device_id_foreign` (`learning_device_id`),
              CONSTRAINT `learning_device_tps_learning_device_id_foreign` FOREIGN KEY (`learning_device_id`) REFERENCES `learning_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `learning_devices` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `user_id` bigint(20) unsigned NOT NULL,
              `academic_year_id` bigint(20) unsigned NOT NULL,
              `grade_id` bigint(20) unsigned NOT NULL,
              `school_subject_id` bigint(20) unsigned NOT NULL,
              `phase` varchar(10) NOT NULL,
              `school_type` enum(\'umum\',\'madrasah\') NOT NULL DEFAULT \'umum\',
              `cp_content` text NOT NULL,
              `cp_elements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`cp_elements`)),
              `chapters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`chapters`)),
              `dpl` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`dpl`)),
              `panca_cinta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`panca_cinta`)),
              `chapter_dpl` text DEFAULT NULL,
              `chapter_kbc` text DEFAULT NULL,
              `chapter_cp_map` text DEFAULT NULL,
              `kktp_intervals` varchar(255) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `learning_devices_user_id_foreign` (`user_id`),
              KEY `learning_devices_academic_year_id_foreign` (`academic_year_id`),
              KEY `learning_devices_grade_id_foreign` (`grade_id`),
              KEY `learning_devices_school_subject_id_foreign` (`school_subject_id`),
              CONSTRAINT `learning_devices_academic_year_id_foreign` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_devices_grade_id_foreign` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_devices_school_subject_id_foreign` FOREIGN KEY (`school_subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `learning_devices_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `leger_imports` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `batch_code` varchar(50) NOT NULL,
              `classroom_id` bigint(20) unsigned DEFAULT NULL,
              `academic_period_id` bigint(20) unsigned DEFAULT NULL,
              `file_name` varchar(255) NOT NULL,
              `class_name_raw` varchar(100) DEFAULT NULL,
              `semester_raw` varchar(50) DEFAULT NULL,
              `year_raw` varchar(20) DEFAULT NULL,
              `total_students` int(11) NOT NULL DEFAULT 0,
              `total_subjects` int(11) NOT NULL DEFAULT 0,
              `imported_by` bigint(20) unsigned DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `leger_imports_classroom_id_foreign` (`classroom_id`),
              KEY `leger_imports_academic_period_id_foreign` (`academic_period_id`),
              KEY `leger_imports_imported_by_foreign` (`imported_by`),
              CONSTRAINT `leger_imports_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
              CONSTRAINT `leger_imports_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
              CONSTRAINT `leger_imports_imported_by_foreign` FOREIGN KEY (`imported_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `letter_logs` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `letter_type` enum(\'keterangan_aktif\',\'mutasi_keluar\',\'surat_tugas\',\'sppd\') NOT NULL,
              `letter_number` varchar(100) NOT NULL,
              `student_id` int(11) unsigned DEFAULT NULL,
              `student_name` varchar(100) DEFAULT NULL,
              `student_nis` varchar(20) DEFAULT NULL,
              `letter_date` date NOT NULL,
              `purpose` text DEFAULT NULL,
              `destination` varchar(200) DEFAULT NULL,
              `reason` text DEFAULT NULL,
              `officer_name` varchar(200) DEFAULT NULL,
              `officer_nip` varchar(30) DEFAULT NULL,
              `officer_rank` varchar(100) DEFAULT NULL,
              `officer_position` varchar(200) DEFAULT NULL,
              `task_description` text DEFAULT NULL COMMENT \'Isi/uraian tugas (surat_tugas)\',
              `origin` varchar(200) DEFAULT NULL COMMENT \'Tempat berangkat (SPPD)\',
              `transport` varchar(100) DEFAULT NULL COMMENT \'Alat angkut (SPPD)\',
              `date_start` date DEFAULT NULL COMMENT \'Tanggal berangkat (SPPD)\',
              `date_end` date DEFAULT NULL COMMENT \'Tanggal kembali (SPPD)\',
              `budget_source` varchar(200) DEFAULT NULL COMMENT \'Pembebanan anggaran (SPPD)\',
              `is_reference` tinyint(1) NOT NULL DEFAULT 0 COMMENT \'1 = Surat Tugas Menindaklanjuti, 0 = Surat Tugas Mandiri\',
              `reference_officer` varchar(200) DEFAULT NULL COMMENT \'Jabatan pejabat pengirim surat rujukan\',
              `reference_number` varchar(100) DEFAULT NULL COMMENT \'Nomor surat rujukan\',
              `reference_date` date DEFAULT NULL COMMENT \'Tanggal surat rujukan\',
              `reference_theme` varchar(300) DEFAULT NULL COMMENT \'Hal/tentang surat rujukan\',
              `printed_by` int(11) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `letter_teachers` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `letter_log_id` int(11) unsigned NOT NULL COMMENT \'FK ke letter_logs.id\',
              `teacher_id` bigint(20) unsigned DEFAULT NULL COMMENT \'FK ke teachers.id; NULL jika guru tidak ada di master\',
              `teacher_name` varchar(100) NOT NULL COMMENT \'Nama snapshot saat surat dibuat\',
              `teacher_nip` varchar(30) DEFAULT NULL,
              `teacher_golongan` varchar(20) DEFAULT NULL COMMENT \'Pangkat/Gol saat surat dibuat\',
              `teacher_jabatan` varchar(100) DEFAULT NULL COMMENT \'Jabatan diisi manual di form\',
              `sort_order` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT \'Urutan guru dalam surat (untuk multi-guru)\',
              PRIMARY KEY (`id`),
              KEY `letter_log_id` (`letter_log_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `majors` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(50) NOT NULL,
              `description` varchar(100) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `qr_scan_logs` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `student_id` int(11) NOT NULL,
              `date` date NOT NULL,
              `scan_time` time NOT NULL,
              `source_ip` varchar(45) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `student_id_date` (`student_id`,`date`),
              KEY `student_id` (`student_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `schedule_availability` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `teacher_id` int(10) unsigned NOT NULL,
              `day` tinyint(3) unsigned NOT NULL COMMENT \'1=Senin...6=Sabtu\',
              `slot_number` tinyint(3) unsigned NOT NULL COMMENT \'0 = kosongkan seluruh hari, >0 = slot jam tertentu\',
              `reason` varchar(100) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `teacher_id_day_slot_number` (`teacher_id`,`day`,`slot_number`)
            ) ENGINE=InnoDB AUTO_INCREMENT=187 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `schedule_entries` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `schedule_id` int(10) unsigned NOT NULL COMMENT \'FK ke schedules\',
              `schedule_load_id` int(10) unsigned NOT NULL COMMENT \'FK ke schedule_loads\',
              `teacher_id` int(10) unsigned NOT NULL,
              `classroom_id` int(10) unsigned NOT NULL,
              `school_subject_id` int(10) unsigned NOT NULL,
              `day` tinyint(3) unsigned NOT NULL COMMENT \'1=Senin, 2=Selasa, ..., 6=Sabtu\',
              `slot_number` tinyint(3) unsigned NOT NULL COMMENT \'Urutan jam ke-\',
              `timeslot_id` int(10) unsigned NOT NULL COMMENT \'FK ke schedule_timeslots\',
              `is_manual` tinyint(1) NOT NULL DEFAULT 0 COMMENT \'0=hasil generate, 1=diubah manual\',
              `is_locked` tinyint(1) DEFAULT 0 COMMENT \'1=dikunci, tidak akan ditimpa saat generate ulang\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `schedule_id_teacher_id_day_slot_number` (`schedule_id`,`teacher_id`,`day`,`slot_number`),
              KEY `schedule_id_classroom_id_day_slot_number` (`schedule_id`,`classroom_id`,`day`,`slot_number`),
              KEY `schedule_id_classroom_id_day` (`schedule_id`,`classroom_id`,`day`),
              KEY `schedule_id_teacher_id_day` (`schedule_id`,`teacher_id`,`day`),
              KEY `idx_locked` (`schedule_id`,`is_locked`)
            ) ENGINE=InnoDB AUTO_INCREMENT=267143 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `schedule_loads` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `schedule_id` int(10) unsigned DEFAULT NULL COMMENT \'FK ke tabel schedules (header jadwal), nullable dulu\',
              `academic_year_id` int(10) unsigned NOT NULL,
              `academic_period_id` int(10) unsigned NOT NULL,
              `teacher_id` int(10) unsigned NOT NULL,
              `school_subject_id` int(10) unsigned NOT NULL COMMENT \'FK ke school_subjects (mapel sekolah)\',
              `classroom_id` int(10) unsigned NOT NULL,
              `hours_per_week` tinyint(3) unsigned NOT NULL DEFAULT 2 COMMENT \'Jumlah jam tatap muka per minggu\',
              `split_config` varchar(50) DEFAULT \'\' COMMENT \'Pecahan blok, contoh: "2,2" untuk 4 jam atau "3,2" untuk 5 jam\',
              `split_order` tinyint(1) DEFAULT 0 COMMENT \'0=urut normal, 1=balik (blok besar dulu)\',
              `notes` varchar(200) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `academic_year_id_academic_period_id` (`academic_year_id`,`academic_period_id`),
              KEY `teacher_id_classroom_id` (`teacher_id`,`classroom_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1030 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `schedule_teacher_settings` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `teacher_id` int(10) unsigned NOT NULL,
              `max_hours_per_day` tinyint(3) unsigned DEFAULT NULL COMMENT \'NULL = pakai global default (6 jam)\',
              `notes` varchar(200) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `teacher_id` (`teacher_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `schedule_timeslots` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `day` tinyint(3) unsigned NOT NULL COMMENT \'1=Senin,2=Selasa,...,6=Sabtu\',
              `slot_number` tinyint(3) unsigned NOT NULL COMMENT \'Urutan jam ke-\',
              `label` varchar(30) NOT NULL COMMENT \'misal: Jam 1, Istirahat\',
              `start_time` time NOT NULL,
              `end_time` time NOT NULL,
              `slot_type` enum(\'kbm\',\'istirahat\',\'upacara\',\'keagamaan\',\'lainnya\') DEFAULT \'kbm\',
              `is_active` tinyint(1) NOT NULL DEFAULT 1,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `day_slot_number` (`day`,`slot_number`)
            ) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `schedules` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `academic_year_id` int(10) unsigned NOT NULL,
              `academic_period_id` int(10) unsigned NOT NULL,
              `name` varchar(100) NOT NULL,
              `active_days` varchar(20) NOT NULL DEFAULT \'1,2,3,4,5\' COMMENT \'CSV hari aktif: 1=Senin dst\',
              `status` enum(\'draft\',\'published\') NOT NULL DEFAULT \'draft\',
              `notes` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `academic_year_id_academic_period_id` (`academic_year_id`,`academic_period_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `school_settings` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `school_name` varchar(200) NOT NULL,
              `npsn` varchar(20) DEFAULT NULL,
              `nss` varchar(20) DEFAULT NULL,
              `school_type` varchar(10) NOT NULL DEFAULT \'MTs\',
              `address` text DEFAULT NULL,
              `village` varchar(100) DEFAULT NULL,
              `district` varchar(100) DEFAULT NULL,
              `city` varchar(100) DEFAULT NULL,
              `province` varchar(100) DEFAULT NULL,
              `postal_code` varchar(10) DEFAULT NULL,
              `phone` varchar(20) DEFAULT NULL,
              `email` varchar(100) DEFAULT NULL,
              `website` varchar(100) DEFAULT NULL,
              `principal_name` varchar(100) DEFAULT NULL,
              `principal_nip` varchar(30) DEFAULT NULL,
              `logo` varchar(255) DEFAULT NULL,
              `logo_kemenag` varchar(255) DEFAULT NULL,
              `logo_sekolah` varchar(255) DEFAULT NULL,
              `letter_kemenag_office` varchar(200) DEFAULT NULL,
              `letter_school_name` varchar(200) DEFAULT NULL,
              `letter_school_code` varchar(50) DEFAULT NULL,
              `letter_city` varchar(100) DEFAULT NULL,
              `letter_origin` varchar(200) DEFAULT NULL COMMENT \'Tempat berangkat default untuk SPPD, contoh: MTs Negeri 1 Wonogiri\',
              `letter_budget_code` varchar(100) DEFAULT NULL COMMENT \'Kode MAK/anggaran default untuk SPPD\',
              `accreditation` enum(\'A\',\'B\',\'C\',\'Belum\') NOT NULL DEFAULT \'Belum\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `proctor_rules` text DEFAULT NULL,
              `student_rules` text DEFAULT NULL,
              `ai_provider` varchar(50) DEFAULT \'gemini\',
              `ai_api_key` text DEFAULT NULL,
              `student_portal_editable_fields` text DEFAULT NULL,
              `student_portal_announcement` text DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `school_subjects` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `subject_id` bigint(20) unsigned DEFAULT NULL,
              `name` varchar(100) NOT NULL,
              `code` varchar(20) DEFAULT NULL,
              `is_active` tinyint(1) NOT NULL DEFAULT 1,
              `sort_order` int(11) DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `school_subjects_subject_id_foreign` (`subject_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `settings` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `setting_key` varchar(100) NOT NULL,
              `setting_value` text DEFAULT NULL,
              `setting_group` varchar(50) NOT NULL DEFAULT \'general\',
              `description` varchar(255) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `setting_key` (`setting_key`)
            ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `student_academic_histories` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `student_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `status` enum(\'Aktif\',\'Naik Kelas\',\'Tinggal Kelas\',\'Lulus\',\'Mutasi\',\'Drop Out\') NOT NULL DEFAULT \'Aktif\',
              `notes` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `student_academic_histories_student_id_foreign` (`student_id`),
              KEY `student_academic_histories_academic_period_id_foreign` (`academic_period_id`),
              KEY `student_academic_histories_classroom_id_foreign` (`classroom_id`),
              CONSTRAINT `student_academic_histories_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_academic_histories_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_academic_histories_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=823 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `student_card_settings` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `front_background` varchar(255) DEFAULT NULL,
              `back_background` varchar(255) DEFAULT NULL,
              `school_stamp` varchar(255) DEFAULT NULL,
              `headmaster_signature` varchar(255) DEFAULT NULL,
              `card_validity` varchar(100) NOT NULL DEFAULT \'Berlaku Selama Menjadi Siswa\',
              `show_qrcode` tinyint(1) NOT NULL DEFAULT 1,
              `card_date` date DEFAULT NULL,
              `motto` varchar(200) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `student_grades` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `student_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `school_subject_id` bigint(20) unsigned NOT NULL,
              `score` decimal(5,2) DEFAULT NULL,
              `import_batch` varchar(50) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `student_id_academic_period_id_school_subject_id` (`student_id`,`academic_period_id`,`school_subject_id`),
              KEY `student_grades_classroom_id_foreign` (`classroom_id`),
              KEY `student_grades_academic_period_id_foreign` (`academic_period_id`),
              KEY `student_grades_school_subject_id_foreign` (`school_subject_id`),
              CONSTRAINT `student_grades_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_grades_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_grades_school_subject_id_foreign` FOREIGN KEY (`school_subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_grades_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=7793 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `student_graduations` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `student_id` bigint(20) unsigned NOT NULL,
              `academic_year_id` bigint(20) unsigned NOT NULL,
              `kelulusan_status` enum(\'LULUS\',\'TIDAK_LULUS\',\'BELUM_DITENTUKAN\') NOT NULL DEFAULT \'BELUM_DITENTUKAN\',
              `decided_at` date DEFAULT NULL,
              `decided_by` bigint(20) unsigned DEFAULT NULL,
              `skl_file_path` varchar(500) DEFAULT NULL,
              `skl_uploaded_at` datetime DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `student_graduations_student_year_unique` (`student_id`,`academic_year_id`),
              KEY `student_graduations_ibfk_2` (`academic_year_id`),
              CONSTRAINT `student_graduations_academic_year_id_foreign` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_graduations_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_graduations_ibfk_2` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_graduations_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=277 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `student_violations` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `student_id` bigint(20) unsigned NOT NULL,
              `rule_id` int(10) unsigned NOT NULL,
              `date` date NOT NULL,
              `notes` text DEFAULT NULL,
              `recorded_by` bigint(20) unsigned DEFAULT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `student_violations_student_id_foreign` (`student_id`),
              KEY `student_violations_rule_id_foreign` (`rule_id`),
              KEY `student_violations_recorded_by_foreign` (`recorded_by`),
              KEY `student_violations_academic_period_id_foreign` (`academic_period_id`),
              CONSTRAINT `student_violations_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_violations_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
              CONSTRAINT `student_violations_rule_id_foreign` FOREIGN KEY (`rule_id`) REFERENCES `violation_rules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `student_violations_student_id_foreign` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `students` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `nis` varchar(20) NOT NULL,
              `nisn` varchar(20) NOT NULL,
              `nik` varchar(20) DEFAULT NULL,
              `family_card_no` varchar(30) DEFAULT NULL,
              `family_head_name` varchar(255) DEFAULT NULL,
              `kip_no` varchar(50) DEFAULT NULL,
              `full_name` varchar(100) NOT NULL,
              `gender` enum(\'L\',\'P\') NOT NULL,
              `birth_place` varchar(100) DEFAULT NULL,
              `birth_date` date DEFAULT NULL,
              `religion` enum(\'Islam\',\'Kristen\',\'Katolik\',\'Hindu\',\'Buddha\',\'Konghucu\') NOT NULL DEFAULT \'Islam\',
              `photo` varchar(255) DEFAULT NULL,
              `entry_year` year(4) DEFAULT NULL,
              `entry_date` date DEFAULT NULL,
              `origin_school` varchar(150) DEFAULT NULL,
              `graduation_certificate_no` varchar(100) DEFAULT NULL,
              `status` enum(\'Aktif\',\'Alumni\',\'Mutasi\',\'Drop Out\') NOT NULL DEFAULT \'Aktif\',
              `exit_date` date DEFAULT NULL,
              `exit_to` varchar(150) DEFAULT NULL,
              `exit_reason` text DEFAULT NULL,
              `graduated_academic_year_id` bigint(20) unsigned DEFAULT NULL,
              `address` text DEFAULT NULL,
              `rt` varchar(5) DEFAULT NULL,
              `rw` varchar(5) DEFAULT NULL,
              `village` varchar(100) DEFAULT NULL,
              `district` varchar(100) DEFAULT NULL,
              `city` varchar(100) DEFAULT NULL,
              `province` varchar(100) DEFAULT NULL,
              `postal_code` varchar(10) DEFAULT NULL,
              `phone` varchar(20) DEFAULT NULL,
              `father_name` varchar(100) DEFAULT NULL,
              `father_nik` varchar(20) DEFAULT NULL,
              `father_birth_place` varchar(100) DEFAULT NULL,
              `father_birth` date DEFAULT NULL,
              `father_edu` varchar(20) DEFAULT NULL,
              `father_job` varchar(50) DEFAULT NULL,
              `father_phone` varchar(20) DEFAULT NULL,
              `mother_name` varchar(100) DEFAULT NULL,
              `mother_nik` varchar(20) DEFAULT NULL,
              `mother_birth_place` varchar(100) DEFAULT NULL,
              `mother_birth` date DEFAULT NULL,
              `mother_edu` varchar(20) DEFAULT NULL,
              `mother_job` varchar(50) DEFAULT NULL,
              `mother_phone` varchar(20) DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `nis` (`nis`),
              UNIQUE KEY `nisn` (`nisn`),
              UNIQUE KEY `students_nisn_unique` (`nisn`),
              KEY `students_graduated_academic_year_id_foreign` (`graduated_academic_year_id`),
              CONSTRAINT `students_graduated_academic_year_id_foreign` FOREIGN KEY (`graduated_academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=823 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `subject_target_hours` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `subject_id` int(10) unsigned NOT NULL COMMENT \'ID mata pelajaran (school_subjects)\',
              `grade_id` int(10) unsigned NOT NULL COMMENT \'ID tingkat (grades)\',
              `target_hours` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT \'Target jam per minggu untuk mapel ini di tingkat ini\',
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `subject_id_grade_id` (`subject_id`,`grade_id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `subjects` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `code` varchar(20) DEFAULT NULL,
              `name` varchar(100) NOT NULL,
              `level` enum(\'MI\',\'MTs\',\'MA\',\'ALL\') DEFAULT \'ALL\',
              `phase` varchar(10) DEFAULT NULL,
              `group` varchar(50) DEFAULT NULL,
              `is_core` tinyint(1) DEFAULT 1,
              `description` text DEFAULT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `teachers` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `user_id` bigint(20) unsigned DEFAULT NULL,
              `gemini_api_key` varchar(255) DEFAULT NULL,
              `nip` varchar(30) DEFAULT NULL,
              `golongan` varchar(20) DEFAULT NULL COMMENT \'Pangkat/Golongan, contoh: Penata Muda/III-a, Pembina/IV-a\',
              `jabatan` varchar(100) DEFAULT NULL COMMENT \'Jabatan, contoh: Pendidik/Guru, Staff TU/Tenaga Kependidikan\',
              `status_kepegawaian` varchar(50) DEFAULT NULL COMMENT \'Status Kepegawaian: PNS, PPPK, Honorer\',
              `is_sertifikasi` tinyint(1) NOT NULL DEFAULT 0 COMMENT \'Status Sertifikasi: 1 = Sudah, 0 = Belum\',
              `short_code` varchar(20) DEFAULT NULL COMMENT \'Kode singkat guru, misal: 01, 32 — sesuai kode di aSc Timetables\',
              `name` varchar(100) NOT NULL,
              `gender` enum(\'L\',\'P\') NOT NULL,
              `phone` varchar(20) DEFAULT NULL,
              `email` varchar(100) DEFAULT NULL,
              `is_active` tinyint(1) NOT NULL DEFAULT 1,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `teachers_user_id_foreign` (`user_id`),
              KEY `idx_teacher_short_code` (`short_code`),
              CONSTRAINT `teachers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `teaching_assignments` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `teacher_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `subject_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `classroom_id_subject_id_academic_period_id` (`classroom_id`,`subject_id`,`academic_period_id`),
              KEY `teaching_assignments_teacher_id_foreign` (`teacher_id`),
              KEY `teaching_assignments_subject_id_foreign` (`subject_id`),
              KEY `teaching_assignments_academic_period_id_foreign` (`academic_period_id`),
              CONSTRAINT `teaching_assignments_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_assignments_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_assignments_subject_id_foreign` FOREIGN KEY (`subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_assignments_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `teaching_journals` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `teacher_id` bigint(20) unsigned NOT NULL,
              `academic_period_id` bigint(20) unsigned NOT NULL,
              `classroom_id` bigint(20) unsigned NOT NULL,
              `school_subject_id` bigint(20) unsigned NOT NULL,
              `meeting_date` date NOT NULL,
              `start_period` tinyint(3) unsigned NOT NULL,
              `end_period` tinyint(3) unsigned NOT NULL,
              `meeting_number` int(10) unsigned NOT NULL DEFAULT 0,
              `topic` text DEFAULT NULL,
              `tp_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tp_ids`)),
              `notes` text DEFAULT NULL,
              `total_students` tinyint(3) unsigned NOT NULL DEFAULT 0,
              `total_present` tinyint(3) unsigned NOT NULL DEFAULT 0,
              `total_sick` tinyint(3) unsigned NOT NULL DEFAULT 0,
              `total_permit` tinyint(3) unsigned NOT NULL DEFAULT 0,
              `total_absent` tinyint(3) unsigned NOT NULL DEFAULT 0,
              `is_final` tinyint(1) NOT NULL DEFAULT 0,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `meeting_date` (`meeting_date`),
              KEY `teacher_id_academic_period_id_classroom_id_school_subject_id` (`teacher_id`,`academic_period_id`,`classroom_id`,`school_subject_id`),
              KEY `teaching_journals_ibfk_2` (`academic_period_id`),
              KEY `teaching_journals_ibfk_3` (`classroom_id`),
              KEY `teaching_journals_ibfk_4` (`school_subject_id`),
              CONSTRAINT `teaching_journals_academic_period_id_foreign` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_classroom_id_foreign` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_ibfk_2` FOREIGN KEY (`academic_period_id`) REFERENCES `academic_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_ibfk_3` FOREIGN KEY (`classroom_id`) REFERENCES `classrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_ibfk_4` FOREIGN KEY (`school_subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_school_subject_id_foreign` FOREIGN KEY (`school_subject_id`) REFERENCES `school_subjects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `teaching_journals_teacher_id_foreign` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `users` (
              `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              `username` varchar(100) NOT NULL,
              `name` varchar(100) NOT NULL,
              `email` varchar(100) DEFAULT NULL,
              `password` varchar(255) NOT NULL,
              `role` enum(\'superadmin\',\'admin\',\'operator\',\'guru\',\'tata_usaha\',\'siswa\') NOT NULL DEFAULT \'operator\',
              `student_id` bigint(20) unsigned DEFAULT NULL,
              `is_password_reset` tinyint(1) DEFAULT 0,
              `is_active` tinyint(1) NOT NULL DEFAULT 1,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              `deleted_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `username` (`username`),
              UNIQUE KEY `email` (`email`),
              KEY `fk_users_student_id` (`student_id`),
              CONSTRAINT `fk_users_student_id` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `violation_categories` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(100) NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `violation_rules` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `category_id` int(10) unsigned NOT NULL,
              `code` varchar(20) NOT NULL,
              `description` varchar(255) NOT NULL,
              `severity` enum(\'Ringan\',\'Sedang\',\'Berat\',\'Khusus\') NOT NULL DEFAULT \'Ringan\',
              `points` int(11) NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `violation_rules_category_id_foreign` (`category_id`),
              CONSTRAINT `violation_rules_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `violation_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('
            CREATE TABLE IF NOT EXISTS `violation_sanctions` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `min_points` int(11) NOT NULL,
              `action_title` varchar(150) NOT NULL,
              `description` text NOT NULL,
              `created_at` datetime DEFAULT NULL,
              `updated_at` datetime DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');

        $this->db->query('SET FOREIGN_KEY_CHECKS = 1;');
    }

    public function down()
    {
        $this->db->query('SET FOREIGN_KEY_CHECKS = 0;');

        $this->db->query('DROP TABLE IF EXISTS `academic_periods`;');
        $this->db->query('DROP TABLE IF EXISTS `academic_years`;');
        $this->db->query('DROP TABLE IF EXISTS `activity_logs`;');
        $this->db->query('DROP TABLE IF EXISTS `ai_kb_cp`;');
        $this->db->query('DROP TABLE IF EXISTS `ai_kb_tp`;');
        $this->db->query('DROP TABLE IF EXISTS `asc_classes`;');
        $this->db->query('DROP TABLE IF EXISTS `asc_imports`;');
        $this->db->query('DROP TABLE IF EXISTS `asc_periods`;');
        $this->db->query('DROP TABLE IF EXISTS `asc_schedules`;');
        $this->db->query('DROP TABLE IF EXISTS `asc_subjects`;');
        $this->db->query('DROP TABLE IF EXISTS `asc_teachers`;');
        $this->db->query('DROP TABLE IF EXISTS `assessment_class_settings`;');
        $this->db->query('DROP TABLE IF EXISTS `assessment_detail_scores`;');
        $this->db->query('DROP TABLE IF EXISTS `assessment_scores`;');
        $this->db->query('DROP TABLE IF EXISTS `assessment_types`;');
        $this->db->query('DROP TABLE IF EXISTS `assessments`;');
        $this->db->query('DROP TABLE IF EXISTS `attendances`;');
        $this->db->query('DROP TABLE IF EXISTS `bk_assignments`;');
        $this->db->query('DROP TABLE IF EXISTS `calendar_categories`;');
        $this->db->query('DROP TABLE IF EXISTS `calendar_events`;');
        $this->db->query('DROP TABLE IF EXISTS `calendar_settings`;');
        $this->db->query('DROP TABLE IF EXISTS `classrooms`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_event_teachers`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_events`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_participants`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_room_rules`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_rooms`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_schedule_proctors`;');
        $this->db->query('DROP TABLE IF EXISTS `exam_schedules`;');
        $this->db->query('DROP TABLE IF EXISTS `grades`;');
        $this->db->query('DROP TABLE IF EXISTS `graduation_settings`;');
        $this->db->query('DROP TABLE IF EXISTS `homeroom_assignments`;');
        $this->db->query('DROP TABLE IF EXISTS `journal_attendances`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_kktps`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_moduls`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_pekan_efektif`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_promes`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_prota`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_rpe`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_device_tps`;');
        $this->db->query('DROP TABLE IF EXISTS `learning_devices`;');
        $this->db->query('DROP TABLE IF EXISTS `leger_imports`;');
        $this->db->query('DROP TABLE IF EXISTS `letter_logs`;');
        $this->db->query('DROP TABLE IF EXISTS `letter_teachers`;');
        $this->db->query('DROP TABLE IF EXISTS `majors`;');
        $this->db->query('DROP TABLE IF EXISTS `qr_scan_logs`;');
        $this->db->query('DROP TABLE IF EXISTS `schedule_availability`;');
        $this->db->query('DROP TABLE IF EXISTS `schedule_entries`;');
        $this->db->query('DROP TABLE IF EXISTS `schedule_loads`;');
        $this->db->query('DROP TABLE IF EXISTS `schedule_teacher_settings`;');
        $this->db->query('DROP TABLE IF EXISTS `schedule_timeslots`;');
        $this->db->query('DROP TABLE IF EXISTS `schedules`;');
        $this->db->query('DROP TABLE IF EXISTS `school_settings`;');
        $this->db->query('DROP TABLE IF EXISTS `school_subjects`;');
        $this->db->query('DROP TABLE IF EXISTS `settings`;');
        $this->db->query('DROP TABLE IF EXISTS `student_academic_histories`;');
        $this->db->query('DROP TABLE IF EXISTS `student_card_settings`;');
        $this->db->query('DROP TABLE IF EXISTS `student_grades`;');
        $this->db->query('DROP TABLE IF EXISTS `student_graduations`;');
        $this->db->query('DROP TABLE IF EXISTS `student_violations`;');
        $this->db->query('DROP TABLE IF EXISTS `students`;');
        $this->db->query('DROP TABLE IF EXISTS `subject_target_hours`;');
        $this->db->query('DROP TABLE IF EXISTS `subjects`;');
        $this->db->query('DROP TABLE IF EXISTS `teachers`;');
        $this->db->query('DROP TABLE IF EXISTS `teaching_assignments`;');
        $this->db->query('DROP TABLE IF EXISTS `teaching_journals`;');
        $this->db->query('DROP TABLE IF EXISTS `users`;');
        $this->db->query('DROP TABLE IF EXISTS `violation_categories`;');
        $this->db->query('DROP TABLE IF EXISTS `violation_rules`;');
        $this->db->query('DROP TABLE IF EXISTS `violation_sanctions`;');

        $this->db->query('SET FOREIGN_KEY_CHECKS = 1;');
    }
}
