<?php
/* /elms/student/results.php — student results table
   - Only the logged-in student's results are shown (staff may pass ?sid=STUDENT_ID).
   - Student name: studentss.full_name (no title); sensible fallbacks if empty.
   - Reg No: center_reg_no -> reference_no -> reg_no/reg_number/registration_no/student_no -> ID-####.
   - Filters, paging, CSV export.
*/

declare(strict_types=1);
if (session_status() === PHP_SESSION_NONE) session_start();
@date_default_timezone_set('Asia/Colombo');

/* ---------- small helpers ---------- */
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8'); }
function nfmt($n){ if ($n===null || $n==='') return '—'; $f=(float)$n; return (abs($f-(int)$f)<0.00001)?(string)(int)$f:number_format($f,2); }

/* ---------- DB bootstrap (robust) ---------- */
$ROOT = dirname(__DIR__); // /elms
$mysqli = null;

$attempts = [];
$tryFiles = [
  $ROOT.'/includes/db.php',
  $ROOT.'/includes/db_boot.php',
];
foreach ($tryFiles as $p){
  $attempts[] = $p.(is_file($p) ? ' [exists]' : ' [missing]');
  if (is_file($p)) { require_once $p; break; }
}

if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  if (defined('DB_HOST') && defined('DB_USER') && defined('DB_PASS') && defined('DB_NAME')) {
    $mysqli = @new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($mysqli && !$mysqli->connect_errno) {
      $mysqli->set_charset('utf8mb4');
      $mysqli->query("SET time_zone = '+05:30'");
      $attempts[] = '[manual connect] OK';
    } else {
      $attempts[] = '[manual connect] FAIL'.($mysqli?(': '.$mysqli->connect_error):'');
    }
  }
}
if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  http_response_code(500);
  echo "<h3>Database not initialized</h3><pre>".esc(implode("\n",$attempts))."</pre>";
  exit;
}
$mysqli->query("SET time_zone = '+05:30'");

/* ---------- table/column helpers ---------- */
function t_exists(mysqli $db,string $t):bool{
  $t=$db->real_escape_string($t);
  $r=$db->query("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$t' LIMIT 1");
  $ok=$r && $r->num_rows>0; if($r)$r->free(); return $ok;
}
function c_exists(mysqli $db,string $t,string $c):bool{
  $t=$db->real_escape_string($t); $c=$db->real_escape_string($c);
  $r=$db->query("SHOW COLUMNS FROM `$t` LIKE '$c'");
  $ok=$r && $r->num_rows>0; if($r)$r->free(); return $ok;
}
function fetch_all_stmt(mysqli_stmt $st):array{
  $rows=[];
  if (method_exists($st,'get_result')){
    $res=$st->get_result(); if($res){ while($x=$res->fetch_assoc()) $rows[]=$x; $res->free(); }
  } else {
    $meta=$st->result_metadata();
    if ($meta){ $fields=$meta->fetch_fields(); $m=[]; $row=[];
      foreach($fields as $f){ $row[$f->name]=null; $m[]=&$row[$f->name]; }
      call_user_func_array([$st,'bind_result'],$m);
      while($st->fetch()) $rows[]=array_map(fn($v)=>$v,$row);
    }
  }
  return $rows;
}
function marks_expr(mysqli $db):string{
  $hasTotal = t_exists($db,'lms_assessments') && c_exists($db,'lms_assessments','total_marks');
  $hasMax   = t_exists($db,'lms_assessments') && c_exists($db,'lms_assessments','max_score');
  if ($hasTotal && $hasMax) return "COALESCE(a.total_marks,a.max_score,100)";
  if ($hasTotal)            return "COALESCE(a.total_marks,100)";
  if ($hasMax)              return "COALESCE(a.max_score,100)";
  return "100";
}

/* ---------- auth: student by default; staff override with ?sid= ---------- */
$student_ok = false; $staff_ok = false;
$student_id = (int)($_SESSION['student_id'] ?? 0);       // your student session key
if ($student_id>0) $student_ok=true;

$role = strtolower((string)($_SESSION['auth']['role'] ?? $_SESSION['user']['role'] ?? ''));
if (in_array($role, ['lecturer','coordinator','admin','super_admin'], true)) $staff_ok=true;

$target_sid = 0;
if ($student_ok) {
  $target_sid = $student_id;
} elseif ($staff_ok && isset($_GET['sid'])) {
  $target_sid = max(0,(int)$_GET['sid']);
} else {
  // important: use the real login route in your system
  $next = rawurlencode($_SERVER['REQUEST_URI'] ?? '/elms/student/results.php');
  header('Location: /elms/auth/login.php?next='.$next);
  exit;
}

