<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

return new class extends Migration
{
    public function up(): void
    {
        if (Schema::hasTable('savings_transactions') && ! Schema::hasColumn('savings_transactions', 'status')) {
            Schema::table('savings_transactions', function (Blueprint $table): void {
                $table->string('status', 30)->default('posted')->after('method')->index();
            });
        }

        $this->restoreSavingsAccountsToActive();
        $this->restoreInvestmentAccountsToActiveWithPendingDeposits();
    }

    public function down(): void
    {
        if (Schema::hasTable('savings_transactions') && Schema::hasColumn('savings_transactions', 'status')) {
            Schema::table('savings_transactions', function (Blueprint $table): void {
                $table->dropIndex(['status']);
                $table->dropColumn('status');
            });
        }
    }

    private function restoreSavingsAccountsToActive(): void
    {
        if (! Schema::hasTable('savings_accounts') || ! Schema::hasColumn('savings_accounts', 'status')) {
            return;
        }

        $updates = [
            'status' => 'active',
        ];

        if (Schema::hasColumn('savings_accounts', 'opened_at')) {
            $updates['opened_at'] = DB::raw('COALESCE(opened_at, created_at)');
        }

        if (Schema::hasColumn('savings_accounts', 'approved_at')) {
            $updates['approved_at'] = DB::raw('COALESCE(approved_at, opened_at, created_at)');
        }

        DB::table('savings_accounts')
            ->where('status', 'pending')
            ->update($updates);
    }

    private function restoreInvestmentAccountsToActiveWithPendingDeposits(): void
    {
        if (! Schema::hasTable('investment_accounts') || ! Schema::hasTable('investment_transactions')) {
            return;
        }

        DB::table('investment_accounts')
            ->where('status', 'pending')
            ->orderBy('id')
            ->get()
            ->each(function ($account): void {
                $amount = (float) ($account->amount ?? 0);
                $hasDeposit = DB::table('investment_transactions')
                    ->where('investment_account_id', $account->id)
                    ->where('type', 'deposit')
                    ->exists();

                if ($amount > 0 && ! $hasDeposit) {
                    DB::table('investment_transactions')->insert([
                        'tenant_id' => $account->tenant_id,
                        'investment_account_id' => $account->id,
                        'customer_id' => $account->customer_id,
                        'created_by' => property_exists($account, 'approved_by') ? $account->approved_by : null,
                        'type' => 'deposit',
                        'amount' => $amount,
                        'balance_before' => 0,
                        'balance_after' => 0,
                        'reference' => $this->reference('INVDEP'),
                        'method' => 'cash',
                        'status' => 'pending',
                        'description' => 'Initial investment deposit awaiting approval',
                        'transacted_at' => now(),
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }

                $updates = [
                    'status' => 'active',
                    'amount' => 0,
                    'balance' => 0,
                    'expected_return' => 0,
                ];

                if (Schema::hasColumn('investment_accounts', 'opened_at')) {
                    $updates['opened_at'] = DB::raw('COALESCE(opened_at, created_at)');
                }

                if (Schema::hasColumn('investment_accounts', 'approved_at')) {
                    $updates['approved_at'] = DB::raw('COALESCE(approved_at, opened_at, created_at)');
                }

                DB::table('investment_accounts')
                    ->where('id', $account->id)
                    ->update($updates);
            });
    }

    private function reference(string $prefix): string
    {
        do {
            $reference = $prefix.'-'.now()->format('YmdHis').'-'.Str::upper(Str::random(6));
        } while (DB::table('investment_transactions')->where('reference', $reference)->exists());

        return $reference;
    }
};
