<?php declare(strict_types=1);
/* Quiz Report (latest attempt per student)
 * Path: /elms/admin/reports/quiz_report.php
 */
define('APP_ROOT', dirname(__DIR__, 2)); // .../elms
if (session_status() === PHP_SESSION_NONE) session_start();

require_once APP_ROOT . '/includes/auth.php';
require_role(['super_admin','admin','branch_admin','course_coordinator','coordinator','lecturer']);

require_once APP_ROOT . '/includes/db.php';

function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
function c_exists(mysqli $db,string $t,string $c):bool{
  $t=$db->real_escape_string($t); $c=$db->real_escape_string($c);
  $q=$db->query("SHOW COLUMNS FROM `$t` LIKE '$c'"); return $q?(bool)$q->num_rows:false;
}
function label_col(mysqli $db,string $t,array $cands):string{
  $cols=[]; if($rs=$db->query("SHOW COLUMNS FROM `$t`")) while($r=$rs->fetch_assoc()) $cols[]=$r['Field'];
  foreach($cands as $c) if(in_array($c,$cols,true)) return $c; return 'id';
}

/* -------- filters -------- */
$course_id = (int)($_GET['course_id'] ?? 0);
$quiz_id   = (int)($_GET['quiz_id']   ?? 0);
$qsearch   = trim((string)($_GET['q'] ?? ''));

$studNameCol = label_col($mysqli,'studentss',['full_name','name','title']);
$quizTitle   = label_col($mysqli,'lms_quizzes',['title','name']);

/* Build SQL (latest attempt + quiz max points) */
$where = []; $types = ''; $bind = [];

if ($quiz_id>0)   { $where[] = 'a.quiz_id=?'; $types.='i'; $bind[]=$quiz_id; }
if ($course_id>0 && c_exists($mysqli,'lms_quizzes','course_id')){
  $where[] = 'q.course_id=?'; $types.='i'; $bind[]=$course_id;
}
if ($qsearch!==''){
  $where[] = "s.$studNameCol LIKE CONCAT('%',?,'%')";
  $types.='s'; $bind[]=$qsearch;
}
$WHERE = $where ? ('WHERE '.implode(' AND ',$where)) : '';

$sql = "
SELECT
  s.id AS student_id,
  s.$studNameCol AS student_name,
  q.id AS quiz_id,
  q.$quizTitle AS quiz_title,
  a.score,
  COALESCE(a.total, qp.max_points) AS total_points,
  ROUND(100 * a.score / NULLIF(COALESCE(a.total, qp.max_points),0), 2) AS percent,
  a.passed,
  COALESCE(a.finished_at, a.created_at) AS attempted_at
FROM (
  SELECT qa.*
  FROM lms_quiz_attempts qa
  JOIN (
    SELECT quiz_id, student_id,
           MAX(COALESCE(finished_at, created_at)) AS last_time
    FROM lms_quiz_attempts
    GROUP BY quiz_id, student_id
  ) t
    ON t.quiz_id=qa.quiz_id
   AND t.student_id=qa.student_id
   AND t.last_time=COALESCE(qa.finished_at, qa.created_at)
) a
JOIN lms_quizzes q       ON q.id=a.quiz_id
JOIN studentss s         ON s.id=a.student_id
LEFT JOIN (
  SELECT quiz_id, SUM(points) AS max_points
  FROM lms_quiz_questions
  GROUP BY quiz_id
) qp ON qp.quiz_id=a.quiz_id
$WHERE
ORDER BY s.$studNameCol, q.$quizTitle
LIMIT 1000";

$rows=[];
$st=$mysqli->prepare($sql);
if($types!=='') $st->bind_param($types, ...$bind);
$st->execute(); $res=$st->get_result();
while($r=$res->fetch_assoc()) $rows[]=$r;
$st->close();

/* CSV export */
if (isset($_GET['export']) && $_GET['export']==='csv'){
  header('Content-Type: text/csv; charset=UTF-8');
  header('Content-Disposition: attachment; filename="quiz_report.csv"');
  $out=fopen('php://output','w');
  fputcsv($out,['Student ID','Student','Quiz ID','Quiz','Score','Total','Percent','Passed','Attempted At']);
  foreach($rows as $r){
    fputcsv($out,[
      $r['student_id'], $r['student_name'], $r['quiz_id'], $r['quiz_title'],
      $r['score'], $r['total_points'], $r['percent'], $r['passed'], $r['attempted_at']
    ]);
  }
  fclose($out); exit;
}

$PAGE_TITLE='Quiz Report';
$ACTIVE_MENU='reports';
require APP_ROOT . '/includes/admin_layout_top.php';
?>
<div class="d-flex align-items-center mb-3">
  <h1 class="h5 m-0">Quiz Report (latest attempt)</h1>
  <a class="btn btn-sm btn-outline-secondary ms-auto" href="?<?= http_build_query(array_merge($_GET,['export'=>'csv'])) ?>">Export CSV</a>
</div>

<form class="row g-2 mb-3" method="get">
  <div class="col-md-2"><input class="form-control" name="course_id" type="number" value="<?= (int)$course_id ?>" placeholder="Course ID"></div>
  <div class="col-md-2"><input class="form-control" name="quiz_id" type="number" value="<?= (int)$quiz_id ?>" placeholder="Quiz ID"></div>
  <div class="col-md-4"><input class="form-control" name="q" value="<?= esc($qsearch) ?>" placeholder="Search student name"></div>
  <div class="col-md-2 d-grid"><button class="btn btn-primary">Filter</button></div>
</form>

<div class="table-responsive">
  <table class="table table-sm table-striped align-middle">
    <thead class="table-light">
      <tr>
        <th>Student</th><th>Quiz</th><th class="text-end">Score</th><th class="text-end">Total</th><th class="text-end">%</th><th>Passed</th><th>Attempted</th>
      </tr>
    </thead>
    <tbody>
      <?php if(!$rows): ?>
        <tr><td colspan="7" class="text-center text-muted">No results.</td></tr>
      <?php else: foreach($rows as $r): ?>
        <tr>
          <td><?= esc($r['student_name']) ?> <span class="text-muted">#<?= (int)$r['student_id'] ?></span></td>
          <td><?= esc($r['quiz_title']) ?> <span class="text-muted">#<?= (int)$r['quiz_id'] ?></span></td>
          <td class="text-end"><?= esc($r['score']) ?></td>
          <td class="text-end"><?= esc($r['total_points']) ?></td>
          <td class="text-end"><?= esc(number_format((float)$r['percent'],2)) ?></td>
          <td><?= $r['passed'] ? '<span class="badge bg-success">PASS</span>':'<span class="badge bg-secondary">—</span>' ?></td>
          <td class="small text-muted"><?= esc($r['attempted_at']) ?></td>
        </tr>
      <?php endforeach; endif; ?>
    </tbody>
  </table>
</div>
<?php require APP_ROOT . '/includes/admin_layout_bottom.php';
