<?php
session_start();
require_once 'config/database.php';

// Get all classes
$stmt = $pdo->query("SELECT * FROM classes WHERE status = 'active' ORDER BY name");
$classes = $stmt->fetchAll();

// Get all subjects
$stmt = $pdo->query("SELECT * FROM subjects WHERE status = 'active' ORDER BY name");
$subjects = $stmt->fetchAll();

// Get class 10 specifically
$stmt = $pdo->prepare("SELECT * FROM classes WHERE name = 'Ten' AND status = 'active'");
$stmt->execute();
$class_10 = $stmt->fetch();

echo "<h2>All Classes</h2>";
echo "<ul>";
foreach ($classes as $class) {
    echo "<li>" . $class['name'] . " " . $class['section'] . " (ID: " . $class['id'] . ")</li>";
}
echo "</ul>";

echo "<h2>All Subjects</h2>";
echo "<ul>";
foreach ($subjects as $subject) {
    echo "<li>" . $subject['name'] . " (" . $subject['code'] . ") (ID: " . $subject['id'] . ")</li>";
}
echo "</ul>";

if ($class_10) {
    echo "<h2>Class 10 Details</h2>";
    echo "<p>Name: " . $class_10['name'] . "</p>";
    echo "<p>ID: " . $class_10['id'] . "</p>";
    
    // Get subjects assigned to class 10
    $stmt = $pdo->prepare("
        SELECT s.* 
        FROM subjects s 
        JOIN class_subjects cs ON s.id = cs.subject_id 
        WHERE cs.class_id = ? AND s.status = 'active' 
        ORDER BY s.name
    ");
    $stmt->execute([$class_10['id']]);
    $class_10_subjects = $stmt->fetchAll();
    
    echo "<h3>Class 10 Subjects (" . count($class_10_subjects) . " found)</h3>";
    echo "<ul>";
    foreach ($class_10_subjects as $subject) {
        echo "<li>" . $subject['name'] . " (" . $subject['code'] . ") (ID: " . $subject['id'] . ")</li>";
    }
    echo "</ul>";
    
    // Get all students in class 10 and their subjects
    $stmt = $pdo->prepare("
        SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number,
               COUNT(ss.subject_id) as subject_count
        FROM students s
        LEFT JOIN student_subjects ss ON s.id = ss.student_id
        WHERE s.class_id = ? AND s.status = 'active'
        GROUP BY s.id, s.student_id, s.first_name, s.last_name, s.roll_number
        ORDER BY s.roll_number
    ");
    $stmt->execute([$class_10['id']]);
    $class_10_students = $stmt->fetchAll();
    
    echo "<h3>Class 10 Students and Their Subject Counts</h3>";
    echo "<ul>";
    foreach ($class_10_students as $student) {
        echo "<li>" . $student['first_name'] . " " . $student['last_name'] . " (Roll: " . $student['roll_number'] . ") - " . $student['subject_count'] . " subjects</li>";
    }
    echo "</ul>";
} else {
    echo "<p>Class 10 not found</p>";
}
?>