<?php

declare(strict_types=1);

namespace App\Services\Metrics;

use App\Models\CompanyCollectorGoal;
use App\Models\Credit;
use App\Models\Installment;
use App\Models\Payment;
use App\Models\User;
use App\Services\Dashboard\DashboardCacheService;
use App\ValueObjects\Money;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

/**
 * Servicio de métricas de cobranza.
 *
 * Métricas específicas para:
 * - Supervisores (rendimiento de equipo)
 * - Collectors (operación diaria)
 *
 * Enfocado en productividad y cumplimiento de metas.
 */
class CollectionMetricsService
{
    public function __construct(
        private DashboardCacheService $cache
    ) {}

    /**
     * Sufijo determinista de cache según el scope de collectors, para que dos
     * supervisores con equipos distintos no compartan la misma entrada.
     *   null → '' (company-wide) · [] → '_none' · [4,3] → '_c3.4'
     *
     * @param  int[]|null  $collectorIds
     */
    private function scopeSuffix(?array $collectorIds): string
    {
        if ($collectorIds === null) {
            return '';
        }

        if ($collectorIds === []) {
            return '_none';
        }

        $ids = array_values(array_unique(array_map('intval', $collectorIds)));
        sort($ids);

        return '_c'.implode('.', $ids);
    }

    /*=========================================================
    | MÉTRICAS PARA SUPERVISORES
    =========================================================*/

    /**
     * Resumen del equipo de collectors para un supervisor.
     */
    public function getTeamSummary(int $companyId, int $supervisorId, ?array $collectorIds = null): array
    {
        $cacheKey = "collection.team_summary_{$supervisorId}".$this->scopeSuffix($collectorIds);

        return $this->cache->remember($companyId, $cacheKey, function () use ($companyId, $collectorIds) {
            $today = Carbon::today();

            // Visibilidad supervisor→cobrador (#71): null = todos; []/array = scoped.
            // Helper para aplicar el filtro a la columna del cobrador del crédito.
            $scopeCredit = fn ($q) => $collectorIds === null ? $q : $q->whereIn('collector_user_id', $collectorIds);

            // Collectors del equipo visible (no toda la empresa para un supervisor scoped).
            $collectors = User::where('company_id', $companyId)
                ->whereHas('roles', fn ($q) => $q->where('name', 'collector'))
                ->when($collectorIds !== null, fn ($q) => $q->whereIn('id', $collectorIds))
                ->get();

            $activeCollectors = $collectors->count();

            // Cobradores con al menos un pago HOY, atribuido por el COBRADOR DEL CRÉDITO
            // (no por quién registró el pago). Así el numerador queda alineado con
            // collected_today y con el divisor de average_per_collector. Scoped (#71).
            $collectorsWithPaymentsToday = (int) DB::table('payments')
                ->join('credits', 'payments.credit_id', '=', 'credits.id')
                ->where('payments.company_id', $companyId)
                ->where('payments.payment_date', $today->toDateString())
                ->where('payments.voided', false)
                ->where('payments.payment_method', '!=', 'reversal')
                ->whereNotNull('credits.collector_user_id')
                ->whereNotExists(function ($sub) {
                    $sub->select(DB::raw(1))
                        ->from('credits as children')
                        ->whereColumn('children.parent_credit_id', 'credits.id');
                })
                ->when($collectorIds !== null, fn ($q) => $q->whereIn('credits.collector_user_id', $collectorIds))
                ->distinct()
                ->count('credits.collector_user_id');

            // Collectors con al menos un crédito ACTIVO asignado (= "activo"). Definición
            // unificada con la PWA (DashboardController::supervisorDashboard) — issue #77.
            $collectorsWithActiveCredits = Credit::where('company_id', $companyId)
                ->whereIn('status', Credit::ACTIVE_STATUSES)
                ->whereDoesntHave('children')
                ->whereNotNull('collector_user_id')
                ->when($collectorIds !== null, fn ($q) => $q->whereIn('collector_user_id', $collectorIds))
                ->distinct()
                ->count('collector_user_id');

            // Total cobrado hoy por el equipo
            $collectedToday = Payment::where('company_id', $companyId)
                ->whereHas('credit', fn ($q) => $scopeCredit($q->leafCredits()))
                ->where('payment_date', $today->toDateString())
                ->collections()
                ->sum('amount');

            // Total cobrado esta semana
            $weekStart = $today->copy()->startOfWeek();
            $collectedWeek = Payment::where('company_id', $companyId)
                ->whereHas('credit', fn ($q) => $scopeCredit($q->leafCredits()))
                ->whereBetween('payment_date', [$weekStart->toDateString(), $today->toDateString()])
                ->collections()
                ->sum('amount');

            // Pagos realizados hoy
            $paymentsCountToday = Payment::where('company_id', $companyId)
                ->whereHas('credit', fn ($q) => $scopeCredit($q->leafCredits()))
                ->where('payment_date', $today->toDateString())
                ->collections()
                ->count();

            return [
                'total_collectors' => $activeCollectors,
                'collectors_with_active_credits' => $collectorsWithActiveCredits,
                'active_collectors_today' => $collectorsWithPaymentsToday,
                'inactive_collectors_today' => $activeCollectors - $collectorsWithPaymentsToday,
                'collected_today' => Money::of($collectedToday)->value(),
                'collected_week' => Money::of($collectedWeek)->value(),
                'payments_count_today' => $paymentsCountToday,
                'average_per_collector' => $collectorsWithPaymentsToday > 0
                    ? Money::of($collectedToday / $collectorsWithPaymentsToday)->value()
                    : 0,
            ];
        }, 'realtime');
    }

