/**
 * Backfill games from UFOS (ATProto record index) into D1 database.
 * Also checks Constellation for moves to determine player_two and status.
 *
 * Usage:
 *   # Generate SQL file
 *   npx tsx scripts/backfill-games.ts > scripts/backfill-games.sql
 *
 *   # Run against D1
 *   npx wrangler d1 execute atprotogo-db --remote --file=scripts/backfill-games.sql
 */

const UFOS_API = 'https://ufos-api.microcosm.blue';
const CONSTELLATION_API = 'https://constellation.microcosm.blue/xrpc';

interface UfosRecord {
  did: string;
  collection: string;
  rkey: string;
  record: {
    $type: string;
    boardSize: number;
    createdAt: string;
    playerOne?: string;
    playerTwo?: string;
    status: string;
    handicap?: number;
    winner?: string;
  };
  time_us: number;
}

interface ConstellationBacklink {
  did: string;
  collection: string;
  rkey: string;
}

interface ConstellationResponse {
  records: ConstellationBacklink[];
  total: number;
  cursor: string | null;
}

async function fetchAllGames(): Promise<UfosRecord[]> {
  const response = await fetch(`${UFOS_API}/records?collection=boo.sky.go.game`);
  if (!response.ok) {
    throw new Error(`UFOS API error: ${response.status}`);
  }
  return response.json();
}

async function fetchGameMoves(gameUri: string): Promise<ConstellationBacklink[]> {
  const allMoves: ConstellationBacklink[] = [];
  let cursor: string | undefined;

  try {
    do {
      const params = new URLSearchParams({
        subject: gameUri,
        source: 'boo.sky.go.move:game',
        limit: '100',
      });
      if (cursor) params.set('cursor', cursor);

      const res = await fetch(
        `${CONSTELLATION_API}/blue.microcosm.links.getBacklinks?${params}`,
        { headers: { Accept: 'application/json' } }
      );

      if (!res.ok) break;

      const body: ConstellationResponse = await res.json();
      allMoves.push(...body.records);
      cursor = body.cursor ?? undefined;
    } while (cursor);
  } catch (err) {
    console.error(`Failed to fetch moves for ${gameUri}:`, err);
  }

  return allMoves;
}

async function fetchGamePasses(gameUri: string): Promise<ConstellationBacklink[]> {
  const allPasses: ConstellationBacklink[] = [];
  let cursor: string | undefined;

  try {
    do {
      const params = new URLSearchParams({
        subject: gameUri,
        source: 'boo.sky.go.pass:game',
        limit: '100',
      });
      if (cursor) params.set('cursor', cursor);

      const res = await fetch(
        `${CONSTELLATION_API}/blue.microcosm.links.getBacklinks?${params}`,
        { headers: { Accept: 'application/json' } }
      );

      if (!res.ok) break;

      const body: ConstellationResponse = await res.json();
      allPasses.push(...body.records);
      cursor = body.cursor ?? undefined;
    } while (cursor);
  } catch (err) {
    console.error(`Failed to fetch passes for ${gameUri}:`, err);
  }

  return allPasses;
}

function escapeSQL(str: string | null | undefined): string {
  if (!str) return '';
  return str.replace(/'/g, "''");
}

async function main() {
  console.error('Fetching games from UFOS...');
  const games = await fetchAllGames();
  console.error(`Found ${games.length} games`);
  console.error('');

  // Output SQL INSERT statements
  console.log('-- Backfill games from UFOS with Constellation move data');
  console.log('-- Generated at:', new Date().toISOString());
  console.log(`-- Total games: ${games.length}`);
  console.log('');

  for (const game of games) {
    const uri = `at://${game.did}/boo.sky.go.game/${game.rkey}`;
    const now = new Date().toISOString();
    const record = game.record;

    // The creator is the DID that owns the record
    const creatorDid = game.did;
    // playerOne might be different from creator in some cases
    let playerOne = record.playerOne || creatorDid;
    let playerTwo = record.playerTwo || null;
    let status = record.status;
    let actionCount = 0;
    let lastActionType: string | null = null;

    console.error(`Processing ${game.rkey}...`);

    // Fetch moves and passes from Constellation
    const [moves, passes] = await Promise.all([
      fetchGameMoves(uri),
      fetchGamePasses(uri),
    ]);

    actionCount = moves.length + passes.length;

    if (moves.length > 0 || passes.length > 0) {
      // Find all unique DIDs that made moves/passes
      const playerDids = new Set<string>();

      for (const move of moves) {
        playerDids.add(move.did);
      }

      for (const pass of passes) {
        playerDids.add(pass.did);
      }

      // If we have moves but no playerTwo in the record, try to determine it
      if (!playerTwo && playerDids.size > 0) {
        // playerTwo is any DID that's not playerOne
        for (const did of playerDids) {
          if (did !== playerOne) {
            playerTwo = did;
            break;
          }
        }
      }

      // Update status to active if there are moves and status is waiting
      if (status === 'waiting' && (moves.length > 0 || passes.length > 0)) {
        status = 'active';
      }

      // Determine last action type (we don't have timestamps from backlinks, so just use 'move' or 'pass')
      if (passes.length > 0) {
        lastActionType = 'pass';
      } else if (moves.length > 0) {
        lastActionType = 'move';
      }
    }

    console.error(`  -> ${moves.length} moves, ${passes.length} passes, status: ${status}, playerTwo: ${playerTwo || 'none'}`);

    const sql = `INSERT OR REPLACE INTO games (id, rkey, creator_did, player_one, player_two, board_size, status, action_count, last_action_type, winner, handicap, created_at, updated_at) VALUES (
  '${escapeSQL(uri)}',
  '${escapeSQL(game.rkey)}',
  '${escapeSQL(creatorDid)}',
  '${escapeSQL(playerOne)}',
  ${playerTwo ? `'${escapeSQL(playerTwo)}'` : 'NULL'},
  ${record.boardSize},
  '${escapeSQL(status)}',
  ${actionCount},
  ${lastActionType ? `'${escapeSQL(lastActionType)}'` : 'NULL'},
  ${record.winner ? `'${escapeSQL(record.winner)}'` : 'NULL'},
  ${record.handicap || 0},
  '${escapeSQL(record.createdAt)}',
  '${escapeSQL(now)}'
);`;
    console.log(sql);
    console.log('');
  }

  console.error('');
  console.error('Done! Run with:');
  console.error('  npx wrangler d1 execute atprotogo-db --remote --file=scripts/backfill-games.sql');
}

main().catch(console.error);