Available rooms count

I have hotel booking system project like oyo. I want to book with dates check in and checkout if available. this is doin by checking the 'bookings' table

bookings have 'room_id', 'checkin','checkout' ,'rooms'(count of booked rooms).etc

My controller code

 if ($validated) {
                $sum=0;
                $som=DB::table('bookings')
                ->where('room_id',$room_id)
                ->where('checkin','>=',request('_checkin'))
                ->where('checkout','<=',request('_checkout'))
                ->where('status',1)
                ->get();

               if($som->count()>0){

                foreach ($som as $key => $values) {
                    $sum+=$values->rooms;
                }
                
                $room_av=DB::table('room_availability')->where('room_id',$room_id)->first();
                $room_available=$room_av->room_count-$sum;

               }
               else{
                $room_av=DB::table('room_availability')->where('room_id',$room_id)->first();
                $room_available=$room_av->room_count;
               }
               if($room_available==0){
                  
                   return back()->withErrors(['room_error' => 'No rooms available in this date']);
               }
               elseif($room_available < request('_room')){
                   
                    $rooms=Str::plural('room',$room_available);
                    $tense=$room_available > 1 ? 'are' : 'is';
                   
                   
                   return back()->withErrors(['room_error' => 'Only '.$room_available.' '.$rooms.' '.$tense.' available.']);
               }
               else{
  booking proces code....           
  }

Here its checking the dates between checkin and checkout if rows found, sum the booked rooms by looping and checking its total available rooms ,

But it works when dates like 21/05/2021 and 23/05/2021(in the bookings table two bookings on the date with rooms of 4 and 6 (capacity of that room type is 10)) but when i choose chekn and chkout dates 22/05/2021 - 24/05/2021 its going to booking process, it should not be allowed for that process. because its already reached the capacity by 10 betwwen the dates,

Please help me thanks in advance