CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `utfn-beta`@`%` 
    SQL SECURITY DEFINER
VIEW `coc_master_stats` AS
    SELECT 
        `rv`.`rankshortname` AS `rankshortname`,
        `rv`.`username` AS `username`,
        `rv`.`userid` AS `userid`,
        `rv`.`rankid` AS `rankid`,
        `rv`.`unitcallsign` AS `unit`,
        `rv`.`unitid` AS `unitid`,
        `rv`.`discord_channel` AS `discord_channel`,
        `rv`.`twoic` AS `twoic`,
        `rv`.`rep` AS `rep`,
        `coc_ics`.`direct_ic` AS `direct_ic`,
        `coc_ics`.`upper_ic` AS `upper_ic`,
        `coc_ics`.`upper_2ic` AS `upper_2ic`,
        (SELECT 
                ROUND(SUM(`o`.`Duration`), 0)
            FROM
                (`operations` `o`
                LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `o`.`id`)))
            WHERE
                ((`ol`.`wasnoshow` = 0)
                    AND (`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`OpParent` <> 2)
                    AND (UNIX_TIMESTAMP(`o`.`DateToBeExecuted`) >= UNIX_TIMESTAMP((NOW() - INTERVAL 30 DAY))))) AS `op_hrs_last_30_days`,
        (SELECT 
                ROUND(SUM(`o`.`Duration`), 0)
            FROM
                (`operations` `o`
                LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `o`.`id`)))
            WHERE
                ((`ol`.`wasnoshow` = 0)
                    AND (`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`OpParent` = 2)
                    AND (UNIX_TIMESTAMP(`o`.`DateToBeExecuted`) >= UNIX_TIMESTAMP((NOW() - INTERVAL 30 DAY))))) AS `ftx_hrs_last_30_days`,
        (SELECT 
                COUNT(`ol`.`user`)
            FROM
                (`operation_layout` `ol`
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                ((`ol`.`user` = `rv`.`userid`)
                    AND (`ol`.`wasnoshow` = 0)
                    AND (`o`.`DateToBeExecuted` < NOW()))) AS `deployments`,
        ROUND(((((`rv`.`joined` - UNIX_TIMESTAMP()) / 86000) * -(1)) / (SELECT 
                        COUNT(`ol`.`user`)
                    FROM
                        (`operation_layout` `ol`
                        LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
                    WHERE
                        ((`ol`.`user` = `rv`.`userid`)
                            AND (`ol`.`wasnoshow` = 0)
                            AND (`o`.`DateToBeExecuted` < NOW())))),
                0) AS `avg_days_between_ops`,
        (SELECT 
                (ROUND(((UNIX_TIMESTAMP(`o`.`DateToBeExecuted`) - UNIX_TIMESTAMP()) / 86000),
                            0) * -(1))
            FROM
                (`operation_layout` `ol`
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                ((`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`DateToBeExecuted` < NOW())
                    AND (`ol`.`wasnoshow` = 0))
            ORDER BY `o`.`DateToBeExecuted` DESC
            LIMIT 1) AS `days_since_last_deploy`,
        (CASE
            WHEN
                ((ROUND(((((`rv`.`joined` - UNIX_TIMESTAMP()) / 86000) * -(1)) / (SELECT 
                                COUNT(`ol`.`user`)
                            FROM
                                (`operation_layout` `ol`
                                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
                            WHERE
                                ((`ol`.`user` = `rv`.`userid`)
                                    AND (`ol`.`wasnoshow` = 0)
                                    AND (`o`.`DateToBeExecuted` < NOW())))),
                        0) * 2) > (SELECT 
                        (ROUND(((UNIX_TIMESTAMP(`o`.`DateToBeExecuted`) - UNIX_TIMESTAMP()) / 86000),
                                    0) * -(1))
                    FROM
                        (`operation_layout` `ol`
                        LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
                    WHERE
                        ((`ol`.`user` = `rv`.`userid`)
                            AND (`o`.`DateToBeExecuted` < NOW())
                            AND (`ol`.`wasnoshow` = 0))
                    ORDER BY `o`.`DateToBeExecuted` DESC
                    LIMIT 1))
            THEN
                'Normal'
            ELSE 'Deteriorating'
        END) AS `deployments_status`,
        ((SELECT 
                SUM(`users_op_hours_in_area`.`op_hrs`)
            FROM
                `users_op_hours_in_area`
            WHERE
                (`users_op_hours_in_area`.`user` = `rv`.`userid`)) + (SELECT 
                ROUND(SUM(`time_in_training_areas`.`time_in_area`),
                            2)
            FROM
                `time_in_training_areas`
            WHERE
                (`time_in_training_areas`.`user` = `rv`.`userid`))) AS `hours`,
        ROUND((((SELECT 
                        SUM(`users_op_hours_in_area`.`op_hrs`)
                    FROM
                        `users_op_hours_in_area`
                    WHERE
                        (`users_op_hours_in_area`.`user` = `rv`.`userid`)) + (SELECT 
                        ROUND(SUM(`time_in_training_areas`.`time_in_area`),
                                    2)
                    FROM
                        `time_in_training_areas`
                    WHERE
                        (`time_in_training_areas`.`user` = `rv`.`userid`))) / (ROUND(((`rv`.`joined` - UNIX_TIMESTAMP()) / 86000),
                        0) * -(1))),
                2) AS `activity_index`,
        (SELECT 
                SUM(`users_op_hours_in_area`.`op_hrs`)
            FROM
                `users_op_hours_in_area`
            WHERE
                (`users_op_hours_in_area`.`user` = `rv`.`userid`)) AS `op_hrs`,
        (SELECT 
                ROUND(SUM(`time_in_training_areas`.`time_in_area`),
                            2)
            FROM
                `time_in_training_areas`
            WHERE
                (`time_in_training_areas`.`user` = `rv`.`userid`)) AS `prac_hrs`,
        (SELECT 
                COUNT(`ol`.`user`)
            FROM
                (`operation_layout` `ol`
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                ((`ol`.`user` = `rv`.`userid`)
                    AND (`ol`.`wasnoshow` = 0)
                    AND (`o`.`DateToBeExecuted` > NOW()))) AS `futurebookings`,
        (SELECT 
                COUNT(`ol`.`user`)
            FROM
                (`operation_layout` `ol`
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                ((`ol`.`user` = `rv`.`userid`)
                    AND (`ol`.`wasnoshow` = 1))) AS `noshows`,
        (((SELECT 
                AVG(`opr`.`Overall`)
            FROM
                `operation_reports` `opr`
            WHERE
                (`opr`.`user` = `rv`.`userid`)) * 2.5) * 10) AS `avgaar`,
        (((SELECT 
                AVG(`opr`.`Overall`)
            FROM
                `operation_reports` `opr`
            WHERE
                ((`opr`.`user` = `rv`.`userid`)
                    AND (`opr`.`unixtime` >= (CURDATE() - INTERVAL 30 DAY)))
            ORDER BY `opr`.`id` DESC) * 2.5) * 10) AS `lastaar`,
        (CASE
            WHEN
                ((((SELECT 
                        AVG(`opr`.`Overall`)
                    FROM
                        `operation_reports` `opr`
                    WHERE
                        ((`opr`.`user` = `rv`.`userid`)
                            AND (`opr`.`unixtime` >= (CURDATE() - INTERVAL 30 DAY)))
                    ORDER BY `opr`.`id` DESC) * 2.5) * 10) >= (((SELECT 
                        AVG(`opr`.`Overall`)
                    FROM
                        `operation_reports` `opr`
                    WHERE
                        (`opr`.`user` = `rv`.`userid`)) * 2.5) * 10))
            THEN
                'Above Average'
            ELSE 'Below Average'
        END) AS `aar_change_status`,
        (SELECT 
                `o`.`DateToBeExecuted`
            FROM
                (`operation_layout` `ol`
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                ((`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`DateToBeExecuted` < NOW())
                    AND (`ol`.`wasnoshow` = 0))
            ORDER BY `o`.`DateToBeExecuted` DESC
            LIMIT 1) AS `lastdeploy`,
        (SELECT 
                `o`.`DateToBeExecuted`
            FROM
                (`operation_layout` `ol`
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                ((`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`DateToBeExecuted` > NOW())
                    AND (`ol`.`wasnoshow` = 0))
            ORDER BY `o`.`DateToBeExecuted`
            LIMIT 1) AS `nextdeploy`,
        ((SELECT 
                `ranks`.`op_hrs`
            FROM
                `ranks`
            WHERE
                (`ranks`.`id` = (`rv`.`rankid` - 1))) - (SELECT 
                ROUND(SUM(`o`.`Duration`), 0)
            FROM
                (`operations` `o`
                LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `o`.`id`)))
            WHERE
                ((`ol`.`wasnoshow` = 0)
                    AND (`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`OpParent` <> 2)))) AS `req_op_hrs`,
        ((SELECT 
                `ranks`.`prac_hrs`
            FROM
                `ranks`
            WHERE
                (`ranks`.`id` = (`rv`.`rankid` - 1))) - (SELECT 
                ROUND(SUM(`o`.`Duration`), 0)
            FROM
                (`operations` `o`
                LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `o`.`id`)))
            WHERE
                ((`ol`.`wasnoshow` = 0)
                    AND (`ol`.`user` = `rv`.`userid`)
                    AND (`o`.`OpParent` = 2)))) AS `req_prac_hrs`,
        (SELECT 
                COUNT(`operation_reports`.`mvp`)
            FROM
                `operation_reports`
            WHERE
                (`operation_reports`.`mvp` = `rv`.`userid`)) AS `mvp`,
        (SELECT 
                `members_mvp`.`times_mvp_last30`
            FROM
                `members_mvp`
            WHERE
                ((`members_mvp`.`user` = `rv`.`userid`)
                    AND (`members_mvp`.`is_current` = 1))) AS `times_mvp_last30`,
        (SELECT 
                `members_mvp`.`times_mvp`
            FROM
                `members_mvp`
            WHERE
                ((`members_mvp`.`user` = `rv`.`userid`)
                    AND (`members_mvp`.`is_current` = 1))) AS `times_mvp`,
        `rv`.`statusshort` AS `status`,
        (ROUND(((`rv`.`joined` - UNIX_TIMESTAMP()) / 86000),
                0) * -(1)) AS `days_enlisted`,
        (SELECT 
                (ROUND(((UNIX_TIMESTAMP(`members_rank`.`AssignedOn_unixtime`) - UNIX_TIMESTAMP()) / 86000),
                            0) * -(1))
            FROM
                `members_rank`
            WHERE
                ((`members_rank`.`MemberId` = `rv`.`userid`)
                    AND (`members_rank`.`IsCurrent` = 1))) AS `time_in_grade`,
        (SELECT 
                (ROUND(((UNIX_TIMESTAMP(`members_status`.`AssignedOn_unixtime`) - UNIX_TIMESTAMP()) / 86000),
                            0) * -(1))
            FROM
                `members_status`
            WHERE
                ((`members_status`.`MemberId` = `rv`.`userid`)
                    AND (`members_status`.`IsCurrent` = 1))) AS `time_in_status`,
        (SELECT 
                COUNT(`coc_report`.`id`)
            FROM
                `coc_report`
            WHERE
                ((`coc_report`.`reported_user` = `rv`.`userid`)
                    AND (`coc_report`.`report_category` = 1))) AS `reprimand_reports`,
        (SELECT 
                COUNT(`coc_report`.`id`)
            FROM
                `coc_report`
            WHERE
                ((`coc_report`.`reported_user` = `rv`.`userid`)
                    AND (`coc_report`.`report_category` = 2))) AS `praise_reports`,
        (SELECT 
                COUNT(`coc_report`.`id`)
            FROM
                `coc_report`
            WHERE
                ((`coc_report`.`reported_user` = `rv`.`userid`)
                    AND (`coc_report`.`report_status` <> 10))) AS `open_reports`,
        (SELECT 
                COUNT(`training_sop_user_flag`.`id`)
            FROM
                `training_sop_user_flag`
            WHERE
                (`training_sop_user_flag`.`user` = `rv`.`userid`)) AS `sop_flags`,
        (SELECT 
                COUNT(`training_sop_user_read_log`.`id`)
            FROM
                `training_sop_user_read_log`
            WHERE
                (`training_sop_user_read_log`.`user` = `rv`.`userid`)) AS `sops_read_views`,
        (SELECT 
                COUNT(DISTINCT `training_sop_user_read_log`.`sop`)
            FROM
                `training_sop_user_read_log`
            WHERE
                (`training_sop_user_read_log`.`user` = `rv`.`userid`)) AS `unique_sops_read`,
        (ROUND(((SELECT 
                        COUNT(DISTINCT `training_sop_user_read_log`.`sop`)
                    FROM
                        `training_sop_user_read_log`
                    WHERE
                        (`training_sop_user_read_log`.`user` = `rv`.`userid`)) / (SELECT 
                        COUNT(`training_sop`.`id`)
                    FROM
                        `training_sop`)),
                2) * 100) AS `sop_read_coverage`,
        (SELECT 
                COUNT(DISTINCT `tsop`.`sop`) AS `sops`
            FROM
                ((`training_sop_in_operations` `tsop`
                LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `tsop`.`operation`)))
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                (`ol`.`user` = `rv`.`userid`)) AS `unique_sops_practiced`,
        (SELECT 
                COUNT(`training_sop`.`id`)
            FROM
                `training_sop`) AS `total_sops`,
        (ROUND(((SELECT 
                        COUNT(DISTINCT `tsop`.`sop`) AS `sops`
                    FROM
                        ((`training_sop_in_operations` `tsop`
                        LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `tsop`.`operation`)))
                        LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
                    WHERE
                        (`ol`.`user` = `rv`.`userid`)) / (SELECT 
                        COUNT(`training_sop`.`id`)
                    FROM
                        `training_sop`)),
                2) * 100) AS `sop_coverage`,
        (SELECT 
                `training_sop_user_read_log`.`read_on`
            FROM
                `training_sop_user_read_log`
            WHERE
                (`training_sop_user_read_log`.`user` = 1)
            ORDER BY `training_sop_user_read_log`.`id` DESC
            LIMIT 1) AS `last_sop_read`,
        (SELECT 
                `o`.`DateToBeExecuted`
            FROM
                ((`training_sop_in_operations` `tsop`
                LEFT JOIN `operation_layout` `ol` ON ((`ol`.`operation` = `tsop`.`operation`)))
                LEFT JOIN `operations` `o` ON ((`o`.`id` = `ol`.`operation`)))
            WHERE
                (`ol`.`user` = 1)
            ORDER BY `o`.`DateToBeExecuted` DESC
            LIMIT 1) AS `last_sop_practice`,
        (SELECT 
                `users`.`leaveto`
            FROM
                `users`
            WHERE
                (`users`.`id` = `rv`.`userid`)) AS `leaveto`,
        (SELECT 
                COUNT(`coc`.`id`)
            FROM
                `coc`
            WHERE
                (`coc`.`1ic` = `rv`.`userid`)) AS `unders`,
        (SELECT 
                COUNT(DISTINCT CONCAT(MONTH(`op`.`DateToBeExecuted`),
                                YEAR(`op`.`DateToBeExecuted`)))
            FROM
                `operation_players` `op`
            WHERE
                (`op`.`userid` = `rv`.`userid`)
            LIMIT 1) AS `ATIU`,
        (SELECT 
                `mr`.`AssignedOn_unixtime`
            FROM
                `members_rank` `mr`
            WHERE
                ((`mr`.`IsCurrent` = 1)
                    AND (`mr`.`MemberId` = `rv`.`userid`))
            LIMIT 1) AS `last_promotion`,
        (SELECT 
                COUNT(DISTINCT CONCAT(MONTH(`op`.`DateToBeExecuted`),
                                YEAR(`op`.`DateToBeExecuted`)))
            FROM
                `operation_players` `op`
            WHERE
                ((`op`.`userid` = `rv`.`userid`)
                    AND (`op`.`DateToBeExecuted` > `last_promotion`))
            LIMIT 1) AS `ATIG`,
        (SELECT 
                MAX(`coc_report`.`unixtime`)
            FROM
                `coc_report`
            WHERE
                ((`coc_report`.`report_category` = 1)
                    AND (`coc_report`.`reported_user` = `rv`.`userid`))) AS `last_reprimand`,
        (CASE
            WHEN
                ((SELECT 
                        MAX(`coc_report`.`unixtime`)
                    FROM
                        `coc_report`
                    WHERE
                        ((`coc_report`.`report_category` = 1)
                            AND (`coc_report`.`reported_user` = `rv`.`userid`))) > (SELECT 
                        `mr`.`AssignedOn_unixtime`
                    FROM
                        `members_rank` `mr`
                    WHERE
                        ((`mr`.`IsCurrent` = 1)
                            AND (`mr`.`MemberId` = `rv`.`userid`))
                    LIMIT 1))
            THEN
                0
            ELSE 1
        END) AS `good_conduct`,
        (SELECT 
                MAX(`w`.`tier`)
            FROM
                `members_tier_display` `w`
            WHERE
                ((`rv`.`userid` = `w`.`user`)
                    AND (`w`.`area` = 3))) AS `maxtier`
    FROM
        ((`roster_view` `rv`
        LEFT JOIN `coc` ON ((`coc`.`user` = `rv`.`userid`)))
        LEFT JOIN `coc_ics` ON ((`coc_ics`.`userid` = `rv`.`userid`)))
    WHERE
        (`rv`.`statusshort` IN ('REG' , 'RES', 'ELOA'))