/**
 * Audit the D1 game index against the AT Protocol records it was built from.
 *
 * Games are discovered from the network (Jetstream + UFOs/Constellation), so a
 * row can outlive the record it came from, or describe a game that was never
 * really played. This reports those, and can remove them from the index.
 *
 * The index is only a cache: pruning a row hides the game from the app, but the
 * AT Protocol record is untouched, so a direct visit to /game/<rkey> will
 * re-import it. Deleting the underlying record is a separate, owner-only action.
 *
 * Usage:
 *   npx tsx scripts/audit-games.ts                 # report only
 *   npx tsx scripts/audit-games.ts --prune         # delete STALE rows
 *   npx tsx scripts/audit-games.ts --drop=<rkey>   # delete specific rows
 */

import { execFileSync } from 'node:child_process';

interface Row {
  rkey: string;
  id: string;
  creator_did: string;
  player_one: string | null;
  player_two: string | null;
  status: string;
  action_count: number;
  created_at: string;
}

type Verdict = 'ok' | 'stale' | 'unreachable';

const DB = 'atprotogo-db';
const COLLECTION = 'boo.sky.go.game';

function query(sql: string): Row[] {
  const raw = execFileSync(
    'npx',
    ['wrangler', 'd1', 'execute', DB, '--remote', '--json', '--command', sql],
    { encoding: 'utf8', maxBuffer: 32 * 1024 * 1024 }
  );
  return JSON.parse(raw)[0].results as Row[];
}

function execute(sql: string): void {
  execFileSync('npx', ['wrangler', 'd1', 'execute', DB, '--remote', '--command', sql], {
    stdio: 'inherit',
  });
}

async function resolvePds(did: string): Promise<string | null> {
  try {
    const res = await fetch(`https://plc.directory/${did}`);
    if (!res.ok) return null;
    const doc = (await res.json()) as {
      service?: Array<{ type: string; serviceEndpoint: string }>;
    };
    return (
      doc.service?.find((s) => s.type === 'AtprotoPersonalDataServer')?.serviceEndpoint ?? null
    );
  } catch {
    return null;
  }
}

/** Does the game record this row was built from still exist? */
async function verify(row: Row): Promise<Verdict> {
  const did = row.id.match(/^at:\/\/(did:[^/]+)\//)?.[1] ?? row.creator_did;
  const pds = await resolvePds(did);
  if (!pds) return 'unreachable';

  try {
    const url = `${pds}/xrpc/com.atproto.repo.getRecord?repo=${encodeURIComponent(did)}&collection=${COLLECTION}&rkey=${encodeURIComponent(row.rkey)}`;
    const res = await fetch(url, { headers: { Accept: 'application/json' } });
    if (res.ok) return 'ok';
    if (res.status === 404 || res.status === 400) return 'stale';
    return 'unreachable';
  } catch {
    return 'unreachable';
  }
}

async function main() {
  const args = process.argv.slice(2);
  const prune = args.includes('--prune');
  const drops = args.filter((a) => a.startsWith('--drop=')).map((a) => a.slice('--drop='.length));

  if (drops.length > 0) {
    const list = drops.map((r) => `'${r.replace(/'/g, "''")}'`).join(',');
    console.error(`Dropping ${drops.length} row(s) from the index: ${drops.join(', ')}`);
    execute(`DELETE FROM games WHERE rkey IN (${list})`);
    console.error('Done.');
    return;
  }

  const rows = query(
    'SELECT rkey, id, creator_did, player_one, player_two, status, action_count, created_at FROM games ORDER BY created_at'
  );
  console.error(`Auditing ${rows.length} games in ${DB} (remote)...\n`);

  const stale: Row[] = [];
  const suspicious: Row[] = [];
  const unreachableRows: Row[] = [];
  let ok = 0;

  for (const row of rows) {
    const verdict = await verify(row);
    if (verdict === 'stale') stale.push(row);
    else if (verdict === 'unreachable') unreachableRows.push(row);
    else ok++;

    // A game cannot have actions without a second player; in practice these are
    // abandoned test games where a lone move was recorded.
    if (verdict !== 'stale' && row.action_count > 0 && !row.player_two) suspicious.push(row);
  }

  const show = (label: string, list: Row[]) => {
    if (list.length === 0) return;
    console.error(`${label} (${list.length})`);
    for (const r of list) {
      console.error(`  ${r.rkey}  status=${r.status} actions=${r.action_count} created=${r.created_at.slice(0, 10)}  ${r.id}`);
    }
    console.error('');
  };

  show('STALE — source record no longer exists', stale);
  show('ACTIVE WITHOUT OPPONENT — actions but no second player', suspicious);
  show('UNREACHABLE — PDS could not be resolved or queried, needs a manual look', unreachableRows);

  console.error(
    `Summary: ${ok} ok, ${stale.length} stale, ${suspicious.length} suspicious, ${unreachableRows.length} unreachable\n`
  );

  if (stale.length > 0 && !prune) {
    console.error('Re-run with --prune to delete the STALE rows from the index.');
  }
  if (prune && stale.length > 0) {
    const list = stale.map((r) => `'${r.rkey.replace(/'/g, "''")}'`).join(',');
    console.error(`Deleting ${stale.length} stale row(s)...`);
    execute(`DELETE FROM games WHERE rkey IN (${list})`);
    console.error('Done. The record is untouched; visiting /game/<rkey> would re-import it.');
  }
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});