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

/* ---------- Helpers ---------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

function colExists(mysqli $db, string $table, string $col): bool {
  static $cache = [];
  $key = "$table.$col";
  if (isset($cache[$key])) return $cache[$key];
  $st = $db->prepare("SELECT 1
                        FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_SCHEMA = DATABASE()
                         AND TABLE_NAME = ?
                         AND COLUMN_NAME = ?
                       LIMIT 1");
  $st->bind_param("ss", $table, $col);
  $st->execute();
  $r = $st->get_result();
  return $cache[$key] = ($r && $r->num_rows > 0);
}

/* ---------- Pass/Fail / Marks / Grade ---------- */
function looks_final($txt){ $t=strtolower($txt); return str_contains($t,'final'); }
function looks_eval($txt){ $t=strtolower($txt); return (str_contains($t,'evaluation') || str_contains($t,'eval')); }

function is_pass(string $unit, int $score, int $total): bool {
  if ($total <= 0) return false;
  $pct = $score / $total;
  if (looks_eval($unit))  return $pct >= 0.50;
  if (looks_final($unit)) return $pct >= 0.41;
  return $pct >= 0.41;
}

function mark_to_100($score, $total): float {
  $score = (float)$score;
  $total = (float)$total;
  if ($total <= 0) return 0.0;
  return round(($score / $total) * 100, 2);
}

function grade_from_100($mark100): string {
  $m = (float)$mark100;
  if ($m >= 75) return 'A';
  if ($m >= 65) return 'B';
  if ($m >= 55) return 'C';
  if ($m >= 40) return 'S';
  return 'F';
}

/* ---------- CSRF (for delete) ---------- */
if (empty($_SESSION['csrf'])) { $_SESSION['csrf'] = bin2hex(random_bytes(16)); }

/* ---------- Inputs ---------- */
$dateToday     = date('Y-m-d');
$filter_course = trim($_GET['course_name'] ?? '');
$filter_unit   = trim($_GET['unit_exam'] ?? '');

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

/* ---------- Today’s sittings (deduplicated by latest student_exams row per student+exam for today) ---------- */
$todaySql = "
  SELECT
      se.id AS student_exam_id,
      se.created_at AS assigned_at,
      se.attempt_no,
      se.student_id,
      se.exam_id,
      s.name,
      s.nic_number,
      e.course_name,
      e.unit_exam,
      r.id AS result_id,
      r.score,
      r.total,
      r.date_taken
  FROM student_exams se
  INNER JOIN (
      SELECT student_id, exam_id, MAX(id) AS latest_se_id
      FROM student_exams
      WHERE DATE(created_at) = CURDATE()
      GROUP BY student_id, exam_id
  ) latest_se
    ON latest_se.latest_se_id = se.id
  INNER JOIN students s ON s.id = se.student_id
  INNER JOIN exams e    ON e.id = se.exam_id
  LEFT JOIN (
      SELECT rr.*
      FROM results rr
      INNER JOIN (
          SELECT student_id, exam_id, MAX(id) AS max_id
          FROM results
          GROUP BY student_id, exam_id
      ) latest_r
        ON latest_r.student_id = rr.student_id
       AND latest_r.exam_id    = rr.exam_id
       AND latest_r.max_id     = rr.id
  ) r
    ON r.student_id = se.student_id
   AND r.exam_id    = se.exam_id
  WHERE DATE(se.created_at) = CURDATE()
";

$params = [];
$types  = '';

if ($filter_course !== '') {
  $todaySql .= " AND e.course_name = ?";
  $params[] = $filter_course;
  $types   .= 's';
}

if ($filter_unit !== '') {
  $todaySql .= " AND e.unit_exam = ?";
  $params[] = $filter_unit;
  $types   .= 's';
}

$todaySql .= " ORDER BY se.created_at DESC, se.id DESC";

$todayStmt = $conn->prepare($todaySql);
if (!$todayStmt) {
  die("Prepare failed (today sittings): " . $conn->error);
}
if (!empty($params)) {
  $todayStmt->bind_param($types, ...$params);
}
$todayStmt->execute();
$res = $todayStmt->get_result();

