<?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
    {
        // For SQLite, we need to recreate the table with the new enum values
        if (DB::getDriverName() === 'sqlite') {
            // Create a temporary table with the new structure
            Schema::create('payment_requests_temp', function (Blueprint $table) {
                $table->id();
                $table->string('title');
                $table->text('description');
                $table->decimal('amount', 10, 2);
                $table->string('currency', 3)->default('NGN');
                $table->enum('status', ['active', 'cancelled', 'settled'])->default('active');
                $table->decimal('amount_paid', 10, 2)->default(0);
                $table->decimal('balance', 10, 2)->default(0);
                $table->json('meta')->nullable();
                $table->string('target_type');
                $table->unsignedBigInteger('target_id')->nullable();
                $table->unsignedBigInteger('student_id')->nullable();
                $table->unsignedBigInteger('school_class_id')->nullable();
                $table->unsignedBigInteger('batch_id')->nullable();
                $table->unsignedBigInteger('created_by');
                $table->string('payment_id')->nullable();
                $table->timestamps();
                $table->softDeletes();
            });

            // Copy data from old table to new table, mapping old statuses to new ones
            DB::statement("
                INSERT INTO payment_requests_temp 
                SELECT 
                    id, title, description, amount, currency,
                    CASE 
                        WHEN status IN ('unpaid', 'processing') THEN 'active'
                        WHEN status = 'cancelled' THEN 'cancelled'
                        WHEN status IN ('paid', 'partial_payment') THEN 'settled'
                        ELSE 'active'
                    END as status,
                    amount_paid, balance, meta, target_type, target_id,
                    student_id, school_class_id, batch_id, created_by, payment_id,
                    created_at, updated_at, deleted_at
                FROM payment_requests
            ");

            // Drop the old table
            Schema::dropIfExists('payment_requests');

            // Rename the temporary table
            Schema::rename('payment_requests_temp', 'payment_requests');

            // Re-add foreign key constraints
            Schema::table('payment_requests', function (Blueprint $table) {
                $table->foreign('student_id')->references('id')->on('users')->onDelete('cascade');
                $table->foreign('school_class_id')->references('id')->on('school_classes')->onDelete('cascade');
                $table->foreign('batch_id')->references('id')->on('batches')->onDelete('cascade');
                $table->foreign('created_by')->references('id')->on('users')->onDelete('cascade');
            });
        } else {
            // For other databases, use ALTER TABLE
            DB::statement("ALTER TABLE payment_requests MODIFY COLUMN status ENUM('active', 'cancelled', 'settled') DEFAULT 'active'");
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        if (DB::getDriverName() === 'sqlite') {
            // Create a temporary table with the old structure
            Schema::create('payment_requests_temp', function (Blueprint $table) {
                $table->id();
                $table->string('title');
                $table->text('description');
                $table->decimal('amount', 10, 2);
                $table->string('currency', 3)->default('NGN');
                $table->enum('status', ['unpaid', 'paid', 'processing', 'partial_payment', 'cancelled'])->default('unpaid');
                $table->decimal('amount_paid', 10, 2)->default(0);
                $table->decimal('balance', 10, 2)->default(0);
                $table->json('meta')->nullable();
                $table->string('target_type');
                $table->unsignedBigInteger('target_id')->nullable();
                $table->unsignedBigInteger('student_id')->nullable();
                $table->unsignedBigInteger('school_class_id')->nullable();
                $table->unsignedBigInteger('batch_id')->nullable();
                $table->unsignedBigInteger('created_by');
                $table->string('payment_id')->nullable();
                $table->timestamps();
                $table->softDeletes();
            });

            // Copy data back, mapping new statuses to old ones
            DB::statement("
                INSERT INTO payment_requests_temp 
                SELECT 
                    id, title, description, amount, currency,
                    CASE 
                        WHEN status = 'active' THEN 'unpaid'
                        WHEN status = 'cancelled' THEN 'cancelled'
                        WHEN status = 'settled' THEN 'paid'
                        ELSE 'unpaid'
                    END as status,
                    amount_paid, balance, meta, target_type, target_id,
                    student_id, school_class_id, batch_id, created_by, payment_id,
                    created_at, updated_at, deleted_at
                FROM payment_requests
            ");

            Schema::dropIfExists('payment_requests');
            Schema::rename('payment_requests_temp', 'payment_requests');

            Schema::table('payment_requests', function (Blueprint $table) {
                $table->foreign('student_id')->references('id')->on('users')->onDelete('cascade');
                $table->foreign('school_class_id')->references('id')->on('school_classes')->onDelete('cascade');
                $table->foreign('batch_id')->references('id')->on('batches')->onDelete('cascade');
                $table->foreign('created_by')->references('id')->on('users')->onDelete('cascade');
            });
        } else {
            DB::statement("ALTER TABLE payment_requests MODIFY COLUMN status ENUM('unpaid', 'paid', 'processing', 'partial_payment', 'cancelled') DEFAULT 'unpaid'");
        }
    }
};