FM / Inc

/home/techb158/workloadmatch.com/Manager/Inc/

/home/techb158/workloadmatch.com/Manager/Inc

Name	Size	Mode	Actions
LLMProviders/	-	0755	rm
AddCourse.php	2556	0644	edit dl rm
AddCourseTime.php	1904	0644	edit dl rm
AddGroup - Copy.php	4633	0644	edit dl rm
AddGroup.php	5698	0644	edit dl rm
AddHoliday.php	1772	0644	edit dl rm
AddTeacherLevel.php	2307	0644	edit dl rm
AddUser.php	12821	0644	edit dl rm
Add_Retake_Remediation.php	1334	0644	edit dl rm
AIScheduleOptimizer.php	11919	0644	edit dl rm
Assignment-1.php	75380	0644	edit dl rm
Assignment-2.php	75380	0644	edit dl rm
Assignment - BackUp- orgenal.php	73272	0644	edit dl rm
Assignment - BackUp.php	62761	0644	edit dl rm
Assignment - Copy (Last File Updated 2025-05-15).php	72933	0644	edit dl rm
Assignment - Copy.php	61826	0644	edit dl rm
Assignment.php	74308	0644	edit dl rm
Assignment_BackUp.php	79047	0644	edit dl rm
Assign_Teacher_Manual.php	20957	0644	edit dl rm
Assign_Teacher_Manual_bak.php.disabled	20535	0644	edit dl rm
Assign_Teacher_Manual_Force_conflict.php	21106	0644	edit dl rm
Available_Teacher_Report.php	2342	0644	edit dl rm
BatchScheduler.php	3588	0644	edit dl rm
CombinedScheduler.php	8130	0644	edit dl rm
ConflictResolver.php	4920	0644	edit dl rm
error_log	869	0644	edit dl rm
Lockup_to_Assign.php	6367	0644	edit dl rm
Process_Assignment - Copy.php	4233	0644	edit dl rm
Process_Assignment.php	5813	0644	edit dl rm
ScheduleEngine.php	5596	0644	edit dl rm
ScheduleGenerator.php	8811	0644	edit dl rm
Schedule_Report_by_Group.php	3074	0644	edit dl rm
TeacherAssignmentEngine.php	12487	0644	edit dl rm
Update_Course.php	2717	0644	edit dl rm
Update_Course_Time.php	1947	0644	edit dl rm
Update_Group - Copy.php	3489	0644	edit dl rm
Update_Group.php	9657	0644	edit dl rm
Update_Holiday.php	2357	0644	edit dl rm
Update_Profile.php	4923	0644	edit dl rm
Update_User.php	5547	0644	edit dl rm

Edit: /home/techb158/workloadmatch.com/Manager/Inc/Assignment.php (74308B)

<?php

include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();

