
    iB                        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ZddlZddl	m	Z	m
Z
mZ ddlmZmZmZmZ ej"                  j%                  dej"                  j'                  ej"                  j)                  e                   ddlmZ ej"                  j%                  dej"                  j'                  e             	 ddlmZmZmZmZmZ dZd	Z d
Z!dZ"dddddddZ#dddddddddddddZ$g d Z%g d!g d"g d#g d$g d%g d&dZ&g d'g d(g d)g d*d+Z'd,ee(e(f   fd-Z)d,e*fd.Z+d/e*fd0Z,d1e-d2e(d3e(d,e(fd4Z. G d5 d6      Z/d7 Z0d8e*d,e*fd9Z1d:e(d;e(d,ee(   fd<Z2	 dHd=e/d>ee(   d:e(d;e(d?e3f
d@Z4	 dHd=e/d>ee(   d:e(d;e(d?e3f
dAZ5	 dHd=e/d>ee(   d:e(d;e(d?e3f
dBZ6	 dHd=e/d>ee(   d:e(d;e(d?e3f
dCZ7	 dHd=e/d:e(d;e(d?e3fdDZ8dE Z9dF Z:e;dGk(  r e< e:              yy# e$ r dZY 	w xY w)Ia  
Full Historical Backfill Engine for The Odds API v4

5-tier backfill covering 60 days (Dec 22, 2025 -> Feb 20, 2026) across ALL available
markets and sports. Separate from existing historical_backfill_theoddsapi.py (untouched).

Tiers:
  1. Core markets (h2h, spreads, totals) for all sports            ~22K credits
  2. Historical player props (per-event)                           ~176K credits
  3. Historical period markets (quarters, halves, periods)          ~29K credits
  4. Historical alt lines (alternate_spreads, alternate_totals)     ~58K credits
  5. Extended sports (soccer, MMA, AFL, NRL, WNBA)                 ~36K credits

Usage:
    python historical_backfill_full_theoddsapi.py --tier 1 --dry-run
    python historical_backfill_full_theoddsapi.py --tier 1 --from 2025-12-22 --to 2026-02-20
    python historical_backfill_full_theoddsapi.py --tier 2 --leagues nba,nfl
    python historical_backfill_full_theoddsapi.py --tier all --batch 5
    python historical_backfill_full_theoddsapi.py --status
    N)datetimetimezone	timedelta)OptionalDictListTuple)normalize_to_full)log_ingestion_startlog_ingestion_completeget_or_create_sports_gameupsert_bookmaker_oddsinsert_odds_historyTFzhttps://api.the-odds-api.com/v4z5/var/www/html/eventheodds/data/backfill_progress.jsoniP  basketball_nbaamericanfootball_nflicehockey_nhlbaseball_mlbbasketball_ncaabamericanfootball_ncaaf)nbanflnhlmlbncaabncaafbasketball_wnba
soccer_eplsoccer_germany_bundesligasoccer_italy_serie_asoccer_spain_la_ligasoccer_france_ligue_onesoccer_uefa_champions_leaguesoccer_uefa_europa_leaguesoccer_usa_mlsmma_mixed_martial_artsaussierules_aflrugbyleague_nrl)wnbaepl
bundesligaseriealaligaligue1ucluelmlsmmaaflnrl)h2hspreadstotals)player_pointsplayer_reboundsplayer_assistsplayer_threesplayer_points_rebounds_assistsplayer_double_doubleplayer_blocksplayer_stealsplayer_turnoversplayer_points_reboundsplayer_points_assistsplayer_rebounds_assists)player_pass_tdsplayer_pass_ydsplayer_rush_ydsplayer_reception_ydsplayer_receptionsplayer_anytime_tdplayer_pass_attemptsplayer_pass_completionsplayer_pass_interceptionsplayer_rush_attemptsplayer_rush_tdsplayer_reception_tds)player_goalsplayer_shots_on_goalplayer_goal_scorer_anytimer7   r9   player_total_saves)batter_home_runspitcher_strikeoutsbatter_hits_runs_rbisbatter_hitsbatter_total_basesbatter_rbisbatter_strikeouts)r7   r8   r9   )rC   rE   rD   )h2h_q1
spreads_q1	totals_q1h2h_q2
spreads_q2	totals_q2h2h_q3
spreads_q3	totals_q3h2h_q4
spreads_q4	totals_q4h2h_h1
spreads_h1	totals_h1h2h_h2
spreads_h2	totals_h2)	rZ   r[   r\   rf   rg   rh   ri   rj   rk   )	h2h_p1
spreads_p1	totals_p1h2h_p2
spreads_p2	totals_p2h2h_p3
spreads_p3	totals_p3)h2h_1st_5_inningsspreads_1st_5_inningstotals_1st_5_innings)r   r   r   r   returnc                  n   d} d}d}	 t        | d      5 }|D ]  }|j                  d      rE|j                  dd      d   j                         j                  d      j                  d      d	   }Y|j                  d
      sk|j                  dd      d   j                         j                  d      } 	 ddd       |xs2 t
        j                  j                  dd      j                  d      d	   }|xs t
        j                  j                  d      }||fS # 1 sw Y   fxY w# t        $ r Y tw xY w)z'Load database URL and API key from .envz/var/www/html/eventheodds/.envNrzSPORTS_DATABASE_URL==   "?r   zTHE_ODDS_API_KEY=SPORTS_DATABASE_URL THE_ODDS_API_KEY)open
