<?php
require_once "db.php"; // <-- your mysqli connection in $conn

$ref = $_GET['ref'] ?? 'NEC204';  // example: student Reg No
$ref = trim($ref);

// ✅ Unit -> Module name mapping (edit names if needed)
$modules = [
  "Unit 01" => "Fundamentals of Computing and Digital Systems",
  "Unit 02" => "Office Productivity and Business Tools",
  "Unit 03" => "Computer Hardware and Systems Architecture",
  "Unit 04" => "Cybersecurity and Network Engineering",
  "Unit 05" => "Web Design and Front-End Development",
  "Unit 06" => "Internet and Cloud Technologies",
  "Unit 07" => "Multimedia and Interactive Technologies",
  "Unit 08" => "Introduction to Data Science and Artificial Intelligence",
  "Unit 09" => "Software Engineering Principles",
  "Unit 10" => "Programming with Python & Java",
  "Unit 11" => "Object-Oriented Programming with C#",
  "Unit 12" => "IT Project Management and Professional Practice",
];

// ✅ fetch latest FINAL per unit
$sql = "
SELECT r1.*
FROM results r1
JOIN (
    SELECT 
        TRIM(SUBSTRING_INDEX(module_name, '-', 1)) AS unit_key,
        MAX(date_taken) AS max_date
    FROM results
    WHERE reference_no = ?
      AND module_name LIKE '%Final Exam%'
    GROUP BY TRIM(SUBSTRING_INDEX(module_name, '-', 1))
) x
  ON TRIM(SUBSTRING_INDEX(r1.module_name, '-', 1)) = x.unit_key
 AND r1.date_taken = x.max_date
WHERE r1.reference_no = ?
  AND r1.module_name LIKE '%Final Exam%'
";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $ref, $ref);
$stmt->execute();
$res = $stmt->get_result();

// store results by unit (Unit 01, Unit 02...)
$finals = [];
while ($row = $res->fetch_assoc()) {
    $unit = trim(explode('-', $row['module_name'])[0]); // "Unit 01"
    $finals[$unit] = $row;
}
$stmt->close();

// helper
function e($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
?>

<table class="table table-bordered">
  <thead>
    <tr>
      <th>Module</th>
      <th>Evaluation Method</th>
      <th>Type</th>
      <th>Date Evaluated</th>
      <th>Grade</th>
      <th>Result</th>
      <th>Score</th>
    </tr>
  </thead>
  <tbody>
    <?php foreach ($modules as $unit => $moduleName): 
        $r = $finals[$unit] ?? null;
        $date  = $r ? $r['date_taken'] : '-';
        $grade = $r ? ($r['grade'] ?? '-') : '-';
        $status = $r ? ($r['status'] ?? '-') : '-';
        $score = $r ? ($r['score'] ?? '-') : '-';
    ?>
      <tr>
        <td><?= e($moduleName) ?></td>
        <td>Online Exam</td>
        <td>Final</td>
        <td><?= e($date) ?></td>
        <td><?= e($grade) ?></td>
        <td><?= e($status) ?></td>
        <td><?= e($score) ?></td>
      </tr>
    <?php endforeach; ?>
  </tbody>
</table>
