<?php
function CheckAcademicSession($conn_rjllearning_specific, $institution_id)
{
    // this function checks active academic session and also checks if the current date falls in the active academic session
    $today_date = date('Y-m-d');
    $sql = $conn_rjllearning_specific->prepare("SELECT id,name,startdate,enddate FROM academic_session WHERE institution_id = ? and active_academic_session = ?");
    $sql->execute(array($institution_id, 1));
    $result = $sql->fetchAll();
    if (count($result) >= 1) {
        $startdate = $result[0]['startdate'];
        $enddate = $result[0]['enddate'];

        // Check if today falls within startdate and enddate
        if ($today_date >= $startdate && $today_date <= $enddate) {
            // Today's date is within the range
            return $result[0]['id'];
        } else {
            // Today's date is outside the range
            $printdate = explode("-", $enddate);
            $printdate = $printdate[2] . '-' . $printdate[1] . '-' . $printdate[0];
            errorreturn('Current academic session has ended on ' . $printdate);
        }
    } else {
        return false;
    }
}

function compareDatesAcademicSession($conn_rjllearning_specific, $institution_id, $mindate, $maxdate = null, $must_be_active_academic = false)
{
    // here we will check if the dates received belong to one specific academic session, and if they do we return the academic session id
    $sql = "SELECT id, name, startdate, enddate, active_academic_session from academic_session where institution_id = ? and (startdate <= ? and enddate >= ?)";
    $array_params = [$institution_id, $mindate, $mindate];
    if ($maxdate != null) {
        $sql .= 'and (startdate <= ? and enddate >= ?)';
        $array_params[] = $maxdate;
        $array_params[] = $maxdate;
    }
    $fetch_academic_session = $conn_rjllearning_specific->prepare($sql);
    $fetch_academic_session->execute($array_params);
    $result_academic_session = $fetch_academic_session->fetchAll();
    if (count($result_academic_session) != 1) {
        if ($maxdate != null) {
            errorreturn('Date range should exist within a specific academic session.');
        } else {
            errorreturn('Unable to find academic session for selected date.');
        }
    }
    if ($must_be_active_academic == true) {
        // check that the dates be in the active academic session only
        if ($result_academic_session[0]['active_academic_session'] != 1) {
            errorreturn('You cannot insert / update / delete data that does not belong to active academic session.');
        }
    }
    return array(
        'academic_session_id' => $result_academic_session[0]['id'],
        'name' => $result_academic_session[0]['name'],
        'startdate' => $result_academic_session[0]['startdate'],
        'enddate' => $result_academic_session[0]['enddate'],
        'active_session' => $result_academic_session[0]['active_academic_session'] == 1 ? 1 : 0,
    );
}

function  GetAcademicSessionName($conn_rjllearning_specific, $institution_id, $academic_id)
{
    $sql = $conn_rjllearning_specific->prepare("SELECT id, name FROM academic_session WHERE institution_id = ? and id = ?");
    $sql->execute(array($institution_id, $academic_id));
    $result = $sql->fetchAll();
    if (count($result) >= 1) {
        return array('id' => $result[0]['id'], 'name' => $result[0]['name']);
    } else {
        errorreturn("SELECT id, name FROM academic_session WHERE institution_id = ? and id = ?" . "     " . $institution_id . '          ' . $academic_id);
        return false;
    }
}

function getInstitution($conn_rjllearning, $institution_id)
{
    $res = $conn_rjllearning->prepare("SELECT id,institution_name,logo,institution_code,white_label_url,white_label_email,white_label_message_header,white_label_app_name FROM institutions WHERE id = ?");
    $res->execute(array($institution_id));
    $result = $res->fetchAll();

    if (count($result) > 0) {
        if ($result[0]['logo'] != null) {
            $data_attachment = json_decode($result[0]['logo'], true);
            $filePath = $data_attachment['filepath'];
            $path = isset($data_attachment['path']) ? $data_attachment['path'] : null; // This can be null
            $originalName = $data_attachment['original_name'];
            $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath , 1 ,$GLOBALS['cloudFrontDomain']);
            // $aws_attachment = $filePath;  
       
        } else {
            $aws_attachment = null;
            $path = null; // If logo doesn't exist, path should be null
        }
        
        if($result[0]['white_label_url'] !=  null){
            $white_label_url = $result[0]['white_label_url'];
        }else{
            $white_label_url =  null;
        }
        
        if($result[0]['white_label_email'] !=  null){
            $white_label_email = $result[0]['white_label_email'];
        }else{
            $white_label_email =  null;
        }
        if($result[0]['white_label_message_header'] !=  null){
            $white_label_message_header = $result[0]['white_label_message_header'];
        }else{
            $white_label_message_header =  null;
        }
        
        if($result[0]['white_label_app_name'] !=  null){
            $white_label_app_name = $result[0]['white_label_app_name'];
        }else{
            $white_label_app_name =  null;
        }

        $institution_info = array(
            "institution_id" => $result[0]['id'],
            "institution_name" => $result[0]['institution_name'],
            "institution_code" => $result[0]['institution_code'],
            "white_label_url" => $white_label_url,
            "white_label_email" => $white_label_email,
            "white_label_message_header" => $white_label_message_header,
            "white_label_app_name" => $white_label_app_name,
            "logo" => $aws_attachment,
            "logo_path" => $path // Include path separately here
        );
    }

    return $institution_info;
}

$class_global_id_array = [];
$class_global_detail_array = [];

function getClass($conn_rjllearning_specific, $institution_id, $class_id, $lazy_fetch = false)
{
    if ($lazy_fetch === true) {
        $index = array_search($class_id, $GLOBALS['class_global_id_array']);
        if ($index !== false) {
            return $GLOBALS['class_global_detail_array'][$index];
        }
    }

    $res = $conn_rjllearning_specific->prepare("SELECT id, classname FROM class_info WHERE id = ? AND institution_id = ?");
    $res->execute([$class_id, $institution_id]);
    $result = $res->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        $class_info = [
            "class_id" => $result['id'],
            "class_name" => $result['classname']
        ];
        $GLOBALS['class_global_id_array'][] = $result['id'];
        $GLOBALS['class_global_detail_array'][] = $class_info;
        return $class_info;
    } else {
        errorreturn("Class not found.");
    }
}


$subject_global_id_array = [];
$subject_global_detail_array = [];

function getSubject($conn_rjllearning_specific, $institution_id, $subject_id, $lazy_fetch = false)
{
    if ($lazy_fetch === true) {
        $index = array_search($subject_id, $GLOBALS['subject_global_id_array']);
        if ($index !== false) {
            return $GLOBALS['subject_global_detail_array'][$index];
        }
    }

    $res = $conn_rjllearning_specific->prepare("SELECT id, subject_name FROM subject_info WHERE id = ? AND institution_id = ?");
    $res->execute([$subject_id, $institution_id]);
    $result = $res->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        $subject_info = [
            "subject_id" => $result['id'],
            "subject_name" => $result['subject_name']
        ];
        $GLOBALS['subject_global_id_array'][] = $result['id'];
        $GLOBALS['subject_global_detail_array'][] = $subject_info;
        return $subject_info;
    } else {
        errorreturn("Subject not found.");
    }
}


$section_global_id_array = [];
$section_global_detail_array = [];

function getSection($conn_rjllearning_specific, $institution_id, $section_id, $lazy_fetch = false)
{
    if ($lazy_fetch === true) {
        $index = array_search($section_id, $GLOBALS['section_global_id_array']);
        if ($index !== false) {
            return $GLOBALS['section_global_detail_array'][$index];
        }
    }

    $res = $conn_rjllearning_specific->prepare("SELECT id, section_name FROM section_info WHERE id = ? AND institution_id = ?");
    $res->execute([$section_id, $institution_id]);
    $result = $res->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        $section_info = [
            "section_id" => $result['id'],
            "section_name" => $result['section_name']
        ];
        $GLOBALS['section_global_id_array'][] = $result['id'];
        $GLOBALS['section_global_detail_array'][] = $section_info;
        return $section_info;
    } else {
        errorreturn("Section not found.");
    }
}

