--System configuration
IF NOT((SELECT SERVERPROPERTY('Edition') AS	[Edition]) = 'SQL Azure')
BEGIN
	DECLARE @NCOMMAND NVARCHAR(MAX)
	SET @NCOMMAND =
	N'EXECUTE SP_CONFIGURE ''show advanced options'', 1;
	RECONFIGURE;
	EXECUTE SP_CONFIGURE ''Ole Automation Procedures'', 1;
	RECONFIGURE;'
	EXECUTE SP_EXECUTESQL @NCOMMAND;
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaSCtlMaster' )
BEGIN
	CREATE TABLE [KaaSCtlMaster](
		[id]				[int] IDENTITY(1,1) NOT NULL,
		[ComponentName]		[varchar](100) NOT NULL,
		[ControlName]		[varchar](100) NULL,
		[FriendlyName]		[varchar](100) NOT NULL,
		[IsVisible]			[bit] NOT NULL,
		[Deleted]			[bit] NOT NULL,
		[ParentId]			[smallint] DEFAULT 0 NOT NULL
		CONSTRAINT [REDACTED]PRIMARY KEY(Id)
	)
END

GO 

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaSCtlGroupControls' )
BEGIN
	CREATE TABLE [KaaSCtlGroupControls](
		[id]		[int] IDENTITY(1,1) NOT NULL,
		[groupid]	[int] NOT NULL,
		[masterid]	[int] NOT NULL,
		CONSTRAINT [REDACTED]PRIMARY KEY(Id)
	)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-details' AND [ControlName] IS  NULL) 
BEGIN
	INSERT INTO [KaaSCtlMaster](
		[ComponentName], 
		[ControlName], 
		[FriendlyName], 
		[IsVisible], 
		[ParentId], 
		[Deleted]) 
	VALUES (
		'current-client-details',
		null, 
		'Current Client Details', 
		0, 
		0, 
		0)
END

GO

--Master
IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'change-matter-security-permissions' AND [ControlName] IS NULL) 
BEGIN
	INSERT INTO [KaaSCtlMaster](
		[ComponentName], 
		[ControlName], 
		[FriendlyName], 
		[IsVisible], 
		[ParentId], 
		[Deleted]) 
	VALUES ('change-matter-security-permissions',
		null, 
		'Change Matter Security Permissions', 
		0, 
		0, 
		0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-billing' AND [ControlName] IS NULL) 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], 
	[ControlName], 
	[FriendlyName], 
	[IsVisible], 
	[ParentId], 
	[Deleted]) 
