scheduling medical appointment challenge

Hi,

I'm working on a medical appointment scheduling system project.

What is the best way to get the first free time of a doctor and schedule it for the patient who just asked for an appointment?

database structure:

appointments table:

• doctor_id
• patient_id
• appointment_start_time
• appointment_end_time

doctors_working_hours table:

• doctor_id
• day_of_week
• start_time
• end_time

Using eloquent, When a patient asks for an appointment with a doctor, I want to schedule the first next free time of the doctor according to the doctor's working hours.

Till now, I came to this:

class Appointment extends Model
{


    /**
     * Schedules an appointment.
     * 
     * @param  array $data   including doctor_id and patient_id
     * @return \App\Appointment
     */
    public static function schedule($data)
    {
        $doctor = User::find($data['doctor_id']);

        $lastAppointment = $doctor->appointments()
                                    // ->select('end_time')
                                    ->latest('end_time')
                                    ->first();
                                    
        $dayOfWeek = $lastAppointment['end_time']->format('w');

        $time = $lastAppointment['end_time']->format('H:i');

        $workSchedules = $doctor->workSchedules()
                                    ->orderBy('day_of_week')
                                    ->orderBy('start_time')
                                    ->get();

    }
}

this is incomplete.

the default duration for each appointment is 15 minutes.

How can I do this?