function getFacultyInfo($conn_rjllearning_specific, $institution_id, $faculty_id)
{

    $res = $conn_rjllearning_specific->prepare("SELECT id,name FROM  faculty_info WHERE id = ? and institution_id=? ");
    $res->execute(array($faculty_id, $institution_id));
    $result_faculty = $res->fetchAll();
    if (count($result_faculty) > 0) {
        $section_info =  array("faculty_id" => $result_faculty[0]['id'], "name" => $result_faculty[0]['name']);
    } else {
        errorreturn("Faculty not found.");
    }
    return $section_info;
}


$branch_global_id_array = [];
$branch_global_detail_array = [];

function getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id, $additional_data = null, $lazy_fetch = false)
{
    if ($lazy_fetch === true) {
        $index = array_search($branch_id, $GLOBALS['branch_global_id_array']);
        if ($index !== false) {
            return $GLOBALS['branch_global_detail_array'][$index];
        }
    }

    $default_columns = ['id', 'name'];
    $columns = $additional_data !== null ? array_merge($default_columns, $additional_data) : $default_columns;

    $sql = "SELECT " . implode(", ", $columns) . " FROM branch WHERE id = ? AND institution_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$branch_id, $institution_id]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        $branch_info = [];
        foreach ($columns as $column_name) {
            if ($column_name === 'id') {
                $branch_info['branch_id'] = $result[$column_name];
            } else {
                $branch_info[$column_name] = $result[$column_name];
            }
        }
        $GLOBALS['branch_global_id_array'][] = $branch_id;
        $GLOBALS['branch_global_detail_array'][] = $branch_info;
        return $branch_info;
    } else {
        errorreturn("Branch does not exist.");
    }
}

function getBranchWorkspaceConnection($conn_rjllearning_specific, $branch_id, $institution_id)
{
    // Query to check if the branch exists with specific IDs
    $sqlbranch = "SELECT * FROM branch WHERE id = ? AND institution_id = ?";
    $stmtBranch = $conn_rjllearning_specific->prepare($sqlbranch);
    $stmtBranch->execute(array($branch_id, $institution_id));
    $resultBranch = $stmtBranch->fetchAll();

    // If the branch is not found
    if (count($resultBranch) != 1) {
        errorreturn('Branch not found.');
    }

    $branch_name =  $resultBranch[0]['name'];
    // // Fetch branch info
    $branch_info = array(
        "rejoice_solutions_company_id" => $resultBranch[0]['rejoice_solutions_company_id'],
        "rejoice_solutions_branch_id" => $resultBranch[0]['rejoice_solutions_branch_id'],
        "rejoice_finware_book_id" => $resultBranch[0]['rejoice_finware_book_id']
    );
    // print_R($branch_info);die();
    // Check if any of the critical fields are null
    foreach ($branch_info as $key => $value) {
        if (is_null($value)) {
            errorreturn('Unable to connect to the Workspace for branch: ' . $branch_name);
        }
    }

    return $branch_info; // Return the branch info if no null values are found
}


function checkSectionInstitutionBranch($conn_rjllearning_specific, $section_id, $institution_id, $branch_id)
{
    if ($section_id != 'all') {
        if (is_array($section_id)) {
            if (count($section_id) <= 0) {
                errorreturn("Select at least one section.");
            }

            // Handling multiple section IDs
            $imploded_section_ids = "('" . implode("','", $section_id) . "')";
            $count_ids = count($section_id);

            // Query for multiple section IDs
            $queryExecute = "SELECT COUNT(*) as total 
                         FROM section_info 
                         WHERE id IN $imploded_section_ids 
                           AND institution_id = ? 
                           AND branch_id LIKE ?";
            $stmt_section = $conn_rjllearning_specific->prepare($queryExecute);
            $stmt_section->execute([$institution_id, '%' . $branch_id . '%']);
        } else {
            // Handling a single section ID
            $query = "SELECT COUNT(*) as total 
                  FROM section_info 
                  WHERE id = ? 
                    AND institution_id = ? 
                    AND branch_id LIKE ?";
            $stmt_section = $conn_rjllearning_specific->prepare($query);
            $stmt_section->execute([$section_id, $institution_id, '%' . $branch_id . '%']);
            $count_ids = 1; // Since it's a single section ID
        }

        // Fetch result and validate
        $result_section = $stmt_section->fetch()['total'];
        if ($result_section != $count_ids) {
            errorreturn("Section Not Found.");
        }

        // Prepare section IDs for return
        $imploded_sectionids = is_array($section_id)
            ? "'" . implode("','", $section_id) . "'"
            : $section_id;
    } else {
        // Handle 'all' case
        $imploded_sectionids = $section_id;
    }

    return $imploded_sectionids;
}

function checkClassInstitutionBranch($conn_rjllearning_specific, $institution_id, $branch_id, $class_id)
{

    // $sql = $conn_rjllearning_specific->prepare("SELECT id FROM class_info WHERE id = ? AND institution_id =? AND branch_id LIKE ?");
    // $sql->execute(array($class_id, $institution_id,'%' . $branch_id . '%'));
    // $result = $sql->fetchAll();
    // if (count($result) != 1) {
    //     errorreturn("Class not found.");
    // }

    // return true;

    if ($class_id != 'all') {
        if (is_array($class_id)) {
            if (count($class_id) <= 0) {
                errorreturn("Select at least one section.");
            }

            // Handling multiple section IDs
            $imploded_class_ids = "('" . implode("','", $class_id) . "')";
            $count_ids = count($class_id);

            // Query for multiple section IDs
            $queryExecute = "SELECT COUNT(*) as total 
                         FROM class_info 
                         WHERE id IN $imploded_class_ids 
                           AND institution_id = ? 
                           AND branch_id LIKE ?";
            $stmt_section = $conn_rjllearning_specific->prepare($queryExecute);
            $stmt_section->execute([$institution_id, '%' . $branch_id . '%']);
        } else {
            // Handling a single section ID
            $query = "SELECT COUNT(*) as total 
                  FROM class_info 
                  WHERE id = ? 
                    AND institution_id = ? 
                    AND branch_id LIKE ?";
            $stmt_section = $conn_rjllearning_specific->prepare($query);
            $stmt_section->execute([$class_id, $institution_id, '%' . $branch_id . '%']);
            $count_ids = 1; // Since it's a single section ID
        }

        // Fetch result and validate
        $result_section = $stmt_section->fetch()['total'];
        if ($result_section != $count_ids) {
            errorreturn("CLass Not Found.");
        }

        // Prepare section IDs for return
        $imploded_class_ids = is_array($class_id)
            ? "'" . implode("','", $class_id) . "'"
            : $class_id;
    } else {
        // Handle 'all' case
        $imploded_class_ids = $class_id;
    }

    return $imploded_class_ids;
}

// function getSectionInstitutionBranch($conn_rjllearning_specific,$institution_id,$branch_id,$section_id){

//         $section = []; // Initialize branches array
//         // print_R($section_id);die();

//             // $imploded_branchids = implode(',', $branch_id); // Convert array to comma-separated string
//             // print_R($imploded_branchids);
//             $sql = "SELECT * FROM section_info WHERE institution_id = ? AND branch_id Like ? AND id IN ($section_id) ";

//             $array_params = [$institution_id, '%' . $branch_id . '%']; // Add institution_id to parameters

//             $stmt = $conn_rjllearning_specific->prepare($sql);
//             $stmt->execute($array_params);
//             $section_rows = $stmt->fetchAll();

//             foreach ($section_rows as $section_data) {
//                 $section[] = array('section_id' => $section_data['id'], 'section_name' => $section_data['section_name']);
//             }


//         return $section;

