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

CREATE TABLE DEPENDENCY_DEFINITION (
    ID                             decimal(20) NOT NULL
    , SE_DEPENDENT_ID                decimal(20)     NOT NULL
    , SE_REQUIRED_ID                 decimal(20)     NOT NULL
    , NAME                           varchar(64)         NULL
    , UNRESOLVED_HANDLING            integer         NOT NULL
    , DMODE                          integer         NOT NULL
    , STATE_SELECTION                integer         NOT NULL
    , CONDITION                      varchar(1024)       NULL
    , RESOLVE_MODE                   integer         NOT NULL
    , EXPIRED_AMOUNT                 integer             NULL
    , EXPIRED_BASE                   integer             NULL
    , SELECT_CONDITION               varchar(1024)       NULL
    , CREATOR_U_ID                   decimal(20)     NOT NULL
    , CREATE_TS                      decimal(20)     NOT NULL
    , CHANGER_U_ID                   decimal(20)     NOT NULL
    , CHANGE_TS                      decimal(20)     NOT NULL
    , VALID_FROM                   decimal(20) NOT NULL
    , VALID_TO                     decimal(20) NOT NULL
);
CREATE INDEX PK_DEPENDENCY_DEFINITION
ON DEPENDENCY_DEFINITION(ID);
CREATE VIEW SCI_C_DEPENDENCY_DEFINITION AS
SELECT 
    ID 
    , SE_DEPENDENT_ID                AS SE_DEPENDENT_ID
    , SE_REQUIRED_ID                 AS SE_REQUIRED_ID
    , NAME                           AS NAME
    , CASE UNRESOLVED_HANDLING WHEN 1 THEN 'IGNORE' WHEN 2 THEN 'ERROR' WHEN 3 THEN 'SUSPEND' WHEN 4 THEN 'DEFER' WHEN 5 THEN 'DEFER_IGNORE' END AS UNRESOLVED_HANDLING
    , CASE DMODE WHEN 1 THEN 'ALL_FINAL' WHEN 2 THEN 'JOB_FINAL' END AS DMODE
    , CASE STATE_SELECTION WHEN 0 THEN 'FINAL' WHEN 1 THEN 'ALL_REACHABLE' WHEN 2 THEN 'UNREACHABLE' WHEN 3 THEN 'DEFAULT' END AS STATE_SELECTION
    , CONDITION                      AS CONDITION
    , CASE RESOLVE_MODE WHEN 0 THEN 'INTERNAL' WHEN 1 THEN 'EXTERNAL' WHEN 2 THEN 'BOTH' END AS RESOLVE_MODE
    , EXPIRED_AMOUNT                 AS EXPIRED_AMOUNT
    , CASE EXPIRED_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 EXPIRED_BASE
    , SELECT_CONDITION               AS SELECT_CONDITION
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , timestamptz 'epoch' + cast(to_char(mod(CREATE_TS, 1125899906842624)/1000, '999999999999') as interval) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , timestamptz 'epoch' + cast(to_char(mod(CHANGE_TS, 1125899906842624)/1000, '999999999999') as interval) AS CHANGE_TS
  FROM DEPENDENCY_DEFINITION
 WHERE VALID_TO = 9223372036854775807;
CREATE VIEW SCI_V_DEPENDENCY_DEFINITION AS
SELECT 
    ID 
    , SE_DEPENDENT_ID                AS SE_DEPENDENT_ID
    , SE_REQUIRED_ID                 AS SE_REQUIRED_ID
    , NAME                           AS NAME
    , CASE UNRESOLVED_HANDLING WHEN 1 THEN 'IGNORE' WHEN 2 THEN 'ERROR' WHEN 3 THEN 'SUSPEND' WHEN 4 THEN 'DEFER' WHEN 5 THEN 'DEFER_IGNORE' END AS UNRESOLVED_HANDLING
    , CASE DMODE WHEN 1 THEN 'ALL_FINAL' WHEN 2 THEN 'JOB_FINAL' END AS DMODE
    , CASE STATE_SELECTION WHEN 0 THEN 'FINAL' WHEN 1 THEN 'ALL_REACHABLE' WHEN 2 THEN 'UNREACHABLE' WHEN 3 THEN 'DEFAULT' END AS STATE_SELECTION
    , CONDITION                      AS CONDITION
    , CASE RESOLVE_MODE WHEN 0 THEN 'INTERNAL' WHEN 1 THEN 'EXTERNAL' WHEN 2 THEN 'BOTH' END AS RESOLVE_MODE
    , EXPIRED_AMOUNT                 AS EXPIRED_AMOUNT
    , CASE EXPIRED_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 EXPIRED_BASE
    , SELECT_CONDITION               AS SELECT_CONDITION
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , timestamptz 'epoch' + cast(to_char(mod(CREATE_TS, 1125899906842624)/1000, '999999999999') as interval) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , timestamptz 'epoch' + cast(to_char(mod(CHANGE_TS, 1125899906842624)/1000, '999999999999') as interval) AS CHANGE_TS
    , VALID_FROM
    , VALID_TO
  FROM DEPENDENCY_DEFINITION;
