<?php
// /elms/lecturer/dashboard.php — v2 (clean UI, robust schema detection)
// - No external libs; uses elms-ui.css if present.
// - Auto-detects tables/columns (works with your dump).
// - KPIs: To Grade, Provisional, Under Review, My Graded Today
// - Lists: To Grade (latest), Corrections Requested, Recent Grading
// - Optional: quick range filter without breaking if cols missing.

declare(strict_types=1);

/* ========= SAFE BOOT ========= */
if (session_status()===PHP_SESSION_NONE) session_start();
@date_default_timezone_set('Asia/Colombo');
$DEBUG = false;                          // flip true to see the debug card
ini_set('display_errors', $DEBUG ? '1':'0');
ini_set('log_errors', '1');
ini_set('error_log', __DIR__.'/lecturer_dash_error.log');

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

/* ========= DB include (robust) ========= */
$attempts=[]; $loaded_db=null;
function try_include_db(array $paths, array &$attempts){
  foreach($paths as $p){ $attempts[]=$p.(is_file($p)?" [exists]":" [missing]"); if(is_file($p)){ require_once $p; return $p; } }
  return null;
}
$docroot = rtrim($_SERVER['DOCUMENT_ROOT'] ?? dirname(__DIR__,2), '/'); // e.g. /home/.../public_html
$here    = __DIR__;
$loaded_db = try_include_db([
  $docroot.'/elms/includes/db.php',
  $docroot.'/elms/includes/db_boot.php',
  dirname($here).'/includes/db.php',
  dirname($here).'/includes/db_boot.php',
  $here.'/../includes/db.php',
  $here.'/../includes/db_boot.php',
], $attempts);

// Manual connect if constants are defined
if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  if (defined('DB_HOST') && defined('DB_USER') && defined('DB_PASS') && defined('DB_NAME')) {
    $mysqli = @new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    if ($mysqli && !$mysqli->connect_errno) {
      $mysqli->set_charset('utf8mb4');
      $mysqli->query("SET time_zone = '+05:30'");
      $attempts[]='[manual connect] OK';
    } else {
      $attempts[]='[manual connect] FAIL: '.($mysqli? $mysqli->connect_error : 'no mysqli');
    }
  }
}
if (!isset($mysqli) || !($mysqli instanceof mysqli)) {
  http_response_code(500);
  echo "<h3>DB not initialized</h3><pre>".esc(implode("\n",$attempts))."</pre>";
  exit;
}
$mysqli->query("SET time_zone = '+05:30'");

/* ========= AUTH (soft) ========= */
if (!function_exists('require_role')) { function require_role($r){} }
require_role(['lecturer','LECTURER','admin','super_admin','course_coordinator','coordinator']);
$uid = (int)($_SESSION['user']['id'] ?? 0);
$who = $_SESSION['user']['full_name'] ?? ($_SESSION['user']['username'] ?? 'Lecturer');

/* ========= helpers ========= */
function table_exists(mysqli $db,string $t){ $pat=$db->real_escape_string($t); $res=$db->query("SHOW TABLES LIKE '$pat'"); $ok=$res && $res->num_rows>0; if($res)$res->free(); return $ok; }
function col_exists(mysqli $db,string $t,string $c){ $t=str_replace('`','``',$t); $c=$db->real_escape_string($c); $res=$db->query("SHOW COLUMNS FROM `$t` LIKE '$c'"); $ok=$res && $res->num_rows>0; if($res)$res->free(); return $ok; }
function fetch_all_stmt(mysqli_stmt $st): array {
  $rows=[]; 
  if (method_exists($st,'get_result')) { $rs=$st->get_result(); if($rs){ $rows=$rs->fetch_all(MYSQLI_ASSOC); $rs->free(); } }
  else { $meta=$st->result_metadata(); if($meta){ $fields=[]; $row=[]; $bind=[]; while($f=$meta->fetch_field()){ $fields[]=$f->name; $row[$f->name]=null; $bind[]=&$row[$f->name]; } call_user_func_array([$st,'bind_result'],$bind); while($st->fetch()){ $rows[] = array_map(fn($v)=>$v, $row); } } }
  return $rows;
}
function count_with(mysqli $db,string $sql,string $types='',array $vals=[]): int {
  $st=$db->prepare($sql); if(!$st) return 0; if($types) $st->bind_param($types,...$vals); $st->execute(); $n=0;
  if (method_exists($st,'get_result')) { $rs=$st->get_result(); if($rs){ $row=$rs->fetch_row(); $n=(int)($row[0]??0); $rs->free(); } }
  else { $st->bind_result($n); $st->fetch(); $n=(int)$n; }
  $st->close(); return $n;
}
function in_list(array $xs): string { return "'".implode("','", array_map(fn($v)=>str_replace("'","''",$v), $xs))."'"; }

