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

// Look for students with names containing "alamgir" or "hossain"
$stmt = $pdo->prepare("
    SELECT s.*, u.username, u.status as user_status, c.name as class_name
    FROM students s 
    JOIN users u ON s.user_id = u.id 
    LEFT JOIN classes c ON s.class_id = c.id
    WHERE s.first_name LIKE ? OR s.last_name LIKE ? OR 
          s.first_name LIKE ? OR s.last_name LIKE ?
");
$stmt->execute(['%alamgir%', '%alamgir%', '%hossain%', '%hossain%']);

$results = $stmt->fetchAll();

echo "Found " . count($results) . " students with 'alamgir' or 'hossain' in their name:\n\n";

foreach ($results as $row) {
    echo "Student ID: " . $row['id'] . "\n";
    echo "Full Name: " . $row['first_name'] . " " . $row['last_name'] . "\n";
    echo "Class: " . ($row['class_name'] ?? 'N/A') . " (ID: " . $row['class_id'] . ")\n";
    echo "Student ID Number: " . $row['student_id'] . "\n";
    echo "Username: " . $row['username'] . "\n";
    echo "User Status: " . $row['user_status'] . "\n";
    echo "Student Status: " . $row['status'] . "\n";
    echo "---\n";
}

// Also check for class ONE students specifically
$stmt = $pdo->prepare("
    SELECT s.*, u.username, u.status as user_status
    FROM students s 
    JOIN users u ON s.user_id = u.id 
    WHERE s.class_id = 1
");
$stmt->execute();

$results = $stmt->fetchAll();

echo "\nTotal students in class ONE: " . count($results) . "\n";
?>