import Database from 'better-sqlite3';
import fs from 'fs';
import path from 'path';
import { randomBytes } from 'node:crypto';

import { ASSISTANT_NAME, DATA_DIR, STORE_DIR } from './config.js';
import { isValidGroupFolder } from './group-folder.js';
import { logger } from './logger.js';
import {
  Credential,
  NewMessage,
  RegisteredGroup,
  ScheduledTask,
  SessionRecord,
  TaskRunLog,
  Tenant,
} from './types.js';
import { normalizeProvider } from './runtime-provider.js';

let db: Database.Database;

function createSchema(database: Database.Database): void {
  database.exec(`
    CREATE TABLE IF NOT EXISTS tenants (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      provider TEXT DEFAULT 'claude',
      discord_category_id TEXT,
      discord_role_id TEXT,
      discord_invite TEXT,
      anthropic_key TEXT,
      max_concurrent INTEGER DEFAULT 3,
      max_memory_mb INTEGER DEFAULT 4096,
      cpu_quota TEXT DEFAULT '200%',
      cpu_weight INTEGER DEFAULT 100,
      max_disk_mb INTEGER DEFAULT 4096,
      show_progress_messages INTEGER DEFAULT 0,
      status TEXT DEFAULT 'active',
      assistant_name TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS chats (
      jid TEXT PRIMARY KEY,
      name TEXT,
      last_message_time TEXT,
      channel TEXT,
      is_group INTEGER DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS messages (
      id TEXT,
      chat_jid TEXT,
      sender TEXT,
      sender_name TEXT,
      content TEXT,
      timestamp TEXT,
      is_from_me INTEGER,
      is_bot_message INTEGER DEFAULT 0,
      PRIMARY KEY (id, chat_jid),
      FOREIGN KEY (chat_jid) REFERENCES chats(jid)
    );
    CREATE INDEX IF NOT EXISTS idx_timestamp ON messages(timestamp);

    CREATE TABLE IF NOT EXISTS scheduled_tasks (
      id TEXT PRIMARY KEY,
      group_folder TEXT NOT NULL,
      chat_jid TEXT NOT NULL,
      prompt TEXT NOT NULL,
      schedule_type TEXT NOT NULL,
      schedule_value TEXT NOT NULL,
      next_run TEXT,
      last_run TEXT,
      last_result TEXT,
      status TEXT DEFAULT 'active',
      created_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_next_run ON scheduled_tasks(next_run);
    CREATE INDEX IF NOT EXISTS idx_status ON scheduled_tasks(status);

    CREATE TABLE IF NOT EXISTS task_run_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      task_id TEXT NOT NULL,
      run_at TEXT NOT NULL,
      duration_ms INTEGER NOT NULL,
      status TEXT NOT NULL,
      result TEXT,
      error TEXT,
      FOREIGN KEY (task_id) REFERENCES scheduled_tasks(id)
    );
    CREATE INDEX IF NOT EXISTS idx_task_run_logs ON task_run_logs(task_id, run_at);

    CREATE TABLE IF NOT EXISTS router_state (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS sessions (
      group_folder TEXT PRIMARY KEY,
      session_id TEXT NOT NULL,
      runtime TEXT DEFAULT 'claude'
    );
    CREATE TABLE IF NOT EXISTS registered_groups (
      jid TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      folder TEXT NOT NULL UNIQUE,
      trigger_pattern TEXT NOT NULL,
      added_at TEXT NOT NULL,
      container_config TEXT,
      requires_trigger INTEGER DEFAULT 1,
      is_main INTEGER DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS tenant_services (
      tenant_id TEXT NOT NULL,
      subdomain TEXT NOT NULL,
      config TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      PRIMARY KEY (tenant_id, subdomain)
    );
    CREATE TABLE IF NOT EXISTS custom_domains (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      domain TEXT NOT NULL UNIQUE,
      target TEXT NOT NULL DEFAULT 'dashboard',
      verification_token TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      created_at TEXT NOT NULL,
      verified_at TEXT
    );
  `);

  // --- Migrations for existing databases ---

  // Add context_mode column if it doesn't exist
  try {
    database.exec(
      `ALTER TABLE scheduled_tasks ADD COLUMN context_mode TEXT DEFAULT 'isolated'`,
    );
  } catch {
    /* column already exists */
  }

  // Add is_bot_message column if it doesn't exist
  try {
    database.exec(
      `ALTER TABLE messages ADD COLUMN is_bot_message INTEGER DEFAULT 0`,
    );
    database
      .prepare(`UPDATE messages SET is_bot_message = 1 WHERE content LIKE ?`)
      .run(`${ASSISTANT_NAME}:%`);
  } catch {
    /* column already exists */
  }

  // Add is_main column if it doesn't exist
  try {
    database.exec(
      `ALTER TABLE registered_groups ADD COLUMN is_main INTEGER DEFAULT 0`,
    );
    database.exec(
      `UPDATE registered_groups SET is_main = 1 WHERE folder = 'main'`,
    );
  } catch {
    /* column already exists */
  }

  // Add channel and is_group columns if they don't exist
  try {
    database.exec(`ALTER TABLE chats ADD COLUMN channel TEXT`);
    database.exec(`ALTER TABLE chats ADD COLUMN is_group INTEGER DEFAULT 0`);
    database.exec(
      `UPDATE chats SET channel = 'whatsapp', is_group = 1 WHERE jid LIKE '%@g.us'`,
    );
    database.exec(
      `UPDATE chats SET channel = 'whatsapp', is_group = 0 WHERE jid LIKE '%@s.whatsapp.net'`,
    );
    database.exec(
      `UPDATE chats SET channel = 'discord', is_group = 1 WHERE jid LIKE 'dc:%'`,
    );
    database.exec(
      `UPDATE chats SET channel = 'telegram', is_group = 1 WHERE jid LIKE 'tg:%'`,
    );
  } catch {
    /* columns already exist */
  }

  // --- Multi-tenant migrations ---

  // Create tenants table if it doesn't exist (handles pre-multi-tenant DBs)
  try {
    database.exec(`
      CREATE TABLE IF NOT EXISTS tenants (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        provider TEXT DEFAULT 'claude',
        discord_category_id TEXT,
        discord_role_id TEXT,
        discord_invite TEXT,
        anthropic_key TEXT,
        max_concurrent INTEGER DEFAULT 3,
        max_memory_mb INTEGER DEFAULT 4096,
        cpu_quota TEXT DEFAULT '200%',
        cpu_weight INTEGER DEFAULT 100,
        max_disk_mb INTEGER DEFAULT 4096,
        show_progress_messages INTEGER DEFAULT 0,
        status TEXT DEFAULT 'active',
        assistant_name TEXT,
        created_at TEXT NOT NULL
      )
    `);
  } catch {
    /* table already exists */
  }

  // Add cpu_weight to tenants if missing
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN cpu_weight INTEGER DEFAULT 100`);
  } catch {
    /* column already exists */
  }

  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN provider TEXT DEFAULT 'claude'`);
  } catch {
    /* column already exists */
  }

  // Add tenant dashboard password hash if missing
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN dashboard_password TEXT`);
  } catch {
    /* column already exists */
  }

  try {
    database.exec(
      `ALTER TABLE tenants ADD COLUMN show_progress_messages INTEGER DEFAULT 0`,
    );
  } catch {
    /* column already exists */
  }

  // Add tenant_id columns to existing tables
  const tablesNeedingTenantId = [
    'registered_groups',
    'chats',
    'messages',
    'sessions',
    'scheduled_tasks',
  ];
  for (const table of tablesNeedingTenantId) {
    try {
      database.exec(`ALTER TABLE ${table} ADD COLUMN tenant_id TEXT`);
    } catch {
      /* column already exists */
    }
  }

  // Add is_trigger to messages if missing
  try {
    database.exec(
      `ALTER TABLE messages ADD COLUMN is_trigger INTEGER DEFAULT 0`,
    );
  } catch {
    /* column already exists */
  }

  // Add email to tenants if missing
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN email TEXT`);
  } catch {
    /* column already exists */
  }

  // Add port_range_start and port_range_size for per-tenant app hosting
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN port_range_start INTEGER`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN port_range_size INTEGER DEFAULT 100`);
  } catch {
    /* column already exists */
  }

  // Add tenant_type (standard/bot) and discord_bot_token for multi-bot support
  try {
    database.exec(
      `ALTER TABLE tenants ADD COLUMN tenant_type TEXT DEFAULT 'standard'`,
    );
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN discord_bot_token TEXT`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN host_mount TEXT`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN is_admin INTEGER DEFAULT 0`);
  } catch {
    /* column already exists */
  }

  // Add model column for per-tenant model selection
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN model TEXT`);
  } catch {
    /* column already exists */
  }
  // Add Bitwarden credentials for per-tenant vault auto-unlock
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN bw_email TEXT`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN bw_password TEXT`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN bw_server TEXT`);
  } catch {
    /* column already exists */
  }
  // Add Bitwarden credentials for per-tenant vault auto-unlock
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN bw_email TEXT`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN bw_password TEXT`);
  } catch {
    /* column already exists */
  }
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN bw_server TEXT`);
  } catch {
    /* column already exists */
  }

  try {
    database.exec(`ALTER TABLE sessions ADD COLUMN runtime TEXT DEFAULT 'claude'`);
  } catch {
    /* column already exists */
  }

  // Create index on registered_groups.tenant_id if not exists
  try {
    database.exec(
      `CREATE INDEX IF NOT EXISTS idx_registered_groups_tenant ON registered_groups(tenant_id)`,
    );
  } catch {
    /* index already exists */
  }

  // Add input_tokens to usage_log if table was created before the column existed
  try {
    database.exec(
      `ALTER TABLE usage_log ADD COLUMN input_tokens INTEGER DEFAULT 0`,
    );
  } catch {}

  // Add group_folder to usage_log if table was created before the column existed
  try {
    database.exec(
      `ALTER TABLE usage_log ADD COLUMN group_folder TEXT`,
    );
  } catch {
    /* column already exists */
  }

  // Usage tracking tables
  database.exec(`
    CREATE TABLE IF NOT EXISTS usage_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tenant_id TEXT NOT NULL,
      timestamp TEXT NOT NULL,
      model TEXT,
      input_tokens INTEGER DEFAULT 0,
      output_tokens INTEGER DEFAULT 0,
      cache_creation_tokens INTEGER DEFAULT 0,
      cache_read_tokens INTEGER DEFAULT 0,
      request_path TEXT,
      duration_ms INTEGER DEFAULT 0,
      group_folder TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_usage_tenant_ts ON usage_log (tenant_id, timestamp);

    CREATE TABLE IF NOT EXISTS usage_limits (
      tenant_id TEXT PRIMARY KEY,
      daily_requests INTEGER
    );
  `);

  // Add daily_duration_ms to usage_limits if not present
  try {
    database.exec(
      `ALTER TABLE usage_limits ADD COLUMN daily_duration_ms INTEGER`,
    );
  } catch {}

  // Credential pool table
  database.exec(`
    CREATE TABLE IF NOT EXISTS credentials (
      id TEXT PRIMARY KEY,
      provider TEXT NOT NULL DEFAULT 'claude',
      type TEXT NOT NULL CHECK(type IN ('oauth', 'apikey')),
      label TEXT,
      status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'paused', 'expired', 'error')),
      access_token TEXT,
      refresh_token TEXT,
      expires_at INTEGER,
      api_key TEXT,
      auth_json TEXT,
      plan_type TEXT,
      rate_limit_tier TEXT,
      last_used_at TEXT,
      total_requests INTEGER DEFAULT 0,
      error_message TEXT,
      created_at TEXT NOT NULL
    )
  `);

  try {
    database.exec(`ALTER TABLE credentials ADD COLUMN provider TEXT NOT NULL DEFAULT 'claude'`);
  } catch {
    /* column already exists */
  }

  try {
    database.exec(`ALTER TABLE credentials ADD COLUMN auth_json TEXT`);
  } catch {
    /* column already exists */
  }

  try {
    database.exec(`ALTER TABLE credentials ADD COLUMN plan_type TEXT`);
  } catch {
    /* column already exists */
  }

  // Optional: pin a tenant to a specific credential
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN credential_id TEXT`);
  } catch { /* column already exists */ }

  // Discord user ID for the tenant owner
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN discord_user_id TEXT`);
  } catch { /* column already exists */ }

  // Pairing code for linking Discord users to tenants on first join
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN discord_pairing_code TEXT`);
  } catch { /* column already exists */ }
  // Rename old column if it exists (migration from pairing_code -> discord_pairing_code)
  try {
    database.exec(`ALTER TABLE tenants RENAME COLUMN pairing_code TO discord_pairing_code`);
  } catch { /* column doesn't exist or already renamed */ }

  // Add agent_status column to chats
  try {
    database.exec(`ALTER TABLE chats ADD COLUMN agent_status TEXT DEFAULT 'IDLE'`);
  } catch { /* column already exists */ }

  // Add hooks_token + hooks_enabled columns to registered_groups
  try {
    database.exec(`ALTER TABLE registered_groups ADD COLUMN hooks_token TEXT`);
  } catch { /* column already exists */ }
  try {
    database.exec(`ALTER TABLE registered_groups ADD COLUMN hooks_enabled INTEGER DEFAULT 0`);
  } catch { /* column already exists */ }

  // Add pi_backend column to tenants
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN pi_backend TEXT`);
  } catch { /* column already exists */ }

  // Add proxy_enabled flag for tenants with tailscale proxy access
  try {
    database.exec(`ALTER TABLE tenants ADD COLUMN proxy_enabled INTEGER DEFAULT 0`);
  } catch { /* column already exists */ }

  // Migrate existing data to default tenant if tenant_id is null
  migrateToDefaultTenant(database);
}

