-- ============================================
-- CRITICAL INDEXES FOR FIXTURES API PERFORMANCE
-- Run this on production to dramatically improve pagination speed
-- ============================================

-- 1. MOST IMPORTANT: Composite index for the main query
-- Covers: date filtering + status + ordering
ALTER TABLE fixtures 
ADD INDEX idx_fixtures_api_main (date, status, league_id);

-- 2. Index for odds existence check (used in whereExists)
-- This speeds up the subquery checking if fixture has odds
ALTER TABLE odds 
ADD INDEX idx_odds_fixture_lookup (fixture_id, bookmaker_id, odd);

-- 3. Index for odds fetching with market filter
-- Covers the main odds query with all filters
ALTER TABLE odds 
ADD INDEX idx_odds_complete (fixture_id, market_id, bookmaker_id, odd);

-- 4. League lookups (for JOIN and filtering)
ALTER TABLE soccer_leagues 
ADD INDEX idx_leagues_country_name (country, name);

-- 5. Ensure team indexes exist (should be from foreign keys)
ALTER TABLE fixtures 
ADD INDEX IF NOT EXISTS idx_home_team (home_team_id);

ALTER TABLE fixtures 
ADD INDEX IF NOT EXISTS idx_away_team (away_team_id);

ALTER TABLE fixtures 
ADD INDEX IF NOT EXISTS idx_league (league_id);

-- 6. Market lookups for odds JOIN
ALTER TABLE soccer_markets 
ADD INDEX IF NOT EXISTS idx_market_name (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_%';

-- ============================================
-- QUERY PERFORMANCE TEST
-- Run this to see execution plan
-- ============================================
EXPLAIN 
SELECT f.id, f.date, f.status, f.league_id
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
  )
ORDER BY f.date
LIMIT 20;
