<?php
require_once("public/connection.php");
require_once("public/functions.php");

session_start();
if (!isset($_SESSION['staff'])) {
    $_SESSION['msg'] = "Please login to view staff attendance history.";
    $_SESSION['type'] = "danger";
    header("Location: login.php");
    exit;
}

/* ================= FILTER VALUES ================= */
$class   = $_GET['class']   ?? '';
$term    = $_GET['term']    ?? '';
$session = $_GET['session'] ?? '';

/* ================= FETCH CLASSES ================= */
$class_result = mysqli_query($connection, "SELECT classes FROM classes ORDER BY classes ASC");

/* ================= FETCH SESSIONS ================= */
$session_result = mysqli_query($connection, "SELECT sessions FROM sessions ORDER BY sessions DESC");

/* ================= BUILD FILTER ================= */
$where = "WHERE 1=1";
if ($class !== '') {
    $where .= " AND s.class = '".mysqli_real_escape_string($connection, $class)."'";
}
if ($term !== '') {
    $where .= " AND s.term = '".mysqli_real_escape_string($connection, $term)."'";
}
if ($session !== '') {
    $where .= " AND s.session = '".mysqli_real_escape_string($connection, $session)."'";
}

/* ================= SUBJECT REGISTRATION SUMMARY ================= */
$query = "
    SELECT 
        s.reg_number,
        s.name,
        s.class,
        s.term,
        s.session,
        GROUP_CONCAT(s.subjects ORDER BY s.subjects SEPARATOR ',') AS subject_list,
        COUNT(s.subjects) AS registered_count,
        COUNT(DISTINCT s.subjects) AS unique_count,
        MAX(s.registration_date) AS registration_date
    FROM subjects s
    $where
    GROUP BY s.reg_number, s.term, s.session
    ORDER BY s.class, s.reg_number
";

$result = mysqli_query($connection, $query);
?>

<div class="container-fluid mt-4">

<div class="card shadow-sm border-0 mb-4">
<div class="card-header bg-primary text-white">
<h5 class="mb-0"><i class="fas fa-book"></i> Subject Registration Audit</h5>
</div>

<div class="card-body">

<!-- ================= FILTER ================= -->
<form method="GET" class="row g-3 mb-4">

<div class="col-md-3">
<label class="form-label">Class</label>
<select name="class" class="form-control">
<option value="">-- All Classes --</option>
<?php while ($c = mysqli_fetch_assoc($class_result)): ?>
<option value="<?= $c['classes'] ?>" <?= $class==$c['classes']?'selected':'' ?>>
<?= $c['classes'] ?>
</option>
<?php endwhile; ?>
</select>
</div>

<div class="col-md-3">
<label class="form-label">Term</label>
<select name="term" class="form-control">
<option value="">-- Select Term --</option>
<option value="First Term" <?= $term=='First Term'?'selected':'' ?>>First Term</option>
<option value="Second Term" <?= $term=='Second Term'?'selected':'' ?>>Second Term</option>
<option value="Third Term" <?= $term=='Third Term'?'selected':'' ?>>Third Term</option>
</select>
</div>

<div class="col-md-3">
<label class="form-label">Session</label>
<select name="session" class="form-control">
<option value="">-- All Sessions --</option>
<?php while ($s = mysqli_fetch_assoc($session_result)): ?>
<option value="<?= $s['sessions'] ?>" <?= $session==$s['sessions']?'selected':'' ?>>
<?= $s['sessions'] ?>
</option>
<?php endwhile; ?>
</select>
</div>

<div class="col-md-3 d-flex align-items-end">
<button class="btn btn-success w-100">
<i class="fas fa-filter"></i> Filter
</button>
</div>

</form>

<!-- ================= TABLE ================= -->
<div class="table-responsive">
<table class="table table-bordered table-hover align-middle">

<thead class="table-dark">
<tr>
<th>Reg No</th>
<th>Name</th>
<th>Class</th>
<th>Registered</th>
<th>Subjects</th>
<th>Term</th>
<th>Session</th>
<th>Date</th>
</tr>
</thead>

<tbody>

<?php if (mysqli_num_rows($result) > 0): ?>
<?php while ($row = mysqli_fetch_assoc($result)): ?>

<?php
$has_duplicate = $row['registered_count'] != $row['unique_count'];

// Prepare subject badges
$subjects_arr = array_map('trim', explode(',', $row['subject_list']));
$sub_counts = array_count_values($subjects_arr);
?>

<tr class="<?= $has_duplicate ? 'table-warning' : '' ?>">

<td><?= $row['reg_number'] ?></td>
<td><?= htmlspecialchars($row['name']) ?></td>
<td><?= $row['class'] ?></td>

<td>
<span class="badge bg-success"><?= $row['registered_count'] ?></span>
</td>

<td style="min-width:280px;">
<?php
foreach ($subjects_arr as $sub) {
    if ($sub_counts[$sub] > 1) {
        // duplicate
        echo '<span class="badge bg-danger me-1 mb-1">'.$sub.'</span>';
    } else {
        // normal
        echo '<span class="badge bg-primary me-1 mb-1">'.$sub.'</span>';
    }
}
?>
</td>

<td><?= $row['term'] ?></td>
<td><?= $row['session'] ?></td>
<td><?= date('d M Y', strtotime($row['registration_date'])) ?></td>

</tr>

<?php endwhile; ?>
<?php else: ?>
<tr>
<td colspan="8" class="text-center text-muted">No subject registrations found</td>
</tr>
<?php endif; ?>

</tbody>

</table>
</div>

</div>
</div>

</div>

<style>
.table-hover tbody tr:hover {
  background-color: #f9f9f9;
}
td {
  vertical-align: middle;
}
.badge {
  font-size: 0.85rem;
}
</style>
