-- Copyright (C) 2001,2002 topIT Informationstechnologie GmbH
-- Copyright (C) 2003-2014 independIT Integrative Technologies GmbH

CREATE TABLE SYSTEM_MESSAGE (
    ID                             bigint NOT NULL
    , MSG_TYPE                       int8            NOT NULL
    , SME_ID                         bigint          NOT NULL
    , MASTER_ID                      bigint          NOT NULL
    , OPERATION                      int8            NOT NULL
    , IS_MANDATORY                   integer         NOT NULL
    , REQUEST_U_ID                   bigint          NOT NULL
    , REQUEST_TS                     bigint          NOT NULL
    , REQUEST_MSG                    lvarchar(512)       NULL
    , ADDITIONAL_LONG                bigint              NULL
    , ADDITIONAL_BOOL                integer             NULL
    , SECOND_LONG                    bigint              NULL
    , COMMENT                        lvarchar(1024)      NULL
    , CREATOR_U_ID                   bigint          NOT NULL
    , CREATE_TS                      bigint          NOT NULL
    , CHANGER_U_ID                   bigint          NOT NULL
    , CHANGE_TS                      bigint          NOT NULL
);
CREATE UNIQUE INDEX PK_SYSTEM_MESSAGE
ON SYSTEM_MESSAGE(ID);
CREATE VIEW SCI_SYSTEM_MESSAGE (ID,MSG_TYPE,SME_ID,MASTER_ID,OPERATION,IS_MANDATORY,REQUEST_U_ID,REQUEST_TS,REQUEST_MSG,ADDITIONAL_LONG,ADDITIONAL_BOOL,SECOND_LONG,COMMENT,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS) AS
SELECT 
    ID 
    , DECODE (MSG_TYPE, 1, 'APPROVAL') AS MSG_TYPE
    , SME_ID                         AS SME_ID
    , MASTER_ID                      AS MASTER_ID
    , DECODE (OPERATION, 1, 'CANCEL', 2, 'RERUN', 3, 'ENABLE', 4, 'SET_STATE', 5, 'IGN_DEPENDENCY', 6, 'IGN_RESOURCE', 7, 'CLONE', 11, 'MODIFY_PARAMETER', 12, 'KILL', 23, 'DISABLE', 13, 'SET_JOB_STATE') AS OPERATION
    , DECODE (IS_MANDATORY, 1, 'TRUE', 0, 'FALSE') AS IS_MANDATORY
    , REQUEST_U_ID                   AS REQUEST_U_ID
    , dbinfo ('utc_to_datetime', round(mod(REQUEST_TS, 1125899906842624)/1000)) AS REQUEST_TS
    , REQUEST_MSG                    AS REQUEST_MSG
    , ADDITIONAL_LONG                AS ADDITIONAL_LONG
    , DECODE (ADDITIONAL_BOOL, 1, 'TRUE', 0, 'FALSE') AS ADDITIONAL_BOOL
    , SECOND_LONG                    AS SECOND_LONG
    , COMMENT                        AS COMMENT
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CREATE_TS, 1125899906842624)/1000)) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CHANGE_TS, 1125899906842624)/1000)) AS CHANGE_TS
  FROM SYSTEM_MESSAGE;
