<?php
// Include the updated transposed tabulation sheet file to test the actual function
// We'll copy the relevant functions here to test the fix

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,
        'individual_results' => [], // Store individual results for detailed display
        'is_fourth_subject' => false // Track if this combined subject is a fourth subject
    ];
    
    $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];
            
            // Check if any of the individual Bangla subjects is a fourth subject
            if (isset($result['is_fourth_subject']) && $result['is_fourth_subject']) {
                $combined['is_fourth_subject'] = true;
            }
            
            $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
            
            // Store individual result for detailed display
            $combined['individual_results'][] = [
                'subject_name' => $subject['name'],
                'cq_marks' => $result['cq_marks'],
                'mcq_marks' => $result['mcq_marks']
            ];
            
            $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;
        
        // For Bangla subjects: determine pass/fail based on Jessore Board rules
        // CQ average > 22 AND MCQ average > 9 to pass
        if ($combined['cq_marks'] > 22 && $combined['mcq_marks'] > 9) {
            // Calculate overall percentage for grade assignment
            $overall_marks = $combined['cq_marks'] + $combined['mcq_marks'];
            $total_possible_marks = 100; // Assuming 50+50 for CQ+MCQ
            $percentage = ($overall_marks / $total_possible_marks) * 100;
            
            if ($percentage >= 80) $combined['grade'] = 'A+';
            elseif ($percentage >= 70) $combined['grade'] = 'A';
            elseif ($percentage >= 60) $combined['grade'] = 'A-';
            elseif ($percentage >= 50) $combined['grade'] = 'B';
            elseif ($percentage >= 40) $combined['grade'] = 'C';
            elseif ($percentage >= 33) $combined['grade'] = 'D';
            else $combined['grade'] = 'F';
        } else {
            // Student fails according to Jessore Board Bangla rules
            $combined['grade'] = 'F';
        }
        
        $combined['grade_point'] = getGradePoint($combined['grade']);
        
        return $combined;
    }
    
    // If no valid results, return null
    return null;
}

// Function to convert grade to grade point
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;
    }
}

// Now test with the actual data
require_once 'config/database.php';

$student_roll = '20250020';
$class_id = 10;
$session = 2025;
$exam_type = 'final';
$group_name = '';

echo "<h2>Testing ACTUAL Fix for Transposed Tabulation Sheet - Student $student_roll</h2>\n";