/**
 * Migrate pre-multi-tenant data: assign unowned groups to a 'default' tenant
 * and prefix their folder paths with the tenant ID.
 */
function migrateToDefaultTenant(database: Database.Database): void {
  const DEFAULT_TENANT = 'default';

  // Check if there are any groups without a tenant_id
  const orphanCount = database
    .prepare(
      'SELECT COUNT(*) as cnt FROM registered_groups WHERE tenant_id IS NULL',
    )
    .get() as { cnt: number };

  if (orphanCount.cnt === 0) return;

  logger.info(
    { orphanCount: orphanCount.cnt },
    'Migrating pre-multi-tenant data to default tenant',
  );

  // Create default tenant if it doesn't exist
  database
    .prepare(
      `INSERT OR IGNORE INTO tenants (id, name, provider, max_concurrent, max_memory_mb, cpu_quota, cpu_weight, max_disk_mb, status, created_at)
       VALUES (?, ?, 'claude', 5, 4096, '200%', 100, 4096, 'active', ?)`,
    )
    .run(DEFAULT_TENANT, 'Default Tenant', new Date().toISOString());

  // Update registered_groups: set tenant_id and prefix folder
  const orphanGroups = database
    .prepare(
      'SELECT jid, folder FROM registered_groups WHERE tenant_id IS NULL',
    )
    .all() as Array<{ jid: string; folder: string }>;

  const updateStmt = database.prepare(
    'UPDATE registered_groups SET tenant_id = ?, folder = ? WHERE jid = ?',
  );

  for (const g of orphanGroups) {
    // Only prefix if not already prefixed
    const newFolder = g.folder.includes('/')
      ? g.folder
      : `${DEFAULT_TENANT}/${g.folder}`;
    updateStmt.run(DEFAULT_TENANT, newFolder, g.jid);
  }

  // Update other tables
  database.exec(
    `UPDATE chats SET tenant_id = '${DEFAULT_TENANT}' WHERE tenant_id IS NULL`,
  );
  database.exec(
    `UPDATE messages SET tenant_id = '${DEFAULT_TENANT}' WHERE tenant_id IS NULL`,
  );
  database.exec(
    `UPDATE scheduled_tasks SET tenant_id = '${DEFAULT_TENANT}' WHERE tenant_id IS NULL`,
  );

  // Update sessions: prefix group_folder with tenant
  const orphanSessions = database
    .prepare(
      'SELECT group_folder, session_id FROM sessions WHERE tenant_id IS NULL',
    )
    .all() as Array<{ group_folder: string; session_id: string }>;

  for (const s of orphanSessions) {
    const newFolder = s.group_folder.includes('/')
      ? s.group_folder
      : `${DEFAULT_TENANT}/${s.group_folder}`;
    // Delete old row and insert new (since group_folder is PK)
    database
      .prepare('DELETE FROM sessions WHERE group_folder = ?')
      .run(s.group_folder);
    database
      .prepare(
        'INSERT OR REPLACE INTO sessions (group_folder, session_id, tenant_id, runtime) VALUES (?, ?, ?, ?)',
      )
      .run(newFolder, s.session_id, DEFAULT_TENANT, 'claude');
  }

  logger.info(
    { tenant: DEFAULT_TENANT, migratedGroups: orphanGroups.length },
    'Migration to default tenant complete',
  );
}

