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

CREATE TABLE INTERVALL (
    ID                             bigint NOT NULL
    , NAME                           varchar(64)     NOT NULL
    , OWNER_ID                       bigint          NOT NULL
    , START_TIME                     bigint              NULL
    , END_TIME                       bigint              NULL
    , DELAY                          bigint              NULL
    , BASE_INTERVAL                  integer             NULL
    , BASE_INTERVAL_MULTIPLIER       integer             NULL
    , DURATION                       integer             NULL
    , DURATION_MULTIPLIER            integer             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                       integer             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 AS
SELECT 
    ID 
    , NAME                           AS NAME
    , OWNER_ID                       AS OWNER_ID
    , timestamp('1970-01-01-00.00.00') + int(mod(START_TIME, 1125899906842624)/1000) seconds AS START_TIME
    , timestamp('1970-01-01-00.00.00') + int(mod(END_TIME, 1125899906842624)/1000) seconds 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
    , timestamp('1970-01-01-00.00.00') + int(mod(SYNC_TIME, 1125899906842624)/1000) seconds 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
    , timestamp('1970-01-01-00.00.00') + int(mod(CREATE_TS, 1125899906842624)/1000) seconds AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , timestamp('1970-01-01-00.00.00') + int(mod(CHANGE_TS, 1125899906842624)/1000) seconds AS CHANGE_TS
  FROM INTERVALL;
