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

CREATE TABLE `OBJECT_EVENT` (
    `ID`                           decimal(20) NOT NULL
    , `TR_ID`                        decimal(20)         NULL
    , `OI_ID`                        decimal(20)     NOT NULL
    , `EVENT_TYPE`                   integer         NOT NULL
    , `SME_ID`                       decimal(20)     NOT NULL
    , `SE_ID`                        decimal(20)     NOT NULL
    , `SUBMIT_TS`                    decimal(20)         NULL
    , `FINAL_TS`                     decimal(20)         NULL
    , `FINAL_ESD_ID`                 decimal(20)         NULL
    , `MAIN_SME_ID`                  decimal(20)         NULL
    , `MAIN_SE_ID`                   decimal(20)         NULL
    , `MAIN_FINAL_TS`                decimal(20)         NULL
    , `MAIN_FINAL_ESD_ID`            decimal(20)         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
) ENGINE = INNODB;
CREATE UNIQUE INDEX PK_OBJECT_EVENT
ON `OBJECT_EVENT`(`ID`);
CREATE VIEW SCI_OBJECT_EVENT AS
SELECT 
    ID 
    , `TR_ID`                        AS `TR_ID`
    , `OI_ID`                        AS `OI_ID`
    , CASE `EVENT_TYPE` WHEN 1 THEN 'CREATE' WHEN 2 THEN 'CHANGE' WHEN 3 THEN 'DELETE' END AS `EVENT_TYPE`
    , `SME_ID`                       AS `SME_ID`
    , `SE_ID`                        AS `SE_ID`
    , from_unixtime((`SUBMIT_TS` & ~1125899906842624)/1000) AS `SUBMIT_TS`
    , from_unixtime((`FINAL_TS` & ~1125899906842624)/1000) AS `FINAL_TS`
    , `FINAL_ESD_ID`                 AS `FINAL_ESD_ID`
    , `MAIN_SME_ID`                  AS `MAIN_SME_ID`
    , `MAIN_SE_ID`                   AS `MAIN_SE_ID`
    , from_unixtime((`MAIN_FINAL_TS` & ~1125899906842624)/1000) AS `MAIN_FINAL_TS`
    , `MAIN_FINAL_ESD_ID`            AS `MAIN_FINAL_ESD_ID`
    , `SE_VERSION`                   AS `SE_VERSION`
    , `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 `OBJECT_EVENT`;