startswithsplitstripFileNotFoundErrorosenvironget)env_pathdb_urlapi_keyflines        .scripts/historical_backfill_full_theoddsapi.pyload_configr   ~   s*   /HFG(C  	GA G??#9:!ZZQ/288:@@EKKCPQRSF__%89"jja0399;AA#FG	G	G Nrzz~~&;R@FFsKANF;(:;G7?	G 	G  s/   D( A-D4D7D( D%!D( (	D43D4c                      	 t        t        d      5 } t        j                  |       cddd       S # 1 sw Y   yxY w# t        t        j
                  f$ r i cY S w xY w)zLoad checkpoint progressrz   N)r   PROGRESS_FILEjsonloadr   JSONDecodeError)r   s    r   load_progressr      sQ    -% 	 99Q<	  	  	 t334 	s#   = 1	= := = AAprogressc                     t        j                  t         j                  j                  t              d       t        t        d      5 }t        j                  | |dt               ddd       y# 1 sw Y   yxY w)zSave checkpoint progressT)exist_okw   )indentdefaultN)	r   makedirspathdirnamer   r   r   dumpstr)r   r   s     r   save_progressr      sP    KK.>	mS	! 6Q		(Aa56 6 6s   	A00A9tierleaguedate_strc                     d|  d| d| S )Nr   : )r   r   r   s      r   get_progress_keyr      s    $q(,,    c                       e Zd ZdefdZddededee   fdZdefdZ		 dd	ed
ede
e   dee   fdZd	ed
edee   fdZd	eded
ede
e   dee   f
dZy)HistoricalFetcherr   c                 n    || _         t        j                         | _        d| _        d | _        d| _        y )Nr   )r   requestsSessionsession
quota_usedquota_remainingrequests_made)selfr   s     r   __init__zHistoricalFetcher.__init__   s0    '')#r   Nendpointparamsrx   c                    |i }| j                   |d<   t         | }	 | j                  j                  ||d      }t	        |j
                  j                  dd            | _        t	        |j
                  j                  dd            | _        | xj                  dz  c_        |j                  d	k(  rt        d
       y|j                  dk(  ry|j                  dk(  rt        d| j                   d       y|j                  dk7  rt        d|j                          y|j                         S # t        $ r}t        d|        Y d}~yd}~ww xY w)z6Make API request with rate limiting and quota trackingNapiKey<   )r   timeoutzx-requests-usedr   zx-requests-remainingr|   i  zERROR: Invalid API keyi  i  zERROR: Quota exceeded (used: )   zERROR: API returned zERROR: Request failed: )r   BASE_URLr   r   intheadersr   r   r   status_codeprintr   	Exception)r   r   r   urlrespes         r   _requestzHistoricalFetcher._request   sA   >F<<x
8*%	<<##C#CD!$,,"2"23Da"HIDO#&t||'7'78NPQ'R#SD !#3&.03&3&5doo5FaHI3&,T-=-=,>?@99; 	+A3/0	s0   B!D2 D2 (D2 :'D2 "D2 2	E;EEc                     | j                   4| j                   t        k  r!t        d| j                    dt         d       yy)z+Check if we have enough credits to continuez
*** CREDIT SAFETY STOP: z remaining (margin: z) ***FT)r   CREDIT_SAFETY_MARGINr   )r   s    r   check_budgetzHistoricalFetcher.check_budget   sK    +0D0DG[0[.t/C/C.D E235: ;r   	sport_keydate_isomarketsc                 j    |t         }|ddj                  |      dd}| j                  d| d|      S )zGet historical sport-level oddsus,americandateregionsr   
oddsFormat/historical/sports//odds)CORE_MARKETSjoinr   )r   r   r   r   r   s        r   get_historical_oddsz%HistoricalFetcher.get_historical_odds   sG     ?"Gxx($	
 }}29+UCVLLr   c                 6    d|i}| j                  d| d|      S )z%Get historical events list (1 credit)r   r   z/events)r   )r   r   r   r   s       r   get_historical_eventsz'HistoricalFetcher.get_historical_events   s'    (#}}29+WEvNNr   event_idc                 `    |ddj                  |      dd}| j                  d| d| d|      S )z+Get historical event-level odds (for props)r   r   r   r   r   z/events/r   )r   r   )r   r   r   r   r   r   s         r   get_historical_event_oddsz+HistoricalFetcher.get_historical_event_odds   sG     xx($	
 }}!)HXJeDf
 	
r   )N)__name__
__module____qualname__r   r   dictr   r   boolr   r   r   r   r   r   r   r   r   r      s      d htn @d  26MS MC M%)#YM:B4.MOs Oc Ohtn O

