bitspire/apps/machine/electron/state-store.ts

/**
 * SQLite State Store for ATM Cash Tracking
 *
 * Persists cassette inventory, cashbox state, and transaction history
 * across reboots. Runs in the Electron main process using better-sqlite3
 * (synchronous API, safe for single-process use).
 *
 * Database location: /var/lib/bitspire/state.db (production)
 *                    ./state.db (development)
 */

import Database from 'better-sqlite3'
import path from 'node:path'
import fs from 'node:fs'

let db: Database.Database | null = null

const SCHEMA_VERSION = '10'

function getDbPath(): string {
  const prodDir = '/var/lib/bitspire'
  if (fs.existsSync(prodDir)) {
    return path.join(prodDir, 'state.db')
  }
  // Development fallback: next to the app
  return path.join(process.cwd(), 'state.db')
}

/**
 * Initialize the database, creating tables if they don't exist.
 * Must be called once at app startup before any other functions.
 */
export function initDatabase(dbPath?: string): void {
  const resolvedPath = dbPath ?? getDbPath()

  // Ensure parent directory exists
  const dir = path.dirname(resolvedPath)
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true })
  }

  db = new Database(resolvedPath)

  // Enable WAL mode for better concurrent read performance
  db.pragma('journal_mode = WAL')

  // Create tables
  db.exec(`
    CREATE TABLE IF NOT EXISTS meta (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS cassettes (
      position INTEGER PRIMARY KEY,
      denomination INTEGER NOT NULL,
      count INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS cashbox (
      id INTEGER PRIMARY KEY CHECK (id = 1),
      total_bills INTEGER NOT NULL DEFAULT 0,
      total_fiat_cents INTEGER NOT NULL DEFAULT 0,
      last_emptied_at INTEGER
    );

    CREATE TABLE IF NOT EXISTS transactions (
      txid TEXT PRIMARY KEY,
      type TEXT NOT NULL CHECK (type IN ('cash_in', 'cash_out', 'manual_dispense')),
      fiat_cents INTEGER NOT NULL,
      sats INTEGER NOT NULL,
      fee_sats INTEGER NOT NULL DEFAULT 0,
      fee_fraction REAL NOT NULL DEFAULT 0,
      exchange_rate REAL NOT NULL DEFAULT 0,
      currency TEXT NOT NULL DEFAULT 'GTQ',
      status TEXT NOT NULL DEFAULT 'complete',
      error TEXT,
      remediated_by TEXT,
      created_at INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS transaction_bills (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      txid TEXT NOT NULL REFERENCES transactions(txid),
      denomination INTEGER NOT NULL,
      count INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS cassette_bills (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      txid TEXT NOT NULL REFERENCES transactions(txid),
      name TEXT NOT NULL,
      position INTEGER NOT NULL,
      denomination INTEGER NOT NULL,
      provisioned INTEGER NOT NULL DEFAULT 0,
      dispensed INTEGER NOT NULL DEFAULT 0,
      rejected INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS operator_commands (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      command TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      result TEXT,
      created_at INTEGER NOT NULL,
      completed_at INTEGER
    );

    CREATE TABLE IF NOT EXISTS fee_config (
      id INTEGER PRIMARY KEY CHECK (id = 1),
      cash_in_fee_fraction REAL NOT NULL,
      cash_out_fee_fraction REAL NOT NULL,
      schema_version INTEGER NOT NULL DEFAULT 1,
      event_created_at INTEGER NOT NULL,
      applied_at INTEGER NOT NULL
    );
  `)

  // Seed meta + cashbox if first run, or run migrations
  const existing = db.prepare('SELECT value FROM meta WHERE key = ?').get('schema_version') as
    | { value: string }
    | undefined
  if (!existing) {
    db.prepare('INSERT INTO meta (key, value) VALUES (?, ?)').run('schema_version', SCHEMA_VERSION)
  } else if (existing.value === '1') {
    // Migration v1 → v2: add fee columns to transactions
    db.exec(`
      ALTER TABLE transactions ADD COLUMN fee_sats INTEGER NOT NULL DEFAULT 0;
      ALTER TABLE transactions ADD COLUMN fee_percent REAL NOT NULL DEFAULT 0;
    `)
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('2', 'schema_version')
    console.log('[StateStore] Migrated schema v1 → v2 (added fee columns)')
    existing.value = '2'
  }

  if (existing && existing.value === '2') {
    // Migration v2 → v3: add exchange rate and currency
    db.exec(`
      ALTER TABLE transactions ADD COLUMN exchange_rate REAL NOT NULL DEFAULT 0;
      ALTER TABLE transactions ADD COLUMN currency TEXT NOT NULL DEFAULT 'GTQ';
    `)
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('3', 'schema_version')
    console.log('[StateStore] Migrated schema v2 → v3 (added exchange_rate, currency)')
    existing.value = '3'
  }

  if (existing && existing.value === '3') {
    // Migration v3 → v4: add status/error to transactions, cassette_bills table
    db.exec(`
      ALTER TABLE transactions ADD COLUMN status TEXT NOT NULL DEFAULT 'complete';
      ALTER TABLE transactions ADD COLUMN error TEXT;

      CREATE TABLE IF NOT EXISTS cassette_bills (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        txid TEXT NOT NULL REFERENCES transactions(txid),
        name TEXT NOT NULL,
        position INTEGER NOT NULL,
        denomination INTEGER NOT NULL,
        provisioned INTEGER NOT NULL DEFAULT 0,
        dispensed INTEGER NOT NULL DEFAULT 0,
        rejected INTEGER NOT NULL DEFAULT 0
      );
    `)
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('4', 'schema_version')
    console.log('[StateStore] Migrated schema v3 → v4 (added status, error, cassette_bills)')
    existing.value = '4'
  }

  if (existing && existing.value === '4') {
    // Migration v4 → v5: add manual_dispense type and remediated_by column
    // SQLite cannot ALTER CHECK constraints, so recreate the table.
    // Disable FK constraints during migration to avoid errors from
    // transaction_bills/cassette_bills referencing transactions(txid).
    db.pragma('foreign_keys = OFF')
    db.exec(`
      DROP TABLE IF EXISTS transactions_new;
      CREATE TABLE transactions_new (
        txid TEXT PRIMARY KEY,
        type TEXT NOT NULL CHECK (type IN ('cash_in', 'cash_out', 'manual_dispense')),
        fiat_cents INTEGER NOT NULL,
        sats INTEGER NOT NULL,
        fee_sats INTEGER NOT NULL DEFAULT 0,
        fee_percent REAL NOT NULL DEFAULT 0,
        exchange_rate REAL NOT NULL DEFAULT 0,
        currency TEXT NOT NULL DEFAULT 'GTQ',
        status TEXT NOT NULL DEFAULT 'complete',
        error TEXT,
        remediated_by TEXT,
        created_at INTEGER NOT NULL
      );
      INSERT INTO transactions_new SELECT txid, type, fiat_cents, sats, fee_sats, fee_percent,
        exchange_rate, currency, status, error, NULL, created_at FROM transactions;
      DROP TABLE transactions;
      ALTER TABLE transactions_new RENAME TO transactions;
    `)
    db.pragma('foreign_keys = ON')
    db.exec(`
      CREATE TABLE IF NOT EXISTS operator_commands (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        command TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        result TEXT,
        created_at INTEGER NOT NULL,
        completed_at INTEGER
      );
    `)
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('5', 'schema_version')
    console.log(
      '[StateStore] Migrated schema v4 → v5 (added manual_dispense type, remediated_by, operator_commands)'
    )
    existing.value = '5'
  }

  if (existing && existing.value === '5') {
    // Migration v5 → v6: add position column to cassettes for physical cartridge ordering
    db.exec(`ALTER TABLE cassettes ADD COLUMN position INTEGER NOT NULL DEFAULT 0`)
    // Backfill position from existing row order (by denomination)
    const rows = db.prepare('SELECT denomination FROM cassettes ORDER BY denomination').all() as {
      denomination: number
    }[]
    const update = db.prepare('UPDATE cassettes SET position = ? WHERE denomination = ?')
    for (let i = 0; i < rows.length; i++) {
      update.run(i + 1, rows[i]!.denomination)
    }
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('6', 'schema_version')
    console.log('[StateStore] Migrated schema v5 → v6 (added cassette position)')
    existing.value = '6'
  }

  if (existing && existing.value === '6') {
    // Migration v6 → v7: rename fee_percent → fee_fraction. Same semantics
    // (unit fraction in [0, 1]); the rename closes the 100× misinterpretation
    // risk between satmachineadmin's consumer and bitspire's stamp.
    // Coordinated with satmachineadmin commit d717a6e (v2-bitspire branch).
    db.exec(`ALTER TABLE transactions RENAME COLUMN fee_percent TO fee_fraction`)
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('7', 'schema_version')
    console.log('[StateStore] Migrated schema v6 → v7 (renamed fee_percent → fee_fraction)')
    existing.value = '7'
  }

  if (existing && existing.value === '7') {
    // Migration v7 → v8: seed `meta` rows for operator-config consumer.
    //   - lastKnownConfigCreatedAt — replay-protection watermark. Drop any
    //     incoming kind-30078 operator-config event whose created_at is
    //     ≤ this value. Default 0 = "haven't applied anything yet."
    //   - bootstrapPublishedAt — gate for the one-shot ATM-side
    //     bitspire-cassettes-state hello-event. NULL = "haven't published
    //     bootstrap yet"; once set, the hello-event publish path becomes
    //     a no-op (continuous reverse-channel publish is v2 territory).
    // See aiolabs/lamassu-next#56 + ~/dev/coordination/log.md (2026-05-30).
    db.prepare('INSERT INTO meta (key, value) VALUES (?, ?)').run('lastKnownConfigCreatedAt', '0')
    db.prepare('INSERT INTO meta (key, value) VALUES (?, ?)').run('bootstrapPublishedAt', '')
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('8', 'schema_version')
    console.log('[StateStore] Migrated schema v7 → v8 (seeded operator-config meta rows)')
    existing.value = '8'
  }

  if (existing && existing.value === '8') {
    // Migration v8 → v9: rebuild `cassettes` so `position` is the PK, NOT
    // `denomination`. Real production machines (Tejo, batm3) load multiple
    // cassettes with the same denomination for cash-out throughput on a
    // single bill class — the v1.0 schema's denomination-PK silently
    // collapsed duplicates and the HAL `indexOf(denomination)` first-match
    // semantics meant only one bay of any given denom could ever dispense.
    // v9 makes `position` the addressable unit (matches the hardware bay
    // layout) and allows `denomination` to repeat across rows.
    //
    // The cassette-config wire shape (operator → ATM kind-30078) flips
    // alongside this from `{denominations: {...}}` → `{positions: {...}}`.
    // See aiolabs/lamassu-next#56 + ~/dev/coordination/log.md 2026-05-30
    // entries (06:30Z → 18:45Z) for the design history.
    db.pragma('foreign_keys = OFF')
    db.exec(`
      DROP TABLE IF EXISTS cassettes_new;
      CREATE TABLE cassettes_new (
        position INTEGER PRIMARY KEY,
        denomination INTEGER NOT NULL,
        count INTEGER NOT NULL DEFAULT 0
      );
      INSERT INTO cassettes_new (position, denomination, count)
        SELECT position, denomination, count FROM cassettes;
      DROP TABLE cassettes;
      ALTER TABLE cassettes_new RENAME TO cassettes;
    `)
    db.pragma('foreign_keys = ON')
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('9', 'schema_version')
    console.log('[StateStore] Migrated schema v8 → v9 (cassettes PK position; allow duplicate denominations)')
    existing.value = '9'
  }

  if (existing && existing.value === '9') {
    // Migration v9 → v10: operator-pushed fee config consumer
    // (aiolabs/lamassu-next#57).
    //   - fee_config table — singleton row (id=1) carrying the last
    //     applied cash-in/cash-out fee fractions. Persisted so a restart
    //     restores the operator's policy without waiting on relay. The
    //     super/operator breakdown is NOT mirrored here — satmachineadmin
    //     is the canonical audit substrate for the split per settlement
    //     (Layer 1 #38). See coord log 2026-06-01T07:56Z for the dumb-
    //     machine/smart-server rationale (the components stay on the
    //     wire — they get parser-side consistency-asserted + logged at
    //     receipt — but don't propagate beyond the parse boundary).
    //   - meta.lastKnownFeeConfigCreatedAt — replay-protection watermark
    //     (separate from `lastKnownConfigCreatedAt` for cassettes, per
    //     d-tag-per-lifecycle convention). Default 0 = "fresh ATM,
    //     nothing applied yet → fail-closed into maintenance screen."
    db.exec(`
      CREATE TABLE IF NOT EXISTS fee_config (
        id INTEGER PRIMARY KEY CHECK (id = 1),
        cash_in_fee_fraction REAL NOT NULL,
        cash_out_fee_fraction REAL NOT NULL,
        schema_version INTEGER NOT NULL DEFAULT 1,
        event_created_at INTEGER NOT NULL,
        applied_at INTEGER NOT NULL
      );
    `)
    db.prepare('INSERT OR IGNORE INTO meta (key, value) VALUES (?, ?)').run(
      'lastKnownFeeConfigCreatedAt',
      '0'
    )
    db.prepare('UPDATE meta SET value = ? WHERE key = ?').run('10', 'schema_version')
    console.log('[StateStore] Migrated schema v9 → v10 (added fee_config + watermark)')
  }

  // Defensive: a fresh install at SCHEMA_VERSION skips all migrations.
  // Seed the operator-config meta rows if they're missing (idempotent).
  const seedMeta = db.prepare('INSERT OR IGNORE INTO meta (key, value) VALUES (?, ?)')
  seedMeta.run('lastKnownConfigCreatedAt', '0')
  seedMeta.run('bootstrapPublishedAt', '')
  seedMeta.run('lastKnownFeeConfigCreatedAt', '0')

  const cashboxRow = db.prepare('SELECT id FROM cashbox WHERE id = 1').get()
  if (!cashboxRow) {
    db.prepare('INSERT INTO cashbox (id) VALUES (1)').run()
  }

  console.log('[StateStore] Initialized database at', resolvedPath)
}

