<?php
/* =========================================================================
   Student Registrations (students.php)
   One row per student per course (via student_numbers when present)
   - LKR vs USD handling separated for “Payment” status
   - reg_created_at normalized to DATETIME; reg_sort_key for stable ordering
   - INFORMATION_SCHEMA checks cached in-request
   ======================================================================= */
declare(strict_types=1);

require_once __DIR__ . '/../includes/auth.php';
ec_session_start();
ec_require_login();
if (!ec_can_any(['students.view','students.manage'])) {
  http_response_code(403); echo 'Forbidden.'; exit;
}

$DEBUG = isset($_GET['debug']) && $_GET['debug'] === '1';

/* ------------ DB ------------ */
$DB = ec_db();
if (!$DB instanceof mysqli) { http_response_code(500); exit('DB connection failed.'); }
$DB->set_charset('utf8mb4');

/* ------------ Helpers ------------ */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8'); }

/* Cache INFORMATION_SCHEMA lookups in-request */
function _schema_cache(): array { static $C = ['t'=>[], 'c'=>[]]; return $C; }
function t_exists(mysqli $db, string $table): bool {
  static $cache = [];
  if (array_key_exists($table, $cache)) return $cache[$table];
  $sql="SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=? LIMIT 1";
  $st=$db->prepare($sql); $st->bind_param('s',$table); $st->execute(); $st->store_result();
  $ok=$st->num_rows>0; $st->close(); return $cache[$table] = $ok;
}
function c_exists(mysqli $db, string $table, string $col): bool {
  static $cache = [];
  $k = $table.'|'.$col;
  if (array_key_exists($k, $cache)) return $cache[$k];
  $sql="SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1";
  $st=$db->prepare($sql); $st->bind_param('ss',$table,$col); $st->execute(); $st->store_result();
  $ok=$st->num_rows>0; $st->close(); return $cache[$k] = $ok;
}
function first_existing_col(mysqli $db, string $table, array $cands): ?string {
  foreach ($cands as $c) if (c_exists($db,$table,$c)) return $c; return null;
}
function short_initial_name(string $full): string {
  $full = trim(preg_replace('/\s+/', ' ', $full));
  if ($full === '') return '—';
  $toks = preg_split('/\s+/', $full);
  if (count($toks) === 1) return ucwords($toks[0]); // keep case simple
  $first = strtoupper(mb_substr(str_replace('.', '', $toks[0]), 0, 1));
  $last  = ucwords($toks[count($toks)-1]);
  return $first.'. '.$last;
}

/** Search helper: returns student ids that match $q (student + EC/NEC) */
function find_student_ids(mysqli $db, string $q, array $searchCols, bool $joinSN): array {
  if ($q === '' || !$searchCols) return [];
  $like = "%$q%";
  $sql = "SELECT DISTINCT s.id
            FROM studentss s
            " . ($joinSN ? "LEFT JOIN (
    SELECT sn1.*
    FROM student_numbers sn1
    INNER JOIN (
        SELECT student_id, course_id, MAX(id) as max_id
        FROM student_numbers
        GROUP BY student_id, course_id
    ) sn2 
    ON sn1.id = sn2.max_id
) sn ON sn.student_id = s.id" : "") . "
           WHERE " . implode(' OR ', array_map(fn($c) => "$c LIKE ?", $searchCols)) . "
           LIMIT 2000";
  $st = $db->prepare($sql); if (!$st) return [];
  $types = str_repeat('s', count($searchCols));
  $params = array_fill(0, count($searchCols), $like);
  $st->bind_param($types, ...$params); $st->execute();
  $rs = $st->get_result(); $ids = [];
  while ($row = $rs->fetch_row()) { $ids[] = (int)$row[0]; }
  $st->close(); return $ids;
}

/* ------------ Input ------------ */
$q = trim((string)($_GET['q'] ?? ''));
$status_filter = strtolower(trim((string)($_GET['status'] ?? '')));
$allowed_status_filters = ['active', 'inactive', 'suspended'];
if (!in_array($status_filter, $allowed_status_filters, true)) {
  $status_filter = '';
}

/* ------------ Guard ------------ */
if (!t_exists($DB,'studentss')) {
  http_response_code(500);
  echo $DEBUG ? 'DEBUG: Table "studentss" not found.' : 'Failed to load students.';
  exit;
}

/* ------------ Build query ------------ */
$sel=[]; $joins=[]; $where=[]; $args=[]; $argTypes='';

$sel[] = "s.id";

/* === student_numbers (course-scoped rows) === */
$hasSN = t_exists($DB,'student_numbers');
$joinedSN = false;
if ($hasSN && c_exists($DB,'student_numbers','student_id')) {
  // Join raw; picking the “latest” registration for a student/course is left to data hygiene.
  // (Can be replaced with a derived table if needed.)
  $joins[] = "LEFT JOIN student_numbers sn ON sn.student_id = s.id";
  $joinedSN = true;
}

/* Name */
$nameCol = first_existing_col($DB,'studentss',['full_name','student_name','name','first_name','initial_name']);
if ($nameCol === 'first_name' && c_exists($DB,'studentss','last_name')) {
  $sel[] = "CONCAT(IFNULL(s.first_name,''),' ',IFNULL(s.last_name,'')) AS full_name";
} else {
  $sel[] = ($nameCol ? "s.$nameCol" : "NULL") . " AS full_name";
}

/* NEC / center reg no (prefer per-course sn.center_reg_no) */
$studentRegCol = first_existing_col($DB,'studentss',['center_reg_no','reg_no','registration_no','registration_number','ref_no','ref_number','admission_no']);
if ($joinedSN && c_exists($DB,'student_numbers','center_reg_no')) {
  $sel[] = "COALESCE(sn.center_reg_no, ".($studentRegCol ? "s.$studentRegCol" : "NULL").") AS center_reg_no";
} else {
  $sel[] = ($studentRegCol ? "s.$studentRegCol" : "NULL") . " AS center_reg_no";
}

/* EC number (display only) */
if ($joinedSN && c_exists($DB,'student_numbers','reference_no')) {
  $sel[] = "sn.reference_no AS ec_reference_no";
} else {
  $sel[] = (c_exists($DB,'studentss','reference_no') ? "s.reference_no" : "NULL") . " AS ec_reference_no";
}

/* Student-level created_at (fallback) */
$createdCol = first_existing_col($DB,'studentss',['created_at','reg_date','registered_at','createdon','date_created','date']);
$sel[] = $createdCol ? "s.$createdCol AS created_at" : "NULL AS created_at";

/* Per-course registration created_at (prefer sn.* date cols) → reg_created_at (DATETIME) */
$snCreatedAtCol = null;
if ($joinedSN) {
  $snCreatedAtCol = first_existing_col($DB, 'student_numbers', [
    'created_at','created_on','createdon','registered_at','reg_date',
    'date_created','date_added','added_on','added_at','timestamp'
  ]);
}
if ($joinedSN && $snCreatedAtCol) {
  $sel[] = "COALESCE(sn.$snCreatedAtCol, ".($createdCol ? "s.$createdCol" : "NULL").") AS reg_created_at";
} else {
  $sel[] = ($createdCol ? "s.$createdCol" : "NULL") . " AS reg_created_at";
}

