<?php
include "../backend/connection.php";
$configure = new DefaultDatabase();
$conf = $configure->connect();

$categories = mysqli_query($conf, "SELECT * FROM categories");
$sections = mysqli_query($conf, "SELECT * FROM grade_section");

?>
<div class="school-container">
    <h2>PARTICIPANTS RESULTS PERFORMANCE</h2>
    <div class="add-school">
        <form method="POST">
            <select id="categorySelect" name="result_category" required>
                <option value="" disabled selected>Select Category</option>
                <?php while($cat = mysqli_fetch_array($categories)): ?>
                    <option value="<?php echo htmlspecialchars($cat["category_name"]); ?>"><?php echo htmlspecialchars($cat["category_name"]); ?></option>
                <?php endwhile; ?>
            </select>
            <select id="sectionSelect" name="result_section" required>
                <option value="" disabled selected>Choose Grade Section</option>
                <?php while($sec = mysqli_fetch_array($sections)): ?>
                    <option value="<?php echo htmlspecialchars($sec["section_name"]); ?>"><?php echo htmlspecialchars($sec["section_name"]); ?></option>
                <?php endwhile; ?>
            </select>
            <button type="submit" name="check_results">See Results</button>
        </form>
    </div>
    <table id="schoolsTable">
        <thead>
            <tr>
                <th>Participant Name</th>
                <th>District</th>
                <th>School</th>
                <th>Category</th>
                <th>Points</th>
                <th>Position</th>
            </tr>
        </thead>
        <tbody>
            <?php
            // Check results specific
            if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST["check_results"])) {
                $result_category = mysqli_real_escape_string($conf, $_POST["result_category"]);
                $result_section = mysqli_real_escape_string($conf, $_POST["result_section"]);

                $query = "SELECT * FROM results WHERE category = '$result_category' AND grade_section = '$result_section' ORDER BY points DESC";
                $result = mysqli_query($conf, $query);

                if (!$result) {
                    echo mysqli_error($conf);
                } else {
                    $position = 1; // Start position from 1
                    while ($res = mysqli_fetch_array($result)) {
            ?>
            <tr>
                <td><?php echo htmlspecialchars($res["full_name"]); ?></td>
                <td><?php echo htmlspecialchars($res["district"]); ?></td>
                <td><?php echo htmlspecialchars($res["school"]); ?></td>
                <td><?php echo htmlspecialchars($res["category"]); ?></td>
                <td><?php echo htmlspecialchars($res["points"]); ?></td>
                <td><?php echo $position++; ?></td>
            </tr>
            <?php 
                    }
                }
            }
            ?>
        </tbody>
    </table>
</div>