<?php
// Start session and include DB connection
chdir('../');
session_start();
require_once('public/connection.php');

if (!isset($_SESSION['staff_id'])) {
    header("Location: ../index.php");
    exit;
}

// Optional: You can filter by class_id, term, session, or student_id from GET/POST
$class_id = $_GET['class_id'] ?? null;
$term = $_GET['term'] ?? null;
$session = $_GET['session'] ?? null;
$student_id = $_GET['student_id'] ?? null;

// Base SQL
$sql = "
    SELECT 
        er.id AS result_id,
        er.student_id,
        s.firstname,
        s.lastname,
        c.classes AS class_name,
        subj.subjects AS subject_name,
        e.title AS exam_title,
        e.exam_date,
        e.term,
        e.session,
        er.mcq_score,
        er.theory_score,
        er.total_score,
        er.status
    FROM 
        exam_results er
    JOIN exams e ON er.exam_id = e.id
    JOIN classes c ON e.class = c.id
    JOIN school_subjects subj ON e.subject_id = subj.id
    JOIN students s ON er.student_id = s.id
    WHERE er.status != 'pending'
";

// Apply filters
$params = [];
$types = "";

if ($class_id) {
    $sql .= " AND c.id = ?";
    $params[] = $class_id;
    $types .= "i";
}
if ($term) {
    $sql .= " AND e.term = ?";
    $params[] = $term;
    $types .= "s";
}
if ($session) {
    $sql .= " AND e.session = ?";
    $params[] = $session;
    $types .= "s";
}
if ($student_id) {
    $sql .= " AND s.id = ?";
    $params[] = $student_id;
    $types .= "i";
}

$sql .= " ORDER BY e.exam_date DESC";

// Prepare and execute
$stmt = $conn->prepare($sql);

if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$results = $stmt->get_result();
?>

<!-- HTML part to show the result (example) -->
<div class="container mt-4">
    <h4>Student Exam Results</h4>
    <?php if ($results->num_rows > 0): ?>
        <table class="table table-bordered table-striped">
            <thead>
                <tr>
                    <th>Student</th>
                    <th>Class</th>
                    <th>Subject</th>
                    <th>Exam Title</th>
                    <th>Term</th>
                    <th>Session</th>
                    <th>MCQ</th>
                    <th>Theory</th>
                    <th>Total</th>
                    <th>Status</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $results->fetch_assoc()): ?>
                    <tr>
                        <td><?= htmlspecialchars($row['firstname'] . ' ' . $row['lastname']) ?></td>
                        <td><?= htmlspecialchars($row['class_name']) ?></td>
                        <td><?= htmlspecialchars($row['subject_name']) ?></td>
                        <td><?= htmlspecialchars($row['exam_title']) ?></td>
                        <td><?= htmlspecialchars($row['term']) ?></td>
                        <td><?= htmlspecialchars($row['session']) ?></td>
                        <td><?= $row['mcq_score'] ?></td>
                        <td><?= $row['theory_score'] ?></td>
                        <td><?= $row['total_score'] ?></td>
                        <td><?= htmlspecialchars($row['status']) ?></td>
                    </tr>
                <?php endwhile; ?>
            </tbody>
        </table>
    <?php else: ?>
        <div class="alert alert-warning">No results found for the selected filters.</div>
    <?php endif; ?>
</div>
<?php include('partials/footer.php'); ?>
