<?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
    {
        // Step 1: Add new columns first
        Schema::table('loans', function (Blueprint $table) {
            $table->unsignedBigInteger('leader_approved_by')->nullable()->after('approved_by');
            $table->timestamp('leader_approved_at')->nullable()->after('approved_at');
        });
        
        // Step 2: Alter the status enum to include new values
        DB::statement("ALTER TABLE loans MODIFY COLUMN status ENUM('pending', 'approved', 'rejected', 'pending_leader_review', 'pending_top_leader_review', 'leader_rejected', 'top_leader_rejected') DEFAULT 'pending'");
        
        // Step 3: Migrate existing data
        // Old 'pending' -> 'pending_leader_review'
        DB::table('loans')
            ->where('status', 'pending')
            ->update(['status' => 'pending_leader_review']);
        
        // Old 'rejected' -> 'leader_rejected'
        DB::table('loans')
            ->where('status', 'rejected')
            ->update(['status' => 'leader_rejected']);
        
        // For existing approved loans, set both leader and top-leader approval
        $approvedLoans = DB::table('loans')->where('status', 'approved')->get();
        foreach ($approvedLoans as $loan) {
            if ($loan->approved_by) {
                DB::table('loans')
                    ->where('id', $loan->id)
                    ->update([
                        'leader_approved_by' => $loan->approved_by,
                        'leader_approved_at' => $loan->approved_at,
                    ]);
            }
        }
        
        // Step 4: Update default status
        DB::statement("ALTER TABLE loans MODIFY COLUMN status ENUM('pending_leader_review', 'pending_top_leader_review', 'leader_rejected', 'approved', 'top_leader_rejected') DEFAULT 'pending_leader_review'");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Revert status enum to include old values
        DB::statement("ALTER TABLE loans MODIFY COLUMN status ENUM('pending', 'approved', 'rejected', 'pending_leader_review', 'pending_top_leader_review', 'leader_rejected', 'top_leader_rejected') DEFAULT 'pending'");
        
        // Convert statuses back
        DB::table('loans')
            ->where('status', 'pending_leader_review')
            ->update(['status' => 'pending']);
        
        DB::table('loans')
            ->where('status', 'pending_top_leader_review')
            ->update(['status' => 'pending']);
        
        DB::table('loans')
            ->whereIn('status', ['leader_rejected', 'top_leader_rejected'])
            ->update(['status' => 'rejected']);
        
        // Remove old enum values
        DB::statement("ALTER TABLE loans MODIFY COLUMN status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending'");
        
        // Drop new columns
        Schema::table('loans', function (Blueprint $table) {
            $table->dropColumn(['leader_approved_by', 'leader_approved_at']);
        });
    }
};
