<?php

function create_timetable_adjustments($conn_rjllearning_specific, $institution_id, $branch_id, $original_teacher_id, $adjustment_info, $adjustment_date, $username)
{
    try {
        $inserted_ids = [];

        foreach ($adjustment_info as $adjustment) {
            if (!isset($adjustment['lecture_id']) || !isset($adjustment['replacement_teacher_id'])) {
                errorreturn("Missing lecture_id or replacement_teacher_id in adjustment_info.");
            }

            // Check if the same replacement_teacher_id already exists for the same lecture_id on the adjustment_date
            $checkSql = "SELECT COUNT(*) as count FROM timetable_adjustments 
                         WHERE institution_id = ? AND branch_id = ? AND lecture_id = ? 
                         AND replacement_teacher_id = ? AND adjustment_date = ?";

            $checkStmt = $conn_rjllearning_specific->prepare($checkSql);
            $checkStmt->execute([
                $institution_id,
                $branch_id,
                $adjustment['lecture_id'],
                $adjustment['replacement_teacher_id'],
                $adjustment_date
            ]);

            $exists = $checkStmt->fetch();

            if ($exists['count'] > 0) {
                errorreturn("Adjustment already exists for lecture id: " . $adjustment['lecture_id'] . " with the same replacement teacher on the given date.");
            }

            // Insert the adjustment
            $sql = "INSERT INTO timetable_adjustments (
                        institution_id, branch_id, original_teacher_id, replacement_teacher_id, lecture_id, 
                        adjustment_date, created_by, created_at, date_created, status
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            $stmt = $conn_rjllearning_specific->prepare($sql);
            $stmt->execute([
                $institution_id,
                $branch_id,
                $original_teacher_id,
                $adjustment['replacement_teacher_id'],
                $adjustment['lecture_id'],
                $adjustment_date,
                $username,
                time(),
                date('Y-m-d'),
                1
            ]);

            $inserted_ids[] = $conn_rjllearning_specific->lastInsertId();
        }

        successreturn("Adjustments added successfully.");
    } catch (Exception $e) {
        errorreturn("Database Error: " . $e->getMessage());
    }
}

function getSpecific_faculty_Timetable($conn_rjllearning_specific, $institution_id, $branch_id, $faculty_id, $date)
{
    try {
        $array_return = [];

        $dayOfWeek = date('N', strtotime($date)); // Get numeric day of the week (1 for Monday, 7 for Sunday)

        $timetable_query = "SELECT id AS lecture_id, class_id, section_id, subject_id, period_start, end_time, day, type, status 
                            FROM timetable 
                            WHERE institution_id = ? 
                            AND branch_id = ? 
                            AND faculty_id = ? 
                            AND day = ? 
                            AND status = 1 
                            AND type = 'timetable' 
                            AND date_created = (
                                SELECT MAX(date_created) 
                                FROM timetable 
                                WHERE institution_id = ? 
                                AND branch_id = ? 
                                AND faculty_id = ? 
                                AND day = ? 
                                AND status = 1 
                                AND type = 'timetable' 
                                AND date_created <= ?
                            )";

        $timetable_stmt = $conn_rjllearning_specific->prepare($timetable_query);
        $timetable_stmt->execute([$institution_id, $branch_id, $faculty_id, $dayOfWeek, 
                                  $institution_id, $branch_id, $faculty_id, $dayOfWeek, $date]);

        $entries = $timetable_stmt->fetchAll(PDO::FETCH_ASSOC);

        if (empty($entries)) {
            errorreturn ("No timetable entries found for this faculty on the given date.");
        }

        $lecture_details = [];
        foreach ($entries as $entry) {
            $class_info = getClass($conn_rjllearning_specific, $institution_id, $entry['class_id']);
            $section_info = getSection($conn_rjllearning_specific, $institution_id, $entry['section_id']);
            $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $entry['subject_id']);

            // Check if the lecture_id exists in timetable_adjustments for the given date
            $adjustment_query = "SELECT COUNT(*) as count, MAX(replacement_teacher_id) as replacement_teacher_id 
                                 FROM timetable_adjustments 
                                 WHERE institution_id = ? 
                                 AND branch_id = ? 
                                 AND lecture_id = ? 
                                 AND adjustment_date = ? 
                                 GROUP BY lecture_id";

            $adjustment_stmt = $conn_rjllearning_specific->prepare($adjustment_query);
            $adjustment_stmt->execute([$institution_id, $branch_id, $entry['lecture_id'], $date]);
            $adjustment = $adjustment_stmt->fetch();

            if (!$adjustment) {
                $lecture_adjusted = 0;
                $replacement_teacher = null;
            } else {
                $lecture_adjusted = $adjustment['count'] > 0 ? 1 : 0;
                $replacement_teacher = !empty($adjustment['replacement_teacher_id']) ? 
                                        getFacultyInfo($conn_rjllearning_specific, $institution_id, $adjustment['replacement_teacher_id']) : null;
            }

            // Construct lecture entry
            $lecture_entry = [
                "lecture_id" => $entry['lecture_id'],
                "class" => $class_info,
                "section" => $section_info,
                "subject" => $subject_info,
                "period_start" => $entry['period_start'],
                "end_time" => $entry['end_time'],
                "day" => $entry['day'],
                "type" => $entry['type'],
                "status" => $entry['status'],
                "lecture_adjusted" => $lecture_adjusted
            ];

            if ($replacement_teacher) {
                $lecture_entry["replacement_teacher"] = $replacement_teacher;
            }

            $lecture_details[] = $lecture_entry; // Push the correctly formatted lecture entry
        }

        $faculty_info = getFacultyInfo($conn_rjllearning_specific, $institution_id, $faculty_id);

        $array_return = [
            "faculty" => $faculty_info,
            "institution_id" => $institution_id,
            "branch_id" => $branch_id,
            "date" => $date,
            "timetable" => $lecture_details
        ];

        return $array_return;
    } catch (Exception $e) {
        return ["status" => 0, "error" => $e->getMessage()];
    }
}



