<?php
// ajax/get_batches_by_course.php  (safe, schema-flexible)
declare(strict_types=1);
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../includes/db.php';

/* ---- DB connection ---- */
$db = null;
if (function_exists('ec_db')) {
  $db = ec_db();
} elseif (isset($GLOBALS['conn']) && $GLOBALS['conn'] instanceof mysqli) {
  $db = $GLOBALS['conn'];
}
if (!$db) { http_response_code(500); echo json_encode(['error'=>true,'message'=>'DB connection failed']); exit; }
$db->set_charset('utf8mb4');

/* ---- input ---- */
$course_id = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
if ($course_id <= 0) { http_response_code(400); echo json_encode(['error'=>true,'message'=>'Missing course_id']); exit; }
$today = date('Y-m-d');

/* ---- helpers ---- */
function col_exists(mysqli $db, string $table, string $col): bool {
  $table = $db->real_escape_string($table);
  $col   = $db->real_escape_string($col);
  $res = $db->query("SHOW COLUMNS FROM `{$table}` LIKE '{$col}'");
  return ($res && $res->num_rows > 0);
}
function pick_course_fk(mysqli $db): ?string {
  foreach (['course_id','course','c_id','courseId','courseID'] as $c) {
    if (col_exists($db,'batches',$c)) return $c;
  }
  return null;
}
function table_exists(mysqli $db, string $t): bool {
  $t = $db->real_escape_string($t);
  $res = $db->query("SHOW TABLES LIKE '{$t}'");
  return ($res && $res->num_rows > 0);
}

/* ---- detect column names we can use (all optional except course FK) ---- */
$fkCourse = pick_course_fk($db);
if (!$fkCourse) {
  http_response_code(500);
  echo json_encode(['error'=>true,'message'=>"Could not find a course foreign key in 'batches'. Expected one of: course_id, course, c_id, courseId, courseID"]);
  exit;
}

$has = [];
foreach ([
  'id','batch_name','registration_open_date','registration_close_date',
  'batch_start_date','batch_end_date','total_seat_count',
  'delivery_mode','medium_of_instruction','intake_name','branch_id'
] as $c) { $has[$c] = col_exists($db,'batches',$c); }

/* NEW: find a code column to expose as batch_code */
$codeCol = null;
foreach (['batch_code','code','batchcode','BatchCode','Batch_code','b_code'] as $c) {
  if (col_exists($db, 'batches', $c)) { $codeCol = $c; break; }
}

$joinBranches = $has['branch_id'] && table_exists($db,'branches')
  && col_exists($db,'branches','id') && col_exists($db,'branches','branch_name');

/* ---- optional fill/registration table just to count filled seats ---- */
$fillTbl = $fillId = $fillBid = $fillStatus = null;
foreach (['enrollments','registrations','student_enrollments','student_registrations'] as $t) {
  if (!table_exists($db,$t)) continue;
  $idC = null; foreach (['id','enrollment_id','reg_id'] as $x) if (col_exists($db,$t,$x)) { $idC=$x; break; }
  $bC  = null; foreach (['batch_id','batchid'] as $x) if (col_exists($db,$t,$x)) { $bC=$x; break; }
  $stC = null; foreach (['status','state'] as $x) if (col_exists($db,$t,$x)) { $stC=$x; break; }
  if ($idC && $bC) { $fillTbl=$t; $fillId=$idC; $fillBid=$bC; $fillStatus=$stC; break; }
}