/* ---------- schema presence ---------- */
if (!t_exists($mysqli,'lms_submissions') || !t_exists($mysqli,'lms_assessments')) {
  http_response_code(500); echo "Missing tables."; exit;
}

$HAS_FINAL   = c_exists($mysqli,'lms_submissions','final_score');
$HAS_PROV    = c_exists($mysqli,'lms_submissions','provisional_score');
$HAS_SCORE   = c_exists($mysqli,'lms_submissions','score');
$HAS_STATUS  = c_exists($mysqli,'lms_submissions','workflow_status');
$HAS_RELEASE = c_exists($mysqli,'lms_submissions','release_at');
$HAS_UPDATED = c_exists($mysqli,'lms_submissions','updated_at');
$HAS_SUBMIT  = c_exists($mysqli,'lms_submissions','submitted_at');
$HAS_BATCHID = c_exists($mysqli,'lms_submissions','batch_id');

$studentsT = t_exists($mysqli,'studentss') ? 'studentss' : (t_exists($mysqli,'students') ? 'students' : null);
$batchesT  = t_exists($mysqli,'batches')   ? 'batches'   : (t_exists($mysqli,'lms_batches') ? 'lms_batches' : null);
$coursesT  = t_exists($mysqli,'courses')   ? 'courses'   : (t_exists($mysqli,'lms_courses') ? 'lms_courses' : null);

/* ---------- name & reg no expressions ---------- */
/* Student name: only full_name (no title). If empty, fallback to first+last, else name, else #id */
$ST_NAME_EXPR = $studentsT
  ? (
      c_exists($mysqli,$studentsT,'full_name')
        ? "NULLIF(TRIM(st.full_name),'')"
        : (
            (c_exists($mysqli,$studentsT,'first_name') || c_exists($mysqli,$studentsT,'last_name'))
              ? "NULLIF(TRIM(CONCAT(COALESCE(st.first_name,''),' ',COALESCE(st.last_name,''))),'')"
              : (c_exists($mysqli,$studentsT,'name') ? "NULLIF(TRIM(st.name),'')" : "NULL")
          )
    )
  : "NULL";
$ST_NAME = "COALESCE($ST_NAME_EXPR, CONCAT('#', s.student_id))";

/* Reg No: center_reg_no -> reference_no -> common alternates -> ID-#### */
if ($studentsT) {
  $regParts = [];
  foreach (['center_reg_no','reference_no','reg_no','reg_number','registration_no','student_no'] as $col) {
    if (c_exists($mysqli,$studentsT,$col)) $regParts[] = "NULLIF(TRIM(st.$col),'')";
  }
  $ST_REG_RAW = $regParts ? ("COALESCE(".implode(',',$regParts).")") : "NULL";
} else {
  $ST_REG_RAW = "NULL";
}
$ST_REG = "COALESCE($ST_REG_RAW, CONCAT('ID-', LPAD(s.student_id, 4, '0')))";

/* Batch name */
$BATCH_NAME = $batchesT
  ? (c_exists($mysqli,$batchesT,'name') ? "b.name"
     : (c_exists($mysqli,$batchesT,'title') ? "b.title"
        : (c_exists($mysqli,$batchesT,'code') ? "b.code" : "NULL")))
  : "NULL";
$BATCH_COURSE_ID = $batchesT && c_exists($mysqli,$batchesT,'course_id') ? "b.course_id" : "NULL";

/* Course name */
$COURSE_NAME = $coursesT
  ? (c_exists($mysqli,$coursesT,'name') ? "c.name"
     : (c_exists($mysqli,$coursesT,'title') ? "c.title"
        : (c_exists($mysqli,$coursesT,'code') ? "c.code" : "NULL")))
  : "NULL";
$ASS_COURSE_ID = c_exists($mysqli,'lms_assessments','course_id') ? "a.course_id" : "NULL";

/* Marks, date, statuses */
$MARKS_EXPR = marks_expr($mysqli);
$DATE_COL   = ($HAS_RELEASE ? "s.release_at" : ($HAS_UPDATED ? "s.updated_at" : ($HAS_SUBMIT ? "s.submitted_at" : "NULL")));
$FINAL_SQL  = ($HAS_FINAL ? "s.final_score" : ($HAS_SCORE ? "s.score" : "NULL"));
$PROV_SQL   = ($HAS_PROV  ? "s.provisional_score" : ($HAS_SCORE ? "s.score" : "NULL"));
$PUB_STATUSES  = "'released','published','approved','graded_final'";
$PROV_STATUSES = "'graded_provisional','under_review','provisional','submitted'";

