sfp_api_laravel_diarista/app/Services/DashboardService.php

<?php

namespace App\Services;

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

class DashboardService
{
  public function __construct() {}

  public function dadosDashboardCliente(): array
  {
    $user = Auth::user();
    $cliente = Client::where('user_id', $user->id)->first();
    $headerBar = [
      'rating'        => $cliente->average_rating,
      'total_ratings' => Review::where('reviews.origin', 'provider')
        ->leftJoin('schedules', 'schedules.id', '=', 'reviews.schedule_id')
        ->where('schedules.client_id', $cliente->id)
        ->count(),
      'total_services' => $cliente->total_services,
    ];

    $address = Address::where('source', 'client')
      ->where('source_id', $cliente->id)
      ->with(['city', 'state'])
      ->first();

    $summaryInfos = [
      'name'             => $user->name,
      'address'          => $address,
      'pending_services' => Schedule::where('client_id', $cliente->id)
        ->where('status', 'pending')
        ->count(),
    ];

    $nextSchedules = Schedule::with('address:district,address,complement,number,source_id,source,id,address_type')
      ->where('schedules.client_id', $cliente->id)
      ->whereIn('schedules.status', ['accepted', 'paid'])
      ->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')
      ->where('schedules.date', '>=', now()->toDateString())
      ->select(
        'schedules.id',
        'schedules.provider_id',
        'provider_user.name as provider_name',
        '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',
      )
      ->orderBy('schedules.date', 'asc')
      ->limit(5)
      ->get();

    $lastDoneSchedules = Schedule::where('schedules.client_id', $cliente->id)
      ->where('schedules.status', 'finished')
      ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
      ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
      ->leftJoin('addresses as provider_address', function ($join) {
        $join->on('provider_address.source_id', '=', 'providers.id')
          ->where('provider_address.source', 'provider')
          ->orderBy('provider_address.is_primary', 'desc');
      })
      ->select(
        'schedules.id',
        'schedules.provider_id',
        'provider_user.name as provider_name',
        'provider_address.district as provider_district',
      )
      ->orderBy('schedules.date', 'desc')
      ->limit(5)
      ->get();

    $favoriteProviders = ClientFavoriteProvider::where('client_favorite_providers.client_id', $cliente->id)
      ->leftJoin('providers', 'providers.id', '=', 'client_favorite_providers.provider_id')
      ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
      ->leftJoin('addresses as provider_address', function ($join) {
        $join->on('provider_address.source_id', '=', 'providers.id')
          ->where('provider_address.source', 'provider')
          ->orderBy('provider_address.is_primary', 'desc');
      })
      ->select(
        'providers.id as provider_id',
        'provider_user.name as provider_name',
        'providers.average_rating',
        'providers.daily_price_8h',
        'providers.daily_price_6h',
        'providers.daily_price_4h',
        'providers.daily_price_2h',
        'provider_address.district as provider_district',
      )
      ->orderBy('client_favorite_providers.created_at', 'desc')
      ->limit(5)
      ->get();

    $blockedProviderIds       = ScheduleBusinessRules::getBlockedProviderIdsForClient($cliente->id);
    $providersWithWorkingDays = ScheduleBusinessRules::getProviderIdsWithWorkingDays();
    $clientPrimaryAddress = Address::where('source', 'client')
      ->where('source_id', $cliente->id)
      ->where('is_primary', true)
      ->first();

    $providersClose = 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'
          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')
      ->where('provider_address.city_id', $clientPrimaryAddress?->city_id)
      ->whereNotIn('providers.id', $blockedProviderIds)
      ->whereIn('providers.id', $providersWithWorkingDays)
      ->whereNull('providers.deleted_at')

      ->select(
        'providers.id as provider_id',
        'provider_user.name as provider_name',
        'provider_address.id as address_id',
        '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"),
      )
      ->get();

    $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')
      ->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',
        'schedules.date',
        DB::raw("TO_CHAR(schedules.date, 'DD \"de\" TMMonth \"de\" YYYY') as formatted_date"),
        'schedules.start_time',
        'schedules.end_time',
        'schedules.period_type',
        'schedules.address_id',
        'schedules.status',
        'schedules.total_amount',
        DB::raw("(SELECT district FROM addresses WHERE source = 'provider' AND source_id = schedules.provider_id and deleted_at is null ORDER BY is_primary DESC LIMIT 1) as provider_district"),
        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"),
      )
      ->orderBy('schedules.date', 'asc')
      ->get();

      $schedulesProposals = ScheduleProposal::query()

    ->leftJoin('schedules', 'schedule_proposals.schedule_id', '=', 'schedules.id')
    ->leftJoin('providers', 'schedule_proposals.provider_id', '=', 'providers.id')
    ->leftJoin('users', 'providers.user_id', '=', 'users.id')

