<?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
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Check if employees table exists first
        if (!Schema::hasTable('employees')) {
            throw new \Exception('Employees table must be created before updating employee_salaries foreign key');
        }
        
        // Get the database name
        $dbName = DB::connection()->getDatabaseName();
        
        // Find all foreign key constraints on employee_id column
        $foreignKeys = DB::select(
            "SELECT CONSTRAINT_NAME, REFERENCED_TABLE_NAME
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'employee_salaries' 
            AND COLUMN_NAME = 'employee_id' 
            AND REFERENCED_TABLE_NAME IS NOT NULL",
            [$dbName]
        );
        
        // Drop any existing foreign key constraint using raw SQL
        foreach ($foreignKeys as $fk) {
            DB::statement("ALTER TABLE employee_salaries DROP FOREIGN KEY `{$fk->CONSTRAINT_NAME}`");
        }
        
        // Now we can safely drop the index if it exists
        $indexes = DB::select(
            "SELECT INDEX_NAME 
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'employee_salaries' 
            AND INDEX_NAME = 'employee_salaries_employee_id_index'",
            [$dbName]
        );
        
        if (!empty($indexes)) {
            try {
                DB::statement("ALTER TABLE employee_salaries DROP INDEX `employee_salaries_employee_id_index`");
            } catch (\Exception $e) {
                // Index might not exist, ignore
            }
        }
        
        // Change the column to reference employees table instead of users
        // The column type should remain the same, we're just changing the foreign key
        DB::statement('ALTER TABLE employee_salaries MODIFY employee_id BIGINT UNSIGNED NOT NULL');
        
        // Add new foreign key (this will automatically create an index)
        Schema::table('employee_salaries', function (Blueprint $table) {
            $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        $dbName = DB::connection()->getDatabaseName();
        
        // Drop the new foreign key
        $foreignKeys = DB::select(
            "SELECT CONSTRAINT_NAME 
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'employee_salaries' 
            AND COLUMN_NAME = 'employee_id' 
            AND REFERENCED_TABLE_NAME = 'employees'",
            [$dbName]
        );
        
        foreach ($foreignKeys as $fk) {
            DB::statement("ALTER TABLE employee_salaries DROP FOREIGN KEY `{$fk->CONSTRAINT_NAME}`");
        }
        
        // Change back to reference users table
        DB::statement('ALTER TABLE employee_salaries MODIFY employee_id BIGINT UNSIGNED NOT NULL');
        
        Schema::table('employee_salaries', function (Blueprint $table) {
            $table->foreign('employee_id')->references('id')->on('users')->onDelete('cascade');
            $table->index('employee_id');
        });
    }
};
