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

echo "<h1>🔍 Debug Student IDs</h1>";

// Check students table
echo "<h2>1. Students in Class 11, Session 2024:</h2>";
$stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number FROM students WHERE class_id = 11 AND session = '2024' AND status = 'active' ORDER BY roll_number LIMIT 10");
$stmt->execute();
$students = $stmt->fetchAll();

echo "<table border='1' style='border-collapse: collapse;'>";
echo "<tr><th>ID (Primary Key)</th><th>Student ID</th><th>Name</th><th>Roll</th></tr>";
foreach ($students as $student) {
    echo "<tr>";
    echo "<td><strong>{$student['id']}</strong></td>";
    echo "<td>{$student['student_id']}</td>";
    echo "<td>{$student['first_name']} {$student['last_name']}</td>";
    echo "<td>{$student['roll_number']}</td>";
    echo "</tr>";
}
echo "</table>";

// Check results table
echo "<h2>2. Results for Subject 2, Session 2024, Midterm:</h2>";
$stmt = $pdo->prepare("SELECT id, student_id, cq_marks, mcq_marks, marks_obtained, grade FROM results WHERE subject_id = 2 AND session = '2024' AND exam_type = 'midterm' AND marks_obtained > 0 ORDER BY student_id LIMIT 10");
$stmt->execute();
$results = $stmt->fetchAll();

echo "<table border='1' style='border-collapse: collapse;'>";
echo "<tr><th>Result ID</th><th>Student ID (FK)</th><th>CQ</th><th>MCQ</th><th>Total</th><th>Grade</th></tr>";
foreach ($results as $result) {
    echo "<tr>";
    echo "<td>{$result['id']}</td>";
    echo "<td><strong>{$result['student_id']}</strong></td>";
    echo "<td>{$result['cq_marks']}</td>";
    echo "<td>{$result['mcq_marks']}</td>";
    echo "<td>{$result['marks_obtained']}</td>";
    echo "<td>{$result['grade']}</td>";
    echo "</tr>";
}
echo "</table>";

// Check if student_id 1647 exists in students table
echo "<h2>3. Check Student ID 1647:</h2>";
$stmt = $pdo->prepare("SELECT * FROM students WHERE id = 1647");
$stmt->execute();
$student_1647 = $stmt->fetch();

if ($student_1647) {
    echo "<div style='background: #d4edda; padding: 15px; border-radius: 5px;'>";
    echo "<p>✅ Student ID 1647 found in students table:</p>";
    echo "<ul>";
    echo "<li>Name: {$student_1647['first_name']} {$student_1647['last_name']}</li>";
    echo "<li>Class ID: {$student_1647['class_id']}</li>";
    echo "<li>Session: {$student_1647['session']}</li>";
    echo "<li>Roll: {$student_1647['roll_number']}</li>";
    echo "<li>Status: {$student_1647['status']}</li>";
    echo "</ul>";
    echo "</div>";
} else {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px;'>";
    echo "<p>❌ Student ID 1647 NOT found in students table!</p>";
    echo "</div>";
}

// Test the exact JOIN
echo "<h2>4. Test JOIN for Student 1647:</h2>";
$stmt = $pdo->prepare("
    SELECT s.id, s.first_name, s.last_name, s.class_id, s.session,
           r.id as result_id, r.marks_obtained, r.cq_marks, r.grade
    FROM students s
    LEFT JOIN results r ON s.id = r.student_id 
        AND r.subject_id = 2 AND r.session = '2024' AND r.exam_type = 'midterm'
    WHERE s.id = 1647
");
$stmt->execute();
$join_result = $stmt->fetch();

if ($join_result) {
    echo "<div style='background: #e7f3ff; padding: 15px; border-radius: 5px;'>";
    echo "<p><strong>JOIN Result:</strong></p>";
    echo "<ul>";
    echo "<li>Student ID: {$join_result['id']}</li>";
    echo "<li>Name: {$join_result['first_name']} {$join_result['last_name']}</li>";
    echo "<li>Class ID: {$join_result['class_id']}</li>";
    echo "<li>Session: {$join_result['session']}</li>";
    echo "<li>Result ID: " . ($join_result['result_id'] ?? 'NULL') . "</li>";
    echo "<li>Marks: " . ($join_result['marks_obtained'] ?? '0.0') . "</li>";
    echo "<li>CQ: " . ($join_result['cq_marks'] ?? '0.0') . "</li>";
    echo "<li>Grade: " . ($join_result['grade'] ?? 'N/A') . "</li>";
    echo "</ul>";
    echo "</div>";
} else {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px;'>";
    echo "<p>❌ No JOIN result found!</p>";
    echo "</div>";
}

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