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

CREATE TABLE [INTERVALL] (
    [ID]                           decimal (38) NOT NULL
    , [NAME]                         varchar(64)     NOT NULL
    , [OWNER_ID]                     decimal (38)    NOT NULL
    , [START_TIME]                   decimal (38)        NULL
    , [END_TIME]                     decimal (38)        NULL
    , [DELAY]                        decimal (38)        NULL
    , [BASE_INTERVAL]                integer             NULL
    , [BASE_INTERVAL_MULTIPLIER]     integer             NULL
    , [DURATION]                     integer             NULL
    , [DURATION_MULTIPLIER]          integer             NULL
    , [SYNC_TIME]                    decimal (38)    NOT NULL
    , [IS_INVERSE]                   integer         NOT NULL
    , [IS_MERGE]                     integer         NOT NULL
    , [EMBEDDED_INT_ID]              decimal (38)        NULL
    , [SE_ID]                        decimal (38)        NULL
    , [OBJ_ID]                       decimal (38)        NULL
    , [OBJ_TYPE]                     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_INTERVALL
ON [INTERVALL]([ID]);
go
CREATE VIEW SCI_INTERVALL AS
SELECT 
    ID 
    , [NAME]                         AS [NAME]
    , [OWNER_ID]                     AS [OWNER_ID]
    , dateadd(ss, round(([START_TIME] % 1125899906842624)/1000, 0), '1970-01-01') AS [START_TIME]
    , dateadd(ss, round(([END_TIME] % 1125899906842624)/1000, 0), '1970-01-01') AS [END_TIME]
    , [DELAY]                        AS [DELAY]
    , CASE [BASE_INTERVAL] 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 [BASE_INTERVAL]
    , [BASE_INTERVAL_MULTIPLIER]     AS [BASE_INTERVAL_MULTIPLIER]
    , CASE [DURATION] 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 [DURATION]
    , [DURATION_MULTIPLIER]          AS [DURATION_MULTIPLIER]
    , dateadd(ss, round(([SYNC_TIME] % 1125899906842624)/1000, 0), '1970-01-01') AS [SYNC_TIME]
    , CASE [IS_INVERSE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_INVERSE]
    , CASE [IS_MERGE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_MERGE]
    , [EMBEDDED_INT_ID]              AS [EMBEDDED_INT_ID]
    , [SE_ID]                        AS [SE_ID]
    , [OBJ_ID]                       AS [OBJ_ID]
    , CASE [OBJ_TYPE] WHEN 25 THEN 'DISTRIBUTION' WHEN 8 THEN 'USER' WHEN 9 THEN 'JOB_DEFINITION' WHEN 11 THEN 'RESOURCE' WHEN 15 THEN 'SCOPE' WHEN 16 THEN 'TRIGGER' WHEN 18 THEN 'EVENT' WHEN 19 THEN 'INTERVAL' WHEN 20 THEN 'SCHEDULE' WHEN 22 THEN 'SCHEDULED_EVENT' WHEN 28 THEN 'RESOURCE_TEMPLATE' WHEN 88 THEN 'INTERVAL_DISPATCHER' END AS [OBJ_TYPE]
    , [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 [INTERVALL];
go
