<?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('loan_write_offs', function (Blueprint $table): void {
            $table->id();
            $table->string('write_off_number')->unique();
            $table->foreignId('loan_id')->constrained()->restrictOnDelete();
            $table->foreignId('customer_id')->constrained()->restrictOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->decimal('original_principal', 18, 2);
            $table->decimal('original_total_repayment', 18, 2);
            $table->decimal('principal_outstanding', 18, 2);
            $table->decimal('interest_outstanding', 18, 2);
            $table->decimal('fees_outstanding', 18, 2);
            $table->decimal('penalties_outstanding', 18, 2);
            $table->decimal('write_off_amount', 18, 2);
            $table->decimal('recovered_amount', 18, 2)->default(0);
            $table->decimal('recoverable_balance', 18, 2);
            $table->text('reason');
            $table->string('status')->default('pending');
            $table->string('accounting_journal_number')->nullable();
            $table->foreignId('requested_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('requested_at');
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('approved_at')->nullable();
            $table->foreignId('rejected_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('rejected_at')->nullable();
            $table->text('decision_comments')->nullable();
            $table->timestamps();

            $table->index(['loan_id', 'status']);
            $table->index(['branch_id', 'status']);
            $table->index(['branch_id', 'approved_at']);
        });

        Schema::create('loan_write_off_documents', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('loan_write_off_id')->constrained('loan_write_offs')->restrictOnDelete();
            $table->string('document_type')->default('Supporting document');
            $table->string('original_name');
            $table->string('file_path');
            $table->string('mime_type')->nullable();
            $table->unsignedBigInteger('file_size')->nullable();
            $table->text('notes')->nullable();
            $table->foreignId('uploaded_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();

            $table->index(['loan_write_off_id', 'created_at']);
        });

        Schema::create('loan_write_off_recoveries', function (Blueprint $table): void {
            $table->id();
            $table->string('recovery_number')->unique();
            $table->foreignId('loan_write_off_id')->constrained('loan_write_offs')->restrictOnDelete();
            $table->foreignId('loan_id')->constrained()->restrictOnDelete();
            $table->foreignId('customer_id')->constrained()->restrictOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->foreignId('financial_account_id')->constrained('financial_accounts')->restrictOnDelete();
            $table->foreignId('financial_account_transaction_id')->nullable();
            $table->foreign('financial_account_transaction_id', 'loan_wor_fin_tx_fk')
                ->references('id')
                ->on('financial_account_transactions')
                ->restrictOnDelete();
            $table->decimal('amount', 18, 2);
            $table->dateTime('recovery_date');
            $table->string('method');
            $table->string('reference')->nullable();
            $table->text('notes')->nullable();
            $table->foreignId('received_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();

            $table->index(['loan_write_off_id', 'recovery_date']);
            $table->index(['loan_id', 'recovery_date']);
            $table->index(['branch_id', 'recovery_date']);
        });

        Schema::table('loans', function (Blueprint $table): void {
            $table->timestamp('written_off_at')->nullable()->after('completed_at');
            $table->foreignId('written_off_by')->nullable()->after('written_off_at')->constrained('users')->nullOnDelete();
        });

        Schema::table('daily_collections', function (Blueprint $table): void {
            $table->decimal('write_off_recoveries', 18, 2)->default(0)->after('penalties');
            $table->unsignedInteger('write_off_recoveries_count')->default(0)->after('write_off_recoveries');
        });
    }

    public function down(): void
    {
        Schema::table('daily_collections', function (Blueprint $table): void {
            $table->dropColumn(['write_off_recoveries', 'write_off_recoveries_count']);
        });

        Schema::table('loans', function (Blueprint $table): void {
            $table->dropForeign(['written_off_by']);
            $table->dropColumn(['written_off_at', 'written_off_by']);
        });

        Schema::dropIfExists('loan_write_off_recoveries');
        Schema::dropIfExists('loan_write_off_documents');
        Schema::dropIfExists('loan_write_offs');
    }
};
