
    \j&                         S r SSKrSSKrSSKrSSKJr  SSKrSSKrSSK	r	SSK
r
SSKrSr/ SQrS\R                  S\4S jrS\4S	 jr\S
:X  a  \R(                  " \" 5       5        gg)u  
Sincronizza le partite future: scarica fixtures.csv da football-data.co.uk
(prossimi turni con le quote correnti), inserisce le partite nel DB con
result NULL e salva uno snapshot delle quote in odds_snapshots.

Pensato per girare ogni giorno (Task Scheduler). Idempotente: le partite
esistenti vengono aggiornate, e lo snapshot quote viene aggiunto solo se
l'ultimo salvato è più vecchio di --min-snapshot-hours (default 6).

Uso:
    python scripts/sync_fixtures.py
    N)datetimez,https://www.football-data.co.uk/fixtures.csv)I1I2E0SP1D1F1dreturnc                     U R                   S:  a  U R                  OU R                  S-
  nU S[        US-   5      SS   3$ )N      /   )monthyearstr)r
   ys     0C:\wamp64\www\Tigertips\scripts\sync_fixtures.pyinfer_seasonr      s?    ''Q,AFFQJAS#a!e*QR.!""    c                  
   [         R                  " SS9n U R                  S[        SS9  [        R
                  " U 5        U R                  5       n[        R                  " [        SS9nUR                  5         UR                  nUS S S	:X  a  S
OSn[        R                  " [        R                  " U5      USS9nUR                   R"                  R%                  5       Ul        UR'                  / SQS9nXUS   R)                  [*        5         nUR,                  (       a  [/        S5        g[        R0                  " US   SSSS9US'   UR'                  S/S9n[/        S[3        U5       SUS   R5                  5       R7                  5        SUS   R9                  5       R7                  5        S35        [:        R<                  " UR>                  UR@                  URB                  URD                  SS9nURG                  5       nURI                  S5        [K        URM                  5       5      nS n	S u  pURO                  5        GH  u  pS! H  nURI                  S"X   45        M     URI                  S#US$   45        URQ                  5       S   nURI                  S#US%   45        URQ                  5       S   nURI                  S&XS      [S        US   5      US   R7                  5       U	" US'5      UUU	" US(5      U	" US)5      U	" US*5      U	" US+5      U	" US,5      U	" US-5      U	" US.5      U	" US/5      U	" US05      U	" US15      U	" US25      U	" US35      45        XRT                  S4:H  -  n
URI                  S5US   R7                  5       UU45        URQ                  5       S   nS6 GH#  u  nnnnU	" UU5      U	" UU5      U	" UU5      nnnU(       d  M-  U(       d  M6  U(       d  M?  US4:  d  MG  US4:  d  MO  US4:  d  MW  URI                  S7U45        URQ                  5       S   nU(       a)  [V        RX                  " 5       U-
  R[                  5       S8-  OS nUb  UUR\                  :  a`  S4U-  S4U-  -   S4U-  -   nURI                  S9UUUUU[_        S4U-  U-  S:5      [_        S4U-  U-  S:5      [_        S4U-  U-  S:5      45        US4-  n  GM     GM     URa                  5         [/        S;U
 S<U 35        URc                  5         g)=Nz$Sync partite future + snapshot quote)descriptionz--min-snapshot-hoursg      @)typedefault   )timeout   s   ﻿z	utf-8-sigzlatin-1skip)encodingon_bad_lines)DivDateHomeTeamAwayTeam)subsetr"   zTNessuna partita futura per le nostre leghe in fixtures.csv (normale fuori stagione).r   r#   Tmixedcoerce)dayfirstformaterrorszFixtures trovati: z
 partite (z -> )	tigertips)hostportuserpassworddatabasezSELECT code, id FROM leaguesc                 0   U R                  U5      nUbK  [        U[        5      (       a  [        R                  " U5      (       d  [
        R                  " U5      (       a  g [        U[        [        [        R                  45      (       a  [        U5      $ U$ )N)	get
isinstancefloatnpisnanpdisnaintfloating)rowcolvs      r   r4   main.<locals>.getA   sa    GGCL9Au--"((1++"''!**%a#ubkk)BCCuQxJJr   )r   r   )r$   r%   z+INSERT IGNORE INTO teams (name) VALUES (%s)z$SELECT id FROM teams WHERE name = %sr$   r%   ay  
            INSERT INTO matches (league_id, season, match_date, match_time,
                                 home_team_id, away_team_id,
                                 b365_home, b365_draw, b365_away,
                                 pinnacle_home, pinnacle_draw, pinnacle_away,
                                 avg_home, avg_draw, avg_away,
                                 max_home, max_draw, max_away)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                season = VALUES(season), match_time = VALUES(match_time),
                b365_home = VALUES(b365_home), b365_draw = VALUES(b365_draw),
                b365_away = VALUES(b365_away),
                pinnacle_home = VALUES(pinnacle_home),
                pinnacle_draw = VALUES(pinnacle_draw),
                pinnacle_away = VALUES(pinnacle_away),
                avg_home = VALUES(avg_home), avg_draw = VALUES(avg_draw),
                avg_away = VALUES(avg_away),
                max_home = VALUES(max_home), max_draw = VALUES(max_draw),
                max_away = VALUES(max_away)
        TimeB365HB365DB365APSHPSDPSAAvgHAvgDAvgAMaxHMaxDMaxAr   zXSELECT id FROM matches WHERE match_date = %s AND home_team_id = %s AND away_team_id = %s))rH   rI   rJ   zfd-avg)rE   rF   rG   zfd-pinnacle)rB   rC   rD   zfd-b365z
                    SELECT MAX(taken_at) FROM odds_snapshots
                    WHERE match_id = %s AND stage = 'pre'
                i  aR  
                        INSERT INTO odds_snapshots
                            (match_id, taken_at, stage, source,
                             odds_home, odds_draw, odds_away,
                             prob_home, prob_draw, prob_away)
                        VALUES (%s, NOW(), 'pre', %s, %s, %s, %s, %s, %s, %s)
                       zPartite nuove inserite: z | snapshot quote salvati: )2argparseArgumentParseradd_argumentr6   configadd_db_args
parse_argsrequestsr4   FIXTURES_URLraise_for_statuscontentr9   read_csvioBytesIOcolumnsr   stripdropnaisinLEAGUESemptyprintto_datetimelenmindatemaxpymysqlconnectr.   r/   r0   r1   cursorexecutedictfetchalliterrowsfetchoner   rowcountr   nowtotal_secondsmin_snapshot_hoursroundcommitclose)parserargsresprawr    dfconncur
league_idsr4   n_newn_snap_rteam_colhome_idaway_idmatch_idhr
   asourceohodoalastage_hovers                               r   mainr   #   s   $$1WXF
.UCH
vD<<b1D
,,C!"1g8{iH	RZZ_xf	MB%%'BJ	A	BB	uINN7#	$B	xx * 	+6
T'RZ[BvJ	6(	#B	s2wi (jnn##%&d2f:>>+;+@+@+B*C1F G ??						$(MMKID
++-CKK./clln%JK ME0HKKE~V 1:Qz]<LM,,.#:Qz]<LM,,.# & 5"L6$;innAv!Wos1gAw!UmSE]C5M!Vnc!Vnc!Vn!Vnc!Vnc!Vn>'	?2 	"" BvY^^%w8	: <<>!$ HOAq!V QC1Is1ayBBrbbRRBFrAv"q&  [" ||~a(  #,,.4/>>@4G&* =ET-D-D$Dr6AF?QV3DKK ! #FBBBq15R$3JBq134 aKF1 HS F 	KKM	$UG+Fvh
OPJJLr   __main__)__doc__rO   rZ   sysr   numpyr7   pandasr9   rh   rR   rU   rV   r`   	Timestampr   r   r;   r   __name__exit r   r   <module>r      st     	 
      =
/#BLL #S #
kc k\ zHHTV r   