<?php

// function getStudents_assignment_status($conn_rjllearning_specific, $institution_id, $branch_id, $from_date, $to_date, $check_academic_confirmation_id, $student_id)
// {
//     $array_return = [];

//     // Step 1: Fetch Student's Class/Section History with Admission Constraints
//     $sql = "SELECT DISTINCT c.student_id, c.class_id, c.section_id, c.date, s.name, s.fathername 
//         FROM cache_academic_students_data AS c
//         JOIN students AS s ON c.student_id = s.id
//         WHERE c.institution_id = ? 
//           AND c.branch_id = ? 
//           AND c.student_id = ?
//           AND c.date <= ?  
//           AND s.date_admission <= ? 
//           AND (s.date_left > ? OR s.date_left IS NULL)
//         ORDER BY c.date DESC";

//     $stmt = $conn_rjllearning_specific->prepare($sql);
//     $stmt->execute([$institution_id, $branch_id, $student_id, $from_date, $from_date, $from_date]);
//     $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

//     // If no data is found in cache_academic_students_data, fetch from students table directly
//     if (!$students) {
//         $sql = "SELECT user_id AS student_id, NULL AS class_id, NULL AS section_id, name, fathername 
//             FROM students 
//             WHERE institution_id = ? 
//               AND branch_id = ? 
//               AND id = ?
//               AND date_admission <= ? 
//               AND (date_left > ? OR date_left IS NULL)";

//         $stmt = $conn_rjllearning_specific->prepare($sql);
//         $stmt->execute([$institution_id, $branch_id, $student_id, $from_date, $from_date]);
//         $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

//         if (!$students) {
//             return ["message" => "No student found"];
//         }
//     }


//     $student_info = []; // Store student info once
//     $assignments_list = []; // Store assignments grouped by student

//     foreach ($students as $student) {
//         $class_id = $student['class_id'];
//         $section_id = $student['section_id'];

//         // Store student info only once
//         if (empty($student_info)) {
//             $student_info = [
//                 "student_id" => $student['student_id'],
//                 "name" => $student['name'],
//                 "fathername" => $student['fathername'],
//                 "assignments" => [] // Assignments will be added here
//             ];
//         }

//         // Step 2: Fetch All Assignments Given to This Class/Section
//         $sqlAssignments = "SELECT id AS assignment_id, name, description
//                           FROM assignments 
//                           WHERE institution_id = ? 
//                              AND branch_id = ? 
//                              AND (class_id = ? OR section_id = ?)";
//         $stmtAssignments = $conn_rjllearning_specific->prepare($sqlAssignments);
//         $stmtAssignments->execute([$institution_id, $branch_id, $class_id, $section_id]);
//         $allAssignments = $stmtAssignments->fetchAll(PDO::FETCH_ASSOC);

//         // Create Assignment Status Map
//         $assignmentStatusMap = [];
//         foreach ($allAssignments as $assignment) {
//             $assignmentStatusMap[$assignment['assignment_id']] = [
//                 "assignment_id" => $assignment['assignment_id'],
//                 "assignment_title" => $assignment['name'],
//                 "assignment_description" => $assignment['description'],
//                 "assignment_status" => "Not Submitted",
//                 "marks_obtained" => 0
//             ];
//         }

//         // Step 3: Fetch Submitted Assignments
//         $sqlSubmitted = "SELECT assignment_id, status, marks_obtained, points
//                          FROM assignment_submission 
//                          WHERE student_id = ? 
//                           AND institution_id = ? 
//                           AND branch_id = ? 
//                           AND assignment_id IN (SELECT id FROM assignments WHERE class_id = ? OR section_id = ?)";
//         $stmtSubmitted = $conn_rjllearning_specific->prepare($sqlSubmitted);
//         $stmtSubmitted->execute([$student_id, $institution_id, $branch_id, $class_id, $section_id]);
//         $submittedAssignments = $stmtSubmitted->fetchAll(PDO::FETCH_ASSOC);

