<?php
// /elms/admin/assessments/resubmit_whitelist.php
// Resubmit whitelist manager with search & bulk add.

if (session_status() === PHP_SESSION_NONE) session_start();

define('APP_ROOT', dirname(__DIR__, 2)); // .../elms
require_once APP_ROOT . '/includes/auth.php';
require_role(['super_admin','admin','branch_admin','course_coordinator','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');

/* ---------- helpers ---------- */
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
function gi($k){ return (isset($_REQUEST[$k]) && $_REQUEST[$k] !== '' ? (int)$_REQUEST[$k] : null); }
function gs($k){ $v=trim((string)($_REQUEST[$k] ?? '')); return $v===''?null:$v; }
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 pick_table(mysqli $db, array $cands): ?string { foreach($cands as $t) if (table_exists($db,$t)) return $t; return null; }
function label_col(mysqli $db,string $t,array $cands,string $fallback='id'): 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 $fallback;
}
function flash($msg,$type='info'){ $_SESSION['__flash'][]=['t'=>$type,'m'=>$msg]; }
function flashes(){ $out=$_SESSION['__flash']??[]; unset($_SESSION['__flash']); return $out; }
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); }

/* ---------- detect schema ---------- */
$ASSESS_TBL = pick_table($mysqli, ['lms_assessments','assessments']);
$STUD_TBL   = pick_table($mysqli, ['students','studentss']);
$WL_TBL     = pick_table($mysqli, ['lms_resubmit_whitelist','assessment_resubmit_whitelist','resubmit_whitelist']);

$assTitle   = $ASSESS_TBL ? label_col($mysqli,$ASSESS_TBL,['title','name'],'id') : 'id';
$studName   = $STUD_TBL   ? label_col($mysqli,$STUD_TBL,['full_name','name','title'],'id') : 'id';
$studRef    = $STUD_TBL   ? label_col($mysqli,$STUD_TBL,['reference_no','ref_no','student_ref'],'id') : 'id';

/* ---------- inputs ---------- */
$assessment_id = gi('assessment_id');