function calculateLoadHours(&$teachers) {
    global $mysqli;
    foreach ($teachers as &$t) {
        $stmt = $mysqli->prepare("
            SELECT IFNULL(SUM(c.Course_Time), 0) AS hrs
            FROM teacher_course_assignments tca
            JOIN Courses c ON tca.Course_ID = c.Course_ID
            WHERE tca.Teacher_ID = ?
        ");
        $stmt->bind_param('i', $t['Teacher_ID']);
        $stmt->execute();
        $t['Current_Load'] = (int) $stmt->get_result()->fetch_assoc()['hrs'];
        $stmt->close();
    }
    unset($t);
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {

$Program_ID    = (int) $_POST['Program_ID'];
$Group_ID      = (int) $_POST['Group_ID'];
$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;

// ----------------------------------------
// STEP 1: Load all unassigned schedules
// ----------------------------------------
if ($Group_ID !== 0) {
    $sql = "
        SELECT * 
        FROM course_group_schedule_main
        WHERE Assigned = 0
          AND Program_ID = ?
          AND Group_ID   = ?
          AND Reserve_Course = ?
        ORDER BY Time_Slot
    ";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('iii', $Program_ID, $Group_ID, $Reserve_Course);
} else {
    $sql = "
        SELECT *
        FROM course_group_schedule_main
        WHERE Assigned = 0
          AND Program_ID = ?
          AND Reserve_Course = ?
        ORDER BY Time_Slot
    ";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('ii', $Program_ID, $Reserve_Course);
}
if (!$stmt->execute()) {
    die("Error fetching schedules: " . $mysqli->error);
}
$schedules = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// ----------------------------------------
// STEP 2: Load all teachers and unavailability
// ----------------------------------------
$sql = "
    SELECT 
        tp.Teacher_ID,
        tp.First_Name,
        tp.Last_Name,
        tp.Seniority_ID,
        tp.Time_Slot    AS Availability,
        tp.Load_Hours,
        tp.User_Access,
        ttl.Top_Teacher_ID,
        ttl.Teacher_Level
    FROM teacher_profile tp
    LEFT JOIN top_teacher_list ttl
      ON tp.Teacher_ID = ttl.Teacher_ID
     AND ttl.Program_ID = ?
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $Program_ID);
$stmt->execute();
$allTeachers = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// attach unavailability slots to each teacher
foreach ($allTeachers as &$t) {
    $t['Unavailability'] = [];
    $stmt = $mysqli->prepare("
        SELECT Unavailable_From, Unavailable_To
        FROM teacher_unavailability
        WHERE Teacher_ID = ? AND status = 'confirmed'
    ");
    $stmt->bind_param('i', $t['Teacher_ID']);
    $stmt->execute();
    $t['Unavailability'] = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
}
unset($t);

// ----------------------------------------
// STEP 3: Split into premium / qualified / unqualified
// ----------------------------------------
$premium    = $qualified = $unqualified = [];
foreach ($allTeachers as $t) {
    if (!empty($t['Top_Teacher_ID'])) {
        $premium[] = $t;
    } elseif ($t['Seniority_ID'] == 1) {
        $qualified[] = $t;
    } else {
        $unqualified[] = $t;
    }
}

// ----------------------------------------
// RANDOMIZE each group so assignment is fair
// ----------------------------------------
//shuffle($premium);
//shuffle($qualified);
//shuffle($unqualified);

///current load to balance better
calculateLoadHours($premium);
calculateLoadHours($qualified);
calculateLoadHours($unqualified);

// Sort by least load (then by Teacher_ID to stabilize tie-breaks)
usort($premium, fn($a, $b) => [$a['Current_Load'], $a['Teacher_ID']] <=> [$b['Current_Load'], $b['Teacher_ID']]);
usort($qualified, fn($a, $b) => [$a['Current_Load'], $a['Teacher_ID']] <=> [$b['Current_Load'], $b['Teacher_ID']]);
usort($unqualified, fn($a, $b) => [$a['Current_Load'], $a['Teacher_ID']] <=> [$b['Current_Load'], $b['Teacher_ID']]);

// ----------------------------------------
// HELPER: fetch preferred courses for a teacher
// ----------------------------------------

function getPreferredCourses($teacherID) {
    global $mysqli;
    $sql = "
        SELECT Course_ID
        FROM teacher_course_preferences
        WHERE Teacher_ID = ?
          AND Priority IN (1,2)
    ";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $teacherID);
    $stmt->execute();
    $ids = array_column($stmt->get_result()->fetch_all(MYSQLI_ASSOC), 'Course_ID');
    $stmt->close();
    return $ids;
}

// ----------------------------------------
// FIND: courses only premium teachers prefer
// ----------------------------------------
$preferredByNonPremium = [];
foreach (array_merge($qualified, $unqualified) as $t) {
    $preferredByNonPremium = array_merge(
        $preferredByNonPremium,
        getPreferredCourses($t['Teacher_ID'])
    );
}
$preferredByNonPremium = array_unique($preferredByNonPremium);

$allCourseIDs = array_column($schedules, 'Course_ID');
$premiumOnlyCourses = array_diff($allCourseIDs, $preferredByNonPremium);

// ----------------------------------------
// CAN_ASSIGN: checks availability / load / prefs / conflicts
// ----------------------------------------
$canAssign = function(array $teacher, array $sched) use ($mysqli): bool {
    // 1) User access
    if ($teacher['User_Access'] == 0) return false;

// 2) Unavailability overlap
 $schStart = strtotime($sched['Start_Date']);
 $schEnd = strtotime($sched['End_Date']);
 foreach ($teacher['Unavailability'] as $u) {
 if ($schStart <= strtotime($u['Unavailable_To']) && $schEnd >= strtotime($u['Unavailable_From'])) {
 return false;
 }
 }

// 3) Timeslot match
    $slots = array_map('trim', explode(',', $teacher['Availability']));
    if (!in_array($sched['Time_Slot'], $slots, true)) return false;

// 4) Conflict: overlapping assignments
 $sql = "
 SELECT COUNT(*) AS cnt
 FROM teacher_course_assignments
 WHERE Teacher_ID = ?
 AND Time_Slot = ?
 AND (? <= End_Date AND ? >= Start_Date)
 ";
 $stm = $mysqli->prepare($sql);
 $stm->bind_param('isss', $teacher['Teacher_ID'], $sched['Time_Slot'], $sched['Start_Date'], $sched['End_Date']);
 $stm->execute();
 if ($stm->get_result()->fetch_assoc()['cnt'] > 0) {
 $stm->close();
 return false;
 }
 $stm->close();

// 5) NEW RULE: no multiple classes with same Group_ID on same day
	$sql = "
		SELECT COUNT(*) AS cnt
		FROM teacher_course_assignments
		WHERE Teacher_ID = ?
		AND Group_ID   = ?
		AND ? BETWEEN Start_Date AND End_Date
	";
	$stm = $mysqli->prepare($sql);
	$stm->bind_param('iis', $teacher['Teacher_ID'], $sched['Group_ID'], $sched['Start_Date']);
	$stm->execute();
	if ($stm->get_result()->fetch_assoc()['cnt'] > 0) {
		$stm->close();
		return false;
	}
	$stm->close();

// 6) Load Hours Check
    $sql = "
        SELECT IFNULL(SUM(c.Course_Time), 0) AS hrs
        FROM teacher_course_assignments tca
        JOIN Courses c USING(Course_ID)
        WHERE tca.Teacher_ID = ?
    ";
    $stm = $mysqli->prepare($sql);
    $stm->bind_param('i', $teacher['Teacher_ID']);
    $stm->execute();
    $current = (int) $stm->get_result()->fetch_assoc()['hrs'];
    $stm->close();

$stm = $mysqli->prepare("SELECT Course_Time FROM Courses WHERE Course_ID = ?");
    $stm->bind_param('i', $sched['Course_ID']);
    $stm->execute();
    $toAdd = (int) $stm->get_result()->fetch_assoc()['Course_Time'];
    $stm->close();

if ($current + $toAdd > $teacher['Load_Hours']) return false;

// 7) Preferences
 $prefs = getPreferredCourses($teacher['Teacher_ID']);
	
	
	//// 8) Limit: no more than 3 courses with the same group
	//$sql = "
	//	SELECT COUNT(DISTINCT Course_ID) AS cnt
	//	FROM teacher_course_assignments
	//	WHERE Teacher_ID = ?
	//	AND Group_ID = ?
	//";
	//$stm = $mysqli->prepare($sql);
	//$stm->bind_param('ii', $teacher['Teacher_ID'], $sched['Group_ID']);
	//$stm->execute();
	//if ($stm->get_result()->fetch_assoc()['cnt'] >= 3) {
	//	$stm->close();
	//	return false;
	//}
	//$stm->close();
	
	// 9) Prevent consecutive assignments with same group
	//Explanation:
	//If the teacher just finished a course with that group (End_Date close to new Start_Date)	
	//And there’s less than 3 days gap	
	//➡️ Do not assign the next course
	$sql = "
		SELECT End_Date
		FROM teacher_course_assignments
		WHERE Teacher_ID = ?
		AND Group_ID = ?
		ORDER BY End_Date DESC
		LIMIT 1
	";
	$stm = $mysqli->prepare($sql);
	$stm->bind_param('ii', $teacher['Teacher_ID'], $sched['Group_ID']);
	$stm->execute();
	$res = $stm->get_result();
	if ($res->num_rows > 0) {
		$lastEnd = $res->fetch_assoc()['End_Date'];
		$gap = (strtotime($sched['Start_Date']) - strtotime($lastEnd)) / (60 * 60 * 24);
		//💡 Don't Skip the back-to-back rule also Premiums.
		//if ($gap <= 2) {
		//	$stm->close();
		//	return false;
		//}
		//💡 Skip the back-to-back rule only for Premiums.
		if ($gap <= 2) {
		// Allow Premium teachers to bypass the rule
		if (isset($teacher['Top_Teacher_ID']) && !empty($teacher['Top_Teacher_ID'])) {
			// Allow assignment Premium can proceed
		} else {
			$stm->close();
			return false;
		}
}

}
	$stm->close();
	
	// ✅ If all checks pass, return true
    return in_array($sched['Course_ID'], $prefs, true);
};

// ----------------------------------------
// ASSIGN_WRAPPER: round-robin assign
// ----------------------------------------
function assignFromList(array &$list, int &$ptr, array $sched, int $Reserve_Course): bool {
 global $canAssign, $mysqli;
 $n = count($list);
 if ($n === 0) {
 return false;
 }
 for ($i = 0; $i < $n; $i++) {
 $idx = ($ptr + $i) % $n;
 $t = $list[$idx];
 if (! $canAssign($t, $sched)) {
 continue;
 }
 // restriction check
 $chk = $mysqli->prepare("
 SELECT 1
 FROM teacher_restricted_groups
 WHERE Teacher_ID = ?
 AND Group_ID = ?
 ");
 $chk->bind_param('ii', $t['Teacher_ID'], $sched['Group_ID']);
 $chk->execute();
 if ($chk->get_result()->num_rows > 0) {
 $chk->close();
 continue;
 }
 $chk->close();
 // insert assignment
 $ins = $mysqli->prepare("
 INSERT INTO teacher_course_assignments
 (Teacher_ID, Course_ID, Group_ID, Program_ID,
 Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())
 ");
 $ins->bind_param(
 'iiiiisss',
 $t['Teacher_ID'],
 $sched['Course_ID'],
 $sched['Group_ID'],
 $sched['Program_ID'],
 $sched['Schedule_ID'],
 $sched['Time_Slot'],
 $sched['Start_Date'],
 $sched['End_Date']
 );
 $ins->execute();
 $ins->close();
 // mark schedule assigned
 $upd = $mysqli->prepare("
			UPDATE course_group_schedule_main
			SET Assigned = 1
			WHERE Schedule_ID = ?
		");
		$upd->bind_param('i', $sched['Schedule_ID']);

$upd->execute();
        $upd->close();
		
		$upd = $mysqli->prepare("
			UPDATE schedule_course_for_group
			SET Assigned = 1
			WHERE Schedule_ID = ?
		");
		$upd->bind_param('i', $sched['Schedule_ID']);
		$upd->execute();
		$upd->close();

// advance pointer
        $ptr = ($idx + 1) % $n;
        return true;
    }
    return false;
}

// ----------------------------------------
// PHASE 1: assign premium-only courses
// ----------------------------------------
$ptrP = $ptrQ = $ptrU = 0;
foreach ($schedules as $i => $sched) {
    if (in_array($sched['Course_ID'], $premiumOnlyCourses, true)) {
        assignFromList($premium, $ptrP, $sched, $sched['Reserve_Course']);
        // remove from list so PHASE 2 won't try it again
        unset($schedules[$i]);
    }
}

// reindex
$schedules = array_values($schedules);

// ----------------------------------------
// PHASE 2: assign all remaining courses
// ----------------------------------------
foreach ($schedules as $sched) {
    $assigned = assignFromList($premium, $ptrP, $sched, $sched['Reserve_Course']) ||
                    assignFromList($qualified, $ptrQ, $sched, $sched['Reserve_Course']) ||
                    assignFromList($unqualified, $ptrU, $sched, $sched['Reserve_Course']);
    if (! $assigned) {
        // log/report any that failed
        error_log("Schedule {$sched['Schedule_ID']} not assigned: no eligible teacher");
    }
}

// ----------------------------------------
// DONE: redirect with success message
// ----------------------------------------
set_flash_msg('Schedule generated successfully!', 'success');
header('Location: generateschedule.php');
exit;

}

/********Good Code Orginal****************
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
    $Program_ID = $_POST['Program_ID'];
    $Group_ID = $_POST['Group_ID'];
    $Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;

if ($Group_ID != 0) {
        $scheduleQuery = "SELECT * FROM course_group_schedule 
                          WHERE Assigned = 0 
                            AND Program_ID = ? 
                            AND Group_ID = ?
                            AND Reserve_Course = ?                     
                          ORDER BY Time_Slot";
        $stmt = $mysqli->prepare($scheduleQuery);
        $stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
    } else {
        $scheduleQuery = "SELECT * FROM course_group_schedule 
                          WHERE Assigned = 0 
                            AND Program_ID = ? 
                            AND Reserve_Course = ? 
                          ORDER BY Time_Slot";
        $stmt = $mysqli->prepare($scheduleQuery);
        $stmt->bind_param("ii", $Program_ID, $Reserve_Course);
    }
    if (!$stmt || !$stmt->execute()) {
        die("Error preparing/executing schedule query: " . $mysqli->error);
    }
    $schedulesResult = $stmt->get_result();
    $schedules = [];
    while ($row = $schedulesResult->fetch_assoc()) {
        $schedules[] = $row;
    }
    $stmt->close();

$teacherQuery = "
        SELECT 
            tp.Teacher_ID, 
            tp.First_Name, 
            tp.Last_Name, 
            tp.Seniority_ID, 
            tp.Time_Slot AS Availability,
            tp.Load_Hours,
            tp.User_Access,
            ttl.Top_Teacher_ID,
            ttl.Teacher_Level
        FROM teacher_profile tp
        LEFT JOIN top_teacher_list ttl 
            ON tp.Teacher_ID = ttl.Teacher_ID 
            AND ttl.Program_ID = ?
        WHERE 1
        ORDER BY tp.First_Name ASC, tp.Last_Name ASC
    ";
    $stmt = $mysqli->prepare($teacherQuery);
    $stmt->bind_param("i", $Program_ID);
    $stmt->execute();
    $result = $stmt->get_result();
    $allTeachers = [];
    while ($row = $result->fetch_assoc()) {
        $allTeachers[] = $row;
    }
    $stmt->close();

foreach ($allTeachers as &$teacher) {
        $teacher['Unavailability'] = [];
        $stmt = $mysqli->prepare("SELECT Unavailable_From, Unavailable_To FROM teacher_unavailability WHERE Teacher_ID = ? AND status = 'confirmed'");
        $stmt->bind_param("i", $teacher['Teacher_ID']);
        $stmt->execute();
        $res = $stmt->get_result();
        while ($unav = $res->fetch_assoc()) {
            $teacher['Unavailability'][] = $unav;
        }
        $stmt->close();
    }

$premiumTeachers = $qualifiedTeachers = $unqualifiedTeachers = [];
    foreach ($allTeachers as $teacher) {
        if (!empty($teacher['Top_Teacher_ID'])) {
            $premiumTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 1) {
            $qualifiedTeachers[] = $teacher;
        } else {
            $unqualifiedTeachers[] = $teacher;
        }
    }

$canAssign = function($teacher, $schedule) use ($mysqli) {
 if (isset($teacher['User_Access']) && $teacher['User_Access'] == 0) {
 return false;
 }
 foreach ($teacher['Unavailability'] as $unav) {
 $start = strtotime($unav['Unavailable_From']);
 $end = strtotime($unav['Unavailable_To']);
 $schStart = strtotime($schedule['Start_Date']);
 $schEnd = strtotime($schedule['End_Date']);
 if (($schStart <= $end) && ($schEnd >= $start)) {
 return false;
 }
 }
 $availableSlots = array_map('trim', explode(',', $teacher['Availability']));
 if (!in_array($schedule['Time_Slot'], $availableSlots)) {
 return false;
 }

$conflictQuery = "SELECT COUNT(*) AS cnt FROM teacher_course_assignments
 WHERE Teacher_ID = ? 
 AND Time_Slot = ?
 AND (? <= End_Date AND ? >= Start_Date)";
 $conflictStmt = $mysqli->prepare($conflictQuery);
 if (!$conflictStmt) return false;
 $conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
 $conflictStmt->execute();
 $conflictResult = $conflictStmt->get_result();
 $conflictRow = $conflictResult->fetch_assoc();
 $conflictStmt->close();
 if ($conflictRow['cnt'] > 0) return false;

$currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
                             FROM teacher_course_assignments tca
                             JOIN Courses c ON tca.Course_ID = c.Course_ID
                             WHERE tca.Teacher_ID = ?";
        $currentLoadStmt = $mysqli->prepare($currentLoadQuery);
        $currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
        $currentLoadStmt->execute();
        $currentLoadResult = $currentLoadStmt->get_result();
        $currentLoad = $currentLoadResult->fetch_assoc()['total_hours'];
        $currentLoadStmt->close();

$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
        $courseStmt = $mysqli->prepare($courseQuery);
        $courseStmt->bind_param("i", $schedule['Course_ID']);
        $courseStmt->execute();
        $newCourseTime = $courseStmt->get_result()->fetch_assoc()['Course_Time'];
        $courseStmt->close();
        if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) return false;

$prefQuery = "SELECT * FROM teacher_course_preferences 
                      WHERE Teacher_ID = ? 
                        AND Course_ID = ? 
                        AND Priority IN (1,2)";
        $prefStmt = $mysqli->prepare($prefQuery);
        $prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
        $prefStmt->execute();
        $prefResult = $prefStmt->get_result();
        $prefStmt->close();
        return $prefResult->num_rows > 0;
    };

$assignTeachers = function($teacherList, &$ptr, $schedule) use ($canAssign, $mysqli) {
 $cnt = count($teacherList);
 for ($i = 0; $i < $cnt; $i++) {
 $currentIndex = ($ptr + $i) % $cnt;
 $teacher = $teacherList[$currentIndex];
			
		
		
		// ✅ Step 2: Check if the teacher can be assigned (availability, preference, etc.)
 if ($canAssign($teacher, $schedule)) {
				
				// ✅ Step 1: Check if the teacher is restricted from this group
 $restrictionCheck = $mysqli->prepare("SELECT 1 FROM teacher_restricted_groups WHERE Teacher_ID = ? AND Group_ID = ?");
 //$restrictionCheck = $mysqli->prepare("SELECT 1 FROM teacher_restricted_groups WHERE Teacher_ID = ? AND Group_ID = ?");
if (!$restrictionCheck) {
 die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}

$restrictionCheck->bind_param("ii", $teacher['Teacher_ID'], $schedule['Group_ID']);
        $restrictionCheck->execute();
        $restrictionCheck->store_result();

// If the teacher is restricted, skip to the next
        if ($restrictionCheck->num_rows > 0) {
            $restrictionCheck->close();
            continue;
        }
        $restrictionCheck->close();
		
                $insertQuery = "INSERT INTO teacher_course_assignments 
                    (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $insertStmt = $mysqli->prepare($insertQuery);
                $assignedAt = date('Y-m-d H:i:s');
                $insertStmt->bind_param("iiiiissss",
                    $teacher['Teacher_ID'],
                    $schedule['Course_ID'],
                    $schedule['Group_ID'],
                    $schedule['Program_ID'],
                    $schedule['Schedule_ID'],
                    $schedule['Time_Slot'],
                    $schedule['Start_Date'],
                    $schedule['End_Date'],
                    $assignedAt
                );
                $insertStmt->execute();
                $insertStmt->close();

$updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
                $updateStmt = $mysqli->prepare($updateQuery);
                $updateStmt->bind_param("i", $schedule['Schedule_ID']);
                $updateStmt->execute();
                $updateStmt->close();

$ptr = ($currentIndex + 1) % $cnt;
                return true;
            }
        }
        return false;
    };

$ptrPremium = $ptrQualified = $ptrUnqualified = 0;
    foreach ($schedules as $schedule) {
        $assigned = $assignTeachers($premiumTeachers, $ptrPremium, $schedule)
                  || $assignTeachers($qualifiedTeachers, $ptrQualified, $schedule)
                  || $assignTeachers($unqualifiedTeachers, $ptrUnqualified, $schedule);
    }

header('Location: generateschedule.php?error=Schedule generated successfully!');
    exit();
}
*/

/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
    // Get Program ID and Group ID from the submitted form.
    $Program_ID = $_POST['Program_ID'];
    $Group_ID = $_POST['Group_ID'];
    $Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;

// -----------------------------
    // STEP 1: Get all unassigned schedules for this Program.
    // -----------------------------
    if ($Group_ID != 0) {
        $scheduleQuery = "SELECT * FROM course_group_schedule 
                          WHERE Assigned = 0 
                            AND Program_ID = ? 
                            AND Group_ID = ?
                            AND Reserve_Course = ?                     
                          ORDER BY Time_Slot";
        $stmt = $mysqli->prepare($scheduleQuery);
        $stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
    } else {
        $scheduleQuery = "SELECT * FROM course_group_schedule 
                          WHERE Assigned = 0 
                            AND Program_ID = ? 
                            AND Reserve_Course = ? 
                          ORDER BY Time_Slot";
        $stmt = $mysqli->prepare($scheduleQuery);
        $stmt->bind_param("ii", $Program_ID, $Reserve_Course);
    }
    if (!$stmt) {
        die("Error preparing schedule query: " . $mysqli->error);
    }
    if (!$stmt->execute()) {
        die("Error executing schedule query: " . $stmt->error);
    }
    $schedulesResult = $stmt->get_result();
    $schedules = [];
    while ($row = $schedulesResult->fetch_assoc()) {
        $schedules[] = $row;
    }
    $stmt->close();

// -----------------------------
    // STEP 2: Get teachers for this Program.
    // Combine data from teacher_profile and top_teacher_list (if any) and include new fields:
    // User_Access, Unavailable_From, and Unavailable_To.
    // -----------------------------
    $teacherQuery = "
        SELECT 
            tp.Teacher_ID, 
            tp.First_Name, 
            tp.Last_Name, 
            tp.Seniority_ID, 
            tp.Time_Slot AS Availability,
            tp.Load_Hours,
            tp.User_Access,
            tp.Unavailable_From,
            tp.Unavailable_To,
            ttl.Top_Teacher_ID,
            ttl.Teacher_Level
        FROM teacher_profile tp
        LEFT JOIN top_teacher_list ttl 
            ON tp.Teacher_ID = ttl.Teacher_ID 
            AND ttl.Program_ID = ?
        WHERE 1
        ORDER BY tp.First_Name ASC, tp.Last_Name ASC
    ";
    $stmt = $mysqli->prepare($teacherQuery);
    if (!$stmt) {
        die("Error preparing teacher query: " . $mysqli->error);
    }
    $stmt->bind_param("i", $Program_ID);
    $stmt->execute();
    $result = $stmt->get_result();
    $allTeachers = [];
    while ($row = $result->fetch_assoc()) {
        $allTeachers[] = $row;
    }
    $stmt->close();

// Separate teachers into three groups.
    $premiumTeachers = [];
    $qualifiedTeachers = [];
    $unqualifiedTeachers = [];
    foreach ($allTeachers as $teacher) {
        if (!empty($teacher['Top_Teacher_ID'])) {
            $premiumTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 1) {
            $qualifiedTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 2) {
            $unqualifiedTeachers[] = $teacher;
        }
    }
    $cntPremium = count($premiumTeachers);
    $cntQualified = count($qualifiedTeachers);
    $cntUnqualified = count($unqualifiedTeachers);
    $ptrPremium = 0;
    $ptrQualified = 0;
    $ptrUnqualified = 0;

// -----------------------------
 // STEP 3: Loop through each unassigned schedule and try to assign a teacher.
 // -----------------------------
 // Define a helper function (as an anonymous function) for assignment checks.
 $canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
 // 1. Skip if teacher's User_Access is 0.
 if (isset($teacher['User_Access']) && $teacher['User_Access'] == 0) {
 return false;
 }
 // 2. Exclude teacher if their unavailable period overlaps the schedule.
 if (isset($teacher['Unavailable_From'], $teacher['Unavailable_To']) &&
 !empty($teacher['Unavailable_From']) && !empty($teacher['Unavailable_To'])) {
 $teacherUnavailableFrom = strtotime($teacher['Unavailable_From']);
 $teacherUnavailableTo = strtotime($teacher['Unavailable_To']);
 $scheduleStart = strtotime($schedule['Start_Date']);
 $scheduleEnd = strtotime($schedule['End_Date']);
 // Overlap exists if the schedule starts before or on the teacher's unavailable end
 // and ends after or on the teacher's unavailable start.
 if (($scheduleStart <= $teacherUnavailableTo) && ($scheduleEnd >= $teacherUnavailableFrom)) {
 return false;
 }
 }
 // 3. Check teacher availability against the schedule's Time_Slot.
 $availableSlots = array_map('trim', explode(',', $teacher['Availability']));
 if (!in_array($schedule['Time_Slot'], $availableSlots)) {
 return false;
 }
 // 4. Check for conflicts: ensure teacher does not have an overlapping assignment.
 $conflictQuery = "SELECT COUNT(*) AS cnt FROM teacher_course_assignments
 WHERE Teacher_ID = ? 
 AND Time_Slot = ?
 AND (? <= End_Date AND ? >= Start_Date)";
 $conflictStmt = $mysqli->prepare($conflictQuery);
 if (!$conflictStmt) {
 echo "Error preparing conflict query: " . $mysqli->error . " ";
 return false;
 }
 $conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
 if (!$conflictStmt->execute()) {
 echo "Error executing conflict query: " . $conflictStmt->error . " ";
 $conflictStmt->close();
 return false;
 }
 $conflictResult = $conflictStmt->get_result();
 $conflictRow = $conflictResult->fetch_assoc();
 $conflictStmt->close();
 if ($conflictRow['cnt'] > 0) {
 return false;
 }
 // 5. Check load hours capacity.
 $currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
 FROM teacher_course_assignments tca
 JOIN Courses c ON tca.Course_ID = c.Course_ID
 WHERE tca.Teacher_ID = ?";
 $currentLoadStmt = $mysqli->prepare($currentLoadQuery);
 if (!$currentLoadStmt) {
 echo "Error preparing current load query: " . $mysqli->error . " ";
 return false;
 }
 $currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
 $currentLoadStmt->execute();
 $currentLoadResult = $currentLoadStmt->get_result();
 $currentLoadRow = $currentLoadResult->fetch_assoc();
 $currentLoadStmt->close();
 $currentLoad = $currentLoadRow['total_hours'];
 // 6. Get the new course's hours.
 $courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
 $courseStmt = $mysqli->prepare($courseQuery);
 if (!$courseStmt) {
 echo "Error preparing course query: " . $mysqli->error . " ";
 return false;
 }
 $courseStmt->bind_param("i", $schedule['Course_ID']);
 $courseStmt->execute();
 $courseResult = $courseStmt->get_result();
 $courseData = $courseResult->fetch_assoc();
 $newCourseTime = $courseData['Course_Time'];
 $courseStmt->close();
 if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
 return false;
 }
 // 7. Check teacher's course preferences.
 $prefQuery = "SELECT * FROM teacher_course_preferences 
 WHERE Teacher_ID = ? 
 AND Course_ID = ? 
 AND Priority IN (1,2)
 ORDER BY Priority ASC";
 $prefStmt = $mysqli->prepare($prefQuery);
 if (!$prefStmt) {
 echo "Error preparing preference query: " . $mysqli->error . " ";
 return false;
 }
 $prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
 $prefStmt->execute();
 $prefResult = $prefStmt->get_result();
 $preferences = [];
 while ($prefRow = $prefResult->fetch_assoc()) {
 $preferences[] = $prefRow;
 }
 $prefStmt->close();
 if (empty($preferences)) {
 return false;
 }
 // If all conditions are met, the teacher can be assigned.
 return true;
 };
 
 // -----------------------------
 // STEP 4: Process each schedule, assigning teachers from premium, then qualified, then unqualified groups.
 // -----------------------------
 foreach ($schedules as $schedule) {
 $assigned = false;
 // Attempt assignment from premium teachers.
 if ($cntPremium > 0) {
 for ($i = 0; $i < $cntPremium; $i++) {
 $currentIndex = ($ptrPremium + $i) % $cntPremium;
 $teacher = $premiumTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 // Insert assignment.
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 // Mark the schedule as assigned.
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 $ptrPremium = ($currentIndex + 1) % $cntPremium;
 $assigned = true;
 break;
 }
 }
 }
 // If not assigned by premium, try qualified teachers.
 if (!$assigned && $cntQualified > 0) {
 for ($i = 0; $i < $cntQualified; $i++) {
 $currentIndex = ($ptrQualified + $i) % $cntQualified;
 $teacher = $qualifiedTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 $ptrQualified = ($currentIndex + 1) % $cntQualified;
 $assigned = true;
 break;
 }
 }
 }
 // Finally, if still not assigned, try unqualified teachers.
 if (!$assigned && $cntUnqualified > 0) {
 for ($i = 0; $i < $cntUnqualified; $i++) {
 $currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
 $teacher = $unqualifiedTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 $ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
 $assigned = true;
 break;
 }
 }
 }
 }
 // After processing, redirect or show a success message.
 header('Location: generateschedule.php?error=Schedule generated successfully!');
 exit();
}

