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

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* ---------- DB ---------- */
$db = isset($conn) ? $conn : (isset($mysqli) ? $mysqli : null);
if (!$db) die("DB connection missing.");
$db->set_charset('utf8mb4');

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

function hasColumn(mysqli $db, string $table, string $column): bool {
    $sql = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1";
    $st = $db->prepare($sql);
    if (!$st) return false;
    $st->bind_param("ss", $table, $column);
    $st->execute();
    $r = $st->get_result();
    $ok = ($r && $r->num_rows > 0);
    $st->close();
    return $ok;
}

function tableExists(mysqli $db, string $table): bool {
    $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_SCHEMA = DATABASE()
                          AND TABLE_NAME=? LIMIT 1");
    if (!$st) return false;
    $st->bind_param("s", $table);
    $st->execute();
    $r = $st->get_result();
    $ok = ($r && $r->num_rows > 0);
    $st->close();
    return $ok;
}

function normalize_mark100_from_row(array $r, bool $hasRPct, bool $hasRMark100, bool $hasRTotal): float {
    if ($hasRMark100 && isset($r['mark100']) && $r['mark100'] !== null && $r['mark100'] !== '') {
        return round((float)$r['mark100'], 2);
    }

    if ($hasRPct && isset($r['percentage']) && $r['percentage'] !== null && $r['percentage'] !== '') {
        return round((float)$r['percentage'], 2);
    }

    $score = isset($r['score']) ? (float)$r['score'] : 0.0;
    $total = ($hasRTotal && isset($r['total'])) ? (float)$r['total'] : 0.0;

    if ($total > 0) {
        return round(($score / $total) * 100, 2);
    }

    return round($score, 2);
}

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

function grade_badge_class(string $grade): string {
    return in_array($grade, ['A','B','C','S'], true) ? 'bg-success' : 'bg-danger';
}

/* ---------- Inputs ---------- */
$start  = $_GET['start'] ?? '';
$end    = $_GET['end'] ?? '';
$course = trim($_GET['course_name'] ?? '');
$unit   = trim($_GET['unit_exam'] ?? '');
$q      = trim($_GET['q'] ?? '');
$export = isset($_GET['export']) ? 1 : 0;

/* ---------- Schema checks ---------- */
$hasRDate    = hasColumn($db, 'results', 'date_taken');
$hasRGrade   = hasColumn($db, 'results', 'grade');
$hasRTotal   = hasColumn($db, 'results', 'total');
$hasRPct     = hasColumn($db, 'results', 'percentage');
$hasRMark100 = hasColumn($db, 'results', 'mark100');
$hasRStatus  = hasColumn($db, 'results', 'status');

/* ---------- Default date = today ---------- */
if ($hasRDate && $start === '' && $end === '') {
    $start = date('Y-m-d');
    $end   = date('Y-m-d');
}

