<?php
$mysqli = new mysqli("localhost","root","","edenrms_db");
if ($mysqli->connect_error) { die($mysqli->connect_error); }
$mysqli->set_charset("utf8mb4");

function next_code(mysqli $db, string $name): string {
  // center → NEC-, reference → EC-
  $st = $db->prepare("SELECT prefix,last_val FROM number_counters WHERE name=? FOR UPDATE");
  $st->bind_param("s",$name); $st->execute();
  $row = $st->get_result()->fetch_assoc(); $st->close();
  if (!$row) { die("Missing counter: $name"); }

  $val = (int)$row['last_val'] + 1;
  $prefix = $row['prefix'];
  $st = $db->prepare("UPDATE number_counters SET last_val=? WHERE name=?");
  $st->bind_param("is",$val,$name); $st->execute(); $st->close();

  // No zero-padding requested; change to sprintf if you want NEC-000201, etc.
  return $prefix.$val;
}

$db = $mysqli;
$db->begin_transaction();
try {
  $rs = $db->query("SELECT id,center_reg_no,reference_no FROM studentss ORDER BY id ASC FOR UPDATE");
  while ($s = $rs->fetch_assoc()) {
    $center = $s['center_reg_no'];
    $ref    = $s['reference_no'];
    if (!$center) $center = next_code($db,'center');
    if (!$ref)    $ref    = next_code($db,'reference');

    $up = $db->prepare("UPDATE studentss SET center_reg_no=?, reference_no=? WHERE id=?");
    $up->bind_param("ssi",$center,$ref,$s['id']);
    $up->execute(); $up->close();
  }
  $db->commit();
  echo "Backfill complete.";
} catch (Throwable $e) {
  $db->rollback();
  echo "Failed: ".$e->getMessage();
}