export function initDatabase(): void {
  const dbPath = path.join(STORE_DIR, 'messages.db');
  fs.mkdirSync(path.dirname(dbPath), { recursive: true });

  db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.pragma('busy_timeout = 5000');
  createSchema(db);

  // Migrate from JSON files if they exist
  migrateJsonState();
}

export function getDb(): Database.Database {
  return db;
}

/** @internal - for tests only. Creates a fresh in-memory database. */
export function _initTestDatabase(): void {
  db = new Database(':memory:');
  createSchema(db);
}

// --- Tenant accessors ---

export function createTenant(tenant: Tenant): void {
  db.prepare(
    `INSERT INTO tenants (id, name, provider, discord_category_id, discord_role_id, discord_invite, anthropic_key, max_concurrent, max_memory_mb, cpu_quota, cpu_weight, max_disk_mb, show_progress_messages, status, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  ).run(
    tenant.id,
    tenant.name,
    normalizeProvider(tenant.provider),
    tenant.discord_category_id ?? null,
    tenant.discord_role_id ?? null,
    tenant.discord_invite ?? null,
    tenant.anthropic_key ?? null,
    tenant.max_concurrent,
    tenant.max_memory_mb,
    tenant.cpu_quota,
    tenant.cpu_weight ?? 100,
    tenant.max_disk_mb,
    tenant.show_progress_messages ? 1 : 0,
    tenant.status,
    tenant.created_at,
  );
}

export function getTenant(id: string): Tenant | undefined {
  return db.prepare('SELECT * FROM tenants WHERE id = ?').get(id) as
    | Tenant
    | undefined;
}

export function getAllTenants(): Tenant[] {
  return db
    .prepare('SELECT * FROM tenants ORDER BY created_at')
    .all() as Tenant[];
}

export function updateTenantRecord(
  id: string,
  updates: Partial<Omit<Tenant, 'id' | 'created_at'>>,
): void {
  const fields: string[] = [];
  const values: unknown[] = [];

  for (const [key, value] of Object.entries(updates)) {
    if (value !== undefined) {
      fields.push(`${key} = ?`);
      values.push(value);
    }
  }

  if (fields.length === 0) return;
  values.push(id);
  db.prepare(`UPDATE tenants SET ${fields.join(', ')} WHERE id = ?`).run(
    ...values,
  );
}

export function deleteRegisteredGroup(jid: string): void {
  db.prepare('DELETE FROM registered_groups WHERE jid = ?').run(jid);
}

export function deleteTenant(id: string): void {
  // Delete tenant's groups, sessions, tasks first
  db.prepare('DELETE FROM registered_groups WHERE tenant_id = ?').run(id);
  db.prepare('DELETE FROM sessions WHERE tenant_id = ?').run(id);
  db.prepare('DELETE FROM scheduled_tasks WHERE tenant_id = ?').run(id);
  db.prepare('DELETE FROM chats WHERE tenant_id = ?').run(id);
  db.prepare('DELETE FROM messages WHERE tenant_id = ?').run(id);
  db.prepare('DELETE FROM tenants WHERE id = ?').run(id);
}

// --- Chat metadata ---

export function storeChatMetadata(
  chatJid: string,
  timestamp: string,
  name?: string,
  channel?: string,
  isGroup?: boolean,
  tenantId?: string,
): void {
  const ch = channel ?? null;
  const group = isGroup === undefined ? null : isGroup ? 1 : 0;
  const tid = tenantId ?? null;

  if (name) {
    db.prepare(
      `
      INSERT INTO chats (jid, name, last_message_time, channel, is_group, tenant_id) VALUES (?, ?, ?, ?, ?, ?)
      ON CONFLICT(jid) DO UPDATE SET
        name = excluded.name,
        last_message_time = MAX(last_message_time, excluded.last_message_time),
        channel = COALESCE(excluded.channel, channel),
        is_group = COALESCE(excluded.is_group, is_group),
        tenant_id = COALESCE(excluded.tenant_id, tenant_id)
    `,
    ).run(chatJid, name, timestamp, ch, group, tid);
  } else {
    db.prepare(
      `
      INSERT INTO chats (jid, name, last_message_time, channel, is_group, tenant_id) VALUES (?, ?, ?, ?, ?, ?)
      ON CONFLICT(jid) DO UPDATE SET
        last_message_time = MAX(last_message_time, excluded.last_message_time),
        channel = COALESCE(excluded.channel, channel),
        is_group = COALESCE(excluded.is_group, is_group),
        tenant_id = COALESCE(excluded.tenant_id, tenant_id)
    `,
    ).run(chatJid, chatJid, timestamp, ch, group, tid);
  }
}

export function updateChatName(chatJid: string, name: string): void {
  db.prepare(
    `
    INSERT INTO chats (jid, name, last_message_time) VALUES (?, ?, ?)
    ON CONFLICT(jid) DO UPDATE SET name = excluded.name
  `,
  ).run(chatJid, name, new Date().toISOString());
}

export interface ChatInfo {
  jid: string;
  name: string;
  last_message_time: string;
  channel: string;
  is_group: number;
  tenant_id: string | null;
}

export function getAllChats(): ChatInfo[] {
  return db
    .prepare(
      `
    SELECT jid, name, last_message_time, channel, is_group, tenant_id
    FROM chats
    ORDER BY last_message_time DESC
  `,
    )
    .all() as ChatInfo[];
}

export function getLastGroupSync(): string | null {
  const row = db
    .prepare(`SELECT last_message_time FROM chats WHERE jid = '__group_sync__'`)
    .get() as { last_message_time: string } | undefined;
  return row?.last_message_time || null;
}

export function setLastGroupSync(): void {
  const now = new Date().toISOString();
  db.prepare(
    `INSERT OR REPLACE INTO chats (jid, name, last_message_time) VALUES ('__group_sync__', '__group_sync__', ?)`,
  ).run(now);
}

// --- Messages ---

export function storeMessage(msg: NewMessage, tenantId?: string): void {
  db.prepare(
    `INSERT OR REPLACE INTO messages (id, chat_jid, sender, sender_name, content, timestamp, is_from_me, is_bot_message, is_trigger, tenant_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  ).run(
    msg.id,
    msg.chat_jid,
    msg.sender,
    msg.sender_name,
    msg.content,
    msg.timestamp,
    msg.is_from_me ? 1 : 0,
    msg.is_bot_message ? 1 : 0,
    msg.is_trigger ? 1 : 0,
    tenantId ?? null,
  );
}

export function storeMessageDirect(msg: {
  id: string;
  chat_jid: string;
  sender: string;
  sender_name: string;
  content: string;
  timestamp: string;
  is_from_me: boolean;
  is_bot_message?: boolean;
  is_trigger?: boolean;
  tenantId?: string;
}): void {
  db.prepare(
    `INSERT OR REPLACE INTO messages (id, chat_jid, sender, sender_name, content, timestamp, is_from_me, is_bot_message, is_trigger, tenant_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  ).run(
    msg.id,
    msg.chat_jid,
    msg.sender,
    msg.sender_name,
    msg.content,
    msg.timestamp,
    msg.is_from_me ? 1 : 0,
    msg.is_bot_message ? 1 : 0,
    msg.is_trigger ? 1 : 0,
    msg.tenantId ?? null,
  );
}

export function getNewMessages(
  jids: string[],
  lastTimestamp: string,
  botPrefix: string,
): { messages: NewMessage[]; newTimestamp: string } {
  if (jids.length === 0) return { messages: [], newTimestamp: lastTimestamp };

  const placeholders = jids.map(() => '?').join(',');
  const sql = `
    SELECT id, chat_jid, sender, sender_name, content, timestamp, is_from_me, is_trigger
    FROM messages
    WHERE timestamp > ? AND chat_jid IN (${placeholders})
      AND is_bot_message = 0 AND content NOT LIKE ?
      AND content != '' AND content IS NOT NULL
    ORDER BY timestamp
  `;

  const rows = db
    .prepare(sql)
    .all(lastTimestamp, ...jids, `${botPrefix}:%`) as NewMessage[];

  let newTimestamp = lastTimestamp;
  for (const row of rows) {
    if (row.timestamp > newTimestamp) newTimestamp = row.timestamp;
  }

  return { messages: rows, newTimestamp };
}

export function getMessagesSince(
  chatJid: string,
  sinceTimestamp: string,
  botPrefix: string,
): NewMessage[] {
  const sql = `
    SELECT id, chat_jid, sender, sender_name, content, timestamp, is_from_me, is_trigger
    FROM messages
    WHERE chat_jid = ? AND timestamp > ?
      AND is_bot_message = 0 AND content NOT LIKE ?
      AND content != '' AND content IS NOT NULL
    ORDER BY timestamp
  `;
  return db
    .prepare(sql)
    .all(chatJid, sinceTimestamp, `${botPrefix}:%`) as NewMessage[];
}

// --- Tasks ---

export function createTask(
  task: Omit<ScheduledTask, 'last_run' | 'last_result'> & {
    tenant_id?: string;
  },
): void {
  db.prepare(
    `
    INSERT INTO scheduled_tasks (id, group_folder, chat_jid, prompt, schedule_type, schedule_value, context_mode, next_run, status, created_at, tenant_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `,
  ).run(
    task.id,
    task.group_folder,
    task.chat_jid,
    task.prompt,
    task.schedule_type,
    task.schedule_value,
    task.context_mode || 'isolated',
    task.next_run,
    task.status,
    task.created_at,
    task.tenant_id ?? null,
  );
}

export function getTaskById(id: string): ScheduledTask | undefined {
  return db.prepare('SELECT * FROM scheduled_tasks WHERE id = ?').get(id) as
    | ScheduledTask
    | undefined;
}

export function getTasksForGroup(groupFolder: string): ScheduledTask[] {
  return db
    .prepare(
      'SELECT * FROM scheduled_tasks WHERE group_folder = ? ORDER BY created_at DESC',
    )
    .all(groupFolder) as ScheduledTask[];
}

export function getAllTasks(): ScheduledTask[] {
  return db
    .prepare('SELECT * FROM scheduled_tasks ORDER BY created_at DESC')
    .all() as ScheduledTask[];
}

export function getTasksForTenant(tenantId: string): ScheduledTask[] {
  return db
    .prepare('SELECT * FROM scheduled_tasks WHERE tenant_id = ? ORDER BY created_at DESC')
    .all(tenantId) as ScheduledTask[];
}

export function updateTask(
  id: string,
  updates: Partial<
    Pick<
      ScheduledTask,
      'prompt' | 'schedule_type' | 'schedule_value' | 'next_run' | 'status'
    >
  >,
): void {
  const fields: string[] = [];
  const values: unknown[] = [];

  if (updates.prompt !== undefined) {
    fields.push('prompt = ?');
    values.push(updates.prompt);
  }
  if (updates.schedule_type !== undefined) {
    fields.push('schedule_type = ?');
    values.push(updates.schedule_type);
  }
  if (updates.schedule_value !== undefined) {
    fields.push('schedule_value = ?');
    values.push(updates.schedule_value);
  }
  if (updates.next_run !== undefined) {
    fields.push('next_run = ?');
    values.push(updates.next_run);
  }
  if (updates.status !== undefined) {
    fields.push('status = ?');
    values.push(updates.status);
  }

  if (fields.length === 0) return;

  values.push(id);
  db.prepare(
    `UPDATE scheduled_tasks SET ${fields.join(', ')} WHERE id = ?`,
  ).run(...values);
}

export function deleteTask(id: string): void {
  db.prepare('DELETE FROM task_run_logs WHERE task_id = ?').run(id);
  db.prepare('DELETE FROM scheduled_tasks WHERE id = ?').run(id);
}

export function getDueTasks(): ScheduledTask[] {
  const now = new Date().toISOString();
  return db
    .prepare(
      `
    SELECT * FROM scheduled_tasks
    WHERE status = 'active' AND next_run IS NOT NULL AND next_run <= ?
    ORDER BY next_run
  `,
    )
    .all(now) as ScheduledTask[];
}

export function updateTaskAfterRun(
  id: string,
  nextRun: string | null,
  lastResult: string,
): void {
  const now = new Date().toISOString();
  db.prepare(
    `
    UPDATE scheduled_tasks
    SET next_run = ?, last_run = ?, last_result = ?, status = CASE WHEN ? IS NULL THEN 'completed' ELSE status END
    WHERE id = ?
  `,
  ).run(nextRun, now, lastResult, nextRun, id);
}

export function logTaskRun(log: TaskRunLog): void {
  db.prepare(
    `
    INSERT INTO task_run_logs (task_id, run_at, duration_ms, status, result, error)
    VALUES (?, ?, ?, ?, ?, ?)
  `,
  ).run(
    log.task_id,
    log.run_at,
    log.duration_ms,
    log.status,
    log.result,
    log.error,
  );
}

// --- Router state accessors ---

export function getRouterState(key: string): string | undefined {
  const row = db
    .prepare('SELECT value FROM router_state WHERE key = ?')
    .get(key) as { value: string } | undefined;
  return row?.value;
}

export function setRouterState(key: string, value: string): void {
  db.prepare(
    'INSERT OR REPLACE INTO router_state (key, value) VALUES (?, ?)',
  ).run(key, value);
}

// --- Session accessors ---

export function getSession(groupFolder: string): string | undefined {
  const row = db
    .prepare('SELECT session_id FROM sessions WHERE group_folder = ?')
    .get(groupFolder) as { session_id: string } | undefined;
  return row?.session_id;
}

export function getSessionRecord(groupFolder: string): SessionRecord | undefined {
  const row = db
    .prepare('SELECT session_id, runtime FROM sessions WHERE group_folder = ?')
    .get(groupFolder) as
    | { session_id: string; runtime: string | null }
    | undefined;
  if (!row) return undefined;
  return {
    id: row.session_id,
    runtime: normalizeProvider(row.runtime),
  };
}

export function setSession(
  groupFolder: string,
  sessionId: string,
  runtime = 'claude',
  tenantId?: string,
): void {
  db.prepare(
    'INSERT OR REPLACE INTO sessions (group_folder, session_id, tenant_id, runtime) VALUES (?, ?, ?, ?)',
  ).run(groupFolder, sessionId, tenantId ?? null, normalizeProvider(runtime));
}


export function deleteSession(groupFolder: string): void {
  db.prepare('DELETE FROM sessions WHERE group_folder = ?').run(groupFolder);
}

export function getAllSessions(): Record<string, SessionRecord> {
  const rows = db
    .prepare('SELECT group_folder, session_id, runtime FROM sessions')
    .all() as Array<{
    group_folder: string;
    session_id: string;
    runtime: string | null;
  }>;
  const result: Record<string, SessionRecord> = {};
  for (const row of rows) {
    result[row.group_folder] = {
      id: row.session_id,
      runtime: normalizeProvider(row.runtime),
    };
  }
  return result;
}

// --- Registered group accessors ---

export function getRegisteredGroup(
  jid: string,
): (RegisteredGroup & { jid: string }) | undefined {
  const row = db
    .prepare('SELECT * FROM registered_groups WHERE jid = ?')
    .get(jid) as
    | {
        jid: string;
        name: string;
        folder: string;
        trigger_pattern: string;
        added_at: string;
        container_config: string | null;
        requires_trigger: number | null;
        is_main: number | null;
        tenant_id: string | null;
      }
    | undefined;
  if (!row) return undefined;
  if (!isValidGroupFolder(row.folder)) {
    logger.warn(
      { jid: row.jid, folder: row.folder },
      'Skipping registered group with invalid folder',
    );
    return undefined;
  }
  return {
    jid: row.jid,
    name: row.name,
    folder: row.folder,
    added_at: row.added_at,
    containerConfig: row.container_config
      ? JSON.parse(row.container_config)
      : undefined,
    requiresTrigger:
      row.requires_trigger === null ? undefined : row.requires_trigger === 1,
    isMain: row.is_main === 1 ? true : undefined,
    tenantId: row.tenant_id ?? undefined,
    hooksToken: (row as any).hooks_token ?? null,
    hooksEnabled: (row as any).hooks_enabled === 1,
  };
}

export function setRegisteredGroup(jid: string, group: RegisteredGroup): void {
  if (!isValidGroupFolder(group.folder)) {
    throw new Error(`Invalid group folder "${group.folder}" for JID ${jid}`);
  }
  db.prepare(
    `INSERT OR REPLACE INTO registered_groups (jid, name, folder, trigger_pattern, added_at, container_config, requires_trigger, is_main, tenant_id)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  ).run(
    jid,
    group.name,
    group.folder,
    '@trigger', // legacy column, trigger detection uses is_trigger on messages now
    group.added_at,
    group.containerConfig ? JSON.stringify(group.containerConfig) : null,
    group.requiresTrigger === undefined ? 1 : group.requiresTrigger ? 1 : 0,
    group.isMain ? 1 : 0,
    group.tenantId ?? null,
  );
  // Auto-enable hooks for new groups (no-op if token already set)
  db.prepare(
    `UPDATE registered_groups SET hooks_token = ?, hooks_enabled = 1 WHERE jid = ? AND hooks_token IS NULL`,
  ).run(generateHooksToken(), jid);
}

export function getAllRegisteredGroups(): Record<string, RegisteredGroup> {
  const rows = db.prepare('SELECT * FROM registered_groups').all() as Array<{
    jid: string;
    name: string;
    folder: string;
    added_at: string;
    container_config: string | null;
    requires_trigger: number | null;
    is_main: number | null;
    tenant_id: string | null;
  }>;
  const result: Record<string, RegisteredGroup> = {};
  for (const row of rows) {
    if (!isValidGroupFolder(row.folder)) {
      logger.warn(
        { jid: row.jid, folder: row.folder },
        'Skipping registered group with invalid folder',
      );
      continue;
    }
    result[row.jid] = {
      name: row.name,
      folder: row.folder,
      added_at: row.added_at,
      containerConfig: row.container_config
        ? JSON.parse(row.container_config)
        : undefined,
      requiresTrigger:
        row.requires_trigger === null ? undefined : row.requires_trigger === 1,
      isMain: row.is_main === 1 ? true : undefined,
      tenantId: row.tenant_id ?? undefined,
    hooksToken: (row as any).hooks_token ?? null,
    hooksEnabled: (row as any).hooks_enabled === 1,
    };
  }
  return result;
}

export function getRegisteredGroupsByTenant(
  tenantId: string,
): Record<string, RegisteredGroup> {
  const rows = db
    .prepare('SELECT * FROM registered_groups WHERE tenant_id = ?')
    .all(tenantId) as Array<{
    jid: string;
    name: string;
    folder: string;
    trigger_pattern: string;
    added_at: string;
    container_config: string | null;
    requires_trigger: number | null;
    is_main: number | null;
    tenant_id: string | null;
  }>;
  const result: Record<string, RegisteredGroup> = {};
  for (const row of rows) {
    if (!isValidGroupFolder(row.folder)) continue;
    result[row.jid] = {
      name: row.name,
      folder: row.folder,
      added_at: row.added_at,
      containerConfig: row.container_config
        ? JSON.parse(row.container_config)
        : undefined,
      requiresTrigger:
        row.requires_trigger === null ? undefined : row.requires_trigger === 1,
      isMain: row.is_main === 1 ? true : undefined,
      tenantId: row.tenant_id ?? undefined,
    hooksToken: (row as any).hooks_token ?? null,
    hooksEnabled: (row as any).hooks_enabled === 1,
    };
  }
  return result;
}

// --- Usage tracking ---

export function logUsage(
  tenantId: string,
  groupFolder: string,
  durationMs: number,
  inputTokens: number = 0,
): void {
  db.prepare(
    `INSERT INTO usage_log (tenant_id, timestamp, group_folder, duration_ms, input_tokens)
     VALUES (?, ?, ?, ?, ?)`,
  ).run(tenantId, new Date().toISOString(), groupFolder, durationMs, inputTokens);
}

export function checkUsageLimit(tenantId: string): {
  allowed: boolean;
  reason?: string;
} {
  const limit = db
    .prepare('SELECT * FROM usage_limits WHERE tenant_id = ?')
    .get(tenantId) as
    | { daily_requests: number | null; daily_duration_ms: number | null }
    | undefined;

  if (!limit) return { allowed: true };

  const today = new Date().toISOString().slice(0, 10);
  const usage = db
    .prepare(
      `SELECT COUNT(*) as requests, COALESCE(SUM(duration_ms), 0) as total_duration_ms
       FROM usage_log WHERE tenant_id = ? AND timestamp >= ?`,
    )
    .get(tenantId, today) as { requests: number; total_duration_ms: number };

  if (limit.daily_requests && usage.requests >= limit.daily_requests) {
    return {
      allowed: false,
      reason: `Daily request limit reached (${usage.requests}/${limit.daily_requests})`,
    };
  }

  if (limit.daily_duration_ms && usage.total_duration_ms >= limit.daily_duration_ms) {
    const usedMins = Math.round(usage.total_duration_ms / 60000);
    const limitMins = Math.round(limit.daily_duration_ms / 60000);
    return {
      allowed: false,
      reason: `Daily usage time reached (${usedMins}min/${limitMins}min)`,
    };
  }

  return { allowed: true };
}

export function getTenantUsage(
  tenantId: string,
  since: string,
): { requests: number; total_duration_ms: number } {
  return db
    .prepare(
      `SELECT COUNT(*) as requests, COALESCE(SUM(duration_ms), 0) as total_duration_ms,
              COALESCE(SUM(input_tokens), 0) as total_input_tokens
       FROM usage_log WHERE tenant_id = ? AND timestamp >= ?`,
    )
    .get(tenantId, since) as { requests: number; total_duration_ms: number; total_input_tokens: number };
}

// --- Tenant services (for persistent Caddy routing) ---

export function upsertTenantService(
  tenantId: string,
  subdomain: string,
  config: object,
): void {
  db.prepare(
    `INSERT OR REPLACE INTO tenant_services (tenant_id, subdomain, config, updated_at)
     VALUES (?, ?, ?, ?)`,
  ).run(tenantId, subdomain, JSON.stringify(config), new Date().toISOString());
}

export function getTenantServices(
  tenantId: string,
): Array<{ subdomain: string; config: Record<string, unknown> }> {
  const rows = db
    .prepare(
      'SELECT subdomain, config FROM tenant_services WHERE tenant_id = ? ORDER BY subdomain',
    )
    .all(tenantId) as Array<{ subdomain: string; config: string }>;
  return rows.map((r) => ({
    subdomain: r.subdomain,
    config: JSON.parse(r.config) as Record<string, unknown>,
  }));
}

export function deleteTenantService(
  tenantId: string,
  subdomain: string,
): void {
  db.prepare(
    'DELETE FROM tenant_services WHERE tenant_id = ? AND subdomain = ?',
  ).run(tenantId, subdomain);
}

// --- Custom domains ---

export interface CustomDomain {
  id: string;
  tenant_id: string;
  domain: string;
  target: string;
  verification_token: string;
  status: 'pending' | 'active' | 'failed';
  created_at: string;
  verified_at: string | null;
}

export function addCustomDomain(
  tenantId: string,
  domain: string,
  target: string,
): CustomDomain {
  const id =
    Date.now().toString(36) + Math.random().toString(36).slice(2, 6);
  const token =
    Math.random().toString(36).slice(2) +
    Math.random().toString(36).slice(2) +
    Math.random().toString(36).slice(2);
  const now = new Date().toISOString();
  db.prepare(
    `INSERT INTO custom_domains (id, tenant_id, domain, target, verification_token, status, created_at)
     VALUES (?, ?, ?, ?, ?, 'pending', ?)`,
  ).run(id, tenantId, domain.toLowerCase().trim(), target, token, now);
  return db
    .prepare('SELECT * FROM custom_domains WHERE id = ?')
    .get(id) as CustomDomain;
}

export function getCustomDomain(domain: string): CustomDomain | undefined {
  return db
    .prepare('SELECT * FROM custom_domains WHERE domain = ?')
    .get(domain.toLowerCase().trim()) as CustomDomain | undefined;
}

export function getCustomDomainById(id: string): CustomDomain | undefined {
  return db
    .prepare('SELECT * FROM custom_domains WHERE id = ?')
    .get(id) as CustomDomain | undefined;
}

export function getTenantCustomDomains(tenantId: string): CustomDomain[] {
  return db
    .prepare(
      'SELECT * FROM custom_domains WHERE tenant_id = ? ORDER BY created_at',
    )
    .all(tenantId) as CustomDomain[];
}

export function getAllPendingCustomDomains(): CustomDomain[] {
  return db
    .prepare("SELECT * FROM custom_domains WHERE status = 'pending'")
    .all() as CustomDomain[];
}

export function activateCustomDomain(domain: string): void {
  db.prepare(
    `UPDATE custom_domains SET status = 'active', verified_at = ? WHERE domain = ?`,
  ).run(new Date().toISOString(), domain.toLowerCase().trim());
}

export function removeCustomDomain(id: string, tenantId: string): boolean {
  const result = db
    .prepare('DELETE FROM custom_domains WHERE id = ? AND tenant_id = ?')
    .run(id, tenantId);
  return result.changes > 0;
}

export function removeCustomDomainAdmin(id: string): boolean {
  const result = db
    .prepare('DELETE FROM custom_domains WHERE id = ?')
    .run(id);
  return result.changes > 0;
}

export function getAllActiveCustomDomains(): CustomDomain[] {
  return db
    .prepare("SELECT * FROM custom_domains WHERE status = 'active'")
    .all() as CustomDomain[];
}

// --- Credential pool ---

export function upsertCredential(cred: Credential): void {
  db.prepare(`
    INSERT INTO credentials (id, provider, type, label, status, access_token, refresh_token, expires_at, api_key, auth_json, plan_type, rate_limit_tier, last_used_at, total_requests, error_message, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      provider = excluded.provider,
      type = excluded.type,
      label = excluded.label,
      status = excluded.status,
      access_token = excluded.access_token,
      refresh_token = excluded.refresh_token,
      expires_at = excluded.expires_at,
      api_key = excluded.api_key,
      auth_json = excluded.auth_json,
      plan_type = excluded.plan_type,
      rate_limit_tier = excluded.rate_limit_tier,
      last_used_at = excluded.last_used_at,
      total_requests = excluded.total_requests,
      error_message = excluded.error_message
  `).run(
    cred.id,
    cred.provider ?? 'claude',
    cred.type,
    cred.label ?? null,
    cred.status,
    cred.access_token ?? null,
    cred.refresh_token ?? null,
    cred.expires_at ?? null,
    cred.api_key ?? null,
    cred.auth_json ?? null,
    cred.plan_type ?? null,
    cred.rate_limit_tier ?? null,
    cred.last_used_at ?? null,
    cred.total_requests,
    cred.error_message ?? null,
    cred.created_at,
  );
}

export function getCredential(id: string): Credential | undefined {
  return db.prepare('SELECT * FROM credentials WHERE id = ?').get(id) as Credential | undefined;
}

export function getAllCredentials(): Credential[] {
  return db.prepare('SELECT * FROM credentials ORDER BY created_at').all() as Credential[];
}

export function getActiveCredentials(): Credential[] {
  return db.prepare("SELECT * FROM credentials WHERE status = 'active' ORDER BY last_used_at ASC NULLS FIRST").all() as Credential[];
}

export function updateCredentialAfterUse(id: string): void {
  db.prepare(
    `UPDATE credentials SET last_used_at = ?, total_requests = total_requests + 1 WHERE id = ?`
  ).run(new Date().toISOString(), id);
}

export function updateCredentialTokens(id: string, accessToken: string, refreshToken: string, expiresAt: number): void {
  db.prepare(
    `UPDATE credentials SET access_token = ?, refresh_token = ?, expires_at = ?, status = 'active', error_message = NULL WHERE id = ?`
  ).run(accessToken, refreshToken, expiresAt, id);
}

export function updateCredentialAuthJson(
  id: string,
  authJson: string,
  expiresAt?: number,
): void {
  if (expiresAt !== undefined) {
    db.prepare(
      `UPDATE credentials SET auth_json = ?, expires_at = ?, status = 'active', error_message = NULL WHERE id = ?`,
    ).run(authJson, expiresAt, id);
    return;
  }

  db.prepare(
    `UPDATE credentials SET auth_json = ?, status = 'active', error_message = NULL WHERE id = ?`,
  ).run(authJson, id);
}

export function updateCredentialStatus(id: string, status: Credential['status'], errorMessage?: string): void {
  db.prepare(
    `UPDATE credentials SET status = ?, error_message = ? WHERE id = ?`
  ).run(status, errorMessage ?? null, id);
}

export function deleteCredential(id: string): void {
  db.prepare('DELETE FROM credentials WHERE id = ?').run(id);
}

// --- JSON migration ---

function migrateJsonState(): void {
  const migrateFile = (filename: string) => {
    const filePath = path.join(DATA_DIR, filename);
    if (!fs.existsSync(filePath)) return null;
    try {
      const data = JSON.parse(fs.readFileSync(filePath, 'utf-8'));
      fs.renameSync(filePath, `${filePath}.migrated`);
      return data;
    } catch {
      return null;
    }
  };

  // Migrate router_state.json
  const routerState = migrateFile('router_state.json') as {
    last_timestamp?: string;
    last_agent_timestamp?: Record<string, string>;
  } | null;
  if (routerState) {
    if (routerState.last_timestamp) {
      setRouterState('last_timestamp', routerState.last_timestamp);
    }
    if (routerState.last_agent_timestamp) {
      setRouterState(
        'last_agent_timestamp',
        JSON.stringify(routerState.last_agent_timestamp),
      );
    }
  }

  // Migrate sessions.json
  const sessions = migrateFile('sessions.json') as Record<
    string,
    string
  > | null;
  if (sessions) {
    for (const [folder, sessionId] of Object.entries(sessions)) {
      setSession(folder, sessionId);
    }
  }

  // Migrate registered_groups.json
  const groups = migrateFile('registered_groups.json') as Record<
    string,
    RegisteredGroup
  > | null;
  if (groups) {
    for (const [jid, group] of Object.entries(groups)) {
      try {
        setRegisteredGroup(jid, group);
      } catch (err) {
        logger.warn(
          { jid, folder: group.folder, err },
          'Skipping migrated registered group with invalid folder',
        );
      }
    }
  }
}

// --- Agent status ---

export type AgentStatus = 'IDLE' | 'WORKING';

export function setAgentStatus(chatJid: string, status: AgentStatus): void {
  db.prepare(`UPDATE chats SET agent_status = ? WHERE jid = ?`).run(status, chatJid);
}

export function resetAllAgentStatuses(): void {
  db.prepare(`UPDATE chats SET agent_status = 'IDLE'`).run();
}

// --- Hooks token ---

export interface HooksConfig {
  jid: string;
  hooksToken: string | null;
  hooksEnabled: boolean;
}

function generateHooksToken(): string {
  return randomBytes(32).toString("hex");
}

export function getHooksConfig(jid: string): HooksConfig | undefined {
  const row = db
    .prepare("SELECT jid, hooks_token, hooks_enabled FROM registered_groups WHERE jid = ?")
    .get(jid) as { jid: string; hooks_token: string | null; hooks_enabled: number } | undefined;
  if (!row) return undefined;
  return {
    jid: row.jid,
    hooksToken: row.hooks_token,
    hooksEnabled: row.hooks_enabled === 1,
  };
}

export function getGroupByHooksToken(token: string): (RegisteredGroup & { jid: string }) | undefined {
  const row = db
    .prepare("SELECT * FROM registered_groups WHERE hooks_token = ? AND hooks_enabled = 1")
    .get(token) as
    | { jid: string; name: string; folder: string; added_at: string; container_config: string | null; requires_trigger: number | null; is_main: number | null; tenant_id: string | null }
    | undefined;
  if (!row) return undefined;
  return {
    jid: row.jid,
    name: row.name,
    folder: row.folder,
    added_at: row.added_at,
    containerConfig: row.container_config ? JSON.parse(row.container_config) : undefined,
    requiresTrigger: row.requires_trigger === null ? undefined : row.requires_trigger === 1,
    isMain: row.is_main === 1 ? true : undefined,
    tenantId: row.tenant_id ?? undefined,
  };
}

export function rotateHooksToken(jid: string): string {
  const token = generateHooksToken();
  db.prepare("UPDATE registered_groups SET hooks_token = ? WHERE jid = ?").run(token, jid);
  return token;
}

export function setHooksEnabled(jid: string, enabled: boolean): void {
  const row = db
    .prepare("SELECT hooks_token FROM registered_groups WHERE jid = ?")
    .get(jid) as { hooks_token: string | null } | undefined;
  if (enabled && row && !row.hooks_token) {
    const token = generateHooksToken();
    db.prepare("UPDATE registered_groups SET hooks_enabled = ?, hooks_token = ? WHERE jid = ?").run(1, token, jid);
  } else {
    db.prepare("UPDATE registered_groups SET hooks_enabled = ? WHERE jid = ?").run(enabled ? 1 : 0, jid);
  }
}
