<?php
// Test script to check class maximum marks calculation

// Include database connection
require_once 'config/database.php';

// Sample data for testing
$student_id = 6194;
$session = 2025;
$exam_type = 'final';

// Get student details
$stmt = $pdo->prepare("
    SELECT s.*, c.name as class_name, c.section 
    FROM students s 
    JOIN classes c ON s.class_id = c.id 
    WHERE s.id = ?
");
$stmt->execute([$student_id]);
$student = $stmt->fetch();

if (!$student) {
    echo "Student not found";
    exit;
}

// Get results for the student
$stmt = $pdo->prepare("
    SELECT r.*, sub.name as subject_name, sub.code as subject_code
    FROM results r
    LEFT JOIN subjects sub ON r.subject_id = sub.id
    WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
    AND r.id IN (
        SELECT MAX(id)
        FROM results
        WHERE student_id = ? AND session = ? AND exam_type = ?
        GROUP BY subject_id
    )
    ORDER BY sub.name
");
$stmt->execute([$student_id, $session, $exam_type, $student_id, $session, $exam_type]);
$results_with_marks = $stmt->fetchAll();

// Group Bangla and English subjects
$bangla_results = [];
$english_results = [];

foreach ($results_with_marks as $result) {
    // Check if this is a Bangla subject
    if (stripos($result['subject_name'], 'বাংলা') !== false || stripos($result['subject_name'], 'Bangla') !== false) {
        $bangla_results[] = $result;
    }
    // Check if this is an English subject
    else if (stripos($result['subject_name'], 'ইংরেজি') !== false || stripos($result['subject_name'], 'English') !== false) {
        $english_results[] = $result;
    }
}

echo "Bangla Subjects:\n";
foreach ($bangla_results as $result) {
    echo "- " . $result['subject_name'] . ": " . ($result['cq_marks'] + $result['mcq_marks']) . " marks\n";
}

echo "\nEnglish Subjects:\n";
foreach ($english_results as $result) {
    echo "- " . $result['subject_name'] . ": " . ($result['cq_marks'] + $result['mcq_marks']) . " marks\n";
}

// Test class maximum calculation for Bangla
if (!empty($bangla_results)) {
    $bangla_ids = array_column($bangla_results, 'subject_id');
    if (!empty($bangla_ids)) {
        // Get all students in the class
        $stmt = $pdo->prepare("SELECT id FROM students WHERE class_id = ? AND status = 'active'");
        $stmt->execute([$student['class_id']]);
        $students = $stmt->fetchAll();
        
        $max_combined_marks = 0;
        foreach ($students as $student_row) {
            $student_id_tmp = $student_row['id'];
            
            // Get results for this student in all Bangla subjects
            $placeholders = str_repeat('?,', count($bangla_ids) - 1) . '?';
            $stmt = $pdo->prepare("SELECT * FROM results WHERE student_id = ? AND subject_id IN ($placeholders) AND session = ? AND exam_type = ?");
            $params = array_merge([$student_id_tmp], $bangla_ids, [$session, $exam_type]);
            $stmt->execute($params);
            $student_results = $stmt->fetchAll();
            
            if (!empty($student_results)) {
                echo "\nStudent ID: " . $student_id_tmp . " has " . count($student_results) . " Bangla results\n";
                foreach ($student_results as $res) {
                    echo "  - " . $res['subject_name'] . ": CQ=" . $res['cq_marks'] . ", MCQ=" . $res['mcq_marks'] . "\n";
                }
            }
        }
    }
}
?>