//         foreach ($submittedAssignments as $submission) {
//             if (isset($assignmentStatusMap[$submission['assignment_id']])) {
//                 $assignmentStatusMap[$submission['assignment_id']]['assignment_status'] = "Submitted";
//                 $assignmentStatusMap[$submission['assignment_id']]['marks_obtained'] =
//                     $submission['marks_obtained'] + $submission['points'];
//             }
//         }

//         // Step 4: Store Assignments Inside Student Info
//         foreach ($assignmentStatusMap as $assignment) {
//             $student_info["assignments"][] = $assignment;
//         }
//     }

//     return $student_info;
// }

function getStudents_assignment_status($conn_rjllearning_specific, $institution_id, $branch_id, $from_date, $to_date, $check_academic_confirmation_id, $student_id, $limit = null, $offset = 0)
{
    $array_return = [];

    // Step 1: Fetch Student's Class/Section History with Admission Constraints
    $sql = "SELECT DISTINCT c.student_id, c.class_id, c.section_id, c.date, s.name, s.fathername 
        FROM cache_academic_students_data AS c
        JOIN students AS s ON c.student_id = s.id
        WHERE c.institution_id = ? 
          AND c.branch_id = ? 
          AND c.student_id = ?
          AND c.date <= ?  
          AND s.date_admission <= ? 
          AND (s.date_left > ? OR s.date_left IS NULL)
        ORDER BY c.date DESC";

    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$institution_id, $branch_id, $student_id, $from_date, $from_date, $from_date]);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // If no data is found in cache_academic_students_data, fetch from students table directly
    if (!$students) {
        $sql = "SELECT user_id AS student_id, NULL AS class_id, NULL AS section_id, name, fathername 
            FROM students 
            WHERE institution_id = ? 
              AND branch_id = ? 
              AND id = ?
              AND date_admission <= ? 
              AND (date_left > ? OR date_left IS NULL)";

        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$institution_id, $branch_id, $student_id, $from_date, $from_date]);
        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$students) {
            return ["total_rows" => 0, "assignments" => [], "maxreach" => 1];
        }
    }

    $student_info = []; // Store student info once
    $assignments_list = []; // Store assignments grouped by student

    foreach ($students as $student) {
        $class_id = $student['class_id'];
        $section_id = $student['section_id'];

        // Store student info only once
        if (empty($student_info)) {
            $student_info = [
                "student_id" => $student['student_id'],
                "name" => $student['name'],
                "fathername" => $student['fathername'],
                "assignments" => [] // Assignments will be added here
            ];
        }

        // Step 2: Fetch All Assignments Given to This Class/Section
        $sqlAssignments = "SELECT id AS assignment_id, name, description
                           FROM assignments 
                           WHERE institution_id = ? 
                             AND branch_id = ? 
                             AND (class_id = ? OR section_id = ?)";
        $stmtAssignments = $conn_rjllearning_specific->prepare($sqlAssignments);
        $stmtAssignments->execute([$institution_id, $branch_id, $class_id, $section_id]);
        $allAssignments = $stmtAssignments->fetchAll(PDO::FETCH_ASSOC);

        // Create Assignment Status Map
        $assignmentStatusMap = [];
        foreach ($allAssignments as $assignment) {
            $assignmentStatusMap[$assignment['assignment_id']] = [
                "assignment_id" => $assignment['assignment_id'],
                "assignment_title" => $assignment['name'],
                "assignment_description" => $assignment['description'],
                "assignment_status" => "Not Submitted",
                "marks_obtained" => 0
            ];
        }

        // Step 3: Fetch Submitted Assignments
        $sqlSubmitted = "SELECT assignment_id, status, marks_obtained, points
                         FROM assignment_submission 
                         WHERE student_id = ? 
                           AND institution_id = ? 
                           AND branch_id = ? 
                           AND assignment_id IN (SELECT id FROM assignments WHERE class_id = ? OR section_id = ?)";
        $stmtSubmitted = $conn_rjllearning_specific->prepare($sqlSubmitted);
        $stmtSubmitted->execute([$student_id, $institution_id, $branch_id, $class_id, $section_id]);
        $submittedAssignments = $stmtSubmitted->fetchAll(PDO::FETCH_ASSOC);

        foreach ($submittedAssignments as $submission) {
            if (isset($assignmentStatusMap[$submission['assignment_id']])) {
                $assignmentStatusMap[$submission['assignment_id']]['assignment_status'] = "Submitted";
                $assignmentStatusMap[$submission['assignment_id']]['marks_obtained'] =
                    $submission['marks_obtained'] + $submission['points'];
            }
        }

        // Step 4: Store Assignments Inside Student Info
        foreach ($assignmentStatusMap as $assignment) {
            $student_info["assignments"][] = $assignment;
        }
    }

    // Count total assignments
    $total_rows = count($student_info["assignments"]);
    
    // Apply limit and offset if provided
    if ($limit !== null && $limit !== 'all') {
        $student_info["assignments"] = array_slice(
            $student_info["assignments"], 
            $offset, 
            $limit
        );
    }
    
    // Determine if we've reached the maximum
    $maxreach = (count($student_info["assignments"]) < 50) ? 1 : 0;
    
    // Return the result in the same format as your product items function
    return [
        'total_rows' => $total_rows,
        'student_info' => $student_info,
        'maxreach' => $maxreach
    ];
}

