-- ------------------------------------------------
-- SoD tables for SoD risks and functionalities 
-- Created 20140520
-- ------------------------------------------------

CREATE TABLE IF NOT EXISTS `sod_functionalities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `description` varchar(256) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
);

CREATE TABLE IF NOT EXISTS `sod_activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `func_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `func` (`func_id`),
  CONSTRAINT `fk_func` FOREIGN KEY (`func_id`) REFERENCES `sod_functionalities` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS `sod_activity_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `act_id` int(11) NOT NULL,
  `object_id` int(11) NOT NULL,
  `line` int(11) NOT NULL DEFAULT '0',
  `val1_low` varchar(40) DEFAULT NULL,
  `val1_high` varchar(40) DEFAULT NULL,
  `val2_low` varchar(40) DEFAULT NULL,
  `val2_high` varchar(40) DEFAULT NULL,
  `val3_low` varchar(40) DEFAULT NULL,
  `val3_high` varchar(40) DEFAULT NULL,
  `val4_low` varchar(40) DEFAULT NULL,
  `val4_high` varchar(40) DEFAULT NULL,
  `val5_low` varchar(40) DEFAULT NULL,
  `val5_high` varchar(40) DEFAULT NULL,
  `val6_low` varchar(40) DEFAULT NULL,
  `val6_high` varchar(40) DEFAULT NULL,
  `val7_low` varchar(40) DEFAULT NULL,
  `val7_high` varchar(40) DEFAULT NULL,
  `val8_low` varchar(40) DEFAULT NULL,
  `val8_high` varchar(40) DEFAULT NULL,
  `val9_low` varchar(40) DEFAULT NULL,
  `val9_high` varchar(40) DEFAULT NULL,
  `val10_low` varchar(40) DEFAULT NULL,
  `val10_high` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `act` (`act_id`),
  KEY `obj` (`object_id`),
  CONSTRAINT `fk_act` FOREIGN KEY (`act_id`) REFERENCES `sod_activities` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_obj` FOREIGN KEY (`object_id`) REFERENCES `sod_auth_object` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

ALTER TABLE `sod_rule`
  CHANGE COLUMN `rule_type` `rule_type` ENUM('crit_role','crit_tcode','sod_role','sod_func') NULL DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `sod_sod_func` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rule_id` int(11) NOT NULL,
  `func_a` int(11) DEFAULT NULL,
  `func_b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rule_id_UNIQUE` (`rule_id`),
  KEY `funca` (`func_a`),
  KEY `funcb` (`func_b`),
  CONSTRAINT `fk_rule` FOREIGN KEY (`rule_id`) REFERENCES `sod_rule` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_funca` FOREIGN KEY (`func_a`) REFERENCES `sod_functionalities` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_funcb` FOREIGN KEY (`func_b`) REFERENCES `sod_functionalities` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
