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

// Check for multiple results for the same student, subject, session, and exam_type
$stmt = $pdo->prepare('SELECT * FROM results WHERE student_id = (SELECT id FROM students WHERE student_id = ?) AND subject_id = ? AND session = ? AND exam_type = ? ORDER BY id');
$stmt->execute(['20250020', 50, 2025, 'final']);
$results = $stmt->fetchAll();

echo "Multiple results for subject ID 50 (Bangla 2nd Paper):\n";
foreach ($results as $result) {
    echo "Result ID: " . $result['id'] . ", CQ: " . $result['cq_marks'] . ", MCQ: " . $result['mcq_marks'] . ", Marks: " . $result['marks_obtained'] . ", Grade: " . $result['grade'] . "\n";
}

// Check for all duplicates for this student
echo "\nChecking for all duplicate results for student 20250020:\n";
$stmt = $pdo->prepare("
    SELECT r1.*, sub.name as subject_name
    FROM results r1
    JOIN subjects sub ON r1.subject_id = sub.id
    WHERE r1.student_id = (SELECT id FROM students WHERE student_id = ?)
    AND r1.session = ?
    AND r1.exam_type = ?
    AND (
        SELECT COUNT(*) 
        FROM results r2 
        WHERE r2.student_id = r1.student_id 
        AND r2.subject_id = r1.subject_id 
        AND r2.session = r1.session 
        AND r2.exam_type = r1.exam_type
    ) > 1
    ORDER BY r1.subject_id, r1.id
");
$stmt->execute(['20250020', 2025, 'final']);
$duplicates = $stmt->fetchAll();

echo "All duplicate results found:\n";
foreach ($duplicates as $result) {
    echo "Result ID: " . $result['id'] . ", Subject: " . $result['subject_name'] . " (ID: " . $result['subject_id'] . "), CQ: " . $result['cq_marks'] . ", MCQ: " . $result['mcq_marks'] . ", Marks: " . $result['marks_obtained'] . ", Grade: " . $result['grade'] . "\n";
}
?>