// ---------------------------------------------------------------------------
// Meta — operator-config consumer state
// (lastKnownConfigCreatedAt + bootstrapPublishedAt for aiolabs/lamassu-next#56)
// ---------------------------------------------------------------------------

/**
 * Read replay-protection watermark. Returns 0 if no operator config has
 * been applied yet (fresh ATM, pre-bootstrap).
 */
export function getLastKnownConfigCreatedAt(): number {
  if (!db) throw new Error('Database not initialized')
  const row = db
    .prepare('SELECT value FROM meta WHERE key = ?')
    .get('lastKnownConfigCreatedAt') as { value: string } | undefined
  return row ? Number(row.value) || 0 : 0
}

/**
 * Read the one-shot bootstrap-publish gate. Returns null if the ATM has
 * not yet published its `bitspire-cassettes-state:<machine_id>` hello-event.
 */
export function getBootstrapPublishedAt(): number | null {
  if (!db) throw new Error('Database not initialized')
  const row = db
    .prepare('SELECT value FROM meta WHERE key = ?')
    .get('bootstrapPublishedAt') as { value: string } | undefined
  if (!row || row.value === '') return null
  const n = Number(row.value)
  return Number.isFinite(n) ? n : null
}

/**
 * Mark the bootstrap hello-event as published. Idempotent — only takes
 * effect the first time it's set. Subsequent calls overwrite the
 * timestamp (harmless; the gate just needs to be non-null).
 */