/* ---- build SELECT safely (only existing columns) ---- */
$sel = [];
$sel[] = $has['id'] ? "b.id" : "CAST(b.`{$fkCourse}` AS UNSIGNED) AS id";
$sel[] = $has['batch_name'] ? "b.batch_name" : "CONCAT('Batch #', b.id) AS batch_name";
$sel[] = $has['batch_start_date'] ? "DATE(b.batch_start_date) AS batch_start_date" : "NULL AS batch_start_date";
$sel[] = $has['batch_end_date']   ? "DATE(b.batch_end_date)   AS batch_end_date"   : "NULL AS batch_end_date";
$sel[] = $has['registration_open_date']  ? "DATE(b.registration_open_date)  AS registration_open_date"  : "NULL AS registration_open_date";
$sel[] = $has['registration_close_date'] ? "DATE(b.registration_close_date) AS registration_close_date" : "NULL AS registration_close_date";
$sel[] = $has['total_seat_count'] ? "b.total_seat_count" : "0 AS total_seat_count";
$sel[] = $has['delivery_mode'] ? "b.delivery_mode" : "NULL AS delivery_mode";
$sel[] = $has['medium_of_instruction'] ? "b.medium_of_instruction" : "NULL AS medium_of_instruction";
$sel[] = $has['intake_name'] ? "b.intake_name" : "NULL AS intake_name";
$sel[] = $has['branch_id'] ? "b.branch_id" : "NULL AS branch_id";
$sel[] = $codeCol ? "b.`{$codeCol}` AS batch_code" : "NULL AS batch_code"; // <— NEW

$joins = [];
if ($joinBranches) {
  $sel[] = "br.branch_name";
  $joins[] = "LEFT JOIN branches br ON br.id = b.branch_id";
} else {
  $sel[] = "NULL AS branch_name";
}
if ($fillTbl) {
  $cond = $fillStatus ? "AND (e.`{$fillStatus}` IS NULL OR e.`{$fillStatus}` NOT IN ('Cancelled','Rejected'))" : "";
  $joins[] = "LEFT JOIN `{$fillTbl}` e ON e.`{$fillBid}` = b.id {$cond}";
  $sel[] = "COUNT(e.`{$fillId}`) AS filled";
} else {
  $sel[] = "0 AS filled";
}

$where = ["b.`{$fkCourse}` = ?"];
$types = 'i'; $params = [$course_id];
if ($has['registration_open_date'])  { $where[] = "(b.registration_open_date IS NULL  OR b.registration_open_date <= ?)";  $types.='s'; $params[]=$today; }
if ($has['registration_close_date']) { $where[] = "(b.registration_close_date IS NULL OR b.registration_close_date >= ?)"; $types.='s'; $params[]=$today; }

$order = $has['batch_start_date'] ? "b.batch_start_date ASC, b.id ASC" : "b.id ASC";

$sql = "SELECT
  ".implode(",\n  ", $sel)."
FROM batches b
".implode("\n", $joins)."
WHERE ".implode(" AND ", $where)."
GROUP BY b.id
ORDER BY {$order}";

try {
  $st = $db->prepare($sql);
  if (!$st) throw new Exception($db->error ?: 'SQL prepare failed');
  $st->bind_param($types, ...$params);
  $st->execute();
  $rs = $st->get_result();

  $out = [];
  while ($r = $rs->fetch_assoc()) {
    $filled = (int)($r['filled'] ?? 0);
    $total  = (int)($r['total_seat_count'] ?? 0);
    $avail  = max(0, $total - $filled);

    // nice label
    $display = trim(($r['batch_name'] ?? 'Batch')) . ' (' .
               (($r['batch_start_date'] ?? '-') ?: '-') . ' - ' .
               (($r['batch_end_date']   ?? '-') ?: '-') . ') – Seats Left: ' . $avail;

    $out[] = [
      'id'                      => (int)$r['id'],
      'batch_name'              => $r['batch_name'],
      'batch_start_date'        => $r['batch_start_date'],
      'batch_end_date'          => $r['batch_end_date'],
      'registration_open_date'  => $r['registration_open_date'],
      'registration_close_date' => $r['registration_close_date'],
      'total_seat_count'        => $total,
      'filled'                  => $filled,
      'available_seats'         => $avail,
      'display_text'            => $display,
      'delivery_mode'           => $r['delivery_mode'],
      'medium_of_instruction'   => $r['medium_of_instruction'],
      'intake_name'             => $r['intake_name'],
      'branch_id'               => $r['branch_id'],
      'branch_name'             => $r['branch_name'],
      'batch_code'              => $r['batch_code'],   // <— NEW in payload
    ];
  }
  $st->close();
  echo json_encode($out);
} catch (Throwable $e) {
  http_response_code(500);
  echo json_encode(['error'=>true,'message'=>$e->getMessage(),'sql'=>$sql]);
}
