<?php
// /elms/includes/scope.php
// Schema-safe helpers (guarded) used across student pages.

declare(strict_types=1);

require_once __DIR__ . '/db.php';

if (!function_exists('esc')) {
  function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'); }
}

/* ------------------------------------------------------------------
   Existence helpers (guarded) — use INFORMATION_SCHEMA with binds
-------------------------------------------------------------------*/
if (!function_exists('table_exists_local')) {
  function table_exists_local(string $table): bool {
    global $mysqli;
    try {
      $sql = "SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
               LIMIT 1";
      $st = $mysqli->prepare($sql);
      $st->bind_param('s', $table);
      $st->execute();
      $st->store_result();
      $ok = $st->num_rows > 0;
      $st->free_result();
      $st->close();
      return $ok;
    } catch (Throwable $e) {
      return false;
    }
  }
}

if (!function_exists('column_exists_local')) {
  function column_exists_local(string $table, string $column): bool {
    global $mysqli;
    try {
      $sql = "SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = DATABASE()
                 AND TABLE_NAME  = ?
                 AND COLUMN_NAME = ?
               LIMIT 1";
      $st = $mysqli->prepare($sql);
      $st->bind_param('ss', $table, $column);
      $st->execute();
      $st->store_result();
      $ok = $st->num_rows > 0;
      $st->free_result();
      $st->close();
      return $ok;
    } catch (Throwable $e) {
      return false;
    }
  }
}

/* ------------------------------------------------------------------
   Small SQL helpers (guarded)
-------------------------------------------------------------------*/
if (!function_exists('sql_all')) {
  function sql_all(string $sql, ?callable $binder = null): array {
    global $mysqli;
    try {
      $st = $mysqli->prepare($sql);
      if ($binder) $binder($st);
      $st->execute();
      $rs  = $st->get_result();
      $out = [];
      while ($r = $rs->fetch_assoc()) $out[] = $r;
      $st->close();
      return $out;
    } catch (Throwable $e) {
      return [];
    }
  }
}
if (!function_exists('sql_one')) {
  function sql_one(string $sql, ?callable $binder = null): ?array {
    $a = sql_all($sql, $binder);
    return $a[0] ?? null;
  }
}

/* ------------------------------------------------------------------
   Data providers (schema-aware, with sensible fallbacks)
-------------------------------------------------------------------*/

/** Courses list: id => label (picks best label column available). */
if (!function_exists('elms_all_courses')) {
  function elms_all_courses(mysqli $db): array {
    if (!table_exists_local('courses')) return [];
    $label = column_exists_local('courses','course_name') ? 'course_name'
           : (column_exists_local('courses','name')        ? 'name'
           : (column_exists_local('courses','title')       ? 'title'
           : (column_exists_local('courses','code')        ? 'code' : 'id')));
    $out = [];
    $rs  = $db->query("SELECT id, $label AS label FROM courses ORDER BY label");
    if ($rs) while ($r = $rs->fetch_assoc()) $out[(int)$r['id']] = (string)$r['label'];
    return $out;
  }
}

/**
 * Branch/Centre map and which batches.* column to use as the reference.
 * Returns: [map, tableName|null, batchesRefCol|null]
 */
if (!function_exists('elms_all_branches')) {
  function elms_all_branches(mysqli $db): array {
    $map = []; $table = null; $refCol = null; $labelCol = null;

    // Prefer branches if present, otherwise centres
    if (table_exists_local('batches') && table_exists_local('branches') && column_exists_local('batches','branch_id')) {
      $table  = 'branches';
      $refCol = 'branch_id';
      foreach (['name','branch_name','title','code'] as $c) {
        if (column_exists_local('branches', $c)) { $labelCol = $c; break; }
      }
    } elseif (table_exists_local('batches') && table_exists_local('centres') && column_exists_local('batches','centre_id')) {
      $table  = 'centres';
      $refCol = 'centre_id';
      foreach (['name','centre_name','center_name','title','code'] as $c) {
        if (column_exists_local('centres', $c)) { $labelCol = $c; break; }
      }
    }

    if ($table) {
      $labelSel = $labelCol ?: 'id';
      $rs = $db->query("SELECT id, $labelSel AS label FROM $table ORDER BY label");
      if ($rs) while ($r = $rs->fetch_assoc()) $map[(int)$r['id']] = (string)$r['label'];
    }

    return [$map, $table, $refCol];
  }
}

/** Batches list: id => label (optionally filter by course/branch). */
if (!function_exists('elms_all_batches')) {
  function elms_all_batches(mysqli $db, int $courseId = 0, int $branchId = 0, ?string $branchRefCol = null): array {
    if (!table_exists_local('batches')) return [];

    $labelCol = column_exists_local('batches','batch_name') ? 'batch_name'
              : (column_exists_local('batches','name')       ? 'name'
              : (column_exists_local('batches','title')      ? 'title' : 'id'));

    $where = "WHERE 1=1"; $types = ''; $vals = [];

    if ($courseId > 0 && column_exists_local('batches','course_id')) {
      $where .= " AND course_id=?"; $types .= 'i'; $vals[] = $courseId;
    }
    if ($branchId > 0 && $branchRefCol && column_exists_local('batches',$branchRefCol)) {
      $where .= " AND $branchRefCol=?"; $types .= 'i'; $vals[] = $branchId;
    }

    $stmt = $db->prepare("SELECT id, $labelCol AS label FROM batches $where ORDER BY id DESC");
    if ($types) $stmt->bind_param($types, ...$vals);
    $stmt->execute();
    $rs = $stmt->get_result();

    $out = [];
    while ($r = $rs->fetch_assoc()) $out[(int)$r['id']] = (string)$r['label'];
    $stmt->close();
    return $out;
  }
}

/**
 * Single batch detail (returns only columns that exist in your schema).
 * Always includes: id; includes course label if possible.
 */
if (!function_exists('elms_get_batch')) {
  function elms_get_batch(mysqli $db, int $batchId): ?array {
    if (!table_exists_local('batches')) return null;

    // Build dynamic column list from batches
    $cols = ['b.id'];
    foreach (['batch_name','batch_start_date','batch_end_date','status','course_id','branch_id','centre_id'] as $c) {
      if (column_exists_local('batches', $c)) $cols[] = "b.$c";
    }
    $select = implode(',', $cols);

    // Optional join to courses for label
    $join  = '';
    $extra = '';
    if (in_array('b.course_id', $cols, true) && table_exists_local('courses')) {
      $clab = column_exists_local('courses','course_name') ? 'course_name'
            : (column_exists_local('courses','name')        ? 'name'
            : (column_exists_local('courses','title')       ? 'title'
            : (column_exists_local('courses','code')        ? 'code' : 'id')));
      $join  = ' JOIN courses c ON c.id = b.course_id ';
      $extra = ", c.$clab AS course_name";
    }

    $sql = "SELECT $select $extra
              FROM batches b
              $join
             WHERE b.id = ?
             LIMIT 1";

    $st = $db->prepare($sql);
    $st->bind_param('i', $batchId);
    $st->execute();
    $row = $st->get_result()->fetch_assoc();
    $st->close();

    return $row ?: null;
  }
}