    ->where('schedules.client_id', $cliente->id)
    ->where('schedules.schedule_type', 'custom')
    ->where('schedules.status', 'pending')
    ->whereNull('schedules.deleted_at')

    ->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',
        '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'
    ])

    ->get();

    $todaySchedules = Schedule::with('address:district,address,number,source_id,source,id,address_type')
      ->where('schedules.client_id', $cliente->id)
      ->whereIn('schedules.status', ['accepted', 'paid', 'started', 'finished'])
      ->whereDate('schedules.date', now()->toDateString())
      ->leftJoin('providers', 'providers.id', '=', 'schedules.provider_id')
      ->leftJoin('users as provider_user', 'provider_user.id', '=', 'providers.user_id')
      ->leftJoin('media', 'media.id', '=', 'providers.profile_media_id')
      ->select(
        'schedules.id',
        'schedules.provider_id',
        'provider_user.name as provider_name',
        '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',
        'media.url as provider_photo',
        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();

    return [
      'headerBar'        => $headerBar,
      'summaryInfos'     => $summaryInfos,
      'pendingSchedules' => $pendingSchedules,
      'nextSchedules'    => $nextSchedules,
      'lastDoneSchedules' => $lastDoneSchedules,
      'favoriteProviders' => $favoriteProviders,
      'providersClose'   => $providersClose,
      'todaySchedules'   => $todaySchedules,
      'schedulesProposals' => $schedulesProposals,
    ];
  }

  public function getScheduleClienteDetails(int $scheduleId): array
  {
    $user = Auth::user();
    $cliente = Client::where('user_id', $user->id)->firstOrFail();

    $schedule = Schedule::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')
      ->leftJoin('media', 'media.id', '=', 'providers.profile_media_id')
      ->select(
        'schedules.provider_id',
        'provider_user.name as provider_name',
        'providers.birth_date as provider_birth_date',
        'media.url as provider_photo',
        'custom_schedules.offers_meal',
      )
      ->firstOrFail();

    $allSpecialities = Speciality::where('active', true)
      ->select('id', 'description')
      ->orderBy('description')
      ->get();

    $providerSpecialityIds = ProviderSpeciality::where('provider_id', $schedule->provider_id)
      ->pluck('speciality_id')
      ->all();

    $specialities = $allSpecialities->map(fn($sp) => [
      'id'             => $sp->id,
      'description'    => $sp->description,
      'has_speciality' => in_array($sp->id, $providerSpecialityIds),
    ])->values();

    return [
      'provider_name'       => $schedule->provider_name,
      'provider_birth_date' => $schedule->provider_birth_date,
      'provider_photo'      => $schedule->provider_photo,
      'offers_meal'         => $schedule->offers_meal,
      'specialities'        => $specialities,
    ];
  }

  public function dadosDashboardPrestador(): array
  {
    $user = Auth::user();
    $provider = Provider::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(),
    ];

    $priceSuggestedAvg = Provider::where('user_id', '!=', $user->id)
      ->whereNotNull('daily_price_8h')
      ->pluck('daily_price_8h')
      ->avg();

    $priceActual = $provider->daily_price_8h;

    $priceSuggested = [
      'average_price' => $priceSuggestedAvg,
      'your_price' => $priceActual,
    ];

    $solicitations = Schedule::with('address:district,source_id,source,id')
      ->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',
        '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"),
      )
      ->orderBy('schedules.date', 'asc')
      ->get();

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

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

    $opportunities = Schedule::with('address:district,source_id,source,id')
      ->where('schedules.schedule_type', 'custom')
      ->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',
        'client_user.name as client_name',
        'clients.average_rating',
        'schedules.date',
        'schedules.start_time',
        'schedules.end_time',
        'schedules.period_type',
        'schedules.schedule_type',
        'schedules.address_id',
        'custom_schedules.address_type',
        DB::raw("CASE
          WHEN schedules.period_type = '2' THEN {$provider->daily_price_2h}
          WHEN schedules.period_type = '4' THEN {$provider->daily_price_4h}
          WHEN schedules.period_type = '6' THEN {$provider->daily_price_6h}
          WHEN schedules.period_type = '8' THEN {$provider->daily_price_8h}
          ELSE 0
        END as total_amount"),
      )
      ->orderBy('schedules.date', 'asc')
      ->get();

    return [
      'headerBar' => $headerBar,
      'summaryInfos' => $summaryInfos,
      'priceSuggested' => $priceSuggested,
      'solicitations' => $solicitations,
      'todayServices' => $todayServices,
      'nextSchedules' => $nextSchedules,
      'opportunities' => $opportunities,
    ];
  }
}