bet-bot diag v3 - parser miss investigation

bet_diag3.py

"""Diagnostic v3: figure out why touch23's 6/2 #bets message was not parsed.

Specifically: did the bot see ANY June messages from touch23 in #bets?
And what does the bot think the #bets channel ID is?
"""
import sqlite3, os, sys, datetime, json

DB = '/data/bets.sqlite3'
con = sqlite3.connect(DB)
con.row_factory = sqlite3.Row
c = con.cursor()

print("=" * 60)
print("PARSER MISS DIAGNOSTIC")
print("=" * 60)

# 1. Channel IDs the bot scans (from env)
print("\n--- Bot's configured channel IDs ---")
for var in ("CHANNEL_IDS", "LONGSHOT_CHANNEL_IDS", "FUTURES_CHANNEL_IDS", "BETS_CHANNEL_ID"):
    print(f"  {var}: {os.environ.get(var, '(not set)')}")

# 2. All distinct channels the bot has EVER ingested from
print("\n--- All channels seen in DB (sorted by last activity) ---")
for r in c.execute("""
    SELECT channel_id, channel_name, COUNT(*) c, MAX(posted_at_utc) last_seen
    FROM bets GROUP BY channel_id ORDER BY last_seen DESC
"""):
    print(f"  {r['channel_id']:<20} {r['channel_name']:<15} count={r['c']:<4} last={r['last_seen']}")

# 3. All June activity by user across all channels
print("\n--- All June bets in DB by author x channel ---")
for r in c.execute("""
    SELECT author_name, channel_name, COUNT(*) c, MIN(posted_at_utc) first, MAX(posted_at_utc) last
    FROM bets
    WHERE bet_date >= '2026-06-01'
    GROUP BY author_name, channel_name
    ORDER BY author_name, channel_name
"""):
    print(f"  {r['author_name']:<20} {r['channel_name']:<15} {r['c']} bets  first={r['first'][:16]}  last={r['last'][:16]}")

# 4. Specifically touch23 - any activity at all in June in #bets?
print("\n--- touch23 in #bets channel (1203003957979521075), all of 2026 ---")
for r in c.execute("""
    SELECT bet_date, posted_at_utc, substr(selection,1,50) sel, sport
    FROM bets
    WHERE author_name='touch23' AND channel_id='1203003957979521075'
    ORDER BY posted_at_utc DESC LIMIT 20
"""):
    print(f"  {r['bet_date']} {r['posted_at_utc'][:16]} {r['sport']:<14} {r['sel']}")

# 5. touch23 EVERYWHERE in last 30 days
print("\n--- touch23 ALL channels last 30 days ---")
for r in c.execute("""
    SELECT bet_date, posted_at_utc, channel_name, channel_id, substr(selection,1,40) sel
    FROM bets
    WHERE author_name='touch23' AND bet_date >= '2026-05-08'
    ORDER BY posted_at_utc DESC
"""):
    print(f"  {r['bet_date']} {r['posted_at_utc'][:16]} ch={r['channel_name']:<13} ({r['channel_id']}) {r['sel']}")

# 6. ALL #bets channel activity since 5/01 — see if bot is even reading the channel
print("\n--- ALL activity in #bets (channel_id=1203003957979521075) since 5/01 ---")
for r in c.execute("""
    SELECT COUNT(*) c FROM bets
    WHERE channel_id='1203003957979521075' AND bet_date >= '2026-05-01'
"""):
    print(f"  total: {r['c']}")
for r in c.execute("""
    SELECT bet_date, posted_at_utc, author_name, substr(selection,1,40) sel
    FROM bets
    WHERE channel_id='1203003957979521075' AND bet_date >= '2026-05-15'
    ORDER BY posted_at_utc DESC LIMIT 30
"""):
    print(f"  {r['bet_date']} {r['posted_at_utc'][:16]} {r['author_name']:<18} {r['sel']}")

# 7. kv_store - what did the bot record as last scan?
print("\n--- kv_store / scan markers ---")
try:
    for r in c.execute("SELECT * FROM kv_store"):
        print(f"  {dict(r)}")
except Exception as e:
    print(f"  ({e})")

print("\nDONE")