*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
    // Get Program ID and Group ID from the submitted form
    $Program_ID = $_POST['Program_ID'];
    $Group_ID = $_POST['Group_ID'];
    $Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;

// -----------------------------
    // STEP 1: Get all unassigned schedules for this Program
    // -----------------------------
    if ($Group_ID != 0) {
        $scheduleQuery = "SELECT * FROM course_group_schedule 
                          WHERE Assigned = 0 
                            AND Program_ID = ? 
                            AND Group_ID = ?
                            AND Reserve_Course = ?                     
                          ORDER BY Time_Slot";
        $stmt = $mysqli->prepare($scheduleQuery);
        $stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
    } else {
        $scheduleQuery = "SELECT * FROM course_group_schedule 
                          WHERE Assigned = 0 
                            AND Program_ID = ? 
                            AND Reserve_Course = ? 
                          ORDER BY Time_Slot";
        $stmt = $mysqli->prepare($scheduleQuery);
        $stmt->bind_param("ii", $Program_ID, $Reserve_Course);
    }

if (!$stmt) {
        die("Error preparing schedule query: " . $mysqli->error);
    }
    if (!$stmt->execute()) {
        die("Error executing schedule query: " . $stmt->error);
    }
    $schedulesResult = $stmt->get_result();
    $schedules = [];
    while ($row = $schedulesResult->fetch_assoc()) {
        $schedules[] = $row;
    }
    $stmt->close();