3 
# 
-0
;?9
IQRV
r   r   c                    | j                         }|j                  d       |j                  d       dD ]Y  \  }}|j                  d|f       |j                         r*	 |r|j                  d| d|        n|j                  d| d       [ | j	                          |j                          y#  | j                          Y xY w)	zEnsure required tables exist.

    PeriodOdds, GameOddsAltLine, GameOdds already exist with their own schemas.
    Only create PropSnapshot if needed.
    a  
        CREATE TABLE IF NOT EXISTS "PropSnapshot" (
            id SERIAL PRIMARY KEY,
            league VARCHAR(20),
            "theoddsapiEventId" VARCHAR(100),
            "gameDate" TIMESTAMP,
            "homeTeam" VARCHAR(100),
            "awayTeam" VARCHAR(100),
            bookmaker VARCHAR(50),
            "marketType" VARCHAR(50),
            "playerName" VARCHAR(100),
            "propLine" DOUBLE PRECISION,
            "overPrice" INTEGER,
            "underPrice" INTEGER,
            "capturedAt" TIMESTAMP DEFAULT NOW(),
            source VARCHAR(50) DEFAULT 'theoddsapi',
            "createdAt" TIMESTAMP DEFAULT NOW()
        )
    z
        CREATE INDEX IF NOT EXISTS idx_prop_snapshot_event
        ON "PropSnapshot"(league, "theoddsapiEventId", "playerName", "capturedAt")
    ))	bookmakerN)sourcez'theoddsapi'z
            SELECT column_name FROM information_schema.columns
            WHERE table_name = 'PeriodOdds' AND column_name = %s
        z$ALTER TABLE "PeriodOdds" ADD COLUMN z TEXT DEFAULT z TEXTN)cursorexecutefetchonerollbackcommitclose)conncurcolr   s       r   ensure_tablesr      s     ++-C KK  	& KK  	 J  W  V	 ||~ KK"Fse>ZaYb cdKK"Fse5 QR  	KKMIIK	 s    /B22Ceventc                    g }g }g }g }g d}| j                  dg       D ]  }|j                  dd      }||v rdnd}|j                  dg       D ]  }	|	j                  d      }
|
dk(  rf|	j                  d	g       D ]P  }|j                  d
      | j                  d      k(  s'|j                  d      9|j                  |d   g|z         R |
dk(  rW|	j                  d	g       D ]A  }|j                  d
      dk(  s|j                  d      *|j                  |d   g|z         C |
dk(  s|	j                  d	g       D ]  }|j                  d
      | j                  d      k(  r*|j                  d      |j                  |d   g|z         P|j                  d
      | j                  d      k(  st|j                  d      |j                  |d   g|z            i }|r)t        t        |      t	        |      z  dz        dz  |d<   |r)t        t        |      t	        |      z  dz        dz  |d<   |r#t        t        |      t	        |      z        |d<   |r#t        t        |      t	        |      z        |d<   |S )zFExtract consensus line from bookmakers (weighted average of top books))fanduel
draftkingsbetmgmcaesars
bookmakerskeyr   r   r|   r   r5   outcomesname	home_teampointr6   Overr4   price	away_teamspread_hometotalml_homeml_away)r   extendroundsumlenr   )r   r5   r6   r  r  preferred_booksbookbook_keyweightmarket
market_keyoresults                r   extract_consensus_liner  )  sk   GFGGDO		,+ >88E2&/1qhhy"- 	>FE*JY&J3 >AuuV}		+(>>155>C]'
|f'<=> x'J3 =AuuV}.155>3MqzlV&;<= u$J3 >AuuV}		+(>>155>C]'
|f'<=v%))K*@@QUU7^E_'
|f'<=	>	>	>* F %c'lS\&AA&E F J}Fc&k 9A =>BwGs7| ;<yGs7| ;<yMr   	from_dateto_datec                     t        j                  | d      }t        j                  |d      }g }|}||k  r5|j                  |j                  d             |t	        d      z  }||k  r5|S )zGenerate list of date strings%Y-%m-%dr|   )days)r   strptimeappendstrftimer   )r  r  startenddatescurrents         r   generate_datesr  R  sm    i4E


GZ
0CEG
S.W%%j129!$$ S. Lr   fetcherleaguesdry_runc                 H   t        dd        t        d       t        d| d|        t        ddj                  |              t        d        t               }|j                         }d}d}	t	        ||      }
|D ]  }i t
        t        j                  |      }|s$t        d	|j                          d
       |
