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

CREATE TABLE AUDIT_TRAIL (
    ID                             bigint NOT NULL
    , USER_ID                        bigint          NOT NULL
    , TS                             bigint          NOT NULL
    , TXID                           bigint          NOT NULL
    , ACTION                         integer         NOT NULL
    , OBJECT_TYPE                    integer         NOT NULL
    , OBJECT_ID                      bigint          NOT NULL
    , ORIGIN_ID                      bigint          NOT NULL
    , IS_SET_WARNING                 integer         NOT NULL
    , ACTION_INFO                    varchar(1024)       NULL
    , INFO_ID                        bigint              NULL
    , ACTION_COMMENT                 varchar(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_AUDIT_TRAIL
ON AUDIT_TRAIL(ID);
CREATE VIEW SCI_AUDIT_TRAIL AS
SELECT 
    ID 
    , USER_ID                        AS USER_ID
    , timestamp('1970-01-01-00.00.00') + int(mod(TS, 1125899906842624)/1000) seconds AS TS
    , TXID                           AS TXID
    , DECODE (ACTION, 1, 'RERUN', 2, 'RERUN_RECURSIVE', 3, 'CANCEL', 4, 'SUSPEND', 5, 'RESUME', 6, 'SET_STATE', 7, 'SET_EXIT_STATE', 8, 'IGNORE_DEPENDENCY', 9, 'IGNORE_DEP_RECURSIVE', 10, 'IGNORE_RESOURCE', 11, 'KILL', 12, 'ALTER_RUN_PROGRAM', 13, 'ALTER_RERUN_PROGRAM', 14, 'COMMENT_JOB', 15, 'SUBMITTED', 16, 'TRIGGER_FAILED', 17, 'TRIGGER_SUBMIT', 18, 'JOB_RESTARTABLE', 19, 'CHANGE_PRIORITY', 20, 'RENICE', 21, 'SUBMIT_SUSPENDED', 22, 'IGNORE_NAMED_RESOURCE', 23, 'TIMEOUT', 24, 'SET_RESOURCE_STATE', 25, 'JOB_IN_ERROR', 26, 'CLEAR_WARNING', 27, 'SET_WARNING', 28, 'JOB_UNREACHABLE', 29, 'SET_PARAMETERS', 30, 'DISABLE', 31, 'ENABLE', 32, 'CLONE', 33, 'APPROVE', 34, 'REJECT', 35, 'APPROVAL_REQUEST', 36, 'REVIEW_REQUEST', 37, 'KILL_RECURSIVE') AS ACTION
    , DECODE (OBJECT_TYPE, 17, 'JOB') AS OBJECT_TYPE
    , OBJECT_ID                      AS OBJECT_ID
    , ORIGIN_ID                      AS ORIGIN_ID
    , DECODE (IS_SET_WARNING, 1, 'TRUE', 0, 'FALSE') AS IS_SET_WARNING
    , ACTION_INFO                    AS ACTION_INFO
    , INFO_ID                        AS INFO_ID
    , ACTION_COMMENT                 AS ACTION_COMMENT
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , timestamp('1970-01-01-00.00.00') + int(mod(CREATE_TS, 1125899906842624)/1000) seconds AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , timestamp('1970-01-01-00.00.00') + int(mod(CHANGE_TS, 1125899906842624)/1000) seconds AS CHANGE_TS
  FROM AUDIT_TRAIL;
CREATE TABLE ARC_AUDIT_TRAIL (
    ID                             bigint NOT NULL
    , USER_ID                        bigint           NULL
    , TS                             bigint           NULL
    , TXID                           bigint           NULL
    , ACTION                         integer          NULL
    , OBJECT_TYPE                    integer          NULL
    , OBJECT_ID                      bigint           NULL
    , ORIGIN_ID                      bigint           NULL
    , IS_SET_WARNING                 integer          NULL
    , ACTION_INFO                    varchar(1024)    NULL
    , INFO_ID                        bigint           NULL
    , ACTION_COMMENT                 varchar(1024)    NULL
    , CREATOR_U_ID                   bigint           NULL
    , CREATE_TS                      bigint           NULL
    , CHANGER_U_ID                   bigint           NULL
    , CHANGE_TS                      bigint           NULL
);
