<?php
// admin/ua_unified_stats_lib.php
// مصدر واحد وخفيف لكل عدادات المشتركين في البرنامج.
// المرجع الحي للاتصال/الفصل هو MikroTik عبر الكاش الموحد، مع fallback آمن من قاعدة البيانات عند تعذر الاتصال.

if (!function_exists('ua_stats_col_exists')) {
  function ua_stats_col_exists(mysqli $conn, string $table, string $col): bool {
    static $cache = [];
    $k = $table . '|' . $col;
    if (array_key_exists($k, $cache)) return $cache[$k];
    $t = $conn->real_escape_string($table);
    $c = $conn->real_escape_string($col);
    $rs = @$conn->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return $cache[$k] = (bool)($rs && $rs->num_rows > 0);
  }
}

if (!function_exists('ua_stats_base_where')) {
  function ua_stats_base_where(mysqli $conn, string $alias = ''): string {
    $p = $alias !== '' ? rtrim($alias, '.') . '.' : '';
    return ua_stats_col_exists($conn, 'subscribers', 'is_deleted') ? "COALESCE({$p}is_deleted,0)=0" : '1=1';
  }
}

if (!function_exists('ua_stats_norm_user')) {
  function ua_stats_norm_user(string $u): string {
    $u = trim($u);
    $u = preg_replace('/@.*/', '', $u);
    return trim((string)$u);
  }
}

if (!function_exists('ua_stats_local_users')) {
  function ua_stats_local_users(mysqli $conn): array {
    $cols = ['id', 'username'];
    if (ua_stats_col_exists($conn, 'subscribers', 'ppp_username')) $cols[] = 'ppp_username';
    $sql = 'SELECT ' . implode(',', array_map(static fn($c) => '`'.$c.'`', $cols)) . ' FROM subscribers WHERE ' . ua_stats_base_where($conn);
    $rs = @$conn->query($sql);
    $out = [];
    if ($rs) {
      while ($r = $rs->fetch_assoc()) {
        $u = ua_stats_norm_user((string)($r['ppp_username'] ?? ''));
        if ($u === '') $u = ua_stats_norm_user((string)($r['username'] ?? ''));
        if ($u !== '') $out[$u] = (int)($r['id'] ?? 0);
      }
    }
    return $out;
  }
}

if (!function_exists('ua_stats_db_cut_count')) {
  function ua_stats_db_cut_count(mysqli $conn): int {
    $parts = [];
    if (ua_stats_col_exists($conn,'subscribers','ppp_is_disabled')) $parts[] = 'COALESCE(ppp_is_disabled,0)=1';
    if (ua_stats_col_exists($conn,'subscribers','mt_disabled')) $parts[] = 'COALESCE(mt_disabled,0)=1';
    if (ua_stats_col_exists($conn,'subscribers','is_cut')) $parts[] = 'COALESCE(is_cut,0)=1';
    if (ua_stats_col_exists($conn,'subscribers','speed_state')) $parts[] = "UPPER(COALESCE(speed_state,''))='CUT'";
    if (ua_stats_col_exists($conn,'subscribers','status')) $parts[] = "UPPER(COALESCE(status,'')) IN ('CUT','DISABLED','STOPPED')";
    if (!$parts) return 0;
    $sql = 'SELECT COUNT(*) c FROM subscribers WHERE ' . ua_stats_base_where($conn) . ' AND (' . implode(' OR ', $parts) . ')';
    $rs = @$conn->query($sql);
    if ($rs && ($r = $rs->fetch_assoc())) return (int)($r['c'] ?? 0);
    return 0;
  }
}


if (!function_exists('ua_stats_table_exists')) {
  function ua_stats_table_exists(mysqli $conn, string $table): bool {
    static $cache = [];
    if (array_key_exists($table, $cache)) return $cache[$table];
    $t = $conn->real_escape_string($table);
    $rs = @$conn->query("SHOW TABLES LIKE '{$t}'");
    return $cache[$table] = (bool)($rs && $rs->num_rows > 0);
  }
}

