<?php
include('session_validator.php');
// Include database connection code
include("includes/db.php");

// Initialize variables
$legclass = '';
$examtype = '';
$examyear = '';
$ledger = [];
$selected_sections = [];
$sections = [];
$displaySections = [];

// Check if form is submitted
if (isset($_GET['class_id']) && isset($_GET['exam_id'])) {
    $classId = $_GET['class_id'];
    $examId = $_GET['exam_id'];
    $selected_sections = $_GET['section_id'] ?? [];

    // Handle single selection or "all" option
    if (is_string($selected_sections)) {
        $selected_sections = [$selected_sections];
    }
    
    // Check if "all" is selected or no sections selected
    if (in_array('all', $selected_sections) || empty($selected_sections)) {
        $selected_sections = 'all';
    }

    // Get exam details
    $examquery = "SELECT * FROM examination_type WHERE id = ?";
    $examstmt = $conn->prepare($examquery);
    $examstmt->bind_param("i", $examId);
    $examstmt->execute();
    $examresult = $examstmt->get_result();
    $examDetails = $examresult->fetch_assoc();
    $examtype = $examDetails['exam_type'] ?? '';
    $examyear = $examDetails['exm_year'] ?? '';
    $examstmt->close();

    // Get class details
    $classquery = "SELECT class_id, class, class_numeric FROM classes WHERE class_id = ?";
    $classstmt = $conn->prepare($classquery);
    $classstmt->bind_param("i", $classId);
    $classstmt->execute();
    $classresult = $classstmt->get_result();
    $classDetails = $classresult->fetch_assoc();
    $legclass = $classDetails['class'] ?? '';
    $class_numeric = $classDetails['class_numeric'] ?? '';
    $classstmt->close();

    // Get sections for this class
    $sectionquery = "SELECT s.section_id, s.section_name 
                    FROM sections s
                    INNER JOIN class_section cs ON s.section_id = cs.section_id
                    WHERE cs.class_id = ? AND s.status = 1
                    ORDER BY s.section_name";
    $sectionstmt = $conn->prepare($sectionquery);
    $sectionstmt->bind_param("i", $classId);
    $sectionstmt->execute();
    $sectionresult = $sectionstmt->get_result();
    while ($row = $sectionresult->fetch_assoc()) {
        $sections[] = $row;
    }
    $sectionstmt->close();

    // Build WHERE clause for sections
    $sectionWhere = "";
    $sectionParams = [];
    $sectionTypes = "";
    
    if ($selected_sections !== 'all' && !empty($selected_sections)) {
        // Create placeholders for multiple sections
        $placeholders = str_repeat('?,', count($selected_sections) - 1) . '?';
        $sectionWhere = " AND m.section_id IN ($placeholders)";
        $sectionParams = $selected_sections;
        $sectionTypes = str_repeat('i', count($selected_sections));
    } else {
        // Get all section IDs for this class
        $allSectionIds = array_column($sections, 'section_id');
        if (!empty($allSectionIds)) {
            $placeholders = str_repeat('?,', count($allSectionIds) - 1) . '?';
            $sectionWhere = " AND m.section_id IN ($placeholders)";
            $sectionParams = $allSectionIds;
            $sectionTypes = str_repeat('i', count($allSectionIds));
        }
    }

    // Get display section names
    $displaySections = [];
    if ($selected_sections === 'all') {
        $displaySections = array_column($sections, 'section_name');
    } else {
        foreach ($sections as $section) {
            if (in_array($section['section_id'], $selected_sections)) {
                $displaySections[] = $section['section_name'];
            }
        }
    }
    
    // Get all subjects assigned to this class from class_subject_mapping
    $subjectQuery = "SELECT DISTINCT 
                        s.id as subject_id,
                        s.subject_code,
                        s.subject_name,
                        s.theory_full_marks,
                        s.internal_full_marks,
                        s.theory_credit_hour,
                        s.internal_credit_hour
                    FROM class_subject_mapping csm
                    INNER JOIN subjects s ON csm.subject_id = s.id
                    WHERE csm.class_id = ?
                    ORDER BY s.subject_name";
    
    $subjectStmt = $conn->prepare($subjectQuery);
    $subjectStmt->bind_param("i", $classId);
    $subjectStmt->execute();
    $subjectResult = $subjectStmt->get_result();
    
    $allClassSubjects = [];
    while ($row = $subjectResult->fetch_assoc()) {
        $allClassSubjects[] = $row;
    }
    $subjectStmt->close();

    // Query students, subjects, and marks for the specific class, exam, and selected sections
    $query = "SELECT 
                s.student_id, 
                s.name AS student_name,
                s.symbol_no,
                s.section as student_section,
                sub.subject_code, 
                sub.subject_name, 
                sub.theory_full_marks, 
                sub.internal_full_marks, 
                sub.theory_credit_hour, 
                sub.internal_credit_hour,
                m.marks,
                m.mark_type,
                m.full_marks as exam_full_marks,
                m.section_id,
                sec.section_name
              FROM marks m
              INNER JOIN students s ON m.student_id = s.student_id
              INNER JOIN subjects sub ON m.subject_id = sub.id
              LEFT JOIN sections sec ON m.section_id = sec.section_id
              WHERE m.class_id = ? 
                AND m.exam_type = ?
                AND m.mark_type = 'theory'";

    // Add section condition if we have sections to filter
    if ($sectionWhere) {
        $query .= $sectionWhere;
    }
    
    $query .= " ORDER BY sec.section_name, s.name, sub.subject_code";

    // Prepare and execute query
    $stmt = $conn->prepare($query);
    
    // Bind parameters
    if (!empty($sectionParams)) {
        // Combine class_id, exam_id, and section parameters
        $allParams = array_merge([$classId, $examId], $sectionParams);
        $allTypes = "ii" . $sectionTypes;
        $stmt->bind_param($allTypes, ...$allParams);
    } else {
        $stmt->bind_param("ii", $classId, $examId);
    }
    
    $stmt->execute();
    $result = $stmt->get_result();

    // Prepare data for display
    $currentStudentId = null;
    $totalMarks = 0;
    $TotalWGP = 0;
    $currentSection = '';

    while ($row = $result->fetch_assoc()) {
        $studentId = $row['student_id'];
        $studentSection = $row['section_name'] ?? $row['student_section'];
        $subjectName = $row['subject_name'];
        $subjectcode = $row['subject_code'];
        $marks = $row['marks'];
        $exam_full_marks = $row['exam_full_marks'];
        $theory_full_marks = $row['theory_full_marks'];
        $full_marks = ($exam_full_marks > 0) ? $exam_full_marks : $theory_full_marks;
        $creditHour = $row['theory_credit_hour'] ?: 0;

        // Track section changes
        if ($currentSection !== $studentSection) {
            $currentSection = $studentSection;
        }

        if ($studentId != $currentStudentId) {
            // Save total marks for the previous student and reset total marks
            if ($currentStudentId !== null) {
                $currentStudentData['total_marks'] = $totalMarks;
                $currentStudentData['totalwgp'] = $TotalWGP;
                $currentStudentData['section'] = $currentSection;
                $ledger[] = $currentStudentData;
            }

            // Initialize data for the new student
            $currentStudentId = $studentId;
            $currentStudentData = [
                'sn' => count($ledger) + 1,
                'name' => $row['student_name'],
                'symbol' => $row['symbol_no'] ?? '',
                'section' => $studentSection,
                'subjects' => [],
                'total_marks' => 0,
                'totalwgp' => 0
            ];

            // Reset totals for the new student
            $totalMarks = 0;
            $TotalWGP = 0;
        }

        // Add subject marks for the current student
        $currentStudentData['subjects'][] = [
            'subject' => $subjectName,
            'subject_code' => $subjectcode,
            'marks' => $marks,
            'full_marks' => $full_marks,
            'creditHour' => $creditHour
        ];

        // Calculate total marks for the current student
        $totalMarks += $marks;

        // Calculate Total WGP for the current student
        if ($full_marks > 0) {
            $GP = calculateGradePoint($marks, $full_marks);
            $TotalWGP += ($GP * $creditHour);
        }
    }

    // Add the last student's total marks and Total WGP to the ledger
    if ($currentStudentId !== null) {
        $currentStudentData['total_marks'] = $totalMarks;
        $currentStudentData['totalwgp'] = $TotalWGP;
        $currentStudentData['section'] = $currentSection;
        $ledger[] = $currentStudentData;
    }
    
    $stmt->close();
}