D ]  }t        d||      }|j                  |      dk(  r&| j                         st        |       |c c S | d}| d}d|fd|ffD ]  \  }}| j                  ||      }t        j                  d       |r|j                  d      sB|j                  d|      }|d   D ]  }|j                  dd      }|j                  dd      }t        ||      }t        ||      }|j                  d      }|j                  d      }d}|r&	 t!        j"                  |j%                  dd            }t'        |      }|s|rAt        d| d| d| d| d|j                  d        d!|j                  d"              |dz  }|dk(  r)g }g }|j                  d       K|j)                  d#       |j)                  |d           |j)                  d$       |j)                  |d            |j                  d"      %|j)                  d%       |j)                  |d"          |j                  d&      %|j)                  d'       |j)                  |d&          |j                  d(      %|j)                  d)       |j)                  |d(          |rJ|j)                  d*       |j)                  |       n&g }g }|j                  d       K|j)                  d+       |j)                  |d           |j)                  d,       |j)                  |d            |j                  d"      %|j)                  d-       |j)                  |d"          |j                  d&      %|j)                  d.       |j)                  |d&          |j                  d(      %|j)                  d/       |j)                  |d(          |r"|j)                  d0       |j)                  |       |r|r|j%                  ddd1      } |j%                  d2d3d31      }!|j+                  |||| |!g       |j-                  d4dj                  |       d5|       |j.                  dkD  r||j.                  z  }|j                  d6g       D ]  }"|"j                  d7d8      }#dx}$x}%x}&x}'x}(})dx}*x}+},|"j                  d9g       D ]X  }-|-j                  d7      }.|-j                  d:g       D ].  }/|.d;k(  rM|/j                  d<      |k(  r|/j                  d=      }$/|/j                  d<      |k(  sD|/j                  d=      }%V|.d>k(  ro|/j                  d<      |k(  r#|/j                  d?      }&|/j                  d=      }(|/j                  d<      |k(  s|/j                  d?      }'|/j                  d=      })|.d@k(  s|/j                  d<      dAk(  r$|/j                  d?      }*|/j                  d=      }+|/j                  d<      dBk(  s|/j                  d=      },1 [ |j-                  dC|||||||#|||$|%|&|'|(|)|*|+|,f       |	dz  }	   |s|j1                          d||<   |
j3                  |      dDz  dk(  ss|st        |       t        dE|j                          d| d| dF|	 dG| j4                   
         |st        |       t        dH| dF|	 dI       |S #  Y xY w)Jz;Backfill core markets (h2h, spreads, totals) for all sports
<============================================================zTIER 1: CORE MARKETS BACKFILLDate range:  to 	Leagues: , r   
---  ---r|   donez
T06:00:00Zz
T23:00:00Zopeningclosingdata	timestampr   r   r  idcommence_timeNZ+00:00z  [DRY]  : z @ z
 | spread=r  z total=r  z7"openingSpreadHome" = COALESCE("openingSpreadHome", %s)z7"openingSpreadAway" = COALESCE("openingSpreadAway", %s)z-"openingTotal" = COALESCE("openingTotal", %s)r  z="openingMoneylineHome" = COALESCE("openingMoneylineHome", %s)r  z="openingMoneylineAway" = COALESCE("openingMoneylineAway", %s)z7"openingCapturedAt" = COALESCE("openingCapturedAt", %s)z7"closingSpreadHome" = COALESCE("closingSpreadHome", %s)z7"closingSpreadAway" = COALESCE("closingSpreadAway", %s)z-"closingTotal" = COALESCE("closingTotal", %s)z="closingMoneylineHome" = COALESCE("closingMoneylineHome", %s)z="closingMoneylineAway" = COALESCE("closingMoneylineAway", %s)z7"closingCapturedAt" = COALESCE("closingCapturedAt", %s))hourminutesecond   ;   zY
                                UPDATE "SportsGame"
                                SET z, "updatedAt" = NOW()
                                WHERE league = %s AND "homeTeam" = %s AND "awayTeam" = %s
                                  AND "gameDate" BETWEEN %s AND %s
                            r   r   unknownr   r   r4   r   r   r5   r   r6   r   Underao  
                            INSERT INTO "OddsSnapshot" (
                                league, "theoddsapiEventId", "externalGameId", "gameDate",
                                "homeTeam", "awayTeam", bookmaker, "snapshotType", "snapshotAt",
                                "moneylineHome", "moneylineAway",
                                "spreadHome", "spreadAway", "spreadHomeOdds", "spreadAwayOdds",
                                total, "totalOverOdds", "totalUnderOdds", source
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi_backfill'
                            )
                            ON CONFLICT (league, "externalGameId", "snapshotAt", source, bookmaker)
                            DO NOTHING
                             z games updated, z snapshots, quota=z
Tier 1 complete: z
 snapshots)r   r   r   r   r  CORE_SPORTSEXTENDED_SPORTSr   upperr   r   r   r   timesleepr
   r   fromisoformatreplacer  r  r  r   rowcountr   indexr   )0r   r   r!  r  r  r"  r   r   total_updatedtotal_snapshotsr  r   r   r   pkey
opening_ts
closing_tssnapshot_typetsr/  r0  r   home_rawaway_rawhomeawayr   commencecommence_dt	consensusupdatesvalues	day_startday_endr  	book_nameml_hml_asp_hsp_asp_h_osp_a_ototover_ounder_or  mkr  s0                                                   r   tier1_core_marketsrf  b  s    
Bvh-	)+	L4y
12	Idii()
*+	VHH
++-CMO9g.E iW6{6o6::6B	v||~&d+, b	WH#Avx8D||D!V+'')h'$$ %:Z0J$:Z0J'0*&=	:?V%W J-!r229bA

1488F#3 HH["5	!&\ A-E$yyb9H$yyb9H,Xv>D,Xv>D$yyH$yy9H"&K!*2*@*@AQAQRUW_A`*aK !7u =I$ 
!M?"TF#dV T((1m(D'EWY]][bMcLdf g%*  %	1"$!#$==7C#NN+de"MM)M*BC#NN+de"MM9]+C*CD$==1=#NN+Z["MM)G*<=$==3?#NN+jk"MM)I*>?$==3?#NN+jk"MM)I*>?"#NN+de"MM)4"$!#$==7C#NN+de"MM)M*BC#NN+de"MM9]+C*CD$==1=#NN+Z["MM)G*<=$==3?#NN+jk"MM)I*>?$==3?#NN+jk"MM)I*>?"#NN+de"MM)4&(3(;(;1UV(;(WI&1&9&9r"UW&9&XG"MM64y'*RSKK -%%)YYw%7$8 9) 
 "()  #||a/ - = !&		, ; .-$(HHUI$>	FJJJtJdJTJFV1555fw&*hhy"&= AF!'E!2B%+ZZ
B%? A#%;'(uuV}'@/0uuW~)*v()B/0uuW~%'9_'(uuV}'@/0uuW~12w)*v()B/0uuW~12w%'8^'(uuV}'>./eeGn12w)*v')A23%%.%AA,  % #Hh $	=) $dFF	& (1,].-gA-J-X !'{{8$q(A-!(+6<<>*!H:RFV());G<S<S;TV WCb	WiWV h	.>>Oz
Z[I! s   %^^!c                    t        dd        t        d       t        d| d|        t        d        t               }|j                         }d}t        ||      }	|D ]  }
t        j                  |
      }t        j                  |
      }|r|s3t        d|
