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

CREATE TABLE [SCHEDULED_EVENT] (
    [ID]                           decimal (38) NOT NULL
    , [OWNER_ID]                     decimal (38)    NOT NULL
    , [SCE_ID]                       decimal (38)    NOT NULL
    , [EVT_ID]                       decimal (38)    NOT NULL
    , [ACTIVE]                       integer         NOT NULL
    , [BROKEN]                       integer         NOT NULL
    , [ERROR_CODE]                   varchar(32)         NULL
    , [ERROR_MSG]                    varchar(256)        NULL
    , [LAST_START_TIME]              decimal (38)        NULL
    , [NEXT_START_TIME]              decimal (38)        NULL
    , [NEXT_IS_TRIGGER]              integer             NULL
    , [BACKLOG_HANDLING]             integer         NOT NULL
    , [SUSPEND_LIMIT]                integer             NULL
    , [SUSPEND_LIMIT_MULTIPLIER]     integer             NULL
    , [IS_CALENDAR]                  integer         NOT NULL
    , [CALENDAR_HORIZON]             integer             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_SCHEDULED_EVENT
ON [SCHEDULED_EVENT]([ID]);
go
CREATE VIEW SCI_SCHEDULED_EVENT AS
SELECT 
    ID 
    , [OWNER_ID]                     AS [OWNER_ID]
    , [SCE_ID]                       AS [SCE_ID]
    , [EVT_ID]                       AS [EVT_ID]
    , CASE [ACTIVE] WHEN 1 THEN 'ACTIVE' WHEN 0 THEN 'INACTIVE' END AS [ACTIVE]
    , CASE [BROKEN] WHEN 1 THEN 'BROKEN' WHEN 0 THEN 'NOBROKEN' END AS [BROKEN]
    , [ERROR_CODE]                   AS [ERROR_CODE]
    , [ERROR_MSG]                    AS [ERROR_MSG]
    , dateadd(ss, round(([LAST_START_TIME] % 1125899906842624)/1000, 0), '1970-01-01') AS [LAST_START_TIME]
    , dateadd(ss, round(([NEXT_START_TIME] % 1125899906842624)/1000, 0), '1970-01-01') AS [NEXT_START_TIME]
    , CASE [NEXT_IS_TRIGGER] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [NEXT_IS_TRIGGER]
    , CASE [BACKLOG_HANDLING] WHEN 0 THEN 'NONE' WHEN 1 THEN 'LAST' WHEN 2 THEN 'ALL' END AS [BACKLOG_HANDLING]
    , CASE [SUSPEND_LIMIT] WHEN 0 THEN 'MINUTE' WHEN 1 THEN 'HOUR' WHEN 2 THEN 'DAY' WHEN 3 THEN 'WEEK' WHEN 4 THEN 'MONTH' WHEN 5 THEN 'YEAR' END AS [SUSPEND_LIMIT]
    , [SUSPEND_LIMIT_MULTIPLIER]     AS [SUSPEND_LIMIT_MULTIPLIER]
    , CASE [IS_CALENDAR] WHEN 1 THEN 'ACTIVE' WHEN 0 THEN 'INACTIVE' END AS [IS_CALENDAR]
    , [CALENDAR_HORIZON]             AS [CALENDAR_HORIZON]
    , [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 [SCHEDULED_EVENT];
go
