<?php
function admitStudent(
    $conn_rjllearning,
    $conn_rjllearning_specific,
    $workspace_id,
    $institution_id,
    $branch_id,
    $reg_no,
    $date_admission,
    $class_admitted,
    $section_admitted,
    $last_school,
    $nationality,
    $nationality_proof_number,
    $caste,
    $dob,
    $gender,
    $blood_group,
    $address_line_1,
    $city,
    $pincode,
    $added_by,
    $name,
    $father_name,
    $mother_name,
    $mobile,
    $email,
    $admission_number,
    $academic_id,
    $father_occupation = null,
    $mother_occupation = null,
    $profile_image = null,
    $sibling_bool = 0,
    $sibling = null
) {
    try {

        global $s3_learning_bucket;
        global $learning_bucketName;
        $sourceBucket =" ";

        // $conn_rjllearning_specific->beginTransaction();

        // Generate unique admission number
        // $stmt = $conn_rjllearning_specific->prepare("SELECT MAX(admission_number) FROM student_admission WHERE institution_id = ? AND branch_id = ?");
        // $stmt->execute([$institution_id, $branch_id]);
        // $admission_number = intval($stmt->fetchColumn()) + 1;

        // // Ensure unique admission number
        // $stmt_check = $conn_rjllearning_specific->prepare("SELECT COUNT(*) FROM student_admission WHERE institution_id = ? AND branch_id = ? AND admission_number = ?");
        // while (true) {
        //     $stmt_check->execute([$institution_id, $branch_id, $admission_number]);
        //     if ($stmt_check->fetchColumn() == 0) break;
        //     $admission_number++;
        // }

        if (!checkOnlyCharacters($name)) { // 6 digit only
            //eg House Name, Street Name
            errorreturn('Name must only contain letters!"');
        }

        if (!CheckEMailIsValidOrNot($email)) {
            errorreturn('Enter valid Email Id');
        }

        if (strlen($mobile) === 10 && is_numeric($mobile)) {
            $mobile = '+91' . $mobile;
        }
        if (strlen($mobile) != 13 or $mobile <= 0) {
            errorreturn('Enter valid 12 digit mobile number ' . $mobile);
        }

        $gender = strtolower(trim($gender));

        if ($gender === 'male') {
            $gender = 'M';
        } elseif ($gender === 'female') {
            $gender = 'F';
        } else {
            errorreturn('Invalid gender details. Must be Male or Female');
        }


        $date_now = date("Y-m-d");

        // Trim and validate DOB
        $dob = trim($dob);

        // Step 1: Try multiple formats to convert DOB to Y-m-d
        $formats = ['d/m/Y', 'd-m-Y', 'Y-m-d'];
        $dob_obj = false;

        foreach ($formats as $format) {
            $dob_obj = DateTime::createFromFormat($format, $dob);
            if ($dob_obj) {
                // NOTE: remove strict === check on format match
                break;
            }
        }

        if (!$dob_obj) {
            errorreturn('Invalid DOB format. Use dd/mm/yyyy, dd-mm-yyyy, or yyyy-mm-dd. Given: ' . $dob);
        }

        $dob_formatted = $dob_obj->format('Y-m-d');

        // Step 2: Ensure DOB is not a future date
        if ($dob_formatted >= $date_now) {
            errorreturn('Invalid DOB. Date of birth cannot be today or a future date.');
        }

        // Step 3: Minimum age check (e.g., must be at least 2 years old)
        $dobDate = DateTime::createFromFormat('Y-m-d', $dob_formatted);
        $currentDate = new DateTime();
        $age = $dobDate->diff($currentDate)->y;

        // if ($age < 2) {
        //     errorreturn('Invalid DOB. Age must be at least 2 years.');
        // }

        // Assign formatted DOB for storage
        $dob = $dob_formatted;


        //student address
        // if (!CheckAddressValidation($address_line_1)) {
        //     //eg House Name, Street Name
        //     errorreturn('Invalid Address');
        // }

        //Branch Pin Code Validation 
        // if (!PinCodeValidation($pincode)) { // 6 digit only
        //     //eg House Name, Street Name
        //     errorreturn('Invalid Pin Code .');
        // }

        // Generate roll number
        $stmt_roll = $conn_rjllearning_specific->prepare("SELECT MAX(roll_no) FROM students WHERE institution_id = ? AND branch_id = ?");
        $stmt_roll->execute([$institution_id, $branch_id]);
        $roll_no = intval($stmt_roll->fetchColumn()) + 1;

        // Validate registration
        $stmt_reg = $conn_rjllearning_specific->prepare("SELECT id FROM student_registration WHERE institution_id = ? AND branch_id = ? AND reg_no = ?");
        $stmt_reg->execute([$institution_id, $branch_id, trim($reg_no)]);
        $reg_data = $stmt_reg->fetch(PDO::FETCH_ASSOC);
        if (!$reg_data)
            errorreturn("Registration number '$reg_no' not found.");
        $reg_id = $reg_data['id'];

        // Handle image


        // Initial insert into student_admission
        $temp_student_id = "{$institution_id}_{$branch_id}_{$reg_id}";
        $stmt_insert = $conn_rjllearning_specific->prepare("
            INSERT INTO student_admission (
                institution_id, branch_id, reg_no, date_admission, admission_number,
                class_admitted, section_admitted, last_tc, last_school,
                nationality, nationality_proof_number, caste,
                birth_certificate, nationality_proof, dob, gender, blood_group,
                address_line_1, city, pincode, date_added, added_by,
                profile_image, sibling_bool, sibling, student_id
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, NULL, NULL, ?, ?, ?,
                ?, ?, ?, NOW(), ?, ?, ?, ?, ?
            )
        ");
        $stmt_insert->execute([
            $institution_id,
            $branch_id,
            $reg_no,
            $date_admission,
            $admission_number,
            $class_admitted,
            $section_admitted,
            $last_school,
            $nationality,
            $nationality_proof_number,
            $caste,
            $dob,
            $gender,
            $blood_group,
            $address_line_1,
            $city,
            $pincode,
            $added_by,
            null,
            $sibling_bool,
            $sibling,
            $temp_student_id
        ]);
        $admission_id = $conn_rjllearning_specific->lastInsertId();

        // Username generation
        $institution_info = getInstitution($conn_rjllearning, $institution_id);
        $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id, ['branch_code']);
        $student_username = $branch_info['branch_code'] . $admission_number . "@" . $institution_info['institution_code'];

        $error_file_path = 'username_skip.txt';
        $original_username = $student_username;
        $suffix = 1;

        $query = "SELECT COUNT(*) AS cnt FROM users WHERE username = :username";
        $stmt = $conn_rjllearning->prepare($query);
        $stmt->execute([':username' => $student_username]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        while ($row && $row['cnt'] > 0) {
            // Log the original username in the error file (only once)
            if ($suffix === 1) {
                file_put_contents($error_file_path, "Username already exists: $student_username  / / / $admission_number\n", FILE_APPEND);
            }

            // Append suffix to username
            $student_username = $original_username . '_' . $suffix;
            $suffix++;

            // Check again with new username
            $stmt = $conn_rjllearning->prepare($query);
            $stmt->execute([':username' => $student_username]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
        }

        // Now $student_username holds a unique value, and you can proceed


        // Students table insert
        $sql = "INSERT INTO students(
                    institution_id, branch_id, admission_id, academic_id, nationality, caste, admission_number,
                    class_id, section_id, roll_no, username, student_profile_image,
                    birth_certificate, nationality_proof, nationality_proof_number,
                    name, fathername, mothername, mobile, email, address_line_1, city,
                    pincode, dob, gender, blood_group, date_admission, date_added,
                    added_by, user_id, can_activate, status, father_occupation, mother_occupation
                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([
            $institution_id,
            $branch_id,
            $reg_id,
            $academic_id,
            $nationality,
            $caste,
            $admission_number,
            $class_admitted,
            $section_admitted,
            $roll_no,
            $student_username,
            null,
            null,
            null,
            $nationality_proof_number,
            $name,
            $father_name,
            $mother_name,
            $mobile,
            $email,
            $address_line_1,
            $city,
            $pincode,
            $dob,
            $gender,
            $blood_group,
            $date_admission,
            date('Y-m-d'),
            $added_by,
            $added_by,
            0,
            1,
            $father_occupation,
            $mother_occupation
        ]);

        $student_id = $conn_rjllearning_specific->lastInsertId();

        // Update student_admission with final student_id
        $sql = "UPDATE student_admission SET student_id = ? WHERE institution_id = ? AND student_id = ?";
        $conn_rjllearning_specific->prepare($sql)->execute([$student_id, $institution_id, $temp_student_id]);

        // Create user
        // Assuming $dob and $final_admission_number are already defined
        $raw_password = $dob . $admission_number . '%';
        
        // Hash the password for secure storage
        $hashed_password = password_hash($raw_password, PASSWORD_DEFAULT);

        // $userid = storeUser($conn_rjllearning, $workspace_id, $institution_id, $name, $dob, $gender, $student_username, $email, $mobile, $hashed_password, 'student', $branch_id);
        $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, 'student', $hashed_password, time(), NULL, '0', '0'));
        $userid = $conn_rjllearning->lastInsertId();

        // Update students table with user ID
        $conn_rjllearning_specific->prepare("UPDATE students SET user_id = ? WHERE id = ? AND institution_id = ?")
            ->execute([$userid, $student_id, $institution_id]);

        // Update registration
        $reg_status = 2;
        $conn_rjllearning_specific->prepare("UPDATE student_registration SET name = ?, fathername = ?, mothername = ?, mobile = ?, email = ?, address_line_1 = ?, city = ?, pincode = ?, dob = ?, gender = ?, status = ?, student_id = ? WHERE id = ? AND institution_id = ?")
            ->execute([$name, $father_name, $mother_name, $mobile, $email, $address_line_1, $city, $pincode, $dob, $gender, $reg_status, $student_id, $reg_id, $institution_id]);


        // if (isset($profile_image) && !empty($profile_image) && file_exists($profile_image)) {
        //     $file_extension = strtolower(pathinfo($profile_image, PATHINFO_EXTENSION));
        //     $file_size = filesize($profile_image);
        //     $file_mime = mime_content_type($profile_image);

        //     $maxFileSize = 2 * 1024 * 1024;


        // }


        if (isset($profile_image) && !empty($profile_image)) {
            copyImageFromAnotherBucketAndUpdate($conn_rjllearning_specific, $s3_learning_bucket, $learning_bucketName, $profile_image, $workspace_id, $institution_id, $student_id, $sourceBucket);
        }

        // if (isset($profile_image) && !empty($profile_image)) {
        //     // Define image name and path for storage in S3 (for students table)
        //     $image_name3 = "students_profile_image_" . $student_id . "." . $file_extension;
        //     $directoryPathMain = "user_uploads/{$workspace_id}/{$institution_id}/students/{$currentYear}/{$currentMonth}/";
        //     $student_profile_path = $directoryPathMain . $image_name3;

        //     // Upload the image to S3
        //     $fakeFile = ['tmp_name' => $profile_image];
        //     awsS3UserUploadInsert($s3_learning_bucket, $learning_bucketName, $fakeFile, $student_profile_path, false, 0);

        //     // Prepare attachment data
        //     $attachment_image = json_encode([
        //         "filepath" => $student_profile_path,
        //         "original_name" => $_FILES['profile_image']['name'] ?? basename($profilephoto)
        //     ]);

        //     // Update database for students
        //     $sql_logo = "UPDATE students SET student_profile_image = ? WHERE id = ?";
        //     $conn_rjllearning_specific->prepare($sql_logo)->execute([$attachment_image, $student_id]);
        // }

        // // Finally, clean up once
        // if (isset($profile_image) && !empty($profile_image) && file_exists($profile_image)) {
        //     unlink($profile_image);
        // }

        cacheAcademicStudentData($conn_rjllearning_specific, $institution_id, $branch_id, $class_admitted, $section_admitted, $student_id, $date_admission);

        // $conn_rjllearning_specific->commit();
        return $student_id;

    } catch (Exception $e) {
        //  $conn_rjllearning_specific->rollBack();
        logDetailedError($e, 'Left Student Import');
        errorreturn(['message' => $e->getMessage()]);
    }
}


