<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;
        }
    </style>
</head>
<?php
// Database connection
$host = 'localhost';
$dbname = 'cbt';
$username = 'root';
$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);

    // Fetch all question levels from the question_log table
    $fetchQuestions = "SELECT question_level FROM question_log";
    $questionsStmt = $pdo->query($fetchQuestions);
    $questionLevels = $questionsStmt->fetchAll(PDO::FETCH_ASSOC);

    // Fetch all levels from the table_setter table
    $fetchLevels = "SELECT id, level FROM table_setter";
    $levelsStmt = $pdo->query($fetchLevels);
    $levels = $levelsStmt->fetchAll(PDO::FETCH_ASSOC);

    // If toggle button is clicked to update the question_level and level_score
    if (isset($_POST['update'])) {
        $selectedQuestionLevel = $_POST['question_level'];
        $selectedLevelId = $_POST['level'];

        // Begin transaction
        $pdo->beginTransaction();

        // Update the corresponding level_question and level_score in the table_setter
        $updateQuery = "
            UPDATE table_setter 
            SET level_question = :question_level, level_score = CONCAT(:question_level, '_score'), status = 'active'
            WHERE id = :level_id
        ";

        $stmt = $pdo->prepare($updateQuery);
        $stmt->execute([
            ':question_level' => $selectedQuestionLevel,
            ':level_id' => $selectedLevelId
        ]);

        // Update the students table with the same question_level and level_score
        $updateStudentsQuery = "
            UPDATE students
            SET question_level = :question_level, question_level_score = CONCAT(:question_level, '_score')
            WHERE level = (
                SELECT level FROM table_setter WHERE id = :level_id
            )
        ";

        $studentsStmt = $pdo->prepare($updateStudentsQuery);
        $studentsStmt->execute([
            ':question_level' => $selectedQuestionLevel,
            ':level_id' => $selectedLevelId
        ]);

        // Commit the transaction
        $pdo->commit();

        echo "Question Assigned successfully!";
    }
} catch (PDOException $e) {
    // Rollback the transaction if there's an error
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Error: " . $e->getMessage();
}
?>

<!-- HTML Form -->
<form action="" method="post">
    <!-- Dropdown to select question level from question_log table -->
    <label for="question_level">Select Question Level:</label>
    <select name="question_level" id="question_level" required>
        <?php foreach ($questionLevels as $question) { ?>
            <option value="<?= $question['question_level'] ?>"><?= $question['question_level'] ?></option>
        <?php } ?>
    </select><br>

    <!-- Dropdown to select level from table_setter table -->
    <label for="level">Select Level:</label>
    <select name="level" id="level" required>
        <?php foreach ($levels as $level) { ?>
            <option value="<?= $level['id'] ?>"><?= $level['level'] ?></option>
        <?php } ?>
    </select><br>

    <!-- Toggle button to update question_level and level_score -->
    <input type="submit" name="update" value="ASSIGN QUESTION TO THIS LEVEL">
</form>
