<?php
/* =============================================================================
   Tracking Sheet — per-batch progress grid (ELMS only)
   - Robust batch picker:
       * Detects table name: batches / centre_batches / center_batches
       * Respects session branch_id when present
       * "All branches" checkbox to ignore branch filter
       * If branch has no batches → auto-fallback to show all branches + notice
   - Grid: Students × Assessments grouped by Stage/Semester
   - Status chips: NS/S/P/F/RN/D/N
   - CSV export for current view
   ========================================================================== */

declare(strict_types=1);
if (session_status() === PHP_SESSION_NONE) session_start();

/* ------------ Includes -------------------- */
define('APP_ROOT', dirname(__DIR__, 2)); // .../elms
require_once APP_ROOT . '/includes/auth.php';
require_role(['super_admin','branch_admin','coordinator','lecturer']);
require_once APP_ROOT . '/includes/db.php';

if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  if (isset($conn) && $conn instanceof mysqli) { $mysqli = $conn; }
}
if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  http_response_code(500); echo 'DB connection not initialized'; exit;
}
@$mysqli->set_charset('utf8mb4');

/* ------------ tiny helpers ---------------- */
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
function gi($k){ return isset($_GET[$k]) && $_GET[$k]!=='' ? (int)$_GET[$k] : null; }
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 $fallback='id'): string {
  if (!$t) return $fallback;
  if ($rs=$db->query("SHOW COLUMNS FROM `{$t}`")) {
    $cols=[]; while($r=$rs->fetch_assoc()) $cols[]=$r['Field']; $rs->free();
    foreach($cands as $c) if (in_array($c,$cols,true)) return $c;
  }
  return $fallback;
}
function choose_table(mysqli $db, array $cands): ?string { foreach($cands as $t){ if(table_exists($db,$t)) return $t; } return null; }
function has_role(array $need): bool {
  $roles = (array)($_SESSION['roles'] ?? $_SESSION['user']['roles'] ?? [strtolower((string)($_SESSION['user']['role'] ?? $_SESSION['role'] ?? ''))]);
  foreach ($need as $r) if (in_array($r, $roles, true)) return true;
  return false;
}

/* ------------ detect tables ---------------- */
$T_BATCHES   = choose_table($mysqli, ['batches','centre_batches','center_batches']); // <— fixed: detect name
$T_COURSES   = choose_table($mysqli, ['courses']);
$T_MODULES   = choose_table($mysqli, ['lms_modules','modules']);
$T_ASSESS    = choose_table($mysqli, ['lms_assessments','assessments']);
$T_SUBMIT    = choose_table($mysqli, ['lms_submissions','lms_student_assessments','submissions']);
$T_GRADES    = choose_table($mysqli, ['lms_grades','grades']);
$T_ENROLL    = choose_table($mysqli, ['lms_enrollments','enrollments','student_batches']);
$T_STUDENTS  = choose_table($mysqli, ['students','studentss']);  // supports your studentss.sql too

/* ------------ inputs ----------------------- */
$branch_id   = isset($_SESSION['branch_id']) ? (int)$_SESSION['branch_id'] : 0;
$batch_id    = gi('batch_id');
$forceAll    = (isset($_GET['all']) && $_GET['all'] === '1');    // ignore branch filter if set
$export      = (isset($_GET['export']) && $_GET['export']==='csv');

/* ------------ batch list for picker (fixed) -------- */
$batchRows = []; $batchNotice = '';
if ($T_BATCHES) {
  $applyBranch = (!$forceAll && $branch_id && col_exists($mysqli,$T_BATCHES,'branch_id'));

  $where = [];
  if ($applyBranch) $where[] = "b.branch_id=".$branch_id;
  $whereSql = $where ? ('WHERE '.implode(' AND ',$where)) : '';

  $courseNameCol = $T_COURSES ? label_col($mysqli,$T_COURSES,['course_name','name','title'],'id') : null;

  $sqlBase = "SELECT b.id, COALESCE(b.batch_name, CONCAT('Batch #',b.id)) AS batch_name,
                     b.course_id".
                     ($courseNameCol ? ", COALESCE(c.`$courseNameCol`,'') AS course_name" : "").
             " FROM `$T_BATCHES` b ".
             ($T_COURSES ? "LEFT JOIN `$T_COURSES` c ON c.id=b.course_id " : "");

  // try with branch restriction (if applied)
  $sql = $sqlBase . " $whereSql ORDER BY b.id DESC LIMIT 300";
  if ($rs=$mysqli->query($sql)) { while($r=$rs->fetch_assoc()) $batchRows[]=$r; $rs->free(); }

  // if nothing and branch filter was applied → fallback to all branches
  if (!$batchRows && $applyBranch) {
    $batchNotice = 'No batches for the selected branch; showing all branches.';
    $sql = $sqlBase . " ORDER BY b.id DESC LIMIT 300";
    if ($rs=$mysqli->query($sql)) { while($r=$rs->fetch_assoc()) $batchRows[]=$r; $rs->free(); }
  }
}