// function teachers_on_leave_and_timetable($conn_rjllearning_specific, $institution_id, $branch_id, $date)
// {
//     try {
//         // Fetch teachers on leave for the given date -- only approved leaves
//         $leave_query = "SELECT staff_id FROM staff_apply_leaves WHERE institution_id = ? AND branch_id = ? 
//                         AND ? BETWEEN start_date AND end_date AND status = 1";

//         $leave_stmt = $conn_rjllearning_specific->prepare($leave_query);
//         $leave_stmt->execute([$institution_id, $branch_id, $date]);
//         $teachers_on_leave = $leave_stmt->fetchAll(PDO::FETCH_ASSOC);
        
//         // print_r($teachers_on_leave);

//         if (empty($teachers_on_leave)) {
//             errorreturn("No teachers on leave for the given date.");
//         }

//         $faculty_timetable_data = [];
//         // $dayOfWeek = strtolower(date('l', strtotime($date))); // Get full day name in lowercase
//         $dayOfWeek = date('N', strtotime($date)); 

//         // Fetch timetable for teachers on leave -- Only approved timetable entries
//         foreach ($teachers_on_leave as $teacher) {
//             $teacherId = $teacher['staff_id'];
            
//             $sql = "SELECT id FROM faculty_info WHERE id = ?";
//             $stmt = $conn_rjllearning_specific->prepare($sql);
//             $stmt->execute([$teacherId]);
//             $faculty = $stmt->fetch(PDO::FETCH_ASSOC);
//             $faculty_id = $faculty['id'];
//             // print_r($faculty_id);
            
//             if (!$faculty_id) {
//                 errorreturn("No faculty found for teacher ID: $teacherId");
//             }

//             $timetable_query = "SELECT id AS lecture_id, class_id, section_id, subject_id, period_start, end_time, day, type, status 
//                     FROM timetable 
//                     WHERE institution_id = ? 
//                     AND branch_id = ? 
//                     AND faculty_id = ? 
//                     AND day = ? 
//                     AND status = 1 
//                     AND type = 'timetable'
//                     AND date_created = (
//                         SELECT MAX(date_created) 
//                         FROM timetable 
//                         WHERE institution_id = ? 
//                         AND branch_id = ? 
//                         AND faculty_id = ? 
//                         AND day = ? 
//                         AND status = 1 
//                         AND type = 'timetable'
//                         AND date_created <= ?
//                     )";
                    
//             $timetable_stmt = $conn_rjllearning_specific->prepare($timetable_query);
//             $timetable_stmt->execute([$institution_id, $branch_id, $faculty_id, $dayOfWeek, 
//                                       $institution_id, $branch_id, $faculty_id, $dayOfWeek, $date]);
            
//             $entries = $timetable_stmt->fetchAll(PDO::FETCH_ASSOC);


