<?php declare(strict_types=1);
/**
 * students.json.php
 * Endpoints:
 *   t=courses
 *   t=batches&course_id={id}
 *   t=students&course_id={id}[&batch_ids=1,2,3]
 *
 * Scope: EXACTLY Courses / Batches / Students for release screen.
 */

define('APP_ROOT', dirname(__DIR__, 2));
if (session_status() === PHP_SESSION_NONE) session_start();

require_once APP_ROOT.'/includes/auth.php';
require_once APP_ROOT.'/includes/db.php';
require_once APP_ROOT.'/includes/scope.php';
require_role(['super_admin','branch_admin']);

header('Content-Type: application/json; charset=utf-8');

/* ---------- mysqli handle ---------- */
$db = null;
if (isset($mysqli) && $mysqli instanceof mysqli) $db = $mysqli;
elseif (isset($conn) && $conn instanceof mysqli) $db = $conn;
else { $db = new mysqli('localhost','root','','edencamp_edencampus'); }
if ($db->connect_errno) { http_response_code(500); echo json_encode(['items'=>[], 'error'=>'DB connect failed']); exit; }
$db->set_charset('utf8mb4');

/* ---------- helpers ---------- */
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;
}
function view_exists(mysqli $db, string $v): bool {
  $v = $db->real_escape_string($v);
  $q = $db->query("SHOW FULL TABLES WHERE Table_type='VIEW' AND Tables_in_{$db->real_escape_string($db->query('select database()')->fetch_row()[0])} = '$v'");
  // Some hosts block that pattern; fallback:
  if (!$q || !$q->num_rows) {
    $q = $db->query("SHOW FULL TABLES WHERE Table_type='VIEW'");
    if ($q) while ($r = $q->fetch_row()) if (strcasecmp($r[0], $v) === 0) return true;
    return false;
  }
  return $q->num_rows > 0;
}
function is_parent_course(mysqli $db, int $course_id): bool {
  $st = $db->prepare("SELECT 1 FROM course_children WHERE parent_id=? LIMIT 1");
  $st->bind_param("i", $course_id);
  $st->execute(); $st->store_result();
  $b = $st->num_rows > 0; $st->close();
  return $b;
}
function to_int_list(string $csv): array {
  $out = [];
  foreach (explode(',', $csv) as $p) {
    $n = (int)trim($p);
    if ($n > 0) $out[] = $n;
  }
  return array_values(array_unique($out));
}
function build_label(array $r): string {
  // Prefer full_name (you have this). Fallbacks keep it robust if schema changes.
  $nm = '';
  if (!empty($r['full_name'])) {
    $nm = trim((string)$r['full_name']);
  } elseif (!empty($r['first_name']) || !empty($r['last_name'])) {
    $nm = trim(($r['first_name'] ?? '').' '.($r['last_name'] ?? ''));
  } elseif (!empty($r['name'])) {
    $nm = trim((string)$r['name']);
  }
  if ($nm === '') $nm = '#'.(string)($r['id'] ?? $r['student_id'] ?? '');
  $parts = [$nm];
  if (!empty($r['reference_no'])) $parts[] = 'Ref '.$r['reference_no'];
  if (!empty($r['center_reg_no'])) $parts[] = $r['center_reg_no'];
  return implode(' • ', $parts);
}

/* ---------- router ---------- */
$t = $_GET['t'] ?? '';

/* ===== 1) COURSES (leaf only) ===== */
if ($t === 'courses') {
  $items = [];
  if (view_exists($db, 'leaf_courses')) {
    $res = $db->query("SELECT id, course_name FROM leaf_courses ORDER BY course_name");
  } else {
    $res = $db->query("
      SELECT c.id, c.course_name
      FROM courses c
      WHERE c.status='Active'
        AND NOT EXISTS (SELECT 1 FROM course_children cc WHERE cc.parent_id = c.id)
      ORDER BY c.course_name
    ");
  }
  if ($res) while ($r = $res->fetch_assoc()) $items[] = ['id'=>(int)$r['id'], 'name'=>$r['course_name']];
  echo json_encode(['items'=>$items]); exit;
}

/* ===== 2) BATCHES for course ===== */
if ($t === 'batches') {
  $course_id = (int)($_GET['course_id'] ?? 0);
  if ($course_id <= 0) { echo json_encode(['items'=>[], 'error'=>'course_id required']); exit; }
  // We run in "leaf-only" mode: reject parent (Bundle)
  if (is_parent_course($db, $course_id)) {
    echo json_encode(['items'=>[], 'note'=>'Select a real course (Bundle is a parent).']); exit;
  }

  if (view_exists($db, 'course_batches_v')) {
    $st = $db->prepare("
      SELECT batch_id AS id, batch_name
      FROM course_batches_v
      WHERE course_id = ?
      ORDER BY batch_start_date DESC, id DESC
    ");
  } else {
    $st = $db->prepare("
      SELECT b.id AS id, b.batch_name
      FROM batches b
      WHERE b.course_id = ?
        AND b.status='Active'
      ORDER BY b.batch_start_date DESC, b.id DESC
    ");
  }
  $st->bind_param("i", $course_id);
  $st->execute(); $rs = $st->get_result();
  $items = [];
  while ($r = $rs->fetch_assoc()) $items[] = ['id'=>(int)$r['id'], 'label'=>$r['batch_name']];
  echo json_encode(['items'=>$items]); exit;
}

/* ===== 3) STUDENTS (course or specific batches) =====
   - if &batch_ids= present => filter by those batches (distinct)
   - else => all students in ANY batch of course_id
*/
if ($t === 'students') {
  $course_id = (int)($_GET['course_id'] ?? 0);
  $batch_ids = isset($_GET['batch_ids']) ? to_int_list((string)$_GET['batch_ids']) : [];

  $items = [];
  if ($batch_ids) {
    // Students from selected batch(es)
    $in = implode(',', array_fill(0, count($batch_ids), '?'));
    $types = str_repeat('i', count($batch_ids));
    $sql = "
      SELECT DISTINCT s.*
      FROM students s
      JOIN enrollments e ON e.student_id = s.id
      WHERE e.batch_id IN ($in)
      ORDER BY s.id DESC
    ";
    $st = $db->prepare($sql);
    $st->bind_param($types, ...$batch_ids);
  } else {
    if ($course_id <= 0) { echo json_encode(['items'=>[], 'error'=>'course_id required']); exit; }
    // Students for a course (any batch under that course)
    if (view_exists($db, 'course_students_v')) {
      $st = $db->prepare("
        SELECT DISTINCT student_id AS id, full_name, reference_no, center_reg_no
        FROM course_students_v
        WHERE course_id = ?
        ORDER BY id DESC
      ");
      $st->bind_param("i", $course_id);
    } else {
      $st = $db->prepare("
        SELECT DISTINCT s.*
        FROM students s
        JOIN enrollments e ON e.student_id = s.id
        JOIN batches     b ON b.id = e.batch_id
        WHERE b.course_id = ?
        ORDER BY s.id DESC
      ");
      $st->bind_param("i", $course_id);
    }
  }

  $st->execute(); $rs = $st->get_result();
  while ($r = $rs->fetch_assoc()) {
    // Normalize id key (view vs table)
    if (!isset($r['id']) && isset($r['student_id'])) $r['id'] = (int)$r['student_id'];
    $items[] = [
      'id'    => (int)$r['id'],
      'label' => build_label($r),
    ];
  }
  echo json_encode(['items'=>$items]); exit;
}

/* ----- default ----- */
echo json_encode(['items'=>[], 'note'=>'unknown t']);
