iS?TdZddlZddlZddlZddlZddlZddlZddlmZmZm Z ddl m Z ejjdejjejjeddlmZedej&ddZej&d d Zej&d d Zej&d dj/ddZdddddddddddd Zddddd d!d"d#d$d%d&d&d'd(d)Zd*ZGd+d,Zd-Zd.Zd/Zd0Z e!d1k(re yy)2u Prop Line Snapshot Worker — Captures genuine closing line data for CLV analysis. PROBLEM: PPL rows are created via createMany({skipDuplicates:true}) with a unique key that includes oddsAmerican. Each odds change creates a NEW row, but snapshotAt is rarely set and hoursToGame is never set. PropLineHistory had fake timestamps. SOLUTION: Every 2 hours, query SGO API for upcoming events with current player prop odds, and write genuine snapshots to PropLineHistory with actual hoursToGame. This gives us a real timeline of odds movement for CLV calculation. RUN: PM2 process (auto-restart, never goes offline) N)datetimetimezone timedelta) defaultdict) load_dotenvz/var/www/html/eventheodds/.envSPORTSGAMEODDS_API_KEY 47d6ce020d896ece307a284e8c78ff7fSPORTSGAMEODDS_BASE_URLz!https://api.sportsgameodds.com/v2SPORTSGAMEODDS_HEADERz x-api-keySPORTS_DATABASE_URLzSpostgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports?NBANHLMLBNCAABEPL BUNDESLIGALA_LIGA IT_SERIE_A FR_LIGUE_1UEFA_CHAMPIONS_LEAGUEMLS) nbanhlmlbncaabepl bundesligala_ligaserie_aligue_1champions_leaguemlspointsreboundsassiststhreePointersMadestealsblocks turnoverspoints+rebounds+assists fantasyScoregoals shots_onGoalshotssaves)r$r%r&r'r(r)r*r+r,r-r. shots_on_goalr/r0i ceZdZdZdZddZy) SGOClientz.Lightweight SGO API client for prop snapshots.ctj|_|jjj t t iy)N)requestsSessionsessionheadersupdate HEADER_NAMEAPI_KEY)selfs 9/var/www/html/eventheodds/scripts/prop_snapshot_worker.py__init__zSGOClient.__init__Ms/'')  ##['$:;c^td}||ddd} |jj||d}|jdk(rt d|d d gS|j |j }|jd gS#t$r}t d |d|d gcYd}~Sd}~wwxYw)z+Get upcoming events with odds for a league.z/eventstruefalse)leagueIDlimit oddsAvailablestarted)paramstimeoutiz SGO API 400 for u — invalid leagueID?Tflushdataz SGO API error for z: N)BASE_URLr7get status_codeprintraise_for_statusjson Exception)r< league_idrDurlrHresprLes r=get_upcoming_eventszSGOClient.get_upcoming_eventsQs '"!#   <<##C#CD3&*9+5KLTXY  ! ! #99;D88FB' '  ( 2aS9 FI s#?B1B B, B'!B,'B,N)2)__name__ __module__ __qualname____doc__r>rXr?r=r3r3Js8<r?r3c @|jdi}|sgSg}|jD]2\}}|jdd}|jdd}|jdd}|jdd}|jdd} |dk7s |d k7s| d k7rq|d vrvtj|} | s|jd } |jd } |jdxs|jd} d}| r tt | }|| r tt | }d}| r t | }|||j|| |||d5|S#t t f$rY\wxYw#t t f$rYXwxYw#t t f$rY]wxYw)zExtract player prop odds from an SGO event. Returns list of dicts: {playerExternalId, propType, lineValue, oddsAmerican, oddId} Only extracts over-side game-level player props. oddsstatID statEntityIDperiodID betTypeIDsideIDgameouover)homeawayallrbbookOddsfairOdds bookOverUnder fairOverUnderN)playerExternalIdpropType lineValue oddsAmericanoddId)rNitems PROP_STATSintfloat ValueError TypeErrorappend)eventr`propsodd_idodd_datastat_id entity_id period_idbet_typeside_id prop_type book_odds_str fair_odds_strline_value_str odds_american line_values r=extract_player_propsrgs 99VR D  E JJL5 ,,x,LL4 LLR0 << R0,,x,  (d"2g6G  3 3 NN7+  ! Z0  Z0 !o6W(,,:W   #E-$8 9   ]  #E-$8 9   ">2   J$6  )!#)   _5 n L5 *    *   *  s6E!E64 F !E32E36FF FFci}|Dcgc]}|d }}|rwdjdgt|z}|jd|d|Dcgc]}d| c}|jD]\}} } |j dd} | | f|| <!|Dcgc] }|d|vs |} }| r| D] } | d}| j dij d d}| j d i}|j d ij d i}|j d dxs&|j ddxs|j dd}|r|s t j|j dd}|jd}|jd||||f|j}|s|d|df||< |Scc}wcc}wcc}w#ttf$rY1wxYw)uMap SGO eventIDs to SportsGame rows. Strategy 1: Direct lookup via externalGameId = 'sgo:{eventID}' Strategy 2: Fallback — match by homeTeam + gameDate (±1 day) Returns: {sgo_event_id: (sg_id, game_date)} eventID,z%szz SELECT "externalGameId", id, "gameDate" FROM "SportsGame" WHERE "externalGameId" IN (z ) zsgo:rbstatusstartsAtteamsrjnameslongmediumshortZz+00:00z%Y-%m-%da SELECT id, "gameDate" FROM "SportsGame" WHERE league = %s AND LOWER("homeTeam") = LOWER(%s) AND "gameDate"::date BETWEEN %s::date - 1 AND %s::date + 1 LIMIT 1 r) joinlenexecutefetchallreplacerNr fromisoformatstrftimerzr{fetchone)curevents canon_leaguesg_mapevt sgo_event_ids placeholderseidext_idsg_id game_datesgo_eid unmatchedr} starts_atr home_names home_teamgame_dt game_date_strrows r=build_game_maprs!F066S^6M6xx]); ;<  ()5~6  '4 4sSEl 4  6 ),  1 $FE9nnVR0G$i0F7O 1 !'G#i.*FGIG /E "C (B/33JCIIIgr*E62.227B?J"vr2qjnnXr6RqV`VdVdelnpVqII "001B1B31QR ' 0 0 < KK =-H J,,.C"1vs1v.s 9 /< M[75 H  *  s) F: F?  GG 6G  GGctjtj}t dddt d|j ddt ddt jt}d}d}d}d} |j}tjD]\}} |j| } | st d|d d,t|| |} t| } t| | z } d}d}d}d}| D]Q}|d }| j|}|s|\}}d }|rX|j |j#tj n|}t%dt'||z j)d z d}t+|}|s|D]}|d}|d}|d}|d}|j-d|t/|||f|j1}|r|d|k(r|dz }V|d u} |d uxr|d|k7}!|r|dnd }"|j-d|t/||||||||"|"r||"z nd | f |dz }| r|dz }|!s|dz }|j3T||z }||z }||z }||z }t d|dt| d| d| d|d|d|d|dd|j-d|j4}#|j3|#dkDrt d|#d d|j7|j7t d"|d|d|d|d# d|S#t8$r>}$|j;t d!|$dt=j>Yd }$~$nd }$~$wwxYw#|j7wxYw)$z/Run one full snapshot cycle across all leagues. A=================================================================TrJuPROP SNAPSHOT CYCLE — z%Y-%m-%d %H:%M:%S UTCrz [z] No upcoming eventsrN)tzinforqrrrsrta SELECT "oddsAmerican" FROM "PropLineHistory" WHERE "playerExternalId" = %s AND "gameId" = %s AND "propType" = %s AND "lineValue" = %s ORDER BY "snapshotAt" DESC LIMIT 1 ra INSERT INTO "PropLineHistory" (league, "gameId", "playerExternalId", "propType", "snapshotAt", "hoursToGame", "lineValue", "oddsAmerican", "previousLine", "lineChange", "isOpening", "createdAt") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) z] z events, z mapped (u unmatched) → z snapshots (z new, z changed, z unchanged)a UPDATE "PlayerPropLine" ppl SET "isClosingLine" = TRUE FROM ( SELECT DISTINCT ON (plh."playerExternalId", plh."gameId", plh."propType", plh."lineValue") plh."playerExternalId", plh."gameId"::bigint AS game_id, plh."propType", plh."lineValue" FROM "PropLineHistory" plh JOIN "SportsGame" sg ON plh."gameId" = sg.id::text WHERE sg."gameDate" BETWEEN NOW() - INTERVAL '3 hours' AND NOW() ORDER BY plh."playerExternalId", plh."gameId", plh."propType", plh."lineValue", plh."hoursToGame" ASC NULLS LAST ) closing WHERE ppl."playerExternalId" = closing."playerExternalId" AND ppl."gameId" = closing.game_id AND ppl."propType" = closing."propType" AND ppl."lineValue" = closing."lineValue" AND ppl."isClosingLine" IS NOT TRUE z Marked z PPL rows as isClosingLinez ERROR in snapshot cycle: z TOTAL: z unchanged/skipped)) rnowrutcrPrpsycopg2connectDB_URLcursorLEAGUESrvrXrrrNrrmaxround total_secondsrrstrrcommitrowcountcloserSrollback traceback print_exc)%clientrconntotal_snapshots total_new total_changed total_skippedrr sgo_league_idrrmatchedr league_newleague_changedleague_skipped league_totalr}rmappingrr hours_to_gamerr~proppidptlvr`lastis_new is_changed previous_oddsclosing_markedrWs% r=snapshot_cyclers! ,,x|| $C Bvh-t$ $S\\2I%J$K LTXY VHT"   F #DOIMMAkkm+2==?\  'L-// >FL>)=>dK$C>F&kGF g-IJNNLC I& **S/#* y!% HQHXHXH`i//x||/DfoG$'5'C-1N1N1PSW1WYZ+[$\M,U3!.,D12Cj)Bk*B/DKK !s5z2r2 4<<>DQ4&!+ !T\F!%T!1!Ed1goJ/3DGMKK!%c%j#r]B%2? -T  !A%L"a !&!+].,b GC J | +O  #I ^ +M ^ +M C ~RF }IgYG[ 0? \'7z.AQQ\^ s\ ~   & A  In--GHPT U   K( YKv O:m_4G IPTV    +A3/t< s2F:L>B3L M$&4MM'M$$M''M9ctddtddtdtdtdz dd dtd d jtj dtd t d dddtddt } |jjtdd}|j}td|jddd tjt }|j}|jd|j!d}td|ddd|jd|jd|jd|jd|j#|j%td dd} |d"z } t+|}td#|d$tdz dd%dt1j2tG#t$r}td|dYd }~:d }~wwxYw#t$r/}td!|dt'j(d"Yd }~d }~wwxYw#t$rJ}td#|d&|dt-j.td'tdz dd(dYd }~d }~wwxYw))u+Main loop — runs forever as a PM2 worker.rTrJzPROP SNAPSHOT WORKER STARTINGz Interval: zs (rz.1fzh)z Leagues: z, zDB: NrYz...z/account/usage )rIzSGO API: credits remaining = creditsRemainingr zWARNING: SGO API check failed: z&SELECT COUNT(*) FROM "PropLineHistory"rzPropLineHistory: rz existing rowszCREATE INDEX IF NOT EXISTS "PropLineHistory_player_game" ON "PropLineHistory" ("playerExternalId", "gameId")zpCREATE INDEX IF NOT EXISTS "PropLineHistory_snapshot" ON "PropLineHistory" ("snapshotAt")zwCREATE INDEX IF NOT EXISTS "PropLineHistory_league_game" ON "PropLineHistory" (league, "gameId")zCREATE INDEX IF NOT EXISTS "PropLineHistory_clv_lookup" ON "PropLineHistory" ("playerExternalId", "gameId", "propType", "lineValue", "snapshotAt" DESC)zDB: OK, indexes verifiedzDB connection failed: rz Cycle z complete. Next in zh. Sleeping...z FAILED: zWill retry in zh...)rPSNAPSHOT_INTERVALrrkeysrr3r7rNrMrRrSrrrrrrrsysexitrrrtimesleep)rrVusagerWrrcountcycles r=mainr{s ($ )6 J()->t-CC,H KSWX Idii /0 1> D S !. ($ [FA~~!!XJn"=r!J  -eii8JC.P-QRZ^_ 'kkm <= q! !%.9F N O > ? B C z {   (5 E    Q"6*E HUG#67H7Mc6RR`aim n $% 9 A /s34@@A.  &qc*$7   Q HUG9QC0 =    ! N#4T#9#">dC4 P P QsKAH 1CH2 'I- H/H**H/2 I*;%I%%I*- K6AJ;;K__main__)"r]osrrrr5rrrr collectionsrpathinsertdirnameabspath__file__dotenvrgetenvr;rMr:splitrrrwrr3rrrrrZr^r?r=rsx  22#277??277??8#<=> ,- ")),.P Q 299.0S Tbii/= (* A G GHK LMN O     /   ,8" "#   $:BJ7tRj6&r zFr?