// -----------------------------
    // STEP 2: Get teachers for this Program by combining teacher_profile and top_teacher_list.
    // Add User_Access field to prevent assigning courses to teachers who have User_Access = 0.
    // -----------------------------
	// Added User_Access field 
    $teacherQuery = "
        SELECT 
            tp.Teacher_ID, 
            tp.First_Name, 
            tp.Last_Name, 
            tp.Seniority_ID, 
            tp.Time_Slot AS Availability,
            tp.Load_Hours,
            tp.User_Access,                
            ttl.Top_Teacher_ID,
            ttl.Teacher_Level
        FROM teacher_profile tp
        LEFT JOIN top_teacher_list ttl 
            ON tp.Teacher_ID = ttl.Teacher_ID 
            AND ttl.Program_ID = ?
        WHERE 1
        ORDER BY tp.First_Name ASC, tp.Last_Name ASC
    ";
    $stmt = $mysqli->prepare($teacherQuery);
    if (!$stmt) {
        die("Error preparing teacher query: " . $mysqli->error);
    }
    $stmt->bind_param("i", $Program_ID);
    $stmt->execute();
    $result = $stmt->get_result();
    $allTeachers = [];
    while ($row = $result->fetch_assoc()) {
        $allTeachers[] = $row;
    }
    $stmt->close();

