--------------------------------------------------
-- Add user, role, tcode table for UM
-- Created 20110928
--------------------------------------------------

CREATE TABLE IF NOT EXISTS `um_user` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `user` varchar(12) NOT NULL default '',
  `first_name` varchar(40) default NULL,
  `last_name` varchar(40) default NULL,
  `mail` varchar(250) default NULL,
  `test_container` varchar(30) default NULL,
  PRIMARY KEY  (`system`,`client`,`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `um_role` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `role` varchar(30) NOT NULL default '',
  `description` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`system`,`client`,`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `um_tcode` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `tcode` varchar(20) NOT NULL default '',
  `description` varchar(36) NOT NULL default '',
  PRIMARY KEY  (`system`,`client`,`tcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `um_role_tcode` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `role` varchar(30) NOT NULL default '',
  `tcode` varchar(20) NOT NULL default '',
  `delete_flag` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`system`,`client`,`role`,`tcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `um_user_role` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `user` varchar(12) NOT NULL default '',
  `role` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`system`,`client`,`user`,`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `um_info` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `user` varchar(12) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `n_users` int(10) unsigned NOT NULL,
  `n_roles` int(10) unsigned NOT NULL,
  `n_tcodes` int(10) unsigned NOT NULL,
  `n_user_role_pairs` int(10) unsigned NOT NULL,
  `n_role_tcode_pairs` int(10) unsigned NOT NULL,
  PRIMARY key (`system`,`client`,`user`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `um_user_role_tcode` (
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `user` varchar(12) NOT NULL,
  `role` varchar(30) NOT NULL,
  `tcode` varchar(20) NOT NULL,
  PRIMARY KEY  (`system`,`client`,`user`,`role`,`tcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--INSERT INTO `um_user_role_tcode` 
--    SELECT ur.system, ur.client, user, ur.role, tcode
--    FROM um_user_role AS ur 
--    JOIN um_role_tcode AS rt 
--    ON ur.role = rt.role AND ur.system = rt.system AND ur.client = rt.client