-- ------------------------------------------------
-- SoD preventive check tables
-- Created 20140630
-- ------------------------------------------------

CREATE TABLE IF NOT EXISTS `sod_preventive_check` (
  `handle` char(32) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `username` varchar(12) NOT NULL,
  `assignments_json` mediumtext,
  `analysis_complete` timestamp NULL,
  `action` enum('continue','cancel'),
  `action_ts` timestamp NULL,
  PRIMARY KEY (`handle`),
  KEY `ts` (`timestamp`)
);

CREATE TABLE IF NOT EXISTS `sod_preventive_check_violation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `handle` char(32) NOT NULL,
  `type` enum('new', 'pre') NOT NULL,
  `rule_id` int(11) NOT NULL,
  `rule_type` enum('crit_role','crit_tcode','sod_role','sod_func') NOT NULL,
  `username` varchar(12) NOT NULL,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `role` text NOT NULL,
  `composite` varchar(30) DEFAULT NULL,
  `rule_name` varchar(64) DEFAULT NULL,
  `risk_level` enum('Low','High') DEFAULT NULL,
  `mitigation` varchar(256) DEFAULT NULL,
  `response` varchar(64) DEFAULT NULL,
  `warning_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `h` (`handle`),
  CONSTRAINT `sod_prev_chk`
    FOREIGN KEY (`handle`)
    REFERENCES `sod_preventive_check` (`handle`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS `sod_preventive_check_approval` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `violation_id` int(11) NOT NULL,
  `handle` char(32) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `text` mediumtext DEFAULT NULL,
  `document` mediumblob DEFAULT NULL,
  `type` varchar(256) DEFAULT NULL,
  `name` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `h` (`handle`),
  CONSTRAINT `sod_prev_chk_appr`
    FOREIGN KEY (`violation_id` )
    REFERENCES `sod_preventive_check_violation` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `sod_prev_chk2`
    FOREIGN KEY (`handle` )
    REFERENCES `sod_preventive_check` (`handle`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

DROP TABLE `sod_temp_violations`;
