<?php
// function getDateSpecificAttendance($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $date) {
//     try {
//         // Build the SQL query
//         $sql = "SELECT s.id, s.name,  s.admission_number, d.value 
//                 FROM students s
//                 LEFT JOIN student_attendance_daywise d ON s.id = d.student_id AND d.date = ?
//                 WHERE s.institution_id = ? AND s.branch_id = ? AND s.class_id = ? AND s.section_id = ?";
        
//         // Prepare the statement
//         $stmt = $conn_rjllearning_specific->prepare($sql);
        
//         // Bind parameters and execute the query
//         $stmt->execute([$date, $institution_id, $branch_id, $class_id, $section_id]);
        
//         // Fetch the results
//         $result = $stmt->fetchAll();

//         // Return the result
        
//         $newArray = array();

// foreach ($result as $item) {
     
//     $newArray[] = array(
//         'student_id' => $item['id'],
//         'name' => $item['name'],
//         'admission_number' => $item['admission_number'],
//         'value' => $item['value']
//     );
// }
//         return $newArray;
//     } catch (PDOException $e) {
//         errorreturn("Error executing query: " . $e->getMessage());
//     }
// }


function getDateSpecificAttendance($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $date, $active_academic_id = null)
{
    // comparing dates to fetch students
    $fetched_academic_session = compareDatesAcademicSession($conn_rjllearning_specific, $institution_id, $date, null);
    if ($date == date("Y-m-d")) {
        $sql = "SELECT id, name, admission_number FROM students 
        WHERE institution_id = ? AND branch_id = ? AND class_id = ? AND section_id = ? AND date_admission <= ? and (date_left > ? or date_left IS NULL) and can_activate = 1";
        $fetch_students = $conn_rjllearning_specific->prepare($sql);
        $fetch_students->execute(array($institution_id, $branch_id, $class_id, $section_id, $date, $date));
        $students = $fetch_students->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $sql = "SELECT id, name, admission_number from students where institution_id = ? and branch_id = ? and date_admission <= ? and (date_left > ? or date_left IS NULL) and can_activate = 1 and id IN (SELECT student_id from cache_academic_students_data where class_id = ? and section_id = ? and (date, student_id) IN (SELECT MAX(date), student_id from cache_academic_students_data where institution_id = ? and branch_id = ? and date <= ? group by student_id))";
        $fetch_students = $conn_rjllearning_specific->prepare($sql);
        $fetch_students->execute(array($institution_id, $branch_id, $date, $date, $class_id, $section_id, $institution_id, $branch_id, $date));
        $students = $fetch_students->fetchAll(PDO::FETCH_ASSOC);
    }
    
    try {

        // Fetch attendance values
        $sql_fetch_attendance_value = "SELECT student_id, value 
         FROM student_attendance_daywise 
         WHERE academic_id = ? AND institution_id = ? AND branch_id = ? AND class_id = ? AND section_id = ? AND date = ?";
        $fetch_attendance_value = $conn_rjllearning_specific->prepare($sql_fetch_attendance_value);
        $fetch_attendance_value->execute(array($fetched_academic_session['academic_session_id'], $institution_id, $branch_id, $class_id, $section_id, $date));
        // errorreturn($fetched_academic_session['academic_session_id']);
        // print_r($fetch_attendance_value->fetchAll());
        // die();
        $attendance_values = $fetch_attendance_value->fetchAll(PDO::FETCH_ASSOC);

        // Create a mapping of attendance values for quick lookup
        $attendance_map = [];
        foreach ($attendance_values as $attendance) {
            $attendance_map[$attendance['student_id']] = $attendance['value'];
        }

        // Merge attendance values into student records
        foreach ($students as &$student) {
            $student['value'] = isset($attendance_map[$student['id']]) ? $attendance_map[$student['id']] : null; // Default to null if no attendance value
            $student['student_id'] = $student['id']; // Rename 'id' to 'student_id'
            unset($student['id']); // Remove the original 'id' key
        }

        // // Build the SQL query
        // $sql = "SELECT s.id, s.name,  s.admission_number, d.value 
        //         FROM students s 
        //         LEFT JOIN student_attendance_daywise d ON s.id = d.student_id
        //         AND d.date = ?
        //         WHERE s.institution_id = ? 
        //         AND s.branch_id = ?
        //         AND s.class_id = ?
        //         AND s.section_id = ? 
        //         AND  s.date_admission <= ?
        //         AND (s.date_left IS NULL OR s.date_left >= ?)";

        // // Prepare the statement
        // $stmt = $conn_rjllearning_specific->prepare($sql);

        // // Bind parameters and execute the query
        // $stmt->execute([$date, $institution_id, $branch_id, $class_id, $section_id, $date, $date]);

        // // Fetch the results
        // $result = $stmt->fetchAll();


        // Return the result

        $newArray = $students;

        // foreach ($result as $item) {

        //     $newArray[] = array(
        //         'student_id' => $item['id'],
        //         'name' => $item['name'],
        //         'admission_number' => $item['admission_number'],
        //         'value' => $item['value']
        //     );
        // }
        return $newArray;
    } catch (PDOException $e) {
        errorreturn("Error executing query: " . $e->getMessage());
    }
}