/*
Run this script on SQL Server 2008 or later. There may be flaws if running on earlier versions of SQL Server.
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Scenario]') AND type in (N'U'))
DROP TABLE [dbo].[Scenario]
GO
CREATE TABLE [dbo].[Scenario] (
	[Id]							[uniqueidentifier]	 NOT NULL,
	[ParentId]						[uniqueidentifier]	 NULL,
	[TemplateId]					[uniqueidentifier]	 NULL,
	[Type]							[int]				 NULL,
	[Name]							[nvarchar](200)		 COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProjectedRevenue]				[decimal](15, 4)	 NULL,
	[ExpectedGrossMargin]			[decimal](15, 4)	 NULL,
	[CalculatedGrossMargin]			[decimal](15, 4)	 NULL,
	[CGSplit]						[decimal](5, 4)		 NULL,
	[EFXSplit]						[decimal](5, 4)		 NULL,
	[StartDate]						[datetime]			 NULL,
	[EndDate]						[datetime]			 NULL,
	[Duration]						[int]				 NULL,
	[TDDirectCosts]					[decimal](15, 4)	 NULL,
	[BUDirectCosts]					[decimal](15, 4)	 NULL,
	[Shots]							[int]				 NULL,
	[TDRevenueShot]					[decimal](15, 4)	 NULL,
	[BURevenueShot]					[decimal](15, 4)	 NULL,
	[FreezeRevenue]					[int]				 NULL,
	[LastUpdate]					[datetime]			 NULL,
	[Color]							[nvarchar](16)		 COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Status]						[int]				 NULL,
	[UseLMMargin]					[int]				 NULL,
	[ExpectedGrossMargin_LM]		[decimal](15, 4)	 NULL,
	[CalculatedGrossMargin_LM]		[decimal](15, 4)	 NULL,
	[TDDirectCosts_LM]				[decimal](15, 4)	 NULL,
	[BUDirectCosts_LM]				[decimal](15, 4)	 NULL,
	[BURevenueShot_LM]				[decimal](15, 4)	 NULL,
	[ShotStartDate]					[datetime]			 NULL,
	[EntryTimeStamp]				[datetime]			 NULL,
	[GrowthScenario]				[int]				 NULL,
	[Actuals_BUDirectCosts]			[decimal](15, 4)	 NULL,
	[Actuals_BUDirectCosts_LM]		[decimal](15, 4)	 NULL,
	[SystemAttributeObjectID]		[uniqueidentifier]	 NULL,
	[ProjectedExpense]				[decimal](15, 4)	 NULL,
	CONSTRAINT [PK_Scenario] PRIMARY KEY CLUSTERED ([Id])
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [FK_Scenario_Project] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Project] ([Id])
GO
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [FK_Scenario_TemplateScenario] FOREIGN KEY ([TemplateId]) REFERENCES [dbo].[Scenario] ([Id])
GO