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

echo "Checking for similar subject codes (potential duplicates):\n";
$stmt = $pdo->query("SELECT id, name, code FROM subjects WHERE status='active' ORDER BY code, name");
$allSubjects = $stmt->fetchAll();

$groups = [];
foreach ($allSubjects as $subject) {
    // Group by base code (before underscore)
    $baseCode = explode('_', $subject['code'])[0];
    if (!isset($groups[$baseCode])) {
        $groups[$baseCode] = [];
    }
    $groups[$baseCode][] = $subject;
}

// Show groups with multiple subjects
foreach ($groups as $baseCode => $subjects) {
    if (count($subjects) > 1) {
        echo "Potential duplicates with base code '$baseCode':\n";
        foreach ($subjects as $subject) {
            echo "  - ID: {$subject['id']}, Name: {$subject['name']}, Code: {$subject['code']}\n";
        }
        echo "\n";
    }
}

echo "\nChecking how subjects are loaded in student_filter.php logic:\n";

// Simulate what happens when no class is selected (fallback to all subjects)
echo "\n1. When NO class selected (fallback to all active subjects):\n";
echo "Total subjects: " . count($allSubjects) . "\n";

// Simulate what happens when a class is selected but no session/group (fallback to class mapping)
echo "\n2. When class selected but NO session/group (class_subjects mapping):\n";
// Let's check for class 10 (Ten) as an example
$stmt = $pdo->prepare("SELECT s.id, s.name, s.code
                       FROM class_subjects cs
                       JOIN subjects s ON cs.subject_id = s.id
                       WHERE cs.class_id = ? AND s.status='active'
                       ORDER BY s.name");
$stmt->execute([10]); // Class 10 = Ten
$classSubjects = $stmt->fetchAll();
echo "Class 10 subjects: " . count($classSubjects) . "\n";

echo "\n3. Checking student_subjects assignments:\n";
// Check what subjects students are actually assigned to
$stmt = $pdo->query("SELECT sub.id, sub.name, sub.code, COUNT(ss.student_id) as student_count
                     FROM student_subjects ss
                     JOIN subjects sub ON ss.subject_id = sub.id
                     GROUP BY sub.id, sub.name, sub.code
                     ORDER BY sub.code, sub.name");
$assignedSubjects = $stmt->fetchAll();

echo "Subjects with student assignments:\n";
foreach ($assignedSubjects as $subject) {
    echo "- ID: {$subject['id']}, Name: {$subject['name']} ({$subject['code']}) - Students: {$subject['student_count']}\n";
}
?>