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

CREATE TABLE [SCHEDULING_HIERARCHY] (
    [ID]                           decimal (38) NOT NULL
    , [SE_PARENT_ID]                 decimal (38)        NULL
    , [SE_CHILD_ID]                  decimal (38)        NULL
    , [ALIAS_NAME]                   varchar(64)         NULL
    , [IS_STATIC]                    integer         NOT NULL
    , [IS_DISABLED]                  integer         NOT NULL
    , [PRIORITY]                     integer         NOT NULL
    , [SUSPEND]                      integer         NOT NULL
    , [RESUME_AT]                    varchar(20)         NULL
    , [RESUME_IN]                    integer             NULL
    , [RESUME_BASE]                  integer             NULL
    , [MERGE_MODE]                   integer         NOT NULL
    , [ESTP_ID]                      decimal (38)        NULL
    , [INT_ID]                       decimal (38)        NULL
    , [ENABLE_CONDITION]             varchar(2048)       NULL
    , [ENABLE_MODE]                  integer         NOT 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
    , [VALID_FROM]                 decimal (38) NOT NULL
    , [VALID_TO]                   decimal (38) NOT NULL
);
go
CREATE INDEX PK_SCHEDULING_HIERARCHY
ON [SCHEDULING_HIERARCHY]([ID]);
go
CREATE VIEW SCI_C_SCHEDULING_HIERARCHY AS
SELECT 
    ID 
    , [SE_PARENT_ID]                 AS [SE_PARENT_ID]
    , [SE_CHILD_ID]                  AS [SE_CHILD_ID]
    , [ALIAS_NAME]                   AS [ALIAS_NAME]
    , CASE [IS_STATIC] WHEN 1 THEN 'STATIC' WHEN 0 THEN 'DYNAMIC' END AS [IS_STATIC]
    , CASE [IS_DISABLED] WHEN 0 THEN 'ENABLED' WHEN 1 THEN 'DISABLED' END AS [IS_DISABLED]
    , [PRIORITY]                     AS [PRIORITY]
    , CASE [SUSPEND] WHEN 1 THEN 'CHILDSUSPEND' WHEN 2 THEN 'NOSUSPEND' WHEN 3 THEN 'SUSPEND' END AS [SUSPEND]
    , [RESUME_AT]                    AS [RESUME_AT]
    , [RESUME_IN]                    AS [RESUME_IN]
    , CASE [RESUME_BASE] 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 [RESUME_BASE]
    , CASE [MERGE_MODE] WHEN 1 THEN 'MERGE_LOCAL' WHEN 2 THEN 'MERGE_GLOBAL' WHEN 3 THEN 'NOMERGE' WHEN 4 THEN 'FAILURE' END AS [MERGE_MODE]
    , [ESTP_ID]                      AS [ESTP_ID]
    , [INT_ID]                       AS [INT_ID]
    , [ENABLE_CONDITION]             AS [ENABLE_CONDITION]
    , CASE [ENABLE_MODE] WHEN 1 THEN 'AND' WHEN 2 THEN 'OR' END AS [ENABLE_MODE]
    , [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 [SCHEDULING_HIERARCHY]
 WHERE VALID_TO = 9223372036854775807;
go
CREATE VIEW SCI_V_SCHEDULING_HIERARCHY AS
SELECT 
    ID 
    , [SE_PARENT_ID]                 AS [SE_PARENT_ID]
    , [SE_CHILD_ID]                  AS [SE_CHILD_ID]
    , [ALIAS_NAME]                   AS [ALIAS_NAME]
    , CASE [IS_STATIC] WHEN 1 THEN 'STATIC' WHEN 0 THEN 'DYNAMIC' END AS [IS_STATIC]
    , CASE [IS_DISABLED] WHEN 0 THEN 'ENABLED' WHEN 1 THEN 'DISABLED' END AS [IS_DISABLED]
    , [PRIORITY]                     AS [PRIORITY]
    , CASE [SUSPEND] WHEN 1 THEN 'CHILDSUSPEND' WHEN 2 THEN 'NOSUSPEND' WHEN 3 THEN 'SUSPEND' END AS [SUSPEND]
    , [RESUME_AT]                    AS [RESUME_AT]
    , [RESUME_IN]                    AS [RESUME_IN]
    , CASE [RESUME_BASE] 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 [RESUME_BASE]
    , CASE [MERGE_MODE] WHEN 1 THEN 'MERGE_LOCAL' WHEN 2 THEN 'MERGE_GLOBAL' WHEN 3 THEN 'NOMERGE' WHEN 4 THEN 'FAILURE' END AS [MERGE_MODE]
    , [ESTP_ID]                      AS [ESTP_ID]
    , [INT_ID]                       AS [INT_ID]
    , [ENABLE_CONDITION]             AS [ENABLE_CONDITION]
    , CASE [ENABLE_MODE] WHEN 1 THEN 'AND' WHEN 2 THEN 'OR' END AS [ENABLE_MODE]
    , [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]
    , VALID_FROM
    , VALID_TO
  FROM [SCHEDULING_HIERARCHY];
go
