ѱoiU, dZddlZddlZddlZddlmZmZddlmZidddgdddgd d d gd d d gd d dgdddgddgddgddgddgddgdddgdd d!gd"gd#gd$gd%dd gddgd dggd&d d gd'd(d)gd*d+gd,d-gd.d/gd0d1gd2d3gd4d5gd6d7gd8d9gd:d;gd< dd=gd>d?gdd@ggdAdBdCgdDdEgdFdGZdHZ dIZ dJZ dKZ e dLk(re yy)Mz Link Player Props to Actual Game Outcomes Matches props to player game stats to calculate hit rates. Run: python3 scripts/link_props_to_outcomes.py N)datetimetimezone) defaultdictpointsespn_ptsassistsespn_astreboundsespn_rebstealsespn_stlblocksespn_blkthreepointersmadethreesnba_fg3mthreepointersattemptednba_fg3afieldgoalsmadenba_fgmfieldgoalsattemptednba_fgafreethrowsmadenba_ftmfreethrowsattemptednba_fta turnoversespn_tonba_tovminutesnba_minespn_min fantasyscoretwopointersmadetwopointersattemptedzpoints+rebounds)rr r)zpoints+assistszrebounds+assistszpoints+rebounds+assistsz blocks+steals passing_yardspass_yds rushing_yardsrush_ydsreceiving_yardsrec_ydspassing_touchdownspass_tdrushing_touchdownsrush_tdreceiving_touchdownsrec_td touchdownstotal_td receptionsrec completionspass_cmp interceptionspass_int) r&r(r*r,r.r0r2r4r6r8nhl_ptsgoals nhl_goals nhl_assists)shots shots_on_goalsogsaves goalie_savespower_play_pointspp_pts)rr;rr?rArC)nbanflnhlc|syd|vrE|jd}t|dkDr&|djr|djS|jS)a Extract base prop type from complex prop type strings. Examples: - 'points' -> 'points' - 'points-LEBRON_JAMES_1_NBA-game-ou-over' -> 'points' - 'fieldGoalsMade-PLAYER_NAME-1q-ou-under' -> 'fieldGoalsMade' - 'blocks+steals' -> 'blocks+steals' N-r)splitlenisupperlower) prop_typepartss ;/var/www/html/eventheodds/scripts/link_props_to_outcomes.pyextract_base_prop_typerRAsX  i$ u:>eAh..08>># # ?? cf|sytjdd|tj}tjdd|tj}|jddjddjd d}dj |j j }|S) zNormalize player name for matching. Handles variations like: - De'Aaron Fox vs DeAaron Fox vs deaaron fox - Nickeil Alexander-Walker vs Nickeil Alexanderwalker - Kevin Porter Jr. vs Kevin Porter Nz\s+(Jr\.?|Sr\.?|III|II|IV|V)$)flagsz \s+Jr\.?\s+ 'rI.)resub IGNORECASEreplacejoinrKrN)names rQnormalize_player_namer`Us  662BBMM RD 66.#t2== AD <<R ( (b 1 9 9#r BD 88DJJL ! ' ' )D KrScd} t|d5}|D]b}|jds|jdddjjdjddccdddS dddt j jd d jddS#1swY;xYw#t$rYIwxYw) Nz/var/www/html/eventheodds/.envrzSPORTS_DATABASE_URL==rJ"?rSPORTS_DATABASE_URLrU)open startswithrKstripFileNotFoundErrorosenvironget)env_pathflines rQ load_db_urlrqjs/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}tddD]\}} |jd|d|d |jtd td |jd |j}td t|d |s2td|j|jdddddStdt!}t!}|D]3} |j#| d|j#t%| d5dj't)|} dj't)|} |jd| d| dt+t,} |jD],} | \} }}}}}t/|}| |t%|f}|| ||<.tdt| dtdg}dddddd}|D]}|\ }} }}}}}}}}t/|}| |t%|f}| j1|i}|s|dxxdz cc<Lt3|} t4j1| ij1| g}!|!s| r| gng}!|!s|dxxdz cc<d}"d| vst|!dkDrqg}#|!D]\}$|$|vr|#j7||$|D]<}%|$|%j9vs|%j9|$vs(|#j7||%\^|#rTt;|#}"nH|!D]C}$|$|vr||$}"n8|D]-}%|$|%j9vs|%j9|$vs(||%}"n|"Cn|"|dxxdz cc<t|"|kDrd}&|dxxdz cc<n$|"|krd}&|dxxdz cc<nd }&|d xxdz cc<|d!xxdz cc<|j7|"|&|f|rRtd"t|d#|j=d$||jtd%t|d&td'td(td)|d!td*|dd+d,|dzt?|d!dz d-d.td/|dd+d,|dzt?|d!dz d-d.td0|d td1|dtd|jd2td3|jD]%} | \}'}}(})}&td4|'d5|d6|(d7|)d+|&d8 '|j|j|S#t$r }Yd}~*d}~wwxYw)9Nz<============================================================zLINK PROPS TO OUTCOMESzTime: z& [1] Ensuring outcome columns exist...)) actualValuezDOUBLE PRECISION)resultz VARCHAR(10))linkedAt TIMESTAMPzH ALTER TABLE "PlayerPropLine" ADD COLUMN IF NOT EXISTS "z" z z Columns readyz [2] Finding props to link...aU SELECT ppl.id, ppl.league, ppl."gameId", ppl."playerExternalId", ppl."propType", ppl."lineValue", p.name as player_name, sg."homeTeam", sg."awayTeam", sg."gameDate"::date as game_date FROM "PlayerPropLine" ppl JOIN "Player" p ON ppl."playerExternalId" = p."externalPlayerId" AND ppl.league = p.league JOIN "SportsGame" sg ON ppl."gameId" = sg.id WHERE ppl.result IS NULL AND sg."homeScore" IS NOT NULL AND ppl.league IN ('nba', 'nfl', 'nhl') ORDER BY ppl.league, ppl."gameId" z Found z props to linkz No props need linkingr)linkedoverunderpushz$ [3] Building player stats lookup...rJ z', 'z SELECT league, "playerName", "gameDate"::date as game_date, team, "statKey", value FROM "PlayerGameMetric" WHERE league IN ('z'') AND "gameDate"::date IN ('z') z Loaded stats for z player-gamesz! [4] Linking props to outcomes...)rwrxryrzno_matchr|+rxryrzrwz [5] Updating z props...z UPDATE "PlayerPropLine" SET "actualValue" = %s, result = %s, "linkedAt" = NOW() WHERE id = %s z Updated z propsz= ============================================================zRESULTS:z Props linked: z Over hits: z (dz.1fz%)z Under hits: z Pushes: z No stat match: ah SELECT p.name, ppl."propType", ppl."lineValue", ppl."actualValue", ppl.result FROM "PlayerPropLine" ppl JOIN "Player" p ON ppl."playerExternalId" = p."externalPlayerId" AND ppl.league = p.league WHERE ppl.result IS NOT NULL AND ppl.league = 'nba' ORDER BY ppl."linkedAt" DESC LIMIT 10 z Sample linked props (NBA):z z: rWu → )) printrnowrutc isoformatrqpsycopg2connectcursorexecute ExceptioncommitfetchallrLclosesetaddstrr^sortedrdictr`rmrR PROP_TO_STATappendrNsum executemanymax)*db_urlconncurcolcol_typee props_to_linkdatesleaguesrow date_list league_list stats_lookupleague player_name game_dateteamstat_keyvalue norm_namekeyupdatesstatsprop_rowprop_idgame_id player_ext_idrO line_valuehomeaway player_statsbase_prop_type stat_keys actual_valuevaluessk actual_skrtr_rpactuals* rQ run_linkagervsl (O "# F8<< -779: ;< (O ]F   F #D ++-C 34  X   KKHHKuBxjY    KKM  *+KK   LLNM HS'( 78  '(  QA>> 12 EEeG CF #c!f+ F5M*I++fWo.KKK '-(%%.K0   t$L||~,@C= Yh)+6 y#i.1&+ S(# ,  L 12- @A ./G!a JE!G8muj-J UY[_aj)+6 y#i.1#''R0  *  "  0 :!$$VR044^RH ,:(I *  "   . C NQ$6F "%MM,r"23&2" !22ioo6G26M"MM,y*AB!"  ""6{  %#/#3L!-IY__..)//2Cr2I'3I'>  +    *  "   * $F &MQ M J &F 'Na NF &MQ M h1 fg67OG8T G ~Y78      3w<./0 / * U8_- ./ M%-3uV}+*"Sw-?E(OTU@V-VWZ,[[] ^_ JuV}o &' eJ/0 12 (OKK    ()||~G03-ivv 4&9+QtfE&F81EFGIIKJJL Lq   s"V66 W W __main__)__doc__rrkrZrr collectionsrrrRr`rqr__name__rSrQrs  '#  8Z( Iz*  Z,  8Z(   8Z(   h 3   !:,  9+   {  9+   {  i+  Iz*    2! " # & Hj1' ($Y/'3#D"H-/ 4*:6):6-y93Y?3Y?!7 B#Z0#U+%z2):6 Y';'}-:>*18<  M. b(* CM` zMrS