<?php
declare(strict_types=1);

/**
 * pages/marks_validation.php  (FINAL EXAM - LAST ATTEMPT MARKS)
 *
 * Shows LAST attempt score for FINAL exam per Unit/Module, per learner in a batch.
 *
 * Endpoints:
 *   GET  ?action=get_batches&course_id=...
 *   POST action=search     -> returns HTML table snippet
 *   POST action=save_row   -> upsert JSON { ok: true }
 */

require_once __DIR__ . '/../includes/auth.php';
ec_session_start();
ec_require_login();

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/* ====== DB CONNECT ====== */
require_once __DIR__ . '/../includes/db.php';
$mysqli = (isset($conn) && $conn instanceof mysqli) ? $conn : new mysqli('localhost','root','','edencamp_edencampus');
$mysqli->set_charset('utf8mb4');

/* ====== HELPERS ====== */
function h($s): string { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function res_first_col(mysqli_result $res) { $row=$res->fetch_row(); return $row[0] ?? null; }

function table_exists(mysqli $db, string $name): bool {
  $st=$db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? LIMIT 1");
  $st->bind_param("s",$name);
  $st->execute();
  $ok = $st->get_result()->num_rows > 0;
  $st->close();
  return $ok;
}

function pick_col(mysqli $db, string $table, array $cands): ?string {
  $cols=[];
  $res=$db->query("SHOW COLUMNS FROM `{$table}`");
  while($r=$res->fetch_assoc()) $cols[strtolower($r['Field'])]=true;
  foreach($cands as $c) if(isset($cols[strtolower($c)])) return $c;
  return null;
}

function results_has_col(mysqli $db, string $col): bool {
  return (bool) pick_col($db,'results',[$col]);
}

/**
 * Module headers from course_modules first (preferred), fallback courses.modules CSV.
 * Returns: [ ['module_id'=>int, 'unit_no'=>int, 'title'=>string], ... ]
 */
function get_modules(mysqli $db, int $course_id): array {
  $out=[];

  if (table_exists($db,'course_modules')) {
    $titleCol = pick_col($db,'course_modules',['title','module_name','name']);
    $orderCol = pick_col($db,'course_modules',['sort_order','order_no','module_no','position']);
    if ($titleCol) {
      $sql = "SELECT id, `$titleCol` AS title".($orderCol? ", `$orderCol` AS ord":"")." FROM course_modules WHERE course_id=? ORDER BY ".($orderCol? "`$orderCol`":"id");
      $st=$db->prepare($sql);
      $st->bind_param("i",$course_id);
      $st->execute();
      $res=$st->get_result();
      $i=0;
      while($r=$res->fetch_assoc()){
        $t=trim((string)($r['title'] ?? ''));
        if($t==='') continue;
        $i++;
        $unitNo = $orderCol ? (int)($r['ord'] ?? $i) : $i;
        if($unitNo <= 0) $unitNo = $i;
        $out[]=[
          'module_id'=>(int)$r['id'],
          'unit_no'=>$unitNo,
          'title'=>$t
        ];
      }
      $st->close();
      if($out) return $out;
    }
  }

  // fallback: courses.modules csv
  $st=$db->prepare("SELECT modules FROM courses WHERE id=? LIMIT 1");
  $st->bind_param("i",$course_id);
  $st->execute();
  $csv=(string)(res_first_col($st->get_result()) ?? '');
  $st->close();
  $i=0;
  foreach(explode(',', $csv) as $m){
    $m=trim($m);
    if($m==='') continue;
    $i++;
    $out[]=['module_id'=>0-$i, 'unit_no'=>$i, 'title'=>$m];
  }
  return $out;
}

/**
 * Find FINAL exam_id for each unit_no (robust).
 * Priority:
 *  1) units+exams tables (by units.name LIKE 'Unit NN%Final%')
 *  2) exams table only (by exams.unit_exam or exams.title containing 'Unit NN' and 'Final')
 *
 * Returns: [ ['unit_no'=>int,'title'=>string,'final_exam_id'=>?int], ... ]
 */
function get_final_exam_map(mysqli $db, int $course_id): array {
  $mods = get_modules($db, $course_id);
  $haveUnits = table_exists($db, 'units');
  $haveExams = table_exists($db, 'exams');

  $out = [];

  foreach ($mods as $m) {
    $unitNo   = (int)$m['unit_no'];
    $title    = (string)$m['title'];
    $moduleId = (int)$m['module_id'];

    $final_exam_id = null;

    // Match both "Unit 01" and "Unit 1"
    $unitPrefix2 = sprintf('Unit %02d', $unitNo);
    $unitPrefix1 = 'Unit ' . (string)$unitNo;

    // 1) Best: exams + units by module_id (does not depend on unit naming)
    if ($final_exam_id === null && $haveExams && $haveUnits && $moduleId > 0 && pick_col($db,'units',['module_id']) !== null) {
      $st = $db->prepare("
        SELECT e.id
        FROM exams e
        JOIN units u ON u.id = e.unit_id
        WHERE e.course_id=? AND u.course_id=? AND u.module_id=?
          AND (
            u.name LIKE '%Final%' OR e.title LIKE '%Final%' OR e.unit_exam LIKE '%Final%'
          )
        ORDER BY e.id DESC
        LIMIT 1
      ");
      $st->bind_param("iii", $course_id, $course_id, $moduleId);
      $st->execute();
      $eid = (int)(res_first_col($st->get_result()) ?? 0);
      $st->close();
      if ($eid > 0) $final_exam_id = $eid;
    }

// 2) exams + units by unit name (SAFE: avoids Unit 1 matching Unit 10)
if ($final_exam_id === null && $haveExams && $haveUnits) {
  $unitRe = '^Unit[[:space:]]*0?' . $unitNo . '([^0-9]|$)';

  $st = $db->prepare("
    SELECT e.id
    FROM exams e
    JOIN units u ON u.id = e.unit_id
    WHERE e.course_id=? AND u.course_id=?
      AND u.name REGEXP ?
      AND (
        u.name LIKE '%Final%' OR e.title LIKE '%Final%' OR e.unit_exam LIKE '%Final%'
      )
    ORDER BY e.id DESC
    LIMIT 1
  ");
  $st->bind_param("iis", $course_id, $course_id, $unitRe);
  $st->execute();
  $eid = (int)(res_first_col($st->get_result()) ?? 0);
  $st->close();
  if ($eid > 0) $final_exam_id = $eid;
}


  // 3) exams only fallback (SAFE REGEXP)
if ($final_exam_id === null && $haveExams) {
  $unitRe2 = 'Unit[[:space:]]*0?' . $unitNo . '([^0-9]|$)';

  $st = $db->prepare("
    SELECT id
    FROM exams
    WHERE course_id=?
      AND (title LIKE '%Final%' OR unit_exam LIKE '%Final%')
      AND (
        title REGEXP ? OR unit_exam REGEXP ?
      )
    ORDER BY id DESC
    LIMIT 1
  ");
  $st->bind_param("iss", $course_id, $unitRe2, $unitRe2);
  $st->execute();
  $eid = (int)(res_first_col($st->get_result()) ?? 0);
  $st->close();
  if ($eid > 0) $final_exam_id = $eid;
}


    $out[] = [
      'unit_no'       => $unitNo,
      'title'         => $title,
      'final_exam_id' => $final_exam_id
    ];
  }

  return $out;
}

/** Map students.course_reg_no -> students.id (for saving / fallback match) */
function map_students_by_reg(mysqli $db, array $regs): array {
  $regs=array_values(array_unique(array_filter(array_map('trim',$regs), fn($x)=>$x!=='')));
  if(!$regs || !table_exists($db,'students')) return [];
  $place=implode(',', array_fill(0,count($regs),'?'));
  $types=str_repeat('s', count($regs));
  $st=$db->prepare("SELECT id, course_reg_no FROM students WHERE course_reg_no IN ($place)");
  $st->bind_param($types, ...$regs);
  $st->execute();
  $res=$st->get_result();
  $out=[];
  while($r=$res->fetch_assoc()){
    $cr=(string)($r['course_reg_no']??'');
    if($cr!=='') $out[$cr]=(int)$r['id'];
  }
  $st->close();
  return $out;
}

/* ====== ENDPOINT: get_batches ====== */
if (isset($_GET['action']) && $_GET['action']==='get_batches') {
  header("Content-Type: application/json");
  $course_id=(int)($_GET['course_id']??0);
  $rows=[];
  if($course_id>0){
    $st=$mysqli->prepare("
      SELECT id, batch_name
      FROM batches
      WHERE course_id=? AND (status='Active' OR status IS NULL)
      ORDER BY batch_start_date DESC, batch_name
    ");
    $st->bind_param("i",$course_id);
    $st->execute();
    $res=$st->get_result();
    while($r=$res->fetch_assoc()) $rows[]=$r;
    $st->close();
  }
  echo json_encode(["items"=>$rows], JSON_UNESCAPED_UNICODE);
  exit;
}

/* ====== ENDPOINT: search ====== */
if (isset($_POST['action']) && $_POST['action']==='search') {
  header("Content-Type: text/html; charset=UTF-8");

  $course_id=(int)($_POST['course_id']??0);
  $batch_id =(int)($_POST['batch_id'] ??0);
  if($course_id<=0 || $batch_id<=0){
    echo "<div class='alert alert-warning'>Select course & batch.</div>";
    exit;
  }

  // course info
  $st=$mysqli->prepare("SELECT id, course_name, code FROM courses WHERE id=? LIMIT 1");
  $st->bind_param("i",$course_id);
  $st->execute();
  $course=$st->get_result()->fetch_assoc();
  $st->close();

  // module -> final exam map
  $moduleMap = get_final_exam_map($mysqli, $course_id);
  if(!$moduleMap){
    echo "<div class='alert alert-info'>No modules defined for this course.</div>";
    exit;
  }

  // students in batch
  $students=[];
  $st=$mysqli->prepare("
    SELECT s.id, s.full_name, s.center_reg_no, s.reference_no
    FROM enrollments e
    JOIN studentss s ON s.id=e.student_id
    WHERE e.batch_id=?
    ORDER BY s.full_name
  ");
  $st->bind_param("i",$batch_id);
  $st->execute();
  $res=$st->get_result();
  while($r=$res->fetch_assoc()) $students[]=$r;
  $st->close();

  if(!$students){
    echo "<div class='alert alert-warning'>No students found.</div>";
    exit;
  }

  // Build maps for matching results to studentss rows (prefer NEC)
  $studentssIds=[]; $studentssIdSet=[];
  $refToStudentssId=[]; // NEC/EC -> studentss.id
  $regList=[];

  foreach($students as $s){
    $sid=(int)$s['id'];
    $studentssIds[]=$sid; $studentssIdSet[$sid]=true;

    $nec=trim((string)($s['center_reg_no'] ?? ''));
    $ec =trim((string)($s['reference_no'] ?? ''));

    if($nec!==''){ $refToStudentssId[$nec]=$sid; $regList[]=$nec; }
    if($ec !==''){ $refToStudentssId[$ec] =$sid; $regList[]=$ec; }
  }
  $regList=array_values(array_unique(array_filter($regList)));

  // students.id mapping (if results.student_id points to students table)
  $studentsIdToStudentssId=[];
  $studentsByReg = map_students_by_reg($mysqli, $regList); // reg => students.id
  foreach($studentsByReg as $reg=>$stuId){
    $ssId = $refToStudentssId[$reg] ?? null;
    if($ssId) $studentsIdToStudentssId[$stuId] = $ssId;
  }

  // Final exam ids we actually have
  $finalExamIds=[];
  foreach($moduleMap as $mm){
    if(!empty($mm['final_exam_id'])) $finalExamIds[]=(int)$mm['final_exam_id'];
  }
  $finalExamIds=array_values(array_unique($finalExamIds));

  if(!table_exists($mysqli,'results')){
    echo "<div class='alert alert-danger'>results table not found.</div>";
    exit;
  }

  $scoreCol = pick_col($mysqli,'results',['score','marks','mark','final_marks','final_score']);
  if(!$scoreCol){
    echo "<div class='alert alert-danger'>results table has no score/marks column.</div>";
    exit;
  }

  $hasRef  = results_has_col($mysqli,'reference_no');
  $dateCol = pick_col($mysqli,'results',['date_taken','date','created_at','updated_at']);

  // Prefill marks from results (LAST attempt per student + exam_id)
  // Prefill marks from results (LAST attempt per student + exam_id)
$marksByStudent=[]; // [studentss.id][exam_id] => score
$seen=[];

if ($finalExamIds) {
  $placeExam=implode(',', array_fill(0,count($finalExamIds),'?'));
  $types=str_repeat('i', count($finalExamIds));
  $params=$finalExamIds;

  $orderBy = $dateCol ? "COALESCE(r.`$dateCol`, '1970-01-01') DESC, r.id DESC" : "r.id DESC";

  // ---- SAFE MATCH STRATEGY ----
  $where = "r.exam_id IN ($placeExam)";

  if ($hasRef && $regList) {
    // safest: match only by reference_no (NEC/EC)
    $placeRef=implode(',', array_fill(0,count($regList),'?'));
    $where .= " AND r.reference_no IN ($placeRef)";
    $types .= str_repeat('s', count($regList));
    $params = array_merge($params, $regList);

  } else {
    // no reference_no column => match by numeric student_id (choose ONE set)
    $studentsIds = array_keys($studentsIdToStudentssId);

    if ($studentsIds) {
      $placeStu=implode(',', array_fill(0,count($studentsIds),'?'));
      $where .= " AND r.student_id IN ($placeStu)";
      $types .= str_repeat('i', count($studentsIds));
      $params = array_merge($params, $studentsIds);
    } else {
      $placeSs=implode(',', array_fill(0,count($studentssIds),'?'));
      $where .= " AND r.student_id IN ($placeSs)";
      $types .= str_repeat('i', count($studentssIds));
      $params = array_merge($params, $studentssIds);
    }
  }

  $sql="
    SELECT r.id, r.student_id, ".($hasRef ? "r.reference_no," : "NULL AS reference_no,")."
           r.exam_id, r.`$scoreCol` AS scoreval
    FROM results r
    WHERE $where
    ORDER BY $orderBy
  ";

  $st=$mysqli->prepare($sql);
  $st->bind_param($types, ...$params);
  $st->execute();
  $rr=$st->get_result();

  while($r=$rr->fetch_assoc()){
    $examId=(int)($r['exam_id']??0);
    if($examId<=0) continue;

    $sid=null;

    if ($hasRef) {
      $ref=trim((string)($r['reference_no'] ?? ''));
      if($ref!=='' && isset($refToStudentssId[$ref])) $sid=(int)$refToStudentssId[$ref];
    } else {
      $rid=(int)($r['student_id'] ?? 0);
      if($rid>0 && isset($studentsIdToStudentssId[$rid])) $sid=(int)$studentsIdToStudentssId[$rid];
      elseif($rid>0 && isset($studentssIdSet[$rid])) $sid=$rid;
    }

    if(!$sid) continue;

    $key=$sid.':'.$examId;
    if(isset($seen[$key])) continue;
    $marksByStudent[$sid][$examId]=$r['scoreval'];
    $seen[$key]=true;
  }
  $st->close();
}


  // warning banner if some/none exams missing
  $missing=0; $have=0;
  foreach($moduleMap as $mm){
    if(empty($mm['final_exam_id'])) $missing++; else $have++;
  }
  if($have===0){
    echo "<div class='alert alert-warning mb-3'>No FINAL exams were matched for this course yet. (Check <code>units</code>/<code>exams</code> names or mapping.) Table still shown; columns are disabled.</div>";
  } elseif($missing>0){
    echo "<div class='alert alert-info mb-3'>Final exams found for <b>{$have}</b> unit(s). <b>{$missing}</b> unit(s) have no Final exam set up yet (disabled).</div>";
  }

  ?>
  <div class="table-responsive">
    <table class="table table-sm table-bordered align-middle">
      <thead class="table-light">
        <tr>
          <th style="min-width:220px">Registration</th>
          <th style="min-width:120px">Course</th>
          <?php foreach($moduleMap as $mm): ?>
            <th style="min-width:150px">
              <?=h($mm['title'])?>
              <div class="small text-muted">Final (Last attempt)</div>
            </th>
          <?php endforeach; ?>
          <th style="min-width:90px">Action</th>
        </tr>
      </thead>
      <tbody>
      <?php foreach($students as $s): $ssId=(int)$s['id']; ?>
        <tr data-student-id="<?=$ssId?>">
          <td>
            <div class="fw-semibold"><?=h($s['full_name'])?></div>
            <div class="text-muted small"><?=h($s['center_reg_no'] ?: ($s['reference_no'] ?: '-'))?></div>
          </td>
          <td><?=h($course['code'] ?: $course['course_name'])?></td>

          <?php foreach($moduleMap as $mm):
            $examId=(int)($mm['final_exam_id'] ?? 0);
            $val = ($examId>0) ? ($marksByStudent[$ssId][$examId] ?? '') : '';
            $disabled = ($examId<=0);
          ?>
            <td>
              <input type="number"
                     class="form-control form-control-sm mark-inp"
                     data-exam-id="<?=$examId?>"
                     min="0" max="100" step="1"
                     value="<?=h($val)?>"
                     <?=$disabled?'disabled':''?>
                     placeholder="<?=$disabled?'-':''?>">
            </td>
          <?php endforeach; ?>

          <td>
            <button class="btn btn-primary btn-sm save-row">ADD</button>
            <span class="ms-2 save-state"></span>
          </td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>
  </div>

  <script>
    (function(){
      const course_id = <?= (int)$course_id ?>;
      const batch_id  = <?= (int)$batch_id  ?>;

      function saveRow($tr){
        const student_id = $tr.data('student-id');
        const marks = {};

        $tr.find('input.mark-inp').each(function(){
          const examId = parseInt(this.dataset.examId || "0", 10);
          if(!examId || this.disabled) return;
          const val = (this.value || '').trim();
          if(val !== '') marks[String(examId)] = val;
        });

        const $btn=$tr.find('.save-row'), $state=$tr.find('.save-state');
        $btn.prop('disabled',true); $state.text('…');

        $.post('marks_validation.php',{action:'save_row',student_id,course_id,batch_id,marks},function(resp){
          if(resp && resp.ok){ $state.html('✅'); }
          else { $state.html('⚠️'); alert(resp && resp.error ? resp.error : 'Save failed'); }
        },'json').always(function(){ $btn.prop('disabled',false); });
      }

      $('.save-row').on('click', function(e){
        e.preventDefault();
        saveRow($(this).closest('tr'));
      });
    })();
  </script>
  <?php
  exit;
}

/* ====== ENDPOINT: save_row ====== */
if (isset($_POST['action']) && $_POST['action']==='save_row') {
  header("Content-Type: application/json");

  $studentss_id=(int)($_POST['student_id']??0);
  $course_id   =(int)($_POST['course_id'] ??0);
  $batch_id    =(int)($_POST['batch_id']  ??0);
  $marks       = $_POST['marks'] ?? [];

  if($studentss_id<=0 || $course_id<=0 || $batch_id<=0){
    echo json_encode(["ok"=>false,"error"=>"Missing ids"]); exit;
  }

  if(!table_exists($mysqli,'results')){
    echo json_encode(["ok"=>false,"error"=>"results table not found"]); exit;
  }

  // student regs (prefer NEC)
  $st=$mysqli->prepare("SELECT reference_no, center_reg_no FROM studentss WHERE id=? LIMIT 1");
  $st->bind_param("i",$studentss_id);
  $st->execute();
  $stu=$st->get_result()->fetch_assoc();
  $st->close();
  if(!$stu){ echo json_encode(["ok"=>false,"error"=>"Student not found"]); exit; }

  $ec=trim((string)($stu['reference_no']??''));
  $nec=trim((string)($stu['center_reg_no']??''));
  $refKey = $nec !== '' ? $nec : $ec;

  // map to students.id (recommended for inserting)
  $students_id = null;
  if(table_exists($mysqli,'students')){
    $st=$mysqli->prepare("
      SELECT id
      FROM students
      WHERE course_reg_no IN (?, ?, ?)
      ORDER BY (course_reg_no = ?) DESC
      LIMIT 1
    ");
    $st->bind_param("ssss",$refKey,$nec,$ec,$refKey);
    $st->execute();
    $students_id = (int)(res_first_col($st->get_result()) ?? 0);
    $st->close();
    if($students_id<=0) $students_id=null;
  }

  $scoreCol = pick_col($mysqli,'results',['score','marks','mark','final_marks','final_score']);
  if(!$scoreCol){ echo json_encode(["ok"=>false,"error"=>"results has no score/marks column"]); exit; }

  if(!results_has_col($mysqli,'exam_id')){
    echo json_encode(["ok"=>false,"error"=>"results has no exam_id column"]); exit;
  }

  $has_total = results_has_col($mysqli,'total');
  $has_grade = results_has_col($mysqli,'grade');
  $has_res   = results_has_col($mysqli,'result');      // some schemas
  $has_stat  = results_has_col($mysqli,'status');      // our dump has status
  $has_date  = results_has_col($mysqli,'date_taken');
  $has_ref   = results_has_col($mysqli,'reference_no');
  $modCol    = pick_col($mysqli,'results',['module_name','unit_name','module_title','unit_title','title','name']);

  $mysqli->begin_transaction();
  try{
    foreach($marks as $examIdStr=>$scoreRaw){
      $exam_id=(int)$examIdStr;
      if($exam_id<=0) continue;

      $score=(int)max(0, min(100,(int)$scoreRaw));

      // best-effort label from units.name (optional)
      $label=null;
      if($modCol && table_exists($mysqli,'exams') && table_exists($mysqli,'units')){
        $st=$mysqli->prepare("SELECT u.name FROM exams e JOIN units u ON u.id=e.unit_id WHERE e.id=? LIMIT 1");
        $st->bind_param("i",$exam_id);
        $st->execute();
        $label=trim((string)(res_first_col($st->get_result()) ?? ''));
        $st->close();
        if($label==='') $label=null;
      }

      // default grading
$total = $has_total ? 100 : null;

if ($score >= 75) {
  $grade = 'A';
  $result = 'Distinction';
} elseif ($score >= 65) {
  $grade = 'B';
  $result = 'Merit';
} elseif ($score >= 55) {
  $grade = 'C';
  $result = 'Credit';
} elseif ($score >= 40) {
  $grade = 'S';
  $result = 'Pass';
} else {
  $grade = 'F';
  $result = 'Fail';
}

      // find latest existing row (prefer reference_no if available)
      $existing_id=null;

      if($has_ref && $refKey!==''){
        $st=$mysqli->prepare("SELECT id FROM results WHERE exam_id=? AND reference_no=? ORDER BY id DESC LIMIT 1");
        $st->bind_param("is",$exam_id,$refKey);
        $st->execute();
        $existing_id=(int)(res_first_col($st->get_result()) ?? 0);
        $st->close();
        if($existing_id<=0) $existing_id=null;
      }

      if($existing_id===null && $students_id!==null){
        $st=$mysqli->prepare("SELECT id FROM results WHERE exam_id=? AND student_id=? ORDER BY id DESC LIMIT 1");
        $st->bind_param("ii",$exam_id,$students_id);
        $st->execute();
        $existing_id=(int)(res_first_col($st->get_result()) ?? 0);
        $st->close();
        if($existing_id<=0) $existing_id=null;
      }

      if($existing_id!==null){
        // UPDATE
        $sets=[];
        $bt=''; $bv=[];

        $sets[]="`$scoreCol`=?"; $bt.='i'; $bv[]=$score;
        if($has_total){ $sets[]="total=?"; $bt.='i'; $bv[]=$total; }
        if($has_grade){ $sets[]="grade=?"; $bt.='s'; $bv[]=$grade; }
        if($has_res){   $sets[]="result=?";$bt.='s'; $bv[]=$result; }
        if($has_stat){  $sets[]="status=?";$bt.='s'; $bv[]=$result; } // many schemas store Pass/Fail in status
        if($has_date){  $sets[]="date_taken=NOW()"; }
        if($has_ref){   $sets[]="reference_no=?"; $bt.='s'; $bv[]=$refKey; }
        if($modCol && $label!==null){ $sets[]="`$modCol`=?"; $bt.='s'; $bv[]=$label; }

        $bt.='i'; $bv[]=$existing_id;

        $sql="UPDATE results SET ".implode(', ',$sets)." WHERE id=?";
        $st=$mysqli->prepare($sql);
        $st->bind_param($bt, ...$bv);
        $st->execute();
        $st->close();
      } else {
        // INSERT (needs students_id because results.student_id is from exam system)
        $insert_student_id = $students_id ?? $studentss_id; // fallback

// (optional) if results has reference_no, we can still save even without students table match
if($students_id === null && !$has_ref){
  throw new RuntimeException("Cannot insert: results has no reference_no, and no matching students.id for reg: {$refKey}");
}


        $cols=["student_id","exam_id","`$scoreCol`"];
        $vals=["?","?","?"];
        $bt="iii";
        $bv=[$students_id,$exam_id,$score];

        if($has_total){ $cols[]="total"; $vals[]="?"; $bt.='i'; $bv[]=$total; }
        if($has_grade){ $cols[]="grade"; $vals[]="?"; $bt.='s'; $bv[]=$grade; }
        if($has_res){   $cols[]="result";$vals[]="?"; $bt.='s'; $bv[]=$result; }
        if($has_stat){  $cols[]="status";$vals[]="?"; $bt.='s'; $bv[]=$result; }
        if($has_date){  $cols[]="date_taken"; $vals[]="NOW()"; }
        if($has_ref){   $cols[]="reference_no"; $vals[]="?"; $bt.='s'; $bv[]=$refKey; }
        if($modCol && $label!==null){ $cols[]="`$modCol`"; $vals[]="?"; $bt.='s'; $bv[]=$label; }

        $sql="INSERT INTO results (".implode(',',$cols).") VALUES (".implode(',',$vals).")";
        $st=$mysqli->prepare($sql);
        $st->bind_param($bt, ...$bv);
        $st->execute();
        $st->close();
      }
    }

    $mysqli->commit();
    echo json_encode(["ok"=>true]); exit;
  } catch(Throwable $e){
    $mysqli->rollback();
    echo json_encode(["ok"=>false,"error"=>$e->getMessage()]); exit;
  }
}

/* ====== NORMAL PAGE RENDER ====== */
$courses=[];
$st=$mysqli->prepare("SELECT id, course_name, code FROM courses WHERE (status='Active' OR status IS NULL) ORDER BY course_name");
$st->execute();
$res=$st->get_result();
while($r=$res->fetch_assoc()) $courses[]=$r;
$st->close();
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>Module Marks Validation</title>
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    body{background:#f8fafc}
    .toolbar{position:sticky;top:0;z-index:10;background:#0d6efd;color:#fff;padding:.2rem 0}
    .toolbar .form-select,.toolbar .btn{height:38px}
    .hero{padding:60px 0;color:#334155;text-align:center}
    .table-responsive{overflow-x:auto}
   :root{ --sidebar-w: 250px; } /* change to your sidebar real width */

.ec-main{
  margin-left: var(--sidebar-w);
  padding: 16px;
}

@media (max-width: 992px){
  .ec-main{ margin-left: 0; }
}

/* ===== PAGE HERO ===== */
.page-shell{
  padding: 0 6px;
}

.page-hero{
  background: #ffffff;
  border: 1px solid #e7ebf3;
  border-radius: 22px;
  box-shadow: 0 8px 24px rgba(15, 23, 42, 0.05);
  padding: 20px 22px;
  margin-bottom: 16px;
}

.page-hero-title{
  margin: 0;
  font-size: 2rem;
  font-weight: 800;
  letter-spacing: -0.02em;
  color: #16233c;
}

.page-hero-subtitle{
  margin: 6px 0 0;
  font-size: .95rem;
  color: #6f7b91;
}

.page-hero-actions{
  display: flex;
  gap: 10px;
  flex-wrap: wrap;
  align-items: center;
}

.toolbar-card{
  background: #0d6efd;
  color: #fff;
  border-radius: 20px;
  padding: 14px 16px;
  box-shadow: 0 8px 24px rgba(13, 110, 253, 0.16);
  margin-bottom: 16px;
}

.toolbar-card .form-select,
.toolbar-card .btn{
  height: 42px;
  border-radius: 12px;
}

.toolbar-card .btn-light{
  font-weight: 700;
}

.results-card{
  background: #fff;
  border: 1px solid #e7ebf3;
  border-radius: 20px;
  box-shadow: 0 8px 24px rgba(15, 23, 42, 0.05);
  padding: 14px;
}

.placeholder-card{
  background: #fff;
  border: 1px solid #e7ebf3;
  border-radius: 20px;
  box-shadow: 0 8px 24px rgba(15, 23, 42, 0.05);
  padding: 40px 20px;
  text-align: center;
  color: #64748b;
}

@media (max-width: 991.98px){
  .page-hero{
    padding: 16px;
  }

  .page-hero-title{
    font-size: 1.55rem;
  }

  .page-hero-actions{
    width: 100%;
  }

  .page-hero-actions .btn{
    flex: 1 1 auto;
  }
}

  </style>
</head>
<body>
<?php
$ACTIVE='marks validation';
include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>
<main class="ec-main">
  <div class="container-fluid py-4 page-shell">

    <div class="page-hero">
      <div class="d-flex justify-content-between align-items-center gap-3 flex-wrap">
        <div>
          <h1 class="page-hero-title">Marks Validation</h1>
          <p class="page-hero-subtitle">
            Search, review, and validate final exam marks by course, batch, and module.
          </p>
        </div>

        
      </div>
    </div>

    <div class="toolbar-card">
      <div class="d-flex gap-2 align-items-center flex-wrap">
        <div class="fw-semibold me-2">MODULE MARKS VALIDATION (FINAL EXAM - LAST ATTEMPT)</div>

        <select id="course" class="form-select w-auto">
          <option value="">Please select</option>
          <?php foreach($courses as $c): ?>
            <option value="<?=$c['id']?>"><?=h($c['course_name'])?><?= $c['code'] ? ' ('.h($c['code']).')' : '' ?></option>
          <?php endforeach; ?>
        </select>

        <select id="batch" class="form-select w-auto" disabled>
          <option value="">Please select a course first</option>
        </select>

        <button id="btnSearch" class="btn btn-light text-primary ms-1" disabled>
          <i class="bi bi-search me-1"></i> Search
        </button>
      </div>
    </div>

    <div class="results-card mb-3" id="results"></div>

    <div class="placeholder-card" id="placeholder">
      <p class="mb-0">Pick a course and batch, then press Search.</p>
    </div>

  </div>
</main>
<?php include __DIR__ . '/../includes/footer.php'; ?>
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script>
  $('#course').on('change', function(){
    const id=this.value; $('#results').empty(); $('#placeholder').show();
    if(!id){
      $('#batch').prop('disabled',true).html('<option value="">Please select a course first</option>');
      $('#btnSearch').prop('disabled',true); return;
    }
    $.getJSON('marks_validation.php',{action:'get_batches',course_id:id},function(resp){
      const items=(resp&&resp.items)||[];
      if(items.length===0){
        $('#batch').prop('disabled',true).html('<option value="">No active batches</option>');
        $('#btnSearch').prop('disabled',true);
      }else{
        const opts=['<option value="">Please select</option>'].concat(items.map(it=>`<option value="${it.id}">${it.batch_name}</option>`));
        $('#batch').prop('disabled',false).html(opts.join(''));
        $('#btnSearch').prop('disabled',false);
      }
    });
  });

  $('#btnSearch').on('click', function(){
    const course_id=$('#course').val(), batch_id=$('#batch').val();
    if(!course_id||!batch_id) return;
    $('#placeholder').hide();
    $('#results').html('<div class="text-muted p-3">Loading…</div>');
    $.post('marks_validation.php',{action:'search',course_id,batch_id},function(html){
      $('#results').html(html);
    },'html');
  });
</script>
</body>
</html>
