<?php declare(strict_types=1);
/**
 * Admin → Batches (friendly list + filters + CSV)
 * Columns shown: id, course name, batch_name, batch_start_date
 * - Robust to missing columns/tables
 * - Uses admin_layout_* (or layout_*) so the sidebar appears on every page
 * - Absolute links via app_url()
 */

if (session_status() === PHP_SESSION_NONE) session_start();

/* ---------- Auth / DB / helpers ---------- */
require_once __DIR__ . '/../../includes/auth.php';
require_role(['super_admin','branch_admin']);

require_once __DIR__ . '/../../includes/db.php';
if (is_file(__DIR__ . '/../../includes/helpers.php')) require_once __DIR__ . '/../../includes/helpers.php';

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

/* ---------- Tiny local helpers (schema-safe) ---------- */
function table_exists_local(mysqli $db, string $t): bool {
  $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? LIMIT 1");
  $st->bind_param('s', $t); $st->execute();
  $ok = (bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
}
function col_exists_local(mysqli $db, string $t, string $c): bool {
  $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1");
  $st->bind_param('ss',$t,$c); $st->execute();
  $ok = (bool)$st->get_result()->fetch_row(); $st->close(); return $ok;
}
function label_col(mysqli $db, string $table, array $candidates): string {
  $have = [];
  if ($rs = $db->query("SHOW COLUMNS FROM `$table`")) { while ($r=$rs->fetch_assoc()) $have[] = $r['Field']; }
  foreach ($candidates as $c) if (in_array($c, $have, true)) return $c;
  return 'id';
}

/* ---------- Guard ---------- */
if (!table_exists_local($mysqli,'batches')) {
  http_response_code(500);
  exit('<h1>Batches table not found</h1><p>Create a table named <code>batches</code>.</p>');
}

/* ---------- Filters ---------- */
$course_id = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
$from      = trim((string)($_GET['from'] ?? ''));      // yyyy-mm-dd
$to        = trim((string)($_GET['to']   ?? ''));      // yyyy-mm-dd
$export    = isset($_GET['export']) ? 1 : 0;

/* ---------- Courses dropdown ---------- */
$courses = [];
if (table_exists_local($mysqli,'courses')) {
  $courseLabel = label_col($mysqli,'courses',['course_name','name','title','label']);
  $sql = "SELECT id, COALESCE($courseLabel,'#') AS label FROM courses ORDER BY label";
  if ($rs = $mysqli->query($sql)) {
    while ($r = $rs->fetch_assoc()) $courses[(int)$r['id']] = $r['label'];
  }
}

/* ---------- WHERE builder (prefers start_date) ---------- */
$useStart = col_exists_local($mysqli,'batches','batch_start_date');
$useEnd   = col_exists_local($mysqli,'batches','batch_end_date');
$useCourseCol = col_exists_local($mysqli,'batches','course_id');

$where   = ["1=1"];
$p_types = '';
$p_vals  = [];

if ($course_id > 0 && $useCourseCol) {
  $where[]   = "b.course_id = ?";
  $p_types  .= 'i';
  $p_vals[]  = $course_id;
}

/* Filter by START DATE when possible (more intuitive with the UI) */
if ($from !== '' && $useStart) { $where[] = "b.batch_start_date >= ?"; $p_types .= 's'; $p_vals[] = $from; }
if ($to   !== '' && $useStart) { $where[] = "b.batch_start_date <= ?"; $p_types .= 's'; $p_vals[] = $to; }

/* If start_date column not present, fall back to end_date */
if (!$useStart && $from !== '' && $useEnd) { $where[] = "b.batch_end_date >= ?"; $p_types .= 's'; $p_vals[] = $from; }
if (!$useStart && $to   !== '' && $useEnd) { $where[] = "b.batch_end_date <= ?"; $p_types .= 's'; $p_vals[] = $to; }

/* ---------- Select batches (id, course_id, name, start_date) ---------- */
$select = "b.id";
if ($useCourseCol) $select .= ", b.course_id";
$select .= ", " . (col_exists_local($mysqli,'batches','batch_name') ? "b.batch_name" : "'' AS batch_name");
$select .= ", " . ($useStart ? "b.batch_start_date" : "NULL AS batch_start_date");

$sql = "SELECT $select FROM batches b WHERE ".implode(' AND ', $where)." ORDER BY b.id DESC";
$st  = $mysqli->prepare($sql);
if ($p_types !== '') $st->bind_param($p_types, ...$p_vals);
$st->execute();
$rs   = $st->get_result();
$rows = $rs->fetch_all(MYSQLI_ASSOC);
$st->close();

/* ---------- Resolve course names (single IN query) ---------- */
$courseNames = [];
if ($rows && $useCourseCol && table_exists_local($mysqli,'courses')) {
  $ids = array_values(array_unique(array_map(fn($r)=>(int)($r['course_id']??0), $rows)));
  $ids = array_filter($ids, fn($v)=>$v>0);
  if ($ids) {
    $courseLabel = label_col($mysqli,'courses',['course_name','name','title','label']);
    $in  = implode(',', array_fill(0, count($ids), '?'));
    $typ = str_repeat('i', count($ids));
    $st  = $mysqli->prepare("SELECT id, COALESCE($courseLabel,'#') AS label FROM courses WHERE id IN ($in)");
    $st->bind_param($typ, ...$ids); $st->execute();
    $crs = $st->get_result();
    while ($r = $crs->fetch_assoc()) $courseNames[(int)$r['id']] = $r['label'];
    $st->close();
  }
}

/* ---------- CSV export ---------- */
if ($export) {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=batches.csv');
  $out = fopen('php://output','w');
  fputcsv($out, ['ID','Course','Batch Name','Start Date']);
  foreach ($rows as $r) {
    $cid  = (int)($r['course_id'] ?? 0);
    $cLab = $cid && isset($courseNames[$cid]) ? $courseNames[$cid] : ($cid ?: '');
    fputcsv($out, [
      (int)$r['id'],
      $cLab,
      (string)$r['batch_name'],
      (string)$r['batch_start_date'],
    ]);
  }
  fclose($out);
  exit;
}

/* ---------- Layout open (ensures sidebar) ---------- */
$__layoutOpened = false;
foreach (['admin_layout_top.php','layout_top.php'] as $f) {
  $p = __DIR__ . '/../../includes/' . $f;
  if (is_file($p)) { require $p; $__layoutOpened = true; break; }
}
if (!$__layoutOpened) {
  echo "<!doctype html><html><head><meta charset='utf-8'><title>Batches</title>";
  echo "<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css'></head><body class='container py-3'>";
}
?>

<h1 class="h4 mb-3">Batches</h1>

<form class="row g-2 mb-3" method="get" action="<?= esc(app_url('/admin/batches/index.php')) ?>">
  <div class="col-auto">
    <label class="form-label">Course</label>
    <select name="course_id" class="form-select">
      <option value="0">All courses</option>
      <?php foreach ($courses as $cid=>$lab): ?>
        <option value="<?= (int)$cid ?>" <?= $course_id===$cid?'selected':'' ?>><?= esc($lab) ?></option>
      <?php endforeach; ?>
    </select>
  </div>
  <div class="col-auto">
    <label class="form-label">From</label>
    <input type="date" name="from" value="<?= esc($from) ?>" class="form-control">
  </div>
  <div class="col-auto">
    <label class="form-label">To</label>
    <input type="date" name="to" value="<?= esc($to) ?>" class="form-control">
  </div>
  <div class="col-auto align-self-end">
    <button class="btn btn-primary">Filter</button>
    <a class="btn btn-outline-secondary" href="<?= esc(app_url('/admin/batches/index.php')) ?>">Reset</a>
    <?php
      $qs = array_filter([
        'course_id' => $course_id ?: null,
        'from'      => $from ?: null,
        'to'        => $to ?: null,
      ]);
    ?>
    <a class="btn btn-success" href="<?= esc(app_url('/admin/batches/index.php')).'?'.http_build_query($qs + ['export'=>1]) ?>">Export CSV</a>
  </div>
</form>

<div class="card border-0 shadow-sm">
  <div class="card-body">
    <div class="table-responsive">
      <table class="table table-hover align-middle">
        <thead>
          <tr>
            <th style="width:90px">ID</th>
            <th>Course</th>
            <th>Batch Name</th>
            <th style="width:170px"><?= $useStart ? 'Start Date' : 'Date' ?></th>
          </tr>
        </thead>
        <tbody>
        <?php if (!$rows): ?>
          <tr><td colspan="4" class="text-center text-muted">No results.</td></tr>
        <?php else: foreach ($rows as $r):
          $cid  = (int)($r['course_id'] ?? 0);
          $cLab = $cid && isset($courseNames[$cid]) ? $courseNames[$cid] : ($cid ?: '—');
        ?>
          <tr>
            <td><?= (int)$r['id'] ?></td>
            <td><?= esc($cLab) ?></td>
            <td><?= esc($r['batch_name']) ?></td>
            <td><?= esc($r['batch_start_date']) ?></td>
          </tr>
        <?php endforeach; endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<?php
/* ---------- Layout close ---------- */
$__layoutClosed = false;
foreach (['admin_layout_bottom.php','layout_bottom.php'] as $f) {
  $p = __DIR__ . '/../../includes/' . $f;
  if (is_file($p)) { require $p; $__layoutClosed = true; break; }
}
if (!$__layoutClosed) echo '</body></html>';