j                          dt        |       d	       |	D ]\  }t        d
|
|      }|j                  |      dk(  r&| j                         st        |       |c c S | d}| j                  ||      }t        j                  d       |r|j                  d      sd||<   |d   }t        d|
j                          d| dt        |       d       |D ]B  }|j                  d      }t        |j                  dd      |
      }t        |j                  dd      |
      }|j                  d      }d}|r&	 t!        j"                  |j%                  dd            }| j                         st        |       |c c c S t'        dt        |      d      D ]~  }|||dz    }|}|r |t)        d
      z
  }|j+                  d      }| j-                  ||||      }t        j                  d       |r|j                  d      sm|d   }|j                  dg       D ]  }|j                  dd       }|j                  d!g       D ]  } | j                  dd      }!| j                  d"g       D ]  }"|"j                  d#|"j                  d$d            }#|"j                  d%      }$|"j                  d&      }%|"j                  d$d      j/                         }&|$j|&d'k(  r|%nd}'|&d(k(  r|%nd}(|r|dz  }|j1                  d)|
||||||!|#|$|'|(|f       |dz  }    E |s|j3                          d||<   |	j5                  |      d*z  dk(  s5|st        |       t        d+| d,| j6                          _  |st        |       t        d-| d.       |S #  Y ?xY w)/z Backfill historical player propsr$  r%  z!TIER 2: HISTORICAL PROPS BACKFILLr&  r'  r   r*   (z prop markets) ---r   r,  
T18:00:00Zr|   r/  r?  r5  r6  z eventsr1  r   r   r  r2  Nr3  r4     hours%Y-%m-%dT%H:%M:%SZr   r   r<  r   r   descriptionr   r   r   overunderaV  
                                    INSERT INTO "PropSnapshot" (
                                        league, "theoddsapiEventId", "gameDate",
                                        "homeTeam", "awayTeam", bookmaker, "marketType",
                                        "playerName", "propLine", "overPrice", "underPrice",
                                        "capturedAt", source
                                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi_backfill')
                                    ON CONFLICT DO NOTHING
                                   z    Props so far: z, quota=z
Tier 2 complete: z prop records)r   r   r   r  r@  r   PROP_MARKETSrB  r	  r   r   r   r   rC  rD  r
   r   rE  rF  ranger   r  r   lowerr   r   rH  r   ))r   r   r!  r  r  r"  r   r   total_propsr  r   r   propsr   rK  	events_tsevents_dataeventsr   r   rR  rS  rT  rU  batch_startbatchprop_tsprop_dt
event_odds
event_datar  r[  r  r  outcomeplayer_name	prop_liner   outcome_type
over_priceunder_prices)                                            r   tier2_historical_propsr  &  sx    
Bvh-	-/	L4y
12	VHH
++-CK9g.E n[OOF+	  (v||~&bU4FGH f	[H#Avx8D||D!V+'')h'"" $*J/I!77	9MKJJqMkoof&=!' (FBv||~&azCK=HI H1 99T?(;)CVL(;)CVL 99_5"&.&<&<X=M=McS[=\&] ++-!(+&& $)CJ#: 61K!+kAo>E (G""-	0B"B")"2"23G"H!(!B!B!8We"J JJqM%Z^^F-C !+F!3J *|R @ $1$(HHUI$>	&*hhy"&= !1F)/E2)>J+1::j"+E 1.5kk-U[]_I`.a,3KK,@	(/G(</6{{62/F/L/L/N#,#4$,6Bf6LURV
7Cw7NeTX#*$/1$4K$, # -$ %+Hk$($	:$/J$+	&"!# !,q 0=1!1$1%61%H1T !'{{8$q(A-!(+*;-x@W@W?XYZMf	[n[` h	}M
:;Ys   !%Q  Q	c                    t        dd        t        d       t        d| d|        t        d        t               }|j                         }d}t        ||      }	|D ]  }
t        j                  |
      }t        j                  |
      }|r|s3t        d|
