Flight-Ops/app/api/admin/tables/[name]/rows/route.test.ts

import fs from 'node:fs/promises';
import os from 'node:os';
import path from 'node:path';
import { afterAll, beforeAll, describe, expect, it } from 'vitest';
import { getGameDb } from '@/lib/db/client';
import { createSession } from '@/lib/auth';
import { DELETE, GET, POST, PUT } from './route';

// v0.71.0 — generic DB CRUD route tests: rows fetch (incl. __rowid for PK-less tables),
// parameterized update/insert/delete, and the validation ladder (404 unknown table, 400 bad input).

let dir: string;
let adminSession = '';

beforeAll(async () => {
  dir = await fs.mkdtemp(path.join(os.tmpdir(), 'flight-admin-rows-'));
  process.env.FLIGHT_DB_PATH = path.join(dir, 'rows.db');
});

afterAll(async () => {
  delete process.env.FLIGHT_DB_PATH;
  await fs.rm(dir, { recursive: true, force: true });
});

// adminSession is minted in beforeAll, so the header helpers read it at call time.
const authHeaders = () => ({ 'X-Flight-Session': adminSession });

// The route handlers take Next.js's dynamic-segment context; unit tests supply it directly.
const ctx = (name: string) => ({ params: Promise.resolve({ name }) });

const getRows = (name: string, query = '') =>
  GET(new Request(`http://localhost/api/admin/tables/${encodeURIComponent(name)}/rows${query}`, { headers: authHeaders() }), ctx(name));
const putRow = (name: string, body: unknown) =>
  PUT(
    new Request(`http://localhost/api/admin/tables/${encodeURIComponent(name)}/rows`, {
      method: 'PUT',
      headers: { ...authHeaders(), 'content-type': 'application/json' },
      body: JSON.stringify(body),
    }),
    ctx(name),
  );
const postRow = (name: string, body: unknown) =>
  POST(
    new Request(`http://localhost/api/admin/tables/${encodeURIComponent(name)}/rows`, {
      method: 'POST',
      headers: { ...authHeaders(), 'content-type': 'application/json' },
      body: JSON.stringify(body),
    }),
    ctx(name),
  );
const deleteRow = (name: string, rowid: number | null) =>
  DELETE(
    new Request(
      `http://localhost/api/admin/tables/${encodeURIComponent(name)}/rows${rowid === null ? '' : `?rowid=${rowid}`}`,
      { method: 'DELETE', headers: authHeaders() },
    ),
    ctx(name),
  );

