i4dZddlZddlZddlZddlZddlZddlZddlZddlmZm Z m Z ddl m Z m Z mZmZej j#dej j%ej j'eddlmZej j#dej j%e ddlmZmZmZmZdZd Zd d d ddddddddddd Z gdgddgdgdddgdidgd id!Z!gd"Z"gd!Z#gd#Z$gd$Z%d%ee&e&ffd&Z'Gd'd(Z(d)Z)d*e&d%ee&e&ffd+Z* d5d,e(d-ee&d.e+fd/Z, d5d,e(d-ee&d.e+fd0Z- d6d,e(d-ee&d.e+fd1Z. d5d,e(d-ee&d.e+fd2Z/d3Z0e1d4k(re2e0yy#e$r dZed YwxYw)7a> The Odds API Extended Markets Fetcher Handles sport-level market fetches that don't fit the per-event prop model: - Period markets (quarters, halves, periods) - Team totals and alternate team totals - Soccer specials (BTTS, draw no bet, double chance, 3-way) - Alternate totals Usage: python theoddsapi_extended_markets.py --periods # Period markets (Q1-Q4, H1-H2, P1-P3) python theoddsapi_extended_markets.py --team-totals # Team totals & alt team totals python theoddsapi_extended_markets.py --soccer # Soccer specials (BTTS, DNB, DC, 3-way) python theoddsapi_extended_markets.py --alt-totals # Alternate totals python theoddsapi_extended_markets.py --all # All of the above python theoddsapi_extended_markets.py --dry-run --all # Preview without writing N)datetimetimezone timedelta)OptionalDictListTuple)normalize_to_full)log_ingestion_startlog_ingestion_completeupsert_bookmaker_oddsget_or_create_sports_gameTFz-Warning: unified_odds_ingestion not availablezhttps://api.the-odds-api.com/v4basketball_nbaamericanfootball_nfl icehockey_nhl baseball_mlbbasketball_ncaabamericanfootball_ncaaf soccer_eplsoccer_germany_bundesligasoccer_italy_serie_asoccer_spain_la_ligasoccer_france_ligue_onesoccer_uefa_champions_leaguesoccer_usa_mls) nbanflnhlmlbncaabncaafepl bundesligaseriealaligaligue1uclmls) h2h_q1 spreads_q1 totals_q1h2h_q2 spreads_q2 totals_q2h2h_q3 spreads_q3 totals_q3h2h_q4 spreads_q4 totals_q4)h2h_h1 spreads_h1 totals_h1h2h_h2 spreads_h2 totals_h2)quartershalves)r)r*r+periods) h2h_p1 spreads_p1 totals_p1h2h_p2 spreads_p2 totals_p2h2h_p3 spreads_p3 totals_p3innings) h2h_1st_1_inningsspreads_1st_1_inningstotals_1st_1_inningsh2h_1st_3_inningsspreads_1st_3_inningstotals_1st_3_inningsh2h_1st_5_inningsspreads_1st_5_inningstotals_1st_5_inningsh2h_1st_7_inningsspreads_1st_7_inningstotals_1st_7_innings)rrrr)btts draw_no_bet double_chance h2h_3_way)rrrrr r!)r"r#r$r%r&r'r(returncnd}d}d} t|d5}|D]}|jdrE|jdddjjdjdd }Y|jd sk|jdddjjd} ddd|xs2t j jd d jdd }|xst j jd }||fS#1swYfxYw#t$rYtwxYw)z'Load database URL and API key from .envz/var/www/html/eventheodds/.envNrzSPORTS_DATABASE_URL=="?rzTHE_ODDS_API_KEY=SPORTS_DATABASE_URLTHE_ODDS_API_KEY)open startswithsplitstripFileNotFoundErrorosenvironget)env_pathdb_urlapi_keyflines &scripts/theoddsapi_extended_markets.py load_configrpcs*/H FG (C  GA G??#9:!ZZQ/288:@@EKKCPQRSF__%89"jja0399;AA#FG  G G Nrzz~~&;R@FFsKANF;(:;G 7? G G    s/ D(A-D4D7D(D%!D(( D43D4c eZdZddededefdZddededeefd Z dd ed e ed ede efd Z dd eded e ed edeef dZ y)ExtendedMarketsFetcherrlrkdry_runc|||_||_||_tj|_d|_d|_y)Nr)rlrkrsrequestsSessionsession quota_usedquota_remaining)selfrlrkrss ro__init__zExtendedMarketsFetcher.__init__zs6   '') #NendpointparamsrXcl|i}|j|d<t|} |jj||d}t |j jdd|_t |j jdd|_|jdk(r td y|jd k(ry|jd k(r td y|jd k7rtd|jd|y|jS#t$r}td|Yd}~yd}~wwxYw)z Make API request and track quotaNapiKey)r~timeoutzx-requests-usedrzx-requests-remainingizERROR: Invalid API keyiizERROR: Quota exceededzERROR: API returned z for zERROR: Request failed: ) rlBASE_URLrwriintheadersrxry status_codeprintjson Exception)rzr}r~urlrespes ro_requestzExtendedMarketsFetcher._requests( >F<<x 8*% <<##C#CD!$,,"2"23Da"HIDO#&t||'7'78NPQ'R#SD 3&.03&3&-/3&,T-=-=,>eH:NO99;   +A3/ 0 s0B D,D<D*DD D3D..D3 sport_keymarketsregionscd|dj|dd}|jd|d|}|xsgS)zGet odds for specific markets,americanrr oddsFormat/sports//oddsjoinr)rzrrrr~results roget_odds_with_marketsz,ExtendedMarketsFetcher.get_odds_with_marketssBxx($  )E:FC|r|event_idc^|dj|dd}|jd|d|d|S)z)Get event-level odds for specific marketsrrrrz/events/rr)rzrrrrr~s roget_event_oddsz%ExtendedMarketsFetcher.get_event_oddss?xx($  }}x {(8*EJFSSr|F)N)us) __name__ __module__ __qualname__strboolr{dictrrrrrr|rorrrrys$$S$4$dhtn@/3 s T#Y (+ 7;Dz ;?TTsT $S T47TCKD>Tr|rrc|j}|jd|jd|jd|js |jd|jd|js |jd|j |j y#|jYhxYw#|jYLxYw)zEnsure required tables exist. Existing tables (PeriodOdds, GameOddsAltLine, GameOdds) already have schemas. Only create PropSnapshot if it doesn't exist yet. 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") z SELECT column_name FROM information_schema.columns WHERE table_name = 'PeriodOdds' AND column_name = 'bookmaker' z2ALTER TABLE "PeriodOdds" ADD COLUMN bookmaker TEXTz SELECT column_name FROM information_schema.columns WHERE table_name = 'PeriodOdds' AND column_name = 'source' zDALTER TABLE "PeriodOdds" ADD COLUMN source TEXT DEFAULT 'theoddsapi'N)cursorexecutefetchonerollbackcommitclose)conncurs ro ensure_tablesrs ++-CKK &KK KK  <<>  KKL M KK  <<>  KK` a KKMIIK  MMO  MMOsB:C:CC% market_keycd|vr|jdd}|d|dfS|jdd}t|dk(r |d|dfS|dfS)zParse a period market key like 'h2h_q1' into (market_type, period). Returns: (market_type, period) e.g. ('h2h', 'q1') _1st__r\rfull)rdrsplitlen)rpartss roparse_period_market_keyrsl *  a(Qxq!!   c1 %E 5zQQxq!! v r|fetcherleaguesrsc|j}d}tddtdtdtjtj j td|D]}tj|}tj|}|r|s3g} |jD]} | j| | s^tdt| dD]} | | | dz} td|jdd j| d d d t| d |j!|| } | s_| D] }|jd}t#|jdd|}t#|jdd|}|jd}d }|r& tj$|j'dd}|jdgD]m}|jdd}|jdgD]B}|jdd}t)|\}}|jdg}d x}x}}d }|D]}|jdd} | |k(s| dk(r5|jd}|jdB|jd}T| |k(s| dk(r|jd}p| dk(sv|jd}|||r)td |d!|d"|d#|d"|d$|d%|d&||d'z }d x}!x}"x}#x}$}%|d(vr |}!|| nd }"n|d)vr|}#n|d*vr|}$|}%|j+d+|||||||!|"|#|$|%|f |d'z }Ep t-j.d'|s|j1|j3td,|td-|j4|S#YxYw).zFFetch period markets (quarters, halves, periods) for specified leaguesr <============================================================zFETCHING PERIOD MARKETSTime:  : Fetching rNz... (z markets)id home_teamr` away_team commence_timeZ+00:00 bookmakerskeyunknownroutcomesnameOverpricepointUnderDraw [DRY]  vs  |  : /z line=r\)spreadsspread)totalstotal)h2h 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, 'live', NOW(), %s, 'theoddsapi') 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" = NOW() z Period markets captured:  Quota remaining: )rrrnowrutc isoformat SPORT_KEYSriPERIOD_MARKETSvaluesextendrangerupperrrr fromisoformatreplacerrtimesleeprrry)&rrrrsrrleaguer period_config all_markets group_markets batch_startbatcheventseventrhomeawaycommence commence_dtbook book_namemarketr market_typeperiodr home_odds away_odds draw_odds line_valueorsp_homesp_awaytot_valml_homeml_aways& rofetch_period_marketsr s2 ++-C E Bvh- #$ F8<< -779: ;< VHeNN6* &**62   *113 .M   } - . !C $4a8V K K!O#=12wJ!%,-EE'N !%,-EE'N  ;%,1B$"!JtfDc&;-WZ%.Kr)Ai[zl#\]!QJE$KONN'NGNg&*??&0G5?5KzkQUG(,??&0G(,@@&/G&/G % #Hk4"GWg#Wi( y<#?#L #\ JJqMmV eN  IIK )% 12  7 78 9: LYs /%N??O c|j}d}tddtdtdtjtj j td|D]}|tvr tj|}|s%ddg}td|jd |j||} | s[| D])} | jd } t| jd d |} t| jd d |} | jd}d}|r& tj|jdd}| jdgD]}|jdd}|jdgD]a}|jdd }d|v}|jdgD]2}|jd|jdd }|jdd j}|jd}|jd}||mt||}|| k(rd}n || k(rd}n|| jd k(rdnd}d|}|rd|}|d k(r|nd}|d!k(r|nd}|r&td"| d#| d$|d%|d$|d&|d'||d(z }|r)|j!d)|| || | ||| k(r| n| ||||xs|f n|j!d*|| || | |||||f |d(z }5d,t#j$d(|s|j'|j)td+|td,|j*|S#Y xYw)-z+Fetch team totals and alternate team totalsrrrzFETCHING TEAM TOTALSr team_totalsalternate_team_totalsrz: Fetching team totalsrrr`rrNrrrrrr alternater descriptionrrrrr team_total_alt_team_total_overunderrrrrz: o=z u=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, NOW(), 'theoddsapi') ON CONFLICT ("league", "gameId", "bookmaker", "market", "team", "lineValue") DO UPDATE SET "oddsAmerican" = EXCLUDED."oddsAmerican", "fetchedAt" = NOW() a INSERT INTO "GameOdds" ( league, "gameId", "gameDate", "homeTeam", "awayTeam", market, bookmaker, "lineValue", "overOdds", "underOdds", "fetchedAt", source ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), 'theoddsapi') ON CONFLICT ("league", "gameId", "bookmaker", "market") DO UPDATE SET "lineValue" = EXCLUDED."lineValue", "overOdds" = EXCLUDED."overOdds", "underOdds" = EXCLUDED."underOdds", "fetchedAt" = NOW() z Team totals captured: r)rrrrrrrTEAM_TOTAL_SPORTSrrirrr rrlowerrrrrrry)rrrrsrrrrrrrrrrrrrrrris_altr team_name outcome_typerrnormalized_teamside market_label over_odds under_oddss rofetch_team_totalsr ~s ++-C E Bvh- ! F8<< -779: ;< VHf * * NN6*   "9: 6<<>""89:..y'B V #EyyH$UYY{B%?HD$UYY{B%?HDyy1HK"*"8"89I9I#x9X"YK ,3I # HHUI6 "hhy"5F#F!'E2!6J(J6F#ZZ B7B#$%EE-vr9J$K '(uuVR'8'>'>'@ %&UU7^ !g%-$+"+chh~.F-GHI..y.I Bv||~&k2 3  6<<>""S[M9:M EyyH$UYY{B%?HD$UYY{B%?HDyy1HK"*"8"89I9I#x9X"YK ,3@  HHUI6 "hhy"5=F!'E2!6J%zz*b9H8<9CT  4&TF#j\!* 4 {#i[I;XY  KK !+tT"Iz!9QJE{=@ M ^ 1 _B  IIK *5' 23  7 78 9: L[s %O  Oc|j}d}tddtdtdtjtj j td|D]}|tvr tj|}|s%ddg}td|jd |j||} | s[| D]} | jd } t| jd d |} t| jd d |} | jd}d}|r& tj|jdd}| jdgD]x}|jdd}|jdgD]M}|jdd }|jdgD]"}|jdd }|jd}|jd}||=d|vr|dk(r|nd}|dk(r|nd}d}n<|| k(s|| jd k(r|nd}|| k(s|| jd k(r|nd}d}|r#td| d | d!|d"|d!|d#| |d$z }|| | | jd | jd fvr|n|dk(rdnd}|| | jd fvr| n| }|jd%|| || | ||||||f |d$z }%P{t!j"d$|s|j%|j'td&|td'|j(|S#YxYw)(zFetch alternate totalsrrrzFETCHING ALTERNATE TOTALSralternate_totalsalternate_spreadsrz: Fetching alternate linesrrr`rrNrrrrrrrrrrrrr alt_total alt_spreadrrrrrr\aw 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, NOW(), 'theoddsapi') ON CONFLICT ("league", "gameId", "bookmaker", "market", "team", "lineValue") DO UPDATE SET "oddsAmerican" = EXCLUDED."oddsAmerican", "fetchedAt" = NOW() z Alt lines captured: r)rrrrrrrALT_TOTAL_SPORTSrrirrr rrrrrrrry)rrrrsrrrrrrrrrrrrrrrrrrrrrr alt_marketteam_name_for_altopp_names rofetch_alt_totalsr5rs ++-C E Bvh- %& F8<< -779: ;< VHL ) ) NN6*  %':; 6<<>""<=>..y'B < #EyyH$UYY{B%?HD$UYY{B%?HDyy1HK"*"8"89I9I#x9X"YK ,3/ # HHUI6 "hhy"5,#F!'E2!6J#ZZ B7)# uuVR0%&UU7^ !g%-$#z115TI26'/tJ)4J15S^I_A_eiI26$,$%))T_J`B`fjJ)5J"!JtfDc*QzlZ]%.Kr%#:;!QJE$59T4S^I_afajajkvaw|jr td/td)|jAycc}w#t0$r} td| Yd} ~ d} ~ wwxYw#t0$r} td | | dz } Yd} ~ d} ~ wwxYw#t0$r} td!| | dz } Yd} ~ d} ~ wwxYw#t0$r} td"| | dz } Yd} ~ d} ~ wwxYw#t0$r} td#| | dz } Yd} ~ d} ~ wwxYw#t0$r} td'| Yd} ~ d} ~ wwxYw#t,rM|rJ|js= t;||| | | dk(rd$nd%&w#t0$r} td'| Yd} ~ wd} ~ wwxYwwwwxYw)0Nz%The Odds API Extended Markets Fetcher)rz --periods store_truezFetch period markets)actionhelpz --team-totalszFetch team totalsz--soccerzFetch soccer specialsz --alt-totalszFetch alt totals & spreadsz--allzFetch all extended marketsz --leaguesznba,nfl,nhl,mlbz2Comma-separated leagues (default: nba,nfl,nhl,mlb))typedefaultr9z --dry-runzPreview without writingr\Tz&ERROR: THE_ODDS_API_KEY not configuredz)ERROR: SPORTS_DATABASE_URL not configured)rsrztheoddsapi-extendedmulti scheduledz(Warning: Could not log ingestion start: rzERROR in period markets: zERROR in team totals: zERROR in soccer specials: zERROR in alt totals: completedcompleted_with_errors) odds_created errors_countstatusz+Warning: Could not log ingestion complete: rrzEXTENDED MARKETS COMPLETEz Total records: z Errors: z Quota used: rz! MODE: DRY RUN (no data written))!argparseArgumentParser add_argumentr parse_argsanyr=rsoccer alt_totalsall print_helprprrrrsrrdrepsycopg2connectrUNIFIED_AVAILABLEr rr r r+r5r rxryr) parserargsrkrlrlrr ingestion_idrrerrorss romainrTs  $ $1X YF  L?UV  CVW  <>UV |B^_  ;WX  #7HQS  L?XY    D  d.. T__dhhW X xxJNN Nt'N$+!mOFG  67  9:$WfdllKG"&,,"4"4S"9:Qqwwy:G:   F #D$L B.t5JGU`aL E F(I << -gtWdllSS    *7D'4<<PP ;; .wgt||TT ?? )'4$,,OO dll I&,!&!'*0A+;;R  Bvh- %' eW %& Jvh   N7--. /0  7 78 9: || 13 VHJJL K; B %M99M> N( N#Q#N((Q+ O4O Q OQ O<O71Q7O<<Q? P&P!Q!P&&Q) Q 2QQ R#$Q<;R#< RRR#RR#__main__r)NF)3__doc__rCrurLrrgsysrrrrtypingrrrr pathinsertdirnameabspath__file__ team_namesr lib.unified_odds_ingestionr r r rrN ImportErrorrrrrr*rr1r)rrprrrrrr r r+r5rTrexitrr|rorbs\" 22..277??277??8#<=>(277??8,-; -  !    % -$$' )  &_\  :\  9  Z .G1BSU38_,;T;T|8vc3h$*/x"8xcx"&xx',y5yd3iy#yxW[*/u#9u$s)u#'ur&+_4_T#Y_"_Dc L zLe; 9:;s?FFF