/* ---------- actions ---------- */
if ($WL_TBL && $assessment_id) {
  // Add by free-text IDs
  if (isset($_POST['action']) && $_POST['action']==='add' && ($raw = gs('student_ids'))) {
    $ids = preg_split('/[,\s]+/', $raw, -1, PREG_SPLIT_NO_EMPTY);
    $ok=0; $skipped=0;
    foreach ($ids as $sid) {
      if (!ctype_digit($sid)) { $skipped++; continue; }
      $sid = (int)$sid;
      // ensure student exists (if table present)
      $exists = true;
      if ($STUD_TBL) {
        $rs = $mysqli->query("SELECT 1 FROM `{$STUD_TBL}` WHERE id={$sid} LIMIT 1");
        $exists = $rs && $rs->num_rows>0; if ($rs) $rs->free();
      }
      if (!$exists) { $skipped++; continue; }
      $by = (int)($_SESSION['user']['id'] ?? $_SESSION['admin']['id'] ?? 0);
      $mysqli->query("INSERT IGNORE INTO `{$WL_TBL}` (assessment_id, student_id, created_by, created_at)
                      VALUES ({$assessment_id}, {$sid}, {$by}, NOW())");
      $ok++;
    }
    if ($ok)      flash("Added {$ok} student(s) to whitelist.","success");
    if ($skipped) flash("Skipped {$skipped} invalid/missing student IDs.","warning");
    header('Location: '.mk_url()); exit;
  }

  // Bulk add from search results
  if (isset($_POST['action']) && $_POST['action']==='bulk_add' && !empty($_POST['selected'])) {
    $sel = array_filter(array_map('intval', (array)$_POST['selected']));
    $by = (int)($_SESSION['user']['id'] ?? $_SESSION['admin']['id'] ?? 0);
    $ok=0;
    foreach ($sel as $sid) {
      $mysqli->query("INSERT IGNORE INTO `{$WL_TBL}` (assessment_id, student_id, created_by, created_at)
                      VALUES ({$assessment_id}, {$sid}, {$by}, NOW())");
      $ok++;
    }
    if ($ok) flash("Added {$ok} selected student(s).","success");
    header('Location: '.mk_url(['find'=>$_GET['find'] ?? null])); exit;
  }

  // Remove single
  if (isset($_POST['action']) && $_POST['action']==='remove' && ($sid = gi('student_id'))) {
    $mysqli->query("DELETE FROM `{$WL_TBL}` WHERE assessment_id={$assessment_id} AND student_id={$sid} LIMIT 1");
    flash("Removed student #{$sid} from whitelist.","success");
    header('Location: '.mk_url()); exit;
  }
}

/* ---------- page ---------- */
$PAGE_TITLE  = 'Resubmit Whitelist';
$ACTIVE_MENU = 'assessments';
require APP_ROOT . '/includes/admin_layout_top.php';
?>

<div class="container-fluid">
  <h5 class="mb-3">Assessment Resubmit Whitelist</h5>

  <?php foreach (flashes() as $f): ?>
    <div class="alert alert-<?php echo esc($f['t']); ?> py-2"><?php echo esc($f['m']); ?></div>
  <?php endforeach; ?>

  <?php if (!$ASSESS_TBL): ?>
    <div class="alert alert-danger">Assessments table not found. Expected <code>lms_assessments</code> or <code>assessments</code>.</div>
    <?php require APP_ROOT . '/includes/admin_layout_bottom.php'; exit; ?>
  <?php endif; ?>

  <?php if (!$assessment_id): ?>
    <!-- Assessment picker -->
    <div class="card">
      <div class="card-body">
        <form class="row g-2 mb-3" method="get">
          <div class="col-md-6">
            <label class="form-label">Search assessments</label>
            <input type="text" class="form-control" name="q" value="<?php echo esc($_GET['q'] ?? ''); ?>" placeholder="Type part of the title or ID and press Apply">
          </div>
          <div class="col-md-2 d-grid align-items-end">
            <button class="btn btn-primary mt-4">Apply</button>
          </div>
        </form>
        <?php
          $q = trim((string)($_GET['q'] ?? ''));
          $where = '';
          if ($q !== '') {
            $qsql = $mysqli->real_escape_string($q);
            if (ctype_digit($q)) {
              $where = "WHERE a.id=".(int)$q." OR a.`{$assTitle}` LIKE '%{$qsql}%'";
            } else {
              $where = "WHERE a.`{$assTitle}` LIKE '%{$qsql}%'";
            }
          }
          $sql = "SELECT a.id, a.`{$assTitle}` AS title FROM `{$ASSESS_TBL}` a {$where} ORDER BY a.id DESC LIMIT 50";
          $rows=[]; if ($rs=$mysqli->query($sql)) { while($r=$rs->fetch_assoc()) $rows[]=$r; $rs->free(); }
        ?>
        <div class="table-responsive">
          <table class="table table-sm align-middle mb-0">
            <thead class="table-light"><tr><th>ID</th><th>Title</th><th></th></tr></thead>
            <tbody>
              <?php if(!$rows): ?>
                <tr><td colspan="3" class="text-center text-muted">No assessments found.</td></tr>
              <?php else: foreach($rows as $r): ?>
                <tr>
                  <td>#<?php echo (int)$r['id']; ?></td>
                  <td><?php echo esc($r['title']); ?></td>
                  <td class="text-end">
                    <a class="btn btn-sm btn-outline-primary" href="<?php echo esc(mk_url(['assessment_id'=>$r['id']])); ?>">Manage whitelist</a>
                  </td>
                </tr>
              <?php endforeach; endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>

  <?php else: ?>
    <!-- Manage whitelist -->
    <?php
      $ass = ['id'=>$assessment_id,'title'=>'Assessment #'.$assessment_id];
      if ($rs=$mysqli->query("SELECT id, `{$assTitle}` AS title FROM `{$ASSESS_TBL}` WHERE id={$assessment_id} LIMIT 1")) {
        if ($r=$rs->fetch_assoc()) $ass=$r; $rs->free();
      }
    ?>

    <div class="d-flex justify-content-between align-items-center mb-2">
      <div class="h6 mb-0">Managing whitelist for: <span class="fw-semibold"><?php echo esc($ass['title']); ?></span> <span class="text-muted">(#<?php echo (int)$ass['id']; ?>)</span></div>
      <div><a class="btn btn-sm btn-outline-secondary" href="<?php echo esc(mk_url(['assessment_id'=>null,'q'=>null])); ?>">Choose another</a></div>
    </div>

    <?php if (!$WL_TBL): ?>
      <div class="alert alert-warning">
        <div class="fw-semibold mb-1">Whitelist table not found.</div>
        Create it once with this SQL:
        <pre class="mb-0" style="white-space:pre-wrap">
CREATE TABLE `lms_resubmit_whitelist` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `assessment_id` INT UNSIGNED NOT NULL,
  `student_id` INT UNSIGNED NOT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_assessment_student` (`assessment_id`,`student_id`),
  KEY `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        </pre>
      </div>
    <?php else: ?>
      <?php
        // Current whitelist
        $wl=[]; $wlIds=[];
        $studSel = $STUD_TBL ? ", COALESCE(s.`{$studName}`,'(unknown)') AS student_name, COALESCE(s.`{$studRef}`,'—') AS ref_no" : ", NULL AS student_name, NULL AS ref_no";
        $join = $STUD_TBL ? "LEFT JOIN `{$STUD_TBL}` s ON s.id=w.student_id" : "";
        $sql = "SELECT w.student_id, w.created_at {$studSel}
                FROM `{$WL_TBL}` w
                {$join}
                WHERE w.assessment_id={$assessment_id}
                ORDER BY w.created_at DESC, w.student_id DESC";
        if ($rs=$mysqli->query($sql)) {
          while($r=$rs->fetch_assoc()){ $wl[]=$r; $wlIds[(int)$r['student_id']]=true; }
          $rs->free();
        }
      ?>

      <!-- Add by raw IDs -->
      <div class="card mb-3">
        <div class="card-body">
          <form method="post" class="row g-2">
            <input type="hidden" name="assessment_id" value="<?php echo (int)$assessment_id; ?>">
            <input type="hidden" name="action" value="add">
            <div class="col-md-8">
              <label class="form-label">Add Student IDs (comma or space separated)</label>
              <input class="form-control" name="student_ids" placeholder="e.g. 101, 102 103">
            </div>
            <div class="col-md-2 d-grid align-items-end">
              <button class="btn btn-primary mt-4">Add to whitelist</button>
            </div>
          </form>
          <div class="text-muted small mt-2">Tip: paste many IDs at once (commas or spaces).</div>
        </div>
      </div>

      <!-- Search & pick students -->
      <div class="card mb-3">
        <div class="card-header"><strong>Find students by name / ref / ID</strong></div>
        <div class="card-body">
          <form class="row g-2 mb-2" method="get">
            <input type="hidden" name="assessment_id" value="<?php echo (int)$assessment_id; ?>">
            <div class="col-md-6">
              <input class="form-control" name="find" value="<?php echo esc($_GET['find'] ?? ''); ?>" placeholder="Type name, ref or ID, then Apply">
            </div>
            <div class="col-md-2 d-grid">
              <button class="btn btn-outline-secondary">Apply</button>
            </div>
          </form>
          <?php
            $find = gs('find');
            $candidates=[];
            if ($find !== null && $STUD_TBL) {
              $q = $mysqli->real_escape_string($find);
              if (ctype_digit($find)) {
                $where = "s.id=".(int)$find." OR s.`{$studName}` LIKE '%{$q}%' OR s.`{$studRef}` LIKE '%{$q}%'";
              } else {
                $where = "s.`{$studName}` LIKE '%{$q}%' OR s.`{$studRef}` LIKE '%{$q}%'";
              }
              $sql = "SELECT s.id, COALESCE(s.`{$studName}`,'(unknown)') AS name, COALESCE(s.`{$studRef}`,'—') AS ref
                      FROM `{$STUD_TBL}` s
                      WHERE {$where}
                      ORDER BY s.id DESC
                      LIMIT 50";
              if ($rs=$mysqli->query($sql)) { while($r=$rs->fetch_assoc()) $candidates[]=$r; $rs->free(); }
            }
          ?>
          <?php if ($find !== null): ?>
            <form method="post">
              <input type="hidden" name="assessment_id" value="<?php echo (int)$assessment_id; ?>">
              <input type="hidden" name="action" value="bulk_add">
              <div class="table-responsive">
                <table class="table table-sm align-middle mb-0">
                  <thead class="table-light"><tr><th style="width:42px"></th><th style="width:120px">ID</th><th>Name</th><th>Ref</th><th>Status</th></tr></thead>
                  <tbody>
                  <?php if(!$candidates): ?>
                    <tr><td colspan="5" class="text-center text-muted">No matches.</td></tr>
                  <?php else: foreach($candidates as $c): $id=(int)$c['id']; $in = isset($wlIds[$id]); ?>
                    <tr>
                      <td><input type="checkbox" name="selected[]" value="<?php echo $id; ?>" <?php echo $in?'disabled':''; ?>></td>
                      <td>#<?php echo $id; ?></td>
                      <td><?php echo esc($c['name']); ?></td>
                      <td><?php echo esc($c['ref']); ?></td>
                      <td><?php echo $in ? '<span class="badge bg-success">In whitelist</span>' : '<span class="badge bg-secondary">Not added</span>'; ?></td>
                    </tr>
                  <?php endforeach; endif; ?>
                  </tbody>
                </table>
              </div>
              <?php if($candidates): ?>
                <div class="mt-2"><button class="btn btn-primary">Add selected</button></div>
              <?php endif; ?>
            </form>
          <?php else: ?>
            <div class="text-muted small">Enter a search term above to find students.</div>
          <?php endif; ?>
        </div>
      </div>

      <!-- Current whitelist -->
      <div class="card">
        <div class="card-header"><strong>Currently whitelisted</strong></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:120px">Student ID</th><th>Student</th><th>Ref</th><th>Added</th><th style="width:110px"></th></tr></thead>
              <tbody>
              <?php if(!$wl): ?>
                <tr><td colspan="5" class="text-center text-muted">No students whitelisted yet.</td></tr>
              <?php else: foreach($wl as $r): ?>
                <tr>
                  <td>#<?php echo (int)$r['student_id']; ?></td>
                  <td><?php echo esc($r['student_name'] ?? '(unknown)'); ?></td>
                  <td><?php echo esc($r['ref_no'] ?? '—'); ?></td>
                  <td><?php echo esc($r['created_at']); ?></td>
                  <td class="text-end">
                    <form method="post" onsubmit="return confirm('Remove this student from whitelist?');">
                      <input type="hidden" name="assessment_id" value="<?php echo (int)$assessment_id; ?>">
                      <input type="hidden" name="student_id" value="<?php echo (int)$r['student_id']; ?>">
                      <input type="hidden" name="action" value="remove">
                      <button class="btn btn-sm btn-outline-danger">Remove</button>
                    </form>
                  </td>
                </tr>
              <?php endforeach; endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    <?php endif; ?>
  <?php endif; ?>
</div>

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