-- ------------------------------------------------
-- Initial commit of SoD tables 
-- Created 20140505
-- ------------------------------------------------

CREATE TABLE IF NOT EXISTS `sod_warning_types` (
  `id` int(11) NOT NULL,
  `type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `sod_warning_types` VALUES (0,'None');
INSERT INTO `sod_warning_types` VALUES (1,'Display Message');
INSERT INTO `sod_warning_types` VALUES (2,'Require Documentation');
INSERT INTO `sod_warning_types` VALUES (3,'Prevent Action');

CREATE TABLE IF NOT EXISTS `sod_approvers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `approver` varchar(64) NOT NULL,
  `system` varchar(8) DEFAULT NULL,
  `client` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sys_cli` (`system`,`client`)
);
INSERT INTO `sod_approvers` VALUES (0, 'None', null, null);
UPDATE `sod_approvers` SET id=0 WHERE id=1;
ALTER TABLE `sod_approvers` AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `sod_text_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text_response` varchar(64) NOT NULL,
  `system` varchar(8) DEFAULT NULL,
  `client` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sys_cli` (`system`,`client`)
);
INSERT INTO `sod_text_responses` VALUES (0, 'No message', null, null);
UPDATE `sod_text_responses` SET id=0 WHERE id=1;
ALTER TABLE `sod_text_responses` AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `sod_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system` varchar(8) DEFAULT NULL,
  `client` varchar(3) DEFAULT NULL,
  `user` varchar(3) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rule_id` int(11) DEFAULT NULL,
  `log` text,
  PRIMARY KEY (`id`),
  KEY `rule` (`rule_id`),
  KEY `sys_cli` (`system`,`client`)
);

CREATE TABLE IF NOT EXISTS `sod_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `system` varchar(8) DEFAULT NULL,
  `client` varchar(3) DEFAULT NULL,
  `rule_type` enum('crit_role','crit_tcode','sod_role') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sys_cli_typ` (`system`,`client`,`rule_type`)
);
INSERT INTO `sod_group` VALUES (0, 'Unassigned', null, null, null);
UPDATE `sod_group` SET id=0 WHERE id=1;
ALTER TABLE `sod_group` AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `sod_rule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `system` varchar(8) DEFAULT NULL,
  `client` varchar(3) DEFAULT NULL,
  `text_response_id` int(11) DEFAULT '0',
  `rule_type` enum('crit_role','crit_tcode','sod_role') DEFAULT NULL,
  `risk_level` enum('Low','High') DEFAULT NULL,
  `status` enum('Inactive','Active') NOT NULL DEFAULT 'Inactive',
  `version` int(11) NOT NULL DEFAULT '1',
  `origin` varchar(45) DEFAULT NULL,
  `help` varchar(45) DEFAULT NULL,
  `group_id` int(11) DEFAULT '0',
  `mitigation` varchar(256) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  `warning_id` int(11) DEFAULT '0' COMMENT '0-None, 1-Warn, 2-Req.Doc, 3-Prevent',
  `approver_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `sys_cli_typ_stat` (`system`,`client`,`rule_type`,`status`)
);

CREATE TABLE IF NOT EXISTS `sod_crit_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) DEFAULT NULL,
  `rule_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rule` (`rule_id`)
);

CREATE TABLE IF NOT EXISTS `sod_sod_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) DEFAULT NULL,
  `rule_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rule` (`rule_id`)
);

CREATE TABLE IF NOT EXISTS `sod_temp_violations` (
  `handle` char(32) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `violations_json` mediumtext,
  PRIMARY KEY (`handle`),
  KEY `ts` (`timestamp`)
);