export function markBootstrapPublished(unixTimestamp: number): void {
  if (!db) throw new Error('Database not initialized')
  db.prepare('UPDATE meta SET value = ? WHERE key = ?').run(
    String(unixTimestamp),
    'bootstrapPublishedAt'
  )
}

export type OperatorCassettesPayload = {
  positions: Record<string, { denomination: number; count: number }>
}

export type ApplyResult =
  | { applied: true }
  | { applied: false; reason: string }

/**
 * Atomic apply of an operator-published cassette config (aiolabs/lamassu-next#56).
 *
 * Caller has already verified the event signature and decrypted the
 * content. This function:
 *
 * 1. Rechecks replay-protection against `meta.lastKnownConfigCreatedAt`
 *    (defense-in-depth — caller should have done this too).
 * 2. Validates the payload's `positions` key set is *exactly* the set of
 *    positions currently in the `cassettes` table. The bay count is
 *    hardware-determined and can't be added to or removed from via this
 *    path; only the per-bay denomination and count are operator-mutable.
 * 3. Validates per-entry `denomination` is a positive int, `count` is a
 *    non-negative int. **Duplicate denominations across positions are
 *    intentionally permitted** — real machines load multiple cassettes
 *    with the same denomination for cash-out throughput.
 * 4. In a single SQLite transaction: updates `cassettes` rows by position
 *    (denomination + count both mutable per row) AND advances
 *    `meta.lastKnownConfigCreatedAt` to `eventCreatedAt`.
 *
 * Mid-write crashes roll back cleanly; on restart the same event is
 * re-delivered by the relay and the watermark check drops it as already
 * consumed (or the watermark is pre-event because the tx rolled back,
 * and the apply runs again from scratch).
 */
