Skip to content

A modern and elegant dashboard for network traffic visualization and analysis.

48.2 KB
Raw Blame
import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';
import { randomBytes } from 'crypto';
import { normalizeGeoIP, type Connection, type DomainStats, type IPStats, type HourlyStats, type ProxyStats, type RuleStats, type ProxyTrafficStats, type DeviceStats } from '@neko-master/shared';
import { getAllSchemaStatements } from '../../database/schema.js';
import { cleanupMisattributedRuleNames } from '../../database/rule-name-cleanup.js';
import {
  AuthRepository,
  SurgeRepository,
  TimeseriesRepository,
  CountryRepository,
  DeviceRepository,
  ProxyRepository,
  RuleRepository,
  IPRepository,
  ConfigRepository,
  type GeoLookupConfig,
  type GeoLookupProvider,
  TrafficWriterRepository,
  DomainRepository,
  BackendRepository,
  HealthRepository,
} from '../../database/repositories/index.js';

export interface TrafficUpdate {
  domain: string;
  ip: string;
  chain: string;
  chains: string[];
  rule: string;
  rulePayload: string;
  upload: number;
  download: number;
  connections?: number;
  sourceIP?: string;
  timestampMs?: number;
}

export interface BackendConfig {
  id: number;
  name: string;
  url: string;
  token: string;
  type: 'clash' | 'surge';
  enabled: boolean;
  is_active: boolean;
  listening: boolean;
  created_at: string;
  updated_at: string;
}

export interface AgentHeartbeat {
  backendId: number;
  agentId: string;
  hostname?: string;
  version?: string;
  gatewayType?: string;
  gatewayUrl?: string;
  remoteIP?: string;
  gatewayLatencyMs?: number | null;
  serverLatencyMs?: number | null;
  lastSeen: string;
}

function normalizeIPStatsGeoIP(items: IPStats[]): IPStats[] {
  return items.map((item) => {
    const normalizedGeoIP = normalizeGeoIP(item.geoIP as unknown);
    return {
      ...item,
      geoIP: normalizedGeoIP ?? undefined,
    };
  });
}

function normalizePaginatedIPStatsGeoIP(result: { data: IPStats[]; total: number }): { data: IPStats[]; total: number } {
  return {
    ...result,
    data: normalizeIPStatsGeoIP(result.data),
  };
}

export class StatsDatabase {
  private db: Database.Database;
  private dbPath: string;

  // Cached prepared statements for getSummary() — avoids re-compilation per call
  private _summaryStmts: ReturnType<StatsDatabase['prepareSummaryStmts']> | null = null;
  private rangeQueryCache = new Map<string, { value: unknown; expiresAt: number }>();

  public readonly repos: {
    auth: AuthRepository;
    surge: SurgeRepository;
    timeseries: TimeseriesRepository;
    country: CountryRepository;
    device: DeviceRepository;
    proxy: ProxyRepository;
    rule: RuleRepository;
    ip: IPRepository;
    config: ConfigRepository;
    trafficWriter: TrafficWriterRepository;
    domain: DomainRepository;
    backend: BackendRepository;
    health: HealthRepository;
  };

  constructor(dbPath = 'stats.db') {
    this.dbPath = path.resolve(dbPath);
    this.db = new Database(this.dbPath);
    this.init();

    this.repos = {
      auth: new AuthRepository(this.db),
      surge: new SurgeRepository(this.db),
      timeseries: new TimeseriesRepository(this.db),
      country: new CountryRepository(this.db),
      device: new DeviceRepository(this.db),
      proxy: new ProxyRepository(this.db),
      rule: new RuleRepository(this.db),
      ip: new IPRepository(this.db),
      config: new ConfigRepository(this.db, this.dbPath),
      trafficWriter: new TrafficWriterRepository(this.db),
      domain: new DomainRepository(this.db),
      backend: new BackendRepository(this.db),
      health: new HealthRepository(this.db),
    };
  }

  private init() {
    const sqliteCacheMb = Math.max(
      16,
      Number.parseInt(process.env.SQLITE_CACHE_MB || '64', 10) || 64,
    );
    const sqliteWalAutocheckpointPages = Math.max(
      100,
      Number.parseInt(process.env.SQLITE_WAL_AUTOCHECKPOINT_PAGES || '1000', 10) || 1000,
    );
    const sqliteBusyTimeoutMs = Math.max(
      1000,
      Number.parseInt(process.env.SQLITE_BUSY_TIMEOUT_MS || '5000', 10) || 5000,
    );

    // Enable WAL mode and performance PRAGMAs for reduced disk IO
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('synchronous = NORMAL');
    this.db.pragma(`wal_autocheckpoint = ${sqliteWalAutocheckpointPages}`);
    this.db.pragma('temp_store = MEMORY');
    this.db.pragma(`cache_size = -${sqliteCacheMb * 1024}`);
    this.db.pragma(`busy_timeout = ${sqliteBusyTimeoutMs}`);

    console.info(
      `[DB] SQLite tuning: cache=${sqliteCacheMb}MB, wal_autocheckpoint=${sqliteWalAutocheckpointPages}, busy_timeout=${sqliteBusyTimeoutMs}ms`,
    );

    // Apply all schema statements from the single source of truth
    for (const stmt of getAllSchemaStatements()) {
      this.db.exec(stmt);
    }

    // Drop legacy (total_download + total_upload) expression indexes: the
    // planner never used them (queries filter by backend_id first) and they
    // amplified every write to the four hottest tables.
    for (const idx of ['idx_domain_stats_traffic', 'idx_ip_stats_traffic', 'idx_proxy_stats_traffic', 'idx_rule_stats_traffic']) {
      this.db.exec(`DROP INDEX IF EXISTS ${idx}`);
    }

    // Migration: Add type column if not exists (for older databases)
    try {
      this.db.exec(`ALTER TABLE backend_configs ADD COLUMN type TEXT DEFAULT 'clash'`);
      console.log('[DB] Migration: Added type column to backend_configs');
    } catch {
      // Column already exists, ignore
    }

    // Migrate existing data if needed (from single-backend schema)
    this.migrateIfNeeded();

    // One-time cleanup: remap v1.3.9-era rule-type keys back to policy groups
    try {
      cleanupMisattributedRuleNames(this.db);
    } catch (error) {
      console.error('[DB] Rule name cleanup failed:', error);
    }

    // Backfill hourly tables from minute data (one-time)
    this.backfillHourlyTables();
  }