j                          dt        |       d	       |	D ]!  }t        d
|
|      }|j                  |      dk(  r&| j                         st        |       |c c S t        dt        |      d      D ]  }|||dz    }| d}| j                  |||      }t        j                  d       |r|j                  d      sN|d   D ],  }|j                  d      }t!        |j                  dd      |
      }t!        |j                  dd      |
      }|j                  d      }d}|r&	 t#        j$                  |j'                  dd            }|j                  dg       D ]  }|j                  dd      }|j                  dg       D ]d  }|j                  dd      }d|v r|j)                  dd      }|d   |d   }}n<|j+                  dd      }t        |      dk(  r|d   n|}t        |      dk(  r|d   nd }dx} x}!x}"x}#}$|j                  d!g       D ]  }%|%j                  d"d      }&|%j                  d#      }'|%j                  d$      }(|(:|d%v r|&|j                  d      k(  sS|'} |'|' nd}!]|d&v r	|&d'k(  sg|'}"j|d(v so|&|j                  d      k(  r|(}#|&|j                  d      k(  s|(}$ | |"|#|$8|r|dz  }A|j-                  d)|
|||||| |!|"|#|$||f       |dz  }g  /  |s|j/                          d||<   |	j1                  |      d*z  dk(  s|rt        |       $  |st        |       t        d+| d,       |S #  Y xY w)-z"Backfill historical period marketsr$  r%  z*TIER 3: HISTORICAL PERIOD MARKETS BACKFILLr&  r'  r   r*  rh  z period markets) ---rq  r,  rj  z
T20:00:00Z)r   r|   r/  r1  r   r   r  r2  Nr3  r4  r   r   r<  r   _1st__r   fullr   r   r   r   )r5   spread)r6   r  r   )r4   	moneylinea<  
                                INSERT INTO "PeriodOdds" (
                                    league, "gameId", "gameDate",
                                    "homeTeam", "awayTeam", period,
                                    "spreadHome", "spreadAway", total,
                                    "moneylineHome", "moneylineAway",
                                    "snapshotType", "snapshotAt", bookmaker, source
                                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'historical', %s, %s, 'theoddsapi_backfill')
                                ON CONFLICT ("league", "gameId", "period", "snapshotType")
                                DO UPDATE SET
                                    "spreadHome" = COALESCE(EXCLUDED."spreadHome", "PeriodOdds"."spreadHome"),
                                    "spreadAway" = COALESCE(EXCLUDED."spreadAway", "PeriodOdds"."spreadAway"),
                                    total = COALESCE(EXCLUDED.total, "PeriodOdds".total),
                                    "moneylineHome" = COALESCE(EXCLUDED."moneylineHome", "PeriodOdds"."moneylineHome"),
                                    "moneylineAway" = COALESCE(EXCLUDED."moneylineAway", "PeriodOdds"."moneylineAway"),
                                    "snapshotAt" = EXCLUDED."snapshotAt"
                            r>  z
Tier 3 complete: z period records)r   r   r   r  r@  r   PERIOD_MARKETSrB  r	  r   r   r   rs  r   rC  rD  r
   r   rE  rF  r   rsplitr   r   rH  ))r   r   r!  r  r  r"  r   r   r  r  r   r   period_mktsr   rK  rz  r{  rO  r/  r   r   rR  rS  rT  rU  r  r[  r  r  partsm_typeperiodsp_homesp_awaytot_valr  r  r  r   
line_valuer   s)                                            r   tier3_historical_periodsr    sy    
Bvh-	68	L4y
12	VHH
++-CE9g.E w,OOF+	$((0v||~&b[)9(::NOP o	,H#Avx8D||D!V+'')h'  %QK(8!< ]'#Ka@ z,229b%2P

1488F#3!&\ S'E$yyH,UYY{B-GPD,UYY{B-GPD$yy9H"&K!*2*@*@AQAQRUW_A`*aK !&		, ; F'$(HHUI$>	&*hhy"&= C'F)/E2)>J  '*4(2(8(8a(@16q58(2(9(9#q(A58Z1_q*58Z1_q& OSRGRgRR'G%+ZZ
B%? 8'(uuVR'8-.UU7^
()g#(=$,#)-B#B'+uyy/E'E2<AKAW:+]a%+/B%B'+v~2<%+/C%C'+uyy/E'E27)-;1G)G27'8*  '7?w[b[j (& %
 (  KK ) " !'+ $dF '' ' "I"!. "QJEGC'F'S']'~ !'{{8$q(A-!(+_o	,w,r h	wo
67Lm! s   %PP
c                    t        dd        t        d       t        d| d|        t        d        t               }|j                         }d}t        ||      }	ddg}
|D ]  }t        j                  |      }|st        d	|j                          d
       |	D ]  }t        d||      }|j                  |      dk(  r&| j                         st        |       |c c S | d}| j                  ||      }t        j                  d       |r|j                  d      sd||<   |d   }|D ]  }|j                  d      }t        |j                  dd      |      }t        |j                  dd      |      }|j                  d      }d}|r&	 t        j                  |j!                  dd            }| j                         st        |       |c c c S |}|r |t#        d      z
  }|j%                  d      }| j'                  ||||
      }t        j                  d       |r|j                  d      s|d   }|j                  dg       D ]k  }|j                  dd      }|j                  dg       D ]@  }|j                  dd      }d|v rd nd!} |j                  d"g       D ]  }!|!j                  d#      }"|!j                  d$      }#|!j                  d%d      }$|"|#=| d!k(  r|$d&k(  r|#nd}%|$d'k(  r|#nd}&n0|$|j                  d      k(  r|#nd}%|$|j                  d      k(  r|#nd}&|r|dz  }|$|j                  d      |j                  d      fv r|$n|$}'|$|j                  d      k(  r|j                  d      n|j                  d      }(|j)                  d(||||||'|(| ||"|#|f       |dz  } C n  |s|j+                          d||<   |	j-                  |      d)z  dk(  sY|st        |       t        d*|j                          d+| d,| d-| j.                            |st        |       t        d.| d/       |S #  Y xY w)0z0Backfill historical alternate spreads and totalsr$  r%  z%TIER 4: HISTORICAL ALT LINES BACKFILLr&  r'  r   alternate_spreadsalternate_totalsr*  r+     r,  ri  r|   r/  r1  r   r   r  r2  Nr3  r4  r   rk  rm  r   r   r<  r   r5   