// Get student info
$stmt = $pdo->prepare("
    SELECT s.* FROM students s 
    WHERE s.student_id = ? AND s.class_id = ? AND s.status = 'active'
");
$stmt->execute([$student_roll, $class_id]);
$student = $stmt->fetch();

if (!$student) {
    echo "<p>Student not found!</p>\n";
    exit();
}

echo "<h3>Student: " . htmlspecialchars($student['first_name'] . ' ' . $student['last_name']) . " ($student_roll)</h3>\n";

// Build the results matrix - using the FIXED query that gets only the latest results
$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 = ?
    AND r.id IN (
        SELECT MAX(id)
        FROM results
        WHERE student_id = r.student_id AND subject_id = r.subject_id AND session = ? AND exam_type = ?
        GROUP BY student_id, subject_id
    )
    " . ($group_name ? "AND LOWER(TRIM(s.group_name)) = LOWER(TRIM(?))" : "") . "
");
$params = [$class_id, $session, $exam_type, $session, $exam_type];
if ($group_name) $params[] = $group_name;
$stmt->execute($params);
$all_results = $stmt->fetchAll();

// Organize results in matrix format
$results_matrix = [];
foreach ($all_results as $result) {
    // Check if this subject is a fourth subject for this student
    $stmt_check = $pdo->prepare("SELECT is_fourth_subject FROM student_subjects WHERE student_id = ? AND subject_id = ? LIMIT 1");
    $stmt_check->execute([$result['student_id'], $result['subject_id']]);
    $subject_info = $stmt_check->fetch();
    $result['is_fourth_subject'] = $subject_info && $subject_info['is_fourth_subject'] ? true : false;
    
    $results_matrix[$result['student_id']][$result['subject_id']] = $result;
}

// Fetch subjects for this class - to identify Bangla subjects
$stmt = $pdo->prepare("
    SELECT DISTINCT s.id, s.name, s.code 
    FROM results r
    JOIN subjects s ON r.subject_id = s.id
    JOIN students st ON r.student_id = st.id
    WHERE st.class_id = ? AND r.session = ? AND r.exam_type = ?
        " . ($group_name ? "AND LOWER(TRIM(st.group_name)) = LOWER(TRIM(?))" : "") . "
        ORDER BY 
            CASE 
                WHEN s.name LIKE '%বাংলা%' OR s.name LIKE '%Bangla%' THEN 1
                WHEN s.name LIKE '%ইংরেজি%' OR s.name LIKE '%English%' THEN 2
                ELSE 3
            END,
            s.name
");
$params = [$class_id, $session, $exam_type];
if ($group_name) $params[] = $group_name;
$stmt->execute($params);
$raw_subjects = $stmt->fetchAll();

// Get Bangla subjects
$bangla_subjects = array_filter($raw_subjects, function($s) { 
    return (stripos($s['name'], 'বাংলা') !== false) || (stripos($s['name'], 'Bangla') !== false && stripos($s['name'], 'Bangladesh') === false); 
});

echo "<h3>Processing Bangla Subjects:</h3>\n";
foreach ($bangla_subjects as $subject) {
    echo "- " . $subject['name'] . " (ID: " . $subject['id'] . ")\n";
}

// Get combined Bangla result using the updated function
$combined_bangla_result = getCombinedBanglaResult($student['id'], $bangla_subjects, $results_matrix);

if ($combined_bangla_result) {
    echo "<h3>Combined Bangla Result (After ACTUAL Fix):</h3>\n";
    echo "CQ Marks Array: " . json_encode($combined_bangla_result['cq_marks_array']) . "<br>\n";
    echo "MCQ Marks Array: " . json_encode($combined_bangla_result['mcq_marks_array']) . "<br>\n";
    echo "Average CQ Marks: " . $combined_bangla_result['cq_marks'] . "<br>\n";
    echo "Average MCQ Marks: " . $combined_bangla_result['mcq_marks'] . "<br>\n";
    echo "Grade: " . $combined_bangla_result['grade'] . "<br>\n";
    echo "Grade Point: " . $combined_bangla_result['grade_point'] . "<br>\n";
    
    // Verify the calculation
    $avg_cq = array_sum($combined_bangla_result['cq_marks_array']) / count($combined_bangla_result['cq_marks_array']);
    $avg_mcq = array_sum($combined_bangla_result['mcq_marks_array']) / count($combined_bangla_result['mcq_marks_array']);
    
    echo "<h4>Verification:</h4>\n";
    echo "Calculated Average CQ: " . $avg_cq . "<br>\n";
    echo "Calculated Average MCQ: " . $avg_mcq . "<br>\n";
    echo "CQ Average > 22: " . ($avg_cq > 22 ? 'YES' : 'NO') . "<br>\n";
    echo "MCQ Average > 9: " . ($avg_mcq > 9 ? 'YES' : 'NO') . "<br>\n";
    
    if ($avg_cq > 22 && $avg_mcq > 9) {
        echo "Student PASSES Bangla<br>\n";
    } else {
        echo "Student FAILS Bangla (as expected according to Jessore Board rules)<br>\n";
    }
} else {
    echo "<p>No combined Bangla result found.</p>\n";
}

echo "<h3>Final Result:</h3>\n";
echo "For student $student_roll, Bangla CQ average is " . round($avg_cq, 2) . ", which is " . ($avg_cq > 22 ? 'GREATER than' : 'LESS than') . " 22.<br>\n";
echo "MCQ average is " . round($avg_mcq, 2) . ", which is " . ($avg_mcq > 9 ? 'GREATER than' : 'LESS than') . " 9.<br>\n";
echo "Since the CQ average is " . ($avg_cq > 22 ? 'GREATER' : 'LESS') . " than 22, the student " . ($avg_cq > 22 && $avg_mcq > 9 ? 'PASSES' : 'FAILS') . " Bangla.<br>\n";
echo "The final grade is " . $combined_bangla_result['grade'] . " with Grade Point " . $combined_bangla_result['grade_point'] . "<br>\n";

if ($combined_bangla_result['grade'] == 'F' && $combined_bangla_result['grade_point'] == 0.00) {
    echo "<h2 style='color: green;'>SUCCESS: The fix is working correctly!</h2>\n";
    echo "The student's Bangla grade is now F with 0.00 GP as expected according to Jessore Board rules.<br>\n";
} else {
    echo "<h2 style='color: red;'>ISSUE: The fix may not be working as expected.</h2>\n";
    echo "Expected: Grade F with GP 0.00<br>\n";
    echo "Actual: Grade " . $combined_bangla_result['grade'] . " with GP " . $combined_bangla_result['grade_point'] . "<br>\n";
}
?>