<?php

declare(strict_types=1);

namespace App\Services\Dashboard;

use App\Models\Credit;
use App\Models\Installment;
use App\Services\Metrics\CashFlowMetricsService;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Servicio centralizado de métricas para el dashboard Admin (PWA).
 *
 * Solo créditos leaf con status IN (active, delayed, overdue).
 * Excluye: cancelled, extended, renewed, restructured, refinanced, etc.
 *
 * DEFINICIONES:
 *
 * Desembolsado    = SUM(credits.amount) de créditos activos leaf.
 *                   Capital principal actualmente en la calle.
 *
 * Cartera         = SUM(installments.total_amount) de créditos activos leaf.
 *                   Valor total del portafolio (principal + interés programado).
 *
 * Por Cobrar      = SUM(total_amount - amount_paid) de cuotas no pagadas.
 *                   Lo que falta por cobrar del portafolio.
 *
 * Vencido         = Por Cobrar WHERE due_date < hoy
 * Cartera Vigente = Por Cobrar WHERE due_date >= hoy
 *
 * Interés Cobrado = SUM de interés efectivamente recaudado.
 *                   Cuotas paid: interest_amount completo.
 *                   Cuotas partial_paid: LEAST(amount_paid, interest_amount)
 *                   (convención interest-first).
 *
 * INVARIANTE:
 *   Por Cobrar = Vencido + Cartera Vigente
 */
class AdminDashboardMetricsService
{
    public function __construct(
        private CashFlowMetricsService $cashFlow,
        private DashboardTrendService $trends,
    ) {}

