<?php
// admin/reports/final_quiz.php — Final/Last Quiz Attempts per Student
if (session_status() === PHP_SESSION_NONE) { session_start(); }

require_once __DIR__ . '/../../includes/auth.php';
require_role(['super_admin','branch_admin']);
require_once __DIR__ . '/../../includes/db.php';
require_once __DIR__ . '/../../includes/helpers.php';  // must contain ensure_quiz_schema($mysqli)
ensure_quiz_schema($mysqli);

function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8'); }

/* ------------ small helpers ------------ */
function table_exists_local($t){ global $mysqli;
  try{$st=$mysqli->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?");
      $st->bind_param('s',$t); $st->execute(); $ok=(bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
  }catch(Throwable $e){ return false; }
}
function column_exists_local($t,$c){ global $mysqli;
  try{$st=$mysqli->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?");
      $st->bind_param('ss',$t,$c); $st->execute(); $ok=(bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
  }catch(Throwable $e){ return false; }
}
function sql_all(string $sql, ?callable $binder = null): array { global $mysqli;
  $out=[]; $st=$mysqli->prepare($sql); if($binder) $binder($st); $st->execute();
  $r=$st->get_result(); while($row=$r->fetch_assoc()) $out[]=$row; $st->close(); return $out;
}
function sql_one(string $sql, ?callable $binder=null): ?array { $a = sql_all($sql,$binder); return $a[0]??null; }

/* ------------ resolve course/labels ------------ */
$course_id = (int)($_GET['course_id'] ?? 0);
$quiz_id   = (int)($_GET['quiz_id'] ?? 0);
$export    = isset($_GET['export']);

$COURSE_LABEL_COL = 'id';
if (table_exists_local('courses')){
  if (column_exists_local('courses','course_name')) $COURSE_LABEL_COL='course_name';
  else if (column_exists_local('courses','name'))   $COURSE_LABEL_COL='name';
  else if (column_exists_local('courses','title'))  $COURSE_LABEL_COL='title';
}

/* Build course list for the picker */
$COURSES = [];
if (table_exists_local('courses')){
  $COURSES = sql_all("SELECT id, $COURSE_LABEL_COL AS label FROM courses ORDER BY id DESC");
}

/* If no course_id given, render picker UI and exit */
if ($course_id <= 0) {
  $__layoutOpened=false;
  foreach(['../../includes/admin_layout_top.php','../../includes/layout_top.php'] as $f){
    $p=__DIR__.'/'.$f; if(is_file($p)){ require_once $p; $__layoutOpened=true; break; }
  }
  if(!$__layoutOpened) echo '<div class="container p-3">';

  echo '<h1 class="h5 mb-3">Final Quiz Report</h1>';
  if (!$COURSES){
    echo '<div class="alert alert-warning">No courses found. Please create a course first.</div>';
  } else {
    echo '<form method="get" class="row g-3">';
    echo '<div class="col-md-6"><label class="form-label">Course</label><select class="form-select" name="course_id" required>';
    echo '<option value="">Select a course…</option>';
    foreach($COURSES as $c){
      echo '<option value="'.(int)$c['id'].'">'.esc($c['label']).'</option>';
    }
    echo '</select></div>';
    echo '<div class="col-12"><button class="btn btn-primary">Load report</button></div>';
    echo '</form>';
  }

  $__layoutClosed=false;
  foreach(['../../includes/admin_layout_bottom.php','../../includes/layout_bottom.php'] as $f){
    $p=__DIR__.'/'.$f; if(is_file($p)){ require_once $p; $__layoutClosed=true; break; }
  }
  if(!$__layoutClosed) echo '</div>';
  exit;
}

/* ------------ get course & quizzes ------------ */
$COURSE = sql_one("SELECT id, $COURSE_LABEL_COL AS label FROM courses WHERE id=?", fn($s)=>$s->bind_param('i',$course_id));

$QUIZZES = sql_all(
  "SELECT id, title,
          COALESCE(updated_at, created_at, CURRENT_TIMESTAMP) AS ts
   FROM lms_quizzes WHERE course_id=? ORDER BY ts DESC, id DESC",
  fn($s)=>$s->bind_param('i',$course_id)
);

/* choose latest quiz if none provided */
if ($quiz_id<=0 && $QUIZZES){ $quiz_id = (int)$QUIZZES[0]['id']; }

$QUIZ = $quiz_id>0
  ? sql_one("SELECT * FROM lms_quizzes WHERE id=? AND course_id=?", fn($s)=>$s->bind_param('ii',$quiz_id,$course_id))
  : null;

/* ------------ find student table/label ------------ */
$STUD_TBL = null; $STUD_ID='id'; $STUD_NAME = null;
foreach (['students','lms_students','users'] as $t){
  if (!table_exists_local($t)) continue;
  if (!column_exists_local($t,'id')) continue;
  $STUD_TBL = $t;
  if     (column_exists_local($t,'full_name'))  $STUD_NAME='full_name';
  elseif (column_exists_local($t,'name'))       $STUD_NAME='name';
  elseif (column_exists_local($t,'first_name') || column_exists_local($t,'last_name')) $STUD_NAME='__split__';
  else   $STUD_NAME=null;
  break;
}

/* ------------ pull last attempts for selected quiz ------------ */
$attempts = [];
if ($QUIZ){
  // most recent attempt per student
  $attempts = sql_all(
    "SELECT a.student_id, a.score, a.total, a.percent, a.passed,
            COALESCE(a.finished_at, a.created_at) AS finished_at
     FROM lms_quiz_attempts a
     JOIN (
        SELECT student_id, MAX(COALESCE(finished_at, created_at)) AS last_dt
        FROM lms_quiz_attempts
        WHERE quiz_id=?
        GROUP BY student_id
     ) x ON x.student_id=a.student_id
        AND COALESCE(a.finished_at, a.created_at)=x.last_dt
     WHERE a.quiz_id=?
     ORDER BY a.percent DESC, finished_at DESC",
    fn($s)=>$s->bind_param('ii',$quiz_id,$quiz_id)
  );

  // decorate with student names if possible
  if ($attempts && $STUD_TBL){
    $ids = array_map(fn($r)=>(int)$r['student_id'], $attempts);
    $ids = array_values(array_unique($ids));
    if ($ids){
      $in  = implode(',', array_fill(0, count($ids), '?'));
      $typ = str_repeat('i', count($ids));
      $cols = "$STUD_ID AS id";
      if ($STUD_NAME === '__split__'){
        $first = column_exists_local($STUD_TBL,'first_name') ? 'first_name' : 'firstname';
        $last  = column_exists_local($STUD_TBL,'last_name') ? 'last_name' : 'lastname';
        $cols .= ", TRIM(CONCAT(IFNULL($first,''),' ',IFNULL($last,''))) AS nm";
      } elseif ($STUD_NAME) {
        $cols .= ", $STUD_NAME AS nm";
      } else {
        $cols .= ", '' AS nm";
      }
      $sql = "SELECT $cols FROM `$STUD_TBL` WHERE id IN ($in)";
      $map = [];
      $rows = sql_all($sql, function($s) use($typ,$ids){ $s->bind_param($typ, ...$ids); });
      foreach($rows as $r){ $map[(int)$r['id']] = $r['nm'] ?? ''; }
      foreach($attempts as &$a){ $a['student_name'] = $map[(int)$a['student_id']] ?? ''; }
      unset($a);
    }
  }
}



/* ------------ CSV export ------------ */
if ($export) {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="final_quiz_course'.$course_id.'_quiz'.$quiz_id.'.csv"');
  $out = fopen('php://output', 'w');
  fputcsv($out, ['Student ID','Student Name','Score','Total','Percent','Passed','Finished At','Course','Quiz']);
  foreach ($attempts as $r){
    fputcsv($out, [
      $r['student_id'],
      $r['student_name'] ?? '',
      $r['score'],
      $r['total'],
      $r['percent'],
      (is_null($r['passed'])?'':($r['passed']?'Yes':'No')),
      $r['finished_at'],
      $COURSE['label'] ?? ('#'.$course_id),
      $QUIZ['title'] ?? ''
    ]);
  }
  fclose($out);
  exit;
}

/* ------------ Render ------------ */
$__layoutOpened=false;
foreach(['../../includes/admin_layout_top.php','../../includes/layout_top.php'] as $f){
  $p=__DIR__.'/'.$f; if(is_file($p)){ require_once $p; $__layoutOpened=true; break; }
}
if(!$__layoutOpened) echo '<div class="container p-3">';

?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <h1 class="h5 m-0">Final Quiz Report</h1>
  <div>
    <a class="btn btn-outline-secondary btn-sm" href="../courses/modules.php?course_id=<?= (int)$course_id ?>">← Back to Modules</a>
  </div>
</div>

<form method="get" class="row g-2 mb-3">
  <div class="col-md-5">
    <label class="form-label">Course</label>
    <select name="course_id" class="form-select" onchange="this.form.submit()">
      <?php foreach($COURSES as $c): ?>
        <option value="<?= (int)$c['id'] ?>" <?= (int)$c['id']===$course_id?'selected':'' ?>>
          <?= esc($c['label']) ?>
        </option>
      <?php endforeach; ?>
    </select>
  </div>
  <div class="col-md-5">
    <label class="form-label">Quiz</label>
    <select name="quiz_id" class="form-select" onchange="this.form.submit()">
      <?php if(!$QUIZZES): ?>
        <option value="">— no quizzes in this course —</option>
      <?php else: foreach($QUIZZES as $q): ?>
        <option value="<?= (int)$q['id'] ?>" <?= (int)$q['id']===$quiz_id?'selected':'' ?>>
          <?= esc($q['title']) ?>
        </option>
      <?php endforeach; endif; ?>
    </select>
  </div>
  <div class="col-md-2 d-flex align-items-end">
    <a class="btn btn-success w-100 <?= $QUIZ?'':'disabled' ?>"
       href="?course_id=<?= (int)$course_id ?>&quiz_id=<?= (int)$quiz_id ?>&export=1">Export CSV</a>
  </div>
</form>

<?php if(!$QUIZ): ?>
  <div class="alert alert-warning">No quiz selected (or none exist) for this course.</div>
<?php else: ?>
  <div class="card">
    <div class="card-header d-flex justify-content-between align-items-center">
      <div>
        <strong>Course:</strong> <?= esc($COURSE['label'] ?? ('#'.$course_id)) ?>
        <span class="mx-2">·</span>
        <strong>Quiz:</strong> <?= esc($QUIZ['title']) ?>
      </div>
      <div>
        <a class="btn btn-sm btn-outline-success" href="?course_id=<?= (int)$course_id ?>&quiz_id=<?= (int)$quiz_id ?>&export=1">Export CSV</a>
      </div>
    </div>
    <div class="table-responsive">
      <table class="table align-middle mb-0">
        <thead>
          <tr>
            <th style="width:90px">Student ID</th>
            <th>Student</th>
            <th class="text-end" style="width:110px">Score</th>
            <th class="text-end" style="width:110px">Percent</th>
            <th style="width:90px">Passed</th>
            <th style="width:180px">Finished</th>
          </tr>
        </thead>
        <tbody>
          <?php if(!$attempts): ?>
            <tr><td colspan="6" class="text-muted p-3">No attempts yet.</td></tr>
          <?php else: foreach($attempts as $r): ?>
            <tr>
              <td><?= (int)$r['student_id'] ?></td>
              <td><?= esc($r['student_name'] ?? '') ?></td>
              <td class="text-end"><?= (int)$r['score'] ?> / <?= (int)$r['total'] ?></td>
              <td class="text-end"><?= number_format((float)$r['percent'], 2) ?>%</td>
              <td>
                <?php if (is_null($r['passed'])): ?>
                  <span class="badge bg-secondary">—</span>
                <?php else: ?>
                  <span class="badge <?= $r['passed']?'bg-success':'bg-danger' ?>"><?= $r['passed']?'Yes':'No' ?></span>
                <?php endif; ?>
              </td>
              <td><?= esc($r['finished_at']) ?></td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>
    </div>
  </div>
<?php endif; ?>

<?php
$__layoutClosed=false;
foreach(['../../includes/admin_layout_bottom.php','../../includes/layout_bottom.php'] as $f){
  $p=__DIR__.'/'.$f; if(is_file($p)){ require_once $p; $__layoutClosed=true; break; }
}
if(!$__layoutClosed) echo '</div>';
