<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 20px;
        }

        form {
            background-color: #ffffff;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            max-width: 600px;
            margin: auto;
        }

        label {
            display: block;
            margin: 10px 0 5px;
        }

        input[type="text"], input[type="file"], input[type="submit"], select {
            width: 100%;
            padding: 10px;
            margin: 5px 0 15px;
            border: 1px solid #cccccc;
            border-radius: 4px;
        }

        input[type="submit"] {
            background-color: #007BFF;
            color: white;
            border: none;
            cursor: pointer;
        }

        input[type="submit"]:hover, {
            background-color: #0056b3;
        }
		        input[type="sumit"] {
            background-color: #0056b3;
			width: 100%;
            padding: 10px;
            margin: 5px 0 15px;
            border: 1px solid #cccccc;
            border-radius: 4px;
			color:#FFFFFF;
        }
    </style>
</head>
<marquee behavior="alternate"><font color="#FF0000">Please do not Re-Authorize a level that has previously attempted assigned Question as this can render the score Invalid</font></marquee>
<?php
// Database connection details
$host = 'localhost';
$dbname = 'cbt'; // Replace with your actual database name
$username = 'root'; // Replace with your database username
$password = ''; // Replace with your database password

try {
    // Create a new PDO instance
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Check if the form is submitted
    if (isset($_POST['update_status'])) {
        $selectedLevel = $_POST['level'];

        // Update the status of students with the selected level
        $updateQuery = "UPDATE students SET status = :status WHERE level = :level";
        $stmt = $pdo->prepare($updateQuery);
        $stmt->execute([
            ':status' => '',
            ':level' => $selectedLevel
        ]);

        // Confirm update
        echo "Status updated to 'accept' for all students in level: " . htmlspecialchars($selectedLevel);
    }

    // Fetch levels for dropdown (you can modify this query based on your levels)
    $levelsQuery = "SELECT DISTINCT level FROM students";
    $levelsStmt = $pdo->query($levelsQuery);

    
} catch (PDOException $e) {
    // Handle any errors
    echo "Error: " . $e->getMessage();
}
?>

<!-- HTML Form for selecting level -->
<form action="" method="post">
    <label for="level">Select Level:</label>
    <select name="level" id="level" required>
        <option value="">--Select Level--</option>
       	<option>NDI</option>
		<option>NDII</option>
		<option>HNDI</option>
		<option>HNDII</option>
    </select>
    <input type="submit" name="update_status" value="ALLOW CLASS TO START EXAM">
</form>

<input type="sumit" align="middle" value="Use this section to Authorize Only One Candidate at a time | USE THE ABOVE SECTION TO AUTHORIZE A WHOLE CLASS" />

<?php
// Database connection settings
$host = 'localhost'; // Replace with your database host
$dbname = 'cbt'; // Replace with your database name
$username = 'root'; // Replace with your database username
$password = ''; // Replace with your database password

// Establish a PDO connection

try {
    // Database connection
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if (isset($_POST['authorize'])) {
        // Get matric from the form
        $matric2 = $_POST['matric2'];

        // First, fetch the `question_level_score` table name from the `students` table
        $query = "SELECT question_level FROM students WHERE matric = :matric";
        $stmt = $pdo->prepare($query);
        $stmt->bindParam(':matric', $matric2);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($result) {
            $scoreTable = $result['question_level']."score";

            // Check if the table exists in the database
            $tableExistsQuery = "SHOW TABLES LIKE :table";
            $tableExistsStmt = $pdo->prepare($tableExistsQuery);
            $tableExistsStmt->bindParam(':table', $scoreTable);
            $tableExistsStmt->execute();

            if ($tableExistsStmt->rowCount() > 0) {
                // Check if the student has a score record in the fetched `scoreTable`
                $checkScoreQuery = "SELECT * FROM `$scoreTable` WHERE matric = :matric";
                $checkStmt = $pdo->prepare($checkScoreQuery);
                $checkStmt->bindParam(':matric', $matric2);
                $checkStmt->execute();
                $scoreExists = $checkStmt->fetch(PDO::FETCH_ASSOC);

                if ($scoreExists) {
                    // If the score exists, delete the record
                    $deleteScoreQuery = "DELETE FROM `$scoreTable` WHERE matric = :matric";
                    $deleteStmt = $pdo->prepare($deleteScoreQuery);
                    $deleteStmt->bindParam(':matric', $matric2);
                    $deleteStmt->execute();
                    echo "Previous score deleted successfully.<br>";
                } else {
                    echo "No previous score found.<br>";
                }

                // After handling the score, update the student's status
                $updateStatusQuery = "UPDATE students SET status = '' WHERE matric = :matric";
                $updateStatusStmt = $pdo->prepare($updateStatusQuery);
                $updateStatusStmt->bindParam(':matric', $matric2);

                if ($updateStatusStmt->execute()) {
                    echo "Student record authorized and status updated successfully.";
                } else {
                    echo "An error occurred while updating the student's status.";
                }
            } else {
                echo "The score table '$scoreTable' does not exist.";
            }
        } else {
            echo "No student found with the given Matric Number.";
        }
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

<!-- HTML Form -->
<form method="POST" action="">
    <label for="matric">Matric Number:</label>
    <input type="text" id="matric2" name="matric2" required>
    <button type="submit" name="authorize">Authorize</button>
</form>
