-- Procedure para manter apenas as últimas 100 partidas no histórico
-- Executa automaticamente a cada 10 segundos via EVENT

DELIMITER $$

-- Remover procedure antiga se existir
DROP PROCEDURE IF EXISTS cleanup_old_rounds$$

-- Criar procedure de limpeza
CREATE PROCEDURE cleanup_old_rounds()
BEGIN
    DECLARE total_rounds INT;
    
    -- Contar total de partidas
    SELECT COUNT(*) INTO total_rounds FROM game_rounds;
    
    -- Se tiver mais de 100, deletar as antigas
    IF total_rounds > 100 THEN
        DELETE FROM game_rounds 
        WHERE id NOT IN (
            SELECT id FROM (
                SELECT id FROM game_rounds 
                ORDER BY id DESC 
                LIMIT 100
            ) AS recent_rounds
        );
        
        -- Limpar histórico de usuários também (manter apenas últimas 100 partidas)
        DELETE FROM game_history 
        WHERE round_id NOT IN (
            SELECT id FROM game_rounds
        );
    END IF;
END$$

-- Remover event antigo se existir
DROP EVENT IF EXISTS cleanup_rounds_event$$

-- Criar event que executa a cada 10 segundos
CREATE EVENT cleanup_rounds_event
ON SCHEDULE EVERY 10 SECOND
DO
    CALL cleanup_old_rounds()$$

DELIMITER ;

-- Ativar event scheduler (se ainda não estiver ativo)
-- SET GLOBAL event_scheduler = ON;
