sfp_api_laravel_diarista/app/Services/SearchService.php

<?php

namespace App\Services;

use App\Models\Address;
use App\Models\Client;
use App\Models\ClientFavoriteProvider;
use App\Models\Provider;
use App\Models\Review;
use App\Models\Schedule;
use App\Rules\ScheduleBusinessRules;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

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

    return Provider::leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
      ->leftJoin(DB::raw("
        (
          SELECT DISTINCT ON (source_id)
            *
          FROM addresses
          WHERE source = 'provider'
          ORDER BY source_id, is_primary DESC
        ) as provider_address
      "), 'provider_address.source_id', '=', 'providers.id')
      ->whereNotNull('provider_address.id')
      ->where('provider_address.city_id', $clientPrimaryAddress?->city_id)
      ->whereNotIn('providers.id', $blockedProviderIds)
      ->whereIn('providers.id', $providersWithWorkingDays)
      ->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',
        'provider_user.name as provider_name',
        'provider_address.district',
        '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"),
      )
      ->orderBy('providers.average_rating', 'desc')
      ->get()
      ->when(
        $date,
        fn($collection) => $collection->whereIn(
          'provider_id',
          ScheduleBusinessRules::getAvailableProviderIdsForDate(
            $date,
            $collection->pluck('provider_id')
          )->toArray()
        )->values()
      )
      ->toArray();
  }

}