-- ============================================
-- PRODUCTION: Apply These Indexes IMMEDIATELY
-- This will fix the slow pagination issue
-- Safe to run multiple times (uses IF NOT EXISTS)
-- ============================================

-- FIXTURES TABLE INDEXES
ALTER TABLE fixtures ADD INDEX IF NOT EXISTS idx_fixtures_api_main (date, status, league_id);
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);

-- ODDS TABLE INDEXES (Most important for performance)
ALTER TABLE odds ADD INDEX IF NOT EXISTS idx_odds_fixture_lookup (fixture_id, odd);
ALTER TABLE odds ADD INDEX IF NOT EXISTS idx_odds_complete (fixture_id, market_id, bookmaker_id);

-- LEAGUE TABLE INDEXES
ALTER TABLE soccer_leagues ADD INDEX IF NOT EXISTS idx_leagues_country_name (country, name);

-- MARKETS TABLE INDEXES
ALTER TABLE soccer_markets ADD INDEX IF NOT EXISTS idx_market_name (name);

-- ============================================
-- VERIFY: Check that indexes were created
-- ============================================
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as COLUMNS
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('fixtures', 'odds', 'soccer_leagues', 'soccer_markets')
  AND INDEX_NAME LIKE 'idx_%'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME, INDEX_NAME;

-- ============================================
-- TEST: Check 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 > 0 LIMIT 1)
ORDER BY f.date
LIMIT 20;

-- Expected: Should show "Using index" in the Extra column