if (!function_exists('ua_stats_db_unsafe_count')) {
  function ua_stats_db_unsafe_count(mysqli $conn): int {
    $base = ua_stats_base_where($conn);
    $ids = [];

    // Old/global open-speed toggle stored the affected accounts here.  This is
    // the closest DB-side equivalent to the previous counter and keeps the
    // number visible even when we do not refresh RouterOS on every page load.
    if (ua_stats_table_exists($conn, 'ppp_open_all_backup')) {
      $hasUser = ua_stats_col_exists($conn, 'ppp_open_all_backup', 'username');
      $hasSid  = ua_stats_col_exists($conn, 'ppp_open_all_backup', 'subscriber_id');
      if ($hasSid) {
        $sql = "SELECT DISTINCT s.id FROM ppp_open_all_backup b JOIN subscribers s ON s.id=b.subscriber_id WHERE {$base}";
        $rs = @$conn->query($sql);
        if ($rs) while ($r = $rs->fetch_assoc()) $ids[(int)($r['id'] ?? 0)] = true;
      } elseif ($hasUser) {
        $userExpr = ua_stats_col_exists($conn,'subscribers','ppp_username') ? "COALESCE(NULLIF(s.ppp_username,''),s.username)" : "s.username";
        $sql = "SELECT DISTINCT s.id FROM ppp_open_all_backup b JOIN subscribers s ON SUBSTRING_INDEX({$userExpr},'@',1)=SUBSTRING_INDEX(b.username,'@',1) WHERE " . ua_stats_base_where($conn, 's');
        $rs = @$conn->query($sql);
        if ($rs) while ($r = $rs->fetch_assoc()) $ids[(int)($r['id'] ?? 0)] = true;
      } else {
        $rs = @$conn->query("SELECT COUNT(*) c FROM ppp_open_all_backup");
        if ($rs && ($r=$rs->fetch_assoc())) return (int)($r['c'] ?? 0);
      }
    }

    // Newer per-account actions may mark the local subscriber row.
    if (ua_stats_col_exists($conn,'subscribers','speed_state')) {
      $rs = @$conn->query("SELECT id FROM subscribers WHERE {$base} AND UPPER(COALESCE(speed_state,'')) IN ('OPEN','OPEN_SPEED','UNLIMITED','OPEN_ALL','NO_LIMIT','WITHOUT_LIMIT')");
      if ($rs) while ($r=$rs->fetch_assoc()) $ids[(int)($r['id'] ?? 0)] = true;
    }

    // Fallback for imported/synced rows that carry the open profile name.
    foreach (['ppp_profile','profile','current_profile'] as $col) {
      if (ua_stats_col_exists($conn,'subscribers',$col)) {
        $rs = @$conn->query("SELECT id FROM subscribers WHERE {$base} AND UPPER(COALESCE(`{$col}`,'')) IN ('OPEN-ALL-UNLIMITED','UNLIMITED','LASER-UNLIMITED')");
        if ($rs) while ($r=$rs->fetch_assoc()) $ids[(int)($r['id'] ?? 0)] = true;
      }
    }

    unset($ids[0]);
    return count($ids);
  }
}

if (!function_exists('ua_stats_unsafe_cache_file')) {
  function ua_stats_unsafe_cache_file(): string {
    $dir = __DIR__ . '/cache/runtime';
    if (!is_dir($dir)) @mkdir($dir, 0777, true);
    return $dir . '/unsafe_speed_count.json';
  }
}

if (!function_exists('ua_stats_read_unsafe_cache')) {
  function ua_stats_read_unsafe_cache(int $ttl = 180): ?int {
    $file = ua_stats_unsafe_cache_file();
    if (!is_file($file) || (time() - (int)@filemtime($file)) > $ttl) return null;
    $j = json_decode((string)@file_get_contents($file), true);
    return is_array($j) && isset($j['count']) ? (int)$j['count'] : null;
  }
}

if (!function_exists('ua_stats_write_unsafe_cache')) {
  function ua_stats_write_unsafe_cache(int $count, string $source = 'unknown'): void {
    @file_put_contents(ua_stats_unsafe_cache_file(), json_encode(['count'=>max(0,$count),'source'=>$source,'ts'=>time()], JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES), LOCK_EX);
  }
}

