<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
include 'connection/config.php';
$pdo = new PDO('mysql:host=localhost;dbname=cbt', 'root', '');

// Initialize question levels array
$questionLevels = [];

// Fetch question levels from question_log
$stmt = $pdo->query("SELECT DISTINCT question_level FROM question_log");
$questionLevels = $stmt->fetchAll(PDO::FETCH_COLUMN);

// Check if delete request is made
if (isset($_POST['delete'])) {
    $questionLevel = $_POST['question_level'];

    // Confirm deletion
    echo "<script>
        if(confirm('Are you sure you want to delete this Question?')) {
            window.location.href = 'delete_question.php?question_level=" . htmlspecialchars($questionLevel) . "';
        } else {
            window.location.href = 'admincontrolcenter2.php?page=delete_question';
        }
    </script>";
    exit; // Stop further processing
}

// Perform deletion if question_level is set in the URL
if (isset($_GET['question_level'])) {
    $questionLevel = $_GET['question_level'];

    // Prepare to delete the dynamically created table
    $tableName = $questionLevel; // Assuming table names are same as question_level
    $scoreTableName = $questionLevel . 'score'; // Assuming score table has this naming convention

    // Check if the concatenated table is empty before deleting
    $checkTableStmt = $pdo->query("SELECT COUNT(*) FROM `$scoreTableName`");
    $rowCount = $checkTableStmt->fetchColumn();

    if ($rowCount > 0) {
        echo "<script>alert('Deletion denied: Previous Candidates Score Exist For this Question.');</script>";
    } else {
        try {
            // Delete the dynamically created question table
            $pdo->exec("DROP TABLE IF EXISTS `$tableName`");

            // Delete the corresponding row in the question_log table
            $stmt = $pdo->prepare("DELETE FROM question_log WHERE question_level = ?");
            $stmt->execute([$questionLevel]);

            // Delete the corresponding score table
            $pdo->exec("DROP TABLE IF EXISTS `$scoreTableName`");

            // Optionally: set the column where the table name exists in students table
            $stmt = $pdo->prepare("UPDATE students SET question_level = NULL, question_level_score = NULL WHERE question_level = ?");
            $stmt->execute([$tableName]);

            echo "<script>alert('Question and associated records deleted successfully!');</script>";
            header("location:admincontrolcenter2.php?page=delete_question");
            exit; // Stop further processing after redirect
        } catch (PDOException $e) {
            echo "Error deleting records: " . $e->getMessage();
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Delete Question</title>
</head>
<body>

<h2>Delete Question</h2>

<!-- Delete Form -->
<form method="POST">
    <label for="question_level">Select Question Level:</label>
    <select name="question_level" id="question_level" required>
        <option value="">-- Select Question Level --</option>
        <?php foreach ($questionLevels as $level): ?>
            <option value="<?php echo htmlspecialchars($level); ?>"><?php echo htmlspecialchars($level); ?></option>
        <?php endforeach; ?>
    </select>
    <input type="submit" name="delete">
</form>

</body>
</html>