// }
function getSectionInstitutionBranch($conn_rjllearning_specific, $institution_id, $branch_id, $section_id)
{
    // Debugging the section_id


    $section = []; // Initialize the array for the sections

    // Convert string representation of an array to an actual array if needed
    if (is_string($section_id)) {
        $section_id = json_decode($section_id, true);
    }

    // SQL base query
    $sql = "SELECT * FROM section_info WHERE institution_id = ? AND branch_id LIKE ? or branch_id = 'all'";

    // Set the parameters for institution_id and branch_id
    $array_params = [$institution_id, '%' . $branch_id . '%'];

    // Check if section_id is provided and handle it
    if ($section_id !== null) {
        // Check if section_id is indeed an array
        if (is_array($section_id)) {

            // Format section_id values for the IN clause (ensure integers for security)
            $imploded_section_ids = implode(",", array_map('intval', $section_id));
            $sql .= " AND id IN ($imploded_section_ids)";
        } else {

            $sql .= " AND id = ?";
            $array_params[] = $section_id; // Add the single section_id to the parameters
        }
    }
    // Execute the SQL query
    try {
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute($array_params);

        // Fetch the results
        $section_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Populate the section array
        foreach ($section_rows as $section_data) {
            $section[] = array('section_id' => $section_data['id'], 'section_name' => $section_data['section_name']);
        }

        // Return the section data
        return $section;
    } catch (PDOException $e) {
        // Handle database errors
        errorreturn('Database error: ' . $e->getMessage());
    }
}

// faculy additional module given to them 

// function getClassSectionSubjectsAndInChargeRoles($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $userid)
// {
//     try {
//         // Step 1: Fetch Faculty ID using userid from staff_access_support
//         $faculty_query = "SELECT id,branch_id FROM  faculty_info WHERE userid = ? AND institution_id = ?";
//         $faculty_stmt = $conn_rjllearning_specific->prepare($faculty_query);
//         $faculty_stmt->execute([$userid, $institution_id]);
//         $faculty = $faculty_stmt->fetch(PDO::FETCH_ASSOC);

//         if (!$faculty) {
//             return errorreturn("Faculty not found.");
//         }

//         $faculty_id = $faculty['id'];
//         $branch_id = $faculty['branch_id'];

//         $global_setting_query = "SELECT attendance_system, faculty_attendance_setting, otp_setting
//                     FROM institution_global_settings 
//                     WHERE institution_id = ? AND branch_id = ?";
//         $global_setting_stmt = $conn_rjllearning_specific->prepare($global_setting_query);
//         $global_setting_stmt->execute([$institution_id, $branch_id]);
//         $global_setting_data = $global_setting_stmt->fetch(PDO::FETCH_ASSOC);

//         // Decode the JSON settings
//         $faculty_attendance_setting = isset($global_setting_data['faculty_attendance_setting']) ? json_decode($global_setting_data['faculty_attendance_setting'], true) : [];
//         $otp_setting = isset($global_setting_data['otp_setting']) ? json_decode($global_setting_data['otp_setting'], true) : [];

//         // Step 2: Fetch timetable data where faculty_id is matched
//         $timetable_query = "SELECT class_id, section_id, subject_id 
//                     FROM timetable 
//                     WHERE faculty_id = ? AND institution_id = ? and branch_id=? and type IN ('timetable', 'faculty_role') ";
//         $timetable_stmt = $conn_rjllearning_specific->prepare($timetable_query);
//         $timetable_stmt->execute([$faculty_id, $institution_id, $branch_id]);
//         $timetable_data = $timetable_stmt->fetchAll();

//         $class_section_subjects = []; // Initialize the main array to store class-section-subject combinations

//         foreach ($timetable_data as $row) {
//             $class_id = $row['class_id'];
//             $section_id = $row['section_id'];
//             // $subject_id = $row['subject_id'];
//             $subject_id = (isset($row['subject_id']) && !empty($row['subject_id'])) ? $row['subject_id'] : null;

//             // Fetch class, section, and subject information
//             $class_info = getClass($conn_rjllearning_specific, $institution_id, $class_id); // Fetch class details
//             $section_info = getSection($conn_rjllearning_specific, $institution_id, $section_id); // Fetch section details
//             $subject_info = null;
//             if ($subject_id != null) {
//                 $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $subject_id); // Fetch subject details
//             }

//             // Create a unique key for the class-section combination
//             $key = "{$class_id}-{$section_id}";

//             // Check if the combination already exists in the results
//             if (!isset($class_section_subjects[$key])) {
//                 $class_section_subjects[$key] = [
//                     "class" => $class_info,
//                     "section" => $section_info,
//                     "subjects" => [] // Initialize subjects as an empty array
//                 ];
//             }

//             // Add the subject details to the subjects array if not already present
//             if ($subject_info !== null) {
//                 $subject_ids = array_column($class_section_subjects[$key]['subjects'], 'subject_id');
//                 if (!in_array($subject_id, $subject_ids)) {
//                     $class_section_subjects[$key]['subjects'][] = $subject_info;
//                 }
//             }
//         }

//         // Convert associative array to indexed array
//         $class_section_subjects = array_values($class_section_subjects);
//         // var_dump($class_section_subjects);
//         // die();
//         // Step 4: Fetch class in-charge roles
//         $incharge_query = "SELECT class_id, section_id 
//                           FROM timetable 
//                           WHERE faculty_id = ? AND institution_id = ? and branch_id=? and type='class_incharge' ";
//         $incharge_stmt = $conn_rjllearning_specific->prepare($incharge_query);
//         $incharge_stmt->execute([$faculty_id, $institution_id, $branch_id]);
//         $incharge_data = $incharge_stmt->fetchAll(PDO::FETCH_ASSOC);

//         // Step 5: Organize the in-charge data into the specified JSON structure
//         $class_incharge_roles = [];

//         foreach ($incharge_data as $row) {
//             $class_id = $row['class_id'];
//             $section_id = $row['section_id'];

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

//             $class_incharge_roles[] = [
//                 "class" => [
//                     "class_id" => $class_id,
//                     "class_name" => $class_info['class_name'] ?? 'Unknown'
//                 ],
//                 "section" => [
//                     "section_id" => $section_id,
//                     "section_name" => $section_info['section_name'] ?? 'Unknown'
//                 ]
//             ];
//         }

//         // Step 6: Fetch modules granted to the faculty
//         $modules_query = "SELECT id, class_sections, function_code
//                   FROM staff_access_support 
//                   WHERE faculty_id = ? AND institution_id = ? and branch_id=?";
//         $modules_stmt = $conn_rjllearning_specific->prepare($modules_query);
//         $modules_stmt->execute([$faculty_id, $institution_id, $branch_id]);
//         $modules_data = $modules_stmt->fetchAll(PDO::FETCH_ASSOC);

//         // Prepare to organize the modules data
//         $staff_access_modules = [];

//         // Loop through each module
//         foreach ($modules_data as $module) {
//             // Decode class_sections JSON
//             $class_sections = json_decode($module['class_sections'], true);

//             // Prepare an array for class_section_modules
//             $class_section_modules = [];

//             // Loop through class_sections to fetch class and section names
//             foreach ($class_sections as $class_section) {
//                 $class_id = $class_section['class_id'];
//                 $section_ids = $class_section['section_id']; // This may be an array or "all"

//                 // Fetch class name using the getClass function
//                 $class_info = [];
//                 // Fetch class name using the getClass function
//                 if ($class_id === "all") {
//                     $class_info = [
//                         'class_id' => 'all',
//                         'class_name' => ucwords('all')
//                     ];
//                 } else {
//                     $class_info = getClass($conn_rjllearning_specific, $institution_id, $class_id);
//                 }
//                 // Prepare sections array to hold multiple sections or "all"
//                 $sections = [];

//                 // Check if section_ids contain "all"
//                 if (in_array("all", $section_ids)) {
//                     // Handle the "all" case
//                     $sections[] = [
//                         "section_id" => "all",
//                         "section_name" => "All" // Change this to the appropriate section name if necessary
//                     ];
//                 } else {
//                     // Fetch names for each section_id
//                     foreach ($section_ids as $section_id) {
//                         // Fetch section name using the getSection function
//                         $section_info = getSection($conn_rjllearning_specific, $institution_id, $section_id);

//                         // Add section info to sections array if valid
//                         if ($section_info) {
//                             $sections[] = $section_info; // Assuming getSection returns an associative array with section info
//                         }
//                     }
//                 }

//                 // Only add to class_section_modules if class_info is valid
//                 if ($class_info) {
//                     $class_section_modules[] = [ // Changed to append to array instead of overwrite
//                         "class" => $class_info,
//                         "section" => $sections // Include all sections or "all" for this class
//                     ];
//                 }
//             }