/* Stable numeric sort key: prefer UNIX_TIMESTAMP(reg_created_at) else sn.id else s.id */
$sel[] = "COALESCE(UNIX_TIMESTAMP(COALESCE(" .
           ($joinedSN && $snCreatedAtCol ? "sn.$snCreatedAtCol, " : "") .
           ($createdCol ? "s.$createdCol" : "NULL") .
         ")), " . ($joinedSN ? "sn.id, " : "") . "s.id) AS reg_sort_key";

/* Effective course_id for the row */
$courseIdExpr = "s.course_id";
if ($joinedSN && c_exists($DB,'student_numbers','course_id') && c_exists($DB,'studentss','course_id')) {
  $courseIdExpr = "COALESCE(sn.course_id, s.course_id)";
} elseif ($joinedSN && c_exists($DB,'student_numbers','course_id') && !c_exists($DB,'studentss','course_id')) {
  $courseIdExpr = "sn.course_id";
}
$sel[] = "$courseIdExpr AS _course_id";

/* Course name */
if (t_exists($DB,'courses')) {
  $cname = first_existing_col($DB,'courses',['course_name','name','title']);
  if ($cname) {
    $joins[] = "LEFT JOIN courses c ON c.id = $courseIdExpr";
    $sel[]   = "c.$cname AS course_name";
  } else {
    $sel[]   = "NULL AS course_name";
  }
} else {
  $sel[] = "NULL AS course_name";
}

/* Branch / Centre */
$sel[] = "NULL AS branch_name";
if (c_exists($DB,'studentss','branch_id')) {
  if (t_exists($DB,'branches')) {
    $bname = first_existing_col($DB,'branches',['name','branch_name','title']);
    if ($bname) { $joins[]="LEFT JOIN branches b ON b.id = s.branch_id"; $sel[count($sel)-1]="b.$bname AS branch_name"; }
  } elseif (t_exists($DB,'centres')) {
    $cname2 = first_existing_col($DB,'centres',['centre_name','name','title']);
    if ($cname2) { $joins[]="LEFT JOIN centres ce ON ce.id = s.branch_id"; $sel[count($sel)-1]="ce.$cname2 AS branch_name"; }
  }
}

/* Creator (best-effort) */
$creatorFKs = ['created_by','creator_id','created_user','created_user_id','user_id','staff_id','added_by','created_userid'];
$creatorCol = null; foreach ($creatorFKs as $fk) if (c_exists($DB,'studentss',$fk)) { $creatorCol=$fk; break; }
$sel[] = "NULL AS creator_name";
if ($creatorCol) {
  if (t_exists($DB,'users')) {
    $names=[]; foreach (['full_name','name','username','email'] as $uc) if (c_exists($DB,'users',$uc)) $names[]="u.$uc";
    if (c_exists($DB,'users','first_name') && c_exists($DB,'users','last_name')) $names[]="CONCAT(u.first_name,' ',u.last_name)";
    $joins[]="LEFT JOIN users u ON u.id = s.$creatorCol";
    $sel[count($sel)-1] = ($names ? "COALESCE(".implode(',',$names).")" : "u.id")." AS creator_name";
  } elseif (t_exists($DB,'admins')) {
    $piece = c_exists($DB,'admins','display_name') ? 'a.display_name' : (c_exists($DB,'admins','username') ? 'a.username' : 'a.id');
    $joins[]="LEFT JOIN admins a ON a.id = s.$creatorCol";
    $sel[count($sel)-1] = "$piece AS creator_name";
  }
}

function payment_match_course_sql(
  mysqli $db,
  string $paymentAlias,
  string $courseExpr
): string {
  $hasPaymentsCourse = c_exists($db, 'payments', 'course_id');
  $hasPaymentsPlan   = c_exists($db, 'payments', 'plan_id');
  $hasSFP            = t_exists($db, 'student_fee_plans');

  $parts = [];

  if ($hasPaymentsCourse) {
    $parts[] = "$paymentAlias.course_id = $courseExpr";
  }

  if ($hasPaymentsPlan && $hasSFP) {
    $parts[] = "EXISTS (
      SELECT 1
      FROM student_fee_plans sfpx
      WHERE sfpx.id = $paymentAlias.plan_id
        AND sfpx.course_id = $courseExpr
    )";
  }

  if (!$parts) {
    return "1=0";
  }

  return '(' . implode(' OR ', $parts) . ')';
}

/* === Payment / Dues (separate LKR vs USD) === */
$hasPayments = t_exists($DB,'payments');
$hasSFP      = t_exists($DB,'student_fee_plans');
$hasSched    = t_exists($DB,'payment_schedule');

/* Current student_fee_plans row id for this student+course */
$currentSfpIdExpr = "NULL";
$currentSfpCountExpr = "0";

if ($hasSFP) {
  $currentSfpIdExpr = "(
    SELECT sfpx.id
    FROM student_fee_plans sfpx
    WHERE sfpx.student_id = s.id
      AND sfpx.course_id = $courseIdExpr
    ORDER BY sfpx.id DESC
    LIMIT 1
  )";

  $currentSfpCountExpr = "(
    SELECT COUNT(*)
    FROM student_fee_plans sfpc
    WHERE sfpc.student_id = s.id
  )";
}

/* LKR paid (total) */
$paidLKRExpr = "0.00";

if ($hasPayments) {
  $paidLKRExpr = "(
    SELECT COALESCE(SUM(COALESCE(p.amount,0)),0)
    FROM payments p
    WHERE p.student_id = s.id
      AND (
        /* best: explicit course match */
        (".(c_exists($DB,'payments','course_id') ? "p.course_id = $courseIdExpr" : "0").")

        OR

        /* next: valid current student_fee_plans row id match */
        (".(c_exists($DB,'payments','plan_id') && $hasSFP ? "p.plan_id = $currentSfpIdExpr" : "0").")

        OR

        /* fallback: broken old rows with NULL/invalid plan_id + NULL/0 course_id
           only if this student has exactly ONE fee-plan row */
        (
          $currentSfpCountExpr = 1
          AND ".(c_exists($DB,'payments','course_id') ? "(p.course_id IS NULL OR p.course_id = 0)" : "1")."
          AND ".(c_exists($DB,'payments','plan_id') ? "(
                p.plan_id IS NULL
                OR p.plan_id = 0
                OR NOT EXISTS (
                  SELECT 1
                  FROM student_fee_plans sfpv
                  WHERE sfpv.id = p.plan_id
                )
              )" : "1")."
        )
      )
  )";
}

/* USD paid (UK awards) */
$paidUSDExpr = "0.00";

