
    حiQ                        d Z ddlZddlZddlZddlZddlZddlZddlmZmZm	Z	 ddl
mZ ej                  j                  dej                  j                  ej                  j                  e                   ddlmZ  ed        ej&                  dd      Z ej&                  d	d
      Z ej&                  dd      Z ej&                  dd      j/                  d      d   ZddddddddddddZddddd d!d"d#d$d%d&d&d'd(d)Zd*Zd+d,d- ed.      d/fgZi Zi Zi Z d0 Z!d1 Z"d2 Z# G d3 d4      Z$d5 Z%d6 Z&d7 Z'd8 Z(d>d9Z)d: Z*d; Z+d< Z,e-d=k(  r e,        yy)?u  
Prop Line Snapshot Worker V2 — Dynamic Frequency Based on Hours-to-Game
========================================================================
Upgrade from flat 2-hour intervals to market-grade snapshot frequency:

  | Hours to Game | Snapshot Interval |
  |---------------|-------------------|
  | >12h          | Every 60 min      |
  | 3h–12h        | Every 15 min      |
  | 30min–3h      | Every 5 min       |
  | <30min        | Every 2 min       |

The worker runs a tight main loop (every 2 minutes) and decides per-league
whether enough time has elapsed since the last fetch, based on the closest
game's hoursToGame. This captures the critical final-hour line movements
that drive real CLV measurement.

RUN: PM2 process (replaces prop-snapshot-worker)
    N)datetimetimezone	timedelta)defaultdict)load_dotenvz/var/www/html/eventheodds/.envSPORTSGAMEODDS_API_KEY 47d6ce020d896ece307a284e8c78ff7fSPORTSGAMEODDS_BASE_URLz!https://api.sportsgameodds.com/v2SPORTSGAMEODDS_HEADERz	x-api-keySPORTS_DATABASE_URLzSpostgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports?NBANHLMLBNCAABEPL
BUNDESLIGALA_LIGA
IT_SERIE_A
FR_LIGUE_1UEFA_CHAMPIONS_LEAGUEMLS)nbanhlmlbncaabepl
bundesligala_ligaserie_aligue_1champions_leaguemlspointsreboundsassiststhreePointersMadestealsblocks	turnoverspoints+rebounds+assistsfantasyScoregoalsshots_onGoalshotssaves)r$   r%   r&   r'   r(   r)   r*   r+   r,   r-   r.   shots_on_goalr/   r0   x   )g      ?r2   )g      @i,  )g      (@i  inf  c                 2    t         D ]  \  }}| |k  s|c S  y)zEDetermine snapshot interval (seconds) based on hours to closest game.r4   )FREQUENCY_TIERS)hours_to_gamemax_htgintervals      </var/www/html/eventheodds/scripts/prop_snapshot_worker_v2.pyget_required_intervalr;   ]   s(    , G#O     c                    t         j                  |       }|yt        j                  | g       }|s||z
  j                         }|dk\  S t	        d      }|D ]*  }t        d||z
  j                         dz        }||k  s)|}, |dk  r||z
  j                         }|dk\  S t        |      }||z
  j                         }||k\  S )zKCheck if this league needs a fresh SGO API fetch based on its closest game.Tr4   r3   r   )league_last_fetchgetleague_game_startstotal_secondsfloatmaxr;   )	leaguenow
