oi9TdZddlZddlZddlZddlmZmZmZddlmZiddddddd d d d d d d dddddddddddddddddddddiddddddd dd!dd"dd#d$d%d$d&d$d'd(d)d(d*d(d+d,d-d,d.d,d/d0d1d0id2d0d3d4d5d4d6d4d7d8d9d8d:d8d;d<d=d<d>d<d?d@dAd@dBd@dCdDdEdDdFdDdGdHidIdHdJdHdKdLdMdLdNdLdOdLdPdQdRdQdSdQdTdUdVdUdWdUdXdYdZdYd[dYd\dYd]d^id_d^d`d^dadbdcdbdddbdedbdfdgdhdgdidgdjdkdldkdmdkdndkdodpdqdpdrdpdsdtdtdtdudududvdvdvdwdwdwdx idydzd{dzd|dzd}dd~dddddddddddddddddddddddddidddddddddddddd$dd$dd(dd(dddddddd0dd0dd4dd4idd4dddddddddddddddddddd8dd8ddddddDddDddLiddLdddddddddddddddd“dd“ddbddbddǓddǓddǓdd˓dd˓dddddddddddddwdwdМiddғddғddғddzddzdd dd ddddddܓddܓddܓddddddddddidddddddddddddddddddd(dd(ddddddddddddidddqdddLddLddddddddddddddddddddd dd d d d id dddddddddddddbddbddǓddǓddǓdddddddd͓dd͓d d!id"d!d#d!d$d!d%d&d'd&d(d&d)dud*dud+dud,dvd-dvd.d/d0d/d1d/d2d3d4d3d5d3d3d6d6d6d7d7d7d8iiid9Z d:e d;e dZ d?Z ed@k(re yy(Az ETL: Populate Opening/Closing Lines from OddsSnapshot to SportsGame Uses team name matching since externalGameIds don't match between sources. Run: python3 scripts/etl_opening_closing_lines.py N)datetimetimezone timedelta) defaultdictz atlanta hawksATLhawksatlantazboston celticsBOScelticsbostonz brooklyn netsBKNnetsbrooklynzcharlotte hornetsCHAhornets charlottez chicago bullsCHIbullschicagozcleveland cavaliersCLE cavalierscavs clevelandzdallas mavericksDAL mavericksmavsdallaszdenver nuggetsDENnuggetsdenverzdetroit pistonsDETpistonsdetroitzgolden state warriorsGSWwarriorsz golden statezhouston rocketsHOUrocketshoustonzindiana pacersINDpacersindianazlos angeles clippersLACclippersz la clipperszlos angeles lakersLALlakersz la lakerszmemphis grizzliesMEM grizzliesmemphisz miami heatMIAheatmiamizmilwaukee bucksMILbucks milwaukeezminnesota timberwolvesMIN timberwolveswolves minnesotaznew orleans pelicansNOPpelicansz new orleansznew york knicksNYKknicksznew yorkzoklahoma city thunderOKCthunderz oklahoma cityokcz orlando magicORLmagicorlandozphiladelphia 76ersPHI76erssixers philadelphiaz phoenix sunsPHXsunsphoenixzportland trail blazersPORz trail blazersblazersportlandzsacramento kingsSACkings sacramentozsan antonio spursSASTORUTAWAS) spursz san antonioztoronto raptorsraptorstorontoz utah jazzjazzutahzwashington wizardswizards washingtonzarizona cardinalsARI cardinalsarizonazatlanta falconsfalconszbaltimore ravensBALravens baltimorez buffalo billsBUFbillsbuffalozcarolina panthersCARpantherscarolinaz chicago bearsbearszcincinnati bengalsCINbengals cincinnatizcleveland brownsbrownszdallas cowboyscowboyszdenver broncosbroncosz detroit lionslionszgreen bay packersGBpackersz green bayzhouston texanstexanszindianapolis coltscolts indianapoliszjacksonville jaguarsJAXjaguars jacksonvillezkansas city chiefsKCchiefsz kansas cityzlas vegas raidersLVraidersz las vegaszlos angeles chargerschargerszlos angeles ramsLARramszmiami dolphinsdolphinszminnesota vikingsvikingsznew england patriotsNEpatriotsz new englandznew orleans saintsNOsaintsznew york giantsNYGgiantsz new york jetsNYJjetszphiladelphia eagleseagleszpittsburgh steelersPITsteelers pittsburghzsan francisco 49ersSF49ersSEATBTEN)z san francisconinerszseattle seahawksseahawksseattleztampa bay buccaneers buccaneersbucsz tampa bayztennessee titanstitans tennesseezwashington commanders commandersz anaheim ducksANAducksanaheimzarizona coyotescoyotesz boston bruinsbruinszbuffalo sabressabreszcalgary flamesCGYflamescalgaryzcarolina hurricanes hurricanescaneszchicago blackhawks blackhawkszcolorado avalancheCOL avalancheavscoloradozcolumbus blue jacketsCBJz blue jacketscolumbusz dallas starsstarszdetroit red wingsz red wingszedmonton oilersEDMoilersedmontonzflorida panthersFLAfloridazlos angeles kingsLAKzminnesota wildwildzmontreal canadiensMTL canadienshabsmontrealznashville predatorsNSH predatorspreds nashvilleznew jersey devilsNJDdevilsz new jerseyznew york islandersNYI islandersznew york rangersNYRrangerszottawa senatorsOTTsenatorssensottawazphiladelphia flyersflyerszpittsburgh penguinspenguinspenszsan jose sharksSJSsharkszsan josezseattle krakenkrakenzst. louis bluesSTLblueszst louis blueszsaint louis bluesztampa bay lightningTBL lightningboltsztoronto maple leafsz maple leafsleafszutah hockey clubr\zvancouver canucksVANcanucks vancouverzvegas golden knightsVGKzgolden knightsknightsWSHWPG)vegaszwashington capitalscapitalscapsz winnipeg jetsrwinnipeg)nbanflnhlmlbncaabncaaf team_nameleaguereturnc0|syt|dkr|jr|S|jj}tj |i}||vr||S|j D]\}}||vs||vs|cS|ddjS)z,Normalize team name to standard abbreviationN)lenisupperlowerstrip TEAM_ALIASESgetitemsupper)rr team_lowerleague_aliasesaliasabbrevs >/var/www/html/eventheodds/scripts/etl_opening_closing_lines.pynormalize_teamrs  9~y002"((*J"%%fb1N^#j))(--/ v J *"5M Ra=    cd} t|d5}|D]b}|jds|jdddjjdjddccdddS dddt j jd d jddS#1swY;xYw#t$rYIwxYw) Nz/var/www/html/eventheodds/.envrzSPORTS_DATABASE_URL=="?rSPORTS_DATABASE_URL)open startswithsplitrFileNotFoundErrorosenvironr)env_pathflines r load_db_urlr s/H (C  RA R??#9:::c1-a0668>>sCII#NqQQ R R R R ::>>/ 4 : :3 ? BB  R R    s: B?B3AB3- B?7B39B?3B<8B?? C  C ctdtdtdtjtjj tdt }|s tdtj|}|j}ddddd}td|jd|j}td t|d |jd i}|jD]H\}}}} } t| |} t| |} |t|| | f} |t|| | f}||| <|||<Jd}|D]p\}}}}} } |s t|j!}t| |} t| |} ||| | f}|j#|}|sX|jd ||f|d z }r|j%||d<td|dtd|jd|j&|d<td|j&d|j%td|jd|j&|d<td|j&d|j%td|jdtddddd d!dd"d#dd$d#tdd%dd%dd&dd&|jD]T}|\}}}}}|dkDr d'|z|z d(d)nd*}|dkDr d'|z|z d(d)nd*}td|dd|d!d|d+d,|d-d.|d+d,|d-d/ V|j)|j)td0td1td2|dtd3|dtd4|dtd|S)5Nz<============================================================z/ETL: OPENING/CLOSING LINES (Team Name Matching)zTime: zSPORTS_DATABASE_URL not setr) games_linked opening_set closing_setalready_linkedz8 [1] Linking OddsSnapshot to SportsGame by team names...z SELECT id, league, "externalGameId", "gameDate", "homeTeam", "awayTeam" FROM "OddsSnapshot" WHERE "gameId" IS NULL ORDER BY league, "gameDate" z Found z unlinked snapshotsz SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' zS UPDATE "OddsSnapshot" SET "gameId" = %s WHERE id = %s rrz Linked z snapshots to SportsGame recordsz [2] Populating opening lines...a WITH earliest_snapshots AS ( SELECT DISTINCT ON ("gameId") "gameId", "spreadHome", "spreadAway", "moneylineHome", "moneylineAway", "total", "snapshotAt" FROM "OddsSnapshot" WHERE "gameId" IS NOT NULL AND "spreadHome" IS NOT NULL ORDER BY "gameId", "snapshotAt" ASC ) UPDATE "SportsGame" g SET "openingSpreadHome" = e."spreadHome", "openingSpreadAway" = e."spreadAway", "openingMoneylineHome" = e."moneylineHome", "openingMoneylineAway" = e."moneylineAway", "openingTotal" = e."total", "openingCapturedAt" = e."snapshotAt", "updatedAt" = NOW() FROM earliest_snapshots e WHERE g.id = e."gameId" AND (g."openingSpreadHome" IS NULL OR g."openingCapturedAt" > e."snapshotAt") rz Set opening lines for z gamesz [3] Populating closing lines...a WITH latest_pregame AS ( SELECT DISTINCT ON (s."gameId") s."gameId", s."spreadHome", s."spreadAway", s."moneylineHome", s."moneylineAway", s."total", s."snapshotAt" FROM "OddsSnapshot" s JOIN "SportsGame" g ON s."gameId" = g.id WHERE s."gameId" IS NOT NULL AND s."spreadHome" IS NOT NULL AND s."snapshotAt" < g."gameDate" ORDER BY s."gameId", s."snapshotAt" DESC ) UPDATE "SportsGame" g SET "closingSpreadHome" = l."spreadHome", "closingSpreadAway" = l."spreadAway", "closingMoneylineHome" = l."moneylineHome", "closingMoneylineAway" = l."moneylineAway", "closingTotal" = l."total", "closingCapturedAt" = l."snapshotAt", "updatedAt" = NOW() FROM latest_pregame l WHERE g.id = l."gameId" AND g."homeScore" IS NOT NULL -- Game completed AND (g."closingSpreadHome" IS NULL OR g."closingCapturedAt" < l."snapshotAt") rz Set closing lines for z [4] Coverage Report:a SELECT league, COUNT(*) as total, COUNT(*) FILTER (WHERE "homeScore" IS NOT NULL) as completed, COUNT(*) FILTER (WHERE "openingSpreadHome" IS NOT NULL) as has_opening, COUNT(*) FILTER (WHERE "closingSpreadHome" IS NOT NULL) as has_closing FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND league IN ('nba', 'nfl', 'nhl', 'ncaab', 'epl', 'mma') GROUP BY league ORDER BY total DESC z Leaguez<10 Completedz>10Openingz>12Closingz ----------z ------------dz.1f%z0%z>8z (z>5z) )z= ============================================================z ETL Complete:z Snapshots linked: z Opening lines set: z Closing lines set: )printrnowrutc isoformatr  RuntimeErrorpsycopg2connectcursorexecutefetchallrrstrdatercommitrowcountclose)db_urlconncurstatsunlinked sg_lookupsg_idrgdhomeaway home_norm away_normkey1key2 linked_countos_idext_id game_datedate_strkeyrowtotal completedopeningclosingopen_pct close_pcts rrun_etlrEs# (O ;< F8<< -779: ;< (O ]F 899   F #D ++-C  E EFKK ||~H HS]O#6 78KK I), %vr4"40 "40 BI6BI6 $ $ L8@4vvy$ y~~'("40 "40 xI6 c"  KK ! A L  KKM(E. Il^#C DE -.KK 6<rLs6   22#    ' 095 % !*E 3;U   !' 0:5  U  %.u  7B5      !(   1:5   u   '25   ;A%   JUV[   E $/ 8>u GOPU  % "+E 4u   )3E u  % "*5 3u# $ !%% $*8% $AI%% $R]^c% & ' &(25' &;H' ( 5) (#+E) (4>u) * + *)25+ *;J5+ *SXY^+ , - ,!(- ,1:5- . e/ .&-e/ .6>u/ .GUV[/ 0 1 0 &u1 0/81 2 !%3 2*9%3 2BKE3 2T^_d3 4 E5 4$+E5 44@5 6 U7 6.35 UuE5#U= B! U! $/! 8A5! 5! "+U!  E! $,U! 5@!   ! !( ! 1:5 !  U !  %/ !  8B5 !   !  !( !  e! &/! 8DU!  E! $,U!  %! "+E!  %! "+E!  ! !(!  T! $-d! 5@!  %! "*5!  e! &-e! 6DU!  ! (1%! :H!  d!!  %-d!!  5B4!! " T#! "$-d#! "5@#! $ %! $(25%! & E'! &$*5'! ( %)! (",U)! * U+! *%.u+! , -! ,'1$-! ,9Ft-! . d/! .%-d/! 0 51! 0#+E1! 2 3! 2!'3! 4 u5! 4'/5! 6 u7! 6'1%7! 6:Fu7! 8 t9! 8&-d9! 8FJUY!u $D$UY!U!&eA! F" "  '" 095" 5" "+U"  " !)%"  % " "*5 "  % "  "*5 "  3u"  E" $.u" 7@"  U" %,U"  %" "("  e!"  &1%!"  :@!"  ISTY!" " u#" "'25#" ";B5#" "KVW\#" $ U%" $%-e%" $6B5%" & e'" &&1%'" ( E)" ($-e)" * 5+" *#-e+" *6u1" 2 %3" 2"*53" 4 55" 4#*55" 43CE5" 4L_`e5" 6 u7" 6'257" 6;B57" 8 u9" 8'4U9" 8=DU9" : E;" :$*5;" < U=" <%.u=" <7B5=" > ?" >(8?" >AJ5?" >\a$%5C" H   [n b!c!3!3!4 Cod z Ir