//             // Now handle the function codes
//             $function_code_ids = json_decode($module['function_code'], true);

//             // Prepare to fetch function details for all IDs
//             if (is_array($function_code_ids) && !empty($function_code_ids)) {
//                 // Create a string of placeholders for the query
//                 $placeholders = implode(',', array_fill(0, count($function_code_ids), '?'));

//                 // Fetch function details for the current function_code
//                 $function_query = "SELECT id, function_label, code, group_label 
//                           FROM function_codes 
//                           WHERE id IN ($placeholders)";
//                 $function_stmt = $conn_rjllearning->prepare($function_query);
//                 $function_stmt->execute($function_code_ids);
//                 $function_data_array = $function_stmt->fetchAll(PDO::FETCH_ASSOC);

//                 // Prepare modules_granted with multiple function codes
//                 $modules_granted = [];
//                 foreach ($function_data_array as $function_data) {
//                     $modules_granted[] = [
//                         "function_code_id" => $function_data['id'] ?? null,
//                         "function_label" => $function_data['function_label'] ?? null,
//                         "function_code" => $function_data['code'] ?? null,
//                         "group_label" => $function_data['group_label'] ?? null
//                     ];
//                 }
//             } else {
//                 $modules_granted = []; // No valid function codes found
//             }

//             // Organize the modules data
//             $staff_access_modules = [ // Changed to append to array instead of overwrite
//                 "class_section_modules" => $class_section_modules,
//                 "modules_granted" => $modules_granted
//             ];
//         }

//         // You can now use $staff_access_modules for further processing


//         // var_dump($class_section_subjects);die();
//         // Step 8: Structure the final output
//         $output = [
//             "class_section_subjects" => $class_section_subjects,
//             "class_incharge_roles" => $class_incharge_roles,
//             "staff_access_modules" => $staff_access_modules,
//             "global_settings" => [
//                 "attendance_system" => $global_setting_data['attendance_system'] ?? null,
//                 "faculty_attendance_setting" => $faculty_attendance_setting,
//                 "otp_setting" => $otp_setting
//             ]
//         ];

//         // Return the structured output
//         return $output;
//     } catch (PDOException $e) {
//         return errorreturn('Database error: ' . $e->getMessage());
//     } catch (Exception $e) {
//         return errorreturn('Error: ' . $e->getMessage());
//     }
// }