    public function getMetrics(int $companyId): array
    {
        $today = now()->toDateString();

        $installmentMetrics = $this->getInstallmentMetrics($companyId, $today);
        $disbursed = $this->getDisbursed($companyId);
        $cashBase = $this->getCashBase($companyId);
        $collectedToday = $this->getCollectedToday($companyId, $today);
        $operationalSummary = $this->getOperationalSummary($companyId, $today);
        $disbursementsToday = $this->getTodayDisbursements($companyId, $today);
        $operationalExpensesToday = $this->getTodayOperationalExpenses($companyId, $today);

        $cartera = $installmentMetrics['cartera'];
        $porCobrar = $installmentMetrics['por_cobrar'];
        $interestEarned = $installmentMetrics['interest_earned'];

        // Utilidad del MES (real, no acumulada): interés ganado en el mes en curso,
        // con tendencia vs el mismo tramo del mes anterior.
        $interestMonth = $this->cashFlow->getInterestEarnedThisMonth($companyId);
        $interestPrevMonth = $this->cashFlow->getInterestEarned(
            $companyId,
            Carbon::now()->subMonthNoOverflow()->startOfMonth(),
            Carbon::now()->subMonthNoOverflow(),
        );
        $interestTrendPct = $interestPrevMonth > 0
            ? round((($interestMonth - $interestPrevMonth) / $interestPrevMonth) * 100, 1)
            : null;

        // Criterio PAR (Portfolio at Risk), alineado con DelinquencyMetricsService:
        // Vencido = saldo total (todas las cuotas pendientes) de los créditos que tienen
        // al menos una cuota con due_date < hoy. Incluye cuotas aún vigentes del mismo
        // crédito, que están en riesgo real al pertenecer a un deudor ya en mora.
        $vencido = $this->getParOverdueBalance($companyId, $today);

        // Cartera Vigente = por cobrar - PAR vencido (invariante garantizado por substracción)
        $carteraVigente = max(0.0, round($porCobrar - $vencido, 2));

        $this->validateIntegrity($porCobrar, $vencido, $carteraVigente, $companyId);

        // Total Negocio = Por Cobrar + Base en Caja
        $businessTotal = round($porCobrar + $cashBase, 2);

        // Tasa de morosidad = Vencido / Por Cobrar
        $delinquencyRate = $porCobrar > 0
            ? round(($vencido / $porCobrar) * 100, 1)
            : 0.0;

        $stockTrends = $this->trends->trendsFor($companyId, [
            'business_total' => $businessTotal,
            'cash_base' => $cashBase,
            'por_cobrar' => $porCobrar,
            'delinquency_rate' => $delinquencyRate,
        ]);

        // Aplana {pct,good}|null → trend_pct/good para el frontend (mismo shape que interest_month).
        $flat = static fn (?array $t): array => [
            'trend_pct' => $t['pct'] ?? null,
            'good' => $t['good'] ?? null,
        ];

        // Polaridad conjunta: una cartera que crece mientras la caja baja o la
        // mora sube no es un logro, es riesgo. Ver spec 2026-09-08 §D.
        $stockTrendsFlat = DashboardTrendService::applyJointPolarity([
            'business_total' => $flat($stockTrends['business_total']),
            'cash_base' => $flat($stockTrends['cash_base']),
            'por_cobrar' => $flat($stockTrends['por_cobrar']),
            'delinquency_rate' => $flat($stockTrends['delinquency_rate']),
        ]);

        return [
            'business_total' => [
                'amount' => $businessTotal,
                ...$stockTrendsFlat['business_total'],
            ],
            'cash_base' => [
                'amount' => round($cashBase, 2),
                ...$stockTrendsFlat['cash_base'],
            ],
            'disbursed' => [
                'amount' => round($disbursed['amount'], 2),
                'credits_count' => $disbursed['credits_count'],
            ],
            'cartera' => [
                'amount' => round($cartera, 2),
            ],
            'por_cobrar' => [
                'amount' => round($porCobrar, 2),
                'current' => round($carteraVigente, 2),
                'overdue' => round($vencido, 2),
                ...$stockTrendsFlat['por_cobrar'],
            ],
            'overdue' => [
                'amount' => round($vencido, 2),
            ],
            'collected_today' => [
                'amount' => round($collectedToday['amount'], 2),
                'count' => $collectedToday['count'],
            ],
            'interest_earned' => round($interestEarned, 2),
            'interest_month' => [
                'amount' => round($interestMonth, 2),
                'trend_pct' => $interestTrendPct,
                'good' => $interestTrendPct === null ? null : $interestTrendPct >= 0,
            ],
            'delinquency_rate' => $delinquencyRate,
            'delinquency_trend' => $stockTrends['delinquency_rate'] === null
                ? null
                : $stockTrendsFlat['delinquency_rate'],
            'operational_summary' => $operationalSummary,
            'disbursements_today' => [
                'count' => $disbursementsToday['count'],
                'amount' => round($disbursementsToday['amount'], 2),
            ],
            'operational_expenses_today' => [
                'count' => $operationalExpensesToday['count'],
                'amount' => round($operationalExpensesToday['amount'], 2),
            ],
        ];
    }

    /**
     * Desembolsado: SUM(credits.amount) + count de créditos activos leaf.
     */
    private function getDisbursed(int $companyId): array
    {
        $result = DB::selectOne('
            SELECT
                COALESCE(SUM(c.amount), 0) as total,
                COUNT(*) as credits_count
            FROM credits c
            WHERE c.company_id = ?
              AND c.status IN (?, ?, ?)
              AND NOT EXISTS (
                  SELECT 1 FROM credits child
                  WHERE child.parent_credit_id = c.id
              )
        ', [
            $companyId,
            ...Credit::ACTIVE_STATUSES,
        ]);

