<?php
require_once 'config/database.php';

// Test student ID 6279
$student_id = 6279;
$session = '2025';
$exam_type = 'final';

try {
    // Get Bangla subjects for this student
    $stmt = $pdo->prepare("
        SELECT r.subject_id, r.marks_obtained, sub.name
        FROM results r
        JOIN subjects sub ON r.subject_id = sub.id
        WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
        AND (sub.name LIKE '%বাংলা%' OR sub.name LIKE '%Bangla%')
        ORDER BY sub.name
    ");
    $stmt->execute([$student_id, $session, $exam_type]);
    $bangla_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "=== BANGLa SUBJECTS FOR STUDENT $student_id ===\n";
    $total_bangla = 0;
    foreach ($bangla_results as $result) {
        echo "Subject: {$result['name']} - Marks: {$result['marks_obtained']}\n";
        $total_bangla += $result['marks_obtained'];
    }
    echo "TOTAL BANGLa MARKS: $total_bangla\n\n";

    // Get English subjects for this student
    $stmt = $pdo->prepare("
        SELECT r.subject_id, r.marks_obtained, sub.name
        FROM results r
        JOIN subjects sub ON r.subject_id = sub.id
        WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
        AND (sub.name LIKE '%ইংরেজি%' OR sub.name LIKE '%English%')
        ORDER BY sub.name
    ");
    $stmt->execute([$student_id, $session, $exam_type]);
    $english_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "=== ENGLISH SUBJECTS FOR STUDENT $student_id ===\n";
    $total_english = 0;
    foreach ($english_results as $result) {
        echo "Subject: {$result['name']} - Marks: {$result['marks_obtained']}\n";
        $total_english += $result['marks_obtained'];
    }
    echo "TOTAL ENGLISH MARKS: $total_english\n\n";

    // Now test class maximum calculation
    echo "=== CLASS MAXIMUM CALCULATION ===\n";

    // Get student's class
    $stmt = $pdo->prepare("SELECT class_id FROM students WHERE id = ?");
    $stmt->execute([$student_id]);
    $student = $stmt->fetch();
    $class_id = $student['class_id'];

    echo "Student Class ID: $class_id\n";

    // Get all Bangla subject IDs from this student's results
    $bangla_ids = array_unique(array_column($bangla_results, 'subject_id'));
    echo "Bangla Subject IDs: " . implode(', ', $bangla_ids) . "\n";

    // Calculate class maximum for Bangla
    $stmt = $pdo->prepare("SELECT id FROM students WHERE class_id = ? AND status = 'active'");
    $stmt->execute([$class_id]);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $max_bangla_marks = 0;
    foreach ($students as $student_row) {
        $placeholders = str_repeat('?,', count($bangla_ids) - 1) . '?';
        $stmt = $pdo->prepare("SELECT SUM(marks_obtained) as total_marks FROM results WHERE student_id = ? AND subject_id IN ($placeholders) AND session = ? AND exam_type = ?");
        $params = array_merge([$student_row['id']], $bangla_ids, [$session, $exam_type]);
        $stmt->execute($params);
        $result = $stmt->fetch();

        if ($result && $result['total_marks'] !== null) {
            $student_total = $result['total_marks'];
            if ($student_total > $max_bangla_marks) {
                $max_bangla_marks = $student_total;
            }
        }
    }

    echo "CLASS MAXIMUM BANGLa MARKS: $max_bangla_marks\n";

    // Same for English
    $english_ids = array_unique(array_column($english_results, 'subject_id'));
    echo "English Subject IDs: " . implode(', ', $english_ids) . "\n";

    $max_english_marks = 0;
    foreach ($students as $student_row) {
        $placeholders = str_repeat('?,', count($english_ids) - 1) . '?';
        $stmt = $pdo->prepare("SELECT SUM(marks_obtained) as total_marks FROM results WHERE student_id = ? AND subject_id IN ($placeholders) AND session = ? AND exam_type = ?");
        $params = array_merge([$student_row['id']], $english_ids, [$session, $exam_type]);
        $stmt->execute($params);
        $result = $stmt->fetch();

        if ($result && $result['total_marks'] !== null) {
            $student_total = $result['total_marks'];
            if ($student_total > $max_english_marks) {
                $max_english_marks = $student_total;
            }
        }
    }

    echo "CLASS MAXIMUM ENGLISH MARKS: $max_english_marks\n";

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>