function getClassSectionSubjectsAndInChargeRoles($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $userid)
{
    try {
        // Step 1: Fetch Faculty ID using userid from staff_access_support
        $faculty_query = "SELECT id,branch_id FROM faculty_info WHERE userid = ? AND institution_id = ?";
        $faculty_stmt = $conn_rjllearning_specific->prepare($faculty_query);
        $faculty_stmt->execute([$userid, $institution_id]);
        $faculty = $faculty_stmt->fetch(PDO::FETCH_ASSOC);

        if (!$faculty) {
            return errorreturn("Faculty not found.");
        }

        $faculty_id = $faculty['id'];
        $branch_id = $faculty['branch_id'];

        $global_setting_query = "SELECT attendance_system, faculty_attendance_setting, otp_setting
                    FROM institution_global_settings 
                    WHERE institution_id = ? AND branch_id = ?";
        $global_setting_stmt = $conn_rjllearning_specific->prepare($global_setting_query);
        $global_setting_stmt->execute([$institution_id, $branch_id]);
        $global_setting_data = $global_setting_stmt->fetch(PDO::FETCH_ASSOC);

        // Decode the JSON settings
        $faculty_attendance_setting = isset($global_setting_data['faculty_attendance_setting']) ? json_decode($global_setting_data['faculty_attendance_setting'], true) : [];
        $otp_setting = isset($global_setting_data['otp_setting']) ? json_decode($global_setting_data['otp_setting'], true) : [];

        // Step 2: Fetch timetable data where faculty_id is matched
        $timetable_query = "SELECT class_id, section_id, subject_id 
                    FROM timetable 
                    WHERE faculty_id = ? AND institution_id = ? and branch_id=? and type IN ('timetable', 'faculty_role') ";
        $timetable_stmt = $conn_rjllearning_specific->prepare($timetable_query);
        $timetable_stmt->execute([$faculty_id, $institution_id, $branch_id]);
        $timetable_data = $timetable_stmt->fetchAll();

        $class_section_subjects = []; // Initialize the main array to store class-section-subject combinations

        foreach ($timetable_data as $row) {
            $class_id = $row['class_id'];
            $section_id = $row['section_id'];
            $subject_id = (isset($row['subject_id']) && !empty($row['subject_id'])) ? $row['subject_id'] : null;

            // Fetch class, section, and subject information
            $class_info = getClass($conn_rjllearning_specific, $institution_id, $class_id);
            $section_info = getSection($conn_rjllearning_specific, $institution_id, $section_id);
            $subject_info = null;
            if ($subject_id != null) {
                $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $subject_id);
            }

            // Create a unique key for the class-section combination
            $key = "{$class_id}-{$section_id}";

            // Check if the combination already exists in the results
            if (!isset($class_section_subjects[$key])) {
                $class_section_subjects[$key] = [
                    "class" => $class_info,
                    "section" => $section_info,
                    "subjects" => []
                ];
            }

            // Add the subject details to the subjects array if not already present
            if ($subject_info !== null) {
                $subject_ids = array_column($class_section_subjects[$key]['subjects'], 'subject_id');
                if (!in_array($subject_id, $subject_ids)) {
                    $class_section_subjects[$key]['subjects'][] = $subject_info;
                }
            }
        }

        // Convert associative array to indexed array
        $class_section_subjects = array_values($class_section_subjects);

        // Step 4: Fetch class in-charge roles
        $incharge_query = "SELECT class_id, section_id 
                           FROM timetable 
                           WHERE faculty_id = ? AND institution_id = ? and branch_id=? and type='class_incharge' ";
        $incharge_stmt = $conn_rjllearning_specific->prepare($incharge_query);
        $incharge_stmt->execute([$faculty_id, $institution_id, $branch_id]);
        $incharge_data = $incharge_stmt->fetchAll(PDO::FETCH_ASSOC);

        // Step 5: Organize the in-charge data into the specified JSON structure
        $class_incharge_roles = [];

        foreach ($incharge_data as $row) {
            $class_id = $row['class_id'];
            $section_id = $row['section_id'];

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

            $class_incharge_roles[] = [
                "class" => [
                    "class_id" => $class_id,
                    "class_name" => $class_info['class_name'] ?? 'Unknown'
                ],
                "section" => [
                    "section_id" => $section_id,
                    "section_name" => $section_info['section_name'] ?? 'Unknown'
                ]
            ];
        }

        // Step 6: Fetch modules granted to the faculty
        $modules_query = "SELECT id, class_sections, function_code
                  FROM staff_access_support 
                  WHERE faculty_id = ? AND institution_id = ? and branch_id=?";
        $modules_stmt = $conn_rjllearning_specific->prepare($modules_query);
        $modules_stmt->execute([$faculty_id, $institution_id, $branch_id]);
        $modules_data = $modules_stmt->fetchAll(PDO::FETCH_ASSOC);

        // Prepare to organize the modules data
        $staff_access_modules = [];

        // Loop through each module
        foreach ($modules_data as $module) {
            // Decode class_sections JSON
            $class_sections = json_decode($module['class_sections'], true);

            // Prepare an array for class_section_modules
            $class_section_modules = [];

            // Loop through class_sections to fetch class and section names
            foreach ($class_sections as $class_section) {
                $class_id = $class_section['class_id'];
                $section_ids = $class_section['section_id']; // This may be an array or "all"

                // Handle case when both class and section are "all"
                if ($class_id === "all" && in_array("all", $section_ids)) {
                    // Fetch all classes for this institution and branch
                    $all_classes_query = "SELECT id as class_id, classname as class_name FROM class_info WHERE institution_id = ? AND branch_id = ? OR branch_id = 'all'";
                    $all_classes_stmt = $conn_rjllearning_specific->prepare($all_classes_query);
                    $all_classes_stmt->execute([$institution_id, $branch_id]);
                    $all_classes = $all_classes_stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Fetch all sections for this institution and branch
                    $all_sections_query = "SELECT id as section_id, section_name FROM section_info WHERE institution_id = ? AND branch_id = ? OR branch_id = 'all'";
                    $all_sections_stmt = $conn_rjllearning_specific->prepare($all_sections_query);
                    $all_sections_stmt->execute([$institution_id, $branch_id]);
                    $all_sections = $all_sections_stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Create entries for all class-section combinations
                    foreach ($all_classes as $class) {
                        foreach ($all_sections as $section) {
                            $class_section_modules[] = [
                                "class" => [
                                    "class_id" => $class['class_id'],
                                    "class_name" => $class['class_name']
                                ],
                                "section" => [
                                    [
                                        "section_id" => $section['section_id'],
                                        "section_name" => $section['section_name']
                                    ]
                                ]
                            ];
                        }
                    }
                }
                // Handle case when only class is "all"
                elseif ($class_id === "all") {
                    // Fetch all classes for this institution and branch
                    $all_classes_query = "SELECT id as class_id, classname as class_name FROM class_info WHERE institution_id = ? AND branch_id = ? OR branch_id = 'all'";
                    $all_classes_stmt = $conn_rjllearning_specific->prepare($all_classes_query);
                    $all_classes_stmt->execute([$institution_id, $branch_id]);
                    $all_classes = $all_classes_stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Fetch sections for each section_id
                    $sections = [];
                    foreach ($section_ids as $section_id) {
                        if ($section_id === "all") {
                            // If section is also "all", fetch all sections
                            $all_sections_query = "SELECT id as section_id, section_name FROM section_info WHERE institution_id = ? AND branch_id = ? OR branch_id = 'all'";
                            $all_sections_stmt = $conn_rjllearning_specific->prepare($all_sections_query);
                            $all_sections_stmt->execute([$institution_id, $branch_id]);
                            $sections = $all_sections_stmt->fetchAll(PDO::FETCH_ASSOC);
                            break;
                        } else {
                            $section_info = getSection($conn_rjllearning_specific, $institution_id, $section_id);
                            if ($section_info) {
                                $sections[] = $section_info;
                            }
                        }
                    }

                    // Create entries for all classes with the specified sections
                    foreach ($all_classes as $class) {
                        $class_section_modules[] = [
                            "class" => [
                                "class_id" => $class['class_id'],
                                "class_name" => $class['class_name']
                            ],
                            "section" => $sections
                        ];
                    }
                }
                // Handle case when only section is "all"
                elseif (in_array("all", $section_ids)) {
                    // Fetch the specific class
                    $class_info = getClass($conn_rjllearning_specific, $institution_id, $class_id);

                    // Fetch all sections for this institution and branch
                    $all_sections_query = "SELECT id as section_id, section_name FROM section_info WHERE institution_id = ? AND branch_id = ? OR branch_id = 'all'";
                    $all_sections_stmt = $conn_rjllearning_specific->prepare($all_sections_query);
                    $all_sections_stmt->execute([$institution_id, $branch_id]);
                    $sections = $all_sections_stmt->fetchAll(PDO::FETCH_ASSOC);

                    $class_section_modules[] = [
                        "class" => $class_info,
                        "section" => $sections
                    ];
                }
                // Normal case - specific class and sections
                else {
                    $class_info = [];
                    if ($class_id === "all") {
                        $class_info = [
                            'class_id' => 'all',
                            'class_name' => ucwords('all')
                        ];
                    } else {
                        $class_info = getClass($conn_rjllearning_specific, $institution_id, $class_id);
                    }

                    $sections = [];
                    foreach ($section_ids as $section_id) {
                        if ($section_id === "all") {
                            $sections[] = [
                                "section_id" => "all",
                                "section_name" => "All"
                            ];
                        } else {
                            $section_info = getSection($conn_rjllearning_specific, $institution_id, $section_id);
                            if ($section_info) {
                                $sections[] = $section_info;
                            }
                        }
                    }

                    if ($class_info) {
                        $class_section_modules[] = [
                            "class" => $class_info,
                            "section" => $sections
                        ];
                    }
                }
            }

            // Now handle the function codes
            $function_code_ids = json_decode($module['function_code'], true);

            // Prepare to fetch function details for all IDs
            if (is_array($function_code_ids) && !empty($function_code_ids)) {
                // Create a string of placeholders for the query
                $placeholders = implode(',', array_fill(0, count($function_code_ids), '?'));

                // Fetch function details for the current function_code
                $function_query = "SELECT id, function_label, code, group_label 
                           FROM function_codes 
                           WHERE id IN ($placeholders)";
                $function_stmt = $conn_rjllearning->prepare($function_query);
                $function_stmt->execute($function_code_ids);
                $function_data_array = $function_stmt->fetchAll(PDO::FETCH_ASSOC);

                // Prepare modules_granted with multiple function codes
                $modules_granted = [];
                foreach ($function_data_array as $function_data) {
                    $modules_granted[] = [
                        "function_code_id" => $function_data['id'] ?? null,
                        "function_label" => $function_data['function_label'] ?? null,
                        "function_code" => $function_data['code'] ?? null,
                        "group_label" => $function_data['group_label'] ?? null
                    ];
                }
            } else {
                $modules_granted = []; // No valid function codes found
            }

            // Organize the modules data
            $staff_access_modules = [
                "class_section_modules" => $class_section_modules,
                "modules_granted" => $modules_granted
            ];
        }

        // Step 8: Structure the final output
        $output = [
            "class_section_subjects" => $class_section_subjects,
            "class_incharge_roles" => $class_incharge_roles,
            "staff_access_modules" => $staff_access_modules,
            "global_settings" => [
                "attendance_system" => $global_setting_data['attendance_system'] ?? null,
                "faculty_attendance_setting" => $faculty_attendance_setting,
                "otp_setting" => $otp_setting
            ]
        ];

        // Return the structured output
        return $output;
    } catch (PDOException $e) {
        return errorreturn('Database error: ' . $e->getMessage());
    } catch (Exception $e) {
        return errorreturn('Error: ' . $e->getMessage());
    }
}