if ($hasPayments && c_exists($DB,'payments','usd_amount')) {
  $paidUSDExpr = "(
    SELECT COALESCE(SUM(COALESCE(p.usd_amount,0)),0)
    FROM payments p
    WHERE p.student_id = s.id
      AND (
        /* best: explicit course match */
        (".(c_exists($DB,'payments','course_id') ? "p.course_id = $courseIdExpr" : "0").")

        OR

        /* next: valid current student_fee_plans row id match */
        (".(c_exists($DB,'payments','plan_id') && $hasSFP ? "p.plan_id = $currentSfpIdExpr" : "0").")

        OR

        /* fallback: broken old rows with NULL/invalid plan_id + NULL/0 course_id
           only if this student has exactly ONE fee-plan row */
        (
          $currentSfpCountExpr = 1
          AND ".(c_exists($DB,'payments','course_id') ? "(p.course_id IS NULL OR p.course_id = 0)" : "1")."
          AND ".(c_exists($DB,'payments','plan_id') ? "(
                p.plan_id IS NULL
                OR p.plan_id = 0
                OR NOT EXISTS (
                  SELECT 1
                  FROM student_fee_plans sfpv
                  WHERE sfpv.id = p.plan_id
                )
              )" : "1")."
        )
      )
  )";
}
/* Dues to date (LKR components only) */
$dueLKRExpr = "0.00";
if ($hasSched) {
  if ($hasSFP) {
    $dueLKRExpr = "(SELECT COALESCE(SUM(COALESCE(ps.course_fee_lkr,0) + COALESCE(ps.registration_fee_lkr,0)),0)
                     FROM payment_schedule ps
                     JOIN student_fee_plans sfp2 ON sfp2.id = ps.plan_id
                    WHERE ps.student_id = s.id
                      AND sfp2.course_id = $courseIdExpr
                      AND ps.due_date <= CURDATE())";
  } else {
    $dueLKRExpr = "(SELECT COALESCE(SUM(COALESCE(ps.course_fee_lkr,0) + COALESCE(ps.registration_fee_lkr,0)),0)
                     FROM payment_schedule ps
                    WHERE ps.student_id = s.id
                      AND ps.due_date <= CURDATE())";
  }
}

/* Dues to date (USD certificate only) */
$dueUSDExpr = "0.00";
if ($hasSched) {
  if ($hasSFP) {
    $dueUSDExpr = "(SELECT COALESCE(SUM(ps.certificate_fee_usd),0)
                     FROM payment_schedule ps
                     JOIN student_fee_plans sfp2 ON sfp2.id = ps.plan_id
                    WHERE ps.student_id = s.id
                      AND sfp2.course_id = $courseIdExpr
                      AND ps.due_date <= CURDATE())";
  } else {
    $dueUSDExpr = "(SELECT COALESCE(SUM(ps.certificate_fee_usd),0)
                     FROM payment_schedule ps
                    WHERE ps.student_id = s.id
                      AND ps.due_date <= CURDATE())";
  }
}





$hasOverdueMonthExpr = "0";

if ($hasSched && $hasPayments && c_exists($DB, 'payments', 'schedule_id')) {
  if ($hasSFP) {
    $hasOverdueMonthExpr = "(
  CASE
    WHEN ($dueLKRExpr > ($paidLKRExpr + 0.009)) THEN 1
    ELSE 0
  END
)";
  } else {
    $hasOverdueMonthExpr = "(
      SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
      FROM payment_schedule ps
      LEFT JOIN (
        SELECT schedule_id, SUM(amount) AS paid_amount
        FROM payments
        WHERE schedule_id IS NOT NULL
        GROUP BY schedule_id
      ) px ON px.schedule_id = ps.id
      WHERE ps.student_id = s.id
        AND ps.due_date <= CURDATE()
        AND (COALESCE(ps.course_fee_lkr,0) + COALESCE(ps.registration_fee_lkr,0))
            > COALESCE(px.paid_amount,0) + 0.009
    )";
  }
}

$sel[] = "$hasOverdueMonthExpr AS has_overdue_month";

$oldestUnpaidDueExpr = "NULL";

if ($hasSched && $hasPayments && c_exists($DB, 'payments', 'schedule_id')) {
  if ($hasSFP) {
    $oldestUnpaidDueExpr = "(
      SELECT MIN(ps.due_date)
      FROM payment_schedule ps
      JOIN student_fee_plans sfp2 ON sfp2.id = ps.plan_id
      LEFT JOIN (
        SELECT schedule_id, SUM(amount) AS paid_amount
        FROM payments
        WHERE schedule_id IS NOT NULL
        GROUP BY schedule_id
      ) px ON px.schedule_id = ps.id
      WHERE ps.student_id = s.id
        AND sfp2.course_id = $courseIdExpr
        AND ps.due_date <= CURDATE()
        AND (COALESCE(ps.course_fee_lkr,0) + COALESCE(ps.registration_fee_lkr,0))
            > COALESCE(px.paid_amount,0) + 0.009
    )";
  } else {
    $oldestUnpaidDueExpr = "(
      SELECT MIN(ps.due_date)
      FROM payment_schedule ps
      LEFT JOIN (
        SELECT schedule_id, SUM(amount) AS paid_amount
        FROM payments
        WHERE schedule_id IS NOT NULL
        GROUP BY schedule_id
      ) px ON px.schedule_id = ps.id
      WHERE ps.student_id = s.id
        AND ps.due_date <= CURDATE()
        AND (COALESCE(ps.course_fee_lkr,0) + COALESCE(ps.registration_fee_lkr,0))
            > COALESCE(px.paid_amount,0) + 0.009
    )";
  }
}

$sel[] = "$oldestUnpaidDueExpr AS oldest_unpaid_due";

$sel[] = "$paidLKRExpr AS paid_total_lkr";
$sel[] = "$paidUSDExpr AS paid_total_usd";
$sel[] = "$dueLKRExpr  AS due_to_date_lkr";
$sel[] = "$dueUSDExpr  AS due_to_date_usd";

$outstandingLkrExpr = "0.00";

