<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('teacher_timetables', function (Blueprint $table) {
            $table->id();
            
            $table->engine = 'InnoDB';
            // Teacher reference (from staff_members where position is teacher)
            $table->unsignedBigInteger('teacher_id');
            $table->foreign('teacher_id')
                  ->references('id')
                  ->on('staff_members')
                  ->whereExists(function ($query) {
                      $query->select('id')
                            ->from('job_positions')
                            ->whereColumn('job_positions.id', 'staff_members.position')
                            ->where('job_positions.title', 'Teacher');
                  })
                  ->onDelete('cascade');
            
            // Class reference
            $table->unsignedBigInteger('class_id');
            $table->foreign('class_id')
                  ->references('id')
                  ->on('classes')
                  ->onDelete('cascade');
            
            // Section reference
            $table->unsignedBigInteger('section_id');
            $table->foreign('section_id')
                  ->references('id')
                  ->on('sections')
                  ->onDelete('cascade');
            
            // Subject reference
            $table->unsignedBigInteger('subject_id');
            $table->foreign('subject_id')
                  ->references('id')
                  ->on('subjects')
                  ->onDelete('cascade');
            
            // Time slots
            $table->time('start_time');
            $table->time('end_time');
            
            
            $table->timestamps();
            
             // Prevent teacher double-booking
            $table->unique([
                'teacher_id',
                'start_time', 
                'end_time'
            ], 'teacher_time_slot_unique');

            // Prevent class-section double-booking
            $table->unique([
                'class_id',
                'section_id', 
                'start_time',
                'end_time'
            ], 'class_section_time_slot_unique');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('teacher_timetables');
    }
};