/* ---------- filters & paging ---------- */
$q      = trim((string)($_GET['q'] ?? ''));
$status = in_array(($_GET['status'] ?? 'all'), ['all','published','provisional'], true) ? $_GET['status'] : 'all';
$per    = (int)($_GET['per'] ?? 50); if ($per<=0) $per=50; if ($per>200) $per=200;
$page   = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page-1)*$per;

/* ---------- dynamic joins & where ---------- */
$where = ["s.student_id=?"]; $types="i"; $bind=[$target_sid];

$join = "";
if ($studentsT) $join .= " LEFT JOIN `$studentsT` st ON st.id = s.student_id ";
if ($HAS_BATCHID && $batchesT) $join .= " LEFT JOIN `$batchesT` b ON b.id = s.batch_id ";
$courseJoinExpr = ($ASS_COURSE_ID!=='NULL') ? $ASS_COURSE_ID : (($BATCH_COURSE_ID!=='NULL')?$BATCH_COURSE_ID:"NULL");
if ($coursesT && $courseJoinExpr!=='NULL') $join .= " LEFT JOIN `$coursesT` c ON c.id = $courseJoinExpr ";

if ($status==='published'){
  $w=[]; if($HAS_FINAL) $w[]="s.final_score IS NOT NULL";
  if($HAS_STATUS) $w[]="LOWER(s.workflow_status) IN ($PUB_STATUSES)";
  if($w) $where[]="(".implode(' OR ',$w).")";
} elseif($status==='provisional'){
  $w=[]; if($HAS_PROV) $w[]="s.provisional_score IS NOT NULL";
  if($HAS_STATUS) $w[]="LOWER(s.workflow_status) IN ($PROV_STATUSES)";
  if($HAS_FINAL) $where[]="s.final_score IS NULL";
  if($w) $where[]="(".implode(' OR ',$w).")";
}

if ($q!==''){
  $where[]="(a.title LIKE ? OR COALESCE($COURSE_NAME,'') LIKE ? OR COALESCE($BATCH_NAME,'') LIKE ? OR COALESCE($ST_NAME,'') LIKE ? OR COALESCE($ST_REG,'') LIKE ?)";
  $types.="sssss"; $like='%'.$q.'%'; array_push($bind,$like,$like,$like,$like,$like);
}
$whereSql = $where ? "WHERE ".implode(' AND ',$where) : "";

/* ---------- count ---------- */
$sqlCount = "SELECT COUNT(*) n
             FROM lms_submissions s
             JOIN lms_assessments a ON a.id=s.assessment_id
             $join $whereSql";
$st=$mysqli->prepare($sqlCount);
$st->bind_param($types, ...$bind);
$st->execute();
$total=0;
if(method_exists($st,'get_result')){ $r=$st->get_result(); if($r){$x=$r->fetch_assoc(); $total=(int)$x['n']; $r->free();}}
else { $st->bind_result($total); $st->fetch(); }
$st->close();
$pages = max(1,(int)ceil($total/$per));

/* ---------- main SELECT (paged) ---------- */
$selectCols = "
  $ST_REG      AS reg_no,
  $ST_NAME     AS student_name,
  $BATCH_NAME  AS batch_name,
  $COURSE_NAME AS course_name,
  a.title      AS assessment,
  $PROV_SQL    AS provisional_score,
  $FINAL_SQL   AS final_score,
  $MARKS_EXPR  AS out_of,
  ".($HAS_STATUS?"LOWER(s.workflow_status)":"NULL")." AS wf,
  $DATE_COL    AS when_at
";
$sql = "SELECT $selectCols
        FROM lms_submissions s
        JOIN lms_assessments a ON a.id=s.assessment_id
        $join
        $whereSql
        ORDER BY COALESCE($DATE_COL, s.id) DESC
        LIMIT $per OFFSET $offset";
$st=$mysqli->prepare($sql);
$st->bind_param($types, ...$bind);
$st->execute();
$rows = fetch_all_stmt($st);
$st->close();