//             if (!empty($entries)) {
//                 $lecture_details = [];

//                 foreach ($entries as $entry) {
//                     $lecture_details[] = [
//                         "lecture_id" => $entry['lecture_id'],
//                         "class_id" => $entry['class_id'],
//                         "section_id" => $entry['section_id'],
//                         "subject_id" => $entry['subject_id'],
//                         "period_start" => $entry['period_start'],
//                         "end_time" => $entry['end_time'],
//                         "day" => $entry['day'],
//                         "type" => $entry['type'],
//                         "status" => $entry['status']
//                     ];
//                 }

//                 $faculty_timetable_data = [
//                     "faculty_id" => $teacherId,
//                     "institution_id" => $institution_id,
//                     "branch_id" => $branch_id,
//                     "date" => $date,
//                     "timetable" => $lecture_details
//                 ];
//             }
//         }

//         if (empty($faculty_timetable_data)) {
//             errorreturn("No timetable entries found for teachers on leave.");
//         }

//         return $faculty_timetable_data;
//     } catch (Exception $e) {
//         return ["status" => 0, "error" => $e->getMessage()];
//     }
// }

function findReplacementTeachers($conn_rjllearning_specific, $date, $day, $period_start, $period_end, $institution_id, $branch_id) 
{
    try {
        // Step 1: Get the faculty_id (teacher who needs replacement) from the timetable
        $teacher_query = "
            SELECT faculty_id FROM timetable 
            WHERE institution_id = ? 
              AND branch_id = ? 
              AND day = ? 
              AND period_start = ? 
              AND end_time = ? 
              AND status = 1";
        $stmt = $conn_rjllearning_specific->prepare($teacher_query);
        $stmt->execute([$institution_id, $branch_id, $day, $period_start, $period_end]);
        $teacher = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$teacher) {
            errorreturn ("No faculty assigned for this period.");
        }
        $teacher_id = $teacher['faculty_id']; // Teacher who needs replacement

        // Step 2: Find available replacement teachers
        $replacement_query = "
            SELECT fi.id AS faculty_id, fi.name AS faculty_name
            FROM faculty_info fi
            LEFT JOIN timetable tt ON fi.id = tt.faculty_id 
                AND tt.institution_id = ? 
                AND tt.branch_id = ? 
                AND tt.day = ? 
                AND tt.status = 1
            LEFT JOIN timetable_adjustments a ON fi.id = a.replacement_teacher_id 
                AND a.adjustment_date = ? 
                AND a.status = 1
            LEFT JOIN timetable tt_adj ON a.lecture_id = tt_adj.id  -- Fetch actual period timing
                AND NOT (tt_adj.period_start >= ? OR tt_adj.end_time <= ?)
            LEFT JOIN staff_apply_leaves sal ON fi.id = sal.staff_id 
                AND ? BETWEEN sal.start_date AND sal.end_date 
                AND sal.status = 1
            LEFT JOIN staff_attendance_daywise sad ON fi.id = sad.user_id 
                AND sad.date = ? 
                AND sad.institution_id = ? 
                AND sad.branch_id = ?
            WHERE fi.institution_id = ? 
              AND fi.branch_id = ? 
              AND sal.staff_id IS NULL 
              AND a.replacement_teacher_id IS NULL -- Ensure they are not already assigned as a replacement for this period
              AND fi.id != ? 
              AND (sad.value IS NULL OR sad.value = 'P') 
              AND (
                  tt.faculty_id IS NULL 
                  OR (
                      tt.faculty_id IS NOT NULL 
                      AND (tt.end_time <= ? OR tt.period_start >= ?) -- Ensure no clash with scheduled periods
                  )
              )
            GROUP BY fi.id
        ";

        $stmt = $conn_rjllearning_specific->prepare($replacement_query);
        $stmt->execute([
            $institution_id, $branch_id, $day, $date, $period_start, $period_end, $date, $date, 
            $institution_id, $branch_id, $institution_id, $branch_id, $teacher_id,
            $period_start, $period_end
        ]);

        $replacement_teachers = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Check if no teachers are found
        if (empty($replacement_teachers)) {
            errorreturn ("No faculties found for the specified criteria.");
        }

        return $replacement_teachers;
    } catch (Exception $e) {
        error_log("Error finding replacement teachers: " . $e->getMessage());
        errorreturn ("Unable to find any replacement.");
    }
}

