<?php
//load_exam_types.php
include('../includes/db.php');

$fetchExamTypesQuery = "
    SELECT et.id, et.exam_type, et.exm_year, et.exm_year_ad, et.created_at,
           et.assigned_class, et.assigned_sections,
           c.class AS class_name
    FROM examination_type et
    LEFT JOIN classes c ON et.assigned_class = c.class_id
    ORDER BY et.exm_year DESC, et.id DESC
";

$result = $conn->query($fetchExamTypesQuery);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {

        echo '<tr class="exam-row">';
        echo '<td><strong>' . $row['id'] . '</strong></td>';
        echo '<td><span class="badge badge-primary">' . htmlspecialchars($row['exam_type']) . '</span></td>';
        echo '<td>' . $row['exm_year'] . '</td>';
        echo '<td>' . $row['exm_year_ad'] . '</td>';

        echo '<td>' . (!empty($row['class_name'])
            ? htmlspecialchars($row['class_name'])
            : '<span class="text-muted">N/A</span>') . '</td>';

        /* Sections */
        if (!empty($row['assigned_sections'])) {
            $sectionIds = explode(',', $row['assigned_sections']);
            $placeholders = implode(',', array_fill(0, count($sectionIds), '?'));

            $stmt = $conn->prepare(
                "SELECT section_name FROM sections WHERE section_id IN ($placeholders)"
            );
            $types = str_repeat("i", count($sectionIds));
            $stmt->bind_param($types, ...$sectionIds);
            $stmt->execute();
            $res = $stmt->get_result();

            $badges = '';
            while ($s = $res->fetch_assoc()) {
                $badges .= '<span class="badge-section">' . $s['section_name'] . '</span>';
            }
            $stmt->close();

            echo '<td>' . $badges . '</td>';
        } else {
            echo '<td><span class="text-muted">All Sections</span></td>';
        }

        echo '<td>'. $row['created_at'] .'</td>';

        echo '<td>
            <a href="edit_exam_type.php?id=' . $row['id'] . '" 
               class="btn btn-sm btn-outline-primary">
               <i class="fa fa-edit"></i> Edit
            </a>
        </td>';

        echo '</tr>';
    }
} else {
    echo '<tr>
            <td colspan="8" class="text-center text-muted">
                No exam types found
            </td>
          </tr>';
}
