<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class FixMigrationErrors extends Migration
{
    public function up()
    {
        $db = db_connect();

        // ── HAPUS DULU RECORD MIGRASI YANG GAGAL ─────────────
        $classes = [
            '%AddMissingLearningDeviceColumns%',
            '%EnsureGraduationTablesExist%',
            '%CreateTeachingJournalsTable%',
            '%EnsureTeachingJournalsTable%',
            '%CreateAiKbTpTable%',
            '%CreateStudentGraduationsTable%',
            '%AddAllowSklDownload%',
            '%ReaddTpIdForeignKey%',
        ];
        foreach ($classes as $class) {
            $db->query("DELETE FROM migrations WHERE class LIKE ?", [$class]);
        }

        // ── 1. PASTIKAN KOLOM DI learning_devices (safe) ────────
        $columns = ['chapters', 'dpl', 'panca_cinta', 'kktp_intervals'];
        foreach ($columns as $col) {
            if (!$db->fieldExists($col, 'learning_devices')) {
                $type = ($col === 'kktp_intervals') ? 'VARCHAR(255)' : 'LONGTEXT';
                $db->query("ALTER TABLE learning_devices ADD COLUMN `{$col}` {$type} NULL AFTER `cp_elements`");
            }
        }

        // ── 2. PASTIKAN TABEL graduation_settings ──────────────
        if (!$db->tableExists('graduation_settings')) {
            $db->query("CREATE TABLE IF NOT EXISTS `graduation_settings` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `academic_year_id` BIGINT UNSIGNED NOT NULL,
                `final_grade_id` BIGINT 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 `final_grade_id` (`final_grade_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        }

        // ── 3. PASTIKAN TABEL student_graduations ──────────────
        if (!$db->tableExists('student_graduations')) {
            $db->query("CREATE TABLE IF NOT EXISTS `student_graduations` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `student_id` BIGINT UNSIGNED NOT NULL,
                `academic_year_id` BIGINT UNSIGNED NOT NULL,
                `kelulusan_status` ENUM('LULUS','TIDAK_LULUS','BELUM_DITENTUKAN') NOT NULL DEFAULT 'BELUM_DITENTUKAN',
                `decided_at` DATE DEFAULT NULL,
                `decided_by` BIGINT 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 `academic_year_id` (`academic_year_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        }

        // ── 4. PASTIKAN TABEL ai_kb_tp ─────────────────────────
        if (!$db->tableExists('ai_kb_tp')) {
            $db->query("CREATE TABLE IF NOT EXISTS `ai_kb_tp` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `academic_year_id` INT UNSIGNED NOT NULL,
                `user_id` INT UNSIGNED NOT NULL,
                `subject_id` VARCHAR(50) DEFAULT NULL,
                `subject_name` VARCHAR(150) NOT NULL,
                `grade_id` INT UNSIGNED NOT NULL,
                `grade_name` VARCHAR(50) NOT NULL,
                `chapters` LONGTEXT DEFAULT NULL,
                `dpl` LONGTEXT DEFAULT NULL,
                `panca_cinta` LONGTEXT DEFAULT NULL,
                `tps` LONGTEXT DEFAULT NULL,
                `created_at` DATETIME DEFAULT NULL,
                `updated_at` DATETIME DEFAULT NULL,
                `deleted_at` DATETIME DEFAULT NULL,
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        }

        // ── 5. PASTIKAN TABEL teaching_journals ────────────────
        if (!$db->tableExists('teaching_journals')) {
            $db->query("CREATE TABLE IF NOT EXISTS `teaching_journals` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `teacher_id` BIGINT UNSIGNED NOT NULL,
                `academic_period_id` BIGINT UNSIGNED NOT NULL,
                `classroom_id` BIGINT UNSIGNED NOT NULL,
                `school_subject_id` BIGINT UNSIGNED NOT NULL,
                `meeting_date` DATE NOT NULL,
                `start_period` TINYINT UNSIGNED NOT NULL,
                `end_period` TINYINT UNSIGNED NOT NULL,
                `meeting_number` INT UNSIGNED NOT NULL DEFAULT '0',
                `topic` TEXT DEFAULT NULL,
                `tp_ids` JSON DEFAULT NULL,
                `notes` TEXT DEFAULT NULL,
                `total_students` TINYINT UNSIGNED NOT NULL DEFAULT '0',
                `total_present` TINYINT UNSIGNED NOT NULL DEFAULT '0',
                `total_sick` TINYINT UNSIGNED NOT NULL DEFAULT '0',
                `total_permit` TINYINT UNSIGNED NOT NULL DEFAULT '0',
                `total_absent` TINYINT 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 `teacher_id` (`teacher_id`),
                KEY `academic_period_id` (`academic_period_id`),
                KEY `classroom_id` (`classroom_id`),
                KEY `school_subject_id` (`school_subject_id`),
                KEY `meeting_date` (`meeting_date`),
                KEY `teacher_period_class_subject` (`teacher_id`,`academic_period_id`,`classroom_id`,`school_subject_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        }

        // ── 6. PASTIKAN TABEL journal_attendances ──────────────
        if (!$db->tableExists('journal_attendances')) {
            $db->query("CREATE TABLE IF NOT EXISTS `journal_attendances` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `journal_id` BIGINT UNSIGNED NOT NULL,
                `student_id` BIGINT UNSIGNED NOT NULL,
                `status` ENUM('Sakit','Izin','Alpha') NOT NULL,
                `created_at` DATETIME DEFAULT NULL,
                PRIMARY KEY (`id`),
                UNIQUE KEY `journal_student` (`journal_id`,`student_id`),
                KEY `student_id` (`student_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        }

        // ── 7. TAMBAH KOLOM allow_skl_download JIKA BELUM ADA ──
        if (!$db->fieldExists('allow_skl_download', 'graduation_settings')) {
            $db->query("ALTER TABLE graduation_settings ADD COLUMN `allow_skl_download` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_active`");
        }

        // ── 8. FK di graduation_settings ────────────────────────
        $this->safeAddFK('graduation_settings', 'graduation_settings_ibfk_1', 'academic_year_id', 'academic_years', 'id');
        $this->safeAddFK('graduation_settings', 'graduation_settings_ibfk_2', 'final_grade_id', 'grades', 'id');

        // ── 9. FK di student_graduations ────────────────────────
        $this->safeAddFK('student_graduations', 'student_graduations_ibfk_1', 'student_id', 'students', 'id');
        $this->safeAddFK('student_graduations', 'student_graduations_ibfk_2', 'academic_year_id', 'academic_years', 'id');

        // ── 10. FK di teaching_journals ─────────────────────────
        $this->safeAddFK('teaching_journals', 'teaching_journals_ibfk_1', 'teacher_id', 'teachers', 'id');
        $this->safeAddFK('teaching_journals', 'teaching_journals_ibfk_2', 'academic_period_id', 'academic_periods', 'id');
        $this->safeAddFK('teaching_journals', 'teaching_journals_ibfk_3', 'classroom_id', 'classrooms', 'id');
        $this->safeAddFK('teaching_journals', 'teaching_journals_ibfk_4', 'school_subject_id', 'school_subjects', 'id');

        // ── 11. FK di journal_attendances ───────────────────────
        $this->safeAddFK('journal_attendances', 'ja_ibfk_1', 'journal_id', 'teaching_journals', 'id');
        $this->safeAddFK('journal_attendances', 'ja_ibfk_2', 'student_id', 'students', 'id');
    }

    private function safeAddFK(string $table, string $fkName, string $column, string $refTable, string $refColumn)
    {
        $db = db_connect();
        // Cek apakah FK sudah ada
        $check = $db->query("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = ?",
            [$db->getDatabase(), $table, $fkName])->getRow();
        if ($check) return;

        // Cek apakah kolom ada
        if (!$db->fieldExists($column, $table)) return;
        if (!$db->fieldExists($refColumn, $refTable)) return;

        try {
            $db->query("ALTER TABLE `{$table}` ADD CONSTRAINT `{$fkName}` FOREIGN KEY (`{$column}`) REFERENCES `{$refTable}` (`{$refColumn}`) ON DELETE CASCADE ON UPDATE CASCADE");
        } catch (\Exception $e) {
            // Abaikan jika gagal
        }
    }

    public function down()
    {
        // One-way fix
    }
}
