<?php
/* ============================================================================
 * Gradebook (robust, no white-screen)
 * Path: /elms/admin/assessments/gradebook.php
 * - Safe includes for auth/layout/db (auto-detects, falls back cleanly)
 * - ?debug=1 shows PHP errors (turns on display_errors)
 * - Assessment picker if assessment_id missing
 * - Filters: batch, search (name/ref), date range, only ungraded
 * - Pagination: 10/20/30/50/100; default 20; latest submissions first
 * - Inline grading + status, bulk actions, CSV download of current page
 * ========================================================================== */
declare(strict_types=1);
if (session_status() === PHP_SESSION_NONE) session_start();

/* ---------- debug switch ---------- */
$DEBUG = (isset($_GET['debug']) && $_GET['debug'] === '1');
if ($DEBUG) { ini_set('display_errors','1'); ini_set('display_startup_errors','1'); error_reporting(E_ALL); }

/* ---------- helpers ---------- */
define('APP_ROOT', dirname(__DIR__, 2)); // .../elms
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
function gi(string $k): ?int { return isset($_GET[$k]) && $_GET[$k]!=='' ? (int)$_GET[$k] : null; }
function gd(string $k): ?string { $v=trim((string)($_GET[$k]??'')); return ($v!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$v))?$v:null; }
function mk_url(array $extra=[]): string { $q=$_GET; foreach($extra as $k=>$v){ if($v===null) unset($q[$k]); else $q[$k]=$v; } return '?'.http_build_query($q); }
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{ 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 'id'; }

/* ---------- auth (safe) ---------- */
foreach ([APP_ROOT.'/includes/auth.php', APP_ROOT.'/includes/auth.inc.php'] as $f) {
  if (is_file($f)) { require_once $f; break; }
}
if (function_exists('require_role')) {
  require_role(['super_admin','admin','branch_admin','course_coordinator','coordinator','lecturer']);
}

/* ---------- DB (robust) ---------- */
$tried=[]; $try=function(string $p)use(&$tried){ $tried[]=$p; if(is_file($p)){ require_once $p; return true; } return false; };
$try(APP_ROOT.'/includes/db.php') || $try(__DIR__.'/../../includes/db.php') || $try(__DIR__.'/../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 "<!doctype html><meta charset='utf-8'><title>Gradebook</title>
        <div style='font:14px system-ui,Segoe UI,Arial;margin:20px'>
          <h3>DB not initialized</h3>
          <p style='color:#666'>Tried:</p>
          <pre style='background:#f8f8f8;border:1px solid #eee;padding:10px'>".esc(implode("\n",$tried))."</pre>
          <p>Add <code>?debug=1</code> to display PHP errors while setting up.</p>
        </div>";
  exit;
}
@$mysqli->set_charset('utf8mb4');

/* ---------- layout find (with fallback) ---------- */
$LAY_TOP=$LAY_BOT=null;
foreach ([APP_ROOT.'/includes/admin_layout_top.php', APP_ROOT.'/includes/layout_admin_top.php'] as $f) if (is_file($f)) { $LAY_TOP=$f; break; }
foreach ([APP_ROOT.'/includes/admin_layout_bottom.php', APP_ROOT.'/includes/layout_admin_bottom.php'] as $f) if (is_file($f)) { $LAY_BOT=$f; break; }

/* ---------- schema ---------- */
$STUD_TBL = table_exists($mysqli,'studentss') ? 'studentss' : (table_exists($mysqli,'students') ? 'students' : 'studentss');
$studName = label_col($mysqli,$STUD_TBL,['full_name','name','title']);
$studRef  = label_col($mysqli,$STUD_TBL,['reference_no','ref_no','student_ref']);

$ASSESS_TBL = 'lms_assessments';
$SUB_TBL    = 'lms_submissions';
$BATCH_TBL  = table_exists($mysqli,'batches') ? 'batches' : null;
$batchName  = $BATCH_TBL ? label_col($mysqli,$BATCH_TBL,['batch_name','name','title']) : null;

/* scores / status / time columns (auto) */
$scoreColFinal = col_exists($mysqli,$SUB_TBL,'final_score') ? 'final_score' : (col_exists($mysqli,$SUB_TBL,'score') ? 'score' : null);
$scoreColProv  = col_exists($mysqli,$SUB_TBL,'provisional_score') ? 'provisional_score' : null;
$scoreExpr     = $scoreColFinal ? "COALESCE(s.$scoreColFinal".($scoreColProv?(", s.$scoreColProv"):'').")" : ($scoreColProv ? "s.$scoreColProv" : "NULL");
$statusCol     = col_exists($mysqli,$SUB_TBL,'workflow_status') ? 'workflow_status' : (col_exists($mysqli,$SUB_TBL,'status') ? 'status' : null);
$timeCol       = col_exists($mysqli,$SUB_TBL,'submitted_at') ? 'submitted_at' : (col_exists($mysqli,$SUB_TBL,'created_at') ? 'created_at' : 'id');

