<?php
include('config.php');
if ($conn->connect_error) die("Connection failed");

$class = $_GET['class'];
$term = $_GET['term'];

$sql = $conn->prepare("
    SELECT 
        s.name,
        s.Admission_num,
        SUM(IFNULL(mt.total, 0) + et.ca_test + et.exam_score) AS total_score,
        COUNT(DISTINCT et.subject) AS subject_count
    FROM end_term_report et
    INNER JOIN students s ON s.Admission_num = et.Admission_no
    LEFT JOIN (
        SELECT Admission_no, subject, total FROM mid_term_test 
        WHERE term = ? AND classes = ?
    ) mt ON mt.Admission_no = et.Admission_no AND mt.subject = et.subject
    WHERE et.classes = ? AND et.term = ?
    GROUP BY et.Admission_no
    ORDER BY total_score DESC
    LIMIT 3
");

$sql->bind_param("ssss", $term, $class, $class, $term);
$sql->execute();
$result = $sql->get_result();

echo "<h3>Top 3 Students in $class - $term</h3>";
echo "<table>
        <tr><th>Position</th><th>Name</th><th>Admission No</th><th>Total</th><th>Percentage</th></tr>";

$position = 1;
while ($row = $result->fetch_assoc()) {
    $percent = $row['subject_count'] > 0 ? round($row['total_score'] / ($row['subject_count'] * 100) * 100, 2) : 0;
    echo "<tr>
            <td>$position</td>
            <td>{$row['name']}</td>
            <td>{$row['Admission_num']}</td>
            <td>{$row['total_score']}</td>
            <td>{$percent}%</td>
          </tr>";
    $position++;
}
echo "</table>";