export function applyOperatorCassettesConfig(
  payload: OperatorCassettesPayload,
  eventCreatedAt: number
): ApplyResult {
  if (!db) throw new Error('Database not initialized')

  const watermark = getLastKnownConfigCreatedAt()
  if (eventCreatedAt <= watermark) {
    return {
      applied: false,
      reason: `event.created_at (${eventCreatedAt}) <= lastKnownConfigCreatedAt (${watermark})`,
    }
  }

  const currentRows = db
    .prepare('SELECT position FROM cassettes')
    .all() as { position: number }[]
  const currentPositions = new Set(currentRows.map((r) => r.position))
  const payloadPositions = new Set(Object.keys(payload.positions).map((k) => Number(k)))

  if (currentPositions.size !== payloadPositions.size) {
    return {
      applied: false,
      reason: `position count mismatch: state.db has ${currentPositions.size}, payload has ${payloadPositions.size}`,
    }
  }
  for (const p of currentPositions) {
    if (!payloadPositions.has(p)) {
      return { applied: false, reason: `payload missing position ${p}` }
    }
  }
  for (const p of payloadPositions) {
    if (!currentPositions.has(p)) {
      return { applied: false, reason: `payload includes unknown position ${p}` }
    }
  }

  for (const [posKey, entry] of Object.entries(payload.positions)) {
    if (!Number.isInteger(entry.denomination) || entry.denomination <= 0) {
      return {
        applied: false,
        reason: `denomination must be positive int (position ${posKey}, got ${entry.denomination})`,
      }
    }
    if (!Number.isInteger(entry.count) || entry.count < 0) {
      return {
        applied: false,
        reason: `count must be non-negative int (position ${posKey}, got ${entry.count})`,
      }
    }
  }

  const updateCassette = db.prepare(
    'UPDATE cassettes SET denomination = ?, count = ? WHERE position = ?'
  )
  const setWatermark = db.prepare('UPDATE meta SET value = ? WHERE key = ?')

  const run = db.transaction(() => {
    for (const [posKey, entry] of Object.entries(payload.positions)) {
      updateCassette.run(entry.denomination, entry.count, Number(posKey))
    }
    setWatermark.run(String(eventCreatedAt), 'lastKnownConfigCreatedAt')
  })

  run()
  console.log(
    `[StateStore] Applied operator cassettes config @ created_at=${eventCreatedAt} (${Object.keys(payload.positions).length} positions)`
  )
  return { applied: true }
}

