-- ⚡ ALL GAMES PERFORMANCE INDEXES
-- Run this file to create all performance indexes at once
-- These dramatically improve query performance as the season progresses
-- Note: You may see errors for DROP INDEX commands if indexes don't exist yet - this is safe to ignore

-- ============================================================
-- DROP EXISTING INDEXES (ignore errors if they don't exist)
-- ============================================================

ALTER TABLE champions_matches DROP INDEX idx_champions_matches_teams_time;
ALTER TABLE champions_matches DROP INDEX idx_champions_matches_teams_reverse;
ALTER TABLE champions_results DROP INDEX idx_champions_results_match_id;
ALTER TABLE champions_game_state DROP INDEX idx_champions_game_state_season_week;
ALTER TABLE champions_game_state DROP INDEX idx_champions_game_state_end_time;

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;

ALTER TABLE liga_matches DROP INDEX idx_liga_matches_teams_time;
ALTER TABLE liga_matches DROP INDEX idx_liga_matches_teams_reverse;
ALTER TABLE liga_results DROP INDEX idx_liga_results_match_id;
ALTER TABLE liga_game_state DROP INDEX idx_liga_game_state_season_week;
ALTER TABLE liga_game_state DROP INDEX idx_liga_game_state_end_time;

-- ============================================================
-- CHAMPIONS INDEXES
-- ============================================================

CREATE INDEX idx_champions_matches_teams_time 
ON champions_matches(home_team_id, away_team_id, start_time);

CREATE INDEX idx_champions_matches_teams_reverse 
ON champions_matches(away_team_id, home_team_id, start_time);

CREATE INDEX idx_champions_results_match_id 
ON champions_results(match_id);

CREATE INDEX idx_champions_game_state_season_week 
ON champions_game_state(season, week, state);

CREATE INDEX idx_champions_game_state_end_time 
ON champions_game_state(state, end_time);

-- ============================================================
-- PREMIER INDEXES
-- ============================================================

CREATE INDEX idx_premier_matches_teams_time 
ON premier_matches(home_team_id, away_team_id, start_time);

CREATE INDEX idx_premier_matches_teams_reverse 
ON premier_matches(away_team_id, home_team_id, start_time);

CREATE INDEX idx_premier_results_match_id 
ON premier_results(match_id);

CREATE INDEX idx_premier_game_state_season_week 
ON premier_game_state(season, week, state);

CREATE INDEX idx_premier_game_state_end_time 
ON premier_game_state(state, end_time);

-- ============================================================
-- LIGA INDEXES
-- ============================================================

CREATE INDEX idx_liga_matches_teams_time 
ON liga_matches(home_team_id, away_team_id, start_time);

CREATE INDEX idx_liga_matches_teams_reverse 
ON liga_matches(away_team_id, home_team_id, start_time);

CREATE INDEX idx_liga_results_match_id 
ON liga_results(match_id);

CREATE INDEX idx_liga_game_state_season_week 
ON liga_game_state(season, week, state);

CREATE INDEX idx_liga_game_state_end_time 
ON liga_game_state(state, end_time);

-- ============================================================
-- VERIFICATION QUERIES
-- ============================================================

SELECT 'Champions Indexes:' AS '';
SHOW INDEX FROM champions_matches WHERE Key_name LIKE 'idx_champions%';
SHOW INDEX FROM champions_results WHERE Key_name LIKE 'idx_champions%';
SHOW INDEX FROM champions_game_state WHERE Key_name LIKE 'idx_champions%';

SELECT 'Premier Indexes:' AS '';
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%';

SELECT 'Liga Indexes:' AS '';
SHOW INDEX FROM liga_matches WHERE Key_name LIKE 'idx_liga%';
SHOW INDEX FROM liga_results WHERE Key_name LIKE 'idx_liga%';
SHOW INDEX FROM liga_game_state WHERE Key_name LIKE 'idx_liga%';