if ($hasSFP) {
  $extraFeeExpr = c_exists($DB,'student_fee_plans','extra_fee_lkr')
    ? "COALESCE(sfp3.extra_fee_lkr,0)"
    : (c_exists($DB,'student_fee_plans','extra_fees_lkr')
        ? "COALESCE(sfp3.extra_fees_lkr,0)"
        : "0");

$paidForCourseExpr = "0";
if ($hasPayments && c_exists($DB,'payments','amount')) {
  $paidForCourseExpr = "(
    SELECT COALESCE(SUM(COALESCE(p2.amount,0)),0)
    FROM payments p2
    WHERE p2.student_id = s.id
      AND (
        /* best: explicit course match */
        (".(c_exists($DB,'payments','course_id') ? "p2.course_id = $courseIdExpr" : "0").")

        OR

        /* next: valid current student_fee_plans row id match */
        (".(c_exists($DB,'payments','plan_id') && $hasSFP ? "p2.plan_id = $currentSfpIdExpr" : "0").")

        OR

        /* fallback: old broken rows without proper course/plan mapping
           only if this student has exactly ONE fee-plan row */
        (
          $currentSfpCountExpr = 1
          AND ".(c_exists($DB,'payments','course_id') ? "(p2.course_id IS NULL OR p2.course_id = 0)" : "1")."
          AND ".(c_exists($DB,'payments','plan_id') ? "(
                p2.plan_id IS NULL
                OR p2.plan_id = 0
                OR NOT EXISTS (
                  SELECT 1
                  FROM student_fee_plans sfpv
                  WHERE sfpv.id = p2.plan_id
                )
              )" : "1")."
        )
      )
  )";
}

  $outstandingLkrExpr = "(
    SELECT GREATEST(
      0,
      (
        COALESCE(sfp3.course_fee_lkr,0)
        + COALESCE(sfp3.registration_fee_lkr,0)
        + COALESCE(sfp3.late_charges,0)
        + $extraFeeExpr
        - COALESCE(sfp3.plan_discount_lkr,0)
        - COALESCE(sfp3.manual_discount_lkr,0)
        - COALESCE(sfp3.method_discount_lkr,0)
      ) - $paidForCourseExpr
    )
    FROM student_fee_plans sfp3
    WHERE sfp3.student_id = s.id
      AND sfp3.course_id = $courseIdExpr
    ORDER BY sfp3.id DESC
    LIMIT 1
  )";
}

$sel[] = "$outstandingLkrExpr AS outstanding_lkr";
$outstandingUsdExpr = "0.00";

if ($hasSFP) {
  $paidUsdForCourseExpr = "0";
if ($hasPayments && c_exists($DB,'payments','usd_amount')) {
  $paidUsdForCourseExpr = "(
    SELECT COALESCE(SUM(COALESCE(p2.usd_amount,0)),0)
    FROM payments p2
    WHERE p2.student_id = s.id
      AND (
        /* best: explicit course match */
        (".(c_exists($DB,'payments','course_id') ? "p2.course_id = $courseIdExpr" : "0").")

        OR

        /* next: valid current student_fee_plans row id match */
        (".(c_exists($DB,'payments','plan_id') && $hasSFP ? "p2.plan_id = $currentSfpIdExpr" : "0").")

        OR

        /* fallback: old broken rows without proper course/plan mapping
           only if this student has exactly ONE fee-plan row */
        (
          $currentSfpCountExpr = 1
          AND ".(c_exists($DB,'payments','course_id') ? "(p2.course_id IS NULL OR p2.course_id = 0)" : "1")."
          AND ".(c_exists($DB,'payments','plan_id') ? "(
                p2.plan_id IS NULL
                OR p2.plan_id = 0
                OR NOT EXISTS (
                  SELECT 1
                  FROM student_fee_plans sfpv
                  WHERE sfpv.id = p2.plan_id
                )
              )" : "1")."
        )
      )
  )";
}

  $outstandingUsdExpr = "(
    SELECT GREATEST(
      0,
      COALESCE(sfp3.certificate_fee_usd,0) - $paidUsdForCourseExpr
    )
    FROM student_fee_plans sfp3
    WHERE sfp3.student_id = s.id
      AND sfp3.course_id = $courseIdExpr
    ORDER BY sfp3.id DESC
    LIMIT 1
  )";
}

$sel[] = "$outstandingUsdExpr AS outstanding_usd";




/* LMS status */
if (c_exists($DB,'studentss','lms_status')) {
  $sel[] = "s.lms_status AS _lms_status";
}


/* Raw/optional state for registration */
foreach (['reg_status','registration_status','status','student_status','enrollment_status','is_active','is_suspended'] as $rc) {
  if (c_exists($DB,'studentss',$rc)) $sel[] = "s.$rc AS _$rc";
}



/* ------------ Search (student-scoped → show all their course rows) ------------ */
$searchCols = [];
foreach (['full_name','student_name','name','first_name','initial_name'] as $sc) if (c_exists($DB,'studentss',$sc)) $searchCols[] = "s.$sc";
foreach (['center_reg_no','reg_no','registration_no','ref_no'] as $sc) if (c_exists($DB,'studentss',$sc)) $searchCols[] = "s.$sc";
foreach (['nic','nic_number','nic_passport','passport'] as $sc) if (c_exists($DB,'studentss',$sc)) $searchCols[] = "s.$sc";
if ($joinedSN) {
  if (c_exists($DB,'student_numbers','center_reg_no')) $searchCols[] = "sn.center_reg_no";
  if (c_exists($DB,'student_numbers','reference_no'))  $searchCols[] = "sn.reference_no";
}

if ($q !== '' && $searchCols) {
  $matchedIds = find_student_ids($DB, $q, $searchCols, $joinedSN);
  if (!$matchedIds) {
    $where[] = '1=0';
  } else {
    $ph = implode(',', array_fill(0, count($matchedIds), '?'));
    $where[] = "s.id IN ($ph)";
    $argTypes .= str_repeat('i', count($matchedIds));
    array_push($args, ...$matchedIds);
  }
}
$GROUPED_VIEW = ($q !== '' && !empty($matchedIds ?? []));

if ($status_filter !== '' && c_exists($DB, 'studentss', 'lms_status')) {
  $where[] = "LOWER(COALESCE(s.lms_status, 'active')) = ?";
  $argTypes .= 's';
  $args[] = $status_filter;
}

/* ------------ Final SQL ------------ */
$orderBy = "reg_sort_key DESC"; // stable numeric sort
$sql = "SELECT ".implode(",\n       ", $sel)."
        FROM studentss s
        ".implode("\n        ", $joins)."
        ".($where ? ("WHERE ".implode(" AND ", $where)) : "")."
        ORDER BY $orderBy
        LIMIT 1000";

$st = $DB->prepare($sql);
if ($st === false) { http_response_code(500); echo $DEBUG ? ("DEBUG prepare failed: ".h($DB->error)) : "Failed to load students."; exit; }
if ($args) { $st->bind_param($argTypes, ...$args); }
if (!$st->execute()) { http_response_code(500); echo $DEBUG ? ("DEBUG exec failed: ".h($st->error)) : "Failed to load students."; exit; }
$res=$st->get_result();
$rows=$res ? $res->fetch_all(MYSQLI_ASSOC) : [];
$st->close();