function get_assignment_stats($assignment_id, $conn_rjllearning_specific, $institution_id, $branch_id)
{
    // Initialize the stats array with default values
    $stats = [
        'assignment_id' => $assignment_id,
        'assignment_name' => '',
        'average_marks' => 0
    ];

    try {
        // Fetch assignment details (name and max_points)
        $sql_assignment = "SELECT name, max_points 
                           FROM assignments 
                           WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $stmt_assignment = $conn_rjllearning_specific->prepare($sql_assignment);
        $stmt_assignment->execute([$assignment_id, $institution_id, $branch_id]);
        $assignment_row = $stmt_assignment->fetch(PDO::FETCH_ASSOC);

        if (!$assignment_row) {
            errorreturn("Assignment not found or invalid assignment ID.");
        }

        $stats['assignment_name'] = $assignment_row['name'];

        // 1. Get the count of students who submitted the assignment
        $sql_student_count = "SELECT COUNT(DISTINCT student_id) AS student_count 
                              FROM assignment_submission 
                              WHERE assignment_id = ? AND institution_id = ? AND branch_id = ? AND status = 1";
        $stmt_student_count = $conn_rjllearning_specific->prepare($sql_student_count);
        $stmt_student_count->execute([$assignment_id, $institution_id, $branch_id]);
        $student_count_row = $stmt_student_count->fetch(PDO::FETCH_ASSOC);
        $student_count = $student_count_row['student_count'] ?? 0;

        // 2. Average score scored by students
        $sql_avg = "SELECT AVG(marks_obtained) as average_score 
                    FROM (
                        SELECT SUM(marks_obtained) as marks_obtained 
                        FROM assignment_submission 
                        WHERE assignment_id = ? AND institution_id = ? AND branch_id = ? AND status = 1 
                        GROUP BY student_id
                    ) as student_totals";
        $stmt_avg = $conn_rjllearning_specific->prepare($sql_avg);
        $stmt_avg->execute([$assignment_id, $institution_id, $branch_id]);
        $avg_row = $stmt_avg->fetch(PDO::FETCH_ASSOC);

        if ($avg_row['average_score'] !== null) {
            $stats['average_marks'] = $avg_row['average_score'];

            // Format to 1 decimal place if needed
            if (fmod($stats['average_marks'], 1) != 0) {
                $stats['average_marks'] = number_format($stats['average_marks'], 1);
            } else {
                $stats['average_marks'] = (int) $stats['average_marks'];
            }
        }

        // If only one student submitted, return only their details
        if ($student_count == 1) {
            $sql_single_student = "SELECT s.id AS student_id, SUM(a.marks_obtained) as marks_obtained, s.name 
                                   FROM assignment_submission a 
                                   JOIN students s ON a.student_id = s.user_id 
                                   WHERE a.assignment_id = ? AND a.institution_id = ? AND a.branch_id = ? AND a.status = 1  
                                   GROUP BY a.student_id";
            $stmt_single_student = $conn_rjllearning_specific->prepare($sql_single_student);
            $stmt_single_student->execute([$assignment_id, $institution_id, $branch_id]);
            $stats['student_details'] = $stmt_single_student->fetch(PDO::FETCH_ASSOC);
        } else {
            // 3. Fetch top 3 students
            $sql_top3 = "SELECT s.id AS student_id, SUM(a.marks_obtained) as marks_obtained, s.name 
                         FROM assignment_submission a 
                         JOIN students s ON a.student_id = s.user_id 
                         WHERE a.assignment_id = ? AND a.institution_id = ? AND a.branch_id = ? AND a.status = 1  
                         GROUP BY a.student_id
                         ORDER BY marks_obtained DESC
                         LIMIT 3";
            $stmt_top3 = $conn_rjllearning_specific->prepare($sql_top3);
            $stmt_top3->execute([$assignment_id, $institution_id, $branch_id]);
            $top3_scores = $stmt_top3->fetchAll(PDO::FETCH_ASSOC);

            // 4. Fetch bottom 3 students
            $sql_low3 = "SELECT s.id AS student_id, SUM(a.marks_obtained) as marks_obtained, s.name 
                         FROM assignment_submission a 
                         JOIN students s ON a.student_id = s.user_id 
                         WHERE a.assignment_id = ? AND a.institution_id = ? AND a.branch_id = ? AND a.status = 1  
                         GROUP BY a.student_id
                         ORDER BY marks_obtained ASC
                         LIMIT 3";
            $stmt_low3 = $conn_rjllearning_specific->prepare($sql_low3);
            $stmt_low3->execute([$assignment_id, $institution_id, $branch_id]);
            $low3_scores = $stmt_low3->fetchAll(PDO::FETCH_ASSOC);

            // Only add top3_scores and low3_scores if there are multiple students
            if (!empty($top3_scores)) {
                $stats['top3_scores'] = $top3_scores;
            }
            if (!empty($low3_scores)) {
                $stats['low3_scores'] = $low3_scores;
            }
        }
    } catch (Exception $e) {
        // Log the error or handle it as needed
        error_log("Error in get_assignment_stats: " . $e->getMessage());
        $stats['error'] = $e->getMessage();
    }

    return $stats;
}



