CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `utfn-beta`@`%` 
    SQL SECURITY DEFINER
VIEW `due_promotion_view` AS
    SELECT 
        `coc_master_stats`.`rankshortname` AS `rankshortname`,
        `coc_master_stats`.`username` AS `username`,
        `coc_master_stats`.`userid` AS `user`,
        `coc_master_stats`.`op_hrs` AS `op_hours`,
        `coc_master_stats`.`prac_hrs` AS `ftx_hours`,
        `coc_master_stats`.`reprimand_reports` AS `reprimands`,
        `coc_master_stats`.`last_promotion` AS `last_promotion`,
        `coc_master_stats`.`ATIU` AS `ATIU`,
        `coc_master_stats`.`ATIG` AS `ATIG`,
        `coc_master_stats`.`last_reprimand` AS `last_reprimand`,
        `coc_master_stats`.`good_conduct` AS `good_conduct`,
        (CASE
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` < 75)
                    AND (`coc_master_stats`.`prac_hrs` < 25))
            THEN
                'Pvt'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 75)
                    AND (`coc_master_stats`.`prac_hrs` >= 25)
                    AND ((`coc_master_stats`.`ATIU` >= 6)
                    OR ((`coc_master_stats`.`ATIU` >= 5)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 4)
                    OR ((`coc_master_stats`.`ATIG` >= 3)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'PFC'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 150)
                    AND (`coc_master_stats`.`prac_hrs` >= 50)
                    AND ((`coc_master_stats`.`ATIU` >= 12)
                    OR ((`coc_master_stats`.`ATIU` >= 10)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 5)
                    OR ((`coc_master_stats`.`ATIG` >= 3)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'SP3'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 300)
                    AND (`coc_master_stats`.`prac_hrs` >= 100)
                    AND ((`coc_master_stats`.`ATIU` >= 12)
                    OR ((`coc_master_stats`.`ATIU` >= 15)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 5)
                    OR ((`coc_master_stats`.`ATIG` >= 3)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'SP2'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 400)
                    AND (`coc_master_stats`.`prac_hrs` >= 125)
                    AND ((`coc_master_stats`.`ATIU` >= 27)
                    OR ((`coc_master_stats`.`ATIU` >= 22)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 6)
                    OR ((`coc_master_stats`.`ATIG` >= 5)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'SP1'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 500)
                    AND (`coc_master_stats`.`prac_hrs` >= 150)
                    AND ((`coc_master_stats`.`ATIU` >= 36)
                    OR ((`coc_master_stats`.`ATIU` >= 29)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 7)
                    OR ((`coc_master_stats`.`ATIU` >= 5)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'SPC'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 600)
                    AND (`coc_master_stats`.`prac_hrs` >= 175)
                    AND ((`coc_master_stats`.`ATIU` >= 46)
                    OR ((`coc_master_stats`.`ATIU` >= 37)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 8)
                    OR ((`coc_master_stats`.`ATIG` >= 6)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'MSP'
            WHEN
                ((`coc_master_stats`.`maxtier` >= 2)
                    AND (`coc_master_stats`.`op_hrs` >= 700)
                    AND (`coc_master_stats`.`prac_hrs` >= 200)
                    AND ((`coc_master_stats`.`ATIU` >= 56)
                    OR ((`coc_master_stats`.`ATIU` >= 48)
                    AND (`coc_master_stats`.`good_conduct` = 1)))
                    AND ((`coc_master_stats`.`ATIG` >= 8)
                    OR ((`coc_master_stats`.`ATIG` >= 6)
                    AND (`coc_master_stats`.`good_conduct` = 1))))
            THEN
                'CMS'
        END) AS `rank_should_be`,
        `coc_master_stats`.`direct_ic` AS `oneic`,
        `coc_master_stats`.`twoic` AS `twoic`,
        `coc_master_stats`.`maxtier` AS `core_infantry_rank`
    FROM
        `coc_master_stats`
    WHERE
        ((`coc_master_stats`.`maxtier` > 1)
            AND (`coc_master_stats`.`rankid` > 27))