/* ------------ if batch selected, load structure -------- */
$stageCol = $T_MODULES ? (col_exists($mysqli,$T_MODULES,'semester') ? 'semester' : (col_exists($mysqli,$T_MODULES,'stage') ? 'stage' : null)) : null;
$assTitleCol = $T_ASSESS ? label_col($mysqli,$T_ASSESS,['code','short_code','title','name'],'id') : 'id';
$studNameCol = $T_STUDENTS ? label_col($mysqli,$T_STUDENTS,['full_name','name','title'],'id') : 'id';
$studRefCol  = $T_STUDENTS ? label_col($mysqli,$T_STUDENTS,['reference_no','ref_no','student_ref'],'id') : 'id';

$gridStages = [];           // [stage] => [assId,...]
$assMeta    = [];           // [assId] => ['title'=>...,'stage'=>n]
$students   = [];           // [student_id] => ['name'=>,'ref'=>]
$subMap     = [];           // "assId-stuId" => 1
$gradeMap   = [];           // "assId-stuId" => ['status'=>...,'score'=>..]

if ($batch_id && $T_MODULES && $T_ASSESS && $T_ENROLL && $T_SUBMIT) {
  /* 1) Modules + Assessments for the batch (collect by stage/semester) */
  $sqlA = "SELECT a.id AS aid, COALESCE(a.`$assTitleCol`, CONCAT('A#',a.id)) AS title,
                  COALESCE(m.`$stageCol`,0) AS stg
           FROM `$T_ASSESS` a
           JOIN `$T_MODULES` m ON m.id=a.module_id
           WHERE m.batch_id=?
           ORDER BY stg ASC, a.id ASC";
  $st = $mysqli->prepare($sqlA); $st->bind_param('i',$batch_id); $st->execute();
  $rs = $st->get_result();
  while($r=$rs->fetch_assoc()){
    $assMeta[(int)$r['aid']] = ['title'=>$r['title'], 'stage'=>(int)$r['stg']];
    $gridStages[(int)$r['stg']][] = (int)$r['aid'];
  }
  $st->close();

  /* 2) Students in batch */
  $sqlS = "SELECT e.student_id sid,
                  ".($T_STUDENTS ? "COALESCE(s.`$studNameCol`,'(unknown)')" : "'(unknown)'")." AS sname,
                  ".($T_STUDENTS ? "COALESCE(s.`$studRefCol`,'—')" : "'—'")." AS sref
           FROM `$T_ENROLL` e
           ".($T_STUDENTS ? "LEFT JOIN `$T_STUDENTS` s ON s.id=e.student_id" : "")."
           WHERE e.batch_id=?
           ORDER BY sname ASC, sid ASC";
  $st = $mysqli->prepare($sqlS); $st->bind_param('i',$batch_id); $st->execute();
  $rs = $st->get_result();
  while($r=$rs->fetch_assoc()){ $students[(int)$r['sid']] = ['name'=>$r['sname'], 'ref'=>$r['sref']]; }
  $st->close();

  /* 3) Submissions map */
  $sqlSub = "SELECT DISTINCT s.assessment_id, s.student_id
             FROM `$T_SUBMIT` s
             JOIN `$T_ASSESS` a ON a.id=s.assessment_id
             JOIN `$T_MODULES` m ON m.id=a.module_id
             WHERE m.batch_id=?";
  $st=$mysqli->prepare($sqlSub); $st->bind_param('i',$batch_id); $st->execute();
  $rs=$st->get_result();
  while($r=$rs->fetch_assoc()){
    $subMap[((int)$r['assessment_id']).'-'.((int)$r['student_id'])] = 1;
  }
  $st->close();

  /* 4) Grades map (status/score if any) */
  if ($T_GRADES){
    $statusCol = col_exists($mysqli,$T_GRADES,'workflow_status') ? 'workflow_status' :
                 (col_exists($mysqli,$T_GRADES,'status') ? 'status' : null);
    $scoreCol  = col_exists($mysqli,$T_GRADES,'final_score') ? 'final_score' :
                 (col_exists($mysqli,$T_GRADES,'score') ? 'score' : null);

    $sel = [];
    $sel[] = $statusCol ? "COALESCE(g.`$statusCol`,'') AS gstat" : "'' AS gstat";
    $sel[] = $scoreCol  ? "g.`$scoreCol` AS gscore" : "NULL AS gscore";
    $selSql = implode(', ', $sel);

    $sqlG = "SELECT g.assessment_id, g.student_id, $selSql
             FROM `$T_GRADES` g
             JOIN `$T_ASSESS` a ON a.id=g.assessment_id
             JOIN `$T_MODULES` m ON m.id=a.module_id
             WHERE m.batch_id=?";
    $st=$mysqli->prepare($sqlG); $st->bind_param('i',$batch_id); $st->execute();
    $rs=$st->get_result();
    while($r=$rs->fetch_assoc()){
      $key = ((int)$r['assessment_id']).'-'.((int)$r['student_id']);
      $gradeMap[$key] = ['status'=> (string)$r['gstat'], 'score'=> $r['gscore']];
    }
    $st->close();
  }
}

