<?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 = isset($conn) ? $conn : (isset($mysqli) ? $mysqli : null);
if (!$db) die("DB connection missing.");
$db->set_charset('utf8mb4');

/* helpers */
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);
    $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");
    $st->bind_param("s", $table);
    $st->execute();
    $r = $st->get_result();
    $ok = ($r && $r->num_rows > 0);
    $st->close();
    return $ok;
}

/* 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;

/* date filter */
$hasRDate = hasColumn($db, 'results', 'date_taken');
if ($hasRDate && $start === '' && $end === '') {
    $start = date('Y-m-d');
    $end   = date('Y-m-d');
}

/* course list */
if (tableExists($db, 'courses')) {
    $course_rs = $db->query("SELECT name FROM courses ORDER BY name ASC");
} else {
    $course_rs = $db->query("SELECT DISTINCT course_name AS name FROM exams ORDER BY name ASC");
}

$hasRGrade = hasColumn($db, 'results', 'grade');

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

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

/* filters */
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";
}

/* SELECT */
$select = "
  SELECT
    r.id, r.score".
    ($hasRDate ? ", r.date_taken" : "").
    ($hasRGrade? ", r.grade"      : "").",
    s.nic_number, s.name,
    e.course_name AS e_course,
    e.unit_exam   AS e_unit
";

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

/* EXPORT CSV */
if ($export) {
    $stmt = $db->prepare($sql);
    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','NIC','Name','Score(100)','Grade']);

    while ($r = $res->fetch_assoc()) {
        $mark100 = (float)$r['score'];

        $grade = $hasRGrade ? ($r['grade'] ?? '') : '';
        if ($grade === '') {
            if      ($mark100 >= 75) $grade = 'A';
            elseif  ($mark100 >= 65) $grade = 'B';
            elseif  ($mark100 >= 55) $grade = 'C';
            elseif  ($mark100 >= 40) $grade = 'S';
            else                     $grade = 'F';
        }

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

/* NORMAL VIEW */
$stmt = $db->prepare($sql);
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>
<link href="../assets/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
<div class="container py-4">
<h3>Exam Results</h3>

<table class="table table-striped">
<thead>
<tr>
  <th>Date</th>
  <th>Course</th>
  <th>Unit</th>
  <th>NIC</th>
  <th>Name</th>
  <th>Score (100)</th>
  <th>Grade</th>
</tr>
</thead>
<tbody>
<?php while($r=$list->fetch_assoc()):
  $mark100 = (float)$r['score'];

  $grade = $hasRGrade ? ($r['grade'] ?? '') : '';
  if ($grade === '') {
      if      ($mark100 >= 75) $grade = 'A';
      elseif  ($mark100 >= 65) $grade = 'B';
      elseif  ($mark100 >= 55) $grade = 'C';
      elseif  ($mark100 >= 40) $grade = 'S';
      else                     $grade = 'F';
  }
?>
<tr>
  <td><?= htmlspecialchars($hasRDate ? $r['date_taken'] : $r['id']) ?></td>
  <td><?= htmlspecialchars($r['e_course']) ?></td>
  <td><?= htmlspecialchars($r['e_unit']) ?></td>
  <td><?= htmlspecialchars($r['nic_number']) ?></td>
  <td><?= htmlspecialchars($r['name']) ?></td>
  <td><strong><?= number_format($mark100,2) ?></strong></td>
  <td>
    <span class="badge <?= $mark100>=40?'bg-success':'bg-danger' ?>">
      <?= $grade ?>
    </span>
  </td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
</div>
</body>
</html>