function insert_StudentRegistration(
    $conn_rjllearning_specific,
    $institution_id,
    $branch_id,
    $reg_no,
    $name,
    $fathername,
    $mothername,
    $mobile,
    $email,
    $address_line_1,
    $city,
    $pincode,
    $dob,
    $gender,
    $for_class,
    $last_school,
    $current_class,
    $sibling_bool,
    $student_id,
    $date_added,
    $username,
    $status,
    $date_now,
    $reg_fees = null,
    $paid_through = null,
    $refrence_number = null,
    $sibling = null
) {
    try {
        if (!checkOnlyCharacters($name)) { // 6 digit only
            //eg House Name, Street Name
            errorreturn('Name must only contain letters!"');
        }

        if (!CheckEMailIsValidOrNot($email)) {
            errorreturn('Enter valid Email Id');
        }
        if (strlen($mobile) === 10 && is_numeric($mobile)) {
            $mobile = '+91' . $mobile;
        }
        if (strlen($mobile) != 13 or $mobile <= 0) {
            errorreturn('Enter valid 12 digit mobile number ' . $mobile);
        }

        $gender = strtolower(trim($gender));

        if ($gender === 'male') {
            $gender = 'M';
        } elseif ($gender === 'female') {
            $gender = 'F';
        } else {
            errorreturn('Invalid gender details. Must be Male or Female');
        }


        $date_now = date("Y-m-d");

        // Trim DOB
        $dob = trim($dob);

        // Try multiple date formats
        $formats = ['d/m/Y', 'd-m-Y', 'Y-m-d'];
        $dob_obj = false;

        foreach ($formats as $format) {
            $dob_obj = DateTime::createFromFormat($format, $dob);
            if ($dob_obj) {
                break;
            }
        }

        if (!$dob_obj) {
            errorreturn('Invalid DOB format. Use dd/mm/yyyy, dd-mm-yyyy, or yyyy-mm-dd. Given1: ' . $dob);
        }

        $dob_formatted = $dob_obj->format('Y-m-d');

        // Ensure DOB is not a future date
        if ($dob_formatted >= $date_now) {
            errorreturn('Invalid DOB. Date of birth cannot be today or a future date.');
        }

        // Minimum age check (e.g., at least 2 years old)
        $dobDate = DateTime::createFromFormat('Y-m-d', $dob_formatted);
        $currentDate = new DateTime();
        $age = $dobDate->diff($currentDate)->y;

        // if ($age < 2) {
        //     errorreturn('Invalid DOB. Age must be at least 2 years.');
        // }

        // Assign formatted DOB for storage
        $dob = $dob_formatted;


        //student address
        // if (!CheckAddressValidation($address_line_1)) {
        //     //eg House Name, Street Name
        //     errorreturn('Invalid Address');
        // }

        //Branch Pin Code Validation 
        // if (!PinCodeValidation($pincode)) { // 6 digit only
        //     //eg House Name, Street Name
        //     errorreturn('Invalid Pin Code');
        // }

        if ($reg_fees != null) {
            if (!is_numeric($reg_fees)) {
                // Handle error - reg_fees is not a valid numeric value
                errorreturn('Registration fees is not a valid numeric value');
            }
        }

        // Validate $paid_through as either "bank" or "cash"
        if ($paid_through != null) {
            if ($paid_through !== "bank" && $paid_through !== "cash" && $paid_through !== "cheque" && $paid_through !== "net_banking" && $paid_through !== "credit_card" && $paid_through !== "UPI") {
                // Handle error - paid_through is not "bank" or "cash"
                errorreturn('Invalid registration fees is paid through');
            }
        }

        if ($refrence_number != null) {
            if (strlen($refrence_number) > 16) {
                // Handle error - reference_number exceeds the maximum length
                errorreturn('Reference number cannot exceed 16 characters.');
            }
        }

        if (!CheckDob($dob) or $dob >= $date_now) {
            errorreturn('Invalid DOB formats');
        }
        // //student address
        // if (!CheckAddressValidation($address_line_1)) {
        //     //eg House Name, Street Name
        //     errorreturn('Invalid Address');
        // }

        //Branch Pin Code Validation 
        // if (!PinCodeValidation($pincode)) { // 6 digit only
        //     //eg House Name, Street Name
        //     errorreturn('Invalid Pin Code');
        // }

        if ($reg_fees != null) {
            if (!is_numeric($reg_fees)) {
                // Handle error - reg_fees is not a valid numeric value
                errorreturn('Registration fees is not a valid numeric value');
            }
        }

        // Validate $paid_through as either "bank" or "cash"
        if ($paid_through != null) {
            if ($paid_through !== "bank" && $paid_through !== "cash" && $paid_through !== "cheque" && $paid_through !== "net_banking" && $paid_through !== "credit_card" && $paid_through !== "UPI") {
                // Handle error - paid_through is not "bank" or "cash"
                errorreturn('Invalid registration fees is paid through');
            }
        }

        if ($refrence_number != null) {
            if (strlen($refrence_number) > 16) {
                // Handle error - reference_number exceeds the maximum length
                errorreturn('Reference number cannot exceed 16 characters.');
            }
        }

        $sql = "INSERT INTO student_registration (institution_id, branch_id, reg_no, name, fathername, mothername, mobile, email, address_line_1, 
                city, pincode, dob, gender, reg_fees, paid_through, refrence_number, for_class, last_school, current_class, sibling_bool, sibling,
                student_id, date_added, added_by, status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([
            $institution_id,
            $branch_id,
            $reg_no,
            $name,
            $fathername,
            $mothername,
            $mobile,
            $email,
            $address_line_1,
            $city,
            $pincode,
            $dob,
            $gender,
            $reg_fees,
            $paid_through,
            $refrence_number,
            $for_class,
            $last_school,
            $current_class,
            $sibling_bool,
            $sibling,
            $student_id,
            date('Y-m-d'),
            $username,
            1
        ]);

        return $reg_no;
        // successreturn("Student registered successfully .$reg_no");
    } catch (PDOException $e) {
        errorreturn("Database error: " . $e->getMessage());
    }
}







