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

CREATE TABLE AUDIT_TRAIL (
    ID                             decimal(20) NOT NULL
    , USER_ID                        decimal(20)     NOT NULL
    , TS                             decimal(20)     NOT NULL
    , TXID                           decimal(20)     NOT NULL
    , ACTION                         integer         NOT NULL
    , OBJECT_TYPE                    integer         NOT NULL
    , OBJECT_ID                      decimal(20)     NOT NULL
    , ORIGIN_ID                      decimal(20)     NOT NULL
    , IS_SET_WARNING                 integer         NOT NULL
    , ACTION_INFO                    varchar(1024)   WITH NULL
    , ACTION_COMMENT                 varchar(1024)   WITH NULL
    , CREATOR_U_ID                   decimal(20)     NOT NULL
    , CREATE_TS                      decimal(20)     NOT NULL
    , CHANGER_U_ID                   decimal(20)     NOT NULL
    , CHANGE_TS                      decimal(20)     NOT NULL
);\g
CREATE UNIQUE INDEX PK_AUDIT_TRAIL
ON AUDIT_TRAIL(ID) WITH STRUCTURE = BTREE;\g
CREATE VIEW SCI_AUDIT_TRAIL AS
SELECT 
    ID 
    , USER_ID                        AS USER_ID
    , '01-JAN-1970 00:00:00 GMT' + date(char(decimal((TS- decimal(TS/1125899906842624, 18, 0)*1125899906842624)/1000, 18, 0)) + ' secs') AS TS
    , TXID                           AS TXID
    , CASE ACTION WHEN 1 THEN 'RERUN' WHEN 2 THEN 'RERUN_RECURSIVE' WHEN 3 THEN 'CANCEL' WHEN 4 THEN 'SUSPEND' WHEN 5 THEN 'RESUME' WHEN 6 THEN 'SET_STATE' WHEN 7 THEN 'SET_EXIT_STATE' WHEN 8 THEN 'IGNORE_DEPENDENCY' WHEN 9 THEN 'IGNORE_DEP_RECURSIVE' WHEN 10 THEN 'IGNORE_RESOURCE' WHEN 11 THEN 'KILL' WHEN 12 THEN 'ALTER_RUN_PROGRAM' WHEN 13 THEN 'ALTER_RERUN_PROGRAM' WHEN 14 THEN 'COMMENT_JOB' WHEN 15 THEN 'SUBMITTED' WHEN 16 THEN 'TRIGGER_FAILED' WHEN 17 THEN 'TRIGGER_SUBMIT' WHEN 18 THEN 'JOB_RESTARTABLE' WHEN 19 THEN 'CHANGE_PRIORITY' WHEN 20 THEN 'RENICE' WHEN 21 THEN 'SUBMIT_SUSPENDED' WHEN 22 THEN 'IGNORE_NAMED_RESOURCE' WHEN 23 THEN 'TIMEOUT' WHEN 24 THEN 'SET_RESOURCE_STATE' WHEN 25 THEN 'JOB_IN_ERROR' WHEN 26 THEN 'CLEAR_WARNING' WHEN 27 THEN 'SET_WARNING' WHEN 28 THEN 'JOB_UNREACHABLE' WHEN 29 THEN 'SET_PARAMETERS' WHEN 30 THEN 'DISABLE' WHEN 31 THEN 'ENABLE' WHEN 32 THEN 'CLONE' END AS ACTION
    , CASE OBJECT_TYPE WHEN 17 THEN 'JOB' END AS OBJECT_TYPE
    , OBJECT_ID                      AS OBJECT_ID
    , ORIGIN_ID                      AS ORIGIN_ID
    , CASE IS_SET_WARNING WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS IS_SET_WARNING
    , ACTION_INFO                    AS ACTION_INFO
    , ACTION_COMMENT                 AS ACTION_COMMENT
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , '01-JAN-1970 00:00:00 GMT' + date(char(decimal((CREATE_TS- decimal(CREATE_TS/1125899906842624, 18, 0)*1125899906842624)/1000, 18, 0)) + ' secs') AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , '01-JAN-1970 00:00:00 GMT' + date(char(decimal((CHANGE_TS- decimal(CHANGE_TS/1125899906842624, 18, 0)*1125899906842624)/1000, 18, 0)) + ' secs') AS CHANGE_TS
  FROM AUDIT_TRAIL;\g
CREATE TABLE ARC_AUDIT_TRAIL (
    ID                             decimal(20) NOT NULL
    , USER_ID                        decimal(20)      NULL
    , TS                             decimal(20)      NULL
    , TXID                           decimal(20)      NULL
    , ACTION                         integer          NULL
    , OBJECT_TYPE                    integer          NULL
    , OBJECT_ID                      decimal(20)      NULL
    , ORIGIN_ID                      decimal(20)      NULL
    , IS_SET_WARNING                 integer          NULL
    , ACTION_INFO                    varchar(1024)    NULL
    , ACTION_COMMENT                 varchar(1024)    NULL
    , CREATOR_U_ID                   decimal(20)      NULL
    , CREATE_TS                      decimal(20)      NULL
    , CHANGER_U_ID                   decimal(20)      NULL
    , CHANGE_TS                      decimal(20)      NULL
);\g