// ---------------------------------------------------------------------------
// Fee config — operator-pushed fee fractions (aiolabs/lamassu-next#57)
// ---------------------------------------------------------------------------

export interface FeeConfigRow {
  cashInFeeFraction: number
  cashOutFeeFraction: number
  schemaVersion: number
  /** Watermark — the event's created_at (NIP-78 replaceable event timestamp). */
  eventCreatedAt: number
  /** Local Date.now() at the moment this row was upserted via IPC. Triage primitive. */
  appliedAt: number
}

/**
 * Read the last-applied operator fee config. Returns null when no event
 * has ever been applied (fresh ATM, pre-operator-publish). The renderer
 * uses null → maintenance screen ("Awaiting fee configuration from
 * operator") per the fail-closed posture from issue #57.
 */
export function getFeeConfig(): FeeConfigRow | null {
  if (!db) throw new Error('Database not initialized')
  const row = db
    .prepare(
      'SELECT cash_in_fee_fraction, cash_out_fee_fraction, schema_version, event_created_at, applied_at FROM fee_config WHERE id = 1'
    )
    .get() as
    | {
        cash_in_fee_fraction: number
        cash_out_fee_fraction: number
        schema_version: number
        event_created_at: number
        applied_at: number
      }
    | undefined
  if (!row) return null
  return {
    cashInFeeFraction: row.cash_in_fee_fraction,
    cashOutFeeFraction: row.cash_out_fee_fraction,
    schemaVersion: row.schema_version,
    eventCreatedAt: row.event_created_at,
    appliedAt: row.applied_at,
  }
}

/**
 * Read replay-protection watermark. Returns 0 if no fee config has ever
 * been applied. Separate from `lastKnownConfigCreatedAt` (cassettes) per
 * the d-tag-per-lifecycle convention — independent watermarks isolate
 * blast radius across operator-pushed config types.
 */
export function getLastKnownFeeConfigCreatedAt(): number {
  if (!db) throw new Error('Database not initialized')
  const row = db
    .prepare('SELECT value FROM meta WHERE key = ?')
    .get('lastKnownFeeConfigCreatedAt') as { value: string } | undefined
  return row ? Number(row.value) || 0 : 0
}