// function get_question_stats($question_id, $conn_rjllearning_specific, $institution_id, $branch_id)
// {
//     // Initialize response array
//     $stats = [
//         'question_detail' => [
//             'question_id' => $question_id,
//             'question_type' => null
//         ],
//         'assignment_detail' => [
//             'assignment_id' => null,
//             'assignment_name' => null
//         ],
//         'question_marks' => 0,
//         'average_marks' => 0,
//         'highest_marks' => 0,
//         'lowest_marks' => 0,
//         'mode' => 0
//     ];

//     try {
//         // Fetch question details including assignment_id and question_type
//         $sql_question = "SELECT aq.assignment_id, aq.question_marks, aq.question_type, a.name as assignment_name
//                          FROM assignment_questions aq
//                          JOIN assignments a ON aq.assignment_id = a.id
//                          WHERE aq.id = ? AND aq.institution_id = ? AND aq.branch_id = ?";
//         $stmt_question = $conn_rjllearning_specific->prepare($sql_question);
//         $stmt_question->execute([$question_id, $institution_id, $branch_id]);
//         $question_row = $stmt_question->fetch(PDO::FETCH_ASSOC);

//         if (!$question_row) {
//             errorreturn("Question not found or invalid question ID.");
//         }

//         $stats['question_detail']['question_type'] = $question_row['question_type'];
//         $stats['assignment_detail']['assignment_id'] = $question_row['assignment_id'];
//         $stats['assignment_detail']['assignment_name'] = $question_row['assignment_name'];
//         $stats['question_marks'] = $question_row['question_marks'];

