<?php

use App\Models\User;
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
{
    private array $permissionsByRole = [
        User::ROLE_TENANT_ADMIN => ['transfers.view', 'transfers.manage', 'transfers.approve'],
        User::ROLE_MANAGER => ['transfers.view', 'transfers.manage', 'transfers.approve'],
        User::ROLE_LOAN_OFFICER => ['transfers.view', 'transfers.manage'],
        User::ROLE_ACCOUNTANT => ['transfers.view', 'transfers.approve'],
    ];

    public function up(): void
    {
        if (! Schema::hasTable('member_transfers')) {
            Schema::create('member_transfers', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
                $table->foreignId('sender_customer_id')->constrained('customers')->cascadeOnDelete();
                $table->foreignId('sender_savings_account_id')->constrained('savings_accounts')->cascadeOnDelete();
                $table->foreignId('recipient_customer_id')->nullable()->constrained('customers')->nullOnDelete();
                $table->foreignId('recipient_savings_account_id')->nullable()->constrained('savings_accounts')->nullOnDelete();
                $table->foreignId('initiated_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('rejected_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('canceled_by')->nullable()->constrained('users')->nullOnDelete();
                $table->foreignId('sender_transaction_id')->nullable()->constrained('savings_transactions')->nullOnDelete();
                $table->foreignId('recipient_transaction_id')->nullable()->constrained('savings_transactions')->nullOnDelete();
                $table->string('transfer_type', 30)->index();
                $table->string('reference')->unique();
                $table->decimal('amount', 14, 2);
                $table->decimal('fee', 14, 2)->default(0);
                $table->decimal('total_amount', 14, 2);
                $table->string('recipient_account_number', 80)->nullable();
                $table->string('recipient_account_name')->nullable();
                $table->string('recipient_bank_name')->nullable();
                $table->string('recipient_bank_code', 80)->nullable();
                $table->text('narration')->nullable();
                $table->string('status', 30)->default('pending')->index();
                $table->string('provider_reference')->nullable();
                $table->json('provider_response')->nullable();
                $table->timestamp('approved_at')->nullable();
                $table->timestamp('rejected_at')->nullable();
                $table->timestamp('canceled_at')->nullable();
                $table->timestamp('posted_at')->nullable()->index();
                $table->text('rejected_reason')->nullable();
                $table->text('canceled_reason')->nullable();
                $table->timestamps();

                $table->index(['tenant_id', 'transfer_type', 'status'], 'mt_tenant_type_status_idx');
                $table->index(['tenant_id', 'sender_customer_id', 'created_at'], 'mt_sender_created_idx');
                $table->index(['tenant_id', 'recipient_customer_id', 'created_at'], 'mt_recipient_created_idx');
            });
        } else {
            $this->addIndexIfMissing('mt_tenant_type_status_idx', ['tenant_id', 'transfer_type', 'status']);
            $this->addIndexIfMissing('mt_sender_created_idx', ['tenant_id', 'sender_customer_id', 'created_at']);
            $this->addIndexIfMissing('mt_recipient_created_idx', ['tenant_id', 'recipient_customer_id', 'created_at']);
        }

        $this->grantPermissions();
    }

    public function down(): void
    {
        $this->revokePermissions();
        Schema::dropIfExists('member_transfers');
    }

    private function grantPermissions(): void
    {
        if (! Schema::hasTable('role_profiles')) {
            return;
        }

        foreach ($this->permissionsByRole as $role => $permissionsToAdd) {
            DB::table('role_profiles')
                ->where('base_role', $role)
                ->orderBy('id')
                ->get(['id', 'permissions'])
                ->each(function (object $profile) use ($permissionsToAdd): void {
                    $permissions = json_decode((string) $profile->permissions, true);
                    $permissions = is_array($permissions) ? $permissions : [];

                    DB::table('role_profiles')->where('id', $profile->id)->update([
                        'permissions' => json_encode(array_values(array_unique([...$permissions, ...$permissionsToAdd]))),
                        'updated_at' => now(),
                    ]);
                });
        }
    }

    private function revokePermissions(): void
    {
        if (! Schema::hasTable('role_profiles')) {
            return;
        }

        $allPermissions = array_values(array_unique(array_merge(...array_values($this->permissionsByRole))));

        DB::table('role_profiles')
            ->orderBy('id')
            ->get(['id', 'permissions'])
            ->each(function (object $profile) use ($allPermissions): void {
                $permissions = json_decode((string) $profile->permissions, true);

                if (! is_array($permissions)) {
                    return;
                }

                DB::table('role_profiles')->where('id', $profile->id)->update([
                    'permissions' => json_encode(array_values(array_diff($permissions, $allPermissions))),
                    'updated_at' => now(),
                ]);
            });
    }

    private function addIndexIfMissing(string $indexName, array $columns): void
    {
        if (! Schema::hasTable('member_transfers') || $this->indexExists($indexName)) {
            return;
        }

        Schema::table('member_transfers', function (Blueprint $table) use ($columns, $indexName): void {
            $table->index($columns, $indexName);
        });
    }

    private function indexExists(string $indexName): bool
    {
        try {
            return collect(Schema::getIndexes('member_transfers'))
                ->contains(fn (array $index): bool => ($index['name'] ?? null) === $indexName);
        } catch (Throwable) {
            return false;
        }
    }
};
