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

// Check a specific student's subjects
$student_id = 1648; // Using an example student ID from the database dump

echo "Checking student subjects for student ID: $student_id\n";

// Check student info
$stmt = $pdo->prepare("SELECT * FROM students WHERE id = ?");
$stmt->execute([$student_id]);
$student = $stmt->fetch();

if (!$student) {
    echo "Student not found!\n";
    exit;
}

echo "Student Info:\n";
echo "- ID: " . $student['id'] . "\n";
echo "- Student ID: " . $student['student_id'] . "\n";
echo "- Name: " . $student['first_name'] . " " . $student['last_name'] . "\n";
echo "- Class ID: " . $student['class_id'] . "\n";
echo "- Roll: " . $student['roll_number'] . "\n";

// Check student's subjects
$stmt = $pdo->prepare("
    SELECT 
        s.id,
        s.name,
        s.code,
        ss.is_fourth_subject,
        ss.created_at
    FROM student_subjects ss
    JOIN subjects s ON ss.subject_id = s.id
    WHERE ss.student_id = ?
    ORDER BY s.name
");
$stmt->execute([$student_id]);
$subjects = $stmt->fetchAll();

echo "\nStudent's Subjects:\n";
if (empty($subjects)) {
    echo "No subjects found for this student!\n";
} else {
    foreach ($subjects as $subject) {
        echo "- " . $subject['name'] . " (" . $subject['code'] . ")";
        if ($subject['is_fourth_subject']) {
            echo " [FOURTH SUBJECT]";
        }
        echo "\n";
    }
}

// Check if there are any issues with the data
echo "\nData Integrity Check:\n";
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM student_subjects WHERE student_id = ?");
$stmt->execute([$student_id]);
$count = $stmt->fetch()['count'];
echo "Total subjects in student_subjects table for this student: $count\n";

// Check for any NULL values in required columns
$stmt = $pdo->prepare("SELECT * FROM student_subjects WHERE student_id = ? AND (subject_id IS NULL OR student_id IS NULL)");
$stmt->execute([$student_id]);
$nullRecords = $stmt->fetchAll();
echo "Records with NULL values: " . count($nullRecords) . "\n";

echo "\nTest completed.\n";
?>