<?php declare(strict_types=1);
/* ============================================================================
 * Submissions list for one Assessment + Batch (admin/assessments/submissions.php)
 * - If assessment_id or batch_id missing → friendly picker
 * - No mysqlnd required (no get_result / fetch_all)
 * - Robust file-path → web URL mapping
 * - Safer column/table detection + fallbacks
 * ========================================================================== */

define('APP_ROOT', dirname(__DIR__, 2)); // .../elms
if (session_status() === PHP_SESSION_NONE) session_start();

require_once APP_ROOT . '/includes/auth.php';
require_role(['admin','super_admin','branch_admin','lecturer','course_coordinator','coordinator']);
require_once APP_ROOT . '/includes/db.php';
@$mysqli->set_charset('utf8mb4');

/* ---------- small helpers ---------- */
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }

/** Base web path of the app (works under subfolders) */
function app_base_web(): string {
  $script = $_SERVER['SCRIPT_NAME'] ?? '/elms/admin/assessments/submissions.php';
  $base = rtrim(dirname($script, 3), '/'); // .../elms
  return $base === '' ? '/' : $base;
}

/** table/column existence helpers */
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;
}

/** Choose a label column that exists on a table */
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';
}

/** Convert stored file_path from DB to a web URL that opens */
function webify_upload_href(string $path): string {
  $p = trim($path); if ($p === '') return '';
  if (preg_match('#^https?://#i', $p)) return $p;   // already absolute
  $p = str_replace('\\','/',$p);
  if (preg_match('#^/?uploads/#i', $p)) {
    $p = ltrim($p, '/');                            // uploads/...
    return app_base_web() . '/' . $p;               // /<base>/uploads/...
  }
  if ($p[0] === '/') return $p;                     // root-relative
  return app_base_web() . '/' . ltrim($p,'/');      // relative under base
}

/* ---- stmt helpers WITHOUT mysqlnd ---- */
function stmt_all_assoc(mysqli_stmt $st): array {
  $meta = $st->result_metadata(); if (!$meta) return [];
  $row = []; $bind = []; $out = [];
  while ($f = $meta->fetch_field()) { $row[$f->name] = null; $bind[] = &$row[$f->name]; }
  call_user_func_array([$st,'bind_result'],$bind);
  while ($st->fetch()) {
    $copy = [];
    foreach ($row as $k=>$v) $copy[$k] = $v;
    $out[] = $copy;
  }
  return $out;
}
function stmt_one_assoc(mysqli_stmt $st): ?array {
  $all = stmt_all_assoc($st); return $all[0] ?? null;
}

/* ---------- inputs ---------- */
$assessment_id = (int)($_GET['assessment_id'] ?? 0);
$batch_id      = (int)($_GET['batch_id'] ?? 0);

/* ---------- discover student table + useful columns ---------- */
$STUD_TBL = table_exists($mysqli,'studentss') ? 'studentss' : (table_exists($mysqli,'students') ? 'students' : null);
if (!$STUD_TBL) { http_response_code(500); exit('Students table not found'); }
$studLabel = label_col($mysqli, $STUD_TBL, ['full_name','name','title']);

/* detect submissions columns */
$SUB_TBL = 'lms_submissions';
$fileCol = col_exists($mysqli,$SUB_TBL,'file_path') ? 'file_path'
        : (col_exists($mysqli,$SUB_TBL,'file') ? 'file'
        : (col_exists($mysqli,$SUB_TBL,'file_name') ? 'file_name' : null));
$subTimeCol = col_exists($mysqli,$SUB_TBL,'submitted_at') ? 'submitted_at'
            : (col_exists($mysqli,$SUB_TBL,'created_at') ? 'created_at'
            : (col_exists($mysqli,$SUB_TBL,'submitted_on') ? 'submitted_on' : null));

