<?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);
/* ---------- save marks / feedback ---------- */
if (($_SERVER['REQUEST_METHOD'] ?? '') === 'POST' && ($_POST['action'] ?? '') === 'save_marks') {
  $submission_id = (int)($_POST['submission_id'] ?? 0);
  $score         = trim((string)($_POST['score'] ?? ''));
  $feedback      = trim((string)($_POST['feedback'] ?? ''));

  if ($submission_id <= 0) {
    $_SESSION['flash_err'] = 'Invalid submission.';
    header("Location: submissions.php?assessment_id={$assessment_id}&batch_id={$batch_id}");
    exit;
  }

  /* detect submissions table */
  $MARK_SUB_TBL = null;
  foreach (['lms_assessment_submissions', 'lms_submissions'] as $tryTbl) {
    if (table_exists($mysqli, $tryTbl) && col_exists($mysqli, $tryTbl, 'id')) {
      $MARK_SUB_TBL = $tryTbl;
      break;
    }
  }

  if (!$MARK_SUB_TBL) {
    $_SESSION['flash_err'] = 'Submissions table not found.';
    header("Location: submissions.php?assessment_id={$assessment_id}&batch_id={$batch_id}");
    exit;
  }

  /* choose score column */
  $scoreCol = null;
  foreach (['marks', 'score', 'final_score', 'provisional_score'] as $c) {
    if (col_exists($mysqli, $MARK_SUB_TBL, $c)) {
      $scoreCol = $c;
      break;
    }
  }

  if (!$scoreCol) {
    $mysqli->query("ALTER TABLE `$MARK_SUB_TBL` ADD COLUMN `marks` DECIMAL(10,2) NULL");
    $scoreCol = 'marks';
  }

  /* feedback column */
  if (!col_exists($mysqli, $MARK_SUB_TBL, 'feedback')) {
    $mysqli->query("ALTER TABLE `$MARK_SUB_TBL` ADD COLUMN `feedback` TEXT NULL");
  }

  /* status column */
  $statusCol = null;
  foreach (['workflow_status', 'status'] as $c) {
    if (col_exists($mysqli, $MARK_SUB_TBL, $c)) {
      $statusCol = $c;
      break;
    }
  }

  if (!$statusCol) {
    $mysqli->query("ALTER TABLE `$MARK_SUB_TBL` ADD COLUMN `status` VARCHAR(50) NOT NULL DEFAULT 'submitted'");
    $statusCol = 'status';
  }

  $scoreVal = ($score === '') ? null : (float)$score;

  if ($scoreVal !== null && ($scoreVal < 0 || $scoreVal > 100)) {
    $_SESSION['flash_err'] = 'Marks must be between 0 and 100.';
    header("Location: submissions.php?assessment_id={$assessment_id}&batch_id={$batch_id}");
    exit;
  }

  $stmt = $mysqli->prepare("
    UPDATE `$MARK_SUB_TBL`
    SET `$scoreCol` = ?,
        `feedback` = ?,
        `$statusCol` = 'marked'
    WHERE id = ?
  ");

  $stmt->bind_param('dsi', $scoreVal, $feedback, $submission_id);
  $stmt->execute();
  $stmt->close();

  $_SESSION['flash_ok'] = 'Marks saved successfully.';
  header("Location: submissions.php?assessment_id={$assessment_id}&batch_id={$batch_id}");
  exit;
}

/* ---------- 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 table + columns */
$SUB_TBL = null;

/*
  Student portal saves to lms_assessment_submissions.
  Old/admin system may use lms_submissions.
  Prefer lms_assessment_submissions if it exists.
*/
foreach (['lms_assessment_submissions', 'lms_submissions'] as $tryTbl) {
  if (table_exists($mysqli, $tryTbl) && col_exists($mysqli, $tryTbl, 'assessment_id') && col_exists($mysqli, $tryTbl, 'student_id')) {
    $SUB_TBL = $tryTbl;
    break;
  }
}

if (!$SUB_TBL) {
  http_response_code(500);
  exit('Submissions table not found');
}

$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';
  ?>
  <?php if (!empty($_SESSION['flash_ok'])): ?>
  <div class="alert alert-success py-2">
    <?= esc($_SESSION['flash_ok']) ?>
  </div>
  <?php unset($_SESSION['flash_ok']); ?>
<?php endif; ?>

<?php if (!empty($_SESSION['flash_err'])): ?>
  <div class="alert alert-danger py-2">
    <?= esc($_SESSION['flash_err']) ?>
  </div>
  <?php unset($_SESSION['flash_err']); ?>
<?php endif; ?>
  <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 ---------- */

/* score columns differ between tables */
$scoreParts = [];

foreach (['final_score', 'provisional_score', 'score', 'marks'] as $sc) {
  if (col_exists($mysqli, $SUB_TBL, $sc)) {
    $scoreParts[] = "s.`$sc`";
  }
}

$scoreExpr = $scoreParts ? "COALESCE(" . implode(',', $scoreParts) . ")" : "NULL";

/* status columns differ between tables */
$statusParts = [];

foreach (['workflow_status', 'status'] as $sc) {
  if (col_exists($mysqli, $SUB_TBL, $sc)) {
    $statusParts[] = "s.`$sc`";
  }
}

$statExpr = $statusParts ? "COALESCE(" . implode(',', $statusParts) . ")" : "'submitted'";

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

$selectOriginalName = col_exists($mysqli, $SUB_TBL, 'original_name')
  ? ", s.`original_name` AS original_name"
  : ", NULL AS original_name";

/*
  lms_assessment_submissions does not have batch_id.
  So if SUB_TBL has batch_id, filter by assessment + batch.
  If not, filter by assessment only.
*/
$whereSql = "s.assessment_id=?";
$bindTypes = "i";
$bindVals = [$assessment_id];

if (col_exists($mysqli, $SUB_TBL, 'batch_id')) {
  $whereSql .= " AND s.batch_id=?";
  $bindTypes .= "i";
  $bindVals[] = $batch_id;
}

$sql = "
  SELECT 
    s.id, 
    s.student_id, 
    $timeExpr AS submitted_at 
    $selectFile
    $selectOriginalName,
    $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 $whereSql
  ORDER BY $timeExpr DESC, s.id DESC
";

$S = $mysqli->prepare($sql);
$S->bind_param($bindTypes, ...$bindVals);
$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:260px">Marks / Feedback</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>
  <form method="post" class="d-flex flex-column gap-2">
    <input type="hidden" name="action" value="save_marks">
    <input type="hidden" name="submission_id" value="<?= (int)$r['id'] ?>">

    <input type="number"
           name="score"
           class="form-control form-control-sm"
           placeholder="Marks"
           min="0"
           max="100"
           step="0.01"
           value="<?= isset($r['score']) && $r['score'] !== '' ? esc($r['score']) : '' ?>">

    <textarea name="feedback"
              class="form-control form-control-sm"
              rows="2"
              placeholder="Feedback"><?= esc($r['feedback'] ?? '') ?></textarea>

    <button class="btn btn-sm btn-primary">
      Save Marks
    </button>
  </form>
</td>
          <td class="text-end">
            <?php
              $href = !empty($r['file_path']) ? webify_upload_href((string)$r['file_path']) : '';
              if ($href) {
                $btnText = !empty($r['original_name']) ? 'Open file' : 'Open file';
echo '<a class="btn btn-sm btn-outline-primary" target="_blank" rel="noopener" href="'.esc($href).'">'.$btnText.'</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';
