<?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
    {
        Schema::table('trade_document_payments', function (Blueprint $table) {
            $table->foreignId('business_id')->nullable()->index()->after('id');
            $table->string('payment_type', 20)->default('payment')->after('amount');
            $table->string('status', 20)->default('posted')->after('payment_type');
            $table->foreignId('parent_payment_id')->nullable()->index()->after('status');
            $table->string('currency', 8)->nullable()->after('parent_payment_id');
            $table->foreignId('corrected_by')->nullable()->index()->after('recorded_by');
            $table->dateTime('corrected_at')->nullable()->after('corrected_by');
            $table->text('correction_reason')->nullable()->after('corrected_at');
            $table->foreignId('voided_by')->nullable()->index()->after('correction_reason');
            $table->dateTime('voided_at')->nullable()->after('voided_by');
            $table->text('void_reason')->nullable()->after('voided_at');
        });

        DB::table('trade_document_payments')->orderBy('id')->each(function ($payment) {
            $document = DB::table('trade_documents')->where('id', $payment->trade_document_id)->first();
            DB::table('trade_document_payments')->where('id', $payment->id)->update([
                'business_id' => $document?->business_id,
                'currency' => $document?->currency,
                'reference' => blank($payment->reference) ? null : trim($payment->reference),
            ]);
        });

        DB::table('trade_document_payments')->whereNotNull('reference')
            ->select('trade_document_id', 'reference', DB::raw('COUNT(*) as aggregate'))
            ->groupBy('trade_document_id', 'reference')->having('aggregate', '>', 1)
            ->get()->each(function ($duplicate) {
                DB::table('trade_document_payments')
                    ->where('trade_document_id', $duplicate->trade_document_id)
                    ->where('reference', $duplicate->reference)->orderBy('id')->skip(1)->take(PHP_INT_MAX)
                    ->get()->each(fn ($payment) => DB::table('trade_document_payments')->where('id', $payment->id)
                        ->update(['reference' => $duplicate->reference.'-DUP-'.$payment->id]));
            });

        Schema::table('trade_document_payments', function (Blueprint $table) {
            $table->unique(['trade_document_id', 'reference'], 'trade_payment_document_reference_unique');
        });
    }

    public function down(): void
    {
        Schema::table('trade_document_payments', function (Blueprint $table) {
            $table->dropUnique('trade_payment_document_reference_unique');
            $table->dropColumn([
                'business_id', 'payment_type', 'status', 'parent_payment_id', 'currency',
                'corrected_by', 'corrected_at', 'correction_reason', 'voided_by', 'voided_at', 'void_reason',
            ]);
        });
    }
};
