<?php
// /elms/lecturer/_probe.php
declare(strict_types=1);
if (session_status()===PHP_SESSION_NONE) session_start();
require_once __DIR__ . '/../includes/db.php';

$lecturer_id = (int)($_SESSION['lecturer_id'] ?? $_SESSION['user']['id'] ?? 0);
function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
function t_exists(string $t): bool { global $mysqli;
  $st=$mysqli->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?");
  $st->bind_param('s',$t); $st->execute();
  $ok=(bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
}

$hasLB = t_exists('lecturer_batches');
$hasLC = t_exists('lecturer_courses');

$sql = "
SELECT
  s.id, s.workflow_status, s.submitted_at,
  s.assessment_id, s.student_id, s.batch_id,
  a.title AS assess_title, a.lecturer_id, a.course_id,
  st.full_name AS student_name,
  COALESCE(b.batch_name,b.name,CONCAT('Batch ',b.id)) AS batch_label
FROM lms_submissions s
JOIN lms_assessments a ON a.id=s.assessment_id
JOIN studentss st ON st.id=s.student_id
JOIN batches b ON b.id=s.batch_id
WHERE s.workflow_status IN ('submitted','resubmitted')
  AND (
        a.lecturer_id = ?
     ".($hasLB ? " OR EXISTS (SELECT 1 FROM lecturer_batches lb WHERE lb.lecturer_id=? AND lb.batch_id=s.batch_id) " : "")."
     ".($hasLC ? " OR EXISTS (SELECT 1 FROM lecturer_courses lc WHERE lc.lecturer_id=? AND lc.course_id=a.course_id) " : "")."
      )
ORDER BY s.submitted_at ASC
LIMIT 50";
$params = [$lecturer_id]; $types="i";
if ($hasLB){ $params[]=$lecturer_id; $types.="i"; }
if ($hasLC){ $params[]=$lecturer_id; $types.="i"; }

$rows=[];
$st=$mysqli->prepare($sql);
$st->bind_param($types, ...$params);
$st->execute();
$r=$st->get_result();
while($x=$r->fetch_assoc()) $rows[]=$x;
$st->close();
?>
<!doctype html><meta charset="utf-8"><title>Lecturer Probe</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<div class="container py-4">
  <h4>Lecturer probe</h4>
  <div class="mb-2"><b>Lecturer ID:</b> <?= (int)$lecturer_id ?></div>
  <div class="mb-2"><b>Has lecturer_batches?</b> <?= $hasLB?'yes':'no' ?> · <b>Has lecturer_courses?</b> <?= $hasLC?'yes':'no' ?></div>
  <?php if (!$rows): ?>
    <div class="alert alert-warning">
      No matches. Causes:
      <ul class="mb-0">
        <li>No submissions in <code>submitted/resubmitted</code> state.</li>
        <li>This lecturer isn’t owner (<code>a.lecturer_id</code>) and not mapped via batches/courses.</li>
        <li>Wrong login (no lecturer id in session).</li>
      </ul>
    </div>
  <?php else: ?>
    <table class="table table-sm">
      <thead class="table-light"><tr>
        <th>#</th><th>Student</th><th>Assessment</th><th>Batch</th><th>Status</th><th>Submitted</th>
      </tr></thead>
      <tbody>
      <?php foreach($rows as $x): ?>
        <tr>
          <td><?= (int)$x['id'] ?></td>
          <td><?= esc($x['student_name']) ?></td>
          <td><?= esc($x['assess_title']) ?> (a.lecturer_id=<?= (int)$x['lecturer_id'] ?>)</td>
          <td><?= esc($x['batch_label']) ?></td>
          <td><?= esc($x['workflow_status']) ?></td>
          <td class="small text-muted"><?= esc($x['submitted_at']) ?></td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>
  <?php endif; ?>
</div>
