<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class EnsureGraduationTablesExist extends Migration
{
    public function up()
    {
        // Migration ini mengamankan tabel yang mungkin terlewat karena:
        // 1. Migration sebelumnya sudah tercatat di tabel migrations online
        //    (dari eksperimen atau SQL manual) tapi tabelnya tidak terbuat.
        // 2. Menyebabkan error "Table doesn't exist".
        // Solusi: CREATE TABLE IF NOT EXISTS dengan struktur yang persis sama.

        $db = db_connect();
        $prefix = $db->getPrefix();

        // ── 1. graduation_settings ───────────────────────────────────
        if (!$db->tableExists('graduation_settings')) {
            $this->forge->addField([
                'id' => [
                    'type'           => 'INT',
                    'unsigned'       => true,
                    'auto_increment' => true,
                ],
                'academic_year_id' => [
                    'type'       => 'BIGINT',
                    'unsigned'   => true,
                    'null'       => false,
                ],
                'final_grade_id' => [
                    'type'       => 'BIGINT',
                    'unsigned'   => true,
                    'null'       => true,
                ],
                'announcement_datetime' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'is_active' => [
                    'type'    => 'TINYINT',
                    'constraint' => 1,
                    'default' => 0,
                ],
                'allow_skl_download' => [
                    'type'       => 'TINYINT',
                    'constraint' => 1,
                    'null'       => false,
                    'default'    => 0,
                ],
                'custom_message' => [
                    'type' => 'TEXT',
                    'null' => true,
                ],
                'created_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'updated_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
            ]);

            $this->forge->addKey('id', true);
            $this->forge->addKey('academic_year_id', false, true); // UNIQUE
            $this->forge->addForeignKey('academic_year_id', 'academic_years', 'id', 'CASCADE', 'CASCADE');
            $this->forge->addForeignKey('final_grade_id', 'grades', 'id', 'SET NULL', 'CASCADE');
            $this->forge->createTable('graduation_settings', true);
        } else {
            // Pastikan kolom allow_skl_download ada (mungkin belum)
            if (!$db->fieldExists('allow_skl_download', 'graduation_settings')) {
                $this->forge->addColumn('graduation_settings', [
                    'allow_skl_download' => [
                        'type'       => 'TINYINT',
                        'constraint' => 1,
                        'null'       => false,
                        'default'    => 0,
                        'after'      => 'is_active',
                    ],
                ]);
            }
        }

        // ── 2. student_graduations ──────────────────────────────────
        if (!$db->tableExists('student_graduations')) {
            $this->forge->addField([
                'id' => [
                    'type'           => 'BIGINT',
                    'unsigned'       => true,
                    'auto_increment' => true,
                ],
                'student_id' => [
                    'type'       => 'BIGINT',
                    'unsigned'   => true,
                    'null'       => false,
                ],
                'academic_year_id' => [
                    'type'       => 'BIGINT',
                    'unsigned'   => true,
                    'null'       => false,
                ],
                'kelulusan_status' => [
                    'type'       => "ENUM('LULUS','TIDAK_LULUS','BELUM_DITENTUKAN')",
                    'null'       => false,
                    'default'    => 'BELUM_DITENTUKAN',
                ],
                'decided_at' => [
                    'type'       => 'DATE',
                    'null'       => true,
                ],
                'decided_by' => [
                    'type'       => 'BIGINT',
                    'unsigned'   => true,
                    'null'       => true,
                ],
                'skl_file_path' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 500,
                    'null'       => true,
                ],
                'skl_uploaded_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'created_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'updated_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'deleted_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
            ]);

            $this->forge->addKey('id', true);
            $this->forge->addForeignKey('student_id', 'students', 'id', 'CASCADE', 'CASCADE');
            $this->forge->addForeignKey('academic_year_id', 'academic_years', 'id', 'CASCADE', 'CASCADE');
            $this->forge->addKey(['student_id', 'academic_year_id'], false, true); // UNIQUE
            $this->forge->createTable('student_graduations', true);
        }

        // ── 3. ai_kb_tp ─────────────────────────────────────────────
        if (!$db->tableExists('ai_kb_tp')) {
            $this->forge->addField([
                'id' => [
                    'type'           => 'INT',
                    'unsigned'       => true,
                    'auto_increment' => true,
                ],
                'academic_year_id' => [
                    'type'       => 'INT',
                    'unsigned'   => true,
                    'null'       => false,
                ],
                'user_id' => [
                    'type'       => 'INT',
                    'unsigned'   => true,
                    'null'       => false,
                ],
                'subject_id' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 50,
                    'null'       => true,
                ],
                'subject_name' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 150,
                    'null'       => false,
                ],
                'grade_id' => [
                    'type'       => 'INT',
                    'unsigned'   => true,
                    'null'       => false,
                ],
                'grade_name' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 50,
                    'null'       => false,
                ],
                'chapters' => [
                    'type' => 'LONGTEXT',
                    'null' => true,
                ],
                'dpl' => [
                    'type' => 'LONGTEXT',
                    'null' => true,
                ],
                'panca_cinta' => [
                    'type' => 'LONGTEXT',
                    'null' => true,
                ],
                'tps' => [
                    'type' => 'LONGTEXT',
                    'null' => true,
                ],
                'created_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'updated_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
                'deleted_at' => [
                    'type' => 'DATETIME',
                    'null' => true,
                ],
            ]);

            $this->forge->addKey('id', true);
            $this->forge->createTable('ai_kb_tp', true);
        }
    }

    public function down()
    {
        // Tidak ada down — kita tidak ingin menghapus tabel yang sudah terlanjur ada
        // Migration ini one-way safety net.
    }
}
