<?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
{
    public function up(): void
    {
        if (Schema::hasTable('savings_accounts')) {
            Schema::table('savings_accounts', function (Blueprint $table): void {
                if (! Schema::hasColumn('savings_accounts', 'approved_by')) {
                    $table->foreignId('approved_by')->nullable()->after('status')->constrained('users')->nullOnDelete();
                }

                if (! Schema::hasColumn('savings_accounts', 'approved_at')) {
                    $table->timestamp('approved_at')->nullable()->after('approved_by');
                }

                if (! Schema::hasColumn('savings_accounts', 'rejected_at')) {
                    $table->timestamp('rejected_at')->nullable()->after('approved_at');
                }

                if (! Schema::hasColumn('savings_accounts', 'rejected_reason')) {
                    $table->text('rejected_reason')->nullable()->after('rejected_at');
                }
            });

            DB::table('savings_accounts')
                ->where('status', 'active')
                ->whereNull('approved_at')
                ->update(['approved_at' => DB::raw('COALESCE(opened_at, created_at)')]);
        }

        if (Schema::hasTable('investment_accounts')) {
            Schema::table('investment_accounts', function (Blueprint $table): void {
                if (! Schema::hasColumn('investment_accounts', 'approved_by')) {
                    $table->foreignId('approved_by')->nullable()->after('status')->constrained('users')->nullOnDelete();
                }

                if (! Schema::hasColumn('investment_accounts', 'approved_at')) {
                    $table->timestamp('approved_at')->nullable()->after('approved_by');
                }

                if (! Schema::hasColumn('investment_accounts', 'rejected_at')) {
                    $table->timestamp('rejected_at')->nullable()->after('approved_at');
                }

                if (! Schema::hasColumn('investment_accounts', 'rejected_reason')) {
                    $table->text('rejected_reason')->nullable()->after('rejected_at');
                }
            });

            DB::table('investment_accounts')
                ->where('status', 'active')
                ->whereNull('approved_at')
                ->update(['approved_at' => DB::raw('COALESCE(opened_at, created_at)')]);
        }
    }

    public function down(): void
    {
        if (Schema::hasTable('investment_accounts')) {
            Schema::table('investment_accounts', function (Blueprint $table): void {
                foreach (['rejected_reason', 'rejected_at', 'approved_at'] as $column) {
                    if (Schema::hasColumn('investment_accounts', $column)) {
                        $table->dropColumn($column);
                    }
                }

                if (Schema::hasColumn('investment_accounts', 'approved_by')) {
                    $table->dropConstrainedForeignId('approved_by');
                }
            });
        }

        if (Schema::hasTable('savings_accounts')) {
            Schema::table('savings_accounts', function (Blueprint $table): void {
                foreach (['rejected_reason', 'rejected_at', 'approved_at'] as $column) {
                    if (Schema::hasColumn('savings_accounts', $column)) {
                        $table->dropColumn($column);
                    }
                }

                if (Schema::hasColumn('savings_accounts', 'approved_by')) {
                    $table->dropConstrainedForeignId('approved_by');
                }
            });
        }
    }
};
