<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('remote_payment_submissions', function (Blueprint $table): void {
            $table->id();
            $table->string('submission_number')->unique();
            $table->foreignId('loan_id')->constrained()->restrictOnDelete();
            $table->foreignId('customer_id')->constrained()->restrictOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->decimal('amount', 18, 2);
            $table->string('payment_channel', 50);
            $table->string('transaction_reference', 150);
            $table->string('proof_path');
            $table->string('proof_original_name');
            $table->string('proof_mime_type', 150)->nullable();
            $table->unsignedBigInteger('proof_file_size')->nullable();
            $table->text('notes')->nullable();
            $table->string('status', 40)->default('pending_verification');
            $table->string('submitted_by_type', 20);
            $table->foreignId('submitted_by_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('submitted_at');
            $table->string('provider_code', 80)->nullable();
            $table->string('provider_status', 80)->nullable();
            $table->text('provider_message')->nullable();
            $table->foreignId('verified_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('verified_at')->nullable();
            $table->text('verification_notes')->nullable();
            $table->foreignId('financial_account_id')->nullable()->constrained()->restrictOnDelete();
            $table->timestamp('payment_date')->nullable();
            $table->foreignId('loan_payment_id')->nullable()->unique()->constrained('loan_payments')->restrictOnDelete();
            $table->timestamps();

            $table->unique(['payment_channel', 'transaction_reference'], 'remote_payment_channel_reference_unique');
            $table->index(['branch_id', 'status', 'submitted_at']);
            $table->index(['loan_id', 'status']);
        });
    }

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