Django store actual availability and query the available schedule for new booking

In the app, there are two types of user: Student and Mentor

1. As a mentor user type, i can set my weekly based availability
2. as a student user type, I can see a mentor availability.

==============================

So I am storing user availability based on week day in WeekDay and AvailableHour models

DAYS_OF_WEEK = (
    (0, 'Monday'),
    (1, 'Tuesday'),
    (2, 'Wednesday'),
    (3, 'Thursday'),
    (4, 'Friday'),
    (5, 'Saturday'),
    (6, 'Sunday'),
)
        
class WeekDay(models.Model):
    mentor = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="weekday"
    )
    weekday = models.IntegerField(choices=DAYS_OF_WEEK)
    

class AvailableHour(models.Model): #Based on weekday
    weekday = models.ForeignKey(
        WeekDay,
        on_delete=models.CASCADE,
        related_name="available_hour"
    )
    from_hour = models.TimeField()
    to_hour = models.TimeField()

3. As a user type student i can book a session only the in mentor availability hour.

So when a student books a session, we are storing the information there below models.

 class BookingSession(models.Model):
   student = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="student_booking"
    )
    mentor = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="mentor_booking"
    )
    date = models.DateField()
    
    from_hour = models.TimeField()
    to_hour = models.TimeField()

So what's the problem now?

As we have user AvailableHour so as soon as a student book a tutor session, then the specified time range is unavabale of tutor profile, right? so that another student can see a tutor actual availability.

So you know, if we get queryset from AvailableHour it doesn't mean tutor is available in that time if any specified time range is already booked and the booked time already stored in BookingSession models

I want to exclude AvailableHour's records if the start and end time are existing in the BookingSession in the specified weekday

I want to show the student, what is tutor's actual availability is.

Like AvailableHour can't be stored actual available if there is already a booking in BookingSession You know it !!

Let's say I have data in BookingSession Table

booked_sesson = [
    {'date': '2021-08-02','from_hour': '12:30', 'to_hour': '12:15' }, # monday
    {'date': '2021-08-04','from_hour': '02:30', 'to_hour': '03:15' }, # Wednesday
]

and in WeekDay and AvailableHour availability table data below

available_based_on_weekday = [
    {
        "weekday": 1, 'opening_hour_time': [ # monday
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'}
        ]
    },
    {
        "weekday": 7, 'opening_hour_time': [ # Saturday
            {'from_hour': '13:30', 'to_hour': '15:15'},
            {'from_hour': '15:30', 'to_hour': '16:15'},
            {'from_hour': '19:30', 'to_hour': '20:15'},
            {'from_hour': '23:30', 'to_hour': '23:45'}
        ]
    },
    {
        "weekday": 3, 'opening_hour_time': [ # Wednesday
            {'from_hour': '02:30', 'to_hour': '03:30'},
            {'from_hour': '17:30', 'to_hour': '18:15'},
            {'from_hour': '19:30', 'to_hour': '20:15'},
            {'from_hour': '21:30', 'to_hour': '22:30'}
        ]
    }
]

What output result I am expecting? After the query, I am expecting these outputs and should be queryset month by month:

actual_availability = [
     {
         'date': '2021-08-01',
         'available': []
     },
     {
         'date': '2021-08-02',
         'available': [
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'}
         ]
     },
     {
         'date': '2021-08-03',
         'available': []
     },
     {
         'date': '2021-08-04',
         'available': [
            {'from_hour': '03:15', 'to_hour': '03:30'},
            {'from_hour': '17:30', 'to_hour': '18:15'},
            {'from_hour': '19:30', 'to_hour': '20:15'},
            {'from_hour': '21:30', 'to_hour': '22:30'}
         ]
     },
     {
         'date': '2021-08-05',
         'available': []
     },
     {
         'date': '2021-08-06',
         'available': []
     },
     {
         'date': '2021-08-07',
         'available': []
     },
     {
         'date': '2021-08-08',
         'available': []
     },
     {
         'date': '2021-08-09',
         'available': [
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'},
            {'from_hour': '12:30', 'to_hour': '12:15'}
         ]
     },
     # to be continued for a month by month
]

If you anyone have any alternative approach, i will welcome, please share your point of views here.

Here you go what I have tried and it's really stupid, doesn't work.

real_available_date_slot = []
for obj in BookingSession.objects.filter(mentor_id='A mentor ID'):
    weekday =  obj.date.isoweekday()
    real_available = []

    available_hour = AvailableHour.objects.filter(weekday__weekday=weekday, weekday__mentor=obj.mentor)
    availables = available_hour.exclude(
        from_hour__gt=obj.to_hour
    ).exclude(
        to_hour__lt=obj.to_hour
    )
    
    for available in availables:
        real_available.append(
            {'from_hour': available.from_hour, 'to_hour': available.to_hour}
        )

    real_available_date_slot.append({
        obj.date: real_available
    })

Also i have tried different other way but i couldn't come with any solution

Can anyone share your thought how we can solve my availability problem? What is your approach of querying this kind of thing? or i made the database design improperly for this case?

from Django store actual availability and query the available schedule for new booking