<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddSubjectIdToSettingsAndScores extends Migration
{
    public function up()
    {
        // 1. Add nullable subject_id column if not exists
        if (!$this->db->fieldExists('subject_id', 'assessment_class_settings')) {
            $this->forge->addColumn('assessment_class_settings', [
                'subject_id' => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true, 'after' => 'classroom_id'],
            ]);
        }

        if (!$this->db->fieldExists('subject_id', 'assessment_scores')) {
            $this->forge->addColumn('assessment_scores', [
                'subject_id' => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true, 'after' => 'classroom_id'],
            ]);
        }

        // 2. Migrate existing data
        // For ASH (Asesmen Sumatif Harian), copy subject_id from assessments
        $this->db->query("
            UPDATE assessment_class_settings acs
            JOIN assessments a ON a.id = acs.assessment_id
            JOIN assessment_types at ON at.id = a.assessment_type_id
            SET acs.subject_id = a.subject_id
            WHERE at.code = 'ASH' AND acs.subject_id IS NULL
        ");

        $this->db->query("
            UPDATE assessment_scores aso
            JOIN assessments a ON a.id = aso.assessment_id
            JOIN assessment_types at ON at.id = a.assessment_type_id
            SET aso.subject_id = a.subject_id
            WHERE at.code = 'ASH' AND aso.subject_id IS NULL
        ");

        // For non-ASH, update subject_id from teaching_assignments based on teacher_id and classroom_id
        $this->db->query("
            UPDATE assessment_class_settings acs
            JOIN teaching_assignments ta ON ta.teacher_id = acs.teacher_id AND ta.classroom_id = acs.classroom_id
            SET acs.subject_id = ta.subject_id
            WHERE acs.subject_id IS NULL
        ");

        // For scores, match with settings to get subject_id
        $this->db->query("
            UPDATE assessment_scores aso
            JOIN assessment_class_settings acs ON acs.assessment_id = aso.assessment_id AND acs.classroom_id = aso.classroom_id
            SET aso.subject_id = acs.subject_id
            WHERE aso.subject_id IS NULL AND acs.subject_id IS NOT NULL
        ");

        // Fallback: if subject_id is still null, set to first available subject in school_subjects to ensure NOT NULL constraint passes
        $subjectRow = $this->db->query("SELECT id FROM school_subjects LIMIT 1")->getRow();
        if ($subjectRow) {
            $fallbackSubjectId = $subjectRow->id;
            $this->db->query("UPDATE assessment_class_settings SET subject_id = ? WHERE subject_id IS NULL", [$fallbackSubjectId]);
            $this->db->query("UPDATE assessment_scores SET subject_id = ? WHERE subject_id IS NULL", [$fallbackSubjectId]);
        }

        // 3. Modify subject_id column to be NOT NULL
        $this->db->query("ALTER TABLE assessment_class_settings MODIFY COLUMN subject_id BIGINT UNSIGNED NOT NULL");
        $this->db->query("ALTER TABLE assessment_scores MODIFY COLUMN subject_id BIGINT UNSIGNED NOT NULL");

        // 4. Add new unique indexes FIRST so MySQL doesn't fail on foreign key index requirements
        $hasNewAcsIndex = $this->db->query("SHOW INDEX FROM assessment_class_settings WHERE Key_name = 'assessment_class_settings_assessment_classroom_subject'")->getRow();
        if (!$hasNewAcsIndex) {
            $this->db->query("ALTER TABLE assessment_class_settings ADD UNIQUE KEY assessment_class_settings_assessment_classroom_subject (assessment_id, classroom_id, subject_id)");
        }

        $hasNewAsoIndex = $this->db->query("SHOW INDEX FROM assessment_scores WHERE Key_name = 'assessment_scores_assessment_student_subject'")->getRow();
        if (!$hasNewAsoIndex) {
            $this->db->query("ALTER TABLE assessment_scores ADD UNIQUE KEY assessment_scores_assessment_student_subject (assessment_id, student_id, subject_id)");
        }

        // 5. Drop old unique indexes
        $hasOldAcsIndex = $this->db->query("SHOW INDEX FROM assessment_class_settings WHERE Key_name = 'assessment_id_classroom_id'")->getRow();
        if ($hasOldAcsIndex) {
            $this->db->query("ALTER TABLE assessment_class_settings DROP INDEX assessment_id_classroom_id");
        }

        $hasOldAsoIndex = $this->db->query("SHOW INDEX FROM assessment_scores WHERE Key_name = 'assessment_id_student_id'")->getRow();
        if ($hasOldAsoIndex) {
            $this->db->query("ALTER TABLE assessment_scores DROP INDEX assessment_id_student_id");
        }

        // 6. Add Foreign Keys for subject_id if they do not exist
        try {
            $this->db->query("ALTER TABLE assessment_class_settings ADD CONSTRAINT fk_acs_subject_id FOREIGN KEY (subject_id) REFERENCES school_subjects(id) ON DELETE CASCADE");
        } catch (\Exception $e) {
            // Ignore if foreign key already exists
        }

        try {
            $this->db->query("ALTER TABLE assessment_scores ADD CONSTRAINT fk_aso_subject_id FOREIGN KEY (subject_id) REFERENCES school_subjects(id) ON DELETE CASCADE");
        } catch (\Exception $e) {
            // Ignore if foreign key already exists
        }
    }

    public function down()
    {
        // 1. Drop Foreign Keys
        try {
            $this->db->query("ALTER TABLE assessment_class_settings DROP FOREIGN KEY fk_acs_subject_id");
        } catch (\Exception $e) {}

        try {
            $this->db->query("ALTER TABLE assessment_scores DROP FOREIGN KEY fk_aso_subject_id");
        } catch (\Exception $e) {}

        // 2. Add back old unique indexes FIRST before dropping new ones, to preserve FK requirements
        $hasOldAcsIndex = $this->db->query("SHOW INDEX FROM assessment_class_settings WHERE Key_name = 'assessment_id_classroom_id'")->getRow();
        if (!$hasOldAcsIndex) {
            $this->db->query("ALTER TABLE assessment_class_settings ADD UNIQUE KEY assessment_id_classroom_id (assessment_id, classroom_id)");
        }

        $hasOldAsoIndex = $this->db->query("SHOW INDEX FROM assessment_scores WHERE Key_name = 'assessment_id_student_id'")->getRow();
        if (!$hasOldAsoIndex) {
            $this->db->query("ALTER TABLE assessment_scores ADD UNIQUE KEY assessment_id_student_id (assessment_id, student_id)");
        }

        // 3. Drop new unique indexes
        $hasNewAcsIndex = $this->db->query("SHOW INDEX FROM assessment_class_settings WHERE Key_name = 'assessment_class_settings_assessment_classroom_subject'")->getRow();
        if ($hasNewAcsIndex) {
            $this->db->query("ALTER TABLE assessment_class_settings DROP INDEX assessment_class_settings_assessment_classroom_subject");
        }

        $hasNewAsoIndex = $this->db->query("SHOW INDEX FROM assessment_scores WHERE Key_name = 'assessment_scores_assessment_student_subject'")->getRow();
        if ($hasNewAsoIndex) {
            $this->db->query("ALTER TABLE assessment_scores DROP INDEX assessment_scores_assessment_student_subject");
        }

        // 4. Drop columns
        if ($this->db->fieldExists('subject_id', 'assessment_class_settings')) {
            $this->forge->dropColumn('assessment_class_settings', 'subject_id');
        }
        if ($this->db->fieldExists('subject_id', 'assessment_scores')) {
            $this->forge->dropColumn('assessment_scores', 'subject_id');
        }
    }
}