/* ------------ status resolver ---------------- */
function status_chip(array $ctx): array {
  // ctx: ['has_sub'=>bool, 'grade'=>['status'=>..., 'score'=>...]]
  if (!$ctx['has_sub']) return ['NS','Not submitted','chip-ns'];

  $status = strtoupper(trim((string)($ctx['grade']['status'] ?? '')));
  $score  = $ctx['grade']['score'] ?? null;

  if ($status !== '') {
    if (in_array($status, ['PASS','P','PASSED'], true))   return ['P','Pass','chip-p'];
    if (in_array($status, ['FAIL','F','FAILED'], true))    return ['F','Fail','chip-f'];
    if (in_array($status, ['REFERRED','REF','RN'], true))  return ['RN','Referred','chip-rn'];
    if (in_array($status, ['DEFER','DEFERRED','D'], true)) return ['D','Deferred','chip-d'];
    if (in_array($status, ['PENDING','MARKING','N'], true))return ['N','No grade','chip-n'];
    return ['S','Submitted','chip-s']; // fallback if some other status
  }
  if ($score !== null && $score !== '') {
    $num = (float)$score;
    if ($num >= 40) return ['P','Pass','chip-p']; // generic pass threshold
    return ['F','Fail','chip-f'];
  }
  return ['S','Submitted','chip-s'];
}

/* ------------ CSV export --------------------- */
if ($export && $batch_id) {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="tracking_sheet_batch_'.$batch_id.'_'.date('Ymd_His').'.csv"');
  $out = fopen('php://output','w');

  $hdr = ['Student ID','Student Name','Ref'];
  foreach ($gridStages as $stg => $assIds) {
    foreach ($assIds as $aid) { $hdr[] = 'Stage '.$stg.' - '.($assMeta[$aid]['title'] ?? ('A#'.$aid)); }
  }
  fputcsv($out, $hdr);

  foreach ($students as $sid=>$sinfo) {
    $row = [$sid, $sinfo['name'], $sinfo['ref']];
    foreach ($gridStages as $stg => $assIds) {
      foreach ($assIds as $aid) {
        $key = $aid.'-'.$sid;
        $chip = status_chip(['has_sub'=> isset($subMap[$key]), 'grade'=> $gradeMap[$key] ?? []]);
        $row[] = $chip[0]; // code
      }
    }
    fputcsv($out, $row);
  }
  exit;
}

