<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        $driver = DB::getDriverName();

        if ($driver === 'sqlite') {
            // SQLite: Drop index, drop column, recreate column, recreate index
            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->dropIndex(['status']); // Drop the index first
            });

            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->dropColumn('status');
            });

            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->enum('status', ['pending', 'approved', 'verified', 'rejected'])
                    ->default('pending')
                    ->after('submission_type');
            });

            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->index('status'); // Recreate the index
            });

            // Update existing values to match new enum
            DB::table('employee_registrations')
                ->where('status', 'pending_approval')
                ->update(['status' => 'pending']);
        } else {
            // MySQL/PostgreSQL: First expand enum to include both old and new values
            DB::statement("ALTER TABLE employee_registrations MODIFY COLUMN status ENUM('pending_approval', 'pending', 'approved', 'verified', 'rejected') NOT NULL DEFAULT 'pending_approval'");

            // Then update existing values to match new enum
            DB::table('employee_registrations')
                ->where('status', 'pending_approval')
                ->update(['status' => 'pending']);

            // Finally, remove old values from enum
            DB::statement("ALTER TABLE employee_registrations MODIFY COLUMN status ENUM('pending', 'approved', 'verified', 'rejected') NOT NULL DEFAULT 'pending'");
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        $driver = DB::getDriverName();

        if ($driver === 'sqlite') {
            // SQLite: Drop index, drop column, recreate column, recreate index
            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->dropIndex(['status']); // Drop the index first
            });

            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->dropColumn('status');
            });

            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->enum('status', ['pending_approval', 'approved', 'rejected'])
                    ->default('pending_approval')
                    ->after('submission_type');
            });

            Schema::table('employee_registrations', function (Blueprint $table) {
                $table->index('status'); // Recreate the index
            });

            // Update values back
            DB::table('employee_registrations')
                ->where('status', 'pending')
                ->update(['status' => 'pending_approval']);
        } else {
            // MySQL/PostgreSQL: First expand enum to include both old and new values
            DB::statement("ALTER TABLE employee_registrations MODIFY COLUMN status ENUM('pending_approval', 'pending', 'approved', 'verified', 'rejected') NOT NULL DEFAULT 'pending'");

            // Update values back
            DB::table('employee_registrations')
                ->where('status', 'pending')
                ->update(['status' => 'pending_approval']);

            // Finally, remove new values from enum
            DB::statement("ALTER TABLE employee_registrations MODIFY COLUMN status ENUM('pending_approval', 'approved', 'rejected') NOT NULL DEFAULT 'pending_approval'");
        }
    }
};
