byreal-trade-copy/src/lib/db/schema.ts

import type Database from 'better-sqlite3'

export function initDb(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS watched_addresses (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      address TEXT NOT NULL UNIQUE,
      label TEXT,
      enabled INTEGER NOT NULL DEFAULT 1,
      copy_multiplier REAL,
      copy_max_usd REAL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS position_mappings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      target_address TEXT NOT NULL,
      target_nft_mint TEXT NOT NULL,
      target_personal_position TEXT,
      our_nft_mint TEXT,
      pool_id TEXT NOT NULL,
      tick_lower INTEGER NOT NULL,
      tick_upper INTEGER NOT NULL,
      status TEXT NOT NULL DEFAULT 'active',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(target_nft_mint)
    );

    CREATE TABLE IF NOT EXISTS copy_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      target_address TEXT NOT NULL,
      target_tx_sig TEXT NOT NULL,
      operation TEXT NOT NULL,
      target_nft_mint TEXT,
      our_nft_mint TEXT,
      our_tx_sig TEXT,
      pool_id TEXT,
      target_amount_a TEXT,
      target_amount_b TEXT,
      our_amount_a TEXT,
      our_amount_b TEXT,
      status TEXT NOT NULL DEFAULT 'pending',
      error_message TEXT,
      swap_tx_sig TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `)

  // Migration: add columns if they don't exist (for existing DBs)
  const cols = db.pragma('table_info(watched_addresses)') as { name: string }[]
  const colNames = new Set(cols.map((c) => c.name))
  if (!colNames.has('copy_multiplier')) {
    db.exec('ALTER TABLE watched_addresses ADD COLUMN copy_multiplier REAL')
  }
  if (!colNames.has('copy_max_usd')) {
    db.exec('ALTER TABLE watched_addresses ADD COLUMN copy_max_usd REAL')
  }
  if (!colNames.has('referrer_mode')) {
    db.exec("ALTER TABLE watched_addresses ADD COLUMN referrer_mode TEXT NOT NULL DEFAULT 'self'")
  }
}