describe('admin rows CRUD', () => {
  beforeAll(async () => {
    adminSession = await createSession(await getGameDb(), 'admin-kamilkrzywda');
  });

  it('GET returns columns + rows with __rowid for the PK-less sim_state table', async () => {
    const res = await getRows('sim_state');
    expect(res.status).toBe(200);
    const body = (await res.json()) as { columns: string[]; rows: Array<Record<string, unknown>> };
    expect(body.columns[0]).toBe('__rowid');
    for (const col of ['clock_ms', 'time_scale']) expect(body.columns).toContain(col);
    expect(body.rows).toHaveLength(1); // the single seeded sim_state row
    expect(typeof body.rows[0].__rowid).toBe('number');
    expect(typeof body.rows[0].clock_ms).toBe('number');
  });

  it('PUT updates a cell and the change survives a re-fetch', async () => {
    const before = await (await getRows('sim_state')).json();
    const originalClockMs = (before as { rows: Array<Record<string, unknown>> }).rows[0].clock_ms;

    const res = await putRow('sim_state', { rowid: 1, values: { clock_ms: 424_242 } });
    expect(res.status).toBe(200);
    expect(await res.json()).toEqual({ ok: true });

    // Re-fetch through the same route — the persisted value is back.
    const body = await (await getRows('sim_state')).json();
    expect((body as { rows: Array<Record<string, unknown>> }).rows[0].clock_ms).toBe(424_242);

    // Restore the original clock so later tests see a sane world.
    const restore = await putRow('sim_state', { rowid: 1, values: { clock_ms: originalClockMs } });
    expect(restore.status).toBe(200);
  });

  it('PUT rejects unknown columns and empty values with 400', async () => {
    const badCol = await putRow('sim_state', { rowid: 1, values: { no_such_column: 1 } });
    expect(badCol.status).toBe(400);
    expect(((await badCol.json()) as { error: string }).error).toMatch(/unknown column/i);

    const empty = await putRow('sim_state', { rowid: 1, values: {} });
    expect(empty.status).toBe(400);

    const noRowid = await putRow('sim_state', { values: { clock_ms: 1 } });
    expect(noRowid.status).toBe(400);
  });

  it('POST inserts a row and DELETE removes it again (users table)', async () => {
    const created = await postRow('users', {
      values: { id: 'user-crud-test', username: 'crud-test-user', password_hash: 'aa$bb', created_at: Date.now() },
    });
    expect(created.status).toBe(200);
    const body = (await created.json()) as { ok: boolean; rowid: number };
    expect(body.ok).toBe(true);
    expect(Number.isInteger(body.rowid)).toBe(true);

    // Visible via GET…
    let list = await (await getRows('users')).json();
    const rows = (list as { rows: Array<Record<string, unknown>> }).rows;
    expect(rows.some((r) => r.username === 'crud-test-user')).toBe(true);

    // …and gone after DELETE.
    const del = await deleteRow('users', body.rowid);
    expect(del.status).toBe(200);
    list = await (await getRows('users')).json();
    expect((list as { rows: Array<Record<string, unknown>> }).rows.some((r) => r.username === 'crud-test-user')).toBe(false);

    // DELETE is idempotent.
    const again = await deleteRow('users', body.rowid);
    expect(again.status).toBe(200);
  });

  it('POST rejects unknown columns and empty values with 400', async () => {
    const badCol = await postRow('users', { values: { id: 'x', no_such_column: 1 } });
    expect(badCol.status).toBe(400);
    expect(((await badCol.json()) as { error: string }).error).toMatch(/unknown column/i);

    const empty = await postRow('users', { values: {} });
    expect(empty.status).toBe(400);
  });

  it('DELETE requires an integer rowid query parameter', async () => {
    const missing = await deleteRow('users', null);
    expect(missing.status).toBe(400);
    const bad = await DELETE(
      new Request('http://localhost/api/admin/tables/users/rows?rowid=abc', { method: 'DELETE', headers: authHeaders() }),
      ctx('users'),
    );
    expect(bad.status).toBe(400);
  });

  it('returns 404 for an unknown table on every verb', async () => {
    const g = await getRows('no_such_table');
    expect(g.status).toBe(404);
    const p = await putRow('no_such_table', { rowid: 1, values: { x: 1 } });
    expect(p.status).toBe(404);
    const i = await postRow('no_such_table', { values: { x: 1 } });
    expect(i.status).toBe(404);
    const d = await deleteRow('no_such_table', 1);
    expect(d.status).toBe(404);
  });

  it('rejects a non-integer limit with 400 and caps at 1000', async () => {
    const bad = await getRows('users', '?limit=abc');
    expect(bad.status).toBe(400);
    const capped = await getRows('users', '?limit=999999');
    expect(capped.status).toBe(200);
  });

  it('keeps the guard in front of everything: no header → 401, non-admin → 403', async () => {
    const noHeader = await GET(new Request('http://localhost/api/admin/tables/users/rows'), ctx('users'));
    expect(noHeader.status).toBe(401);

    const inst = await getGameDb();
    inst.raw.run(
      `INSERT INTO users (id, username, password_hash, admin, created_at)
       SELECT 'user-rows-nonadmin', 'rows-nonadmin', 'aa$bb', 0, ? WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 'user-rows-nonadmin')`,
      [Date.now()],
    );
    const nonAdminSession = await createSession(inst, 'user-rows-nonadmin');
    const forbidden = await GET(
      new Request('http://localhost/api/admin/tables/users/rows', { headers: { 'X-Flight-Session': nonAdminSession } }),
      ctx('users'),
    );
    expect(forbidden.status).toBe(403);
  });
});