<?php

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

/**
 * Fix SSD → SSP currency code in all enum columns.
 *
 * The correct ISO 4217 code for South Sudanese Pound is SSP, not SSD.
 * This migration:
 *   1. Alters the enum definition on each affected table to replace SSD with SSP.
 *   2. Updates any existing rows that stored 'SSD' to 'SSP'.
 *
 * Safe to re-run — UPDATE with WHERE is idempotent.
 */
return new class extends Migration
{
    /**
     * Tables and columns that had 'SSD' in their enum definition.
     */
    private array $targets = [
        ['table' => 'contribution_fees',        'column' => 'currency', 'enum' => "ENUM('USD','SSP','KES','UGX') NOT NULL DEFAULT 'SSP'"],
        ['table' => 'subscription_fees',        'column' => 'currency', 'enum' => "ENUM('USD','SSP','KES','UGX') NOT NULL DEFAULT 'SSP'"],
        ['table' => 'contributions',            'column' => 'currency', 'enum' => "ENUM('USD','SSP','KES','UGX') NOT NULL DEFAULT 'SSP'"],
        ['table' => 'loan_products',            'column' => 'currency', 'enum' => "ENUM('USD','SSP','KES','UGX') NOT NULL DEFAULT 'SSP'"],
        ['table' => 'loan_applications',        'column' => 'currency', 'enum' => "ENUM('USD','SSP','KES','UGX') NOT NULL DEFAULT 'SSP'"],
        ['table' => 'membership_transactions',  'column' => 'currency', 'enum' => "ENUM('USD','SSP','KES','UGX') NOT NULL DEFAULT 'SSP'"],
    ];

    public function up(): void
    {
        if (DB::getDriverName() !== 'mysql') {
            return; // SQLite has no enum constraints — skip
        }

        foreach ($this->targets as $target) {
            // 1. Alter the enum definition
            DB::statement("ALTER TABLE `{$target['table']}` MODIFY COLUMN `{$target['column']}` {$target['enum']}");

            // 2. Update any existing rows that stored the old 'SSD' value
            DB::table($target['table'])
                ->where($target['column'], 'SSD')
                ->update([$target['column'] => 'SSP']);
        }
    }

    public function down(): void
    {
        if (DB::getDriverName() !== 'mysql') {
            return;
        }

        foreach ($this->targets as $target) {
            $oldEnum = str_replace("'SSP'", "'SSD'", $target['enum']);
            DB::statement("ALTER TABLE `{$target['table']}` MODIFY COLUMN `{$target['column']}` {$oldEnum}");

            DB::table($target['table'])
                ->where($target['column'], 'SSP')
                ->update([$target['column'] => 'SSD']);
        }
    }
};
