// pull-data.ts: copy a val's SQLite database and blobs to this VM.
//
//   EXPORT_TOKEN=... deno run -A pull-data.ts <export-data URL> <val dir> [--global]
//
// <export-data URL> is the address of export-data.ts on Val Town. The data
// lands in <val dir>/data/sqlite.db and <val dir>/data/blobs.db (with
// --global: the account-wide database and blobs, best pulled into
// ~/vals/.global). Existing files are kept as *.old. Stop the val's
// services first. Ends by comparing row and blob counts with Val Town.
import { DatabaseSync } from "node:sqlite";

const [base, dir] = Deno.args.filter((a) => !a.startsWith("--"));
const global = Deno.args.includes("--global");
const token = Deno.env.get("EXPORT_TOKEN");
if (!base || !dir || !token) {
  console.error("usage: EXPORT_TOKEN=... pull-data.ts <export-data URL> <val dir> [--global]");
  Deno.exit(2);
}
const scope = global ? "?scope=global" : "";
const auth = { Authorization: `Bearer ${token}` };

async function remote(sql: string, args: unknown[] = []) {
  const res = await fetch(new URL("/sql" + scope, base), {
    method: "POST",
    headers: { ...auth, "Content-Type": "application/json" },
    body: JSON.stringify({ sql, args }),
  });
  if (!res.ok) throw new Error(`${res.status}: ${await res.text()}`);
  return (await res.json()) as { columns: string[]; rows: unknown[][] };
}
const q = (s: string) => '"' + s.replaceAll('"', '""') + '"';

const out = `${dir}/data`;
await Deno.mkdir(out, { recursive: true });
async function fresh(name: string) {
  for (const suffix of ["", "-wal", "-shm"]) {
    try {
      await Deno.rename(`${out}/${name}${suffix}`, `${out}/${name}.old${suffix}`);
    } catch { /* none yet */ }
  }
  return new DatabaseSync(`${out}/${name}`);
}

// SQLite
const schema = await remote(
  "SELECT type, name, tbl_name, sql FROM sqlite_schema WHERE sql IS NOT NULL ORDER BY rowid",
);
const entries = schema.rows.map(([type, name, tbl, sql]) => ({ type, name, tbl, sql } as
  { type: string; name: string; tbl: string; sql: string }));
const virtual = entries.filter((e) => /^CREATE\s+VIRTUAL/i.test(e.sql)).map((e) => e.name);
const skip = (t: string) =>
  t.startsWith("sqlite_") || t.startsWith("libsql_") || t.startsWith("_litestream") ||
  virtual.some((v) => t === v || t.startsWith(v + "_"));
const db = await fresh("sqlite.db");
const counts: Record<string, number> = {};
for (const t of entries.filter((e) => e.type === "table" && !skip(e.name))) {
  db.exec(t.sql);
  const info = await remote(`PRAGMA table_xinfo(${q(t.name)})`);
  const ci = Object.fromEntries(info.columns.map((c, i) => [c, i]));
  const cols = info.rows.filter((r) => Number(r[ci.hidden]) === 0);
  const names = cols.map((r) => q(String(r[ci.name])));
  const pk = cols.filter((r) => Number(r[ci.pk]) > 0)
    .sort((a, b) => Number(a[ci.pk]) - Number(b[ci.pk])).map((r) => q(String(r[ci.name])));
  // SQLite writes each value as an SQL literal, so integers, reals, blobs
  // and text (as hex, NULs included) come across exactly.
  const lits = names.map((c) =>
    `CASE WHEN typeof(${c})='text' THEN 'CAST(X''' || hex(${c}) || ''' AS TEXT)' ELSE quote(${c}) END`
  ).join(",");
  let n = 0;
  db.exec("BEGIN");
  for (;;) {
    const page = await remote(
      `SELECT ${lits} FROM ${q(t.name)} ORDER BY ${pk.length ? pk.join(",") : "rowid"} LIMIT 500 OFFSET ?`,
      [n],
    );
    for (const row of page.rows) {
      db.exec(`INSERT INTO ${q(t.name)} (${names.join(",")}) VALUES (${row.join(",")})`);
    }
    n += page.rows.length;
    if (page.rows.length < 500) break;
  }
  db.exec("COMMIT");
  counts[t.name] = n;
}
if (entries.some((e) => e.name === "sqlite_sequence")) {
  const seq = await remote("SELECT name, CAST(seq AS TEXT) FROM sqlite_sequence");
  for (const [name, s] of seq.rows) {
    if (skip(String(name)) || !/^\d+$/.test(String(s))) continue;
    db.prepare("DELETE FROM sqlite_sequence WHERE name=?").run(String(name));
    db.prepare("INSERT INTO sqlite_sequence(name, seq) VALUES (?, ?)").run(String(name), BigInt(String(s)));
  }
}
for (const e of entries.filter((e) => ["index", "trigger", "view"].includes(e.type) && !skip(e.tbl))) {
  db.exec(e.sql);
}
let ok = true;
for (const [t, n] of Object.entries(counts)) {
  const there = Number((await remote(`SELECT count(*) FROM ${q(t)}`)).rows[0][0]);
  const here = Number((db.prepare(`SELECT count(*) AS n FROM ${q(t)}`).get() as { n: number }).n);
  if (there !== here || n !== here) ok = false;
  console.log(`table ${t}: ${here} rows here, ${there} on Val Town`);
}
const check = db.prepare("PRAGMA integrity_check").get() as Record<string, string>;
console.log(`integrity_check: ${Object.values(check)[0]}`);
db.close();
if (virtual.length) {
  console.log(`skipped virtual tables (rebuild them from their source): ${virtual.join(", ")}`);
}

// Blobs
const list = await (await fetch(new URL("/blobs" + scope, base), { headers: auth })).json() as
  { key: string; size: number; lastModified: string }[];
const bdb = await fresh("blobs.db");
bdb.exec(
  "CREATE TABLE blobs (key TEXT PRIMARY KEY, value BLOB NOT NULL, last_modified TEXT NOT NULL)",
);
const put = bdb.prepare("INSERT INTO blobs (key, value, last_modified) VALUES (?, ?, ?)");
let bytes = 0;
for (const b of list) {
  const u = new URL("/blob" + scope, base);
  u.searchParams.set("key", b.key);
  const res = await fetch(u, { headers: auth });
  if (!res.ok) throw new Error(`blob ${b.key}: ${res.status}`);
  const v = new Uint8Array(await res.arrayBuffer());
  if (b.size !== undefined && v.length !== b.size) {
    ok = false;
    console.log(`blob ${b.key}: ${v.length} bytes, Val Town lists ${b.size}`);
  }
  put.run(b.key, v, b.lastModified ?? new Date().toISOString());
  bytes += v.length;
}
bdb.close();
console.log(`blobs: ${list.length} (${bytes} bytes)`);
console.log(ok ? "pull-data: all counts match" : "pull-data: MISMATCH, see above");
if (!ok) Deno.exit(1);