<?php

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

    $sql_count = "SELECT  count(*) as total_rows FROM student_admission  where institution_id =?";
    $array_params = [$institution_id];

    if ($branch_id == 'all') {
        $sql_count .= "";
    } else if ($branch_id !=  null  && $branch_id != 'all') {
        $sql_count .= " AND branch_id = ? ";
        $array_params[] = $branch_id;
    }
    if ($search_string != null) {
        // $sql_count .= " AND (reg_no LIKE ? OR admission_number LIKE ? OR name LIKE ? OR mobile LIKE ? OR email LIKE ?)  ";
        $sql_count .= " AND (reg_no LIKE ? OR admission_number LIKE ? OR nationality LIKE ? OR city LIKE ?)  ";
        $array_params[] = '%' . $search_string . '%';
        $array_params[] = '%' . $search_string . '%';
        $array_params[] = '%' . $search_string . '%';
        $array_params[] = '%' . $search_string . '%';
        // $array_params[] = '%' . $search_string . '%';
    }
    $stmt = $conn_rjllearning_specific->prepare($sql_count);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];
    if ($limit == 'all') {
        $sql = "SELECT * FROM  student_admission WHERE institution_id = ?";
        $array_params = [$institution_id];
        if ($branch_id == 'all') {
            $sql .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }
        if ($search_string != null) {
            // $sql .= " AND (reg_no LIKE ? OR admission_number LIKE ? OR name LIKE ? OR mobile LIKE ? OR email LIKE ?)  ";
            $sql .= " AND (reg_no LIKE ? OR admission_number LIKE ? OR nationality LIKE ? OR city LIKE ?)  ";

            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            // $array_params[] = '%' . $search_string . '%';
        }
    } else {
        $sql = "SELECT * FROM  student_admission WHERE institution_id = ? ";
        $array_params = [$institution_id];
        if ($branch_id == 'all') {
            $sql .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }
        $sql .= " order by id desc, date_added desc ";
        $sql .= "LIMIT " . $offset . " ," . $limit . "";
    }
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute($array_params);
    $result = $stmt->fetchAll();
    $data = array();
    foreach ($result as $rows) {
        $class_info = getClass($conn_rjllearning_specific, $institution_id, $rows['class_admitted']);
        $section = getSection($conn_rjllearning_specific, $institution_id, $rows['section_admitted']);
        $branch_info =  getBranchInfo($conn_rjllearning_specific, $institution_id, $rows['branch_id']);

        if ($rows['last_tc'] != null and !is_array($rows['last_tc'])) {
            $last_tc_row = json_decode($rows['last_tc'], true);
            $filePath = $last_tc_row['filepath'];
            $originalName = $last_tc_row['original_name'];
            $aws_last_tc = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $last_tc_data   = array("filepath" => $aws_last_tc, "original_name" => $originalName);
        } else {
            $last_tc_data =  null;
        }

        if ($rows['profile_image'] != null and !is_array($rows['profile_image'])) {
            $profile_image_data = json_decode($rows['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);
            $final_array_profile   = array("filepath" => $aws_profile_image, "original_name" => $originalName);
        } else {
            $final_array_profile =  null;
        }

        if ($rows['birth_certificate'] != null and !is_array($rows['birth_certificate'])) {
            $birth_certificate_data = json_decode($rows['birth_certificate'], true);
            $filePath = $birth_certificate_data['filepath'];
            $originalName = $birth_certificate_data['original_name'];
            $aws_birth_certificate_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $final_array_birth_certificate  = array("filepath" => $aws_birth_certificate_image, "original_name" => $originalName);
        } else {
            $final_array_birth_certificate =  null;
        }

        if ($rows['nationality_proof'] != null and !is_array($rows['nationality_proof'])) {
            $nationality_proof_data = json_decode($rows['nationality_proof'], true);
            $filePath = $nationality_proof_data['filepath'];
            $originalName = $nationality_proof_data['original_name'];
            $aws_nationality_proof_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $final_array_nationality_proof  = array("filepath" => $aws_nationality_proof_image, "original_name" => $originalName);
        } else {
            $final_array_nationality_proof =  null;
        }

        // if ($rows['admission_profile_image'] != null and !is_array($rows['admission_profile_image'])) {
        //     $admission_profile_image_data = json_decode($rows['admission_profile_image'], true);
        //     $filePath = $admission_profile_image_data['filepath'];
        //     $originalName = $admission_profile_image_data['original_name'];
        //     $aws_admission_profile_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
        //     $final_array_admission_profile   = array("filepath" => $aws_admission_profile_image, "original_name" => $originalName);
        // } else {
        //     $final_array_admission_profile =  null;
        // }

        $data[] = array(
            'admission_id' => $rows['id'],
            'branch_id' => $branch_info,
            'reg_no' => $rows['reg_no'],
            'date_admission' => $rows['date_admission'],
            'admission_number' => $rows['admission_number'],
            'class_admitted' => $class_info,
            'section_admitted' => $section,
            'last_tc' => $last_tc_data,
            'last_school' => $rows['last_school'],
            'nationality' => $rows['nationality'],
            'nationality_proof_number' => $rows['nationality_proof_number'],
            'caste' => $rows['caste'],
            'birth_certificate' => $final_array_birth_certificate,
            'nationality_proof' => $final_array_nationality_proof,
            'dob' => $rows['dob'],
            'gender' => $rows['gender'],
            'blood_group' => $rows['blood_group'],
            'address_line_1' => $rows['address_line_1'],
            'city' => $rows['city'],
            'pincode' => $rows['pincode'],
            'date_added' => $rows['date_added'],
            'added_by' => $rows['added_by'],
            'profile_image' => $final_array_profile,
            'sibling_bool' => $rows['sibling_bool'],
            'sibling' => $rows['sibling'],
            'student_id' => $rows['student_id'],
        );
    }

    if (count($result) < 50) {

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

function fetchSpecificStudentAdmission($conn_rjllearning_specific, $admission_id, $institution_id)
{

    $res = $conn_rjllearning_specific->prepare("SELECT * FROM student_admission WHERE id = ? AND institution_id = ?  ");
    $res->execute(array($admission_id, $institution_id));
    $result = $res->fetchAll();

    if (count($result) == 1) {

        $class_info = getClass($conn_rjllearning_specific, $institution_id, $result[0]['class_admitted']);
        $section = getSection($conn_rjllearning_specific, $institution_id, $result[0]['section_admitted']);
        $branch_info =  getBranchInfo($conn_rjllearning_specific, $institution_id, $result[0]['branch_id']);

        if ($result[0]['last_tc'] != null and !is_array($result[0]['last_tc'])) {
            $last_tc_row = json_decode($result[0]['last_tc'], true);
            $filePath = $last_tc_row['filepath'];
            $originalName = $last_tc_row['original_name'];
            $aws_last_tc = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $last_tc_data   = array("filepath" => $aws_last_tc, "original_name" => $originalName);
        } else {
            $last_tc_data =  null;
        }

        if ($result[0]['profile_image'] != null and !is_array($result[0]['profile_image'])) {
            $profile_image_data = json_decode($result[0]['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);
            $final_array_profile   = array("filepath" => $aws_profile_image, "original_name" => $originalName);
        } else {
            $final_array_profile =  null;
        }

        if ($result[0]['birth_certificate'] != null and !is_array($result[0]['birth_certificate'])) {
            $birth_certificate_data = json_decode($result[0]['birth_certificate'], true);
            $filePath = $birth_certificate_data['filepath'];
            $originalName = $birth_certificate_data['original_name'];
            $aws_birth_certificate_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $final_array_birth_certificate  = array("filepath" => $aws_birth_certificate_image, "original_name" => $originalName);
        } else {
            $final_array_birth_certificate =  null;
        }

        // if ($result[0]['nationality_proof'] != null and !is_array($result[0]['nationality_proof'])) {
        //     $nationality_proof_data = json_decode($result[0]['nationality_proof'], true);
        //     $filePath = $nationality_proof_data['filepath'];
        //     $originalName = $nationality_proof_data['original_name'];
        //     $aws_nationality_proof_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
        //     $final_array_nationality_proof  = array("filepath" => $aws_nationality_proof_image, "original_name" => $originalName);
        // } else {
        //     $final_array_nationality_proof =  null;
        // }
        
        if ($result[0]['nationality_proof'] != null && !is_array($result[0]['nationality_proof'])) {
            $nationality_proof_data = json_decode($result[0]['nationality_proof'], true);
            if (!empty($nationality_proof_data['filepath'])) {
                $filePath = $nationality_proof_data['filepath'];
                $originalName = $nationality_proof_data['original_name'];
                $aws_nationality_proof_image = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                $final_array_nationality_proof  = array("filepath" => $aws_nationality_proof_image, "original_name" => $originalName);
            } else {
                $final_array_nationality_proof = null;
            }
        } else {
                $final_array_nationality_proof = null;
        }


        $sibling = array();  // Initialize the sibling array
        
        if ($result[0]['sibling_bool'] == 1) {
            $sibling_bool = $result[0]['sibling_bool'];

            // Retrieve sibling admission numbers from the result
            $sibling_ids = $result[0]['sibling'];

            // Prepare the SQL query to fetch sibling details
            $sqlStudents = "SELECT id, admission_number, name FROM students WHERE institution_id = ? AND branch_id = ? AND admission_number IN ($sibling_ids)";
            $array_params = [$institution_id, $result[0]['branch_id']];
            $stmtStudents = $conn_rjllearning_specific->prepare($sqlStudents);
            $stmtStudents->execute($array_params);
            $result_students = $stmtStudents->fetchAll();
            foreach ($result_students as $rows) {
                $sibling[] = array(
                    'id' => $rows['id'],
                    'admission_number' => $rows['admission_number'],
                    'name' => $rows['name']
                );
            }
        } else {
            // Reset sibling_bool and sibling to null if no siblings are found
            $sibling_bool = 0;
            $sibling = null;
        }
        
        $student = "SELECT name, email, mobile FROM students WHERE institution_id = ? AND branch_id = ? AND id = ?";
            $array_params = [$institution_id, $result[0]['branch_id'], $result[0]['student_id']];
            $stmtstudent = $conn_rjllearning_specific->prepare($student);
            $stmtstudent->execute($array_params);
            $result_student = $stmtstudent->fetchAll();
            
            $student_name = $result_student[0]['name'];
            $student_email = $result_student[0]['email'];
            $student_mobile = $result_student[0]['mobile'];

        return  array(
            'admission_id' => $result[0]['id'],
            'branch_id' => $branch_info,
            'reg_no' => $result[0]['reg_no'],
            'date_admission' => $result[0]['date_admission'],
            'admission_number' => $result[0]['admission_number'],
            'class_admitted' => $class_info,
            'section_admitted' => $section,
            'last_tc' => $last_tc_data,
            'last_school' => $result[0]['last_school'],
            'nationality' => $result[0]['nationality'],
            'nationality_proof_number' => $result[0]['nationality_proof_number'],
            'caste' => $result[0]['caste'],
            'birth_certificate' => $final_array_birth_certificate,
            'nationality_proof' => $final_array_nationality_proof,
            'dob' => $result[0]['dob'],
            'gender' => $result[0]['gender'],
            'blood_group' => $result[0]['blood_group'],
            'address_line_1' => $result[0]['address_line_1'],
            'city' => $result[0]['city'],
            'pincode' => $result[0]['pincode'],
            'date_added' => $result[0]['date_added'],
            'added_by' => $result[0]['added_by'],
            'profile_image' => $final_array_profile,
            'sibling_bool' => $sibling_bool,
            'sibling' => $sibling,
            'name' => $student_name,
            'email' => $student_email,
            'mobile' => $student_mobile
        );

    } else {

        errorreturn("Student admission not found. Contact admin.");
    }
}