import Database from 'better-sqlite3'
import path from 'path'
import fs from 'node:fs'
import os from 'node:os'
import { execFileSync } from 'node:child_process'
import { getForgeDataDir, migrateFromOldDataDir } from './forge-data'

declare const globalThis: {
  __forgeDb?: Database.Database
} & typeof global

export function getDbPath(): string {
  // Attempt migration from old .forge-data/ on first access
  migrateFromOldDataDir()
  return path.join(getForgeDataDir(), 'forge.db')
}

function ensureHotfixMigrations(db: Database.Database): void {
  const tableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='im_channels'").get() as { name: string } | undefined
  if (!tableExists) return

  const imCols = db.prepare("PRAGMA table_info(im_channels)").all() as { name: string }[]
  const imColNames = imCols.map(c => c.name)
  if (!imColNames.includes('name')) {
    db.exec("ALTER TABLE im_channels ADD COLUMN name TEXT NOT NULL DEFAULT ''")
  }
  if (!imColNames.includes('default_workspace_id')) {
    db.exec("ALTER TABLE im_channels ADD COLUMN default_workspace_id TEXT NOT NULL DEFAULT ''")
  }
  if (!imColNames.includes('default_model')) {
    db.exec("ALTER TABLE im_channels ADD COLUMN default_model TEXT NOT NULL DEFAULT ''")
  }
  // Clean up legacy seed rows (old design: id='telegram' etc., new design: id=UUID)
  db.prepare("DELETE FROM im_channels WHERE id = type AND type IN ('feishu','telegram','discord','weixin','dingtalk','wecom')").run()

  const bindingTableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='channel_bindings'").get() as { name: string } | undefined
  if (!bindingTableExists) return

  const cbCols = db.prepare("PRAGMA table_info(channel_bindings)").all() as { name: string }[]
  const cbColNames = cbCols.map(c => c.name)
  if (!cbColNames.includes('session_id')) {
    db.exec("ALTER TABLE channel_bindings ADD COLUMN session_id TEXT DEFAULT NULL")
  }
  if (!cbColNames.includes('workspace_id')) {
    db.exec("ALTER TABLE channel_bindings ADD COLUMN workspace_id TEXT NOT NULL DEFAULT ''")
  }
  if (!cbColNames.includes('updated_at')) {
    db.exec("ALTER TABLE channel_bindings ADD COLUMN updated_at TEXT NOT NULL DEFAULT ''")
  }
  db.prepare("UPDATE channel_bindings SET updated_at = created_at WHERE updated_at = ''").run()
  db.prepare("UPDATE channel_bindings SET workspace_id = workspace WHERE workspace_id = '' AND workspace != ''").run()
  db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_channel_bindings_channel_chat ON channel_bindings(channel_id, chat_id)')

  const imSchemaRow = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='im_channels'").get() as { sql: string } | undefined
  if (imSchemaRow && (!imSchemaRow.sql.includes("'dingtalk'") || !imSchemaRow.sql.includes("'wecom'"))) {
    migrateImChannelsTable(db)
  }

  // Migrate cron_tasks: add webhook_enabled column
  const cronCols = db.prepare("PRAGMA table_info(cron_tasks)").all() as { name: string }[]
  const cronColNames = cronCols.map(c => c.name)
  if (!cronColNames.includes('webhook_enabled')) {
    db.exec("ALTER TABLE cron_tasks ADD COLUMN webhook_enabled INTEGER NOT NULL DEFAULT 0")
  }

  // Create webhook_tokens table
  db.exec(`
    CREATE TABLE IF NOT EXISTS webhook_tokens (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL DEFAULT '',
      token TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `)

  // Create invitations table (idempotent). The schema/columns/indexes are
  // pulled from module-level constants below so the initial CREATE and any
  // forced migration rebuild cannot drift apart.
  db.exec(INVITATIONS_SCHEMA)
  for (const idx of INVITATIONS_INDEXES) db.exec(idx)
  ensureColumn(db, 'invitations', 'project_root', "TEXT NOT NULL DEFAULT ''")

  // One-time migration: older deployments had a 'revoked' status we have
  // since removed (member deletion is now hard-delete, not soft-mark).
  // SQLite cannot ALTER CHECK, so we detect via sqlite_master and rebuild.
  // Any 'revoked' rows are dropped — under hard-delete semantics they
  // should not exist, and if they do, the DELETE path was interrupted.
  const createSqlRow = db
    .prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='invitations'")
    .get() as { sql: string } | undefined
  if (createSqlRow && createSqlRow.sql.includes("'revoked'")) {
    const invRows = db
      .prepare(`SELECT ${INVITATION_COLUMNS.join(', ')} FROM invitations`)
      .all() as Array<Record<string, string>>
    db.transaction(() => {
      db.exec('DROP TABLE invitations')
      db.exec(INVITATIONS_SCHEMA)
      for (const idx of INVITATIONS_INDEXES) db.exec(idx)
      const placeholders = INVITATION_COLUMNS.map(() => '?').join(', ')
      const insertInvitation = db.prepare(
        `INSERT INTO invitations (${INVITATION_COLUMNS.join(', ')}) VALUES (${placeholders})`,
      )
      for (const r of invRows) {
        if (r.status === 'revoked') continue
        insertInvitation.run(...INVITATION_COLUMNS.map((c) => r[c] ?? ''))
      }
    })()
  }
}

// Single source of truth for the invitations table. Both the initial CREATE
// and the legacy-status migration rebuild from these constants so the two
// paths cannot drift.
const INVITATION_STATUSES = [
  'pending', 'phone_verified', 'qr_generated',
  'bot_scanned', 'bot_connected', 'completed',
  'expired', 'cancelled',
] as const

const INVITATION_COLUMNS = [
  'id', 'invite_token', 'inviter_user_id', 'invitee_phone', 'invitee_name',
  'status', 'qrcode_key', 'qrcode_url', 'bot_token', 'ilink_bot_id',
  'baseurl', 'cdn_baseurl', 'created_user_id', 'created_user_dir',
  'created_channel_id', 'project_root', 'expires_at', 'created_at', 'updated_at',
] as const

