X&pi?AdZddlZddlZddlZddlZddlmZmZmZdZdZ dZ dZ dZ d Z d Zd Zed k(reyy) z4 Critical Data Gap Backfill - Final Working Version N)datetimetimezone timedeltag333333?c~d} t|d5}|D]S}|jds|jdddjjddccdddS dddt j jdd jddS#1swY;xYw#t$rYIwxYw) Nz/var/www/html/eventheodds/.envrzSPORTS_DATABASE_URL==?rSPORTS_DATABASE_URL)open startswithsplitstripFileNotFoundErrorosenvironget)env_pathflines A/var/www/html/eventheodds/scripts/backfill_critical_gaps_final.py load_db_urlr s/H (C  GA G??#9:::c1-a0668>>sCAFF G G G G ::>>/ 4 : :3 ? BB  G G    s9 B0B$6B$ B0(B$*B0$B-)B00 B<;B<cZt}|s tdtj|S)NzSPORTS_DATABASE_URL not set)r RuntimeErrorpsycopg2connect)db_urls rget_db_connectionrs) ]F 899   F ##ctdtdtdt}|j}|jd|jd|j d}dD]}td|d d }d d d dd d|dd} t j ||d}|jd k7rtd|jc|j}|j dg}tdt|d|D]} |jd|| j d| j d| j dxs| j d| j d| j d| j d| j d| j d | j d!| j d"| j d#| j d$| j d%| j d&| j d'f|d(z }"|j |j|jtd,|d-|S#t$r)} td)| |jYd*} ~ d*} ~ wwxYw#t$r)} td+| |jYd*} ~ hd*} ~ wwxYw).z1Backfill NHL goalie stats from NHL Stats REST API= ============================================================z1. NHL GOALIE STATS BACKFILL<============================================================a CREATE TABLE IF NOT EXISTS "NHLGoalieStats" ( id BIGSERIAL PRIMARY KEY, season VARCHAR(20), player_id INT, goalie_name VARCHAR(100), team_abbrev VARCHAR(10), games_played INT, games_started INT, wins INT, losses INT, ot_losses INT, save_pctg FLOAT, goals_against_avg FLOAT, shutouts INT, shots_against INT, saves INT, goals_against INT, time_on_ice INT, updated_at TIMESTAMP DEFAULT NOW(), UNIQUE(season, player_id) ) zWCREATE INDEX IF NOT EXISTS idx_nhlgoalie_team ON "NHLGoalieStats" (team_abbrev, season)r)2025202620242025z Fetching goalies for season z...z1https://api.nhle.com/stats/rest/en/goalie/summaryfalsez/[{"property":"gamesPlayed","direction":"DESC"}]z seasonId=z and gameTypeId=2) isAggregateisGamesortstartlimit cayenneExp)paramstimeoutz API returned dataz Found z goaliesa INSERT INTO "NHLGoalieStats" ( season, player_id, goalie_name, team_abbrev, games_played, games_started, wins, losses, ot_losses, save_pctg, goals_against_avg, shutouts, shots_against, saves, goals_against, time_on_ice ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (season, player_id) DO UPDATE SET games_played = EXCLUDED.games_played, wins = EXCLUDED.wins, losses = EXCLUDED.losses, save_pctg = EXCLUDED.save_pctg, goals_against_avg = EXCLUDED.goals_against_avg, shutouts = EXCLUDED.shutouts, saves = EXCLUDED.saves, updated_at = NOW() playerIdgoalieFullName teamAbbrevslastTeamAbbrev gamesPlayed gamesStartedwinslossesotLossessavePctggoalsAgainstAverageshutouts shotsAgainstsaves goalsAgainst timeOnIcer z Insert error: Nz Fetch error: u ✅ Inserted z goalie records) printrcursorexecutecommitrequestsr status_codejsonlen Exceptionrollbackclose) conncurtotal season_idurlr/respr1goaliesgoaliees rbackfill_nhl_goaliesrV#s - () &M  D ++-CKK .KKijKKM E-=  .yk=>A"E%i[0AB  0 <<FB?D3&(8(8'9:;99;Dhhvr*G HS\N(3 4! $$KK!$"6::j#96::FV;W =1QVZZ@P5Q =16::n3M 6*FJJx,@&**ZBX :. ;P0Q :. >0J 7+VZZ-G ;/ #6QJE; $D KKMs=~IIKJJL OE7/ 23 L!$.qc23MMOO$   OA3' ( MMOO sJ?J-AJ-DI81J-8 J*J%J-%J**J-- K6KKctdtdtdt}|j}|jd|jd|jd|jd|j |jd|j }|j |j |j td |d |S) z>Copy NBA player on/off impact from existing PlayerImpact tabler"z2. NBA PLAYER ON/OFF BACKFILLr#z-DROP TABLE IF EXISTS "NBAPlayerOnOff" CASCADEa CREATE TABLE "NBAPlayerOnOff" ( id BIGSERIAL PRIMARY KEY, season VARCHAR(20), player_id VARCHAR(100), player_name VARCHAR(100), team VARCHAR(50), games_played INT, games_missed INT, team_wins_with INT, team_losses_with INT, team_wins_without INT, team_losses_without INT, win_pct_with FLOAT, win_pct_without FLOAT, win_pct_diff FLOAT, avg_pts_with FLOAT, avg_pts_without FLOAT, net_impact FLOAT, updated_at TIMESTAMP DEFAULT NOW(), UNIQUE(season, player_id) ) zACREATE INDEX idx_nbaonoff_team ON "NBAPlayerOnOff" (team, season)zHCREATE INDEX idx_nbaonoff_impact ON "NBAPlayerOnOff" (win_pct_diff DESC)a INSERT INTO "NBAPlayerOnOff" ( season, player_id, player_name, team, games_played, games_missed, team_wins_with, team_losses_with, team_wins_without, team_losses_without, win_pct_with, win_pct_without, win_pct_diff, avg_pts_with, avg_pts_without, net_impact ) SELECT CAST(season AS VARCHAR), "playerId", "playerName", team, "gamesPlayed", "gamesMissed", "teamWinsWithPlayer", "teamLossesWithPlayer", "teamWinsWithoutPlayer", "teamLossesWithoutPlayer", "winPctWithPlayer", "winPctWithoutPlayer", "winPctDiff", "avgPointsWithPlayer", "avgPointsWithoutPlayer", "netImpact" FROM "PlayerImpact" WHERE league = 'nba' ON CONFLICT (season, player_id) DO NOTHING ✅ Created z, NBA player on/off records from PlayerImpact)rBrrCrDrErowcountrL)rMrNcopieds rbackfill_nba_on_offr[s - )* &M  D ++-CKK?@KK .KKSTKKZ[KKMKK <\\FKKMIIKJJL L L MN Mr ctdtdtdt}|j}|jd|jd|j d}t j }tddD]B}|t|z }|jd }tjtd |} tj|d }|jd k7rj|j!} | jdg} | D]} | jd} | s| jdg} | s-| d}|jdijdijddsetjtd| } tj|d }|jd k7r|j!}|jdi}|jdg}|sdx}}|djdgD]}|jddk(r|}|}|r|s|jdg}|jdg}t#|dk\r/t#|dk\r |jdd| | jd|jd ijd!|jd ijd!t%|djd"dt%|d#jd"dt%|djd"dt%|d#jd"dt%|jd$dt%|jd$df |j&dkDr|d#z } |dkDs|d%zdk(s$td&|d'|j E|j |j+|j+td(|d)|S#t($r }Yd}~d}~wwxYw#t($r }Yd}~d}~wwxYw)*zABackfill soccer half scoring from ESPN API using summary endpointr"z3. SOCCER HALF SCORING BACKFILLr#a CREATE TABLE IF NOT EXISTS "SoccerPeriodScoring" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(20), external_game_id VARCHAR(100), game_date TIMESTAMP, home_team VARCHAR(100), away_team VARCHAR(100), home_1h INT, home_2h INT, away_1h INT, away_2h INT, home_total INT, away_total INT, created_at TIMESTAMP DEFAULT NOW(), UNIQUE(league, external_game_id) ) z]CREATE INDEX IF NOT EXISTS idx_soccerperiod_date ON "SoccerPeriodScoring" (league, game_date)rZ)daysz%Y%m%dzLhttps://site.api.espn.com/apis/site/v2/sports/soccer/eng.1/scoreboard?dates=r.)r0r'eventsid competitionsstatustype completedFzIhttps://site.api.espn.com/apis/site/v2/sports/soccer/eng.1/summary?event=headerN competitorshomeAwayhome linescoresa  INSERT INTO "SoccerPeriodScoring" ( league, external_game_id, game_date, home_team, away_team, home_1h, home_2h, away_1h, away_2h, home_total, away_total ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, external_game_id) DO NOTHING epldateteam displayName displayValuer score z Progress: z matches with half scoring...u ✅ Added z! soccer matches with half scoring)rBrrCrDrErnowrangerstrftimetimesleep REQUEST_DELAYrFrrGrHrIintrYrJrL)rMrNrO base_date days_backrldate_strrQrRr1r_eventevent_idracomp summary_url summary_resp summary_datarecomps home_data away_datachome_lsaway_lsrUs rbackfill_soccer_half_scoringrs - +, &M  D ++-CKK $KKopKKM E I1b\W 9)44==* =!]]e\fgK <<R0D3&99;DXXh+F@  99T? %yy< ##Axx"-11&"=AA+uU =) ijrist 0#+<< R#HL#//36 #/#4#4#6L)--h;F"JJ~r:E  ,00I "1X\\-<*55,6()I()I * %I 'mmL"=G'mmL"=G7|q(S\Q-> %"8UYYv->%MM&"599-H%MM&"599-H ~q AB ~q AB ~q AB ~q AB gq 9: gq 9: (< ?@ L!   sb&P.:B'P."&PP. 6P?P.;P;P.=E"PP. P+ P.&P++P.. Q=Qctdtdtdt}|j}|jd|jd|j  |jd|j |jd|j }|j td|d |jd |j }|j |j|jtd |d |S#|j YxYw) z%Create injury status history trackingr"z4. INJURY TIMESTAMP HISTORYr#a CREATE TABLE IF NOT EXISTS "InjuryStatusHistory" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(20), player_external_id VARCHAR(100), player_name VARCHAR(200), team VARCHAR(50), status VARCHAR(50), previous_status VARCHAR(50), injury VARCHAR(200), status_changed_at TIMESTAMP, created_at TIMESTAMP DEFAULT NOW() ) ztCREATE INDEX IF NOT EXISTS idx_injuryhistory_player ON "InjuryStatusHistory" (player_external_id, status_changed_at)zOALTER TABLE "PlayerInjury" ADD COLUMN IF NOT EXISTS status_changed_at TIMESTAMPz UPDATE "PlayerInjury" SET status_changed_at = COALESCE(status_changed_at, "updatedAt", "createdAt", NOW()) WHERE status_changed_at IS NULL z Set timestamps for z injury recordsa INSERT INTO "InjuryStatusHistory" ( league, player_external_id, player_name, team, status, injury, status_changed_at ) SELECT league, "playerExternalId", "playerName", team, status, "injuryType", COALESCE(status_changed_at, "updatedAt", NOW()) FROM "PlayerInjury" WHERE status IS NOT NULL rXz injury history records)rBrrCrDrErKrYrL)rMrNupdatedsnapshots rbackfill_injury_timestampsrjs - '( &M  D ++-CKK   KKGHKKM ef KK llGKKM !'/ :;KK ||HKKMIIKJJL L "9 :; OI s /!D**D>cntdtdtdtjtjj tdi} t |d< t|d< t|d < t|d <td tdtdtd|jddtd|jddtd|jd dtd|jd dtd|S#t$r2}td|ddl}|jd|d<Yd}~d}~wwxYw#t$r2}td|ddl}|jd|d<Yd}~6d}~wwxYw#t$r2}td |ddl}|jd|d <Yd}~fd}~wwxYw#t$r2}td |ddl}|jd|d <Yd}~d}~wwxYw)Nr#z"CRITICAL DATA GAP BACKFILL - FINALzTime: nhl_goalieszNHL Goalie error: r nba_on_offzNBA On/Off error: soccer_halveszSoccer error: injury_historyzInjury error: r"zBACKFILL COMPLETEz NHL Goalies: z NBA On/Off: zSoccer Half Scores: zInjury History: )rBrrrrutc isoformatrVrJ traceback print_excr[rrr)resultsrUrs rmainrs  &M ./ F8<< -779: ;< &MG#!5!7 " 3 5 %#?#A &$>$@ ! -  &M M'++mQ78 9: L\156 78 _a!@ A BC W[[)91=> ?@ &M NU # "1#&'!"  # " "1#&' !  " % qc"##$ % & qc"#$% ! &s`" D?0 E=> F; G9? E:'E55E:= F8'F33F8; G6'G11G69 H4'H//H4__main__)__doc__rFrrrurrrrwrrrVr[rrr__name__r rrsd 22  C$i^Ld@LCR5p zFr