if (!function_exists('ua_stats_write_footer_cache')) {
  function ua_stats_write_footer_cache(array $out): void {
    if (!function_exists('mt_runtime_cache_write')) return;
    mt_runtime_cache_write('footer_counts', [
      'ok' => !empty($out['ok']),
      'ts' => time(),
      'total' => (int)($out['total'] ?? 0),
      'online' => (int)($out['online'] ?? 0),
      'offline' => (int)($out['offline'] ?? 0),
      'disabled' => (int)($out['disabled'] ?? 0),
      'unsafe_speed' => (int)($out['unsafe_speed'] ?? 0),
      'open_all_mode' => (int)($out['open_all_mode'] ?? 0),
      'source_online' => (string)($out['online_source'] ?? 'unified'),
      'source_disabled' => (string)($out['disabled_source'] ?? 'unified'),
      'source_unsafe' => (string)($out['unsafe_source'] ?? 'none'),
      'router_online' => $out['router_online'] ?? null,
      'live_data_reliable' => !empty($out['live_data_reliable']),
      'reason' => (string)($out['reason'] ?? ''),
    ]);
  }
}

if (!function_exists('ua_unified_stats')) {
  function ua_unified_stats(mysqli $conn, int $ttl = 10, bool $allowRefresh = false): array {
    require_once __DIR__ . '/mikrotik_lib.php';
    require_once __DIR__ . '/ppp_active_cache_lib.php';
    if (!function_exists('setting_get') && is_file(__DIR__ . '/settings_store.php')) {
      @include_once __DIR__ . '/settings_store.php';
    }

    $out = [
      'ok' => true,
      'total' => 0,
      'online' => 0,
      'offline' => 0,
      'disabled' => 0,
      'financial_cut' => 0,
      'unsafe_speed' => 0,
      'open_all_mode' => 0,
      'ppp_active' => 0,
      'online_source' => 'none',
      'disabled_source' => 'none',
      'unsafe_source' => 'none',
      'updated_at' => date('Y-m-d H:i:s'),
      'reason' => '',
      'router_online' => null,
      'live_data_reliable' => false,
    ];

    try {
      $base = ua_stats_base_where($conn);
      $rs = @$conn->query("SELECT COUNT(*) c FROM subscribers WHERE {$base}");
      if ($rs && ($r = $rs->fetch_assoc())) $out['total'] = (int)($r['c'] ?? 0);

      $localUsers = ua_stats_local_users($conn);

      // المتصلون: مرجع MikroTik الحقيقي عبر cache/ppp_active.json، ويحدث is_online أيضاً.
      if ($allowRefresh && function_exists('ua_ppp_cache_rebuild')) {
        $activeCache = ua_ppp_cache_rebuild($conn, max(3, $ttl), false);
      } elseif (function_exists('ua_ppp_cache_snapshot')) {
        $activeCache = ua_ppp_cache_snapshot(86400);
      } elseif (function_exists('ua_ppp_cache_read')) {
        $activeCache = ua_ppp_cache_read(86400);
      } else {
        $activeCache = [];
      }
      $activeUsers = [];
      foreach ((array)($activeCache['users'] ?? []) as $u) {
        $u = ua_stats_norm_user((string)$u);
        if ($u !== '') $activeUsers[$u] = true;
      }
      $hasRouterObservation = !empty($activeCache['data_available'])
        || (!empty($activeCache['live_data_reliable']) && isset($activeCache['users']));
      $online = 0;
      if ($hasRouterObservation) {
        if ($localUsers) {
          foreach ($localUsers as $u => $_id) if (isset($activeUsers[$u])) $online++;
        } else {
          $online = (int)($activeCache['online'] ?? 0);
        }
      } elseif (ua_stats_col_exists($conn, 'subscribers', 'is_online')) {
        // No RouterOS observation exists yet. Preserve the last DB state; a
        // transport failure must not be interpreted as 0 active subscribers.
        $rsOnline = @$conn->query("SELECT COUNT(*) c FROM subscribers WHERE {$base} AND COALESCE(is_online,0)=1");
        if ($rsOnline && ($rOnline = $rsOnline->fetch_assoc())) $online = (int)($rOnline['c'] ?? 0);
      }
      $out['online'] = $online;
      $out['ppp_active'] = $online;
      $out['online_source'] = (string)($activeCache['source'] ?? 'ppp_active_cache');
      $out['router_online'] = array_key_exists('router_online', $activeCache) ? $activeCache['router_online'] : null;
      $out['live_data_reliable'] = !empty($activeCache['live_data_reliable']);
      $out['data_available'] = $hasRouterObservation;
      $out['connection_state'] = (string)($activeCache['connection_state'] ?? ($out['live_data_reliable'] ? 'online' : 'unknown'));
      $out['data_age'] = (int)($activeCache['data_age'] ?? $activeCache['age'] ?? 0);

      // المفصولون: مرجع MikroTik secret disabled للحسابات الموجودة في البرنامج، وفallback DB إن تعذر.
      $disabled = null;
      if (function_exists('mt_ppp_secret_reality_map_cached')) {
        try {
          // UI readers must never turn a stale snapshot into a synchronous
          // RouterOS request. Only the background/forced path may refresh it.
          $real = mt_ppp_secret_reality_map_cached($conn, max(20, $ttl), $allowRefresh);
          if (is_array($real) && $real) {
            $disabled = 0;
            foreach ($localUsers as $u => $_id) {
              if (isset($real[$u]) && !empty($real[$u]['disabled'])) $disabled++;
            }
            $out['disabled_source'] = 'mikrotik_secret_cache';
          }
        } catch (Throwable $e) {
          $out['reason'] = $e->getMessage();
        }
      }
      if ($disabled === null) {
        $disabled = ua_stats_db_cut_count($conn);
        $out['disabled_source'] = 'db_fallback';
      }
      $out['disabled'] = (int)$disabled;
      $out['financial_cut'] = (int)$disabled;

      // بلا تقييد / سرعة مفتوحة: يعتمد فقط على قراءة MikroTik.
      // ممنوع هنا أي fallback من قاعدة البيانات مثل speed_state أو ppp_open_all_backup،
      // حتى لا يظهر رقم مستنتج محلياً يخالف واقع RouterOS.
      $unsafe = 0;
      $cachedUnsafe = null;
      if (!$allowRefresh && function_exists('ua_stats_read_unsafe_cache')) {
        $cachedUnsafe = ua_stats_read_unsafe_cache(max(60, min(300, $ttl * 6)));
        if ($cachedUnsafe !== null) {
          $unsafe = (int)$cachedUnsafe;
          $out['unsafe_source'] = 'unsafe_speed_cache';
        } else {
          $staleUnsafe = ua_stats_read_unsafe_cache(86400);
          $unsafe = $staleUnsafe !== null ? (int)$staleUnsafe : ua_stats_db_unsafe_count($conn);
          $out['unsafe_source'] = $staleUnsafe !== null
            ? 'stale_unsafe_speed_cache'
            : 'database_state_fallback';
        }
      }
      if ($allowRefresh && function_exists('mt_ppp_users_without_limits')) {
        try {
          $unsafe = count(mt_ppp_users_without_limits($conn, array_keys($localUsers)));
          $out['unsafe_source'] = 'mikrotik_limits';
          if (function_exists('ua_stats_write_unsafe_cache')) ua_stats_write_unsafe_cache((int)$unsafe, 'mikrotik_limits');
        } catch (Throwable $e) {
          $staleUnsafe = function_exists('ua_stats_read_unsafe_cache') ? ua_stats_read_unsafe_cache(86400) : null;
          $unsafe = $staleUnsafe !== null ? (int)$staleUnsafe : ua_stats_db_unsafe_count($conn);
          $out['unsafe_source'] = $staleUnsafe !== null ? 'stale_unsafe_speed_cache_on_error' : 'database_state_after_mikrotik_error';
          if ($out['reason'] === '') $out['reason'] = $e->getMessage();
        }
      } elseif ($out['unsafe_source'] === 'none') {
        $unsafe = ua_stats_db_unsafe_count($conn);
        $out['unsafe_source'] = 'database_state_function_fallback';
      }
      $out['unsafe_speed'] = (int)$unsafe;

      if (function_exists('setting_get')) {
        try { $out['open_all_mode'] = ((string)setting_get($conn, 'ppp_open_all_mode', '0') === '1') ? 1 : 0; } catch (Throwable $e) {}
      }

      if ($out['online'] > $out['total'] && $out['total'] > 0) $out['online'] = $out['total'];
      $out['ppp_active'] = $out['online'];
      $out['offline'] = max(0, (int)$out['total'] - (int)$out['online']);
      ua_stats_write_footer_cache($out);
    } catch (Throwable $e) {
      $out['ok'] = false;
      $out['reason'] = $e->getMessage();
    }

    return $out;
  }
}
