<?php declare(strict_types=1);
/* Student full report: quizzes + assessments
 * Path (direct):           /elms/admin/reports/student_full_report.php
 * Path (with student_id):  /elms/admin/reports/student_full_report.php?student_id=###
 */
define('APP_ROOT', dirname(__DIR__, 2));
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';

if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  if (isset($conn) && $conn instanceof mysqli) { $mysqli = $conn; }
}
@$mysqli->set_charset('utf8mb4');

/* ------------ helpers -------------- */
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
function table_exists(mysqli $db, string $t): bool {
  $t=$db->real_escape_string($t);
  if ($rs=$db->query("SHOW TABLES LIKE '{$t}'")) { $ok=$rs->num_rows>0; $rs->free(); return $ok; }
  return false;
}
function col_exists(mysqli $db, string $t, string $c): bool {
  $t=$db->real_escape_string($t); $c=$db->real_escape_string($c);
  if ($rs=$db->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'")) { $ok=$rs->num_rows>0; $rs->free(); return $ok; }
  return 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';
}

/* ------------ choose tables (basic fallbacks) -------------- */
$T_STU = table_exists($mysqli,'studentss') ? 'studentss' : (table_exists($mysqli,'students') ? 'students' : 'studentss');
$T_QATT = table_exists($mysqli,'lms_quiz_attempts') ? 'lms_quiz_attempts' : 'quiz_attempts';
$T_QUIZ = table_exists($mysqli,'lms_quizzes') ? 'lms_quizzes' : 'quizzes';
$T_QQUE = table_exists($mysqli,'lms_quiz_questions') ? 'lms_quiz_questions' : (table_exists($mysqli,'quiz_questions') ? 'quiz_questions' : null);
$T_SUB  = table_exists($mysqli,'lms_submissions') ? 'lms_submissions' : 'submissions';
$T_ASSE = table_exists($mysqli,'lms_assessments') ? 'lms_assessments' : 'assessments';

/* ------------ column labels -------------- */
$studNameCol = label_col($mysqli,$T_STU, ['full_name','name','title']);
$studRefCol  = label_col($mysqli,$T_STU, ['reference_no','student_ref','reg_no','ref_no']);
$assTitleCol = label_col($mysqli,$T_ASSE,['title','name']);
$quizTitleCol= label_col($mysqli,$T_QUIZ,['title','name']);

/* If student_id is missing: show a small search picker instead of error */
$student_id = (int)($_GET['student_id'] ?? 0);
if ($student_id <= 0) {
  $PAGE_TITLE  = 'Student Full Report';
  $ACTIVE_MENU = 'reports';
  require APP_ROOT . '/includes/admin_layout_top.php';

  $q = trim((string)($_GET['q'] ?? ''));
  $rows = [];
  if ($q !== '') {
    // Build a simple search across name and ref columns
    $like = '%'.$q.'%';
    $sql = "SELECT id, `$studNameCol` AS nm, COALESCE(`$studRefCol`,'') AS refv
            FROM `$T_STU`
            WHERE `$studNameCol` LIKE ? OR `$studRefCol` LIKE ?
            ORDER BY `$studNameCol` ASC
            LIMIT 50";
    $st = $mysqli->prepare($sql);
    $st->bind_param('ss', $like, $like);
    $st->execute();
    $rows = $st->get_result()->fetch_all(MYSQLI_ASSOC);
    $st->close();
  }
  ?>
  <div class="card">
    <div class="card-header"><strong>Pick a Student</strong></div>
    <div class="card-body">
      <form class="row g-2" method="get">
        <div class="col-md-6">
          <label class="form-label">Search by name or reference</label>
          <input type="text" class="form-control" name="q" value="<?= esc($q) ?>" placeholder="Type name, reg no, or reference…">
        </div>
        <div class="col-md-2 d-grid align-items-end">
          <button class="btn btn-primary mt-4">Search</button>
        </div>
        <div class="col-md-2 d-grid align-items-end">
          <a class="btn btn-outline-secondary mt-4" href="student_full_report.php">Reset</a>
        </div>
      </form>

      <?php if ($q !== ''): ?>
        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead class="table-light"><tr><th>ID</th><th>Name</th><th>Ref</th><th></th></tr></thead>
            <tbody>
            <?php if(!$rows): ?>
              <tr><td colspan="4" class="text-muted text-center">No students found.</td></tr>
            <?php else: foreach($rows as $r): ?>
              <tr>
                <td>#<?= (int)$r['id'] ?></td>
                <td><?= esc($r['nm']) ?></td>
                <td><?= esc($r['refv']) ?></td>
                <td class="text-end">
                  <a class="btn btn-sm btn-primary" href="student_full_report.php?student_id=<?= (int)$r['id'] ?>">Open report</a>
                </td>
              </tr>
            <?php endforeach; endif; ?>
            </tbody>
          </table>
        </div>
      <?php endif; ?>
    </div>
  </div>
  <?php
  require APP_ROOT . '/includes/admin_layout_bottom.php';
  exit;
}

/* ---------- From here: we have a student_id, render the full report ---------- */

/* Student row */
$student=null;
$st=$mysqli->prepare("SELECT id, `$studNameCol` AS nm, COALESCE(`$studRefCol`,'') AS refv FROM `$T_STU` WHERE id=?");
$st->bind_param('i',$student_id); $st->execute(); $student=$st->get_result()->fetch_assoc(); $st->close();
if(!$student){
  $PAGE_TITLE='Student Full Report';
  $ACTIVE_MENU='reports';
  require APP_ROOT . '/includes/admin_layout_top.php';
  echo '<div class="alert alert-danger">Student not found.</div>';
  require APP_ROOT . '/includes/admin_layout_bottom.php';
  exit;
}

/* Quizzes: take latest attempt per quiz for this student */
$pointsCol = ($T_QQUE && col_exists($mysqli,$T_QQUE,'points')) ? 'points' : ( ($T_QQUE && col_exists($mysqli,$T_QQUE,'mark')) ? 'mark' : null );
$qpJoin = '';
if ($T_QQUE && $pointsCol) {
  $qpJoin = "LEFT JOIN (SELECT quiz_id, SUM($pointsCol) AS max_points FROM `$T_QQUE` GROUP BY quiz_id) qp ON qp.quiz_id=a.quiz_id";
} else {
  $qpJoin = "LEFT JOIN (SELECT NULL AS quiz_id, NULL AS max_points) qp ON 1=1";
}

$qz = $mysqli->prepare("
SELECT q.id AS quiz_id, q.`$quizTitleCol` 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 `$T_QATT` qa
  JOIN (
    SELECT quiz_id, student_id, MAX(COALESCE(finished_at, created_at)) AS last_time
    FROM `$T_QATT` WHERE student_id=?
    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 `$T_QUIZ` q ON q.id=a.quiz_id
$qpJoin
ORDER BY q.`$quizTitleCol`
");
$qz->bind_param('i',$student_id); $qz->execute(); $quizzes=$qz->get_result()->fetch_all(MYSQLI_ASSOC); $qz->close();

/* Assessments: latest submission per assessment for this student */
$as = $mysqli->prepare("
SELECT a.id AS assessment_id, a.`$assTitleCol` AS assessment_title,
       ls.batch_id,
       COALESCE(ls.final_score, ls.provisional_score, ls.score) AS score,
       COALESCE(ls.workflow_status, ls.status) AS workflow_status,
       COALESCE(ls.updated_at, ls.graded_at, ls.submitted_at, ls.created_at) AS last_action_at
FROM (
  SELECT s.*
  FROM `$T_SUB` s
  JOIN (
    SELECT assessment_id, student_id,
           MAX(COALESCE(updated_at, graded_at, submitted_at, created_at)) AS last_time
    FROM `$T_SUB` WHERE student_id=?
    GROUP BY assessment_id, student_id
  ) t ON t.assessment_id=s.assessment_id AND t.student_id=s.student_id
     AND t.last_time=COALESCE(s.updated_at, s.graded_at, s.submitted_at, s.created_at)
) ls
JOIN `$T_ASSE` a ON a.id=ls.assessment_id
ORDER BY a.`$assTitleCol`
");
$as->bind_param('i',$student_id); $as->execute(); $assess=$as->get_result()->fetch_all(MYSQLI_ASSOC); $as->close();

$PAGE_TITLE='Student 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">Student Full Report</h1>
  <a class="btn btn-sm btn-outline-secondary ms-auto" href="student_full_report.php">Pick another student</a>
</div>

<div class="alert alert-light border">
  <strong>Student:</strong> <?= esc($student['nm']) ?>
  <span class="text-muted">#<?= (int)$student['id'] ?></span>
  <?php if(!empty($student['refv'])): ?>
    <span class="ms-2">Ref: <span class="text-muted"><?= esc($student['refv']) ?></span></span>
  <?php endif; ?>
</div>

<div class="row g-3">
  <div class="col-xl-6">
    <div class="card">
      <div class="card-header"><strong>Quizzes (latest attempt)</strong></div>
      <div class="card-body table-responsive">
        <table class="table table-sm align-middle mb-0">
          <thead class="table-light"><tr><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(!$quizzes): ?><tr><td colspan="6" class="text-muted text-center">No quiz attempts.</td></tr>
          <?php else: foreach($quizzes as $r): ?>
            <tr>
              <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(($r['percent']!==null)?number_format((float)$r['percent'],2):'—') ?></td>
              <td><?= ($r['passed'] ?? null) ? '<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>
    </div>
  </div>

  <div class="col-xl-6">
    <div class="card">
      <div class="card-header"><strong>Assessments / Projects (latest submission)</strong></div>
      <div class="card-body table-responsive">
        <table class="table table-sm align-middle mb-0">
          <thead class="table-light"><tr><th>Assessment</th><th>Batch</th><th class="text-end">Score</th><th>Status</th><th>Last Action</th></tr></thead>
          <tbody>
          <?php if(!$assess): ?><tr><td colspan="5" class="text-muted text-center">No submissions.</td></tr>
          <?php else: foreach($assess as $r): ?>
            <tr>
              <td><?= esc($r['assessment_title']) ?> <span class="text-muted">#<?= (int)$r['assessment_id'] ?></span></td>
              <td><?= (int)$r['batch_id'] ?></td>
              <td class="text-end"><?= esc($r['score']) ?></td>
              <td class="text-uppercase small"><?= esc($r['workflow_status']) ?></td>
              <td class="small text-muted"><?= esc($r['last_action_at']) ?></td>
            </tr>
          <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>
<?php require APP_ROOT . '/includes/admin_layout_bottom.php';
