USE EnVisage
GO

IF OBJECT_ID('TeamAllocation') IS NULL
BEGIN
	CREATE TABLE [dbo].[TeamAllocation] (
		[Id]						[uniqueidentifier]	 NOT NULL,
		[ScenarioId]				[uniqueidentifier]	 NOT NULL,
		[TeamId]					[uniqueidentifier]	 NOT NULL,
		[ExpenditureCategoryId]		[uniqueidentifier]	 NOT NULL,
		[WeekEndingDate]			[datetime]			 NOT NULL,
		[Quantity]					[decimal](18, 6)	 NOT NULL,
		[LastUpdate]				[datetime]			 NOT NULL,
		CONSTRAINT [PK_TeamAllocation] PRIMARY KEY CLUSTERED ([Id]),
		CONSTRAINT [IX_TeamAllocation] UNIQUE NONCLUSTERED ([ScenarioId], [ExpenditureCategoryId], [TeamId], [WeekEndingDate])
	) ON [PRIMARY]
END
GO

IF OBJECT_ID('FK_TeamAllocation_ExpenditureCategoryId') IS NULL
BEGIN
	ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_ExpenditureCategoryId] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
END
GO

IF OBJECT_ID('FK_TeamAllocation_ScenarioId') IS NULL
BEGIN
	ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_ScenarioId] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
END
GO

IF OBJECT_ID('FK_TeamAllocation_TeamId') IS NULL
BEGIN
	ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_TeamId] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
END
GO