function createManageAlumni(
    $conn_rjllearning_specific,
    $conn_rjllearning,
    $workspace_id,
    $institution_id,
    $branch_id,
    $class_id,
    $section_id,
    $student_id,
    $nationality,
    $caste,
    $date_admission,
    $dob,
    $subject_studied,
    $last_class,
    $promotion_higher_class,
    $paid,
    $fee_concession,
    $working_days,
    $board,
    $ncc,
    $conduct,
    $present_days,
    $class_in_figure,
    $extra_curricular_activities,
    $date_application_certificate,
    $date_issue_certificate,
    $reasons_leaving_school,
    $remarks,
    $username
) {


    // $conn_rjllearning_specific->beginTransaction();

    $check = $conn_rjllearning_specific->prepare("SELECT id FROM left_students WHERE institution_id = ? AND branch_id = ? AND class_id = ? AND section_id = ? AND student_id = ?");
    $check->execute([$institution_id, $branch_id, $class_id, $section_id, $student_id]);
    if ($check->rowCount() > 0) {
        errorreturn("Alumni already exists.");
    }

    $fetch = $conn_rjllearning_specific->prepare("SELECT user_id, admission_number FROM students WHERE institution_id = ? AND id = ?");
    $fetch->execute([$institution_id, $student_id]);
    $result = $fetch->fetch();
    if (!$result) {
        // $conn_rjllearning_specific->rollBack();
        errorreturn("Student not found.");
    }

    $student_userid = $result['user_id'];
    $admission_number = $result['admission_number'];

    // Step 1: Check if the class with the given ID exists
    $stmt_check = $conn_rjllearning_specific->prepare("
        SELECT id FROM class_info 
        WHERE classname = ? 
        AND institution_id = ? 
        AND (branch_id LIKE ? OR branch_id = 'all')
    ");
    $stmt_check->execute([$last_class, $institution_id, "%$branch_id%"]);
    
    if ($stmt_check->rowCount() > 0) {
        // Class exists, fetch its ID
        $last_class = $stmt_check->fetchColumn();  // Use the existing class ID
    } else {
        // Class not found, do NOT insert, just use null
        $last_class = null;
    }


    // Step 1: Check if the promotion higher class with the given ID exists
    $stmt_check = $conn_rjllearning_specific->prepare("
        SELECT id FROM class_info 
        WHERE classname = ? 
        AND institution_id = ? 
        AND (branch_id LIKE ? OR branch_id = 'all')
    ");
    $stmt_check->execute([$promotion_higher_class, $institution_id, "%$branch_id%"]);
    
    if ($stmt_check->rowCount() > 0) {
        // Class exists, fetch its ID
        $promotion_higher_class = $stmt_check->fetchColumn();  // Use the existing class ID
    } else {
        // Class not found, do NOT insert, just use null
        $promotion_higher_class = null;
    }




    $insert_query = "INSERT INTO left_students (
        institution_id, branch_id, class_id, section_id, student_id, nationality, caste, date_admission, dob,subject_studied, last_class, promotion_higher_class, paid, fee_concession,
        working_days, board, ncc, conduct, present_days, class_in_figure, extra_curricular_activities,
        date_application_certificate, date_issue_certificate, reasons_leaving_school, remarks, added_by, admission_number
    ) VALUES (
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
    )";

    $stmt = $conn_rjllearning_specific->prepare($insert_query);
    $subject_studied_json = json_encode($subject_studied);
    $stmt->execute([
        $institution_id,
        $branch_id,
        $class_id,
        $section_id,
        $student_id,
        $nationality,
        $caste,
        $date_admission,
        $dob,
        $subject_studied_json,
        $last_class,
        $promotion_higher_class,
        $paid,
        $fee_concession,
        $working_days,
        $board,
        $ncc,
        $conduct,
        $present_days,
        $class_in_figure,
        $extra_curricular_activities,
        $date_application_certificate,
        $date_issue_certificate,
        $reasons_leaving_school,
        $remarks,
        $username,
        $admission_number
    ]);

    $left_id = $conn_rjllearning_specific->lastInsertId();

    // Fetch all student_admission records with non-empty sibling fields
    $admn_to_remove = $admission_number; // the admission number of the student being marked as left

    // Fetch all student_admission records with non-empty sibling fields
    // Assuming $institution_id and $branch_id are already set

    $sql = "SELECT id, sibling 
            FROM students
            WHERE institution_id = ? 
            AND branch_id = ? 
            AND sibling LIKE ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $array_params = [$institution_id, $branch_id, "%'" . $admission_number . "%'"];
    $stmt->execute($array_params);
    $students_with_siblings = $stmt->fetchAll();


    foreach ($students_with_siblings as $row) {

        $current_id = $row['id'];
        $sibling_str = $row['sibling'];

        // Remove quotes and whitespace from sibling admission numbers
        $siblings = array_map('trim', explode(",", str_replace("'", "", $sibling_str)));

        // Check if the current student's siblings include the one being removed
        if (in_array($admn_to_remove, $siblings)) {

            // Remove the sibling to be removed
            $updated_siblings = array_filter($siblings, function ($s) use ($admn_to_remove) {
                return $s !== $admn_to_remove;
            });
            $final_sibling_str = count($updated_siblings) > 0 ? "'" . implode("','", $updated_siblings) . "'" : '';

            $update_left_sibling = "UPDATE left_students SET left_sibling_id = ? WHERE $id = ? AND institution_id = ?";
            $conn_rjllearning_specific->prepare($update_left_sibling)->execute([$final_sibling_str, $id, $institution_id]);


            // If no siblings are left after removal, reset both sibling fields
            if (count($updated_siblings) === 0) {
                $update = "UPDATE students SET sibling = '', sibling_bool = 0 WHERE id = ?";
                $conn_rjllearning_specific->prepare($update)->execute([$current_id]);
            } else {
                // If there are more than one sibling left, update the sibling list
                $updated_str = "'" . implode("','", $updated_siblings) . "'";
                $update = "UPDATE students SET sibling = ? WHERE id = ?";
                $conn_rjllearning_specific->prepare($update)->execute([$updated_str, $current_id]);
            }
        }
    }


    // Mark student and user as left
    $conn_rjllearning_specific->prepare("UPDATE students SET can_activate = 0, status = 0, date_left = ? WHERE institution_id = ? AND id = ?")
        ->execute([$date_issue_certificate, $institution_id, $student_id]);

    // $branch = getBranchDetails($conn_rjllearning_specific, $branch_id, $institution_id);
    // if (!empty($branch)) {
    //     $student_data = ["workspace_id" => $workspace_id, "branch" => $branch, "student" => $student_id];
    //     curlLeftStudentFinware($student_data);
    // }



    $conn_rjllearning->prepare("UPDATE users SET can_activate = 0, status = 0 WHERE workspace_id = ? AND institution_id = ? AND id = ?")
        ->execute([$workspace_id, $institution_id, $student_userid]);
    // $conn_rjllearning_specific->commit();
    return true;
}






function logDetailedError(Throwable $e, $context = '')
{
    $logFile = '/home/rejoicebilldesk/public_html/rjllearning_development/resources/lib/common_modules/import/left_student_import/error_log_rjl.txt';

    $message = "\n====================================\n";
    $message .= "Error Context: {$context}\n";
    $message .= "Message: " . $e->getMessage() . "\n";
    $message .= "File: " . $e->getFile() . "\n";
    $message .= "Line: " . $e->getLine() . "\n";
    $message .= "Trace:\n" . $e->getTraceAsString() . "\n";
    $message .= "Date: " . date('Y-m-d H:i:s') . "\n";
    $message .= "====================================\n";

    file_put_contents($logFile, $message, FILE_APPEND); // Log to file

    // Optional: Also echo it (for local development)
    if ($_SERVER['REMOTE_ADDR'] === '127.0.0.1') {
        echo "<pre>$message</pre>";
    }
}




// function fetch_profile_photo_tmp($profile_photo_filename)
// {
//     global $profile_photo_tmp; // declare global variable

//     $baseUrl = "https://rejoicelearning.in/rjldocumentmanager/dataInstitution/gvis/";

//     $profile_photo_tmp = null; // initialize

//     if (empty($profile_photo_filename)) {
//         return;
//     }

//     $file_name = trim($profile_photo_filename);
//     if (empty($file_name)) {
//         return;
//     }

//     $remoteUrl = $baseUrl . $file_name;
//     $file_extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

//     $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
//     if (!in_array($file_extension, $allowed_extensions)) {
//         errorreturn("Invalid file type for profilephoto: .$file_extension not allowed");
//     }

//     $tempPath = sys_get_temp_dir() . '/' . uniqid("profile_", true) . "." . $file_extension;

//     $ch = curl_init($remoteUrl);
//     $fp = fopen($tempPath, 'w+');
//     curl_setopt($ch, CURLOPT_FILE, $fp);
//     curl_setopt($ch, CURLOPT_TIMEOUT, 30);
//     curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
//     curl_exec($ch);

//     if (curl_errno($ch)) {
//         $error = curl_error($ch);
//         fclose($fp);
//         unlink($tempPath);
//         errorreturn("Failed to download profilephoto from: $remoteUrl\nReason: $error");
//     }

//     curl_close($ch);
//     fclose($fp);

//     // ✅ store file path in global variable
//     $profile_photo_tmp = $tempPath;
// }
function safe_unserializes($serialized_data)
{
    // First try direct unserialization
    $result = @unserialize($serialized_data);

    if ($result !== false || $serialized_data === 'b:0;') {
        return $result;
    }

    // If that fails, try fixing common serialization issues

    // 1. Check for encoding issues - try converting to UTF-8
    $utf8_data = mb_convert_encoding($serialized_data, 'UTF-8', 'UTF-8');
    $result = @unserialize($utf8_data);
    if ($result !== false) {
        return $result;
    }

    // 2. Fix string length issues in serialization format
    $fixed_data = preg_replace_callback(
        '!s:(\d+):"(.*?)";!s',
        function ($match) {
            return 's:' . strlen($match[2]) . ':"' . $match[2] . '";';
        },
        $serialized_data
    );

    $result = @unserialize($fixed_data);
    if ($result !== false) {
        return $result;
    }

    // If all else fails, return false to indicate failure
    return false;
}




function copyImageFromAnotherBucketAndUpdate(
    $conn_rjllearning_specific,
    $s3_learning_bucket,
    $learning_bucketName,
    $profilephoto,
    $workspace_id,
    $institution_id,
    $student_id,
    $sourceBucket
) {
    $currentYear = date('Y');
    $currentMonth = date('m');
    $fileName = basename($profilephoto);

    // File parts
    $fileInfo = pathinfo($fileName);
    $fileBaseName = $fileInfo['filename'];
    $fileExtension = isset($fileInfo['extension']) ? '.' . $fileInfo['extension'] : '';

    // Destination keys for both tables
    $destinationKeyStudents = "user_uploads/{$workspace_id}/{$institution_id}/students/{$currentYear}/{$currentMonth}/{$student_id}/Students_Image_{$student_id}{$fileExtension}";

    $destinationKeyAdmission = "user_uploads/{$workspace_id}/{$institution_id}/student_admission/{$currentYear}/{$currentMonth}/{$student_id}/AdmissionImage_" . $student_id . $fileExtension;

    $copySource = "{$sourceBucket}/Profiles/{$profilephoto}";
    try {
        // Copy to students key
        $s3_learning_bucket->copyObject([
            'Bucket'     => $learning_bucketName,
            'Key'        => $destinationKeyStudents,
            'CopySource' => $copySource,
        ]);

        // Copy to student_admission key
        $s3_learning_bucket->copyObject([
            'Bucket'     => $learning_bucketName,
            'Key'        => $destinationKeyAdmission,
            'CopySource' => $copySource,
        ]);

        // Prepare JSON attachments
        $attachmentStudents = json_encode([
            'filepath' => $destinationKeyStudents,
            'original_name' => basename($destinationKeyStudents)
        ]);

        $attachmentAdmission = json_encode([
            'filepath' => $destinationKeyAdmission,
            'original_name' => basename($destinationKeyAdmission)
        ]);

        // Update students table
        $sql1 = "UPDATE students SET student_profile_image = ? WHERE id = ?";
        $conn_rjllearning_specific->prepare($sql1)->execute([$attachmentStudents, $student_id]);

        // Update student_admission table
        $sql2 = "UPDATE student_admission SET profile_image = ? WHERE student_id = ?";
        $conn_rjllearning_specific->prepare($sql2)->execute([$attachmentAdmission, $student_id]);

        return true;

    } catch (Aws\Exception\AwsException $e) {
        return [
            'status' => false,
            'message' => 'AWS S3 Error: ' . $e->getAwsErrorMessage()
        ];
    } catch (PDOException $e) {
        return [
            'status' => false,
            'message' => 'Database Error: ' . $e->getMessage()
        ];
    } catch (Exception $e) {
        return [
            'status' => false,
            'message' => 'Unexpected Error: ' . $e->getMessage()
        ];
    }
}


