RANKING_UPDATE_TOTAL.sql

--
-- Sistema de Atualização do Ranking Geral 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_TOTAL`;
DELIMITER ;;
CREATE EVENT `RANKING_UPDATE_TOTAL` 
    ON SCHEDULE EVERY 1 DAY STARTS (CURDATE() + INTERVAL 1 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 WebRankingTotalTemp (INDEX(TotalScore), INDEX(TotalGrade)) ENGINE=MEMORY AS (SELECT Id, TotalScore, 0 AS TotalRank, 99 AS TotalGrade FROM Game WHERE NoRankUpdate=0);

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

    -- Diamond Wand
    SET TOTAL_GRADE = CEIL(TOTAL_ACC * 0.001); -- 0.1%
    SET @QUERY_STRING = CONCAT('UPDATE WebRankingTotalTemp SET TotalGrade=-1 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=0 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=1 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=2 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=3 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=4 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=5 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=6 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=7 WHERE TotalGrade=99 ORDER BY TotalScore 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 WebRankingTotalTemp SET TotalGrade=8 WHERE TotalGrade=99;
    
    -- Gera o Rank ordenado
    SET @RANK_POSITION = 0;
    UPDATE WebRankingTotalTemp SET TotalRank=(@RANK_POSITION:=@RANK_POSITION+1) ORDER BY TotalScore DESC;

    -- Envia as atualizações para tabela principal
    UPDATE Game g JOIN WebRankingTotalTemp t ON t.Id=g.Id SET g.TotalRank=t.TotalRank, g.TotalGrade=t.TotalGrade;

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