// Separate teachers into groups.
    $premiumTeachers = [];
    $qualifiedTeachers = [];
    $unqualifiedTeachers = [];
    
    foreach ($allTeachers as $teacher) {
        // Note: if Teacher_User_Access = 0, the teacher is not eligible;
        // The check is performed later in the assignment function.
        if (!empty($teacher['Top_Teacher_ID'])) {
            $premiumTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 1) {
            $qualifiedTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 2) {
            $unqualifiedTeachers[] = $teacher;
        }
    }

// Initialize round-robin pointers for each group.
    $cntPremium = count($premiumTeachers);
    $cntQualified = count($qualifiedTeachers);
    $cntUnqualified = count($unqualifiedTeachers);
    $ptrPremium = 0;
    $ptrQualified = 0;
    $ptrUnqualified = 0;

// -----------------------------
    // STEP 3: Loop through each unassigned schedule and try to assign a teacher
    // -----------------------------

// Helper inline function: for each teacher, perform assignment checks.
 // (Availability, conflicts, load hours, preferences)
 $canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
 // New check: Do not assign courses if the teacher's User_Access equals 0.
 if (isset($teacher['User_Access']) && $teacher['User_Access'] == 0) {
 return false;
 }
 
 // Check availability: Teacher's Availability field is a comma-separated list.
 $availableSlots = array_map('trim', explode(',', $teacher['Availability']));
 if (!in_array($schedule['Time_Slot'], $availableSlots)) {
 return false;
 }
 
 // Check conflicts: any overlapping assignment in the same time slot.
 $conflictQuery = "SELECT COUNT(*) AS cnt FROM teacher_course_assignments
 WHERE Teacher_ID = ? 
 AND Time_Slot = ?
 AND (? <= End_Date AND ? >= Start_Date)";
 $conflictStmt = $mysqli->prepare($conflictQuery);
 if (!$conflictStmt) {
 echo "Error preparing conflict query: " . $mysqli->error . " ";
 return false;
 }
 $conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
 if (!$conflictStmt->execute()) {
 echo "Error executing conflict query: " . $conflictStmt->error . " ";
 $conflictStmt->close();
 return false;
 }
 $conflictResult = $conflictStmt->get_result();
 $conflictRow = $conflictResult->fetch_assoc();
 $conflictStmt->close();
 if ($conflictRow['cnt'] > 0) {
 return false;
 }
 
 // Check load hours capacity.
 // 1. Get teacher's current assigned hours.
 $currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
 FROM teacher_course_assignments tca
 JOIN Courses c ON tca.Course_ID = c.Course_ID
 WHERE tca.Teacher_ID = ?";
 $currentLoadStmt = $mysqli->prepare($currentLoadQuery);
 if (!$currentLoadStmt) {
 echo "Error preparing current load query: " . $mysqli->error . " ";
 return false;
 }
 $currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
 $currentLoadStmt->execute();
 $currentLoadResult = $currentLoadStmt->get_result();
 $currentLoadRow = $currentLoadResult->fetch_assoc();
 $currentLoadStmt->close();
 $currentLoad = $currentLoadRow['total_hours'];
 
 // 2. Get new course's hours.
 $courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
 $courseStmt = $mysqli->prepare($courseQuery);
 if (!$courseStmt) {
 echo "Error preparing course query: " . $mysqli->error . " ";
 return false;
 }
 $courseStmt->bind_param("i", $schedule['Course_ID']);
 $courseStmt->execute();
 $courseResult = $courseStmt->get_result();
 $courseData = $courseResult->fetch_assoc();
 $newCourseTime = $courseData['Course_Time'];
 $courseStmt->close();
 
 if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
 return false;
 }
 
 // Check teacher's course preferences.
 $prefQuery = "SELECT * FROM teacher_course_preferences 
 WHERE Teacher_ID = ? 
 AND Course_ID = ? 
 AND Priority IN (1,2)
 ORDER BY Priority ASC";
 $prefStmt = $mysqli->prepare($prefQuery);
 if (!$prefStmt) {
 echo "Error preparing preference query: " . $mysqli->error . " ";
 return false;
 }
 $prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
 $prefStmt->execute();
 $prefResult = $prefStmt->get_result();
 $preferences = [];
 while ($prefRow = $prefResult->fetch_assoc()) {
 $preferences[] = $prefRow;
 }
 $prefStmt->close();
 
 if (empty($preferences)) {
 return false;
 }
 
 return true;
 }; // end of anonymous function
 
 // Process each schedule entry and assign a teacher based on the groups, in a round-robin fashion.
 foreach ($schedules as $schedule) {
 $assigned = false;
 
 // Try premium teachers first.
 if ($cntPremium > 0) {
 for ($i = 0; $i < $cntPremium; $i++) {
 $currentIndex = ($ptrPremium + $i) % $cntPremium;
 $teacher = $premiumTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 // Insert assignment.
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 
 // Mark schedule as assigned.
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 
 $ptrPremium = ($currentIndex + 1) % $cntPremium;
 $assigned = true;
 break;
 }
 }
 }
 
 // If not assigned by premium, try qualified teachers.
 if (!$assigned && $cntQualified > 0) {
 for ($i = 0; $i < $cntQualified; $i++) {
 $currentIndex = ($ptrQualified + $i) % $cntQualified;
 $teacher = $qualifiedTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 
 $ptrQualified = ($currentIndex + 1) % $cntQualified;
 $assigned = true;
 break;
 }
 }
 }
 
 // If still not assigned, try unqualified teachers.
 if (!$assigned && $cntUnqualified > 0) {
 for ($i = 0; $i < $cntUnqualified; $i++) {
 $currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
 $teacher = $unqualifiedTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 
 $ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
 $assigned = true;
 break;
 }
 }
 }
 }
 // After processing, redirect or show a success message.
 header('Location: generateschedule.php?error=Schedule generated successfully!');
 exit();
}
*/