/* ------------ Page frame ------------ */
$ACTIVE = 'students';
include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>
<main class="ec-main">
<style>
  :root{
    --bg:#f6f8fc;
    --surface:#ffffff;
    --surface-2:#fbfdff;
    --text:#0f172a;
    --muted:#64748b;
    --border:#e5e7eb;

    --primary:#2563eb;
    --primary-dark:#1d4ed8;
    --danger:#ef4444;
    --success:#16a34a;
    --warning:#f59e0b;
    --slate:#64748b;

    --shadow:0 10px 30px rgba(15,23,42,.06);
    --shadow-sm:0 6px 18px rgba(15,23,42,.05);
    --shadow-hover:0 18px 40px rgba(15,23,42,.10);

    --radius:20px;
    --radius-sm:14px;
  }

  body{
    background:var(--bg);
    margin:0 !important;
    color:var(--text);
  }

  .ec-main{
    background:var(--bg);
    padding:24px !important;
    margin-top:0 !important;
    margin-left:270px !important;
    min-height:100vh;
    position:relative;
    z-index:1;
  }

  @media (max-width:991.98px){
    .ec-main{
      margin-left:0 !important;
      padding:12px !important;
    }
  }

  .ec-page-shell{
    max-width:1550px;
    margin:0 auto;
  }

  .ec-page-head{
    background:linear-gradient(180deg, rgba(255,255,255,.95), rgba(255,255,255,.82));
    border:1px solid rgba(229,231,235,.9);
    border-radius:24px;
    box-shadow:var(--shadow);
    padding:1.15rem 1.2rem;
    margin-bottom:1rem;
    backdrop-filter:blur(10px);
  }

  .ec-page-title{
    font-size:1.65rem;
    font-weight:900;
    letter-spacing:-.02em;
    color:var(--text);
    margin:0 0 .18rem 0;
  }

  .ec-page-sub{
    color:var(--muted);
    font-size:.9rem;
    margin:0;
  }

  .ec-card{
    border:1px solid var(--border);
    border-radius:var(--radius);
    background:var(--surface);
    box-shadow:var(--shadow);
    transition:box-shadow .18s ease, transform .18s ease;
  }

  .ec-card:hover{
    box-shadow:var(--shadow-hover);
  }

  .card-rounded{
    border-radius:var(--radius) !important;
  }

  .card-rounded .card-body{
    padding:18px 18px;
  }

  .ec-toolbar{
    display:flex;
    align-items:center;
    justify-content:space-between;
    gap:1rem;
    margin-bottom:1rem;
    flex-wrap:wrap;
  }

  .ec-toolbar-title{
    font-size:1.08rem;
    font-weight:900;
    color:var(--text);
    display:flex;
    align-items:center;
    gap:.55rem;
    margin:0;
  }

  .ec-toolbar-sub{
    color:var(--muted);
    font-size:.84rem;
    margin:0;
  }

  .searchbar{
    margin-bottom:1rem !important;
  }

  .searchbar-grid{
    display:grid;
    grid-template-columns:minmax(0,1.7fr) minmax(180px,.7fr) 120px;
    gap:.75rem;
    align-items:stretch;
  }

  .search-shell{
    display:flex;
    align-items:center;
    background:var(--surface-2);
    border:1px solid var(--border);
    border-radius:16px;
    min-height:50px;
    overflow:hidden;
    box-shadow:inset 0 1px 0 rgba(255,255,255,.6);
  }

  .search-shell:focus-within{
    background:#fff;
    border-color:rgba(37,99,235,.45);
    box-shadow:0 0 0 .2rem rgba(37,99,235,.10);
  }

  .search-shell-icon{
    width:48px;
    min-width:48px;
    display:flex;
    align-items:center;
    justify-content:center;
    color:var(--muted);
    font-size:1rem;
  }

  .search-shell input{
    border:0 !important;
    background:transparent !important;
    box-shadow:none !important;
    height:50px;
    padding:.75rem .9rem .75rem 0;
    font-size:.95rem;
  }

  .searchbar .form-select{
    height:50px;
    font-size:.94rem;
    border-radius:16px !important;
    border:1px solid var(--border);
    background:var(--surface-2);
    box-shadow:none !important;
    padding-left:.95rem;
  }

  .searchbar .form-select:focus{
    background:#fff;
    border-color:rgba(37,99,235,.45);
    box-shadow:0 0 0 .2rem rgba(37,99,235,.10) !important;
  }

  .searchbar .btn{
    height:50px;
    border-radius:16px !important;
    font-weight:800;
    white-space:nowrap;
    display:inline-flex;
    align-items:center;
    justify-content:center;
    box-shadow:none !important;
  }

  .search-clear-btn{
    min-width:110px;
    border:1px solid #cfd8e3 !important;
    background:#fff !important;
    color:#475569 !important;
  }

  .search-clear-btn:hover{
    background:#f8fafc !important;
    color:#0f172a !important;
    border-color:#bfcada !important;
  }

  .students-table-wrap{
    border:1px solid #edf2f7;
    border-radius:18px;
    overflow-x:auto !important;
    overflow-y:hidden !important;
    -webkit-overflow-scrolling:touch;
    background:#fff;
  }

  .students-table{
    min-width:1320px;
    width:100%;
    margin-bottom:0;
    table-layout:auto;
    border-collapse:separate;
    border-spacing:0;
    --bs-table-bg:transparent;
  }

  .students-table.align-middle > tbody > tr > td,
  .students-table.align-middle > thead > tr > th{
    padding:.88rem .82rem;
    vertical-align:middle;
  }

  .students-table thead.table-light th{
    background:#f8fafc !important;
    color:#334155;
    font-size:.77rem;
    text-transform:uppercase;
    letter-spacing:.08em;
    font-weight:800;
    border-bottom:1px solid var(--border) !important;
    white-space:nowrap;
    position:sticky;
    top:0;
    z-index:2;
  }

  .students-table thead tr:first-child th{
    border-bottom:0 !important;
  }

  .students-table tbody tr{
    transition:background .16s ease;
  }

  .students-table tbody tr:hover{
    background:#fbfdff;
  }

  .students-table tbody td{
    border-color:#edf2f7;
  }

  .students-table td:nth-child(1),
  .students-table th:nth-child(1){
    min-width:230px;
    position:sticky;
    left:0;
    z-index:1;
    background:inherit;
  }

  .students-table thead th:nth-child(1){
    z-index:3;
    background:#f8fafc !important;
  }

  .students-table tbody td:nth-child(1){
    background:#fff;
  }

  .students-table tbody tr:hover td:nth-child(1){
    background:#fbfdff;
  }

  .students-table td:nth-child(2),
  .students-table td:nth-child(3),
  .students-table td:nth-child(4),
  .students-table th:nth-child(2),
  .students-table th:nth-child(3),
  .students-table th:nth-child(4){
    min-width:110px;
    white-space:nowrap;
  }

  .students-table td:nth-child(5),
  .students-table th:nth-child(5){
    min-width:270px;
  }

  .students-table td:nth-child(6),
  .students-table th:nth-child(6){
    min-width:140px;
    white-space:nowrap;
  }

  .students-table td:nth-child(7),
  .students-table th:nth-child(7){
    min-width:155px;
    white-space:nowrap;
  }

  .students-table td:nth-child(8),
  .students-table th:nth-child(8){
    min-width:135px;
    white-space:nowrap;
  }

  .students-table td:nth-child(9),
  .students-table th:nth-child(9){
    min-width:115px;
    white-space:nowrap;
  }

  .students-table td{
    word-break:normal;
  }

  .students-table td:nth-child(5){
    white-space:normal;
  }

  .student-name{
    font-size:1rem;
    font-weight:800;
    line-height:1.2;
    margin-bottom:.18rem;
  }

  .student-name .st-link,
  .student-name .st-link:visited{
    color:var(--text) !important;
    text-decoration:none !important;
  }

  .student-name .st-link:hover,
  .student-name .st-link:focus{
    color:var(--primary) !important;
    text-decoration:none !important;
  }

  .sub-muted{
    color:var(--muted);
    font-size:.79rem;
  }

  .badge{
    font-weight:700;
    letter-spacing:.01em;
    padding:.42rem .62rem;
  }

  .rounded-pill.text-bg-success{
    background:rgba(22,163,74,.12) !important;
    color:var(--success) !important;
  }

  .rounded-pill.text-bg-danger{
    background:rgba(239,68,68,.12) !important;
    color:var(--danger) !important;
  }

  .rounded-pill.text-bg-warning{
    background:rgba(245,158,11,.16) !important;
    color:#b45309 !important;
  }

  .rounded-pill.text-bg-secondary,
  .badge.text-bg-secondary{
    background:rgba(100,116,139,.13) !important;
    color:var(--slate) !important;
  }

  .rounded-pill.text-bg-light{
    background:#f8fafc !important;
    color:#475569 !important;
    border:1px solid var(--border);
  }

  .btn-outline-primary.btn-sm{
    border-radius:12px !important;
    padding:.42rem .78rem;
    font-weight:700;
  }

  .btn-outline-primary.btn-sm:hover{
    transform:translateY(-1px);
  }

  .ec-empty{
    padding:2.4rem 1rem !important;
    color:var(--muted);
    text-align:center;
  }

  #studentDetailModal .modal-dialog{ max-width:1000px; }
  #actionModal .modal-dialog{ max-width:1100px; }

  #studentDetailModal .modal-body,
  #actionModal .modal-body{
    padding:0 !important;
  }

  .modal-backdrop{ z-index:1250 !important; }
  .modal{ z-index:1255 !important; }

  .modal-backdrop:not(.show){
    display:none !important;
    pointer-events:none !important;
  }

  .modal.fade:not(.show){
    pointer-events:none !important;
  }

  @media (max-width:1199.98px){
    .students-table{
      min-width:1220px;
    }
  }

  @media (max-width:991.98px){
    .searchbar-grid{
      grid-template-columns:1fr;
    }

    .search-clear-btn{
      width:100%;
    }

    .students-table td:nth-child(1),
    .students-table th:nth-child(1){
      position:relative;
      left:auto;
    }
  }

  @media (max-width:767.98px){
    .ec-page-title{
      font-size:1.24rem;
    }

    .ec-page-head{
      padding:.95rem;
    }

    .card-rounded .card-body{
      padding:14px;
    }

    .students-table{
      font-size:.9rem;
    }
  }
