<?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('withdraws') && !Schema::hasColumn('withdraws', 'transaction_id')) {
            Schema::table('withdraws', function (Blueprint $table) {
                $table->string('transaction_id', 64)->nullable()->after('id')->index();
            });
        }

        if (Schema::hasTable('withdraws') && Schema::hasColumn('withdraws', 'transaction_id')) {
            DB::table('withdraws')
                ->whereNull('transaction_id')
                ->orderBy('id')
                ->select('id')
                ->chunkById(200, function ($rows) {
                    foreach ($rows as $row) {
                        DB::table('withdraws')
                            ->where('id', $row->id)
                            ->whereNull('transaction_id')
                            ->update([
                                'transaction_id' => 'WD-' . str_pad((string) $row->id, 8, '0', STR_PAD_LEFT),
                            ]);
                    }
                });
        }

        if (!Schema::hasTable('withdraw_histories')) {
            Schema::create('withdraw_histories', function (Blueprint $table) {
                $table->id();
                $table->foreignId('withdraw_id')->nullable()->index();
                $table->foreignId('user_id')->nullable()->index();
                $table->foreignId('admin_id')->nullable()->index();
                $table->string('event', 50)->index();
                $table->string('from_status', 30)->nullable()->index();
                $table->string('to_status', 30)->nullable()->index();
                $table->decimal('amount', 14, 2)->default(0);
                $table->decimal('charge', 14, 2)->default(0);
                $table->decimal('total', 14, 2)->default(0);
                $table->decimal('balance_before', 14, 2)->nullable();
                $table->decimal('balance_after', 14, 2)->nullable();
                $table->text('note')->nullable();
                $table->json('metadata')->nullable();
                $table->timestamps();
            });
        }

        if (Schema::hasTable('withdraws') && Schema::hasTable('withdraw_histories')) {
            DB::table('withdraws')
                ->orderBy('id')
                ->select(['id', 'user_id', 'status', 'amount', 'charge', 'total', 'transaction_id', 'created_at', 'updated_at'])
                ->chunkById(200, function ($rows) {
                    foreach ($rows as $row) {
                        $exists = DB::table('withdraw_histories')
                            ->where('withdraw_id', $row->id)
                            ->exists();

                        if ($exists) {
                            continue;
                        }

                        DB::table('withdraw_histories')->insert([
                            'withdraw_id' => $row->id,
                            'user_id' => $row->user_id,
                            'admin_id' => null,
                            'event' => (string) $row->status === 'pending' ? 'submitted' : 'legacy_status',
                            'from_status' => null,
                            'to_status' => $row->status,
                            'amount' => (float) $row->amount,
                            'charge' => (float) $row->charge,
                            'total' => (float) $row->total,
                            'balance_before' => null,
                            'balance_after' => null,
                            'note' => 'পুরনো withdraw record history table-এ sync করা হয়েছে।',
                            'metadata' => json_encode([
                                'backfilled' => true,
                                'transaction_id' => $row->transaction_id ?? null,
                            ], JSON_UNESCAPED_UNICODE),
                            'created_at' => $row->created_at ?? now(),
                            'updated_at' => $row->updated_at ?? now(),
                        ]);
                    }
                });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('withdraw_histories');

        if (Schema::hasTable('withdraws') && Schema::hasColumn('withdraws', 'transaction_id')) {
            Schema::table('withdraws', function (Blueprint $table) {
                $table->dropColumn('transaction_id');
            });
        }
    }
};
