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

CREATE TABLE `NICE_PROFILE_ENTRY` (
    `ID`                           decimal(20) NOT NULL
    , `NP_ID`                        decimal(20)     NOT NULL
    , `PREFERENCE`                   integer         NOT NULL
    , `FOLDER_ID`                    decimal(20)         NULL
    , `IS_SUSPENDED`                 integer         NOT NULL
    , `RENICE`                       integer         NOT NULL
    , `IS_ACTIVE`                    integer         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_NICE_PROFILE_ENTRY
ON `NICE_PROFILE_ENTRY`(`ID`);
CREATE VIEW SCI_NICE_PROFILE_ENTRY AS
SELECT 
    ID 
    , `NP_ID`                        AS `NP_ID`
    , `PREFERENCE`                   AS `PREFERENCE`
    , `FOLDER_ID`                    AS `FOLDER_ID`
    , CASE `IS_SUSPENDED` WHEN 0 THEN 'NOSUSPEND' WHEN 1 THEN 'SUSPEND' WHEN 2 THEN 'ADMINSUSPEND' END AS `IS_SUSPENDED`
    , `RENICE`                       AS `RENICE`
    , CASE `IS_ACTIVE` WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS `IS_ACTIVE`
    , `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 `NICE_PROFILE_ENTRY`;