  // Migrate from old single-backend schema to multi-backend schema
  private migrateIfNeeded() {
    // Check if we need to migrate by checking if there's data without backend_id
    const needsMigration = this.db.prepare(`
      SELECT 1 FROM sqlite_master 
      WHERE type='table' AND name='domain_stats'
    `).get() as { '1': number } | undefined;

    if (!needsMigration) return;

    // Check if domain_stats table has backend_id column
    const tableInfo = this.db.prepare(`PRAGMA table_info(domain_stats)`).all() as { name: string }[];
    const hasBackendId = tableInfo.some(col => col.name === 'backend_id');

    if (!hasBackendId) {
      console.log('[DB] Migrating from single-backend to multi-backend schema...');
      this.performMigration();
    }

    // Check if backend_configs has listening column
    const backendInfo = this.db.prepare(`PRAGMA table_info(backend_configs)`).all() as { name: string }[];
    const hasListening = backendInfo.some(col => col.name === 'listening');

    if (!hasListening) {
      console.log('[DB] Adding listening column to backend_configs...');
      this.db.exec(`ALTER TABLE backend_configs ADD COLUMN listening BOOLEAN DEFAULT 1;`);
    }

    // Check if backend_configs has type column
    const hasType = backendInfo.some(col => col.name === 'type');

    if (!hasType) {
      console.log('[DB] Adding type column to backend_configs...');
      this.db.exec(`ALTER TABLE backend_configs ADD COLUMN type TEXT DEFAULT 'clash';`);
    }

    // Check if ip_stats has chains column (added for proxy display per IP)
    const ipStatsInfo = this.db.prepare(`PRAGMA table_info(ip_stats)`).all() as { name: string }[];
    const hasIPChains = ipStatsInfo.some(col => col.name === 'chains');

    if (!hasIPChains) {
      console.log('[DB] Adding chains column to ip_stats...');
      this.db.exec(`ALTER TABLE ip_stats ADD COLUMN chains TEXT;`);
    }

    // Check if ip_stats has rules column (added for per-rule IP traffic)
    const ipStatsInfo2 = this.db.prepare(`PRAGMA table_info(ip_stats)`).all() as { name: string }[];
    const hasIPRules = ipStatsInfo2.some(col => col.name === 'rules');

    if (!hasIPRules) {
      console.log('[DB] Adding rules column to ip_stats...');
      this.db.exec(`ALTER TABLE ip_stats ADD COLUMN rules TEXT;`);
    }

    // Check if domain_stats has rules column
    const domainStatsInfo = this.db.prepare(`PRAGMA table_info(domain_stats)`).all() as { name: string }[];
    const hasDomainRules = domainStatsInfo.some(col => col.name === 'rules');

    if (!hasDomainRules) {
      console.log('[DB] Adding rules column to domain_stats...');
      this.db.exec(`ALTER TABLE domain_stats ADD COLUMN rules TEXT;`);
    }

    // Check if domain_stats has chains column
    const hasDomainChains = domainStatsInfo.some(col => col.name === 'chains');

    if (!hasDomainChains) {
      console.log('[DB] Adding chains column to domain_stats...');
      this.db.exec(`ALTER TABLE domain_stats ADD COLUMN chains TEXT;`);
    }

    // Migration: Add gateway_latency_ms column to agent_heartbeats
    try {
      this.db.exec(`ALTER TABLE agent_heartbeats ADD COLUMN gateway_latency_ms INTEGER`);
      console.log('[DB] Migration: Added gateway_latency_ms column to agent_heartbeats');
    } catch {
      // Column already exists
    }

    // Migration: Add server_latency_ms column to agent_heartbeats
    try {
      this.db.exec(`ALTER TABLE agent_heartbeats ADD COLUMN server_latency_ms INTEGER`);
      console.log('[DB] Migration: Added server_latency_ms column to agent_heartbeats');
    } catch {
      // Column already exists
    }

    // Migration: Add server_latency_ms column to backend_health_logs
    try {
      this.db.exec(`ALTER TABLE backend_health_logs ADD COLUMN server_latency_ms INTEGER`);
      console.log('[DB] Migration: Added server_latency_ms column to backend_health_logs');
    } catch {
      // Column already exists
    }

    // Migrate connection_logs data to new aggregation tables (one-time backfill)
    this.migrateConnectionLogsToAggregation();
  }

  // Backfill minute_stats, domain_proxy_stats, ip_proxy_stats from connection_logs
  private migrateConnectionLogsToAggregation() {
    // Check if minute_stats is empty and connection_logs has data
    const minuteCount = (this.db.prepare(`SELECT COUNT(*) as c FROM minute_stats`).get() as { c: number }).c;
    const logCount = (this.db.prepare(`SELECT COUNT(*) as c FROM connection_logs`).get() as { c: number }).c;

    if (minuteCount > 0 || logCount === 0) return;

    console.log(`[DB] Migrating ${logCount} connection_logs rows to aggregation tables...`);

    try {
      this.db.exec(`BEGIN TRANSACTION`);

      // Backfill minute_stats
      this.db.exec(`
        INSERT INTO minute_stats (backend_id, minute, upload, download, connections)
        SELECT backend_id,
               strftime('%Y-%m-%dT%H:%M:00', timestamp) as minute,
               SUM(upload), SUM(download), COUNT(*)
        FROM connection_logs
        GROUP BY backend_id, strftime('%Y-%m-%dT%H:%M:00', timestamp)
      `);

      // Backfill domain_proxy_stats
      this.db.exec(`
        INSERT INTO domain_proxy_stats (backend_id, domain, chain, total_upload, total_download, total_connections, last_seen)
        SELECT backend_id, domain, chain,
               SUM(upload), SUM(download), COUNT(*), MAX(timestamp)
        FROM connection_logs
        WHERE domain IS NOT NULL AND domain != 'unknown'
        GROUP BY backend_id, domain, chain
      `);

      // Backfill ip_proxy_stats
      this.db.exec(`
        INSERT INTO ip_proxy_stats (backend_id, ip, chain, total_upload, total_download, total_connections, last_seen, domains)
        SELECT backend_id, ip, chain,
               SUM(upload), SUM(download), COUNT(*), MAX(timestamp),
               GROUP_CONCAT(DISTINCT CASE WHEN domain IS NOT NULL AND domain != 'unknown' THEN domain END)
        FROM connection_logs
        GROUP BY backend_id, ip, chain
      `);

      this.db.exec(`COMMIT`);
      console.log('[DB] Migration to aggregation tables completed successfully');
    } catch (error) {
      this.db.exec(`ROLLBACK`);
      console.error('[DB] Migration to aggregation tables failed:', error);
    }
  }

