<?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
    {
        Schema::table('membership_users', function (Blueprint $table) {
            // Payment_plan
            if (!Schema::hasColumn('membership_users', 'payment_plan_id')) {
                $table->foreignId('payment_plan_id')
                    ->nullable()
                    ->after('id')
                    ->constrained('payment_plan')
                    ->nullOnDelete();
            }
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        $tableName = 'membership_users';
        $database = DB::getDatabaseName();

        $foreignKeys = DB::table('information_schema.KEY_COLUMN_USAGE')
            ->select('CONSTRAINT_NAME')
            ->where('TABLE_SCHEMA', $database)
            ->where('TABLE_NAME', $tableName)
            ->where('COLUMN_NAME', 'payment_plan_id')
            ->whereNotNull('REFERENCED_TABLE_NAME')
            ->pluck('CONSTRAINT_NAME')
            ->unique()
            ->values();

        foreach ($foreignKeys as $foreignKeyName) {
            DB::statement("ALTER TABLE `{$tableName}` DROP FOREIGN KEY `{$foreignKeyName}`");
        }

        Schema::table($tableName, function (Blueprint $table) {
            if (Schema::hasColumn('membership_users', 'payment_plan_id')) {
                $table->dropColumn('payment_plan_id');
            }
        });
    }
};