    /**
     * Rendimiento de collectors hoy (para tabla de supervisor).
     *
     * @return Collection<int, array{
     *   collector_id: int,
     *   collector_name: string,
     *   collected_today: float,
     *   payments_count: int,
     *   goal: float|null,
     *   goal_percentage: float,
     *   status: string
     * }>
     */
    public function getCollectorPerformanceToday(int $companyId): Collection
    {
        return $this->cache->remember($companyId, 'collection.performance_today', function () use ($companyId) {
            $today = Carbon::today()->toDateString();

            // ── 1. Lista de collectors (1 query) ───────────────────────────────
            $collectors = User::where('company_id', $companyId)
                ->whereHas('roles', fn ($q) => $q->where('name', 'collector'))
                ->pluck('name', 'id'); // [id => name]

            // ── 2. Totales del día por collector (1 query, sin N+1) ────────────
            // Agrupa SUM(amount) y COUNT(*) por collector en una sola pasada SQL.
            $totals = DB::table('payments')
                ->join('credits', 'payments.credit_id', '=', 'credits.id')
                ->where('payments.company_id', $companyId)
                ->where('payments.payment_date', $today)
                ->where('payments.voided', false)
                ->where('payments.payment_method', '!=', 'reversal')
                ->whereIn('credits.status', Credit::ACTIVE_STATUSES)
                ->whereNotExists(function ($sub) {
                    $sub->select(DB::raw(1))
                        ->from('credits as children')
                        ->whereColumn('children.parent_credit_id', 'credits.id');
                })
                ->whereIn('credits.collector_user_id', $collectors->keys())
                ->select([
                    'credits.collector_user_id',
                    DB::raw('COALESCE(SUM(payments.amount), 0) as collected_today'),
                    DB::raw('COUNT(*) as payments_count'),
                ])
                ->groupBy('credits.collector_user_id')
                ->get()
                ->keyBy('collector_user_id');

            // ── 3. Metas diarias por collector (1 query) ──────────────────────
            $goals = CompanyCollectorGoal::forCompany($companyId); // [collector_id => daily_goal]

            // ── 4. Combinar sin loops de queries ───────────────────────────────
            $result = $collectors->map(function (string $name, int $collectorId) use ($totals, $goals) {
                $row = $totals->get($collectorId);
                $collectedToday = $row ? (float) $row->collected_today : 0.0;
                $paymentsCount = $row ? (int) $row->payments_count : 0;
                $goal = $goals[$collectorId] ?? null;

                $goalPercentage = ($goal && $goal > 0)
                    ? (int) min(round(($collectedToday / $goal) * 100), 999)
                    : 0;

                $status = match (true) {
                    $goal === null => 'no_goal',
                    $goalPercentage >= 100 => 'goal_met',
                    $goalPercentage >= 50 => 'on_track',
                    default => 'goal_not_met',
                };

                return [
                    'collector_id' => $collectorId,
                    'collector_name' => $name,
                    'collected_today' => Money::of($collectedToday)->value(),
                    'payments_count' => $paymentsCount,
                    'goal' => $goal,
                    'goal_percentage' => $goalPercentage,
                    'status' => $status,
                ];
            })->values()->sortByDesc('collected_today')->values();

            return $result;
        }, 'realtime');
    }

