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

session_start();
if (!isset($_SESSION['staff'])) {
    $_SESSION['msg'] = "Please login to view result upload audit.";
    $_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 (LIKE subjects_reg) ================= */
$where = "1=1";

if ($class !== '') {
    $where .= " AND s.class = '".mysqli_real_escape_string($connection, $class)."'";
}
if ($term !== '') {
    $where .= " AND r.term = '".mysqli_real_escape_string($connection, $term)."'";
}
if ($session !== '') {
    $where .= " AND r.session = '".mysqli_real_escape_string($connection, $session)."'";
}

/* ================= RESULT UPLOAD AUDIT ================= */
$sql = "
    SELECT 
        r.reg_number,
        CONCAT(s.firstname,' ',COALESCE(s.othername,''),' ',s.lastname) AS student_name,
        s.class AS student_class,
        r.term,
        r.session,
        COUNT(r.subjects) AS subject_count,
        GROUP_CONCAT(r.subjects ORDER BY r.subjects SEPARATOR ', ') AS subject_list
    FROM results1 r
    INNER JOIN students s ON s.reg_number = r.reg_number
    WHERE $where
      AND r.subject_total IS NOT NULL
      AND r.subject_total != ''
    GROUP BY 
        r.reg_number,
        r.term,
        r.session,
        s.class,
        s.firstname,
        s.othername,
        s.lastname
    ORDER BY s.class, r.reg_number
";

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

<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-clipboard-check"></i> Result Upload Audit
</h5>
</div>

<div class="card-body">

<!-- ================= FILTER (SAME AS subjects_reg) ================= -->
<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>No. of Subjects Uploaded</th>
<th>Uploaded Subjects</th>
<th>Term</th>
<th>Session</th>
</tr>
</thead>

<tbody>

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

<tr>
<td><?= htmlspecialchars($row['reg_number']) ?></td>
<td><?= htmlspecialchars($row['student_name']) ?></td>
<td><?= htmlspecialchars($row['student_class']) ?></td>

<td>
<span class="badge bg-success">
<?= (int)$row['subject_count'] ?>
</span>
</td>

<td style="min-width:300px;">
<?php
$subjects = explode(',', $row['subject_list']);
foreach ($subjects as $sub) {
    echo '<span class="badge bg-primary me-1 mb-1">'
        .htmlspecialchars(trim($sub)).
        '</span>';
}
?>
</td>

<td><?= htmlspecialchars($row['term']) ?></td>
<td><?= htmlspecialchars($row['session']) ?></td>
</tr>

<?php endwhile; ?>
<?php else: ?>
<tr>
<td colspan="7" class="text-center text-muted">
No uploaded results 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>