// below function to be called only in the case of cud functions
function validateBranchClassSection($specific_db_conn, $institution_id, $branch_input, $class_input, $section_input, $support_branch_all = 0, $checkClass = 0, $support_class_all = 0, $checkSection = 1,  $support_section_all = 0)
{
    if (is_array($branch_input) and in_array('all', $branch_input)) {
        if ($support_branch_all == 0) {
            errorreturn("You cannot select All as branch.");
        }
        if (count($branch_input) > 1) {
            errorreturn('You can either select All or select multiple branches.');
        }
        $sql_get_all_branch_id = "SELECT id from branch where institution_id = ?";
        $get_all_branch_id = $specific_db_conn->prepare($sql_get_all_branch_id);
        $get_all_branch_id->execute(array($institution_id));
        $result_branch_id = $get_all_branch_id->fetchAll(PDO::FETCH_ASSOC);

        $all_branch_id_array = array_column($result_branch_id, 'id');
    } else if ($branch_input == 'all') {
        if ($support_branch_all == 0) {
            errorreturn("You cannot select All as branch.");
        }

        $sql_get_all_branch_id = "SELECT id from branch where institution_id = ?";
        $get_all_branch_id = $specific_db_conn->prepare($sql_get_all_branch_id);
        $get_all_branch_id->execute(array($institution_id));
        $result_branch_id = $get_all_branch_id->fetchAll(PDO::FETCH_ASSOC);

        $all_branch_id_array = array_column($result_branch_id, 'id');
    }
    if ($checkClass == 1) {
        if (is_array($class_input) and in_array('all', $class_input)) {
            if ($support_class_all == 0) {
                errorreturn("You cannot select All as class.");
            }
            if (count($class_input) > 1) {
                errorreturn('You can either select All or select multiple classes.');
            }
        } else if ($class_input == 'all') {
            if ($support_class_all == 0) {
                errorreturn("You cannot select All as class.");
            }
        }
    }

    if ($checkSection == 1) {
        if (is_array($section_input) and in_array('all', $section_input)) {
            if ($support_section_all == 0) {
                errorreturn("You cannot select All as section.");
            }
            if (count($section_input) > 1) {
                errorreturn('You can either select All or select multiple sections.');
            }
        } else if ($section_input == 'all') {
            if ($support_section_all == 0) {
                errorreturn("You cannot select All as section.");
            }
        }
    }


    // check support branch for all, if allowed only then branch input either array or specific can have all as a value.
    // check support class for all, if allowed only then class input either array or specific can have all as a value.

    // if branch input is array
    // if it contains all it can only br all else should be specific element array of branch ids
    // if it is all, any class / classes thorugh array can be selected that is common for all, check through intersection, only those classes will be allowed to pass through that are common in all branches, else return error
    // if it is not all, classes have to be intersection of the selected branches only or can be all which means it is to be done for all classes of the selected branches.
    // if branch input is not array
    // it has to check only in reference with branch

    if ($checkClass == 1) {
        $sql_get_common_classes = "SELECT id from class_info where institution_id = ?";

        if (is_array($branch_input)) { // branch is either [all] or [1,2,3,4]
            if (in_array('all', $branch_input)) {

                if (is_array($class_input)) {
                    if (in_array('all', $class_input)) {
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating intersection possibilities for all branch case and multiple but not all classes
                        $sql_get_common_classes .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_classes .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_classes .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_classes .= ")";
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll(PDO::FETCH_ASSOC);
                        $all_class_id_array = array_column($common_classes, 'id');
                        // all your class inputs should be present in the all class id arrray then return true else errorreturn
                        foreach ($class_input as $class_id) {
                            if (!in_array($class_id, $all_class_id_array)) {
                                errorreturn("Class ID $class_id is not found in the allowed intersection.");
                            }
                        }

                        return true;
                    }
                } else {
                    if ($class_input == 'all') {
                        // nothing needs to be checked
                        return true;
                    } else {
                        // check that this class should be common in specific multiple branch
                        // validating intersection possibilities for spec ific multiple branch case and specific class
                        $sql_get_common_classes .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_classes .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_classes .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_classes .= ") and id = ?";
                        $array_params[] = $class_input;
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll();
                        if (count($common_classes) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected class is not available in all branches.");
                        }
                    }
                }
            } else {

                if (is_array($class_input)) {
                    if (in_array('all', $class_input)) {
                        if (count($class_input) > 1) {
                            errorreturn("You can either select all as a class option or select multiple other class options.");
                        }
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating intersection possibilities for all branch case and multiple but not all classes
                        $sql_get_common_classes .= " and (";
                        $array_params = [$institution_id];
                        foreach ($branch_input as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_classes .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_classes .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_classes .= ")";
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll(PDO::FETCH_ASSOC);
                        $all_class_id_array = array_column($common_classes, 'id');
                        // all your class inputs should be present in the all class id arrray then return true else errorreturn
                        foreach ($class_input as $class_id) {
                            if (!in_array($class_id, $all_class_id_array)) {
                                errorreturn("Class ID $class_id is not found in the allowed intersection.");
                            }
                        }

                        return true;
                    }
                } else {
                    if ($class_input != 'all') {
                        // check that this class should be common in all branch
                        // validating intersection possibilities for all branch case and specific class
                        $sql_get_common_classes .= " and (";
                        $array_params = [$institution_id];
                        foreach ($branch_input as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_classes .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_classes .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_classes .= ") and id = ?";
                        $array_params[] = $class_input;
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll();
                        if (count($common_classes) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected class is not available in all branches.");
                        }
                    }
                }
            }
        } else {
            if ($branch_input == 'all') {

                if (is_array($class_input)) {
                    if (in_array('all', $class_input)) {
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating intersection possibilities for all branch case and multiple but not all classes
                        $sql_get_common_classes .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_classes .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_classes .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_classes .= ")";
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll(PDO::FETCH_ASSOC);
                        $all_class_id_array = array_column($common_classes, 'id');
                        // all your class inputs should be present in the all class id arrray then return true else errorreturn
                        foreach ($class_input as $class_id) {
                            if (!in_array($class_id, $all_class_id_array)) {
                                errorreturn("Class ID $class_id is not found in the allowed intersection.");
                            }
                        }

                        return true;
                    }
                } else {
                    if ($class_input == 'all') {
                        // nothing needs to be checked
                        return true;
                    } else {
                        // check that this class should be common in specific multiple branch
                        // validating intersection possibilities for spec ific multiple branch case and specific class
                        $sql_get_common_classes .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_classes .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_classes .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_classes .= ") and id = ?";
                        $array_params[] = $class_input;
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll();
                        if (count($common_classes) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected class is not available in all branches.");
                        }
                    }
                }
            } else { // specific branch heree
                if (is_array($class_input)) {
                    if (in_array('all', $class_input)) {
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating multiple classes with specific branch
                        $sql_get_common_classes .= " and ";
                        $sql_get_common_classes .= "branch_id LIKE ?";
                        $array_params[] = '%' . $branch_input . '%';
                        $array_params = [$institution_id];

                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll(PDO::FETCH_ASSOC);
                        $all_class_id_array = array_column($common_classes, 'id');
                        // all your class inputs should be present in the all class id arrray then return true else errorreturn
                        foreach ($class_input as $class_id) {
                            if (!in_array($class_id, $all_class_id_array)) {
                                errorreturn("Class ID $class_id is not found in the allowed intersection.");
                            }
                        }

                        return true;
                    }
                } else {
                    if ($class_input != 'all') {
                        // check that this class should be common in 'all' branch
                        $sql_get_common_classes .= " and ";
                        $sql_get_common_classes .= "branch_id LIKE ?";
                        $array_params[] = '%' . $branch_input . '%';
                        $sql_get_common_classes .= " and id = ?";
                        $array_params = [$institution_id];
                        $array_params[] = $class_input;
                        $get_common_classes = $specific_db_conn->prepare($sql_get_common_classes);
                        $get_common_classes->execute($array_params);
                        $common_classes = $get_common_classes->fetchAll();
                        if (count($common_classes) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected class is not available in all branches.");
                        }
                    }
                }
            }
        }
    }

    // for sections
    if ($checkSection == 1) {
        $sql_get_common_sections = "SELECT id from section_info where institution_id = ?";

        if (is_array($branch_input)) { // branch is either [all] or [1,2,3,4]
            if (in_array('all', $branch_input)) {

                if (is_array($section_input)) {
                    if (in_array('all', $section_input)) {
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating intersection possibilities for all branch case and multiple but not all sections
                        $sql_get_common_sections .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_sections .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_sections .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_sections .= ")";
                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll(PDO::FETCH_ASSOC);
                        $all_section_id_array = array_column($common_sections, 'id');
                        // all your section inputs should be present in the all section id arrray then return true else errorreturn
                        foreach ($section_input as $section_id) {
                            if (!in_array($section_id, $all_section_id_array)) {
                                errorreturn("Section ID 1 is not found in the allowed intersection.");
                            }
                        }

                        return true;
                    }
                } else {
                    if ($section_input == 'all') {
                        // nothing needs to be checked
                        return true;
                    } else {
                        // check that this section should be common in specific multiple branch
                        // validating intersection possibilities for spec ific multiple branch case and specific section
                        $sql_get_common_sections .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_sections .= "branch_id LIKE ?";
                                $array_params[] = '%' . $temp_branch_id . '%';
                            } else {
                                $sql_get_common_sections .= "and branch_id LIKE ?";
                                $array_params[] = '%' . $temp_branch_id . '%';
                            }
                        }
                        $sql_get_common_sections .= ") and id = ?";
                        $array_params[] = $section_input;
                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll();
                        if (count($common_sections) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected section is not 1 available in all branches.");
                        }
                    }
                }
            } else {

                if (is_array($section_input)) {
                    if (in_array('all', $section_input)) {
                        if (count($section_input) > 1) {
                            errorreturn("You can either select all as a section option or select multiple other section options.");
                        }
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating intersection possibilities for all branch case and multiple but not all sections
                        $sql_get_common_sections .= " and (";
                        $array_params = [$institution_id];
                        foreach ($branch_input as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_sections .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_sections .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_sections .= ")";
                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll(PDO::FETCH_ASSOC);
                        $all_section_id_array = array_column($common_sections, 'id');
                        // all your section inputs should be present in the all section id arrray then return true else errorreturn
                        foreach ($section_input as $section_id) {
                            if (!in_array($section_id, $all_section_id_array)) {
                                errorreturn("Section ID 2 is not found in the allowed intersection.");
                            }
                        }

                        return true;
                    }
                } else {
                    if ($section_input != 'all') {
                        // check that this section should be common in all branch
                        // validating intersection possibilities for all branch case and specific section
                        $sql_get_common_sections .= " and (";
                        $array_params = [$institution_id];
                        foreach ($branch_input as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_sections .= "branch_id LIKE ?";
                                $array_params[] = '%' . $temp_branch_id . '%';
                            } else {
                                $sql_get_common_sections .= "and branch_id LIKE ?";
                                $array_params[] = '%' . $temp_branch_id . '%';
                            }
                        }
                        $sql_get_common_sections .= ") and id = ?";
                        $array_params[] = $section_input;
                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll();
                        if (count($common_sections) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected section is not 2 available in all branches.");
                        }
                    }
                }
            }
        } else {
            if ($branch_input == 'all') {

                if (is_array($section_input)) {
                    if (in_array('all', $section_input)) {
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating intersection possibilities for all branch case and multiple but not all sections
                        $sql_get_common_sections .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_sections .= "branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            } else {
                                $sql_get_common_sections .= "and branch_id LIKE ?";
                                $array_params[] = '%"' . $temp_branch_id . '"%';
                            }
                        }
                        $sql_get_common_sections .= ")";
                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll(PDO::FETCH_ASSOC);
                        $all_section_id_array = array_column($common_sections, 'id');
                        // all your section inputs should be present in the all section id arrray then return true else errorreturn
                        foreach ($section_input as $section_id) {
                            if (!in_array($section_id, $all_section_id_array)) {
                                errorreturn("Section ID 3 is not found in the allowed intersection.");
                            }
                        }
                    }
                } else {
                    if ($section_input == 'all') {
                        // nothing needs to be checked
                        return true;
                    } else {

                        // check that this section should be common in specific multiple branch
                        // validating intersection possibilities for spec ific multiple branch case and specific section
                        $sql_get_common_sections .= " and (";
                        $array_params = [$institution_id];
                        foreach ($all_branch_id_array as $index => $temp_branch_id) {
                            if ($index == 0) {
                                $sql_get_common_sections .= "branch_id LIKE ?";
                                $array_params[] = '%' . $temp_branch_id . '%';
                            } else {
                                $sql_get_common_sections .= "and branch_id LIKE ?";
                                $array_params[] = '%' . $temp_branch_id . '%';
                            }
                        }
                        $sql_get_common_sections .= ") and id = ?";
                        $array_params[] = $section_input;

                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll();
                        if (count($common_sections) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected section is 3 not available in all branches.");
                        }
                    }
                }
            } else { // specific branch heree
                if (is_array($section_input)) {
                    if (in_array('all', $section_input)) {
                        // nothing needs to be checked here
                        return true;
                    } else {
                        // validating multiple sections with specific branch
                        $sql_get_common_sections .= " and ";
                        $sql_get_common_sections .= "(branch_id LIKE ? or branch_id = 'all')";
                        $array_params = [$institution_id, '%' . $branch_input . '%'];

                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll(PDO::FETCH_ASSOC);
                        $all_section_id_array = array_column($common_sections, 'id');
                        // all your section inputs should be present in the all section id arrray then return true else errorreturn
                        foreach ($section_input as $section_id) {
                            if (!in_array($section_id, $all_section_id_array)) {
                                errorreturn("Section ID 4is not found in the allowed intersection.");
                            }
                        }
                    }
                } else {
                    if ($section_input != 'all') {
                        // check that this section should be common in 'all' branch
                        $sql_get_common_sections .= " and ";
                        $sql_get_common_sections .= "(branch_id LIKE ? or branch_id LIKE ?) and id = ?";
                        $array_params = [$institution_id, 'all', "%'".$branch_input."'%", $section_input];
                        $get_common_sections = $specific_db_conn->prepare($sql_get_common_sections);
                        $get_common_sections->execute($array_params);
                        $common_sections = $get_common_sections->fetchAll();
                        if (count($common_sections) == 1) {
                            return true;
                        } else {
                            errorreturn("The selected section is 4 not available in all branches.");
                        }
                    }
                }
            }
        }
    }
}

