sfp_api_laravel_ginastica_cerebro/app/Services/FranchisorDashboardService.php

<?php

namespace App\Services;

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

/**
 * Agrega os indicadores da tela inicial (Dashboard) da visão do Franqueador.
 *
 * Premissas de cálculo:
 * - "Matrículas por Período" = quantidade de alunos cadastrados por mês (created_at),
 *   nos últimos 6 meses.
 * - "Aniversariantes do Mês" = alunos cujo mês de nascimento é o mês corrente.
 * - "Tickets Abertos" = chamados de suporte com status 'in_progress'.
 * - "Tarefas Pendentes" = cartões do Kanban que ainda não foram concluídos.
 * - "Receita Geral" = total cobrado das franquias (franchisee_account_receives),
 *   pago + em aberto; a legenda traz a quantidade de pagamentos pendentes.
 * - "Estoque Geral de Produtos" = soma das quantidades em estoque dos produtos.
 *
 * Todas as métricas por unidade respeitam o filtro `unitIds` (vazio = rede inteira);
 * o estoque de produtos é do catálogo do franqueador (não filtra por unidade).
 */
class FranchisorDashboardService
{
    private const SETTLED = [
        ReceivableStatus::PAID->value,
        ReceivableStatus::CANCELLED->value,
    ];

    public function overview(array $unitIds = [], ?Carbon $startDate = null, ?Carbon $endDate = null): array
    {
        $startDate ??= Carbon::now()->startOfMonth();
        $endDate   ??= Carbon::now()->endOfMonth();

        return [
            'enrollments'     => $this->enrollments($unitIds),
            'birthdays'       => $this->birthdays($unitIds),
            'attendance'      => $this->attendance($unitIds, $startDate, $endDate),
            'open_tickets'    => $this->openTickets($unitIds),
            'tickets'         => $this->tickets($unitIds),
            'pending_tasks'   => $this->pendingTasks($unitIds),
            'general_revenue' => $this->generalRevenue($unitIds),
            'product_stock'   => $this->productStock(),
            'products'        => $this->products(),
            'units_summary'   => $this->unitsSummary($unitIds, $startDate, $endDate),
        ];
    }

    /**
     * Receita usa a mesma base do cálculo do TBR. O status é derivado da
     * existência de contrato de franquia vigente na data atual.
     */
    private function unitsSummary(array $unitIds, Carbon $startDate, Carbon $endDate): array
    {
        $units = DB::table('units')
            ->whereNull('deleted_at')
            ->when(!empty($unitIds), fn ($q) => $q->whereIn('id', $unitIds));

        $filteredUnitIds = (clone $units)->pluck('id')->map(fn ($id) => (int) $id)->all();
        $total           = count($filteredUnitIds);
        $today           = Carbon::today()->toDateString();

        $activeContracts = $total === 0
            ? 0
            : (int) DB::table('franchisee_contracts')
                ->whereNull('deleted_at')
                ->whereIn('unit_id', $filteredUnitIds)
                ->whereNotNull('start_date')
                ->where('start_date', '<=', $today)
                ->where(function ($q) use ($today) {
                    $q->whereNull('end_date')->orWhere('end_date', '>=', $today);
                })
                ->distinct()
                ->count('unit_id');

        $receivables = $total === 0
            ? ['received' => 0.0, 'missing' => 0.0]
            : $this->unitReceivables($filteredUnitIds, $startDate, $endDate);

        return [
            'total'                   => $total,
            'revenue'                 => round($receivables['received'], 2),
            'missing_receivables'     => round($receivables['missing'], 2),
            'active_contracts'        => $activeContracts,
            'without_active_contract' => max(0, $total - $activeContracts),
        ];
    }

