<?php
declare(strict_types=1);
require __DIR__ . '/_common.php';
ec_require_coordinator();

$PAGE_TITLE = 'Submissions';
$ACTIVE     = 'submissions';

list($scopeSql, $scopeParams) = coord_branch_scope('b');

/* Build dynamic SELECT parts based on available columns */
$hasSubsTable   = tbl($mysqli, 'lms_submissions');
$hasSubmittedAt = $hasSubsTable && col($mysqli, 'lms_submissions', 'submitted_at');

$selectSubs = $hasSubsTable ? "COUNT(s.id) AS total_subs" : "0 AS total_subs";
$selectLast = $hasSubmittedAt ? "MAX(s.submitted_at) AS last_submit" : "NULL AS last_submit";
$joinSubs   = $hasSubsTable ? "LEFT JOIN lms_submissions s ON s.assessment_id = a.id" : "";

$sql = "
  SELECT a.id, a.title, a.available_from, a.due_at, $selectSubs, $selectLast
  FROM lms_assessments a
  JOIN lms_assessment_batches ab ON ab.assessment_id = a.id
  JOIN batches b ON b.id = ab.batch_id
  $joinSubs
  WHERE $scopeSql
  GROUP BY a.id, a.title, a.available_from, a.due_at
  ORDER BY a.id DESC
";
$rows = qall($mysqli, $sql, $scopeParams, str_repeat('i', count($scopeParams)));
?>
<!doctype html><html lang="en"><head>
  <meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Submissions</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css" rel="stylesheet">
</head><body>
<div class="ec-shell">
  <?php include __DIR__ . '/partials/sidebar.php'; ?>
  <div class="ec-content">
    <?php include __DIR__ . '/partials/topbar.php'; ?>
    <div class="wrap container-fluid">
      <div class="card section-card">
        <div class="card-header bg-white d-flex justify-content-between align-items-center">
          <strong>Submissions by Assessment</strong>
          <?php if (!$hasSubsTable): ?>
            <span class="badge bg-warning text-dark">lms_submissions table not found — showing zeros</span>
          <?php endif; ?>
        </div>
        <div class="card-body p-0">
          <div class="table-responsive">
            <table class="table table-hover mb-0">
              <thead class="table-light">
                <tr>
                  <th>Assessment</th>
                  <th>Open</th>
                  <th>Due</th>
                  <th class="text-end">Total submissions</th>
                  <th>Last submitted</th>
                </tr>
              </thead>
              <tbody>
              <?php if (!$rows): ?>
                <tr><td colspan="5" class="text-center text-muted py-4">No data.</td></tr>
              <?php else: foreach($rows as $r): ?>
                <tr>
                  <td class="fw-semibold"><?php echo h($r['title']); ?></td>
                  <td><?php echo h($r['available_from']); ?></td>
                  <td><?php echo h($r['due_at']); ?></td>
                  <td class="text-end"><?php echo (int)$r['total_subs']; ?></td>
                  <td><?php echo $r['last_submit'] ? h($r['last_submit']) : '—'; ?></td>
                </tr>
              <?php endforeach; endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>
</body></html>