const INVITATIONS_SCHEMA = `
  CREATE TABLE IF NOT EXISTS invitations (
    id TEXT PRIMARY KEY,
    invite_token TEXT NOT NULL UNIQUE,
    inviter_user_id TEXT NOT NULL,
    invitee_phone TEXT NOT NULL DEFAULT '',
    invitee_name TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'pending'
      CHECK (status IN (${INVITATION_STATUSES.map((s) => `'${s}'`).join(', ')})),
    qrcode_key TEXT NOT NULL DEFAULT '',
    qrcode_url TEXT NOT NULL DEFAULT '',
    bot_token TEXT NOT NULL DEFAULT '',
    ilink_bot_id TEXT NOT NULL DEFAULT '',
    baseurl TEXT NOT NULL DEFAULT '',
    cdn_baseurl TEXT NOT NULL DEFAULT '',
    created_user_id TEXT NOT NULL DEFAULT '',
    created_user_dir TEXT NOT NULL DEFAULT '',
    created_channel_id TEXT NOT NULL DEFAULT '',
    project_root TEXT NOT NULL DEFAULT '',
    expires_at INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (inviter_user_id) REFERENCES user(id) ON DELETE CASCADE
  )
`

const INVITATIONS_INDEXES = [
  'CREATE UNIQUE INDEX IF NOT EXISTS idx_invitations_token ON invitations(invite_token)',
  'CREATE INDEX IF NOT EXISTS idx_invitations_inviter ON invitations(inviter_user_id)',
  'CREATE INDEX IF NOT EXISTS idx_invitations_status ON invitations(status)',
  'CREATE INDEX IF NOT EXISTS idx_invitations_phone ON invitations(invitee_phone)',
]

