-- Performance Optimization Indexes for Payout Distribution
-- Run this SQL to significantly improve payout query performance

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

-- Optimize bet selections queries (status + match_id filtering)
CREATE INDEX idx_champions_bet_selections_status_match 
ON champions_bet_selections(status, match_id);

-- Optimize bet options lookup (option_id is heavily queried)
CREATE INDEX idx_champions_bet_options_id_match 
ON champions_bet_options(id, match_id);

-- Optimize bet group queries
CREATE INDEX idx_champions_bet_selections_bet_id 
ON champions_bet_selections(bet_id);

-- Optimize game state transitions (state + timestamps)
CREATE INDEX idx_champions_game_state_processing 
ON champions_game_state(state, payout_processed, end_time);

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

CREATE INDEX idx_premier_bet_selections_status_match 
ON premier_bet_selections(status, match_id);

CREATE INDEX idx_premier_bet_options_id_match 
ON premier_bet_options(id, match_id);

CREATE INDEX idx_premier_bet_selections_bet_id 
ON premier_bet_selections(bet_id);

CREATE INDEX idx_premier_game_state_processing 
ON premier_game_state(state, payout_processed, end_time);

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

CREATE INDEX idx_liga_bet_selections_status_match 
ON liga_bet_selections(status, match_id);

CREATE INDEX idx_liga_bet_options_id_match 
ON liga_bet_options(id, match_id);

CREATE INDEX idx_liga_bet_selections_bet_id 
ON liga_bet_selections(bet_id);

CREATE INDEX idx_liga_game_state_processing 
ON liga_game_state(state, payout_processed, end_time);

-- ====== JACKPOT INDEXES ======

-- Optimize jackpot queries (assigned_bet_id + game_type filtering)
CREATE INDEX idx_jackpot_assignments 
ON jackpot(assigned_bet_id, assigned_game_type, paid_out);

-- Optimize active jackpot lookup
CREATE INDEX idx_jackpot_active 
ON jackpot(is_active, assigned_bet_id);

-- ====== WALLET INDEXES ======

-- Wallet balance updates by user_id (if not already exists)
CREATE INDEX idx_wallets_user_id 
ON wallets(user_id);

-- Show index creation results
SELECT 
    'Indexes created successfully! Run ANALYZE TABLE to update statistics.' AS Status;

-- Update table statistics for query optimizer
ANALYZE TABLE champions_bet_selections, champions_bet_options, champions_bets, champions_game_state;
ANALYZE TABLE premier_bet_selections, premier_bet_options, premier_bets, premier_game_state;
ANALYZE TABLE liga_bet_selections, liga_bet_options, liga_bets, liga_game_state;
ANALYZE TABLE jackpot, wallets;