$todayRows = [];
if ($res) {
  while ($row = $res->fetch_assoc()) {
    $todayRows[] = $row;
  }
}

/* ---------- KPIs ---------- */
$totalSittings = count($todayRows);
$completedSittings = 0;
$sumScore = 0;
$sumTotal = 0;
$passes = 0;

foreach ($todayRows as $r) {
  $hasResult = isset($r['result_id']) && (int)$r['result_id'] > 0;
  if (!$hasResult) continue;

  $completedSittings++;
  $sc = (int)($r['score'] ?? 0);
  $tt = (int)($r['total'] ?? 0);

  $sumScore += $sc;
  $sumTotal += $tt;

  if (is_pass((string)($r['unit_exam'] ?? ''), $sc, $tt)) {
    $passes++;
  }
}

$avgScorePct = ($sumTotal > 0) ? round(($sumScore / $sumTotal) * 100) : 0;
$passRatePct = ($completedSittings > 0) ? round(($passes / $completedSittings) * 100) : 0;

/* ---------- All exams ---------- */
$exams = $conn->query("
  SELECT e.id, e.title, e.course_name, e.unit_exam,
         (SELECT COUNT(*) FROM questions q WHERE q.exam_id = e.id) AS q_count,
         (SELECT COUNT(*) FROM students s WHERE s.course_name = e.course_name AND s.unit_exam = e.unit_exam) AS s_count
    FROM exams e
ORDER BY e.course_name, e.unit_exam, e.id DESC
");
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>EDEN Campus Online Systems</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    :root{
      --brand-dark:#0a2a5a; --brand-darker:#071d40; --brand-red:#e42424;
      --muted:#6b7280; --ok:#16a34a; --warn:#dc2626; --card:#ffffff; --bg:#eef3fb;
    }
    body{background:var(--bg); font-family:system-ui,-apple-system,"Segoe UI",Roboto,Ubuntu,"Helvetica Neue",Arial;}
    .btn-theme{background:var(--brand-red); border-color:var(--brand-red); color:#fff;}
    .btn-theme:hover{background:#c51e1e; border-color:#c51e1e;}
    .card-neo{border:0; border-radius:16px; overflow:hidden; box-shadow:0 16px 36px rgba(10,27,66,.10); background:var(--card);}
    .card-header.brand{background:#0b2c61; color:#fff; font-weight:700;}
    .subtle{color:var(--muted)}
    .kpi{display:flex; align-items:center; gap:.75rem; padding:14px; border:1px solid #e5e7eb; border-radius:14px; background:#fff}
    .kpi .big{font-size:1.4rem; font-weight:800}
    .kpi.ok .big{color:var(--ok)}
    .kpi.warn .big{color:var(--warn)}
    .table thead{background:#eef2ff;}
    .table-hover tbody tr:hover{background:#f8fafc;}
    .badge-pill{border-radius:50rem}
    .filters-sticky{position:sticky; top:0; z-index:11; box-shadow:0 6px 14px rgba(0,0,0,.06);}
    @media (max-width: 576px){ .table-wrap{display:none} .cards-wrap{display:block} }
    @media (min-width: 577px){ .table-wrap{display:block} .cards-wrap{display:none} }
    .exam-card{background:#fff; border:1px solid #e5e7eb; border-radius:14px; padding:12px; margin-bottom:10px;}
    .exam-card .title{font-weight:700}
    .exam-card .line{display:flex; justify-content:space-between; gap:10px; font-size:.95rem}
    .exam-card .pill{padding:.15rem .5rem; border-radius:999px; background:#badge bg-success}
    #allExamsWrap{display:none}
  </style>
</head>
<body>
<?php include __DIR__ . '/../includes/topbar.php'; ?>

<main class="container py-4">

  <?php if (!empty($_SESSION['flash'])): $f=$_SESSION['flash']; unset($_SESSION['flash']); ?>
    <div class="alert alert-<?= h($f['type'] ?? 'info') ?> mb-3"><?= h($f['text'] ?? '') ?></div>
  <?php endif; ?>

  <div class="mb-3">
    <h3 class="fw-bold m-0">Admin Dashboard</h3>
  </div>

  <div class="row g-2 mb-3">
    <div class="col-12 col-md-4">
      <div class="kpi">
        <div class="big"><?= (int)$totalSittings ?></div>
        <div>
          <div class="fw-bold">Today’s Exam Sittings</div>
          <div class="subtle small"><?= h($dateToday) ?></div>
        </div>
      </div>
    </div>
    <div class="col-6 col-md-4">
      <div class="kpi <?= $passRatePct>=50?'ok':'warn' ?>">
        <div class="big"><?= (int)$passRatePct ?>%</div>
        <div>
          <div class="fw-bold">Pass rate</div>
          <div class="subtle small">Eval ≥50% • Final ≥41%</div>
        </div>
      </div>
    </div>
    <div class="col-6 col-md-4">
      <div class="kpi">
        <div class="big"><?= (int)$avgScorePct ?>%</div>
        <div>
          <div class="fw-bold">Avg score</div>
          <div class="subtle small">All sittings today</div>
        </div>
      </div>
    </div>
  </div>

  <form class="card card-neo mb-3 filters-sticky" method="get" autocomplete="off">
    <div class="card-header brand">Filter Today’s Sittings</div>
    <div class="card-body">
      <div class="row g-2">
        <div class="col-md-5">
          <label class="form-label">Course</label>
          <div class="input-group">
            <select class="form-select" name="course_name" id="course_name">
              <option value="">-- All Courses --</option>
              <?php if ($courses) while($c=$courses->fetch_assoc()):
                $sel = ($c['course_name'] === $filter_course) ? 'selected' : ''; ?>
                <option value="<?= h($c['course_name']) ?>" <?= $sel ?>><?= h($c['course_name']) ?></option>
              <?php endwhile; ?>
            </select>
            <a class="btn btn-outline-primary" href="courses.php" target="_blank">Manage</a>
            <button class="btn btn-outline-secondary" type="button" id="refreshCourses">↻</button>
          </div>
        </div>
        <div class="col-md-5">
          <label class="form-label">Unit</label>
          <div class="input-group">
            <select class="form-select" name="unit_exam" id="unit_exam">
              <option value="">-- All Units --</option>
            </select>
            <button class="btn btn-outline-secondary" type="button" id="refreshUnits">↻</button>
          </div>
        </div>
        <div class="col-md-2 d-flex align-items-end">
          <button class="btn btn-theme w-100">Apply</button>
        </div>
      </div>
    </div>
  </form>

  <div class="card card-neo mb-3">
    <div class="card-header brand d-flex justify-content-between align-items-center">
      <span>Today’s Exam Sittings (<?= h($dateToday) ?>)</span>
      <span class="small text-white-50">Swipe on mobile • Click row for actions</span>
    </div>

    <div class="card-body p-0 table-wrap">
      <div class="table-responsive">
        <table class="table table-hover m-0 align-middle">
          <thead>
            <tr>
              <th>Register (NIC)</th>
              <th>Name</th>
              <th>Course</th>
              <th>Unit</th>
              <th>Marks (100)</th>
              <th>Grade</th>
              <th>Status</th>
              <th class="text-end" style="width:170px">Action</th>
            </tr>
          </thead>
          <tbody>
          <?php if (!empty($todayRows)): foreach($todayRows as $row): ?>
            <?php
              $hasResult = isset($row['result_id']) && (int)$row['result_id'] > 0;
              $score = $hasResult ? (float)$row['score'] : null;
              $total = $hasResult ? (float)$row['total'] : null;
              $unit  = (string)($row['unit_exam'] ?? '');
              $mark100 = $hasResult ? mark_to_100($score, $total) : null;
              $grade   = $hasResult ? grade_from_100($mark100) : null;
              $pass    = $hasResult ? is_pass($unit, (int)$score, (int)$total) : false;
            ?>
            <tr>
              <td><?= h($row['nic_number'] ?? '-') ?></td>
              <td><?= h($row['name'] ?? '-') ?></td>
              <td><?= h($row['course_name'] ?? '-') ?></td>
              <td><?= h($row['unit_exam'] ?? '-') ?></td>
              <td>
                <?php if ($hasResult): ?>
                  <strong><?= number_format($mark100, 2) ?></strong><span class="subtle">/100</span>
                <?php else: ?>
                  <span class="text-muted">-</span>
                <?php endif; ?>
              </td>
              <td>
                <?php if ($hasResult): ?>
                  <span class="badge bg-success badge-pill"><?= h($grade) ?></span>
                <?php else: ?>
                  <span class="text-muted">-</span>
                <?php endif; ?>
              </td>
              <td>
                <?php if (!$hasResult): ?>
                  <span class="badge bg-secondary badge-pill">Pending</span>
                <?php elseif (!$pass): ?>
                  <span class="badge bg-danger badge-pill">Fail</span>
                <?php else: ?>
                  <span class="badge bg-success badge-pill">Pass</span>
                <?php endif; ?>
              </td>
              <td class="text-end">
                <?php if (!$hasResult): ?>
                  <span class="text-muted">—</span>
                <?php elseif (!$pass): ?>
                  <a class="btn btn-warning btn-sm"
                     href="reset_result.php?result_id=<?= (int)$row['result_id'] ?>&csrf=<?= urlencode($_SESSION['csrf']) ?>"
                     onclick="return confirm('Reset this failed attempt so the student can retake?');">Reset</a>
                <?php else: ?>
                  <span class="text-muted">—</span>
                <?php endif; ?>
              </td>
            </tr>
          <?php endforeach; else: ?>
            <tr><td colspan="8" class="text-center p-4">No sittings found.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>

    <div class="card-body cards-wrap">
      <?php if (!empty($todayRows)): foreach($todayRows as $row): ?>
        <?php
          $hasResult = isset($row['result_id']) && (int)$row['result_id'] > 0;
          $score = $hasResult ? (float)$row['score'] : null;
          $total = $hasResult ? (float)$row['total'] : null;
          $unit  = (string)($row['unit_exam'] ?? '');
          $mark100 = $hasResult ? mark_to_100($score, $total) : null;
          $grade   = $hasResult ? grade_from_100($mark100) : null;
          $pass    = $hasResult ? is_pass($unit, (int)$score, (int)$total) : false;
        ?>
        <div class="exam-card">
          <div class="title d-flex justify-content-between">
            <span><?= h($row['name'] ?? '-') ?></span>
            <span class="pill"><?= h($row['nic_number'] ?? '-') ?></span>
          </div>
          <div class="line"><span class="subtle">Course</span><span><?= h($row['course_name'] ?? '-') ?></span></div>
          <div class="line"><span class="subtle">Unit</span><span><?= h($row['unit_exam'] ?? '-') ?></span></div>
          <div class="line">
            <span class="subtle">Marks</span>
            <span>
              <?php if ($hasResult): ?>
                <strong><?= number_format($mark100, 2) ?></strong><span class="subtle">/100</span>
              <?php else: ?>
                <span class="text-muted">-</span>
              <?php endif; ?>
            </span>
          </div>
          <div class="line">
            <span class="subtle">Grade</span>
            <span>
              <?php if ($hasResult): ?>
                <span class="badge bg-success"><?= h($grade) ?></span>
              <?php else: ?>
                <span class="text-muted">-</span>
              <?php endif; ?>
            </span>
          </div>
          <div class="d-flex justify-content-between align-items-center mt-2">
            <?php if (!$hasResult): ?>
              <span class="badge bg-secondary">Pending</span>
              <span class="text-muted small">—</span>
            <?php elseif (!$pass): ?>
              <span class="badge bg-danger">Fail</span>
              <a class="btn btn-warning btn-sm"
                 href="reset_result.php?result_id=<?= (int)$row['result_id'] ?>&csrf=<?= urlencode($_SESSION['csrf']) ?>"
                 onclick="return confirm('Reset this failed attempt so the student can retake?');">Reset</a>
            <?php else: ?>
              <span class="badge bg-success">Pass</span>
              <span class="text-muted small">—</span>
            <?php endif; ?>
          </div>
        </div>
      <?php endforeach; else: ?>
        <div class="text-center text-muted">No sittings found.</div>
      <?php endif; ?>
    </div>
  </div>

<?php if (in_array($role, ['super','admin'], true)): ?>
  <div class="mb-2 d-flex justify-content-between align-items-center">
    <button class="btn btn-outline-dark" type="button" onclick="toggleAllExams()">All Exams</button>
    <small class="subtle">Manage questions/students per exam</small>
  </div>
<?php endif; ?>
  <div id="allExamsWrap" class="card card-neo">
    <div class="card-header brand">All Exams</div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table table-hover m-0 align-middle">
          <thead>
            <tr>
              <th>Exam Title</th>
              <th>Course</th>
              <th>Unit</th>
              <th class="text-center">Qns</th>
              <th class="text-center">Students</th>
              <th class="text-center">Actions</th>
            </tr>
          </thead>
          <tbody>
            <?php if ($exams && $exams->num_rows): while($e=$exams->fetch_assoc()): ?>
              <tr>
                <td><?= h($e['title']) ?></td>
                <td><?= h($e['course_name']) ?></td>
                <td><?= h($e['unit_exam']) ?></td>
                <td class="text-center"><span class="badge bg-success badge-pill"><?= (int)$e['q_count'] ?></span></td>
                <td class="text-center"><span class="badge bg-secondary badge-pill"><?= (int)$e['s_count'] ?></span></td>
                <td class="text-center">
                  <div class="btn-group">
                    <a href="add_question.php?exam_id=<?= (int)$e['id'] ?>" class="btn btn-sm btn-primary">Add Q</a>
                    <a href="view_questions.php?exam_id=<?= (int)$e['id'] ?>" class="btn btn-sm btn-info">View Q</a>
                    <a href="view_students.php?course=<?= urlencode($e['course_name']) ?>&unit=<?= urlencode($e['unit_exam']) ?>" class="btn btn-sm btn-secondary">Students</a>
                    <a href="edit_exam.php?exam_id=<?= (int)$e['id'] ?>" class="btn btn-sm btn-warning">Edit</a>
                    <a href="delete_exam.php?exam_id=<?= (int)$e['id'] ?>"
                       class="btn btn-sm btn-danger"
                       onclick="return confirm('Delete this exam? This removes its questions and results.');">Delete</a>
                  </div>
                </td>
              </tr>
            <?php endwhile; else: ?>
              <tr><td colspan="6" class="text-center p-4">No exams found.</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

</main>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
function toggleAllExams(){
  const wrap = document.getElementById('allExamsWrap');
  wrap.style.display = (wrap.style.display === 'none' || wrap.style.display === '') ? 'block' : 'none';
}
function loadUnits(){
  const c = $('#course_name').val();
  if(!c){
    $('#unit_exam').html('<option value="">-- All Units --</option>');
    return;
  }
  $.get('get_units.php',{course_name:c},function(html){
    $('#unit_exam').html('<option value="">-- All Units --</option>'+html);
    const currentUnit = '<?= h($filter_unit) ?>';
    if(currentUnit) $('#unit_exam').val(currentUnit);
  }).fail(()=>alert('Failed to load units.'));
}
$(function(){
  $('#course_name').on('change', function(){
    $('#unit_exam').val('');
    loadUnits();
  });
  $('#refreshCourses').on('click', function(){
    $.get('get_courses.php', function(html){
      $('#course_name').html('<option value="">-- All Courses --</option>'+html);
      $('#unit_exam').html('<option value="">-- All Units --</option>');
    }).fail(()=>alert('Failed to refresh courses.'));
  });
  $('#refreshUnits').on('click', loadUnits);

  const selectedCourse='<?= h($filter_course) ?>';
  if(selectedCourse){
    $('#course_name').val(selectedCourse);
    loadUnits();
  }
});
</script>
</body>
</html>