<?php
include '../../includes/header.php';
include '../../includes/db_connection.php';

if ($_SESSION['role'] == 'Student') {
    $student_id = $_SESSION['related_id'];
} else {
    $student_id = isset($_GET['student_id']) ? $_GET['student_id'] : null;
}

if (!$student_id) {
    echo "<p style='color:red;'>No student ID provided.</p>";
    echo "</div></body></html>";
    exit();
}

$student_sql = "SELECT first_name, last_name FROM students WHERE student_id = ?";
$student_stmt = $conn->prepare($student_sql);
$student_stmt->bind_param("i", $student_id);
$student_stmt->execute();
$student_result = $student_stmt->get_result();
$student_data = $student_result->fetch_assoc();
$student_stmt->close();

if (!$student_data) {
    echo "<p style='color:red;'>Student not found.</p>";
    echo "</div></body></html>";
    exit();
}

$results_sql = "SELECT subj.subject_name, er.score, er.exam_date
                FROM exam_results er
                JOIN subjects subj ON er.subject_id = subj.subject_id
                WHERE er.student_id = ?
                ORDER BY er.exam_date DESC";
$results_stmt = $conn->prepare($results_sql);
$results_stmt->bind_param("i", $student_id);
$results_stmt->execute();
$results_result = $results_stmt->get_result();
$results_stmt->close();
?>

<div class="container">
    <h2>Student Report: <?php echo htmlspecialchars($student_data['first_name'] . " " . $student_data['last_name']); ?></h2>
    
    <h3>Exam Results</h3>
    <?php
    if ($results_result->num_rows > 0) {
        echo "<table>";
        echo "<tr><th>Subject</th><th>Score</th><th>Exam Date</th></tr>";
        while($row = $results_result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['subject_name']) . "</td>";
            echo "<td>" . htmlspecialchars($row['score']) . "</td>";
            echo "<td>" . htmlspecialchars($row['exam_date']) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p>No exam results found for this student.</p>";
    }
    ?>
</div>

<?php
$conn->close();
?>
</div>
</body>
</html>