    /**
     * Separa os recebíveis das unidades entre valores efetivamente recebidos no
     * período e valores com vencimento no período que ainda não foram recebidos.
     * Mantém a mesma composição usada pelo faturamento-base do TBR.
     */
    private function unitReceivables(array $unitIds, Carbon $startDate, Carbon $endDate): array
    {
        $start = $startDate->toDateString();
        $end = $endDate->toDateString();

        $sources = [
            DB::table('student_contract_installments')
                ->whereNull('deleted_at')
                ->whereIn('unit_id', $unitIds),
            DB::table('unit_account_receivables')
                ->whereNull('deleted_at')
                ->whereIn('unit_id', $unitIds),
            DB::table('franchisee_account_receives')
                ->whereNull('deleted_at')
                ->where('origin', 'manual_unit')
                ->whereNotNull('unit_id')
                ->whereIn('unit_id', $unitIds),
        ];

        $received = 0.0;
        $missing = 0.0;

        foreach ($sources as $source) {
            $received += (float) (clone $source)
                ->where('status', ReceivableStatus::PAID->value)
                ->whereBetween('payment_date', [$start, $end])
                ->sum('paid_value');

            $missing += (float) (clone $source)
                ->whereNotIn('status', self::SETTLED)
                ->whereBetween('due_date', [$start, $end])
                ->sum('value');
        }

        return ['received' => $received, 'missing' => $missing];
    }

    /**
     * Matrículas (alunos cadastrados) por mês nos últimos 6 meses.
     */
    private function enrollments(array $unitIds): array
    {
        $start = Carbon::now()->startOfMonth()->subMonthsNoOverflow(5);

        // Agrupa por ano-mês de forma portável (independente do SGBD): puxa apenas
        // a data de cadastro no período e conta os meses em memória.
        $createdAt = DB::table('students')
            ->whereNull('deleted_at')
            ->where('created_at', '>=', $start)
            ->when(!empty($unitIds), fn ($q) => $q->whereIn('unit_id', $unitIds))
            ->pluck('created_at');

        $counts = [];
        foreach ($createdAt as $date) {
            $ym = Carbon::parse($date)->format('Y-m');
            $counts[$ym] = ($counts[$ym] ?? 0) + 1;
        }

        $labels = [];
        $data   = [];
        $months = ['JAN', 'FEV', 'MAR', 'ABR', 'MAI', 'JUN', 'JUL', 'AGO', 'SET', 'OUT', 'NOV', 'DEZ'];

        for ($i = 0; $i < 6; $i++) {
            $month    = $start->copy()->addMonthsNoOverflow($i);
            $labels[] = $months[$month->month - 1];
            $data[]   = (int) ($counts[$month->format('Y-m')] ?? 0);
        }

        return ['labels' => $labels, 'data' => $data];
    }

    /**
     * Aniversariantes do mês corrente (dia + nome).
     */
    private function birthdays(array $unitIds): array
    {
        return DB::table('students')
            ->whereNull('deleted_at')
            ->whereMonth('birth_date', Carbon::now()->month)
            ->when(!empty($unitIds), fn ($q) => $q->whereIn('unit_id', $unitIds))
            ->get(['name', 'birth_date'])
            ->map(fn ($s) => [
                'day'  => (int) Carbon::parse($s->birth_date)->day,
                'name' => $s->name,
            ])
            ->sortBy('day')
            ->values()
            ->all();
    }

    private function openTickets(array $unitIds): int
    {
        return (int) $this->openTicketsQuery($unitIds)->count();
    }

    private function tickets(array $unitIds): array
    {
        return $this->openTicketsQuery($unitIds)
            ->orderByDesc('created_at')
            ->get(['id', 'severity', 'created_at', 'sector', 'status', 'title'])
            ->map(fn ($ticket) => [
                'id'       => (int) $ticket->id,
                'number'   => '#' . $ticket->id,
                'title'    => $ticket->title,
                'priority' => $ticket->severity,
                'date'     => Carbon::parse($ticket->created_at)->format('d/m/Y'),
                'sector'   => $ticket->sector ?: 'Não informado',
                'status'   => $ticket->status,
            ])
            ->all();
    }

