iFi:tdZddlZddlZddlZddlmZmZddlmZmZm Z m Z ejjdejjejjejjedddlmZmZdhZdefd Z d4d ed ed ed eedeedef dZ d5dedededededededee edeedefdZ d6ded edededed ed!ed"eed#eed$eed ed%edefd&Z d7ded eded ed!ed"eed#eed$eed'eed(ed ed)eedefd*Zd+ed edefd,Zd+ed ede efd-Z d8dededed.eeddf d/Z! d8d edededed.eedeef d0Z" d9d edededed1e eee fd.eed ed2edeeeffd3Z# ddl$Zy#e%$rYywxYw):z Unified Odds Ingestion Helper This module provides functions to write odds data to the new unified schema (BookmakerOdds, OddsHistoryV2, OddsIngestion tables). Used by existing cron scripts to ensure data flows to both legacy and unified tables. N)datetimetimezone)OptionalDictListAnyz..)ABBREV_TO_FULLnormalize_to_fullnbadb_urlc,tj|S)z$Create database connection from URL.)psycopg2connect)r s ?/var/www/html/eventheodds/scripts/lib/unified_odds_ingestion.pyget_db_connectionrs   F ##sourceleaguerun_type start_dateend_datereturnc|j}|jd|||||f|jd}|j|j |S)zN Log the start of an ingestion run. Returns the ingestion run ID. z INSERT INTO "OddsIngestion" ( source, league, "runType", "startDate", "endDate", "startedAt", status ) VALUES (%s, %s, %s, %s, %s, NOW(), 'running') RETURNING id r)cursorexecutefetchonecommitclose)connrrrrrcur ingestion_ids rlog_ingestion_startr"sZ ++-CKK &(J 9 ;<<>!$LKKMIIK rr!games_processedbookmakers_seen odds_created odds_updatedsnapshots_created errors_counterrorsmetadatastatusc  |j} | jd|||||||rtjj |nd| rtjj | nd| |f |j | j y)z'Log the completion of an ingestion run.a UPDATE "OddsIngestion" SET "completedAt" = NOW(), "durationMs" = EXTRACT(EPOCH FROM (NOW() - "startedAt")) * 1000, "gamesProcessed" = %s, "bookmakersSeen" = %s, "oddsCreated" = %s, "oddsUpdated" = %s, "snapshotsCreated" = %s, "errorsCount" = %s, errors = %s::jsonb, metadata = %s::jsonb, status = %s WHERE id = %s N)rrrextrasJsonrr) rr!r#r$r%r&r'r(r)r*r+r s rlog_ingestion_completer/6sz ++-CKK , <(.V$D*2X&  * KKMIIKrgame_id game_date home_team away_team bookmakermarket line_value home_odds away_odds is_openingc |j} ||nd}| jd||||f| j}|r^|\}}}d}|||t|z }d}| || |z }| jd| | ||||f|j | j y| jd||||||||| | || | t jtj| f|j | j y)zU Upsert a bookmaker odds record. Returns (created: bool, updated: bool). Ngz SELECT id, "openingLineValue", "openingHomeOdds" FROM "BookmakerOdds" WHERE "gameId" = %s AND bookmaker = %s AND market = %s AND COALESCE("lineValue", 0) = %s a UPDATE "BookmakerOdds" SET "homeOdds" = COALESCE(%s, "homeOdds"), "awayOdds" = COALESCE(%s, "awayOdds"), "lineValue" = COALESCE(%s, "lineValue"), "lineMovement" = %s, "oddsMovement" = %s, "fetchedAt" = NOW(), "updatedAt" = NOW() WHERE id = %s )FTa INSERT INTO "BookmakerOdds" ( "gameId", league, "gameDate", "homeTeam", "awayTeam", bookmaker, market, "lineValue", "homeOdds", "awayOdds", "openingLineValue", "openingHomeOdds", "openingAwayOdds", "openingAt", source, "fetchedAt" ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) )TF) rrrfloatrrrnowrutc)rr0rr1r2r3r4r5r6r7r8rr9r line_value_normexisting existing_idexisting_opening_lineexisting_opening_odds line_movement odds_movements rupsert_bookmaker_oddsrE^s8& ++-C%/$:jOKK 9fo 6 8 ||~HDLA *,A  !&;&G&/D)EEM  %:%F%(==M  J }k Z \     VY 9 vz9i  9hll8<<.H    "  r hours_to_game snapshot_typebookmaker_odds_idc |j} | g|retjtj}|j |j tj}||z jdz } | jd| |||||||nd||| | | f |j| jy#tj$r#|j| jYywxYw)z\ Insert an odds history snapshot. Returns True if inserted, False if duplicate. )tzinfoia{ INSERT INTO "OddsHistoryV2" ( "bookmakerOddsId", "gameId", league, "gameDate", bookmaker, market, "lineValue", "homeOdds", "awayOdds", "snapshotAt", "hoursToGame", "snapshotType", source ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), %s, %s, %s ) rTF)rrr<rr=rJreplace total_secondsrrrrIntegrityErrorrollback)rr0rr1r4r5r6r7r8rFrGrrHr r<s rinsert_odds_historyrOs& ++-Cll8<<(    #!)))>I"S779D@    w vZ-Cz y =&       " "  s;AB>>3C43C4 team_namect||xs|}|tvr|Stj|i}|j D]\}}||k(s |cS|S)zDConvert incoming team text to the canonical SportsGame storage form.)r SPORTSGAME_CODE_LEAGUESr getitems)rPr full_name league_mapabbrev mapped_fulls r_to_sportsgame_team_valuerYsf!)V4A I ,,##FB/J)//1 ) #M rcg}|s|S|j}t||xs|}t||}|||fD]}|s||vs |j||S)z Build lookup candidates that can match both legacy full-name rows and the corrected canonical storage form for the league. )stripr rYappend)rPr candidatesrawrU stored_valuevalues r_sportsgame_lookup_candidatesrasq J  // C!#v.5#I,S&9Ly,/% U*,   e $% rexternal_game_idc |j}|jd|||||||f|jr|j|j y)z Normalize an existing SportsGame row in place when we match it through a legacy identity (full names, missing externalGameId, etc.). a UPDATE "SportsGame" SET "homeTeam" = %s, "awayTeam" = %s, "externalGameId" = COALESCE("SportsGame"."externalGameId", %s), "updatedAt" = NOW() WHERE id = %s AND ( COALESCE("homeTeam", '') <> %s OR COALESCE("awayTeam", '') <> %s OR (%s IS NOT NULL AND "externalGameId" IS NULL) ) N)rrrowcountrr)rr0r2r3rbr s r_refresh_sports_game_identityresW ++-CKK         0 || IIKrc |j}t||}t||}t||}t||}|j}|rL|j d|f|j } | r't || d||||j| dS|jdddd} | jddd} |j d|||| | ||f|j } | r't || d||||j| dS|j} |j d|| ||||f|j d} |j|j| S#tj$ru|j|j|j}|j d |||| | f|j } |j| r| dcYSd cYSwxYw) z Find or create a SportsGame record. Returns the game ID. Uses the league's canonical SportsGame storage format. zY SELECT id FROM "SportsGame" WHERE "externalGameId" = %s r)hourminutesecond microsecond;)rgrhriab SELECT id FROM "SportsGame" WHERE league = %s AND "homeTeam" = ANY(%s) AND "awayTeam" = ANY(%s) AND "gameDate" BETWEEN %s AND %s ORDER BY CASE WHEN "homeTeam" = %s AND "awayTeam" = %s THEN 0 ELSE 1 END, CASE WHEN "externalGameId" IS NULL THEN 1 ELSE 0 END, id DESC a INSERT INTO "SportsGame" ( league, season, "gameDate", "homeTeam", "awayTeam", "externalGameId", "createdAt", "updatedAt" ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW()) ON CONFLICT (league, season, "gameDate", "homeTeam", "awayTeam") DO UPDATE SET "externalGameId" = COALESCE("SportsGame"."externalGameId", EXCLUDED."externalGameId"), "updatedAt" = NOW() RETURNING id z SELECT id FROM "SportsGame" WHERE league = %s AND "homeTeam" = %s AND "awayTeam" = %s AND "gameDate" BETWEEN %s AND %s N)lowerrarYrrrrerrKyearrrrMrN)rrr1r2r3rb home_lookup away_lookupr result day_startday_endseasonr0s rget_or_create_sports_gameru0s \\^F/ 6BK/ 6BK))VGKK  +{Iw 9 U W\\^F %dF1Iy)M]^ ay^^F-  fiI?O P R,,.#    " "-  kkm  iIw ?  A "vay,,-s'A E44BG<7G<;G<bookmaker_oddscreate_snapshotsc dddd} t||||||} | s| S|D]} | jdd} | jdi} | jD]\}}|dk(rd}n|dk(rd }n |d k(rd }n|}|jd }|jd }|jd}||Xt|| ||||| ||||| \}}|r | dxxdz cc<|r | dxxdz cc<|st || ||| ||||| s| dxxdz cc<| S)aD Process all bookmaker odds for a single game. bookmaker_odds should be a list of dicts with: - bookmaker: str - markets: dict with keys like 'h2h', 'spreads', 'totals' each containing 'home_odds', 'away_odds', 'line' (for spreads/totals) Returns dict with 'created', 'updated', 'snapshots'. r)createdupdated snapshotsr4unknownmarketsh2h moneylinespreadsspreadtotalstotalliner7r8ryrz)rr{)rurSrTrErO)rrr1r2r3rvrbrrwstatsr0 book_datar4r} market_key market_datar5r6r7r8ryrzs rprocess_odds_for_gamersu*aa 8E( fiI7GG  #), MM+y9 -- 2.'.}}% , #J U"$y(!x' #$0J# 4I# 4I Y%6 5gvy)Y6:y)V  GW i A% i A%  &'69vz9i! +&!+&K% , ),V Lr) scheduledNN) rrrrrrNN completed) the-odds-apiF)NperiodicrN)N)NrT)&__doc__ossysrrrtypingrrrrpathinsertjoindirnameabspath__file__ team_namesr r rRstrrintr"r/r;booltuplerErOrYrarerurpsycopg2.extras ImportErrorrrrs ',,277<<0I JDQR8 !'$c$ %)#'     "  x  :"&#%%% %  %  %%% T#Y %tn% %h!Q Q Q Q  Q  QQ QQ}Q}Q QQ Q|&*# '+2 2 2 2  2  22}2}2E?22 2 }2 2j  c c S#$s)0'+ & && & sm &  &^'+ W- W-W- W-  W- sm W-c]W-B'+ !I II I  I c3h( IsmI II #s(^IZ   s*F//F76F7