/* ---------- Course list ---------- */
$course_rs = false;
if (tableExists($db, 'courses')) {
    $course_rs = $db->query("
        SELECT DISTINCT course_name
        FROM courses
        WHERE course_name IS NOT NULL AND TRIM(course_name) <> ''
        ORDER BY course_name ASC
    ");
} else {
    $course_rs = $db->query("
        SELECT DISTINCT course_name
        FROM exams
        WHERE course_name IS NOT NULL AND TRIM(course_name) <> ''
        ORDER BY course_name ASC
    ");
}

/* ---------- Filters ---------- */
$where  = " WHERE 1=1 ";
$params = [];
$types  = "";

if ($hasRDate) {
    if ($start !== '') {
        $where .= " AND DATE(r.date_taken) >= ? ";
        $params[] = $start;
        $types .= "s";
    }
    if ($end !== '') {
        $where .= " AND DATE(r.date_taken) <= ? ";
        $params[] = $end;
        $types .= "s";
    }
}
if ($course !== '') {
    $where .= " AND e.course_name = ? ";
    $params[] = $course;
    $types .= "s";
}
if ($unit !== '') {
    $where .= " AND e.unit_exam = ? ";
    $params[] = $unit;
    $types .= "s";
}
if ($q !== '') {
    $like = "%$q%";
    $where .= " AND (s.nic_number LIKE ? OR s.name LIKE ?) ";
    $params[] = $like;
    $params[] = $like;
    $types .= "ss";
}

/* ---------- Query ---------- */
$select = "
    SELECT
        r.id,
        r.score" .
        ($hasRTotal   ? ", r.total"      : "") .
        ($hasRDate    ? ", r.date_taken" : "") .
        ($hasRGrade   ? ", r.grade"      : "") .
        ($hasRPct     ? ", r.percentage" : "") .
        ($hasRMark100 ? ", r.mark100"    : "") .
        ($hasRStatus  ? ", r.status"     : "") . ",
        s.nic_number,
        s.name,
        e.course_name AS e_course,
        e.unit_exam   AS e_unit
";

$join = "
    FROM results r
    JOIN students s ON s.id = r.student_id
    JOIN exams e    ON e.id = r.exam_id
";

$order = $hasRDate ? " ORDER BY r.date_taken DESC, r.id DESC " : " ORDER BY r.id DESC ";
$sql   = $select . $join . $where . $order;

/* ---------- Export ---------- */
if ($export) {
    $stmt = $db->prepare($sql);
    if (!$stmt) die("Prepare failed: " . $db->error);

    if ($types !== "") {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $res = $stmt->get_result();

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="results_export.csv"');

    $out = fopen('php://output', 'w');
    fputcsv($out, ['Date/Time', 'Course', 'Unit', 'Register (NIC)', 'Name', 'Score (100)', 'Grade']);

    while ($r = $res->fetch_assoc()) {
        $mark100 = normalize_mark100_from_row($r, $hasRPct, $hasRMark100, $hasRTotal);

        $grade = $hasRGrade ? trim((string)($r['grade'] ?? '')) : '';
        if ($grade === '') {
            $grade = grade_from_mark100($mark100);
        }

        fputcsv($out, [
            $hasRDate ? ($r['date_taken'] ?? '') : $r['id'],
            $r['e_course'] ?? '',
            $r['e_unit'] ?? '',
            $r['nic_number'] ?? '',
            $r['name'] ?? '',
            number_format($mark100, 2, '.', ''),
            $grade
        ]);
    }

    fclose($out);
    exit;
}

/* ---------- Normal view ---------- */
$stmt = $db->prepare($sql);
if (!$stmt) die("Prepare failed: " . $db->error);

if ($types !== "") {
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$list = $stmt->get_result();
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Exam Results</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>
    body { background:#f4f7fb; }
    .table td, .table th { vertical-align: middle; white-space: nowrap; }
    .filters .form-label { margin-bottom: .25rem; }
    .card-neo{
      border:0;
      border-radius:16px;
      overflow:hidden;
      box-shadow:0 16px 36px rgba(10,27,66,.10);
      background:#fff;
    }
    .card-header.brand{
      background:#0b2c61;
      color:#fff;
      font-weight:700;
    }
  </style>
</head>
<body class="bg-light">
<?php include __DIR__ . '/../includes/topbar.php'; ?>

<div class="container py-4">
  <div class="d-flex justify-content-between align-items-center mb-3">
    <h3 class="m-0 fw-bold">Exam Results</h3>
    <div class="d-flex gap-2">
      <a href="dashboard.php" class="btn btn-outline-secondary btn-sm">Dashboard</a>
    </div>
  </div>

  <form method="get" class="card card-neo mb-3 filters">
    <div class="card-header brand">Filters & Export</div>
    <div class="card-body">
      <div class="row g-3">
        <?php if ($hasRDate): ?>
        <div class="col-md-2">
          <label class="form-label">Start Date</label>
          <input type="date" class="form-control" name="start" value="<?= h($start) ?>">
        </div>

        <div class="col-md-2">
          <label class="form-label">End Date</label>
          <input type="date" class="form-control" name="end" value="<?= h($end) ?>">
        </div>
        <?php endif; ?>

        <div class="col-md-3">
          <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 ($course_rs) while($c = $course_rs->fetch_assoc()):
                $nm = trim((string)($c['course_name'] ?? ''));
                if ($nm === '') continue;
              ?>
                <option value="<?= h($nm) ?>" <?= $nm === $course ? 'selected' : '' ?>>
                  <?= h($nm) ?>
                </option>
              <?php endwhile; ?>
            </select>
            <a class="btn btn-outline-primary" href="courses.php" target="_blank">Manage</a>
          </div>
        </div>

        <div class="col-md-3">
          <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">
          <label class="form-label">Search (NIC or Name)</label>
          <input type="text" class="form-control" name="q" placeholder="Search" value="<?= h($q) ?>">
        </div>

        <div class="col-md-12 d-flex align-items-end gap-2">
          <button class="btn btn-dark" type="submit">Apply Filters</button>
          <a class="btn btn-outline-secondary" href="results.php">Clear</a>
          <button name="export" value="1" class="btn btn-success" type="submit">Export CSV</button>
        </div>
      </div>
    </div>
  </form>

  <div class="card card-neo">
    <div class="card-header brand">Results</div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table table-striped table-hover m-0">
          <thead class="table-light">
            <tr>
              <th>Date/Time</th>
              <th>Course</th>
              <th>Unit</th>
              <th>Register (NIC)</th>
              <th>Name</th>
              <th>Score (100)</th>
              <th>Grade</th>
            </tr>
          </thead>
          <tbody>
          <?php if ($list && $list->num_rows): ?>
            <?php while($r = $list->fetch_assoc()): ?>
              <?php
                $mark100 = normalize_mark100_from_row($r, $hasRPct, $hasRMark100, $hasRTotal);

                $grade = $hasRGrade ? trim((string)($r['grade'] ?? '')) : '';
                if ($grade === '') {
                    $grade = grade_from_mark100($mark100);
                }
              ?>
              <tr>
                <td><?= h($hasRDate ? ($r['date_taken'] ?? '') : $r['id']) ?></td>
                <td><?= h($r['e_course'] ?? '') ?></td>
                <td><?= h($r['e_unit'] ?? '') ?></td>
                <td><?= h($r['nic_number'] ?? '') ?></td>
                <td><?= h($r['name'] ?? '') ?></td>
                <td><strong><?= number_format($mark100, 2) ?></strong></td>
                <td><span class="badge <?= grade_badge_class($grade) ?>"><?= h($grade) ?></span></td>
              </tr>
            <?php endwhile; ?>
          <?php else: ?>
            <tr><td colspan="7" class="text-center p-4">No results for selected filters.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script>
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 current = "<?= h($unit) ?>";
    if (current) $('#unit_exam').val(current);
  }).fail(function(){
    alert('Failed to load units.');
  });
}

$('#course_name').on('change', function(){
  $('#unit_exam').val('');
  loadUnits();
});

$('#refreshUnits').on('click', loadUnits);

$(function(){
  if ($('#course_name').val()) loadUnits();
});
</script>
</body>
</html>