<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

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

        $checks = [
            // Tabel → [kolom → definisi SQL]
            'graduation_settings' => [
                'allow_skl_download' => "ALTER TABLE graduation_settings ADD COLUMN `allow_skl_download` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_active`",
            ],
            'school_settings' => [
                'ai_provider' => "ALTER TABLE school_settings ADD COLUMN `ai_provider` VARCHAR(50) DEFAULT 'gemini' AFTER `student_rules`",
                'ai_api_key'  => "ALTER TABLE school_settings ADD COLUMN `ai_api_key` TEXT DEFAULT NULL AFTER `ai_provider`",
            ],
            'students' => [
                'graduated_academic_year_id' => "ALTER TABLE students ADD COLUMN `graduated_academic_year_id` BIGINT UNSIGNED DEFAULT NULL AFTER `exit_reason`",
            ],
            'exam_events' => [
                'pairing_plan' => "ALTER TABLE exam_events ADD COLUMN `pairing_plan` TEXT DEFAULT NULL",
                'event_code'   => "ALTER TABLE exam_events ADD COLUMN `event_code` VARCHAR(20) DEFAULT NULL",
                'proctors_per_room' => "ALTER TABLE exam_events ADD COLUMN `proctors_per_room` INT(11) NOT NULL DEFAULT 1",
                'chairman_name' => "ALTER TABLE exam_events ADD COLUMN `chairman_name` VARCHAR(150) DEFAULT NULL",
                'chairman_nip'  => "ALTER TABLE exam_events ADD COLUMN `chairman_nip` VARCHAR(50) DEFAULT NULL",
                'proctor_rules' => "ALTER TABLE exam_events ADD COLUMN `proctor_rules` TEXT DEFAULT NULL",
                'student_rules' => "ALTER TABLE exam_events ADD COLUMN `student_rules` TEXT DEFAULT NULL",
            ],
            'exam_event_teachers' => [
                'max_days' => "ALTER TABLE exam_event_teachers ADD COLUMN `max_days` INT(11) NOT NULL DEFAULT 0",
            ],
            'teachers' => [
                'golongan' => "ALTER TABLE teachers ADD COLUMN `golongan` VARCHAR(20) DEFAULT NULL AFTER `nip`",
            ],
            'school_settings' => [
                'proctor_rules' => "ALTER TABLE school_settings ADD COLUMN `proctor_rules` TEXT DEFAULT NULL",
                'student_rules' => "ALTER TABLE school_settings ADD COLUMN `student_rules` TEXT DEFAULT NULL",
            ],
            'letter_logs' => [
                'is_reference' => "ALTER TABLE letter_logs ADD COLUMN `is_reference` TINYINT(1) NOT NULL DEFAULT 0",
            ],
            'learning_devices' => [
                'chapters' => "ALTER TABLE learning_devices ADD COLUMN `chapters` LONGTEXT DEFAULT NULL AFTER `cp_elements`",
                'dpl' => "ALTER TABLE learning_devices ADD COLUMN `dpl` LONGTEXT DEFAULT NULL AFTER `chapters`",
                'panca_cinta' => "ALTER TABLE learning_devices ADD COLUMN `panca_cinta` LONGTEXT DEFAULT NULL AFTER `dpl`",
                'kktp_intervals' => "ALTER TABLE learning_devices ADD COLUMN `kktp_intervals` VARCHAR(255) DEFAULT NULL AFTER `chapter_cp_map`",
            ],
        ];

        foreach ($checks as $table => $columns) {
            foreach ($columns as $col => $sql) {
                try {
                    if (!$db->fieldExists($col, $table)) {
                        $db->query($sql);
                    }
                } catch (\Exception $e) {
                    // Skip jika gagal
                }
            }
        }
    }

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