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

// Test the fixed query for student ID 6185 with session 2025 and exam_type midterm
$student_id = 6185;
$session = '2025';
$exam_type = 'midterm';

echo "=== TESTING DUPLICATE SUBJECT FIX ===\n\n";

// Original query (might have duplicates)
echo "1. Original query results:\n";
$stmt = $pdo->prepare("
    SELECT r.*, sub.name as subject_name
    FROM results r
    LEFT JOIN subjects sub ON r.subject_id = sub.id
    WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
    ORDER BY sub.name
");
$stmt->execute([$student_id, $session, $exam_type]);
$original_results = $stmt->fetchAll();

echo "Total results: " . count($original_results) . "\n";
$subject_names = [];
foreach ($original_results as $result) {
    $subject_names[] = $result['subject_name'];
}
print_r(array_count_values($subject_names));

echo "\n2. Fixed query results:\n";
// Fixed query (should not have duplicates)
$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]);
$fixed_results = $stmt->fetchAll();

echo "Total results: " . count($fixed_results) . "\n";
$fixed_subject_names = [];
foreach ($fixed_results as $result) {
    $fixed_subject_names[] = $result['subject_name'];
}
print_r(array_count_values($fixed_subject_names));

echo "\n=== COMPARISON ===\n";
echo "Original count: " . count($original_results) . "\n";
echo "Fixed count: " . count($fixed_results) . "\n";

if (count($fixed_results) <= count($original_results)) {
    echo "✅ Fix applied successfully - removed duplicates\n";
} else {
    echo "❌ Fix not working properly\n";
}

// Check for duplicates in fixed results
$has_duplicates = false;
$subject_counts = array_count_values($fixed_subject_names);
foreach ($subject_counts as $count) {
    if ($count > 1) {
        $has_duplicates = true;
        break;
    }
}

if (!$has_duplicates) {
    echo "✅ No duplicates found in fixed results\n";
} else {
    echo "❌ Duplicates still exist in fixed results\n";
}

echo "\n=== TEST COMPLETE ===\n";
?>