sfp_api_laravel_diarista/app/Services/DashboardService.php

<?php

namespace App\Services;

use App\Models\Address;
use App\Models\Client;
use App\Models\ClientFavoriteProvider;
use App\Models\ClientPaymentMethod;
use App\Models\Provider;
use App\Models\ProviderSpeciality;
use App\Models\Review;
use App\Models\Schedule;
use App\Models\ScheduleProposal;
use App\Models\Speciality;
use App\Rules\ScheduleBusinessRules;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class DashboardService
{
    public function __construct(
        private readonly CustomScheduleService $customScheduleService,
    ) {}

    public function dadosDashboardCliente(): array
    {
        $user = Auth::user();

        $cliente = Client::where('user_id', $user->id)->first();

        $headerBar = [
            'rating'        => $cliente->average_rating,
            'total_ratings' => Review::where('reviews.origin', 'provider')
                ->leftJoin('schedules', 'schedules.id', '=', 'reviews.schedule_id')
                ->where('schedules.client_id', $cliente->id)
                ->count(),
            'total_services' => $cliente->total_services,
        ];

        $address = Address::where('source', 'client')
            ->where('source_id', $cliente->id)
            ->with(['city', 'state'])
            ->select('id', 'source', 'source_id', 'address', 'number', 'district', 'nickname', 'address_type', 'city_id', 'state_id', 'is_primary')
            ->first();

        $summaryInfos = [
            'name'             => $user->name,
            'address'          => $address,
            'pending_services' => Schedule::where('client_id', $cliente->id)
                ->where('status', 'pending')
                ->count(),
        ];

        $nextSchedules = Schedule::with('address:district,address,source_id,source,id,address_type')
            ->where('schedules.client_id', $cliente->id)
            ->whereIn('schedules.status', ['accepted', 'paid'])
            ->whereDate('schedules.date', '>=', now()->toDateString())
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin('custom_schedules', 'custom_schedules.schedule_id', '=', 'schedules.id')
            ->where('schedules.date', '>=', now()->toDateString())
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'schedules.date',
                'schedules.start_time',
                'schedules.end_time',
                'schedules.total_amount',
                'schedules.period_type',
                'schedules.schedule_type',
                'schedules.address_id',
                'custom_schedules.address_type as custom_address_type',
            )
            ->orderBy('schedules.date', 'asc')
            ->limit(5)
            ->get();

        $lastDoneSchedules = Schedule::where('schedules.client_id', $cliente->id)
            ->where('schedules.status', 'finished')
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin('addresses as provider_address', function ($join) {
                $join->on('provider_address.source_id', '=', 'providers.id')
                    ->where('provider_address.source', 'provider')
                    ->orderBy('provider_address.is_primary', 'desc');
            })
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'provider_address.district as provider_district',
            )
            ->orderBy('schedules.date', 'desc')
            ->limit(5)
            ->get();

        $favoriteProviders = ClientFavoriteProvider::where('client_favorite_providers.client_id', $cliente->id)
            ->leftJoin('providers', 'providers.id', '=', 'client_favorite_providers.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin('addresses as provider_address', function ($join) {
                $join->on('provider_address.source_id', '=', 'providers.id')
                    ->where('provider_address.source', 'provider')
                    ->orderBy('provider_address.is_primary', 'desc');
            })
            ->select(
                'providers.id as provider_id',
                'provider_user.name as provider_name',
                'providers.average_rating',
                'provider_address.district as provider_district',
            )
            ->orderBy('client_favorite_providers.created_at', 'desc')
            ->limit(5)
            ->get();

        $blockedProviderIds = ScheduleBusinessRules::getBlockedProviderIdsForClient($cliente->id);
        $providersWithWorkingDays = ScheduleBusinessRules::getProviderIdsWithWorkingDays();

        $clientPrimaryAddress = Address::where('source', 'client')
            ->where('source_id', $cliente->id)
            ->where('is_primary', true)
            ->first();

        $providersCloseDistanceSelect = $this->distanceSelect(
            $clientPrimaryAddress?->latitude !== null ? (float) $clientPrimaryAddress->latitude : null,
            $clientPrimaryAddress?->longitude !== null ? (float) $clientPrimaryAddress->longitude : null,
        );

        $providersClose = Provider::leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin(DB::raw("
        (
          SELECT DISTINCT ON (source_id)
            *
          FROM addresses
          WHERE source = 'provider'
          AND deleted_at IS NULL
          ORDER BY source_id, is_primary DESC
        ) as provider_address
      "), 'provider_address.source_id', '=', 'providers.id')
            ->whereNotNull('provider_address.id')
            ->where('provider_address.city_id', $clientPrimaryAddress?->city_id)
            ->whereNotIn('providers.id', $blockedProviderIds)
            ->whereIn('providers.id', $providersWithWorkingDays)
            ->whereNull('providers.deleted_at')
            ->select(
                'providers.id as provider_id',
                'provider_user.name as provider_name',
                'provider_address.id as address_id',
                'provider_address.district',
                'provider_address.latitude as provider_latitude',
                'provider_address.longitude as provider_longitude',
                'providers.average_rating',
                'providers.total_services',
                'providers.daily_price_8h',
                'providers.daily_price_6h',
                'providers.daily_price_4h',
                'providers.daily_price_2h',
                DB::raw("(
          SELECT COUNT(*)
          FROM reviews
          LEFT JOIN schedules ON schedules.id = reviews.schedule_id
          WHERE reviews.origin = 'provider'
          AND schedules.provider_id = providers.id
        ) as total_reviews"),
                $providersCloseDistanceSelect,
            )
            ->get();

        $pendingSchedules = Schedule::with('address:district,address,number,source_id,source,id,address_type')
            ->where('schedules.client_id', $cliente->id)
            ->whereIn('schedules.status', ['pending', 'accepted'])
            ->where('schedules.schedule_type', 'default')
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'schedules.date',
                'schedules.address_id',
                'schedules.status',
                'schedules.total_amount',
                'schedules.start_time',
                'schedules.end_time',
                DB::raw("CASE
          WHEN (now() - schedules.created_at) < INTERVAL '1 hour' THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (now() - schedules.created_at)) / 60), 'min')
          WHEN (now() - schedules.created_at) < INTERVAL '1 day' THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (now() - schedules.created_at)) / 3600), 'h')
          ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (now() - schedules.created_at)) / 86400), 'd')
        END as time_since_request")
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $schedulesProposals = ScheduleProposal::query()
            ->leftJoin('schedules', 'schedule_proposals.schedule_id', '=', 'schedules.id')
            ->leftJoin('providers', 'schedule_proposals.provider_id', '=', 'providers.id')
            ->leftJoin('users', 'providers.user_id', '=', 'users.id')
            ->where('schedules.client_id', $cliente->id)
            ->where('schedules.schedule_type', 'custom')
            ->where('schedules.status', 'pending')
            ->whereNull('schedules.deleted_at')
            ->whereDate('schedules.date', '>=', now()->toDateString())
            ->orderBy('schedule_proposals.created_at', 'desc')
            ->select([
                'schedule_proposals.id',

                DB::raw("DATE_PART('year', AGE(providers.birth_date)) as idade"),
                'providers.id as provider_id',
                'schedules.id as schedule_id',
                'schedules.date',
                'schedules.start_time',
                'schedules.end_time',
                'schedules.period_type',
                'schedules.total_amount',
                'providers.daily_price_8h',
                'providers.average_rating',
                'providers.total_services',

                'users.name as provider_name',
            ])
            ->get();

        $todaySchedules = Schedule::with('address:district,address,number,source_id,source,id,address_type')
            ->where('schedules.client_id', $cliente->id)
            ->whereIn('schedules.status', ['accepted', 'paid', 'started', 'finished'])
            ->whereDate('schedules.date', now()->toDateString())
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin('media', 'media.id', '=', 'providers.profile_media_id')
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'schedules.date',
                'schedules.start_time',
                'schedules.end_time',
                'schedules.total_amount',
                'schedules.period_type',
                'schedules.schedule_type',
                'schedules.address_id',
                'schedules.status',
                'schedules.code_verified',
                'schedules.code',
                'media.url as provider_photo',
                DB::raw("EXISTS(
          SELECT 1 FROM reviews
          WHERE reviews.schedule_id = schedules.id
            AND reviews.origin = 'client'
            AND reviews.origin_id = {$cliente->id}
            AND reviews.deleted_at IS NULL
        ) as client_reviewed"),
            )
            ->orderBy('schedules.start_time', 'asc')
            ->get();

        $hasPaymentMethods = ClientPaymentMethod::where('client_id', $cliente->id)->exists();

        return [
            'headerBar'           => $headerBar,
            'summaryInfos'        => $summaryInfos,
            'pendingSchedules'    => $pendingSchedules,
            'nextSchedules'       => $nextSchedules,
            'lastDoneSchedules'   => $lastDoneSchedules,
            'favoriteProviders'   => $favoriteProviders,
            'providersClose'      => $providersClose,
            'todaySchedules'      => $todaySchedules,
            'schedulesProposals'  => $schedulesProposals,
            'has_payment_methods' => $hasPaymentMethods,
        ];
    }

    public function getScheduleClienteDetails(int $scheduleId): array
    {
        $user = Auth::user();

        $cliente = Client::where('user_id', $user->id)->firstOrFail();

        $schedule = Schedule::where('schedules.id', $scheduleId)
            ->where('schedules.client_id', $cliente->id)
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin('custom_schedules', 'custom_schedules.schedule_id', '=', 'schedules.id')
            ->leftJoin('media', 'media.id', '=', 'providers.profile_media_id')
            ->select(
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'providers.birth_date as provider_birth_date',
                'media.url as provider_photo',
                'custom_schedules.offers_meal',
            )
            ->firstOrFail();

        $allSpecialities = Speciality::where('active', true)
            ->select('id', 'description')
            ->orderBy('description')
            ->get();

        $providerSpecialityIds = ProviderSpeciality::where('provider_id', $schedule->provider_id)
            ->pluck('speciality_id')
            ->all();

        $specialities = $allSpecialities->map(fn ($sp) => [
            'id'             => $sp->id,
            'description'    => $sp->description,
            'has_speciality' => in_array($sp->id, $providerSpecialityIds),
        ])->values();

        return [
            'provider_name'       => $schedule->provider_name,
            'provider_birth_date' => $schedule->provider_birth_date,
            'provider_photo'      => $schedule->provider_photo,
            'offers_meal'         => $schedule->offers_meal,
            'specialities'        => $specialities,
        ];
    }

    public function dadosDashboardPrestador(): array
    {
        $user = Auth::user();

        $provider = Provider::where('user_id', $user->id)->first();

        $headerBar = [
            'rating'         => $provider->average_rating,
            'total_ratings'  => Review::where('reviews.origin', 'client')->leftJoin('schedules', 'schedules.id', '=', 'reviews.schedule_id')->where('schedules.provider_id', $provider->id)->count(),
            'total_services' => $provider->total_services,
        ];

        $address = Address::where('source', 'provider')->where('source_id', $provider->id)->with(['city', 'state'])->first();

        $summaryInfos = [
            'name'             => $user->name,
            'address'          => $address,
            'pending_services' => Schedule::where('provider_id', $provider->id)->where('status', 'pending')->count(),
        ];

        $priceSuggestedAvg = Provider::where('user_id', '!=', $user->id)
            ->whereNotNull('daily_price_8h')
            ->pluck('daily_price_8h')
            ->avg();

        $priceActual = $provider->daily_price_8h;

        $priceSuggested = [
            'average_price' => $priceSuggestedAvg,
            'your_price'    => $priceActual,
        ];

        $solicitations = Schedule::with('address:district,source_id,source,id')
            ->where('schedules.provider_id', $provider->id)
            ->where('schedules.status', 'pending')
            ->leftJoin('clients', 'clients.id', '=', 'schedules.client_id')
            ->leftJoin('users as client_user', 'client_user.id', '=', 'clients.user_id')
            ->leftJoin('custom_schedules', 'custom_schedules.schedule_id', '=', 'schedules.id')
            ->select(
                'schedules.id',
                'client_user.name as client_name',
                'clients.average_rating',
                'schedules.date',
                DB::raw("TO_CHAR(schedules.date, 'DD/MM/YYYY') as formatted_date"),
                'schedules.start_time',
                'schedules.end_time',
                'schedules.total_amount',
                'schedules.period_type',
                'schedules.schedule_type',
                'schedules.address_id',
                'schedules.status',
                'custom_schedules.offers_meal',
                DB::raw("CASE
          WHEN (now() - schedules.created_at) < INTERVAL '1 day' THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (now() - schedules.created_at)) / 3600), ' hours ago')
          ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (now() - schedules.created_at)) / 86400), ' days ago')
        END as time_since_request"),
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $todayServices = Schedule::with('address:district,address,number,source_id,source,id')
            ->where('schedules.provider_id', $provider->id)
            ->whereIn('schedules.status', ['accepted', 'paid', 'started', 'finished'])
            ->whereDate('schedules.date', now()->toDateString())
            ->leftJoin('clients', 'clients.id', '=', 'schedules.client_id')
            ->leftJoin('users as client_user', 'client_user.id', '=', 'clients.user_id')
            ->leftJoin('custom_schedules', 'custom_schedules.schedule_id', '=', 'schedules.id')
            ->select(
                'schedules.id',
                'schedules.client_id',
                'client_user.name as client_name',
                'schedules.date',
                'schedules.start_time',
                'schedules.end_time',
                'schedules.total_amount',
                'schedules.period_type',
                'schedules.address_id',
                'schedules.schedule_type',
                'schedules.status',
                'schedules.code_verified',
                'schedules.code',
                'custom_schedules.offers_meal',
                DB::raw("EXISTS(
          SELECT 1 FROM reviews
          WHERE reviews.schedule_id = schedules.id
            AND reviews.origin = 'provider'
            AND reviews.origin_id = {$provider->id}
            AND reviews.deleted_at IS NULL
        ) as provider_reviewed"),
            )
            ->orderBy('schedules.start_time', 'asc')
            ->get();

        $nextSchedules = Schedule::with('address:district,address,number,source_id,source,id')
            ->where('schedules.provider_id', $provider->id)
            ->whereIn('schedules.status', ['accepted', 'paid'])
            ->whereDate('schedules.date', '>=', now()->toDateString())
            ->leftJoin('clients', 'clients.id', '=', 'schedules.client_id')
            ->leftJoin('users as client_user', 'client_user.id', '=', 'clients.user_id')
            ->leftJoin('custom_schedules', 'custom_schedules.schedule_id', '=', 'schedules.id')
            ->select(
                'schedules.id',
                'client_user.name as client_name',
                'schedules.date',
                'schedules.start_time',
                'schedules.end_time',
                'schedules.total_amount',
                'schedules.period_type',
                'schedules.address_id',
                'schedules.schedule_type',
                'schedules.status',
                'custom_schedules.offers_meal',
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        // $opportunities = Schedule::with('address:district,source_id,source,id')
        //   ->where('schedules.schedule_type', 'custom')
        //   ->where('schedules.status', 'pending')
        //   ->whereDate('schedules.date', '>=', now()->toDateString())
        //   ->leftJoin('clients', 'clients.id', '=', 'schedules.client_id')
        //   ->leftJoin('users as client_user', 'client_user.id', '=', 'clients.user_id')
        //   ->leftJoin('custom_schedules', 'custom_schedules.schedule_id', '=', 'schedules.id')
        //   ->select(
        //     'schedules.id',
        //     'custom_schedules.id as custom_schedule_id',
        //     'client_user.name as client_name',
        //     'clients.average_rating',
        //     'schedules.date',
        //     'schedules.start_time',
        //     'schedules.end_time',
        //     'schedules.period_type',
        //     'schedules.schedule_type',
        //     'schedules.address_id',
        //     'custom_schedules.address_type',
        //     DB::raw("CASE
        //       WHEN schedules.period_type = '2' THEN {$provider->daily_price_2h}
        //       WHEN schedules.period_type = '4' THEN {$provider->daily_price_4h}
        //       WHEN schedules.period_type = '6' THEN {$provider->daily_price_6h}
        //       WHEN schedules.period_type = '8' THEN {$provider->daily_price_8h}
        //       ELSE 0
        //     END as total_amount"),
        //   )
        //   ->orderBy('schedules.date', 'asc')
        //   ->get();

        $opportunities = $this->customScheduleService->getAvailableOpportunities($provider->id);

        return [
            'headerBar'      => $headerBar,
            'summaryInfos'   => $summaryInfos,
            'priceSuggested' => $priceSuggested,
            'solicitations'  => $solicitations,
            'todayServices'  => $todayServices,
            'nextSchedules'  => $nextSchedules,
            'opportunities'  => $opportunities,
        ];
    }

    private function distanceSelect(?float $clientLatitude, ?float $clientLongitude): \Illuminate\Contracts\Database\Query\Expression
    {
        if ($clientLatitude === null || $clientLongitude === null) {
            return DB::raw('NULL as distance_km');
        }

        return DB::raw("
            CASE
                WHEN provider_address.latitude IS NOT NULL
                AND provider_address.longitude IS NOT NULL
                THEN ROUND((
                    6371 * acos(
                        least(1, greatest(-1,
                            cos(radians({$clientLatitude}))
                            * cos(radians(provider_address.latitude))
                            * cos(radians(provider_address.longitude) - radians({$clientLongitude}))
                            + sin(radians({$clientLatitude}))
                            * sin(radians(provider_address.latitude))
                        ))
                    )
                )::numeric, 1)
                ELSE NULL
            END as distance_km
        ");
    }
}