sfp_api_laravel_diarista/app/Services/DashboardService.php

<?php

namespace App\Services;

use App\Enums\UserTypeEnum;
use App\Models\Address;
use App\Models\Client;
use App\Models\ClientFavoriteProvider;
use App\Models\ClientPaymentMethod;
use App\Models\Notification;
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\Auth\Access\AuthorizationException;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;

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

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

        if ($user->type !== UserTypeEnum::CLIENT) {
            throw new AuthorizationException('Apenas clientes podem acessar este recurso.');
        }

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

        $headerBar = [
            'rating'         => $cliente->average_rating,
            'total_services' => $cliente->total_services,

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

        $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,

            'profile_photo' => $cliente->profileMedia?->path
                ? Storage::temporaryUrl($cliente->profileMedia->path, now()->addMinutes(60))
                : null,

            'pending_services' => Schedule::where('client_id', $cliente->id)
                ->whereIn('status', ['pending', 'paid', 'accepted'])
                ->whereDate('date', '>=', now()->toDateString())
                ->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')
            ->leftJoin('media as provider_media', 'provider_media.id', '=', 'providers.profile_media_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',
                'provider_media.path as provider_photo_path',

                DB::raw("(SELECT ci.cart_id FROM cart_items ci WHERE ci.schedule_id = schedules.id LIMIT 1) as cart_id"),

                DB::raw(
                    "(SELECT COUNT(*) FROM cart_items ci_count
                        WHERE ci_count.cart_id = (
                            SELECT ci.cart_id FROM cart_items ci
                                WHERE ci.schedule_id = schedules.id
                                    LIMIT 1
                        )
                    ) as cart_items_count"
                ),
            )
            ->orderBy('schedules.date', 'asc')
            ->limit(5)
            ->get();

        $nextSchedules->each(function ($item) {
            $item->provider_photo = $item->provider_photo_path
                ? Storage::temporaryUrl($item->provider_photo_path, now()->addMinutes(60))
                : null;

            unset($item->provider_photo_path);
        });

        $latestPerProvider = Schedule::where('client_id', $cliente->id)
            ->where('status', 'finished')
            ->select('provider_id', DB::raw('MAX(id) as max_id'))
            ->groupBy('provider_id');

        $lastDoneSchedules = Schedule::joinSub($latestPerProvider, 'latest', function ($join) {
                $join->on('schedules.id', '=', 'latest.max_id');
            })
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoinSub(Address::preferredForProvider(), 'provider_address', fn($join) =>
                $join->on('provider_address.source_id', '=', 'providers.id')
                    ->where('provider_address.rn', 1)
            )
            ->leftJoin('media as provider_media', 'provider_media.id', '=', 'providers.profile_media_id')
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'provider_address.district as provider_district',
                'provider_media.path as provider_photo_path',
            )
            ->orderBy('schedules.date', 'desc')
            ->limit(5)
            ->get();

        $lastDoneSchedules->each(function ($item) {
            $item->provider_photo = $item->provider_photo_path
                ? Storage::temporaryUrl($item->provider_photo_path, now()->addMinutes(60))
                : null;

            unset($item->provider_photo_path);
        });

        $favoriteProviders = ClientFavoriteProvider::where('client_favorite_providers.client_id', $cliente->id)
            ->leftJoin('providers', 'providers.id', '=', 'client_favorite_providers.provider_id')
            ->whereNotNull('providers.recipient_default_bank_account')
            ->whereRaw("providers.recipient_default_bank_account::text <> '{}'")
            ->whereRaw("providers.recipient_default_bank_account::text <> '[]'")
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoinSub(Address::preferredForProvider(), 'provider_address', fn($join) =>
                $join->on('provider_address.source_id', '=', 'providers.id')
                    ->where('provider_address.rn', 1)
            )
            ->leftJoin('media as provider_media', 'provider_media.id', '=', 'providers.profile_media_id')
            ->select(
                'providers.id as provider_id',
                'provider_user.name as provider_name',
                'providers.average_rating',
                'provider_address.district as provider_district',
                'provider_media.path as provider_photo_path',
            )
            ->orderBy('client_favorite_providers.created_at', 'desc')
            ->limit(5)
            ->get();

        $favoriteProviders->each(function ($item) {
            $item->provider_photo = $item->provider_photo_path
                ? Storage::temporaryUrl($item->provider_photo_path, now()->addMinutes(60))
                : null;

            unset($item->provider_photo_path);
        });

        $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')
            ->visibleToCustomers()
            ->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'
            )
            ->leftJoin('media as provider_media', 'provider_media.id', '=', 'providers.profile_media_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,

                'provider_media.path as provider_photo_path',
            )
            ->orderBy('distance_km', 'asc')
            ->get();

        $providersClose->each(function ($item) {
            $item->provider_photo = $item->provider_photo_path
                ? Storage::temporaryUrl($item->provider_photo_path, now()->addMinutes(60))
                : null;

            unset($item->provider_photo_path);
        });

        $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')
            ->leftJoin('media as provider_media', 'provider_media.id', '=', 'providers.profile_media_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"
                ),

                'provider_media.path as provider_photo_path',

                DB::raw("(SELECT ci.cart_id FROM cart_items ci WHERE ci.schedule_id = schedules.id LIMIT 1) as cart_id"),

                DB::raw(
                    "(SELECT COUNT(*) FROM cart_items ci_count
                        WHERE ci_count.cart_id = (
                            SELECT ci.cart_id FROM cart_items ci
                                WHERE ci.schedule_id = schedules.id
                                    LIMIT 1
                        )
                    ) as cart_items_count"
                ),
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $pendingSchedules->each(function ($item) {
            $item->provider_photo = $item->provider_photo_path
                ? Storage::temporaryUrl($item->provider_photo_path, now()->addMinutes(60))
                : null;

            unset($item->provider_photo_path);
        });

        $proposalsDistanceSelect = DistanceService::sqlExpression(
            $clientPrimaryAddress?->latitude  !== null ? (float) $clientPrimaryAddress->latitude  : null,
            $clientPrimaryAddress?->longitude !== null ? (float) $clientPrimaryAddress->longitude : null,
        );

        $schedulesProposals = ScheduleProposal::query()
            ->leftJoin('schedules', 'schedule_proposals.schedule_id', '=', 'schedules.id')
            ->leftJoin('providers', 'schedule_proposals.provider_id', '=', 'providers.id')
            ->whereNotNull('providers.recipient_default_bank_account')
            ->whereRaw("providers.recipient_default_bank_account::text <> '{}'")
            ->whereRaw("providers.recipient_default_bank_account::text <> '[]'")
            ->leftJoin('users', 'providers.user_id', '=', 'users.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'
            )
            ->leftJoin('media as provider_media', 'provider_media.id', '=', 'providers.profile_media_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',

                $proposalsDistanceSelect,

                'provider_media.path as provider_photo_path',
            ])
            ->get();

        $custom_schedules_with_no_proposals = Schedule::where('client_id', $cliente->id)
            ->where('schedule_type', 'custom')
            ->where('status', 'pending')
            ->whereDate('date', '>=', now()->toDateString())
            ->doesntHave('proposals')
            ->get();

        $schedulesProposals->each(function ($item) {
            $item->provider_photo = $item->provider_photo_path
                ? Storage::temporaryUrl($item->provider_photo_path, now()->addMinutes(60))
                : null;

            unset($item->provider_photo_path);
        });

        $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.path as provider_photo',

                DB::raw("(SELECT ci.cart_id FROM cart_items ci WHERE ci.schedule_id = schedules.id LIMIT 1) as cart_id"),

                DB::raw(
                    "(SELECT COUNT(*) FROM cart_items ci_count
                        WHERE ci_count.cart_id = (
                            SELECT ci.cart_id FROM cart_items ci
                                WHERE ci.schedule_id = schedules.id
                                    LIMIT 1
                        )
                    ) as cart_items_count"
                ),

                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()
            ->map(function ($item) {
                $item->provider_photo = $item->provider_photo
                    ? Storage::temporaryUrl($item->provider_photo, now()->addMinutes(60))
                    : null;

                return $item;
            });

        $notifications = Notification::where('user_id', $user->id)
            ->orderBy('read', 'asc')
            ->orderBy('created_at', 'desc')
            ->limit(10)
            ->get()
            ->map(function ($notification) {
                return [
                    'id'          => $notification->id,
                    'title'       => $notification->title,
                    'description' => $notification->description,
                    'time'        => $notification->created_at->diffForHumans(),
                    'read'        => $notification->read,
                    'avatar'      => '/icons/avatar.svg',
                ];
            });

        $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,
            'customSchedulesNoProposals' => $custom_schedules_with_no_proposals,
            'notifications'       => $notifications,
            '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.path 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,
            'offers_meal'         => $schedule->offers_meal,
            'specialities'        => $specialities,

            'provider_photo' => $schedule->provider_photo
                ? Storage::temporaryUrl($schedule->provider_photo, now()->addMinutes(60))
                : null,
        ];
    }

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

        if ($user->type !== UserTypeEnum::PROVIDER) {
            throw new AuthorizationException('Apenas prestadores podem acessar este recurso.');
        }

        $provider = Provider::with('profileMedia')->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(),

            'profile_photo' => $provider->profileMedia?->path
                ? Storage::temporaryUrl($provider->profileMedia->path, now()->addMinutes(60))
                : null,
        ];

        $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,latitude,longitude')
            ->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('media as client_media', 'client_media.id', '=', 'clients.profile_media_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',
                'client_media.path as customer_photo_path',

                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();

        $providerLat = $address?->latitude  !== null ? (float) $address->latitude  : null;
        $providerLng = $address?->longitude !== null ? (float) $address->longitude : null;

        $solicitations->each(function ($solicitation) use ($providerLat, $providerLng) {
            $solicitation->customer_photo = $solicitation->customer_photo_path
                ? Storage::temporaryUrl($solicitation->customer_photo_path, now()->addMinutes(60))
                : null;

            unset($solicitation->customer_photo_path);

            $solicitation->distance_km = DistanceService::calculate(
                $providerLat,
                $providerLng,
                $solicitation->address?->latitude  !== null ? (float) $solicitation->address->latitude  : null,
                $solicitation->address?->longitude !== null ? (float) $solicitation->address->longitude : null,
            );
        });

        $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('media as client_media', 'client_media.id', '=', 'clients.profile_media_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',
                'client_media.path as customer_photo_path',

                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();

        $todayServices->each(function ($s) {
            $s->customer_photo = $s->customer_photo_path
                ? Storage::temporaryUrl($s->customer_photo_path, now()->addMinutes(60))
                : null;

            unset($s->customer_photo_path);
        });

        $nextSchedules = Schedule::with('address:district,address,number,source_id,source,id,latitude,longitude')
            ->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('media as client_media', 'client_media.id', '=', 'clients.profile_media_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',
                'client_media.path as customer_photo_path',
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $nextSchedules->each(function ($schedule) use ($providerLat, $providerLng) {
            $schedule->customer_photo = $schedule->customer_photo_path
                ? Storage::temporaryUrl($schedule->customer_photo_path, now()->addMinutes(60))
                : null;

            unset($schedule->customer_photo_path);

            $schedule->distance_km = DistanceService::calculate(
                $providerLat,
                $providerLng,
                $schedule->address?->latitude  !== null ? (float) $schedule->address->latitude  : null,
                $schedule->address?->longitude !== null ? (float) $schedule->address->longitude : null,
            );
        });

        // $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();

        $notifications = Notification::where('user_id', $user->id)
            ->orderBy('read', 'asc')
            ->orderBy('created_at', 'desc')
            ->limit(10)
            ->get()
            ->map(function ($notification) {
                return [
                    'id'          => $notification->id,
                    'title'       => $notification->title,
                    'description' => $notification->description,
                    'time'        => $notification->created_at->diffForHumans(),
                    'read'        => $notification->read,
                    'avatar'      => '/icons/avatar.svg',
                ];
            });

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

        $opportunities->each(function ($o) {
            $o->customer_photo = $o->client?->profileMedia?->path
                ? Storage::temporaryUrl($o->client->profileMedia->path, now()->addMinutes(60))
                : null;
        });

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

    private function distanceSelect(?float $clientLatitude, ?float $clientLongitude): \Illuminate\Contracts\Database\Query\Expression
    {
        return DistanceService::sqlExpression($clientLatitude, $clientLongitude);
    }
}