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

import { getDb } from './index'

export type ReferrerMode = 'self' | 'follow_target'

export interface WatchedAddress {
  id: number
  address: string
  label: string | null
  enabled: number
  copy_multiplier: number | null
  copy_max_usd: number | null
  referrer_mode: ReferrerMode
  created_at: string
}

export interface PositionMapping {
  id: number
  target_address: string
  target_nft_mint: string
  target_personal_position: string | null
  our_nft_mint: string | null
  pool_id: string
  tick_lower: number
  tick_upper: number
  status: string
  pnl_usd: string | null
  pnl_percent: string | null
  apr: string | null
  bonus_usd: string | null
  earned_usd: string | null
  liquidity_usd: string | null
  created_at: string
  updated_at: string
}

export interface CopyHistoryRow {
  id: number
  target_address: string
  target_tx_sig: string
  operation: string
  target_nft_mint: string | null
  our_nft_mint: string | null
  our_tx_sig: string | null
  pool_id: string | null
  target_amount_a: string | null
  target_amount_b: string | null
  our_amount_a: string | null
  our_amount_b: string | null
  status: string
  error_message: string | null
  swap_tx_sig: string | null
  created_at: string
}

// Watched Addresses
export function getWatchedAddresses(): WatchedAddress[] {
  return getDb()
    .prepare('SELECT * FROM watched_addresses ORDER BY created_at DESC')
    .all() as WatchedAddress[]
}

export function getEnabledWatchedAddresses(): WatchedAddress[] {
  return getDb()
    .prepare('SELECT * FROM watched_addresses WHERE enabled = 1')
    .all() as WatchedAddress[]
}

export function addWatchedAddress(address: string, label?: string) {
  return getDb()
    .prepare('INSERT OR IGNORE INTO watched_addresses (address, label) VALUES (?, ?)')
    .run(address, label || null)
}

export function removeWatchedAddress(id: number) {
  return getDb().prepare('DELETE FROM watched_addresses WHERE id = ?').run(id)
}

export function toggleWatchedAddress(id: number, enabled: boolean) {
  return getDb()
    .prepare('UPDATE watched_addresses SET enabled = ? WHERE id = ?')
    .run(enabled ? 1 : 0, id)
}

export function getWatchedAddressByAddress(address: string): WatchedAddress | undefined {
  return getDb().prepare('SELECT * FROM watched_addresses WHERE address = ?').get(address) as
    | WatchedAddress
    | undefined
}

export function updateWatchedAddressSettings(
  id: number,
  settings: {
    copyMultiplier?: number | null
    copyMaxUsd?: number | null
    referrerMode?: ReferrerMode
  },
) {
  const sets: string[] = []
  const values: unknown[] = []

  if (settings.copyMultiplier !== undefined) {
    sets.push('copy_multiplier = ?')
    values.push(settings.copyMultiplier)
  }
  if (settings.copyMaxUsd !== undefined) {
    sets.push('copy_max_usd = ?')
    values.push(settings.copyMaxUsd)
  }
  if (settings.referrerMode !== undefined) {
    sets.push('referrer_mode = ?')
    values.push(settings.referrerMode)
  }

  if (sets.length === 0) return
  values.push(id)

  return getDb()
    .prepare(`UPDATE watched_addresses SET ${sets.join(', ')} WHERE id = ?`)
    .run(...values)
}

// Position Mappings
export function getPositionMappings(status?: string): PositionMapping[] {
  if (status) {
    return getDb()
      .prepare('SELECT * FROM position_mappings WHERE status = ? ORDER BY created_at DESC')
      .all(status) as PositionMapping[]
  }
  return getDb()
    .prepare('SELECT * FROM position_mappings ORDER BY created_at DESC')
    .all() as PositionMapping[]
}

export function getPositionMappingByTargetNft(targetNftMint: string): PositionMapping | undefined {
  return getDb()
    .prepare('SELECT * FROM position_mappings WHERE target_nft_mint = ?')
    .get(targetNftMint) as PositionMapping | undefined
}

export function getPositionMappingByTargetPosition(
  targetPersonalPosition: string,
): PositionMapping | undefined {
  return getDb()
    .prepare('SELECT * FROM position_mappings WHERE target_personal_position = ?')
    .get(targetPersonalPosition) as PositionMapping | undefined
}

export function upsertPositionMapping(params: {
  targetAddress: string
  targetNftMint: string
  targetPersonalPosition?: string
  ourNftMint?: string
  poolId: string
  tickLower: number
  tickUpper: number
  status?: string
}) {
  return getDb()
    .prepare(
      `INSERT INTO position_mappings (target_address, target_nft_mint, target_personal_position, our_nft_mint, pool_id, tick_lower, tick_upper, status)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(target_nft_mint) DO UPDATE SET
         our_nft_mint = COALESCE(excluded.our_nft_mint, our_nft_mint),
         status = COALESCE(excluded.status, status),
         updated_at = datetime('now')`,
    )
    .run(
      params.targetAddress,
      params.targetNftMint,
      params.targetPersonalPosition || null,
      params.ourNftMint || null,
      params.poolId,
      params.tickLower,
      params.tickUpper,
      params.status || 'active',
    )
}