last_fetchstartselapsedmin_htgstart_dthtgrequired_intervals	            r:   should_fetch_leaguerM   e   s    "&&v.J   ##FB/F#224$ ElG !hn335<==G !|#224$-g6Z..0G'''r<   c                 
   g }|D ]^  }|j                  di       j                  dd      }|s(	 t        j                  |j                  dd            }|j	                  |       ` |t        | <   y# t
        t        f$ r Y |w xY w)zJCache game start times from SGO API response for hoursToGame calculations.statusstartsAt Z+00:00N)r?   r   fromisoformatreplaceappend
ValueError	TypeErrorr@   )rD   eventsrG   event	starts_atdts         r:   update_game_starts_cacher]      s    F IIh+//
B?	++I,=,=c8,LMb! "(v 	* s   6A00BBc                       e Zd ZdZd ZddZy)	SGOClientz.Lightweight SGO API client for prop snapshots.c                     t        j                         | _        | j                  j                  j	                  t
        t        i       y )N)requestsSessionsessionheadersupdateHEADER_NAMEAPI_KEY)selfs    r:   __init__zSGOClient.__init__   s/    '')##['$:;r<   c                 <   t          d}||ddd}	 | j                  j                  ||d      }|j                  dk(  rg S |j	                          |j                         }|j                  dg       S # t        $ r}t        d	| d
| d       g cY d}~S d}~ww xY w)z+Get upcoming events with odds for a league.z/eventstruefalse)leagueIDlimitoddsAvailablestarted   )paramstimeouti  dataz  SGO API error for z: TflushN)BASE_URLrc   r?   status_coderaise_for_statusjson	Exceptionprint)rh   	league_idrn   urlrr   resprt   es           r:   get_upcoming_eventszSGOClient.get_upcoming_events   s    
'"!#	
		<<##C#CD3&	!!#99;D88FB'' 	(2aS9FI	s#   .A3 1A3 3	B<BBBN)2   )__name__
__module____qualname____doc__ri   r    r<   r:   r_   r_      s    8<r<   r_   c           
         | j                  di       }|sg S g }|j                         D ]L  \  }}|j                  dd      }|j                  dd      }|j                  dd      }|j                  dd      }|j                  dd      }	|dk7  s
|d	k7  s|	d
k7  rq|dv rvt        j                  |      }
|
s|j                  d      }|j                  d      }|j                  d      xs |j                  d      }d}|r	 t        t	        |            }||r	 t        t	        |            }d}|r	 t	        |      }||d}|r	 t        t	        |            }|j                  ||
||||d       O |S # t
        t        f$ r Y vw xY w# t
        t        f$ r Y rw xY w# t
        t        f$ r Y ww xY w# t
        t        f$ r Y mw xY w)z+Extract player prop odds from an SGO event.oddsstatIDrQ   statEntityIDperiodID	betTypeIDsideIDgameouover)homeawayallrQ   bookOddsfairOddsbookOverUnderfairOverUnderN)playerExternalIdpropType	lineValueoddsAmericanr   oddId)r?   items
PROP_STATSintrB   rW   rX   rV   )rZ   r   propsodd_idodd_datastat_id	entity_id	period_idbet_typeside_id	prop_typebook_odds_strfair_odds_strline_value_strodds_american
line_valuefair_odds_vals                    r:   extract_player_propsr      s   99VR D	E JJL 9,,x,LL4	LLR0	<<R0,,x,(d"2g6G33NN7+	 Z0 Z0!o6W(,,:W #E-$8 9  ] #E-$8 9 
">2
  J$6  #E-$8 9 	 )!#)%
 	e9v LG 	* 
 	*  	*  	* sH   E;F4F%
F:;FFF"!F"%F76F7:GGc                    i }|D cg c]  }|d   	 }}|rwdj                  dgt        |      z        }| j                  d| d|D cg c]  }d| 	 c}       | j                         D ]  \  }}	}
|j	                  dd      }|	|
f||<   ! |D cg c]  }|d   |vs| }}|r|D ]
  }|d   }|j                  di       j                  d	d      }|j                  d
i       }|j                  di       j                  di       }|j                  dd      xs& |j                  dd      xs |j                  dd      }|r|s	 t        j                  |j	                  dd            }|j                  d      }| j                  d||||f       | j                         }|s|d   |d   f||<    |S c c}w c c}w c c}w # t        t        f$ r Y 1w xY w)z$Map SGO eventIDs to SportsGame rows.eventID,z%szz
            SELECT "externalGameId", id, "gameDate"
            FROM "SportsGame"
            WHERE "externalGameId" IN (z
)
        zsgo:rQ   rO   rP   teamsr   nameslongmediumshortrR   rS   z%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      )joinlenexecutefetchallrU   r?   r   rT   strftimerW   rX   fetchone)currY   canon_leaguesg_mapevtsgo_event_idsplaceholderseidext_idsg_id	game_datesgo_eid	unmatchedrZ   r[   r   
home_names	home_teamgame_dtgame_date_strrows                        r:   build_game_mapr      s   F/56S^6M6xx]); ;< ( )5~ 6	 '44sSEl4		6
 ), 	1$FE9nnVR0G$i0F7O	1 !'G#i.*FGIG 	/E	"C		(B/33JCIIIgr*E62.227B?J"vr2qjnnXr6RqV`VdVdelnpVqII"001B1B31QR ' 0 0 < KK  	=-HJ ,,.C"1vs1v.s3	/6 MS 7 5
 H 	* s)   F:	F?
GG	6G		GGc                 <   | j                  |      }|syt        ||       t        |||      }t        |      }d}	d}
d}d}|D ]Q  }|d   }|j	                  |      }|s|\  }}d}|rX|j
                   |j                  t        j                        n|}t        dt        ||z
  j                         dz  d            }t        |      }|s|D ]  }|d   }|d	   }|d
   }|d   }|j                  d|t        |      ||f       |j                         }|r|d   |k(  r|dz  }V|du }|duxr |d   |k7  }|r|d   nd}|j                  d|t        |      ||||||||r||z
  nd|f       |dz  }|r|	dz  }	|s|
