bet-bot live DB diagnostic

bet_diag.py

"""
Bet bot live DB diagnostic — read-only.
Prints state of /data/bets.sqlite3 so we can plan the resettle + bot fix.
"""
import sqlite3, os, sys, datetime

DB = '/data/bets.sqlite3'

print("=" * 60)
print("BET BOT LIVE DB DIAGNOSTIC")
print("=" * 60)

if not os.path.exists(DB):
    print(f"FATAL: {DB} does not exist")
    sys.exit(1)

size = os.path.getsize(DB)
mtime = datetime.datetime.fromtimestamp(os.path.getmtime(DB), datetime.timezone.utc).isoformat()
print(f"file: {DB}")
print(f"size: {size:,} bytes")
print(f"mtime: {mtime}")

con = sqlite3.connect(DB)
con.row_factory = sqlite3.Row
c = con.cursor()

total = c.execute('SELECT COUNT(*) FROM bets').fetchone()[0]
latest = c.execute('SELECT MAX(bet_date) FROM bets').fetchone()[0]
pending = c.execute("SELECT COUNT(*) FROM bets WHERE result='pending'").fetchone()[0]
print(f"\ntotal_bets: {total}")
print(f"latest_bet_date: {latest}")
print(f"pending_count: {pending}")

print("\n--- result distribution ---")
for r in c.execute('SELECT result, COUNT(*) c FROM bets GROUP BY result ORDER BY c DESC'):
    print(f"  {r['result']!r}: {r['c']}")

print("\n--- result_source distribution ---")
for r in c.execute('SELECT result_source, COUNT(*) c FROM bets GROUP BY result_source ORDER BY c DESC'):
    print(f"  {r['result_source']!r}: {r['c']}")

# Count auto_default rows by date to see if auto-resolve has fired since 5/11
print("\n--- auto_default by month (was auto-resolve firing?) ---")
for r in c.execute("SELECT substr(bet_date,1,7) ym, COUNT(*) c FROM bets WHERE result_source='auto_default' GROUP BY ym ORDER BY ym"):
    print(f"  {r['ym']}: {r['c']}")

print("\n--- explicit by month (was reaction grading firing?) ---")
for r in c.execute("SELECT substr(bet_date,1,7) ym, COUNT(*) c FROM bets WHERE result_source='explicit' GROUP BY ym ORDER BY ym"):
    print(f"  {r['ym']}: {r['c']}")

print("\n--- pending rows (all) ---")
for r in c.execute("SELECT bet_date, posted_at_utc, author_name, channel_name, sport, substr(selection,1,50) sel, message_id, channel_id FROM bets WHERE result='pending' ORDER BY bet_date DESC"):
    print(f"  {dict(r)}")

print("\n--- kv table (last successful run markers) ---")
try:
    for r in c.execute("SELECT * FROM kv ORDER BY key"):
        print(f"  {dict(r)}")
except Exception as e:
    print(f"  (no kv table: {e})")

print("\n--- last 10 bets by posted_at_utc (is bot still ingesting?) ---")
for r in c.execute("SELECT bet_date, posted_at_utc, author_name, channel_name, substr(selection,1,40) sel, result, result_source FROM bets ORDER BY posted_at_utc DESC LIMIT 10"):
    print(f"  {dict(r)}")

# Channel breakdown of pending
print("\n--- pending by channel ---")
for r in c.execute("SELECT channel_name, channel_id, COUNT(*) c FROM bets WHERE result='pending' GROUP BY channel_id ORDER BY c DESC"):
    print(f"  {dict(r)}")

con.close()
print("\nDIAGNOSTIC COMPLETE")