<?php

namespace App\Services\CreditScore;

use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;

class CreditScorePortfolioReportService
{
    public function generate(array $branchIds): array
    {
        $branchIds = array_values(array_unique(array_filter(array_map('intval', $branchIds), fn (int $id) => $id > 0)));
        if ($branchIds === []) {
            throw ValidationException::withMessages(['branch_ids' => 'At least one accessible branch is required.']);
        }

        $version = (string) config('credit_score.version', 'v1');
        $staleCutoff = now()->subHours((int) config('credit_score.operations.stale_after_hours', 24));

        $customerSummary = DB::table('customers')
            ->leftJoin('customer_risk_profiles as risk', 'risk.customer_id', '=', 'customers.id')
            ->whereIn('customers.branch_id', $branchIds)
            ->selectRaw('COUNT(customers.id) as total_customers')
            ->selectRaw('SUM(CASE WHEN risk.score IS NOT NULL THEN 1 ELSE 0 END) as scored_customers')
            ->selectRaw('SUM(CASE WHEN risk.id IS NULL OR risk.calculated_at IS NULL OR risk.calculated_at < ? OR risk.score_version IS NULL OR risk.score_version <> ? THEN 1 ELSE 0 END) as needs_refresh', [$staleCutoff, $version])
            ->selectRaw('SUM(CASE WHEN risk.manual_review_required = 1 THEN 1 ELSE 0 END) as manual_review_customers')
            ->selectRaw('SUM(CASE WHEN risk.thin_file = 1 THEN 1 ELSE 0 END) as thin_file_customers')
            ->selectRaw('AVG(risk.score) as average_score')
            ->first();

        $exposureSummary = DB::table('loans')
            ->whereIn('branch_id', $branchIds)
            ->where('status', 'active')
            ->selectRaw('COUNT(id) as active_loans')
            ->selectRaw('COALESCE(SUM(total_outstanding), 0) as total_outstanding')
            ->selectRaw('COALESCE(SUM(total_overdue), 0) as total_overdue')
            ->first();

        $customerBands = DB::table('customers')
            ->leftJoin('customer_risk_profiles as risk', 'risk.customer_id', '=', 'customers.id')
            ->whereIn('customers.branch_id', $branchIds)
            ->groupBy('risk.score_band')
            ->selectRaw('risk.score_band as score_band')
            ->selectRaw('COUNT(customers.id) as customer_count')
            ->selectRaw('AVG(risk.score) as average_score')
            ->selectRaw('AVG(risk.confidence) as average_confidence')
            ->selectRaw('SUM(CASE WHEN risk.manual_review_required = 1 THEN 1 ELSE 0 END) as manual_review_count')
            ->selectRaw('SUM(CASE WHEN risk.thin_file = 1 THEN 1 ELSE 0 END) as thin_file_count')
            ->get()
            ->keyBy(fn ($row) => $row->score_band ?: 'unscored');

        $exposureBands = DB::table('loans')
            ->join('customers', 'customers.id', '=', 'loans.customer_id')
            ->leftJoin('customer_risk_profiles as risk', 'risk.customer_id', '=', 'customers.id')
            ->whereIn('loans.branch_id', $branchIds)
            ->where('loans.status', 'active')
            ->groupBy('risk.score_band')
            ->selectRaw('risk.score_band as score_band')
            ->selectRaw('COUNT(loans.id) as active_loans')
            ->selectRaw('COALESCE(SUM(loans.total_outstanding), 0) as total_outstanding')
            ->selectRaw('COALESCE(SUM(loans.total_overdue), 0) as total_overdue')
            ->get()
            ->keyBy(fn ($row) => $row->score_band ?: 'unscored');

        $bandDefinitions = collect(config('credit_score.bands', []))
            ->map(fn (array $band, string $key) => ['key' => $key, 'label' => (string) ($band['label'] ?? str($key)->headline())])
            ->values()
            ->push(['key' => 'unscored', 'label' => 'Unscored']);

        $bands = $bandDefinitions->map(function (array $definition) use ($customerBands, $exposureBands): array {
            $customers = $customerBands->get($definition['key']);
            $exposure = $exposureBands->get($definition['key']);
            $outstanding = (float) ($exposure->total_outstanding ?? 0);
            $overdue = (float) ($exposure->total_overdue ?? 0);

            return [
                'key' => $definition['key'],
                'label' => $definition['label'],
                'customers' => (int) ($customers->customer_count ?? 0),
                'average_score' => $customers?->average_score !== null ? round((float) $customers->average_score, 1) : null,
                'average_confidence' => $customers?->average_confidence !== null ? round((float) $customers->average_confidence, 1) : null,
                'manual_review' => (int) ($customers->manual_review_count ?? 0),
                'thin_file' => (int) ($customers->thin_file_count ?? 0),
                'active_loans' => (int) ($exposure->active_loans ?? 0),
                'outstanding' => round($outstanding, 2),
                'overdue' => round($overdue, 2),
                'arrears_rate' => $outstanding > 0 ? round(($overdue / $outstanding) * 100, 2) : 0.0,
            ];
        })->all();

        $branchCustomers = DB::table('branches')
            ->leftJoin('customers', 'customers.branch_id', '=', 'branches.id')
            ->leftJoin('customer_risk_profiles as risk', 'risk.customer_id', '=', 'customers.id')
            ->whereIn('branches.id', $branchIds)
            ->groupBy('branches.id', 'branches.name')
            ->orderBy('branches.name')
            ->selectRaw('branches.id as branch_id, branches.name as branch_name')
            ->selectRaw('COUNT(customers.id) as customers')
            ->selectRaw('SUM(CASE WHEN risk.score IS NOT NULL THEN 1 ELSE 0 END) as scored_customers')
            ->selectRaw('AVG(risk.score) as average_score')
            ->selectRaw('SUM(CASE WHEN risk.manual_review_required = 1 THEN 1 ELSE 0 END) as manual_review')
            ->get()
            ->keyBy('branch_id');

        $branchExposure = DB::table('branches')
            ->leftJoin('loans', function ($join): void {
                $join->on('loans.branch_id', '=', 'branches.id')->where('loans.status', '=', 'active');
            })
            ->whereIn('branches.id', $branchIds)
            ->groupBy('branches.id')
            ->selectRaw('branches.id as branch_id')
            ->selectRaw('COUNT(loans.id) as active_loans')
            ->selectRaw('COALESCE(SUM(loans.total_outstanding), 0) as total_outstanding')
            ->selectRaw('COALESCE(SUM(loans.total_overdue), 0) as total_overdue')
            ->get()
            ->keyBy('branch_id');

        $branches = $branchCustomers->map(function ($row) use ($branchExposure): array {
            $exposure = $branchExposure->get($row->branch_id);
            $outstanding = (float) ($exposure->total_outstanding ?? 0);
            $overdue = (float) ($exposure->total_overdue ?? 0);

            return [
                'branch_id' => (int) $row->branch_id,
                'branch_name' => (string) $row->branch_name,
                'customers' => (int) $row->customers,
                'scored_customers' => (int) $row->scored_customers,
                'average_score' => $row->average_score !== null ? round((float) $row->average_score, 1) : null,
                'manual_review' => (int) $row->manual_review,
                'active_loans' => (int) ($exposure->active_loans ?? 0),
                'outstanding' => round($outstanding, 2),
                'overdue' => round($overdue, 2),
                'arrears_rate' => $outstanding > 0 ? round(($overdue / $outstanding) * 100, 2) : 0.0,
            ];
        })->values()->all();

        $totalCustomers = (int) ($customerSummary->total_customers ?? 0);
        $scoredCustomers = (int) ($customerSummary->scored_customers ?? 0);
        $totalOutstanding = (float) ($exposureSummary->total_outstanding ?? 0);
        $totalOverdue = (float) ($exposureSummary->total_overdue ?? 0);

        return [
            'generated_at' => now(),
            'score_version' => $version,
            'stale_cutoff' => $staleCutoff,
            'summary' => [
                'total_customers' => $totalCustomers,
                'scored_customers' => $scoredCustomers,
                'unscored_customers' => max(0, $totalCustomers - $scoredCustomers),
                'coverage_rate' => $totalCustomers > 0 ? round(($scoredCustomers / $totalCustomers) * 100, 2) : 0.0,
                'average_score' => $customerSummary?->average_score !== null ? round((float) $customerSummary->average_score, 1) : null,
                'needs_refresh' => (int) ($customerSummary->needs_refresh ?? 0),
                'manual_review' => (int) ($customerSummary->manual_review_customers ?? 0),
                'thin_file' => (int) ($customerSummary->thin_file_customers ?? 0),
                'active_loans' => (int) ($exposureSummary->active_loans ?? 0),
                'total_outstanding' => round($totalOutstanding, 2),
                'total_overdue' => round($totalOverdue, 2),
                'arrears_rate' => $totalOutstanding > 0 ? round(($totalOverdue / $totalOutstanding) * 100, 2) : 0.0,
            ],
            'bands' => $bands,
            'branches' => $branches,
        ];
    }
}
