#!/usr/bin/env python3
"""Delete old low-quality replay payloads while preserving game results."""

from __future__ import annotations

import argparse
import asyncio
from datetime import datetime, timedelta, timezone

async def cleanup(dsn: str, older_than_days: int, dry_run: bool, vacuum: bool) -> None:
  try:
    import asyncpg
  except ImportError as exc:
    raise RuntimeError("asyncpg is required for PostgreSQL cleanup") from exc

  cutoff = datetime.now(timezone.utc) - timedelta(days=max(1, older_than_days))
  connection = await asyncpg.connect(dsn=dsn)
  try:
    candidates = int(await connection.fetchval("""
      SELECT count(*)
      FROM games
      WHERE has_replay
        AND quality_status = 'low_quality'
        AND COALESCE(ended_at, started_at) < $1
    """, cutoff))
    rows = await connection.fetchrow("""
      SELECT
        (SELECT count(*) FROM turns t JOIN games g USING (game_id)
          WHERE g.has_replay AND g.quality_status = 'low_quality'
            AND COALESCE(g.ended_at, g.started_at) < $1) AS turns,
        (SELECT count(*) FROM snake_turns t JOIN games g USING (game_id)
          WHERE g.has_replay AND g.quality_status = 'low_quality'
            AND COALESCE(g.ended_at, g.started_at) < $1) AS snake_turns,
        (SELECT count(*) FROM game_snakes t JOIN games g USING (game_id)
          WHERE g.has_replay AND g.quality_status = 'low_quality'
            AND COALESCE(g.ended_at, g.started_at) < $1) AS game_snakes
    """, cutoff)
    print(
      f"candidates before {cutoff.isoformat()}: games={candidates:,}, "
      f"turns={rows['turns']:,}, snake_turns={rows['snake_turns']:,}, "
      f"game_snakes={rows['game_snakes']:,}"
    )
    if dry_run or candidates == 0:
      print("dry run: no rows changed" if dry_run else "nothing to clean")
      return

    async with connection.transaction():
      game_ids = await connection.fetch("""
        SELECT game_id FROM games
        WHERE has_replay
          AND quality_status = 'low_quality'
          AND COALESCE(ended_at, started_at) < $1
        FOR UPDATE
      """, cutoff)
      ids = [row["game_id"] for row in game_ids]
      await connection.execute("DELETE FROM snake_turns WHERE game_id = ANY($1::text[])", ids)
      await connection.execute("DELETE FROM turns WHERE game_id = ANY($1::text[])", ids)
      await connection.execute("DELETE FROM game_snakes WHERE game_id = ANY($1::text[])", ids)
      await connection.execute("""
        UPDATE games
        SET has_replay = FALSE, quality_status = 'low_quality'
        WHERE game_id = ANY($1::text[])
      """, ids)
    print(f"cleaned replay payloads for {len(ids):,} games; result rows preserved")
    if vacuum:
      await connection.execute("VACUUM (ANALYZE) games")
      await connection.execute("VACUUM (ANALYZE) game_snakes")
      await connection.execute("VACUUM (ANALYZE) turns")
      await connection.execute("VACUUM (ANALYZE) snake_turns")
      print("vacuum/analyze complete")
  finally:
    await connection.close()

def main() -> None:
  parser = argparse.ArgumentParser(description=__doc__)
  parser.add_argument("--dsn", required=True)
  parser.add_argument("--older-than-days", type=int, default=30)
  parser.add_argument("--execute", action="store_true", help="Apply deletion; default is dry-run")
  parser.add_argument("--vacuum", action="store_true")
  args = parser.parse_args()
  asyncio.run(cleanup(args.dsn, args.older_than_days, not args.execute, args.vacuum))

if __name__ == "__main__":
  main()