<?php
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 can_activate = 1
                AND (date_left > ? OR date_left IS NULL)
                ORDER BY name ASC";  // 🔹 Sort by name
        $fetch_students = $conn_rjllearning_specific->prepare($sql);
        $fetch_students->execute([$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
                    )
                )
                ORDER BY name ASC";  // 🔹 Sort by name
        $fetch_students = $conn_rjllearning_specific->prepare($sql);
        $fetch_students->execute([$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([$fetched_academic_session['academic_session_id'], $institution_id, $branch_id, $class_id, $section_id, $date]);
        $attendance_values = $fetch_attendance_value->fetchAll(PDO::FETCH_ASSOC);

        // Map attendance values
        $attendance_map = [];
        foreach ($attendance_values as $attendance) {
            $attendance_map[$attendance['student_id']] = $attendance['value'];
        }

        // Merge attendance into student data
        foreach ($students as &$student) {
            $student['value'] = $attendance_map[$student['id']] ?? null;
            $student['student_id'] = $student['id'];
            unset($student['id']);
        }

        return $students;

    } catch (PDOException $e) {
        errorreturn("Error executing query: " . $e->getMessage());
    }
}
