#!/bin/bash
# Sync odds from all available sources
# Run via cron every 30 minutes during game hours

cd /var/www/html/eventheodds

echo "$(date): Starting odds sync..."

# 1. Fetch live odds from TheOddsAPI
echo "Fetching from TheOddsAPI..."
python3 scripts/theoddsapi_enhanced_fetcher.py --live --leagues nba,nfl,nhl,mlb,ncaab,ncaaf 2>&1

# 2. Sync from OddsSnapshot to SportsGame
echo "Syncing OddsSnapshot to SportsGame..."
PGPASSWORD=eventheodds_dev_password psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -c "
UPDATE \"SportsGame\" g
SET
    \"moneylineHome\" = os.ml_home,
    \"moneylineAway\" = os.ml_away,
    \"spreadHome\" = COALESCE(g.\"spreadHome\", os.spread_home),
    \"total\" = COALESCE(g.\"total\", os.total),
    \"oddsUpdatedAt\" = NOW(),
    \"updatedAt\" = NOW()
FROM (
    SELECT DISTINCT ON (league, DATE(\"gameDate\"), \"homeTeam\", \"awayTeam\")
        league, DATE(\"gameDate\") as game_day, \"homeTeam\", \"awayTeam\",
        \"moneylineHome\" as ml_home, \"moneylineAway\" as ml_away,
        \"spreadHome\" as spread_home, total
    FROM \"OddsSnapshot\"
    WHERE \"moneylineHome\" IS NOT NULL
    ORDER BY league, DATE(\"gameDate\"), \"homeTeam\", \"awayTeam\", \"snapshotAt\" DESC
) os
WHERE g.league = os.league
  AND DATE(g.\"gameDate\") = os.game_day
  AND g.\"moneylineHome\" IS NULL
  AND (
    (UPPER(g.\"homeTeam\") = UPPER(os.\"homeTeam\") AND UPPER(g.\"awayTeam\") = UPPER(os.\"awayTeam\"))
    OR
    (UPPER(g.\"homeTeam\") LIKE '%' || UPPER(os.\"homeTeam\") || '%' OR UPPER(os.\"homeTeam\") LIKE '%' || UPPER(g.\"homeTeam\") || '%')
  );
"

# 3. Cross-fill between duplicate games
echo "Cross-filling duplicate games..."
PGPASSWORD=eventheodds_dev_password psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -c "
UPDATE \"SportsGame\" g1
SET
    \"moneylineHome\" = g2.\"moneylineHome\",
    \"moneylineAway\" = g2.\"moneylineAway\",
    \"spreadHome\" = COALESCE(g1.\"spreadHome\", g2.\"spreadHome\"),
    \"total\" = COALESCE(g1.\"total\", g2.\"total\"),
    \"oddsUpdatedAt\" = NOW(),
    \"updatedAt\" = NOW()
FROM \"SportsGame\" g2
WHERE g1.league = g2.league
  AND DATE(g1.\"gameDate\") = DATE(g2.\"gameDate\")
  AND g1.\"moneylineHome\" IS NULL
  AND g2.\"moneylineHome\" IS NOT NULL
  AND g1.id <> g2.id
  AND (
    (g1.\"homeTeam\" = g2.\"homeTeam\" AND g1.\"awayTeam\" = g2.\"awayTeam\")
    OR
    (g1.\"homeTeam\" LIKE '%' || g2.\"homeTeam\" || '%' OR g2.\"homeTeam\" LIKE '%' || g1.\"homeTeam\" || '%')
  );
"

echo "$(date): Odds sync complete"

# Show current stats
echo ""
echo "Current coverage:"
PGPASSWORD=eventheodds_dev_password psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -c "
SELECT
  league,
  season,
  COUNT(*) as games,
  COUNT(CASE WHEN \"moneylineHome\" IS NOT NULL THEN 1 END) as with_odds,
  ROUND(100.0 * COUNT(CASE WHEN \"moneylineHome\" IS NOT NULL THEN 1 END) / COUNT(*), 1) as odds_pct
FROM \"SportsGame\"
WHERE season >= 2025 AND league IN ('nba','nfl','nhl','mlb','ncaab','ncaaf')
GROUP BY league, season
ORDER BY league, season;
"