i%dZddlZddlZddlZddlZddlZddlmZmZmZdZ de de fdZ de fdZ d e fd Zd edefd Zd eddfdZdZedk(reyy)a Backfill NCAAB SportsGame rows from orphaned OddsSnapshot records. Steps: 1. Find all OddsSnapshot rows where league='ncaab' and gameId IS NULL. 2. Get distinct (homeTeam, awayTeam, gameDate::date) combinations. 3. For each combination not already covered by a SportsGame row, insert one. 4. Link OddsSnapshot rows to their newly created (or pre-existing) SportsGame rows. Usage: python3 ncaab_backfill_games_from_odds.py [--dry-run] N)datetimedatetimezonez/var/www/html/eventheodds/.envpathreturncli}t|5}|D]}|j}|r|jdr'd|vr,|jd\}}}|j}|j}t |dk\r|ddvr|d|dk(r|dd}|||< ddd|S#1swY|SxYw) zFParse a simple KEY=VALUE .env file, ignoring comments and blank lines.#=r)"'N)openstrip startswith partitionlen)renvfhlinekey_values C/var/www/html/eventheodds/scripts/ncaab_backfill_games_from_odds.pyload_envrs C d r D::Sa CH   J  Js BB))B3cptt}|jdd}|stdtz|S)NSPORTS_DATABASE_URLz!SPORTS_DATABASE_URL not found in )rENV_FILEget RuntimeError)rurls r get_db_urlr$2s6 8 C ''' ,C >IJJ Jr#cT|jdd}tj|S)z?Return a psycopg2 connection, stripping any ?schema=... suffix.?r)splitpsycopg2connect)r# clean_urls rr*r*:s% #q!I   I &&r% game_datecV|jdk\r |jS|jdz S)uw Return the academic-year season label for a given date. Convention observed in the DB: - Aug–Dec of year Y -> season Y (fall semester of Y/Y+1) - Jan–Jul of year Y -> season Y-1 (spring semester of Y-1/Y) Examples: Nov 2025 -> 2025 Jan 2026 -> 2025 Apr 2026 -> 2025 Nov 2024 -> 2024 Jan 2025 -> 2024 r)monthyear)r,s rcompute_seasonr1Ds)!~~~~!!r%dry_runcdtj}tdtjddtd|d|t }t |}d|_|jtjj}|jd|jd }td ||d k(rtd |jy|jd |j}tdt!|g}|D]}|d} |d} |d} |d} |jd| | | f|j} | rtd| d| d| d| dd| d ]t#| }| |krdnd}|j%| | | ||| dtd| d| d| d|d |d!| d td"t!||std#|jyd }d }tjt&j(}|D]}t|dj*|dj,|dj.t&j($}|r/td%|dd|d&d|d'd|d(d |d) |d*z }y|jd+|d(||d'|d&|d)|f|j}|r5|d*z }td,|dd|dd|d&d|d'd|d(d |d) |d*z }td-|dd|d&d|d'td.|d/||r7td0td1|j1|jytd2|jd3|j2}td4||jd|jd }td5||j5td6tjdd7|j|jy)8N[z%Y-%m-%d %H:%M:%Sz+] Starting NCAAB backfill from OddsSnapshotz dry_run=z today=F)cursor_factoryz~ SELECT COUNT(*) AS cnt FROM "OddsSnapshot" WHERE league = 'ncaab' AND "gameId" IS NULL cntz5 Orphaned OddsSnapshot rows (ncaab, gameId IS NULL): rzNothing to do.aX SELECT "homeTeam", "awayTeam", "gameDate"::date AS game_date, COUNT(*) AS snapshot_count FROM "OddsSnapshot" WHERE league = 'ncaab' AND "gameId" IS NULL GROUP BY "homeTeam", "awayTeam", "gameDate"::date ORDER BY "gameDate"::date, "homeTeam" z,Distinct (homeTeam, awayTeam, date) combos: homeTeamawayTeamr,snapshot_countz SELECT id FROM "SportsGame" WHERE league = 'ncaab' AND "homeTeam" = %s AND "awayTeam" = %s AND "gameDate"::date = %s LIMIT 1 z SKIP z z @ z -> already exists (id=idz, z snapshots)final scheduled)homeawayr,seasonstatusn_snapsz QUEUE z season=z status=z (z Games to insert: zNo new SportsGame rows needed.)tzinfoz DRY-RUN INSERT: r>r=r?r@raJ INSERT INTO "SportsGame" (league, season, "gameDate", "homeTeam", "awayTeam", status, "updatedAt") VALUES ('ncaab', %s, %s, %s, %s, %s, %s) ON CONFLICT (league, season, "gameDate", "homeTeam", "awayTeam") DO NOTHING RETURNING id z INSERTED id=z CONFLICT (no-op) z Inserted: z Conflicts/skipped: zL DRY-RUN: would now run UPDATE to link OddsSnapshot rows to SportsGame rows.zNo changes committed.z9 Linking orphaned OddsSnapshot rows to SportsGame rows...aP UPDATE "OddsSnapshot" os SET "gameId" = g.id FROM "SportsGame" g WHERE os.league = 'ncaab' AND os."gameId" IS NULL AND g.league = 'ncaab' AND g."homeTeam" = os."homeTeam" AND g."awayTeam" = os."awayTeam" AND g."gameDate"::date = os."gameDate"::date zOddsSnapshot rows linked: z&Remaining orphaned OddsSnapshot rows: z [z] Done. Committed.)rtodayprintrnowr$r* autocommitcursorr)extrasRealDictCursorexecutefetchoneclosefetchallrr1appendrutcr0r/dayrollbackrowcountcommit)r2rCr#conncur orphan_countcombos to_insertrowr=r>gdaterAexistingr?r@insertedskipped_conflictnow_tsggame_dtresultlinked remainings rrunrd]s JJLE Ahlln. //Z [\ Jwixw /0 ,C 3%(L B<. QRq   KK   \\^F 8V FG Ip::K &'  D%  "<<>  HUG2dV3tf4LXVZ^L\\^_f^ggrs t#E*F % W;F   "  "   HUG2dV3tfIfXYvhVYZaYbbmn o=p@ I/ 01  ./  H \\(,, 'F "V1[>..+0D0Da nFXFX"*,,0  &q~&6b6 3qykak])AhK=B C MH    hK  fI fI hK      MH N6$<.1[>2B"QvYKsSTU[S\R]ak])AhK=B C  !  (;(81V9+S6 T UE"VH L "78H7I JK  ]^ %&   FGKK   \\F &vh /0KK  u%I 29+ >?KKM C 011C DEIIKJJLr%ctjd}|jddd|j} t |j y#t $r#tdtjd Yyt$rQ}td |tj d dl }|jtjd Yd}~yd}~wwxYw) NzBBackfill NCAAB SportsGame rows from orphaned OddsSnapshot records.) descriptionz --dry-run store_truez7Print what would be done without making any DB changes.)actionhelp)r2z Interrupted.rz ERROR: )filer)argparseArgumentParser add_argument parse_argsrdr2KeyboardInterruptrDsysexit Exceptionstderr traceback print_exc)parserargsexcrts rmainrys  $ $XF  F    D  DLL!     #cjj1  sA)C>CACC__main__)__doc__osrprkr)psycopg2.extrasrrrr strdictrr$r*intr1boolrdry__name__r%rrs  -- ,34(C''"d"s"2mm$mh. zFr%