<?php
include('config.php');


if ($conn->connect_error) die("Connection failed");

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

// Fetch student and academic scores
$sql = $conn->prepare("
    SELECT 
        s.name AS student_name,
        e.subject,
        e.ca_test,
        e.exam_score,
        e.Teacher,
        IFNULL(m.total, 0) AS mid_term_total
    FROM end_term_report e
    INNER JOIN students s ON e.Admission_no = s.Admission_num
    LEFT JOIN (
        SELECT subject, total, Admission_no
        FROM mid_term_test 
        WHERE term = ? AND classes = ? AND Admission_no = ?
    ) m ON m.Admission_no = e.Admission_no AND m.subject = e.subject
    WHERE 
        e.classes = ? AND e.term = ? AND e.Admission_no = ?
");
$sql->bind_param("ssssss", $term, $class, $adm, $class, $term, $adm);
$sql->execute();
$result = $sql->get_result();

$student_name = '';
$grand_total = 0;
$total_subjects = 0;

if ($result->num_rows > 0) {
    $first = $result->fetch_assoc();
    $student_name = $first['student_name'];

    echo "<h3>Report for: <strong>$student_name</strong> (Admission No: $adm)</h3>";
    echo "<table><tr>
            <th>Subject</th>
            <th>Mid Term Total</th>
            <th>End Term CA Test</th>
            <th>CA Score (Mid + End)</th>
            <th>Exam Score</th>
            <th>Total</th>
            <th>Teacher</th>
          </tr>";

    // First row
    $mid = $first['mid_term_total'];
    $ca = $first['ca_test'];
    $ca_score = $mid + $ca;
    $exam = $first['exam_score'];
    $total = $ca_score + $exam;
    $grand_total += $total;
    $total_subjects++;

    echo "<tr>
            <td>{$first['subject']}</td>
            <td>$mid</td>
            <td>$ca</td>
            <td>$ca_score</td>
            <td>$exam</td>
            <td>$total</td>
            <td>{$first['Teacher']}</td>
          </tr>";

    // Remaining rows
    while ($row = $result->fetch_assoc()) {
        $mid = $row['mid_term_total'];
        $ca = $row['ca_test'];
        $ca_score = $mid + $ca;
        $exam = $row['exam_score'];
        $total = $ca_score + $exam;
        $grand_total += $total;
        $total_subjects++;

        echo "<tr>
                <td>{$row['subject']}</td>
                <td>$mid</td>
                <td>$ca</td>
                <td>$ca_score</td>
                <td>$exam</td>
                <td>$total</td>
                <td>{$row['Teacher']}</td>
              </tr>";
    }

    echo "</table>";

    $percentage = $total_subjects > 0 ? round($grand_total / ($total_subjects * 100) * 100, 2) : 0;

    echo "<h4>Total Score Across Subjects: <strong>$grand_total</strong></h4>";
    echo "<h4>Percentage: <strong>$percentage%</strong></h4>";

} else {
    echo "<p>No report found for this selection.</p>";
}
