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

CREATE TABLE [AUDIT_TRAIL] (
    [ID]                           decimal (38) NOT NULL
    , [USER_ID]                      decimal (38)    NOT NULL
    , [TS]                           decimal (38)    NOT NULL
    , [TXID]                         decimal (38)    NOT NULL
    , [ACTION]                       integer         NOT NULL
    , [OBJECT_TYPE]                  integer         NOT NULL
    , [OBJECT_ID]                    decimal (38)    NOT NULL
    , [ORIGIN_ID]                    decimal (38)    NOT NULL
    , [IS_SET_WARNING]               integer         NOT NULL
    , [ACTION_INFO]                  varchar(1024)       NULL
    , [INFO_ID]                      decimal (38)        NULL
    , [ACTION_COMMENT]               varchar(1024)       NULL
    , [CREATOR_U_ID]                 decimal (38)    NOT NULL
    , [CREATE_TS]                    decimal (38)    NOT NULL
    , [CHANGER_U_ID]                 decimal (38)    NOT NULL
    , [CHANGE_TS]                    decimal (38)    NOT NULL
);
go
CREATE UNIQUE INDEX PK_AUDIT_TRAIL
ON [AUDIT_TRAIL]([ID]);
go
CREATE VIEW SCI_AUDIT_TRAIL AS
SELECT 
    ID 
    , [USER_ID]                      AS [USER_ID]
    , dateadd(ss, round(([TS] % 1125899906842624)/1000, 0), '1970-01-01') 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' WHEN 33 THEN 'APPROVE' WHEN 34 THEN 'REJECT' WHEN 35 THEN 'APPROVAL_REQUEST' WHEN 36 THEN 'REVIEW_REQUEST' WHEN 37 THEN 'KILL_RECURSIVE' 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]
    , [INFO_ID]                      AS [INFO_ID]
    , [ACTION_COMMENT]               AS [ACTION_COMMENT]
    , [CREATOR_U_ID]                 AS [CREATOR_U_ID]
    , dateadd(ss, round(([CREATE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CREATE_TS]
    , [CHANGER_U_ID]                 AS [CHANGER_U_ID]
    , dateadd(ss, round(([CHANGE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CHANGE_TS]
  FROM [AUDIT_TRAIL];
go
CREATE TABLE ARC_AUDIT_TRAIL (
    ID                             decimal (38) NOT NULL
    , [USER_ID]                      decimal (38)     NULL
    , [TS]                           decimal (38)     NULL
    , [TXID]                         decimal (38)     NULL
    , [ACTION]                       integer          NULL
    , [OBJECT_TYPE]                  integer          NULL
    , [OBJECT_ID]                    decimal (38)     NULL
    , [ORIGIN_ID]                    decimal (38)     NULL
    , [IS_SET_WARNING]               integer          NULL
    , [ACTION_INFO]                  varchar(1024)    NULL
    , [INFO_ID]                      decimal (38)     NULL
    , [ACTION_COMMENT]               varchar(1024)    NULL
    , [CREATOR_U_ID]                 decimal (38)     NULL
    , [CREATE_TS]                    decimal (38)     NULL
    , [CHANGER_U_ID]                 decimal (38)     NULL
    , [CHANGE_TS]                    decimal (38)     NULL
);
go
