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

// Check how group names are stored in the database
echo "Checking group names in students table for class 10, session 2025:\n";
$stmt = $pdo->prepare("SELECT DISTINCT group_name FROM students WHERE class_id = ? AND session = ? AND group_name IS NOT NULL");
$stmt->execute([10, '2025']);
$groups = $stmt->fetchAll(PDO::FETCH_COLUMN);

echo "Groups found:\n";
foreach ($groups as $group) {
    echo "- '" . $group . "' (length: " . strlen($group) . ")\n";
}

// Check for encoding issues
echo "\nChecking for encoding issues:\n";
foreach ($groups as $group) {
    echo "Group '$group':\n";
    echo "  - md5: " . md5($group) . "\n";
    echo "  - json encoded: " . json_encode($group) . "\n";
    echo "  - bin2hex: " . bin2hex($group) . "\n";
}

// Check what's in the student_groups table
echo "\nChecking student_groups table:\n";
$stmt = $pdo->query("SELECT group_name, group_code FROM student_groups WHERE status='active' ORDER BY group_name");
$group_defs = $stmt->fetchAll();
foreach ($group_defs as $group_def) {
    echo "- '{$group_def['group_name']}' ({$group_def['group_code']})\n";
}

// Check if there are students with exactly 'HUMANITIES' group name
echo "\nChecking students with exact group name 'HUMANITIES':\n";
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE class_id = ? AND session = ? AND group_name = ?");
$stmt->execute([10, '2025', 'HUMANITIES']);
$result = $stmt->fetch();
echo "Students with group_name = 'HUMANITIES': {$result['count']}\n";

// Check if there are students with COALESCE(group_name,'') = 'HUMANITIES'
echo "\nChecking students with COALESCE(group_name,'') = 'HUMANITIES':\n";
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE class_id = ? AND session = ? AND COALESCE(group_name,'') = ?");
$stmt->execute([10, '2025', 'HUMANITIES']);
$result = $stmt->fetch();
echo "Students with COALESCE(group_name,'') = 'HUMANITIES': {$result['count']}\n";
?>