sfp_api_laravel_serprati/app/Services/ReportService.php

<?php

namespace App\Services;

use App\Enums\AppointmentStatusEnum;
use App\Enums\PartnerAgreementTypeEnum;
use App\Enums\UserTypeEnum;
use App\Models\Appointment;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Database\Eloquent\Collection;

class ReportService
{
    private const USOS_CONVENIO_STATUSES = [
        AppointmentStatusEnum::CONFIRMADO,
        AppointmentStatusEnum::CONCLUIDO,
    ];

    public function getCounters(): array
    {
        $now = Carbon::now();

        return [
            'novos_associados' => User::where('type', UserTypeEnum::ASSOCIADO)
                ->whereYear('created_at', $now->year)
                ->whereMonth('created_at', $now->month)
                ->count(),
            'contatos' => User::where('type', UserTypeEnum::ASSOCIADO)->count(),
            'exclusoes_mes' => User::where('type', UserTypeEnum::ASSOCIADO)
                ->whereNotNull('excluded_at')
                ->whereYear('excluded_at', $now->year)
                ->whereMonth('excluded_at', $now->month)
                ->count(),
            'usos_convenio' => Appointment::whereIn('status', self::USOS_CONVENIO_STATUSES)->count(),
        ];
    }

    public function getNovoAssociadosPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $now = Carbon::now();

        $query = User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereYear('created_at', $now->year)
            ->whereMonth('created_at', $now->month)
            ->orderBy('created_at', 'desc');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term, $filters) {
                $q->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(cpf)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw("TO_CHAR(created_at, 'DD/MM/YYYY') LIKE ?", [$term]);
            });
        }

        return $query->paginate($perPage);
    }

    public function getContatosAssociadosPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $query = User::where('type', UserTypeEnum::ASSOCIADO)
            ->orderBy('name', 'asc');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term) {
                $q->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(email)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(COALESCE(phone, \'\'))) LIKE UNACCENT(?)', [$term]);
            });
        }

        return $query->paginate($perPage);
    }

    public function getExclusoesMesPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $now = Carbon::now();

        $query = User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereNotNull('excluded_at')
            ->whereYear('excluded_at', $now->year)
            ->whereMonth('excluded_at', $now->month)
            ->orderBy('excluded_at', 'desc');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term) {
                $q->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(cpf)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw("TO_CHAR(excluded_at, 'DD/MM/YYYY') LIKE ?", [$term]);
            });
        }

        return $query->paginate($perPage);
    }

    public function getAllNovoAssociados(): Collection
    {
        $now = Carbon::now();

        return User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereYear('created_at', $now->year)
            ->whereMonth('created_at', $now->month)
            ->orderBy('created_at', 'desc')
            ->get();
    }

    public function getAllContatosAssociados(): Collection
    {
        return User::where('type', UserTypeEnum::ASSOCIADO)
            ->orderBy('name', 'asc')
            ->get();
    }

    public function getUsosConvenioPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        return $this->usosConvenioQuery($filters)->paginate($perPage);
    }

    public function getAllUsosConvenio(array $filters = []): Collection
    {
        return $this->usosConvenioQuery($filters)->get();
    }

    public function formatUsoConvenio(Appointment $appointment): array
    {
        $criadoEm = $appointment->requested_at ?? $appointment->created_at;

        return [
            'id'          => $appointment->id,
            'convenio'    => $appointment->partnerAgreement?->company_name,
            'type'        => $appointment->partnerAgreement?->type?->value,
            'servico'     => $appointment->partnerAgreementService?->name,
            'associado'   => $appointment->user?->name,
            'criado_em'   => $criadoEm?->format('d/m/Y H:i'),
            'agendado_em' => $appointment->date
                ? $appointment->date->format('d/m/Y') . ($appointment->time
                    ? ' ' . Carbon::parse($appointment->time)->format('H:i')
                    : '')
                : null,
        ];
    }

    public function usoConvenioTipoLabel(?string $type): string
    {
        return match ($type) {
            PartnerAgreementTypeEnum::AGREEMENT->value => 'Convênio Médico',
            PartnerAgreementTypeEnum::PARTNER->value   => 'Parceiro',
            default                                    => '',
        };
    }

    private function usosConvenioQuery(array $filters = []): Builder
    {
        $query = Appointment::query()
            ->with([
                'user:id,name',
                'partnerAgreement:id,company_name,type',
                'partnerAgreementService:id,name',
            ])
            ->whereIn('status', self::USOS_CONVENIO_STATUSES)
            ->orderByRaw('COALESCE(appointments.requested_at, appointments.created_at) DESC');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term) {
                $q->whereHas('partnerAgreement', fn($p) => $p->whereRaw('UNACCENT(LOWER(company_name)) LIKE UNACCENT(?)', [$term]))
                  ->orWhereHas('partnerAgreementService', fn($s) => $s->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term]))
                  ->orWhereHas('user', fn($u) => $u->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term]))
                  ->orWhereRaw("TO_CHAR(appointments.date, 'DD/MM/YYYY') LIKE ?", [$term])
                  ->orWhereRaw("TO_CHAR(COALESCE(appointments.requested_at, appointments.created_at), 'DD/MM/YYYY') LIKE ?", [$term]);
            });
        }

        return $query;
    }

    public function getAllExclusoesMes(): Collection
    {
        $now = Carbon::now();

        return User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereNotNull('excluded_at')
            ->whereYear('excluded_at', $now->year)
            ->whereMonth('excluded_at', $now->month)
            ->orderBy('excluded_at', 'desc')
            ->get();
    }
}