function canPrincipalEditBranch($branch_input)
{
    // Check if input is an array
    if (is_array($branch_input)) {
        // Check if there are multiple branches
        if (count($branch_input) > 1) {
            return false; // Cannot edit or delete if more than one branch
        } elseif (count($branch_input) == 1) {
            // Access the first branch and check the branch_id
            if (isset($branch_input) && $branch_input !== 'all') {
                return true; // Can edit if branch_id is NOT 'all'
            }
            return false; // Cannot edit if branch_id is 'all' or invalid
        }
    }
    // If input is not an array, return true (default allow edit)
    return true;
}

function findAcademicSession($conn, $table, $id, $institution_id)
{
    $sql = "SELECT academic_id FROM `$table` WHERE id = ? AND institution_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->execute([$id, $institution_id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function checkEditPermission($conn_table, $conn_users, $table, $record_id, $current_user_role)
{
    // Authority hierarchy
    $roles_hierarchy = [
        'superadmin' => 1,
        'admin' => 2,
        'principal' => 3,
        'teacher' => 4 // Staff = Teacher
    ];

    // Step 1: Check if the table has either 'added_by' or 'created_by'
    $columns = ['added_by', 'created_by'];
    $columnToCheck = null;

    foreach ($columns as $column) {
        $query = "SHOW COLUMNS FROM $table LIKE ?";
        $stmt = $conn_table->prepare($query);
        $stmt->execute([$column]);
        if ($stmt->fetch()) {
            $columnToCheck = $column;
            break;
        }
    }

    // Step 2: If neither column exists, return an error
    if (!$columnToCheck) {
        errorreturn(['error' => "Neither 'added_by' nor 'created_by' exists in the $table table."]);
    }

    // Step 3: Fetch the `added_by` or `created_by` value from the record
    $query = "SELECT $columnToCheck FROM $table WHERE id = ?";
    $stmt = $conn_table->prepare($query);
    $stmt->execute([$record_id]);
    $record = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$record) {
        errorreturn(['error' => "Record not found."]);
    }

    $added_by_user_id = $record[$columnToCheck];

    // Step 4: Fetch the corresponding user from the `users` table using the second connection
    $query = "SELECT username, role FROM users WHERE id = ?";
    $stmt = $conn_users->prepare($query);
    $stmt->execute([$added_by_user_id]);
    $user = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$user) {
        errorreturn(['error' => "User not found."]);
    }

    $added_by_username = $user['username'];
    $added_by_role = $user['role'];

    // Step 5: Permission Logic Based on Roles
    if ($current_user_role === 'superadmin') {
        // Superadmin can edit everything
        return true;
    }

    if ($current_user_role === 'admin' && $added_by_role === 'superadmin') {
        // Admin cannot edit records added by Superadmin
        errorreturn("Admin cannot edit records added by Superadmin.");
    }

    if ($current_user_role === 'principal' && in_array($added_by_role, ['admin', 'superadmin'])) {
        // Principal cannot edit records added by Admin or Superadmin
        errorreturn("Principal cannot edit records added by higher authorities (Admin or Superadmin).");
    }

    if ($current_user_role === 'staff' && $added_by_role !== 'staff') {
        // Staff (Teacher) can only edit records added by other staff
        errorreturn("Teachers can only edit records added by other teachers.");
    }

    // Step 6: If all checks pass, return true
    return true;
}

