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

// Get search parameters
$name = isset($_GET['name']) ? trim($_GET['name']) : '';
$roll = isset($_GET['roll']) ? trim($_GET['roll']) : '';
$class = isset($_GET['class']) ? trim($_GET['class']) : '';
$session = isset($_GET['session']) ? trim($_GET['session']) : '';

// Build query
$conditions = [];
$params = [];

if ($name !== '') {
    $conditions[] = "(first_name LIKE :name1 OR last_name LIKE :name2)";
    $params[':name1'] = "%{$name}%";
    $params[':name2'] = "%{$name}%";
}

if ($roll !== '') {
    $conditions[] = "roll_number LIKE :roll";
    $params[':roll'] = "%{$roll}%";
}

if ($class !== '') {
    $conditions[] = "class_id = :class";
    $params[':class'] = (int)$class;
}

if ($session !== '') {
    $conditions[] = "session = :session";
    $params[':session'] = $session;
}

$baseQuery = "FROM students WHERE status = 'active'";
if (!empty($conditions)) {
    $baseQuery .= ' AND ' . implode(' AND ', $conditions);
}

// Count total students
$countSql = "SELECT COUNT(*) " . $baseQuery;
$countStmt = $pdo->prepare($countSql);
foreach ($params as $key => $value) {
    $countStmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$countStmt->execute();
$totalStudents = (int)$countStmt->fetchColumn();

// Get students with limit
$dataSql = "SELECT id, first_name, last_name, roll_number, class_id, session " .
    $baseQuery . " ORDER BY roll_number ASC LIMIT 20";

$stmt = $pdo->prepare($dataSql);
foreach ($params as $key => $value) {
    $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$stmt->execute();
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get classes for dropdown
$classes = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name ASC")->fetchAll(PDO::FETCH_ASSOC);

// Get sessions for dropdown
$sessions = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session DESC LIMIT 10")->fetchAll(PDO::FETCH_COLUMN);

?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student Search Test</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-4">
        <h2><i class="fas fa-search"></i> Student Search Test Page</h2>
        
        <!-- Search Form -->
        <div class="card mb-4">
            <div class="card-body">
                <h5 class="mb-3">Advanced Search Test</h5>
                <form method="GET" class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">Name</label>
                        <input type="text" name="name" class="form-control" value="<?php echo htmlspecialchars($name); ?>" placeholder="Student name">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Roll Number</label>
                        <input type="text" name="roll" class="form-control" value="<?php echo htmlspecialchars($roll); ?>" placeholder="Roll number">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">Class</label>
                        <select name="class" class="form-select">
                            <option value="">All Classes</option>
                            <?php foreach ($classes as $cls): ?>
                                <option value="<?php echo $cls['id']; ?>" <?php echo ($class !== '' && (int)$class === (int)$cls['id']) ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($cls['name'] . ' - ' . $cls['section']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Session</label>
                        <select name="session" class="form-select">
                            <option value="">All Sessions</option>
                            <?php foreach ($sessions as $sess): ?>
                                <option value="<?php echo $sess; ?>" <?php echo ($session !== '' && (string)$session === (string)$sess) ? 'selected' : ''; ?>><?php echo $sess; ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2 d-flex align-items-end">
                        <button type="submit" class="btn btn-primary w-100">
                            <i class="fas fa-search"></i> Search
                        </button>
                    </div>
                </form>
            </div>
        </div>

        <!-- Results -->
        <div class="card">
            <div class="card-header">
                <h5>Search Results</h5>
                <p class="mb-0">Total students found: <?php echo $totalStudents; ?></p>
            </div>
            <div class="card-body">
                <?php if (empty($students)): ?>
                    <div class="alert alert-info">
                        <i class="fas fa-info-circle"></i> No students found matching your criteria.
                    </div>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-striped">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>Name</th>
                                    <th>Roll Number</th>
                                    <th>Class</th>
                                    <th>Session</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($students as $student): ?>
                                    <tr>
                                        <td><?php echo $student['id']; ?></td>
                                        <td><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                        <td><?php echo htmlspecialchars($student['roll_number']); ?></td>
                                        <td>
                                            <?php 
                                            foreach ($classes as $cls) {
                                                if ($cls['id'] == $student['class_id']) {
                                                    echo htmlspecialchars($cls['name'] . ' - ' . $cls['section']);
                                                    break;
                                                }
                                            }
                                            ?>
                                        </td>
                                        <td><?php echo htmlspecialchars($student['session']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php endif; ?>
            </div>
        </div>

        <!-- Debug Info -->
        <div class="card mt-4">
            <div class="card-header">
                <h5>Debug Information</h5>
            </div>
            <div class="card-body">
                <h6>Search Parameters:</h6>
                <ul>
                    <li>Name: "<?php echo htmlspecialchars($name); ?>"</li>
                    <li>Roll: "<?php echo htmlspecialchars($roll); ?>"</li>
                    <li>Class ID: "<?php echo htmlspecialchars($class); ?>"</li>
                    <li>Session: "<?php echo htmlspecialchars($session); ?>"</li>
                </ul>
                
                <h6>Generated Query:</h6>
                <pre><?php echo htmlspecialchars($dataSql); ?></pre>
                
                <h6>Parameters:</h6>
                <pre><?php print_r($params); ?></pre>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>