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

// Look for a student named "Alamgir Hossain" in class ONE
$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.first_name LIKE ? AND s.last_name LIKE ?) 
    AND s.class_id = 1
");
$search_first = '%Alamgir%';
$search_last = '%Hossain%';
$stmt->execute([$search_first, $search_last]);

$results = $stmt->fetchAll();

echo "Found " . count($results) . " students named Alamgir Hossain in class ONE:\n\n";

foreach ($results as $row) {
    echo "Student ID: " . $row['id'] . "\n";
    echo "Full Name: " . $row['first_name'] . " " . $row['last_name'] . "\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 if there might be a student with this name in any class
$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 ? AND s.last_name LIKE ?) OR
    CONCAT(s.first_name, ' ', s.last_name) LIKE ?
");
$stmt->execute([$search_first, $search_last, '%Alamgir Hossain%']);

$results = $stmt->fetchAll();

echo "\nFound " . count($results) . " students named Alamgir Hossain in any class:\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";
}
?>