<?php
function getapplyleaves($conn_rjllearning_specific, $institution_id, $branch_id,$student_id,  $start_date, $end_date,  $limit, $offset)
{
    $sql_count = "SELECT count(*) as total_rows 
              FROM apply_leaves 
              WHERE institution_id = ? 
              AND student_id = (SELECT user_id FROM students WHERE id = ?)";
    $array_params = [$institution_id,$student_id];

    // Append conditions based on filters
    if ($branch_id != null && $branch_id != 'all') {
        $sql_count .= " AND branch_id = ?";
        $array_params[] = $branch_id;
    }

    if ($start_date != null && $end_date != null) {
        $sql_count .= " AND date_created BETWEEN ? AND ?";
        $array_params[] = $start_date;
        $array_params[] = $end_date;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_count);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    $sql = "SELECT * FROM apply_leaves WHERE institution_id = ?  AND student_id = (SELECT user_id FROM students WHERE id = ?)";
    $array_params = array($institution_id,$student_id);

    // Append conditions based on filters
    if ($branch_id != null && $branch_id != 'all') {
        $sql .= " AND branch_id = ?";
        $array_params[] = $branch_id;
    }
   
    if ($start_date != null && $end_date != null) {
        $sql .= " AND date_created BETWEEN ? AND ?";
        $array_params[] = $start_date;
        $array_params[] = $end_date;
    }

    $sql .= " ORDER BY date_created DESC ";

    if ($limit != 'all') {
        $sql .= "LIMIT " . $offset . " ," . $limit . "";
    }

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

    // Process the result and prepare the response
    $array_return = [];
    $branch_info_all = [];
    $class_info_all = [];
    $section_info_all = [];
    $subject_info_all = [];

    $leave_info = [];

    foreach ($result as $row) {

        // SQL query to select the student's ID and name
        $student_sql = "SELECT id, name FROM students WHERE institution_id = ? AND user_id = ?";

        // Prepare the statement
        $student_stmt = $conn_rjllearning_specific->prepare($student_sql);

        // Execute the statement with the provided parameters
        $student_stmt->execute([$institution_id, $row['student_id']]);

        // Fetch a single result as an associative array
        $student_result = $student_stmt->fetch(PDO::FETCH_ASSOC);


        if ($student_result) {
            // Access data correctly from the associative array
            $student_id = $student_result['id'];
            $student_name = $student_result['name'];
        } else {
            $student_id = null;
            $student_name = null;
        }

        // Process attachment
        if (!empty($row['attachment']) && is_string($row['attachment'])) {
            $data_attachment = json_decode($row['attachment'], true);
            if (isset($data_attachment['filepath']) && isset($data_attachment['original_name'])) {
                $filePath = $data_attachment['filepath'];
                $originalName = $data_attachment['original_name'];
                $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                $final_array_attachment = array("filepath" => $aws_attachment, "original_name" => $originalName);
            } else {
                $final_array_attachment = null;
            }
        } else {
            $final_array_attachment = null;
        }


        // Branch Info
        $branch_id = $row['branch_id'];
        if ($branch_id == 'all') {
            $branch_info = array('branch_id' => $branch_id, 'name' => ucwords($branch_id));
        } else {
            // Fetch branch data from database
            $branch_sql = "SELECT * FROM branch WHERE institution_id = ? AND id = ?";
            $stmt = $conn_rjllearning_specific->prepare($branch_sql);
            $stmt->execute([$institution_id, $branch_id]);
            $branch_data = $stmt->fetch();

            $branch_info = array('branch_id' => $branch_data['id'], 'name' => $branch_data['name']);
        }

        // Class Info
        $class_id = $row['class_id'];
        if ($class_id == 'all') {
            $class_info = array('class_id' => $class_id, 'classname' => ucwords($class_id));
        } else {
            // Fetch class data from database
            $class_sql = "SELECT * FROM class_info WHERE institution_id = ? AND id = ?";
            $stmt = $conn_rjllearning_specific->prepare($class_sql);
            $stmt->execute([$institution_id, $class_id]);
            $class_data = $stmt->fetch();

            $class_info = array('class_id' => $class_data['id'], 'classname' => $class_data['classname']);
        }

        // Section Info
        $section_id = $row['section_id'];
        if ($section_id == 'all') {
            $section_info = array('section_id' => $section_id, 'section_name' => ucwords($section_id));
        } else {
            // Fetch section data from database
            $section_sql = "SELECT * FROM section_info WHERE institution_id = ? AND id = ?";
            $stmt = $conn_rjllearning_specific->prepare($section_sql);
            $stmt->execute([$institution_id, $section_id]);
            $section_data = $stmt->fetch();

            $section_info = array('section_id' => $section_data['id'], 'section_name' => $section_data['section_name']);
        }

        // Merge branch, class, and section info arrays
        $branch_info_all = $branch_info;
        $class_info_all = $class_info;
        $section_info_all = $section_info;

        // Get subject info
        // Get subject info
        $subject_id = $row['subject_id'];

        if ($subject_id == 'all') {
            $subject_info = array('subject_id' => $subject_id, 'subject_name' => ucwords($subject_id));
        } else {
            // Fetch section data from database
            $section_sql = "SELECT * FROM subject_info WHERE institution_id = ? AND id = ?";
            $stmt = $conn_rjllearning_specific->prepare($section_sql);
            $stmt->execute([$institution_id, $subject_id]);
            $subject_data = $stmt->fetch();

            $subject_info = array('section_id' => $subject_data['id'], 'subject_name' => $subject_data['subject_name']);
        }
        // Create the response array
        $array_return[] = [
            'student_id' => $student_id,
            'name' => $student_name,
            'branch_info' => $branch_info_all,
            'class_info' => $class_info_all,
            'section_info' => $section_info_all,
            'subject_info' => $subject_info,
            'apply_leave_id' => $row['id'],
            'attachment' => $final_array_attachment,
            'subject_leave' => $row['subject_leave'],
            'description' => $row['description'],
            'start_date' => $row['start_date'],
            'end_date' => $row['end_date'],
            'date_created' => $row['date_created'],
            'time_created' => $row['time_created'],
            'status' => $row['status'],

        ];
    }
    // Return the array
    // return $array_return;

    if (count($result) < 50) {

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

// function getapplyleaves($conn_rjllearning_specific, $institution_id, $branch_id, $student_id, $start_date, $end_date, $limit, $offset)
// {
//     // Use JOIN instead of subquery for count
//     $sql_count = "SELECT count(*) as total_rows 
//                   FROM apply_leaves al
//                   JOIN students s ON al.student_id = s.user_id AND al.institution_id = s.institution_id
//                   WHERE al.institution_id = ? 
//                   AND s.id = ?";
//     $array_params = [$institution_id, $student_id];

//     // Append conditions based on filters
//     if ($branch_id != null && $branch_id != 'all') {
//         $sql_count .= " AND al.branch_id = ?";
//         $array_params[] = $branch_id;
//     }

//     if ($start_date != null && $end_date != null) {
//         $sql_count .= " AND al.date_created BETWEEN ? AND ?";
//         $array_params[] = $start_date;
//         $array_params[] = $end_date;
//     }

//     $stmt = $conn_rjllearning_specific->prepare($sql_count);
//     $stmt->execute($array_params);
//     $result_total = $stmt->fetchAll();
//     $totalrows = $result_total[0]['total_rows'];

//     // Use JOIN instead of subquery for main data
//     $sql = "SELECT al.*, s.id as student_db_id, s.name as student_name 
//             FROM apply_leaves al
//             JOIN students s ON al.student_id = s.user_id AND al.institution_id = s.institution_id
//             WHERE al.institution_id = ? 
//             AND s.id = ?";
//     $array_params = array($institution_id, $student_id);

//     // Append conditions based on filters
//     if ($branch_id != null && $branch_id != 'all') {
//         $sql .= " AND al.branch_id = ?";
//         $array_params[] = $branch_id;
//     }
   
//     if ($start_date != null && $end_date != null) {
//         $sql .= " AND al.date_created BETWEEN ? AND ?";
//         $array_params[] = $start_date;
//         $array_params[] = $end_date;
//     }

//     $sql .= " ORDER BY al.date_created DESC ";

//     if ($limit != 'all') {
//         $sql .= "LIMIT " . $offset . " ," . $limit . "";
//     }

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

//     // Process the result and prepare the response
//     $array_return = [];
//     $branch_info_all = [];
//     $class_info_all = [];
//     $section_info_all = [];
//     $subject_info_all = [];

//     $leave_info = [];

//     foreach ($result as $row) {
//         // No need for separate student query since we joined in the main query
//         $student_id = $row['student_db_id'];
//         $student_name = $row['student_name'];

//         // Process attachment
//         if (!empty($row['attachment']) && is_string($row['attachment'])) {
//             $data_attachment = json_decode($row['attachment'], true);
//             if (isset($data_attachment['filepath']) && isset($data_attachment['original_name'])) {
//                 $filePath = $data_attachment['filepath'];
//                 $originalName = $data_attachment['original_name'];
//                 $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
//                 $final_array_attachment = array("filepath" => $aws_attachment, "original_name" => $originalName);
//             } else {
//                 $final_array_attachment = null;
//             }
//         } else {
//             $final_array_attachment = null;
//         }

//         // Branch Info
//         $branch_id = $row['branch_id'];
//         if ($branch_id == 'all') {
//             $branch_info = array('branch_id' => $branch_id, 'name' => ucwords($branch_id));
//         } else {
//             // Fetch branch data from database
//             $branch_sql = "SELECT * FROM branch WHERE institution_id = ? AND id = ?";
//             $stmt = $conn_rjllearning_specific->prepare($branch_sql);
//             $stmt->execute([$institution_id, $branch_id]);
//             $branch_data = $stmt->fetch();

//             $branch_info = array('branch_id' => $branch_data['id'], 'name' => $branch_data['name']);
//         }

//         // Class Info
//         $class_id = $row['class_id'];
//         if ($class_id == 'all') {
//             $class_info = array('class_id' => $class_id, 'classname' => ucwords($class_id));
//         } else {
//             // Fetch class data from database
//             $class_sql = "SELECT * FROM class_info WHERE institution_id = ? AND id = ?";
//             $stmt = $conn_rjllearning_specific->prepare($class_sql);
//             $stmt->execute([$institution_id, $class_id]);
//             $class_data = $stmt->fetch();

//             $class_info = array('class_id' => $class_data['id'], 'classname' => $class_data['classname']);
//         }

//         // Section Info
//         $section_id = $row['section_id'];
//         if ($section_id == 'all') {
//             $section_info = array('section_id' => $section_id, 'section_name' => ucwords($section_id));
//         } else {
//             // Fetch section data from database
//             $section_sql = "SELECT * FROM section_info WHERE institution_id = ? AND id = ?";
//             $stmt = $conn_rjllearning_specific->prepare($section_sql);
//             $stmt->execute([$institution_id, $section_id]);
//             $section_data = $stmt->fetch();

//             $section_info = array('section_id' => $section_data['id'], 'section_name' => $section_data['section_name']);
//         }

//         // Merge branch, class, and section info arrays
//         $branch_info_all = $branch_info;
//         $class_info_all = $class_info;
//         $section_info_all = $section_info;

//         // Get subject info
//         $subject_id = $row['subject_id'];

//         if ($subject_id == 'all') {
//             $subject_info = array('subject_id' => $subject_id, 'subject_name' => ucwords($subject_id));
//         } else {
//             // Fetch subject data from database
//             $subject_sql = "SELECT * FROM subject_info WHERE institution_id = ? AND id = ?";
//             $stmt = $conn_rjllearning_specific->prepare($subject_sql);
//             $stmt->execute([$institution_id, $subject_id]);
//             $subject_data = $stmt->fetch();

//             $subject_info = array('subject_id' => $subject_data['id'], 'subject_name' => $subject_data['subject_name']);
//         }
        
//         // Create the response array
//         $array_return[] = [
//             'student_id' => $student_id,
//             'name' => $student_name,
//             'branch_info' => $branch_info_all,
//             'class_info' => $class_info_all,
//             'section_info' => $section_info_all,
//             'subject_info' => $subject_info,
//             'apply_leave_id' => $row['id'],
//             'attachment' => $final_array_attachment,
//             'subject_leave' => $row['subject_leave'],
//             'description' => $row['description'],
//             'start_date' => $row['start_date'],
//             'end_date' => $row['end_date'],
//             'date_created' => $row['date_created'],
//             'time_created' => $row['time_created'],
//             'status' => $row['status'],
//         ];
//     }

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