    /**
     * Tendencia de cobros del equipo por día (última semana).
     *
     * @return Collection<int, array{date: string, amount: float, count: int}>
     */
    public function getTeamCollectionTrend(int $companyId, int $days = 7, ?array $collectorIds = null): Collection
    {
        $cacheKey = "collection.team_trend_{$days}".$this->scopeSuffix($collectorIds);

        return $this->cache->remember($companyId, $cacheKey, function () use ($companyId, $days, $collectorIds) {
            $from = Carbon::now()->subDays($days - 1)->startOfDay();
            $to = Carbon::now()->endOfDay();

            $payments = Payment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorIds) {
                    $q->leafCredits();
                    if ($collectorIds !== null) {
                        $q->whereIn('collector_user_id', $collectorIds);
                    }
                })
                ->collections()
                ->whereBetween('payment_date', [$from->toDateString(), $to->toDateString()])
                ->select(
                    DB::raw('DATE(payment_date) as date'),
                    DB::raw('SUM(amount) as total'),
                    DB::raw('COUNT(*) as count')
                )
                ->groupBy('date')
                ->get()
                ->keyBy('date');

            $result = collect();
            $cursor = $from->copy();

            while ($cursor->lessThanOrEqualTo($to)) {
                $dateKey = $cursor->toDateString();
                $data = $payments->get($dateKey);

                $result->push([
                    'date' => $dateKey,
                    'amount' => Money::of($data?->total ?? 0)->value(),
                    'count' => $data?->count ?? 0,
                ]);

                $cursor->addDay();
            }

            return $result;
        }, 'frequent');
    }

    /**
     * Alertas de rendimiento para supervisores.
     *
     * @return Collection<int, array{
     *   type: string,
     *   severity: string,
     *   collector_id: int|null,
     *   collector_name: string|null,
     *   message: string,
     *   data: array
     * }>
     */
    public function getPerformanceAlerts(int $companyId): Collection
    {
        return $this->cache->remember($companyId, 'collection.alerts', function () use ($companyId) {
            $alerts = collect();
            $today = Carbon::today();

            // Obtener collectors (1 query)
            $collectors = User::where('company_id', $companyId)
                ->whereHas('roles', fn ($q) => $q->where('name', 'collector'))
                ->get(['id', 'name']);

            if ($collectors->isEmpty()) {
                return $alerts;
            }

            $collectorIds = $collectors->pluck('id')->all();

            // Última fecha de pago por cobrador (1 query agregada — evita N+1).
            $lastPaymentByCollector = DB::table('payments')
                ->join('credits', 'payments.credit_id', '=', 'credits.id')
                ->where('payments.company_id', $companyId)
                ->where('payments.voided', false)
                ->where('payments.payment_method', '!=', 'reversal')
                ->whereIn('credits.collector_user_id', $collectorIds)
                ->groupBy('credits.collector_user_id')
                ->selectRaw('credits.collector_user_id as collector_id, MAX(payments.payment_date) as last_date')
                ->pluck('last_date', 'collector_id');

            // Cartera activa y morosa por cobrador (1 query agregada — evita N+1).
            $creditStats = DB::table('credits')
                ->where('company_id', $companyId)
                ->whereIn('status', Credit::ACTIVE_STATUSES)
                ->whereNotNull('collector_user_id')
                ->whereNotExists(function ($sub) {
                    $sub->select(DB::raw(1))
                        ->from('credits as children')
                        ->whereColumn('children.parent_credit_id', 'credits.id');
                })
                ->whereIn('collector_user_id', $collectorIds)
                ->groupBy('collector_user_id')
                ->selectRaw(
                    'collector_user_id, COUNT(*) as total, SUM(CASE WHEN status IN (?, ?) THEN 1 ELSE 0 END) as overdue',
                    [Credit::STATUS_DELAYED, Credit::STATUS_OVERDUE],
                )
                ->get()
                ->keyBy('collector_user_id');

            foreach ($collectors as $collector) {
                // Alerta: Collector sin pagos en 3+ días
                $lastDate = $lastPaymentByCollector[$collector->id] ?? null;

                if ($lastDate) {
                    $daysSinceLastPayment = Carbon::parse($lastDate)->diffInDays($today);

                    if ($daysSinceLastPayment >= 3) {
                        $alerts->push([
                            'type' => 'no_activity',
                            'severity' => $daysSinceLastPayment >= 5 ? 'high' : 'medium',
                            'collector_id' => $collector->id,
                            'collector_name' => $collector->name,
                            'message' => "{$collector->name}: Sin pagos registrados hace {$daysSinceLastPayment} días",
                            'data' => [
                                'days_inactive' => $daysSinceLastPayment,
                                'last_payment_date' => $lastDate,
                            ],
                        ]);
                    }
                }

                // Alerta: Alta morosidad en cartera del collector
                $stats = $creditStats->get($collector->id);
                $totalCredits = $stats ? (int) $stats->total : 0;
                $overdueCredits = $stats ? (int) $stats->overdue : 0;

                if ($totalCredits > 0) {
                    $delinquencyRate = ($overdueCredits / $totalCredits) * 100;

                    if ($delinquencyRate >= 15) {
                        $alerts->push([
                            'type' => 'high_delinquency',
                            'severity' => $delinquencyRate >= 25 ? 'high' : 'medium',
                            'collector_id' => $collector->id,
                            'collector_name' => $collector->name,
                            'message' => "{$collector->name}: Morosidad alta ({$overdueCredits}/{$totalCredits} créditos)",
                            'data' => [
                                'delinquency_rate' => round($delinquencyRate, 2),
                                'overdue_credits' => $overdueCredits,
                                'total_credits' => $totalCredits,
                            ],
                        ]);
                    }
                }
            }

            // Ordenar por severidad
            $severityOrder = ['high' => 0, 'medium' => 1, 'low' => 2];

            return $alerts->sortBy(fn ($a) => $severityOrder[$a['severity']] ?? 3)->values();
        }, 'standard');
    }

    /*=========================================================
    | MÉTRICAS PARA COLLECTORS (PWA)
    =========================================================*/

    /**
     * Resumen diario para un collector específico.
     */
    public function getCollectorDailySummary(int $companyId, int $collectorId): array
    {
        return $this->cache->remember($companyId, "collection.daily_{$collectorId}", function () use ($companyId, $collectorId) {
            $today = Carbon::today();
            $weekStart = $today->copy()->startOfWeek();
            $monthStart = $today->copy()->startOfMonth();

            // Créditos asignados activos
            $activeCredits = Credit::where('company_id', $companyId)
                ->leafCredits()
                ->where('collector_user_id', $collectorId)
                ->whereIn('status', Credit::ACTIVE_STATUSES)
                ->count();

            // Cuotas pendientes de hoy
            $dueTodayQuery = Installment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()
                        ->where('collector_user_id', $collectorId)
                        ->whereIn('status', Credit::ACTIVE_STATUSES);
                })
                ->where('due_date', $today->toDateString())
                ->where('status', '!=', Installment::STATUS_PAID);

            $dueToday = $dueTodayQuery->count();
            $dueTodayAmount = (clone $dueTodayQuery)
                ->selectRaw('COALESCE(SUM(total_amount - amount_paid), 0) as total')
                ->value('total');

            // Cuotas vencidas (antes de hoy)
            $overdueQuery = Installment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()
                        ->where('collector_user_id', $collectorId)
                        ->whereIn('status', Credit::ACTIVE_STATUSES);
                })
                ->where('due_date', '<', $today->toDateString())
                ->where('status', '!=', Installment::STATUS_PAID);

            $overdueCount = $overdueQuery->count();
            $overdueAmount = (clone $overdueQuery)
                ->selectRaw('COALESCE(SUM(total_amount - amount_paid), 0) as total')
                ->value('total');

            // Clientes distintos con cuotas hoy
            $dueTodayClients = (int) DB::table('installments')
                ->join('credits', 'installments.credit_id', '=', 'credits.id')
                ->where('credits.company_id', $companyId)
                ->where('credits.collector_user_id', $collectorId)
                ->whereIn('credits.status', Credit::ACTIVE_STATUSES)
                ->whereNotExists(function ($sub) {
                    $sub->select(DB::raw(1))
                        ->from('credits as children')
                        ->whereColumn('children.parent_credit_id', 'credits.id');
                })
                ->where('installments.due_date', $today->toDateString())
                ->where('installments.status', '!=', Installment::STATUS_PAID)
                ->distinct()
                ->count('credits.client_id');

            // Cobrado hoy (monto + cantidad de pagos)
            $collectedTodayQuery = Payment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()->where('collector_user_id', $collectorId);
                })
                ->where('payment_date', $today->toDateString())
                ->collections();

            $collectedToday = (clone $collectedTodayQuery)->sum('amount');
            $collectedTodayCount = $collectedTodayQuery->count();

            // Cobrado esta semana
            $collectedWeek = Payment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()->where('collector_user_id', $collectorId);
                })
                ->whereBetween('payment_date', [$weekStart->toDateString(), $today->toDateString()])
                ->collections()
                ->sum('amount');

            // Cobrado este mes
            $collectedMonth = Payment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()->where('collector_user_id', $collectorId);
                })
                ->whereBetween('payment_date', [$monthStart->toDateString(), $today->toDateString()])
                ->collections()
                ->sum('amount');

            return [
                'active_credits' => $activeCredits,
                'due_today_count' => $dueToday,
                'due_today_amount' => Money::of($dueTodayAmount)->value(),
                'due_today_clients' => $dueTodayClients,
                'overdue_count' => $overdueCount,
                'overdue_amount' => Money::of($overdueAmount)->value(),
                'collected_today' => Money::of($collectedToday)->value(),
                'collected_today_count' => $collectedTodayCount,
                'collected_week' => Money::of($collectedWeek)->value(),
                'collected_month' => Money::of($collectedMonth)->value(),
            ];
        }, 'realtime');
    }

    /**
     * Lista de cobros pendientes para hoy (para un collector).
     *
     * @return Collection<int, array{
     *   installment_id: int,
     *   credit_id: int,
     *   client_id: int,
     *   client_name: string,
     *   installment_number: int,
     *   due_date: string,
     *   days_overdue: int,
     *   amount_due: float,
     *   priority: string
     * }>
     */
    public function getPendingCollections(int $companyId, int $collectorId): Collection
    {
        return $this->cache->remember($companyId, "collection.pending_{$collectorId}", function () use ($companyId, $collectorId) {
            $today = Carbon::today();

            $installments = Installment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()
                        ->where('collector_user_id', $collectorId)
                        ->whereIn('status', Credit::ACTIVE_STATUSES);
                })
                ->where('status', '!=', Installment::STATUS_PAID)
                ->where('due_date', '<=', $today->toDateString())
                ->with(['credit.client:id,name'])
                ->orderBy('due_date')
                ->get();

            return $installments->map(function ($installment) use ($today) {
                $daysOverdue = $installment->due_date->lt($today)
                    ? $installment->due_date->diffInDays($today)
                    : 0;

                $priority = match (true) {
                    $daysOverdue > 30 => 'critical',
                    $daysOverdue > 7 => 'high',
                    $daysOverdue > 0 => 'medium',
                    default => 'normal',
                };

                return [
                    'installment_id' => $installment->id,
                    'credit_id' => $installment->credit_id,
                    'client_id' => $installment->credit->client_id,
                    'client_name' => $installment->credit->client?->name ?? 'N/A',
                    'installment_number' => $installment->installment_number,
                    'due_date' => $installment->due_date->toDateString(),
                    'days_overdue' => $daysOverdue,
                    'amount_due' => Money::of($installment->total_amount - $installment->amount_paid)->value(),
                    'priority' => $priority,
                ];
            })->sortByDesc('days_overdue')->values();
        }, 'realtime');
    }

    /**
     * Top critical clients for the Home dashboard (aggregated by client).
     *
     * SQL-level aggregation: groups overdue installments by client,
     * returns top 5 ordered by max_days_overdue DESC, total_amount DESC.
     *
     * @return array{
     *   clients: array<int, array{client_id: int, client_name: string, installments_count: int, total_amount: float, max_days_overdue: int, oldest_due_date: string, priority: string}>,
     *   total_clients: int,
     *   total_installments: int,
     *   total_amount: float
     * }
     */
    public function getTopCriticalClients(int $companyId, int $collectorId, int $limit = 5): array
    {
        return $this->cache->remember($companyId, "collection.critical_clients_{$collectorId}", function () use ($companyId, $collectorId, $limit) {
            $today = Carbon::today()->toDateString();

            // Aggregated query: group by client, all in SQL
            $rows = DB::table('installments')
                ->join('credits', 'installments.credit_id', '=', 'credits.id')
                ->join('clients', 'credits.client_id', '=', 'clients.id')
                ->where('credits.company_id', $companyId)
                ->where('credits.collector_user_id', $collectorId)
                ->whereIn('credits.status', Credit::ACTIVE_STATUSES)
                ->whereNotExists(function ($sub) {
                    $sub->select(DB::raw(1))
                        ->from('credits as children')
                        ->whereColumn('children.parent_credit_id', 'credits.id');
                })
                ->where('installments.status', '!=', Installment::STATUS_PAID)
                ->where('installments.due_date', '<=', $today)
                ->selectRaw(
                    'clients.id as client_id,
                     clients.name as client_name,
                     COUNT(*) as installments_count,
                     COALESCE(SUM(installments.total_amount - installments.amount_paid), 0) as total_amount,
                     MAX(DATEDIFF(?, installments.due_date)) as max_days_overdue,
                     MIN(installments.due_date) as oldest_due_date',
                    [$today],
                )
                ->groupBy('clients.id', 'clients.name')
                ->orderByDesc('max_days_overdue')
                ->orderByDesc('total_amount')
                ->get();

            $totalClients = $rows->count();
            $totalInstallments = $rows->sum('installments_count');
            $totalAmount = $rows->sum('total_amount');

            $clients = $rows->take($limit)->map(function ($row) {
                $daysOverdue = (int) $row->max_days_overdue;
                $priority = match (true) {
                    $daysOverdue > 30 => 'critical',
                    $daysOverdue > 7 => 'high',
                    $daysOverdue > 0 => 'medium',
                    default => 'normal',
                };

                return [
                    'client_id' => (int) $row->client_id,
                    'client_name' => $row->client_name,
                    'installments_count' => (int) $row->installments_count,
                    'total_amount' => Money::of($row->total_amount)->value(),
                    'max_days_overdue' => $daysOverdue,
                    'oldest_due_date' => $row->oldest_due_date,
                    'priority' => $priority,
                ];
            })->values()->toArray();

            return [
                'clients' => $clients,
                'total_clients' => $totalClients,
                'total_installments' => (int) $totalInstallments,
                'total_amount' => Money::of($totalAmount)->value(),
            ];
        }, 'realtime');
    }

    /**
     * Aging breakdown de cuotas vencidas para un collector.
     *
     * Ejecuta una sola query SQL con CASE para clasificar en buckets (por cuota):
     *   1-30d, 31-60d, 61-90d, 90+d
     *
     * @return array{
     *   total_count: int,
     *   total_amount: float,
     *   oldest_due_date: ?string,
     *   buckets: array<int, array{label: string, key: string, min_days: int, max_days: ?int, count: int, amount: float}>
     * }
     */
    public function getOverdueAging(int $companyId, int $collectorId): array
    {
        return $this->cache->remember($companyId, "collection.aging_{$collectorId}", function () use ($companyId, $collectorId) {
            $today = Carbon::today()->toDateString();

            // Single SQL query with CASE-based bucketing
            $rows = DB::table('installments')
                ->join('credits', 'installments.credit_id', '=', 'credits.id')
                ->where('credits.company_id', $companyId)
                ->where('credits.collector_user_id', $collectorId)
                ->whereIn('credits.status', Credit::ACTIVE_STATUSES)
                ->whereNotExists(function ($sub) {
                    $sub->select(DB::raw(1))
                        ->from('credits as children')
                        ->whereColumn('children.parent_credit_id', 'credits.id');
                })
                ->where('installments.status', '!=', Installment::STATUS_PAID)
                ->where('installments.due_date', '<', $today)
                ->selectRaw("
                    CASE
                        WHEN DATEDIFF(?, installments.due_date) BETWEEN 1 AND 30 THEN '1_30'
                        WHEN DATEDIFF(?, installments.due_date) BETWEEN 31 AND 60 THEN '31_60'
                        WHEN DATEDIFF(?, installments.due_date) BETWEEN 61 AND 90 THEN '61_90'
                        WHEN DATEDIFF(?, installments.due_date) > 90 THEN '90_plus'
                    END as bucket,
                    COUNT(*) as count,
                    COALESCE(SUM(installments.total_amount - installments.amount_paid), 0) as amount,
                    MIN(installments.due_date) as oldest_date
                ", [$today, $today, $today, $today])
                ->groupBy('bucket')
                ->get()
                ->keyBy('bucket');

            // NOTA: el aging del cobrador es por CUOTA vencida (no por crédito) y usa
            // 4 buckets, a diferencia del reporte de morosidad por crédito
            // (DelinquencyMetricsService::getAgingReport, 7 buckets). El primer
            // bucket cubre 1–30 días (el CASE clasifica BETWEEN 1 AND 30); antes
            // estaba mal rotulado como '8_30'/"8–30 días", ocultando las cuotas
            // vencidas 1–7 días bajo una etiqueta incorrecta.
            $bucketDefs = [
                ['key' => '1_30',    'label' => '1–30 días',  'min_days' => 1,  'max_days' => 30],
                ['key' => '31_60',   'label' => '31–60 días', 'min_days' => 31, 'max_days' => 60],
                ['key' => '61_90',   'label' => '61–90 días', 'min_days' => 61, 'max_days' => 90],
                ['key' => '90_plus', 'label' => '90+ días',   'min_days' => 91, 'max_days' => null],
            ];

            $buckets = [];
            $totalCount = 0;
            $totalAmount = 0.0;
            $oldestDate = null;

            foreach ($bucketDefs as $def) {
                $row = $rows->get($def['key']);
                $count = $row ? (int) $row->count : 0;
                $amount = $row ? Money::of($row->amount)->value() : 0.0;

                $buckets[] = [
                    'key' => $def['key'],
                    'label' => $def['label'],
                    'min_days' => $def['min_days'],
                    'max_days' => $def['max_days'],
                    'count' => $count,
                    'amount' => $amount,
                ];

                $totalCount += $count;
                $totalAmount += $amount;

                if ($row && $row->oldest_date) {
                    if (! $oldestDate || $row->oldest_date < $oldestDate) {
                        $oldestDate = $row->oldest_date;
                    }
                }
            }

            // Sort by highest financial exposure first
            usort($buckets, fn ($a, $b) => $b['amount'] <=> $a['amount']);

            return [
                'total_count' => $totalCount,
                'total_amount' => $totalAmount,
                'oldest_due_date' => $oldestDate,
                'buckets' => $buckets,
            ];
        }, 'realtime');
    }

    /**
     * Historial de pagos recientes de un collector.
     *
     * @return Collection<int, array{
     *   payment_id: int,
     *   credit_id: int,
     *   client_name: string,
     *   amount: float,
     *   payment_date: string,
     *   payment_method: string
     * }>
     */
    public function getRecentPayments(int $companyId, int $collectorId, int $limit = 10): Collection
    {
        $payments = Payment::where('company_id', $companyId)
            ->whereHas('credit', function ($q) use ($collectorId) {
                $q->leafCredits()->where('collector_user_id', $collectorId);
            })
            ->collections()
            ->with(['credit.client:id,name'])
            ->orderByDesc('payment_date')
            ->orderByDesc('created_at')
            ->limit($limit)
            ->get();

        return $payments->map(fn ($payment) => [
            'payment_id' => $payment->id,
            'credit_id' => $payment->credit_id,
            'client_name' => $payment->credit->client?->name ?? 'N/A',
            'amount' => Money::of($payment->amount)->value(),
            'payment_date' => $payment->payment_date->toDateString(),
            'payment_method' => $payment->payment_method,
        ]);
    }

    /**
     * Progreso semanal del collector (para gráfico en PWA).
     *
     * @return Collection<int, array{day: string, day_name: string, amount: float}>
     */
    public function getWeeklyProgress(int $companyId, int $collectorId): Collection
    {
        return $this->cache->remember($companyId, "collection.weekly_{$collectorId}", function () use ($companyId, $collectorId) {
            $weekStart = Carbon::now()->startOfWeek();
            $today = Carbon::now();

            $payments = Payment::where('company_id', $companyId)
                ->whereHas('credit', function ($q) use ($collectorId) {
                    $q->leafCredits()->where('collector_user_id', $collectorId);
                })
                ->collections()
                ->whereBetween('payment_date', [$weekStart->toDateString(), $today->toDateString()])
                ->select(
                    DB::raw('DATE(payment_date) as date'),
                    DB::raw('SUM(amount) as total')
                )
                ->groupBy('date')
                ->pluck('total', 'date');

            $result = collect();
            $dayNames = ['Lun', 'Mar', 'Mié', 'Jue', 'Vie', 'Sáb', 'Dom'];
            $cursor = $weekStart->copy();

            for ($i = 0; $i < 7; $i++) {
                $dateKey = $cursor->toDateString();
                $amount = $payments->get($dateKey, 0);

                $result->push([
                    'day' => $dateKey,
                    'day_name' => $dayNames[$i],
                    'amount' => Money::of($amount)->value(),
                ]);

                $cursor->addDay();
            }

            return $result;
        }, 'frequent');
    }
}