<?php 
// /edenrms/pages/submit_registration.php
// Register a student + generate NEC/EC + (multi)enroll + save fee plan/schedule
// Bundle behaviour:
//  - When the selected course is a bundle PARENT, store TWO ECs in one row of
//    student_numbers (reference_no & reference_no2) keyed by (student_id, parent_id).
//  - For single courses, store one EC (reference_no).
// Existing student re-registering the SAME course: put the new EC into reference_no2 only.

declare(strict_types=1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/* ---------- Fatal logger ---------- */
register_shutdown_function(function () {
  $e = error_get_last();
  if ($e && in_array($e['type'], [E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR], true)) {
    $root = realpath(dirname(__DIR__));
    $line = '['.date('Y-m-d H:i:s').'] FATAL '.$e['message'].' in '.$e['file'].':'.$e['line'].PHP_EOL;
    @file_put_contents($root . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . '_reg_log.txt', $line, FILE_APPEND);
    error_log('[submit_registration] FATAL '.$e['message'].' in '.$e['file'].':'.$e['line']);
  }
});

/* ---------- DEBUG ---------- */
$EC_DEBUG = (
  (isset($_GET['debug']) && $_GET['debug'] == '1') ||
  (isset($_POST['debug']) && $_POST['debug'] == '1')
);
if ($EC_DEBUG) {
  @ini_set('display_errors','1');
  @ini_set('display_startup_errors','1');
  error_reporting(E_ALL);
}

/* ---------- Session ---------- */
if (session_status() !== PHP_SESSION_ACTIVE) { @session_start(); }

/* ---------- DB bootstrap ---------- */
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/email_helper.php';
require_once __DIR__ . '/../includes/whatsapp_helper.php';

ec_require_login();
$db = ec_db();
if (!$db) { http_response_code(500); exit($EC_DEBUG ? 'DEBUG: DB connection failed' : 'Registration failed. Please contact admin.'); }
$db->set_charset('utf8mb4');

/* ---------- Relax sql_mode a bit ---------- */
try {
  $db->query("SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(@@sql_mode,'STRICT_TRANS_TABLES',''),'STRICT_ALL_TABLES',''),'ONLY_FULL_GROUP_BY','')");
} catch (Throwable $e) { /* ignore */ }

/* ---------- Method guard ---------- */
if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
  http_response_code(405); exit("Use POST.");
}

/* ---------- upload limit pre-flight ---------- */
function ec_ini_bytes(string $v): int {
  $v = trim($v); if ($v === '') return 0;
  $num = (float)$v; $last = strtolower($v[strlen($v)-1]);
  switch ($last) { case 'g': $num*=1024; case 'm': $num*=1024; case 'k': $num*=1024; }
  return (int)$num;
}
$maxPost = ec_ini_bytes(ini_get('post_max_size') ?: '0');
$contentLen = (int)($_SERVER['CONTENT_LENGTH'] ?? 0);
if ($maxPost && $contentLen && $contentLen > $maxPost) {
  http_response_code(413);
  exit("Photo + form is larger than the server limit (post_max_size=" . ini_get('post_max_size') . "). Please use a smaller image or ask admin to raise the limit.");
}
if (!empty($_FILES['photo']) && isset($_FILES['photo']['error'])) {
  $err = (int)$_FILES['photo']['error'];
  if ($err === UPLOAD_ERR_INI_SIZE || $err === UPLOAD_ERR_FORM_SIZE) { http_response_code(413); exit("Photo is larger than the upload limit (upload_max_filesize=" . ini_get('upload_max_filesize') . ")."); }
  if ($err === UPLOAD_ERR_PARTIAL) { http_response_code(400); exit("Photo upload was interrupted. Please try again."); }
  if ($err === UPLOAD_ERR_NO_TMP_DIR || $err === UPLOAD_ERR_CANT_WRITE) { http_response_code(500); exit("Server cannot save uploaded files (tmp dir / permissions). Please contact admin."); }
}

/* ---------- helpers ---------- */
function esc_ident(string $t): string { return '`'.str_replace('`','``',$t).'`'; }
function clean($v){ return is_string($v) ? trim($v) : $v; }
function log_err(string $m): void {
  $root = realpath(dirname(__DIR__));
  $logf = $root . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . '_reg_log.txt';
  $line = '['.date('Y-m-d H:i:s').'] '.$m.PHP_EOL;
  @file_put_contents($logf, $line, FILE_APPEND);
  error_log('[submit_registration] '.$m);
}
function table_exists(mysqli $db, string $t): bool {
  $t = $db->real_escape_string($t);
  $q = $db->query("SHOW TABLES LIKE '{$t}'");
  return $q && $q->num_rows > 0;
}

/* ---------- photo save (resizes; emits .jpg) ---------- */
function ec_upload_student_photo(array $file): ?string {
  if (empty($file['tmp_name'])) { log_err('photo: empty tmp_name'); return null; }
  if (!is_uploaded_file($file['tmp_name'])) { log_err('photo: not an uploaded file'); return null; }

  $mime = '';
  try { if (class_exists('finfo')) { $f = new finfo(FILEINFO_MIME_TYPE); $mime = (string)$f->file($file['tmp_name']); } } catch(Throwable $e) {}
  if ($mime === '') { $gi = @getimagesize($file['tmp_name']); if (is_array($gi) && !empty($gi['mime'])) $mime = (string)$gi['mime']; }
  if ($mime === '' && function_exists('mime_content_type')) { $mime = (string)@mime_content_type($file['tmp_name']); }
  $mime = strtolower($mime); if ($mime === 'image/jpg' || $mime === 'image/pjpeg') $mime = 'image/jpeg';

  $allowed = ['image/jpeg','image/png','image/webp'];
  if (!in_array($mime, $allowed, true)) { log_err('photo: mime not allowed => ['.$mime.'] (name='.$file['name'].')'); return null; }

  $projectRoot = realpath(dirname(__DIR__));
  $fsBase = $projectRoot . DIRECTORY_SEPARATOR . 'uploads';
  if (!is_dir($fsBase)) { @mkdir($fsBase, 0775, true); }
  if (!is_dir($fsBase)) { log_err('photo: uploads dir could not be created: '.$fsBase); return null; }
  if (!is_writable($fsBase)) { log_err('photo: uploads dir not writable: '.$fsBase); return null; }
  $ht = $fsBase . DIRECTORY_SEPARATOR . '.htaccess';
  if (!is_file($ht)) { @file_put_contents($ht, "Options -Indexes\n"); }

  $stamp = date('Ymd_His');
  $rand4 = bin2hex(random_bytes(3));
  $name  = "student_{$stamp}_{$rand4}.jpg";
  $dest  = $fsBase . DIRECTORY_SEPARATOR . $name;

  if (!function_exists('imagecreatetruecolor')) {
    $ext = ($mime==='image/png')?'.png' : (($mime==='image/webp')?'.webp':'.jpg');
    $name = "student_{$stamp}_{$rand4}{$ext}";
    $dest = $fsBase . DIRECTORY_SEPARATOR . $name;
    if (@move_uploaded_file($file['tmp_name'], $dest)) { @chmod($dest, 0644); log_err('photo: saved (no-GD) => '.$name); return $name; }
    $err = error_get_last(); log_err('photo: move_uploaded_file failed (no-GD): '.($err['message'] ?? 'unknown')); return null;
  }

  $src = null;
  if     ($mime === 'image/jpeg') $src = @imagecreatefromjpeg($file['tmp_name']);
  elseif ($mime === 'image/png')  $src = @imagecreatefrompng($file['tmp_name']);
  elseif ($mime === 'image/webp' && function_exists('imagecreatefromwebp')) $src = @imagecreatefromwebp($file['tmp_name']);
  if (!$src) { log_err('photo: GD failed to open image (mime='.$mime.')'); return null; }

  $w = imagesx($src); $h = imagesy($src);
  if ($w <= 0 || $h <= 0) { log_err('photo: invalid dimensions'); @imagedestroy($src); return null; }
  $max = 900; $scale = min(1.0, $max / max(1,$w), $max / max(1,$h));
  $nw = max(1, (int)round($w * $scale)); $nh = max(1, (int)round($h * $scale));
  $dst = imagecreatetruecolor($nw, $nh); imageinterlace($dst, true);
  imagecopyresampled($dst, $src, 0,0,0,0, $nw,$nh, $w,$h);
  if (!@imagejpeg($dst, $dest, 82)) { $err = error_get_last(); log_err('photo: imagejpeg failed: '.($err['message'] ?? 'unknown')); @imagedestroy($dst); @imagedestroy($src); return null; }
  @chmod($dest, 0644); @imagedestroy($dst); @imagedestroy($src); log_err('photo: saved => '.$name); return $name;
}