/* ---------- inputs ---------- */
$assessment_id = gi('assessment_id');
$batch_id      = gi('batch_id');
$only_ungraded = (isset($_GET['only_ungraded']) && $_GET['only_ungraded']==='1');
$search        = trim((string)($_GET['q'] ?? ''));
$date_from     = gd('date_from');
$date_to       = gd('date_to');

$per_opts=[10,20,30,50,100];
$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 20;
if (!in_array($per_page,$per_opts,true)) $per_page=20;
$page = max(1,(int)($_GET['page'] ?? 1));
$offset = ($page-1)*$per_page;

/* ---------- assessment picker if missing ---------- */
if (!$assessment_id) {
  $assess=[]; if ($rs=$mysqli->query("SELECT a.id,a.title,COALESCE(c.course_name,c.name,c.title,'') AS course_label
                                      FROM `$ASSESS_TBL` a LEFT JOIN courses c ON c.id=a.course_id
                                      ORDER BY a.id DESC LIMIT 200")){
    while($r=$rs->fetch_assoc()) $assess[]=$r; $rs->free();
  }
  $batches=[]; if ($BATCH_TBL && $rs=$mysqli->query("SELECT id, `$batchName` AS label FROM `$BATCH_TBL` ORDER BY id DESC LIMIT 200")){
    while($r=$rs->fetch_assoc()) $batches[]=$r; $rs->free();
  }

  $PAGE_TITLE='Gradebook — choose assessment'; $ACTIVE_MENU='reports';
  if ($LAY_TOP) require $LAY_TOP; else {
    echo "<!doctype html><meta charset='utf-8'><title>$PAGE_TITLE</title><link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css'><div class='container py-3'>";
  } ?>
  <div class="card">
    <div class="card-header"><strong>Open Gradebook</strong></div>
    <div class="card-body">
      <form class="row g-3" method="get">
        <div class="col-lg-7">
          <label class="form-label">Assessment</label>
          <select name="assessment_id" class="form-select" required>
            <option value="">— choose —</option>
            <?php foreach($assess as $a): ?>
              <option value="<?= (int)$a['id'] ?>">#<?= (int)$a['id'] ?> — <?= esc($a['title']) ?><?= $a['course_label']?' · '.esc($a['course_label']):'' ?></option>
            <?php endforeach; ?>
          </select>
        </div>
        <div class="col-lg-3">
          <label class="form-label">Batch (optional)</label>
          <select name="batch_id" class="form-select">
            <option value="">— any —</option>
            <?php foreach($batches as $b): ?>
              <option value="<?= (int)$b['id'] ?>">#<?= (int)$b['id'] ?> — <?= esc($b['label']) ?></option>
            <?php endforeach; ?>
          </select>
        </div>
        <div class="col-lg-2 d-grid align-items-end">
          <button class="btn btn-primary mt-4">Open</button>
        </div>
      </form>
    </div>
  </div>
  <?php if ($LAY_BOT) require $LAY_BOT; else echo "</div>"; exit;
}

/* ---------- assessment info ---------- */
$assTitle='Assessment #'.$assessment_id; $courseLabel=''; $max_score=100;
if ($rs=$mysqli->query("SELECT a.title, COALESCE(c.course_name,c.name,c.title,'') AS course_label, COALESCE(a.max_score,100) AS mx
                        FROM `$ASSESS_TBL` a LEFT JOIN courses c ON c.id=a.course_id
                        WHERE a.id=".(int)$assessment_id." LIMIT 1")){
  if ($r=$rs->fetch_assoc()){ $assTitle=$r['title']?:$assTitle; $courseLabel=$r['course_label']; $max_score=(int)$r['mx']; }
  $rs->free();
}

/* ---------- save inline grades ---------- */
if ($_SERVER['REQUEST_METHOD']==='POST' && isset($_POST['save_grades'])) {
  if (!isset($_SESSION['csrf_grade'])) $_SESSION['csrf_grade']=bin2hex(random_bytes(16));
  $token=(string)($_POST['csrf']??''); if(!hash_equals($_SESSION['csrf_grade'],$token)){ http_response_code(400); echo 'Invalid CSRF'; exit; }

  $ids=$_POST['id']??[]; $scores=$_POST['score']??[]; $statuses=$_POST['status']??[];
  foreach($ids as $i=>$sid){
    $sid=(int)$sid;
    $sc = isset($scores[$i]) && $scores[$i]!=='' ? max(0,min($max_score,(float)$scores[$i])) : null;
    $st = trim((string)($statuses[$i]??''));
    $set=[];
    if($sc!==null){
      if($scoreColFinal) $set[]="`$scoreColFinal`=".(float)$sc;
      elseif($scoreColProv) $set[]="`$scoreColProv`=".(float)$sc;
    }
    if($statusCol) $set[]="`$statusCol`='".$mysqli->real_escape_string($st)."'";
    if($set) $mysqli->query("UPDATE `$SUB_TBL` s SET ".implode(', ',$set)." WHERE s.id=$sid AND s.assessment_id=".(int)$assessment_id);
  }
  header('Location: '.mk_url()); exit;
}

/* ---------- WHERE ---------- */
$w=["s.assessment_id=".(int)$assessment_id];
if ($batch_id)      $w[]="s.batch_id=".(int)$batch_id;
if ($only_ungraded) $w[]="(".$scoreExpr." IS NULL OR ".$scoreExpr."=0)";
if ($search!==''){ $q="'%".$mysqli->real_escape_string($search)."%'"; $w[]="(st.`$studName` LIKE $q OR st.`$studRef` LIKE $q)"; }
if ($date_from=gd('date_from')) $w[]="s.`$timeCol`>='".$mysqli->real_escape_string($date_from)." 00:00:00'";
if ($date_to=gd('date_to'))     $w[]="s.`$timeCol`<='".$mysqli->real_escape_string($date_to)." 23:59:59'";
$where='WHERE '.implode(' AND ',$w);

/* ---------- count + pages ---------- */
$total=0; if($rs=$mysqli->query("SELECT COUNT(*) AS c FROM `$SUB_TBL` s JOIN `$STUD_TBL` st ON st.id=s.student_id $where")){ $r=$rs->fetch_assoc(); $total=(int)$r['c']; $rs->free(); }
$pages=max(1,(int)ceil($total/$per_page));
$offset=min($offset,max(0,($pages-1)*$per_page));

/* ---------- fetch page ---------- */
$batchJoin=$BATCH_TBL ? "LEFT JOIN `$BATCH_TBL` b ON b.id=s.batch_id" : "";
$batchSel = $batchName ? ", b.`$batchName` AS batch_name" : ", s.batch_id AS batch_name";
$sql="SELECT s.id, s.student_id, st.`$studName` AS student_name, st.`$studRef` AS ref_no,
             $scoreExpr AS score, ".($statusCol?"s.`$statusCol`":"NULL")." AS st,
             s.`$timeCol` AS submitted_at $batchSel
      FROM `$SUB_TBL` s
      JOIN `$STUD_TBL` st ON st.id=s.student_id
      $batchJoin
      $where
      ORDER BY s.`$timeCol` DESC, s.id DESC
      LIMIT $offset,$per_page";
$rows=[]; if($rs=$mysqli->query($sql)){ while($r=$rs->fetch_assoc()) $rows[]=$r; $rs->free(); }

/* ---------- CSV of current page ---------- */
if (isset($_GET['export']) && $_GET['export']==='csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="gradebook_'.$assessment_id.'_'.date('Ymd_His').'.csv'");
  $out=fopen('php://output','w');
  fputcsv($out,['#','Student','Ref','Submitted','Score','Status','Batch']);
  $i=$offset+1; foreach($rows as $r) fputcsv($out,[$i++,$r['student_name'],$r['ref_no'],$r['submitted_at'],$r['score'],$r['st'],$r['batch_name']??'']); exit;
}

/* ---------- render ---------- */
$PAGE_TITLE='Gradebook — '.esc($assTitle); $ACTIVE_MENU='reports';
if ($LAY_TOP) require $LAY_TOP; else {
  echo "<!doctype html><meta charset='utf-8'><title>$PAGE_TITLE</title><link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css'><div class='container py-3'>";
}
if (!isset($_SESSION['csrf_grade'])) $_SESSION['csrf_grade']=bin2hex(random_bytes(16));
?>
<div class="d-flex align-items-center justify-content-between mb-2">
  <h1 class="h5 m-0">Gradebook — <?= esc($assTitle) ?></h1>
  <div class="text-muted small">Course: <?= esc($courseLabel) ?> · Max score: <?= (int)$max_score ?></div>
</div>

<div class="card mb-3">
  <div class="card-body">
    <form class="row g-2" method="get">
      <input type="hidden" name="assessment_id" value="<?= (int)$assessment_id ?>">
      <div class="col-md-2"><label class="form-label">Batch ID</label><input class="form-control" name="batch_id" value="<?= esc($batch_id) ?>" placeholder="optional"></div>
      <div class="col-md-3"><label class="form-label">Search (name/ref)</label><input class="form-control" name="q" value="<?= esc($search) ?>" placeholder="e.g. EC-213 or Saman"></div>
      <div class="col-md-2"><label class="form-label">From</label><input class="form-control" type="date" name="date_from" value="<?= esc($date_from) ?>"></div>
      <div class="col-md-2"><label class="form-label">To</label><input class="form-control" type="date" name="date_to" value="<?= esc($date_to) ?>"></div>
      <div class="col-md-2"><label class="form-label">Only ungraded</label>
        <select class="form-select" name="only_ungraded">
          <option value="0" <?= !$only_ungraded?'selected':'' ?>>No</option>
          <option value="1" <?=  $only_ungraded?'selected':'' ?>>Yes</option>
        </select>
      </div>
      <div class="col-md-1"><label class="form-label">Rows</label>
        <select class="form-select" name="per_page" onchange="this.form.submit()">
          <?php foreach([10,20,30,50,100] as $n): ?><option value="<?= $n ?>" <?= $per_page==$n?'selected':'' ?>><?= $n ?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col-12 d-flex gap-2">
        <button class="btn btn-primary">Apply</button>
        <a class="btn btn-outline-secondary" href="<?= esc(strtok($_SERVER['REQUEST_URI'],'?')) ?>">Reset</a>
        <a class="btn btn-outline-secondary" href="<?= esc(mk_url(['export'=>'csv'])) ?>">Export CSV</a>
        <a class="btn btn-outline-secondary" href="<?= esc(mk_url(['debug'=>'1'])) ?>">Debug=1</a>
      </div>
    </form>
  </div>
</div>

<form method="post">
  <input type="hidden" name="csrf" value="<?= esc($_SESSION['csrf_grade']) ?>">
  <div class="card">
    <div class="card-header d-flex justify-content-between align-items-center">
      <strong>Submissions (latest first)</strong>
      <div class="small text-muted">Total: <?= (int)$total ?> · Page <?= (int)$page ?> / <?= (int)$pages ?></div>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table table-sm align-middle mb-0">
          <thead class="table-light">
            <tr>
              <th style="width:64px">#</th>
              <th>Student</th>
              <th>Ref</th>
              <th>Submitted</th>
              <th style="width:140px">Score (0–<?= (int)$max_score ?>)</th>
              <th style="width:160px">Status</th>
              <th>Batch</th>
            </tr>
          </thead>
          <tbody>
          <?php if(!$rows): ?>
            <tr><td colspan="7" class="text-center text-muted">No submissions.</td></tr>
          <?php else:
            $i=$offset+1;
            $statusOpts=['pending'=>'Pending','graded'=>'Graded','late'=>'Late','resubmit'=>'Resubmit','rejected'=>'Rejected'];
            foreach($rows as $r):
              $cur=(string)($r['st']??''); if($cur!=='' && !isset($statusOpts[$cur])) $statusOpts[$cur]=ucfirst($cur);
          ?>
            <tr>
              <td><?= (int)$i++ ?><input type="hidden" name="id[]" value="<?= (int)$r['id'] ?>"></td>
              <td><?= esc($r['student_name']) ?></td>
              <td><?= esc($r['ref_no']) ?></td>
              <td class="small text-muted"><?= esc($r['submitted_at']) ?></td>
              <td><input type="number" step="0.01" min="0" max="<?= (int)$max_score ?>" class="form-control form-control-sm" name="score[]" value="<?= $r['score']!==null ? esc($r['score']) : '' ?>"></td>
              <td>
                <select name="status[]" class="form-select form-select-sm">
                  <?php foreach($statusOpts as $k=>$lbl): ?>
                    <option value="<?= esc($k) ?>" <?= $k===$cur?'selected':'' ?>><?= esc($lbl) ?></option>
                  <?php endforeach; ?>
                </select>
              </td>
              <td><?= esc($r['batch_name'] ?? '') ?></td>
            </tr>
          <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
    <div class="card-footer d-flex justify-content-between align-items-center">
      <div class="btn-group">
        <a class="btn btn-sm btn-outline-secondary <?= $page<=1?'disabled':'' ?>" href="<?= esc(mk_url(['page'=>max(1,$page-1)])) ?>">Prev</a>
        <a class="btn btn-sm btn-outline-secondary <?= $page>=$pages?'disabled':'' ?>" href="<?= esc(mk_url(['page'=>min($pages,$page+1)])) ?>">Next</a>
      </div>
      <div class="d-flex gap-2">
        <button class="btn btn-success" name="save_grades" value="1">Save changes</button>
        <button type="button" class="btn btn-outline-secondary" onclick="bulkSetStatus('graded')">Mark all shown: Graded</button>
        <button type="button" class="btn btn-outline-secondary" onclick="bulkSetStatus('pending')">Mark all shown: Pending</button>
      </div>
    </div>
  </div>
</form>

<script>
function bulkSetStatus(val){ document.querySelectorAll('select[name="status[]"]').forEach(s => s.value=val); }
</script>
<?php if ($LAY_BOT) require $LAY_BOT; else echo "</div>"; ?>