export function updatePositionMappingStatus(targetNftMint: string, status: string) {
  return getDb()
    .prepare(
      "UPDATE position_mappings SET status = ?, updated_at = datetime('now') WHERE target_nft_mint = ?",
    )
    .run(status, targetNftMint)
}

export function getPositionMappingById(id: number): PositionMapping | undefined {
  return getDb().prepare('SELECT * FROM position_mappings WHERE id = ?').get(id) as
    | PositionMapping
    | undefined
}

export function deletePositionMapping(id: number) {
  return getDb().prepare('DELETE FROM position_mappings WHERE id = ?').run(id)
}

export function updatePositionMappingSyncData(
  ourNftMint: string,
  data: {
    pnlUsd?: string
    pnlPercent?: string
    apr?: string
    bonusUsd?: string
    earnedUsd?: string
    liquidityUsd?: string
  },
) {
  return getDb()
    .prepare(
      `UPDATE position_mappings SET
        pnl_usd = ?, pnl_percent = ?, apr = ?, bonus_usd = ?, earned_usd = ?, liquidity_usd = ?,
        updated_at = datetime('now')
       WHERE our_nft_mint = ?`,
    )
    .run(
      data.pnlUsd || null,
      data.pnlPercent || null,
      data.apr || null,
      data.bonusUsd || null,
      data.earnedUsd || null,
      data.liquidityUsd || null,
      ourNftMint,
    )
}

export function getActivePositionNftMints(): string[] {
  const rows = getDb()
    .prepare("SELECT our_nft_mint FROM position_mappings WHERE status = 'active' AND our_nft_mint IS NOT NULL")
    .all() as { our_nft_mint: string }[]
  return rows.map((r) => r.our_nft_mint)
}

// Copy History
export function addCopyHistory(params: {
  targetAddress: string
  targetTxSig: string
  operation: string
  targetNftMint?: string
  poolId?: string
  targetAmountA?: string
  targetAmountB?: string
  status?: string
}): number {
  const result = getDb()
    .prepare(
      `INSERT INTO copy_history (target_address, target_tx_sig, operation, target_nft_mint, pool_id, target_amount_a, target_amount_b, status)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    )
    .run(
      params.targetAddress,
      params.targetTxSig,
      params.operation,
      params.targetNftMint || null,
      params.poolId || null,
      params.targetAmountA || null,
      params.targetAmountB || null,
      params.status || 'pending',
    )
  return result.lastInsertRowid as number
}

export function updateCopyHistory(
  id: number,
  updates: {
    ourNftMint?: string
    ourTxSig?: string
    ourAmountA?: string
    ourAmountB?: string
    status?: string
    errorMessage?: string
    swapTxSig?: string
  },
) {
  const sets: string[] = []
  const values: unknown[] = []

  if (updates.ourNftMint !== undefined) {
    sets.push('our_nft_mint = ?')
    values.push(updates.ourNftMint)
  }
  if (updates.ourTxSig !== undefined) {
    sets.push('our_tx_sig = ?')
    values.push(updates.ourTxSig)
  }
  if (updates.ourAmountA !== undefined) {
    sets.push('our_amount_a = ?')
    values.push(updates.ourAmountA)
  }
  if (updates.ourAmountB !== undefined) {
    sets.push('our_amount_b = ?')
    values.push(updates.ourAmountB)
  }
  if (updates.status !== undefined) {
    sets.push('status = ?')
    values.push(updates.status)
  }
  if (updates.errorMessage !== undefined) {
    sets.push('error_message = ?')
    values.push(updates.errorMessage)
  }
  if (updates.swapTxSig !== undefined) {
    sets.push('swap_tx_sig = ?')
    values.push(updates.swapTxSig)
  }

  if (sets.length === 0) return

  values.push(id)
  return getDb()
    .prepare(`UPDATE copy_history SET ${sets.join(', ')} WHERE id = ?`)
    .run(...values)
}

export function getCopyHistory(limit = 50, offset = 0): CopyHistoryRow[] {
  return getDb()
    .prepare('SELECT * FROM copy_history ORDER BY created_at DESC LIMIT ? OFFSET ?')
    .all(limit, offset) as CopyHistoryRow[]
}

// Settings
export function getSetting(key: string): string | undefined {
  const row = getDb().prepare('SELECT value FROM settings WHERE key = ?').get(key) as
    | { value: string }
    | undefined
  return row?.value
}

export function setSetting(key: string, value: string) {
  return getDb()
    .prepare(
      `INSERT INTO settings (key, value) VALUES (?, ?)
       ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = datetime('now')`,
    )
    .run(key, value)
}

export function getAllSettings(): Record<string, string> {
  const rows = getDb().prepare('SELECT key, value FROM settings').all() as {
    key: string
    value: string
  }[]
  return Object.fromEntries(rows.map((r) => [r.key, r.value]))
}

// Check if TX already processed
export function isTxProcessed(txSig: string): boolean {
  const row = getDb().prepare('SELECT id FROM copy_history WHERE target_tx_sig = ?').get(txSig) as
    | { id: number }
    | undefined
  return !!row
}