/* ---------- academic email / password ---------- */
function ec_make_academic_email(mysqli $db, string $table, string $localSeed): string {
  $digits = preg_replace('/\D+/', '', $localSeed);
  $base   = $digits !== '' ? ('Ec' . $digits) : ('Ec' . preg_replace('/[^A-Za-z0-9]+/', '', $localSeed ?: 'ref'));
  $domain = 'eden.academy';
  $sql    = "SELECT 1 FROM ".esc_ident($table)." WHERE academic_email = ? LIMIT 1";
  $stmt   = $db->prepare($sql);
  for ($i = 0; $i <= 99; $i++) {
    $local = $i === 0 ? $base : $base.$i;
    $email = $local . '@' . $domain;
    $stmt->bind_param('s', $email);
    $stmt->execute();
    $stmt->store_result();
    if ($stmt->num_rows === 0) { $stmt->close(); return $email; }
  }
  $email = $base . random_int(100, 999) . '@' . $domain;
  $stmt->bind_param('s', $email);
  $stmt->execute();
  $stmt->store_result();
  $stmt->close();
  return $email;
}
function ec_generate_password(int $len = 12): array {
  $alphabet = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz23456789!@$%*?-';
  $max = strlen($alphabet) - 1;
  $plain = '';
  for ($i=0; $i<$len; $i++) { $plain .= $alphabet[random_int(0, $max)]; }
  $hash = password_hash($plain, PASSWORD_BCRYPT, ['cost' => 12]);
  return [$plain, $hash];
}

/* ---------- photo column autodetect ---------- */
function ec_detect_photo_column(array $has): ?string {
  if (isset($has['photograph'])) return 'photograph';
  $preferred = ['photo','image','profile_picture','profile_pic'];
  foreach ($preferred as $c) if (isset($has[$c])) return $c;
  foreach (array_keys($has) as $c) {
    if (preg_match('/^(?:st|student|user)?_?(?:photo|photograph|image|img|picture|pic|avatar)$/i', $c)) return $c;
    if (preg_match('/^profile_?(?:picture|pic|image|img)$/i', $c)) return $c;
  }
  return null;
}

function ec_collect_used_numeric_suffixes(mysqli $db, string $sql): array {
  $used = [];
  $rs = $db->query($sql);
  if ($rs) {
    while ($row = $rs->fetch_assoc()) {
      foreach ($row as $val) {
        $code = trim((string)$val);
        if ($code !== '' && preg_match('/(\d+)$/', $code, $m)) {
          $used[(int)$m[1]] = true;
        }
      }
    }
    $rs->free();
  }
  return $used;
}

function ec_smallest_missing(array $used, int $start = 203): int {
  $n = max(1, $start);
  while (isset($used[$n])) $n++;
  return $n;
}

