RANKING_UPDATE_SEASON.sql

--
-- Sistema de Atualização do Ranking Semanal para o Gunbound WC v440 GBS
-- Copyright (C) 2016  Érycson Nóbrega <[email protected]>
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
--

DROP EVENT IF EXISTS `RANKING_UPDATE_SEASON`;
DELIMITER ;;
CREATE EVENT `RANKING_UPDATE_SEASON`
    ON SCHEDULE EVERY 1 WEEK STARTS (CURDATE() + INTERVAL 6 - WEEKDAY(CURDATE()) DAY) 
    ON COMPLETION PRESERVE ENABLE DO
BEGIN
    -- Criado por: ExtremsX
    -- Versão Script: 1.0 (2016-03-04)
    -- Versão GB: v440 GBS

    DECLARE TOTAL_GRADE INT;
    DECLARE TOTAL_ACC INT;

    -- Cria uma tabela temporaria para não bloquear a tabela principal
    CREATE TEMPORARY TABLE WebRankingSeasonTemp (INDEX(SeasonScore), INDEX(SeasonGrade)) ENGINE=MEMORY AS (SELECT Id, SeasonScore, 0 AS SeasonRank, 99 AS SeasonGrade FROM Game WHERE NoRankUpdate=0);

    -- A Little Chick 
    UPDATE WebRankingSeasonTemp SET SeasonGrade=19 WHERE SeasonScore<1100 ORDER BY SeasonScore ASC;
    -- Wood Axe 
    UPDATE WebRankingSeasonTemp SET SeasonGrade=17 WHERE SeasonScore BETWEEN 1200 AND 1499 ORDER BY SeasonScore ASC;
    -- Double Wood Axe 
    UPDATE WebRankingSeasonTemp SET SeasonGrade=17 WHERE SeasonScore BETWEEN 1200 AND 1499 ORDER BY SeasonScore ASC;
    -- Stone Axe 
    UPDATE WebRankingSeasonTemp SET SeasonGrade=16 WHERE SeasonScore BETWEEN 1500 AND 1799 ORDER BY SeasonScore ASC;
    -- Double Stone Axe 
    UPDATE WebRankingSeasonTemp SET SeasonGrade=15 WHERE SeasonScore BETWEEN 1800 AND 2299 ORDER BY SeasonScore ASC;
    -- Metal Axe 
    UPDATE WebRankingSeasonTemp SET SeasonGrade=14 WHERE SeasonScore BETWEEN 2300 AND 2799 ORDER BY SeasonScore ASC;
    -- Double Metal Axe
    UPDATE WebRankingSeasonTemp SET SeasonGrade=13 WHERE SeasonScore BETWEEN 2800 AND 3499 ORDER BY SeasonScore ASC;
    -- Silver Axe
    UPDATE WebRankingSeasonTemp SET SeasonGrade=12 WHERE SeasonScore BETWEEN 3500 AND 4199 ORDER BY SeasonScore ASC;
    -- Double Silver Axe
    UPDATE WebRankingSeasonTemp SET SeasonGrade=11 WHERE SeasonScore BETWEEN 4200 AND 5099 ORDER BY SeasonScore ASC;
    -- Double Gold Axe
    UPDATE WebRankingSeasonTemp SET SeasonGrade=10 WHERE SeasonScore BETWEEN 5100 AND 5999 ORDER BY SeasonScore ASC;
    -- Gold Axe
    UPDATE WebRankingSeasonTemp SET SeasonGrade=9 WHERE SeasonScore BETWEEN 6000 AND 6899 ORDER BY SeasonScore ASC;
    
    -- Silver Dragon
    UPDATE WebRankingSeasonTemp SET SeasonGrade=-4 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT 1;
    
    -- Red Dragon
    UPDATE WebRankingSeasonTemp SET SeasonGrade=-3 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT 4;
    
    -- Blue Dragon
    UPDATE WebRankingSeasonTemp SET SeasonGrade=-2 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT 16;
    
    -- Verifica quantas pessoas sobraram
    SELECT COUNT(SeasonScore) INTO TOTAL_ACC FROM WebRankingSeasonTemp WHERE SeasonGrade=99;

    -- Diamond Wand
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.001); -- 0.1%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=-1 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Ruby Wand 
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.009); -- 0.9%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=0 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Spphire Wand
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.02); -- 2%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=1 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Amethyst Wand
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.03); -- 3%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=2 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Gold Double Sided Axe+ 
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.04); -- 4%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=3 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Gold Double Sided Axe
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.1); -- 10%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=4 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Silver Double Sided Axe+
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.1); -- 10%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=5 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Silver Double Sided Axe
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.2); -- 20%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=6 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Metal Double Sided Axe+
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.2); -- 20%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingSeasonTemp SET SeasonGrade=7 WHERE SeasonGrade=99 ORDER BY SeasonScore DESC LIMIT ', TOTAL_GRADE);
    PREPARE stmt FROM @QUERY_STRING;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
        
    -- Metal Double Sided Axe
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.3); -- 30%
    INSERT INTO WebRankingUpdateLog (Time, Message) VALUES (NOW(), CONCAT('Metal Double Sided Axe: ', TOTAL_GRADE));
    UPDATE WebRankingSeasonTemp SET SeasonGrade=8 WHERE SeasonGrade=99;
    
    -- Gera o Rank ordenado
    SET @RANK_POSITION = 0;
    UPDATE WebRankingSeasonTemp SET SeasonRank=(@RANK_POSITION:=@RANK_POSITION+1) ORDER BY SeasonScore DESC;

    -- Envia as atualizações para tabela principal
    UPDATE Game g JOIN WebRankingSeasonTemp t ON t.Id=g.Id SET g.SeasonRank=t.SeasonRank, g.SeasonGrade=t.SeasonGrade;

    -- Deleta a tabela temporarioa
    DROP TEMPORARY TABLE WebRankingSeasonTemp;
END;;