/* ========= schema detection ========= */
$hasAssess    = table_exists($mysqli,'lms_assessments');
$hasSubs      = table_exists($mysqli,'lms_submissions');
$hasLectCourses = table_exists($mysqli,'lecturer_courses');
$hasLectBatches = table_exists($mysqli,'lecturer_batches');
$studentsTbl  = table_exists($mysqli,'studentss') ? 'studentss' : (table_exists($mysqli,'students') ? 'students' : null);

$subHas = fn($c)=> $hasSubs && col_exists($mysqli,'lms_submissions',$c);
$assHas = fn($c)=> $hasAssess && col_exists($mysqli,'lms_assessments',$c);

$hasSubmittedAt = $subHas('submitted_at');
$hasCreatedAt   = $subHas('created_at');
$hasUpdatedAt   = $subHas('updated_at');
$hasGradedAt    = $subHas('graded_at');

$submittedExpr = $hasSubmittedAt ? 's.submitted_at'
               : ($hasCreatedAt   ? 's.created_at'
               : ($hasUpdatedAt   ? 's.updated_at'
               : ($hasGradedAt    ? 's.graded_at' : 'NULL')));

$updatedExpr   = $hasUpdatedAt   ? 's.updated_at'
               : ($hasSubmittedAt ? 's.submitted_at'
               : ($hasCreatedAt   ? 's.created_at'
               : ($hasGradedAt    ? 's.graded_at' : 'NULL')));

$gradedExpr    = $hasGradedAt ? 's.graded_at' : $updatedExpr;

// ORDER newest available timestamp
$orderParts=[]; if($hasSubmittedAt) $orderParts[]='s.submitted_at'; if($hasCreatedAt) $orderParts[]='s.created_at'; if($hasUpdatedAt) $orderParts[]='s.updated_at'; if($hasGradedAt) $orderParts[]='s.graded_at';
$orderExprQueue = $orderParts ? ('COALESCE('.implode(',',$orderParts).') DESC') : 's.id DESC';

/* ========= lecturer filter options =========
   Priority:
   1) If subs has lecturer_id -> filter on s.lecturer_id
   2) Else if assessments has lecturer_id -> join & filter by a.lecturer_id
   3) Else if mapping tables exist -> join lecturer_courses/batches
*/
$mapSubLect   = $subHas('lecturer_id');
$mapAssLect   = $assHas('lecturer_id');

$filterJoin = '';
$filterWhere= '1';
$types=''; $vals=[];

if ($uid>0) {
  if ($mapSubLect) {
    $filterWhere = 's.lecturer_id=?'; $types.='i'; $vals[]=$uid;
  } elseif ($mapAssLect) {
    $filterWhere = 'a.lecturer_id=?'; $types.='i'; $vals[]=$uid;
  } elseif ($hasLectCourses || $hasLectBatches) {
    if ($hasLectCourses) $filterJoin .= ' LEFT JOIN lecturer_courses lc ON lc.course_id=a.course_id';
    if ($hasLectBatches) $filterJoin .= ' LEFT JOIN lecturer_batches lb ON lb.batch_id=s.batch_id';
    $ors=[];
    if ($hasLectCourses){ $ors[]='lc.lecturer_id=?'; $types.='i'; $vals[]=$uid; }
    if ($hasLectBatches){ $ors[]='lb.lecturer_id=?'; $types.='i'; $vals[]=$uid; }
    $filterWhere = $ors ? ('('.implode(' OR ',$ors).')') : '1';
  }
}

/* ========= status buckets ========= */
$ST_TO_GRADE     = ['submitted','needs_correction'];
$ST_PROVISIONAL  = ['graded_provisional'];
$ST_UNDER_REVIEW = ['under_review','coord_review','coordinator_review'];
$ST_GRADED_ANY   = ['graded_provisional','under_review','coord_approved','approved'];

/* ========= optional time range filter (7d default) ========= */
$days = max(1, (int)($_GET['days'] ?? 7));
$rangeCond = " AND $gradedExpr >= DATE_SUB(NOW(), INTERVAL $days DAY) ";

