SpaceWanderer/lib/db/admin.test.ts

import { mkdtemp, rm } from 'node:fs/promises'
import { tmpdir } from 'node:os'
import path from 'node:path'
import { getTableName } from 'drizzle-orm'
import { afterAll, describe, expect, it } from 'vitest'
import { openGameDb, type GameDb } from './client'
import * as schema from './schema'
import { AdminError, deleteAdminRow, insertAdminRow, isAdminTableName, listAdminTables, readAdminRows, updateAdminRow } from './admin'

const dirs: string[] = []

async function freshFile(): Promise<GameDb> {
  const dir = await mkdtemp(path.join(tmpdir(), 'sw-admin-test-'))
  dirs.push(dir)
  return openGameDb(path.join(dir, 'test.db'))
}

afterAll(async () => {
  for (const d of dirs) await rm(d, { recursive: true, force: true })
})

function expectAdminError(promise: Promise<unknown>, status: number): Promise<void> {
  return promise.then(
    () => {
      throw new Error(`expected AdminError ${status}, resolved instead`)
    },
    (err) => {
      expect(err).toBeInstanceOf(AdminError)
      expect((err as AdminError).status).toBe(status)
    },
  )
}

describe('admin table registry', () => {
  it('exposes every game table from schema.ts, keyed by SQL name', async () => {
    const db = await freshFile()
    const tables = await listAdminTables(db)
    expect(tables.map((t) => t.name).sort()).toEqual(
      Object.values(schema)
        .map((s) => getTableName(s as Parameters<typeof getTableName>[0]))
        .sort(),
    )
    expect(tables.every((t) => isAdminTableName(t.name))).toBe(true)
    for (const bad of ['_meta', 'nope', 'Saves']) expect(isAdminTableName(bad)).toBe(false)

    const saves = tables.find((t) => t.name === 'saves')!
    expect(saves.columns.map((c) => c.name)).toEqual(['id', 'seed', 'turn', 'created_at', 'schema_version'])
    expect(saves.count).toBe(0)
  })

  it('rejects unknown tables with 404 on every operation', async () => {
    const db = await freshFile()
    await expectAdminError(readAdminRows(db, 'nope'), 404)
    await expectAdminError(insertAdminRow(db, 'nope', {}), 404)
    await expectAdminError(updateAdminRow(db, 'nope', { id: 1 }, {}), 404)
    await expectAdminError(deleteAdminRow(db, 'nope', { id: 1 }), 404)
  })
})

describe('admin row CRUD (single-PK tables)', () => {
  it('inserts, reads back and pages rows with limit/offset validation', async () => {
    const db = await freshFile()
    const row = await insertAdminRow(db, 'saves', { seed: 'abc', turn: 0, created_at: '2026-01-01T00:00:00Z' })
    expect(row.id).toBe(1)
    expect(row.seed).toBe('abc')

    const all = await readAdminRows(db, 'saves')
    expect(all.total).toBe(1)
    expect(all.rows[0]).toMatchObject({ id: 1, seed: 'abc', turn: 0, schema_version: null })

    for (const bad of [0, -1, 201]) await expectAdminError(readAdminRows(db, 'saves', bad), 400)
    await expectAdminError(readAdminRows(db, 'saves', 50, -1), 400)
  })

  it('rejects unknown columns, missing required columns and wrong value types with 400', async () => {
    const db = await freshFile()
    await expectAdminError(insertAdminRow(db, 'saves', { bogus: 1 }), 400)
    await expectAdminError(insertAdminRow(db, 'saves', { seed: 'x' }), 400) // created_at missing
    await expectAdminError(insertAdminRow(db, 'saves', { seed: 'x', turn: 'nope', created_at: 't' }), 400)
    await expectAdminError(insertAdminRow(db, 'saves', ['not', 'an', 'object']), 400)
  })

  it('updates by key and returns the fresh row; unknown keys are 404', async () => {
    const db = await freshFile()
    await insertAdminRow(db, 'saves', { seed: 'abc', turn: 0, created_at: 't' })

    const updated = await updateAdminRow(db, 'saves', { id: 1 }, { turn: 5 })
    expect(updated).toMatchObject({ id: 1, seed: 'abc', turn: 5 })

    await expectAdminError(updateAdminRow(db, 'saves', { id: 99 }, { turn: 1 }), 404)
    await expectAdminError(updateAdminRow(db, 'saves', {}, { turn: 1 }), 400) // missing pk column in key
    await expectAdminError(updateAdminRow(db, 'saves', { id: 1 }, {}), 400) // nothing to set
  })

  it('deletes by key and returns the removed row; second delete is 404', async () => {
    const db = await freshFile()
    await insertAdminRow(db, 'saves', { seed: 'abc', turn: 0, created_at: 't' })

    const deleted = await deleteAdminRow(db, 'saves', { id: 1 })
    expect(deleted).toMatchObject({ id: 1, seed: 'abc' })
    expect((await readAdminRows(db, 'saves')).total).toBe(0)
    await expectAdminError(deleteAdminRow(db, 'saves', { id: 1 }), 404)
  })

  it('maps SQLite constraint violations to 409', async () => {
    const db = await freshFile()
    await insertAdminRow(db, 'crew', { save_id: 1, name: 'A', role: 'PILOT', trait_good: 'g', trait_bad: 'b', origin: 'STARTING CREW' })
    // ship_systems has UNIQUE(save_id, type) — a duplicate is a constraint failure, not a crash.
    await insertAdminRow(db, 'ship_systems', { save_id: 1, type: 'HULL', level: 0, capacity: 0 })
    await expectAdminError(insertAdminRow(db, 'ship_systems', { save_id: 1, type: 'HULL', level: 1, capacity: 0 }), 409)
  })
})

describe('admin row CRUD (composite-PK tables)', () => {
  it('requires every PK column in the key for jump_routes', async () => {
    const db = await freshFile()
    const row = await insertAdminRow(db, 'jump_routes', { from_system: 1, to_system: 2 })
    expect(row).toMatchObject({ from_system: 1, to_system: 2 })

    // PK columns are NOT NULL without defaults — omitting one is a 400, not a SQLite error.
    await expectAdminError(insertAdminRow(db, 'jump_routes', { from_system: 1 }), 400)
    await expectAdminError(updateAdminRow(db, 'jump_routes', { from_system: 1 }, {}), 400)

    const deleted = await deleteAdminRow(db, 'jump_routes', { from_system: 1, to_system: 2 })
    expect(deleted).toMatchObject({ from_system: 1, to_system: 2 })
    await expectAdminError(deleteAdminRow(db, 'jump_routes', { from_system: 1, to_system: 3 }), 404)
  })
})