<?php
function getStudentInfo($conn_rjllearning_specific, $institution_id, $student_id)
{

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


function getStudentsForNotification($conn_rjllearning_specific, $institution_id, $branch_id, $class_id = null, $section_id = null, $student_id = null, $additionaldata = null)
{

    try {
        // Initialize the base query
        $sql = "SELECT ";

        // Add the columns dynamically
        if ($additionaldata && is_array($additionaldata)) {
            $sql .= implode(", ", $additionaldata);
        } else {
            $sql .= "*"; // Default to selecting all columns if no additional data is provided
        }

        $sql .= " FROM students WHERE institution_id = ?  AND can_activate = 1";

        // Initialize the array of parameters
        $array_params = [$institution_id];
        if ($branch_id !== null) {
            if ($branch_id === 'all') {
                // If 'all', match all branch_ids (no condition needed)
            } else {
                $branch_id = json_decode($branch_id, true);
                if (is_array($branch_id)) {
                    // Format branch_id values for the IN clause
                    $imploded_branch_ids = implode(",", array_map('intval', $branch_id)); // Convert each to integer for security
                    $sql .= " AND branch_id IN ($imploded_branch_ids)";
                } else {
                    // If branch_id is a single value, handle it as such
                    $sql .= " AND branch_id = ?";
                    $array_params[] = $branch_id;
                }
            }
        }

        // Add class_id to the query if provided
        if ($class_id !== null) {
            // Check if the value is 'all', in which case we include all class_ids
            if ($class_id === 'all') {
                // If 'all', don't add the class_id condition to the query
            } else {
                $sql .= " AND class_id = ?";
                $array_params[] = $class_id;
            }
        }


        // Add section_id to the query if provided
        if ($section_id !== null) {

            if ($section_id === 'all') {
                // If 'all', don't add the section_id condition to the query
            } else {
                $section_id = json_decode($section_id, true);
                if (is_array($section_id)) {

                    // Format section_id values for the IN clause
                    $imploded_section_ids = implode(",", array_map('intval', $section_id)); // Convert each to integer for security
                    $sql .= " AND section_id IN ($imploded_section_ids)";
                } else {

                    // If section_id is a single value, handle it as such
                    $sql .= " AND section_id = ?";
                    $array_params[] = $section_id;
                }
            }
        }
        // Add student_id to the query if provided
        if (!empty($student_id)) {

            if ($student_id === 'all') {
                // If 'all', don't add the student_id condition to the query
            } else {
                // Ensure $student_id is treated as an array
                if (!is_array($student_id)) {
                    $student_id = [$student_id]; // Convert to an array if it's a single value
                }

                // Format student IDs for the IN clause
                $imploded_student_ids = implode(",", array_map(function ($id) {
                    return "'" . (int)$id . "'"; // Safely ensure each student_id is treated as an integer and quoted
                }, $student_id));

                $sql .= " AND id IN ($imploded_student_ids)";
            }
        }
        // Order the results by name
        $sql .= " ORDER BY name ASC";

        // Prepare and execute the SQL query
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute($array_params);

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

        // Return the list of students, or an empty array if no students were found
        return $students;
    } catch (PDOException $e) {
        // Handle database errors (but allow process to continue)
        return []; // Return an empty array instead of error if database issues occur
    }
}




function GetBasicStudentsDetail($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $search_string, $limit, $offset, $additionaldata, $check_academic_confirmation_id = null)
{
    $sql_count = "SELECT COUNT(id) AS total_rows FROM students WHERE  institution_id = ? and can_activate = 1";
    $array_params = [$institution_id];

    if ($check_academic_confirmation_id == 'all' or $check_academic_confirmation_id == null) {
        $sql_count .= "";
    } else if ($check_academic_confirmation_id !=  null  && $check_academic_confirmation_id != 'all') {
        $sql_count .= " and  academic_id = ? ";
        $array_params[] = $check_academic_confirmation_id;
    }

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

    $sql_append_data = facultyFilterQueryAppend($sql_count, $array_params, $GLOBALS['function_type'], $GLOBALS['class_section_subjects'], $GLOBALS['class_incharge_roles'], $GLOBALS['staff_access_modules'], $class_id, $section_id, null, $GLOBALS['conn_rjllearning'], 1, 1, 1, 1, true);
    $sql_count = $sql_append_data['sql']; // Get the modified SQL
    $array_params = $sql_append_data['array_params'];


    $validate_student = $conn_rjllearning_specific->prepare($sql_count);
    $validate_student->execute($array_params);

    $result_total = $validate_student->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

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

        $sql .= " FROM students";
        $sql .= " WHERE institution_id = ? and can_activate = 1";

        $array_params = [$institution_id];

        if ($check_academic_confirmation_id == 'all' or $check_academic_confirmation_id == null) {
            $sql .= "";
        } else if ($check_academic_confirmation_id !=  null  && $check_academic_confirmation_id != 'all') {
            $sql .= " and  academic_id = ? ";
            $array_params[] = $check_academic_confirmation_id;
        }
        if ($branch_id == 'all') {
            $sql .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }

        $sql_append_data = facultyFilterQueryAppend($sql, $array_params, $GLOBALS['function_type'], $GLOBALS['class_section_subjects'], $GLOBALS['class_incharge_roles'], $GLOBALS['staff_access_modules'], $class_id, $section_id, null, $GLOBALS['conn_rjllearning'], 1, 1, 1, 1, true);
        $sql = $sql_append_data['sql']; // Get the modified SQL
        $array_params = $sql_append_data['array_params'];

        if ($search_string != null) {
            $sql .= " AND (name LIKE ? OR email LIKE ? OR fathername LIKE ? OR mothername LIKE ?)";
            $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 .= $column_name;
            } else {
                $sql .= ", " . $column_name;
            }
            $array_counter++;
        }

        $sql .= " FROM students";
        $sql .= " WHERE institution_id = ? and can_activate = 1 ";

        $array_params = [$institution_id];

        if ($check_academic_confirmation_id == 'all' or $check_academic_confirmation_id == null) {
            $sql .= "";
        } else if ($check_academic_confirmation_id !=  null  && $check_academic_confirmation_id != 'all') {
            $sql .= " and  academic_id = ? ";
            $array_params[] = $check_academic_confirmation_id;
        }
        if ($branch_id == 'all') {
            $sql .= "";
        } else if ($branch_id !=  null  and $branch_id != 'all') {
            $sql .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }

        $sql_append_data = facultyFilterQueryAppend($sql, $array_params, $GLOBALS['function_type'], $GLOBALS['class_section_subjects'], $GLOBALS['class_incharge_roles'], $GLOBALS['staff_access_modules'], $class_id, $section_id, null, $GLOBALS['conn_rjllearning'], 1, 1, 1, 1);
        $sql = $sql_append_data['sql']; // Get the modified SQL
        $array_params = $sql_append_data['array_params'];

        if ($search_string != null) {
            $sql .= " AND (name LIKE ? OR email LIKE ? OR fathername LIKE ? OR mothername LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
        }
        $sql .= " order by name asc , fathername asc";
        $sql .= " LIMIT " . $offset . " ," . $limit . "";
    }

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

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

            if ($column_name === 'birth_certificate') {

                $fieldarray['birth_certificate'] = $res[$column_name];
                if ($fieldarray['birth_certificate'] !=  null) {
                    $birth_certificate_data = json_decode($res['birth_certificate'], true);
                    $filePath = $birth_certificate_data['filepath'];
                    $originalName = $birth_certificate_data['original_name'];
                    $aws_birth_certificate = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                    $fieldarray['birth_certificate']   = array("filepath" => $aws_birth_certificate, "original_name" => $originalName);
                } else {
                    $fieldarray['birth_certificate'] =  null;
                }
            } elseif ($column_name === 'nationality_proof') {

                $fieldarray['nationality_proof'] = $res[$column_name];
                if ($fieldarray['nationality_proof'] !=  null) {
                    $proof_data = json_decode($res['nationality_proof'], true);
                    $filePath = $proof_data['filepath'];
                    $originalName = $proof_data['original_name'];
                    $aws_nationality_proof = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                    $fieldarray['nationality_proof']    = array("filepath" => $aws_nationality_proof, "original_name" => $originalName);
                } else {
                    $fieldarray['nationality_proof'] =  null;
                }
            } elseif ($column_name === 'student_profile_image') {

                $fieldarray['student_profile_image'] = $res[$column_name];
                if ($fieldarray['student_profile_image'] !=  null) {
                    $profile_image_data = json_decode($res['student_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);
                    //   print_r($aws_profile_image);
                    $fieldarray['student_profile_image']   = $aws_profile_image;
                } else {
                    $fieldarray['student_profile_image'] =  null;
                }
            } elseif ($column_name === 'class_id') {
                $fieldarray['class_id'] = getClass($conn_rjllearning_specific, $institution_id, $res[$column_name]);
            } elseif ($column_name === 'section_id') {
                $fieldarray['section_id'] = getSection($conn_rjllearning_specific, $institution_id, $res[$column_name]);
            } elseif ($column_name === 'branch_id') {
                
                $fieldarray['branch_id'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $res[$column_name]);
            } else {
                // For all other columns
                $fieldarray[$column_name] = $res[$column_name];
            }
        }
        array_push($array_return, $fieldarray);
    }

    //return $array_return;
    if (count($result) < 50) {

        return array('total_rows' => $totalrows, 'students_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'students_info' => $array_return, 'maxreach' => 0);
    }
}
