<?php

namespace App\Repositories\Eloquent;

use App\Models\Professional;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use App\Repositories\Interfaces\ProfessionalRepositoryInterface;

class ProfessionalRepository extends BaseRepository implements ProfessionalRepositoryInterface
{
    /**
    * ProfessionalRepository constructor.
    *
    * @param Professional $model
    */
    public function __construct(Professional $model)
    {
       parent::__construct($model);
    }

    /**
     * @return Collection
     */
    public function lockForBooking(int $id): void
    {
        $this->model->newQuery()->whereKey($id)->lockForUpdate()->firstOrFail();
    }

    public function getApplicationList(): Collection
    {
        return $this->model
            ->orderBy('id', 'desc')
            ->with(['specialties', 'office', 'schedules', 'documents'])
            ->get();
    }

    /**
     * @param array $filters
     * 
     * @return array
     */
    public function searchProfessional(array $filters): array
    {
        $query = $this->model->newQuery();

        $query->select(
            'professionals.id as professional_id',
            'professionals.professional_uuid as professional_uuid',
            'users.id as user_id',
            'professions.salutation',
            'profiles.gender',
            'profiles.full_name as name',
            'professions.name as profession', 
            'professions.color_hex', 
            DB::raw('COALESCE(NULLIF(professionals.default_rate, 0), (SELECT professional_schedules.rate FROM professional_schedules WHERE professional_schedules.professional_id = professionals.id ORDER BY professional_schedules.id ASC LIMIT 1), 0) as default_rate'),
            'profiles.image',
            DB::raw('COUNT(ratings.id) as rev_count, AVG(rate) as rate_ave')
        );
        
        $query->join('users', 'professionals.user_id', '=', 'users.id');
        $query->join('profiles', 'users.id', '=', 'profiles.user_id');
        $query->join('professions', 'professionals.profession_id', '=', 'professions.id');
        $query->leftJoin('ratings', 'professionals.id', '=', 'ratings.professional_id');

        $allowedColumnsToFilter = [
            'professional',
            'profession_id',
            'professional_uuid',
            'professional_id'
        ];

        $this->processFilters($query, $filters, $allowedColumnsToFilter);
        
        $records = $query->where('professionals.status', 'approved')
                    ->where('users.status', 'active')
                    ->groupBy(
                        'professionals.id',
                        'professionals.professional_uuid',
                        'users.id',
                        'professions.salutation',
                        'profiles.gender',
                        'profiles.full_name',
                        'professions.name',
                        'professions.color_hex',
                        'professionals.default_rate',
                        'profiles.image'
                    )
                    ->get();
        $specialties = \App\Models\ProfessionalSpecialty::query()
            ->with('specialty')
            ->whereIn('professional_id', $records->pluck('professional_id'))
            ->get()
            ->groupBy('professional_id');
        foreach ($records as $record) {
            $record->specialty_names = ($specialties->get($record->professional_id) ?? collect())
                ->map(fn ($item) => trim($item->custom_name ?: ($item->specialty?->name ?? '')))
                ->filter()->unique()->values()->all();
        }

        return [
            'records' => $records,
            'totalRows' => count($records)
        ];
    }

    public function viewProfessionalDetails(string $professionalId)
    {
        return $this->model
            ->where('professionals.professional_uuid', $professionalId)
            ->with(['specialties.specialty', 'ratings', 'schedules', 'educations'])
            ->first();
    }

    public function getAvailableForInstantBooking(int $professionId, ?int $specialtyId = null, ?int $excludeUserId = null): Collection
    {
        $baseQuery = fn () => $this->model->newQuery()->with('user')
            ->where('profession_id', $professionId)
            ->when($excludeUserId, fn ($query) => $query->where('user_id', '!=', $excludeUserId))
            ->where('status', 'approved')
            ->whereHas('user', fn ($query) => $query->where('status', 'active'));

        if ($specialtyId) {
            $specialtyMatches = $baseQuery()
                ->whereHas('specialties', fn ($query) => $query->where('specialty_id', $specialtyId))
                ->get();
            if ($specialtyMatches->isNotEmpty()) {
                return $specialtyMatches;
            }
        }

        return $baseQuery()->get();
    }
}