dz  }
 |j!                          T ||	|
|fS )zASnapshot a single league. Returns (new, changed, skipped) counts.r   r   r   r   r   r   N)tzinfor4      r   r   r   r   a<  
                SELECT "oddsAmerican"
                FROM "PropLineHistory"
                WHERE "playerExternalId" = %s
                  AND "gameId" = %s
                  AND "propType" = %s
                  AND "lineValue" = %s
                ORDER BY "snapshotAt" DESC
                LIMIT 1
            r   a]  
                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())
            )r   r]   r   r   r?   r   rU   r   utcrC   roundrA   r   r   strr   commit)clientr   connr   sgo_league_idrE   rY   r   matched
league_newleague_changedleague_skippedleague_totalrZ   r   mappingr   r   r7   r   r   proppidptlvr   lastis_new
is_changedprevious_oddss                                 r:   snapshot_leaguer   &  s    ''6F \62C6F&kGJNNL @I**S/"y@I@P@P@Xi''x||'<^gG5'C-)F)F)H4)OQR#STM$U+ -	$D)*Cj!Bk"B'D KK 	 s5z2r*	, <<>DQ4!#T\FT)=d1goJ'+DGMKK  c%j#r]B*7%T ALa
!#[-	$^ 	A@D ^^CCr<   c                 `    | j                  d       | j                  }|j                          |S )z5Mark isClosingLine on PPL for recently started games.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
    )r   rowcountr   )r   r   markeds      r:   mark_closing_linesr   |  s,    KK  	& \\FKKMMr<   c                    t        d| dz         D ]&  }	 t        j                  t              }d|_        |c S  y	# t        j
                  $ r?}|| k  r/t        d| d|  d| d| d       t        j                  |       n Y d	}~wd	}~ww xY w)
z?Create a DB connection with retry logic for transient failures.r   Fz  DB connect failed (attempt /z), retrying in zs: Tru   N)	rangepsycopg2connectDB_URL
autocommitOperationalErrorr|   timesleep)max_retriesretry_delayattemptr   r   s        r:   _connect_dbr     s    K!O, 
		##F+D#DOK	

 (( 	$5gYa}O\g[hhklmknow{|

;' (	s   !:B5BBc                     	 | j                    | j                         }|j                  d       |j                          |j	                          y# t
        $ r Y yw xY w)z.Check if a psycopg2 connection is still alive.zSELECT 1FT)isolation_levelcursorr   r   closer{   )r   r   s     r:   _is_connection_deadr    sN    kkmJ		 s   AA 	AAc                 <   t        j                  t        j                        g }t        j                         D ]%  \  }}t        |      s|j                  ||f       ' |syt        dd d       t        d| dj                  d       dt        |       d	t        t               d
	d       t        d d       t               }d}d}d}d}		 |j                         }
|D ]  \  }}t        j                  |g       }d}|rt        fd|D              }	 t!        | |
|||      \  }}}}t.        |<   ||z  }||z  }||z  }|	|z  }	|t1        |      nd}||ddnd}t        d| d| d| d| d| d| d| dd        	 t3        |      rt               }|j                         }
t5        |
|      }|dkD  rt        d| dd       |
j'                          	 |j'                          t        d"| d| d| d|	 d	d       |S # t"        j$                  $ r}t        d| d| d       	 |j'                          n# t(        $ r Y nw xY wt               }|j                         }
	 t!        | |
|||      \  }}}}nD# t(        $ r8}t        d| d| d       t+        j,                          d\  }}}}Y d}~nd}~ww xY wY d}~d}~ww xY w# t"        j$                  $ r}t        d | d       Y d}~0d}~ww xY w# t(        $ rP}	 |j7                          n# t(        $ r Y nw xY wt        d!| d       t+        j,                          Y d}~|d}~ww xY w# t(        $ r Y w xY w# 	 |j'                          w # t(        $ r Y w w xY wxY w)#uP   One tick of the main loop — check which leagues need snapshots and fetch them.r   
A=================================================================Tru   zTICK u    — z%Y-%m-%d %H:%M:%S UTCr   z leagues dueNc              3   ^   K   | ]$  }t        d |z
  j                         dz         & yw)r   r4   N)rC   rA   ).0srE   s     r:   	<genexpr>ztick_cycle.<locals>.<genexpr>  s*     ^1c!a#g%<%<%>%EF^s   *-z  DB connection lost during [z], reconnecting: z  Retry failed for [z]: r   r4   z.1fhr   z  [z
] closest=z
 interval=u   s → z snaps (z new, z chg, z skip)z	  Marked z PPL rows as isClosingLinez3  DB error during closing line marking (skipping): z  ERROR in tick: z	  TOTAL: )r   rE   r   r   LEAGUESr   rM   rV   r|   r   r   r   r   r@   r?   minr   r   r   r  r{   	traceback	print_excr>   r;   r  r   rollback)r   	cycle_numleagues_to_fetchr   r   r   total_snapshots	total_newtotal_changedtotal_skippedr   cached_startsrI   ltlnlclsr   retry_er9   htg_strclosing_markedrE   s                         @r:   