/* ========= KPI cards ========= */
$toGrade = ($hasSubs && $hasAssess) ? count_with(
  $mysqli,
  "SELECT COUNT(*) FROM lms_submissions s
    JOIN lms_assessments a ON a.id=s.assessment_id
    $filterJoin
   WHERE $filterWhere AND s.workflow_status IN (".in_list($ST_TO_GRADE).")",
  $types,$vals
) : 0;

$provisional = ($hasSubs && $hasAssess) ? count_with(
  $mysqli,
  "SELECT COUNT(*) FROM lms_submissions s
    JOIN lms_assessments a ON a.id=s.assessment_id
    $filterJoin
   WHERE $filterWhere AND s.workflow_status IN (".in_list($ST_PROVISIONAL).")",
  $types,$vals
) : 0;

$underReview = ($hasSubs && $hasAssess) ? count_with(
  $mysqli,
  "SELECT COUNT(*) FROM lms_submissions s
    JOIN lms_assessments a ON a.id=s.assessment_id
    $filterJoin
   WHERE $filterWhere AND s.workflow_status IN (".in_list($ST_UNDER_REVIEW).")",
  $types,$vals
) : 0;

$gradedToday = ($hasSubs && ($hasGradedAt || $hasUpdatedAt)) ? count_with(
  $mysqli,
  "SELECT COUNT(*) FROM lms_submissions s
    JOIN lms_assessments a ON a.id=s.assessment_id
    $filterJoin
   WHERE $filterWhere
     AND ".($hasGradedAt ? 's.graded_at' : 's.updated_at')." >= CURDATE()
     AND s.workflow_status IN (".in_list($ST_GRADED_ANY).")",
  $types,$vals
) : 0;

/* ========= data lists ========= */
// To Grade (latest 10)
$queue = [];
if ($hasSubs && $hasAssess) {
  $joinStudent = $studentsTbl ? "LEFT JOIN `$studentsTbl` st ON st.id=s.student_id" : "";
  $studentExpr = $studentsTbl ? "COALESCE(st.full_name, CONCAT('#',s.student_id))" : "CONCAT('#',s.student_id)";
  $sql = "SELECT s.id, s.batch_id, $submittedExpr AS submitted_at,
                 a.title AS assessment, $studentExpr AS student
            FROM lms_submissions s
            JOIN lms_assessments a ON a.id=s.assessment_id
            $joinStudent
            $filterJoin
           WHERE $filterWhere
             AND s.workflow_status IN (".in_list($ST_TO_GRADE).")
        ORDER BY $orderExprQueue
           LIMIT 10";
  $st=$mysqli->prepare($sql); if($types)$st->bind_param($types,...$vals);
  $st->execute(); $queue=fetch_all_stmt($st); $st->close();
}

// Corrections
$corrections=[];
if ($hasSubs && $hasAssess) {
  $sql = "SELECT s.id, a.title AS assessment, $updatedExpr AS updated_at
            FROM lms_submissions s
            JOIN lms_assessments a ON a.id=s.assessment_id
            $filterJoin
           WHERE $filterWhere AND s.workflow_status='needs_correction'
        ORDER BY $updatedExpr DESC
           LIMIT 10";
  $st=$mysqli->prepare($sql); if($types)$st->bind_param($types,...$vals);
  $st->execute(); $corrections=fetch_all_stmt($st); $st->close();
}

// Recent grading (last X days)
$recent=[];
if ($hasSubs && $hasAssess) {
  $sql = "SELECT a.title AS assessment, $gradedExpr AS graded_at
            FROM lms_submissions s
            JOIN lms_assessments a ON a.id=s.assessment_id
            $filterJoin
           WHERE $filterWhere
             AND s.workflow_status IN (".in_list($ST_GRADED_ANY).")
             $rangeCond
        ORDER BY $gradedExpr DESC
           LIMIT 10";
  $st=$mysqli->prepare($sql); if($types)$st->bind_param($types,...$vals);
  $st->execute(); $recent=fetch_all_stmt($st); $st->close();
}

