<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');

// Create a database connection 
$conn = new PDO("mysql:host=localhost;dbname=vdacocmz_schdb_coll", "vdacocmz_mendohz", "@Dei22*#");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Fetch dynamic options for class, term, and year from the database
$query = "SELECT DISTINCT class FROM tbl_student_results";
$stmt = $conn->prepare($query);
$stmt->execute();
$classes = $stmt->fetchAll(PDO::FETCH_COLUMN);

$query = "SELECT DISTINCT term FROM tbl_student_results";
$stmt = $conn->prepare($query);
$stmt->execute();
$terms = $stmt->fetchAll(PDO::FETCH_COLUMN);

$query = "SELECT DISTINCT year FROM tbl_student_results";
$stmt = $conn->prepare($query);
$stmt->execute();
$years = $stmt->fetchAll(PDO::FETCH_COLUMN);

// Initialize variables to store class, term, and year for display
$page_title = '';
$total_results_count = 0;

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Extract search data from the form
    $class = strip_tags($_POST['class']);
    $term = strip_tags($_POST['term']);
    $year = strip_tags($_POST['year']);

    // Retrieve uploaded class results from the database based on the search criteria
    $query = "SELECT DISTINCT class, term, year, subject, csv_no FROM tbl_student_results WHERE class = ? AND term = ? AND year = ?";
    $stmt = $conn->prepare($query);
    $stmt->execute([$class, $term, $year]);
    $class_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Set page title
    $page_title = "Class: $class, Term: $term, Year: $year";

    // Count of total uploaded class results
    $total_results_count_query = "SELECT COUNT(DISTINCT csv_no) FROM tbl_student_results WHERE class = ? AND term = ? AND year = ?";
    $stmt = $conn->prepare($total_results_count_query);
    $stmt->execute([$class, $term, $year]);
    $total_results_count = $stmt->fetchColumn();
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <!-- Your head section goes here -->
    <style>
        body {
            display: flex;
            align-items: center;
            justify-content: center;
            height: auto;
            margin: 10;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }

        table, th, td {
            border: 1px solid #ddd;
        }

        th, td {
            padding: 10px;
            text-align: left;
        }
    </style>
</head>

<body class="fixed-nav sticky-footer bg-dark" id="page-top">
    <!-- Your navigation goes here -->

    <div class="content-wrapper">
        <div class="container-fluid">
            <!-- Breadcrumbs go here -->

            <div class="row">
                <div class="col-lg-12">
                    <center>
                        <h2>Uploaded Class Results</h2>
                    </center>

                    <div class="form-container">
                        <!-- Your search form goes here -->
                        <form method="POST">
                            <div>
                                <label for="class">Class:</label>
                                <select id="class" name="class" required>
                                    <?php foreach ($classes as $class_option) : ?>
                                        <option value="<?php echo $class_option; ?>" <?php echo ($class_option === $class) ? 'selected' : ''; ?>><?php echo $class_option; ?></option>
                                    <?php endforeach; ?>
                                </select>

                                <label for="term">Term:</label>
                                <select id="term" name="term" required>
                                    <?php foreach ($terms as $term_option) : ?>
                                        <option value="<?php echo $term_option; ?>" <?php echo ($term_option === $term) ? 'selected' : ''; ?>><?php echo $term_option; ?></option>
                                    <?php endforeach; ?>
                                </select>

                                <label for="year">Year:</label>
                                <select id="year" name="year" required>
                                    <?php foreach ($years as $year_option) : ?>
                                        <option value="<?php echo $year_option; ?>" <?php echo ($year_option === $year) ? 'selected' : ''; ?>><?php echo $year_option; ?></option>
                                    <?php endforeach; ?>
                                </select>

                                <button type="submit">Search</button>
                            </div>
                        </form>

                        <div>
                            <p><?php echo "Total Uploaded Class Results Count: $total_results_count"; ?></p>
                        </div>

                        <?php
                        if (!empty($page_title)) {
                            echo "<h3>$page_title</h3>";
                        }

                        if (isset($class_results)) {
                            // Display uploaded class results based on the search result
                            ?>
                            <table>
                                <tr>
                                    <th>S/N</th>
                                    <th>Class</th>
                                    <th>Term</th>
                                    <th>Year</th>
                                    <th>Subject</th>
                                    <th>CSV Number</th>
                                    <th>Action</th>
                                </tr>
                                <?php
                                $serial_number = 1;
                                foreach ($class_results as $record) {
                                    ?>
                                    <tr>
                                        <td><?php echo $serial_number++; ?></td>
                                        <td><?php echo $record['class']; ?></td>
                                        <td><?php echo $record['term']; ?></td>
                                        <td><?php echo $record['year']; ?></td>
                                        <td><?php echo $record['subject']; ?></td>
                                        <td><?php echo $record['csv_no']; ?></td>
                                        <td>
                                            <a href="edit_subject.php?class=<?php echo $record['class']; ?>&term=<?php echo $record['term']; ?>&year=<?php echo $record['year']; ?>&subject=<?php echo $record['subject']; ?>" target="_blank">Edit</a>
                                            |
                                            <a href="delete_class_result_subject.php?csv_no=<?php echo $record['csv_no']; ?>" onclick="return confirm('Are you sure you want to delete all records with CSV Number: <?php echo $record['csv_no']; ?>?')">Delete</a>
                                        </td>
                                    </tr>
                                    <?php
                                }
                                ?>
                            </table>
                            <?php
                        }
                        ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Your footer goes here -->

    <!-- Your scripts go here -->
</body>

</html>
