-- ⚡ PREMIER PERFORMANCE INDEXES
-- Run these on the live database to speed up Premier queries
-- Note: You may see errors if indexes don't exist yet - this is normal and safe to ignore

-- Drop existing indexes (ignore errors if they don't exist)
ALTER TABLE premier_matches DROP INDEX idx_premier_matches_teams_time;
ALTER TABLE premier_matches DROP INDEX idx_premier_matches_teams_reverse;
ALTER TABLE premier_results DROP INDEX idx_premier_results_match_id;
ALTER TABLE premier_game_state DROP INDEX idx_premier_game_state_season_week;
ALTER TABLE premier_game_state DROP INDEX idx_premier_game_state_end_time;

-- Create indexes
-- Index for finding matches between two teams (both directions)
CREATE INDEX idx_premier_matches_teams_time 
ON premier_matches(home_team_id, away_team_id, start_time);

-- Composite index for reverse lookup (when teams are swapped)
CREATE INDEX idx_premier_matches_teams_reverse 
ON premier_matches(away_team_id, home_team_id, start_time);

-- Index to optimize the join with results table
CREATE INDEX idx_premier_results_match_id 
ON premier_results(match_id);

-- Index for game state queries by season and week
CREATE INDEX idx_premier_game_state_season_week 
ON premier_game_state(season, week, state);

-- Index for finding finished games by end_time
CREATE INDEX idx_premier_game_state_end_time 
ON premier_game_state(state, end_time);

-- Verify indexes were created
SHOW INDEX FROM premier_matches WHERE Key_name LIKE 'idx_premier%';
SHOW INDEX FROM premier_results WHERE Key_name LIKE 'idx_premier%';
SHOW INDEX FROM premier_game_state WHERE Key_name LIKE 'idx_premier%';