  /**
   * One-time backfill: populate hourly_dim_stats and hourly_country_stats
   * from existing minute_dim_stats and minute_country_stats data.
   */
  private backfillHourlyTables() {
    const hourlyDimCount = (this.db.prepare(`SELECT COUNT(*) as c FROM hourly_dim_stats`).get() as { c: number }).c;
    const minuteDimCount = (this.db.prepare(`SELECT COUNT(*) as c FROM minute_dim_stats`).get() as { c: number }).c;

    if (hourlyDimCount > 0 || minuteDimCount === 0) return;

    console.log(`[DB] Backfilling hourly tables from ${minuteDimCount} minute_dim_stats rows...`);

    try {
      this.db.exec(`BEGIN TRANSACTION`);

      // Backfill hourly_dim_stats from minute_dim_stats
      this.db.exec(`
        INSERT INTO hourly_dim_stats (backend_id, hour, domain, ip, source_ip, chain, rule, upload, download, connections)
        SELECT backend_id,
               SUBSTR(minute, 1, 13) || ':00:00' as hour,
               domain, ip, source_ip, chain, rule,
               SUM(upload), SUM(download), SUM(connections)
        FROM minute_dim_stats
        GROUP BY backend_id, SUBSTR(minute, 1, 13), domain, ip, source_ip, chain, rule
      `);

      // Backfill hourly_country_stats from minute_country_stats
      const minuteCountryCount = (this.db.prepare(`SELECT COUNT(*) as c FROM minute_country_stats`).get() as { c: number }).c;
      if (minuteCountryCount > 0) {
        this.db.exec(`
          INSERT INTO hourly_country_stats (backend_id, hour, country, country_name, continent, upload, download, connections)
          SELECT backend_id,
                 SUBSTR(minute, 1, 13) || ':00:00' as hour,
                 country, MAX(country_name), MAX(continent),
                 SUM(upload), SUM(download), SUM(connections)
          FROM minute_country_stats
          GROUP BY backend_id, SUBSTR(minute, 1, 13), country
        `);
      }

      this.db.exec(`COMMIT`);
      const hourlyRows = (this.db.prepare(`SELECT COUNT(*) as c FROM hourly_dim_stats`).get() as { c: number }).c;
      console.log(`[DB] Backfill complete: ${hourlyRows} hourly_dim_stats rows created`);
    } catch (error) {
      this.db.exec(`ROLLBACK`);
      console.error('[DB] Hourly backfill failed:', error);
    }
  }