/* ---------- CSV export (all matching, no LIMIT) ---------- */
if (isset($_GET['export']) && $_GET['export']==='csv'){
  $sqlAll = "SELECT $selectCols
             FROM lms_submissions s
             JOIN lms_assessments a ON a.id=s.assessment_id
             $join
             $whereSql
             ORDER BY COALESCE($DATE_COL, s.id) DESC";
  $st=$mysqli->prepare($sqlAll);
  $st->bind_param($types, ...$bind);
  $st->execute();
  $all = fetch_all_stmt($st);
  $st->close();

  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=results.csv');
  $out=fopen('php://output','w');
  fputcsv($out,['Reg No','Student','Batch','Course','Assessment','Provisional','Final','Out of','Status','When']);
  foreach($all as $r){
    fputcsv($out,[
      (string)($r['reg_no']??''),
      (string)($r['student_name']??''),
      (string)($r['batch_name']??''),
      (string)($r['course_name']??''),
      (string)($r['assessment']??''),
      nfmt($r['provisional_score']),
      nfmt($r['final_score']),
      nfmt($r['out_of']),
      (string)($r['wf']??''),
      (string)($r['when_at']??''),
    ]);
  }
  fclose($out); exit;
}

/* ---------- layout wrappers ---------- */
$PAGE_TITLE='My Results'; $ACTIVE_MENU='results';
$top1 = __DIR__.'/studentlayout_top.php';     // your student skin
$bot1 = __DIR__.'/studentlayout_bottom.php';
$top2 = __DIR__.'/layout_top.php';            // generic fallback
$bot2 = __DIR__.'/layout_bottom.php';