function get_specific_adjustment($conn_rjllearning_specific, $institution_id, $branch_id, $adjustment_id)
{
    try {
        
        $sql = "SELECT * FROM timetable_adjustments WHERE institution_id = ? AND branch_id = ? AND id = ? AND status = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$institution_id, $branch_id, $adjustment_id, 1]);
        $adjustment = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$adjustment) {
            errorreturn("adjustment not found.");
        }

        $array_return = [];

        $sql_lecture = "SELECT id, class_id, subject_id, section_id, period_start, end_time, day FROM timetable WHERE id = ? AND 
        institution_id = ? AND branch_id = ? AND type = 'timetable'";
        $stmt_lecture = $conn_rjllearning_specific->prepare($sql_lecture);
        $stmt_lecture->execute([$adjustment['lecture_id'], $institution_id, $branch_id]);
        $lecture_details = $stmt_lecture->fetch(PDO::FETCH_ASSOC);

        $class_info = getClass($conn_rjllearning_specific, $institution_id, $lecture_details['class_id']);
        $section_info = getSection($conn_rjllearning_specific, $institution_id, $lecture_details['section_id']);
        $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $lecture_details['subject_id']);

        if ($lecture_details) {
            $lecture_details = [
                "lecture_id" => $lecture_details['id'],
                "class_info" => $class_info,
                "section_info" => $section_info,
                "subject_info" => $subject_info,
                "period_start" => $lecture_details['period_start'],
                "period_end" => $lecture_details['end_time'],
            ];
        }

        $original_teacher_info = getFacultyInfo($conn_rjllearning_specific, $institution_id, $adjustment['original_teacher_id']);
        $replacement_teacher_info = getFacultyInfo($conn_rjllearning_specific, $institution_id, $adjustment['replacement_teacher_id']);

        $array_return = ([
            'adjustment_id' => $adjustment['id'],
            'institution_id' => $adjustment['institution_id'],
            'branch_id' => $adjustment['branch_id'],
            'original_teacher_info' => $original_teacher_info,
            'replacement_teacher' => $replacement_teacher_info,
            'lecture' => $lecture_details,
            'adjustment_date' => $adjustment['adjustment_date'],
            'created_by' => $adjustment['created_by'],
            'created_at' => $adjustment['created_at'],
            'date_created' => $adjustment['date_created'],
            'last_modified_by' => $adjustment['last_modified_by'],
            'last_modified_at' => $adjustment['last_modified_at'],
            'status' => $adjustment['status'],
        ]);

        return  $array_return;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function fetch_timetable_adjustments($conn_rjllearning_specific, $conn_rjllearning, $institution_id, $branch_id, $search_string, $from_date, $to_date, $limit, $offset)
{

    try {
        $sql_count = "SELECT  count(*) as total_rows FROM timetable_adjustments ta where ta.institution_id = ? AND ta.branch_id = ? AND ta.status = ?";
        $array_params = [$institution_id, $branch_id, 1];

        if ($search_string != null) {
            $sql_count .= " AND (ta.original_teacher_id IN (SELECT id FROM faculty_info WHERE name LIKE ?) 
                              OR ta.replacement_teacher_id IN (SELECT id FROM faculty_info WHERE name LIKE ?))";
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_count .= " AND ta.adjustment_date BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_count);
        $stmt->execute($array_params);
        $result_total = $stmt->fetchAll();
        $totalrows = $result_total[0]['total_rows'];

        if ($limit == 'all') {

            $sql_adjustment = "SELECT * FROM timetable_adjustments ta where ta.institution_id =? AND ta.branch_id = ? AND ta.status =?";
            $array_params = [$institution_id, $branch_id, 1];

            if ($search_string != null) {
                $sql_adjustment .= " AND (ta.original_teacher_id IN (SELECT id FROM faculty_info WHERE name LIKE ?) 
                                        OR ta.replacement_teacher_id IN (SELECT id FROM faculty_info WHERE name LIKE ?))";
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_adjustment .= " AND ta.adjustment_date BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }

            $sql_adjustment .=  "order by ta.date_created desc, ta.created_at desc";
        } else {
            $sql_adjustment = "SELECT * FROM  timetable_adjustments ta where ta.institution_id =? AND ta.branch_id = ? AND ta.status =?";
            $array_params = [$institution_id, $branch_id, 1];

            if ($search_string != null) {
                $sql_adjustment .= " AND (ta.original_teacher_id IN (SELECT id FROM faculty_info WHERE name LIKE ?) 
                                        OR ta.replacement_teacher_id IN (SELECT id FROM faculty_info WHERE name LIKE ?))";
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_adjustment .= " AND ta.adjustment_date BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }
            $sql_adjustment .= " order by ta.date_created desc, ta.created_at desc ";
            $sql_adjustment .= "LIMIT " . $offset . " ," . $limit . "";
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_adjustment);
        $stmt->execute($array_params);
        $session_rows = $stmt->fetchAll();

        $array_return = [];

        foreach ($session_rows as $rows) {

            $institution = getInstitution($conn_rjllearning, $rows['institution_id']);
            $branch = getBranchInfo($conn_rjllearning_specific, $rows['institution_id'], $rows['branch_id']);

            $sql_lecture = "SELECT id, class_id, subject_id, section_id, period_start, end_time, day FROM timetable WHERE id = ? AND 
                            institution_id = ? AND branch_id = ? AND type = 'timetable'";
            $stmt_lecture = $conn_rjllearning_specific->prepare($sql_lecture);
            $stmt_lecture->execute([$rows['lecture_id'], $institution_id, $rows['branch_id']]);
            $lecture_details = $stmt_lecture->fetch(PDO::FETCH_ASSOC);
            
            if (!$lecture_details || !is_array($lecture_details)) {
            // Skip this record or handle the error appropriately
                continue; // or you could set default values
            }

            $class_info = getClass($conn_rjllearning_specific, $institution_id, $lecture_details['class_id']);
            $section_info = getSection($conn_rjllearning_specific, $institution_id, $lecture_details['section_id']);
            $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $lecture_details['subject_id']);

            if ($lecture_details) {
                $lecture_details = [
                    "lecture_id" => $lecture_details['id'],
                    "class_info" => $class_info,
                    "section_info" => $section_info,
                    "subject_info" => $subject_info,
                    "period_start" => $lecture_details['period_start'],
                    "period_end" => $lecture_details['end_time'],
                ];
            }

            $original_teacher_info = getFacultyInfo($conn_rjllearning_specific, $institution_id, $rows['original_teacher_id']);
            $replacement_teacher_info = getFacultyInfo($conn_rjllearning_specific, $institution_id, $rows['replacement_teacher_id']);

            $array_return[] = array(
                'adjustment_id' => $rows['id'],
                'institution' => $institution,
                'branch' => $branch,
                'original_teacher_info' => $original_teacher_info,
                'replacement_teacher_info' => $replacement_teacher_info,
                'lecture_details' => $lecture_details,
                'adjustment_date' => $rows['adjustment_date'],
                'created_by' => $rows['created_by'],
                'created_at' => $rows['created_at'],
                'date_created' => $rows['date_created'],
                'last_modified_by' => $rows['last_modified_by'],
                'last_modified_at' => $rows['last_modified_at'],
                'status' => $rows['status'],
            );
        }

        if (count($session_rows) < 50) {
            return array('total_rows' => $totalrows, 'adjustment_info' => $array_return, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'adjustment_info' => $array_return, 'maxreach' => 0);
        }
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function delete_adjusted_timetable($conn_rjllearning_specific, $institution_id, $branch_id, $adjustment_id) 
{
    try {
        // Get current date and time
        $current_date = date('Y-m-d');
        $current_time = date('H:i:s'); // 24-hour format (HH:MM:SS)

        // Fetch lecture_id and adjustment_date from timetable_adjustments
        $query = "SELECT lecture_id, adjustment_date FROM timetable_adjustments WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute([$adjustment_id, $institution_id, $branch_id]);
        $adjustment_data = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$adjustment_data) {
            errorreturn ("No adjustment record found.");
        }

        $lecture_id = $adjustment_data['lecture_id'];
        $adjustment_date = $adjustment_data['adjustment_date'];

        // Check if adjustment_date is in the past
        if ($adjustment_date < $current_date) {
            errorreturn ("Deletion not allowed adjustment date is in the past.");
        }

        $query = "SELECT period_start, end_time FROM timetable WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute([$lecture_id, $institution_id, $branch_id]);
        $timetable_data = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$timetable_data) {
            errorreturn ("Timetable record not found.");
        }

        $period_start = $timetable_data['period_start'];
        $end_time = $timetable_data['end_time']; 

        // If adjustment date is today, check the time
        if ($adjustment_date == $current_date) {
            if ($current_time >= $period_start) {
                errorreturn ("Deletion not allowed .");
            }
        }

        $delete_query = "DELETE FROM timetable_adjustments WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $delete_stmt = $conn_rjllearning_specific->prepare($delete_query);
        $delete_stmt->execute([$adjustment_id, $institution_id, $branch_id]);

        if ($delete_stmt->rowCount() > 0) {
            successreturn ("Adjustment deleted successfully.");
        } else {
            errorreturn ("No adjustment found or already deleted.");
        }
    } catch (Exception $e) {
        error_log("Error deleting adjusted timetable: " . $e->getMessage());
        errorreturn ("Unable to delete adjustment timetable.");
    }
}