function registerStudent(
    $conn_rjllearning_specific,
    $institution_id,
    $branch_id,
    $vendor_name,
    $unmarked_group,
    $amount,
    $date,
    $transaction_type_group,
    $payment_bool,
    $payment_method,
    $username,
    $transaction_reference_id = null // Optional parameter for reference number
) {
    try {
        // Fetch the `existing_id` from the `unmarked_transaction_type_group` table
        $query = "SELECT id FROM unmarked_transaction_type_group WHERE group_name = ?";
        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute([$unmarked_group]);
        $existing_id = $stmt->fetchColumn();

        if (!$existing_id) {
            errorreturn("Error: No matching group found for the transaction type group: $unmarked_group");
            return false;
        }

        // Generate reference number if not provided
        if (empty($transaction_reference_id)) {
            $currentDate = date("Ymd"); // Format date as YYYYMMDD
            $transaction_reference_id = "REG-" . strtoupper($vendor_name) . "-" . $currentDate;
        }
        $remarks = "Paid by $vendor_name for student registration.";

        // Prepare SQL query to insert data into the `unmarked_transaction` table
        $insert_transaction_sql = "INSERT INTO unmarked_transaction (
            institution_id, branch_id, vendor_name, category_id, amount, date, 
            transaction_reference_id, payment_bool, payment_method, paid_on, 
            attachment, remarks, created_by, date_created, time_created, 
            last_edit, last_edit_at, approved_by, recorded_in_finware, 
            transaction_type_group, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        $stmt = $conn_rjllearning_specific->prepare($insert_transaction_sql);
        $stmt->execute([
            $institution_id,
            $branch_id,
            $vendor_name,
            $existing_id,
            $amount,
            $date,
            $transaction_reference_id,
            $payment_bool,
            $payment_method,
            date('Y-m-d'), // paid_on
            null,           // attachment
            $remarks,
            $username,
            date('Y-m-d'),  // date_created
            time(),         // time_created
            null,           // last_edit
            null,           // last_edit_at
            null,           // approved_by
            0,              // recorded_in_finware
            $transaction_type_group,
            1               // status
        ]);

        // Return true if everything is successful
        return true;
    } catch (PDOException $e) {
        errorreturn("Error: " . $e->getMessage());
        return false;
    }
}

function getBranchCountryStateInfo($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id) {
    // Initialize the result array
    $result = [
        'country_name' => null,
        'state_name' => null
    ];

    // Fetch the country_id and state_id from the branch table
    $query = "SELECT country, state FROM branch WHERE institution_id = ? AND id = ?";
    $params = [$institution_id, $branch_id];
    
    $stmt = $conn_rjllearning_specific->prepare($query);
    $stmt->execute($params);
    $branch_info = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($branch_info) {
        $country_id = $branch_info['country'];
        $state_id = $branch_info['state'];

        // Fetch country name
        if ($country_id) {
            $query = "SELECT name FROM country_info WHERE id = ?";
            $stmt = $conn_rjllearning->prepare($query);
            $stmt->execute([$country_id]);
            $country_result = $stmt->fetch(PDO::FETCH_ASSOC);
            $result['country_name'] = $country_result ? $country_result['name'] : null;
        }

        // Fetch state name
        if ($state_id) {
            $query = "SELECT state FROM state_info WHERE id = ? AND country_id = ?";
            $stmt = $conn_rjllearning->prepare($query);
            $stmt->execute([$state_id, $country_id]);
            $state_result = $stmt->fetch(PDO::FETCH_ASSOC);
            $result['state_name'] = $state_result ? $state_result['state'] : null;
        }
    }

    return $result;
}

function updateStudentUsername($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $old_admission_number, $new_admission_number) {
    // Step 1: Get student info
    $get_student = $conn_rjllearning_specific->prepare("
        SELECT user_id, name, email 
        FROM students 
        WHERE admission_number = ? AND institution_id = ? AND branch_id = ?
    ");
    $get_student->execute([$new_admission_number, $institution_id, $branch_id]);
    $student = $get_student->fetch(PDO::FETCH_ASSOC);

    if (!$student || empty($student['user_id'])) {
        errorreturn("Student user ID not found after updating admission number.");
    }

    $user_id = $student['user_id'];
    $name = $student['name'] ?? 'Student';
    $email = $student['email'] ?? null;

    // Step 2: Fetch institution and branch info
    $institution_info = getInstitution($conn_rjllearning, $institution_id);
    if (!$institution_info || empty($institution_info['institution_code'])) {
        errorreturn("Institution info not found.");
    }

    $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id, ['branch_code']);
    if (!$branch_info || empty($branch_info['branch_code'])) {
        errorreturn("Branch info not found.");
    }

    // Step 3: Generate new username
    $student_username = $branch_info['branch_code'] . $new_admission_number . "@" . $institution_info['institution_code'];

    // Step 4: Check if username is available
    if (!checkUsernameAvailabilty($conn_rjllearning, $student_username)) {
        errorreturn("Username '$student_username' already exists. Please try another username.");
    }

    // Step 5: Update students and users tables
    $update_student = $conn_rjllearning_specific->prepare("
        UPDATE students 
        SET username = ? 
        WHERE user_id = ? AND institution_id = ? AND branch_id = ?
    ");
    $update_student->execute([$student_username, $user_id, $institution_id, $branch_id]);

    $update_user = $conn_rjllearning->prepare("
        UPDATE users 
        SET username = ? 
        WHERE id = ?
    ");
    $update_user->execute([$student_username, $user_id]);

   

    // Step 7: Send email if email exists
    if (!empty($email)) {
        require_once $GLOBALS['base_path_to_learning_resources'] . 'lib/mail_system/mail_Template1.php';
        $web_app_title = "Rejoice Learning"; // or dynamically assign
        $subject = 'Your Admission Details Have Been Updated';

        $message = "
            <p>This is to inform you that your admission number and username have been successfully updated to the following details:</p>
            <p>
                Name: <b>$name</b><br>
                New Admission Number: <b>$new_admission_number</b><br>
                New Username: <b>$student_username</b>
            </p>
            <p>You can now log in using your new username at <a href='https://students.rejoicelearning.in'>https://students.rejoicelearning.in</a> or through your designated school app.</p>
            <p>If you have any questions or concerns, please contact the school administration.</p>
        ";


        sendMailTemplate1(
            $conn_rjllearning,
            'Rejoice Learning <no-reply@rejoicelearning.in>',
            $email,
            $subject,
            $message,
            $GLOBALS['workspace_id'],
            $institution_id,
            $branch_id,
            $user_id,
            null,
            'real_time'
        );
    }

    return true;
}


function dateToWords($dateString) {
    // Validate date
    if (!$dateString || !strtotime($dateString)) {
        return "Invalid date";
    }

    $timestamp = strtotime($dateString);
    $day   = date('j', $timestamp); // Day without leading zero
    $month = date('F', $timestamp); // Full month name
    $year  = date('Y', $timestamp); // Year

    // Add ordinal suffix to day
    $dayWithSuffix = $day . date('S', $timestamp);

    return $dayWithSuffix . " " . $month . " " . $year;
}



function aes128Encrypt($plaintext,$key){

    $cipher = "AES-128-ECB";

    in_array($cipher, openssl_get_cipher_methods(true));

    $ivlen = openssl_cipher_iv_length($cipher);

    $iv = openssl_random_pseudo_bytes(1);

    $ciphertext = openssl_encrypt($plaintext, $cipher, $key, $options=0, "");

    //return $ciphertext."n";

    return $ciphertext;   

}

// function aes128Decrypt($encryptedData, $key) {
//     $cipher = "aes-128-ecb"; // Ensure correct case

//     if (!in_array($cipher, openssl_get_cipher_methods(true))) {
//         die("Cipher method not supported.");
//     }

//     $key = substr(hash('sha256', $key, true), 0, 16); // Ensure 16-byte key
//     $encryptedText = base64_decode($encryptedData); // Decode from base64

//     return openssl_decrypt($encryptedText, $cipher, $key, OPENSSL_RAW_DATA);
// }

function aes128Decrypt($ciphertext, $key) {
    $cipher = "AES-128-ECB";
    return openssl_decrypt($ciphertext, $cipher, $key, 0);
}


