<?php
// Final Quiz Report (course-level, one row per student: latest attempt in course)
// Path: /elms/admin/reports/final_quiz.php

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';
if (is_file(__DIR__.'/../../includes/helpers.php')) require_once __DIR__.'/../../includes/helpers.php';

/* ---------- small safe helpers ---------- */
if (!function_exists('esc')) {
  function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8'); }
}
if (!function_exists('table_exists_local')) {
  function table_exists_local($t){ global $mysqli;
    try{$st=$mysqli->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? LIMIT 1");
      $st->bind_param('s',$t); $st->execute(); $ok=(bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
    }catch(Throwable $e){return false;}
  }
}
if (!function_exists('column_exists_local')) {
  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=? LIMIT 1");
      $st->bind_param('ss',$t,$c); $st->execute(); $ok=(bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
    }catch(Throwable $e){return false;}
  }
}

/* ---------- emergency schema (no-op if already there) ---------- */
if (!function_exists('ensure_quiz_schema')) {
  function ensure_quiz_schema(){
    global $mysqli;
    $mysqli->query("CREATE TABLE IF NOT EXISTS lms_quizzes (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      course_id INT UNSIGNED NOT NULL,
      module_id INT UNSIGNED NOT NULL,
      title VARCHAR(255) NOT NULL,
      description TEXT NULL,
      time_limit_sec INT NULL,
      max_attempts INT NULL,
      pass_score INT NULL,
      randomized TINYINT(1) NOT NULL DEFAULT 0,
      active TINYINT(1) NOT NULL DEFAULT 1,
      created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $mysqli->query("CREATE TABLE IF NOT EXISTS lms_quiz_questions (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      quiz_id INT UNSIGNED NOT NULL,
      question_text TEXT NOT NULL,
      points INT NOT NULL DEFAULT 1,
      sort_order INT NOT NULL DEFAULT 0,
      KEY idx_quiz (quiz_id, sort_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $mysqli->query("CREATE TABLE IF NOT EXISTS lms_quiz_options (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      question_id INT UNSIGNED NOT NULL,
      option_text TEXT NOT NULL,
      is_correct TINYINT(1) NOT NULL DEFAULT 0,
      sort_order INT NOT NULL DEFAULT 0,
      KEY idx_q (question_id, sort_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $mysqli->query("CREATE TABLE IF NOT EXISTS lms_quiz_attempts (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      quiz_id INT UNSIGNED NOT NULL,
      student_id INT UNSIGNED NOT NULL,
      attempt_no INT NOT NULL DEFAULT 1,
      started_at DATETIME NULL,
      finished_at DATETIME NULL,
      score INT NULL,
      max_score INT NULL,
      percentage DECIMAL(6,2) NULL,
      passed TINYINT(1) NULL,
      KEY idx_qs (quiz_id, student_id),
      KEY idx_finished (finished_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $mysqli->query("CREATE TABLE IF NOT EXISTS lms_quiz_answers (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      attempt_id INT UNSIGNED NOT NULL,
      question_id INT UNSIGNED NOT NULL,
      option_id INT UNSIGNED NULL,
      correct TINYINT(1) NULL,
      points_awarded INT NULL,
      KEY idx_attempt (attempt_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  }
}
ensure_quiz_schema();

/* ---------- inputs ---------- */
$course_id  = (int)($_GET['course_id'] ?? 0);
$final_only = isset($_GET['final_only']) ? (int)$_GET['final_only'] : 1; // default: only "Final" quizzes
$export     = isset($_GET['export']) ? 1 : 0;

/* ---------- course list (for selector if no course_id) ---------- */
$courses = [];
$courseLabel = null;
if (table_exists_local('courses')) {
  $cname = column_exists_local('courses','course_name') ? 'course_name'
         : (column_exists_local('courses','name') ? 'name'
         : (column_exists_local('courses','title') ? 'title' : 'id'));
  $res = $mysqli->query("SELECT id, $cname AS label FROM courses ORDER BY id DESC");
  while($r=$res->fetch_assoc()) $courses[]=$r;
  if ($course_id>0){
    $st=$mysqli->prepare("SELECT $cname AS label FROM courses WHERE id=?");
    $st->bind_param('i',$course_id); $st->execute(); $r=$st->get_result()->fetch_assoc(); $st->close();
    $courseLabel = $r['label'] ?? ('#'.$course_id);
  }
}

/* ---------- when a course is chosen: fetch one row per student (latest attempt) ---------- */
$rows = [];
if ($course_id>0) {
  // Anti-join to keep only the latest attempt per student in this course.
  // "Latest" = greater finished_at (NULL treated as far future), tie-breaker higher id.
  $andFinal = $final_only ? "AND (q.title LIKE '%final%' OR q.title LIKE '%Final%' OR q.title LIKE '%FINAL%')" : "";

  $sql = "
  SELECT a1.id AS attempt_id, a1.student_id, a1.quiz_id, a1.attempt_no,
         a1.started_at, a1.finished_at, a1.score, a1.max_score, a1.percentage, a1.passed,
         q.title AS quiz_title, q.pass_score,
         s_label.n AS student_name
  FROM lms_quiz_attempts a1
  JOIN lms_quizzes q ON q.id = a1.quiz_id
  /* student name best-effort */
  LEFT JOIN (
    SELECT id,
      /* try to pick a name-like column */
      COALESCE(
        " . (table_exists_local('students') && column_exists_local('students','full_name') ? "full_name" :
           (table_exists_local('students') && column_exists_local('students','name') ? "name" :
           (table_exists_local('students') && column_exists_local('students','student_name') ? "student_name" :
           (table_exists_local('students') && column_exists_local('students','first_name') ? "CONCAT(first_name,' ',COALESCE(last_name,''))" : "' '"
        )))) . "
      ) AS n
    FROM " . (table_exists_local('students') ? "students" : "(SELECT 0 id, '' n) x") . "
  ) s_label ON s_label.id = a1.student_id
  LEFT JOIN lms_quiz_attempts a2
    ON a2.student_id = a1.student_id
   AND a2.quiz_id IN (SELECT id FROM lms_quizzes WHERE course_id = ? $andFinal)
   AND (
        COALESCE(a2.finished_at,'9999-12-31') >  COALESCE(a1.finished_at,'9999-12-31')
        OR (
             COALESCE(a2.finished_at,'9999-12-31') = COALESCE(a1.finished_at,'9999-12-31')
             AND a2.id > a1.id
           )
       )
  WHERE q.course_id = ? $andFinal
    AND a2.id IS NULL
  ORDER BY a1.finished_at DESC, a1.id DESC";

  $st=$mysqli->prepare($sql);
  $st->bind_param('ii',$course_id,$course_id);
  $st->execute(); $res=$st->get_result();
  while($r=$res->fetch_assoc()){
    // Compute pass if null but pass_score exists
    if ($r['passed']===null && $r['pass_score']!==null) {
      $r['passed'] = ((int)$r['score'] >= (int)$r['pass_score']) ? 1 : 0;
    }
    $rows[]=$r;
  }
  $st->close();

  if ($export){
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=final_quiz_course_'.$course_id.'.csv');
    $out=fopen('php://output','w');
    fputcsv($out, ['Student ID','Student','Quiz','Attempt #','Score','Max','%','Pass','Started','Finished','Attempt ID']);
    foreach($rows as $r){
      fputcsv($out, [
        $r['student_id'],
        $r['student_name'] ?: ('Student #'.$r['student_id']),
        $r['quiz_title'],
        $r['attempt_no'],
        (int)$r['score'],
        (int)$r['max_score'],
        number_format((float)$r['percentage'],2),
        ($r['passed'] ? 'PASS' : 'FAIL'),
        $r['started_at'],
        $r['finished_at'],
        $r['attempt_id'],
      ]);
    }
    fclose($out);
    exit;
  }
}

/* ---------- layout ---------- */
$PAGE_TITLE='Final Quiz Report';
$ACTIVE_MENU='reports';
$__layout=false;
foreach(['../../includes/admin_layout_top.php','../../includes/layout_top.php','../../includes/admin_header.php','../../includes/header_admin.php'] as $f){
  $p=__DIR__.'/'.$f; if(is_file($p)){ require_once $p; $__layout=true; break; }
}
if (!$__layout) 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>
  <?php if ($course_id>0): ?>
    <div class="d-flex align-items-center gap-2">
      <a class="btn btn-outline-secondary btn-sm" href="final_quiz.php">Change course</a>
      <a class="btn btn-sm btn-success" href="final_quiz.php?course_id=<?= (int)$course_id ?>&final_only=<?= (int)$final_only ?>&export=1">Export CSV</a>
    </div>
  <?php endif; ?>
</div>

<?php if ($course_id<=0): ?>
  <div class="card">
    <div class="card-header">Choose a course</div>
    <div class="card-body">
      <?php if (!$courses): ?>
        <div class="text-muted">No courses found.</div>
      <?php else: ?>
        <form class="row g-3" method="get">
          <div class="col-md-6">
            <label class="form-label">Course</label>
            <select class="form-select" name="course_id" required>
              <option value="">Select…</option>
              <?php foreach($courses as $c): ?>
                <option value="<?= (int)$c['id'] ?>"><?= esc($c['label'] ?? ('#'.$c['id'])) ?></option>
              <?php endforeach; ?>
            </select>
          </div>
          <div class="col-md-4">
            <label class="form-label">Only “Final” quizzes?</label>
            <select class="form-select" name="final_only">
              <option value="1" selected>Yes</option>
              <option value="0">No (show any quiz)</option>
            </select>
          </div>
          <div class="col-md-2 d-grid align-items-end">
            <button class="btn btn-primary">View report</button>
          </div>
        </form>
      <?php endif; ?>
    </div>
  </div>
<?php else: ?>
  <div class="card mb-3">
    <div class="card-header d-flex justify-content-between align-items-center">
      <div>
        <strong>Course:</strong> <?= esc($courseLabel ?? ('#'.$course_id)) ?>
        <span class="ms-3"><strong>Filter:</strong> <?= $final_only ? 'Only titles containing “Final”' : 'All quizzes in course' ?></span>
      </div>
      <form class="d-inline" method="get">
        <input type="hidden" name="course_id" value="<?= (int)$course_id ?>">
        <input type="hidden" name="final_only" value="<?= $final_only?0:1 ?>">
        <button class="btn btn-sm btn-outline-primary">Toggle Final-only</button>
      </form>
    </div>
    <div class="card-body p-0">
      <?php if (!$rows): ?>
        <div class="p-3 text-muted">No attempts found for this course with the current filter.</div>
      <?php else: ?>
        <div class="table-responsive">
          <table class="table table-sm align-middle mb-0">
            <thead>
              <tr>
                <th>Student</th>
                <th>Quiz</th>
                <th class="text-center">Attempt #</th>
                <th class="text-end">Score</th>
                <th class="text-end">%</th>
                <th class="text-center">Result</th>
                <th>Started</th>
                <th>Finished</th>
                <th class="text-end">Actions</th>
              </tr>
            </thead>
            <tbody>
              <?php foreach($rows as $r): ?>
                <tr>
                  <td>
                    <?= esc($r['student_name'] ?: ('Student #'.$r['student_id'])) ?>
                    <div class="text-muted small">ID <?= (int)$r['student_id'] ?></div>
                  </td>
                  <td><?= esc($r['quiz_title'] ?: ('Quiz #'.$r['quiz_id'])) ?></td>
                  <td class="text-center"><?= (int)$r['attempt_no'] ?></td>
                  <td class="text-end"><?= (int)$r['score'] ?> / <?= (int)$r['max_score'] ?></td>
                  <td class="text-end"><?= number_format((float)$r['percentage'],2) ?>%</td>
                  <td class="text-center">
                    <span class="badge <?= $r['passed'] ? 'bg-success' : 'bg-danger' ?>">
                      <?= $r['passed'] ? 'PASS' : 'FAIL' ?>
                    </span>
                  </td>
                  <td><?= esc($r['started_at'] ?? '—') ?></td>
                  <td><?= esc($r['finished_at'] ?? '—') ?></td>
                  <td class="text-end">
                    <a class="btn btn-sm btn-outline-primary"
                       href="final_quiz_student.php?attempt_id=<?= (int)$r['attempt_id'] ?>">
                      View
                    </a>
                  </td>
                </tr>
              <?php endforeach; ?>
            </tbody>
          </table>
        </div>
      <?php endif; ?>
    </div>
  </div>
<?php endif; ?>

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