</style>

<div class="ec-page-shell">
  <div class="ec-page-head">
    <h1 class="ec-page-title">Student Registrations</h1>
    <p class="ec-page-sub">Search, filter, and review student records by course, status, and registration details.</p>
  </div>

  <div class="card ec-card card-rounded">
    <div class="card-body">

      <div class="ec-toolbar">
        <div>
          <h5 class="ec-toolbar-title mb-1">
            <i class="bi bi-people"></i>
            Students Overview
          </h5>
          <p class="ec-toolbar-sub">One row per student per course, with registration and payment status.</p>
        </div>
      </div>

      <form class="searchbar" method="get" id="searchForm">
  <div class="searchbar-grid">
    <div class="search-shell">
      <div class="search-shell-icon">
        <i class="bi bi-search"></i>
      </div>
      <input
        name="q"
        value="<?= h($q) ?>"
        type="text"
        class="form-control"
        id="q"
        placeholder="Search name, NEC/EC, NIC…">
    </div>

    <select name="status" id="statusFilter" class="form-select">
      <option value="">All Status</option>
      <option value="active" <?= $status_filter === 'active' ? 'selected' : '' ?>>Active</option>
      <option value="inactive" <?= $status_filter === 'inactive' ? 'selected' : '' ?>>Inactive</option>
      <option value="suspended" <?= $status_filter === 'suspended' ? 'selected' : '' ?>>Suspended</option>
    </select>

    <a href="students.php" class="btn btn-outline-secondary search-clear-btn">Clear</a>
  </div>
</form>

      <div class="table-responsive">
        <table class="table align-middle">
          <thead class="table-light">
            <tr>
              <th rowspan="2" style="min-width:220px">Student</th>
              <th colspan="3" class="text-center">Status</th>
              <th rowspan="2">Course</th>
              <th rowspan="2">Branch</th>
              <th rowspan="2" style="min-width:140px">Date Created</th>
              <th rowspan="2">Creator</th>
              <th rowspan="2">Action</th>
            </tr>
            <tr>
              <th>Registration</th>
              <th>Payment</th>
              <th>Certification</th>
            </tr>
          </thead>
          <tbody>
<?php

