magik/server/db.mjs

/**
 * Read-only card index access.
 *
 * Everything the browser needs to browse and filter Commander-legal cards is
 * answered from `data/cards.db`. The database is built by
 * `scripts/build-cards.mjs` and is never written to at runtime.
 */

import { DatabaseSync } from 'node:sqlite'
import { existsSync } from 'node:fs'
import { resolve, dirname } from 'node:path'
import { fileURLToPath } from 'node:url'

const ROOT = resolve(dirname(fileURLToPath(import.meta.url)), '..')
const DB_PATH = process.env.CARDS_DB ?? resolve(ROOT, 'data/cards.db')

let db = null

export function cards() {
  if (!db) {
    if (!existsSync(DB_PATH)) {
      throw new Error(
        `card index missing at ${DB_PATH} — run \`pnpm cards:build\` to create it`,
      )
    }
    db = new DatabaseSync(DB_PATH, { readOnly: true })
  }
  return db
}

const CARD_COLUMNS = `
  id, name, mana_cost, cmc, type_line, oracle_text, color_identity, colors,
  rarity, power, toughness, loyalty, layout, faces, released_at, artist,
  set_name, set_code, commander_legality, commander_eligible, is_partner,
  is_basic_land, legal_formats, image_small, image_normal, image_large,
  image_png, image_art_crop, image_board_crop
`

/** Shapes a database row into the JSON the client consumes. */
function hydrate(row) {
  if (!row) return null
  return {
    id: row.id,
    name: row.name,
    manaCost: row.mana_cost,
    cmc: row.cmc,
    typeLine: row.type_line,
    oracleText: row.oracle_text,
    colorIdentity: row.color_identity,
    colors: row.colors,
    rarity: row.rarity,
    power: row.power,
    toughness: row.toughness,
    loyalty: row.loyalty,
    faces: row.faces ? JSON.parse(row.faces) : undefined,
    releasedAt: row.released_at,
    artist: row.artist,
    setName: row.set_name,
    setCode: row.set_code,
    bannedInCommander: row.commander_legality === 'banned',
    commanderEligible: row.commander_eligible === 1,
    isPartner: row.is_partner === 1,
    isBasicLand: row.is_basic_land === 1,
    legalFormats: JSON.parse(row.legal_formats),
    image: {
      small: row.image_small,
      normal: row.image_normal,
      large: row.image_large,
      png: row.image_png,
      artCrop: row.image_art_crop,
      boardCrop: row.image_board_crop,
    },
  }
}

const SORTS = {
  name: 'name COLLATE NOCASE ASC',
  cmc: 'cmc ASC, name COLLATE NOCASE ASC',
  '-cmc': 'cmc DESC, name COLLATE NOCASE ASC',
  '-name': 'name COLLATE NOCASE DESC',
  released: 'released_at DESC, name COLLATE NOCASE ASC',
  '-released': 'released_at ASC, name COLLATE NOCASE ASC',
  rarity: `CASE rarity
      WHEN 'mythic' THEN 0 WHEN 'rare' THEN 1 WHEN 'uncommon' THEN 2 ELSE 3 END,
    name COLLATE NOCASE ASC`,
}

const RARITIES = new Set(['common', 'uncommon', 'rare', 'mythic'])
const SUPER_TYPES = new Set(['creature', 'planeswalker', 'artifact', 'enchantment', 'land', 'battle'])
const TYPES = new Set(['instant', 'sorcery', 'adventure', 'tribal', 'conspiracy', 'phenomenon', 'plane', 'scheme', 'vanguard', 'conspiracy'])
const COLORS = new Set(['W', 'U', 'B', 'R', 'G'])
const LEGACIES = new Set(['legal', 'banned', 'not_legal'])

/** Escapes a user string for use inside a LIKE pattern. */
function escapeLike(text) {
  return text.replace(/[\\%_]/g, (c) => `\\${c}`)
}

function likePattern(text) {
  return `%${escapeLike(text)}%`
}

/**
 * Translates validated query parameters into a WHERE clause.
 *
 * Every value reaching SQL goes through this function, so parameter binding
 * stays the only place user data meets the statement.
 */