    private function openTicketsQuery(array $unitIds)
    {
        return DB::table('support_tickets')
            ->whereNull('deleted_at')
            ->where('status', 'in_progress')
            ->when(!empty($unitIds), fn ($q) => $q->where(function ($units) use ($unitIds) {
                $units->whereIn('applicant_unit_id', $unitIds)
                    ->orWhereIn('target_unit_id', $unitIds)
                    ->orWhereIn('unit_id', $unitIds);
            }));
    }

    private function attendance(array $unitIds, Carbon $startDate, Carbon $endDate): array
    {
        $rows = DB::table('units as u')
            ->leftJoin('classes as c', function ($join) use ($startDate, $endDate) {
                $join->on('c.unit_id', '=', 'u.id')
                    ->whereNull('c.deleted_at')
                    ->whereBetween('c.date_time_start', [$startDate, $endDate]);
            })
            ->leftJoin('class_attendances as ca', function ($join) {
                $join->on('ca.class_id', '=', 'c.id')->whereNull('ca.deleted_at');
            })
            ->whereNull('u.deleted_at')
            ->when(!empty($unitIds), fn ($q) => $q->whereIn('u.id', $unitIds))
            ->groupBy('u.id', 'u.fantasy_name')
            ->orderBy('u.fantasy_name')
            ->get([
                'u.id',
                'u.fantasy_name',
                DB::raw('COUNT(ca.id) as attendance_count'),
                DB::raw('SUM(CASE WHEN ca.in_class THEN 1 ELSE 0 END) as present_count'),
            ]);

        $total = (int) $rows->sum('attendance_count');
        $present = (int) $rows->sum('present_count');

        return [
            'average' => $total > 0 ? round(($present / $total) * 100, 1) : 0,
            'units'   => $rows->map(function ($row) {
                $count = (int) $row->attendance_count;
                $average = $count > 0
                    ? round(((int) $row->present_count / $count) * 100, 1)
                    : 0;

                return [
                    'id'        => (int) $row->id,
                    'unit'      => $row->fantasy_name,
                    'frequency' => $average,
                    'status'    => $count === 0
                        ? 'Sem dados'
                        : ($average >= 70 ? 'Alto' : 'Baixa'),
                ];
            })->all(),
        ];
    }

    private function pendingTasks(array $unitIds): int
    {
        return (int) DB::table('kanbans')
            ->whereNull('deleted_at')
            ->where('phase', '!=', 'concluido')
            ->when(!empty($unitIds), fn ($q) => $q->whereIn('unit_id', $unitIds))
            ->count();
    }

    /**
     * Receita Geral = total das cobranças às franquias (pago + em aberto),
     * com a contagem das que ainda estão pendentes.
     */
    private function generalRevenue(array $unitIds): array
    {
        $base = DB::table('franchisee_account_receives')
            ->whereNull('deleted_at')
            ->whereNotNull('unit_id')
            ->when(!empty($unitIds), fn ($q) => $q->whereIn('unit_id', $unitIds));

        $value = (float) (clone $base)->sum('value');

        $pending = (int) (clone $base)
            ->whereNotIn('status', self::SETTLED)
            ->count();

        return ['value' => $value, 'pending_count' => $pending];
    }

    private function productStock(): int
    {
        return (int) DB::table('products')
            ->whereNull('deleted_at')
            ->sum('quantity');
    }

    private function products(): array
    {
        return DB::table('products')
            ->whereNull('deleted_at')
            ->orderBy('name')
            ->get(['id', 'name', 'quantity'])
            ->map(function ($product) {
                $quantity = (int) $product->quantity;

                return [
                    'id'       => (int) $product->id,
                    'name'     => $product->name,
                    'quantity' => $quantity,
                    'status'   => $quantity <= 15
                        ? 'Baixo'
                        : ($quantity <= 30 ? 'Limitado' : 'Alto'),
                ];
            })->all();
    }
}