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

$search_term = 'alamgir hossain';
echo "Searching for student: " . $search_term . "\n";

// Search for users with matching username, email or name
$stmt = $pdo->prepare("
    SELECT u.*, s.first_name, s.last_name, s.class_id 
    FROM users u 
    LEFT JOIN students s ON u.id = s.user_id 
    WHERE (u.username LIKE ? OR u.email LIKE ? OR 
          (s.first_name LIKE ? AND s.last_name LIKE ?) OR
          CONCAT(s.first_name, ' ', s.last_name) LIKE ?)
");
$search_param = '%' . $search_term . '%';
$first_name = '%alamgir%';
$last_name = '%hossain%';
$stmt->execute([$search_param, $search_param, $first_name, $last_name, $search_param]);

$results = $stmt->fetchAll();

echo "Found " . count($results) . " matching records:\n";

foreach ($results as $row) {
    echo "User ID: " . $row['id'] . 
         ", Username: " . $row['username'] . 
         ", Role: " . $row['role'] . 
         ", Name: " . ($row['first_name'] ?? '') . " " . ($row['last_name'] ?? '') . 
         ", Class ID: " . ($row['class_id'] ?? 'N/A') . 
         ", Status: " . $row['status'] . "\n";
}

// Also check directly in students table
echo "\nDirect search in students table:\n";
$stmt = $pdo->prepare("
    SELECT * FROM students 
    WHERE (first_name LIKE ? AND last_name LIKE ?) OR
          CONCAT(first_name, ' ', last_name) LIKE ?
");
$stmt->execute([$first_name, $last_name, $search_param]);

$student_results = $stmt->fetchAll();
echo "Found " . count($student_results) . " matching student records:\n";

foreach ($student_results as $row) {
    echo "Student ID: " . $row['id'] . 
         ", Student ID: " . $row['student_id'] . 
         ", Name: " . $row['first_name'] . " " . $row['last_name'] . 
         ", Class ID: " . $row['class_id'] . 
         ", User ID: " . $row['user_id'] . 
         ", Status: " . $row['status'] . "\n";
}

// Check if there's a specific student in class ONE
echo "\nChecking for students in class ONE (ID 1):\n";
$stmt = $pdo->prepare("
    SELECT * FROM students 
    WHERE ((first_name LIKE ? AND last_name LIKE ?) OR
           CONCAT(first_name, ' ', last_name) LIKE ?) AND
           class_id = 1
");
$stmt->execute([$first_name, $last_name, $search_param]);

$class_one_results = $stmt->fetchAll();
echo "Found " . count($class_one_results) . " matching student records in class ONE:\n";

foreach ($class_one_results as $row) {
    echo "Student ID: " . $row['id'] . 
         ", Student ID: " . $row['student_id'] . 
         ", Name: " . $row['first_name'] . " " . $row['last_name'] . 
         ", User ID: " . $row['user_id'] . 
         ", Status: " . $row['status'] . "\n";
}
?>