alt_spread	alt_totalr   r   r   r   r   r=  a  
                                INSERT INTO "GameOddsAltLine" (
                                    league, "gameId", "gameDate",
                                    "homeTeam", "awayTeam", team, opponent,
                                    market, bookmaker, "lineValue",
                                    "oddsAmerican", "fetchedAt", source
                                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'theoddsapi_backfill')
                                ON CONFLICT ("league", "gameId", "bookmaker", "market", "team", "lineValue")
                                DO UPDATE SET "oddsAmerican" = EXCLUDED."oddsAmerican", "fetchedAt" = EXCLUDED."fetchedAt"
                            r>  r?  r5  r6  z alt line records, quota=z
Tier 4 complete: z alt line records)r   r   r   r  r@  r   rB  r   r   r   r   rC  rD  r
   r   rE  rF  r   r  r   r   r   rH  r   ))r   r   r!  r  r  r"  r   r   r  r  alt_marketsr   r   r   rK  rw  rx  ry  r   r   rR  rS  rT  rU  alt_tsalt_dtr~  r  r  r[  r  r  alt_typer  r  r   r   	over_odds
under_odds
team_label	opp_labels)                                            r   tier4_historical_alt_linesr  =  s    
Bvh-	13	L4y
12	VHH
++-CE9g.E&(:;K r:OOF+	v||~&d+, k	:H#Avx8D||D!V+'')h' $*J/I!77	9MKJJqMkoof&=!' (F M' 99T?(;)CVL(;)CVL 99_5"&.&<&<X=M=McS[=\&] ++-!(+ L #(91+==F#__-ABF$>>x
 

1!)?'/
&NN<< -'D $	 :I"&((9b"9 *'%+ZZr%:
3<
3J<P[!'J!; &'A)*wJ$%EE'NE#$55#4D)1U] (';659V^E	6:goU4
59UYY{=S5SEY]	6:eii>T6TUZ^
& %
 ( 26%))K:PRWR[R[\gRh9i1iosJBF%))T_J`B`		+(>fkfofop{f|IKK 	)  !'+ $dJ	 ()Z %v	" "QJEM&'	*'-'AM'^ !'{{8$q(A-!(+6<<>*!H:Rw ?&6679 :Uk	:r:h h	w&7
89Les   %QQ	c                     t        dd        t        d       t        d| d|        t        d        t        t        j                               }t	        | |||||      S )zGBackfill core markets for extended sports (soccer, MMA, AFL, NRL, WNBA)r$  r%  z TIER 5: EXTENDED SPORTS BACKFILLr&  r'  )r   listrA  keysrf  )r   r   r  r  r"  extended_leaguess         r   tier5_extended_sportsr    sg     
Bvh-	,.	L4y
12	VH O0023gt-=y'SZ[[r   c                  \   t               } | st        d       yi }| j                         D ]K  \  }}|j                  d      d   }||vrddd||<   |dk(  r||   dxx   dz  cc<   <||   dxx   dz  cc<   M t        d	d
        t        d       t        d
        t	        |j                               D ]>  \  }}|d   |d   z   }|dkD  r|d   |z  dz  nd}t        d| d|d    d| d|dd	       @ t        dt        |               t        dt                t        d
        y)zShow backfill progress statuszNo backfill progress found.Nr   r   )r,  pendingr,  r|   r  r$  r%  zBACKFILL PROGRESSd   r?  r6  /rh  z.1fz%) completez
Total entries: zProgress file: )r   r   itemsr   sortedr	  r   )r   tier_countsr   statusr   countsr  pcts           r   show_statusr    sa   H+, K~~' .Vyy~a {")*q 9KVf%*%i(A-(. 
Bvh-	
	VH{0023 Kfv	!22.3aifVnu$s*Q4&6&>*!E7"SI[IJK 
c(m_
-.	OM?
+,	VHr   c                  ^   t        j                  d      } | j                  dt        dd       | j                  ddt        d	d
       | j                  ddt        dd       | j                  dt        dd       | j                  dt        dd       | j                  ddd       | j                  ddd       | j                  ddd       | j                         }|j                  rt                y|j                  rst               }d|j                   d }d}|D cg c]  }|j                  |      s| }}|D ]
  }||= |d!z  } t        |       t        d"| d#|j                          yt               \  }}|st        d$       y!|st        d%       y!t        |      }	t!        j"                  |      }
t%        |
       |j&                  j)                  d&      D cg c]  }|j+                          }}|j                  d'k7  r|j                  j)                  d&      ng d(}d }t,        rY|j.                  sM	 t1        |
