sfp_api_laravel_diarista/app/Services/SearchService.php

<?php

namespace App\Services;

use App\Models\Address;
use App\Models\Client;
use App\Models\Media;
use App\Models\Provider;
use App\Rules\ScheduleBusinessRules;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;

class SearchService
{
    public function __construct() {}

    public function buscaPrestadores(?string $name = null, ?string $date = null): array
    {
        $user = Auth::user();

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

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

        $providersWithWorkingDays = ScheduleBusinessRules::getProviderIdsWithWorkingDays();

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

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

        $baseQuery = Provider::leftJoin(
            'users as provider_user',
            'provider_user.id',
            '=',
            'providers.user_id'
        )
            ->visibleToCustomers()
            ->leftJoin(
                DB::raw("
                    (
                        SELECT DISTINCT ON (source_id)
                            *
                        FROM addresses
                        WHERE source = 'provider'
                        AND deleted_at IS NULL
                        ORDER BY source_id, is_primary DESC
                    ) AS provider_address
                "),
                'provider_address.source_id',
                '=',
                'providers.id'
            )
            ->whereNotNull('provider_address.id')
            ->whereNotIn('providers.id', $blockedProviderIds)
            ->whereIn('providers.id', $providersWithWorkingDays)
            ->whereNull('providers.deleted_at')
            ->whereNotNull('providers.daily_price_8h')
            ->whereNotNull('providers.daily_price_6h')
            ->whereNotNull('providers.daily_price_4h')
            ->whereNotNull('providers.daily_price_2h')
            ->when($name, fn ($q) => $q->where('provider_user.name', 'ILIKE', "%{$name}%"))
            ->select(
                'providers.id as provider_id',
                'providers.profile_media_id',
                'provider_user.name as provider_name',
                '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',
                'providers.created_at',

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

                $distanceSelect,
            )
            ->orderByRaw('distance_km ASC NULLS LAST');

        $providers = (clone $baseQuery)
            ->when(
                $clientPrimaryAddress?->city_id,
                fn ($query, int $cityId) => $query->where('provider_address.city_id', $cityId)
            )
            ->get();

        if ($providers->isEmpty() && $clientPrimaryAddress?->city_id) {
            $providers = $baseQuery->get();
        }

        $filtered = $providers->when(
            $date,
            fn ($collection) => $collection->whereIn(
                'provider_id',
                ScheduleBusinessRules::getAvailableProviderIdsForDate(
                    $date,
                    $collection->pluck('provider_id')
                )->toArray()
            )->values()
        );

        $mediaIds = $filtered->pluck('profile_media_id')->filter()->unique()->values();

        $mediaUrls = [];

        if ($mediaIds->isNotEmpty()) {
            Media::whereIn('id', $mediaIds)->get()->each(function ($media) use (&$mediaUrls) {
                $mediaUrls[$media->id] = $media->path
                    ? Storage::temporaryUrl($media->path, now()->addMinutes(60))
                    : null;
            });
        }

        return $filtered->map(function ($item) use ($mediaUrls) {
            $arr = is_array($item) ? $item : $item->toArray();

            $arr['profile_media_url'] = $mediaUrls[$arr['profile_media_id'] ?? null] ?? null;

            unset($arr['profile_media_id']);

            return $arr;
        })->values()->toArray();
    }

    //

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