/* ------------ page chrome -------------------- */
$PAGE_TITLE  = 'Tracking Sheet';
$ACTIVE_MENU = 'reports';
require APP_ROOT . '/includes/admin_layout_top.php';
?>
<style>
  .chip{display:inline-block;padding:.15rem .45rem;border-radius:999px;font-size:.72rem;font-weight:700;color:#111}
  .chip-ns{background:#ffe08a}   /* Not submitted - yellow */
  .chip-s {background:#cbd5e1}   /* Submitted - slate */
  .chip-p {background:#86efac}   /* Pass - green */
  .chip-f {background:#fda4af}   /* Fail - rose */
  .chip-rn{background:#fecaca}   /* Referred - light red */
  .chip-d {background:#fde68a}   /* Deferred - amber */
  .chip-n {background:#d1d5db}   /* No grade - gray */
  .sticky-topbar{position:sticky;top:64px;z-index:10;background:#fff;border-bottom:1px solid #e5e7eb}
  .table thead th{font-size:.82rem;color:#64748b;white-space:nowrap}
  .stage-head{background:#f8fafc;font-weight:600;border-top:2px solid #e2e8f0}
  .ass-head{white-space:nowrap;max-width:160px;overflow:hidden;text-overflow:ellipsis}
  .legend i{display:inline-block;width:18px;height:18px;border-radius:50%;vertical-align:middle;margin-right:6px}
  .legend .ns{background:#ffe08a}.legend .s{background:#cbd5e1}.legend .p{background:#86efac}
  .legend .f{background:#fda4af}.legend .rn{background:#fecaca}.legend .d{background:#fde68a}.legend .n{background:#d1d5db}
</style>

<div class="d-flex align-items-center justify-content-between mb-3 sticky-topbar p-2">
  <div class="h5 mb-0"><i class="bi bi-bullseye"></i> Tracking Sheet</div>
  <div class="d-flex align-items-center gap-2">
    <?php if ($batch_id): ?>
      <a class="btn btn-sm btn-outline-secondary" href="?batch_id=<?= (int)$batch_id ?>&export=csv<?= $forceAll?'&all=1':'' ?>">Generate CSV</a>
    <?php else: ?>
      <button class="btn btn-sm btn-outline-secondary" disabled>Generate CSV</button>
    <?php endif; ?>

    <form method="get" class="d-flex align-items-center gap-2">
      <select name="batch_id" class="form-select form-select-sm" style="min-width:340px">
        <option value="">SELECT BATCH</option>
        <?php foreach ($batchRows as $b): ?>
          <option value="<?= (int)$b['id'] ?>" <?= $batch_id===(int)$b['id']?'selected':'' ?>>
            #<?= (int)$b['id'] ?> — <?= esc($b['batch_name']) ?><?= !empty($b['course_name'])?' · '.esc($b['course_name']):'' ?>
          </option>
        <?php endforeach; ?>
      </select>

      <label class="form-check-label small d-flex align-items-center">
        <input type="checkbox" class="form-check-input me-1" name="all" value="1" <?= $forceAll?'checked':'' ?>>
        All branches
      </label>

      <button class="btn btn-sm btn-primary">Apply</button>
    </form>
  </div>
</div>

<?php if (!empty($batchNotice)): ?>
  <div class="text-muted small mb-2"><?= esc($batchNotice) ?></div>
<?php endif; ?>

<?php if (!$batch_id): ?>
  <div class="text-muted">Select a batch to view the tracking sheet</div>
<?php elseif (!$gridStages): ?>
  <div class="alert alert-warning">No assessments found for this batch.</div>
<?php else: ?>

  <div class="legend mb-2 small">
    <strong class="me-2">Legend:</strong>
    <span class="me-3"><i class="ns"></i>NS</span>
    <span class="me-3"><i class="s"></i>S</span>
    <span class="me-3"><i class="p"></i>P</span>
    <span class="me-3"><i class="f"></i>F</span>
    <span class="me-3"><i class="rn"></i>RN</span>
    <span class="me-3"><i class="d"></i>D</span>
    <span class="me-3"><i class="n"></i>N</span>
  </div>

  <div class="table-responsive">
    <table class="table table-sm align-middle">
      <thead>
        <!-- Stage group header -->
        <tr class="stage-head">
          <th style="min-width:90px">Student ID</th>
          <th style="min-width:260px">Student Name</th>
          <th>Ref</th>
          <?php foreach ($gridStages as $stg=>$assIds): ?>
            <th class="text-center" colspan="<?= count($assIds) ?>">Stage <?= (int)$stg ?></th>
          <?php endforeach; ?>
        </tr>
        <!-- Assessment titles header -->
        <tr>
          <th></th><th></th><th></th>
          <?php foreach ($gridStages as $stg=>$assIds): foreach ($assIds as $aid): ?>
            <th class="ass-head"><?= esc($assMeta[$aid]['title'] ?? ('A#'.$aid)) ?></th>
          <?php endforeach; endforeach; ?>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($students as $sid=>$sinfo): ?>
          <tr>
            <td><?= (int)$sid ?></td>
            <td><?= esc($sinfo['name']) ?></td>
            <td><?= esc($sinfo['ref']) ?></td>
            <?php foreach ($gridStages as $stg=>$assIds): foreach ($assIds as $aid):
              $key  = $aid.'-'.$sid;
              $chip = status_chip(['has_sub'=> isset($subMap[$key]), 'grade'=> $gradeMap[$key] ?? []]);
            ?>
              <td><span class="chip <?= esc($chip[2]) ?>" title="<?= esc($chip[1]) ?>"><?= esc($chip[0]) ?></span></td>
            <?php endforeach; endforeach; ?>
          </tr>
        <?php endforeach; ?>
        <?php if (!$students): ?>
          <tr><td colspan="<?= 3 + array_sum(array_map('count',$gridStages)) ?>" class="text-center text-muted">No students enrolled.</td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
<?php endif; ?>

<?php require APP_ROOT . '/includes/admin_layout_bottom.php';
/* =============================== END ================================== */
