<?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=british_schdb_coll", "british_smt_user", "@British22*#");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Fetch dynamic options for class, term, year, and subject 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);

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

// Initialize variables to store class, term, and year for display
$page_title = '';
$subject_name = '';
$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']);
    $subject = strip_tags($_POST['subject']);

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

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

    // Set subject name
    $subject_name = $subject;

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

// Handle form submission for updating scores
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['update_results'])) {
    // Extract data from the form
    $record_ids = $_POST['record_id'];
    $test1_scores = $_POST['test1'];
    $test2_scores = $_POST['test2'];
    $exam_scores = $_POST['exam'];

    // Update the records in the database
    $update_query = "UPDATE tbl_student_results SET test1 = ?, test2 = ?, exam = ?, total = (test1 + test2 + exam) WHERE id = ?";
    $stmt = $conn->prepare($update_query);

    foreach ($record_ids as $index => $record_id) {
        $test1 = $test1_scores[$index];
        $test2 = $test2_scores[$index];
        $exam = $exam_scores[$index];

        $stmt->execute([$test1, $test2, $exam, $record_id]);
    }

    if ($stmt->rowCount() > 0) {
        $success_message = "You have successfully updated scores for $subject - $class, $term, $year";
    } else {
        $error_message = "Error updating scores.";
    }
}
?>

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

<head>
    <!-- Your head section goes here -->
</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>Edit Subject Scores</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>

                                <label for="subject">Subject:</label>
                                <select id="subject" name="subject" required>
                                    <?php foreach ($subjects as $subject_option) : ?>
                                        <option value="<?php echo $subject_option; ?>" <?php echo ($subject_option === $subject) ? 'selected' : ''; ?>><?php echo $subject_option; ?></option>
                                    <?php endforeach; ?>
                                </select>

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

                        <div>
                            <p><?php echo "Subject Name: $subject_name"; ?></p>
                            <p><?php echo "Total Student Results Count: $total_results_count"; ?></p>
                        </div>

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

                        if (isset($score_records)) {
                            // Display editable records based on the search result
                            ?>
                            <form method="POST">
                                <table>
                                    <tr>
                                        <th>S/N</th>
                                        <th>Student Name</th>
                                        <th>Admission No.</th>
                                        <th>Cont. Ass.</th>
                                        <th>Project</th>
                                        <th>Exam</th>
                                        <th>Total</th>
                                        <th>Action</th>
                                    </tr>
                                    <?php
                                    foreach ($score_records as $index => $record) {
                                        ?>
                                        <tr>
                                            <td><?php echo $index + 1; ?></td>
                                            <td><?php echo $record['name']; ?></td>
                                            <td><?php echo $record['registration_no']; ?></td>
                                            <td><input type="text" name="test1[]" value="<?php echo $record['test1']; ?>" required></td>
                                            <td><input type="text" name="test2[]" value="<?php echo $record['test2']; ?>" required></td>
                                            <td><input type="text" name="exam[]" value="<?php echo $record['exam']; ?>" required></td>
                                            <td><?php echo $record['total']; ?></td>
                                            <td>
                                                <input type="hidden" name="record_id[]" value="<?php echo $record['id']; ?>">
                                                <a href="delete_result_subject.php?record_id=<?php echo $record['id']; ?>" onclick="return confirm('Are you sure you want to delete this record?')">Delete</a>
                                            </td>
                                        </tr>
                                        <?php
                                    }
                                    ?>
                                </table>
                                <button type="submit" name="update_results">Save Updated Result</button>
                            </form>
                            <?php
                        }
                        ?>

                        <?php
                        if (isset($success_message)) {
                            echo "<p style='color: green;'>$success_message</p>";
                        } elseif (isset($error_message)) {
                            echo "<p style='color: red;'>$error_message</p>";
                        }
                        ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Your footer goes here -->

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

</html>
