<?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('loans', function (Blueprint $table): void {
            $table->decimal('principal_outstanding', 18, 2)->nullable();
            $table->decimal('interest_outstanding', 18, 2)->nullable();
            $table->decimal('fees_outstanding', 18, 2)->nullable();
            $table->decimal('penalties_outstanding', 18, 2)->nullable();
            $table->decimal('total_outstanding', 18, 2)->nullable();
            $table->decimal('total_paid', 18, 2)->default(0);
            $table->timestamp('last_payment_at')->nullable();
            $table->timestamp('completed_at')->nullable();
        });

        Schema::table('loan_installments', function (Blueprint $table): void {
            $table->decimal('principal_paid', 18, 2)->default(0);
            $table->decimal('interest_paid', 18, 2)->default(0);
            $table->decimal('fees_paid', 18, 2)->default(0);
            $table->decimal('penalty_due', 18, 2)->default(0);
            $table->decimal('penalty_paid', 18, 2)->default(0);
        });

        Schema::create('loan_payments', function (Blueprint $table): void {
            $table->id();
            $table->string('receipt_number')->unique();
            $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()->constrained('financial_account_transactions')->restrictOnDelete();
            $table->decimal('amount', 18, 2);
            $table->decimal('principal_amount', 18, 2)->default(0);
            $table->decimal('interest_amount', 18, 2)->default(0);
            $table->decimal('fees_amount', 18, 2)->default(0);
            $table->decimal('penalties_amount', 18, 2)->default(0);
            $table->dateTime('payment_date');
            $table->string('method');
            $table->string('reference')->nullable();
            $table->text('notes')->nullable();
            $table->foreignId('received_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->index(['branch_id', 'payment_date']);
            $table->index(['loan_id', 'payment_date']);
            $table->index(['customer_id', 'payment_date']);
        });

        Schema::create('loan_payment_allocations', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('loan_payment_id')->constrained('loan_payments')->restrictOnDelete();
            $table->foreignId('loan_installment_id')->constrained('loan_installments')->restrictOnDelete();
            $table->decimal('principal', 18, 2)->default(0);
            $table->decimal('interest', 18, 2)->default(0);
            $table->decimal('fees', 18, 2)->default(0);
            $table->decimal('penalties', 18, 2)->default(0);
            $table->decimal('total', 18, 2);
            $table->timestamps();
            $table->unique(['loan_payment_id', 'loan_installment_id'], 'payment_installment_unique');
        });

        Schema::create('customer_statement_entries', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('customer_id')->constrained()->restrictOnDelete();
            $table->foreignId('loan_id')->constrained()->restrictOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->dateTime('entry_date');
            $table->string('reference');
            $table->string('type');
            $table->text('description');
            $table->decimal('debit', 18, 2)->default(0);
            $table->decimal('credit', 18, 2)->default(0);
            $table->decimal('running_balance', 18, 2);
            $table->nullableMorphs('related');
            $table->timestamps();
            $table->index(['customer_id', 'entry_date']);
            $table->index(['loan_id', 'entry_date']);
        });

        Schema::create('daily_collections', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->foreignId('financial_account_id')->constrained('financial_accounts')->restrictOnDelete();
            $table->date('collection_date');
            $table->decimal('amount', 18, 2)->default(0);
            $table->decimal('principal', 18, 2)->default(0);
            $table->decimal('interest', 18, 2)->default(0);
            $table->decimal('fees', 18, 2)->default(0);
            $table->decimal('penalties', 18, 2)->default(0);
            $table->unsignedInteger('payments_count')->default(0);
            $table->timestamps();
            $table->unique(['branch_id', 'financial_account_id', 'collection_date'], 'daily_collection_unique');
        });

        DB::table('loan_installments')->update([
            'principal_paid' => 0,
            'interest_paid' => 0,
            'fees_paid' => 0,
            'penalty_due' => 0,
            'penalty_paid' => 0,
        ]);

        DB::table('loans')->whereIn('status', ['active'])->orderBy('id')->chunkById(100, function ($loans): void {
            foreach ($loans as $loan) {
                DB::table('loans')->where('id', $loan->id)->update([
                    'principal_outstanding' => $loan->principal,
                    'interest_outstanding' => $loan->interest,
                    'fees_outstanding' => $loan->fees,
                    'penalties_outstanding' => 0,
                    'total_outstanding' => $loan->total_repayment,
                    'total_paid' => 0,
                ]);
            }
        });

        DB::table('loan_disbursements')->orderBy('id')->chunkById(100, function ($rows): void {
            foreach ($rows as $row) {
                $loan = DB::table('loans')->where('id', $row->loan_id)->first();
                if (! $loan) continue;
                DB::table('customer_statement_entries')->insert([
                    'customer_id' => $loan->customer_id,
                    'loan_id' => $loan->id,
                    'branch_id' => $loan->branch_id,
                    'entry_date' => $row->disbursement_date,
                    'reference' => $row->disbursement_number,
                    'type' => 'loan_disbursement',
                    'description' => 'Loan obligation '.$loan->loan_number,
                    'debit' => $loan->total_repayment,
                    'credit' => 0,
                    'running_balance' => $loan->total_repayment,
                    'related_type' => App\Models\LoanDisbursement::class,
                    'related_id' => $row->id,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('daily_collections');
        Schema::dropIfExists('customer_statement_entries');
        Schema::dropIfExists('loan_payment_allocations');
        Schema::dropIfExists('loan_payments');

        Schema::table('loan_installments', function (Blueprint $table): void {
            $table->dropColumn(['principal_paid','interest_paid','fees_paid','penalty_due','penalty_paid']);
        });
        Schema::table('loans', function (Blueprint $table): void {
            $table->dropColumn(['principal_outstanding','interest_outstanding','fees_outstanding','penalties_outstanding','total_outstanding','total_paid','last_payment_at','completed_at']);
        });
    }
};
