<?php
session_start();
require_once "../includes/db.php";
if (!isset($_SESSION['admin'])) { header("Location: index.php"); exit; }

/* ---------- Inputs ---------- */
$course = trim($_GET['course'] ?? '');
$unit   = trim($_GET['unit']   ?? '');
$q      = trim($_GET['q']      ?? '');   // quick search (name / NIC / reg no)

/* ---------- Courses for picker ---------- */
if ($conn->query("SHOW TABLES LIKE 'courses'")->num_rows > 0) {
  $courses_rs = $conn->query("SELECT course_name FROM courses ORDER BY course_name ASC");
} else {
  $courses_rs = $conn->query("SELECT DISTINCT course_name AS name FROM exams ORDER BY name ASC");
}

/* ---------- Actions (require course/unit context) ---------- */
if ($course !== '' && $unit !== '') {
  if (isset($_GET['reset'])) {
    $id = (int)$_GET['reset'];
    // Reset attempts only for this course/unit (not all exams)
    $stmt = $conn->prepare("
      DELETE r FROM results r
      JOIN exams e ON e.id = r.exam_id
     WHERE r.student_id=? AND e.course_name=? AND e.unit_exam=?");
    $stmt->bind_param("iss", $id, $course, $unit);
    $stmt->execute();
    header("Location: view_students.php?course=".urlencode($course)."&unit=".urlencode($unit));
    exit;
  }
  if (isset($_GET['delete'])) {
    $id = (int)$_GET['delete'];
    // Delete results scoped to this course/unit, then student row (if you truly want to remove the student)
    $stmt = $conn->prepare("
      DELETE r FROM results r
      JOIN exams e ON e.id = r.exam_id
     WHERE r.student_id=? AND e.course_name=? AND e.unit_exam=?");
    $stmt->bind_param("iss", $id, $course, $unit);
    $stmt->execute();

    $stmt2 = $conn->prepare("DELETE FROM students WHERE id=? AND course_name=? AND unit_exam=?");
    $stmt2->bind_param("iss", $id, $course, $unit);
    $stmt2->execute();

    header("Location: view_students.php?course=".urlencode($course)."&unit=".urlencode($unit));
    exit;
  }
}

/* ---------- Fetch students (only if we have course/unit) ---------- */
$students = null;
if ($course !== '' && $unit !== '') {
  // Grouped count of attempts for this course/unit only
  $sql = "
    SELECT s.id, s.name, s.nic_number, IFNULL(s.course_reg_no,'') AS course_reg_no,
           COUNT(r.id) AS attempts
      FROM students s
 LEFT JOIN exams e  ON e.course_name = s.course_name AND e.unit_exam = s.unit_exam
 LEFT JOIN results r ON r.student_id = s.id AND r.exam_id = e.id
     WHERE s.course_name = ?
       AND s.unit_exam   = ?
  ";
  $params = [$course, $unit];
  $types  = "ss";

  if ($q !== '') {
    $sql .= " AND (s.name LIKE ? OR s.nic_number LIKE ? OR s.course_reg_no LIKE ?)";
    $like = "%$q%";
    $params[] = $like; $params[] = $like; $params[] = $like;
    $types   .= "sss";
  }
  $sql .= " GROUP BY s.id ORDER BY s.id DESC";

  $st = $conn->prepare($sql);
  $st->bind_param($types, ...$params);
  $st->execute();
  $students = $st->get_result();
}
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Students <?= ($course&&$unit) ? "• ".htmlspecialchars($course)." / ".htmlspecialchars($unit) : "" ?></title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link href="../assets/css/bootstrap.min.css" rel="stylesheet">
  <style>
    body{background:#f7f8fb; font-family:system-ui,-apple-system,"Segoe UI",Roboto,Ubuntu,"Helvetica Neue",Arial;}
    .card-neo{border:0;border-radius:16px;box-shadow:0 16px 36px rgba(10,27,66,.08); background:#fff;}
    .card-header.brand{background:#0a2a5a; color:#fff; font-weight:700;}
    .table thead{background:#eef2ff;}
    .badge-pill{border-radius:50rem}
    /* mobile helpers */
    @media (max-width: 576px){
      .table-responsive{font-size:.92rem}
      .actions-row .btn{margin-bottom:6px}
    }
    /* little “chip” */
    .pill{padding:.15rem .5rem;border-radius:999px;background:#eef2ff}
  </style>
</head>
<body class="p-3 p-md-4">

<div class="container">

  <div class="d-flex flex-wrap justify-content-between align-items-center mb-3">
    <h3 class="m-0">Students <?= ($course&&$unit) ? "— ".htmlspecialchars($course)." / ".htmlspecialchars($unit) : "" ?></h3>
    <div class="d-flex gap-2">
      <a class="btn btn-secondary btn-sm" href="dashboard.php">⬅ Dashboard</a>
      <a class="btn btn-success btn-sm" href="student_create.php">+ Add Student</a>
    </div>
  </div>

  <?php if ($course === '' || $unit === ''): ?>
    <!-- ---------- Course/Unit Picker (when missing) ---------- -->
    <div class="card card-neo">
      <div class="card-header brand">Choose Course & Unit</div>
      <div class="card-body">
        <form method="get" class="row g-3" id="pickerForm">
          <div class="col-12 col-md-6">
            <label class="form-label">Course</label>
            <select class="form-select" name="course" id="course_name" required>
              <option value="">-- Select Course --</option>
              <?php if ($courses_rs) while($c=$courses_rs->fetch_assoc()): ?>
                <option value="<?= htmlspecialchars($c['name']) ?>"><?= htmlspecialchars($c['name']) ?></option>
              <?php endwhile; ?>
            </select>
          </div>
          <div class="col-12 col-md-6">
            <label class="form-label">Unit</label>
            <select class="form-select" name="unit" id="unit_exam" required>
              <option value="">-- Select Unit --</option>
            </select>
          </div>
          <div class="col-12">
            <button class="btn btn-dark">Load Students</button>
            <a class="btn btn-outline-secondary" href="add_student.php" target="_blank">Manage All Students</a>
          </div>
        </form>
        <div class="text-muted mt-2">Tip: Units list updates automatically when you pick a course.</div>
      </div>
    </div>

  <?php else: ?>
    <!-- ---------- Filters line (search within chosen course/unit) ---------- -->
    <form class="card card-neo mb-3 p-3" method="get" autocomplete="off">
      <input type="hidden" name="course" value="<?= htmlspecialchars($course) ?>">
      <input type="hidden" name="unit" value="<?= htmlspecialchars($unit) ?>">
      <div class="row g-2 align-items-end">
        <div class="col-md-4">
          <label class="form-label">Course</label>
          <div class="form-control-plaintext"><span class="pill"><?= htmlspecialchars($course) ?></span></div>
        </div>
        <div class="col-md-4">
          <label class="form-label">Unit</label>
          <div class="form-control-plaintext"><span class="pill"><?= htmlspecialchars($unit) ?></span></div>
        </div>
        <div class="col-md-4">
          <label class="form-label">Search</label>
          <input type="text" class="form-control" name="q" value="<?= htmlspecialchars($q) ?>" placeholder="Name / NIC / Reg No">
        </div>
        <div class="col-12 col-md-4 mt-2">
          <button class="btn btn-dark">Apply</button>
          <a class="btn btn-outline-secondary" href="view_students.php">Change Course/Unit</a>
        </div>
      </div>
    </form>

    <!-- ---------- List ---------- -->
    <div class="card card-neo">
      <div class="card-header d-flex justify-content-between align-items-center">
        <span>Students</span>
        <a class="btn btn-sm btn-success" href="student_create.php">+ Add Student</a>
      </div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table table-striped table-hover m-0 align-middle">
            <thead>
              <tr>
                <th style="min-width:140px">Reg No.</th>
                <th>Name</th>
                <th>NIC</th>
                <th class="text-center">Attempts</th>
                <th style="width:360px">Actions</th>
              </tr>
            </thead>
            <tbody>
              <?php if (!$students || $students->num_rows===0): ?>
                <tr><td colspan="5" class="text-center p-4">No students found.</td></tr>
              <?php else: while($s = $students->fetch_assoc()): ?>
                <tr>
                  <td><?= htmlspecialchars($s['course_reg_no']) ?></td>
                  <td><?= htmlspecialchars($s['name']) ?></td>
                  <td><?= htmlspecialchars($s['nic_number']) ?></td>
                  <td class="text-center"><span class="badge bg-info badge-pill"><?= (int)$s['attempts'] ?></span></td>
                  <td class="actions-row">
                    <div class="d-flex flex-wrap gap-2">
                      <a class="btn btn-warning btn-sm" href="student_edit.php?id=<?= (int)$s['id'] ?>">Edit</a>
                      <a class="btn btn-outline-info btn-sm"
                         href="view_students.php?course=<?= urlencode($course) ?>&unit=<?= urlencode($unit) ?>&reset=<?= (int)$s['id'] ?>"
                         onclick="return confirm('Reset attempts for this student on this unit?')">Reset Attempts</a>
                      <a class="btn btn-danger btn-sm"
                         href="view_students.php?course=<?= urlencode($course) ?>&unit=<?= urlencode($unit) ?>&delete=<?= (int)$s['id'] ?>"
                         onclick="return confirm('Delete this student record for this unit (and its results)?')">Delete</a>
                    </div>
                  </td>
                </tr>
              <?php endwhile; endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  <?php endif; ?>

</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
/* Auto-load units when picking a course (only shown when course/unit missing) */
function loadUnits(){
  const c = $('#course_name').val();
  if(!c){ $('#unit_exam').html('<option value="">-- Select Unit --</option>'); return; }
  $.get('get_units.php',{course_name:c},function(html){
    $('#unit_exam').html('<option value="">-- Select Unit --</option>'+html);
  }).fail(()=>alert('Failed to load units.'));
}
$(function(){
  $('#course_name').on('change', function(){ $('#unit_exam').val(''); loadUnits(); });
});
</script>
</body>
</html>