  private performMigration() {
    // Create temporary tables with new schema and migrate data
    // This is a complex migration - we'll create a default backend and associate all data with it

    this.db.exec(`BEGIN TRANSACTION;`);

    try {
      // Create a default backend if none exists
      const existingBackend = this.db.prepare(`SELECT id FROM backend_configs LIMIT 1`).get() as { id: number } | undefined;
      let defaultBackendId: number;

      if (!existingBackend) {
        const result = this.db.prepare(`
          INSERT INTO backend_configs (name, url, token, enabled, is_active, listening)
          VALUES ('Default', 'http://192.168.1.1:9090', '', 1, 1, 1)
        `).run();
        defaultBackendId = Number(result.lastInsertRowid);
      } else {
        defaultBackendId = existingBackend.id;
      }

      // Migrate domain_stats - recreate table with new schema
      this.db.exec(`
        CREATE TABLE domain_stats_new (
          backend_id INTEGER NOT NULL,
          domain TEXT NOT NULL,
          ips TEXT,
          total_upload INTEGER DEFAULT 0,
          total_download INTEGER DEFAULT 0,
          total_connections INTEGER DEFAULT 0,
          last_seen DATETIME,
          rules TEXT,
          chains TEXT,
          PRIMARY KEY (backend_id, domain),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO domain_stats_new 
        SELECT ${defaultBackendId} as backend_id, domain, ips, total_upload, total_download, 
               total_connections, last_seen, rules, chains 
        FROM domain_stats;
      `);
      this.db.exec(`DROP TABLE domain_stats;`);
      this.db.exec(`ALTER TABLE domain_stats_new RENAME TO domain_stats;`);

      // Migrate ip_stats
      this.db.exec(`
        CREATE TABLE ip_stats_new (
          backend_id INTEGER NOT NULL,
          ip TEXT NOT NULL,
          domains TEXT,
          total_upload INTEGER DEFAULT 0,
          total_download INTEGER DEFAULT 0,
          total_connections INTEGER DEFAULT 0,
          last_seen DATETIME,
          asn TEXT,
          geoip TEXT,
          chains TEXT,
          rules TEXT,
          PRIMARY KEY (backend_id, ip),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO ip_stats_new 
        SELECT ${defaultBackendId} as backend_id, ip, domains, total_upload, total_download, 
               total_connections, last_seen, asn, geoip, NULL 
        FROM ip_stats;
      `);
      this.db.exec(`DROP TABLE ip_stats;`);
      this.db.exec(`ALTER TABLE ip_stats_new RENAME TO ip_stats;`);

      // Migrate proxy_stats
      this.db.exec(`
        CREATE TABLE proxy_stats_new (
          backend_id INTEGER NOT NULL,
          chain TEXT NOT NULL,
          total_upload INTEGER DEFAULT 0,
          total_download INTEGER DEFAULT 0,
          total_connections INTEGER DEFAULT 0,
          last_seen DATETIME,
          PRIMARY KEY (backend_id, chain),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO proxy_stats_new 
        SELECT ${defaultBackendId} as backend_id, chain, total_upload, total_download, 
               total_connections, last_seen 
        FROM proxy_stats;
      `);
      this.db.exec(`DROP TABLE proxy_stats;`);
      this.db.exec(`ALTER TABLE proxy_stats_new RENAME TO proxy_stats;`);

      // Migrate rule_stats
      this.db.exec(`
        CREATE TABLE rule_stats_new (
          backend_id INTEGER NOT NULL,
          rule TEXT NOT NULL,
          final_proxy TEXT,
          total_upload INTEGER DEFAULT 0,
          total_download INTEGER DEFAULT 0,
          total_connections INTEGER DEFAULT 0,
          last_seen DATETIME,
          PRIMARY KEY (backend_id, rule),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO rule_stats_new 
        SELECT ${defaultBackendId} as backend_id, rule, final_proxy, total_upload, total_download, 
               total_connections, last_seen 
        FROM rule_stats;
      `);
      this.db.exec(`DROP TABLE rule_stats;`);
      this.db.exec(`ALTER TABLE rule_stats_new RENAME TO rule_stats;`);

      // Migrate rule_proxy_map
      this.db.exec(`
        CREATE TABLE rule_proxy_map_new (
          backend_id INTEGER NOT NULL,
          rule TEXT,
          proxy TEXT,
          PRIMARY KEY (backend_id, rule, proxy),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO rule_proxy_map_new 
        SELECT ${defaultBackendId} as backend_id, rule, proxy 
        FROM rule_proxy_map;
      `);
      this.db.exec(`DROP TABLE rule_proxy_map;`);
      this.db.exec(`ALTER TABLE rule_proxy_map_new RENAME TO rule_proxy_map;`);

      // Migrate country_stats
      this.db.exec(`
        CREATE TABLE country_stats_new (
          backend_id INTEGER NOT NULL,
          country TEXT NOT NULL,
          country_name TEXT,
          continent TEXT,
          total_upload INTEGER DEFAULT 0,
          total_download INTEGER DEFAULT 0,
          total_connections INTEGER DEFAULT 0,
          last_seen DATETIME,
          PRIMARY KEY (backend_id, country),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO country_stats_new 
        SELECT ${defaultBackendId} as backend_id, country, country_name, continent, total_upload, 
               total_download, total_connections, last_seen 
        FROM country_stats;
      `);
      this.db.exec(`DROP TABLE country_stats;`);
      this.db.exec(`ALTER TABLE country_stats_new RENAME TO country_stats;`);

      // Migrate hourly_stats
      this.db.exec(`
        CREATE TABLE hourly_stats_new (
          backend_id INTEGER NOT NULL,
          hour TEXT NOT NULL,
          upload INTEGER DEFAULT 0,
          download INTEGER DEFAULT 0,
          connections INTEGER DEFAULT 0,
          PRIMARY KEY (backend_id, hour),
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO hourly_stats_new 
        SELECT ${defaultBackendId} as backend_id, hour, upload, download, connections 
        FROM hourly_stats;
      `);
      this.db.exec(`DROP TABLE hourly_stats;`);
      this.db.exec(`ALTER TABLE hourly_stats_new RENAME TO hourly_stats;`);

      // Migrate connection_logs
      this.db.exec(`
        CREATE TABLE connection_logs_new (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          backend_id INTEGER NOT NULL,
          domain TEXT,
          ip TEXT,
          chain TEXT,
          upload INTEGER DEFAULT 0,
          download INTEGER DEFAULT 0,
          timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (backend_id) REFERENCES backend_configs(id) ON DELETE CASCADE
        );
      `);
      this.db.exec(`
        INSERT INTO connection_logs_new (id, backend_id, domain, ip, chain, upload, download, timestamp)
        SELECT id, ${defaultBackendId} as backend_id, domain, ip, chain, upload, download, timestamp 
        FROM connection_logs;
      `);
      this.db.exec(`DROP TABLE connection_logs;`);
      this.db.exec(`ALTER TABLE connection_logs_new RENAME TO connection_logs;`);

      this.db.exec(`COMMIT;`);
      console.log('[DB] Migration completed successfully');
    } catch (error) {
      this.db.exec(`ROLLBACK;`);
      console.error('[DB] Migration failed:', error);
      throw error;
    }
  }

  // ==================== Traffic Writer ====================
  updateTrafficStats(backendId: number, update: TrafficUpdate) { this.repos.trafficWriter.updateTrafficStats(backendId, update); }
  batchUpdateTrafficStats(backendId: number, updates: TrafficUpdate[], reduceWrites = false) { this.repos.trafficWriter.batchUpdateTrafficStats(backendId, updates, reduceWrites); }

