sfp_api_laravel_ginastica_cerebro/app/Services/FinancialDashboardService.php

<?php

namespace App\Services;

use App\Enums\ReceivableStatus;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

/**
 * Agrega os indicadores financeiros consolidados da rede (visão do Franqueador).
 *
 * Premissas de cálculo (#CB017 — confirmar com o negócio se necessário):
 * - "Receita de Mensalidade" = parcelas de aluno pagas no mês (paid_value).
 * - "TBR Total" = TBR faturada à franquia no mês (franchisee_account_receives.value).
 * - "Fundo de Marketing" = soma do fundo configurado por unidade (unit_financials).
 * - "Contas a Receber" = TBR pendente das franquias (franchisee_account_receives).
 * - "Saldo Bancário" = entradas (to_account) menos saídas (from_account) por conta.
 * - "Desempenho por Franquia": a "Despesa" de cada unidade é o total que ela deve
 *   pagar ao franqueador (TBR) acumulado até o mês corrente (due_date <= fim do mês).
 */
class FinancialDashboardService
{
    private const SETTLED = [
        ReceivableStatus::PAID->value,
        ReceivableStatus::CANCELLED->value,
    ];

    public function overview(): array
    {
        $now           = Carbon::now();
        $monthStart    = $now->copy()->startOfMonth();
        $monthEnd      = $now->copy()->endOfMonth();
        $prevStart     = $now->copy()->subMonthNoOverflow()->startOfMonth();
        $prevEnd       = $now->copy()->subMonthNoOverflow()->endOfMonth();

        $revenueCurrent = $this->monthlyRevenue($monthStart, $monthEnd);
        $revenuePrev    = $this->monthlyRevenue($prevStart, $prevEnd);

        $tbrCurrent = $this->tbrTotal($monthStart, $monthEnd);
        $tbrPrev    = $this->tbrTotal($prevStart, $prevEnd);

        return [
            'marketing_fund' => [
                'value'      => $this->marketingFund(),
                'percentage' => 0,
            ],
            'monthly_revenue' => [
                'value'      => $revenueCurrent,
                'percentage' => $this->variation($revenueCurrent, $revenuePrev),
            ],
            'tbr_total' => [
                'value'      => $tbrCurrent,
                'percentage' => $this->variation($tbrCurrent, $tbrPrev),
            ],
            'accounts_payable'    => $this->pendingPayables(),
            'accounts_receivable' => $this->pendingReceivables(),
            'bank_balance_total'  => $this->bankBalanceTotal(),
            'stock_value'         => $this->stockValue(),
            'last_transactions'   => $this->lastTransactions(),
            'franchise_performance' => $this->franchisePerformance($monthStart, $monthEnd),
        ];
    }

    private function monthlyRevenue(Carbon $start, Carbon $end): float
    {
        return (float) DB::table('student_contract_installments')
            ->whereNull('deleted_at')
            ->where('status', ReceivableStatus::PAID->value)
            ->whereBetween('payment_date', [$start, $end])
            ->sum('paid_value');
    }

    private function tbrTotal(Carbon $start, Carbon $end): float
    {
        return (float) DB::table('franchisee_account_receives')
            ->whereNull('deleted_at')
            ->whereBetween('due_date', [$start, $end])
            ->sum('value');
    }

    private function marketingFund(): float
    {
        return (float) DB::table('unit_financials')
            ->whereNull('deleted_at')
            ->sum('marketing_fund');
    }

    private function pendingPayables(): array
    {
        $row = DB::table('company_account_payables')
            ->whereNull('deleted_at')
            ->whereNotIn('status', self::SETTLED)
            ->selectRaw('COALESCE(SUM(value + fine - discount - paid_value), 0) AS total, COUNT(*) AS qty')
            ->first();

        return ['value' => (float) $row->total, 'count' => (int) $row->qty];
    }

    private function pendingReceivables(): array
    {
        // Recebíveis do franqueador = TBR cobrada das franquias ainda em aberto.
        $row = DB::table('franchisee_account_receives')
            ->whereNull('deleted_at')
            ->whereNotIn('status', self::SETTLED)
            ->selectRaw('COALESCE(SUM(value + fees - discount - paid_value), 0) AS total, COUNT(*) AS qty')
            ->first();

        return ['value' => (float) $row->total, 'count' => (int) $row->qty];
    }

    private function stockValue(): float
    {
        // Valor total do estoque = soma de (preço de venda x quantidade) de todos os
        // produtos. Mesma base exibida por produto na aba de Produtos (price_sale * quantity).
        return (float) DB::table('products')
            ->whereNull('deleted_at')
            ->selectRaw('COALESCE(SUM(price_sale * quantity), 0) AS total')
            ->value('total');
    }

    private function bankBalanceTotal(): float
    {
        // Saldo da rede = créditos (chegam no to_account) menos débitos (saem do from_account),
        // somados por conta. No modelo de partida dobrada atual (from/to obrigatórios) as
        // transferências internas se anulam; o valor reflete apenas fluxos externos. O cálculo
        // de saldo real por banco será consolidado no #CB018 (Tesouraria), que definirá
        // saldo de abertura / contas de origem externas.
        $credits = (float) DB::table('treasury_launches')
            ->whereNull('deleted_at')
            ->sum('amount');

        $debits = (float) DB::table('treasury_launches')
            ->whereNull('deleted_at')
            ->sum('amount');

        return $credits - $debits;
    }

    private function lastTransactions(int $limit = 8): array
    {
        return DB::table('treasury_launches')
            ->whereNull('deleted_at')
            ->orderByDesc('launch_date')
            ->orderByDesc('id')
            ->limit($limit)
            ->get(['id', 'description', 'amount', 'is_reconciled'])
            ->map(fn ($t) => [
                'id'          => $t->id,
                'description' => $t->description,
                'value'       => (float) $t->amount,
                'status'      => $t->is_reconciled ? 'Pago' : 'Pendente',
            ])
            ->all();
    }

    private function franchisePerformance(Carbon $start, Carbon $end): array
    {
        $revenue = DB::table('student_contract_installments')
            ->whereNull('deleted_at')
            ->where('status', ReceivableStatus::PAID->value)
            ->whereBetween('payment_date', [$start, $end])
            ->groupBy('unit_id')
            ->selectRaw('unit_id, SUM(paid_value) AS total')
            ->pluck('total', 'unit_id');

        // Despesa = total que a unidade deve pagar ao franqueador (TBR) acumulado
        // até o mês corrente (toda cobrança com vencimento até o fim do mês).
        $expense = DB::table('franchisee_account_receives')
            ->whereNull('deleted_at')
            ->where('due_date', '<=', $end)
            ->groupBy('unit_id')
            ->selectRaw('unit_id, SUM(value) AS total')
            ->pluck('total', 'unit_id');

        return DB::table('units')
            ->whereNull('deleted_at')
            ->get(['id', 'fantasy_name'])
            ->map(function ($unit) use ($revenue, $expense) {
                $rev = (float) ($revenue[$unit->id] ?? 0);
                $exp = (float) ($expense[$unit->id] ?? 0);

                return [
                    'id'          => $unit->id,
                    'franchise'   => $unit->fantasy_name,
                    'revenue'     => $rev,
                    'expense'     => $exp,
                    'performance' => $rev > 0 ? round((($rev - $exp) / $rev) * 100, 1) : 0,
                ];
            })
            ->all();
    }

    private function variation(float $current, float $previous): float
    {
        if ($previous <= 0) {
            return $current > 0 ? 100.0 : 0.0;
        }

        return round((($current - $previous) / $previous) * 100, 1);
    }
}