<?php

function getFaculty($conn_rjllearning_specific, $institution_id, $branch_id, $additionaldata, $search_string, $limit, $offset)
{

    $sql = "SELECT  count(*) as total_rows FROM  faculty_info  where institution_id =?  and date_of_termination IS NULL and (name LIKE ? OR email LIKE ? OR  mobile LIKE ? OR designation LIKE ? OR father_name LIKE ?  OR mother_name LIKE ?)";
    $array_params = [$institution_id, '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%'];
    // if($branch_id != null){
    //     $sql .= "AND branch_id = ?";
    //     $array_params[] = $branch_id;
    // }
    if ($branch_id == 'all') {
        $sql .= "";
    } else if ($branch_id !=  null  && $branch_id != 'all') {
        $sql .= " AND branch_id = ? ";
        $array_params[] = $branch_id;
    }
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    if ($limit == 'all') {
        $sql_faculty = "SELECT ";
        $array_counter = 0;
        foreach ($additionaldata as $column_name) {
            if ($array_counter == 0) {
                $sql_faculty .= $column_name;
            } else {
                $sql_faculty .= ", " . $column_name;
            }
            $array_counter++;
        }
        $sql_faculty .= " FROM faculty_info";
        $sql_faculty .= " WHERE institution_id = ? and date_of_termination IS NULL";

        $array_params = [$institution_id];

        if ($branch_id == 'all') {
            $sql_faculty .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_faculty .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }
        if ($search_string != null) {
            $sql_faculty .= " AND (name LIKE ? OR email LIKE ? OR  mobile LIKE ? OR designation LIKE ? OR father_name LIKE ?  OR mother_name LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
        }
    } else {
        $sql_faculty = "SELECT ";
        $array_counter = 0;
        foreach ($additionaldata as $column_name) {
            if ($array_counter == 0) {
                $sql_faculty .= $column_name;
            } else {
                $sql_faculty .= ", " . $column_name;
            }
            $array_counter++;
        }
        $sql_faculty .= " FROM faculty_info";
        $sql_faculty .= " WHERE institution_id = ? and date_of_termination IS NULL";

        $array_params = [$institution_id];
        if ($branch_id == 'all') {
            $sql_faculty .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_faculty .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }
        if ($search_string != null) {
            $sql_faculty .= " AND (name LIKE ? OR email LIKE ? OR  mobile LIKE ? OR designation LIKE ? OR father_name LIKE ?  OR mother_name LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
        }
        $sql_faculty .=  "LIMIT " . $offset . " ," . $limit . " ";
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_faculty);
    $stmt->execute($array_params);
    $result = $stmt->fetchAll();

    $array_return = [];
    foreach ($result as $res) {
        $fieldarray = [];

        // Fetch staff access support ID
        $sql = "SELECT id FROM staff_access_support WHERE faculty_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$res['id']]);
        $staff_result = $stmt->fetch();
        $staff_access_support = $staff_result['id'] ?? null;

        // Add staff_access_support to the result array
        $fieldarray['staff_support_id'] = $staff_access_support;

        // Iterate over the additional data and populate results
        foreach ($additionaldata as $column_name) {
            if ($column_name === 'id') {
                $fieldarray['faculty_id'] = $res[$column_name];
            } elseif ($column_name === 'profile_image') {
                if ($res['profile_image'] != null && !is_array($res['profile_image'])) {
                    $data_attachment = json_decode($res['profile_image'], true);
                    $filePath = $data_attachment['filepath'];
                    $originalName = $data_attachment['original_name'];
                    $aws_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                    $fieldarray['profile_image'] = $aws_image;
                } else {
                    $fieldarray['profile_image'] = null;
                }
            } elseif ($column_name === 'branch_id') {
                $fieldarray['branch_id'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $res['branch_id']);
            } elseif ($column_name === 'country') {
                $country = $res['country'];
                $sql_country = $conn_rjllearning_specific->prepare("SELECT * FROM country_info WHERE id = ?");
                $sql_country->execute([$country]);
                $rows_country = $sql_country->fetch();
                if ($rows_country) {
                    $fieldarray['country_name'] = $rows_country['name'];
                }
            } else {
                // For all other columns
                $fieldarray[$column_name] = $res[$column_name];
            }
        }

        array_push($array_return, $fieldarray);
    }

    if (count($result) < 50) {
        return array('total_rows' => $totalrows, 'faculty_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'faculty_info' => $array_return, 'maxreach' => 0);
    }
}

// function GetBasicfacultyDetail($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset, $additionaldata, $role_option = null, $faculty_status = null)
// {
//     $sql_count = "SELECT COUNT(f.id) AS total_rows FROM faculty_info f 
//                   WHERE f.institution_id = ? AND f.status = 1 AND f.date_of_termination IS NULL";
//     $array_params = [$institution_id];

//     // if ($role_option == 1) {
//     //     $sql_count .= " AND f.role IN ('teacher','principal')";
//     // }
//     $sql_count .= " AND f.role IN ('teacher','principal')";

//     if ($branch_id !== 'all' && $branch_id !== null) {
//         $sql_count .= " AND f.branch_id = ?";
//         $array_params[] = $branch_id;
//     }

//     $validate_faculty = $conn_rjllearning_specific->prepare($sql_count);
//     $validate_faculty->execute($array_params);
//     $result_total = $validate_faculty->fetch(PDO::FETCH_ASSOC);
//     $totalrows = $result_total['total_rows'];

//     if ($limit == 'all') {
//         $sql = "SELECT ";
//         $array_counter = 0;
//         foreach ($additionaldata as $column_name) {
//             if ($array_counter == 0) {
//                 $sql .= "f." . $column_name; // Ensure alias "f." is added for clarity
//             } else {
//                 $sql .= ", f." . $column_name;
//             }
//             $array_counter++;
//         }

//         $sql .= " FROM faculty_info f WHERE f.institution_id = ? AND f.status = 1 AND f.date_of_termination IS NULL
//         AND f.role IN ('teacher','principal')";

//         $array_params = [$institution_id];
//         // if ($role_option ==  1) {
//         //     $sql .= " AND f.role IN ('teacher','principal') ";
//         // }

//         if ($branch_id == 'all') {
//             $sql .= "";
//         } else if ($branch_id !=  null  && $branch_id != 'all') {
//             $sql .= " AND f.branch_id = ? ";
//             $array_params[] = $branch_id;
//         }

//         if ($search_string != null) {
//             $sql .= " AND (f.name LIKE ? OR f.email LIKE ? OR f.mobile LIKE ? OR f.designation LIKE ? OR f.father_name LIKE ? OR f.mother_name LIKE ?)";
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//         }
//     } else {

//         $sql = "SELECT ";
//         $array_counter = 0;
//         foreach ($additionaldata as $column_name) {
//             if ($array_counter == 0) {
//                 $sql .= "f." . $column_name; // Ensure alias "f." is added for clarity
//             } else {
//                 $sql .= ", f." . $column_name;
//             }
//             $array_counter++;
//         }

//         $sql .= " FROM faculty_info f WHERE f.institution_id = ? AND f.status = 1 AND f.date_of_termination IS NULL
//         AND f.role IN ('teacher','principal')";

//         $array_params = [$institution_id];
//         // if ($role_option ==  1) {
//         //     $sql .= " AND f.role IN ('teacher','principal') ";
//         // }

//         if ($branch_id == 'all') {
//             $sql .= "";
//         } else if ($branch_id !=  null  && $branch_id != 'all') {
//             $sql .= " AND f.branch_id = ? ";
//             $array_params[] = $branch_id;
//         }

//         if ($search_string != null) {
//             $sql .= " AND (f.name LIKE ? OR f.email LIKE ? OR f.mobile LIKE ? OR f.designation LIKE ? OR f.father_name LIKE ? OR f.mother_name LIKE ?)";
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//         }
//         $sql .= " LIMIT " . (int)$limit . " OFFSET " . (int)$offset;
//     }

//     $stmt = $conn_rjllearning_specific->prepare($sql);
//     $stmt->execute($array_params);
//     $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//     $array_return = [];
//     foreach ($result as $res) {
//         $fieldarray = $res;

//         // Fetch user details from the users table using $conn_rjllearning
//         $stmt_user = $conn_rjllearning->prepare("SELECT status, can_activate FROM users WHERE id = ?");
//         $stmt_user->execute([$res['userid']]);
//         $user_data = $stmt_user->fetch(PDO::FETCH_ASSOC);

//         // if ($faculty_status !== null) {
//         //     if ($faculty_status == 1 && (!isset($user_data['status']) || $user_data['status'] != 1 || $user_data['can_activate'] != 1)) {
//         //         continue;
//         //     } elseif ($faculty_status == 0 && (!isset($user_data['status']) || $user_data['status'] != 0 || $user_data['can_activate'] != 1)) {
//         //         continue;
//         //     } elseif ($faculty_status == -1 && (!isset($user_data['can_activate']) || $user_data['can_activate'] != 0)) {
//         //         continue;
//         //     }
//         // } else {
//         //     if (!isset($user_data['can_activate']) || ($user_data['can_activate'] == 1 && !in_array($user_data['status'], [0, 1]))) {
//         //         continue;
//         //     }
//         // }

//         if ($faculty_status !== null) {
//             if ($faculty_status == 1) {
//                 // Active only: status=1 and can_activate=1
//                 if (!(isset($user_data['status']) && $user_data['status'] == 1
//                     && isset($user_data['can_activate']) && $user_data['can_activate'] == 1)) {
//                     continue;
//                 }
//             } elseif ($faculty_status == 0) {
//                 // Blocked only: status=0 and can_activate=1
//                 if (!(isset($user_data['status']) && $user_data['status'] == 0
//                     && isset($user_data['can_activate']) && $user_data['can_activate'] == 1)) {
//                     continue;
//                 }
//             } elseif ($faculty_status == -1) {
//                 // Left only: can_activate=0
//                 if (!(isset($user_data['can_activate']) && $user_data['can_activate'] == 0)) {
//                     continue;
//                 }
//             }
//         } else {
//             // When no status filter: fetch both active and blocked (can_activate=1 and status=0/1)
//             if (!isset($user_data['can_activate']) || $user_data['can_activate'] != 1) {
//                 continue;
//             }
//             if (!isset($user_data['status']) || !in_array($user_data['status'], [0, 1])) {
//                 continue;
//             }
//         }

//         // Fetch staff access support ID
//         $stmt_staff = $conn_rjllearning_specific->prepare("SELECT id FROM staff_access_support WHERE faculty_id = ?");
//         $stmt_staff->execute([$res['id']]);
//         $staff_result = $stmt_staff->fetch(PDO::FETCH_ASSOC);
//         $fieldarray['staff_support_id'] = $staff_result['id'] ?? null;

//         // Process profile image
//         if (in_array('profile_image', $additionaldata)) {
//             $fieldarray['profile_image'] = null;
//             if (!empty($res['profile_image'])) {
//                 $profile_image_data = json_decode($res['profile_image'], true);
//                 $filePath = $profile_image_data['filepath'];
//                 $originalName = $profile_image_data['original_name'];
//                 $aws_profile_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
//                 $fieldarray['profile_image'] = ["filepath" => $aws_profile_image, "original_name" => $originalName];
//             }
//         }

//         // Process branch info
//         if (in_array('branch_id', $additionaldata)) {
//             $fieldarray['branch_id'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $res['branch_id']);
//         }

//         array_push($array_return, $fieldarray);
//     }

//     return [
//         'total_rows' => $totalrows,
//         'faculty_info' => $array_return,
//         'maxreach' => count($result) < 50 ? 1 : 0
//     ];
// }

// function GetBasicfacultyDetail($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset, $additionaldata, $role_option = null, $faculty_status = null)
// {
//     $sql_count = "SELECT COUNT(f.id) AS total_rows FROM faculty_info f 
//                   WHERE f.institution_id = ? AND f.role IN ('teacher','principal')";
//     $array_params = [$institution_id];

//     if ($branch_id !== 'all' && $branch_id !== null) {
//         $sql_count .= " AND f.branch_id = ?";
//         $array_params[] = $branch_id;
//     }

//     $validate_faculty = $conn_rjllearning_specific->prepare($sql_count);
//     $validate_faculty->execute($array_params);
//     $result_total = $validate_faculty->fetch(PDO::FETCH_ASSOC);
//     $totalrows = $result_total['total_rows'];

//     if ($limit == 'all') {
//         $sql = "SELECT ";
//         $array_counter = 0;
//         foreach ($additionaldata as $column_name) {
//             if ($array_counter == 0) {
//                 $sql .= "f." . $column_name;
//             } else {
//                 $sql .= ", f." . $column_name;
//             }
//             $array_counter++;
//         }

//         $sql .= " FROM faculty_info f WHERE f.institution_id = ?
//         AND f.role IN ('teacher','principal')";

//         $array_params = [$institution_id];

//         if ($branch_id == 'all') {
//             $sql .= "";
//         } else if ($branch_id != null && $branch_id != 'all') {
//             $sql .= " AND f.branch_id = ? ";
//             $array_params[] = $branch_id;
//         }

//         if ($search_string != null) {
//             $sql .= " AND (f.name LIKE ? OR f.email LIKE ? OR f.mobile LIKE ? OR f.designation LIKE ? OR f.father_name LIKE ? OR f.mother_name LIKE ?)";
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//         }
//     } else {
//         $sql = "SELECT ";
//         $array_counter = 0;
//         foreach ($additionaldata as $column_name) {
//             if ($array_counter == 0) {
//                 $sql .= "f." . $column_name;
//             } else {
//                 $sql .= ", f." . $column_name;
//             }
//             $array_counter++;
//         }

//         $sql .= " FROM faculty_info f WHERE f.institution_id = ?
//         AND f.role IN ('teacher','principal')";

//         $array_params = [$institution_id];

//         if ($branch_id == 'all') {
//             $sql .= "";
//         } else if ($branch_id != null && $branch_id != 'all') {
//             $sql .= " AND f.branch_id = ? ";
//             $array_params[] = $branch_id;
//         }

//         if ($search_string != null) {
//             $sql .= " AND (f.name LIKE ? OR f.email LIKE ? OR f.mobile LIKE ? OR f.designation LIKE ? OR f.father_name LIKE ? OR f.mother_name LIKE ?)";
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//             $array_params[] = '%' . $search_string . '%';
//         }
//         $sql .= " LIMIT " . (int)$limit . " OFFSET " . (int)$offset;
//     }

//     $stmt = $conn_rjllearning_specific->prepare($sql);
//     $stmt->execute($array_params);
//     $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//     $array_return = [];
//     foreach ($result as $res) {
//         $fieldarray = $res;

//         // Fetch user details from the users table
//         $stmt_user = $conn_rjllearning->prepare("SELECT status, can_activate FROM users WHERE id = ?");
//         $stmt_user->execute([$res['userid']]);
//         $user_data = $stmt_user->fetch(PDO::FETCH_ASSOC);

//         // Skip if user data not found
//         if (!$user_data) {
//             continue;
//         }

//         // Apply faculty status filtering
//         if ($faculty_status !== null) {
//             if ($faculty_status == 1) {
//                 // Active only: status=1 and can_activate=1
//                 if (!($user_data['status'] == 1 && $user_data['can_activate'] == 1)) {
//                     continue;
//                 }
//             } elseif ($faculty_status == 0) {
//                 // Blocked only: status=0 and can_activate=1
//                 if (!($user_data['status'] == 0 && $user_data['can_activate'] == 1)) {
//                     continue;
//                 }
//             } elseif ($faculty_status == -1) {
//                 // Left only: can_activate=0
//                 if (!($user_data['can_activate'] == 0)) {
//                     continue;
//                 }
//             }
//         } else {
//             // When no status filter: fetch both active and blocked (can_activate=1 and status=0/1)
//             if ($user_data['can_activate'] != 1) {
//                 continue;
//             }
//             if (!in_array($user_data['status'], [0, 1])) {
//                 continue;
//             }
//         }

//         // Fetch staff access support ID
//         $stmt_staff = $conn_rjllearning_specific->prepare("SELECT id FROM staff_access_support WHERE faculty_id = ?");
//         $stmt_staff->execute([$res['id']]);
//         $staff_result = $stmt_staff->fetch(PDO::FETCH_ASSOC);
//         $fieldarray['staff_support_id'] = $staff_result['id'] ?? null;

//         // Process profile image
//         if (in_array('profile_image', $additionaldata)) {
//             $fieldarray['profile_image'] = null;
//             if (!empty($res['profile_image'])) {
//                 $profile_image_data = json_decode($res['profile_image'], true);
//                 $filePath = $profile_image_data['filepath'];
//                 $originalName = $profile_image_data['original_name'];
//                 $aws_profile_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
//                 $fieldarray['profile_image'] = ["filepath" => $aws_profile_image, "original_name" => $originalName];
//             }
//         }

//         // Process branch info
//         if (in_array('branch_id', $additionaldata)) {
//             $fieldarray['branch_id'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $res['branch_id']);
//         }

//         array_push($array_return, $fieldarray);
//     }

//     return [
//         'total_rows' => $totalrows,
//         'faculty_info' => $array_return,
//         'maxreach' => count($result) < 50 ? 1 : 0
//     ];
// }

function GetBasicfacultyDetail($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset, $additionaldata, $role_option = null, $faculty_status = null, $registered_bool_epf_esi = null, $date_filter = null, $date_comparison = null)
{
    // First get all faculty IDs that match the basic criteria
    $sql = "SELECT f.id, f.userid, f.role FROM faculty_info f 
        WHERE f.institution_id = ?";
    
    $array_params = [$institution_id];

    if ($branch_id !== 'all' && $branch_id !== null) {
        $sql .= " AND f.branch_id = ?";
        $array_params[] = $branch_id;
    }

    if ($search_string != null) {
        $sql .= " AND (f.name LIKE ? OR f.email LIKE ? OR f.mobile LIKE ? OR f.designation LIKE ? OR f.father_name LIKE ? OR f.mother_name LIKE ?)";
        $search_param = '%' . $search_string . '%';
        for ($i = 0; $i < 6; $i++) {
            $array_params[] = $search_param;
        }
    }
    
    if ($role_option == 1) {
        // Only 'teacher' and 'principal'
        $sql .= " AND f.role IN ('teacher', 'principal')";
    } else {
        // Include all roles ('teacher', 'principal', 'other')
        $sql .= " AND f.role IN ('teacher', 'principal', 'other')";
    }
    
    if ($registered_bool_epf_esi !== null) {
    if($registered_bool_epf_esi == 0){
        // Handle both 0 and NULL values
        $sql .= " AND (f.registered_bool_epf_esi = 0 OR f.registered_bool_epf_esi IS NULL)"; 
    } else {
        $sql .= " AND f.registered_bool_epf_esi = ?";
        $array_params[] = $registered_bool_epf_esi;
    }
}
    
    if ($date_filter !== null) {
        $date_value = date('Y-m-d', strtotime($date_filter));
        switch ($date_comparison) {
            case 'equal_to':
                $sql .= " AND DATE(f.date_of_joining_as_per_registration) = ?";
                break;
            case 'lessthan_equal_to':
                $sql .= " AND DATE(f.date_of_joining_as_per_registration) <= ?";
                break;
            case 'greaterthan_equal_to':
                $sql .= " AND DATE(f.date_of_joining_as_per_registration) >= ?";
                break;
            default:
                $sql .= " AND DATE(f.date_of_joining_as_per_registration) = ?";
                break;
        }
        $array_params[] = $date_value;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute($array_params);
    $all_faculty = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Now filter by user status
    $filtered_faculty_ids = [];
    $filtered_user_ids = [];
    
    foreach ($all_faculty as $faculty) {
        
        $other_faculty_role =$faculty['role'];
        if ($other_faculty_role === 'other') {
        $filtered_faculty_ids[] = $faculty['id'];
        // No user ID added
        continue;
    }
        
        $stmt_user = $conn_rjllearning->prepare("SELECT status, can_activate FROM users WHERE id = ?");
        $stmt_user->execute([$faculty['userid']]);
        $user_data = $stmt_user->fetch(PDO::FETCH_ASSOC);

        if (!$user_data) continue;

        if ($faculty_status !== null) {
            if ($faculty_status == 1 && $user_data['status'] == 1 && $user_data['can_activate'] == 1) {
                $filtered_faculty_ids[] = $faculty['id'];
                $filtered_user_ids[] = $faculty['userid'];
            } elseif ($faculty_status == 0 && $user_data['status'] == 0 && $user_data['can_activate'] == 1) {
                $filtered_faculty_ids[] = $faculty['id'];
                $filtered_user_ids[] = $faculty['userid'];
            } elseif ($faculty_status == -1 && $user_data['can_activate'] == 0) {
                $filtered_faculty_ids[] = $faculty['id'];
                $filtered_user_ids[] = $faculty['userid'];
            }
        } else {
            // No status filter - include both active and blocked
            if ($user_data['can_activate'] == 1 && in_array($user_data['status'], [0, 1])) {
                $filtered_faculty_ids[] = $faculty['id'];
                $filtered_user_ids[] = $faculty['userid'];
            }
        }
    }
   
    // If no results after filtering, return empty
    if (empty($filtered_faculty_ids)) {
        return [
            'total_rows' => 0,
            'faculty_info' => [],
            'maxreach' => 0
        ];
    }

    // Now get the full faculty data for the filtered IDs
    $sql = "SELECT ";
    $array_counter = 0;
    foreach ($additionaldata as $column_name) {
        if ($array_counter == 0) {
            $sql .= "f." . $column_name;
        } else {
            $sql .= ", f." . $column_name;
        }
        $array_counter++;
    }
    $sql .= " FROM faculty_info f WHERE f.id IN (" . implode(',', array_fill(0, count($filtered_faculty_ids), '?')) . ")";

    // Add pagination if needed
    if ($limit != 'all') {
        $sql .= " LIMIT " . (int)$limit . " OFFSET " . (int)$offset;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute($filtered_faculty_ids);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Process the results
    $array_return = [];
    foreach ($result as $res) {
        $fieldarray = $res;
        
        if (isset($res['name']) && isset($res['mobile'])) {
            $fieldarray['formatted_name'] = $res['name'] . ' (' . $res['mobile'] . ')';
        } else {
            $fieldarray['formatted_name'] = $res['name'] ?? '';
        }

        $fieldarray['registered_bool_epf_esi'] = $res['registered_bool_epf_esi'] ?? null;
        $fieldarray['date_of_joining_as_per_registration'] = $res['date_of_joining_as_per_registration'] ?? null;
         // Check if 'userid' exists and is not null
        if (!empty($res['userid'])) {
            $stmt_fetch_user = $conn_rjllearning->prepare("
                SELECT username, status, can_activate 
                FROM users 
                WHERE id = ?
            ");
            $stmt_fetch_user->execute([$res['userid']]);
            $user_info = $stmt_fetch_user->fetch(PDO::FETCH_ASSOC);
        } else {
            $user_info = null;
        }
        
        // Assign values or null if not found or user ID was null
        $fieldarray['username'] = $user_info['username'] ?? null;
        $fieldarray['status'] = $user_info['status'] ?? null;
        $fieldarray['can_activate'] = $user_info['can_activate'] ?? null;

        // $fieldarray['status'] = $user_info['status'] ?? null;
        // $fieldarray['can_activate'] = $user_info['can_activate'] ?? null;

        // Fetch staff access support ID
        $stmt_staff = $conn_rjllearning_specific->prepare("SELECT id FROM staff_access_support WHERE faculty_id = ?");
        $stmt_staff->execute([$res['id']]);
        $staff_result = $stmt_staff->fetch(PDO::FETCH_ASSOC);
        $fieldarray['staff_support_id'] = $staff_result['id'] ?? null;

        // Process profile image
        if (in_array('profile_image', $additionaldata)) {
            $fieldarray['profile_image'] = null;
            if (!empty($res['profile_image'])) {
                $profile_image_data = json_decode($res['profile_image'], true);
                $filePath = $profile_image_data['filepath'];
                $originalName = $profile_image_data['original_name'];
                $aws_profile_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                $fieldarray['profile_image'] = ["filepath" => $aws_profile_image, "original_name" => $originalName];
            }
        }

        // Process branch info
        if (in_array('branch_id', $additionaldata)) {
            $fieldarray['branch_id'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $res['branch_id']);
        }

        array_push($array_return, $fieldarray);
    }

    return [
        'total_rows' => count($filtered_faculty_ids), // Accurate count of filtered results
        'faculty_info' => $array_return,
        'maxreach' => count($result) < 50 ? 1 : 0
    ];
}
function storeUser($conn_rjllearning, $workspace_id, $institution_id, $name, $dob, $gender, $student_username, $email, $mobile, $password, $role, $branch_id = null)
{

    $sql = "INSERT INTO users(workspace_id,institution_id,branch_id,name,dob,gender,username,email,mobile,role,password,date_time_created,designation,status,can_activate)
    VALUES(?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?)";
    $stmt =  $conn_rjllearning->prepare($sql);
    $stmt->execute(array($workspace_id, $institution_id, $branch_id, $name, $dob, $gender, $student_username, $email, $mobile, $role, $password, time(), NULL, '1', '1'));
    $userid = $conn_rjllearning->lastInsertId();
    return $userid;
}


function updateUsers($conn_rjllearning, $workspace_id, $institution_id, $name, $dob, $gender, $email, $mobile, $user_id)
{
    $sql = "UPDATE users SET name=?,gender=?,email=?,mobile=?,dob=? WHERE id = ? and institution_id=? ";
    $conn_rjllearning->prepare($sql)->execute(array($name, $gender, $email, $mobile, $dob, $user_id, $institution_id));
    return true;
}

function fetch_specific_manage_teacher_alumni($conn_rjllearning_specific, $left_teacher_id, $institution_id)
{

    $sql_fetch = "SELECT * FROM left_teachers WHERE id = ? AND institution_id = ?";
    $array_params = [$left_teacher_id, $institution_id];

    try {
        $sql_fetch_stmt = $conn_rjllearning_specific->prepare($sql_fetch);
        $sql_fetch_stmt->execute($array_params);
        $left_teachers = $sql_fetch_stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($left_teachers)) {
            $response = [];
            $teacher_sql = "SELECT * FROM faculty_info WHERE institution_id = ? AND id = ?";
            foreach ($left_teachers as $alumni) {
                $teacher_id = $alumni['teacher_id'];

                $teacher_stmt = $conn_rjllearning_specific->prepare($teacher_sql);
                $teacher_stmt->execute([$institution_id, $teacher_id]);
                $teacher_result = $teacher_stmt->fetchAll(PDO::FETCH_ASSOC);

                if (empty($teacher_result)) {
                    errorreturn("No data found");
                }

                $teacher_id = $teacher_result[0]['id'];
                $teacher_name = $teacher_result[0]['name'];
                $teacher_father_name = $teacher_result[0]['father_name'];
                $teacher_email = $teacher_result[0]['email'];
                $gender = $teacher_result[0]['gender'];
                $teacher_mothername = $teacher_result[0]['mother_name'];
                $teacher_mobile = $teacher_result[0]['mobile'];
                $teacher_address = $teacher_result[0]['address'];
                $city = $teacher_result[0]['city'];
                $teacher_profile_image = $teacher_result[0]['profile_image'];
                $teacher_nationality = $teacher_result[0]['nationality'];
                $teacher_designation = $teacher_result[0]['designation'];
                $teacher_dob = $teacher_result[0]['dob'];
                $teacher_status = $teacher_result[0]['status'];

                if ($teacher_profile_image != null && !is_array($teacher_profile_image)) {
                    $data_attachment = json_decode($teacher_profile_image, true);
                    $filePath = $data_attachment['filepath'];
                    $originalName = $data_attachment['original_name'];
                    $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                    $teacher_profile_image = ["filepath" => $aws_attachment, "original_name" => $originalName];
                } else {
                    $teacher_profile_image = null;
                }

                $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $alumni['branch_id']);

                $personal_info = [
                    'teacher_id' => $teacher_id,
                    'name' => $teacher_name,
                    'fathername' => $teacher_father_name,
                    'mothername' => $teacher_mothername,
                    'mobile' => $teacher_mobile,
                    'gender' => $gender,
                    'email' => $teacher_email,
                    'teacher_profile_image' => $teacher_profile_image,
                    'city' => $city,
                    'address_line_1' => $teacher_address,
                    'nationality' => $teacher_nationality,
                    'designation' => $teacher_designation,
                    'dob' => $teacher_dob,
                    'status' => $teacher_status,
                ];

                $attachment_data = null;
                if (!empty($alumni['attachment']) && !is_array($alumni['attachment'])) {
                    $data_attachment = json_decode($alumni['attachment'], true);
                    if (!empty($data_attachment['filepath']) && !empty($data_attachment['original_name'])) {
                        $filePath = $data_attachment['filepath'];
                        $originalName = $data_attachment['original_name'];
                        $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                        $attachment_data = ["filepath" => $aws_attachment, "original_name" => $originalName];
                    }
                }

                $other_info = [
                    'date_of_leaving' => $alumni['date_of_leaving'],
                    'attachment' => $attachment_data,
                    'reason' => $alumni['reason'],
                    'remarks' => $alumni['remarks'],
                    'marked_by' => $alumni['marked_by'],
                    'marked_on' => $alumni['marked_on']
                ];

                $response = [
                    'left_teacher_id' => $alumni['id'],
                    'branch_info' => $branch_info,
                    'personal_info' => $personal_info,
                    'other_info' => $other_info,
                ];
            }
            return $response;
        } else {
            errorreturn("No alumni found for the given parameters.");
        }
    } catch (PDOException $e) {
        errorreturn("Database error: " . $e->getMessage());
    }
}

