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

echo "<h1>🔍 Test Latest Marks Query</h1>";

$subject_id = 2;
$session = '2024';
$exam_type = 'midterm';
$class_id = 11;

// Test the new query
$query = "
    SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number,
           r.id as result_id, r.marks_obtained, r.cq_marks, r.mcq_marks, 
           r.practical_marks, r.assignment_marks, r.attendance_marks, r.grade
    FROM students s
    LEFT JOIN (
        SELECT r1.* FROM results r1
        INNER JOIN (
            SELECT student_id, MAX(id) as max_id 
            FROM results 
            WHERE subject_id = ? AND exam_id IS NULL AND session = ? AND exam_type = ?
            GROUP BY student_id
        ) r2 ON r1.student_id = r2.student_id AND r1.id = r2.max_id
    ) r ON s.id = r.student_id
    WHERE s.class_id = ? AND s.session = ? AND s.status = 'active'
    ORDER BY s.id, s.roll_number, s.first_name
    LIMIT 5
";

$executeParams = [$subject_id, $session, $exam_type, $class_id, $session];

echo "<h2>Query Parameters:</h2>";
echo "<pre>" . print_r($executeParams, true) . "</pre>";

try {
    $stmt = $pdo->prepare($query);
    $stmt->execute($executeParams);
    $students = $stmt->fetchAll();
    
    echo "<h2>Results:</h2>";
    if (empty($students)) {
        echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px;'>";
        echo "<p>❌ No students found!</p>";
        echo "</div>";
    } else {
        echo "<div style='background: #d4edda; padding: 15px; border-radius: 5px;'>";
        echo "<p>✅ Found " . count($students) . " students</p>";
        echo "</div>";
        
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr style='background: #f8f9fa;'>";
        echo "<th>Student ID</th><th>Name</th><th>Roll</th><th>Result ID</th><th>CQ</th><th>MCQ</th><th>Total</th><th>Grade</th>";
        echo "</tr>";
        
        foreach ($students as $student) {
            $bg_color = ($student['id'] == 1647) ? 'background: #fff3cd;' : '';
            echo "<tr style='$bg_color'>";
            echo "<td><strong>{$student['id']}</strong></td>";
            echo "<td>{$student['first_name']} {$student['last_name']}</td>";
            echo "<td>{$student['roll_number']}</td>";
            echo "<td>" . ($student['result_id'] ?? 'NULL') . "</td>";
            echo "<td>" . ($student['cq_marks'] ?? '0.0') . "</td>";
            echo "<td>" . ($student['mcq_marks'] ?? '0.0') . "</td>";
            echo "<td><strong>" . ($student['marks_obtained'] ?? '0.0') . "</strong></td>";
            echo "<td><strong>" . ($student['grade'] ?? 'N/A') . "</strong></td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
} catch (Exception $e) {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px;'>";
    echo "<p>❌ Query Error: " . $e->getMessage() . "</p>";
    echo "</div>";
}

echo "<p><a href='marks/entry.php'>← Back to Marks Entry</a></p>";
?>