VALUES ('current-client-billing',
	null, 
	'Current Client Billing', 
	0, 
	0, 
	0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'general-tab' AND [ControlName] =  'btnSaveGeneral') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('general-tab','btnSaveGeneral', 'Current Client Details - General', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'bank-details-tab' AND [ControlName] =  'btnSaveBankDetails') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('bank-details-tab','btnSaveBankDetails', 'Current Client Details - Bank Details', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'billing-details-tab' AND [ControlName] =  'btnSaveBillingDetails') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('billing-details-tab','btnSaveBillingDetails', 'Current Client Details - Billing Details', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnSaveCategories') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnSaveCategories', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnSaveCorporate') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnSaveCorporate', 'Current Client Details - Corporate', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnSaveCrossRef') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnSaveCrossRef', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'legal-details-tab' AND [ControlName] =  'btnSaveLegalDetails') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('legal-details-tab','btnSaveLegalDetails', 'Current Client Details - Legal Details', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'matters-tab' AND [ControlName] =  'btnSaveMatters') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('matters-tab','btnSaveMatters', 'Current Client Details - Matters', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'notes-tab' AND [ControlName] =  'btnSaveNotes') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('notes-tab','btnSaveNotes', 'Current Client Details - Notes', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'permission-tab' AND [ControlName] =  'btnSavePermission') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('permission-tab','btnSavePermission', 'Current Client Details - Permission', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'personal-tab' AND [ControlName] =  'btnSavePersonal') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('personal-tab','btnSavePersonal', 'Current Client Details - Personal', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'client-contact-tab' AND [ControlName] =  'btnClientContactInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('client-contact-tab','btnClientContactInsert', 'Current Client Details - Client Contact', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'client-contact-tab' AND [ControlName] =  'btnClientContactChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('client-contact-tab','btnClientContactChange', 'Current Client Details - Client Contact', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'client-contact-tab' AND [ControlName] =  'btnClientContactDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('client-contact-tab','btnClientContactDelete', 'Current Client Details - Client Contact', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnCrossRefInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnCrossRefInsert', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnCrossRefChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnCrossRefChange', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'cross-ref-tab' AND [ControlName] =  'btnCrossRefDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('cross-ref-tab','btnCrossRefDelete', 'Current Client Details - Cross Reference', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnCategoriesInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnCategoriesInsert', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnCategoriesChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnCategoriesChange', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'categories-tab' AND [ControlName] =  'btnCategoriesDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('categories-tab','btnCategoriesDelete', 'Current Client Details - Categories', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnCorporateOfficerInsert') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnCorporateOfficerInsert', 'Current Client Details - Corporate', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnCorporateOfficerChange') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnCorporateOfficerChange', 'Current Client Details - Corporate', 0, 1, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'corporate-tab' AND [ControlName] =  'btnCorporateOfficerDelete') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('corporate-tab','btnCorporateOfficerDelete', 'Current Client Details - Corporate', 0, 1, 0)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-detail-permission-tab' AND [ControlName] =  'mat-tab-label-0-10') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('current-client-detail-permission-tab','mat-tab-label-0-10', 'Current Client Details - Permission Tab', 0, 2, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlMaster] WHERE [ComponentName] = 'current-client-detail-billing-tab' AND [ControlName] =  'dvCurrentClientDetailsManageBilling') 
BEGIN
INSERT INTO [KaaSCtlMaster]([ComponentName], [ControlName], [FriendlyName], [IsVisible], [ParentId], [Deleted]) VALUES ('current-client-detail-billing-tab','dvCurrentClientDetailsManageBilling', 'Current Client Details - Billing Tab', 0, 3, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlGroupControls] WHERE [GroupId] = '1000033' AND [MasterId] =  1) 
BEGIN
INSERT INTO [KaaSCtlGroupControls] ([GroupId], [MasterId]) VALUES ('1000033',1)
END

GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlGroupControls] WHERE [GroupId] = '1000012' AND [MasterId] =  2) 
BEGIN
INSERT INTO [KaaSCtlGroupControls] ([GroupId], [MasterId]) VALUES ('1000012',2)
END


GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [KaaSCtlGroupControls] WHERE [GroupId] = '1000006' AND [MasterId] =  3) 
BEGIN
INSERT INTO [KaaSCtlGroupControls] ([GroupId], [MasterId]) VALUES ('1000006',3)
END

GO


IF NOT EXISTS(SELECT TOP 1 1 FROM Settings where [KeyName] = 'StatuteWarning')
BEGIN
	INSERT INTO Settings ([KeyName],[KeyValue]) VALUES ('StatuteWarning',0)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM Settings where [KeyName] = 'StatuteActionCode')
BEGIN
	INSERT INTO Settings ([KeyName],[KeyValue]) VALUES ('StatuteActionCode','')
END
GO

--Sprint 6 Scritps
IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'ClientLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'ClientLabel',
		 'Client'
		)
END
GO

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'MatterLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'MatterLabel',
		 'Matter'
		)
END
GO

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'AssociateLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'AssociateLabel',
		 'Associate'
		)
END
GO

IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'CaseLabel')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'CaseLabel',
		 'Case'
		)
END
GO


IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE [name] = 'Settings') AND NOT EXISTS (SELECT TOP 1 1 FROM [Settings] WHERE [KeyName] = 'DocumentNamingMethod')
BEGIN
	INSERT INTO
		[Settings]
		(
		 [KeyName],
		 [KeyValue]
		)
	VALUES
		(
		 'DocumentNamingMethod',
		 'D'
		)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE [Name] = 'RecentHandlerList')