  // ==================== Domain ====================
  getDomainByName(backendId: number, domain: string) { return this.repos.domain.getDomainByName(backendId, domain); }
  getDomainStats(backendId: number, limit = 100, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'domainStats',
      [backendId, limit, start || '', end || ''],
      start,
      end,
      () => this.repos.domain.getDomainStats(backendId, limit, start, end),
    );
  }
  getTopDomains(backendId: number, limit = 10, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'topDomains',
      [backendId, limit, start || '', end || ''],
      start,
      end,
      () => this.repos.domain.getTopDomains(backendId, limit, start, end),
    );
  }
  getTopDomainsLight(backendId: number, limit = 10, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'topDomainsLight',
      [backendId, limit, start || '', end || ''],
      start,
      end,
      () => this.repos.domain.getTopDomainsLight(backendId, limit, start, end),
    );
  }
  getDomainStatsPaginated(
    backendId: number,
    opts: { offset?: number; limit?: number; sortBy?: string; sortOrder?: string; search?: string; start?: string; end?: string } = {},
  ) {
    return this.withRangeQueryCache(
      'domainStatsPaginated',
      [backendId, opts.offset || 0, opts.limit || 50, opts.sortBy || '', opts.sortOrder || '', opts.search || '', opts.start || '', opts.end || ''],
      opts.start,
      opts.end,
      () => this.repos.domain.getDomainStatsPaginated(backendId, opts),
    );
  }
  getDomainIPDetails(backendId: number, domain: string, start?: string, end?: string, limit?: number, sourceIP?: string, sourceChain?: string) {
    return normalizeIPStatsGeoIP(
      this.repos.ip.getDomainIPDetails(backendId, domain, start, end, limit, sourceIP, sourceChain),
    );
  }
  getDomainProxyStats(backendId: number, domain: string, start?: string, end?: string, sourceIP?: string, sourceChain?: string) { return this.repos.domain.getDomainProxyStats(backendId, domain, start, end, sourceIP, sourceChain); }

  // ==================== IP ====================
  getIPStats(backendId: number, limit = 100, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'ipStats',
      [backendId, limit, start || '', end || ''],
      start,
      end,
      () => normalizeIPStatsGeoIP(this.repos.ip.getIPStats(backendId, limit, start, end)),
    );
  }
  getIPStatsByIPs(backendId: number, ips: string[]) {
    return normalizeIPStatsGeoIP(this.repos.ip.getIPStatsByIPs(backendId, ips));
  }
  getIPStatsPaginated(
    backendId: number,
    opts: { offset?: number; limit?: number; sortBy?: string; sortOrder?: string; search?: string; start?: string; end?: string } = {},
  ) {
    return this.withRangeQueryCache(
      'ipStatsPaginated',
      [backendId, opts.offset || 0, opts.limit || 50, opts.sortBy || '', opts.sortOrder || '', opts.search || '', opts.start || '', opts.end || ''],
      opts.start,
      opts.end,
      () => normalizePaginatedIPStatsGeoIP(this.repos.ip.getIPStatsPaginated(backendId, opts)),
    );
  }
  getIPDomainDetails(backendId: number, ip: string, start?: string, end?: string, limit?: number, sourceIP?: string, sourceChain?: string) { return this.repos.ip.getIPDomainDetails(backendId, ip, start, end, limit, sourceIP, sourceChain); }
  getIPProxyStats(backendId: number, ip: string, start?: string, end?: string, sourceIP?: string, sourceChain?: string) { return this.repos.ip.getIPProxyStats(backendId, ip, start, end, sourceIP, sourceChain); }
  getTopIPs(backendId: number, limit = 10, start?: string, end?: string) { return this.getIPStats(backendId, limit, start, end); }
  getTopIPsLight(backendId: number, limit = 10, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'topIPsLight',
      [backendId, limit, start || '', end || ''],
      start,
      end,
      () => normalizeIPStatsGeoIP(this.repos.ip.getTopIPsLight(backendId, limit, start, end)),
    );
  }
  updateASNInfo(ip: string, asn: string, org: string) { this.repos.ip.updateASNInfo(ip, asn, org); }
  getASNInfo(ips: string[]) { return this.repos.ip.getASNInfo(ips); }
  getASNInfoForIP(ip: string) { return this.repos.ip.getASNInfoForIP(ip); }
  getIPGeolocation(ip: string): any {
    return this.repos.ip.getIPGeolocation(ip);
  }

  getIPGeolocations(ips: string[]): Record<string, any> {
    return this.repos.ip.getIPGeolocations(ips);
  }
  saveIPGeolocation(ip: string, geo: { country: string; country_name: string; city: string; asn: string; as_name: string; as_domain: string; continent: string; continent_name: string }) { this.repos.ip.saveIPGeolocation(ip, geo); }

  // ==================== Timeseries ====================
  getHourlyStats(backendId: number, hours = 24, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'hourlyStats',
      [backendId, hours, start || '', end || ''],
      start,
      end,
      () => this.repos.timeseries.getHourlyStats(backendId, hours, start, end),
    );
  }
  getTodayTraffic(backendId: number) { return this.repos.timeseries.getTodayTraffic(backendId); }
  getTrafficInRange(backendId: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'trafficInRange',
      [backendId, start || '', end || ''],
      start,
      end,
      () => this.repos.timeseries.getTrafficInRange(backendId, start, end),
    );
  }
  getTrafficTrend(backendId: number, minutes?: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'trafficTrend',
      [backendId, minutes || 30, start || '', end || ''],
      start,
      end,
      () => this.repos.timeseries.getTrafficTrend(backendId, minutes, start, end),
    );
  }
  getTrafficTrendAggregated(backendId: number, minutes?: number, bucketMinutes?: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'trafficTrendAggregated',
      [backendId, minutes || 30, bucketMinutes || 1, start || '', end || ''],
      start,
      end,
      () => this.repos.timeseries.getTrafficTrendAggregated(backendId, minutes, bucketMinutes, start, end),
    );
  }

  // ==================== Country ====================
  getCountryStats(backendId: number, limit?: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'countryStats',
      [backendId, limit || 50, start || '', end || ''],
      start,
      end,
      () => this.repos.country.getCountryStats(backendId, limit, start, end),
    );
  }
  updateCountryStats(backendId: number, country: string, countryName: string, continent: string, upload: number, download: number, timestampMs?: number, connections?: number) { this.repos.country.updateCountryStats(backendId, country, countryName, continent, upload, download, timestampMs, connections); }
  batchUpdateCountryStats(backendId: number, results: Array<{ country: string; countryName: string; continent: string; upload: number; download: number; connections?: number; timestampMs?: number }>) { this.repos.country.batchUpdateCountryStats(backendId, results); }

  // ==================== Device ====================
  getDevices(backendId: number, limit?: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'deviceStats',
      [backendId, limit || 50, start || '', end || ''],
      start,
      end,
      () => this.repos.device.getDevices(backendId, limit, start, end),
    );
  }
  getDeviceDomains(backendId: number, sourceIP: string, limit?: number, start?: string, end?: string) { return this.repos.device.getDeviceDomains(backendId, sourceIP, limit, start, end); }
  getDeviceIPs(backendId: number, sourceIP: string, limit?: number, start?: string, end?: string) {
    return normalizeIPStatsGeoIP(this.repos.device.getDeviceIPs(backendId, sourceIP, limit, start, end));
  }

  // ==================== Proxy ====================
  getProxyStats(backendId: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'proxyStats',
      [backendId, start || '', end || ''],
      start,
      end,
      () => this.repos.proxy.getProxyStats(backendId, start, end),
    );
  }
  getProxyDomains(backendId: number, chain: string, limit?: number, start?: string, end?: string) { return this.repos.proxy.getProxyDomains(backendId, chain, limit, start, end); }
  getProxyIPs(backendId: number, chain: string, limit?: number, start?: string, end?: string) {
    return normalizeIPStatsGeoIP(this.repos.proxy.getProxyIPs(backendId, chain, limit, start, end));
  }

  // ==================== Rule ====================
  getRuleStats(backendId: number, start?: string, end?: string) {
    return this.withRangeQueryCache(
      'ruleStats',
      [backendId, start || '', end || ''],
      start,
      end,
      () => this.repos.rule.getRuleStats(backendId, start, end),
    );
  }
  getRuleProxyMap(backendId: number) { return this.repos.rule.getRuleProxyMap(backendId); }
  getRuleDomains(backendId: number, rule: string, limit?: number, start?: string, end?: string) { return this.repos.rule.getRuleDomains(backendId, rule, limit, start, end); }
  getRuleIPs(backendId: number, rule: string, limit?: number, start?: string, end?: string) {
    return normalizeIPStatsGeoIP(this.repos.rule.getRuleIPs(backendId, rule, limit, start, end));
  }
  getRuleDomainProxyStats(backendId: number, rule: string, domain: string, start?: string, end?: string) { return this.repos.rule.getRuleDomainProxyStats(backendId, rule, domain, start, end); }
  getRuleDomainIPDetails(backendId: number, rule: string, domain: string, start?: string, end?: string, limit?: number) {
    return normalizeIPStatsGeoIP(this.repos.rule.getRuleDomainIPDetails(backendId, rule, domain, start, end, limit));
  }
  getRuleIPProxyStats(backendId: number, rule: string, ip: string, start?: string, end?: string) { return this.repos.rule.getRuleIPProxyStats(backendId, rule, ip, start, end); }
  getRuleIPDomainDetails(backendId: number, rule: string, ip: string, start?: string, end?: string, limit?: number) { return this.repos.rule.getRuleIPDomainDetails(backendId, rule, ip, start, end, limit); }
  getRuleChainFlow(backendId: number, rule: string, start?: string, end?: string, realtimeRows?: Array<{ rule: string; chain: string; totalUpload: number; totalDownload: number; totalConnections: number }>, proxyConfig?: Record<string, { now?: string }>) { return this.repos.rule.getRuleChainFlow(backendId, rule, start, end, realtimeRows, proxyConfig); }
  getAllRuleChainFlows(backendId: number, start?: string, end?: string, realtimeRows?: Array<{ rule: string; chain: string; totalUpload: number; totalDownload: number; totalConnections: number }>, proxyConfig?: Record<string, { now?: string }>) { return this.repos.rule.getAllRuleChainFlows(backendId, start, end, realtimeRows, proxyConfig); }

  // ==================== Private helpers (kept for getSummary) ====================
  private toMinuteKey(date: Date): string {
    return `${date.toISOString().slice(0, 16)}:00`;
  }

  private parseMinuteRange(
    start?: string,
    end?: string,
  ): { startMinute: string; endMinute: string } | null {
    if (!start || !end) return null;
    const startDate = new Date(start);
    const endDate = new Date(end);
    if (Number.isNaN(startDate.getTime()) || Number.isNaN(endDate.getTime()) || startDate > endDate) return null;
    return { startMinute: this.toMinuteKey(startDate), endMinute: this.toMinuteKey(endDate) };
  }

  private toHourKey(date: Date): string {
    return `${date.toISOString().slice(0, 13)}:00:00`;
  }

  private resolveFactTableRange(
    start?: string,
    end?: string,
  ): { table: 'hourly_dim_stats' | 'minute_dim_stats'; startKey: string; endKey: string } {
    if (!start || !end) {
      return { table: 'minute_dim_stats', startKey: '', endKey: '' };
    }
    const startDate = new Date(start);
    const endDate = new Date(end);
    if (Number.isNaN(startDate.getTime()) || Number.isNaN(endDate.getTime())) {
      return { table: 'minute_dim_stats', startKey: this.toMinuteKey(startDate), endKey: this.toMinuteKey(endDate) };
    }
    const rangeMs = endDate.getTime() - startDate.getTime();
    const SIX_HOURS_MS = 6 * 60 * 60 * 1000;
    if (rangeMs > SIX_HOURS_MS) {
      return { table: 'hourly_dim_stats', startKey: this.toHourKey(startDate), endKey: this.toHourKey(endDate) };
    }
    return { table: 'minute_dim_stats', startKey: this.toMinuteKey(startDate), endKey: this.toMinuteKey(endDate) };
  }

  private getRangeCacheTTL(end?: string): number {
    const realtimeTTL = Math.max(
      1_000,
      parseInt(process.env.DB_RANGE_QUERY_CACHE_TTL_MS || '8000', 10) || 8000,
    );
    const historicalTTL = Math.max(
      realtimeTTL,
      parseInt(process.env.DB_HISTORICAL_QUERY_CACHE_TTL_MS || '300000', 10) || 300000,
    );
    if (!end) return realtimeTTL;

    const endMs = new Date(end).getTime();
    if (Number.isNaN(endMs)) return realtimeTTL;

    const toleranceMs = Math.max(
      10_000,
      parseInt(process.env.REALTIME_RANGE_END_TOLERANCE_MS || '120000', 10) || 120000,
    );
    return endMs >= Date.now() - toleranceMs ? realtimeTTL : historicalTTL;
  }

  private pruneRangeQueryCache(): void {
    const maxEntries = Math.max(
      128,
      parseInt(process.env.DB_RANGE_QUERY_CACHE_MAX_ENTRIES || '1024', 10) || 1024,
    );
    const now = Date.now();

    for (const [key, entry] of this.rangeQueryCache) {
      if (entry.expiresAt <= now) {
        this.rangeQueryCache.delete(key);
      }
    }

    while (this.rangeQueryCache.size > maxEntries) {
      const oldestKey = this.rangeQueryCache.keys().next().value as string | undefined;
      if (!oldestKey) break;
      this.rangeQueryCache.delete(oldestKey);
    }
  }

  private withRangeQueryCache<T>(
    prefix: string,
    parts: Array<string | number>,
    start: string | undefined,
    end: string | undefined,
    compute: () => T,
  ): T {
    if (process.env.DB_RANGE_QUERY_CACHE_DISABLED === '1') {
      return compute();
    }

    if (!this.parseMinuteRange(start, end)) {
      return compute();
    }

    const key = `${prefix}:${parts.join('|')}`;
    const now = Date.now();
    const cached = this.rangeQueryCache.get(key);
    if (cached && cached.expiresAt > now) {
      return cached.value as T;
    }

    const value = compute();
    const ttl = this.getRangeCacheTTL(end);
    this.rangeQueryCache.set(key, {
      value,
      expiresAt: now + ttl,
    });
    this.pruneRangeQueryCache();
    return value;
  }

  clearRangeQueryCache(backendId?: number): void {
    if (backendId === undefined) {
      this.rangeQueryCache.clear();
      return;
    }

    const backendMarker = `:${backendId}|`;
    for (const key of this.rangeQueryCache.keys()) {
      if (key.includes(backendMarker)) {
        this.rangeQueryCache.delete(key);
      }
    }
  }

  private prepareSummaryStmts() {
    return {
      rangeCombined: this.db.prepare(`
        SELECT
          COALESCE(SUM(connections), 0) as connections,
          COALESCE(SUM(upload), 0) as upload,
          COALESCE(SUM(download), 0) as download,
          COUNT(DISTINCT CASE WHEN domain != '' THEN domain END) as uniqueDomains,
          COUNT(DISTINCT CASE WHEN ip != '' THEN ip END) as uniqueIPs
        FROM minute_dim_stats
        WHERE backend_id = ? AND minute >= ? AND minute <= ?
      `),
      rangeCombinedHourly: this.db.prepare(`
        SELECT
          COALESCE(SUM(connections), 0) as connections,
          COALESCE(SUM(upload), 0) as upload,
          COALESCE(SUM(download), 0) as download,
          COUNT(DISTINCT CASE WHEN domain != '' THEN domain END) as uniqueDomains,
          COUNT(DISTINCT CASE WHEN ip != '' THEN ip END) as uniqueIPs
        FROM hourly_dim_stats
        WHERE backend_id = ? AND hour >= ? AND hour <= ?
      `),
      allTraffic: this.db.prepare(`SELECT COALESCE(SUM(total_connections), 0) as connections, COALESCE(SUM(total_upload), 0) as upload, COALESCE(SUM(total_download), 0) as download FROM ip_stats WHERE backend_id = ?`),
      allDomains: this.db.prepare(`SELECT COUNT(DISTINCT domain) as count FROM domain_stats WHERE backend_id = ?`),
      allIPs: this.db.prepare(`SELECT COUNT(DISTINCT ip) as count FROM ip_stats WHERE backend_id = ?`),
    };
  }

  private get summaryStmts() {
    if (!this._summaryStmts) {
      this._summaryStmts = this.prepareSummaryStmts();
    }
    return this._summaryStmts;
  }

  // ==================== Summary (kept in db.ts - cross-table aggregation) ====================
  getSummary(
    backendId: number,
    start?: string,
    end?: string,
  ): { totalConnections: number; totalUpload: number; totalDownload: number; uniqueDomains: number; uniqueIPs: number } {
    return this.withRangeQueryCache(
      'summary',
      [backendId, start || '', end || ''],
      start,
      end,
      () => {
        const range = this.parseMinuteRange(start, end);
        const s = this.summaryStmts;
        if (range) {
          const resolved = this.resolveFactTableRange(start, end);
          const stmt = resolved.table === 'hourly_dim_stats' ? s.rangeCombinedHourly : s.rangeCombined;
          const { connections, upload, download, uniqueDomains, uniqueIPs } = stmt.get(
            backendId, resolved.startKey, resolved.endKey,
          ) as { connections: number; upload: number; download: number; uniqueDomains: number; uniqueIPs: number };
          return { totalConnections: connections, totalUpload: upload, totalDownload: download, uniqueDomains, uniqueIPs };
        }
        const { connections, upload, download } = s.allTraffic.get(backendId) as { connections: number; upload: number; download: number };
        const uniqueDomains = (s.allDomains.get(backendId) as { count: number }).count;
        const uniqueIPs = (s.allIPs.get(backendId) as { count: number }).count;
        return { totalConnections: connections, totalUpload: upload, totalDownload: download, uniqueDomains, uniqueIPs };
      },
    );
  }

  /**
   * @deprecated connection_logs is no longer written to (replaced by aggregation tables).
   * Kept for API backward compatibility — always returns [].
   */
  getRecentConnections(backendId: number, _limit = 100): Connection[] {
    return [];
  }

  // ==================== Delegated to repositories ====================

  // Config
  cleanupOldData(backendId: number | null, days: number) { return this.repos.config.cleanupOldData(backendId, days); }
  cleanupASNCache(days: number) { return this.repos.config.cleanupASNCache(days); }
  getDatabaseSize() { return this.repos.config.getDatabaseSize(); }
  getConnectionLogsCount(backendId: number) { return this.repos.config.getConnectionLogsCount(backendId); }
  getTotalConnectionLogsCount() { return this.repos.config.getTotalConnectionLogsCount(); }
  getRetentionConfig() { return this.repos.config.getRetentionConfig(); }
  updateRetentionConfig(updates: { connectionLogsDays?: number; hourlyStatsDays?: number; autoCleanup?: boolean }) { return this.repos.config.updateRetentionConfig(updates); }
  getGeoLookupConfig(): GeoLookupConfig { return this.repos.config.getGeoLookupConfig(); }
  updateGeoLookupConfig(updates: { provider?: GeoLookupProvider; onlineApiUrl?: string }): GeoLookupConfig {
    return this.repos.config.updateGeoLookupConfig(updates);
  }
  vacuum() { this.repos.config.vacuum(); }
  deleteOldMinuteStats(cutoff: string) { return this.repos.config.deleteOldMinuteStats(cutoff); }
  deleteOldConnectionLogs(cutoff: string) { return this.repos.config.deleteOldConnectionLogs(cutoff); }
  deleteOldHourlyStats(cutoff: string) { return this.repos.config.deleteOldHourlyStats(cutoff); }
  getCleanupStats() { return this.repos.config.getCleanupStats(); }

  // Backend
  createBackend(backend: { name: string; url: string; token?: string; type?: 'clash' | 'surge' }) { return this.repos.backend.createBackend(backend); }
  getAllBackends() { return this.repos.backend.getAllBackends(); }
  getBackend(id: number) { return this.repos.backend.getBackend(id); }
  getActiveBackend() { return this.repos.backend.getActiveBackend(); }
  getListeningBackends() { return this.repos.backend.getListeningBackends(); }
  updateBackend(id: number, updates: Partial<Omit<BackendConfig, 'id' | 'created_at'>>) { this.repos.backend.updateBackend(id, updates); }
  setActiveBackend(id: number) { this.repos.backend.setActiveBackend(id); }
  setBackendListening(id: number, listening: boolean) { this.repos.backend.setBackendListening(id, listening); }
  deleteBackend(id: number) {
    this.repos.backend.deleteBackend(id);
    this.clearRangeQueryCache(id);
  }
  deleteBackendData(id: number) {
    this.repos.backend.deleteBackendData(id);
    this.clearRangeQueryCache(id);
  }
  getGlobalSummary() { return this.repos.backend.getGlobalSummary(); }

  upsertAgentHeartbeat(input: {
    backendId: number;
    agentId: string;
    hostname?: string;
    version?: string;
    gatewayType?: string;
    gatewayUrl?: string;
    remoteIP?: string;
    gatewayLatencyMs?: number;
    serverLatencyMs?: number;
    lastSeen?: string;
  }): void {
    const stmt = this.db.prepare(`
      INSERT INTO agent_heartbeats (
        backend_id, agent_id, hostname, version, gateway_type, gateway_url, remote_ip, gateway_latency_ms, server_latency_ms, last_seen, updated_at
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
      ON CONFLICT(backend_id) DO UPDATE SET
        agent_id = excluded.agent_id,
        hostname = COALESCE(excluded.hostname, agent_heartbeats.hostname),
        version = COALESCE(excluded.version, agent_heartbeats.version),
        gateway_type = COALESCE(excluded.gateway_type, agent_heartbeats.gateway_type),
        gateway_url = COALESCE(excluded.gateway_url, agent_heartbeats.gateway_url),
        remote_ip = COALESCE(excluded.remote_ip, agent_heartbeats.remote_ip),
        gateway_latency_ms = COALESCE(excluded.gateway_latency_ms, agent_heartbeats.gateway_latency_ms),
        server_latency_ms = COALESCE(excluded.server_latency_ms, agent_heartbeats.server_latency_ms),
        last_seen = excluded.last_seen,
        updated_at = CURRENT_TIMESTAMP
    `);

    const lastSeen = input.lastSeen || new Date().toISOString();
    stmt.run(
      input.backendId,
      input.agentId,
      input.hostname || null,
      input.version || null,
      input.gatewayType || null,
      input.gatewayUrl || null,
      input.remoteIP || null,
      input.gatewayLatencyMs ?? null,
      input.serverLatencyMs ?? null,
      lastSeen,
    );
  }

  getAgentHeartbeat(backendId: number): AgentHeartbeat | undefined {
    const stmt = this.db.prepare(`
      SELECT
        backend_id as backendId,
        agent_id as agentId,
        hostname,
        version,
        gateway_type as gatewayType,
        gateway_url as gatewayUrl,
        remote_ip as remoteIP,
        gateway_latency_ms as gatewayLatencyMs,
        server_latency_ms as serverLatencyMs,
        last_seen as lastSeen
      FROM agent_heartbeats
      WHERE backend_id = ?
      LIMIT 1
    `);
    return stmt.get(backendId) as AgentHeartbeat | undefined;
  }

  clearAgentHeartbeat(backendId: number): void {
    const stmt = this.db.prepare('DELETE FROM agent_heartbeats WHERE backend_id = ?');
    stmt.run(backendId);
  }

  // Auth
  getAuthConfig() { return this.repos.auth.getAuthConfig(); }
  updateAuthConfig(updates: { enabled?: boolean; tokenHash?: string | null }) { this.repos.auth.updateAuthConfig(updates); }
  getOrCreateCookieSecret() { return this.repos.auth.getOrCreateCookieSecret(() => randomBytes(32).toString('hex')); }

  // Surge
  getSurgePolicyCache(backendId: number) { return this.repos.surge.getSurgePolicyCache(backendId); }
  updateSurgePolicyCache(backendId: number, policies: Array<{ policyGroup: string; selectedPolicy: string | null; policyType?: string; allPolicies?: string[] }>) { this.repos.surge.updateSurgePolicyCache(backendId, policies); }
  getSurgePolicyCacheLastUpdate(backendId: number) { return this.repos.surge.getSurgePolicyCacheLastUpdate(backendId); }
  clearSurgePolicyCache(backendId: number) { this.repos.surge.clearSurgePolicyCache(backendId); }

  // ==================== Agent Config Snapshot (persist across restarts) ====================
  saveAgentSnapshot(
    backendId: number,
    config: { rules: unknown[]; proxies: Record<string, unknown>; providers: Record<string, unknown>; timestamp: number; hash: string },
    policyState?: { proxies: Record<string, unknown>; providers: Record<string, unknown>; timestamp: number },
  ): void {
    const stmt = this.db.prepare(`
      INSERT INTO agent_snapshots (backend_id, config_json, policy_state_json, updated_at)
      VALUES (?, ?, ?, CURRENT_TIMESTAMP)
      ON CONFLICT(backend_id) DO UPDATE SET
        config_json = excluded.config_json,
        policy_state_json = excluded.policy_state_json,
        updated_at = CURRENT_TIMESTAMP
    `);
    stmt.run(backendId, JSON.stringify(config), policyState ? JSON.stringify(policyState) : null);
  }

  loadAgentSnapshot(backendId: number): { config: unknown; policyState?: unknown } | undefined {
    const stmt = this.db.prepare(`
      SELECT config_json, policy_state_json
      FROM agent_snapshots
      WHERE backend_id = ?
      LIMIT 1
    `);
    const row = stmt.get(backendId) as { config_json: string; policy_state_json: string | null } | undefined;
    if (!row) return undefined;
    try {
      return {
        config: JSON.parse(row.config_json),
        policyState: row.policy_state_json ? JSON.parse(row.policy_state_json) : undefined,
      };
    } catch {
      return undefined;
    }
  }

  loadAllAgentSnapshots(): Array<{ backendId: number; config: unknown; policyState?: unknown }> {
    const stmt = this.db.prepare(`
      SELECT backend_id, config_json, policy_state_json
      FROM agent_snapshots
    `);
    const rows = stmt.all() as Array<{ backend_id: number; config_json: string; policy_state_json: string | null }>;
    const results: Array<{ backendId: number; config: unknown; policyState?: unknown }> = [];
    for (const row of rows) {
      try {
        results.push({
          backendId: row.backend_id,
          config: JSON.parse(row.config_json),
          policyState: row.policy_state_json ? JSON.parse(row.policy_state_json) : undefined,
        });
      } catch {
        // Skip invalid JSON rows
      }
    }
    return results;
  }

  deleteAgentSnapshot(backendId: number): void {
    const stmt = this.db.prepare('DELETE FROM agent_snapshots WHERE backend_id = ?');
    stmt.run(backendId);
  }

  close() {
    this.db.close();
  }
}

export type { GeoLookupConfig, GeoLookupProvider };

export default StatsDatabase;