USE [HeuristicLab.Hive-3.3]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/* drop existing tables */
DROP TABLE [statistics].[FactTask]
GO

DROP TABLE [statistics].[FactClientInfo]
GO

DROP TABLE [statistics].[DimUser]
GO

DROP TABLE [statistics].[DimJob]
GO

DROP TABLE [statistics].[DimClient]
GO

DROP TABLE [statistics].[DimTime]
GO

/* create tables */
CREATE TABLE [statistics].[DimTime] (
    [Time]   DATETIME NOT NULL,
    [Minute] DATETIME NOT NULL,
    [Hour]   DATETIME NOT NULL,
    [Day]    DATE     NOT NULL,
    [Month]  DATE     NOT NULL,
    [Year]   DATE     NOT NULL,
    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
);
CREATE TABLE [statistics].[DimClient] (
    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
    [Name]             VARCHAR (MAX)    NOT NULL,
    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
    [ExpirationTime]   DATETIME         NULL,
    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
    [GroupName]        VARCHAR (MAX)    NULL,
    [GroupName2]       VARCHAR (MAX)    NULL,
    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [statistics].[DimJob] (
    [JobId]          UNIQUEIDENTIFIER NOT NULL,
    [UserId]         UNIQUEIDENTIFIER NOT NULL,
    [JobName]        VARCHAR (MAX)    NOT NULL,
    [UserName]       VARCHAR (MAX)    NOT NULL,
    [DateCreated]    DATETIME		  NOT NULL,
    [TotalTasks]     INT              NOT NULL,
    [CompletedTasks] INT              NOT NULL,
    [DateCompleted]  DATETIME		  NULL,
    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
);
CREATE TABLE [statistics].[DimUser] (
    [UserId] UNIQUEIDENTIFIER NOT NULL,
    [Name]   VARCHAR (MAX)    NOT NULL,
    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
);
CREATE TABLE [statistics].[FactClientInfo] (
    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
    [Time]                 DATETIME         NOT NULL,
    [UserId]               UNIQUEIDENTIFIER NOT NULL,
    [NumUsedCores]         INT              NOT NULL,
    [NumTotalCores]        INT              NOT NULL,
    [UsedMemory]           INT              NOT NULL,
    [TotalMemory]          INT              NOT NULL,
    [CpuUtilization]       FLOAT (53)       NOT NULL,
    [SlaveState]           VarChar(15)      NOT NULL,
    [IdleTime]             INT              NOT NULL,
    [OfflineTime]          INT              NOT NULL,
    [UnavailableTime]      INT              NOT NULL,
    [IsAllowedToCalculate] BIT              NOT NULL,
    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
);
CREATE TABLE [statistics].[FactTask] (
  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
  [CalculatingTime]    INT              NOT NULL,
  [WaitingTime]        INT              NOT NULL,
  [TransferTime]       INT              NOT NULL,
  [NumCalculationRuns] INT              NOT NULL,
  [NumRetries]         INT              NOT NULL,
  [CoresRequired]      INT              NOT NULL,
  [MemoryRequired]     INT              NOT NULL,
  [Priority]           INT              NOT NULL,
  [LastClientId]       UNIQUEIDENTIFIER NULL,
  [JobId]		       UNIQUEIDENTIFIER NOT NULL,
  [StartTime]          DATETIME         NULL,
  [EndTime]            DATETIME         NULL,
  [TaskState]          VARCHAR (30)     NOT NULL,
  [Exception]          VARCHAR (MAX)    NULL,
  [InitialWaitingTime] INT              NULL,
  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
);

/* dummy for nullable userIds in FactClientInfo */
INSERT INTO [statistics].[DimUser] ([UserId], [Name])
VALUES ('00000000-0000-0000-0000-000000000000', 'NULL')