<?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->string('late_payment_penalty_type')->default('none');
            $table->decimal('late_payment_penalty_value', 18, 6)->default(0);
            $table->decimal('overdue_principal', 18, 2)->default(0);
            $table->decimal('overdue_interest', 18, 2)->default(0);
            $table->decimal('overdue_fees', 18, 2)->default(0);
            $table->decimal('overdue_penalties', 18, 2)->default(0);
            $table->decimal('total_overdue', 18, 2)->default(0);
            $table->unsignedInteger('days_overdue')->default(0);
            $table->string('arrears_bucket')->nullable();
            $table->timestamp('arrears_updated_at')->nullable();
            $table->index(['status', 'arrears_bucket']);
            $table->index(['branch_id', 'days_overdue']);
        });

        Schema::create('loan_penalty_charges', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('loan_id')->constrained()->restrictOnDelete();
            $table->foreignId('loan_installment_id')->constrained('loan_installments')->restrictOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->date('effective_date');
            $table->string('period_key');
            $table->string('penalty_type');
            $table->decimal('penalty_value', 18, 6);
            $table->decimal('base_amount', 18, 2)->default(0);
            $table->decimal('amount', 18, 2);
            $table->unsignedInteger('days_overdue');
            $table->string('source')->default('automated');
            $table->timestamps();

            $table->unique(['loan_installment_id', 'period_key'], 'penalty_installment_period_unique');
            $table->index(['loan_id', 'effective_date']);
            $table->index(['branch_id', 'effective_date']);
        });

        Schema::create('collection_follow_ups', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('loan_id')->constrained()->restrictOnDelete();
            $table->foreignId('customer_id')->constrained()->restrictOnDelete();
            $table->foreignId('branch_id')->constrained()->restrictOnDelete();
            $table->string('action_type');
            $table->dateTime('action_at');
            $table->text('notes')->nullable();
            $table->date('promise_date')->nullable();
            $table->foreignId('employee_id')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();

            $table->index(['loan_id', 'action_at']);
            $table->index(['branch_id', 'action_at']);
            $table->index(['customer_id', 'action_at']);
            $table->index(['action_type', 'promise_date']);
        });

        DB::table('loans')->orderBy('id')->chunkById(100, function ($loans): void {
            foreach ($loans as $loan) {
                $product = DB::table('loan_products')->where('id', $loan->loan_product_id)->first();
                if (! $product) {
                    continue;
                }

                DB::table('loans')->where('id', $loan->id)->update([
                    'late_payment_penalty_type' => $product->late_payment_penalty_type,
                    'late_payment_penalty_value' => $product->late_payment_penalty_value,
                ]);
            }
        });
    }

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

        Schema::table('loans', function (Blueprint $table): void {
            $table->dropIndex(['status', 'arrears_bucket']);
            $table->dropIndex(['branch_id', 'days_overdue']);
            $table->dropColumn([
                'late_payment_penalty_type', 'late_payment_penalty_value',
                'overdue_principal', 'overdue_interest', 'overdue_fees', 'overdue_penalties',
                'total_overdue', 'days_overdue', 'arrears_bucket', 'arrears_updated_at',
            ]);
        });
    }
};
