sfp_api_laravel_diarista/app/Services/ProviderCalendarService.php

<?php

namespace App\Services;

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

class ProviderCalendarService
{
    public function __construct() {}

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

        $provider = Provider::where('user_id', $user->id)->first();

        $selectFields = [
            '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.schedule_type',
            'schedules.address_id',
            'schedules.status',
            '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 reviews.stars FROM reviews
        WHERE reviews.schedule_id = schedules.id
          AND reviews.origin = 'provider'
          AND reviews.origin_id = {$provider->id}
          AND reviews.deleted_at IS NULL
        LIMIT 1
      ) as provider_stars"),
        ];

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

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

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