/* ---------- show picker if params missing ---------- */
if ($assessment_id <= 0 || $batch_id <= 0) {
  $courseLabel = label_col($mysqli,'courses',['course_name','name','title','label']);
  $batchLabel  = label_col($mysqli,'batches',['batch_name','name','title']);

  // assessments (use fetch_assoc loop, not fetch_all)
  $assess = [];
  if ($rs = $mysqli->query("
      SELECT a.id, a.title, COALESCE(c.$courseLabel,'') AS course_label
      FROM lms_assessments a
      LEFT JOIN courses c ON c.id=a.course_id
      ORDER BY a.id DESC
      LIMIT 150")) {
    while($r=$rs->fetch_assoc()) $assess[]=$r; $rs->free();
  }

  // batches
  $batches = [];
  if ($rs = $mysqli->query("SELECT b.id, b.$batchLabel AS label FROM batches b ORDER BY b.id DESC LIMIT 150")) {
    while($r=$rs->fetch_assoc()) $batches[]=$r; $rs->free();
  }

  $PAGE_TITLE  = 'Submissions — choose assessment & batch';
  $ACTIVE_MENU = 'assessments';
  require APP_ROOT . '/includes/admin_layout_top.php';
  ?>
  <div class="card shadow-sm">
    <div class="card-header"><strong>Open submissions</strong></div>
    <div class="card-body">
      <form method="get" class="row g-3">
        <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-5">
          <label class="form-label">Batch</label>
          <select name="batch_id" class="form-select" required>
            <option value="">— choose —</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-12">
          <button class="btn btn-primary">Open</button>
          <a class="btn btn-outline-secondary" href="<?= app_base_web() ?>/admin/assessments/index.php">Back to Assessments</a>
        </div>
      </form>
    </div>
  </div>
  <?php
  require APP_ROOT . '/includes/admin_layout_bottom.php';
  exit;
}

/* ---------- details for header ---------- */
$courseLabel = label_col($mysqli,'courses',['course_name','name','title','label']);
$batchLabel  = label_col($mysqli,'batches',['batch_name','name','title']);

$A = $mysqli->prepare("
  SELECT a.title, a.type, COALESCE(c.$courseLabel,'') AS course_label
  FROM lms_assessments a
  LEFT JOIN courses c ON c.id=a.course_id
  WHERE a.id=? LIMIT 1
");
$A->bind_param('i',$assessment_id);
$A->execute(); $ass = stmt_one_assoc($A); $A->close();

$B = $mysqli->prepare("SELECT $batchLabel AS label FROM batches WHERE id=? LIMIT 1");
$B->bind_param('i',$batch_id);
$B->execute(); $bat = stmt_one_assoc($B); $B->close();

/* ---------- load submissions for this assessment+batch ---------- */
$scoreExpr = "COALESCE(s.final_score, s.provisional_score, s.score)";
$statExpr  = "COALESCE(s.workflow_status, s.status)";
$timeExpr  = $subTimeCol ? "s.`{$subTimeCol}`" : "NULL";

$selectFile = $fileCol ? ", s.`{$fileCol}` AS file_path" : ", NULL AS file_path";

$sql = "
  SELECT s.id, s.student_id, $timeExpr AS submitted_at $selectFile,
         $statExpr AS st, $scoreExpr AS score,
         stt.`$studLabel` AS student_name
  FROM `{$SUB_TBL}` s
  JOIN `{$STUD_TBL}` stt ON stt.id = s.student_id
  WHERE s.assessment_id=? AND s.batch_id=?
  ORDER BY $timeExpr DESC, s.id DESC
";
$S = $mysqli->prepare($sql);
$S->bind_param('ii',$assessment_id,$batch_id);
$S->execute(); $rows = stmt_all_assoc($S); $S->close();

/* ---------- layout ---------- */
$PAGE_TITLE  = 'Submissions — '.esc($ass['title'] ?? ('Assessment #'.$assessment_id));
$ACTIVE_MENU = 'assessments';
require APP_ROOT . '/includes/admin_layout_top.php';
?>
<div class="d-flex align-items-center justify-content-between mb-2">
  <h5 class="m-0">
    Submissions — <?= esc($ass['title'] ?? ('#'.$assessment_id)) ?>
    <?php if (!empty($ass['type'])): ?>
      <span class="text-muted text-uppercase small ms-2"><?= esc($ass['type']) ?></span>
    <?php endif; ?>
  </h5>
  <a class="btn btn-sm btn-outline-secondary" href="<?= app_base_web() ?>/admin/assessments/index.php">← Back to Assessments</a>
</div>
<div class="small text-muted mb-3">
  Course: <?= esc($ass['course_label'] ?? '') ?> · Batch: <?= esc($bat['label'] ?? ('#'.$batch_id)) ?>
</div>

<div class="card">
  <div class="card-body table-responsive">
    <table class="table table-sm align-middle mb-0">
      <thead class="table-light">
        <tr>
          <th style="min-width:220px">Student</th>
          <th style="width:180px">Submitted</th>
          <th>Status</th>
          <th style="width:120px">Score</th>
          <th style="width:120px" class="text-end">File</th>
        </tr>
      </thead>
      <tbody>
      <?php if(!$rows): ?>
        <tr><td colspan="5" class="text-center text-muted">No submissions yet.</td></tr>
      <?php else: foreach($rows as $r): ?>
        <tr>
          <td>
            <strong><?= esc($r['student_name']) ?></strong>
            <div class="text-muted small">ID: <?= (int)$r['student_id'] ?></div>
          </td>
          <td class="small"><?= esc((string)($r['submitted_at'] ?? '')) ?></td>
          <td class="text-uppercase small"><?= esc((string)($r['st'] ?? '')) ?></td>
          <td><?= isset($r['score']) && $r['score'] !== '' ? (float)$r['score'] : '—' ?></td>
          <td class="text-end">
            <?php
              $href = !empty($r['file_path']) ? webify_upload_href((string)$r['file_path']) : '';
              if ($href) {
                echo '<a class="btn btn-sm btn-outline-primary" target="_blank" rel="noopener" href="'.esc($href).'">Open file</a>';
              } else {
                echo '<span class="badge bg-secondary">No file</span>';
              }
            ?>
          </td>
        </tr>
      <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>
</div>

<?php require APP_ROOT . '/includes/admin_layout_bottom.php';