BEGIN
CREATE TABLE RecentHandlerList (
    [Id] INT IDENTITY(1,1) CONSTRAINT PK_RecentHandlerList PRIMARY KEY,
	[Code] VARCHAR(10),
	[AccessedBy] VARCHAR(10) CONSTRAINT FK_Handlers_Code_RecentHandlerList_AccessedBy FOREIGN KEY REFERENCES Handlers(CODE),
	[AccessedTime] DATETIME,
)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'Panels')
BEGIN
CREATE TABLE [dbo].[Panels](
	[PanelId] [int] IDENTITY(1,1) NOT NULL,
	[PanelName] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
	[DashboardType] [varchar](50) NULL,
	[Width] [int] NULL,
	CONSTRAINT pk_PanelId PRIMARY KEY (PanelId)
)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'Widgets')
BEGIN
CREATE TABLE [dbo].[Widgets](
	[WidgetId] [int] IDENTITY(1,1) NOT NULL,
	[WidgetName] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
	[TemplateId] [int] NULL,
	[WidgetData] [text] NULL,
	CONSTRAINT pk_WidgetId PRIMARY KEY (WidgetId)
	)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'PanelWidgetAssociation')
BEGIN
CREATE TABLE [dbo].[PanelWidgetAssociation](
	[AssociationID] [int] IDENTITY(1,1) NOT NULL,
	[PanelId] [int] NOT NULL,
	[WidgetId] [int] NOT NULL,
	CONSTRAINT pk_AssociationId PRIMARY KEY (AssociationId),
	CONSTRAINT [FkPanelAssociation] FOREIGN KEY([PanelId]) REFERENCES [dbo].[Panels] ([PanelId]),
	CONSTRAINT [FkWidgetAssociation] FOREIGN KEY([WidgetId]) REFERENCES [dbo].[Widgets] ([WidgetId])
	)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'UserPanels')
BEGIN
CREATE TABLE [dbo].[UserPanels](
	[UserPanelId] [int] IDENTITY(1,1) NOT NULL,
	[HandlerCode] [varchar](10) NOT NULL,
	[PanelId] [int] NOT NULL,
	[PanelName] [varchar](255) NULL,
	[ParentPanel] [int] NULL,
	CONSTRAINT pk_UserPanelId PRIMARY KEY (UserPanelId),
	CONSTRAINT [FkUserPanelPanelId] FOREIGN KEY([PanelId]) REFERENCES [dbo].[Panels] ([PanelId])
	)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'UserWidgets')
BEGIN
CREATE TABLE [dbo].[UserWidgets](
	[UserWidgetsId] [int] IDENTITY(1,1) NOT NULL,
	[UserPanelId] [int] NOT NULL,
	[WidgetId] [int] NOT NULL,
	[WidgetData] [text] NULL,
	CONSTRAINT pk_UserWidgetsId PRIMARY KEY (UserWidgetsId),
	CONSTRAINT [FkUserWidgetsPanelId] FOREIGN KEY([UserPanelId]) REFERENCES [dbo].[UserPanels] ([UserPanelId]),
	CONSTRAINT [FkUserWidgetsWidgetId] FOREIGN KEY([WidgetId]) REFERENCES [dbo].[Widgets] ([WidgetId])
	)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'UITemplates')
BEGIN
CREATE TABLE [dbo].[UITemplates](
	[TemplateId] [int] IDENTITY(1,1) NOT NULL,
	[TemplateName] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
	[TemplateQuery] [text] NULL,
	[QueryComponent] [varchar](255) NULL,
	[DisplayComponent] [varchar](255) NULL,
	CONSTRAINT pk_TemplateId PRIMARY KEY (TemplateId),
	CONSTRAINT [FK_Widget_TemplateId] FOREIGN KEY([TemplateId]) REFERENCES [dbo].[UITemplates] ([TemplateId])
	)
END
GO



-------------------------------------------Panels Insert Start---------------------------------------------------------

