ݬoi : 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]dbdcdddcdedcdfdcdgdcdhdidjdidkdidldiidmdidndodpdodqdodrdodsdtdudtdvdtdwdtdxdydzdyd{dyd|dyd}dyd~dddddiddddddddddddddddddddddddddddddddddddddddddddddd 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ԓddDddDdGdDddۓddۓddۓddۓiddSddSdVdSdd]dd]dad]dddddddddddddgdddddddddiddddddyddyd}dydddddddddddddddddddddddddddddddddddddddiddd dd dd dd dd ddddd dd d d ddddddddddddddiddddddddddddddddddddd dd!dd"d#d$d#d%d#d&d#d'd$id(d$d(d$d)d/d*d/d2d/d+d,d-d,d.d,d/d,d0d1dd1d2d1d3d1d4d5dd5d6d5dސd5id7d]d8d]dad]d9d:d;d:d<d:d=d:d>d:d?d@dAd@dBd@dCd@dDd@dEdFdGdFdHdFdIdFidJdKdLdKdMdKdNdOdPdOdQdOdRdSdTdSdUdSdVdSdWdSdXdydYdyd}dydZdd[dd\diddd]d^d_d^d`d^dad^dbd^dcdddddeddfdgdhdgdidgdjdgdkdldmdldndldodlidpdldqddrddsddtdduddvddwddxdydzdyd{dyd|dyd}d~dd~dd~dd~dd~dddddddddiddddddddddddddddddddddddddddddddddiddddddddddddddddddddddddddddddddddidddddddddddddddddddddÐdēdŐdēddēdƐdēdǐdȓdɐdȓdʐdȓdːdːdːdːd̜id͐dΓdϐdГdѐdғdӐdғdddԐdՓd֐dՓdאdؓdِdؓdڐdۓdddddddܐdݓdސdݓdߐdݓddddddddddZdZdZdZdZ dZ e dk(re yy(z Link all orphaned records to SportsGame using team name matching. Tables: OddsSnapshot, GameHalfLine, BettingSplits, SharpMove Run: python3 scripts/link_tables_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 yorknyknyzoklahoma city thunderOKCthunderz oklahoma cityokcz orlando magicORLmagicorlandoorlzphiladelphia 76ersPHI76erssixers philadelphiaphiz phoenix sunsPHXsunsphoenixphxphozportland trail blazersPORz trail blazersblazersportlandporzsacramento kingsSACkings sacramentosaczsan antonio spursSASspursz san antoniosassaztoronto raptorsTORUTAWAS) raptorstorontotorz 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 jacksonvillejaxzkansas city chiefsKCchiefsz kansas citykczlas vegas raidersLVraidersz las vegaslvzlos angeles chargerschargerszlos angeles ramsLARramslarlazmiami dolphinsdolphinszminnesota vikingsvikingsznew england patriotsNEpatriotsz new englandneznew orleans saintsNOsaintsznew york giantsNYGgiantsnygz new york jetsNYJjetsnyjzphiladelphia eagleseagleszpittsburgh steelersPITsteelers pittsburghpitzsan francisco 49ersSF49ersz san franciscosfninerszseattle seahawksSEAseahawksseattleTBTEN)seaztampa bay buccaneers buccaneersbucsz tampa baytbztennessee titanstitans tennesseetenzwashington commanders commandersr~rz anaheim ducksANAducksanaheimanazarizona coyotescoyotesz boston bruinsbruinszbuffalo sabressabreszcalgary flamesCGYflamescalgarycgyzcarolina hurricanes hurricanescaneszchicago blackhawks blackhawkszcolorado avalancheCOL avalancheavscoloradocolzcolumbus blue jacketsCBJz blue jacketscolumbuscbjz dallas starsstarszdetroit red wingsz red wingszedmonton oilersEDMoilersedmontonedmzflorida panthersFLAfloridaflazlos angeles kingsLAKlakzminnesota wildwildzmontreal canadiensMTL canadienshabsmontrealmtlznashville predatorsNSH predatorspreds nashvillenshznew jersey devilsNJDdevilsz new jerseynjdznew york islandersNYI islandersnyiznew york rangersNYRrangersnyrzottawa senatorsOTTsenatorssensottawaottzphiladelphia flyersflyerszpittsburgh penguinspenguinspenszsan jose sharksSJSsharkszsan josesjssjzseattle krakenkrakenrzst. louis bluesSTLblueszst louis bluesstlztampa bay lightningTBL lightningboltstblrztoronto maple leafsz maple leafsleafsrxzutah hockey clubrzr{zvancouver canucksVANcanucks vancouvervanzvegas golden knightsVGKzgolden knightsknightsvegasvgkWSHWPG)zwashington capitalscapitalscapsrz winnipeg jetsrwinnipegwpgarsenalARSarsz aston villaAVLvillaavl bournemouthBOUbou brentfordBREbrebrightonBHAzbrighton and hove albionbhachelseaCHEchezcrystal palaceCRYpalacecryevertonEVEevefulhamFULfulipswichIPSz ipswich townips leicesterLEIzleicester citylei liverpoolLIVlivzmanchester cityMCIzman citymcizmanchester unitedMUNz man unitedzman utdmun newcastleNEWznewcastle unitednewznottingham forestNFOforestnfo southamptonSOUsou tottenhamTOTztottenham hotspurtotzwest hamWHUzwest ham unitedwhuWOL)rK wolverhamptonzwolverhampton wandererswolajaxAJA barcelonaBARz bayern munichBAYbayerndortmundDORzborussia dortmundz inter milanINTinterjuventusJUVpsgPSGzparis saint-germainparisz real madridRMASLAVILPAF)madridz slavia prahaz slavia prague villarrealzpafos fcpafos)nbanflnhlepluclcF|syt|j}t|dkr|jr|S|j }t j |i}||vr||S|jD]\}}||vs||vs|cS|ddjS)z,Normalize team name to standard abbreviationN) strstriplenisupperlower TEAM_ALIASESgetitemsupper) team_nameleagueteam_str team_lowerleague_aliasesaliasabbrevs >/var/www/html/eventheodds/scripts/link_tables_to_sportsgame.pynormalize_teamrs 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 startswithsplitrFileNotFoundErrorosenvironr)env_pathflines r load_db_urlrs/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|jdi}|jD]N\}}}}}t||}t||}|s$|s'|t|||f} |t|||f} ||| <||| <P|S)z8Build lookup: (league, date, home_norm, away_norm) -> idz SELECT id, league, DATE("gameDate") as gd, "homeTeam", "awayTeam" FROM "SportsGame" WHERE "gameDate" >= '2024-10-01' )executefetchallrr) curlookupsg_idrgdhomeaway home_norm away_normkey1key2s rbuild_sportsgame_lookuprsKK F),!%vr4"40 "40 CGY :DCGY :D F4L F4L! Mrc|jd|d|j}d}|D]\}}} } } | s t| |} t| |} t| drt | j n t | dd}||| | f}|j |}|sq|jd|d||f|d z }|j|||<|S) z%Link records in a table to SportsGamezM SELECT id, league, "homeTeam", "awayTeam", "gameDate" FROM "zo" WHERE "gameId" IS NULL AND "homeTeam" IS NOT NULL AND "awayTeam" IS NOT NULL rdateN z UPDATE "z." SET "gameId" = %s WHERE id = %s r)rrrhasattrrrrcommit)rconn table_name sg_lookupstatsunlinkedlinkedrec_idrrr game_daterrdate_strkeyrs r link_tablersKKl ||~H F19-dI "40 "40 ,3Iv,F3y~~'(CPYN[^\^L_xI6 c"  KK# %  " aKF  KKME* Mrc |tdtdtdtjtjj tdt }tj|}|j}tdt|}tdt|di}gd}|D]E}td|j|d zd |d  t|||||}td |d Gtdtdtd|j!dtdddddddddddtd|j#D]5} | \} } }| dkDr d|z| z ddnd} t| dd| dd|dd| d7|j%|j%tdtdt'|j)d td|S#t$r)}td||jYd}~rd}~wwxYw)!Nz<============================================================zLINK TABLES TO SPORTSGAMEzTime: z" [1] Building SportsGame lookup...z Indexed z game entries) OddsSnapshot GameHalfLine BettingSplits SharpMovez [z ] Linking z...z Linked z recordsz Error: z= ============================================================zLINKAGE REPORT:a3 SELECT 'OddsSnapshot' as tbl, COUNT(*) as total, COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) as linked FROM "OddsSnapshot" UNION ALL SELECT 'GameHalfLine', COUNT(*), COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) FROM "GameHalfLine" UNION ALL SELECT 'BettingSplits', COUNT(*), COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) FROM "BettingSplits" UNION ALL SELECT 'SharpMove', COUNT(*), COUNT(CASE WHEN "gameId" IS NOT NULL THEN 1 END) FROM "SharpMove" Tablez<20 Totalz>10Linked%z>8z2--------------------------------------------------rdz.1fz0%zLinkage complete: z records linked)printrnowrutc isoformatrpsycopg2connectcursorrrindexr Exceptionrollbackrrclosesumvalues) db_urlrrrrtablestablererowrtotalpcts r run_linkagers+ (O %& F8<< -779: ;< (O ]F   F #D ++-C /0',I Js9~&m 45 ELF FLL')**UG3?@ T5)UCF IfXX. /  /  (OKK    WSM73-q#aBx @A (O||~> UF,1AIVE!#&a(4 S 5+Qvcl!C8<=> IIKJJL / s5<<>23? CD (O LM  IaS/ " MMOO s,H  H;H66H;__main__) __doc__rrrrrrrrrr__name__rrrs  '   ' 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) (UYZ_) * + *)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.3uUE5#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\!  d!!  %-d!!  5B4!!  JNt!! " T#! "$-d#! "5@#! "HLT#! $ %! $(25%! $;@%! & E'! &$*5'! &38'! &AEe'! ( %)! (",U)! (5:5)! * U+! *%.u+! *7u"  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%" & e'" &&1%'" &:?'" ( E)" ($-e)" (6;E)" * 5+" *#-e+" *6u1" 0GLU1" 0UYZ_1" 2 %3" 2"*53" 2383" 4 55" 4#*55" 43CE5" 4LQRW5" 6 u7" 6'257" 6;B57" 6KPQV7" 6Y]^c7" 8 u9" 8'4U9" 8=DU9" 8MRSX9" : E;" :$*5;" :38;" < U=" <%.u=" <7B5=" <KPQV=" > ?" >(8?" >AJ5?" >SZ[`?" >chin?" @ %%u5C" F 5  u %u .3U  u $U  U  "5   E   6u   ?DU   5       % "*5 38  5    %   5 )% 27  U -e 6;E  U "5  5 #-e 6;E  U %1% :CE LQRW  U / 8=e  U!  %-e!  6;E! " u# "$U# $ U% $0% $9@% $INu% & E' &-e' &6;E' (%EZ_) ,  "E +:E CKU 5 $e -@%  u &u /9%  U  .u  7A%   u   ,U   5