<?php
include("includes/db.php");

if (isset($_POST['exam_type'])) {
    $examTypeId = $conn->real_escape_string($_POST['exam_type']);

    // Updated query with classes.class_id in SELECT list
    $subjectQuery = "
        SELECT DISTINCT 
            classes.class_id, 
            classes.class_name, 
            classes.section, 
            subjects.subject_code, 
            subjects.subject_name
        FROM classes
        INNER JOIN marks ON classes.class_id = marks.class_id
        INNER JOIN subjects ON marks.subject_id = subjects.id
        WHERE marks.exam_type = '$examTypeId' AND classes.status = 'on'
        ORDER BY classes.class_id, subjects.subject_code;
    ";

    $subjectResult = $conn->query($subjectQuery);

    if ($subjectResult === false) {
        echo "<p>Error executing query: " . $conn->error . "</p>";
        exit();
    }

    if ($subjectResult->num_rows > 0) {
        $currentClass = "";
        $currentSection = "";
        $output = '';

        while ($row = $subjectResult->fetch_assoc()) {
            $className = htmlspecialchars($row['class_name']);
            $section = htmlspecialchars($row['section']);
            $subjectCode = htmlspecialchars($row['subject_code']);
            $subjectName = htmlspecialchars($row['subject_name']);

            if ($currentClass != $className || $currentSection != $section) {
                if ($currentClass != "" && $currentSection != "") {
                    $output .= "</tbody></table>";
                }

                $output .= "<h2>$className - $section</h2>";
                $output .= "
                    <table class='table table-striped table-bordered table-hover'>
                        <thead class='thead-dark'>
                            <tr>
                                <th>Subject Code</th>
                                <th>Subject Name</th>
                            </tr>
                        </thead>
                        <tbody>
                ";
                $currentClass = $className;
                $currentSection = $section;
            }

            $output .= "
                <tr>
                    <td>$subjectCode</td>
                    <td>$subjectName</td>
                </tr>
            ";
        }

        $output .= "</tbody></table>";
        echo $output;
    } else {
        echo "<p>No data found for this exam type.</p>";
    }
} else {
    echo "<p>Invalid request.</p>";
}
?>
