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

CREATE TABLE DEPENDENCY_INSTANCE (
    ID                             decimal(20) NOT NULL
    , DD_ID                          decimal(20)     NOT NULL
    , DEPENDENT_ID                   decimal(20)     NOT NULL
    , DEPENDENT_ID_ORIG              decimal(20)     NOT NULL
    , DEPENDENCY_OPERATION           integer         NOT NULL
    , REQUIRED_ID                    decimal(20)     NOT NULL
    , REQUIRED_SE_ID                 decimal(20)         NULL
    , STATE                          integer         NOT NULL
    , IGNORE                         integer         NOT NULL
    , DI_ID_ORIG                     decimal(20)     NOT NULL
    , SE_VERSION                     decimal(20)     NOT 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
);
CREATE UNIQUE INDEX PK_DEPENDENCY_INSTANCE
ON DEPENDENCY_INSTANCE(ID);
CREATE VIEW SCI_DEPENDENCY_INSTANCE AS
SELECT 
    ID 
    , DD_ID                          AS DD_ID
    , DEPENDENT_ID                   AS DEPENDENT_ID
    , DEPENDENT_ID_ORIG              AS DEPENDENT_ID_ORIG
    , CASE DEPENDENCY_OPERATION WHEN 1 THEN 'AND' WHEN 2 THEN 'OR' END AS DEPENDENCY_OPERATION
    , REQUIRED_ID                    AS REQUIRED_ID
    , REQUIRED_SE_ID                 AS REQUIRED_SE_ID
    , CASE STATE WHEN 0 THEN 'OPEN' WHEN 1 THEN 'FULFILLED' WHEN 2 THEN 'FAILED' WHEN 3 THEN 'BROKEN' WHEN 4 THEN 'DEFERRED' WHEN 8 THEN 'CANCELLED' END AS STATE
    , CASE IGNORE WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' WHEN 2 THEN 'RECURSIVE' END AS IGNORE
    , DI_ID_ORIG                     AS DI_ID_ORIG
    , SE_VERSION                     AS SE_VERSION
    , 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_INSTANCE;
CREATE TABLE ARC_DEPENDENCY_INSTANCE (
    ID                             decimal(20) NOT NULL
    , DD_ID                          decimal(20)      NULL
    , DEPENDENT_ID                   decimal(20)      NULL
    , DEPENDENT_ID_ORIG              decimal(20)      NULL
    , DEPENDENCY_OPERATION           integer          NULL
    , REQUIRED_ID                    decimal(20)      NULL
    , REQUIRED_SE_ID                 decimal(20)      NULL
    , STATE                          integer          NULL
    , IGNORE                         integer          NULL
    , DI_ID_ORIG                     decimal(20)      NULL
    , SE_VERSION                     decimal(20)      NULL
    , CREATOR_U_ID                   decimal(20)      NULL
    , CREATE_TS                      decimal(20)      NULL
    , CHANGER_U_ID                   decimal(20)      NULL
    , CHANGE_TS                      decimal(20)      NULL
);