if (!$rows) {
  echo '<tr><td colspan="9" class="ec-empty">No student records found.</td></tr>';
} elseif ($GROUPED_VIEW) {
  $byStudent = [];
  foreach ($rows as $r) { $byStudent[(int)$r['id']][] = $r; }

$badge = function(string $s): string {
  return match ($s) {
    'Pending'    => '<span class="badge rounded-pill text-bg-secondary">Pending</span>',
    'Inactive'   => '<span class="badge rounded-pill text-bg-secondary">Inactive</span>',
    'Suspended'  => '<span class="badge rounded-pill text-bg-warning">Suspended</span>',
    'Overdue'    => '<span class="badge rounded-pill text-bg-danger">Overdue</span>',
    'Paid'       => '<span class="badge rounded-pill text-bg-success">Paid</span>',
    'Completed'  => '<span class="badge rounded-pill text-bg-success">Completed</span>',
    'Active'     => '<span class="badge rounded-pill text-bg-success">Active</span>',
    'N/A'        => '<span class="badge rounded-pill text-bg-light">N/A</span>',
    default      => '<span class="badge rounded-pill text-bg-light">'.h($s).'</span>',
  };
};

$computeRegStatus = function(array $r): string {
    $lmsStatusRaw = strtolower(trim((string)($r['_lms_status'] ?? 'active')));
    $lmsStatus    = in_array($lmsStatusRaw, ['active','inactive','suspended'], true) ? $lmsStatusRaw : 'active';

    // Manual LMS status first
    if ($lmsStatus === 'inactive') {
        return 'Inactive';
    }

    if ($lmsStatus === 'suspended') {
        return 'Suspended';
    }

    $paidTotalLkr  = (float)($r['paid_total_lkr'] ?? 0);
    $paidTotalUsd  = (float)($r['paid_total_usd'] ?? 0);
    $hasAnyPayment = ($paidTotalLkr > 0.009 || $paidTotalUsd > 0.009);

    if (!$hasAnyPayment) {
        return 'Pending';
    }

    return 'Active';
};

foreach ($byStudent as $studentId => $list) {
  $rowspan = count($list);
  $first = true;
  foreach ($list as $r) {
$lmsStatusRaw     = strtolower(trim((string)($r['_lms_status'] ?? 'active')));
$lmsStatus        = in_array($lmsStatusRaw, ['active','inactive','suspended'], true) ? $lmsStatusRaw : 'active';

$outstandingLkr   = (float)($r['outstanding_lkr'] ?? 0);
$outstandingUsd   = (float)($r['outstanding_usd'] ?? 0);
$hasOverdueMonth  = (int)($r['has_overdue_month'] ?? 0);
$paidTotalLkr     = (float)($r['paid_total_lkr'] ?? 0);
$paidTotalUsd     = (float)($r['paid_total_usd'] ?? 0);
$hasAnyPayment    = ($paidTotalLkr > 0.009 || $paidTotalUsd > 0.009);

if (in_array($lmsStatus, ['inactive','suspended'], true)) {
    $payStatus = 'N/A';
} elseif ($outstandingLkr <= 0.009 && $outstandingUsd <= 0.009) {
    $payStatus = 'Completed';
} elseif ($hasOverdueMonth === 1) {
    $payStatus = 'Overdue';
} elseif ($hasAnyPayment) {
    $payStatus = 'Paid';
} else {
    $payStatus = 'Pending';
}

    $regStatus = $computeRegStatus($r);
    $displayName = short_initial_name((string)($r['full_name'] ?? ''));
    $createdDisp = '—';
    if (!empty($r['reg_created_at'])) {
      $ts = strtotime((string)$r['reg_created_at']);
      if ($ts && $ts > 0) $createdDisp = h(date('Y-m-d H:i', $ts));
    }

    echo '<tr>';

    if ($first) {
      echo '<td rowspan="'.(int)$rowspan.'">';
      echo '  <div class="student-name">';
      echo '    <a href="student_detail.php?id='.(int)$r['id'].'" class="st-link js-student" data-id="'.(int)$r['id'].'">'.h($displayName).'</a>';
      echo '  </div>';
      echo '  <div class="sub-muted">Reg: '.h($r['center_reg_no'] ?? '—').'</div>';
      echo '</td>';
      $first = false;
    }

    echo '<td>'.$badge($regStatus).'</td>';
    echo '<td>'.$badge($payStatus).'</td>';
    echo '<td><span class="badge text-bg-secondary">N/A</span></td>';
    echo '<td>'.h($r['course_name'] ?? '—').'</td>';
    echo '<td>'.h($r['branch_name'] ?? '—').'</td>';
    echo '<td class="text-nowrap">'.$createdDisp.'</td>';
    echo '<td>'.h($r['creator_name'] ?? '—').'</td>';
    echo '<td><button type="button"
       class="btn btn-outline-primary btn-sm js-view"
       data-id="'.(int)$r['id'].'"
       data-url="student_action_tabs.php?id='.(int)$r['id'].'&course_id='.(int)($r['_course_id'] ?? 0).'&modal=1">
       View
     </button></td>';

    echo '</tr>';
  }
  echo '<tr><td colspan="9" style="padding:0;border:0;height:6px;background:#f8f9fb"></td></tr>';
}

} else {
  foreach ($rows as $r):
$lmsStatusRaw     = strtolower(trim((string)($r['_lms_status'] ?? 'active')));
$lmsStatus        = in_array($lmsStatusRaw, ['active','inactive','suspended'], true) ? $lmsStatusRaw : 'active';

$outstandingLkr   = (float)($r['outstanding_lkr'] ?? 0);
$outstandingUsd   = (float)($r['outstanding_usd'] ?? 0);
$hasOverdueMonth  = (int)($r['has_overdue_month'] ?? 0);
$paidTotalLkr     = (float)($r['paid_total_lkr'] ?? 0);
$paidTotalUsd     = (float)($r['paid_total_usd'] ?? 0);
$hasAnyPayment    = ($paidTotalLkr > 0.009 || $paidTotalUsd > 0.009);

if (in_array($lmsStatus, ['inactive','suspended'], true)) {
    $payStatus = 'N/A';
} elseif ($outstandingLkr <= 0.009 && $outstandingUsd <= 0.009) {
    $payStatus = 'Completed';
} elseif ($hasOverdueMonth === 1) {
    $payStatus = 'Overdue';
} elseif ($hasAnyPayment) {
    $payStatus = 'Paid';
} else {
    $payStatus = 'Pending';
}
$regStatus = (function(array $r): string{
    $lmsStatusRaw = strtolower(trim((string)($r['_lms_status'] ?? 'active')));
    $lmsStatus    = in_array($lmsStatusRaw, ['active','inactive','suspended'], true) ? $lmsStatusRaw : 'active';

    // Manual LMS status first
    if ($lmsStatus === 'inactive') {
        return 'Inactive';
    }

    if ($lmsStatus === 'suspended') {
        return 'Suspended';
    }

    $paidTotalLkr  = (float)($r['paid_total_lkr'] ?? 0);
    $paidTotalUsd  = (float)($r['paid_total_usd'] ?? 0);
    $hasAnyPayment = ($paidTotalLkr > 0.009 || $paidTotalUsd > 0.009);

    if (!$hasAnyPayment) {
        return 'Pending';
    }

    return 'Active';
})($r);

$badge=function(string $s):string{
  return match($s){
    'Pending'    => '<span class="badge rounded-pill text-bg-secondary">Pending</span>',
    'Inactive'   => '<span class="badge rounded-pill text-bg-secondary">Inactive</span>',
    'Suspended'  => '<span class="badge rounded-pill text-bg-warning">Suspended</span>',
    'Overdue'    => '<span class="badge rounded-pill text-bg-danger">Overdue</span>',
    'Paid'       => '<span class="badge rounded-pill text-bg-success">Paid</span>',
    'Completed'  => '<span class="badge rounded-pill text-bg-success">Completed</span>',
    'Active'     => '<span class="badge rounded-pill text-bg-success">Active</span>',
    'N/A'        => '<span class="badge rounded-pill text-bg-light">N/A</span>',
    default      => '<span class="badge rounded-pill text-bg-light">'.h($s).'</span>',
  };
};
    $displayName = short_initial_name((string)($r['full_name'] ?? ''));
    $createdDisp = '—';
    if (!empty($r['reg_created_at'])) {
      $ts = strtotime((string)$r['reg_created_at']);
      if ($ts && $ts > 0) $createdDisp = h(date('Y-m-d H:i', $ts));
    }
?>
  <tr>
    <td>
      <div class="student-name">
        <a href="student_detail.php?id=<?= (int)$r['id'] ?>" class="st-link js-student" data-id="<?= (int)$r['id'] ?>">
          <?= h($displayName) ?>
        </a>
      </div>
      <div class="sub-muted">Reg: <?= h($r['center_reg_no'] ?? '—') ?></div>
    </td>
    <td><?= $badge($regStatus) ?></td>
    <td><?= $badge($payStatus) ?></td>
    <td><span class="badge text-bg-secondary">N/A</span></td>
    <td><?= h($r['course_name'] ?? '—') ?></td>
    <td><?= h($r['branch_name'] ?? '—') ?></td>
    <td class="text-nowrap"><?= $createdDisp ?></td>
    <td><?= h($r['creator_name'] ?? '—') ?></td>
    <td>
      <button
        type="button"
        class="btn btn-outline-primary btn-sm js-view"
        data-id="<?= (int)$r['id'] ?>"
        data-url="student_action_tabs.php?id=<?= (int)$r['id'] ?>&course_id=<?= (int)($r['_course_id'] ?? 0) ?>&modal=1">
        View
      </button>
    </td>
  </tr>
<?php
  endforeach;
}
?>
          </tbody>
        </table>
      </div>

    </div>
  </div>
