<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AlterAiKbCpTable extends Migration
{
    public function up()
    {
        $this->forge->addColumn('ai_kb_cp', [
            'academic_year_id' => [
                'type'       => 'BIGINT',
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'after'      => 'subject_id',
            ],
            'user_id' => [
                'type'       => 'BIGINT',
                'unsigned'   => true,
                'null'       => true,
                'default'    => null,
                'after'      => 'academic_year_id',
            ],
            'elements' => [
                'type'       => 'JSON',
                'null'       => true,
                'default'    => null,
                'after'      => 'content',
            ],
        ]);

        // Assign existing CP rows to the active academic year if possible
        $db = \Config\Database::connect();
        $activeYear = $db->table('academic_years')
            ->where('is_active', 1)
            ->where('deleted_at', null)
            ->get()
            ->getRowArray();

        if ($activeYear) {
            $db->table('ai_kb_cp')
               ->where('academic_year_id', null)
               ->update(['academic_year_id' => $activeYear['id']]);
        }

        // Add foreign keys using raw queries if preferred, or DB Forge if supported
        // In CI4, addForeignKey for existing table isn't fully supported via Forge alter, so we do it via raw queries
        try {
            $db->query('ALTER TABLE ai_kb_cp ADD CONSTRAINT ai_kb_cp_academic_year_id_foreign FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE SET NULL ON UPDATE CASCADE');
        } catch (\Exception $e) {}

        try {
            $db->query('ALTER TABLE ai_kb_cp ADD CONSTRAINT ai_kb_cp_user_id_foreign FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE');
        } catch (\Exception $e) {}

        // Add indices
        try {
            $db->query('ALTER TABLE ai_kb_cp ADD INDEX idx_ai_kb_cp_academic_year_id (academic_year_id)');
        } catch (\Exception $e) {}

        try {
            $db->query('ALTER TABLE ai_kb_cp ADD INDEX idx_ai_kb_cp_user_id (user_id)');
        } catch (\Exception $e) {}
    }

    public function down()
    {
        $db = \Config\Database::connect();
        try {
            $db->query('ALTER TABLE ai_kb_cp DROP FOREIGN KEY ai_kb_cp_academic_year_id_foreign');
        } catch (\Exception $e) {}
        try {
            $db->query('ALTER TABLE ai_kb_cp DROP FOREIGN KEY ai_kb_cp_user_id_foreign');
        } catch (\Exception $e) {}
        try {
            $db->query('ALTER TABLE ai_kb_cp DROP INDEX idx_ai_kb_cp_academic_year_id');
        } catch (\Exception $e) {}
        try {
            $db->query('ALTER TABLE ai_kb_cp DROP INDEX idx_ai_kb_cp_user_id');
        } catch (\Exception $e) {}

        $this->forge->dropColumn('ai_kb_cp', ['academic_year_id', 'user_id', 'elements']);
    }
}