SET IDENTITY_INSERT [dbo].[Panels] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 1)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (1, N'Tasks', N'Panel to display all the tasks', 'tasks', 100)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 2)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (2, N'Fees Issued', N'Fees Issued Information Panel', 'tasks', 50)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 3)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (3, N'Account Summary', N'Account Summary', 'caseDiary', 100)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 4)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (4, N'Fees Issued', N'Fees Issued Information Panel', null, 0)
END

GO

SET IDENTITY_INSERT [dbo].[Panels] OFF
GO
---------------------------------------------Panels Insert End-----------------------------------------------------------



------------------------------------------UI Templates Insert Start-------------------------------------------------------

SET IDENTITY_INSERT [dbo].[UITemplates] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1000)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1000, N'Over due Tasks', N'Template for over due tasks', N'', N'overDueTasksQueryComponent', N'overDueTasksDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1001)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1001, N'Outstanding Tasks', N'Template for Outstanding Tasks', N'', N'Outstanding Tasks QueryComponent', N'Outstanding Tasks DisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1002)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1002, N'Pending Tasks', N'Template for Pending tasks', N'', N'pendingTasksQueryComponent', N'pendingTasksDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1003)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1003, N'My Status', N'Template for current user status', N'', N'myStatusQueryComponent', N'myStatusDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1004)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1004, N'Users in Project', N'Template for users based on project ', N'', N'projectUserQueryComponent', N'projectUserDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1005)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1005, N'User List', N'All Users template', N'', N'userListQueryComponent', N'userListDisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1006)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1006, N'Critical Tasks', N'Template for Critical tasks', N'', N'CriticalTasks Query component', N'CriticalTasks Display component')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1007)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1007, N'Court Dates', N'Template for Court Dates', N'', N'Court Dates QueryComponent', N'Court Dates DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1008)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1008, N'High priority Tasks', N'Template for High priority Tasks', N'', N'High priority Tasks QueryComponent', N'High priority Tasks DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1009)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1009, N'Phone Message', N'Template for Phone Message', N'', N'Phone Message QueryComponent', N'Phone Message DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1010)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1010, N'Undertakings', N'Template for Undertakings', N'', N'Undertakings QueryComponent', N'Undertakings DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1011)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1011, N'Statute date approaching', N'Template for Statute date approaching', N'', N'Statute date approaching QueryComponent', N'Statute date approaching DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1012)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1012, N'Open Matters', N'Template for Open Matters', N'', N'Open Matters QueryComponent', N'Open Matters DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1013)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent])
	VALUES 
		(1013, N'My dormant Matters', N'Template for My dormant Matters', N'', N'My dormant Matters QueryComponent', N'My dormant Matters DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1014)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1014, N'No. of Effectively complete Matters', N'Template for No. of Effectively complete Matters', N'', N'No. of Effectively complete Matters QueryComponent', N'No. of Effectively complete Matters DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1015)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1015, N'No. of Cases where expected invoice date has passed', N'Template for No. of Cases where expected invoice date has passed', N'', N'No. of Cases where expected invoice date has passed QueryComponent', N'No. of Cases where expected invoice date has passed DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1016)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1016, N'No. of Cases approaching estimate fee', N'Template for No. of Cases approaching estimate fee', N'', N'No. of Cases approaching estimate fee QueryComponent', N'No. of Cases approaching estimate fee DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1017)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1017, N'No. of Cases approaching lockup value limit', N'Template for No. of Cases approaching lockup value limit', N'', N'No. of Cases approaching lockup value limit QueryComponent', N'No. of Cases approaching lockup value limit DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1018)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1018, N'No. of Clients that require money laundering checks', N'Template for No. of Clients that require money laundering checks', N'', N'No. of Clients that require money laundering checks QueryComponent', N'No. of Clients that require money laundering checks DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1019)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1019, N'No. of Cases with no estimate given', N'Template for No. of Cases with no estimate given', N'', N'No. of Cases with no estimate given QueryComponent', N'No. of Cases with no estimate given DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1020)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1020, N'No. of Cases with no estimated fee', N'Template for No of Cases with no estimated fee', N'', N'No of Cases with no estimated fee Query Component', N'No of Cases with no estimated fee Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1021)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1021, N'Debtors A/C', N'Debtors Account information', N'', N'Debtors Account Query Component', N'Debtors Account Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1022)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1022, N'Outlay A/C', N'Outlay Account information', N'', N'Outlay Account Query Component', N'Outlay Account Query Component')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1023)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1023, N'Outlay Budget', N'Outlay Budget information', N'', N'Outlay Budget Query Component', N'Outlay Budget Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1024)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1024, N'Current Outstanding Fees', N'Current Outstanding Fees information', N'', N'Current Outstanding Fees Query Component', N'Current Outstanding Fees Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1025)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1025, N'Client A/C', N'Client Account information', N'', N'Client Account Query Component', N'Client Account Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1026)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1026, N'Client Current', N'Client Current information', N'', N'Client Current Query Component', N'Client Current Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1027)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1027, N'Client Deposit', N'Client Deposit information', N'', N'Client Deposit Query Component', N'Client Deposit Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1028)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1028, N'Total Work In Progress', N'Total Work In Progress information', N'', N'Total Work In Progress Query Component', N'Total Work In Progress Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1029)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1029, N'Write Off Time', N'Write Off Time information', N'', N'Write Off Time Query Component', N'Write Off Time Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1030)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1030, N'Fees Issued To Date', N'Fees Issued To Date information', N'', N'Fees Issued To Date Query Component', N'Fees Issued To Date Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1031)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1031, N'Fees To Date + WIP', N'Fees To Date + WIP information', N'', N'Fees To Date + WIP Query Component', N'Fees To Date + WIP Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1032)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1032, N'Estimate Fees', N'Estimate Fees information', N'', N'Estimate Fees Query Component', N'Estimate Fees Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1033)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1033, N'Draft Invoices', N'Draft Invoices information', N'', N'Draft Invoices Query Component', N'Draft Invoices Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1034)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1034, N'Last Bill Date', N'Last Bill Date information', N'', N'Last Bill Date Query Component', N'Last Bill Date Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1035)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1035, N'Expected Bill Date', N'Expected Bill Date information', N'', N'Expected Bill Date Query Component', N'Expected Bill Date Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1036)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1036, N'Current Profit/Loss', N'Current Profit/Loss information', N'', N'Current Profit/Loss Query Component', N'Current Profit/Loss Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1037)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1037, N'Possible Value To The Client ', N'Possible Value To The Client  information', N'', N'Possible Value To The Client  Query Component', N'Possible Value To The Client  Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1038)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1038, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', N'', N'Anti Money Laundering Checked Query Component', N'Anti Money Laundering Checked Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1039)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1039, N'Current Loked Up Value', N'Current Loked Up Value', N'', N'Current Loked Up Value', N'Current Loked Up Value')