</div>

<!-- Student Details modal -->
<div class="modal fade" id="studentDetailModal" tabindex="-1" aria-hidden="true">
  <div class="modal-dialog modal-xl modal-dialog-scrollable">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="studentDetailTitle">Student details</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body" id="studentDetailBody">
        <div class="p-4 text-center text-muted">Loading…</div>
      </div>
    </div>
  </div>
</div>

<!-- Action tabs modal -->
<div class="modal fade" id="actionModal" tabindex="-1" aria-hidden="true">
  <div class="modal-dialog modal-xl modal-dialog-scrollable">
    <div class="modal-content">
      <div class="modal-header py-2">
        <h5 class="modal-title"><i class="bi bi-ui-checks-grid me-2"></i>Student Overview</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body" id="actionModalBody">
        <div class="text-center py-5"><div class="spinner-border text-info"></div></div>
      </div>
    </div>
  </div>
</div>

<script>
(function(){
  function ensureJQ(next){
    if (window.jQuery) return next(window.jQuery);
    var s=document.createElement('script');
    s.src='https://code.jquery.com/jquery-3.7.1.min.js'; s.crossOrigin='anonymous'; s.referrerPolicy='no-referrer';
    s.onload=function(){ next(window.jQuery); }; document.head.appendChild(s);
  }
  function ensureBS(next){
    if (window.bootstrap && window.bootstrap.Modal) return next();
    var s=document.createElement('script');
    s.src='https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js';
    s.crossOrigin='anonymous'; s.referrerPolicy='no-referrer';
    s.onload=next; document.head.appendChild(s);
  }
  function start($){
    if (document.readyState === 'loading') document.addEventListener('DOMContentLoaded', function(){ init($); });
    else init($);
  }
  function init($){
    document.querySelectorAll('.modal-backdrop').forEach(el => el.remove());
    document.body.classList.remove('modal-open');
    document.body.style.removeProperty('padding-right');
    document.body.style.removeProperty('overflow');

    ['studentDetailModal','actionModal'].forEach(id=>{
      const el=document.getElementById(id);
      if (el && el.parentElement!==document.body) document.body.appendChild(el);
    });

    function openModal(id, options){
      document.querySelectorAll('.modal-backdrop').forEach(b => b.remove());
      document.body.classList.remove('modal-open');
      document.body.style.removeProperty('padding-right');
      document.body.style.removeProperty('overflow');

      const el = document.getElementById(id);
      return bootstrap.Modal.getOrCreateInstance(
        el,
        Object.assign({ backdrop: true, keyboard: true, focus: true }, options || {})
      );
    }

    ['studentDetailModal','actionModal'].forEach(id=>{
      const el=document.getElementById(id); if(!el) return;
      el.addEventListener('hidden.bs.modal',()=>{
        document.querySelectorAll('.modal-backdrop').forEach(b=>b.remove());
        document.body.classList.remove('modal-open');
        document.body.style.removeProperty('padding-right');
      });
    });

    const q = document.getElementById('q'),
          form = document.getElementById('searchForm'),
          statusFilter = document.getElementById('statusFilter');

    if (q && form) {
      let t = null;
      q.addEventListener('input', () => {
        clearTimeout(t);
        t = setTimeout(() => form.submit(), 1000);
      });
    }

    if (statusFilter && form) {
      statusFilter.addEventListener('change', () => form.submit());
    }

    const cache=new Map(), inflight=new Map();

    function fetchHtml(url){
      if(cache.has(url)) return $.Deferred().resolve(cache.get(url)).promise();
      if(inflight.has(url)) return inflight.get(url);
      const req=$.ajax({url, dataType:'html', headers:{'X-Requested-With':'fetch'}})
        .done(html=>{ cache.set(url,html); inflight.delete(url); })
        .fail(()=>inflight.delete(url));
      inflight.set(url,req); return req;
    }

    $(document).on('mouseenter focus', '.js-student', function(){
      const url='student_detail.php?id='+encodeURIComponent(this.dataset.id)+'&modal=1';
      if(!cache.has(url)) fetchHtml(url);
    });

    $(document).on('click', '.js-student', function(e){
      e.preventDefault();
      const url='student_detail.php?id='+encodeURIComponent(this.dataset.id)+'&modal=1';
      $('#studentDetailBody').html('<div class="p-4 text-center text-muted">Loading…</div>');
      openModal('studentDetailModal').show();
      fetchHtml(url).done(html=>$('#studentDetailBody').html(html))
        .fail(()=>$('#studentDetailBody').html('<div class="p-4 text-danger text-center">Failed to load.</div>'));
    });

    $(document).on('mouseenter focus', '.js-view', function(){
      const url=this.dataset.url; if(url && !cache.has(url)) fetchHtml(url);
    });

    $(document).on('click', '.js-view', function (e) {
      e.preventDefault();

      const raw = this.dataset.url || '';
      const abs = new URL(raw, location.origin);
      const url = abs.pathname.startsWith('/edenrms/') ? abs.href
                 : (location.origin + '/edenrms/pages/' + raw.replace(/^\/?pages\//,''));

      const u = new URL(url, location.origin);
      const courseId = u.searchParams.get('course_id') || '';

      const $modal = $('#actionModal');
      $modal[0].dataset.courseId = courseId;

      $('#actionModalBody').html('<div class="text-center py-5"><div class="spinner-border text-info"></div></div>');
      bootstrap.Modal.getOrCreateInstance($modal[0], { backdrop: 'static', keyboard: false }).show();

      $.ajax({ url, dataType: 'html' })
        .done(html => $('#actionModalBody').html(html))
        .fail((xhr) => {
          const msg = (xhr && (xhr.responseText || xhr.statusText)) || 'Unknown error';
          $('#actionModalBody').html(
            '<div class="p-3 text-danger">Failed to load.<br><pre class="mt-2 small" style="white-space:pre-wrap">' +
            String(msg).slice(0, 2000) + '</pre></div>'
          );
          console.error('Student Overview load failed:', xhr);
        });
    });
  }
  ensureJQ(function($){ ensureBS(function(){ start($); }); });
})();
</script>
</main>
<?php @include __DIR__ . '/../includes/footer.php'; ?>
