Issue with Checking Lesson Scheduling Conflicts in Laravel

Hi everyone,

I'm working on a Laravel application to schedule lessons for a timetable, and I'm facing an issue with checking for conflicts when scheduling new lessons. Specifically, I want to prevent the creation of new lessons that overlap in time with existing lessons for the same teacher, class, or stream. Below are the detailed scenarios I want to handle:

1. A new lesson cannot be scheduled for the same class and stream at the same time.
2. A new lesson cannot be scheduled with the same teacher at the same time, even for different classes and streams.
3. The new lesson should not conflict with existing lessons for the same day and overlapping times.

Here is my current function:

// Lesson model
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Lesson extends Model
{
    use HasFactory, SoftDeletes;

    const WEEK_DAYS = [
        '1' => 'Monday',
        '2' => 'Tuesday',
        '3' => 'Wednesday',
        '4' => 'Thursday',
        '5' => 'Friday',
        '6' => 'Saturday',
        '7' => 'Sunday',
    ];

    public function class()
    {
        return $this->belongsTo(Classes::class, 'classes_id');
    }

    public function stream()
    {
        return $this->belongsTo(Stream::class);
    }

    public function course()
    {
        return $this->belongsTo(Course::class);
    }

    public function teacher()
    {
        return $this->belongsTo(Teacher::class);
    }

    public function students()
    {
        return $this->hasMany(Student::class);
    }

    public static function isLessonAvailable($weekday, $startTime, $endTime, $classId, $streamId, $courseId, $teacherId, $lessonId = null)
    {
        $conflicts = self::where('weekday', $weekday)
            ->when($lessonId, function ($query) use ($lessonId) {
                return $query->where('id', '!=', $lessonId);
            })
            ->where([
                ['start_at', '<', $endTime],
                ['end_at', '>', $startTime],
            ])
            ->where(function ($query) use ($classId, $streamId, $courseId, $teacherId) {
                $query->where('classes_id', $classId)
                    ->where('stream_id', $streamId)
                    ->orWhere('course_id', $courseId)
                    ->orWhere('teacher_id', $teacherId);
            })
            ->where(function ($query) use ($teacherId, $courseId) {
                $query->where('teacher_id', $teacherId)
                    ->orWhere('course_id', $courseId);
            })
            ->where(function ($query) use ($courseId, $teacherId) {
                $query->where('course_id', $courseId)
                    ->orWhere('teacher_id', $teacherId);
            })
            ->exists();

        return $conflicts;
    }

}


// Lessons schema
Schema::create('lessons', function (Blueprint $table) {
    $table->id();
    $table->foreignId('classes_id')->constrained()->onDelete('cascade');
    $table->foreignId('stream_id')->constrained()->onDelete('cascade');
    $table->foreignId('course_id')->constrained()->onDelete('cascade');
    $table->foreignId('teacher_id')->constrained()->onDelete('cascade');
    $table->integer('weekday');
    $table->time('start_at');
    $table->time('end_at');
    $table->timestamps();
    $table->softDeletes();
});

Form Values:

1. First Case:

   • weekday: 2 (Tuesday)
   • startTime: 09:20:00
   • endTime: 10:10:00
   • classId: 2
   • streamId: 2
   • courseId: 5
   • teacherId: 1
   • lessonId: null

2. Second Case:

   • weekday: 1 (Monday)
   • startTime: 09:20:00
   • endTime: 10:10:00
   • classId: 2
   • streamId: 2
   • courseId: 5
   • teacherId: 1
   • lessonId: null

Existing Lessons (in the DB):

1. Lesson 1:

   • weekday: 1 (Monday)
   • start_at: 08:30
   • end_at: 09:20
   • class_id: 1
   • stream_id: 4
   • course_id: 4
   • teacher_id: 1

2. Lesson 2:

   • weekday: 1 (Monday)
   • start_at: 09:20
   • end_at: 10:10
   • class_id: 1
   • stream_id: 4
   • course_id: 2
   • teacher_id: 2

Despite trying to debug in every possible way I know, I'm still encountering issues where the function returns false even when there should be a conflict. Any insights or improvements on how to correctly implement this conflict checking logic would be greatly appreciated.