function buildFilters(q) {
  const where = []
  const params = []
  // An exact name beats a name prefix, which beats a substring or rules-text
  // hit, so typing "sol" puts Sol Ring first rather than the alphabetically
  // earlier Sol Grail or the "Absolutes" a substring search would surface.
  let rank = null
  let rankParams = []

  const text = (q.get('q') ?? '').trim()
  if (text !== '') {
    where.push(`(
      name LIKE ? ESCAPE '\\' COLLATE NOCASE
      OR name LIKE ? ESCAPE '\\' COLLATE NOCASE
      OR oracle_text LIKE ? ESCAPE '\\' COLLATE NOCASE
    )`)
    params.push(`${escapeLike(text)}%`, likePattern(text), likePattern(text))
    rank = `CASE WHEN name = ? COLLATE NOCASE THEN 0
             WHEN name LIKE ? ESCAPE '\\' COLLATE NOCASE THEN 1
             WHEN name LIKE ? ESCAPE '\\' COLLATE NOCASE THEN 2
             ELSE 3 END`
    rankParams = [text, `${escapeLike(text)}%`, likePattern(text)]
  }

  const colors = (q.get('colors') ?? '').toUpperCase().split('').filter((c) => COLORS.has(c))
  if (colors.length > 0) {
    // A card is a match only if it uses no colour outside the selection:
    // that is the colour-identity containment a Commander deck needs.
    for (const c of 'WUBRG') {
      if (colors.includes(c)) continue
      where.push(`(color_identity = '' OR instr(color_identity, ?) = 0)`)
      params.push(c)
    }
  }

  const supertypes = (q.get('type') ?? '').toLowerCase().split('+').filter(Boolean)
  for (const t of supertypes) {
    if (SUPER_TYPES.has(t)) {
      where.push(`instr(lower(type_line), ?) > 0`)
      params.push(t)
    }
  }

  const types = (q.get('subtype') ?? '').toLowerCase().split('+').filter(Boolean)
  for (const t of types) {
    if (TYPES.has(t)) {
      where.push(`instr(lower(type_line), ?) > 0`)
      params.push(t)
    }
  }

  const min = Number.parseInt(q.get('cmcMin') ?? '', 10)
  if (Number.isFinite(min)) {
    where.push('cmc >= ?')
    params.push(min)
  }
  const max = Number.parseInt(q.get('cmcMax') ?? '', 10)
  if (Number.isFinite(max)) {
    where.push('cmc <= ?')
    params.push(max)
  }

  const rarities = (q.get('rarity') ?? '').toLowerCase().split('+').filter((r) => RARITIES.has(r))
  for (const r of rarities) {
    where.push('rarity = ?')
    params.push(r)
  }

  const identity = (q.get('identity') ?? '').toUpperCase().split('').filter((c) => COLORS.has(c))
  if (identity.length > 0) {
    for (const c of 'WUBRG') {
      if (identity.includes(c)) continue
      where.push(`(color_identity = '' OR instr(color_identity, ?) = 0)`)
      params.push(c)
    }
  }

  if (q.get('commander') === '1') where.push('commander_eligible = 1')
  if (q.get('partners') === '1') where.push('is_partner = 1')
  if (q.get('basic') === '1') where.push('is_basic_land = 1')

  // Commander's ban list is small but non-empty, and those cards are
  // otherwise indistinguishable in a browser. Default to the legal pool;
  // callers opt into `legality=banned` when they want to explain one.
  const legality = (q.get('legality') ?? 'legal').toLowerCase()
  if (LEGACIES.has(legality)) {
    where.push('commander_legality = ?')
    params.push(legality)
  }

  return { sql: where.length ? `WHERE ${where.join(' AND ')}` : '', params, rank, rankParams }
}

export function searchCards(q) {
  const { sql: where, params, rank, rankParams } = buildFilters(q)

  const limit = Math.min(Math.max(Number.parseInt(q.get('limit') ?? '60', 10) || 60, 1), 200)
  const page = Math.max(Number.parseInt(q.get('page') ?? '1', 10) || 1, 1)
  const sort = SORTS[q.get('sort') ?? ''] ?? SORTS.name
  // Relevance is a prefix on the ordering, not a replacement for it: an
  // explicit sort choice still decides the order within each rank.
  const order = rank === null ? sort : `${rank}, ${sort}`

  const total = cards().prepare(`SELECT count(*) AS n FROM cards ${where}`).get(...params).n

  const rows = cards()
    .prepare(`SELECT ${CARD_COLUMNS} FROM cards ${where} ORDER BY ${order} LIMIT ? OFFSET ?`)
    .all(...params, ...rankParams, limit, (page - 1) * limit)

  return { total, page, limit, pages: Math.ceil(total / limit), cards: rows.map(hydrate) }
}

export function getCard(id) {
  return hydrate(
    cards()
      .prepare(`SELECT ${CARD_COLUMNS} FROM cards WHERE id = ?`)
      .get(id) ?? null,
  )
}

export function getCardsByIds(ids) {
  if (ids.length === 0) return []
  const placeholders = ids.map(() => '?').join(',')
  const rows = cards()
    .prepare(`SELECT ${CARD_COLUMNS} FROM cards WHERE id IN (${placeholders})`)
    .all(...ids)
  const byId = new Map(rows.map((r) => [r.id, hydrate(r)]))
  return ids.map((id) => byId.get(id)).filter(Boolean)
}

/**
 * Resolves exact card names to records.
 *
 * Imported decklists and share links carry names, not ids; this turns them
 * into real cards in one round trip instead of one search per card.
 */
export function getCardsByNames(names) {
  const unique = [...new Set(names.filter((n) => typeof n === 'string' && n !== ''))]
  if (unique.length === 0) return []
  if (unique.length > 400) unique.length = 400

  const placeholders = unique.map(() => '?').join(',')
  const rows = cards()
    .prepare(`SELECT ${CARD_COLUMNS} FROM cards WHERE name IN (${placeholders})`)
    .all(...unique)

  const byName = new Map(rows.map((r) => [r.name, hydrate(r)]))
  return {
    cards: unique.map((name) => byName.get(name)).filter(Boolean),
    missing: unique.filter((name) => !byName.has(name)),
  }
}

export function cardCount() {
  return cards().prepare('SELECT count(*) AS n FROM cards').get().n
}