END
GO

SET IDENTITY_INSERT [dbo].[UITemplates] OFF
GO


-------------------------------------UI Templates Insert End-------------------------------------------------------


------------------------------------------Widget Insert Start-----------------------------------------------------

SET IDENTITY_INSERT [dbo].[Widgets] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 100)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(100, N'Over due Tasks', N'Shows all Over due Tasks', 1000, N' {"color":"warn-fg", "title":"Overdue", "icon":"receipt"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 101)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(101, N'Outstanding Tasks', N'Shows all Outstanding Tasks', 1001, N' {"color":"orange-fg", "title":"Outstanding", "icon":"list_alt"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 102)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(102, N'Fees Issued YTD', N'Shows all Fees Issued Year to Date', 1002, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 103)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(103, N'Fees Issued This Month', N'Shows all Fees Issued This Month', 1003, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 104)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(104, N'Chargeable Time YTD', N'Shows all Chargeable Time YTD', 1004, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 105)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(105, N'Chargeable Time This Month', N'Shows all Chargeable Time This Month', 1005, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 106)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData])
	VALUES 
		(106, N'Critical Tasks', N'Shows all Critical Tasks', 1006, N' {"color":"red-fg", "title":"Critical", "icon":"priority_high"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 107)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(107, N'Court Dates', N'Shows all Court Dates', 1007, N' {"color":"blue-fg", "title":"Court Dates", "icon":"event_available"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 108)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(108, N'High priority Tasks', N'Shows all High priority Tasks', 1008, N' {"color":"deep-purple-fg", "title":"Priority", "icon":"format_line_spacing"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 109)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(109, N'Phone message', N'Shows all Phone message', 1009, N' {"color":"amber-fg", "title":"Messages", "icon":"local_phone"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 110)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(110, N'Undertakings', N'Shows all Undertakings', 1010, N' {"color":"yellow-fg", "title":"Undertakings", "icon":"child_friendly"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 111)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(111, N'Statue date approaching', N'Shows count of Statue date approaching', 1011, N' {"color":"green-fg", "title":"Statute", "icon":"card_membership"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 112)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(112, N'Open Matters', N'Shows count of Open Matters', 1012, N' {"color":"light-green-fg", "title":"Matters", "icon":"local_library"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 113)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(113, N'My dormant Matters', N'Shows count of My dormant Matters', 1013, N' {"color":"lime-fg", "title":"Dormant", "icon":"book"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 114)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(114, N'No of Effectively Complete Matters', N'Shows count of No of Effectively Complete Matters', 1014, N' {"color":"brown-fg", "title":"Complete Matters", "icon":"calendar_today"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 115)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(115, N'No. of Cases where expected invoice date has passed', N'Shows count of No. of Cases where expected invoice date has passed', 1015, N' {"color":"fuse-navy-fg", "title":"Date Passed Cases", "icon":"event"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 116)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(116, N'No. of Cases approaching estimate fee', N'Shows count of No. of Cases approaching estimate fee', 1016, N' {"color":"teal-fg", "title":"Estimate Fee", "icon":"confirmation_number"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 117)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(117, N'No. of Cases approaching lockup value limit', N'Shows count of No. of Cases approaching lockup value limit', 1017, N' {"color":"indigo-fg", "title":"Lookup Value", "icon":"vertical_align_top"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 118)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(118, N'No. of Clients that require money laundering checks', N'Shows count of No. of Clients that require money laundering checks', 1018, N' {"color":"purple-fg", "title":"Money Laundering", "icon":"assignment_ind"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 119)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(119, N'No. of Cases with no estimate given', N'Shows count of No. of Cases with no estimate given', 1019, N' {"color":"pink-fg", "title":"No Estimate Time", "icon":"event_note"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 120)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(120, N'No. of Cases with no estimated fee', N'Shows count of No of Cases with no estimated fee', 1020, N' {"color":"accent-fg", "title":"No Estimate Fee", "icon":"account_balance_wallet"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 121)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(121, N'Debtors A/C', N'Debtors Account information', 1021, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 122)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(122, N'Outlay A/C', N'Outlay Account information', 1022, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 123)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(123, N'Outlay Budget', N'Outlay Budget information', 1023, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 124)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(124, N'Current Outstanding Fees', N'Current Outstanding Fees information', 1024, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 125)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(125, N'Client A/C', N'Client Account information', 1025, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 126)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(126, N'Client Current', N'Client Current information', 1026, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 127)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(127, N'Client Deposit', N'Client Deposit information', 1027, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 128)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(128, N'Total Work In Progress', N'Total Work In Progress information', 1028, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 129)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(129, N'Write Off Time', N'Write Off Time information', 1029, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 130)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(130, N'Fees Issued To Date', N'Fees Issued To Date information', 1030, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 131)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(131, N'Fees To Date + WIP', N'Fees To Date + WIP information', 1031, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 132)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(132, N'Estimate Fees', N'Estimate Fees information', 1032, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 133)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(133, N'Draft Invoices', N'Draft Invoices information', 1033, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 134)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(134, N'Last Bill Date', N'Last Bill Date information', 1034, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 135)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(135, N'Expected Bill Date', N'Expected Bill Date information', 1035, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 136)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(136, N'Current Profit/Loss', N'Current Profit/Loss information', 1036, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 137)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(137, N'Possible Value To The Client ', N'Possible Value To The Client  information', 1037, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 138)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(138, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', 1038, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 139)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(139, N'Current Loked Up Value', N'Current Loked Up Value', 1039, N'')
END
GO
SET IDENTITY_INSERT [dbo].[Widgets] OFF
GO

---------------------------------------------------Widget Insert End-------------------------------------


---------------------------------------[PanelWidgetAssociation] Insert Start-----------------------------

SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 1)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (1, 1, 100)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 2)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (2, 1, 101)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 3)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (3, 2, 102)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 4)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (4, 4, 103)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 5)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (5, 4, 104)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 6)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (6, 4, 105)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 7)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (7, 1, 106)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 8)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (8, 1, 107)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 9)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (9, 1, 108)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 10)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (10, 1, 109)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 11)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (11, 1, 110)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 12)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (12, 1, 111)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 13)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (13, 1, 112)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 14)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (14, 1, 113)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 15)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (15, 1, 114)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 16)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (16, 1, 115)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 17)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (17, 1, 116)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 18)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (18, 1, 117)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 19)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (19, 1, 118)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 20)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (20, 1, 119)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 21)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (21, 1, 120)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 22)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (22, 3, 121)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 23)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (23, 3, 122)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 24)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (24, 3, 123)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 25)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (25, 3, 124)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 26)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (26, 3, 125)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 27)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (27, 3, 126)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 28)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (28, 3, 127)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 29)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (29, 3, 128)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 30)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (30, 3, 129)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 31)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (31, 3, 130)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 32)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (32, 3, 131)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 33)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (33, 3, 132)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 34)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (34, 3, 133)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 35)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (35, 3, 134)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 36)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (36, 3, 135)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 37)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (37, 3, 136)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 38)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (38, 3, 137)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 39)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (39, 3, 138)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 40)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (40, 3, 139)
END
GO


SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF
GO

------------------------------------[PanelWidgetAssociation] Insert End--------------------------------------------------------

IF NOT EXISTS(SELECT TOP 1 1  FROM SYS.schemas WHERE [name] = 'Common')
BEGIN
	Declare @Query nvarchar(200)
	SET @Query = 'CREATE SCHEMA Common'
	exec sp_executesql @Query
END

GO

IF OBJECT_ID(N'[Common].[Release]', N'U')IS NULL
BEGIN
CREATE TABLE [Common].[Release]
(
	[ReleaseID]		[smallint] IDENTITY(1,1) NOT NULL,
	[Version]		[nvarchar](40) NOT NULL,
	[VersionLabel]	[nvarchar](40) NOT NULL,
	[ReleaseDate]	[datetime] NOT NULL,
	CONSTRAINT [[REDACTED] PRIMARY KEY(ReleaseID)
 )
END
GO

IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'Release' and [NAME] = 'ExecutedPatchFrom')
BEGIN
	ALTER TABLE [Common].[Release]
	ADD [ExecutedPatchFrom] TINYINT DEFAULT 0 NOT NULL
END
GO

IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'Release' and [NAME] = 'ExecutedBy')
BEGIN
	ALTER TABLE [Common].[Release]
	ADD [ExecutedBy] VARCHAR(200)
END
GO

IF OBJECT_ID(N'BriefBuildingWebJobStatus',N'U')IS NULL
BEGIN
	Create TABLE 
		BriefBuildingWebJobStatus
		(
			[WebJobId]			VARCHAR(50) NOT NULL,
			[CreatedTime]		DATETIME,
			[ExpirationTime]	DATETIME,			
			[BriefId]			INT,
			[Status]			VARCHAR(20),
			[NoOfDocument]		INT,
			[Completed]			INT,
			CONSTRAINT [REDACTED]PRIMARY KEY ([WebJobId]),
			CONSTRAINT fk_BriefBuildingWebJobStatus_BriefId_Brief_BriefId FOREIGN KEY (BriefId) REFERENCES Brief(ID)
	)
END
GO