function ensureColumn(db: Database.Database, table: string, column: string, definition: string): void {
  const tableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name = ?").get(table) as { name: string } | undefined
  if (!tableExists) return
  const cols = db.prepare(`PRAGMA table_info(${table})`).all() as { name: string }[]
  if (!cols.some(c => c.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`)
  }
}

function dedupeMcpServerNamesForUser(db: Database.Database, userId: string): void {
  const rows = db.prepare(
    "SELECT id, name, user_id FROM mcp_servers WHERE user_id = ? OR user_id = '' ORDER BY user_id DESC, created_at ASC, id ASC",
  ).all(userId) as { id: string; name: string; user_id: string }[]

  const used = new Set<string>()
  for (const row of rows) {
    let name = row.name || 'MCP Server'
    if (used.has(name)) {
      const base = name
      let counter = 2
      while (used.has(`${base} (${counter})`)) counter++
      name = `${base} (${counter})`
      db.prepare('UPDATE mcp_servers SET name = ?, updated_at = datetime(\'now\') WHERE id = ?').run(name, row.id)
    }
    used.add(name)
  }
}

function deleteDuplicateMcpServerConfigs(db: Database.Database, userId: string): void {
  const rows = db.prepare(
    "SELECT id, protocol, config, created_at FROM mcp_servers WHERE user_id = ? ORDER BY created_at ASC, id ASC",
  ).all(userId) as { id: string; protocol: string; config: string; created_at: string }[]

  const seen = new Set<string>()
  for (const row of rows) {
    const key = `${row.protocol}\n${row.config}`
    if (seen.has(key)) {
      db.prepare('DELETE FROM mcp_servers WHERE id = ?').run(row.id)
      continue
    }
    seen.add(key)
  }
}

function ensureAuthAndOwnershipSchema(db: Database.Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS user (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL DEFAULT '',
      email TEXT NOT NULL UNIQUE,
      emailVerified INTEGER NOT NULL DEFAULT 0,
      image TEXT,
      role TEXT NOT NULL DEFAULT 'user',
      banned INTEGER NOT NULL DEFAULT 0,
      banReason TEXT,
      banExpires INTEGER,
      createdAt INTEGER NOT NULL,
      updatedAt INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS account (
      id TEXT PRIMARY KEY,
      accountId TEXT NOT NULL,
      providerId TEXT NOT NULL,
      userId TEXT NOT NULL,
      accessToken TEXT,
      refreshToken TEXT,
      idToken TEXT,
      accessTokenExpiresAt INTEGER,
      refreshTokenExpiresAt INTEGER,
      scope TEXT,
      password TEXT,
      createdAt INTEGER NOT NULL,
      updatedAt INTEGER NOT NULL,
      FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE
    );
    CREATE UNIQUE INDEX IF NOT EXISTS idx_account_provider_account
      ON account(providerId, accountId);
    CREATE INDEX IF NOT EXISTS idx_account_user ON account(userId);

    CREATE TABLE IF NOT EXISTS session (
      id TEXT PRIMARY KEY,
      expiresAt INTEGER NOT NULL,
      token TEXT NOT NULL UNIQUE,
      createdAt INTEGER NOT NULL,
      updatedAt INTEGER NOT NULL,
      ipAddress TEXT,
      userAgent TEXT,
      userId TEXT NOT NULL,
      FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE
    );
    CREATE INDEX IF NOT EXISTS idx_auth_session_user ON session(userId);

    CREATE TABLE IF NOT EXISTS verification (
      id TEXT PRIMARY KEY,
      identifier TEXT NOT NULL,
      value TEXT NOT NULL,
      expiresAt INTEGER NOT NULL,
      createdAt INTEGER,
      updatedAt INTEGER
    );
  `)

  ensureColumn(db, 'workspaces', 'owner_user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'workspaces', 'team_id', 'TEXT')
  ensureColumn(db, 'workspaces', 'visibility', "TEXT NOT NULL DEFAULT 'private'")
  ensureColumn(db, 'user', 'role', "TEXT NOT NULL DEFAULT 'user'")
  ensureColumn(db, 'user', 'banned', 'INTEGER NOT NULL DEFAULT 0')
  ensureColumn(db, 'user', 'banReason', 'TEXT')
  ensureColumn(db, 'user', 'banExpires', 'INTEGER')
  ensureColumn(db, 'user', 'phoneNumber', 'TEXT')
  ensureColumn(db, 'user', 'phoneNumberVerified', 'INTEGER NOT NULL DEFAULT 0')
  ensureColumn(db, 'sessions', 'user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'sessions', 'external_user_id', 'TEXT')
  ensureColumn(db, 'api_providers', 'user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'api_providers', 'models_fetched_at', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'api_providers', 'models_fetch_error', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'agents', 'user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'skills', 'user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'mcp_servers', 'user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'im_channels', 'owner_user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'cron_tasks', 'owner_user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'webhook_tokens', 'owner_user_id', "TEXT NOT NULL DEFAULT ''")
  ensureColumn(db, 'marketplace_templates', 'owner_user_id', "TEXT NOT NULL DEFAULT ''")

  db.exec('DROP INDEX IF EXISTS idx_mcp_servers_name')
  db.exec('DROP INDEX IF EXISTS idx_sessions_ext_user')
  db.exec('CREATE INDEX IF NOT EXISTS idx_sessions_external_user ON sessions(external_user_id, workspace, updated_at DESC)')
  db.exec('CREATE INDEX IF NOT EXISTS idx_sessions_user_status_updated ON sessions(user_id, status, updated_at DESC)')

  db.exec(`
    CREATE TABLE IF NOT EXISTS user_settings (
      user_id TEXT NOT NULL,
      key TEXT NOT NULL,
      value TEXT NOT NULL,
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      PRIMARY KEY (user_id, key),
      FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
    );
    CREATE INDEX IF NOT EXISTS idx_agents_user ON agents(user_id, updated_at);
    CREATE INDEX IF NOT EXISTS idx_skills_user ON skills(user_id, updated_at);
    CREATE INDEX IF NOT EXISTS idx_mcp_servers_user ON mcp_servers(user_id, updated_at);

    CREATE TABLE IF NOT EXISTS workspace_members (
      workspace_id TEXT NOT NULL,
      user_id TEXT NOT NULL,
      role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      PRIMARY KEY (workspace_id, user_id),
      FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
      FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
    );
    CREATE INDEX IF NOT EXISTS idx_workspace_members_user
      ON workspace_members(user_id, role);
    CREATE INDEX IF NOT EXISTS idx_workspaces_owner
      ON workspaces(owner_user_id, last_opened_at);
  `)

  db.exec(`
    CREATE TABLE IF NOT EXISTS external_users (
      id TEXT PRIMARY KEY,
      owner_user_id TEXT NOT NULL,
      display_name TEXT NOT NULL DEFAULT '',
      avatar_url TEXT NOT NULL DEFAULT '',
      phone TEXT,
      status TEXT NOT NULL DEFAULT 'active',
      metadata TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      last_active_at TEXT NOT NULL DEFAULT ''
    );
    CREATE INDEX IF NOT EXISTS idx_external_users_owner
      ON external_users(owner_user_id, last_active_at);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_external_users_owner_phone
      ON external_users(owner_user_id, phone) WHERE phone IS NOT NULL;

    CREATE TABLE IF NOT EXISTS external_identities (
      id TEXT PRIMARY KEY,
      external_user_id TEXT NOT NULL,
      channel_type TEXT NOT NULL,
      channel_id TEXT NOT NULL DEFAULT '',
      platform_user_id TEXT NOT NULL,
      platform_name TEXT NOT NULL DEFAULT '',
      platform_avatar TEXT NOT NULL DEFAULT '',
      linked_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_ext_identities_user
      ON external_identities(external_user_id);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_ext_identities_lookup
      ON external_identities(channel_type, channel_id, platform_user_id);

    CREATE TABLE IF NOT EXISTS wxapp_configs (
      id TEXT PRIMARY KEY,
      workspace_id TEXT NOT NULL UNIQUE,
      slug TEXT NOT NULL UNIQUE,
      appid TEXT NOT NULL DEFAULT '',
      app_secret TEXT NOT NULL DEFAULT '',
      default_model TEXT NOT NULL DEFAULT '',
      permission_mode TEXT NOT NULL DEFAULT 'full',
      page_title TEXT NOT NULL DEFAULT '',
      description TEXT NOT NULL DEFAULT '',
      welcome_message TEXT NOT NULL DEFAULT '',
      icon_emoji TEXT NOT NULL DEFAULT '',
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_wxapp_configs_workspace
      ON wxapp_configs(workspace_id);

    CREATE TABLE IF NOT EXISTS wxapp_tokens (
      id TEXT PRIMARY KEY,
      token TEXT NOT NULL UNIQUE,
      external_user_id TEXT NOT NULL,
      workspace_id TEXT NOT NULL,
      openid TEXT NOT NULL,
      expires_at TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_wxapp_tokens_user
      ON wxapp_tokens(external_user_id, workspace_id);
    CREATE INDEX IF NOT EXISTS idx_wxapp_tokens_expiry
      ON wxapp_tokens(expires_at);
  `)

  // Migrate external_users: phone column from NOT NULL DEFAULT '' to nullable
  // (SQLite can't ALTER COLUMN, so check and rebuild if needed)
  const euPhoneCol = db.prepare('PRAGMA table_info(external_users)').all() as { name: string; notnull: number }[]
  const phoneCol = euPhoneCol.find(c => c.name === 'phone')
  if (phoneCol && phoneCol.notnull === 1) {
    db.pragma('foreign_keys = OFF')
    db.exec(`
      CREATE TABLE external_users_new (
        id TEXT PRIMARY KEY,
        owner_user_id TEXT NOT NULL,
        display_name TEXT NOT NULL DEFAULT '',
        avatar_url TEXT NOT NULL DEFAULT '',
        phone TEXT,
        status TEXT NOT NULL DEFAULT 'active',
        metadata TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        updated_at TEXT NOT NULL DEFAULT (datetime('now')),
        last_active_at TEXT NOT NULL DEFAULT ''
      );
      INSERT INTO external_users_new (id, owner_user_id, display_name, avatar_url, phone, status, metadata, created_at, updated_at, last_active_at)
      SELECT id, owner_user_id, display_name, avatar_url, NULLIF(phone, ''), status, metadata, created_at, updated_at, last_active_at
      FROM external_users;
      DROP TABLE external_users;
      ALTER TABLE external_users_new RENAME TO external_users;
      CREATE INDEX IF NOT EXISTS idx_external_users_owner ON external_users(owner_user_id, last_active_at);
      CREATE UNIQUE INDEX IF NOT EXISTS idx_external_users_owner_phone ON external_users(owner_user_id, phone) WHERE phone IS NOT NULL;
    `)
    db.pragma('foreign_keys = ON')
  }

  const firstUser = db.prepare('SELECT id FROM user ORDER BY createdAt ASC LIMIT 1').get() as { id: string } | undefined
  if (firstUser) {
    db.prepare("UPDATE workspaces SET owner_user_id = ? WHERE owner_user_id = ''").run(firstUser.id)
    db.prepare("UPDATE sessions SET user_id = ? WHERE user_id = ''").run(firstUser.id)
    db.prepare("UPDATE api_providers SET user_id = ? WHERE user_id = ''").run(firstUser.id)
    db.prepare("UPDATE agents SET user_id = ? WHERE user_id = ''").run(firstUser.id)
    db.prepare("UPDATE skills SET user_id = ? WHERE user_id = ''").run(firstUser.id)
    dedupeMcpServerNamesForUser(db, firstUser.id)
    db.prepare("UPDATE mcp_servers SET user_id = ? WHERE user_id = ''").run(firstUser.id)
    db.prepare("UPDATE im_channels SET owner_user_id = ? WHERE owner_user_id = ''").run(firstUser.id)
    db.prepare("UPDATE cron_tasks SET owner_user_id = ? WHERE owner_user_id = ''").run(firstUser.id)
    db.prepare("UPDATE webhook_tokens SET owner_user_id = ? WHERE owner_user_id = ''").run(firstUser.id)
    db.prepare("UPDATE marketplace_templates SET owner_user_id = ? WHERE owner_user_id = ''").run(firstUser.id)
    deleteDuplicateMcpServerConfigs(db, firstUser.id)
    dedupeMcpServerNamesForUser(db, firstUser.id)
  }

  db.exec(`
    CREATE TABLE IF NOT EXISTS provider_models (
      id TEXT PRIMARY KEY,
      provider_id TEXT NOT NULL,
      user_id TEXT NOT NULL DEFAULT '',
      model_id TEXT NOT NULL,
      label TEXT NOT NULL,
      capabilities TEXT NOT NULL DEFAULT '{}',
      aliases TEXT NOT NULL DEFAULT '[]',
      source TEXT NOT NULL DEFAULT 'dynamic',
      status TEXT NOT NULL DEFAULT 'available',
      fetched_at TEXT NOT NULL DEFAULT (datetime('now')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_provider_models_provider_user ON provider_models(provider_id, user_id);
    CREATE INDEX IF NOT EXISTS idx_provider_models_model ON provider_models(model_id);
  `)
}

function migrateImChannelsTable(db: Database.Database): void {
  const rows = db.prepare('SELECT * FROM im_channels').all()
  const bindingTableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='channel_bindings'").get() as { name: string } | undefined
  const permissionTableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='channel_permission_links'").get() as { name: string } | undefined
  const bindingRows = bindingTableExists ? db.prepare('SELECT * FROM channel_bindings').all() as Record<string, unknown>[] : []
  const permissionRows = permissionTableExists ? db.prepare('SELECT * FROM channel_permission_links').all() as Record<string, unknown>[] : []

  // Temporarily disable FK enforcement to allow DROP TABLE im_channels
  // which is referenced by channel_bindings, channel_permission_links,
  // weixin_context_tokens, weixin_sync_cursors via ON DELETE CASCADE.
  db.pragma('foreign_keys = OFF')
  db.exec('DROP TABLE im_channels')
  db.pragma('foreign_keys = ON')
  db.exec(`
    CREATE TABLE im_channels (
                               id TEXT PRIMARY KEY,
                               type TEXT NOT NULL CHECK (type IN ('feishu', 'telegram', 'discord', 'weixin', 'dingtalk', 'wecom')),
                               name TEXT NOT NULL DEFAULT '',
                               enabled INTEGER NOT NULL DEFAULT 0,
                               status TEXT NOT NULL DEFAULT 'not_configured' CHECK (status IN ('connected', 'disconnected', 'not_configured', 'error')),
                               credentials TEXT NOT NULL DEFAULT '{}',
                               default_workspace_id TEXT NOT NULL DEFAULT '',
                               default_model TEXT NOT NULL DEFAULT '',
                               dm_policy TEXT NOT NULL DEFAULT 'open',
                               group_policy TEXT NOT NULL DEFAULT 'open',
                               trigger_mode TEXT NOT NULL DEFAULT 'mention',
                               group_whitelist TEXT NOT NULL DEFAULT '[]',
                               sender_whitelist TEXT NOT NULL DEFAULT '[]',
                               created_at TEXT NOT NULL DEFAULT (datetime('now')),
                               updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `)
  for (const row of rows as Record<string, unknown>[]) {
    db.prepare('INSERT INTO im_channels (id, type, name, enabled, status, credentials, default_workspace_id, default_model, dm_policy, group_policy, trigger_mode, group_whitelist, sender_whitelist, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)').run(
        row.id, row.type, row.name || row.id, row.enabled, row.status === 'disconnected' ? 'not_configured' : row.status,
        row.credentials || '{}', row.default_workspace_id || '', row.default_model || '', row.dm_policy || 'open', row.group_policy || 'open', row.trigger_mode || 'mention',
        row.group_whitelist || '[]', row.sender_whitelist || '[]', row.created_at, row.updated_at
    )
  }
  // NOTE: Old seed statements removed — channels are now created on demand via POST /api/im-channels
  // Cleanup: delete legacy seed rows where id equals the platform type (not UUID) db.prepare("DELETE FROM im_channels WHERE id = type AND type IN ('feishu','telegram','discord','weixin','dingtalk','wecom')").run()


  if (bindingTableExists) {
    const insertBinding = db.prepare(`
      INSERT OR IGNORE INTO channel_bindings
        (id, channel_id, chat_id, chat_name, workspace, workspace_id, session_id, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `)
    for (const row of bindingRows) {
      insertBinding.run(
          row.id, row.channel_id, row.chat_id, row.chat_name || '', row.workspace || '',
          row.workspace_id || '', row.session_id || null, row.created_at, row.updated_at || row.created_at || ''
      )
    }
  }

  if (permissionTableExists) {
    const insertPermission = db.prepare(`
      INSERT OR IGNORE INTO channel_permission_links
        (id, channel_id, chat_id, agent_id, permission_mode, created_at)
      VALUES (?, ?, ?, ?, ?, ?)
    `)
    for (const row of permissionRows) {
      insertPermission.run(
          row.id, row.channel_id, row.chat_id, row.agent_id || null,
          row.permission_mode || 'confirm', row.created_at
      )
    }
  }
}

export function getDb(): Database.Database {
  if (globalThis.__forgeDb) {
    ensureHotfixMigrations(globalThis.__forgeDb)
    ensureAuthAndOwnershipSchema(globalThis.__forgeDb)
    return globalThis.__forgeDb
  }

  const db = new Database(getDbPath())
  db.pragma('journal_mode = WAL')
  db.pragma('foreign_keys = ON')

  db.exec(`
    CREATE TABLE IF NOT EXISTS sessions (
                                          id TEXT PRIMARY KEY,
                                          title TEXT NOT NULL DEFAULT 'New Session',
                                          workspace TEXT NOT NULL DEFAULT '',
                                          model TEXT NOT NULL DEFAULT 'claude-sonnet-4-6',
                                          status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS messages (
                                          id TEXT PRIMARY KEY,
                                          session_id TEXT NOT NULL,
                                          role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
      content TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
      );

    CREATE TABLE IF NOT EXISTS settings (
                                          key TEXT PRIMARY KEY,
                                          value TEXT NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, created_at);

    CREATE TABLE IF NOT EXISTS skills (
                                        id TEXT PRIMARY KEY,
                                        name TEXT NOT NULL,
                                        description TEXT NOT NULL DEFAULT '',
                                        scope TEXT NOT NULL DEFAULT 'workspace',
                                        enabled INTEGER NOT NULL DEFAULT 1,
                                        content TEXT NOT NULL DEFAULT '',
                                        created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS agents (
                                        id TEXT PRIMARY KEY,
                                        name TEXT NOT NULL,
                                        description TEXT NOT NULL DEFAULT '',
                                        model TEXT NOT NULL DEFAULT 'claude-sonnet-4-6',
                                        permission_mode TEXT NOT NULL DEFAULT 'confirm',
                                        is_main INTEGER NOT NULL DEFAULT 0,
                                        parent_id TEXT,
                                        enabled INTEGER NOT NULL DEFAULT 1,
                                        instructions TEXT NOT NULL DEFAULT '',
                                        soul TEXT NOT NULL DEFAULT '',
                                        identity TEXT NOT NULL DEFAULT '',
                                        tools_config TEXT NOT NULL DEFAULT '{}',
                                        created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (parent_id) REFERENCES agents(id) ON DELETE SET NULL
      );

    CREATE TABLE IF NOT EXISTS agent_skills (
                                              agent_id TEXT NOT NULL,
                                              skill_id TEXT NOT NULL,
                                              PRIMARY KEY (agent_id, skill_id),
      FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
      FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
      );

    CREATE TABLE IF NOT EXISTS mcp_servers (
                                             id TEXT PRIMARY KEY,
                                             name TEXT NOT NULL,
                                             protocol TEXT NOT NULL DEFAULT 'stdio',
                                             config TEXT NOT NULL DEFAULT '{}',
                                             enabled INTEGER NOT NULL DEFAULT 1,
                                             status TEXT NOT NULL DEFAULT 'disconnected',
                                             created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS im_channels (
                                             id TEXT PRIMARY KEY,
                                             type TEXT NOT NULL CHECK (type IN ('feishu', 'telegram', 'discord', 'weixin', 'dingtalk', 'wecom')),
      name TEXT NOT NULL DEFAULT '',
      enabled INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'not_configured' CHECK (status IN ('connected', 'disconnected', 'not_configured', 'error')),
      credentials TEXT NOT NULL DEFAULT '{}',
      default_workspace_id TEXT NOT NULL DEFAULT '',
      default_model TEXT NOT NULL DEFAULT '',
      dm_policy TEXT NOT NULL DEFAULT 'open',
      group_policy TEXT NOT NULL DEFAULT 'open',
      trigger_mode TEXT NOT NULL DEFAULT 'mention',
      group_whitelist TEXT NOT NULL DEFAULT '[]',
      sender_whitelist TEXT NOT NULL DEFAULT '[]',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS h5_pages (
                                         id TEXT PRIMARY KEY,
                                         workspace_id TEXT NOT NULL UNIQUE,
                                         slug TEXT NOT NULL UNIQUE,
                                         app_secrets TEXT NOT NULL DEFAULT '{}',
                                         default_model TEXT NOT NULL DEFAULT '',
                                         permission_mode TEXT NOT NULL DEFAULT 'full' CHECK (permission_mode IN ('confirm', 'full')),
                                         page_title TEXT NOT NULL DEFAULT '',
                                         assistant_name TEXT NOT NULL DEFAULT '',
                                         description TEXT NOT NULL DEFAULT '',
                                         welcome_message TEXT NOT NULL DEFAULT '',
                                         icon_emoji TEXT NOT NULL DEFAULT '',
                                         enabled INTEGER NOT NULL DEFAULT 1,
                                         created_at TEXT NOT NULL DEFAULT (datetime('now')),
                                         updated_at TEXT NOT NULL DEFAULT (datetime('now')),
                                         FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
    );
    CREATE UNIQUE INDEX IF NOT EXISTS idx_h5_pages_slug ON h5_pages(slug);
    CREATE INDEX IF NOT EXISTS idx_h5_pages_workspace ON h5_pages(workspace_id);

    CREATE TABLE IF NOT EXISTS cron_tasks (
                                            id TEXT PRIMARY KEY,
                                            name TEXT NOT NULL,
                                            schedule TEXT NOT NULL DEFAULT '',
                                            action TEXT NOT NULL DEFAULT '',
                                            action_type TEXT NOT NULL DEFAULT 'custom-prompt' CHECK (action_type IN ('run-agent', 'run-skill', 'custom-prompt')),
      agent_name TEXT NOT NULL DEFAULT '',
      skill_name TEXT NOT NULL DEFAULT '',
      workspace_id TEXT NOT NULL DEFAULT '',
      enabled INTEGER NOT NULL DEFAULT 1,
      is_heartbeat INTEGER NOT NULL DEFAULT 0,
      config TEXT NOT NULL DEFAULT '{}',
      last_run_at TEXT,
      last_run_result TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS task_executions (
                                                 id TEXT PRIMARY KEY,
                                                 task_id TEXT NOT NULL,
                                                 task_name TEXT NOT NULL,
                                                 result TEXT NOT NULL DEFAULT '',
                                                 status TEXT NOT NULL DEFAULT 'ok' CHECK (status IN ('ok', 'alert', 'error')),
      session_id TEXT NOT NULL DEFAULT '',
      executed_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (task_id) REFERENCES cron_tasks(id) ON DELETE CASCADE
      );

    CREATE INDEX IF NOT EXISTS idx_task_executions_task ON task_executions(task_id, executed_at DESC);

    CREATE TABLE IF NOT EXISTS hooks (
                                       id TEXT PRIMARY KEY,
                                       name TEXT NOT NULL,
                                       event TEXT NOT NULL DEFAULT 'pre_tool',
                                       tool_pattern TEXT NOT NULL DEFAULT '*',
                                       action TEXT NOT NULL DEFAULT 'log' CHECK (action IN ('shell', 'block', 'log')),
      command TEXT NOT NULL DEFAULT '',
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS workspaces (
                                            id TEXT PRIMARY KEY,
                                            path TEXT NOT NULL UNIQUE,
                                            last_opened_at TEXT NOT NULL DEFAULT (datetime('now')),
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS api_providers (
                                               id TEXT PRIMARY KEY,
                                               name TEXT NOT NULL,
                                               provider TEXT NOT NULL,
                                               api_key TEXT NOT NULL DEFAULT '',
                                               base_url TEXT NOT NULL DEFAULT '',
                                               model_name TEXT NOT NULL DEFAULT '',
                                               is_active INTEGER NOT NULL DEFAULT 0,
                                               status TEXT NOT NULL DEFAULT 'not_configured',
                                               status_error TEXT NOT NULL DEFAULT '',
                                               created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS channel_bindings (
                                                  id TEXT PRIMARY KEY,
                                                  channel_id TEXT NOT NULL,
                                                  chat_id TEXT NOT NULL,
                                                  chat_name TEXT NOT NULL DEFAULT '',
                                                  workspace TEXT NOT NULL DEFAULT '',
                                                  workspace_id TEXT NOT NULL DEFAULT '',
                                                  session_id TEXT DEFAULT NULL,
                                                  created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (channel_id) REFERENCES im_channels(id) ON DELETE CASCADE
      );
    CREATE UNIQUE INDEX IF NOT EXISTS idx_channel_bindings_channel_chat
      ON channel_bindings(channel_id, chat_id);

    CREATE TABLE IF NOT EXISTS channel_permission_links (
                                                          id TEXT PRIMARY KEY,
                                                          channel_id TEXT NOT NULL,
                                                          chat_id TEXT NOT NULL,
                                                          agent_id TEXT,
                                                          permission_mode TEXT NOT NULL DEFAULT 'confirm',
                                                          created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (channel_id) REFERENCES im_channels(id) ON DELETE CASCADE,
      FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL
      );

    CREATE TABLE IF NOT EXISTS marketplace_templates (
                                                       id TEXT PRIMARY KEY,
                                                       name TEXT NOT NULL,
                                                       created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    -- Seed API providers
    INSERT OR IGNORE INTO api_providers (id, name, provider) VALUES ('anthropic', 'Anthropic', 'anthropic');
    INSERT OR IGNORE INTO api_providers (id, name, provider) VALUES ('minimax', 'MiniMax', 'minimax');
    INSERT OR IGNORE INTO api_providers (id, name, provider) VALUES ('zhipu', 'GLM', 'zhipu');
    INSERT OR IGNORE INTO api_providers (id, name, provider) VALUES ('moonshot', 'Kimi', 'moonshot');
    INSERT OR IGNORE INTO api_providers (id, name, provider) VALUES ('qwen', 'Qwen', 'qwen');
    INSERT OR IGNORE INTO api_providers (id, name, provider) VALUES ('stt', 'STT 语音识别', 'stt');

    -- Seed heartbeat task (disabled by default — user enables in Schedule view)
    INSERT OR IGNORE INTO cron_tasks (id, name, is_heartbeat, schedule, action, config, enabled)
      VALUES ('heartbeat', 'Heartbeat', 1, '*/30 * * * *', '/heartbeat',
        '{"check_interval":"30m","notify_channel":"","notification_email":"","checklist_path":"HEARTBEAT.md"}', 0);
  `)

  // --- Migrations ---
  ensureAuthAndOwnershipSchema(db)

  // Migrate workspaces table: old schema had (name, emoji, description, is_default), new has (path, last_opened_at)
  const wsCols = db.prepare("PRAGMA table_info(workspaces)").all() as { name: string }[]
  const wsColNames = wsCols.map(c => c.name)

  if (wsColNames.includes('is_default') && !wsColNames.includes('path')) {
    // Old schema detected — migrate
    const oldRows = db.prepare('SELECT * FROM workspaces').all() as Record<string, unknown>[]
    db.exec('DROP TABLE workspaces')
    db.exec(`
      CREATE TABLE workspaces (
                                id TEXT PRIMARY KEY,
                                path TEXT NOT NULL UNIQUE,
                                last_opened_at TEXT NOT NULL DEFAULT (datetime('now')),
                                created_at TEXT NOT NULL DEFAULT (datetime('now'))
      )
    `)

    // Migrate default workspace with cwd as path
    const defaultWs = oldRows.find(r => r.is_default === 1)
    if (defaultWs) {
      db.prepare('INSERT INTO workspaces (id, path, created_at) VALUES (?, ?, ?)').run(
          defaultWs.id, process.cwd(), defaultWs.created_at as string
      )
    }
  }

  // Migrate sessions table: remove working_directory column if it exists
  const sessionCols = db.prepare("PRAGMA table_info(sessions)").all() as { name: string }[]
  const sessionColNames = sessionCols.map(c => c.name)

  if (sessionColNames.includes('working_directory')) {
    // Rebuild sessions table without working_directory
    const sessionRows = db.prepare('SELECT id, title, workspace, model, status, created_at, updated_at FROM sessions').all()
    db.exec('DROP TABLE sessions')
    db.exec(`
      CREATE TABLE sessions (
                              id TEXT PRIMARY KEY,
                              title TEXT NOT NULL DEFAULT 'New Session',
                              workspace TEXT NOT NULL DEFAULT '',
                              model TEXT NOT NULL DEFAULT 'claude-sonnet-4-6',
                              status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
                              created_at TEXT NOT NULL DEFAULT (datetime('now')),
                              updated_at TEXT NOT NULL DEFAULT (datetime('now'))
      )
    `)
    db.exec('CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, created_at)')
    const insertSession = db.prepare('INSERT INTO sessions (id, title, workspace, model, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)')
    for (const row of sessionRows as Record<string, unknown>[]) {
      insertSession.run(row.id, row.title, row.workspace, row.model, row.status, row.created_at, row.updated_at)
    }
  }

  // Migrate im_channels: old CHECK constraint didn't include 'disconnected'
  const imSchemaRow = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='im_channels'").get() as { sql: string } | undefined
  const needsImMigration = imSchemaRow && (!imSchemaRow.sql.includes("'disconnected'") || !imSchemaRow.sql.includes("'weixin'") || !imSchemaRow.sql.includes("'dingtalk'") || !imSchemaRow.sql.includes("'wecom'"))
  if (needsImMigration) {
    // Old schema missing status/channel type values — recreate table with updated constraints.
    migrateImChannelsTable(db)
  }

  // Migrate im_channels: relax default policies from pairing/allowlist to open
  // (only updates rows that still have the old restrictive defaults + empty whitelists)
  db.prepare("UPDATE im_channels SET dm_policy = 'open' WHERE dm_policy = 'pairing' AND sender_whitelist = '[]'").run()
  db.prepare("UPDATE im_channels SET group_policy = 'open' WHERE group_policy = 'allowlist' AND group_whitelist = '[]'").run()

  // Migrate im_channels: add multi-bot fields
  const imCols = db.prepare("PRAGMA table_info(im_channels)").all() as { name: string }[]
  const imColNames = imCols.map(c => c.name)
  if (!imColNames.includes('name')) {
    db.exec("ALTER TABLE im_channels ADD COLUMN name TEXT NOT NULL DEFAULT ''")
  }
  if (!imColNames.includes('default_workspace_id')) {
    db.exec("ALTER TABLE im_channels ADD COLUMN default_workspace_id TEXT NOT NULL DEFAULT ''")
  }
  if (!imColNames.includes('default_model')) {
    db.exec("ALTER TABLE im_channels ADD COLUMN default_model TEXT NOT NULL DEFAULT ''")
  }
  // Clean up legacy seed rows (old design: id='telegram' etc., new design: id=UUID)
  db.prepare("DELETE FROM im_channels WHERE id = type AND type IN ('feishu','telegram','discord','weixin','dingtalk','wecom')").run()

  // Migrate api_providers: add status + status_error columns
  const apCols = db.prepare("PRAGMA table_info(api_providers)").all() as { name: string }[]
  const apColNames = apCols.map(c => c.name)
  if (!apColNames.includes('status')) {
    db.exec("ALTER TABLE api_providers ADD COLUMN status TEXT NOT NULL DEFAULT 'not_configured'")
    db.exec("ALTER TABLE api_providers ADD COLUMN status_error TEXT NOT NULL DEFAULT ''")
  }

  // Migrate api_providers: add model_name column
  if (!apColNames.includes('model_name')) {
    db.exec("ALTER TABLE api_providers ADD COLUMN model_name TEXT NOT NULL DEFAULT ''")
  }

  // Migrate hooks table: remove CHECK constraint on event column to support new event types
  // (notification, stop, subagent_start, subagent_stop)
  // SQLite cannot ALTER CHECK constraints, so test if a new event value is accepted.
  try {
    // Try inserting a test row with a new event type; if CHECK rejects it, migrate
    db.exec("INSERT INTO hooks (id, name, event) VALUES ('__migration_test__', '__test__', 'notification')")
    db.exec("DELETE FROM hooks WHERE id = '__migration_test__'")
  } catch {
    // Old CHECK constraint blocks new event types — rebuild table without event CHECK
    const hookRows = db.prepare('SELECT * FROM hooks').all() as Record<string, unknown>[]
    db.exec('DROP TABLE hooks')
    db.exec(`
      CREATE TABLE hooks (
                           id TEXT PRIMARY KEY,
                           name TEXT NOT NULL,
                           event TEXT NOT NULL DEFAULT 'pre_tool',
                           tool_pattern TEXT NOT NULL DEFAULT '*',
                           action TEXT NOT NULL DEFAULT 'log' CHECK (action IN ('shell', 'block', 'log')),
        command TEXT NOT NULL DEFAULT '',
        enabled INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL DEFAULT (datetime('now'))
      )
    `)
    const insertHook = db.prepare(
        'INSERT INTO hooks (id, name, event, tool_pattern, action, command, enabled, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
    )
    for (const row of hookRows) {
      insertHook.run(row.id, row.name, row.event, row.tool_pattern, row.action, row.command, row.enabled, row.created_at)
    }
  }

  // Migrate cron_tasks: add workspace_id, action_type, agent_name, skill_name columns
  const cronCols = db.prepare("PRAGMA table_info(cron_tasks)").all() as { name: string }[]
  const cronColNames = cronCols.map(c => c.name)
  if (!cronColNames.includes('workspace_id')) {
    db.exec("ALTER TABLE cron_tasks ADD COLUMN workspace_id TEXT NOT NULL DEFAULT ''")
  }
  if (!cronColNames.includes('action_type')) {
    db.exec("ALTER TABLE cron_tasks ADD COLUMN action_type TEXT NOT NULL DEFAULT 'custom-prompt'")
  }
  if (!cronColNames.includes('agent_name')) {
    db.exec("ALTER TABLE cron_tasks ADD COLUMN agent_name TEXT NOT NULL DEFAULT ''")
  }
  if (!cronColNames.includes('skill_name')) {
    db.exec("ALTER TABLE cron_tasks ADD COLUMN skill_name TEXT NOT NULL DEFAULT ''")
  }

  // Migrate task_executions: add session_id column
  const teCols = db.prepare("PRAGMA table_info(task_executions)").all() as { name: string }[]
  const teColNames = teCols.map(c => c.name)
  if (!teColNames.includes('session_id')) {
    db.exec("ALTER TABLE task_executions ADD COLUMN session_id TEXT NOT NULL DEFAULT ''")
  }

  // Migrate channel_bindings: add session_id column for per-session binding
  const cbCols = db.prepare("PRAGMA table_info(channel_bindings)").all() as { name: string }[]
  const cbColNames = cbCols.map(c => c.name)
  if (!cbColNames.includes('session_id')) {
    db.exec("ALTER TABLE channel_bindings ADD COLUMN session_id TEXT DEFAULT NULL")
  }
  if (!cbColNames.includes('workspace_id')) {
    db.exec("ALTER TABLE channel_bindings ADD COLUMN workspace_id TEXT NOT NULL DEFAULT ''")
  }
  if (!cbColNames.includes('updated_at')) {
    db.exec("ALTER TABLE channel_bindings ADD COLUMN updated_at TEXT NOT NULL DEFAULT ''")
  }
  db.prepare("UPDATE channel_bindings SET updated_at = created_at WHERE updated_at = ''").run()
  db.prepare("UPDATE channel_bindings SET workspace_id = workspace WHERE workspace_id = '' AND workspace != ''").run()
  db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_channel_bindings_channel_chat ON channel_bindings(channel_id, chat_id)')

  // Migrate sessions: add permission_mode column (per-session override, like model)
  if (!sessionColNames.includes('permission_mode')) {
    db.exec("ALTER TABLE sessions ADD COLUMN permission_mode TEXT NOT NULL DEFAULT ''")
  }

  // Phase 0: 引擎标识字段（多引擎路由）。旧数据回退 'claude'，行为零变化。
  if (!sessionColNames.includes('backend')) {
    db.exec("ALTER TABLE sessions ADD COLUMN backend TEXT NOT NULL DEFAULT 'claude'")
  }

  // IM Bridge Layer 5 tables: outbound refs, audit logs, dedup
  db.exec(`
    CREATE TABLE IF NOT EXISTS channel_outbound_refs (
                                                       id TEXT PRIMARY KEY,
                                                       channel_type TEXT NOT NULL,
                                                       chat_id TEXT NOT NULL,
                                                       internal_id TEXT NOT NULL,
                                                       platform_msg_id TEXT NOT NULL,
                                                       created_at TEXT NOT NULL DEFAULT (datetime('now'))
      );
    CREATE INDEX IF NOT EXISTS idx_outbound_refs_lookup
      ON channel_outbound_refs(channel_type, chat_id, internal_id);

    CREATE TABLE IF NOT EXISTS channel_audit_logs (
                                                    id TEXT PRIMARY KEY,
                                                    channel_type TEXT NOT NULL,
                                                    chat_id TEXT NOT NULL,
                                                    action TEXT NOT NULL,
                                                    details TEXT NOT NULL DEFAULT '{}',
                                                    created_at TEXT NOT NULL DEFAULT (datetime('now'))
      );
    CREATE INDEX IF NOT EXISTS idx_audit_logs_time
      ON channel_audit_logs(created_at DESC);

    CREATE TABLE IF NOT EXISTS channel_dedupe (
                                                hash TEXT PRIMARY KEY,
                                                channel_type TEXT NOT NULL,
                                                chat_id TEXT NOT NULL,
                                                created_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

    CREATE TABLE IF NOT EXISTS weixin_context_tokens (
                                                       id TEXT PRIMARY KEY,
                                                       channel_id TEXT NOT NULL,
                                                       peer_user_id TEXT NOT NULL,
                                                       context_token TEXT NOT NULL,
                                                       updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (channel_id) REFERENCES im_channels(id) ON DELETE CASCADE,
      UNIQUE(channel_id, peer_user_id)
      );
    CREATE INDEX IF NOT EXISTS idx_weixin_ctx_token
      ON weixin_context_tokens(channel_id, peer_user_id);

    -- Pairing requests for DM policy = 'pairing'
    CREATE TABLE IF NOT EXISTS im_pairing_requests (
      id TEXT PRIMARY KEY,
      channel_id TEXT NOT NULL,
      sender_id TEXT NOT NULL,
      code TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'expired')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      expires_at TEXT NOT NULL DEFAULT (datetime('now', '+10 minutes')),
      verified_at TEXT,
      FOREIGN KEY (channel_id) REFERENCES im_channels(id) ON DELETE CASCADE
    );
    CREATE INDEX IF NOT EXISTS idx_pairing_channel_sender
      ON im_pairing_requests(channel_id, sender_id, status);

    -- Persistent long-poll cursor (get_updates_buf) per WeChat channel.
    -- Without persistence, restarting the process resets the cursor and the
    -- WeChat server may either replay history or skip messages received during
    -- downtime. Saved after every successful poll, loaded on adapter start.
    CREATE TABLE IF NOT EXISTS weixin_sync_cursors (
      channel_id TEXT PRIMARY KEY,
      get_updates_buf TEXT NOT NULL,
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (channel_id) REFERENCES im_channels(id) ON DELETE CASCADE
    );
  `)

  // Migrate h5_pages: add page metadata fields (title, description, welcome message, icon)
  const h5Cols = db.prepare("PRAGMA table_info(h5_pages)").all() as { name: string }[]
  const h5ColNames = h5Cols.map(c => c.name)
  if (!h5ColNames.includes('page_title')) {
    db.exec("ALTER TABLE h5_pages ADD COLUMN page_title TEXT NOT NULL DEFAULT ''")
  }
  if (!h5ColNames.includes('description')) {
    db.exec("ALTER TABLE h5_pages ADD COLUMN description TEXT NOT NULL DEFAULT ''")
  }
  if (!h5ColNames.includes('welcome_message')) {
    db.exec("ALTER TABLE h5_pages ADD COLUMN welcome_message TEXT NOT NULL DEFAULT ''")
  }
  if (!h5ColNames.includes('icon_emoji')) {
    db.exec("ALTER TABLE h5_pages ADD COLUMN icon_emoji TEXT NOT NULL DEFAULT ''")
  }
  if (!h5ColNames.includes('assistant_name')) {
    db.exec("ALTER TABLE h5_pages ADD COLUMN assistant_name TEXT NOT NULL DEFAULT ''")
  }

  ensureHotfixMigrations(db)
  ensureAuthAndOwnershipSchema(db)
  syncMcpFromClaudeCode(db)

  globalThis.__forgeDb = db
  return db
}

/**
 * Read Claude Code's MCP server config from ~/.claude.json and auto-create
 * any servers that don't already exist in Forge's database.
 */
function syncMcpFromClaudeCode(db: Database.Database) {
  try {
    const firstUser = db.prepare('SELECT id FROM user ORDER BY createdAt ASC LIMIT 1').get() as { id: string } | undefined
    if (!firstUser) return

    const claudeJsonPath = path.join(os.homedir(), '.claude.json')
    const raw = fs.readFileSync(claudeJsonPath, 'utf-8')
    const data = JSON.parse(raw)
    const mcpServers = data.mcpServers
    if (!mcpServers || typeof mcpServers !== 'object') return

    const insert = db.prepare(
      'INSERT OR IGNORE INTO mcp_servers (id, name, protocol, config, enabled, status, user_id) VALUES (?, ?, ?, ?, 1, ?, ?)'
    )

    for (const [name, serverCfg] of Object.entries(mcpServers)) {
      const cfg = serverCfg as Record<string, unknown>
      const protocol = (cfg.type as string) || 'stdio'

      // Build config matching Forge's expected format
      const forgeConfig: Record<string, unknown> = {}
      if (protocol === 'stdio') {
        if (cfg.command) forgeConfig.command = cfg.command
        if (cfg.args) forgeConfig.args = cfg.args
        if (cfg.env && Object.keys(cfg.env as object).length > 0) forgeConfig.env = cfg.env
      } else {
        if (cfg.url) forgeConfig.url = cfg.url
        if (cfg.headers) forgeConfig.headers = cfg.headers
      }

      const existing = db.prepare('SELECT id FROM mcp_servers WHERE user_id = ? AND (name = ? OR (protocol = ? AND config = ?))')
        .get(firstUser.id, name, protocol, JSON.stringify(forgeConfig)) as { id: string } | undefined
      if (existing) continue

      const status = testMcpConnection(protocol, forgeConfig)
      insert.run(crypto.randomUUID(), name, protocol, JSON.stringify(forgeConfig), status, firstUser.id)
    }
  } catch {
    // ~/.claude.json doesn't exist or isn't valid — skip silently
  }
}

/**
 * Quick connectivity check for an MCP server config.
 * stdio: check if command binary exists. SSE/HTTP: skip (async fetch not available here).
 */
function testMcpConnection(protocol: string, config: Record<string, unknown>): string {
  if (protocol === 'stdio' && config.command) {
    try {
      const mainCmd = String(config.command).split(/\s+/)[0]
      // Check if it's an absolute path that exists, or use `which`
      if (mainCmd.startsWith('/')) {
        if (fs.existsSync(mainCmd)) return 'connected'
      } else {
        execFileSync('/usr/bin/which', [mainCmd], { timeout: 3000, encoding: 'utf-8' })
        return 'connected'
      }
    } catch { /* command not found */ }
    return 'error'
  }
  return 'disconnected'
}