-- Indexes for soccer fixtures API performance optimization
-- Compatible with MySQL 5.x and 8.x

-- Core fixture queries (date range + status filtering)
ALTER TABLE fixtures ADD INDEX idx_date_status (date, status);

-- League filtering
ALTER TABLE fixtures ADD INDEX idx_league_date (league_id, date);

-- Team lookups (foreign keys should already be indexed, but ensure)
ALTER TABLE fixtures ADD INDEX idx_home_team (home_team_id);
ALTER TABLE fixtures ADD INDEX idx_away_team (away_team_id);

-- Odds filtering (checking if fixture has odds)
ALTER TABLE odds ADD INDEX idx_fixture_bookmaker (fixture_id, bookmaker_id);
ALTER TABLE odds ADD INDEX idx_fixture_market (fixture_id, market_id);

-- Composite index for the main query (most selective)
ALTER TABLE fixtures ADD INDEX idx_date_status_league (date, status, league_id);

-- League country filtering
ALTER TABLE soccer_leagues ADD INDEX idx_country (country);

-- ============================================
-- ADDITIONAL INDEXES FOR OPTIMIZED QUERY
-- ============================================

-- Critical: Composite index for odds subquery optimization
-- This index covers the WHERE clause in the subquery for massive performance boost
ALTER TABLE odds ADD INDEX idx_fixture_market_label_bookmaker (fixture_id, market_id, label, bookmaker_id, odd);

-- Odds validation filter (for WHERE odd IS NOT NULL AND odd > 0)
ALTER TABLE odds ADD INDEX idx_odds_valid (fixture_id, odd, market_id);

-- Fixture and bookmaker lookup
ALTER TABLE odds ADD INDEX idx_fixture_bookmaker_odd (fixture_id, bookmaker_id, odd);

-- Market name lookup (for JOIN with soccer_markets)
ALTER TABLE soccer_markets ADD INDEX idx_market_name (name);

-- League composite for country filtering + ordering
ALTER TABLE soccer_leagues ADD INDEX idx_country_name (country, name);

-- ============================================
-- VERIFY INDEXES
-- ============================================
SHOW INDEX FROM fixtures WHERE Key_name LIKE 'idx_%';
SHOW INDEX FROM odds WHERE Key_name LIKE 'idx_%';
SHOW INDEX FROM soccer_leagues WHERE Key_name LIKE 'idx_%';
SHOW INDEX FROM soccer_markets WHERE Key_name LIKE 'idx_%';

-- ============================================
-- TEST QUERY PERFORMANCE
-- ============================================
EXPLAIN
SELECT f.id, f.date, f.status
FROM fixtures f
WHERE f.date > NOW()
  AND f.date <= DATE_ADD(NOW(), INTERVAL 2 DAY)
  AND f.status IN ('scheduled', 'NS')
  AND EXISTS (
    SELECT 1 FROM odds o 
    WHERE o.fixture_id = f.id 
      AND o.odd IS NOT NULL 
      AND o.odd > 0 
    LIMIT 1
  )
ORDER BY f.date
LIMIT 20;

-- Expected: "Using index" or "Using where; Using index" in Extra column