        return [
            'amount' => (float) $result->total,
            'credits_count' => (int) $result->credits_count,
        ];
    }

    /**
     * Métricas de cuotas en una sola query:
     * - Cartera (total portfolio = SUM total_amount de TODAS las cuotas)
     * - Por Cobrar (remaining = total_amount - amount_paid de cuotas no pagadas)
     * - Vencido (due_date < hoy)
     * - Cartera Vigente (due_date >= hoy)
     * - Interés Cobrado (paid: full interest, partial_paid: min of paid vs interest)
     */
    private function getInstallmentMetrics(int $companyId, string $today): array
    {
        $result = DB::selectOne('
            SELECT
                COALESCE(SUM(i.total_amount), 0) as cartera,

                COALESCE(SUM(
                    CASE WHEN i.status IN (?, ?, ?)
                    THEN GREATEST(0, i.total_amount - i.amount_paid)
                    ELSE 0 END
                ), 0) as por_cobrar,

                COALESCE(SUM(
                    CASE WHEN i.status IN (?, ?, ?) AND i.due_date < ?
                    THEN GREATEST(0, i.total_amount - i.amount_paid)
                    ELSE 0 END
                ), 0) as overdue_amount,

                COALESCE(SUM(
                    CASE WHEN i.status IN (?, ?, ?) AND i.due_date >= ?
                    THEN GREATEST(0, i.total_amount - i.amount_paid)
                    ELSE 0 END
                ), 0) as current_amount,

                COALESCE(SUM(
                    CASE WHEN i.status = ? THEN i.interest_amount
                         WHEN i.status = ? THEN LEAST(i.amount_paid, i.interest_amount)
                    ELSE 0 END
                ), 0) as interest_earned

            FROM installments i
            INNER JOIN credits c ON c.id = i.credit_id
            WHERE c.company_id = ?
              AND c.status IN (?, ?, ?)
              AND NOT EXISTS (
                  SELECT 1 FROM credits child
                  WHERE child.parent_credit_id = c.id
              )
        ', [
            // por_cobrar
            Installment::STATUS_PENDING, Installment::STATUS_PARTIAL_PAID, Installment::STATUS_OVERDUE,
            // overdue_amount
            Installment::STATUS_PENDING, Installment::STATUS_PARTIAL_PAID, Installment::STATUS_OVERDUE,
            $today,
            // current_amount
            Installment::STATUS_PENDING, Installment::STATUS_PARTIAL_PAID, Installment::STATUS_OVERDUE,
            $today,
            // interest_earned
            Installment::STATUS_PAID,
            Installment::STATUS_PARTIAL_PAID,
            // WHERE
            $companyId,
            ...Credit::ACTIVE_STATUSES,
        ]);

        return [
            'cartera' => (float) $result->cartera,
            'por_cobrar' => (float) $result->por_cobrar,
            'overdue_amount' => (float) $result->overdue_amount,
            'current_amount' => (float) $result->current_amount,
            'interest_earned' => (float) $result->interest_earned,
        ];
    }

    /**
     * Base en Caja: SUM(ingresos) - SUM(egresos efectivos que afectan caja).
     *
     * CRITERIOS DELIBERADOS:
     * - Se suman TODOS los ingresos (el income de OPENING_BALANCE es la fuente de verdad
     *   del saldo inicial; settings.opening_balance no se mantiene sincronizado con los
     *   registros de Income, por lo que no es fiable).
     * - Se restan solo los egresos EFECTIVOS con affects_cash=true: aprobados o que no
     *   requieren aprobación (equivalente SQL de Expense::scopeEffective). Un gasto
     *   registrado por un supervisor o cobrador queda PENDIENTE y NO descuenta caja hasta
     *   que el admin lo aprueba. Misma regla canónica que getCashPosition y getNetProfit.
     */
    private function getCashBase(int $companyId): float
    {
        $result = DB::selectOne("
            SELECT
                (SELECT COALESCE(SUM(amount), 0) FROM incomes WHERE company_id = ?) -
                (SELECT COALESCE(SUM(amount), 0) FROM expenses
                    WHERE company_id = ? AND affects_cash = 1
                      AND (approval_status = 'approved' OR requires_approval = 0))
            as cash_base
        ", [$companyId, $companyId]);

        return (float) $result->cash_base;
    }

    /**
     * Cobrado Hoy: pagos del día (no anulados).
     */
    private function getCollectedToday(int $companyId, string $today): array
    {
        $result = DB::selectOne('
            SELECT
                COALESCE(SUM(amount), 0) as total_amount,
                COUNT(*) as count
            FROM payments
            WHERE company_id = ?
              AND payment_date = ?
              AND voided = 0
              AND payment_method <> "reversal"
        ', [$companyId, $today]);

        return [
            'amount' => (float) $result->total_amount,
            'count' => (int) $result->count,
        ];
    }

    /**
     * Resumen operativo.
     *
     * Tasa de cobro = pagadas_hoy / (pagadas_hoy + pendientes_hoy) * 100
     */
    private function getOperationalSummary(int $companyId, string $today): array
    {
        $result = DB::selectOne('
            SELECT
                SUM(CASE WHEN i.due_date = ? AND i.status IN (?, ?, ?) THEN (i.total_amount - i.amount_paid) ELSE 0 END) as due_today_amount,
                SUM(CASE WHEN i.due_date = ? AND i.status IN (?, ?, ?) THEN 1 ELSE 0 END) as pending_today,
                SUM(CASE WHEN i.due_date = ? AND i.status = ? THEN 1 ELSE 0 END) as paid_today,
                SUM(CASE WHEN i.due_date < ? AND i.status IN (?, ?, ?) THEN 1 ELSE 0 END) as overdue_count
            FROM installments i
            INNER JOIN credits c ON c.id = i.credit_id
            WHERE c.company_id = ?
              AND c.status IN (?, ?, ?)
              AND NOT EXISTS (
                  SELECT 1 FROM credits child
                  WHERE child.parent_credit_id = c.id
              )
        ', [
            $today,
            Installment::STATUS_PENDING, Installment::STATUS_PARTIAL_PAID, Installment::STATUS_OVERDUE,
            $today,
            Installment::STATUS_PENDING, Installment::STATUS_PARTIAL_PAID, Installment::STATUS_OVERDUE,
            $today,
            Installment::STATUS_PAID,
            $today,
            Installment::STATUS_PENDING, Installment::STATUS_PARTIAL_PAID, Installment::STATUS_OVERDUE,
            $companyId,
            ...Credit::ACTIVE_STATUSES,
        ]);

        $pendingToday = (int) ($result->pending_today ?? 0);
        $paidToday = (int) ($result->paid_today ?? 0);
        $totalToday = $pendingToday + $paidToday;

        $collectionRate = $totalToday > 0
            ? round(($paidToday / $totalToday) * 100, 1)
            : 0.0;

        $activeCollectors = (int) Credit::query()
            ->where('company_id', $companyId)
            ->whereIn('status', Credit::ACTIVE_STATUSES)
            ->whereDoesntHave('children')
            ->whereNotNull('collector_user_id')
            ->distinct('collector_user_id')
            ->count('collector_user_id');

        return [
            'due_today_amount' => (float) ($result->due_today_amount ?? 0),
            'due_today_count' => $pendingToday,
            'paid_today_count' => $paidToday,
            'overdue_count' => (int) ($result->overdue_count ?? 0),
            'active_collectors' => $activeCollectors,
            'collection_rate_today' => $collectionRate,
        ];
    }

    /**
     * Cartera Vencida bajo criterio PAR (Portfolio at Risk).
     *
     * Equivalente a DelinquencyMetricsService::getDelinquencySummary() → total_overdue_balance.
     *
     * Definición: saldo pendiente total (ALL unpaid installments) de cada crédito leaf activo
     * que tenga al menos una cuota con due_date < hoy. Esto incluye cuotas cuyo vencimiento
     * aún no llegó pero que pertenecen a un deudor ya en mora, reflejando el riesgo real.
     *
     * Contraste con el cálculo anterior (solo cuotas vencidas):
     *   - Anterior: SUM(saldo de cuotas individuales con due_date < hoy)
     *   - PAR:      SUM(saldo TOTAL del crédito) para créditos con ≥1 cuota vencida
     */
    private function getParOverdueBalance(int $companyId, string $today): float
    {
        $result = DB::selectOne('
            SELECT COALESCE(SUM(i.total_amount - i.amount_paid), 0) as par_overdue
            FROM installments i
            INNER JOIN credits c ON c.id = i.credit_id
            WHERE c.company_id = ?
              AND c.status IN (?, ?, ?)
              AND NOT EXISTS (
                  SELECT 1 FROM credits ch WHERE ch.parent_credit_id = c.id
              )
              AND i.status != ?
              AND i.credit_id IN (
                  SELECT DISTINCT i2.credit_id
                  FROM installments i2
                  INNER JOIN credits c2 ON c2.id = i2.credit_id
                  WHERE c2.company_id = ?
                    AND c2.status IN (?, ?, ?)
                    AND NOT EXISTS (
                        SELECT 1 FROM credits ch WHERE ch.parent_credit_id = c2.id
                    )
                    AND i2.status != ?
                    AND i2.due_date < ?
              )
        ', [
            // outer join
            $companyId,
            ...Credit::ACTIVE_STATUSES,
            Installment::STATUS_PAID,
            // inner subquery
            $companyId,
            ...Credit::ACTIVE_STATUSES,
            Installment::STATUS_PAID,
            $today,
        ]);

        return (float) $result->par_overdue;
    }

    /**
     * Desembolsos de créditos del día.
     *
     * Criterio: category IN ('disbursement', 'credit_disbursement').
     * 'disbursement' es el valor legado (ExpenseCategory::DISBURSEMENT @deprecated),
     * 'credit_disbursement' es el valor actual (ExpenseCategory::CREDIT_DISBURSEMENT).
     * Ambos representan salidas de capital por apertura de créditos al cliente.
     */
    private function getTodayDisbursements(int $companyId, string $today): array
    {
        $result = DB::selectOne('
            SELECT
                COALESCE(SUM(amount), 0) as total,
                COUNT(*) as cnt
            FROM expenses
            WHERE company_id = ?
              AND DATE(created_at) = ?
              AND category IN (?, ?)
        ', [
            $companyId,
            $today,
            'disbursement',        // ExpenseCategory::DISBURSEMENT (legacy)
            'credit_disbursement', // ExpenseCategory::CREDIT_DISBURSEMENT
        ]);

        return [
            'amount' => (float) $result->total,
            'count' => (int) $result->cnt,
        ];
    }

    /**
     * Gastos operativos del día (excluye desembolsos de créditos).
     *
     * Criterio: category NOT IN ('disbursement', 'credit_disbursement').
     * Captura todos los demás tipos: salary_wages, rent, utilities,
     * transportation, profit_withdrawal, etc.
     */
    private function getTodayOperationalExpenses(int $companyId, string $today): array
    {
        // Solo egresos EFECTIVOS: aprobados o que no requieren aprobación (igual que
        // getCashBase). Antes incluía pendientes de aprobación, mostrando un total de
        // gasto operativo mayor al que realmente afectó la caja.
        $result = DB::selectOne('
            SELECT
                COALESCE(SUM(amount), 0) as total,
                COUNT(*) as cnt
            FROM expenses
            WHERE company_id = ?
              AND DATE(created_at) = ?
              AND category NOT IN (?, ?)
              AND (approval_status = ? OR requires_approval = 0)
        ', [
            $companyId,
            $today,
            'disbursement',        // ExpenseCategory::DISBURSEMENT (legacy)
            'credit_disbursement', // ExpenseCategory::CREDIT_DISBURSEMENT
            'approved',
        ]);

        return [
            'amount' => (float) $result->total,
            'count' => (int) $result->cnt,
        ];
    }

    /**
     * Validación: Por Cobrar = Vencido + Cartera Vigente.
     */
    private function validateIntegrity(
        float $porCobrar,
        float $vencido,
        float $carteraVigente,
        int $companyId
    ): void {
        $sumCheck = round($vencido + $carteraVigente, 2);
        $porCobrarRounded = round($porCobrar, 2);

        if (abs($sumCheck - $porCobrarRounded) > 0.01) {
            Log::warning('[AdminDashboard] Integrity: Por Cobrar != Vencido + Cartera Vigente', [
                'company_id' => $companyId,
                'por_cobrar' => $porCobrarRounded,
                'vencido' => $vencido,
                'cartera_vigente' => $carteraVigente,
                'sum' => $sumCheck,
            ]);
        }
    }
}