<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class UpdateAttendancesTable extends Migration
{
    public function up()
    {
        // Hapus foreign key dulu
        $this->db->query("ALTER TABLE attendances DROP FOREIGN KEY attendances_classroom_id_foreign");
        $this->db->query("ALTER TABLE attendances DROP FOREIGN KEY attendances_academic_period_id_foreign");
        $this->db->query("ALTER TABLE attendances DROP FOREIGN KEY attendances_recorded_by_foreign");
        
        // Hapus unique key jika ada
        try {
            $this->db->query("ALTER TABLE attendances DROP KEY student_id");
        } catch (\Exception $e) {}
        
        // Ubah kolom status (HANYA Sakit, Izin, Alpha - hapus Hadir)
        $this->forge->modifyColumn('attendances', [
            'status' => [
                'type'       => 'ENUM',
                'constraint' => ['Sakit', 'Izin', 'Alpha'],
                'null'       => false,
            ],
        ]);
        
        // Tambah unique key (student_id, date, academic_period_id)
        $this->db->query("ALTER TABLE attendances ADD UNIQUE KEY unique_attendance (student_id, date, academic_period_id)");
        
        // Pasang ulang foreign key
        $this->db->query("ALTER TABLE attendances ADD CONSTRAINT attendances_classroom_id_foreign FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE");
        $this->db->query("ALTER TABLE attendances ADD CONSTRAINT attendances_academic_period_id_foreign FOREIGN KEY (academic_period_id) REFERENCES academic_periods(id) ON DELETE CASCADE");
        $this->db->query("ALTER TABLE attendances ADD CONSTRAINT attendances_recorded_by_foreign FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL");
    }

    public function down()
    {
        // Kembalikan ke enum dengan Hadir
        $this->forge->modifyColumn('attendances', [
            'status' => [
                'type'       => 'ENUM',
                'constraint' => ['Hadir', 'Sakit', 'Izin', 'Alpha'],
                'default'    => 'Hadir',
            ],
        ]);
        
        $this->db->query("ALTER TABLE attendances DROP KEY unique_attendance");
    }
}