/**
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
    // Get Program ID and Group ID from the submitted form
    $Program_ID = $_POST['Program_ID'];
    $Group_ID = $_POST['Group_ID'];
	$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;

// For demonstration, using fixed Program_ID and Group_ID (in your actual system, these come from the submitted form)
//$Program_ID = 1;
//$Group_ID   = 13;

// -----------------------------
// STEP 1: Get all unassigned schedules for this Program
// -----------------------------

if (!$stmt) {
    die("Error preparing schedule query: " . $mysqli->error);
}

if (!$stmt->execute()) {
    die("Error executing schedule query: " . $stmt->error);
}
$schedulesResult = $stmt->get_result();
$schedules = [];
while ($row = $schedulesResult->fetch_assoc()) {
    $schedules[] = $row;
}
$stmt->close();

// -----------------------------
// STEP 2: Get teachers for this Program, combining teacher_profile and top_teacher_list.
// Ordering: Premium (top) teachers first, then teachers with Seniority_ID = 1 (qualified),
// then teachers with Seniority_ID = 2 (unqualified).
// -----------------------------
//$teacherQuery = "
//    SELECT 
//        tp.Teacher_ID, 
//        tp.First_Name, 
//        tp.Last_Name, 
//        tp.Seniority_ID, 
//        tp.Time_Slot AS Availability,
//        tp.Load_Hours,
//        ttl.Top_Teacher_ID,
//        ttl.Teacher_Level
//    FROM teacher_profile tp
//    LEFT JOIN top_teacher_list ttl 
//        ON tp.Teacher_ID = ttl.Teacher_ID 
//        AND ttl.Program_ID = ?
//    WHERE 1
//    ORDER BY 
//        CASE 
//            WHEN ttl.Top_Teacher_ID IS NOT NULL THEN 1
//            WHEN tp.Seniority_ID = 1 THEN 2
//            WHEN tp.Seniority_ID = 2 THEN 3
//            ELSE 4
//        END,
//        tp.First_Name ASC,
//        tp.Last_Name ASC
//";
//$stmt = $mysqli->prepare($teacherQuery);
//if (!$stmt) {
//    die("Error preparing teacher query: " . $mysqli->error);
//}
//$stmt->bind_param("i", $Program_ID);
//if (!$stmt->execute()) {
//    die("Error executing teacher query: " . $stmt->error);
//}
//$result = $stmt->get_result();
//$teachers = [];
//while ($row = $result->fetch_assoc()) {
//    $teachers[] = $row;
//}
//$stmt->close();

//// Debug: Optional print teacher list
//foreach ($teachers as $teacher) {
// $isPremium = !empty($teacher['Top_Teacher_ID']) ? "Premium (Level: " . $teacher['Teacher_Level'] . ")" : "";
// echo $teacher['First_Name'] . " " . $teacher['Last_Name'] . " - Seniority: " . $teacher['Seniority_ID'] . " " . $isPremium . " - Load Hours: " . $teacher['Load_Hours'] . " ";
//}

// STEP 2: Get teachers for this Program by combining teacher_profile and top_teacher_list.
// (No filtering on teacher_profile by Program_ID because it's not in that table.)
$teacherQuery = "
    SELECT 
        tp.Teacher_ID, 
        tp.First_Name, 
        tp.Last_Name, 
        tp.Seniority_ID, 
        tp.Time_Slot AS Availability,
        tp.Load_Hours,
        ttl.Top_Teacher_ID,
        ttl.Teacher_Level
    FROM teacher_profile tp
    LEFT JOIN top_teacher_list ttl 
        ON tp.Teacher_ID = ttl.Teacher_ID 
        AND ttl.Program_ID = ?
    WHERE 1
    ORDER BY tp.First_Name ASC, tp.Last_Name ASC
";
$stmt = $mysqli->prepare($teacherQuery);
if (!$stmt) {
    die("Error preparing teacher query: " . $mysqli->error);
}
$stmt->bind_param("i", $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
$allTeachers = [];
while ($row = $result->fetch_assoc()) {
    $allTeachers[] = $row;
}
$stmt->close();

// Initialize the round-robin pointer
$teacherCount = count($teachers);
$teacherIndex = 0;

// -----------------------------
// STEP 3: Loop through each unassigned schedule and try to assign a teacher
// -----------------------------
// Separate teachers into groups
$premiumTeachers = [];
$qualifiedTeachers = [];
$unqualifiedTeachers = [];

foreach ($allTeachers as $teacher) {
        // Note: if Teacher_User_Access = 0, the teacher is not eligible;
        // The check is performed later in the assignment function.
        if (!empty($teacher['Top_Teacher_ID'])) {
            $premiumTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 1) {
            $qualifiedTeachers[] = $teacher;
        } elseif ($teacher['Seniority_ID'] == 2) {
            $unqualifiedTeachers[] = $teacher;
        }
    }

// Initialize round-robin pointers for each group
$ptrPremium = 0;
$ptrQualified = 0;
$ptrUnqualified = 0;
$cntPremium = count($premiumTeachers);
$cntQualified = count($qualifiedTeachers);
$cntUnqualified = count($unqualifiedTeachers);

//// (Optional) Debug output: print ordered teacher groups
//echo "<h3>Premium Teachers:</h3>";
//foreach ($premiumTeachers as $t) {
// echo $t['First_Name']." ".$t['Last_Name']." (Level: ".$t['Teacher_Level'].") ";
//}
//echo "<h3>Qualified Teachers:</h3>";
//foreach ($qualifiedTeachers as $t) {
// echo $t['First_Name']." ".$t['Last_Name']." (Seniority: ".$t['Seniority_ID'].") ";
//}
//echo "<h3>Unqualified Teachers:</h3>";
//foreach ($unqualifiedTeachers as $t) {
// echo $t['First_Name']." ".$t['Last_Name']." (Seniority: ".$t['Seniority_ID'].") ";
//}
//echo "<hr>";

// Helper inline: For each teacher, perform assignment checks.
// (Availability, conflict, load hours, preferences)
foreach ($schedules as $schedule) {
 $assigned = false;
 
 // Function to check if teacher can be assigned
 // (We inline the checks here.)
 $canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
 // Check availability: Teacher's Availability field is a comma-separated list.
 $availableSlots = array_map('trim', explode(',', $teacher['Availability']));
 if (!in_array($schedule['Time_Slot'], $availableSlots)) {
 return false;
 }
 
 // Check conflicts: any overlapping assignment in the same time slot.
 $conflictQuery = "SELECT COUNT(*) AS cnt FROM teacher_course_assignments
 WHERE Teacher_ID = ? 
 AND Time_Slot = ?
 AND (? <= End_Date AND ? >= Start_Date)";
 $conflictStmt = $mysqli->prepare($conflictQuery);
 if (!$conflictStmt) {
 echo "Error preparing conflict query: " . $mysqli->error . " ";
 return false;
 }
 $conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
 if (!$conflictStmt->execute()) {
 echo "Error executing conflict query: " . $conflictStmt->error . " ";
 $conflictStmt->close();
 return false;
 }
 $conflictResult = $conflictStmt->get_result();
 $conflictRow = $conflictResult->fetch_assoc();
 $conflictCount = $conflictRow['cnt'];
 $conflictStmt->close();
 if ($conflictCount > 0) {
 return false;
 }
 
 // Check load hours capacity:
 // 1. Get teacher's current assigned hours.
 $currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
 FROM teacher_course_assignments tca
 JOIN Courses c ON tca.Course_ID = c.Course_ID
 WHERE tca.Teacher_ID = ?";
 $currentLoadStmt = $mysqli->prepare($currentLoadQuery);
 if (!$currentLoadStmt) {
 echo "Error preparing current load query: " . $mysqli->error . " ";
 return false;
 }
 $currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
 $currentLoadStmt->execute();
 $currentLoadResult = $currentLoadStmt->get_result();
 $currentLoadRow = $currentLoadResult->fetch_assoc();
 $currentLoad = $currentLoadRow['total_hours'];
 $currentLoadStmt->close();
 
 // 2. Get new course's hours.
 $courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
 $courseStmt = $mysqli->prepare($courseQuery);
 if (!$courseStmt) {
 echo "Error preparing course query: " . $mysqli->error . " ";
 return false;
 }
 $courseStmt->bind_param("i", $schedule['Course_ID']);
 $courseStmt->execute();
 $courseResult = $courseStmt->get_result();
 $courseData = $courseResult->fetch_assoc();
 $newCourseTime = $courseData['Course_Time'];
 $courseStmt->close();
 
 if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
 return false;
 }
 
 // Check teacher's course preferences.
 $prefQuery = "SELECT * FROM teacher_course_preferences 
 WHERE Teacher_ID = ? 
 AND Course_ID = ? 
 AND Priority IN (1,2)
 ORDER BY Priority ASC";
 $prefStmt = $mysqli->prepare($prefQuery);
 if (!$prefStmt) {
 echo "Error preparing preference query: " . $mysqli->error . " ";
 return false;
 }
 $prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
 $prefStmt->execute();
 $prefResult = $prefStmt->get_result();
 $preferences = [];
 while ($prefRow = $prefResult->fetch_assoc()) {
 $preferences[] = $prefRow;
 }
 $prefStmt->close();
 
 if (empty($preferences)) {
 return false;
 }
 
 return true;
 }; // end of anonymous function
 
 // Try to assign from premium teachers first
 if ($cntPremium > 0) {
 for ($i = 0; $i < $cntPremium; $i++) {
 $currentIndex = ($ptrPremium + $i) % $cntPremium;
 $teacher = $premiumTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 // Assign this teacher
 // Insert assignment
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 
 // Mark schedule as assigned.
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 
 // Update pointer for premium group.
 $ptrPremium = ($currentIndex + 1) % $cntPremium;
 $assigned = true;
 break;
 }
 }
 }
 
 // If not assigned by premium, try qualified teachers.
 if (!$assigned && $cntQualified > 0) {
 for ($i = 0; $i < $cntQualified; $i++) {
 $currentIndex = ($ptrQualified + $i) % $cntQualified;
 $teacher = $qualifiedTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 
 $ptrQualified = ($currentIndex + 1) % $cntQualified;
 $assigned = true;
 break;
 }
 }
 }
 
 // If still not assigned, try unqualified teachers.
 if (!$assigned && $cntUnqualified > 0) {
 for ($i = 0; $i < $cntUnqualified; $i++) {
 $currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
 $teacher = $unqualifiedTeachers[$currentIndex];
 if ($canAssign($teacher, $schedule)) {
 $insertQuery = "INSERT INTO teacher_course_assignments 
 (Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 $insertStmt = $mysqli->prepare($insertQuery);
 if (!$insertStmt) {
 echo "Error preparing insert query: " . $mysqli->error . " ";
 continue;
 }
 $assignedAt = date('Y-m-d H:i:s');
 $insertStmt->bind_param("iiiiissss",
 $teacher['Teacher_ID'],
 $schedule['Course_ID'],
 $schedule['Group_ID'],
 $schedule['Program_ID'],
 $schedule['Schedule_ID'],
 $schedule['Time_Slot'],
 $schedule['Start_Date'],
 $schedule['End_Date'],
 $assignedAt
 );
 if (!$insertStmt->execute()) {
 echo "Error executing insert query: " . $insertStmt->error . " ";
 $insertStmt->close();
 continue;
 }
 $insertStmt->close();
 
 $updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
 $updateStmt = $mysqli->prepare($updateQuery);
 if (!$updateStmt) {
 echo "Error preparing update query: " . $mysqli->error . " ";
 continue;
 }
 $updateStmt->bind_param("i", $schedule['Schedule_ID']);
 if (!$updateStmt->execute()) {
 echo "Error executing update query: " . $updateStmt->error . " ";
 $updateStmt->close();
 continue;
 }
 $updateStmt->close();
 
 $ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
 $assigned = true;
 break;
 }
 }
 }
}
// After processing, redirect or show a success message
header('Location: generateschedule.php?error=Schedule generated successfully!');
 exit();
}
*/
?>

cmd: