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

CREATE TABLE `EXIT_STATE` (
    `ID`                           decimal(20) NOT NULL
    , `PREFERENCE`                   integer         NOT NULL
    , `IS_FINAL`                     integer         NOT NULL
    , `IS_RESTARTABLE`               integer         NOT NULL
    , `IS_UNREACHABLE`               integer         NOT NULL
    , `IS_BROKEN`                    integer         NOT NULL
    , `IS_BATCH_DEFAULT`             integer         NOT NULL
    , `IS_DEPENDENCY_DEFAULT`        integer         NOT NULL
    , `ESP_ID`                       decimal(20)     NOT NULL
    , `ESD_ID`                       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
    , `VALID_FROM`                 decimal(20) NOT NULL
    , `VALID_TO`                   decimal(20) NOT NULL
) ENGINE = INNODB;
CREATE INDEX PK_EXIT_STATE
ON `EXIT_STATE`(`ID`);
CREATE VIEW SCI_C_EXIT_STATE AS
SELECT 
    ID 
    , `PREFERENCE`                   AS `PREFERENCE`
    , CASE `IS_FINAL` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_FINAL`
    , CASE `IS_RESTARTABLE` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_RESTARTABLE`
    , CASE `IS_UNREACHABLE` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_UNREACHABLE`
    , CASE `IS_BROKEN` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_BROKEN`
    , CASE `IS_BATCH_DEFAULT` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_BATCH_DEFAULT`
    , CASE `IS_DEPENDENCY_DEFAULT` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_DEPENDENCY_DEFAULT`
    , `ESP_ID`                       AS `ESP_ID`
    , `ESD_ID`                       AS `ESD_ID`
    , `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 `EXIT_STATE`
 WHERE VALID_TO = 9223372036854775807;
CREATE VIEW SCI_V_EXIT_STATE AS
SELECT 
    ID 
    , `PREFERENCE`                   AS `PREFERENCE`
    , CASE `IS_FINAL` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_FINAL`
    , CASE `IS_RESTARTABLE` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_RESTARTABLE`
    , CASE `IS_UNREACHABLE` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_UNREACHABLE`
    , CASE `IS_BROKEN` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_BROKEN`
    , CASE `IS_BATCH_DEFAULT` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_BATCH_DEFAULT`
    , CASE `IS_DEPENDENCY_DEFAULT` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_DEPENDENCY_DEFAULT`
    , `ESP_ID`                       AS `ESP_ID`
    , `ESD_ID`                       AS `ESD_ID`
    , `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 `EXIT_STATE`;
