<?php

function getBranchDetails($conn_rjllearning_specific, $branch_id, $institution_id)
{
    // Query to check if the branch exists with specific IDs
    $sqlbranch = "SELECT * FROM branch WHERE id = ? AND institution_id = ?";
    $stmtBranch = $conn_rjllearning_specific->prepare($sqlbranch);
    $stmtBranch->execute(array($branch_id, $institution_id));
    $resultBranch = $stmtBranch->fetchAll();
    // print_r($resultBranch);die();
    // If the branch is not found
    if (count($resultBranch) != 1) {
        errorreturn('Branch not found.');
    }

    // Fetch branch info
    $branch_info = array(
        "rejoice_solutions_company_id" => $resultBranch[0]['rejoice_solutions_company_id'],
        "rejoice_solutions_branch_id" => $resultBranch[0]['rejoice_solutions_branch_id'],
        "rejoice_finware_book_id" => $resultBranch[0]['rejoice_finware_book_id']
    );

    //   //  Check if any of the critical fields are null
    //     foreach ($branch_info as $key => $value) {
    //         if (is_null($value)) {
    //          //errorreturn('Finware database connection is not established with learning.');
    //           return null;
    //         }
    //     }
    // Check if any of the critical fields are null, return null to avoid further processing
    if (in_array(null, $branch_info, true)) {
        return null; // Return null if any critical field is null
    }

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

function getDistinctClassesAndSections($conn_rjllearning_specific, $branch_id, $institution_id)
{
    // Query to get distinct classes and sections
    $sql_Section = "SELECT section_name from section_info where  (branch_id = 'all'  OR branch_id LIKE ?) AND institution_id = ?";
    $fetch_section = $conn_rjllearning_specific->prepare($sql_Section);
    $fetch_section->execute(["%'" . $branch_id . "'%", $institution_id]);
    $sections = [];
    foreach ($fetch_section->fetchAll() as $row) {
        $sections[] = $row['section_name'];
    }

    $sql = "SELECT classname from class_info where  (branch_id = 'all' OR branch_id LIKE ?) AND institution_id = ?";
    $fetch_class = $conn_rjllearning_specific->prepare($sql);
    $fetch_class->execute(["%'" . $branch_id . "'%", $institution_id]);
    $classes = [];
    $groups = []; // To store the group hierarchy
    foreach ($fetch_class->fetchAll() as $row) {
        $classes[] = $row['classname'];
        // Create group hierarchy: Sundry Debtors > Students > Class > Section
        foreach ($sections as $temp_Section) {
            $groups[] = array(
                'main_ledgerhead' => 'Sundry Debtors',
                // 'group_name' => 'Students',
                'class' => $row['classname'],
                'section' => $temp_Section
            );
        }
    }
    return $groups; // Return the array with the group hierarchy
}


// Function to fetch and format all students for a specific branch
function getStudentsByBranch($conn_rjllearning_specific, $branch_id, $institution_id)
{
    // Query to get all students based on branch
    $sqlStudents = "SELECT students.name,students.gender, students.fathername, students.mothername, students.mothername, students.admission_number,students.mobile,students.email,
                    students.address_line_1,students.id,
                           class_info.classname, section_info.section_name
                    FROM students
                    JOIN class_info ON students.class_id = class_info.id
                    JOIN section_info ON students.section_id = section_info.id
                    WHERE students.branch_id = ? AND students.institution_id = ?";

    $stmtStudents = $conn_rjllearning_specific->prepare($sqlStudents);
    $stmtStudents->execute(array($branch_id, $institution_id));
    $students = $stmtStudents->fetchAll(PDO::FETCH_ASSOC); // Fetch as associative array

    // Check if students exist
    if (empty($students)) {
        return [];
    }



    $formattedData = []; // Array to store formatted data for students

    foreach ($students as &$student) {
        $product_connection_id = $student['id'];
        //$ledger_address = $student['address_line_1'];
        $name = $student['name'];
        $father_name = $student['fathername']; // Corrected the key name to match the SQL query
        $mother_name = $student['mothername'];
        $admission_number = $student['admission_number'];
        $class = $student['classname'];
        $section = $student['section_name'];
        $mobile = $student['mobile'];
        $email = $student['email'];
        $gender = $student['gender'];

        // Format the ledger name: "John Doe S/o Peter Doe - 12345"
        //$ledger_name = "$name S/o $father_name - $admission_number";
        // Check if father's name is null
        if (!empty($father_name)) {
            // Use father's name if it's not null
            // Use "S/o" for males and "D/o" for females
            $relation = ($gender === 'M') ? 'S/o' : 'D/o';
            $ledger_name = "$name $relation $father_name - $admission_number";
            //$ledger_name = "$name S/o $father_name - $admission_number";
        } elseif (!empty($mother_name)) {
            // Use mother's name if father's name is null
            //$ledger_name = "$name D/o $mother_name - $admission_number";
            $relation = ($gender === 'M') ? 'S/o' : 'D/o';
            $ledger_name = "$name $relation $mother_name - $admission_number";
        } else {
            // If both father's and mother's names are null, handle as needed (e.g., just the name and admission number)
            $ledger_name = "$name - $admission_number";
        }

        $ledger_address = sanitizeInput($student['address_line_1']);
        $ledger_name = sanitizeInput($ledger_name);

        // Create array with student and branch details for formattedData
        $formattedData[] = array(
            'product_connection_id' => $product_connection_id,
            'ledger_address' => $ledger_address,
            'ledger_name' => $ledger_name,
            'ledger_main_ledgerhead' => 'Sundry Debtors',
            'mobile' => $mobile,
            'email' => $email,
            'class' => $class,
            'section' => $section,
            'admission_number' => $student['admission_number']
        );
    }
    unset($student); // Break the reference

    return $formattedData;
}
