<?php
// admin/subscriber_reference_data_lib.php
// Stage 15: shared speed/package/sector reference lists for add/edit subscriber pages.

require_once __DIR__ . '/helpers.php';

if (!function_exists('subscriber_ref_select_expr')) {
  function subscriber_ref_select_expr(mysqli $conn, string $table, string $col, string $alias='', string $fallback="''"): string {
    $alias = $alias !== '' ? $alias : $col;
    if (table_exists($conn, $table) && col_exists($conn, $table, $col)) {
      return "`$col` AS `$alias`";
    }
    return $fallback . " AS `$alias`";
  }
}

if (!function_exists('subscriber_ref_fetch_speeds')) {
  function subscriber_ref_fetch_speeds(mysqli $conn): array {
    $rows = [];
    if (!table_exists($conn, 'speeds')) return $rows;

    $cols = [];
    foreach (['id','speed_name','price','mikrotik_profile','rate_limit'] as $col) {
      if (col_exists($conn, 'speeds', $col)) $cols[] = "`$col`";
    }
    if (!$cols) return $rows;

    $order = col_exists($conn, 'speeds', 'id') ? '`id` DESC' : '1';
    $q = @ $conn->query('SELECT '.implode(',', $cols).' FROM `speeds` ORDER BY '.$order);
    if ($q) while ($r = $q->fetch_assoc()) $rows[] = $r;
    return $rows;
  }
}

if (!function_exists('subscriber_ref_fetch_packages')) {
  function subscriber_ref_fetch_packages(mysqli $conn): array {
    $rows = [];
    if (!table_exists($conn, 'packages')) return $rows;

    $pCols = [];
    foreach (['id','package_name','price','validity_days','speed_id','mikrotik_profile','quota_gb','subscription_fee'] as $col) {
      if (col_exists($conn, 'packages', $col)) $pCols[] = "p.`$col`";
    }
    if (!$pCols) return $rows;

    $canJoinSpeed = table_exists($conn, 'speeds') && col_exists($conn, 'packages', 'speed_id') && col_exists($conn, 'speeds', 'id') && col_exists($conn, 'speeds', 'speed_name');
    $linked = $canJoinSpeed ? ', s.`speed_name` AS `linked_speed_name`' : ", '' AS `linked_speed_name`";
    $join = $canJoinSpeed ? ' LEFT JOIN `speeds` s ON s.`id`=p.`speed_id`' : '';
    $order = col_exists($conn, 'packages', 'id') ? 'p.`id` DESC' : '1';

    $q = @ $conn->query('SELECT '.implode(',', $pCols).$linked.' FROM `packages` p'.$join.' ORDER BY '.$order);
    if ($q) while ($r = $q->fetch_assoc()) $rows[] = $r;
    return $rows;
  }
}

if (!function_exists('subscriber_ref_fetch_sectors')) {
  function subscriber_ref_fetch_sectors(mysqli $conn, bool $enabledOnly=true): array {
    $rows = [];
    if (!table_exists($conn, 'sectors')) return $rows;

    $cols = [];
    foreach (['id','name','vlan_id','pppoe_profile'] as $col) {
      if (col_exists($conn, 'sectors', $col)) $cols[] = "`$col`";
    }
    if (!$cols) return $rows;

    $where = ($enabledOnly && col_exists($conn, 'sectors', 'is_enabled')) ? ' WHERE `is_enabled`=1' : '';
    $orderParts = [];
    if (col_exists($conn, 'sectors', 'vlan_id')) $orderParts[] = '`vlan_id` ASC';
    if (col_exists($conn, 'sectors', 'id')) $orderParts[] = '`id` ASC';
    $order = $orderParts ? implode(',', $orderParts) : '1';

    $q = @ $conn->query('SELECT '.implode(',', $cols).' FROM `sectors`'.$where.' ORDER BY '.$order);
    if ($q) while ($r = $q->fetch_assoc()) $rows[] = $r;
    return $rows;
  }
}
