Untitled

<?php
public function checkBookedRoom(Request $request)
    {
        $this->user = $request->user();

        $request->validate([
            'booking_id' => 'nullable|uuid',
            'room_id' => 'required|uuid',
            'booking_sub_rooms' => 'nullable|array',
            'booking_sub_rooms.*' => 'required|uuid',
            'event_start_date' => 'nullable|date_format:Y-m-d|before_or_equal:event_end_date',
            'event_end_date' => 'nullable|date_format:Y-m-d|after_or_equal:event_start_date',
        ]);

        $eventStartDate = $request->input('event_start_date');
        $eventEndDate = $request->input('event_end_date');
        $eventDateRanges = [$eventStartDate, $eventEndDate];

        $room = Room::query()
            ->where('id_hash', $request->input('room_id'))
            ->first();

        if (is_null($room)) {
            throw ValidationException::withMessages([
                'room_id' => ['Invalid selected room.']
            ]);
        }

        $bookedRooms = Booking::query();

        $bookedRooms->select([
            'bookings.id',
            'bookings.id_hash',
            'booking_events.event_start_date',
            'booking_events.event_end_date',
        ]);

        $bookedRooms->join('booking_events', function ($join) {
            $join->on('booking_events.booking_id', 'bookings.id');
            $join->whereNull('booking_events.deleted_at');
        });

        // Join booking_event_agendas with polymorphic relationships to Room and SubRoom
        $bookedRooms->leftJoin('booking_event_agendas', function ($join) {
            $join->on('booking_event_agendas.booking_id', 'bookings.id')
                ->whereIn('booking_event_agendas.additionalable_type', [
                    \App\Models\Room::class,
                    \App\Models\SubRoom::class,
                ])
                ->whereNull('booking_event_agendas.deleted_at');
        });

        if ($request->filled('booking_sub_rooms') && count($request->input('booking_sub_rooms'))) {
            $bookedRooms->addSelect(['m_sub_rooms.name']);
            $bookedRooms->join('booking_sub_rooms', function ($join) {
                $join->on('booking_sub_rooms.booking_id', 'bookings.id');
                $join->whereNull('booking_sub_rooms.deleted_at');
            });
            $bookedRooms->join('m_sub_rooms', function ($join) {
                $join->on('m_sub_rooms.id', 'booking_sub_rooms.sub_room_id');
                $join->whereNull('m_sub_rooms.deleted_at');
            });

            $bookedRooms->orWhere(function ($query) use ($request) {
                $query->whereIn('booking_event_agendas.additionalable_id', function ($subQuery) use ($request) {
                    $subQuery->select('id')
                        ->from('m_sub_rooms')
                        ->whereIn('id_hash', $request->input('booking_sub_rooms'));
                })
                ->where('booking_event_agendas.additionalable_type', \App\Models\SubRoom::class);
            });

            $bookedRooms->orWhere('m_sub_rooms.room_id', $room->id);
            $bookedRooms->orWhere('m_sub_rooms.id_hash', $request->input('booking_sub_rooms'));
        } else {
            $bookedRooms->addSelect(['m_rooms.name']);
            $bookedRooms->join('m_rooms', function ($join) {
                $join->on('m_rooms.id', 'bookings.room_id');
                $join->whereNull('m_rooms.deleted_at');
            });
        }

        
        if ($request->filled('booking_id')) {
            $bookedRooms->whereNot('bookings.id_hash', $request->input('booking_id'));
        }
        

         // Check the room_id in booking_event_agendas where additionalable_type is Room
        $bookedRooms->where(function ($query) use ($room) {
            $query->where(function ($q) use ($room) {
                $q->where('booking_event_agendas.additionalable_id', $room->id)
                ->where('booking_event_agendas.additionalable_type', \App\Models\Room::class);
            });
        });

        $bookedRooms->where('bookings.room_id', $room->id);

        $bookedRooms->where('bookings.status', Booking::STATUS_PAID);

        if ($request->filled(['event_start_date', 'event_end_date'])) {
            $bookedRooms->where(function ($query) use ($eventDateRanges) {
                $query->whereBetween('booking_events.event_start_date', $eventDateRanges);
                $query->orWhereBetween('booking_events.event_end_date', $eventDateRanges);
            });
        } else {
            $bookedRooms->whereRaw("1 = 0");
        }

        $bookedRooms = $bookedRooms->get();

        if ($bookedRooms->isNotEmpty()) {
            $messages = [];

            foreach ($bookedRooms as $row) {
                $eStartDate = Carbon::parse($row->event_start_date)->format('Y/m/d');
                $eEndDate = Carbon::parse($row->event_end_date)->format('Y/m/d');

                $messages[] = "{$row->name} has been booked from {$eStartDate} to {$eEndDate}";
            }

            throw ValidationException::withMessages(['check_booked_room' => $messages]);
        }

        return ResponseFormatter::success($bookedRooms);
    }