--------------------------------------------------
-- 2.1.2 to 2.2.0 delta
-- based on apm_2_1_2-to-2_2.sql
-- Created 20090923, TNS
--------------------------------------------------



CREATE TABLE cehior_auth_lang (
    unikid int(11) NOT NULL COMMENT '' auto_increment,
    textid int(11) NOT NULL COMMENT '',
    lang varchar(32) NULL DEFAULT 'EN' COMMENT '' COLLATE utf8_general_ci,
    `text` text NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    type varchar(1024) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    comment text NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    file varchar(1024) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    screen varchar(1024) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    PRIMARY KEY (unikid)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE temp_auth (
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    object varchar(10) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `values` varchar(1000) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    result smallint(6) NOT NULL COMMENT '',
    message varchar(255) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    INDEX guiid (guiid, object)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE temp_auth_post (
    id int(11) NOT NULL COMMENT '' auto_increment,
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `values` longtext NOT NULL COMMENT '' COLLATE utf8_general_ci,
    PRIMARY KEY (id),
    INDEX guiid (guiid)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE temp_role (
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
    role varchar(30) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    description varchar(80) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    PRIMARY KEY (guiid, role),
    INDEX `timestamp` (`timestamp`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE temp_role_tcode (
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
    role varchar(30) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    tcode varchar(20) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    delete_flag tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
    PRIMARY KEY (guiid, role, tcode),
    INDEX `timestamp` (`timestamp`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE temp_tcode (
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
    tcode varchar(20) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    description varchar(36) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    PRIMARY KEY (guiid, tcode),
    INDEX `timestamp` (`timestamp`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE temp_user (
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
    user varchar(12) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    first_name varchar(40) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    last_name varchar(40) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    mail varchar(250) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    test_container varchar(30) NULL DEFAULT NULL COMMENT '' COLLATE utf8_general_ci,
    PRIMARY KEY (guiid, user),
    INDEX `timestamp` (`timestamp`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE temp_user_role (
    guiid char(32) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
    user varchar(12) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    role varchar(30) NOT NULL DEFAULT '' COMMENT '' COLLATE utf8_general_ci,
    PRIMARY KEY (guiid, user, role),
    INDEX `timestamp` (`timestamp`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

ALTER TABLE cehior_auth_header_task
    ADD INDEX unikid (unikid),
    ADD INDEX system (`system`, client),
    ADD INDEX issuenum (issuenum),
    ADD INDEX create_date (create_date, create_time),
    ADD INDEX finish_date (finish_date, finish_time);


ALTER TABLE cehior_auth_proj_header
    ADD auth_group varchar(12) NOT NULL DEFAULT '' COMMENT 'Authorization group' COLLATE utf8_general_ci AFTER client;


DROP TABLE cehior_auth_proj_roller_tcode_temp;

DROP TABLE cehior_auth_proj_test_roller_temp;

DROP TABLE cehior_auth_proj_test_user_temp;

ALTER TABLE cehior_auth_system_descr
    ADD displayproject int(11) NOT NULL DEFAULT '0' COMMENT '' AFTER extAnalysis;


ALTER TABLE temp_field
    MODIFY `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
    COMMENT='Temp. Auth. Object Class list';

ALTER TABLE temp_object
    MODIFY `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
    COMMENT='Temp. Auth. Object Class list';

ALTER TABLE temp_object_class
    MODIFY `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
    COMMENT='Temp. Auth. Object Class list';

ALTER TABLE temp_orglvl
    MODIFY `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
    COMMENT='Temp. Auth. Object Class list';

ALTER TABLE temp_user_group
    MODIFY `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
    COMMENT='Temp. User Group list';

TRUNCATE TABLE `cehior_auth_reports`;
INSERT INTO `cehior_auth_reports` VALUES (1,'Project - Status report',NULL,2,'reportDataProjectStatus',NULL,NULL,1),(2,'Project - Issue details',NULL,2,'reportDataIssueDetails',NULL,NULL,1),(3,'Test Report - Test user',NULL,2,'reportDataTestUser',NULL,NULL,1),(4,'Test Report - Test execution overview',NULL,2,'reportDataTestOverview',NULL,NULL,1);

TRUNCATE TABLE `cehior_auth_reports_selections`;
INSERT INTO `cehior_auth_reports_selections` VALUES ('1',1,'Project',NULL,'select','yes','select concat(`system`, \" / \", client) `group`, proj_name `option` from cehior_auth_proj_header order by `system`, client, proj_name'),('2',1,'Project',NULL,'select','yes','select concat(system, \" / \", client) `group`, proj_name `option` from cehior_auth_proj_header order by `system`, client, proj_name'),('3',1,'Project',NULL,'select','yes','select concat(system, \" / \", client) `group`, proj_name `option` from cehior_auth_proj_header order by `system`, client, proj_name'),('4',1,'Project',NULL,'select','yes','select concat(system, \" / \", client) `group`, proj_name `option` from cehior_auth_proj_header order by `system`, client, proj_name');
