<?php
session_start();
require_once '../config/database.php';
require_once '../includes/site_settings.php';

// Get site settings
$settings = get_all_settings($pdo);
$site_name = $settings['site_name'] ?? 'বিদ্যা নিকেতন';
$site_address = $settings['site_address'] ?? 'ঠিকানা: গ্রাম/থানা, জেলা, বাংলাদেশ';
$logo_image = $settings['logo_image'] ?? null;
$logo_size = isset($settings['logo_size']) ? intval($settings['logo_size']) : 100;

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit();
}

// Get teacher's assigned classes if user is a teacher
$teacher_id = null;
$assigned_class_ids = [];
if ($_SESSION['user_role'] == 'teacher') {
    // Get teacher ID
    $stmt = $pdo->prepare("SELECT id FROM teachers WHERE user_id = ?");
    $stmt->execute([$_SESSION['user_id']]);
    $teacher = $stmt->fetch();
    if ($teacher) {
        $teacher_id = $teacher['id'];
        
        // Get assigned class IDs
        $stmt = $pdo->prepare("SELECT DISTINCT c.id FROM classes c WHERE (c.class_teacher_id = ? OR c.id IN (SELECT class_id FROM class_subjects WHERE teacher_id = ?)) AND c.status = 'active'");
        $stmt->execute([$teacher_id, $teacher_id]);
        $assigned_classes = $stmt->fetchAll();
        $assigned_class_ids = array_column($assigned_classes, 'id');
    }
}

// Get search parameters
$class_id = $_GET['class_id'] ?? '';
$session = $_GET['session'] ?? date('Y');
$exam_type = $_GET['exam_type'] ?? '';
$group_name = $_GET['group_name'] ?? '';

$class_info = null;
$students = [];
$subjects = [];
$results_matrix = [];