/**
 * Function to calculate Grade Point (GP) based on obtained marks and full marks.
 *
 * @param float $obtainedMarks
 * @param float $fullMarks
 * @return float
 */
function calculateGradePoint($obtainedMarks, $fullMarks)
{
    if ($fullMarks <= 0) return 0;
    
    $MP = ($obtainedMarks / $fullMarks);

    if ($MP >= 0.9) {
        return 4.0;
    } elseif ($MP >= 0.8) {
        return 3.6;
    } elseif ($MP >= 0.7) {
        return 3.2;
    } elseif ($MP >= 0.6) {
        return 2.8;
    } elseif ($MP >= 0.5) {
        return 2.4;
    } elseif ($MP >= 0.4) {
        return 2.0;
    } elseif ($MP >= 0.35) {
        return 1.6;
    } elseif ($MP >= 0.0001) {
        return 0.0;
    } else {
        return 0.0; // For ABSENT
    }
}

/**
 * Function to calculate Grade based on obtained marks and full marks.
 *
 * @param float $obtainedMarks
 * @param float $fullMarks
 * @return string
 */
function calculateGrade($obtainedMarks, $fullMarks)
{
    if ($fullMarks <= 0) return 'Abs';
    
    $MP1 = ($obtainedMarks / $fullMarks);

    if ($MP1 >= 0.9) {
        return 'A+';
    } elseif ($MP1 >= 0.8) {
        return 'A';
    } elseif ($MP1 >= 0.7) {
        return 'B+';
    } elseif ($MP1 >= 0.6) {
        return 'B';
    } elseif ($MP1 >= 0.5) {
        return 'C+';
    } elseif ($MP1 >= 0.4) {
        return 'C';
    } elseif ($MP1 >= 0.35) {
        return 'D';
    } elseif ($MP1 >= 0.0001) {
        return 'NG';
    } else {
        return 'Abs'; // For ABSENT
    }
}