d)d*d+t3        j4                  |j6                  d,      t3        j4                  |j8                  d,            }d}t        d.d/        t        d0       t        d1d2j=                  |              t        d3|j6                   d4|j8                          t        d5d2j=                  |              t        d6|j.                          t        d/        |	j?                  d7       t        d8|	j@                   d9|	jB                   d:       	 |D ]&  }|dk(  r2|tE        |	|
||j6                  |j8                  |j.                        z  }n|d;k(  r2|tG        |	|
||j6                  |j8                  |j.                        z  }n|d<k(  r2|tI        |	|
||j6                  |j8                  |j.                        z  }nl|d=k(  r2|tK        |	|
||j6                  |j8                  |j.                        z  }n5|d>k(  r0|tM        |	|
|j6                  |j8                  |j.                        z  }|	jO                         r' n t,        r7|r5|j.                  s)	 tQ        |
||||j6                  |j8                  d?d@A       t        d.d/        t        dC       t        dD|        t        dE|	jR                          t        dF|	j@                          t        dG|	jB                          |j.                  rt        dH       t        d/        |
jU                          yc c}w c c}w # t:        $ r}t        d-|        Y d }~	d }~ww xY w# t:        $ r}t        dB|        Y d }~d }~ww xY w# t,        r^|r[|j.                  sN	 tQ        |
||||j6                  |j8                  d?d@A       w # t:        $ r}t        dB|        Y d }~w d }~ww xY ww w w xY w)INzFull Historical Backfill Engine)rn  z--tier1zTier to run: 1|2|3|4|5|all)typer   helpz--fromr  z
2025-12-22zStart date (YYYY-MM-DD))destr  r   r  z--tor  z
2026-02-20zEnd date (YYYY-MM-DD)z	--leaguesznba,nfl,nhl,mlb,ncaab,ncaafzComma-separated leaguesz--batchr   zDays per batchz	--dry-run
store_truezPreview without writing)actionr  z--statuszShow progress statusz--resetzReset progress for a tierr   r   r   r|   zReset z progress entries for tier z&ERROR: THE_ODDS_API_KEY not configuredz)ERROR: SPORTS_DATABASE_URL not configuredr   all)r  2345ztheoddsapi-backfillmultibackfillr  z"Warning: Could not log ingestion: r$  z<############################################################zFULL HISTORICAL BACKFILLzTiers: r)  zRange: r'  r(  z	Dry run: z/sports/zStarting quota: z used, z
 remainingr  r  r  r  )tiersfromto	completed)odds_createdmetadatar  z#Warning: Could not log completion: zBACKFILL SUMMARYz  Total records: z  API requests: z  Quota used: z  Quota remaining: z!  MODE: DRY RUN (no data written))+argparseArgumentParseradd_argumentr   r   
parse_argsr  r  resetr   r   r   r   r   r   r   psycopg2connectr   r!  r   r   UNIFIED_AVAILABLEr"  r   r   r  r  r  r   r   r   r   r   rf  r  r  r  r  r   r   r   r   )parserargsr   prefixremovedkkeys_to_remover   r   r   r   lr!  r  ingestion_idr   r  r   s                     r   mainr    s   $$1RSF
sC9  ;
{l6  8
YS,4  6
#7T6  8
	R>NO
L?XY

<>TU
	,=XYD{{zz ?		{!$%-Ff1E!FF 	AqLG	 	hwi:499+FG!mOFG679:(GF#D$"&,,"4"4S"9:Qqwwy:G:$(II$6DIIOOC <UE L	<.+Wj!!$..*=!!$,,
;L E	Bvh-	$&	GDIIe$%
&'	GDNN#4~
67	Idii()
*+	Idll^
$%	VH Z 	W//08O8O7PPZ
[\A 	Ds{+T7DNNDLL$,,X X/T7DNNDLL$,,X X1T7DNNDLL$,,X X3T7DNNDLL$,,X X.T4>>4<<O O '')%	* dllA&,!&',dnnDLLY&	 
Bvh-		eW
%&	W223
45	N7--.
/0	 7 78
9:||13	VHJJL} G* ;  	<6qc:;;	<^  A;A3?@@A dllA&,!&',dnnDLLY&	  A;A3?@@A 7Cs   %U3<U3)U8AU= 'D'W W ((V" =	VVV"	W+V>>WX,(XX,	X&X!X,!X&&X,__main__)F)=__doc__r  r   r  psycopg2.extrasr   r   sysrC  r   r   r   typingr   r   r   r	   r   insertr   abspath__file__
team_namesr
   lib.unified_odds_ingestionr   r   r   r   r   r  ImportErrorr   r   r   r@  rA  r   rr  r  r   r   r   r   r   r   r   r   r   r  r  r   rf  r  r  r  r  r  r  r   exitr   r   r   <module>r     s,  (      	 
  2 2 . . 277??277??8#<= > ( 277??8, - 
  -G  !% -$$')&# , DF-6
(U38_ &t 6D 6-3 - -s -s -M
 M
`1h&$ &4 &R	c 	C 	DI 	" FK} 1 }$s) }"%}03}>B}J JO@$5 @d3i @&)@47@BF@P MRI&7 IS	 I),I7:IEIIb OTF(9 F$s) F+.F9<FGKF\ IN
\#4 
\%(
\36
\AE
\"<wt zL O"  s   G GG