export interface FeeConfigPayload {
  cashInFeeFraction: number
  cashOutFeeFraction: number
  schemaVersion: number
}

/**
 * Atomic apply of an operator-published fee config (aiolabs/lamassu-next#57).
 *
 * Caller has already verified the event signature, decrypted the content,
 * enforced the per-direction 15% cap, and ignored any unknown top-level
 * keys (v2 forward-compat). This function:
 *
 * 1. Rechecks replay-protection against `meta.lastKnownFeeConfigCreatedAt`
 *    (defense-in-depth — caller should have done this too).
 * 2. Re-validates both fractions are in [0, 0.15] (defense in depth — the
 *    renderer-side cap is the primary check; the state-store guard catches
 *    bypass via a buggy or tampered renderer).
 * 3. In a single SQLite transaction: upserts the singleton fee_config row
 *    AND advances `meta.lastKnownFeeConfigCreatedAt` to `eventCreatedAt`.
 */
const FEE_CAP_PER_DIRECTION = 0.15

export function applyFeeConfig(
  payload: FeeConfigPayload,
  eventCreatedAt: number
): ApplyResult {
  if (!db) throw new Error('Database not initialized')

  const watermark = getLastKnownFeeConfigCreatedAt()
  if (eventCreatedAt <= watermark) {
    return {
      applied: false,
      reason: `event.created_at (${eventCreatedAt}) <= lastKnownFeeConfigCreatedAt (${watermark})`,
    }
  }

  const rangeChecks: [string, number][] = [
    ['cash_in_fee_fraction', payload.cashInFeeFraction],
    ['cash_out_fee_fraction', payload.cashOutFeeFraction],
  ]
  for (const [name, value] of rangeChecks) {
    if (!Number.isFinite(value) || value < 0 || value > FEE_CAP_PER_DIRECTION) {
      return {
        applied: false,
        reason: `${name} out of range [0, ${FEE_CAP_PER_DIRECTION}]: ${value}`,
      }
    }
  }
  if (!Number.isInteger(payload.schemaVersion) || payload.schemaVersion < 1) {
    return { applied: false, reason: `invalid schema_version: ${payload.schemaVersion}` }
  }
  if (!Number.isInteger(eventCreatedAt) || eventCreatedAt < 0) {
    return { applied: false, reason: `invalid event_created_at: ${eventCreatedAt}` }
  }

  const upsert = db.prepare(
    'INSERT INTO fee_config (id, cash_in_fee_fraction, cash_out_fee_fraction, schema_version, event_created_at, applied_at) VALUES (1, ?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET cash_in_fee_fraction = excluded.cash_in_fee_fraction, cash_out_fee_fraction = excluded.cash_out_fee_fraction, schema_version = excluded.schema_version, event_created_at = excluded.event_created_at, applied_at = excluded.applied_at'
  )
  const setWatermark = db.prepare('UPDATE meta SET value = ? WHERE key = ?')

  const run = db.transaction(() => {
    upsert.run(
      payload.cashInFeeFraction,
      payload.cashOutFeeFraction,
      payload.schemaVersion,
      eventCreatedAt,
      Date.now()
    )
    setWatermark.run(String(eventCreatedAt), 'lastKnownFeeConfigCreatedAt')
  })

  run()
  console.log(
    `[StateStore] Applied fee config @ event_created_at=${eventCreatedAt} ` +
      `cash_in=${payload.cashInFeeFraction} cash_out=${payload.cashOutFeeFraction} ` +
      `schema=${payload.schemaVersion}`
  )
  return { applied: true }
}

// ---------------------------------------------------------------------------
// Cassettes
// ---------------------------------------------------------------------------

export interface CassetteRow {
  denomination: number
  count: number
  position: number
}

export function loadCassettes(): CassetteRow[] {
  if (!db) throw new Error('Database not initialized')
  return db
    .prepare('SELECT denomination, count, position FROM cassettes ORDER BY position, denomination')
    .all() as CassetteRow[]
}

/**
 * Upsert cassette rows. Position is the addressable unit; the same
 * denomination may legitimately appear on multiple positions (real
 * machines load N cassettes of the same denomination for cash-out
 * throughput).
 */
