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

CREATE TABLE INTERVALL (
    ID                             bigint NOT NULL
    , NAME                           lvarchar(64)    NOT NULL
    , OWNER_ID                       bigint          NOT NULL
    , START_TIME                     bigint              NULL
    , END_TIME                       bigint              NULL
    , DELAY                          bigint              NULL
    , BASE_INTERVAL                  int8                NULL
    , BASE_INTERVAL_MULTIPLIER       int8                NULL
    , DURATION                       int8                NULL
    , DURATION_MULTIPLIER            int8                NULL
    , SYNC_TIME                      bigint          NOT NULL
    , IS_INVERSE                     integer         NOT NULL
    , IS_MERGE                       integer         NOT NULL
    , EMBEDDED_INT_ID                bigint              NULL
    , SE_ID                          bigint              NULL
    , OBJ_ID                         bigint              NULL
    , OBJ_TYPE                       int8                NULL
    , CREATOR_U_ID                   bigint          NOT NULL
    , CREATE_TS                      bigint          NOT NULL
    , CHANGER_U_ID                   bigint          NOT NULL
    , CHANGE_TS                      bigint          NOT NULL
);
CREATE UNIQUE INDEX PK_INTERVALL
ON INTERVALL(ID);
CREATE VIEW SCI_INTERVALL (ID,NAME,OWNER_ID,START_TIME,END_TIME,DELAY,BASE_INTERVAL,BASE_INTERVAL_MULTIPLIER,DURATION,DURATION_MULTIPLIER,SYNC_TIME,IS_INVERSE,IS_MERGE,EMBEDDED_INT_ID,SE_ID,OBJ_ID,OBJ_TYPE,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS) AS
SELECT 
    ID 
    , NAME                           AS NAME
    , OWNER_ID                       AS OWNER_ID
    , dbinfo ('utc_to_datetime', round(mod(START_TIME, 1125899906842624)/1000)) AS START_TIME
    , dbinfo ('utc_to_datetime', round(mod(END_TIME, 1125899906842624)/1000)) AS END_TIME
    , DELAY                          AS DELAY
    , DECODE (BASE_INTERVAL, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS BASE_INTERVAL
    , BASE_INTERVAL_MULTIPLIER       AS BASE_INTERVAL_MULTIPLIER
    , DECODE (DURATION, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS DURATION
    , DURATION_MULTIPLIER            AS DURATION_MULTIPLIER
    , dbinfo ('utc_to_datetime', round(mod(SYNC_TIME, 1125899906842624)/1000)) AS SYNC_TIME
    , DECODE (IS_INVERSE, 1, 'TRUE', 0, 'FALSE') AS IS_INVERSE
    , DECODE (IS_MERGE, 1, 'TRUE', 0, 'FALSE') AS IS_MERGE
    , EMBEDDED_INT_ID                AS EMBEDDED_INT_ID
    , SE_ID                          AS SE_ID
    , OBJ_ID                         AS OBJ_ID
    , DECODE (OBJ_TYPE, 25, 'DISTRIBUTION', 8, 'USER', 9, 'JOB_DEFINITION', 11, 'RESOURCE', 15, 'SCOPE', 16, 'TRIGGER', 18, 'EVENT', 19, 'INTERVAL', 20, 'SCHEDULE', 22, 'SCHEDULED_EVENT', 28, 'RESOURCE_TEMPLATE', 88, 'INTERVAL_DISPATCHER') AS OBJ_TYPE
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CREATE_TS, 1125899906842624)/1000)) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CHANGE_TS, 1125899906842624)/1000)) AS CHANGE_TS
  FROM INTERVALL;
