<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration {
    public function up(): void
    {
        // Drop dependent table exam_subject_rules if exists
        if (Schema::hasTable('exam_subject_rules')) {
            Schema::drop('exam_subject_rules');
        }

        // Drop foreign keys and columns referencing exams in other tables
        if (Schema::hasTable('result_marks') && Schema::hasColumn('result_marks', 'exam_id')) {
            try {
                $constraints = DB::select("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'result_marks' AND COLUMN_NAME = 'exam_id' AND REFERENCED_TABLE_NAME IS NOT NULL");
                foreach ($constraints as $c) {
                    DB::statement("ALTER TABLE result_marks DROP FOREIGN KEY `{$c->CONSTRAINT_NAME}`");
                }
            } catch (\Throwable $e) {}
            try { DB::statement('ALTER TABLE result_marks DROP COLUMN `exam_id`'); } catch (\Throwable $e) {}
        }

        if (Schema::hasTable('final_results') && Schema::hasColumn('final_results', 'exam_id')) {
            try {
                $constraints = DB::select("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'final_results' AND COLUMN_NAME = 'exam_id' AND REFERENCED_TABLE_NAME IS NOT NULL");
                foreach ($constraints as $c) {
                    DB::statement("ALTER TABLE final_results DROP FOREIGN KEY `{$c->CONSTRAINT_NAME}`");
                }
            } catch (\Throwable $e) {}
            try { DB::statement('ALTER TABLE final_results DROP COLUMN `exam_id`'); } catch (\Throwable $e) {}
        }

        // Finally drop exams and exam_types tables if they exist
        if (Schema::hasTable('exams')) {
            Schema::drop('exams');
        }

        if (Schema::hasTable('exam_types')) {
            Schema::drop('exam_types');
        }
    }

    public function down(): void
    {
        // Intentionally left empty (destructive removal)
    }
};