function next_center_no(mysqli $db, string $tbl): string {
  $t = esc_ident($tbl);

  $used = [];

  $used += ec_collect_used_numeric_suffixes($db, "
    SELECT center_reg_no
    FROM {$t}
    WHERE center_reg_no LIKE 'NEC%'
    FOR UPDATE
  ");

  if (table_exists($db, 'student_numbers')) {
    $used += ec_collect_used_numeric_suffixes($db, "
      SELECT center_reg_no
      FROM student_numbers
      WHERE center_reg_no LIKE 'NEC%'
      FOR UPDATE
    ");
  }

  $next = ec_smallest_missing($used, 203);
  return 'NEC' . $next;
}

function next_reference_no(mysqli $db, string $tbl): string {
  $t = esc_ident($tbl);

  $used = [];

  $used += ec_collect_used_numeric_suffixes($db, "
    SELECT reference_no
    FROM {$t}
    WHERE reference_no LIKE 'EC%'
    FOR UPDATE
  ");

  if (table_exists($db, 'student_numbers')) {
    $used += ec_collect_used_numeric_suffixes($db, "
      SELECT reference_no, reference_no2
      FROM student_numbers
      WHERE reference_no LIKE 'EC%' OR reference_no2 LIKE 'EC%'
      FOR UPDATE
    ");
  }

  $next = ec_smallest_missing($used, 203);
  return 'EC' . $next;
}
/* ---------- who is creating this registration ---------- */
$creator_id = (int)($_SESSION['user']['id'] ?? ($_SESSION['user_id'] ?? 0));
$creator_name = trim((string)(
  $_SESSION['user']['name'] ?? $_SESSION['user']['full_name'] ?? $_SESSION['user']['username'] ?? ''
));
if ($creator_name === '' && $creator_id > 0) {
  try {
    $st = $db->prepare("SELECT name, full_name, username FROM users WHERE id=? LIMIT 1");
    $st->bind_param("i", $creator_id);
    $st->execute();
    if ($r = $st->get_result()->fetch_assoc()) { $creator_name = (string)($r['name'] ?? $r['full_name'] ?? $r['username'] ?? ''); }
    $st->close();
  } catch (Throwable $e) { /* non-fatal */ }
}

/* ---------- plan + schedule saver ---------- */
function ec_today_ymd(): string {
  // Keep consistent with Sri Lanka
  $tz = new DateTimeZone('Asia/Colombo');
  return (new DateTime('now', $tz))->format('Y-m-d');
}

/**
 * Add N months keeping the same day-of-month where possible.
 * If the target month has fewer days, clamp to the last day.
 * Example: 2026-01-31 + 1 month => 2026-02-28
 */
function ec_add_months_preserve_dom(string $startYmd, int $addMonths): string {
  $tz = new DateTimeZone('Asia/Colombo');
  $base = DateTime::createFromFormat('Y-m-d', $startYmd, $tz) ?: new DateTime('now', $tz);

  $day   = (int)$base->format('d');
  $year  = (int)$base->format('Y');
  $month = (int)$base->format('m');

  $month += $addMonths;
  $year  += intdiv($month - 1, 12);
  $month  = (($month - 1) % 12) + 1;

  $daysInTarget = cal_days_in_month(CAL_GREGORIAN, $month, $year);
  $day = min($day, $daysInTarget);

  return sprintf('%04d-%02d-%02d', $year, $month, $day);
}
function ec_add_days_ymd(string $startYmd, int $days): string {
  $tz = new DateTimeZone('Asia/Colombo');
  $dt = DateTime::createFromFormat('Y-m-d', $startYmd, $tz) ?: new DateTime('now', $tz);
  $dt->modify(($days >= 0 ? '+' : '') . $days . ' days');
  return $dt->format('Y-m-d');
}

function ec_due_in_month(string $firstDueYmd, int $monthsAfter, ?int $preferredDay): string {
  $tz = new DateTimeZone('Asia/Colombo');
  $base = DateTime::createFromFormat('Y-m-d', $firstDueYmd, $tz) ?: new DateTime('now', $tz);

  $domFirst = (int)$base->format('d');
  $wantDay  = ($preferredDay && $preferredDay >= 1) ? $preferredDay : $domFirst;

  // Move to first day of month then add months
  $firstOfMonth = DateTime::createFromFormat('Y-m-d', $base->format('Y-m-01'), $tz);
  $firstOfMonth->modify("+{$monthsAfter} month");

  $year  = (int)$firstOfMonth->format('Y');
  $month = (int)$firstOfMonth->format('m');

  $daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
  $day = min(max($wantDay, 1), $daysInMonth);

  return sprintf('%04d-%02d-%02d', $year, $month, $day);
}

function save_selected_plan_for_student(mysqli $db, int $student_id, int $course_id, ?string $planJson): ?int {
  if (!$student_id || !$planJson) return null;

  $p = json_decode($planJson, true);
  if (!is_array($p)) { log_err("PLAN JSON decode failed: ".json_last_error_msg()); return null; }

  // ✅ If client sends a specific course_id (bundle child), use it
  $cidFromJson = (int)($p['course_id'] ?? 0);
  if ($cidFromJson > 0) $course_id = $cidFromJson;

  $col_exists = function(mysqli $db, string $table, string $col): bool {
    $t = $db->real_escape_string($table);
    $c = $db->real_escape_string($col);
    $q = $db->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return $q && $q->num_rows > 0;
  };

  // Create tables if missing
  try {
    $db->query("CREATE TABLE IF NOT EXISTS student_fee_plans (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      student_id INT UNSIGNED NOT NULL,
      course_id INT UNSIGNED NULL,
      plan_code VARCHAR(32) NULL,
      plan_type VARCHAR(20) NULL,
      plan_name VARCHAR(190) NULL,
      course_fee_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      registration_fee_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      certificate_fee_usd DECIMAL(10,2) NOT NULL DEFAULT 0.00,
      plan_discount_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      manual_discount_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      method_discount_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      method VARCHAR(50) NULL,
      sub_total_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      total_payable_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      totals_json LONGTEXT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      KEY idx_sfp_student (student_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  } catch (Throwable $e) {
    log_err("CREATE student_fee_plans failed: ".$e->getMessage());
    return null;
  }

  try {
    $db->query("CREATE TABLE IF NOT EXISTS payment_schedule (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NULL,
  course_id INT UNSIGNED NULL,
  installment_no INT UNSIGNED NOT NULL,
  due_date DATE NULL,
  course_fee_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  registration_fee_lkr DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  certificate_fee_usd DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ps_student (student_id),
  KEY idx_ps_plan (plan_id),
  KEY idx_ps_course (course_id),
  KEY idx_ps_ord (student_id, installment_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  } catch (Throwable $e) {
    log_err("CREATE payment_schedule failed: ".$e->getMessage());
  }

  $t    = (array)($p['totals']   ?? []);
  $calc = (array)($p['computed'] ?? []);

  $cL   = (float)($t['cL'] ?? 0);
  $rL   = (float)($t['rL'] ?? 0);
  $cU   = (float)($t['cU'] ?? 0);

  $pdL  = (float)($p['plan_discount_lkr'] ?? 0);
  $mdL  = (float)($p['manual_discount_lkr'] ?? 0);
  $mtdL = (float)($calc['method_discount_lkr'] ?? 0);

  $sub  = (float)($calc['sub_total_lkr'] ?? ($cL + $rL));
  $ttl  = (float)($calc['total_payable_lkr'] ?? max(0, $sub - $pdL - $mdL - $mtdL));

  $planCode = (string)($p['plan_id']   ?? '');
  $planType = (string)($p['type']      ?? '');
  $planName = (string)($p['plan_name'] ?? '');
  $method   = (string)($p['method']    ?? '');
  $totals_json = json_encode($t);

  // ✅ BULLETPROOF: If client totals are 0, compute from DB using plan_id
  $sumL = $cL + $rL;
  $sumU = $cU;

  if (($sumL <= 0.0 && $sumU <= 0.0) && ctype_digit($planCode)) {
    $pid = (int)$planCode;

    // header
    $hdr = [];
    try {
      $st = $db->prepare("SELECT course_id, course_fee_lkr, registration_fee_lkr, certificate_fee_usd
                          FROM payment_plans WHERE id=? LIMIT 1");
      $st->bind_param("i", $pid);
      $st->execute();
      $hdr = $st->get_result()->fetch_assoc() ?: [];
      $st->close();
    } catch (Throwable $e) {
      log_err("Fallback read payment_plans failed: ".$e->getMessage());
    }

    if (!empty($hdr['course_id'])) $course_id = (int)$hdr['course_id'];

    $hdrCL = (float)($hdr['course_fee_lkr'] ?? 0);
    $hdrRL = (float)($hdr['registration_fee_lkr'] ?? 0);
    $hdrCU = (float)($hdr['certificate_fee_usd'] ?? 0);

    // installment sum if needed
    if ($planType === 'inst' && table_exists($db, 'payment_plan_installments')) {
      try {
        $st2 = $db->prepare("
          SELECT
            COALESCE(SUM(course_fee_lkr),0) AS cL,
            COALESCE(SUM(registration_fee_lkr),0) AS rL,
            COALESCE(SUM(certificate_fee_usd),0) AS cU
          FROM payment_plan_installments
          WHERE plan_id=?
        ");
        $st2->bind_param("i", $pid);
        $st2->execute();
        $s = $st2->get_result()->fetch_assoc() ?: [];
        $st2->close();

        $cL = (float)($s['cL'] ?? 0);
        $rL = (float)($s['rL'] ?? 0);

        // prefer header cert fee if exists
        $cU = $hdrCU > 0 ? $hdrCU : (float)($s['cU'] ?? 0);

        // recompute sub/ttl if client computed fields were also 0
        $sub = $sub > 0 ? $sub : ($cL + $rL);
        $ttl = $ttl > 0 ? $ttl : max(0, $sub - $pdL - $mdL - $mtdL);

      } catch (Throwable $e) {
        log_err("Fallback sum installments failed: ".$e->getMessage());
      }
    } else {
      // standard/discount => header fee is the truth
      $cL = $hdrCL;
      $rL = $hdrRL;
      $cU = $hdrCU;

      $sub = $sub > 0 ? $sub : ($cL + $rL);
      $ttl = $ttl > 0 ? $ttl : max(0, $sub - $pdL - $mdL - $mtdL);
    }
  }

  $textCol = $col_exists($db, 'student_fee_plans', 'plan_code') ? 'plan_code'
           : ($col_exists($db, 'student_fee_plans', 'plan_id')  ? 'plan_id'   : 'plan_code');

  $sql = "INSERT INTO student_fee_plans
          (student_id, course_id, {$textCol}, plan_type, plan_name,
           course_fee_lkr, registration_fee_lkr, certificate_fee_usd,
           plan_discount_lkr, manual_discount_lkr, method_discount_lkr, method,
           sub_total_lkr, total_payable_lkr, totals_json)
          VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

  $stmt = $db->prepare($sql);

  // bind as strings is OK for DECIMAL in MySQL; keep consistent with your existing style
  $cL_s = (string)((float)$cL);
  $rL_s = (string)((float)$rL);
  $cU_s = (string)((float)$cU);
  $pd_s = (string)((float)$pdL);
  $md_s = (string)((float)$mdL);
  $mtd_s= (string)((float)$mtdL);
  $sub_s= (string)((float)$sub);
  $ttl_s= (string)((float)$ttl);

  $stmt->bind_param(
    "iisssssssssssss",
    $student_id, $course_id, $planCode, $planType, $planName,
    $cL_s, $rL_s, $cU_s, $pd_s, $md_s, $mtd_s, $method, $sub_s, $ttl_s, $totals_json
  );

  $stmt->execute();
  $sfp_id = (int)$stmt->insert_id;
  $stmt->close();

  // Clear any previous schedule for same student+this plan_id
  if ($del = $db->prepare("DELETE FROM payment_schedule WHERE student_id=? AND (plan_id IS NULL OR plan_id=?)")) {
    $del->bind_param("ii", $student_id, $sfp_id);
    $del->execute();
    $del->close();
  }

  // Build installments list: take from payload OR DB fallback
  $installments = [];
  if (!empty($p['installments']) && is_array($p['installments'])) {
    $installments = $p['installments'];
  }

  if (empty($installments) && $planType === 'inst' && ctype_digit($planCode) && table_exists($db, 'payment_plan_installments')) {
    try {
      $pid = (int)$planCode;
      $st = $db->prepare("
        SELECT installment_no, due_date, course_fee_lkr, registration_fee_lkr, certificate_fee_usd
        FROM payment_plan_installments
        WHERE plan_id=?
        ORDER BY installment_no ASC, id ASC
      ");
      $st->bind_param("i", $pid);
      $st->execute();
      $rs = $st->get_result();
      while ($r = $rs->fetch_assoc()) {
        $installments[] = [
          'installment_no'       => (int)($r['installment_no'] ?? 0),
          'due_date'             => (string)($r['due_date'] ?? ''),
          'course_fee_lkr'       => (float)($r['course_fee_lkr'] ?? 0),
          'registration_fee_lkr' => (float)($r['registration_fee_lkr'] ?? 0),
          'certificate_fee_usd'  => (float)($r['certificate_fee_usd'] ?? 0),
        ];
      }
      $st->close();
    } catch (Throwable $e) {
      log_err("payment_plan_installments fallback failed: ".$e->getMessage());
      $installments = [];
    }
  }

  // Preferred due day
  $prefDay = null;
  if (isset($_POST['preferred_due_day']) && $_POST['preferred_due_day'] !== '') {
    $prefDay = (int)$_POST['preferred_due_day'];
  } elseif (isset($p['preferred_due_day']) && $p['preferred_due_day'] !== '') {
    $prefDay = (int)$p['preferred_due_day'];
  }
  if ($prefDay !== null && ($prefDay < 1 || $prefDay > 31)) $prefDay = null;

  // Generate due dates + insert schedule
  if (!empty($installments)) {
    usort($installments, function($a,$b){
      return ((int)($a['installment_no'] ?? 0)) <=> ((int)($b['installment_no'] ?? 0));
    });
    foreach ($installments as $i => &$row) { $row['installment_no'] = $i + 1; }
    unset($row);

    $regDate  = ec_today_ymd();
    $firstDue = ec_add_days_ymd($regDate, 5);

    foreach ($installments as &$row) {
      $no = (int)$row['installment_no'];
      if ($no === 1) $row['due_date'] = $firstDue;
      else $row['due_date'] = ec_due_in_month($firstDue, $no - 1, $prefDay);
    }
    unset($row);

    $ins = $db->prepare(
  "INSERT INTO payment_schedule
   (student_id, plan_id, installment_no, due_date, course_fee_lkr, registration_fee_lkr, certificate_fee_usd)
   VALUES (?,?,?,?,?,?,?)"
);

// bind ONCE (by reference)
$no  = 0;
$due = null;   // string|null
$cFee = 0.0;
$rFee = 0.0;
$uFee = 0.0;

$ins->bind_param("iiisddd", $student_id, $sfp_id, $no, $due, $cFee, $rFee, $uFee);

foreach ($installments as $row) {
  $no = (int)($row['installment_no'] ?? 0);
  if ($no <= 0) continue;

  $dd  = trim((string)($row['due_date'] ?? ''));
  $due = ($dd !== '' ? $dd : null);

  $cFee = (float)($row['course_fee_lkr'] ?? 0);
  $rFee = (float)($row['registration_fee_lkr'] ?? 0);
  $uFee = (float)($row['certificate_fee_usd'] ?? 0);

  $ins->execute();
}

$ins->close();

  }

  return $sfp_id;
}

/* ---------- students table & field map ---------- */
$STUDENTS_TBL = table_exists($db, 'studentss') ? 'studentss'
              : (table_exists($db, 'students') ? 'students' : '');
if ($STUDENTS_TBL === '') {
  $msg = "No students table found (expected 'studentss' or 'students').";
  log_err($msg);
  if ($EC_DEBUG) { http_response_code(500); exit("DEBUG: ".$msg); }
  http_response_code(500); exit("Registration failed. Please contact admin.");
}
$fields = [
  'title'                 => 'title',
  'full_name'             => 'full_name',
  'gender'                => 'gender',
  'dob'                   => 'dob',
  'nationality'           => 'nationality',
  'nic'                   => 'nic_passport', // will switch to 'nic' if present
  'mobile'                => 'mobile',
  'email'                 => 'email',
  'address1'              => 'address1',
  'address2'              => 'address2',
  'city'                  => 'city',
  'country'               => 'country',
  'guardian_name'         => 'guardian_name',
  'guardian_relationship' => 'guardian_relationship',
  'guardian_contact'      => 'guardian_contact',
  'course'                => 'course_id',
  'batch'                 => 'batch_id',
];

/* ---------- branch via form ---------- */
$req_branch_id   = isset($_POST['reg_branch_id']) && $_POST['reg_branch_id'] !== '' ? (int)$_POST['reg_branch_id'] : null;
$req_branch_name = trim((string)($_POST['reg_branch_name'] ?? ''));
if ($req_branch_id && $req_branch_name === '') {
  try {
    $st = $db->prepare("SELECT name FROM branches WHERE id=? LIMIT 1");
    $st->bind_param("i", $req_branch_id);
    $st->execute();
    if ($r = $st->get_result()->fetch_assoc()) { $req_branch_name = (string)($r['name'] ?? ''); }
    $st->close();
  } catch (Throwable $e) { /* non-fatal */ }
}

/* ---------- Existing student quick validation ---------- */
$existingId = (int)($_POST['existing_student_id'] ?? 0);
if ($existingId <= 0) {
  $required = ['full_name','gender','mobile'];
  foreach ($required as $k) {
    if (!isset($_POST[$k]) || trim((string)$_POST[$k]) === '') {
      http_response_code(400);
      exit("Missing required field: {$k}");
    }
  }
}

/* ---------- discover columns ---------- */
$has = [];
$tbl_esc = $db->real_escape_string($STUDENTS_TBL);
$res = $db->query("
  SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = '{$tbl_esc}'
");
if ($res) { while ($row = $res->fetch_row()) { $has[$row[0]] = true; } $res->close(); }
if (!isset($has['nic_passport']) && isset($has['nic'])) { $fields['nic'] = 'nic'; }

/* ---------- detect available creator columns ---------- */
$CREATOR_ID_COL = null;
foreach (['created_by','created_by_id','created_user_id','creator_id','staff_id','user_id','created_by_user_id'] as $c) {
  if (isset($has[$c])) { $CREATOR_ID_COL = $c; break; }
}
$CREATOR_NAME_COL = null;
foreach (['created_by_name','creator','created_user'] as $c) {
  if (isset($has[$c])) { $CREATOR_NAME_COL = $c; break; }
}

/* ---------- idempotency helpers ---------- */
function ec_reg_lock_key(array $POST): string {
  $nic = trim((string)($POST['nic'] ?? ''));
  $name = trim((string)($POST['full_name'] ?? ''));
  $mobile = trim((string)($POST['mobile'] ?? ''));
  $seed = $nic !== '' ? "NIC:$nic" : "NM:$name|$mobile";
  return 'ec_reg_' . hash('sha1', $seed);
}
function ec_try_get_lock(mysqli $db, string $key, int $seconds = 10): void {
  $sql = "SELECT GET_LOCK(?, ?)";
  $st = $db->prepare($sql);
  $st->bind_param('si', $key, $seconds);
  $st->execute();
  $st->bind_result($ok);
  $st->fetch();
  $st->close();
  if ((int)$ok !== 1) { throw new Exception("Could not acquire registration lock"); }
}
function ec_release_lock(mysqli $db, string $key): void {
  try { $st = $db->prepare("SELECT RELEASE_LOCK(?)"); $st->bind_param('s', $key); $st->execute(); $st->close(); } catch (Throwable $e) {}
}
function ec_find_recent_student(mysqli $db, string $table, array $POST): ?array {
  $name   = trim((string)($POST['full_name'] ?? ''));
  $nicIn  = trim((string)($POST['nic'] ?? ''));
  $mobile = trim((string)($POST['mobile'] ?? ''));

  $hasNic = false; $hasNicPassport = false; $hasCreatedAt = false;
  if ($q = $db->query("SHOW COLUMNS FROM `{$table}` LIKE 'nic'"))           { $hasNic = ($q->num_rows > 0); $q->close(); }
  if ($q = $db->query("SHOW COLUMNS FROM `{$table}` LIKE 'nic_passport'"))  { $hasNicPassport = ($q->num_rows > 0); $q->close(); }
  if ($q = $db->query("SHOW COLUMNS FROM `{$table}` LIKE 'created_at'"))    { $hasCreatedAt = ($q->num_rows > 0); $q->close(); }

  $timeFilter = $hasCreatedAt ? " AND created_at >= (NOW() - INTERVAL 5 MINUTE) " : "";

  if ($nicIn !== '' && ($hasNic || $hasNicPassport)) {
    if ($hasNic && $hasNicPassport) {
      $sql = "SELECT id, reference_no, center_reg_no, academic_email FROM `{$table}`
              WHERE (nic = ? OR nic_passport = ?) AND full_name = ? {$timeFilter}
              ORDER BY id DESC LIMIT 1";
      $st = $db->prepare($sql);
      $st->bind_param('sss', $nicIn, $nicIn, $name);
    } elseif ($hasNic) {
      $sql = "SELECT id, reference_no, center_reg_no, academic_email FROM `{$table}`
              WHERE nic = ? AND full_name = ? {$timeFilter}
              ORDER BY id DESC LIMIT 1";
      $st = $db->prepare($sql);
      $st->bind_param('ss', $nicIn, $name);
    } else {
      $sql = "SELECT id, reference_no, center_reg_no, academic_email FROM `{$table}`
              WHERE nic_passport = ? AND full_name = ? {$timeFilter}
              ORDER BY id DESC LIMIT 1";
      $st = $db->prepare($sql);
      $st->bind_param('ss', $nicIn, $name);
    }
  } else {
    $sql = "SELECT id, reference_no, center_reg_no, academic_email FROM `{$table}`
            WHERE full_name = ? AND mobile = ? {$timeFilter}
            ORDER BY id DESC LIMIT 1";
    $st = $db->prepare($sql);
    $st->bind_param('ss', $name, $mobile);
  }

  $st->execute();
  if (method_exists($st,'get_result')) {
    $res = $st->get_result();
    $row = $res ? $res->fetch_assoc() : null;
    $st->close();
    return $row ?: null;
  } else {
    $st->bind_result($id,$ref,$cen,$aem);
    $row = null;
    if ($st->fetch()) { $row = ['id'=>$id,'reference_no'=>$ref,'center_reg_no'=>$cen,'academic_email'=>$aem]; }
    $st->close();
    return $row;
  }
}

/* ---------- Bundle helpers ---------- */
function ec_get_bundle_children(mysqli $db, int $parentId): array {
  $children = [];
  if ($parentId <= 0) return [];
  if (table_exists($db, 'course_children')) {
    try {
      $st = $db->prepare("SELECT child_id FROM course_children WHERE parent_id=? ORDER BY sort_order, child_id");
      $st->bind_param("i", $parentId);
      $st->execute();
      $st->bind_result($cid);
      while ($st->fetch()) { $children[] = (int)$cid; }
      $st->close();
    } catch (Throwable $e) { /* ignore */ }
  }
  if (!$children) {
    try {
      $st = $db->prepare("SELECT bundle_courses FROM courses WHERE id=? LIMIT 1");
      $st->bind_param("i", $parentId);
      $st->execute();
      $st->bind_result($csv);
      if ($st->fetch() && $csv) {
        foreach (preg_split('/\s*,\s*/', (string)$csv, -1, PREG_SPLIT_NO_EMPTY) as $raw) {
          $children[] = (int)$raw;
        }
      }
      $st->close();
    } catch (Throwable $e) { /* ignore */ }
  }
  $children = array_values(array_unique(array_filter($children, fn($v)=>$v>0)));
  return $children;
}

/* ---------- student_numbers helpers (row-level) ---------- */
function ec_sn_get(mysqli $db, int $sid, int $cid): ?array {
  $st = $db->prepare("SELECT id, reference_no, reference_no2, center_reg_no FROM student_numbers WHERE student_id=? AND course_id=? FOR UPDATE");
  $st->bind_param("ii", $sid, $cid);
  $st->execute();
  if (method_exists($st,'get_result')) {
    $r = $st->get_result()->fetch_assoc() ?: null;
    $st->close();
    return $r ?: null;
  } else {
    $st->bind_result($id,$r1,$r2,$nec);
    $row = null;
    if ($st->fetch()) $row = ['id'=>$id,'reference_no'=>$r1,'reference_no2'=>$r2,'center_reg_no'=>$nec];
    $st->close();
    return $row;
  }
}

/* ----- Acquire per-student lock ----- */
$__LOCK_KEY = ec_reg_lock_key($_POST);
ec_try_get_lock($db, $__LOCK_KEY, 10);

/* ====== TRANSACTION ====== */
$db->query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
$db->begin_transaction();

try {
  /* numbers table bootstrap */
  try {
    $db->query("CREATE TABLE IF NOT EXISTS student_numbers (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      student_id INT UNSIGNED NOT NULL,
      course_id INT UNSIGNED NULL,
      reference_no VARCHAR(50) NULL,
      reference_no2 VARCHAR(50) NULL,
      center_reg_no VARCHAR(50) NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_student_course (student_id, course_id),
      KEY idx_student (student_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
  } catch (Throwable $e) { log_err('CREATE student_numbers failed: '.$e->getMessage()); }

  $skipInsert = false;
  $newId = 0;
  $existingRecent = null;

  /* ------- Numbers + email + password ------- */
  $center_no = null; $ref_no = null; $academic_email = null;
  $generated_plain_pw = null; $generated_hash_pw = null;

  // 1) Explicit existing student
  if ($existingId > 0) {
    $skipInsert = true;
    $newId = $existingId;

    $st = $db->prepare("SELECT reference_no, center_reg_no, academic_email FROM ".esc_ident($STUDENTS_TBL)." WHERE id=?");
    $st->bind_param("i", $newId);
    $st->execute();
    if (method_exists($st,'get_result')) {
      $r = $st->get_result()->fetch_assoc() ?: [];
      $ref_no = $r['reference_no'] ?? null;
      $center_no = $r['center_reg_no'] ?? null;
      $academic_email = $r['academic_email'] ?? null;
    } else {
      $st->bind_result($ref_no,$center_no,$academic_email);
      $st->fetch();
    }
    $st->close();
  } else {
    // 2) Protect against double-submits
    $existingRecent = ec_find_recent_student($db, $STUDENTS_TBL, $_POST);
    if ($existingRecent) {
      $skipInsert = true;
      $newId = (int)$existingRecent['id'];
      $ref_no = $existingRecent['reference_no'] ?? null;
      $center_no = $existingRecent['center_reg_no'] ?? null;
      $academic_email = $existingRecent['academic_email'] ?? null;
    }
  }
  $IS_EXISTING = (bool)$skipInsert;

  /* auto-detect photo column */
  $photoCol = ec_detect_photo_column($has);
  if ($EC_DEBUG && !$photoCol && !empty($_FILES['photo']['tmp_name'])) {
    log_err("No suitable photo column found in table '{$STUDENTS_TBL}'. Columns: ".implode(',', array_keys($has)));
  }

  if (!$skipInsert) {
    // Prepare INSERT buffers first
    $cols=[]; $vals=[]; $ph=[]; $types='';

    if (isset($has['center_reg_no'])) { $center_no = next_center_no($db, $STUDENTS_TBL); }
    if (isset($has['reference_no']))  { $ref_no    = next_reference_no($db, $STUDENTS_TBL); }

    if (isset($has['academic_email'])) {
      $seed = $ref_no ?? $center_no ?? '';
      if ($seed !== '') { $academic_email = ec_make_academic_email($db, $STUDENTS_TBL, (string)$seed); }
    }

    if (isset($has['academic_password_hash']) || isset($has['academic_password'])) {
      [$generated_plain_pw, $generated_hash_pw] = ec_generate_password(12);
    }

    if ($center_no !== null)     { $cols[]='`center_reg_no`';       $ph[]='?'; $types.='s'; $vals[]=$center_no; }
    if ($ref_no    !== null)     { $cols[]='`reference_no`';        $ph[]='?'; $types.='s'; $vals[]=$ref_no; }
    if ($academic_email !== null){ $cols[]='`academic_email`';      $ph[]='?'; $types.='s'; $vals[]=$academic_email; }

    if ($generated_hash_pw && isset($has['academic_password_hash'])) {
      $cols[]='`academic_password_hash`'; $ph[]='?'; $types.='s'; $vals[]=$generated_hash_pw;
    } elseif ($generated_plain_pw && isset($has['academic_password'])) {
      $cols[]='`academic_password`';      $ph[]='?'; $types.='s'; $vals[]=$generated_plain_pw;
    }

    if ($req_branch_id !== null && isset($has['branch_id'])) { $cols[] = '`branch_id`';   $ph[]='?'; $types .= 'i'; $vals[] = $req_branch_id; }
    if ($req_branch_name !== '' && isset($has['branch_name'])) { $cols[] = '`branch_name`'; $ph[]='?'; $types .= 's'; $vals[] = $req_branch_name; }

    foreach ($fields as $form=>$col) {
      if (!isset($has[$col])) continue;
      $val = $_POST[$form] ?? null;
      if (in_array($col, ['course_id','batch_id'], true)) {
        if (is_string($val) && strpos($val, ',') !== false) { $val = null; }
        else { $val = ($val === '' ? null : (int)$val); }
        $types .= 'i'; $vals[] = $val;
      } else {
        $val = clean($val);
        if (($col === 'nic' || $col === 'nic_passport') && $val === '') { $val = null; }
        $types .= 's'; $vals[] = $val;
      }
      $cols[]="`$col`"; $ph[]='?';
    }

    // photo (insert)
    if ($photoCol && isset($_FILES['photo']) && $_FILES['photo']['error']===UPLOAD_ERR_OK) {
      if ($rel = ec_upload_student_photo($_FILES['photo'])) {
        $cols[]='`'.$photoCol.'`'; $ph[]='?'; $types.='s'; $vals[]=$rel;
      }
    }

    /* creator columns */
    if ($creator_id > 0 && $CREATOR_ID_COL) { $cols[]='`'.$CREATOR_ID_COL.'`'; $ph[]='?'; $types.='i'; $vals[]=$creator_id; }
    if ($creator_name !== '' && $CREATOR_NAME_COL) { $cols[]='`'.$CREATOR_NAME_COL.'`'; $ph[]='?'; $types.='s'; $vals[]=$creator_name; }

    if (isset($has['created_at'])) { $cols[]='`created_at`'; $ph[]='NOW()'; }

    if (!$cols) { throw new Exception("No insertable columns found for table ".$STUDENTS_TBL); }

    $sql = "INSERT INTO ".esc_ident($STUDENTS_TBL)." (".implode(',', $cols).") VALUES (".implode(',', $ph).")";
    $stmt = $db->prepare($sql);
    $bind = []; $bind[] = &$types; foreach ($vals as $k=>$v) { $bind[] = &$vals[$k]; }
    call_user_func_array([$stmt,'bind_param'],$bind);
    $stmt->execute();
    $newId = $stmt->insert_id;
    $stmt->close();

  } else {
    // Existing student: DO NOT mutate their row
    $st = $db->prepare("SELECT reference_no, center_reg_no, academic_email FROM ".esc_ident($STUDENTS_TBL)." WHERE id=?");
    $st->bind_param("i", $newId);
    $st->execute();
    if (method_exists($st,'get_result')) {
      $r = $st->get_result()->fetch_assoc() ?: [];
      $ref_no = $r['reference_no'] ?? null;
      $center_no = $r['center_reg_no'] ?? null;
      $academic_email = $r['academic_email'] ?? null;
    } else {
      $st->bind_result($ref_no,$center_no,$academic_email);
      $st->fetch();
    }
    $st->close();
  }

/* ===== ENROLL ===== */
$batchRaw       = trim((string)($_POST['batch'] ?? $_POST['batch_id'] ?? ''));
$courseIdUser   = (int)($_POST['course'] ?? $_POST['course_id'] ?? 0);

/* helper: parse a CSV/list into int ids */
$parse_ids = function($in): array {
  if (is_array($in))  { $arr = $in; }
  else                { $arr = preg_split('/\s*,\s*/', (string)$in, -1, PREG_SPLIT_NO_EMPTY); }
  $ids = [];
  foreach ($arr as $v) { $v = (int)$v; if ($v > 0) $ids[] = $v; }
  return array_values(array_unique($ids));
};

$batchIds = $parse_ids($batchRaw);
if (!$batchIds && $batchRaw !== '') $batchIds = [ (int)$batchRaw ];

$has_batches     = table_exists($db,'batches');
$has_enrollments = table_exists($db,'enrollments');

/* Detect bundle children for the selected course (if any) */
$bundleChildren = ec_get_bundle_children($db, $courseIdUser);
$isBundleParent = ($courseIdUser > 0 && count($bundleChildren) >= 2);

/* Optional: per-child batch inputs (tolerant formats) */
$bundleBatchIdsCsv = trim((string)($_POST['bundle_batch_ids'] ?? ''));  // e.g. "12,34"
$bundleBatchMapRaw = (string)($_POST['bundle_batch_map'] ?? '');        // e.g. '{"101":12,"102":34}'
$bundleBatchIds    = $parse_ids($bundleBatchIdsCsv);

/* If JSON map present, prefer it and keep a (child_id => batch_id) mapping */
$bundleBatchMap = [];
if ($bundleBatchMapRaw !== '') {
  $tmp = json_decode($bundleBatchMapRaw, true);
  if (is_array($tmp)) {
    foreach ($tmp as $childId => $batchId) {
      $c = (int)$childId; $b = (int)$batchId;
      if ($c > 0 && $b > 0) $bundleBatchMap[$c] = $b;
    }
  }
}

/* Collect all candidate batch ids we should enroll to */
$allCandidateBatches = $batchIds;
if ($bundleBatchIds)         { $allCandidateBatches = array_merge($allCandidateBatches, $bundleBatchIds); }
if ($bundleBatchMap)         { $allCandidateBatches = array_merge($allCandidateBatches, array_values($bundleBatchMap)); }
$allCandidateBatches = array_values(array_unique(array_filter($allCandidateBatches, fn($v)=>$v>0)));

$batchCourseIds = [];  // will be filled after validation

if ($allCandidateBatches && $has_enrollments) {
  /* Fast lookups: batch_id -> course_id (validate ownership) */
  $batchCourseLookup = [];
  if ($has_batches) {
    // fetch all in one go
    $inPlaceholders = implode(',', array_fill(0, count($allCandidateBatches), '?'));
    $types = str_repeat('i', count($allCandidateBatches));
    $stmt  = $db->prepare("SELECT id, course_id FROM batches WHERE id IN ($inPlaceholders)");
    $bind  = []; $bind[] = &$types;
    foreach ($allCandidateBatches as $k => $v) { $bind[] = &$allCandidateBatches[$k]; }
    call_user_func_array([$stmt, 'bind_param'], $bind);
    $stmt->execute();
    $stmt->bind_result($bid,$bcid);
    while ($stmt->fetch()) { $batchCourseLookup[(int)$bid] = (int)$bcid; }
    $stmt->close();
  }

  /* Tiny helper: validated insert (idempotent) */
  $insertEnroll = function(int $batchId) use ($db, $newId) {
    $en = $db->prepare(
      "INSERT INTO enrollments (batch_id, student_id, enrollment_date)
         SELECT ?, ?, CURDATE()
           FROM DUAL
          WHERE NOT EXISTS (SELECT 1 FROM enrollments WHERE batch_id=? AND student_id=?)"
    );
    $en->bind_param("iiii", $batchId, $newId, $batchId, $newId);
    $en->execute(); $en->close();
  };

  /* Enrollment rules:
     - If NOT a bundle: allow any candidate (existing behavior)
     - If a bundle: ensure each batch belongs to either selected parent course (rare) or one of its child courses.
       We still allow tolerant behavior (log but continue) if mismatch detected. */
  foreach ($allCandidateBatches as $batchId) {
    $courseFromBatch = 0;
    if ($has_batches && isset($batchCourseLookup[$batchId])) {
      $courseFromBatch = (int)$batchCourseLookup[$batchId];
    } elseif ($has_batches) {
      // fallback single lookup
      $chk = $db->prepare("SELECT course_id FROM batches WHERE id=? LIMIT 1");
      $chk->bind_param("i", $batchId);
      $chk->execute();
      $chk->bind_result($courseFromBatch);
      if (!$chk->fetch()) { $courseFromBatch = 0; }
      $chk->close();
    }

    // validation for bundle parent
    if ($isBundleParent) {
      $ok = false;
      if ($courseFromBatch === $courseIdUser) $ok = true;
      if (!$ok && in_array($courseFromBatch, $bundleChildren, true)) $ok = true;

      if (!$ok && $courseFromBatch > 0) {
        log_err("Bundle enrollment: batch {$batchId} belongs to course {$courseFromBatch} which is not in bundle children of parent {$courseIdUser}; continuing by tolerance.");
      }
    }

    // enroll + remember course ids
    $insertEnroll($batchId);
    if ($courseFromBatch > 0) {
      $batchCourseIds[] = $courseFromBatch;
      if ($courseIdUser === 0) { $courseIdUser = $courseFromBatch; }
    }
  }
} elseif ($allCandidateBatches && !$has_enrollments) {
  log_err("Skipped enrollment: 'enrollments' table not found on live.");
}

/* Deduplicate for later plan logic */
$batchCourseIds = array_values(array_unique(array_filter($batchCourseIds, fn($v)=>$v>0)));

/* Decide which course_id to store with the fee plan (unchanged logic, but now considers bundle child batches too) */
$uniq = $batchCourseIds;
$courseIdForPlan = $courseIdUser;
if ($courseIdForPlan === 0) {
  $courseIdForPlan = (count($uniq) === 1) ? $uniq[0] : 0;
} else {
  if ($uniq && !in_array($courseIdForPlan, $uniq, true)) {
    log_err("Selected course {$courseIdForPlan} not present in batch set ".implode(',',$uniq)."; saving plan with inferred value if singular.");
    $courseIdForPlan = (count($uniq) === 1) ? $uniq[0] : $courseIdForPlan;
  }
}

  /* ===== EXISTING student: ensure their course is set if empty ===== */
  if ($IS_EXISTING && isset($has['course_id']) && $courseIdForPlan > 0) {
    $st = $db->prepare("
      UPDATE ".esc_ident($STUDENTS_TBL)."
         SET course_id = COALESCE(NULLIF(course_id,0), ?)
       WHERE id = ?
    ");
    $st->bind_param("ii", $courseIdForPlan, $newId);
    $st->execute();
    $st->close();
  }

  /* ===== SAVE SELECTED PLAN + SCHEDULE ===== */
  save_selected_plan_for_student($db, $newId, $courseIdForPlan, $_POST['selected_plan_json'] ?? null);

  /* ===== Assign numbers (bundle-aware) ===== */
  $numbersForFlash = [];

  // Small name cache for success message
  $nameCache = [];
  $getName = function(int $cid) use ($db, &$nameCache): string {
    if ($cid <= 0) return 'Course';
    if (isset($nameCache[$cid])) return $nameCache[$cid];
    $st = $db->prepare("SELECT course_name FROM courses WHERE id=? LIMIT 1");
    $st->bind_param("i", $cid);
    $st->execute();
    $st->bind_result($nm); $nmFetched = $st->fetch() ? (string)$nm : '';
    $st->close();
    $nameCache[$cid] = ($nmFetched !== '') ? $nmFetched : ("Course #".$cid);
    return $nameCache[$cid];
  };

  // Detect if the selected course is a bundle parent with at least 2 children
  $bundleChildren = ec_get_bundle_children($db, $courseIdUser);
  $isBundleParent = ($courseIdUser > 0 && count($bundleChildren) >= 2);

  if ($isBundleParent) {
    /* ----- Bundle parent logic ----- */
    $ONLY_EC_MODE = (bool)$IS_EXISTING;

    // Lock existing row (if any)
    $existingSN = ec_sn_get($db, $newId, $courseIdUser);

    if ($existingSN) {
      // Row exists already: add only reference_no2 if empty
      if (empty($existingSN['reference_no2'])) {
        $newEC = next_reference_no($db, $STUDENTS_TBL);
        $upd = $db->prepare("UPDATE student_numbers SET reference_no2=? WHERE id=?");
        $upd->bind_param("si", $newEC, $existingSN['id']);
        $upd->execute(); $upd->close();

        // success list uses the 2nd child name
        $c2 = (int)($bundleChildren[1] ?? 0);
        if ($c2 > 0) {
          $numbersForFlash[] = ['course_id'=>$c2, 'course_name'=>$getName($c2), 'reference_no'=>$newEC];
        }
        // keep NEC as-is
        $center_no = $existingSN['center_reg_no'] ?: $center_no;
      } else {
        log_err("Bundle parent re-register called but reference_no2 already set for student_id={$newId}, course_id={$courseIdUser}.");
      }

    } else {
      // First time bundle: allocate two ECs, single NEC
      if ($ONLY_EC_MODE) {
        $ec1 = next_reference_no($db, $STUDENTS_TBL);
        $ec2 = next_reference_no($db, $STUDENTS_TBL);
        $nec = $center_no ?: null;
      } else {
        $ec1 = $ref_no    ?? next_reference_no($db, $STUDENTS_TBL);
        $ec2 = next_reference_no($db, $STUDENTS_TBL);
        $nec = $center_no ?? next_center_no($db, $STUDENTS_TBL);
        $ref_no    = $ref_no    ?? $ec1;
        $center_no = $center_no ?? $nec;
      }

      $ins = $db->prepare("
        INSERT INTO student_numbers (student_id, course_id, reference_no, reference_no2, center_reg_no)
        VALUES (?,?,?,?,?)
        ON DUPLICATE KEY UPDATE
          reference_no  = IF(reference_no IS NULL OR reference_no='', VALUES(reference_no), reference_no),
          reference_no2 = IF(reference_no2 IS NULL OR reference_no2='', VALUES(reference_no2), reference_no2),
          center_reg_no = COALESCE(center_reg_no, VALUES(center_reg_no))
      ");
      $ins->bind_param("iisss", $newId, $courseIdUser, $ec1, $ec2, $nec);
      $ins->execute(); $ins->close();

      // Build success list using first two children
      $c1 = (int)$bundleChildren[0]; $c2 = (int)$bundleChildren[1];
      $numbersForFlash[] = ['course_id'=>$c1, 'course_name'=>$getName($c1), 'reference_no'=>$ec1];
      $numbersForFlash[] = ['course_id'=>$c2, 'course_name'=>$getName($c2), 'reference_no'=>$ec2];

      if (count($bundleChildren) > 2) {
        log_err("Bundle parent {$courseIdUser} has >2 children; only first two ECs saved to reference_no/reference_no2.");
      }
    }

  } else {
    /* ----- NON-BUNDLE: one EC per concrete course ----- */
    $courseIds = $uniq;
    if ($courseIdUser > 0) $courseIds[] = $courseIdUser;
    $bundleRaw = trim((string)($_POST['bundle_course_ids'] ?? ''));
    if ($bundleRaw !== '') {
      $bundleIds = array_filter(array_map('intval', preg_split('/\s*,\s*/', $bundleRaw, -1, PREG_SPLIT_NO_EMPTY)));
      $courseIds = array_merge($courseIds, $bundleIds);
    }
    $courseIds = array_values(array_unique(array_filter($courseIds, fn($v)=>$v>0)));

    if ($courseIds) {
      $ONLY_EC_MODE = (bool)$IS_EXISTING;

      // upsert prepared once; this ensures a second registration drops into reference_no2
      $upsert = $db->prepare("
        INSERT INTO student_numbers (student_id, course_id, reference_no, reference_no2, center_reg_no)
        VALUES (?,?,?,NULL,?)
        ON DUPLICATE KEY UPDATE
          reference_no  = IF(reference_no IS NULL OR reference_no='', VALUES(reference_no), reference_no),
          reference_no2 = IF((reference_no IS NOT NULL AND reference_no <> '') AND
                             (reference_no2 IS NULL OR reference_no2=''),
                             VALUES(reference_no), reference_no2),
          center_reg_no = COALESCE(center_reg_no, VALUES(center_reg_no))
      ");

      foreach ($courseIds as $idx => $cid) {
        // Compose the EC/NEC to feed into the VALUES(...)
        if ($idx === 0) {
          if ($ONLY_EC_MODE) {
            $ec  = next_reference_no($db, $STUDENTS_TBL);
            $nec = $center_no ?: null;
          } else {
            $ec  = $ref_no    ?? next_reference_no($db, $STUDENTS_TBL);
            $nec = $center_no ?? next_center_no($db, $STUDENTS_TBL);
            $ref_no    = $ref_no    ?? $ec;
            $center_no = $center_no ?? $nec;
          }
        } else {
          $ec  = next_reference_no($db, $STUDENTS_TBL);
          $nec = $center_no ?: null;
        }

        $upsert->bind_param("iiss", $newId, $cid, $ec, $nec);
        $upsert->execute();

        $numbersForFlash[] = ['course_id'=>$cid, 'course_name'=>$getName($cid), 'reference_no'=>$ec];
      }
      $upsert->close();
    }
  }

  /* Flash for success page */
  if (!isset($_SESSION)) { @session_start(); }
  $_SESSION['reg_flash'] = [
    'student_id'        => $newId,
    'reference_no'      => $ref_no,
    'center_reg_no'     => $center_no,   // single NEC overall
    'academic_email'    => $academic_email ?? ($existingRecent['academic_email'] ?? null),
    'academic_password' => $generated_plain_pw,
    'numbers'           => $numbersForFlash, // [{course_id, course_name, reference_no}]
    'is_existing'       => $IS_EXISTING,
  ];

  $db->commit();

/* -----------------------------
   Registration confirmation email
   Controlled by:
   settings_sms.php → Email Notification Settings → Registration Confirmation
------------------------------*/
try {
    $studentIdForEmail = (int)$newId;

    $studentEmail = '';
    $studentName  = '';

    $stMail = $db->prepare("
        SELECT *
        FROM studentss
        WHERE id = ?
        LIMIT 1
    ");

    if ($stMail) {
        $stMail->bind_param('i', $studentIdForEmail);
        $stMail->execute();
        $studentRow = $stMail->get_result()->fetch_assoc();
        $stMail->close();

        if ($studentRow) {
            $studentEmail = trim((string)($studentRow['email'] ?? ''));

            $studentName = trim((string)(
                $studentRow['full_name']
                ?? $studentRow['student_name']
                ?? $studentRow['name']
                ?? ''
            ));

            /*
             * Build bundle/single course reference rows.
             * $numbersForFlash example:
             * [
             *   ['course_id' => 1, 'course_name' => 'Course A', 'reference_no' => 'NEC...'],
             *   ['course_id' => 2, 'course_name' => 'Course B', 'reference_no' => 'NEC...']
             * ]
             */
            $referenceRowsHtml = '';

            if (!empty($numbersForFlash) && is_array($numbersForFlash)) {
                foreach ($numbersForFlash as $n) {
                    $courseName = htmlspecialchars((string)($n['course_name'] ?? ''), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
                    $courseRef  = htmlspecialchars((string)($n['reference_no'] ?? ''), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

                    if ($courseName === '' && $courseRef === '') {
                        continue;
                    }

                    $referenceRowsHtml .= '
                        <tr>
                            <td style="border:1px solid #e5e7eb;">' . $courseName . '</td>
                            <td style="border:1px solid #e5e7eb;"><strong>' . $courseRef . '</strong></td>
                        </tr>
                    ';
                }
            }

            /*
             * Fallback: if numbersForFlash is empty, show main reference_no.
             */
            if ($referenceRowsHtml === '') {
                $referenceRowsHtml = '
                    <tr>
                        <td style="border:1px solid #e5e7eb;">Main Course</td>
                        <td style="border:1px solid #e5e7eb;"><strong>' . htmlspecialchars((string)$ref_no, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . '</strong></td>
                    </tr>
                ';
            }

            $academicEmail = trim((string)(
                $academic_email
                ?? ($existingRecent['academic_email'] ?? '')
                ?? ''
            ));

            $academicPassword = trim((string)($generated_plain_pw ?? ''));

            if ($academicPassword === '') {
                $academicPassword = 'Existing password unchanged';
            }

            $isExistingText = !empty($IS_EXISTING) ? 'Yes' : 'No';

            if ($studentEmail !== '') {
                rms_send_template_email(
                    $db,
                    'registration_confirmation',
                    $studentEmail,
                    [
                        'student_name'      => $studentName,
                        'student_id'        => (string)$newId,
                        'reference_no'      => (string)$ref_no,
                        'center_reg_no'     => (string)$center_no,
                        'academic_email'    => $academicEmail,
                        'academic_password' => $academicPassword,
                        'is_existing'       => $isExistingText,
                        'reference_rows'    => $referenceRowsHtml,
                    ],
                    $studentIdForEmail,
                    null,
                    $studentName
                );
            }
        }
    }
} catch (Throwable $mailError) {
    error_log('Registration confirmation email failed: ' . $mailError->getMessage());
}

/* -----------------------------
   Registration WhatsApp Notification
------------------------------*/
try {
    $studentPhone = '';

    if (!empty($studentRow)) {
        $studentPhone = trim((string)(
            $studentRow['mobile']
            ?? $studentRow['phone']
            ?? $studentRow['contact_no']
            ?? ''
        ));
    }

    $courseRefsText = '';

    if (!empty($numbersForFlash) && is_array($numbersForFlash)) {
        foreach ($numbersForFlash as $n) {
            $courseName = trim((string)($n['course_name'] ?? ''));
            $courseRef  = trim((string)($n['reference_no'] ?? ''));

            if ($courseName === '' && $courseRef === '') {
                continue;
            }

            $courseRefsText .= $courseName . ': ' . $courseRef . "\n";
        }
    }

    if ($courseRefsText === '') {
        $courseRefsText = 'Main Course: ' . (string)$ref_no;
    }

    if ($studentPhone !== '') {
        wa_send_message(
            $db,
            'registration_confirmation',
            $studentPhone,
            [
                'student_name'          => $studentName,
                'student_id'            => (string)$newId,
                'reference_no'          => (string)$ref_no,
                'center_reg_no'         => (string)$center_no,
                'academic_email'        => $academicEmail,
                'academic_password'     => $academicPassword,
                'is_existing'           => $isExistingText,
                'course_references_text'=> $courseRefsText
            ],
            $newId,
            null
        );
    }

} catch (Throwable $waError) {
    error_log('Registration WhatsApp failed: ' . $waError->getMessage());
}

ec_release_lock($db, $__LOCK_KEY);
header("Location: new_registration.php?success=1");
exit;

} catch (Throwable $e) {
  $db->rollback();
  ec_release_lock($db, $__LOCK_KEY);
  log_err("Registration failed: ".$e->getMessage());
  if ($EC_DEBUG) { http_response_code(500); echo "DEBUG Registration failed: ".$e->getMessage();
 exit; }
  http_response_code(500); exit("Registration failed. Please contact admin.");
}