tick_cycler    s   
,,x||
$C '.}} C#m|S1##\=$ABC 	Bvh-t$	E)E#,,/F"G!H I%&'qWlDKOQ	VHT"=DOIMMCkkm+; &	Q'L-.22<DMG^P]^^0!0dLR_ad!eBB$ /2l+r!OOIRMRM :A9L,W5RVH+2+>Q'CGC~Zy
8* MHRDrd&FDKOQK&	QR	Y"4("}kkm/T:N!	.!11KLTXY 				JJL 
Io&hykO6-8?CEw ,, 05l^CTUVTWX`deJJL  "}kkm0%4VS$Vceh%iNBB  00c'KSWX'')%/NBB00N (( 	YGsKSWXX	Y
  	MMO 		!!%T2  			JJL 		s#  AL H2AL AK L &M) K%K9I
	K
	IKIK4J
	K
	K.KKKKL KL L
/L?L L

L 	M&L('M!(	L41M!3L44'M!M9 !M&&M9 )	M65M69N;NN	NNNNc            
         t        dd       t        dd       t        dt         dt        dz  dd	d       t        d
d       t        D ]-  \  } }| dk  rd|  dnd}t        d| d| d|dz  dd	d       / t        ddj                  t        j                                d       t        dt        dd  dd       t        dd       t               }	 |j                  j                  t         dd      }|j                         }t        d|j                  dd       d       	 t               }|j                         }|j                  d       |j!                         d   }	t        d |	d!d"d       |j                  d#       |j                  d$       |j                  d%       |j                  d&       |j                  d'       |j#                          |j%                          t        d(d       d}
	 |
d*z  }
	 t+        ||
       t1        j2                  t               ,# t        $ r}t        d| d       Y d}~!d}~ww xY w# t        $ r/}t        d)| d       t'        j(                  d*       Y d}~d}~ww xY w# t        $ r1}t        d+|
 d,| d       t-        j.                          Y d}~d}~ww xY w)-uB   Main loop — runs forever as a PM2 worker with dynamic frequency.r  Tru   u-   PROP SNAPSHOT WORKER V2 — DYNAMIC FREQUENCYzTick interval: zs (<   z.0fzmin)zFrequency tiers:i  <r
  z>12hz  z: every z	Leagues: z, zDB: Nr   z...z/account/usage
   )rs   zSGO API: credits remaining = creditsRemainingr   zWARNING: SGO API check failed: z&SELECT COUNT(*) FROM "PropLineHistory"r   zPropLineHistory: r   z 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)zCREATE INDEX IF NOT EXISTS "PropLineHistory_velocity"
                       ON "PropLineHistory" ("gameId", "propType", "playerExternalId", "snapshotAt")zDB: OK, indexes verifiedzDB connection failed: r   z
Tick z	 FAILED: )r|   TICK_INTERVALr6   r   r  keysr   r_   rc   r?   rw   rz   r{   r   r   r   r   r   r  sysexitr  r  r  r   r   )r8   r9   	htg_labelr   r   usager   r   r   countcycles              r:   mainr,    s   	($	
9F	OM?#mB.>s-C4
HPTU	T*, V&-mayN	9+XhZs8B;s2C4HPTUV 
Idii/0
1>	DS
!.	($[FA~~!!XJn"=r!J		-eii8JC.P-QRZ^_
}kkm<=q!!%.9F N 	O > 	? B 	C z 	{ 	 h 	i

(5
 E

	"vu%
 	

=! =  A/s34@@A2  &qc*$7  	"GE7)A3/t<!!	"sJ   AH5 4CI J 5	I>II	J%%JJ	K 'KK__main__)      ).r   osr&  r   r   ra   r  r   r   r   collectionsr   pathinsertdirnameabspath__file__dotenvr   getenvrg   rw   rf   splitr   r  r   r$  rB   r6   r>   r@   league_event_cacher;   rM   r]   r_   r   r   r   r   r   r  r  r,  r   r   r<   r:   <module>r;     s  & 
 
     2 2 # 277??277??8#<= >  , - ")),.P
Q299.0STbii/=	(i
kkpkpqtkuvw
y
 /  ,8""#
$ 
 
5\4	    (D( 8BJ-`SDl4
_D:"z zF r<   