if     (is_file($top1)) require $top1;
elseif (is_file($top2)) require $top2;
else {
  echo "<!doctype html><meta charset='utf-8'><title>".esc($PAGE_TITLE)."</title>";
  echo "<style>body{font-family:system-ui,Segoe UI,Arial,sans-serif;background:#f6f7fb;color:#0f172a;margin:0}
  .wrap{max-width:1200px;margin:24px auto;padding:0 16px}</style><div class='wrap'><h1>".esc($PAGE_TITLE)."</h1>";
}
?>
<style>
.results-card{background:#fff;border:1px solid #e8e9ee;border-radius:16px;box-shadow:0 10px 24px rgba(0,0,0,.04);overflow:hidden}
.header-bar{display:flex;flex-wrap:wrap;gap:10px;align-items:center;justify-content:space-between;padding:14px 16px;background:linear-gradient(180deg,#ffffff,#fafbff)}
.filters{display:flex;flex-wrap:wrap;gap:10px;align-items:center}
.input{padding:10px 12px;border:1px solid #e8e9ee;border-radius:10px;min-width:240px}
.select{padding:10px 12px;border:1px solid #e8e9ee;border-radius:10px}
.btn{padding:10px 12px;border:1px solid #e8e9ee;border-radius:10px;background:#fff;cursor:pointer;font-weight:700}
.btn.primary{background:#5b21b6;border-color:#5b21b6;color:#fff}
.meta{color:#65738b;font-size:12px}
.table-wrap{max-height:60vh;overflow:auto;border-top:1px solid #e8e9ee}
table{width:100%;border-collapse:collapse;background:#fff}
thead th{position:sticky;top:0;background:#fff;border-bottom:1px solid #e8e9ee;padding:12px;text-align:left;z-index:2}
tbody td{padding:10px;border-bottom:1px solid #f2f3f7}
tbody tr:nth-child(odd){background:#fcfcff}
tbody tr:hover{background:#f6f6fe}
td.num,th.num{text-align:right}
@media(max-width:900px){
  thead{display:none}
  table,tbody,tr,td{display:block;width:100%}
  tr{padding:8px 10px;border-bottom:1px solid #e8e9ee}
  td{border:none;padding:6px 10px}
  td::before{content:attr(data-label);display:block;color:#65738b;font-size:12px;margin-bottom:2px}
  .table-wrap{max-height:none}
}
.tag{padding:.25rem .6rem;border-radius:999px;background:#eef2ff;color:#3730a3;font-weight:700;font-size:12px;white-space:nowrap}
.tag.yellow{background:#fde68a;color:#7c2d12}
.pager{display:flex;gap:8px;align-items:center;justify-content:flex-end;padding:10px 16px}
</style>

<div class="results-card">
  <div class="header-bar">
    <form class="filters" method="get" action="">
      <?php if ($staff_ok && !$student_ok): ?>
        <input type="hidden" name="sid" value="<?= (int)$target_sid ?>">
      <?php endif; ?>
      <input class="input" name="q" value="<?= esc($q) ?>" placeholder="Search reg, student, course, batch, assessment…">
      <select class="select" name="status">
        <option value="all"        <?= $status==='all'?'selected':'' ?>>All</option>
        <option value="published"  <?= $status==='published'?'selected':'' ?>>Published</option>
        <option value="provisional"<?= $status==='provisional'?'selected':'' ?>>Provisional</option>
      </select>
      <select class="select" name="per">
        <?php foreach([20,50,100,200] as $opt): ?>
          <option value="<?= $opt ?>" <?= $per==$opt?'selected':'' ?>><?= $opt ?>/page</option>
        <?php endforeach; ?>
      </select>
      <button class="btn">Apply</button>
      <a class="btn" href="?<?= $staff_ok&&!$student_ok ? ('sid='.(int)$target_sid) : '' ?>">Reset</a>
      <a class="btn" href="?<?= http_build_query(array_filter([
          'sid'=>$staff_ok&&!$student_ok?(int)$target_sid:null,
          'q'=>$q,'status'=>$status,'per'=>$per,'export'=>'csv'
      ])) ?>">Export CSV</a>
    </form>
    <div class="meta">Total: <b><?= (int)$total ?></b> • Page <?= (int)$page ?> / <?= (int)$pages ?></div>
  </div>

  <div class="table-wrap">
    <table>
      <thead>
        <tr>
          <th style="min-width:120px">Reg No</th>
          <th style="min-width:160px">Student</th>
          <th style="min-width:140px">Batch</th>
          <th style="min-width:180px">Course</th>
          <th style="min-width:220px">Assessment</th>
          <th class="num" style="min-width:110px">Provisional</th>
          <th class="num" style="min-width:90px">Final</th>
          <th class="num" style="min-width:90px">Out of</th>
          <th style="min-width:120px">Status</th>
          <th style="min-width:160px">When</th>
        </tr>
      </thead>
      <tbody>
      <?php if (!$rows): ?>
        <tr><td data-label="Info" colspan="10" class="meta">No results.</td></tr>
      <?php else:
        foreach($rows as $r):
          $wf=strtolower((string)($r['wf']??''));
          $cls = in_array($wf,['released','published','approved','graded_final']) ? 'tag' : 'tag yellow';
      ?>
        <tr>
          <td data-label="Reg No"><?= esc($r['reg_no'] ?? '') ?></td>
          <td data-label="Student"><?= esc($r['student_name'] ?? '') ?></td>
          <td data-label="Batch"><?= esc($r['batch_name'] ?? '') ?></td>
          <td data-label="Course"><?= esc($r['course_name'] ?? '') ?></td>
          <td data-label="Assessment"><?= esc($r['assessment'] ?? '') ?></td>
          <td data-label="Provisional" class="num"><b><?= esc(nfmt($r['provisional_score'])) ?></b></td>
          <td data-label="Final" class="num"><b><?= esc(nfmt($r['final_score'])) ?></b></td>
          <td data-label="Out of" class="num"><?= esc(nfmt($r['out_of'])) ?></td>
          <td data-label="Status"><span class="<?= $cls ?>"><?= esc($wf ?: '—') ?></span></td>
          <td data-label="When" class="meta"><?= esc($r['when_at'] ?? '') ?></td>
        </tr>
      <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>

  <form class="pager" method="get">
    <?php if ($staff_ok && !$student_ok): ?>
      <input type="hidden" name="sid" value="<?= (int)$target_sid ?>">
    <?php endif; ?>
    <input type="hidden" name="q" value="<?= esc($q) ?>">
    <input type="hidden" name="status" value="<?= esc($status) ?>">
    <input type="hidden" name="per" value="<?= (int)$per ?>">
    <button class="btn" name="page" value="1" <?= $page<=1?'disabled':'' ?>>« First</button>
    <button class="btn" name="page" value="<?= max(1,$page-1) ?>" <?= $page<=1?'disabled':'' ?>>‹ Prev</button>
    <div class="meta">Page <?= (int)$page ?> / <?= (int)$pages ?></div>
    <button class="btn" name="page" value="<?= min($pages,$page+1) ?>" <?= $page>=$pages?'disabled':'' ?>>Next ›</button>
    <button class="btn" name="page" value="<?= (int)$pages ?>" <?= $page>=$pages?'disabled':'' ?>>Last »</button>
  </form>
</div>

<?php
if     (is_file($bot1)) require $bot1;
elseif (is_file($bot2)) require $bot2;
else echo "</div>";