export function setCassettes(
  cassettes: { denomination: number; count: number; position?: number }[]
): void {
  if (!db) throw new Error('Database not initialized')

  const upsert = db.prepare(
    'INSERT INTO cassettes (position, denomination, count) VALUES (?, ?, ?) ON CONFLICT(position) DO UPDATE SET denomination = excluded.denomination, count = excluded.count'
  )

  const run = db.transaction(
    (rows: { denomination: number; count: number; position?: number }[]) => {
      for (let i = 0; i < rows.length; i++) {
        const row = rows[i]!
        upsert.run(row.position ?? i + 1, row.denomination, row.count)
      }
    }
  )

  run(cassettes)
  console.log('[StateStore] Cassettes updated:', cassettes)
}

/**
 * Decrement the count for a specific physical bay (position). Use this
 * after a successful dispense — the dispenser returns per-position
 * results, so the caller already knows which bay drained how many.
 */
export function updateCassetteCountByPosition(position: number, delta: number): void {
  if (!db) throw new Error('Database not initialized')

  db.prepare('UPDATE cassettes SET count = MAX(0, count + ?) WHERE position = ?').run(
    delta,
    position
  )
}

/**
 * Get aggregate inventory as denomination → total-count-across-positions.
 * Used by the renderer / state machine for "do we have enough $20s to
 * cover this withdraw" gating where the per-bay breakdown doesn't matter.
 * For per-bay state use `loadCassettes()`.
 */
export function getInventory(): Record<number, number> {
  const rows = loadCassettes()
  const inv: Record<number, number> = {}
  for (const row of rows) {
    if (row.count > 0) {
      inv[row.denomination] = (inv[row.denomination] ?? 0) + row.count
    }
  }
  return inv
}

// ---------------------------------------------------------------------------
// Cashbox
// ---------------------------------------------------------------------------

export interface CashboxRow {
  totalBills: number
  totalFiatCents: number
  lastEmptiedAt: number | null
}

export function getCashbox(): CashboxRow {
  if (!db) throw new Error('Database not initialized')

  const row = db
    .prepare('SELECT total_bills, total_fiat_cents, last_emptied_at FROM cashbox WHERE id = 1')
    .get() as { total_bills: number; total_fiat_cents: number; last_emptied_at: number | null }

  return {
    totalBills: row.total_bills,
    totalFiatCents: row.total_fiat_cents,
    lastEmptiedAt: row.last_emptied_at,
  }
}

/**
 * Increment cashbox totals after a cash-in transaction.
 */
export function addToCashbox(bills: number, fiatCents: number): void {
  if (!db) throw new Error('Database not initialized')

  db.prepare(
    'UPDATE cashbox SET total_bills = total_bills + ?, total_fiat_cents = total_fiat_cents + ? WHERE id = 1'
  ).run(bills, fiatCents)
}

/**
 * Reset the cashbox (operator emptied it).
 */
export function emptyCashbox(): void {
  if (!db) throw new Error('Database not initialized')

  db.prepare(
    'UPDATE cashbox SET total_bills = 0, total_fiat_cents = 0, last_emptied_at = ? WHERE id = 1'
  ).run(Date.now())

  console.log('[StateStore] Cashbox emptied')
}

// ---------------------------------------------------------------------------
// Transactions
// ---------------------------------------------------------------------------

interface TransactionInput {
  txid: string
  type: 'cash_in' | 'cash_out' | 'manual_dispense'
  status: 'complete' | 'dispense_error' | 'partial' | 'remediated'
  fiatCents: number
  sats: number
  feeSats: number
  feeFraction: number
  exchangeRate: number
  currency: string
  bills: { denomination: number; count: number }[]
  cassettes?: {
    name: string
    position: number
    denomination: number
    provisioned: number
    dispensed: number
    rejected: number
  }[]
  error?: string | null
}

/**
 * Record a completed transaction and update cassette/cashbox state atomically.
 */
