<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateStudentPortalSettingsAndAuth extends Migration
{
    public function up()
    {
        // 1. Modify the role column ENUM to add 'siswa'
        $this->db->query("ALTER TABLE users MODIFY COLUMN role ENUM('superadmin','admin','operator','guru','tata_usaha','siswa') NOT NULL DEFAULT 'operator'");

        // 2. Add student_id to users to link user account with students table
        if (!$this->db->fieldExists('student_id', 'users')) {
            $this->forge->addColumn('users', [
                'student_id' => [
                    'type'       => 'BIGINT',
                    'unsigned'   => true,
                    'null'       => true,
                    'default'    => null,
                    'after'      => 'role'
                ],
            ]);
            
            // Add foreign key constraint
            $this->db->query("ALTER TABLE users ADD CONSTRAINT fk_users_student_id FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE");
        }

        // 3. Add is_password_reset flag to users
        if (!$this->db->fieldExists('is_password_reset', 'users')) {
            $this->forge->addColumn('users', [
                'is_password_reset' => [
                    'type'       => 'TINYINT',
                    'constraint' => 1,
                    'default'    => 0,
                    'after'      => 'student_id'
                ],
            ]);
        }

        // 4. Add settings to school_settings
        if (!$this->db->fieldExists('student_portal_editable_fields', 'school_settings')) {
            $this->forge->addColumn('school_settings', [
                'student_portal_editable_fields' => [
                    'type' => 'TEXT',
                    'null' => true,
                    'default' => null,
                ],
            ]);
        }
        if (!$this->db->fieldExists('student_portal_announcement', 'school_settings')) {
            $this->forge->addColumn('school_settings', [
                'student_portal_announcement' => [
                    'type' => 'TEXT',
                    'null' => true,
                    'default' => null,
                ],
            ]);
        }
    }

    public function down()
    {
        // Drop settings from school_settings
        if ($this->db->fieldExists('student_portal_editable_fields', 'school_settings')) {
            $this->forge->dropColumn('school_settings', 'student_portal_editable_fields');
        }
        if ($this->db->fieldExists('student_portal_announcement', 'school_settings')) {
            $this->forge->dropColumn('school_settings', 'student_portal_announcement');
        }

        // Drop foreign key first
        try {
            $this->db->query("ALTER TABLE users DROP FOREIGN KEY fk_users_student_id");
        } catch (\Exception $e) {}

        // Drop columns from users
        if ($this->db->fieldExists('student_id', 'users')) {
            $this->forge->dropColumn('users', 'student_id');
        }
        if ($this->db->fieldExists('is_password_reset', 'users')) {
            $this->forge->dropColumn('users', 'is_password_reset');
        }

        // Revert role ENUM
        $this->db->table('users')->where('role', 'siswa')->update(['role' => 'operator']);
        $this->db->query("ALTER TABLE users MODIFY COLUMN role ENUM('superadmin','admin','operator','guru','tata_usaha') NOT NULL DEFAULT 'operator'");
    }
}
