-- -------------------------------------------- --
--  APM-3325 - UM, upload sap star
--  Creation of tables for upload of star generated files
-- -------------------------------------------- --
 CREATE TABLE IF NOT EXISTS `um_starfile` (
  `um_starfile_id` int NOT NULL AUTO_INCREMENT COMMENT 'unique id for file',
  `system` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'identification of uploaded file',
  `filename` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the name actual name of the file uploaded',
  `upload_date` datetime DEFAULT NULL COMMENT 'Date and time upload took place',
  PRIMARY KEY (`um_starfile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains metadate for uploaded start files';

CREATE TABLE IF NOT EXISTS `um_starfile_data` (
  `um_stardata_id` int NOT NULL AUTO_INCREMENT COMMENT 'unique id',
  `um_star_id` int DEFAULT NULL COMMENT 'key from um_star',
  `step` int DEFAULT NULL COMMENT 'The filename of the uploaded file',
  `rule` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
  `final` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `object` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`um_stardata_id`),
  UNIQUE KEY `um_star_id_UNIQUE` (`um_stardata_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='contains data for uploaded star files';

CREATE TABLE IF NOT EXISTS `um_starfile_cat` (
  `step` int NOT NULL,
  `rule` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `system` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `um_role` 
ADD COLUMN `star_category_step` int COLLATE utf8mb4_unicode_ci DEFAULT NULL;
