/**
 * Runs one statement in a repository's or account's Durable Object database through the dev server's
 * `/__dev/scoped-sql` route (enabled with EMAIL_DEV_SINK=kv on a loopback forge), the scoped counterpart of
 * `wrangler d1 execute`. Synchronous like the D1 helpers, so it shells out to a one-line fetch.
 */
export function scopedSqlRows<T = Record<string, unknown>>(
  base: string,
  scope: "repo" | "account",
  id: string,
  sql: string,
): T[] {
  const script = `const url = process.argv[1];
const r = await fetch(url, { method: "POST", headers: { "content-type": "application/json", origin: new URL(url).origin, "sec-fetch-site": "same-origin" }, body: process.argv[2] });
if (!r.ok) { console.error(r.status, await r.text()); process.exit(1); }
process.stdout.write(await r.text());`;
  const result = Bun.spawnSync(["bun", "-e", script, `${base}/__dev/scoped-sql`, JSON.stringify({ scope, id, sql })], {
    stderr: "pipe",
    stdout: "pipe",
  });
  if (result.exitCode !== 0) throw new Error(`scoped sql failed: ${result.stderr.toString()}`);
  return JSON.parse(result.stdout.toString()) as T[];
}

/** Runs a command from `root`, returning stdout; throws with stderr on failure. */
export function sh(root: string, cmd: string[]): string {
  const result = Bun.spawnSync(cmd, { cwd: root, stdout: "pipe", stderr: "pipe" });
  if (result.exitCode !== 0) throw new Error(`${cmd.join(" ")} failed: ${result.stderr.toString()}`);
  return result.stdout.toString();
}

/** `wrangler d1 execute --local` against the checkout at `root`, for the directory tables that stay in D1. */
export function d1Rows(root: string, sql: string): Array<Record<string, unknown>> {
  const out = sh(root, ["bunx", "wrangler", "d1", "execute", "gitfor", "--local", "--json", "--command", sql]);
  return (JSON.parse(out) as Array<{ results: Array<Record<string, unknown>> }>)[0]?.results ?? [];
}