// Function to calculate subject-wise statistics
function calculateSubjectStatistics($ledger, $subjectName) {
    $subjectStats = [
        'total_marks' => 0,
        'students_with_marks' => 0,
        'grade_counts' => array_fill_keys(['A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'NG', 'Abs'], 0),
        'total_grade_point' => 0
    ];
    
    foreach ($ledger as $student) {
        foreach ($student['subjects'] as $subject) {
            if ($subject['subject'] === $subjectName) {
                $marks = $subject['marks'];
                $full_marks = $subject['full_marks'];
                
                $subjectStats['total_marks'] += $marks;
                if ($marks > 0) {
                    $subjectStats['students_with_marks']++;
                }
                
                $grade = calculateGrade($marks, $full_marks);
                $subjectStats['grade_counts'][$grade]++;
                
                $GP = calculateGradePoint($marks, $full_marks);
                $creditHour = $subject['creditHour'] ?: 0;
                $subjectStats['total_grade_point'] += ($GP * $creditHour);
            }
        }
    }
    
    return $subjectStats;
}

// Function to calculate section-wise statistics
function calculateSectionStatistics($ledger) {
    $sectionStats = [];
    
    foreach ($ledger as $student) {
        $section = $student['section'] ?? 'Unknown';
        
        if (!isset($sectionStats[$section])) {
            $sectionStats[$section] = [
                'student_count' => 0,
                'total_marks' => 0,
                'passed_students' => 0,
                'failed_students' => 0
            ];
        }
        
        $sectionStats[$section]['student_count']++;
        $sectionStats[$section]['total_marks'] += $student['total_marks'];
        
        // Simple pass/fail logic (adjust as needed)
        $total_subjects = count($student['subjects']);
        $passed_subjects = 0;
        foreach ($student['subjects'] as $subject) {
            if ($subject['marks'] / $subject['full_marks'] >= 0.4) {
                $passed_subjects++;
            }
        }
        
        if ($passed_subjects >= $total_subjects * 0.6) { // 60% subjects passed
            $sectionStats[$section]['passed_students']++;
        } else {
            $sectionStats[$section]['failed_students']++;
        }
    }
    
    return $sectionStats;
}

// Function to get all subjects assigned to class
function getAllClassSubjects($conn, $classId) {
    $subjectQuery = "SELECT DISTINCT 
                        s.id as subject_id,
                        s.subject_code,
                        s.subject_name,
                        s.theory_full_marks,
                        s.internal_full_marks,
                        s.theory_credit_hour,
                        s.internal_credit_hour
                    FROM class_subject_mapping csm
                    INNER JOIN subjects s ON csm.subject_id = s.id
                    WHERE csm.class_id = ?
                    ORDER BY s.subject_name";
    
    $subjectStmt = $conn->prepare($subjectQuery);
    $subjectStmt->bind_param("i", $classId);
    $subjectStmt->execute();
    $subjectResult = $subjectStmt->get_result();
    
    $subjects = [];
    while ($row = $subjectResult->fetch_assoc()) {
        $subjects[] = $row;
    }
    $subjectStmt->close();
    
    return $subjects;
}

// Function to get unique subjects from ledger and combine with class subjects
function getAllUniqueSubjects($ledger, $allClassSubjects) {
    $uniqueSubjectsFromMarks = [];
    
    if (!empty($ledger) && !empty($ledger[0]['subjects'])) {
        foreach ($ledger[0]['subjects'] as $subject) {
            if (!in_array($subject['subject'], $uniqueSubjectsFromMarks)) {
                $uniqueSubjectsFromMarks[] = $subject['subject'];
            }
        }
    }
    
    // Get all subjects from class assignment
    $allClassSubjectNames = array_column($allClassSubjects, 'subject_name');
    
    // Combine and remove duplicates
    $allUniqueSubjects = array_unique(array_merge($allClassSubjectNames, $uniqueSubjectsFromMarks));
    
    // Sort alphabetically
    sort($allUniqueSubjects);
    
    return $allUniqueSubjects;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>SRMS - Result Analysis</title>
    <link rel="stylesheet" href="css/bootstrap.min.css" media="screen">
    <link rel="stylesheet" href="css/font-awesome.min.css" media="screen">
    <link rel="stylesheet" href="css/animate-css/animate.min.css" media="screen">
    <link rel="stylesheet" href="css/lobipanel/lobipanel.min.css" media="screen">
    <link rel="stylesheet" href="css/prism/prism.css" media="screen">
    <link rel="stylesheet" href="css/select2/select2.min.css">
    <link rel="stylesheet" href="css/main.css" media="screen">
    <script src="js/modernizr/modernizr.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css">
    <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>
    <style>
        #customers {
            border-collapse: collapse;
            width: 100%;
            margin-bottom: 20px;
        }
        
        #customers th, #customers td {
            border: 1px solid #ddd;
            padding: 6px;
            text-align: center;
            font-size: 12px;
        }
        
        #customers th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        
        .section-header {
            background-color: #e3f2fd !important;
            font-weight: bold;
            text-align: left !important;
        }
        
        .print-only {
            display: none;
        }
        
        .select2-container--default .select2-selection--multiple {
            height: auto;
            min-height: 34px;
            padding: 3px;
        }
        
        .select2-container--default.select2-container--focus .select2-selection--multiple {
            border-color: #80bdff;
            box-shadow: 0 0 0 0.2rem rgba(0,123,255,.25);
        }
        
        .select2-container--default .select2-selection--multiple .select2-selection__choice {
            background-color: #007bff;
            border: 1px solid #006fe6;
            color: white;
            padding: 2px 8px;
            margin: 2px;
        }
        
        .stats-card {
            background: #f8f9fa;
            border: 1px solid #dee2e6;
            border-radius: 5px;
            padding: 15px;
            margin-bottom: 20px;
        }
        
        .stat-item {
            display: inline-block;
            margin-right: 20px;
            margin-bottom: 10px;
        }
        
        .stat-value {
            font-size: 24px;
            font-weight: bold;
            color: #007bff;
        }
        
        .stat-label {
            font-size: 14px;
            color: #6c757d;
        }
        
        .section-badge {
            display: inline-block;
            background-color: #007bff;
            color: white;
            padding: 3px 10px;
            border-radius: 15px;
            font-size: 12px;
            margin: 2px;
            font-weight: 500;
        }
        
        @media print {
            .no-print {
                display: none !important;
            }
            .print-only {
                display: block;
            }
            body * {
                visibility: hidden;
            }
            #customers, #customers * {
                visibility: visible;
            }
            #customers {
                position: absolute;
                left: 0;
                top: 0;
                width: 100%;
            }
        }
        
        .selected-sections-display {
            background-color: #f8f9fa;
            border: 1px solid #dee2e6;
            border-radius: 5px;
            padding: 10px;
            margin-top: 10px;
        }
        
        .no-marks {
            color: #999;
            font-style: italic;
        }
    </style>
