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

CREATE TABLE TRIGGER_DEFINITION (
    ID                             bigint NOT NULL
    , NAME                           lvarchar(64)    NOT NULL
    , FIRE_ID                        bigint          NOT NULL
    , OBJECT_TYPE                    int8            NOT NULL
    , SE_ID                          bigint          NOT NULL
    , MAIN_SE_ID                     bigint              NULL
    , PARENT_SE_ID                   bigint              NULL
    , IS_ACTIVE                      integer         NOT NULL
    , IS_INVERSE                     integer         NOT NULL
    , ACTION                         int8            NOT NULL
    , TYPE                           int8            NOT NULL
    , IS_MASTER                      integer         NOT NULL
    , IS_SUSPEND                     integer         NOT NULL
    , IS_CREATE                      integer             NULL
    , IS_CHANGE                      integer             NULL
    , IS_DELETE                      integer             NULL
    , IS_GROUP                       integer             NULL
    , RESUME_AT                      lvarchar(20)        NULL
    , RESUME_IN                      int8                NULL
    , RESUME_BASE                    int8                NULL
    , IS_WARN_ON_LIMIT               integer         NOT NULL
    , LIMIT_STATE                    bigint              NULL
    , MAX_RETRY                      int8            NOT NULL
    , SUBMIT_OWNER_ID                bigint              NULL
    , CONDITION                      lvarchar(1024)      NULL
    , CHECK_AMOUNT                   int8                NULL
    , CHECK_BASE                     int8                NULL
    , CREATOR_U_ID                   bigint          NOT NULL
    , CREATE_TS                      bigint          NOT NULL
    , CHANGER_U_ID                   bigint          NOT NULL
    , CHANGE_TS                      bigint          NOT NULL
    , VALID_FROM                   bigint NOT NULL
    , VALID_TO                     bigint NOT NULL
);
CREATE INDEX PK_TRIGGER_DEFINITION
ON TRIGGER_DEFINITION(ID);
CREATE VIEW SCI_C_TRIGGER_DEFINITION (ID,NAME,FIRE_ID,OBJECT_TYPE,SE_ID,MAIN_SE_ID,PARENT_SE_ID,IS_ACTIVE,IS_INVERSE,ACTION,TYPE,IS_MASTER,IS_SUSPEND,IS_CREATE,IS_CHANGE,IS_DELETE,IS_GROUP,RESUME_AT,RESUME_IN,RESUME_BASE,IS_WARN_ON_LIMIT,LIMIT_STATE,MAX_RETRY,SUBMIT_OWNER_ID,CONDITION,CHECK_AMOUNT,CHECK_BASE,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS) AS
SELECT 
    ID 
    , NAME                           AS NAME
    , FIRE_ID                        AS FIRE_ID
    , DECODE (OBJECT_TYPE, 0, 'JOB_DEFINITION', 1, 'RESOURCE', 2, 'NAMED_RESOURCE', 3, 'OBJECT_MONITOR') AS OBJECT_TYPE
    , SE_ID                          AS SE_ID
    , MAIN_SE_ID                     AS MAIN_SE_ID
    , PARENT_SE_ID                   AS PARENT_SE_ID
    , DECODE (IS_ACTIVE, 1, 'TRUE', 0, 'FALSE') AS IS_ACTIVE
    , DECODE (IS_INVERSE, 1, 'TRUE', 0, 'FALSE') AS IS_INVERSE
    , DECODE (ACTION, 0, 'SUBMIT', 1, 'RERUN') AS ACTION
    , DECODE (TYPE, 0, 'IMMEDIATE_LOCAL', 2, 'BEFORE_FINAL', 3, 'AFTER_FINAL', 1, 'IMMEDIATE_MERGE', 4, 'FINISH_CHILD', 5, 'UNTIL_FINISHED', 6, 'UNTIL_FINAL', 7, 'WARNING') AS TYPE
    , DECODE (IS_MASTER, 1, 'MASTER', 0, 'NOMASTER') AS IS_MASTER
    , DECODE (IS_SUSPEND, 1, 'SUSPEND', 0, 'NOSUSPEND') AS IS_SUSPEND
    , DECODE (IS_CREATE, 1, 'TRUE', 0, 'FALSE') AS IS_CREATE
    , DECODE (IS_CHANGE, 1, 'TRUE', 0, 'FALSE') AS IS_CHANGE
    , DECODE (IS_DELETE, 1, 'TRUE', 0, 'FALSE') AS IS_DELETE
    , DECODE (IS_GROUP, 1, 'TRUE', 0, 'FALSE') AS IS_GROUP
    , RESUME_AT                      AS RESUME_AT
    , RESUME_IN                      AS RESUME_IN
    , DECODE (RESUME_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS RESUME_BASE
    , DECODE (IS_WARN_ON_LIMIT, 1, 'TRUE', 0, 'FALSE') AS IS_WARN_ON_LIMIT
    , LIMIT_STATE                    AS LIMIT_STATE
    , MAX_RETRY                      AS MAX_RETRY
    , SUBMIT_OWNER_ID                AS SUBMIT_OWNER_ID
    , CONDITION                      AS CONDITION
    , CHECK_AMOUNT                   AS CHECK_AMOUNT
    , DECODE (CHECK_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS CHECK_BASE
    , 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 TRIGGER_DEFINITION
 WHERE VALID_TO = 9223372036854775807;
CREATE VIEW SCI_V_TRIGGER_DEFINITION (ID,NAME,FIRE_ID,OBJECT_TYPE,SE_ID,MAIN_SE_ID,PARENT_SE_ID,IS_ACTIVE,IS_INVERSE,ACTION,TYPE,IS_MASTER,IS_SUSPEND,IS_CREATE,IS_CHANGE,IS_DELETE,IS_GROUP,RESUME_AT,RESUME_IN,RESUME_BASE,IS_WARN_ON_LIMIT,LIMIT_STATE,MAX_RETRY,SUBMIT_OWNER_ID,CONDITION,CHECK_AMOUNT,CHECK_BASE,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS, VALID_FROM, VALID_TO) AS
SELECT 
    ID 
    , NAME                           AS NAME
    , FIRE_ID                        AS FIRE_ID
    , DECODE (OBJECT_TYPE, 0, 'JOB_DEFINITION', 1, 'RESOURCE', 2, 'NAMED_RESOURCE', 3, 'OBJECT_MONITOR') AS OBJECT_TYPE
    , SE_ID                          AS SE_ID
    , MAIN_SE_ID                     AS MAIN_SE_ID
    , PARENT_SE_ID                   AS PARENT_SE_ID
    , DECODE (IS_ACTIVE, 1, 'TRUE', 0, 'FALSE') AS IS_ACTIVE
    , DECODE (IS_INVERSE, 1, 'TRUE', 0, 'FALSE') AS IS_INVERSE
    , DECODE (ACTION, 0, 'SUBMIT', 1, 'RERUN') AS ACTION
    , DECODE (TYPE, 0, 'IMMEDIATE_LOCAL', 2, 'BEFORE_FINAL', 3, 'AFTER_FINAL', 1, 'IMMEDIATE_MERGE', 4, 'FINISH_CHILD', 5, 'UNTIL_FINISHED', 6, 'UNTIL_FINAL', 7, 'WARNING') AS TYPE
    , DECODE (IS_MASTER, 1, 'MASTER', 0, 'NOMASTER') AS IS_MASTER
    , DECODE (IS_SUSPEND, 1, 'SUSPEND', 0, 'NOSUSPEND') AS IS_SUSPEND
    , DECODE (IS_CREATE, 1, 'TRUE', 0, 'FALSE') AS IS_CREATE
    , DECODE (IS_CHANGE, 1, 'TRUE', 0, 'FALSE') AS IS_CHANGE
    , DECODE (IS_DELETE, 1, 'TRUE', 0, 'FALSE') AS IS_DELETE
    , DECODE (IS_GROUP, 1, 'TRUE', 0, 'FALSE') AS IS_GROUP
    , RESUME_AT                      AS RESUME_AT
    , RESUME_IN                      AS RESUME_IN
    , DECODE (RESUME_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS RESUME_BASE
    , DECODE (IS_WARN_ON_LIMIT, 1, 'TRUE', 0, 'FALSE') AS IS_WARN_ON_LIMIT
    , LIMIT_STATE                    AS LIMIT_STATE
    , MAX_RETRY                      AS MAX_RETRY
    , SUBMIT_OWNER_ID                AS SUBMIT_OWNER_ID
    , CONDITION                      AS CONDITION
    , CHECK_AMOUNT                   AS CHECK_AMOUNT
    , DECODE (CHECK_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS CHECK_BASE
    , 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
    , VALID_FROM
    , VALID_TO
  FROM TRIGGER_DEFINITION;