//         // 1. Average marks for the question (Remove decimals only if not needed)
//         $sql_avg = "SELECT AVG(marks_obtained) as average_marks 
//                     FROM assignment_submission 
//                     WHERE assignment_questions_id = ? 
//                     AND institution_id = ? AND branch_id = ? AND status = 1";
//         $stmt_avg = $conn_rjllearning_specific->prepare($sql_avg);
//         $stmt_avg->execute([$question_id, $institution_id, $branch_id]);
//         $avg_row = $stmt_avg->fetch(PDO::FETCH_ASSOC);

//         // if ($avg_row['average_marks'] !== null) {
//         //     $stats['average_marks'] = number_format($avg_row['average_marks'], 1);
//         // }

//         if ($avg_row['average_marks'] !== null) {
//             $stats['average_marks'] = $avg_row['average_marks'];

//             // Check if the number is a float (has decimals)
//             if (fmod($stats['average_marks'], 1) != 0) {
//                 $stats['average_marks'] = number_format($stats['average_marks'], 1); // Format to 1 decimal place
//             } else {
//                 $stats['average_marks'] = (int) $stats['average_marks']; // Keep it as an integer
//             }
//         }

//         // 2. Highest marks for the question
//         $sql_highest = "SELECT MAX(marks_obtained) as highest_marks 
//                         FROM assignment_submission 
//                         WHERE assignment_questions_id = ? 
//                         AND institution_id = ? AND branch_id = ? AND status = 1";
//         $stmt_highest = $conn_rjllearning_specific->prepare($sql_highest);
//         $stmt_highest->execute([$question_id, $institution_id, $branch_id]);
//         $high_row = $stmt_highest->fetch(PDO::FETCH_ASSOC);
//         // $stats['highest_marks'] = (int) ($high_row['highest_marks'] ?? 0);
//         $stats['highest_marks'] = isset($high_row['highest_marks']) ? (int) $high_row['highest_marks'] : 0;


//         // 3. Lowest marks for the question
//         $sql_lowest = "SELECT MIN(marks_obtained) as lowest_marks 
//                       FROM assignment_submission 
//                       WHERE assignment_questions_id = ? 
//                       AND institution_id = ? AND branch_id = ? AND status = 1";
//         $stmt_lowest = $conn_rjllearning_specific->prepare($sql_lowest);
//         $stmt_lowest->execute([$question_id, $institution_id, $branch_id]);
//         $low_row = $stmt_lowest->fetch(PDO::FETCH_ASSOC);
//         // $stats['lowest_marks'] = (int) ($low_row['lowest_marks'] ?? 0);
//         $stats['lowest_marks'] = isset($low_row['lowest_marks']) ? (int) $low_row['lowest_marks'] : 0;


//         // 4. Mode (Most Frequent Mark)
//         $sql_mode = "SELECT marks_obtained FROM assignment_submission WHERE assignment_questions_id = ? AND institution_id = ? 
//                      AND branch_id = ? AND status = 1 GROUP BY marks_obtained HAVING COUNT(*) > 1 
//                      ORDER BY COUNT(*) DESC, marks_obtained DESC 
//                      LIMIT 1";
//         $stmt_mode = $conn_rjllearning_specific->prepare($sql_mode);
//         $stmt_mode->execute([$question_id, $institution_id, $branch_id]);
//         $mode_row = $stmt_mode->fetch(PDO::FETCH_ASSOC);
//         $stats['mode'] = $mode_row ? (int) $mode_row['marks_obtained'] : null;
//     } catch (Exception $e) {
//         error_log("Error in get_question_stats: " . $e->getMessage());
//         $stats['error'] = $e->getMessage();
//     }

//     return $stats;
// }
