-- ------------------------------------------------
-- SoD dashboard tables
-- Created 20141020
-- ------------------------------------------------

CREATE TABLE IF NOT EXISTS `sod_dashboard_groupcount` (
  `date` date NOT NULL,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `group_id` int(11) NOT NULL,
  `rule_type` enum('crit_role','crit_tcode','sod_role','sod_func') NOT NULL,
  `count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`group_id`,`rule_type`,`system`,`client`)
);

CREATE TABLE IF NOT EXISTS `sod_dashboard_rolecount` (
  `date` date NOT NULL,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `total_roles_in_system` int(11) NOT NULL DEFAULT '0',
  `total_roles_in_violation` int(11) NOT NULL DEFAULT '0',
  `crit_role` int(11) NOT NULL DEFAULT '0',
  `sod_role` int(11) NOT NULL DEFAULT '0',
  `crit_tcode` int(11) NOT NULL DEFAULT '0',
  `sod_func` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`system`,`date`,`client`)
);

CREATE TABLE IF NOT EXISTS `sod_dashboard_rulecount` (
  `date` date NOT NULL,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `group_id` int(11) NOT NULL,
  `rule_type` enum('crit_role','crit_tcode','sod_role','sod_func') NOT NULL,
  `count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`group_id`,`rule_type`,`system`,`client`)
);

CREATE TABLE IF NOT EXISTS `sod_dashboard_usercount` (
  `date` date NOT NULL,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `total_users_in_system` int(11) NOT NULL DEFAULT '0',
  `total_user_in_violation` int(11) NOT NULL DEFAULT '0',
  `crit_role` int(11) NOT NULL DEFAULT '0',
  `sod_role` int(11) NOT NULL DEFAULT '0',
  `crit_tcode` int(11) NOT NULL DEFAULT '0',
  `sod_func` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`system`,`date`,`client`)
);