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

CREATE TABLE [RESSOURCE] (
    [ID]                           decimal (38) NOT NULL
    , [NR_ID]                        decimal (38)    NOT NULL
    , [SCOPE_ID]                     decimal (38)        NULL
    , [MASTER_ID]                    decimal (38)        NULL
    , [OWNER_ID]                     decimal (38)    NOT NULL
    , [LINK_ID]                      decimal (38)        NULL
    , [MANAGER_ID]                   decimal (38)        NULL
    , [TAG]                          varchar(64)         NULL
    , [RSD_ID]                       decimal (38)        NULL
    , [RSD_TIME]                     decimal (38)        NULL
    , [DEFINED_AMOUNT]               integer             NULL
    , [REQUESTABLE_AMOUNT]           integer             NULL
    , [AMOUNT]                       integer             NULL
    , [FREE_AMOUNT]                  integer             NULL
    , [IS_ONLINE]                    integer             NULL
    , [FACTOR]                       float               NULL
    , [TRACE_INTERVAL]               integer             NULL
    , [TRACE_BASE]                   integer             NULL
    , [TRACE_BASE_MULTIPLIER]        integer         NOT NULL
    , [TD0_AVG]                      float           NOT NULL
    , [TD1_AVG]                      float           NOT NULL
    , [TD2_AVG]                      float           NOT NULL
    , [LW_AVG]                       float           NOT NULL
    , [LAST_EVAL]                    decimal (38)    NOT NULL
    , [LAST_WRITE]                   decimal (38)    NOT NULL
    , [CREATOR_U_ID]                 decimal (38)    NOT NULL
    , [CREATE_TS]                    decimal (38)    NOT NULL
    , [CHANGER_U_ID]                 decimal (38)    NOT NULL
    , [CHANGE_TS]                    decimal (38)    NOT NULL
);
go
CREATE UNIQUE INDEX PK_RESSOURCE
ON [RESSOURCE]([ID]);
go
CREATE VIEW SCI_RESSOURCE AS
SELECT 
    ID 
    , [NR_ID]                        AS [NR_ID]
    , [SCOPE_ID]                     AS [SCOPE_ID]
    , [MASTER_ID]                    AS [MASTER_ID]
    , [OWNER_ID]                     AS [OWNER_ID]
    , [LINK_ID]                      AS [LINK_ID]
    , [MANAGER_ID]                   AS [MANAGER_ID]
    , [TAG]                          AS [TAG]
    , [RSD_ID]                       AS [RSD_ID]
    , dateadd(ss, round(([RSD_TIME] % 1125899906842624)/1000, 0), '1970-01-01') AS [RSD_TIME]
    , [DEFINED_AMOUNT]               AS [DEFINED_AMOUNT]
    , [REQUESTABLE_AMOUNT]           AS [REQUESTABLE_AMOUNT]
    , [AMOUNT]                       AS [AMOUNT]
    , [FREE_AMOUNT]                  AS [FREE_AMOUNT]
    , CASE [IS_ONLINE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_ONLINE]
    , [FACTOR]                       AS [FACTOR]
    , [TRACE_INTERVAL]               AS [TRACE_INTERVAL]
    , [TRACE_BASE]                   AS [TRACE_BASE]
    , [TRACE_BASE_MULTIPLIER]        AS [TRACE_BASE_MULTIPLIER]
    , [TD0_AVG]                      AS [TD0_AVG]
    , [TD1_AVG]                      AS [TD1_AVG]
    , [TD2_AVG]                      AS [TD2_AVG]
    , [LW_AVG]                       AS [LW_AVG]
    , dateadd(ss, round(([LAST_EVAL] % 1125899906842624)/1000, 0), '1970-01-01') AS [LAST_EVAL]
    , dateadd(ss, round(([LAST_WRITE] % 1125899906842624)/1000, 0), '1970-01-01') AS [LAST_WRITE]
    , [CREATOR_U_ID]                 AS [CREATOR_U_ID]
    , dateadd(ss, round(([CREATE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CREATE_TS]
    , [CHANGER_U_ID]                 AS [CHANGER_U_ID]
    , dateadd(ss, round(([CHANGE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CHANGE_TS]
  FROM [RESSOURCE];
go