/* ========= VIEW ========= */
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Lecturer — Dashboard</title>
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <?php if (is_file(__DIR__.'/../assets/elms-ui.css')): ?>
    <link rel="stylesheet" href="../assets/elms-ui.css?v=3">
  <?php else: ?>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@picocss/pico@2/css/pico.min.css">
    <style>
      :root{--brand:#5b21b6} body{background:#f6f7fb}
      .page{display:grid;grid-template-columns:260px 1fr;min-height:100vh}
      .sidebar{background:#0f172a;color:#e2e8f0;padding:18px 14px}
      .side-head{font-weight:800;letter-spacing:.06em;text-transform:uppercase;margin-bottom:10px;color:#c7d2fe}
      .who{background:#111827;border:1px solid #1f2937;border-radius:12px;padding:10px 12px;margin-bottom:12px}
      .nav a{display:block;color:#cbd5e1;text-decoration:none;padding:8px 10px;border-radius:8px}
      .nav a.active,.nav a:hover{background:#1f2937;color:#fff}
      .content{padding:16px 20px}
      .topbar{display:flex;gap:12px;justify-content:space-between;align-items:center;margin-bottom:12px}
      .crumb{font-weight:700;font-size:18px}
      .wrap{max-width:1200px;margin-inline:auto}
      .cards{display:grid;grid-template-columns:repeat(4,1fr);gap:12px;margin-bottom:16px}
      .card{background:#fff;border:1px solid #e5e7eb;border-radius:14px;padding:14px}
      .card--stat{display:flex;gap:12px;align-items:center}
      .stat-icon{width:42px;height:42px;border-radius:10px;background:#ede9fe;display:grid;place-items:center}
      .stat-title{font-size:12px;letter-spacing:.08em;text-transform:uppercase;color:#6b7280}
      .stat-value{font-size:28px;font-weight:800}
      .btn{display:inline-block;padding:6px 12px;border:1px solid #c7cbd1;border-radius:8px;text-decoration:none}
      .brand{border-color:var(--brand);color:#fff;background:var(--brand)}
      .muted{color:#6b7280}
      .grid{display:grid;gap:12px}
      .table{width:100%;border-collapse:collapse}
      .table th,.table td{border-bottom:1px solid #eef2f7;padding:8px 6px;text-align:left}
      .footer{color:#94a3b8;margin:20px 0}
      @media (max-width: 992px){ .cards{grid-template-columns:repeat(2,1fr)} .page{grid-template-columns:80px 1fr} .sidebar .who,.sidebar .nav a{font-size:12px} }
      @media (max-width: 640px){ .cards{grid-template-columns:1fr} .grid{grid-template-columns:1fr} }
      .toolbar{display:flex;gap:8px;align-items:center;margin:0 0 12px}
      .toolbar input[type=number]{width:90px}
    </style>
  <?php endif; ?>
</head>
<body>
<div class="page">
  <aside class="sidebar">
    <div class="side-head">Lecturer</div>
    <div class="who">
      <div style="font-weight:700"><?= esc($who) ?></div>
      <div style="text-transform:uppercase;font-size:11px;letter-spacing:.08em">LECTURER</div>
    </div>
    <nav class="nav">
      <a class="active" href="./dashboard.php">🏠 Dashboard</a>
      <a href="./assessments/index.php">🧾 Assessments</a>
      <a href="../logout.php">🔓 Logout</a>
    </nav>
  </aside>

  <main class="content">
    <div class="topbar">
      <div class="crumb">Dashboard</div>
      <div class="when"><?= date('Y-m-d H:i') ?> (Sri Lanka)</div>
    </div>

    <div class="wrap">

      <div class="toolbar">
        <form method="get" class="toolbar">
          <label class="muted">Recent range (days):</label>
          <input type="number" name="days" value="<?= (int)$days ?>" min="1" max="90">
          <button class="btn">Apply</button>
        </form>
      </div>

      <div class="cards">
        <div class="card card--stat">
          <div class="stat-icon"><svg width="20" height="20" viewBox="0 0 24 24" fill="none"><path d="M3 13h4l2 3h6l2-3h4M6 7h12l3 6v7H3v-7l3-6Z" stroke="#5b21b6" stroke-width="1.5"/></svg></div>
          <div><div class="stat-title">To Grade</div><div class="stat-value"><?= (int)$toGrade ?></div><div><a class="btn brand" href="./assessments/index.php">Open Assessments</a></div></div>
        </div>
        <div class="card card--stat">
          <div class="stat-icon"><svg width="22" height="22" viewBox="0 0 24 24" fill="none"><path d="m5 12 4 4L19 6" stroke="#5b21b6" stroke-width="1.8"/></svg></div>
          <div><div class="stat-title">Provisional</div><div class="stat-value"><?= (int)$provisional ?></div><div class="muted">Use “Submit for approval”.</div></div>
        </div>
        <div class="card card--stat">
          <div class="stat-icon"><svg width="20" height="20" viewBox="0 0 24 24" fill="none"><path d="M8 2v3M16 2v3M3 9h18M4 5h16a1 1 0 0 1 1 1v13a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2V6a1 1 0 0 1 1-1Z" stroke="#5b21b6" stroke-width="1.5"/></svg></div>
          <div><div class="stat-title">Under Review</div><div class="stat-value"><?= (int)$underReview ?></div><div class="muted">Coordinator reviewing</div></div>
        </div>
        <div class="card card--stat">
          <div class="stat-icon"><svg width="22" height="22" viewBox="0 0 24 24" fill="none"><path d="m5 12 4 4L19 6" stroke="#5b21b6" stroke-width="1.8"/></svg></div>
          <div><div class="stat-title">My Graded Today</div><div class="stat-value"><?= (int)$gradedToday ?></div></div>
        </div>
      </div>

      <div class="grid" style="grid-template-columns:1.3fr .7fr">
        <div class="card">
          <div class="section-title" style="font-weight:700">To Grade (latest)</div>
          <?php if(!$queue): ?>
            <p class="muted">Nothing to grade.</p>
          <?php else: ?>
            <table class="table">
              <thead><tr><th>Student</th><th>Assessment</th><th>Batch</th><th>Submitted</th></tr></thead>
              <tbody>
                <?php foreach($queue as $q): ?>
                <tr>
                  <td><?= esc($q['student'] ?? '#'.$q['id']) ?></td>
                  <td><?= esc($q['assessment'] ?? '-') ?></td>
                  <td class="muted"><?= esc((string)($q['batch_id'] ?? '—')) ?></td>
                  <td class="muted"><?= esc($q['submitted_at'] ?? '—') ?></td>
                </tr>
                <?php endforeach; ?>
              </tbody>
            </table>
            <div style="margin-top:10px"><a class="btn" href="./assessments/index.php">Open list</a></div>
          <?php endif; ?>
        </div>

        <div>
          <div class="card" style="margin-bottom:12px">
            <div class="section-title" style="font-weight:700">Corrections Requested</div>
            <?php if(!$corrections): ?>
              <p class="muted">No pending corrections.</p>
            <?php else: ?>
              <table class="table">
                <thead><tr><th>Assessment</th><th>Updated</th></tr></thead>
                <tbody>
                  <?php foreach($corrections as $c): ?>
                  <tr><td><?= esc($c['assessment']) ?></td><td class="muted"><?= esc($c['updated_at']) ?></td></tr>
                  <?php endforeach; ?>
                </tbody>
              </table>
            <?php endif; ?>
          </div>

          <div class="card">
            <div class="section-title" style="font-weight:700">My Recent Grading (last <?= (int)$days ?>d)</div>
            <?php if(!$recent): ?>
              <p class="muted">No recent actions.</p>
            <?php else: ?>
              <table class="table">
                <thead><tr><th>Assessment</th><th>Graded at</th></tr></thead>
                <tbody>
                  <?php foreach($recent as $r): ?>
                  <tr><td><?= esc($r['assessment']) ?></td><td class="muted"><?= esc($r['graded_at']) ?></td></tr>
                  <?php endforeach; ?>
                </tbody>
              </table>
            <?php endif; ?>
          </div>
        </div>
      </div>

      <?php if($DEBUG): ?>
      <div class="card" style="margin-top:12px">
        <div class="section-title" style="font-size:18px">Debug</div>
        <p class="muted">DB include loaded: <?= esc((string)$loaded_db) ?></p>
        <pre class="muted" style="white-space:pre-wrap"><?= esc(implode("\n",$attempts)) ?></pre>
        <p class="muted">Tables — assessments: <?= $hasAssess?'yes':'no' ?>, submissions: <?= $hasSubs?'yes':'no' ?>, students: <?= $studentsTbl?:'none' ?>, lect_courses: <?= $hasLectCourses?'yes':'no' ?>, lect_batches: <?= $hasLectBatches?'yes':'no' ?></p>
        <p class="muted">Time cols — submitted_at: <?= $hasSubmittedAt?'y':'n' ?>, created_at: <?= $hasCreatedAt?'y':'n' ?>, updated_at: <?= $hasUpdatedAt?'y':'n' ?>, graded_at: <?= $hasGradedAt?'y':'n' ?></p>
      </div>
      <?php endif; ?>

      <p class="footer">© <?= date('Y') ?> EdenCampus</p>
    </div>
  </main>
</div>
</body>
</html>
