sfp_api_laravel_diarista/app/Services/DashboardService.php

<?php

namespace App\Services;

use App\Enums\ApprovalStatusEnum;
use App\Enums\GenderEnum;
use App\Enums\ServicePackageStatusEnum;
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\ServicePackage;
use App\Rules\ScheduleBusinessRules;
use Illuminate\Auth\Access\AuthorizationException;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;

class DashboardService
{
    private const NEARBY_RADIUS_KM = 20.0;

    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(__('messages.only_clients_allowed'));
        }

        $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')
            ->orderByDesc('is_primary')
            ->orderByDesc('id')
            ->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)
            ->where('schedules.status', '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')
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'providers.gender',
                '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',

                DB::raw("
                (
                    SELECT spi.service_package_id
                    FROM service_package_items spi
                    WHERE spi.schedule_id = schedules.id
                    LIMIT 1
                ) AS service_package_id
            "),

                DB::raw("
                (
                    SELECT COUNT(*)
                    FROM service_package_items spi_count
                    JOIN schedules s_count ON s_count.id = spi_count.schedule_id
                        AND s_count.status NOT IN ('cancelled', 'rejected')
                        AND s_count.schedule_type = 'default'
                    WHERE spi_count.service_package_id = (
                        SELECT spi.service_package_id
                        FROM service_package_items spi
                        WHERE spi.schedule_id = schedules.id
                        LIMIT 1
                    )
                ) AS service_package_items_count
            "),
            )
            ->orderBy('schedules.date', 'asc')
            ->limit(5)
            ->get();

        $nextSchedules->each(function ($item) {
            $item->gender_label = GenderEnum::labelFor($item->gender);
        });

        $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)
            )
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'provider_user.name as provider_name',
                'providers.gender',
                'provider_address.district as provider_district',
            )
            ->orderBy('schedules.date', 'desc')
            ->limit(5)
            ->get();

        $lastDoneSchedules->each(function ($item) {
            $item->gender_label = GenderEnum::labelFor($item->gender);
        });

        $favoriteProviders = ClientFavoriteProvider::where('client_favorite_providers.client_id', $cliente->id)
            ->leftJoin('providers', 'providers.id', '=', 'client_favorite_providers.provider_id')
            ->whereExists(Provider::hasActivePrimaryBankAccount())
            ->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)
            )
            ->select(
                'providers.id as provider_id',
                'provider_user.name as provider_name',
                'providers.gender',
                'providers.average_rating',
                'provider_address.district as provider_district',
            )
            ->orderBy('client_favorite_providers.created_at', 'desc')
            ->limit(5)
            ->get();

        $favoriteProviders->each(function ($item) {
            $item->gender_label = GenderEnum::labelFor($item->gender);
        });

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

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

        $providersCloseCityId    = $clientPrimaryAddress?->city_id;
        $providersCloseLatitude  = $clientPrimaryAddress?->latitude !== null ? (float) $clientPrimaryAddress->latitude : null;
        $providersCloseLongitude = $clientPrimaryAddress?->longitude !== null ? (float) $clientPrimaryAddress->longitude : null;

        $hasLocation = $providersCloseLatitude !== null && $providersCloseLongitude !== null;

        $providersCloseDistanceSelect = $this->distanceSelect(
            $providersCloseLatitude,
            $providersCloseLongitude,
        );

        $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
        ");

        $providersClose = $hasLocation
            ? Provider::leftJoin(
                'users as provider_user',
                'provider_user.id',
                '=',
                'providers.user_id'
            )
                ->visibleToCustomers()
                ->leftJoin(
                    $providerAddressLatestSubquery,
                    'provider_address.source_id',
                    '=',
                    'providers.id'
                )
                ->whereNotNull('provider_address.id')
                ->where(function ($query) use ($providersCloseCityId, $providersCloseLatitude, $providersCloseLongitude) {
                    if ($providersCloseCityId !== null) {
                        $query->orWhere('provider_address.city_id', $providersCloseCityId);
                    }

                    if ($providersCloseLatitude !== null && $providersCloseLongitude !== null) {
                        $query->orWhereRaw(
                            DistanceService::withinRadiusSqlCondition(
                                (float) $providersCloseLatitude,
                                (float) $providersCloseLongitude,
                                self::NEARBY_RADIUS_KM,
                            )
                        );
                    }
                })
                ->whereNotIn('providers.id', $blockedProviderIds)
                ->whereIn('providers.id', $providersWithWorkingDays)
                ->whereNull('providers.deleted_at')
                ->select(
                    'providers.id as provider_id',
                    'provider_user.name as provider_name',
                    'providers.gender',
                    '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,
                )
                ->orderByRaw('distance_km ASC NULLS LAST')
                ->get()
            : collect();

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

            $providersClose->each(function ($item) use ($clientPrimaryAddress) {
                $item->gender_label = GenderEnum::labelFor($item->gender);

                if ($item->distance_km === null) {
                    $item->distance_km = $this->zipCodeCoordinatesService->calculateDistance(
                        $clientPrimaryAddress?->latitude !== null ? (float) $clientPrimaryAddress->latitude : null,
                        $clientPrimaryAddress?->longitude !== null ? (float) $clientPrimaryAddress->longitude : null,
                        $clientPrimaryAddress?->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->specialities = ProviderSpeciality::query()
                    ->join('specialities', 'specialities.id', '=', 'provider_specialities.speciality_id')
                    ->where('provider_specialities.provider_id', $item->provider_id)
                    ->where('specialities.active', true)
                    ->orderBy('specialities.description')
                    ->get([
                        'specialities.id',
                        'specialities.description',
                    ]);

                $item->age = Provider::query()
                    ->where('id', $item->provider_id)
                    ->value(DB::raw("DATE_PART('year', AGE(birth_date))"));

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

        $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')
            ->whereDate('schedules.date', '>=', now()->toDateString())
            ->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',
                'providers.gender',
                '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
            "),

                DB::raw("
                (
                    SELECT spi.service_package_id
                    FROM service_package_items spi
                    WHERE spi.schedule_id = schedules.id
                    LIMIT 1
                ) AS service_package_id
            "),

                DB::raw("
                (
                    SELECT COUNT(*)
                    FROM service_package_items spi_count
                    JOIN schedules s_count ON s_count.id = spi_count.schedule_id
                        AND s_count.status NOT IN ('cancelled', 'rejected')
                        AND s_count.schedule_type = 'default'
                    WHERE spi_count.service_package_id = (
                        SELECT spi.service_package_id
                        FROM service_package_items spi
                        WHERE spi.schedule_id = schedules.id
                        LIMIT 1
                    )
                ) AS service_package_items_count
            "),
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $pendingSchedules->each(function ($item) {
            $item->gender_label = GenderEnum::labelFor($item->gender);
        });

        $proposalsDistanceSelect = DistanceService::sqlExpression(
            $providersCloseLatitude,
            $providersCloseLongitude,
        );

        $schedulesProposals = ScheduleProposal::query()
            ->leftJoin(
                'schedules',
                'schedule_proposals.schedule_id',
                '=',
                'schedules.id'
            )
            ->leftJoin(
                'providers',
                'schedule_proposals.provider_id',
                '=',
                'providers.id'
            )
            ->whereExists(Provider::hasActivePrimaryBankAccount())
            ->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(
                'addresses as schedule_address',
                'schedule_address.id',
                '=',
                'schedules.address_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',
                'providers.gender',
                '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',

                'schedule_address.address as address',
                'schedule_address.number as address_number',
                'schedule_address.district as address_district',
                'schedule_address.address_type as address_type',

                $proposalsDistanceSelect,
            ])
            ->get();

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

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

        $schedulesProposals->each(function ($item) use ($clientPrimaryAddress) {
            $item->gender_label = GenderEnum::labelFor($item->gender);

            $item->address = [
                'address'      => $item->address,
                'number'       => $item->address_number,
                'district'     => $item->address_district,
                'address_type' => $item->address_type,
            ];

            unset($item->address_number, $item->address_district);

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

            unset(
                $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', ['paid', 'started', 'cancelled', 'finished'])
            ->whereDate('schedules.date', now()->toDateString())
            ->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',
                'providers.gender',
                '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',

                DB::raw("
                (
                    SELECT spi.service_package_id
                    FROM service_package_items spi
                    WHERE spi.schedule_id = schedules.id
                    LIMIT 1
                ) AS service_package_id
            "),

                DB::raw("
                (
                    SELECT COUNT(*)
                    FROM service_package_items spi_count
                    JOIN schedules s_count ON s_count.id = spi_count.schedule_id
                        AND s_count.status NOT IN ('cancelled', 'rejected')
                        AND s_count.schedule_type = 'default'
                    WHERE spi_count.service_package_id = (
                        SELECT spi.service_package_id
                        FROM service_package_items spi
                        WHERE spi.schedule_id = schedules.id
                        LIMIT 1
                    )
                ) AS service_package_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->gender_label = GenderEnum::labelFor($item->gender);

                return $item;
            });

        $providerCollections = collect([
            $nextSchedules,
            $lastDoneSchedules,
            $favoriteProviders,
            $providersClose,
            $pendingSchedules,
            $schedulesProposals,
            $todaySchedules,
        ]);

        $providerPhotoUrls = $this->providerPhotoUrls(
            $providerCollections->flatMap(
                fn(Collection $items) => $items->pluck('provider_id'),
            ),
        );

        $providerCollections->each(function (Collection $items) use ($providerPhotoUrls) {
            $items->each(function ($item) use ($providerPhotoUrls) {
                $item->provider_photo = $providerPhotoUrls->get($item->provider_id);
            });
        });

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

        $pendingServicePackages = ServicePackage::query()
            ->where('client_id', $cliente->id)
            ->where('status', ServicePackageStatusEnum::OPEN->value)
            ->whereHas('items.schedule', fn($q) => $q->where('status', 'accepted'))
            ->whereDoesntHave('items.schedule', fn($q) => $q->where('status', 'pending'))
            ->with(['items.schedule' => function ($query) {
                $query->with(['provider.user', 'address']);
            }])
            ->with('provider.user')
            ->get();

        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,
            'has_location'               => $hasLocation,
            'pendingServicePackages'     => $pendingServicePackages,
        ];
    }

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

        if ($user->type !== UserTypeEnum::CLIENT) {
            throw new AuthorizationException(__('messages.only_clients_allowed'));
        }

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

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

        $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'
            )
            ->whereExists(Provider::hasActivePrimaryBankAccount())
            ->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(
                'addresses as schedule_address',
                'schedule_address.id',
                '=',
                'schedules.address_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',
                'providers.gender',
                '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',

                'schedule_address.address as address',
                'schedule_address.number as address_number',
                'schedule_address.district as address_district',
                'schedule_address.address_type as address_type',

                $proposalsDistanceSelect,
            ])
            ->get();

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

        $customSchedulesNoProposals = Schedule::with('address:district,address,number,source_id,source,id')
            ->where('client_id', $cliente->id)
            ->where('schedule_type', 'custom')
            ->where('status', 'pending')
            ->whereDate('date', '>=', now()->toDateString())
            ->doesntHave('proposals')
            ->get();

        $schedulesProposals->each(function ($item) use ($clientPrimaryAddress) {
            $item->gender_label = GenderEnum::labelFor($item->gender);

            $item->address = [
                'address'      => $item->address,
                'number'       => $item->address_number,
                'district'     => $item->address_district,
                'address_type' => $item->address_type,
            ];

            unset($item->address_number, $item->address_district);

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

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

        $packageItemCounts = DB::table('service_package_items as spi_count')
            ->join('schedules as s_count', function ($join) {
                $join->on('s_count.id', '=', 'spi_count.schedule_id')
                    ->whereNotIn('s_count.status', ['cancelled', 'rejected'])
                    ->where('s_count.schedule_type', 'default');
            })
            ->groupBy('spi_count.service_package_id')
            ->select('spi_count.service_package_id', DB::raw('COUNT(*) AS items_count'));

        $pendingSchedules = Schedule::query()
            ->where('schedules.client_id', $cliente->id)
            ->where('schedules.schedule_type', 'default')
            ->where('schedules.status', 'pending')
            ->whereDate('schedules.date', '>=', now()->toDateString())
            ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
            ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
            ->leftJoin('addresses as schedule_address', 'schedule_address.id', '=', 'schedules.address_id')
            ->leftJoin('service_package_items as spi', 'spi.schedule_id', '=', 'schedules.id')
            ->leftJoinSub(
                $packageItemCounts,
                'package_counts',
                'package_counts.service_package_id',
                '=',
                'spi.service_package_id',
            )
            ->orderBy('schedules.date', 'asc')
            ->select([
                'schedules.id',
                'schedules.provider_id',
                'schedules.date',
                'schedules.start_time',
                'schedules.end_time',
                'schedules.status',
                'provider_user.name as provider_name',
                'spi.service_package_id',

                DB::raw('COALESCE(package_counts.items_count, 1) AS service_package_items_count'),

                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
            "),

                'schedule_address.address as address_address',
                'schedule_address.number as address_number',
                'schedule_address.district as address_district',
            ])
            ->get()
            ->unique(fn ($schedule) => $schedule->service_package_id
                ? 'package-'.$schedule->service_package_id
                : 'schedule-'.$schedule->id)
            ->values();

        $pendingSchedules->each(function ($schedule) {
            $schedule->address = [
                'address'  => $schedule->address_address,
                'number'   => $schedule->address_number,
                'district' => $schedule->address_district,
            ];

            unset(
                $schedule->address_address,
                $schedule->address_number,
                $schedule->address_district,
            );
        });

        $pendingServicePackages = ServicePackage::query()
            ->where('client_id', $cliente->id)
            ->where('status', ServicePackageStatusEnum::OPEN->value)
            ->whereHas('items.schedule', fn ($query) => $query->where('status', 'accepted'))
            ->whereDoesntHave('items.schedule', fn ($query) => $query->where('status', 'pending'))
            ->with([
                'provider:id,user_id',
                'provider.user:id,name',
                'items:id,service_package_id,schedule_id',

                'items.schedule' => fn ($query) => $query
                    ->select('id', 'date', 'start_time', 'end_time', 'status', 'total_amount', 'address_id')
                    ->with('address:id,address,number,district'),
            ])
            ->get();

        $providerPhotoUrls = $this->providerPhotoUrls(
            $schedulesProposals->pluck('provider_id')
                ->merge($pendingSchedules->pluck('provider_id'))
                ->merge($pendingServicePackages->pluck('provider_id')),
        );

        collect([$schedulesProposals, $pendingSchedules])->each(
            fn (Collection $items) => $items->each(function ($item) use ($providerPhotoUrls) {
                $item->provider_photo = $providerPhotoUrls->get($item->provider_id);
            }),
        );

        $pendingServicePackages = $pendingServicePackages->map(
            function (ServicePackage $package) use ($providerPhotoUrls) {
                $schedules = $package->items
                    ->pluck('schedule')
                    ->filter()
                    ->values();

                $activeSchedules = $schedules->reject(
                    fn ($schedule) => in_array($schedule->status, ['cancelled', 'rejected'], true),
                );

                return [
                    'id'                          => $package->id,
                    'provider_id'                 => $package->provider_id,
                    'provider_name'               => $package->provider?->user?->name,
                    'provider_photo'              => $providerPhotoUrls->get($package->provider_id),
                    'total_amount'                => (float) $activeSchedules->sum(
                        fn ($schedule) => (float) $schedule->total_amount,
                    ),
                    'service_package_items_count' => $activeSchedules->count(),

                    'schedules' => $schedules->map(fn ($schedule) => [
                        'id'           => $schedule->id,
                        'status'       => $schedule->status,
                        'date'         => $schedule->date?->format('Y-m-d'),
                        'start_time'   => $schedule->start_time,
                        'end_time'     => $schedule->end_time,
                        'total_amount' => (float) $schedule->total_amount,

                        'address' => [
                            'address'  => $schedule->address?->address,
                            'number'   => $schedule->address?->number,
                            'district' => $schedule->address?->district,
                        ],
                    ])->values(),
                ];
            },
        );

        return [
            'schedulesProposals'         => $schedulesProposals,
            'customSchedulesNoProposals' => $customSchedulesNoProposals,
            'pendingSchedules'           => $pendingSchedules,
            'pendingServicePackages'     => $pendingServicePackages,
        ];
    }

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

        if ($user->type !== UserTypeEnum::PROVIDER) {
            throw new AuthorizationException(__('messages.only_providers_allowed'));
        }

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

        if ($provider->approval_status === ApprovalStatusEnum::PENDING) {
            return [
                'headerBar'           => $headerBar,
                'summaryInfos'        => $summaryInfos,
                'priceSuggested'      => null,
                'todayServices'       => [],
                'solicitations'       => [],
                'nextSchedules'       => [],
                'opportunities'       => [],
                'notifications'       => [],
                'pendingConfirmation' => [],
            ];
        }

        $providerCityId = $address?->city_id;

        $priceSuggestedAvg = Provider::query()
            ->where('providers.user_id', '!=', $user->id)
            ->whereNotNull('providers.daily_price_8h')
            ->whereExists(function ($query) use ($providerCityId) {
                $query->select(DB::raw(1))
                    ->from('addresses')
                    ->whereColumn('addresses.source_id', 'providers.id')
                    ->where('addresses.source', 'provider')
                    ->whereNull('addresses.deleted_at')
                    ->where('addresses.city_id', $providerCityId);
            })
            ->avg('providers.daily_price_8h');

        $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',
            'customSchedule.specialities',
        ])
            ->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',
                'schedules.client_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
            "),

                DB::raw("
                (
                    SELECT service_package_items.service_package_id
                    FROM service_package_items
                    WHERE service_package_items.schedule_id = schedules.id
                    LIMIT 1
                ) AS service_package_id
            "),
            )
            ->orderBy('schedules.date', 'asc')
            ->get()
            ->append('specialities')
            ->makeHidden('customSchedule');

        $solicitations->each(function ($solicitation) use ($address) {
            $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', ['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
            "),

                DB::raw("
                (
                    SELECT service_package_items.service_package_id
                    FROM service_package_items
                    WHERE service_package_items.schedule_id = schedules.id
                    LIMIT 1
                ) AS service_package_id
            "),
            )
            ->orderBy('schedules.start_time', 'asc')
            ->get();


        $pendingConfirmation = Schedule::with([
            'address' => fn ($query) => $query->withTrashed()->select([
                'district', 'address', 'number', 'source_id', 'source', 'id', 'zip_code', 'latitude', 'longitude',
            ]),
        ])
            ->where('schedules.provider_id', $provider->id)
            ->where('schedules.status', 'accepted')
            ->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',
                'custom_schedules.offers_meal',

                DB::raw("
            (
                SELECT service_package_items.service_package_id
                FROM service_package_items
                WHERE service_package_items.schedule_id = schedules.id
                LIMIT 1
            ) AS service_package_id
        "),
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $pendingConfirmation->each(function ($schedule) use ($address) {
            $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,
            );
        });

        $nextSchedules = Schedule::with([
            'address' => fn ($query) => $query->withTrashed()->select([
                'district', 'address', 'number', 'source_id', 'source', 'id', 'zip_code', 'latitude', 'longitude',
            ]),
        ])
            ->where('schedules.provider_id', $provider->id)
            ->where('schedules.status', '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',
                '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',
                'custom_schedules.offers_meal',

                DB::raw("
                    (
                        SELECT service_package_items.service_package_id
                        FROM service_package_items
                        WHERE service_package_items.schedule_id = schedules.id
                        LIMIT 1
                    ) AS service_package_id
                "),
            )
            ->orderBy('schedules.date', 'asc')
            ->get();

        $nextSchedules->each(function ($schedule) use ($address) {
            $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,
            );
        });

        $clientCollections = collect([
            $solicitations,
            $todayServices,
            $pendingConfirmation,
            $nextSchedules
        ]);

        $clientPhotoUrls = $this->clientPhotoUrls(
            $clientCollections->flatMap(
                fn(Collection $items) => $items->pluck('client_id'),
            ),
        );

        $clientCollections->each(function (Collection $items) use ($clientPhotoUrls) {
            $items->each(function ($item) use ($clientPhotoUrls) {
                $item->customer_photo = $clientPhotoUrls->get($item->client_id);
            });
        });

        $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,
            'pendingConfirmation' => $pendingConfirmation,
            '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::with('customSchedule.specialities')
            ->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')
            ->select(
                'schedules.id',
                'schedules.provider_id',
                'schedules.schedule_type',
                'provider_user.name as provider_name',
                'providers.birth_date as provider_birth_date',
                'providers.gender',
                'custom_schedules.offers_meal',
                'custom_schedules.min_price',
                'custom_schedules.max_price',
            )
            ->firstOrFail();

        $providerPhoto = $this->providerPhotoUrls([$schedule->provider_id])
            ->get($schedule->provider_id);

        return [
            'schedule_type'       => $schedule->schedule_type,
            'provider_name'       => $schedule->provider_name,
            'provider_birth_date' => $schedule->provider_birth_date,
            'gender'              => $schedule->gender,
            'gender_label'        => GenderEnum::labelFor($schedule->gender),
            'offers_meal'         => $schedule->offers_meal,
            'specialities'        => $schedule->specialities,
            'min_price'           => $schedule->min_price,
            'max_price'           => $schedule->max_price,

            'provider_photo' => $providerPhoto,
        ];
    }

    // gera urls apenas para fotos verificadas ou visiveis ao proprio prestador.

    private function providerPhotoUrls(iterable $providerIds): Collection
    {
        return Provider::query()
            ->select('id', 'profile_media_id')
            ->with('profileMedia')
            ->whereIn('id', collect($providerIds)->filter()->unique())
            ->get()
            ->mapWithKeys(function (Provider $provider) {
                $path = $provider->profileMedia?->path;

                return [
                    $provider->id => $path ? Storage::temporaryUrl($path, now()->addMinutes(60)) : null,
                ];
            });
    }

    // Gera URLs apenas para fotos verificadas ou visíveis ao próprio cliente.

    private function clientPhotoUrls(iterable $clientIds): Collection
    {
        return Client::query()
            ->select('id', 'profile_media_id')
            ->with('profileMedia')
            ->whereIn('id', collect($clientIds)->filter()->unique())
            ->get()
            ->mapWithKeys(function (Client $client) {
                $path = $client->profileMedia?->path;

                return [
                    $client->id => $path ? Storage::temporaryUrl($path, 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 distanceSelect(?float $clientLatitude, ?float $clientLongitude): \Illuminate\Contracts\Database\Query\Expression
    {
        return DistanceService::sqlExpression($clientLatitude, $clientLongitude);
    }
}