<?php

namespace App\Services\CreditScore;

use App\Models\Customer;
use App\Models\Loan;
use Carbon\CarbonImmutable;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
use InvalidArgumentException;

class CreditScoreInputBuilder
{
    public function build(Customer $customer, ?Loan $proposedLoan = null): array
    {
        $this->assertProposedLoanBelongsToCustomer($customer, $proposedLoan);

        $customerId = (int) $customer->getKey();
        $branchId = (int) $customer->branch_id;
        $today = CarbonImmutable::today();
        $graceDays = max(0, (int) config('credit_score.thresholds.repayment.grace_days', 3));
        $seriousArrearsDays = max(1, (int) config('credit_score.thresholds.repayment.serious_arrears_days', 30));

        $creditStatuses = ['active', 'completed', 'fully_paid', 'closed_through_top_up', 'written_off'];

        $loanSummary = DB::table('loans')
            ->where('customer_id', $customerId)
            ->where('branch_id', $branchId)
            ->whereIn('status', $creditStatuses)
            ->selectRaw(
                "COUNT(*) as total_loans,
                SUM(CASE WHEN status IN ('completed','fully_paid') THEN 1 ELSE 0 END) as completed_loans,
                SUM(CASE WHEN status = 'active' AND COALESCE(days_overdue, 0) = 0 AND COALESCE(total_overdue, 0) = 0 THEN 1 ELSE 0 END) as active_good_loans,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_loan_count,
                COALESCE(SUM(CASE WHEN status = 'active' THEN COALESCE(total_outstanding, total_repayment, 0) ELSE 0 END), 0) as active_total_outstanding,
                COALESCE(SUM(CASE WHEN status = 'active' THEN COALESCE(total_overdue, 0) ELSE 0 END), 0) as active_total_overdue,
                COALESCE(SUM(CASE WHEN status = 'active' THEN COALESCE(total_repayment, 0) ELSE 0 END), 0) as active_total_repayment,
                COALESCE(MAX(CASE WHEN status = 'active' THEN COALESCE(days_overdue, 0) ELSE 0 END), 0) as current_days_overdue,
                COALESCE(MAX(COALESCE(days_overdue, 0)), 0) as stored_max_days_overdue,
                SUM(CASE WHEN COALESCE(days_overdue, 0) >= ? THEN 1 ELSE 0 END) as serious_arrears_loans",
                [$seriousArrearsDays],
            )
            ->first();

        $installments = $this->installmentSummary($customerId, $branchId, $creditStatuses, $today, $graceDays);
        $latePenaltyCount = DB::table('loan_penalty_charges as penalty')
            ->join('loans as loan', 'loan.id', '=', 'penalty.loan_id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->count();

        $activeOutstanding = (float) ($loanSummary->active_total_outstanding ?? 0);
        $activeOverdue = (float) ($loanSummary->active_total_overdue ?? 0);
        $overdueRatio = $activeOutstanding > 0 ? min(1, $activeOverdue / $activeOutstanding) : 0.0;

        $writeOffSummary = DB::table('loan_write_offs')
            ->where('customer_id', $customerId)
            ->where('branch_id', $branchId)
            ->where('status', 'approved')
            ->selectRaw(
                'COUNT(*) as write_offs,
                SUM(CASE WHEN COALESCE(recoverable_balance, 0) > 0 THEN 1 ELSE 0 END) as unresolved_write_offs,
                COALESCE(SUM(write_off_amount), 0) as written_off_amount,
                COALESCE(SUM(recovered_amount), 0) as recovered_amount'
            )
            ->first();

        $writtenOffAmount = (float) ($writeOffSummary->written_off_amount ?? 0);
        $recoveredAmount = (float) ($writeOffSummary->recovered_amount ?? 0);
        $writeOffRecoveryRatio = $writtenOffAmount > 0
            ? min(1, max(0, $recoveredAmount / $writtenOffAmount))
            : 0.0;

        $restructurings = DB::table('loan_restructurings as restructuring')
            ->join('loans as loan', 'loan.id', '=', 'restructuring.loan_id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->where('restructuring.status', 'approved')
            ->count();

        $monthlyDebtService = (float) DB::table('loan_installments as installment')
            ->join('loans as loan', 'loan.id', '=', 'installment.loan_id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->where('loan.status', 'active')
            ->where('installment.is_active', true)
            ->where('installment.remaining', '>', 0)
            ->whereDate('installment.due_date', '<=', $today->addDays(30)->toDateString())
            ->sum('installment.remaining');

        $proposedProduct = $proposedLoan?->relationLoaded('product')
            ? $proposedLoan->product
            : $proposedLoan?->product()->first();
        $proposedMonthlyInstallment = $this->estimateProposedMonthlyInstallment($proposedLoan, $proposedProduct);

        $collateralContext = $this->collateralContext($customerId, $branchId, $proposedLoan, $proposedProduct);
        $guarantorRequired = $this->guarantorRequired($customerId, $branchId, $proposedProduct);

        $conduct = DB::table('blacklist_records')
            ->where('customer_id', $customerId)
            ->where('status', 'active')
            ->selectRaw(
                "SUM(CASE WHEN type = 'blacklist' THEN 1 ELSE 0 END) as active_blacklist,
                SUM(CASE WHEN type = 'watchlist' THEN 1 ELSE 0 END) as active_watchlist"
            )
            ->first();

        $seriousCollectionEscalations = DB::table('collection_follow_ups')
            ->where('customer_id', $customerId)
            ->where('branch_id', $branchId)
            ->where('action_type', 'demand_notice')
            ->count();

        $maxDaysOverdue = max(
            (int) ($loanSummary->stored_max_days_overdue ?? 0),
            (int) ($installments->max_paid_days_overdue ?? 0),
        );

        return [
            'repayment' => [
                'total_installments' => (int) ($installments->total_installments ?? 0),
                'completed_installments' => (int) ($installments->completed_installments ?? 0),
                'on_time_installments' => (int) ($installments->on_time_installments ?? 0),
                'within_grace_installments' => (int) ($installments->within_grace_installments ?? 0),
                'missed_installments' => (int) ($installments->missed_installments ?? 0),
                'current_days_overdue' => (int) ($loanSummary->current_days_overdue ?? 0),
                'max_days_overdue' => $maxDaysOverdue,
                'overdue_ratio' => round($overdueRatio, 6),
                'late_penalty_count' => (int) $latePenaltyCount,
                'serious_arrears_loans' => (int) ($loanSummary->serious_arrears_loans ?? 0),
            ],
            'loan_history' => [
                'total_loans' => (int) ($loanSummary->total_loans ?? 0),
                'completed_loans' => (int) ($loanSummary->completed_loans ?? 0),
                'active_good_loans' => (int) ($loanSummary->active_good_loans ?? 0),
                'write_offs' => (int) ($writeOffSummary->write_offs ?? 0),
                'unresolved_write_offs' => (int) ($writeOffSummary->unresolved_write_offs ?? 0),
                'restructurings' => (int) $restructurings,
                'write_off_recovery_ratio' => round($writeOffRecoveryRatio, 6),
            ],
            'affordability' => [
                'monthly_income' => (float) ($customer->monthly_income ?? 0),
                'monthly_debt_service' => round($monthlyDebtService, 2),
                'proposed_monthly_installment' => round($proposedMonthlyInstallment, 2),
                'active_total_outstanding' => round($activeOutstanding, 2),
                'active_loan_count' => (int) ($loanSummary->active_loan_count ?? 0),
            ],
            'collateral' => $collateralContext,
            'profile' => [
                'occupation_or_business_present' => $this->hasValue($customer->occupation) || $this->hasValue($customer->employer_business),
                'monthly_income_present' => (float) ($customer->monthly_income ?? 0) > 0,
                'address_present' => DB::table('customer_addresses')->where('customer_id', $customerId)->exists(),
                'primary_next_of_kin_present' => DB::table('next_of_kin')->where('customer_id', $customerId)->where('is_primary', true)->exists(),
                'required_documents_complete' => DB::table('customer_documents')->where('customer_id', $customerId)->exists(),
                'guarantor_required' => $guarantorRequired,
                'active_guarantor_present' => ! $guarantorRequired || DB::table('guarantors')->where('customer_id', $customerId)->where('status', 'active')->exists(),
            ],
            'conduct' => [
                'active_blacklist' => (int) ($conduct->active_blacklist ?? 0) > 0,
                'active_watchlist' => (int) ($conduct->active_watchlist ?? 0) > 0,
                'serious_collection_escalations' => (int) $seriousCollectionEscalations,
                'unresolved_control_flags' => 0,
            ],
        ];
    }

    private function installmentSummary(
        int $customerId,
        int $branchId,
        array $creditStatuses,
        CarbonImmutable $today,
        int $graceDays,
    ): object {
        $paymentCompletion = DB::table('loan_payment_allocations as allocation')
            ->join('loan_payments as payment', 'payment.id', '=', 'allocation.loan_payment_id')
            ->selectRaw('allocation.loan_installment_id, MAX(payment.payment_date) as final_payment_at')
            ->groupBy('allocation.loan_installment_id');

        $daysLate = $this->daysBetweenExpression('payment_completion.final_payment_at', 'installment.due_date');

        return DB::table('loan_installments as installment')
            ->join('loans as loan', 'loan.id', '=', 'installment.loan_id')
            ->leftJoinSub($paymentCompletion, 'payment_completion', function ($join): void {
                $join->on('payment_completion.loan_installment_id', '=', 'installment.id');
            })
            ->leftJoin('loan_installment_excuses as excuse', 'excuse.loan_installment_id', '=', 'installment.id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->whereIn('loan.status', $creditStatuses)
            ->where('installment.is_active', true)
            ->where(function (Builder $query) use ($today): void {
                $query->whereDate('installment.due_date', '<=', $today->toDateString())
                    ->orWhere('installment.remaining', '<=', 0);
            })
            ->selectRaw(
                "COUNT(DISTINCT installment.id) as total_installments,
                COUNT(DISTINCT CASE WHEN installment.remaining <= 0 THEN installment.id END) as completed_installments,
                COUNT(DISTINCT CASE WHEN installment.remaining <= 0 AND payment_completion.final_payment_at IS NOT NULL AND {$daysLate} <= 0 THEN installment.id END) as on_time_installments,
                COUNT(DISTINCT CASE WHEN installment.remaining <= 0 AND payment_completion.final_payment_at IS NOT NULL AND {$daysLate} > 0 AND {$daysLate} <= ? THEN installment.id END) as within_grace_installments,
                COUNT(DISTINCT CASE WHEN installment.due_date < ? AND installment.remaining > 0 AND excuse.id IS NULL THEN installment.id END) as missed_installments,
                COALESCE(MAX(CASE WHEN installment.remaining <= 0 AND payment_completion.final_payment_at IS NOT NULL AND {$daysLate} > 0 THEN {$daysLate} ELSE 0 END), 0) as max_paid_days_overdue",
                [$graceDays, $today->toDateString()],
            )
            ->first() ?? (object) [];
    }

    private function collateralContext(int $customerId, int $branchId, ?Loan $proposedLoan, $proposedProduct): array
    {
        $activeRequirement = DB::table('loans as loan')
            ->join('loan_products as product', 'product.id', '=', 'loan.loan_product_id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->where('loan.status', 'active')
            ->selectRaw(
                'MAX(CASE WHEN product.collateral_required = 1 THEN 1 ELSE 0 END) as collateral_required,
                COALESCE(SUM(CASE WHEN product.collateral_required = 1 THEN loan.principal ELSE 0 END), 0) as relevant_principal'
            )
            ->first();

        $proposedRequiresCollateral = (bool) ($proposedProduct?->collateral_required ?? false);
        $required = (int) ($activeRequirement->collateral_required ?? 0) > 0 || $proposedRequiresCollateral;
        $relevantPrincipal = (float) ($activeRequirement->relevant_principal ?? 0)
            + ($proposedRequiresCollateral ? max(0, (float) ($proposedLoan?->principal ?? 0)) : 0);

        if (! $required) {
            return [
                'collateral_required' => false,
                'relevant_principal' => 0,
                'accepted_collateral_value' => 0,
                'valid_collateral' => true,
            ];
        }

        $eligibleCollateralIds = DB::table('loan_collateral as pivot')
            ->join('loans as loan', 'loan.id', '=', 'pivot.loan_id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->where(function (Builder $query) use ($proposedLoan): void {
                $query->where('loan.status', 'active');
                if ($proposedLoan?->exists) {
                    $query->orWhere('loan.id', $proposedLoan->getKey());
                }
            })
            ->select('pivot.collateral_id')
            ->distinct();

        $collateral = DB::table('collaterals')
            ->whereIn('id', $eligibleCollateralIds)
            ->selectRaw(
                "COUNT(*) as collateral_count,
                COALESCE(SUM(COALESCE(accepted_value, 0)), 0) as accepted_value,
                SUM(CASE WHEN status IN ('accepted','in_custody') THEN 1 ELSE 0 END) as valid_count"
            )
            ->first();

        $collateralCount = (int) ($collateral->collateral_count ?? 0);
        $validCount = (int) ($collateral->valid_count ?? 0);

        return [
            'collateral_required' => true,
            'relevant_principal' => round($relevantPrincipal, 2),
            'accepted_collateral_value' => round((float) ($collateral->accepted_value ?? 0), 2),
            'valid_collateral' => $collateralCount > 0 && $validCount === $collateralCount,
        ];
    }

    private function guarantorRequired(int $customerId, int $branchId, $proposedProduct): bool
    {
        if ((bool) ($proposedProduct?->guarantor_required ?? false)) {
            return true;
        }

        return DB::table('loans as loan')
            ->join('loan_products as product', 'product.id', '=', 'loan.loan_product_id')
            ->where('loan.customer_id', $customerId)
            ->where('loan.branch_id', $branchId)
            ->where('loan.status', 'active')
            ->where('product.guarantor_required', true)
            ->exists();
    }

    private function estimateProposedMonthlyInstallment(?Loan $loan, $product): float
    {
        if (! $loan) {
            return 0.0;
        }

        if ($loan->exists) {
            $scheduled = (float) DB::table('loan_installments')
                ->where('loan_id', $loan->getKey())
                ->where('is_active', true)
                ->where('remaining', '>', 0)
                ->whereDate('due_date', '<=', CarbonImmutable::today()->addDays(30)->toDateString())
                ->sum('remaining');

            if ($scheduled > 0) {
                return $scheduled;
            }
        }

        $totalRepayment = max(0, (float) ($loan->total_repayment ?? 0));
        if ($totalRepayment <= 0 || ! $product) {
            return 0.0;
        }

        $duration = max(1, (int) ($product->duration ?? 1));
        $months = match ((string) ($product->duration_unit ?? 'months')) {
            'days' => max(1 / 30, $duration / 30),
            'weeks' => max(7 / 30, ($duration * 7) / 30),
            'years' => $duration * 12,
            default => $duration,
        };

        return $totalRepayment / max($months, 1 / 30);
    }

    private function daysBetweenExpression(string $laterColumn, string $earlierColumn): string
    {
        return match (DB::getDriverName()) {
            'sqlite' => "CAST(julianday(date({$laterColumn})) - julianday(date({$earlierColumn})) AS INTEGER)",
            default => "DATEDIFF(DATE({$laterColumn}), DATE({$earlierColumn}))",
        };
    }

    private function hasValue(mixed $value): bool
    {
        return is_string($value) ? trim($value) !== '' : $value !== null;
    }

    private function assertProposedLoanBelongsToCustomer(Customer $customer, ?Loan $loan): void
    {
        if (! $loan) {
            return;
        }

        if ((int) $loan->customer_id !== (int) $customer->getKey() || (int) $loan->branch_id !== (int) $customer->branch_id) {
            throw new InvalidArgumentException('The proposed loan must belong to the scored customer and branch.');
        }
    }
}
