oi7  dZddlZddlZddlmZmZiddddddddd d d d d d d d ddddddddddddddddddiddddddddddd dd!dd"dd#d$d%d$d&d$d'd$d(d$d)d*d+d*d,d*d-d*id.d/d0d/d1d/d2d/d3d4d5d4d6d4d7d4d8d4d9d:d;d:dd?d@d?dAd?dBd?idCdDdEdDdFdDdGdDdHdIdJdIdKdIdLdIdMdNdOdNdPdNdQdNdRdSdTdSdUdSdVdSdWdXidYdXdZdXd[dXd\d]d^d]d_d]d`d]dad]dbdcdddcdedcdfdcdgdcdhdidjdidkdidldiidmdndodndpdndqdndrdsdtdsdudsdvdsdwdxdydxdzdxd{dxd|dxd}d~dd~dd~dd~idd~dddddddddddddddddddddddddddddddddddddddddddd iddddddddddddddddddddddddddddddddddiddddddddddddddddddddddd"ddd$dd$d(d$dd*dd*id-d*dd/dd/d2d/dddddddddd:dd:d=d:dd?dd?dd?dBd?ddʓddʓiddʓddʓddʓddГddГddГddГddՓddՓddՓddՓddՓddDddDdGdDddݓddݓiddݓddSddSdVdSdd]dd]dad]dddddddddddddeddgdddddiddddddddddxddxd|dxddddddddddddddddddddiddddddddddd dd dd dd d dd dd dd ddddddddidddddddddd dd d d ddddddddd dd!dd"dd#dd$dd%diddd&dd'dddd(d)d*d)d+d)d,d)d-d)d.d/d0d/d1d/d2d/d3d$d4d$d(d$d5d/id6d/d2d/d7d8d9d8d:d8d;d8d<d=dd=d>d=d?d=d@dAddAdBdAdCdAdDd]dEd]dad]idFdGdHdGdIdGdJdGdKdGdLdMdNdMdOdMdPdMdQdMdRdSdTdSdUdSdVdSdWdSdXdYdZdYid[dYd\d]d^d]d_d]d`dadbdadcdadddadedadfdxdgdxd|dxdhddiddjddddkdlidmdldndldodldpdldqddrddddsdtdudtdvdtdwdtdxdydzdyd{dyd|dyd dyd}did~dddddddddddddddddddddddddddddddddddddddddZdZdZdZe dk(reyy(z Sync odds from GameOdds to SportsGame using team name matching. Fixes games that have odds in GameOdds but not in SportsGame. Run: python3 scripts/sync_gameodds_to_sportsgame.py N)datetimetimezonez atlanta hawksATLhawksatlantaatlzboston celticsBOScelticsbostonbosz brooklyn netsBKNnetsbrooklynbknzcharlotte hornetsCHAhornets charlottechaz chicago bullsCHIbullschicagochizcleveland cavaliersCLE cavalierscavs clevelandclezdallas mavericksDAL mavericksmavsdallasdalzdenver nuggetsDENnuggetsdenverdenzdetroit pistonsDETpistonsdetroitdetzgolden state warriorsGSWwarriorsz golden stategswgszhouston rocketsHOUrocketshoustonhouzindiana pacersINDpacersindianaindzlos angeles clippersLACclippersz la clipperslaczlos angeles lakersLALlakersz la lakerslalzmemphis grizzliesMEM grizzliesmemphismemz miami heatMIAheatmiamimiazmilwaukee bucksMILbucks milwaukeemilzminnesota timberwolvesMIN timberwolveswolves minnesotaminznew orleans pelicansNOPpelicansz new orleansnopnoznew york knicksNYKknicksznew yorknykzoklahoma city thunderOKCthunderz oklahoma cityokcz orlando magicORLmagicorlandoorlzphiladelphia 76ersPHI76erssixers philadelphiaphiz phoenix sunsPHXsunsphoenixphxphozportland trail blazersPORz trail blazersblazersportlandporzsacramento kingsSACkings sacramentosaczsan antonio spursSASspursz san antoniosassaztoronto raptorsTORraptorsUTAWAS) torontotorz utah jazzjazzutahutazwashington wizardswizards washingtonwaswshzarizona cardinalsARI cardinalsarizonaarizatlanta falconsfalconszbaltimore ravensBALravens baltimorebalz buffalo billsBUFbillsbuffalobufzcarolina panthersCARpantherscarolinacarz chicago bearsbearszcincinnati bengalsCINbengals cincinnaticinzcleveland brownsbrownszdallas cowboyscowboyszdenver broncosbroncosz detroit lionslionszgreen bay packersGBpackersz green baygbzhouston texanstexanszindianapolis coltscolts indianapoliszjacksonville jaguarsJAXjaguars jacksonvillejaxjaczkansas city chiefsKCchiefsz kansas citykczlas vegas raidersLVraidersz las vegaslvlvrzlos angeles chargerschargerszlos angeles ramsLARramslarzmiami dolphinsdolphinszminnesota vikingsvikingsznew england patriotsNEpatriotsz new englandneznew orleans saintsNOsaintsznew york giantsNYGgiantsnygz new york jetsNYJjetsnyjzphiladelphia eagleseagleszpittsburgh steelersPITsteelers pittsburghpitzsan francisco 49ersSF49ersz san franciscosfninerszseattle seahawksSEAseahawksseattleseaztampa bay buccaneersTB buccaneersbucsz tampa baytbztennessee titansTENtitans tennesseetenzwashington commanders commandersr}r~z anaheim ducksANAducksanaheimanaz boston bruinsbruinszbuffalo sabressabreszcalgary flamesCGYflamescalgarycgyzcarolina hurricanes hurricanescaneszchicago blackhawks blackhawkszcolorado avalancheCOL avalancheavscoloradocolzcolumbus blue jacketsCBJz blue jacketscolumbuscbjz dallas starsstarszdetroit red wingsz red wingszedmonton oilersEDMoilersedmontonedmzflorida panthersFLAfloridaflazlos angeles kingsLAKlaklazminnesota wildwildzmontreal canadiensMTL canadienshabsmontrealmtlznashville predatorsNSH predatorspreds nashvillenshznew jersey devilsNJDdevilsz new jerseynjdnjznew york islandersNYI islandersnyiznew york rangersNYRrangersnyrzottawa senatorsOTTsenatorssensottawaottzphiladelphia flyersflyerszpittsburgh penguinspenguinspenszsan jose sharksSJSsharkszsan josesjssjzseattle krakenkrakenzst. louis bluesSTLblueszst louis bluesstlztampa bay lightningTBL lightningboltstblztoronto maple leafsz maple leafsleafsrwzutah hockey clubryrzzvancouver canucksVANcanucks vancouvervanzvegas golden knightsVGKzgolden knightsknightsvegasvgkzwashington capitalsWSHcapitalsWPG)capsr~z winnipeg jetsrwinnipegwpg)nbanflnhlcF|syt|j}t|dkr|jr|S|j }t j |i}||vr||S|jD]\}}||vs||vs|cS|ddjS)N) strstriplenisupperlower TEAM_ALIASESgetitemsupper) team_nameleagueteam_str team_lowerleague_aliasesaliasabbrevs @/var/www/html/eventheodds/scripts/sync_gameodds_to_sportsgame.pynormalize_teamrAus 9~##%H 8}h..0!J!%%fb1N^#j))'--/ v J *"5M BQ<   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 startswithsplitr1FileNotFoundErrorosenvironr6)env_pathflines r@ load_db_urlrTs/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 c tdtdtdtjtjj tdt }tj|}|j}td|jdi}|jD]:\}}}}}t||} t||} | s$| s'|t|| | f} ||| <<tdt|dtd|jd |j} tdt| d td g} | D]n}|\ }}}}}}}}}|r|st||} t||} |t|| | f} |j| }|sO|R| j!||r| nd|||d |fp| r+|j#d | tdt| dn td|j%td|jdtddddddddddddtdddddddd|jD])}|\}}}}td|dd|dd|dd|xsd d!d +|j'|j'td"td#t| d$tdt| S)%Nz<============================================================zSYNC GAMEODDS TO SPORTSGAMEzTime: z" [1] Building SportsGame lookup...z SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND "spreadHome" IS NULL z Found z# SportsGame records missing spreadsz' [2] Getting best odds from GameOdds...a WITH best_spreads AS ( SELECT DISTINCT ON (league, "gameId") league, "gameId", DATE("gameDate") as gd, "homeTeam", "awayTeam", "lineValue" as spread, "homeOdds" as spread_home_odds, "awayOdds" as spread_away_odds FROM "GameOdds" WHERE market = 'spreads' AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC ), best_ml AS ( SELECT DISTINCT ON (league, "gameId") league, "gameId", "homeOdds" as ml_home, "awayOdds" as ml_away FROM "GameOdds" WHERE market = 'h2h' AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC ), best_total AS ( SELECT DISTINCT ON (league, "gameId") league, "gameId", "lineValue" as total, "overOdds", "underOdds" FROM "GameOdds" WHERE market = 'totals' AND "gameDate" >= '2024-10-01' ORDER BY league, "gameId", "isBestLine" DESC NULLS LAST, "fetchedAt" DESC ) SELECT s.league, s."gameId", s.gd, s."homeTeam", s."awayTeam", s.spread, m.ml_home, m.ml_away, t.total FROM best_spreads s LEFT JOIN best_ml m ON s.league = m.league AND s."gameId" = m."gameId" LEFT JOIN best_total t ON s.league = t.league AND s."gameId" = t."gameId" z games with odds in GameOddsz" [3] Syncing odds to SportsGame...z odds-api-synca: UPDATE "SportsGame" SET "spreadHome" = %s, "spreadAway" = %s, "moneylineHome" = %s, "moneylineAway" = %s, "total" = %s, "oddsSource" = %s, "updatedAt" = NOW() WHERE id = %s z Updated z SportsGame records with oddsz No updates neededz [4] Coverage Report:a SELECT league, COUNT(*) FILTER (WHERE "homeScore" IS NOT NULL) as completed, COUNT(*) FILTER (WHERE "spreadHome" IS NOT NULL) as has_spread, ROUND(100.0 * COUNT(*) FILTER (WHERE "spreadHome" IS NOT NULL) / NULLIF(COUNT(*) FILTER (WHERE "homeScore" IS NOT NULL), 0), 1) as pct FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' AND league IN ('nba', 'nfl', 'nhl', 'ncaab', 'ncaaf', 'epl', 'mlb') GROUP BY league ORDER BY completed DESC z Leaguez<10 Completedz>10z Has Spreadz>12%z>8z ----------z ------------z--------rz>7.1fz= ============================================================zSync complete: z games updated)printrnowrutc isoformatrTpsycopg2connectcursorexecutefetchallrAr0r2r6append executemanycommitclose)db_urlconncur sg_lookupsg_idr:gdhomeaway home_norm away_normkeygameoddsupdatesrowgame_idspreadml_homeml_awaytotal completed has_spreadpcts r@run_syncr}s' (O '( F8<< -779: ;< (O ]F   F #D ++-C /0KK I),#%vr4"40 "40 3r7Iy9C"IcN #  HS^$$G HI 45KK <||~H HS]O#? @A /0GKNHT4'54 "40 "40 s2w 95 c" V' NN!t *      3w<.(EFG #$KKM "#KK    BxnAk#.a S/A3r( KL Bvhaxq% 12||~T-0* :s 6#,a #a 3/?q%@PPQRSTIIKJJL / OCL> 89 (O w<rB__main__) __doc__r^rOrrr5rArTr}__name__rBr@rs$  '   ' 095 BG % !*E 3;U DI5   !' 0:5 CH  U  %.u  7B5  KPQV      !(   1:5   CH   u   '25   ;A%   JUV[   ^cdi   E $/ 8>u GOPU X]^c  % "+E 4u GLU   )3E u GLU  % "*5 3u# "GLU# $ !%% $*8% $AI%% $R]^c% $fklq% & ' &(25' &;H' &QVW\' &_cdi' ( 5) (#+E) (4>u) (GLU) * + *)25+ *;J5+ *SXY^+ , - ,!(- ,1:5- ,CH- . e/ .&-e/ .6>u/ .GUV[/ .^cdi/ 0 1 0 &u1 0/81 0AFu1 0OTUZ1 2 !%3 2*9%3 2BKE3 2T^_d3 2glmr3 4 E5 4$+E5 44@5 4INu5 6 U7 6%,U7 65B57 6KPQV7 6Y]^c7 8 59 8#,U9 8@EUE5#USXaf= @! U! $/! 8A5! JOQV! 5! "+U! 495!  E! $,U! 5@! INu!   ! !( ! 1:5 ! CH !  U !  %/ !  8B5 !  KPQV !   !  !( !  16u !  e! &/! 8DU! MRSX!  E! $,U! 5:5!  %! "+E! 49%!  %! "+E! 49%!  ! !(! 16u!  T! $-d! 5@! HLT!  %! "*5! 38!  e! &-e! 6DU! MRSX!  ! (1%! :H! QVW\! _dej!  d!!  %-d!!  5B4!!  JNt!! " T#! "$-d#! "5@#! "HLT#! "TYZ^#! $ %! $(25%! $;@%! & E'! &$*5'! &38'! ( %)! (",U)! (5:5)! * U+! *%.u+! *7 E?! >$,U?! >5@?! >INu?! @ A! @)5eA! @>CEA! @LQRWA! D! !  '! 095! BG! !  (%! 16!  %! "*5! 38!  % ! "*5 ! 3u!  5! #+E! 4>u! GLU!  E! $.u! 7@! INu!  U! %,U! 5:5! CG!  %! "(! 16u!  e! &1%! :@! ISTY! \abg!  u!!  '25!!  ;B5!!  KVW\!!  _dej!! " U#! "%-e#! "6B5#! "KPQV#! "Y]^c#! $ e%! $&1%%! $:?%! & E'! &$-e'! &6;E'! ( 5)! (#-e)! (6u/! .GLU/! .UYZ_/! 0 %1! 0"*51! 0381! 2 53! 2#*53! 23CE3! 2LQRW3! 4 u5! 4'255! 4;B55! 4KPQV5! 4Y]^c5! 6 u7! 6'4U7! 6=DU7! 6MRSX7! 8 E9! 8$*59! 8389! : U;! :%.u;! :7B5;! :KPQV;! < =! <(8=! <AJ5=! <SZ[`=! <chin=! > u?! >'1%?! >BGu5A! Ge P  CCL z JrB