<?php
require_once 'db.php';

$class = $_GET['class_grade'] ?? '';

if ($class) {
    $sql = "SELECT s.unique_reg_number, s.full_name
            FROM students s
            WHERE s.class_grade = ?
            AND NOT EXISTS (
                SELECT 1 
                FROM student_assignments sa
                WHERE sa.unique_reg_number = s.unique_reg_number
                AND sa.status = 'assigned'
            )
            ORDER BY s.full_name ASC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $class);
    $stmt->execute();
    $res = $stmt->get_result();

    if ($res->num_rows > 0) {
        echo "<option value=''>Choose Student...</option>";
        while ($row = $res->fetch_assoc()) {
            echo "<option value='".htmlspecialchars($row['unique_reg_number'])."'>"
                 .htmlspecialchars($row['full_name'])." (".htmlspecialchars($row['unique_reg_number']).")</option>";
        }
    } else {
        echo "<option value=''>No available students</option>";
    }

    $stmt->close();
} else {
    echo "<option value=''>Invalid request</option>";
}
