-- 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
) ENGINE = INNODB;
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`
    , from_unixtime((`CREATE_TS` & ~1125899906842624)/1000) AS `CREATE_TS`
    , `CHANGER_U_ID`                 AS `CHANGER_U_ID`
    , from_unixtime((`CHANGE_TS` & ~1125899906842624)/1000) 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`
    , from_unixtime((`CREATE_TS` & ~1125899906842624)/1000) AS `CREATE_TS`
    , `CHANGER_U_ID`                 AS `CHANGER_U_ID`
    , from_unixtime((`CHANGE_TS` & ~1125899906842624)/1000) AS `CHANGE_TS`
    , VALID_FROM
    , VALID_TO
  FROM `DEPENDENCY_DEFINITION`;