function get_timetable_byDate($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $date) {
    try {
        // Convert the given date to a day number (1 = Monday, ..., 7 = Sunday)
        $day_of_week = date('N', strtotime($date));  

        $sql = "SELECT tt.id AS lecture_id, tt.subject_id, tt.period_start, tt.end_time, 
                       tt.day, tt.type, tt.status, tt.faculty_id
                FROM timetable tt
                INNER JOIN (
                    SELECT class_id, section_id, period_start, MAX(date_created) AS latest_date
                    FROM timetable
                    WHERE institution_id = ? AND branch_id = ? 
                          AND class_id = ? AND section_id = ? 
                          AND day = ? AND status = 1
                    GROUP BY class_id, section_id, period_start
                ) latest_tt 
                ON tt.class_id = latest_tt.class_id 
                AND tt.section_id = latest_tt.section_id 
                AND tt.period_start = latest_tt.period_start
                AND tt.date_created = latest_tt.latest_date
                WHERE tt.institution_id = ? 
                      AND tt.branch_id = ? 
                      AND tt.class_id = ? 
                      AND tt.section_id = ? 
                      AND tt.day = ? 
                      AND tt.status = 1
                ORDER BY tt.period_start ASC";

        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([
            $institution_id, $branch_id, $class_id, $section_id, $day_of_week, 
            $institution_id, $branch_id, $class_id, $section_id, $day_of_week
        ]);

        $timetable_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$timetable_data) {
            errorreturn("No timetable found for the given date.");
        }

        $lecture_details = [];
        foreach ($timetable_data as $timetable) {
            $subject_info = null;
            if (!is_null($timetable['subject_id'])) {
                $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $timetable['subject_id']);
            }
            
            $faculty_info = null;
            if(!is_null($timetable['faculty_id'])){
                $faculty_info = getFacultyInfo($conn_rjllearning_specific, $institution_id, $timetable['faculty_id']);
            }
            $lecture_entry = [
                "lecture_id" => $timetable['lecture_id'],
                "subject" => $subject_info,
                "period_start" => $timetable['period_start'],
                "end_time" => $timetable['end_time'],
                "day" => $timetable['day'],
                "type" => $timetable['type'],
                "status" => $timetable['status'],
                "faculty" => $faculty_info
            ];
            
            // Check if an adjustment exists in timetable_adjustments for this lecture on the given date
            $adjustment_query = "SELECT replacement_teacher_id FROM timetable_adjustments 
                                 WHERE institution_id = ? 
                                   AND branch_id = ? 
                                   AND lecture_id = ? 
                                   AND adjustment_date = ?";
            $adj_stmt = $conn_rjllearning_specific->prepare($adjustment_query);
            $adj_stmt->execute([$institution_id, $branch_id, $timetable['lecture_id'], $date]);
            $adjustment = $adj_stmt->fetch(PDO::FETCH_ASSOC);
            
            $replacement_teacher = null;

            if ($adjustment){
                $replacement_teacher = getFacultyInfo($conn_rjllearning_specific, $institution_id, $adjustment['replacement_teacher_id']);
            }

            if ($replacement_teacher) {
                $lecture_entry["replacement_teacher"] = $replacement_teacher;
            }

            $lecture_details[] = $lecture_entry;
        }

        // Fetch class and section details separately
        $class_info = getClass($conn_rjllearning_specific, $institution_id, $class_id);
        $section_info = getSection($conn_rjllearning_specific, $institution_id, $section_id);

        // Final structured response
        $array_return = [
            "institution_id" => $institution_id,
            "branch_id"      => $branch_id,
            "date"           => $date,
            "class"          => $class_info,
            "section"        => $section_info,
            "timetable"      => $lecture_details
        ];

        return $array_return;

    } catch (Exception $e) {
        errorreturn("Database Error: " . $e->getMessage());
    }
}