export function recordTransaction(tx: TransactionInput): void {
  if (!db) throw new Error('Database not initialized')

  if (tx.feeFraction < 0 || tx.feeFraction > 1) {
    throw new Error(
      `[StateStore] feeFraction out of range [0, 1]: ${tx.feeFraction}. ` +
        `Unit fraction expected (0.05 = 5%), not a percentage.`
    )
  }

  const insertTx = db.prepare(
    'INSERT INTO transactions (txid, type, status, error, fiat_cents, sats, fee_sats, fee_fraction, exchange_rate, currency, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
  )
  const insertBill = db.prepare(
    'INSERT INTO transaction_bills (txid, denomination, count) VALUES (?, ?, ?)'
  )
  const insertCassetteBill = db.prepare(
    'INSERT INTO cassette_bills (txid, name, position, denomination, provisioned, dispensed, rejected) VALUES (?, ?, ?, ?, ?, ?, ?)'
  )
  const updateCassette = db.prepare(
    'UPDATE cassettes SET count = MAX(0, count + ?) WHERE denomination = ?'
  )
  const updateCashboxStmt = db.prepare(
    'UPDATE cashbox SET total_bills = total_bills + ?, total_fiat_cents = total_fiat_cents + ? WHERE id = 1'
  )

  const run = db.transaction((t: TransactionInput) => {
    insertTx.run(
      t.txid,
      t.type,
      t.status,
      t.error ?? null,
      t.fiatCents,
      t.sats,
      t.feeSats,
      t.feeFraction,
      t.exchangeRate,
      t.currency,
      Date.now()
    )

    for (const bill of t.bills) {
      insertBill.run(t.txid, bill.denomination, bill.count)
    }

    // Insert per-cassette detail when available
    if (t.cassettes) {
      for (const c of t.cassettes) {
        insertCassetteBill.run(
          t.txid,
          c.name,
          c.position,
          c.denomination,
          c.provisioned,
          c.dispensed,
          c.rejected
        )
      }
    }

    if (t.type === 'cash_out') {
      // Decrement cassettes by ACTUALLY dispensed count (not requested)
      if (t.cassettes) {
        for (const c of t.cassettes) {
          if (c.dispensed > 0) {
            updateCassette.run(-c.dispensed, c.denomination)
          }
        }
      } else {
        // Fallback: use bill counts (backward compat for mocks without cassette data)
        for (const bill of t.bills) {
          updateCassette.run(-bill.count, bill.denomination)
        }
      }
    }

    if (t.type === 'cash_in') {
      // Bills inserted by customer go into cashbox
      const totalBills = t.bills.reduce((sum, b) => sum + b.count, 0)
      updateCashboxStmt.run(totalBills, t.fiatCents)
    }
  })

  run(tx)
  console.log('[StateStore] Recorded transaction:', tx.txid, tx.type, `(${tx.status})`)
}

// ---------------------------------------------------------------------------
// Operator Command Queue
// ---------------------------------------------------------------------------

export interface OperatorCommand {
  id: number
  command: string
  status: string
  created_at: number
}

/**
 * Get the next pending operator command (FIFO).
 */
export function getPendingCommand(): OperatorCommand | null {
  if (!db) throw new Error('Database not initialized')

  const row = db
    .prepare(
      "SELECT id, command, status, created_at FROM operator_commands WHERE status = 'pending' ORDER BY id LIMIT 1"
    )
    .get() as { id: number; command: string; status: string; created_at: number } | undefined

  return row ?? null
}

/**
 * Mark a command as executing (prevents double-pickup).
 */
export function markCommandExecuting(id: number): void {
  if (!db) throw new Error('Database not initialized')
  db.prepare(
    "UPDATE operator_commands SET status = 'executing' WHERE id = ? AND status = 'pending'"
  ).run(id)
}

/**
 * Complete a command with a result.
 */
export function completeCommand(id: number, result: string, error?: boolean): void {
  if (!db) throw new Error('Database not initialized')
  db.prepare(
    'UPDATE operator_commands SET status = ?, result = ?, completed_at = ? WHERE id = ?'
  ).run(error ? 'error' : 'complete', result, Date.now(), id)
}

// ---------------------------------------------------------------------------
// Transaction Remediation
// ---------------------------------------------------------------------------

/**
 * Mark a failed transaction as remediated by a manual dispense.
 * Only updates transactions with status 'dispense_error' or 'partial'.
 * Returns true if the transaction was updated, false if not found or not in error state.
 */
export function remediateTransaction(txid: string, remediatedByTxid: string): boolean {
  if (!db) throw new Error('Database not initialized')

  const result = db
    .prepare(
      'UPDATE transactions SET status = ?, remediated_by = ? WHERE txid = ? AND status IN (?, ?)'
    )
    .run('remediated', remediatedByTxid, txid, 'dispense_error', 'partial')

  if (result.changes > 0) {
    console.log('[StateStore] Remediated transaction:', txid, '→', remediatedByTxid)
  }
  return result.changes > 0
}

/**
 * Close the database (for clean shutdown).
 */
export function closeDatabase(): void {
  if (db) {
    db.close()
    db = null
    console.log('[StateStore] Database closed')
  }
}