sfp_api_laravel_diarista/app/Services/ClientCalendarService.php

<?php

namespace App\Services;

use App\Models\Client;
use App\Models\Schedule;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class ClientCalendarService
{
  public function __construct() {}

  public function getCalendar(): array
  {
    $user = Auth::user();
    $client = Client::where('user_id', $user->id)->first();

    $selectFields = [
      'schedules.id',
      'schedules.provider_id',
      'schedules.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',
      'provider_user.name as provider_name',
      'providers.average_rating',
      'providers.total_services',
      DB::raw("(SELECT media.url FROM media WHERE media.source_id = providers.id AND media.source = 'provider' AND media.deleted_at IS NULL LIMIT 1) as provider_photo"),
      DB::raw("EXISTS(
        SELECT 1 FROM reviews
        WHERE reviews.schedule_id = schedules.id
          AND reviews.origin = 'client'
          AND reviews.origin_id = {$client->id}
          AND reviews.deleted_at IS NULL
      ) as client_reviewed"),
      DB::raw("(
        SELECT reviews.stars FROM reviews
        WHERE reviews.schedule_id = schedules.id
          AND reviews.origin = 'client'
          AND reviews.origin_id = {$client->id}
          AND reviews.deleted_at IS NULL
        LIMIT 1
      ) as client_stars"),
    ];

    $upcomingSchedules = Schedule::with('address:district,address,number,source_id,source,id')
      ->where('schedules.client_id', $client->id)
      ->whereIn('schedules.status', ['pending', 'accepted', 'paid', 'started'])
      ->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($selectFields)
      ->orderBy('schedules.date', 'asc')
      ->orderBy('schedules.start_time', 'asc')
      ->get();

    $completedSchedules = Schedule::with('address:district,address,number,source_id,source,id')
      ->where('schedules.client_id', $client->id)
      ->whereIn('schedules.status', ['finished', 'cancelled'])
      ->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($selectFields)
      ->orderBy('schedules.date', 'desc')
      ->orderBy('schedules.start_time', 'desc')
      ->get();

    return [
      'upcomingSchedules' => $upcomingSchedules,
      'completedSchedules' => $completedSchedules,
    ];
  }
}