</head>
<body class="top-navbar-fixed">
    <div class="main-wrapper">
        <!-- ========== TOP NAVBAR ========== -->
        <?php include('includes/topbar.php'); ?>
        
        <!-- ========== WRAPPER FOR BOTH SIDEBARS & MAIN CONTENT ========== -->
        <div class="content-wrapper">
            <div class="content-container">
                <!-- ========== LEFT SIDEBAR ========== -->
                <?php include('includes/leftbar.php'); ?>
                
                <div class="main-page">
                    <div class="container-fluid">
                        <div class="row page-title-div">
                            <div class="col-md-6">
                                <h2 class="title">Result Analysis</h2>
                            </div>
                        </div>
                        
                        <div class="row breadcrumb-div">
                            <div class="col-md-6">
                                <ul class="breadcrumb">
                                    <li><a href="dashboard.php"><i class="fa fa-home"></i> Home</a></li>
                                    <li> Reports </li>
                                    <li class="">Average Report</li>
                                </ul>
                            </div>
                        </div>
                    </div>
                    
                    <div class="container-fluid">
                        <div class="row">
                            <div class="col-md-12">
                                <div class="panel">
                                    <div class="panel-heading">
                                        <div class="panel-title">
                                            <h5>Result Analysis</h5>
                                        </div>
                                    </div>
                                    <div class="panel-body">
                                        <div class="container no-print">
                                            <form action="average.php" method="get" id="analysisForm">
                                                <div class="row">
                                                    <div class="col-md-3">
                                                        <div class="form-group">
                                                            <label for="exam_id">Select Exam:</label>
                                                            <select id="exam_id" name="exam_id" class="form-control select2" required>
                                                                <option value="">Select Exam</option>
                                                                <?php
                                                                // Fetch exams from examination_type table
                                                                $queryexam = "SELECT id, exm_year, exam_type FROM examination_type ORDER BY exm_year DESC, id DESC";
                                                                $resultexam = $conn->query($queryexam);
                                                                
                                                                while ($row = $resultexam->fetch_assoc()) {
                                                                    $selected = (isset($_GET['exam_id']) && $_GET['exam_id'] == $row['id']) ? 'selected' : '';
                                                                    echo "<option value='" . $row['id'] . "' $selected>" . 
                                                                         htmlspecialchars($row['exam_type'] . " - " . $row['exm_year']) . "</option>";
                                                                }
                                                                ?>
                                                            </select>
                                                        </div>
                                                    </div>
                                                    
                                                    <div class="col-md-3">
                                                        <div class="form-group">
                                                            <label for="class_id">Select Class:</label>
                                                            <select name="class_id" id="class_id" class="form-control select2" onchange="loadSections(this.value)">
                                                                <option value="">Select Class</option>
                                                                <?php
                                                                // Query classes from the database
                                                                $classQuery = "SELECT DISTINCT c.class_id, c.class 
                                                                              FROM classes c
                                                                              INNER JOIN class_section cs ON c.class_id = cs.class_id
                                                                              WHERE c.status = 1
                                                                              ORDER BY c.class_numeric";
                                                                $classResult = $conn->query($classQuery);
                                                                
                                                                while ($row = $classResult->fetch_assoc()) {
                                                                    $selected = (isset($_GET['class_id']) && $_GET['class_id'] == $row['class_id']) ? 'selected' : '';
                                                                    echo "<option value='" . $row['class_id'] . "' $selected>" . 
                                                                         htmlspecialchars($row['class']) . "</option>";
                                                                }
                                                                ?>
                                                            </select>
                                                        </div>
                                                    </div>
                                                    
                                                    <div class="col-md-3">
                                                        <div class="form-group">
                                                            <label for="section_id">Select Sections:</label>
                                                            <select name="section_id[]" id="section_id" class="form-control select2" multiple="multiple">
                                                                <option value="all">All Sections</option>
                                                                <?php
                                                                if (isset($_GET['class_id']) && !empty($sections)) {
                                                                    foreach ($sections as $section) {
                                                                        $selected = '';
                                                                        if (isset($_GET['section_id'])) {
                                                                            if (is_array($_GET['section_id'])) {
                                                                                $selected = in_array($section['section_id'], $_GET['section_id']) ? 'selected' : '';
                                                                            } else {
                                                                                $selected = ($_GET['section_id'] == $section['section_id']) ? 'selected' : '';
                                                                            }
                                                                        }
                                                                        echo "<option value='" . $section['section_id'] . "' $selected>" . 
                                                                             htmlspecialchars($section['section_name']) . "</option>";
                                                                    }
                                                                }
                                                                ?>
                                                            </select>
                                                            <small class="text-muted">Hold Ctrl/Cmd to select multiple sections. Select "All Sections" for complete analysis.</small>
                                                        </div>
                                                    </div>
                                                    
                                                    <div class="col-md-3">
                                                        <div class="form-group" style="padding-top: 28px;">
                                                            <button type="submit" class="btn btn-primary">
                                                                <i class="fa fa-search"></i> Show Analysis
                                                            </button>
                                                            <button type="button" onclick="resetForm()" class="btn btn-default">
                                                                <i class="fa fa-refresh"></i> Reset
                                                            </button>
                                                        </div>
                                                    </div>
                                                </div>
                                                <div class="row">
                                                    <div class="col-md-12">
                                                        <div id="selectedSectionsDisplay" class="selected-sections-display" style="display: none;">
                                                            <strong>Selected Sections:</strong>
                                                            <div id="selectedSectionsList"></div>
                                                        </div>
                                                    </div>
                                                </div>
                                            </form>
                                        </div>
                                        
                                        <?php if (isset($ledger) && !empty($ledger)) : ?>
                                        <?php 
                                        // Calculate overall statistics
                                        $totalStudents = count($ledger);
                                        $totalMarksAll = array_sum(array_column($ledger, 'total_marks'));
                                        $averageMarksAll = ($totalStudents > 0) ? number_format($totalMarksAll / $totalStudents, 2) : 0;
                                        
                                        // Get all unique subjects (both from marks and class assignment)
                                        $allUniqueSubjects = getAllUniqueSubjects($ledger, $allClassSubjects ?? []);
                                        
                                        // Calculate section statistics
                                        $sectionStats = calculateSectionStatistics($ledger);
                                        
                                        // Calculate passed students
                                        $passedStudents = 0;
                                        foreach ($ledger as $student) {
                                            $total_subjects = count($student['subjects']);
                                            $passed_subjects = 0;
                                            foreach ($student['subjects'] as $subject) {
                                                if ($subject['marks'] / $subject['full_marks'] >= 0.4) {
                                                    $passed_subjects++;
                                                }
                                            }
                                            if ($passed_subjects >= $total_subjects * 0.6) {
                                                $passedStudents++;
                                            }
                                        }
                                        ?>
                                        
                                        <div class="mt-4">
                                            <div class="stats-card">
                                                <div class="row">
                                                    <div class="col-md-12">
                                                        <h4>Result Analysis for <?php echo htmlspecialchars($legclass); ?> 
                                                            <?php if (!empty($displaySections)): ?>
                                                            - 
                                                            <?php foreach ($displaySections as $section): ?>
                                                            <span class="section-badge"><?php echo htmlspecialchars($section); ?></span>
                                                            <?php endforeach; ?>
                                                            <?php endif; ?>
                                                            in <?php echo htmlspecialchars($examtype . " (" . $examyear . ")"); ?>
                                                        </h4>
                                                        <?php if (!empty($allClassSubjects)): ?>
                                                        <p><strong>Subjects assigned to class:</strong> 
                                                            <?php 
                                                            $subjectNames = array_column($allClassSubjects, 'subject_name');
                                                            echo htmlspecialchars(implode(', ', $subjectNames));
                                                            ?>
                                                        </p>
                                                        <?php endif; ?>
                                                    </div>
                                                </div>
                                                <div class="row">
                                                    <div class="col-md-2">
                                                        <div class="stat-item">
                                                            <div class="stat-value"><?php echo $totalStudents; ?></div>
                                                            <div class="stat-label">Students</div>
                                                        </div>
                                                    </div>
                                                    <div class="col-md-2">
                                                        <div class="stat-item">
                                                            <div class="stat-value"><?php echo count($allUniqueSubjects); ?></div>
                                                            <div class="stat-label">Subjects</div>
                                                        </div>
                                                    </div>
                                                    <div class="col-md-2">
                                                        <div class="stat-item">
                                                            <div class="stat-value"><?php echo $averageMarksAll; ?></div>
                                                            <div class="stat-label">Avg Marks</div>
                                                        </div>
                                                    </div>
                                                    <div class="col-md-2">
                                                        <div class="stat-item">
                                                            <div class="stat-value"><?php echo count($displaySections); ?></div>
                                                            <div class="stat-label">Sections</div>
                                                        </div>
                                                    </div>
                                                    <div class="col-md-2">
                                                        <div class="stat-item">
                                                            <div class="stat-value"><?php echo count($sectionStats); ?></div>
                                                            <div class="stat-label">Active Sections</div>
                                                        </div>
                                                    </div>
                                                    <div class="col-md-2">
                                                        <div class="stat-item">
                                                            <div class="stat-value"><?php echo $passedStudents; ?></div>
                                                            <div class="stat-label">Passed Students</div>
                                                        </div>
                                                    </div>
                                                </div>
                                            </div>
                                            
                                            <div class="no-print mb-3">
                                                <button onclick="printTable()" class="btn btn-primary">
                                                    <i class="fa fa-print"></i> Print Report
                                                </button>
                                                <button id="exportExcelBtn" class="btn btn-success">
                                                    <i class="fa fa-file-excel-o"></i> Export to Excel
                                                </button>
                                                <button onclick="exportToPDF()" class="btn btn-danger">
                                                    <i class="fa fa-file-pdf-o"></i> Export to PDF
                                                </button>
                                            </div>
                                            
                                            <!-- Section-wise Statistics (if multiple sections) -->
                                            <?php if (count($sectionStats) > 1): ?>
                                       <table id="customers" class="no-print">
                                                <thead>
                                                    <tr>
                                                        <td colspan="5" style="border:none;font-size: 16px; text-align: center;">
                                                            <b><u>Section-wise Performance Summary</u></b>
                                                        </td>
                                                    </tr>
                                                    <tr>
                                                        <th>Section</th>
                                                        <th>Students</th>
                                                        <th>Average Marks</th>
                                                        <th>Passed</th>
                                                        <th>Failed</th>
                                                        <th>Pass %</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach ($sectionStats as $sectionName => $stats): ?>
                                                    <?php 
                                                    $sectionAvg = ($stats['student_count'] > 0) ? 
                                                                number_format($stats['total_marks'] / $stats['student_count'], 2) : 0;
                                                    $passPercentage = ($stats['student_count'] > 0) ? 
                                                                    number_format(($stats['passed_students'] / $stats['student_count']) * 100, 2) : 0;
                                                    ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($sectionName); ?></td>
                                                        <td><?php echo $stats['student_count']; ?></td>
                                                        <td><?php echo $sectionAvg; ?></td>
                                                        <td><?php echo $stats['passed_students']; ?></td>
                                                        <td><?php echo $stats['failed_students']; ?></td>
                                                        <td><?php echo $passPercentage; ?>%</td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                    <tr style="background-color: #f0f0f0; font-weight: bold;">
                                                        <td>Total</td>
                                                        <td><?php echo array_sum(array_column($sectionStats, 'student_count')); ?></td>
                                                        <td><?php 
                                                            $totalStudentsAll = array_sum(array_column($sectionStats, 'student_count'));
                                                            $totalMarksAll = array_sum(array_column($sectionStats, 'total_marks'));
                                                            echo ($totalStudentsAll > 0) ? number_format($totalMarksAll / $totalStudentsAll, 2) : 0;
                                                        ?></td>
                                                        <td><?php echo array_sum(array_column($sectionStats, 'passed_students')); ?></td>
                                                        <td><?php echo array_sum(array_column($sectionStats, 'failed_students')); ?></td>
                                                        <td><?php 
                                                            $totalPassed = array_sum(array_column($sectionStats, 'passed_students'));
                                                            echo ($totalStudentsAll > 0) ? number_format(($totalPassed / $totalStudentsAll) * 100, 2) : 0; ?>%
                                                        </td>
                                                    </tr>
                                                </tbody>
                                            </table>
                                            <?php endif; ?>
                                            
                                            <!-- Main Analysis Table -->
                                            <table id="customers" class="no-print">
                                                <thead>
                                                    <tr>
                                                        <td colspan="<?php echo count($allUniqueSubjects) * 2 + (count($displaySections) > 1 ? 4 : 3); ?>" style="border:none;font-size: 18px; text-align: center;">
                                                            <b><u>Detailed Result Analysis</u></b>
                                                        </td>
                                                    </tr>
                                                    <tr>
                                                        <th rowspan="2" style="width:150px">Student Name</th>
                                                        <th rowspan="2" style="width:80px">Symbol No</th>
                                                        <?php if (count($displaySections) > 1): ?>
                                                        <th rowspan="2" style="width:80px">Section</th>
                                                        <?php endif; ?>
                                                        <?php foreach ($allUniqueSubjects as $subject) : ?>
                                                        <th colspan="2" style="font-size: 12px;">
                                                            <?= htmlspecialchars($subject) ?>
                                                        </th>
                                                        <?php endforeach; ?>
                                                        <th rowspan="2" style="width:80px">Total</th>
                                                        <th rowspan="2" style="width:80px">Average</th>
                                                        <th rowspan="2" style="width:60px">Status</th>
                                                    </tr>
                                                    <tr>
                                                        <?php foreach ($allUniqueSubjects as $subject) : ?>
                                                        <th style="width:60px">Marks</th>
                                                        <th style="width:60px">Grade</th>
                                                        <?php endforeach; ?>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php 
                                                    $subjectTotals = array_fill(0, count($allUniqueSubjects), 0);
                                                    $studentCounts = array_fill(0, count($allUniqueSubjects), 0);
                                                    $gradeCounts = array_fill_keys($allUniqueSubjects, array_fill_keys(['A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'NG', 'Abs'], 0));
                                                    $currentDisplaySection = '';
                                                    ?>
                                                    
                                                    <?php foreach ($ledger as $student) : ?>
                                                    <?php 
                                                    // Add section header if showing multiple sections
                                                    if (count($displaySections) > 1 && $currentDisplaySection !== $student['section']) {
                                                        $currentDisplaySection = $student['section'];
                                                        echo '<tr class="section-header">';
                                                        echo '<td colspan="' . (count($allUniqueSubjects) * 2 + (count($displaySections) > 1 ? 4 : 3)) . '">';
                                                        echo '<strong>Section: ' . htmlspecialchars($currentDisplaySection) . '</strong>';
                                                        echo '</td>';
                                                        echo '</tr>';
                                                    }
                                                    ?>
                                                    <tr>
                                                        <td><?= htmlspecialchars($student['name']) ?></td>
                                                        <td><?= htmlspecialchars($student['symbol']) ?></td>
                                                        <?php if (count($displaySections) > 1): ?>
                                                        <td><?= htmlspecialchars($student['section']) ?></td>
                                                        <?php endif; ?>
                                                        
                                                        <?php 
                                                        $studentSubjectIndex = 0;
                                                        $studentTotal = 0;
                                                        $passedSubjects = 0;
                                                        $totalSubjects = count($allUniqueSubjects);
                                                        ?>
                                                        
                                                        <?php foreach ($allUniqueSubjects as $subjectIndex => $subjectName) : ?>
                                                            <?php 
                                                            $subjectFound = false;
                                                            $subjectMarks = 0;
                                                            $subjectGrade = 'Abs';
                                                            $subjectFullMarks = 0;
                                                            ?>
                                                            
                                                            <?php foreach ($student['subjects'] as $subject) : ?>
                                                                <?php if ($subject['subject'] === $subjectName) : ?>
                                                                    <?php 
                                                                    $subjectFound = true;
                                                                    $subjectMarks = $subject['marks'];
                                                                    $subjectFullMarks = $subject['full_marks'];
                                                                    $subjectGrade = calculateGrade($subjectMarks, $subjectFullMarks);
                                                                    
                                                                    // Update statistics
                                                                    $subjectTotals[$subjectIndex] += $subjectMarks;
                                                                    if ($subjectMarks > 0) {
                                                                        $studentCounts[$subjectIndex]++;
                                                                    }
                                                                    $gradeCounts[$subjectName][$subjectGrade]++;
                                                                    $studentTotal += $subjectMarks;
                                                                    
                                                                    // Check if passed this subject
                                                                    if ($subjectMarks / $subjectFullMarks >= 0.4) {
                                                                        $passedSubjects++;
                                                                    }
                                                                    ?>
                                                                <?php endif; ?>
                                                            <?php endforeach; ?>
                                                            
                                                            <td><?= $subjectFound ? $subjectMarks : '<span class="no-marks">-</span>' ?></td>
                                                            <td><?= $subjectFound ? $subjectGrade : '<span class="no-marks">-</span>' ?></td>
                                                        <?php endforeach; ?>
                                                        
                                                        <td><b><?= $studentTotal ?></b></td>
                                                        <td><b><?= ($totalSubjects > 0) ? number_format($studentTotal / $totalSubjects, 2) : 0 ?></b></td>
                                                        <td><?= ($passedSubjects >= $totalSubjects * 0.6) ? '<span class="text-success">Pass</span>' : '<span class="text-danger">Fail</span>' ?></td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                    
                                                    <!-- Summary Rows -->
                                                    <tr style="background-color: #f0f0f0;">
                                                        <td colspan="<?php echo (count($displaySections) > 1 ? 3 : 2); ?>"><b>Subject Totals</b></td>
                                                        <?php foreach ($allUniqueSubjects as $index => $subject) : ?>
                                                        <td colspan="2"><b><?= $subjectTotals[$index] ?></b></td>
                                                        <?php endforeach; ?>
                                                        <td colspan="2"><b><?= array_sum($subjectTotals) ?></b></td>
                                                        <td></td>
                                                    </tr>
                                                    
                                                    <tr style="background-color: #f8f8f8;">
                                                        <td colspan="<?php echo (count($displaySections) > 1 ? 3 : 2); ?>"><b>Students Appeared</b></td>
                                                        <?php foreach ($studentCounts as $count) : ?>
                                                        <td colspan="2"><b><?= $count ?></b></td>
                                                        <?php endforeach; ?>
                                                        <td colspan="2"><b><?= count($ledger) ?></b></td>
                                                        <td></td>
                                                    </tr>
                                                    
                                                    <tr style="background-color: #e8e8e8;">
                                                        <td colspan="<?php echo (count($displaySections) > 1 ? 3 : 2); ?>"><b>Average Marks</b></td>
                                                        <?php foreach ($allUniqueSubjects as $index => $subject) : ?>
                                                        <?php 
                                                        $avg = ($studentCounts[$index] > 0) ? 
                                                               number_format($subjectTotals[$index] / $studentCounts[$index], 2) : 0;
                                                        ?>
                                                        <td colspan="2"><b><?= $avg ?></b></td>
                                                        <?php endforeach; ?>
                                                        <td colspan="2"><b><?= 
                                                            (count($ledger) > 0) ? 
                                                            number_format(array_sum($subjectTotals) / count($ledger), 2) : 0 
                                                        ?></b></td>
                                                        <td></td>
                                                    </tr>
                                                </tbody>
                                            </table>
                                            
                                            <!-- Grade Distribution Table -->
                                            <table id="customers" class="mt-4">
                                                <thead>
                                                    <tr>
                                                        <td colspan="<?php echo count($allUniqueSubjects) + 1; ?>" style="border:none;font-size: 16px; text-align: center;">
                                                            <b><u>Grade Distribution by Subject</u></b>
                                                        </td>
                                                    </tr>
                                                    <tr>
                                                        <th>Subject / Grade</th>
                                                        <?php 
                                                        $grades = ['A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'NG', 'Abs'];
                                                        foreach ($grades as $grade) : ?>
                                                        <th style="width:50px"><?= $grade ?></th>
                                                        <?php endforeach; ?>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach ($allUniqueSubjects as $subject) : ?>
                                                    <tr>
                                                        <td><?= htmlspecialchars($subject) ?></td>
                                                        <?php foreach ($grades as $grade) : ?>
                                                        <td><?= $gradeCounts[$subject][$grade] ?? 0 ?></td>
                                                        <?php endforeach; ?>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                </tbody>
                                            </table>
                                            
                                            <!-- Subject-wise Detailed Statistics -->
                                            <table id="customers" class="mt-4 no-print">
                                                <thead>
                                                    <tr>
                                                        <td colspan="5" style="border:none;font-size: 16px; text-align: center;">
                                                            <b><u>Subject-wise Detailed Statistics</u></b>
                                                        </td>
                                                    </tr>
                                                    <tr>
                                                        <th>Subject</th>
                                                        <th>Total Marks</th>
                                                        <th>Present Students</th>
                                                        <th>Average Marks</th>
                                                        <th>Average Grade Point</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach ($allUniqueSubjects as $subject) : ?>
                                                    <?php 
                                                    $subjectStats = calculateSubjectStatistics($ledger, $subject);
                                                    $avgMarks = ($subjectStats['students_with_marks'] > 0) ? 
                                                               number_format($subjectStats['total_marks'] / $subjectStats['students_with_marks'], 2) : 0;
                                                    $avgGP = ($subjectStats['students_with_marks'] > 0) ? 
                                                            number_format($subjectStats['total_grade_point'] / $subjectStats['students_with_marks'], 2) : 0;
                                                    ?>
                                                    <tr>
                                                        <td><?= htmlspecialchars($subject) ?></td>
                                                        <td><?= $subjectStats['total_marks'] ?></td>
                                                        <td><?= $subjectStats['students_with_marks'] ?></td>
                                                        <td><?= $avgMarks ?></td>
                                                        <td><?= $avgGP ?></td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                </tbody>
                                            </table>
                                        </div>
                                        <?php elseif (isset($_GET['class_id']) && isset($_GET['exam_id'])) : ?>
                                        <div class="alert alert-warning mt-4">
                                            <h4>No Data Available</h4>
                                            <p>No marks found for the selected criteria.</p>
                                            <?php if (isset($allClassSubjects) && !empty($allClassSubjects)): ?>
                                            <p><strong>Subjects assigned to class:</strong> 
                                                <?php 
                                                $subjectNames = array_column($allClassSubjects, 'subject_name');
                                                echo htmlspecialchars(implode(', ', $subjectNames));
                                                ?>
                                            </p>
                                            <p><strong>Note:</strong> Make sure marks are entered for the selected exam.</p>
                                            <?php endif; ?>
                                        </div>
                                        <?php endif; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- Scripts -->
    <script src="js/jquery/jquery-2.2.4.min.js"></script>
    <script src="js/bootstrap/bootstrap.min.js"></script>
    <script src="js/pace/pace.min.js"></script>
    <script src="js/lobipanel/lobipanel.min.js"></script>
    <script src="js/iscroll/iscroll.js"></script>
    <script src="js/prism/prism.js"></script>
    <script src="js/select2/select2.min.js"></script>
    <script src="js/main.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
    
    <script>
    $(document).ready(function() {
        // Initialize Select2 with multiple selection
        $('.select2').select2({
            placeholder: "Select options",
            allowClear: true
        });
        
        // Initialize section select2 with specific options
        $('#section_id').select2({
            placeholder: "Select sections or choose 'All Sections'",
            allowClear: true,
            closeOnSelect: false
        });
        
        // Handle "All Sections" selection
        $('#section_id').on('select2:select', function (e) {
            var data = e.params.data;
            if (data.id === 'all') {
                // If "All Sections" is selected, clear other selections
                $('#section_id').val('all').trigger('change');
            } else {
                // If a specific section is selected, remove "All Sections" if it was selected
                var currentValues = $('#section_id').val();
                if (currentValues && currentValues.includes('all')) {
                    currentValues = currentValues.filter(function(value) {
                        return value !== 'all';
                    });
                    $('#section_id').val(currentValues).trigger('change');
                }
            }
            updateSelectedSectionsDisplay();
        });
        
        $('#section_id').on('select2:unselect', function (e) {
            updateSelectedSectionsDisplay();
        });
        
        // Load sections when class changes
        if ($('#class_id').val()) {
            loadSections($('#class_id').val());
        }
        
        // Update selected sections display
        updateSelectedSectionsDisplay();
        
        // Export to Excel
        $('#exportExcelBtn').on('click', function() {
            var tables = document.querySelectorAll('#customers');
            var wb = XLSX.utils.book_new();
            
            tables.forEach(function(table, index) {
                var ws = XLSX.utils.table_to_sheet(table);
                XLSX.utils.book_append_sheet(wb, ws, 'Sheet' + (index + 1));
            });
            
            var fileName = 'Result_Analysis_' + 
                          '<?php echo preg_replace("/[^a-zA-Z0-9]/", "_", $legclass); ?>_' + 
                          '<?php echo (!empty($displaySections) && count($displaySections) <= 3) ? preg_replace("/[^a-zA-Z0-9]/", "_", implode("_", $displaySections)) . "_" : ""; ?>' +
                          '<?php echo preg_replace("/[^a-zA-Z0-9]/", "_", $examtype); ?>_' + 
                          '<?php echo $examyear; ?>_' + 
                          new Date().toISOString().slice(0,10) + '.xlsx';
            
            XLSX.writeFile(wb, fileName);
        });
    });
    
    function loadSections(classId) {
        if (!classId) {
            $('#section_id').html('<option value="all">All Sections</option>').val('all').trigger('change');
            updateSelectedSectionsDisplay();
            return;
        }
        
        $.ajax({
            url: 'ajax/get_sections_by_class.php',
            type: 'GET',
            data: { class_id: classId },
            dataType: 'json',
            success: function(sections) {
                let options = '<option value="all">All Sections</option>';
                if (sections && sections.length > 0) {
                    sections.forEach(function(section) {
                        // Check if this section was previously selected
                        let selected = '';
                        <?php if (isset($_GET['section_id'])): ?>
                        let previousSelections = <?php echo json_encode((array)$_GET['section_id']); ?>;
                        if (previousSelections && previousSelections.includes(section.section_id.toString())) {
                            selected = 'selected';
                        }
                        <?php endif; ?>
                        options += '<option value="' + section.section_id + '" ' + selected + '>' + section.section_name + '</option>';
                    });
                }
                $('#section_id').html(options).trigger('change');
                updateSelectedSectionsDisplay();
            },
            error: function() {
                $('#section_id').html('<option value="all">All Sections</option>').val('all').trigger('change');
                updateSelectedSectionsDisplay();
            }
        });
    }
    
    function updateSelectedSectionsDisplay() {
        var selectedOptions = $('#section_id').val();
        var displayDiv = $('#selectedSectionsDisplay');
        var listDiv = $('#selectedSectionsList');
        
        if (!selectedOptions || selectedOptions.length === 0 || (selectedOptions.length === 1 && selectedOptions[0] === 'all')) {
            displayDiv.hide();
            return;
        }
        
        var sections = [];
        $('#section_id option:selected').each(function() {
            if ($(this).val() !== 'all') {
                sections.push($(this).text());
            }
        });
        
        if (sections.length === 0) {
            displayDiv.hide();
        } else {
            listDiv.empty();
            sections.forEach(function(section) {
                listDiv.append('<span class="section-badge">' + section + '</span>');
            });
            displayDiv.show();
        }
    }
    
    function resetForm() {
        $('#analysisForm')[0].reset();
        $('#section_id').val('all').trigger('change');
        $('.select2').trigger('change');
        updateSelectedSectionsDisplay();
    }
    
    function printTable() {
        window.print();
    }
    
    function exportToPDF() {
        const { jsPDF } = window.jspdf;
        const doc = new jsPDF('l', 'mm', 'a4');
        
        // Get all tables
        const tables = document.querySelectorAll('#customers');
        
        let yOffset = 20;
        const pageHeight = doc.internal.pageSize.height;
        const margin = 20;
        
        // Add title
        doc.setFontSize(16);
        doc.setTextColor(0, 0, 0);
        doc.text('Result Analysis Report', margin, yOffset);
        yOffset += 10;
        
        doc.setFontSize(12);
        doc.text('Class: <?php echo addslashes($legclass); ?>', margin, yOffset);
        yOffset += 6;
        <?php if (!empty($displaySections) && count($displaySections) <= 3): ?>
        doc.text('Section(s): <?php echo addslashes(implode(", ", $displaySections)); ?>', margin, yOffset);
        yOffset += 6;
        <?php endif; ?>
        doc.text('Exam: <?php echo addslashes($examtype . " (" . $examyear . ")"); ?>', margin, yOffset);
        yOffset += 10;
        
        // Process each table
        tables.forEach(function(table, index) {
            if (yOffset > pageHeight - 50) {
                doc.addPage();
                yOffset = 20;
            }
            
            // Convert table to canvas
            html2canvas(table).then(canvas => {
                const imgData = canvas.toDataURL('image/png');
                const imgWidth = 190;
                const imgHeight = canvas.height * imgWidth / canvas.width;
                
                if (yOffset + imgHeight > pageHeight - margin) {
                    doc.addPage();
                    yOffset = 20;
                }
                
                doc.addImage(imgData, 'PNG', margin, yOffset, imgWidth, imgHeight);
                yOffset += imgHeight + 10;
                
                // Save PDF after last table
                if (index === tables.length - 1) {
                    doc.save('Result_Analysis_<?php echo preg_replace("/[^a-zA-Z0-9]/", "_", $legclass); ?>_' + 
                             '<?php echo (!empty($displaySections) && count($displaySections) <= 3) ? preg_replace("/[^a-zA-Z0-9]/", "_", implode("_", $displaySections)) . "_" : ""; ?>' +
                             '<?php echo preg_replace("/[^a-zA-Z0-9]/", "_", $examtype); ?>_' + 
                             '<?php echo $examyear; ?>_' + 
                             new Date().toISOString().slice(0,10) + '.pdf');
                }
            });
        });
    }
    </script>
</body>
</html>