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\Log;
use Illuminate\Support\Facades\Storage;

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

    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())
            ->where('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')
            ->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)
            ->orderByDesc('is_primary')
            ->orderByDesc('id')
            ->first();

        $clientDistanceAddress = $this->addressForDistance($cliente->id, $clientPrimaryAddress);

        $clientCoordinates = $this->zipCodeCoordinatesService->resolve(
            $clientDistanceAddress?->latitude !== null ? (float) $clientDistanceAddress->latitude : null,
            $clientDistanceAddress?->longitude !== null ? (float) $clientDistanceAddress->longitude : null,
            $clientDistanceAddress?->zip_code,
        );

        $providersCloseDistanceSelect = $this->distanceSelect(
            $clientCoordinates['latitude'] ?? null,
            $clientCoordinates['longitude'] ?? null,
        );

        $providerAddressLatestSubquery = DB::raw("
                    (
                        SELECT DISTINCT ON (source_id)
                            *
                        FROM addresses
                        WHERE source = 'provider'
                        AND deleted_at IS NULL
                        ORDER BY
                            source_id,
                            (latitude IS NOT NULL AND longitude IS NOT NULL) DESC,
                            is_primary DESC,
                            id DESC
                    ) AS provider_address
                ");

        $providersCloseDebugQuery = Provider::leftJoin(
            'users as provider_user',
            'provider_user.id',
            '=',
            'providers.user_id'
        )
            ->visibleToCustomers()
            ->leftJoin(
                $providerAddressLatestSubquery,
                'provider_address.source_id',
                '=',
                'providers.id'
            )
            ->whereNull('providers.deleted_at');

        Log::info('Dashboard providersClose context', [
            'client_id'                       => $cliente->id,
            'user_id'                         => $user->id,
            'client_primary_address_id'       => $clientPrimaryAddress?->id,
            'client_primary_address_city_id'  => $clientPrimaryAddress?->city_id,
            'client_primary_address_state_id' => $clientPrimaryAddress?->state_id,
            'client_distance_address_id'      => $clientDistanceAddress?->id,
            'client_distance_zip_code'        => $clientDistanceAddress?->zip_code,
            'client_coordinates'              => $clientCoordinates,
            'blocked_provider_ids_count'      => $blockedProviderIds->count(),
            'providers_working_days_count'    => $providersWithWorkingDays->count(),
        ]);

        Log::info('Dashboard providersClose filter counts', [
            'visible_with_bank' => (clone $providersCloseDebugQuery)
                ->distinct('providers.id')
                ->count('providers.id'),

            'with_provider_address' => (clone $providersCloseDebugQuery)
                ->whereNotNull('provider_address.id')
                ->distinct('providers.id')
                ->count('providers.id'),

            'after_city_filter' => (clone $providersCloseDebugQuery)
                ->whereNotNull('provider_address.id')
                ->when(
                    $clientPrimaryAddress?->city_id,
                    fn ($query, int $cityId) => $query->where('provider_address.city_id', $cityId)
                )
                ->distinct('providers.id')
                ->count('providers.id'),

            'after_block_filter' => (clone $providersCloseDebugQuery)
                ->whereNotNull('provider_address.id')
                ->when(
                    $clientPrimaryAddress?->city_id,
                    fn ($query, int $cityId) => $query->where('provider_address.city_id', $cityId)
                )
                ->whereNotIn('providers.id', $blockedProviderIds)
                ->distinct('providers.id')
                ->count('providers.id'),

            'after_working_days_filter' => (clone $providersCloseDebugQuery)
                ->whereNotNull('provider_address.id')
                ->when(
                    $clientPrimaryAddress?->city_id,
                    fn ($query, int $cityId) => $query->where('provider_address.city_id', $cityId)
                )
                ->whereNotIn('providers.id', $blockedProviderIds)
                ->whereIn('providers.id', $providersWithWorkingDays)
                ->distinct('providers.id')
                ->count('providers.id'),
        ]);

        $providersClose = Provider::leftJoin(
            'users as provider_user',
            'provider_user.id',
            '=',
            'providers.user_id'
        )
            ->visibleToCustomers()
            ->leftJoin(
                $providerAddressLatestSubquery,
                'provider_address.source_id',
                '=',
                'providers.id'
            )
            ->leftJoin(
                'media as provider_media',
                'provider_media.id',
                '=',
                'providers.profile_media_id'
            )
            ->whereNotNull('provider_address.id')
            ->when(
                $clientPrimaryAddress?->city_id,
                fn ($query, int $cityId) => $query->where('provider_address.city_id', $cityId)
            )
            ->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.zip_code as provider_zip_code',
                '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',
            )
            ->orderByRaw('distance_km ASC NULLS LAST')
            ->get();

        Log::info('Dashboard providersClose query result', [
            'count'                => $providersClose->count(),
            'provider_ids_sample'  => $providersClose->pluck('provider_id')->take(20)->values()->all(),
            'null_distance_count'  => $providersClose->whereNull('distance_km')->count(),
            'provider_city_filter' => $clientPrimaryAddress?->city_id,
        ]);

        $this->zipCodeCoordinatesService->preload(
            $providersClose->whereNull('distance_km')->pluck('provider_zip_code')
        );

        $providersClose->each(function ($item) use ($clientDistanceAddress) {
            if ($item->distance_km === null) {
                $item->distance_km = $this->zipCodeCoordinatesService->calculateDistance(
                    $clientDistanceAddress?->latitude !== null ? (float) $clientDistanceAddress->latitude : null,
                    $clientDistanceAddress?->longitude !== null ? (float) $clientDistanceAddress->longitude : null,
                    $clientDistanceAddress?->zip_code,
                    $item->provider_latitude !== null ? (float) $item->provider_latitude : null,
                    $item->provider_longitude !== null ? (float) $item->provider_longitude : null,
                    $item->provider_zip_code,
                );
            }

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

            unset($item->provider_photo_path, $item->provider_zip_code);

            $item->daily_price_8h_base = $item->daily_price_8h;
            $item->daily_price_6h_base = $item->daily_price_6h;
            $item->daily_price_4h_base = $item->daily_price_4h;
            $item->daily_price_2h_base = $item->daily_price_2h;

            $item->daily_price_8h = $this->applyCreditCardFee($item->daily_price_8h);
            $item->daily_price_6h = $this->applyCreditCardFee($item->daily_price_6h);
            $item->daily_price_4h = $this->applyCreditCardFee($item->daily_price_4h);
            $item->daily_price_2h = $this->applyCreditCardFee($item->daily_price_2h);
        });

        $providersClose = $providersClose
            ->sortBy(fn ($provider) => $provider->distance_km ?? PHP_FLOAT_MAX)
            ->values();

        Log::info('Dashboard providersClose final result', [
            'count' => $providersClose->count(),
            'items_sample' => $providersClose->take(5)->map(fn ($provider) => [
                'provider_id'   => $provider->provider_id,
                'provider_name' => $provider->provider_name,
                'district'      => $provider->district,
                'distance_km'   => $provider->distance_km,
            ])->values()->all(),
        ]);

        $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(
            $clientCoordinates['latitude'] ?? null,
            $clientCoordinates['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',

                'provider_address.latitude as provider_latitude',
                'provider_address.longitude as provider_longitude',
                'provider_address.zip_code as provider_zip_code',

                $proposalsDistanceSelect,

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

        $this->zipCodeCoordinatesService->preload(
            $schedulesProposals->whereNull('distance_km')->pluck('provider_zip_code')
        );

        $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) use ($clientDistanceAddress) {
            if ($item->distance_km === null) {
                $item->distance_km = $this->zipCodeCoordinatesService->calculateDistance(
                    $clientDistanceAddress?->latitude !== null ? (float) $clientDistanceAddress->latitude : null,
                    $clientDistanceAddress?->longitude !== null ? (float) $clientDistanceAddress->longitude : null,
                    $clientDistanceAddress?->zip_code,
                    $item->provider_latitude !== null ? (float) $item->provider_latitude : null,
                    $item->provider_longitude !== null ? (float) $item->provider_longitude : null,
                    $item->provider_zip_code,
                );
            }

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

            unset(
                $item->provider_photo_path,
                $item->provider_latitude,
                $item->provider_longitude,
                $item->provider_zip_code,
            );
        });

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

        $solicitations->each(function ($solicitation) use ($address) {
            $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 = $this->zipCodeCoordinatesService->calculateDistance(
                $address?->latitude !== null ? (float) $address->latitude : null,
                $address?->longitude !== null ? (float) $address->longitude : null,
                $address?->zip_code,
                $solicitation->address?->latitude  !== null ? (float) $solicitation->address->latitude  : null,
                $solicitation->address?->longitude !== null ? (float) $solicitation->address->longitude : null,
                $solicitation->address?->zip_code,
            );
        });

        $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,zip_code,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 ($address) {
            $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 = $this->zipCodeCoordinatesService->calculateDistance(
                $address?->latitude !== null ? (float) $address->latitude : null,
                $address?->longitude !== null ? (float) $address->longitude : null,
                $address?->zip_code,
                $schedule->address?->latitude  !== null ? (float) $schedule->address->latitude  : null,
                $schedule->address?->longitude !== null ? (float) $schedule->address->longitude : null,
                $schedule->address?->zip_code,
            );
        });

        $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,
        ];
    }

    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,
        ];
    }

    //

    private function applyCreditCardFee(?float $price): ?float
    {
        if ($price === null) {
            return null;
        }

        $rate = $this->platformCreditCardFeeRate();

        return round($price * (1 + $rate), 2);
    }

    private function platformCreditCardFeeRate(): float
    {
        $rate = config('services.pagarme.platform_credit_card_fee_rate', 0.16);

        if (is_string($rate)) {
            $rate = str_replace(',', '.', trim($rate));
        }

        $rate = (float) $rate;

        return $rate > 1 ? $rate / 100 : $rate;
    }

    private function addressForDistance(int $clientId, ?Address $primaryAddress): ?Address
    {
        if ($primaryAddress?->latitude !== null && $primaryAddress?->longitude !== null) {
            return $primaryAddress;
        }

        return Address::where('source', 'client')
            ->where('source_id', $clientId)
            ->whereNotNull('latitude')
            ->whereNotNull('longitude')
            ->orderByDesc('is_primary')
            ->orderByDesc('id')
            ->first() ?? $primaryAddress;
    }

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