if ($class_id && $session && $exam_type) {
    // Get class details
    $stmt = $pdo->prepare("SELECT * FROM classes WHERE id = ?");
    $stmt->execute([$class_id]);
    $class_info = $stmt->fetch();
    
    if ($class_info) {
        // Get students in this class
        $group_condition = $group_name ? "AND s.group_name = ?" : "";
        $params = [$class_id];
        if ($group_name) $params[] = $group_name;
        
        $stmt = $pdo->prepare("
            SELECT s.* FROM students s 
            WHERE s.class_id = ? AND s.status = 'active' $group_condition
            ORDER BY s.student_id
        ");
        $stmt->execute($params);
        $students = $stmt->fetchAll();
        
        // Get subjects for this class and exam
        $stmt = $pdo->prepare("
            SELECT DISTINCT sub.id, sub.name, sub.code 
            FROM results r
            JOIN subjects sub ON r.subject_id = sub.id
            JOIN students s ON r.student_id = s.id
            WHERE s.class_id = ? AND r.session = ? AND r.exam_type = ?
            " . ($group_name ? "AND s.group_name = ?" : "") . "
            ORDER BY sub.name
        ");
        $params = [$class_id, $session, $exam_type];
        if ($group_name) $params[] = $group_name;
        $stmt->execute($params);
        $raw_subjects = $stmt->fetchAll();
        
        // Group Bangla and English subjects
        $subjects = groupSubjects($raw_subjects);
        
        // Store original subjects for processing
        $original_subjects = $raw_subjects;
        
        // Get all results for this class, session, and exam
        $stmt = $pdo->prepare("
            SELECT r.*, s.student_id as roll_number
            FROM results r
            JOIN students s ON r.student_id = s.id
            WHERE s.class_id = ? AND r.session = ? AND r.exam_type = ?
            " . ($group_name ? "AND s.group_name = ?" : "")
        );
        $stmt->execute($params);
        $all_results = $stmt->fetchAll();
        
        // Organize results in matrix format
        foreach ($all_results as $result) {
            $results_matrix[$result['student_id']][$result['subject_id']] = $result;
        }
        
        // Calculate statistics
        $total_students = count($students);
        $passed_students = 0;
        $failed_students = 0;
        $subject_stats = [];
        
        // Calculate merit positions for all students
        $student_merit_positions = [];
        if (!empty($students)) {
            // Get all students in the same class with their GPA for merit calculation
            // We need to calculate GPA for each student to determine merit position correctly
            
            // Use the same students that we're displaying
            $all_student_ids = array_column($students, 'id');
            
            // Calculate GPA for each student
            $student_gpas = [];
            foreach ($all_student_ids as $student_id) {
                // Get student results
                $stmt = $pdo->prepare("SELECT * FROM results WHERE student_id = ? AND session = ? AND exam_type = ?");
                $stmt->execute([$student_id, $session, $exam_type]);
                $student_results = $stmt->fetchAll();
                
                if (empty($student_results)) {
                    $student_gpas[$student_id] = 0.00;
                    continue;
                }
                
                $total_grade_points = 0;
                $gpa_subjects = 0;
                $fourth_subject_found = false;
                $student_failed = false;
                
                // First, group results by subject type to identify combined subjects
                $bangla_subjects = [];
                $english_subjects = [];
                $other_subjects = [];
                
                foreach ($student_results as $result) {
                    // Get subject name for proper grading
                    $stmt = $pdo->prepare("SELECT name FROM subjects WHERE id = ?");
                    $stmt->execute([$result['subject_id']]);
                    $subject_data = $stmt->fetch();
                    $subject_name = $subject_data ? $subject_data['name'] : '';
                    
                    // Group results by subject type
                    if (stripos($subject_name, 'বাংলা') !== false || stripos($subject_name, 'Bangla') !== false) {
                        $bangla_subjects[] = ['result' => $result, 'name' => $subject_name];
                    } else if (stripos($subject_name, 'ইংরেজি') !== false || stripos($subject_name, 'English') !== false) {
                        $english_subjects[] = ['result' => $result, 'name' => $subject_name];
                    } else {
                        $other_subjects[] = ['result' => $result, 'name' => $subject_name];
                    }
                }
                
                // Process Bangla subjects
                if (count($bangla_subjects) > 1) {
                    // Multiple Bangla subjects - need to combine them
                    // For merit calculation, we'll process them individually but check for fourth subject status
                    foreach ($bangla_subjects as $bangla) {
                        $result = $bangla['result'];
                        $subject_name = $bangla['name'];
                        
                        // Check if this subject is a fourth subject
                        $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                        $stmt->execute([$student_id, $result['subject_id']]);
                        $subject_info = $stmt->fetch();
                        $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                        
                        // Apply custom grading for Bangla subject
                        $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                        $custom_grade = calculateBanglaGrade($cq_marks_array, $result['mcq_marks'], 100, 40); // Assuming 100 total marks and 40 pass marks
                        $subject_grade = $custom_grade;
                        
                        if ($subject_grade == 'F') {
                            // Check if student failed in this subject and it's not a fourth subject
                            if (!$is_fourth_subject) {
                                $student_failed = true;
                            }
                        } else {
                            // Add grade point for GPA calculation (only for passed subjects)
                            if ($is_fourth_subject) {
                                $fourth_subject_found = true;
                                $fourth_subject_grade_point = getGradePoint($subject_grade);
                                // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                                $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                                $total_grade_points += $adjusted_grade_point;
                            } else {
                                // Regular subject - add full grade point
                                $total_grade_points += getGradePoint($subject_grade);
                            }
                            $gpa_subjects++;
                        }
                    }
                } else if (count($bangla_subjects) == 1) {
                    // Single Bangla subject
                    $bangla = $bangla_subjects[0];
                    $result = $bangla['result'];
                    $subject_name = $bangla['name'];
                    
                    // Check if this subject is a fourth subject
                    $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                    $stmt->execute([$student_id, $result['subject_id']]);
                    $subject_info = $stmt->fetch();
                    $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                    
                    // Apply custom grading for Bangla subject
                    $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                    $custom_grade = calculateBanglaGrade($cq_marks_array, $result['mcq_marks'], 100, 40); // Assuming 100 total marks and 40 pass marks
                    $subject_grade = $custom_grade;
                    
                    if ($subject_grade == 'F') {
                        // Check if student failed in this subject and it's not a fourth subject
                        if (!$is_fourth_subject) {
                            $student_failed = true;
                        }
                    } else {
                        // Add grade point for GPA calculation (only for passed subjects)
                        if ($is_fourth_subject) {
                            $fourth_subject_found = true;
                            $fourth_subject_grade_point = getGradePoint($subject_grade);
                            // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                            $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                            $total_grade_points += $adjusted_grade_point;
                        } else {
                            // Regular subject - add full grade point
                            $total_grade_points += getGradePoint($subject_grade);
                        }
                        $gpa_subjects++;
                    }
                }
                
                // Process English subjects
                if (count($english_subjects) > 1) {
                    // Multiple English subjects - need to combine them
                    // For merit calculation, we'll process them individually but check for fourth subject status
                    foreach ($english_subjects as $english) {
                        $result = $english['result'];
                        $subject_name = $english['name'];
                        
                        // Check if this subject is a fourth subject
                        $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                        $stmt->execute([$student_id, $result['subject_id']]);
                        $subject_info = $stmt->fetch();
                        $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                        
                        // Apply custom grading for English subject
                        $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                        $custom_grade = calculateEnglishGrade($cq_marks_array, 100, 40); // Assuming 100 total marks and 40 pass marks
                        $subject_grade = $custom_grade;
                        
                        if ($subject_grade == 'F') {
                            // Check if student failed in this subject and it's not a fourth subject
                            if (!$is_fourth_subject) {
                                $student_failed = true;
                            }
                        } else {
                            // Add grade point for GPA calculation (only for passed subjects)
                            if ($is_fourth_subject) {
                                $fourth_subject_found = true;
                                $fourth_subject_grade_point = getGradePoint($subject_grade);
                                // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                                $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                                $total_grade_points += $adjusted_grade_point;
                            } else {
                                // Regular subject - add full grade point
                                $total_grade_points += getGradePoint($subject_grade);
                            }
                            $gpa_subjects++;
                        }
                    }
                } else if (count($english_subjects) == 1) {
                    // Single English subject
                    $english = $english_subjects[0];
                    $result = $english['result'];
                    $subject_name = $english['name'];
                    
                    // Check if this subject is a fourth subject
                    $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                    $stmt->execute([$student_id, $result['subject_id']]);
                    $subject_info = $stmt->fetch();
                    $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                    
                    // Apply custom grading for English subject
                    $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                    $custom_grade = calculateEnglishGrade($cq_marks_array, 100, 40); // Assuming 100 total marks and 40 pass marks
                    $subject_grade = $custom_grade;
                    
                    if ($subject_grade == 'F') {
                        // Check if student failed in this subject and it's not a fourth subject
                        if (!$is_fourth_subject) {
                            $student_failed = true;
                        }
                    } else {
                        // Add grade point for GPA calculation (only for passed subjects)
                        if ($is_fourth_subject) {
                            $fourth_subject_found = true;
                            $fourth_subject_grade_point = getGradePoint($subject_grade);
                            // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                            $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                            $total_grade_points += $adjusted_grade_point;
                        } else {
                            // Regular subject - add full grade point
                            $total_grade_points += getGradePoint($subject_grade);
                        }
                        $gpa_subjects++;
                    }
                }
                
                // Process other subjects
                foreach ($other_subjects as $other) {
                    $result = $other['result'];
                    $subject_name = $other['name'];
                    
                    // Check if this subject is a fourth subject
                    $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                    $stmt->execute([$student_id, $result['subject_id']]);
                    $subject_info = $stmt->fetch();
                    $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                    
                    // Get grade for this subject using standard grading
                    $subject_grade = '';
                    if ($result['marks_obtained'] >= 80) $subject_grade = 'A+';
                    elseif ($result['marks_obtained'] >= 70) $subject_grade = 'A';
                    elseif ($result['marks_obtained'] >= 60) $subject_grade = 'A-';
                    elseif ($result['marks_obtained'] >= 50) $subject_grade = 'B';
                    elseif ($result['marks_obtained'] >= 40) $subject_grade = 'C';
                    elseif ($result['marks_obtained'] >= 33) $subject_grade = 'D';
                    else $subject_grade = 'F';
                    
                    if ($subject_grade == 'F') {
                        // Check if student failed in this subject and it's not a fourth subject
                        if (!$is_fourth_subject) {
                            $student_failed = true;
                        }
                    } else {
                        // Add grade point for GPA calculation (only for passed subjects)
                        if ($is_fourth_subject) {
                            $fourth_subject_found = true;
                            $fourth_subject_grade_point = getGradePoint($subject_grade);
                            // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                            $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                            $total_grade_points += $adjusted_grade_point;
                        } else {
                            // Regular subject - add full grade point
                            $total_grade_points += getGradePoint($subject_grade);
                        }
                        $gpa_subjects++;
                    }
                }
                
                // Calculate GPA
                $gpa = 0.00;
                if ($gpa_subjects > 0) {
                    if ($fourth_subject_found) {
                        // When 4th subject exists, divide by (gpa_subjects - 1)
                        // Make sure we don't divide by zero
                        $gpa = ($gpa_subjects - 1) > 0 ? round($total_grade_points / ($gpa_subjects - 1), 2) : 0.00;
                    } else {
                        // Normal calculation
                        $gpa = round($total_grade_points / $gpa_subjects, 2);
                    }
                }
                
                // Students who failed should have lower merit (0 GPA)
                $student_gpas[$student_id] = $student_failed ? 0.00 : $gpa;
            }
            
            // Sort students by GPA in descending order (highest GPA first)
            arsort($student_gpas);
            
            // Create an array of student IDs ordered by merit position
            $merit_order = [];
            $total_students = count($student_gpas);
            $position = $total_students;
            foreach ($student_gpas as $student_id => $gpa) {
                $merit_order[$student_id] = $position--;
            }
            
            // Assign merit positions to students
            foreach ($students as $student) {
                $student_merit_positions[$student['id']] = isset($merit_order[$student['id']]) ? $merit_order[$student['id']] : null;
            }
        }
        
        foreach ($students as $student) {
            $student_total = 0;
            $student_subjects = 0;
            $student_failed = false;
            
            foreach ($subjects as $subject) {
                // Handle combined subjects differently
                if ($subject['id'] == 'combined_bangla') {
                    // Get combined Bangla result
                    $result = getCombinedBanglaResult($student['id'], array_filter($original_subjects, function($s) { return stripos($s['name'], 'বাংলা') !== false || stripos($s['name'], 'Bangla') !== false; }), $results_matrix);
                    if ($result) {
                        // Apply custom grading for combined Bangla subject
                        $custom_grade = calculateBanglaGrade($result['cq_marks_array'], $result['mcq_marks'], $result['full_marks'], $result['pass_marks']);
                        $result['grade'] = $custom_grade;
                        
                        $student_total += $result['marks_obtained'];
                        $student_subjects++;
                        
                        if ($result['grade'] == 'F') {
                            $student_failed = true;
                        }
                        
                        // Subject statistics for combined subjects
                        if (!isset($subject_stats[$subject['id']])) {
                            $subject_stats[$subject['id']] = [
                                'total_marks' => 0,
                                'student_count' => 0,
                                'passed' => 0,
                                'failed' => 0,
                                'highest' => 0,
                                'lowest' => 999
                            ];
                        }
                        
                        $subject_stats[$subject['id']]['total_marks'] += $result['marks_obtained'];
                        $subject_stats[$subject['id']]['student_count']++;
                        
                        if ($result['grade'] != 'F') {
                            $subject_stats[$subject['id']]['passed']++;
                        } else {
                            $subject_stats[$subject['id']]['failed']++;
                        }
                        
                        if ($result['marks_obtained'] > $subject_stats[$subject['id']]['highest']) {
                            $subject_stats[$subject['id']]['highest'] = $result['marks_obtained'];
                        }
                        
                        if ($result['marks_obtained'] < $subject_stats[$subject['id']]['lowest']) {
                            $subject_stats[$subject['id']]['lowest'] = $result['marks_obtained'];
                        }
                    } else {
                        // Student did not participate or sit for this subject exam
                        // Only mark as failed if this is a mandatory subject (not a fourth subject)
                        $student_failed = true; // For combined subjects, we assume they are mandatory
                        $student_subjects++;
                        
                        // Update subject statistics for failed/absent students
                        if (!isset($subject_stats[$subject['id']])) {
                            $subject_stats[$subject['id']] = [
                                'total_marks' => 0,
                                'student_count' => 0,
                                'passed' => 0,
                                'failed' => 0,
                                'highest' => 0,
                                'lowest' => 999
                            ];
                        }
                        
                        $subject_stats[$subject['id']]['student_count']++;
                        $subject_stats[$subject['id']]['failed']++;
                        
                        // Set lowest to 0 for absent students
                        if (0 < $subject_stats[$subject['id']]['lowest']) {
                            $subject_stats[$subject['id']]['lowest'] = 0;
                        }
                    }
                } else if ($subject['id'] == 'combined_english') {
                    // Get combined English result
                    $result = getCombinedEnglishResult($student['id'], array_filter($original_subjects, function($s) { return stripos($s['name'], 'ইংরেজি') !== false || stripos($s['name'], 'English') !== false; }), $results_matrix);
                    if ($result) {
                        // Apply custom grading for combined English subject
                        $custom_grade = calculateEnglishGrade($result['cq_marks_array'], $result['full_marks'], $result['pass_marks']);
                        $result['grade'] = $custom_grade;
                        
                        $student_total += $result['marks_obtained'];
                        $student_subjects++;
                        
                        if ($result['grade'] == 'F') {
                            $student_failed = true;
                        }
                        
                        // Subject statistics for combined subjects
                        if (!isset($subject_stats[$subject['id']])) {
                            $subject_stats[$subject['id']] = [
                                'total_marks' => 0,
                                'student_count' => 0,
                                'passed' => 0,
                                'failed' => 0,
                                'highest' => 0,
                                'lowest' => 999
                            ];
                        }
                        
                        $subject_stats[$subject['id']]['total_marks'] += $result['marks_obtained'];
                        $subject_stats[$subject['id']]['student_count']++;
                        
                        if ($result['grade'] != 'F') {
                            $subject_stats[$subject['id']]['passed']++;
                        } else {
                            $subject_stats[$subject['id']]['failed']++;
                        }
                        
                        if ($result['marks_obtained'] > $subject_stats[$subject['id']]['highest']) {
                            $subject_stats[$subject['id']]['highest'] = $result['marks_obtained'];
                        }
                        
                        if ($result['marks_obtained'] < $subject_stats[$subject['id']]['lowest']) {
                            $subject_stats[$subject['id']]['lowest'] = $result['marks_obtained'];
                        }
                    } else {
                        // Student did not participate or sit for this subject exam
                        // Only mark as failed if this is a mandatory subject (not a fourth subject)
                        $student_failed = true; // For combined subjects, we assume they are mandatory
                        $student_subjects++;
                        
                        // Update subject statistics for failed/absent students
                        if (!isset($subject_stats[$subject['id']])) {
                            $subject_stats[$subject['id']] = [
                                'total_marks' => 0,
                                'student_count' => 0,
                                'passed' => 0,
                                'failed' => 0,
                                'highest' => 0,
                                'lowest' => 999
                            ];
                        }
                        
                        $subject_stats[$subject['id']]['student_count']++;
                        $subject_stats[$subject['id']]['failed']++;
                        
                        // Set lowest to 0 for absent students
                        if (0 < $subject_stats[$subject['id']]['lowest']) {
                            $subject_stats[$subject['id']]['lowest'] = 0;
                        }
                    }
                } else if (isset($results_matrix[$student['id']][$subject['id']])) {
                    $result = $results_matrix[$student['id']][$subject['id']];
                    $student_total += $result['marks_obtained'];
                    $student_subjects++;
                    
                    // Apply custom grading for Bangla and English subjects
                    if (stripos($subject['name'], 'বাংলা') !== false || stripos($subject['name'], 'Bangla') !== false) {
                        // For Bangla: Check average of 2 CQ marks + MCQ marks
                        $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                        $custom_grade = calculateBanglaGrade($cq_marks_array, $result['mcq_marks'], 100, 40); // Assuming 100 total marks and 40 pass marks
                        $result['grade'] = $custom_grade;
                    } else if (stripos($subject['name'], 'ইংরেজি') !== false || stripos($subject['name'], 'English') !== false) {
                        // For English: Check CQ marks average
                        $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                        $custom_grade = calculateEnglishGrade($cq_marks_array, 100, 40); // Assuming 100 total marks and 40 pass marks
                        $result['grade'] = $custom_grade;
                    }
                    
                    if ($result['grade'] == 'F') {
                        $student_failed = true;
                    }
                    
                    // Subject statistics
                    if (!isset($subject_stats[$subject['id']])) {
                        $subject_stats[$subject['id']] = [
                            'total_marks' => 0,
                            'student_count' => 0,
                            'passed' => 0,
                            'failed' => 0,
                            'highest' => 0,
                            'lowest' => 999
                        ];
                    }
                    
                    $subject_stats[$subject['id']]['total_marks'] += $result['marks_obtained'];
                    $subject_stats[$subject['id']]['student_count']++;
                    
                    if ($result['grade'] != 'F') {
                        $subject_stats[$subject['id']]['passed']++;
                    } else {
                        $subject_stats[$subject['id']]['failed']++;
                    }
                    
                    if ($result['marks_obtained'] > $subject_stats[$subject['id']]['highest']) {
                        $subject_stats[$subject['id']]['highest'] = $result['marks_obtained'];
                    }
                    
                    if ($result['marks_obtained'] < $subject_stats[$subject['id']]['lowest']) {
                        $subject_stats[$subject['id']]['lowest'] = $result['marks_obtained'];
                    }
                } else {
                    // Student did not participate or sit for this subject exam
                    // Only mark as failed if this is a mandatory subject (not a fourth subject)
                    $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                    $stmt->execute([$student['id'], $subject['id']]);
                    $subject_info = $stmt->fetch();
                    $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                    
                    // Only mark as failed if it's not a fourth subject
                    if (!$is_fourth_subject) {
                        $student_failed = true;
                    }
                    $student_subjects++;
                    
                    // Update subject statistics for failed/absent students
                    if (!isset($subject_stats[$subject['id']])) {
                        $subject_stats[$subject['id']] = [
                            'total_marks' => 0,
                            'student_count' => 0,
                            'passed' => 0,
                            'failed' => 0,
                            'highest' => 0,
                            'lowest' => 999
                        ];
                    }
                    
                    $subject_stats[$subject['id']]['student_count']++;
                    $subject_stats[$subject['id']]['failed']++;
                    
                    // Set lowest to 0 for absent students
                    if (0 < $subject_stats[$subject['id']]['lowest']) {
                        $subject_stats[$subject['id']]['lowest'] = 0;
                    }
                }
            }
            
            if ($student_failed) {
                $failed_students++;
            } else {
                $passed_students++;
            }
        }
    }
}

function getGradePoint($grade) {
    switch ($grade) {
        case 'A+': return 5.00;
        case 'A': return 4.00;
        case 'A-': return 3.50;
        case 'B': return 3.00;
        case 'C': return 2.00;
        case 'D': return 1.00;
        default: return 0.00;
    }
}

// Custom function to calculate Bangla subject grade based on SSC criteria
// Average of 2 CQ marks + MCQ marks must pass for overall pass
function calculateBanglaGrade($cq_marks_array, $mcq_marks, $total_marks, $pass_marks) {
    // Calculate average of 2 CQ marks
    $cq_average = 0;
    if (count($cq_marks_array) >= 2) {
        $cq_average = ($cq_marks_array[0] + $cq_marks_array[1]) / 2;
    } else if (count($cq_marks_array) == 1) {
        $cq_average = $cq_marks_array[0];
    }
    
    // For Bangla: CQ average > 22 AND MCQ > 9 to pass
    if ($cq_average > 22 && $mcq_marks > 9) {
        // Calculate overall marks
        $overall_marks = $cq_average + $mcq_marks;
        
        // Calculate percentage based on total marks
        $percentage = ($overall_marks / $total_marks) * 100;
        
        if ($percentage >= 80) return 'A+';
        elseif ($percentage >= 70) return 'A';
        elseif ($percentage >= 60) return 'A-';
        elseif ($percentage >= 50) return 'B';
        elseif ($percentage >= 40) return 'C';
        elseif ($percentage >= 33) return 'D';
        else return 'F';
    } else {
        // If criteria not met, return F
        return 'F';
    }
}

// Custom function to calculate English subject grade based on new criteria
// For English: CQ average > 32 to pass
function calculateEnglishGrade($cq_marks_array, $total_marks, $pass_marks) {
    // Calculate average of 2 CQ marks
    $cq_average = 0;
    if (count($cq_marks_array) >= 2) {
        $cq_average = ($cq_marks_array[0] + $cq_marks_array[1]) / 2;
    } else if (count($cq_marks_array) == 1) {
        $cq_average = $cq_marks_array[0];
    }
    
    // For English: CQ average > 32 to pass
    if ($cq_average > 32) {
        // Calculate percentage based on total marks
        $percentage = ($cq_average / $total_marks) * 100;
        
        if ($percentage >= 80) return 'A+';
        elseif ($percentage >= 70) return 'A';
        elseif ($percentage >= 60) return 'A-';
        elseif ($percentage >= 50) return 'B';
        elseif ($percentage >= 40) return 'C';
        elseif ($percentage >= 33) return 'D';
        else return 'F';
    } else {
        // If criteria not met, return F
        return 'F';
    }
}

// Function to group Bangla and English subjects
function groupSubjects($raw_subjects) {
    $grouped_subjects = [];
    $bangla_subjects = [];
    $english_subjects = [];
    
    foreach ($raw_subjects as $subject) {
        // Check if this is a Bangla subject (Bangla or Bangla Grammar)
        if (stripos($subject['name'], 'বাংলা') !== false || stripos($subject['name'], 'Bangla') !== false) {
            $bangla_subjects[] = $subject;
        }
        // Check if this is an English subject (English or English Grammar)
        else if (stripos($subject['name'], 'ইংরেজি') !== false || stripos($subject['name'], 'English') !== false) {
            $english_subjects[] = $subject;
        }
        // For other subjects, add directly
        else {
            $grouped_subjects[] = $subject;
        }
    }
    
    // Add combined Bangla subject only if there are multiple Bangla subjects
    if (count($bangla_subjects) > 1) {
        $grouped_subjects[] = [
            'id' => 'combined_bangla',
            'name' => 'বাংলা(একত্রে)',
            'code' => 'CBAN'
        ];
    } else if (count($bangla_subjects) == 1) {
        // Add single Bangla subject as is
        $grouped_subjects[] = $bangla_subjects[0];
    }
    
    // Add combined English subject only if there are multiple English subjects
    if (count($english_subjects) > 1) {
        $grouped_subjects[] = [
            'id' => 'combined_english',
            'name' => 'ইংরেজি(একত্রে)',
            'code' => 'CENG'
        ];
    } else if (count($english_subjects) == 1) {
        // Add single English subject as is
        $grouped_subjects[] = $english_subjects[0];
    }
    
    return $grouped_subjects;
}

// Function to get combined results for grouped subjects
function getCombinedBanglaResult($student_id, $bangla_subjects, $results_matrix) {
    if (empty($bangla_subjects)) return null;
    
    // If only one Bangla subject, return it as is
    if (count($bangla_subjects) == 1) {
        $subject_id = $bangla_subjects[0]['id'];
        return isset($results_matrix[$student_id][$subject_id]) ? $results_matrix[$student_id][$subject_id] : null;
    }
    
    // For multiple Bangla subjects, combine them
    $combined = [
        'student_id' => $student_id,
        'subject_id' => 'combined_bangla',
        'cq_marks' => 0,
        'cq_marks_array' => [],
        'mcq_marks' => 0,
        'mcq_marks_array' => [],
        'practical_marks' => 0,
        'assignment_marks' => 0,
        'attendance_marks' => 0,
        'marks_obtained' => 0,
        'full_marks' => 0,
        'pass_marks' => 0,
        'grade' => 'F',
        'grade_point' => 0.00
    ];
    
    $valid_results = 0;
    
    // Sum up the marks from all Bangla subjects
    foreach ($bangla_subjects as $subject) {
        $subject_id = $subject['id'];
        if (isset($results_matrix[$student_id][$subject_id])) {
            $result = $results_matrix[$student_id][$subject_id];
            $combined['cq_marks'] += $result['cq_marks'];
            $combined['cq_marks_array'][] = $result['cq_marks'];
            $combined['mcq_marks'] += $result['mcq_marks'];
            $combined['mcq_marks_array'][] = $result['mcq_marks'];
            $combined['practical_marks'] += $result['practical_marks'];
            $combined['assignment_marks'] += $result['assignment_marks'];
            $combined['attendance_marks'] += $result['attendance_marks'];
            $combined['marks_obtained'] += $result['marks_obtained'];
            $combined['full_marks'] += 100; // Assuming 100 marks per subject
            $combined['pass_marks'] += 40;  // Assuming 40 pass marks per subject
            $valid_results++;
        }
    }
    
    // Average the marks for proper grading
    if ($valid_results > 0) {
        $combined['cq_marks'] = $combined['cq_marks'] / $valid_results;
        $combined['mcq_marks'] = $combined['mcq_marks'] / $valid_results;
        $combined['marks_obtained'] = $combined['marks_obtained'] / $valid_results;
        $combined['full_marks'] = $combined['full_marks'] / $valid_results;
        $combined['pass_marks'] = $combined['pass_marks'] / $valid_results;
    }
    
    return $combined;
}

// Function to get combined results for grouped English subjects
function getCombinedEnglishResult($student_id, $english_subjects, $results_matrix) {
    if (empty($english_subjects)) return null;
    
    // If only one English subject, return it as is
    if (count($english_subjects) == 1) {
        $subject_id = $english_subjects[0]['id'];
        return isset($results_matrix[$student_id][$subject_id]) ? $results_matrix[$student_id][$subject_id] : null;
    }
    
    // For multiple English subjects, combine them
    $combined = [
        'student_id' => $student_id,
        'subject_id' => 'combined_english',
        'cq_marks' => 0,
        'cq_marks_array' => [],
        'mcq_marks' => 0,
        'mcq_marks_array' => [],
        'practical_marks' => 0,
        'assignment_marks' => 0,
        'attendance_marks' => 0,
        'marks_obtained' => 0,
        'full_marks' => 0,
        'pass_marks' => 0,
        'grade' => 'F',
        'grade_point' => 0.00
    ];
    
    $valid_results = 0;
    
    // Sum up the marks from all English subjects
    foreach ($english_subjects as $subject) {
        $subject_id = $subject['id'];
        if (isset($results_matrix[$student_id][$subject_id])) {
            $result = $results_matrix[$student_id][$subject_id];
            $combined['cq_marks'] += $result['cq_marks'];
            $combined['cq_marks_array'][] = $result['cq_marks'];
            $combined['mcq_marks'] += $result['mcq_marks'];
            $combined['mcq_marks_array'][] = $result['mcq_marks'];
            $combined['practical_marks'] += $result['practical_marks'];
            $combined['assignment_marks'] += $result['assignment_marks'];
            $combined['attendance_marks'] += $result['attendance_marks'];
            $combined['marks_obtained'] += $result['marks_obtained'];
            $combined['full_marks'] += 100; // Assuming 100 marks per subject
            $combined['pass_marks'] += 40;  // Assuming 40 pass marks per subject
            $valid_results++;
        }
    }
    
    // Average the marks for proper grading
    if ($valid_results > 0) {
        $combined['cq_marks'] = $combined['cq_marks'] / $valid_results;
        $combined['mcq_marks'] = $combined['mcq_marks'] / $valid_results;
        $combined['marks_obtained'] = $combined['marks_obtained'] / $valid_results;
        $combined['full_marks'] = $combined['full_marks'] / $valid_results;
        $combined['pass_marks'] = $combined['pass_marks'] / $valid_results;
    }
    
    return $combined;
}

// Get all classes for dropdown
if ($_SESSION['user_role'] == 'teacher' && !empty($assigned_class_ids)) {
    // Teacher: only show assigned classes
    $placeholders = str_repeat('?,', count($assigned_class_ids) - 1) . '?';
    $stmt = $pdo->prepare("SELECT * FROM classes WHERE id IN ($placeholders) AND status = 'active' ORDER BY name");
    $stmt->execute($assigned_class_ids);
    $all_classes = $stmt->fetchAll();
} else {
    // Admin: show all classes
    $stmt = $pdo->query("SELECT * FROM classes WHERE status = 'active' ORDER BY name");
    $all_classes = $stmt->fetchAll();
}

// Get available sessions
$stmt = $pdo->query("SELECT DISTINCT session FROM results ORDER BY session DESC");
$sessions = $stmt->fetchAll();

// Get available exam types
$stmt = $pdo->query("SELECT DISTINCT exam_type FROM results ORDER BY exam_type");
$exam_types = $stmt->fetchAll();

// Get groups for selected class
$groups = [];
if ($class_id) {
    $stmt = $pdo->prepare("SELECT DISTINCT group_name FROM students WHERE class_id = ? AND group_name IS NOT NULL ORDER BY group_name");
    $stmt->execute([$class_id]);
    $groups = $stmt->fetchAll();
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>বিষয় ভিত্তিক টেবুলেশন শীট - স্কুল ম্যানেজমেন্ট সিস্টেম</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="../assets/css/style.css" rel="stylesheet">
    <style>
        @media print {
            .no-print { display: none !important; }
            .container-fluid { max-width: 100% !important; }
            body { font-size: 10px; }
            .tabulation-sheet { page-break-inside: avoid; }
            .table { font-size: 9px; }
        }
        
        .tabulation-sheet {
            background: white;
            padding: 20px;
            margin: 20px 0;
        }
        
        .school-header {
            text-align: center;
            border-bottom: 2px solid #000;
            padding-bottom: 15px;
            margin-bottom: 20px;
        }
        
        .school-logo {
            width: 60px;
            height: 60px;
            border: 2px solid #000;
            border-radius: 50%;
            display: inline-flex;
            align-items: center;
            justify-content: center;
            margin-bottom: 10px;
            font-size: 20px;
            background: #f8f9fa;
        }
        
        .exam-info {
            display: grid;
            grid-template-columns: 1fr 1fr 1fr;
            gap: 20px;
            margin-bottom: 20px;
            padding: 15px;
            background: #f8f9fa;
            border: 1px solid #ddd;
        }
        
        .tabulation-table {
            width: 100%;
            border-collapse: collapse;
            font-size: 11px;
            table-layout: auto; /* Allow table to adjust column widths automatically */
        }
        
        .tabulation-table th,
        .tabulation-table td {
            border: 1px solid #000;
            padding: 4px 6px;
            text-align: center;
            white-space: nowrap; /* Prevent text wrapping to maintain alignment */
        }
        
        .tabulation-table th {
            background-color: #f8f9fa;
            font-weight: bold;
        }
        
        .student-name {
            text-align: left !important;
            padding-left: 8px !important;
            min-width: 120px; /* Minimum width for student names */
        }
        
        .marks-column {
            min-width: 80px; /* Minimum width for marks breakdown column */
        }
        
        .result-column {
            min-width: 90px; /* Minimum width for combined result column */
        }
        
        .total-column {
            min-width: 60px; /* Minimum width for total marks column */
        }
        
        .grade-column {
            min-width: 50px; /* Minimum width for grade column */
        }
        
        .gpa-column {
            min-width: 60px; /* Minimum width for GPA column */
        }
        
        .result-column {
            min-width: 100px; /* Minimum width for combined result column */
        }
        
        .grade-f {
            background-color: #ffebee !important;
            color: #c62828;
            font-weight: bold;
        }
        
        .grade-a-plus {
            background-color: #e8f5e8 !important;
            color: #2e7d32;
            font-weight: bold;
        }
        
        .statistics-section {
            margin-top: 30px;
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 30px;
        }
        
        .signature-section {
            display: grid;
            grid-template-columns: 1fr 1fr 1fr;
            gap: 30px;
            margin-top: 40px;
            text-align: center;
        }
        
        .signature-box {
            border-top: 1px solid #000;
            padding-top: 10px;
            margin-top: 50px;
        }
        
        .rotate-text {
            writing-mode: vertical-rl;
            text-orientation: mixed;
            white-space: nowrap;
            min-width: 25px;
        }
        
        /* Responsive adjustments for smaller screens */
        @media (max-width: 1200px) {
            .tabulation-table {
                font-size: 10px;
            }
            
            .tabulation-table th,
            .tabulation-table td {
                padding: 3px 4px;
            }
            
            .student-name {
                min-width: 100px;
            }
            
            .marks-column {
                min-width: 70px;
            }
            
            .result-column {
                min-width: 90px;
            }
        }
        
        @media (max-width: 992px) {
            .tabulation-table {
                font-size: 9px;
            }
            
            .exam-info {
                grid-template-columns: 1fr;
            }
            
            .student-name {
                min-width: 80px;
            }
            
            .marks-column {
                min-width: 60px;
            }
            
            .result-column {
                min-width: 80px;
            }
        }
    </style>
</head>
<body>
    <!-- Navigation -->
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary no-print">
        <div class="container-fluid">
            <a class="navbar-brand" href="../index.php">
                <i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট সিস্টেম
            </a>
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="marksheet.php">
                    <i class="fas fa-certificate"></i> মার্কশীট
                </a>
                <a class="nav-link" href="tabulation_sheet.php">
                    <i class="fas fa-table"></i> টেবুলেশন শীট
                </a>
                <a class="nav-link" href="subject_code_tabulation_sheet.php">
                    <i class="fas fa-list-ol"></i> বিষয় কোড ভিত্তিক
                </a>
                <a class="nav-link" href="index.php">
                    <i class="fas fa-chart-bar"></i> রিপোর্ট
                </a>
                <a class="nav-link" href="../index.php">
                    <i class="fas fa-home"></i> হোম
                </a>
                <a class="nav-link" href="../logout.php">
                    <i class="fas fa-sign-out-alt"></i> লগআউট
                </a>
            </div>
        </div>
    </nav>

    <div class="container-fluid mt-4">
        <!-- Search Form -->
        <div class="card no-print mb-4">
            <div class="card-header bg-primary text-white">
                <h5><i class="fas fa-table"></i> বিষয় ভিত্তিক টেবুলেশন শীট তৈরি করুন</h5>
            </div>
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">ক্লাস নির্বাচন করুন</label>
                        <select name="class_id" class="form-select" required onchange="this.form.submit()">
                            <option value="">নির্বাচন করুন</option>
                            <?php foreach ($all_classes as $c): ?>
                                <option value="<?php echo $c['id']; ?>" <?php echo $c['id'] == $class_id ? 'selected' : ''; ?>>
                                    <?php echo $c['name'] . ' - ' . $c['section']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">গ্রুপ</label>
                        <select name="group_name" class="form-select">
                            <option value="">সব গ্রুপ</option>
                            <?php foreach ($groups as $g): ?>
                                <option value="<?php echo $g['group_name']; ?>" <?php echo $g['group_name'] == $group_name ? 'selected' : ''; ?>>
                                    <?php echo $g['group_name']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">সেশন</label>
                        <select name="session" class="form-select" required>
                            <option value="">নির্বাচন করুন</option>
                            <?php foreach ($sessions as $s): ?>
                                <option value="<?php echo $s['session']; ?>" <?php echo $s['session'] == $session ? 'selected' : ''; ?>>
                                    <?php echo $s['session']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">পরীক্ষার ধরন</label>
                        <select name="exam_type" class="form-select" required>
                            <option value="">নির্বাচন করুন</option>
                            <?php foreach ($exam_types as $e): ?>
                                <option value="<?php echo $e['exam_type']; ?>" <?php echo $e['exam_type'] == $exam_type ? 'selected' : ''; ?>>
                                    <?php echo ucfirst($e['exam_type']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">&nbsp;</label>
                        <button type="submit" class="btn btn-primary d-block w-100">
                            <i class="fas fa-table"></i> তৈরি করুন
                        </button>
                    </div>
                </form>
            </div>
        </div>

        <?php if ($class_info && !empty($students) && !empty($subjects)): ?>
        <!-- Print Controls -->
        <div class="text-center mb-3 no-print">
            <button onclick="window.print()" class="btn btn-success btn-lg me-2">
                <i class="fas fa-print"></i> প্রিন্ট করুন
            </button>
            <button onclick="exportToExcel()" class="btn btn-info btn-lg">
                <i class="fas fa-file-excel"></i> এক্সেল এ রপ্তানি
            </button>
        </div>

        <!-- Tabulation Sheet -->
        <div class="tabulation-sheet">
            <!-- School Header -->
            <div class="school-header">
                <div class="school-logo">
                    <?php if ($logo_image): ?>
                        <img src="../<?php echo htmlspecialchars($logo_image); ?>" alt="Logo" style="max-height: <?php echo $logo_size; ?>px; width: auto;">
                    <?php else: ?>
                        <i class="fas fa-graduation-cap"></i>
                    <?php endif; ?>
                </div>
                <h3 style="margin: 10px 0 5px 0;"><?php echo htmlspecialchars($site_name); ?></h3>
                <p style="margin: 0; font-size: 14px;"><?php echo htmlspecialchars($site_address); ?></p>
                <h4 style="margin: 10px 0 0 0; text-decoration: underline;">বিষয় ভিত্তিক টেবুলেশন শীট</h4>
            </div>

            <!-- Exam Information -->
            <div class="exam-info">
                <div>
                    <strong>ক্লাস:</strong> <?php echo $class_info['name'] . ' - ' . $class_info['section']; ?><br>
                    <strong>গ্রুপ:</strong> <?php echo $group_name ?: 'সব গ্রুপ'; ?>
                </div>
                <div>
                    <strong>পরীক্ষা:</strong> <?php echo ucfirst($exam_type); ?><br>
                    <strong>সেশন:</strong> <?php echo $session; ?>
                </div>
                <div>
                    <strong>মোট ছাত্র-ছাত্রী:</strong> <?php echo $total_students; ?><br>
                    <strong>তারিখ:</strong> <?php echo date('d/m/Y'); ?>
                </div>
            </div>

            <!-- Tabulation Table -->
            <div class="table-responsive">
                <table class="tabulation-table">
                    <thead>
                        <tr>
                            <th rowspan="2" class="student-column">রোল</th>
                            <th rowspan="2" class="student-name">নাম</th>
                            <?php 
                            // Determine which columns to show based on subject setup
                            $subject_configs = [];
                            
                            // Get mark distribution for each subject
                            foreach ($subjects as $subject) {
                                $stmt = $pdo->prepare("
                                    SELECT cq_marks, mcq_marks, practical_marks 
                                    FROM mark_distributions 
                                    WHERE subject_id = ? AND class_id = ? AND session = ? AND exam_type = ?
                                    LIMIT 1
                                ");
                                $stmt->execute([$subject['id'], $class_id, $session, $exam_type]);
                                $mark_dist = $stmt->fetch();
                                
                                if ($mark_dist) {
                                    $subject_configs[$subject['id']] = [
                                        'show_cq' => !empty($mark_dist['cq_marks']) && $mark_dist['cq_marks'] > 0,
                                        'show_mcq' => !empty($mark_dist['mcq_marks']) && $mark_dist['mcq_marks'] > 0,
                                        'show_practical' => !empty($mark_dist['practical_marks']) && $mark_dist['practical_marks'] > 0
                                    ];
                                } else {
                                    // Default to showing all if no configuration found
                                    $subject_configs[$subject['id']] = [
                                        'show_cq' => true,
                                        'show_mcq' => true,
                                        'show_practical' => true
                                    ];
                                }
                            }
                            
                            // Determine overall columns to show (if any subject uses them)
                            $show_cq = false;
                            $show_mcq = false;
                            $show_practical = false;
                            
                            foreach ($subject_configs as $config) {
                                if ($config['show_cq']) $show_cq = true;
                                if ($config['show_mcq']) $show_mcq = true;
                                if ($config['show_practical']) $show_practical = true;
                            }
                            
                            // Calculate column count per subject (2 columns: marks breakdown, combined result)
                            $column_count = 2;
                            
                            foreach ($subjects as $subject): ?>
                                <th colspan="<?php echo $column_count; ?>" class="subject-column"><?php echo $subject['name']; ?><?php if (!empty($subject['code'])): ?><br><small>(<?php echo $subject['code']; ?>)</small><?php endif; ?></th>
                            <?php endforeach; ?>
                            <th rowspan="2" class="total-column">মোট</th>
                            <th rowspan="2" class="grade-column">গ্রেড</th>
                            <th rowspan="2" class="gpa-column">জিপিএ</th>
                            <th rowspan="2" class="result-column">ফলাফল</th>
                        </tr>
                        <tr>
                            <?php foreach ($subjects as $subject): ?>
                                <th class="marks-column">মার্কস</th>
                                <th class="result-column">মোট/গ্রেড/জিপিএ</th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($students as $student): ?>
                            <?php
                            $student_total = 0;
                            $student_failed = false;
                            $student_grades = [];
                            $total_grade_points = 0;
                            $gpa_subjects = 0; // Subjects used for GPA calculation (excluding failed subjects)
                            $fourth_subject_found = false; // Track if 4th subject exists
                            $fourth_subject_grade_point = 0; // Track 4th subject grade point for adjustment
                            ?>
                            <tr>
                                <td><?php echo $student['student_id']; ?></td>
                                <td class="student-name"><?php echo $student['first_name'] . ' ' . $student['last_name']; ?></td>
                                
                                <?php foreach ($subjects as $subject): ?>
                                    <?php 
                                    // Get subject configuration
                                    $subj_config = $subject_configs[$subject['id']] ?? [
                                        'show_cq' => $show_cq,
                                        'show_mcq' => $show_mcq,
                                        'show_practical' => $show_practical
                                    ];
                                    ?>
                                    <?php 
                                    // Handle combined subjects
                                    if ($subject['id'] == 'combined_bangla') {
                                        // Get combined Bangla result
                                        $result = getCombinedBanglaResult($student['id'], array_filter($original_subjects, function($s) { return stripos($s['name'], 'বাংলা') !== false || stripos($s['name'], 'Bangla') !== false; }), $results_matrix);
                                        if ($result) {
                                            // Apply custom grading for combined Bangla subject
                                            $custom_grade = calculateBanglaGrade($result['cq_marks_array'], $result['mcq_marks'], $result['full_marks'], $result['pass_marks']);
                                            $result['grade'] = $custom_grade;
                                        }
                                    } else if ($subject['id'] == 'combined_english') {
                                        // Get combined English result
                                        $result = getCombinedEnglishResult($student['id'], array_filter($original_subjects, function($s) { return stripos($s['name'], 'ইংরেজি') !== false || stripos($s['name'], 'English') !== false; }), $results_matrix);
                                        if ($result) {
                                            // Apply custom grading for combined English subject
                                            $custom_grade = calculateEnglishGrade($result['cq_marks_array'], $result['full_marks'], $result['pass_marks']);
                                            $result['grade'] = $custom_grade;
                                        }
                                    } else if (isset($results_matrix[$student['id']][$subject['id']])) {
                                        $result = $results_matrix[$student['id']][$subject['id']];
                                        
                                        // Apply custom grading for Bangla and English subjects
                                        if (stripos($subject['name'], 'বাংলা') !== false || stripos($subject['name'], 'Bangla') !== false) {
                                            // For Bangla: Check average of 2 CQ marks + MCQ marks
                                            $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                                            $custom_grade = calculateBanglaGrade($cq_marks_array, $result['mcq_marks'], 100, 40); // Assuming 100 total marks and 40 pass marks
                                            $result['grade'] = $custom_grade;
                                        } else if (stripos($subject['name'], 'ইংরেজি') !== false || stripos($subject['name'], 'English') !== false) {
                                            // For English: Check CQ marks average
                                            $cq_marks_array = [$result['cq_marks']]; // Simplified - in real system, this would be an array of individual CQ marks
                                            $custom_grade = calculateEnglishGrade($cq_marks_array, 100, 40); // Assuming 100 total marks and 40 pass marks
                                            $result['grade'] = $custom_grade;
                                        }
                                    } else {
                                        $result = null;
                                    }
                                    ?>
                                    
                                    <?php if ($result): ?>
                                        
                                        <?php
                                        $student_total += $result['marks_obtained'];
                                        $student_grades[] = $result['grade'];
                                        
                                        // Check if this subject is a fourth subject
                                        $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                                        $stmt->execute([$student['id'], $subject['id']]);
                                        $subject_info = $stmt->fetch();
                                        $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                                        
                                        if ($result['grade'] == 'F') {
                                            // Check if student failed in this subject and it's not a fourth subject
                                            if (!$is_fourth_subject) {
                                                $student_failed = true;
                                            }
                                        } else {
                                            // Add grade point for GPA calculation (only for passed subjects)
                                            if ($is_fourth_subject) {
                                                $fourth_subject_found = true;
                                                $fourth_subject_grade_point = getGradePoint($result['grade']);
                                                // For 4th subject: if GPA > 2.00, subtract 2.00, otherwise subtract 0
                                                // Modified to match the requirement: if 4th subject marks > 2, subtract 2, otherwise subtract 0
                                                $adjusted_grade_point = ($fourth_subject_grade_point > 2.00) ? ($fourth_subject_grade_point - 2.00) : $fourth_subject_grade_point;
                                                $total_grade_points += $adjusted_grade_point;
                                            } else {
                                                // Regular subject - add full grade point
                                                $total_grade_points += getGradePoint($result['grade']);
                                            }
                                            $gpa_subjects++;
                                        }
                                        
                                        // Combined subject statistics are now handled in the initial loop
                                        ?>
                                        <td>
                                            <?php 
                                            $marks_breakdown = [];
                                            if ($subj_config['show_cq']) {
                                                // For combined subjects, show individual CQ marks and average without fractions
                                                if (($subject['name'] == 'বাংলা(একত্রে)' || $subject['name'] == 'ইংরেজি(একত্রে)') && isset($result['cq_marks_array'])) {
                                                    // Show individual marks
                                                    $individual_marks = [];
                                                    foreach ($result['cq_marks_array'] as $mark) {
                                                        $individual_marks[] = ceil($mark);
                                                    }
                                                    // Calculate average and round up fractional part
                                                    $avg_cq = count($result['cq_marks_array']) > 0 ? array_sum($result['cq_marks_array']) / count($result['cq_marks_array']) : 0;
                                                    $marks_breakdown[] = "সৃ: " . implode(', ', $individual_marks) . ' (গড়: ' . ceil($avg_cq) . ')';
                                                } 
                                                // For single Bangla/English subjects, apply the same logic
                                                else if ((stripos($subject['name'], 'বাংলা') !== false || stripos($subject['name'], 'Bangla') !== false || stripos($subject['name'], 'ইংরেজি') !== false || stripos($subject['name'], 'English') !== false) && isset($result['cq_marks'])) {
                                                    $marks_breakdown[] = "সৃ: " . ceil($result['cq_marks']);
                                                } else if (!empty($result['cq_marks'])) {
                                                    $marks_breakdown[] = "সৃ: " . ceil($result['cq_marks']);
                                                }
                                            }
                                            if ($subj_config['show_mcq']) {
                                                // For combined subjects, show individual MCQ marks and average without fractions
                                                if (($subject['name'] == 'বাংলা(একত্রে)' || $subject['name'] == 'ইংরেজি(একত্রে)') && isset($result['mcq_marks_array'])) {
                                                    // Show individual marks
                                                    $individual_marks = [];
                                                    foreach ($result['mcq_marks_array'] as $mark) {
                                                        $individual_marks[] = ceil($mark);
                                                    }
                                                    // Calculate average and round up fractional part
                                                    $avg_mcq = count($result['mcq_marks_array']) > 0 ? array_sum($result['mcq_marks_array']) / count($result['mcq_marks_array']) : 0;
                                                    $marks_breakdown[] = "বহু: " . implode(', ', $individual_marks) . ' (গড়: ' . ceil($avg_mcq) . ')';
                                                }
                                                // For single Bangla/English subjects, apply the same logic
                                                else if ((stripos($subject['name'], 'বাংলা') !== false || stripos($subject['name'], 'Bangla') !== false || stripos($subject['name'], 'ইংরেজি') !== false || stripos($subject['name'], 'English') !== false) && isset($result['mcq_marks'])) {
                                                    $marks_breakdown[] = "বহু: " . ceil($result['mcq_marks']);
                                                } else if (!empty($result['mcq_marks'])) {
                                                    $marks_breakdown[] = "বহু: " . ceil($result['mcq_marks']);
                                                }
                                            }
                                            if ($subj_config['show_practical'] && !empty($result['practical_marks'])) {
                                                $marks_breakdown[] = "ব্য: " . ceil($result['practical_marks']);
                                            }
                                            echo !empty($marks_breakdown) ? implode("<br>", $marks_breakdown) : '-';
                                            ?>
                                        </td>
                                        <td>
                                            <?php 
                                            echo "মোট: " . $result['marks_obtained'] . "<br>";
                                            echo "গ্রেড: " . $result['grade'] . "<br>";
                                            // Calculate and show GPA for this subject
                                            $subject_gpa = getGradePoint($result['grade']);
                                            echo "জিপিএ: " . number_format($subject_gpa, 2);
                                            ?>
                                        </td>
                                    <?php else: ?>
                                        <?php 
                                        // Student did not participate or sit for this exam
                                        // Check if this subject is a fourth subject before marking as failed
                                        $stmt = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
                                        $stmt->execute([$student['id'], $subject['id']]);
                                        $subject_info = $stmt->fetch();
                                        $is_fourth_subject = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
                                        
                                        // Only mark as failed if it's not a fourth subject
                                        if (!$is_fourth_subject) {
                                            $student_failed = true;
                                        }
                                        ?>
                                        <td>-</td>
                                        <td>
                                            <?php 
                                            echo "মোট: 0<br>";
                                            echo "গ্রেড: F<br>";
                                            echo "জিপিএ: 0.00";
                                            ?>
                                        </td>
                                    <?php endif; ?>
                                <?php endforeach; ?>
                                
                                <?php
                                // Calculate overall grade
                                $overall_grade = $student_failed ? 'F' : 'PASS';
                                if (!$student_failed && $student_total > 0) {
                                    $total_possible = count($subjects) * 100; // Assuming 100 marks per subject
                                    $percentage = ($student_total / $total_possible) * 100;
                                    
                                    if ($percentage >= 80) $overall_grade = 'A+';
                                    elseif ($percentage >= 70) $overall_grade = 'A';
                                    elseif ($percentage >= 60) $overall_grade = 'A-';
                                    elseif ($percentage >= 50) $overall_grade = 'B';
                                    elseif ($percentage >= 40) $overall_grade = 'C';
                                    elseif ($percentage >= 33) $overall_grade = 'D';
                                    else $overall_grade = 'F';
                                }
                                
                                // Calculate GPA (Grade Point Average) with Jessore Board 4th subject rules
                                $gpa = 0.00;
                                if ($gpa_subjects > 0) {
                                    // For Jessore Board: if 6 subjects including 4th subject, divide by 5
                                    // Otherwise, use normal calculation
                                    $total_subjects = count($subjects);
                                    // For Jessore Board: when 4th subject exists, divide by (number of subjects - 1)
                                    // because 4th subject's extra points are added but the subject itself is not counted in division
                                    if ($fourth_subject_found) {
                                        // When 4th subject exists, divide by (gpa_subjects - 1)
                                        // because 4th subject is not counted in the division
                                        // Make sure we don't divide by zero
                                        $gpa = ($gpa_subjects - 1) > 0 ? round($total_grade_points / ($gpa_subjects - 1), 2) : 0.00;
                                    } else {
                                        // Normal calculation
                                        $gpa = round($total_grade_points / $gpa_subjects, 2);
                                    }
                                } else {
                                    $gpa = 0.00; // GPA is 0.00 if all subjects are failed
                                }
                                
                                // Get merit position for this student
                                $merit_position = isset($student_merit_positions[$student['id']]) ? $student_merit_positions[$student['id']] : null;
                                ?>
                                
                                <td><strong><?php echo $student_total; ?></strong></td>
                                <td class="<?php echo $overall_grade == 'F' ? 'grade-f' : ($overall_grade == 'A+' ? 'grade-a-plus' : ''); ?>">
                                    <strong><?php echo $overall_grade; ?></strong>
                                </td>
                                <td><strong><?php echo !$student_failed && $gpa_subjects > 0 ? number_format($gpa, 2) : ''; ?></strong></td>
                                <td class="<?php echo $student_failed ? 'grade-f' : 'text-success'; ?>">
                                    <strong>
                                        <?php echo $student_failed ? 'অকৃতকার্য' : 'কৃতকার্য'; ?>
                                        <?php if ($merit_position): ?>
                                            <span style="font-size: 0.8em;">(<?php echo $merit_position; ?>)</span>
                                        <?php endif; ?>
                                    </strong>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>

            <!-- Statistics Section -->
            <div class="statistics-section">
                <div>
                    <h5><i class="fas fa-chart-bar"></i> সামগ্রিক পরিসংখ্যান</h5>
                    <table class="table table-bordered table-sm">
                        <tr>
                            <td><strong>মোট ছাত্র-ছাত্রী:</strong></td>
                            <td><?php echo $total_students; ?></td>
                        </tr>
                        <tr>
                            <td><strong>কৃতকার্য:</strong></td>
                            <td class="text-success"><?php echo $passed_students; ?> (<?php echo $total_students > 0 ? round(($passed_students/$total_students)*100, 1) : 0; ?>%)</td>
                        </tr>
                        <tr>
                            <td><strong>অকৃতকার্য:</strong></td>
                            <td class="text-danger"><?php echo $failed_students; ?> (<?php echo $total_students > 0 ? round(($failed_students/$total_students)*100, 1) : 0; ?>%)</td>
                        </tr>
                        <tr>
                            <td><strong>পাসের হার:</strong></td>
                            <td><strong><?php echo $total_students > 0 ? round(($passed_students/$total_students)*100, 1) : 0; ?>%</strong></td>
                        </tr>
                    </table>
                </div>
                
                <div>
                    <h5><i class="fas fa-book"></i> বিষয়ভিত্তিক পরিসংখ্যান</h5>
                    <table class="table table-bordered table-sm">
                        <thead>
                            <tr>
                                <th>বিষয়</th>
                                <th>কোড</th>
                                <th>গড়</th>
                                <th>সর্বোচ্চ</th>
                                <th>সর্বনিম্ন</th>
                                <th>পাস</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($subjects as $subject): ?>
                                <?php if (isset($subject_stats[$subject['id']])): ?>
                                    <?php 
                                    $stats = $subject_stats[$subject['id']];
                                    $average = $stats['student_count'] > 0 ? round($stats['total_marks'] / $stats['student_count'], 1) : 0;
                                    $pass_rate = $stats['student_count'] > 0 ? round(($stats['passed'] / $stats['student_count']) * 100, 1) : 0;
                                    ?>
                                    <tr>
                                        <td><?php echo substr($subject['name'], 0, 15); ?></td>
                                        <td><?php echo $subject['code'] ?? 'N/A'; ?></td>
                                        <td><?php echo $average; ?></td>
                                        <td><?php echo $stats['highest']; ?></td>
                                        <td><?php echo $stats['lowest']; ?></td>
                                        <td><?php echo $pass_rate; ?>%</td>
                                    </tr>
                                <?php endif; ?>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <!-- Signatures -->
            <div class="signature-section">
                <div>
                    <div class="signature-box"> প্রধান শিক্ষক</div>
                </div>
                <div>
                    <div class="signature-box">পরীক্ষা কমিটি</div>
                </div>
                <div>
                    <div class="signature-box">পরীক্ষা কমিটির সদস্যবৃন্দ</div>
                </div>
            </div>
        </div>

        <?php elseif ($class_id && $session && $exam_type): ?>
        <div class="alert alert-warning text-center">
            <i class="fas fa-exclamation-triangle"></i> 
            নির্বাচিত ক্লাস, সেশন ও পরীক্ষার জন্য কোন ডেটা পাওয়া যায়নি।
        </div>
        <?php endif; ?>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function exportToExcel() {
            // Simple Excel export functionality
            const table = document.querySelector('.tabulation-table');
            const wb = XLSX.utils.table_to_book(table, {sheet: "Tabulation Sheet"});
            const filename = `Subject_Based_Tabulation_Sheet_${new Date().toISOString().slice(0,10)}.xlsx`;
            XLSX.writeFile(wb, filename);
        }
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
</body>
</html>