--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 pk_KaaSCtlMaster_Id 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 pk_KaaSCtlGroupControls_Id PRIMARY KEY(Id)
	)
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

------------------------------------[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 [pk_Release_ReleaseID] 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 NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'Release' and [NAME] = 'UpdatedStatus')
BEGIN
	ALTER TABLE [Common].[Release]
	ADD [UpdatedStatus] TINYINT DEFAULT 0 NOT NULL
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 pk_BriefBuildingWebJobStatus PRIMARY KEY ([WebJobId]),
			CONSTRAINT fk_BriefBuildingWebJobStatus_BriefId_Brief_BriefId FOREIGN KEY (BriefId) REFERENCES Brief(ID)
	)
END
GO

IF OBJECT_ID(N'CustomFilterbyUserandPage',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[CustomFilterbyUserandPage](
		[ID]			[int]			IDENTITY(1,1) NOT NULL,
		[HandlerCode]	[varchar](50)	NOT NULL,
		[PageName]		[varchar](50)	NOT NULL,
		[FilterName]	[varchar](50)	NOT NULL,
		[CreatedDate]	[datetime]		NOT NULL,
		[ActionType]	[varchar](50)	NULL,
		[Priority]		[varchar](50)	NULL,
		[Flag]			[varchar](50)	NULL,
		[IsDefault]		[bit]			DEFAULT 0,
	CONSTRAINT pk_CustomFilterbyUserandPage_id PRIMARY KEY (ID))

END

GO

IF OBJECT_ID(N'AppRedirectionUrls',N'U')IS NULL
BEGIN
	Create TABLE 
		AppRedirectionUrls
		(
			[UrlId]				INT IDENTITY(1,1) NOT NULL,
			[ShortCode]			VARCHAR(100) NOT NULL,
			[Description]		VARCHAR(100) NOT NULL,
			[UrlLink]			VARCHAR(200) NOT NULL,
			[IsActive]		    BIT,
			[CreatedTime]		DATETIME,
			CONSTRAINT pk_AppRedirectionUrls PRIMARY KEY ([UrlId])
	)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE [type] = 'D' And [name] = 'DF_AppRedirectionUrls_IsActive')
BEGIN
	ALTER TABLE [AppRedirectionUrls] ADD  CONSTRAINT [DF_AppRedirectionUrls_IsActive]  DEFAULT ((1)) FOR [IsActive]
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE [type] = 'D' And [name] = 'DF_AppRedirectionUrls_CreatedTime')
BEGIN
	ALTER TABLE [AppRedirectionUrls] ADD  CONSTRAINT [DF_AppRedirectionUrls_CreatedTime]  DEFAULT (GETUTCDATE()) FOR [CreatedTime]
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'WidgetQuery' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD WidgetQuery TEXT NULL
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'RedirectionUrlId' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD RedirectionUrlId INT NULL
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'ActionQuery' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD ActionQuery TEXT NULL
END
GO

IF NOT EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'RedirectionFilter' WHERE SO.[name] = 'Widgets' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [Widgets] ADD RedirectionFilter VARCHAR(300) NULL
END
GO


--SELECT * FROM Widgets

UPDATE Widgets SET RedirectionUrlId = (SELECT UrlId From AppRedirectionUrls WHERE ShortCode='TASK_OVERDUE') WHERE TemplateId = 1000
GO

UPDATE Widgets SET RedirectionUrlId = (SELECT UrlId From AppRedirectionUrls WHERE ShortCode='TASK_OUTSTANDING') WHERE TemplateId = 1001
GO

UPDATE Widgets SET RedirectionUrlId = (SELECT UrlId From AppRedirectionUrls WHERE ShortCode='TASK_MY') WHERE TemplateId IN(1006,1007,1008,1009,1010,1011)
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.ActionType=''C''' WHERE WidgetId = 106
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.ActionType=''O''' WHERE WidgetId = 107
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.Priority=''H''' WHERE WidgetId = 108
GO

UPDATE Widgets SET RedirectionFilter = 'DEL.ActionType=''T''' WHERE WidgetId = 109
GO

IF EXISTS (SELECT SC.* FROM SYS.[objects] SO INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'TemplateQuery' WHERE SO.[name] = 'UITemplates' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [dbo].[UITemplates] DROP COLUMN TemplateQuery
END
GO

IF OBJECT_ID(N'CustomFilterbyUserandPage',N'U') IS NOT NULL  
BEGIN
	IF NOT EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'FilterOrder'
			  AND Object_ID = Object_ID(N'CustomFilterbyUserandPage',N'U'))
		BEGIN
		ALTER TABLE [dbo].[CustomFilterbyUserandPage] ADD FilterOrder int default 0
		END
	
END 
GO

IF OBJECT_ID(N'CustomFilterbyUserandPage',N'U') IS NOT NULL  
BEGIN
	IF EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'FilterOrder'
			  AND Object_ID = Object_ID(N'CustomFilterbyUserandPage',N'U'))
		BEGIN
			UPDATE  [dbo].[CustomFilterbyUserandPage] SET FilterOrder = 0 WHERE FilterOrder is null
		END
END 
GO

IF OBJECT_ID(N'CustomSavedFilters',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].CustomSavedFilters(
		[ID]			[int]			IDENTITY(1,1) NOT NULL,
		[FilterID]		[int]			NOT NULL,
		[FilterColumn]	[varchar](50)	NOT NULL,
		[Value]			[varchar](2000)	NOT NULL
	CONSTRAINT pk_CustomSavedFilters_id PRIMARY KEY (ID) 
	)
END

GO

--- Custom Filter Defined Script Start

IF OBJECT_ID(N'CustomFilterColumndetails',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].CustomFilterColumndetails(
		[ID]				[int]			IDENTITY(1,1) NOT NULL,
		[name]				[VARCHAR](50)	NOT NULL,
		[Code]				[varchar](50)	NOT NULL,
		[isSelected]		bit				NOT NULL DEFAULT 1,
		[FilterOrder]		int				NOT NULL DEFAULT 0,
		[IsIconAvailable]	bit				NOT NULL DEFAULT 0,
		[Iconpath]			[varchar](50)	NOT NULL DEFAULT '',
		[ColumnName]		[varchar](50)	NOT NULL,
		[PageName]			[varchar](50)	NOT NULL
	CONSTRAINT pk_CustomFilterColumndetails_id PRIMARY KEY (ID) 
	)
END

GO


IF NOT EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO 
INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'HandlerCode'
 WHERE SO.[name] = 'TransactViewProperty' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [dbo].[TransactViewProperty] 
	ADD [HandlerCode] VARCHAR(10)
END 
GO

IF NOT EXISTS (SELECT TOP 1 1  FROM sys.objects where type='F' and name ='FK_Handlers_Code_TransactViewProperty_HandlerCode') 
BEGIN	
	ALTER TABLE [dbo].[TransactViewProperty]  WITH NOCHECK
	ADD  CONSTRAINT [FK_Handlers_Code_TransactViewProperty_HandlerCode] FOREIGN KEY([HandlerCode]) REFERENCES [dbo].[Handlers] ([CODE])
END
GO

IF EXISTS (SELECT TOP 1 1  FROM sys.objects where type='F' and name ='FK_Handlers_Code_TransactViewProperty_HandlerCode') 
BEGIN
	ALTER TABLE [dbo].[TransactViewProperty] 
	CHECK CONSTRAINT [FK_Handlers_Code_TransactViewProperty_HandlerCode]
END
GO

IF OBJECT_ID(N'TransactUDFFieldMapping',N'U') IS NULL
BEGIN

CREATE
 TABLE [dbo].[TransactUDFFieldMapping]
      ([Id]							INT	IDENTITY(1,1),
       [TransactUDFField]           NVARCHAR(60),
       [KeyhouseUDFField]           NVARCHAR(60),
       [IsRetired]                  BIT      DEFAULT 0  NOT NULL,
       CONSTRAINT [pk_TransactUDFFieldMapping]
                  PRIMARY KEY CLUSTERED
                 ([Id])
		)
END

GO
																						   
IF NOT EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO 
INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] AND SC.[name] = 'TransactSide'
 WHERE SO.[name] = 'TransactUDFFieldMapping' AND SO.[type] = 'U')
BEGIN
	ALTER TABLE [dbo].[TransactUDFFieldMapping] 
	ADD [TransactSide] VARCHAR(1) DEFAULT 'C' NOT NULL
END 

GO



--Updating seller price UDF field to Purchaser price as discussed for TRansact - Keyhouse integration 
IF NOT  EXISTS(SELECT TOP 1 1 FROM [TransactUDFFieldMapping] WHERE TransactUDFField = 'UDFPropertyPurchasePrice' AND KeyhouseUDFField='PropertyPurchasePrice')
	BEGIN
	IF EXISTS(SELECT TOP 1 1 FROM [TransactUDFFieldMapping] WHERE TransactUDFField = 'UDFPropertySalePrice')
	BEGIN
		UPDATE [TransactUDFFieldMapping] SET TransactUDFField = 'UDFPropertyPurchasePrice' , KeyhouseUDFField='PropertyPurchasePrice' WHERE TransactUDFField = 'UDFPropertySalePrice'

	END
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [SystemUserDefinedFields] WHERE FIELDNAME = 'PropertyPurchasePrice')
BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM [SystemUserDefinedFields] WHERE FIELDNAME = 'PropertySalePrice')
		BEGIN
			UPDATE [SystemUserDefinedFields] SET FIELDNAME = 'PropertyPurchasePrice' , PROMPT='Enter property purchase price' WHERE FIELDNAME = 'PropertySalePrice'

		END
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Udftemplatesworktypes] WHERE UDUVCODE = 'PropertyPurchasePrice')
BEGIN
		IF EXISTS(SELECT TOP 1 1 FROM [Udftemplatesworktypes] WHERE UDUVCODE = 'PropertySalePrice')
		BEGIN
			UPDATE [Udftemplatesworktypes] SET UDUVCODE = 'PropertyPurchasePrice' WHERE UDUVCODE = 'PropertySalePrice'

		END
END
GO

IF OBJECT_ID(N'DefaultPageColumnsByHanlder',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[DefaultPageColumnsByHanlder](
		[ID]				[int]				IDENTITY(1,1) NOT NULL,
		[HandlerCode]		[varchar](50)		NOT NULL,
		[PageName]			[varchar](50)		NOT NULL,
		[DefaultColumns]	[nvarchar](MAX)		NOT NULL
	CONSTRAINT pk_DefaultPageColumnsByHanlder_id PRIMARY KEY (ID))

END

GO

-- Alter script to include publish column for UDF fields
IF EXISTS 
(SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.columns [SC]
                       ON [SC].object_id = [SO].object_id
                      AND [SC].[name] = 'PUBLISH'
    WHERE [SO].[name] = 'SystemUserDefinedFields'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)

BEGIN
	ALTER TABLE [dbo].[SystemUserDefinedFields]
	ADD [PUBLISH] BIT NOT NULL
	CONSTRAINT [DF_SystemUserDefinedFields_PUBLISH] DEFAULT (0)
END
GO

IF OBJECT_ID(N'PortalNotificationTemplate',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[PortalNotificationTemplate](
		[PortalTemplateId]		[INT]				IDENTITY(1,1) NOT NULL,
		[TemplateType]			[VARCHAR](100)		NOT NULL,
		[TemplateSubject]		[VARCHAR](100)		NULL,
		[TemplateBody]			[VARCHAR](MAX)		NOT NULL
	CONSTRAINT PK_PortalNotificationTemplate_Id PRIMARY KEY ([PortalTemplateId]))

END
GO


IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'DiaryAttachments' and [NAME] = 'UploadId')
BEGIN
	ALTER TABLE [dbo].[DiaryAttachments]
	ADD [UploadId] UNIQUEIDENTIFIER
END
GO

IF OBJECT_ID(N'NotificationDetails',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[NotificationDetails]
	(
		[NotificationId] 			INT 			NOT NULL IDENTITY(1,1),
		[NotifyCreatedUserCode] 	VARCHAR(10)		NULL,
		[NotifyMessage] 			VARCHAR(MAX)	NULL,
		[NotifyCreatedDate] 		DATETIME		NULL,
		[NotifyReceiverUserCode] 	VARCHAR(10)		NULL,
		[NotifyStatus]  			VARCHAR(1)		NULL,
		[NotifyHeader]  			VARCHAR(500)	NULL,
		[NotifyRedirectURL]			VARCHAR(MAX)	NULL
		CONSTRAINT PK_NotificationDetails_Id PRIMARY KEY ([NotificationId])
	)
END
GO

-- Included Field in CustomFilterbyUserandPage to save current grid state
IF NOT EXISTS (SELECT TOP 1 1  FROM SYS.OBJECTS obj
	INNER JOIN SYS.COLUMNS col on col.object_id = obj.object_id 
	WHERE obj.[name] = 'CustomFilterbyUserandPage' and col.[name] = 'GridViewState')
BEGIN
	ALTER TABLE
		[dbo].[CustomFilterbyUserandPage]
	ADD
		[GridViewState] NVARCHAR(MAX)
END
GO


IF OBJECT_ID(N'ControlAccountLog',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[ControlAccountLog]
	   (
			 [Id]			                   [INT] IDENTITY(1,1) NOT NULL,
			 [User]			                   [VARCHAR](10),
			 [EntryDate]	                   [DATETIME],
			 [ModifiedDate]                    [DATETIME],
			 [LastBatchNo]                     [INT],
			 [ClientAcMatterBalance]           [DECIMAL](17,2),
			 [OutlayAcMatterBalance]           [DECIMAL](17,2),
			 [BillingAcMatterBalance]          [DECIMAL](17,2),
			 [SupplierAcMatterBalance]         [DECIMAL](17,2),
			 [ClientControlAccounts]           [DECIMAL](17,2),
			 [OutlayControlAccounts]           [DECIMAL](17,2),
			 [BillingControlAccounts]          [DECIMAL](17,2),
			 [SupplierControlAccounts]         [DECIMAL](17,2),
			 CONSTRAINT pk_ControlAccountHistory_Id PRIMARY KEY(Id),
			 CONSTRAINT [Fk_ControlAccountHistory_User] FOREIGN KEY([User]) REFERENCES [dbo].[Handlers] ([CODE])
		) 
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'NotificationDetails' and [NAME] = 'NotifyType')
BEGIN
 ALTER TABLE [dbo].[NotificationDetails] 
		 ADD [NotifyType]	VARCHAR(1)	NULL
 ALTER TABLE NotificationDetails ADD NotifyErrorMessage VARCHAR(MAX)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'NotificationDetails' and [NAME] = 'NotifyErrorMessage')
BEGIN
 ALTER TABLE [dbo].[NotificationDetails] 
		 ADD [NotifyErrorMessage] VARCHAR(MAX)	NULL
END
GO

IF OBJECT_ID(N'RequestFileLog',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[RequestFileLog]
	   (
			 [RequestId]			       [INT] IDENTITY(1,1) NOT NULL,
			 [Recepient]			       [VARCHAR](30),
			 [Subject]	                   [VARCHAR](30),
			 [Message]                     [VARCHAR](500),
			 [RequestLink]                 [VARCHAR](1000),
			 [RequestedDate]			   [DATETIME]
		     CONSTRAINT PK_RequestFileLog_RequestId PRIMARY KEY ([RequestId])
		) 
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'RequestFileLog' and [NAME] = 'FileUniqueId')
BEGIN
 ALTER TABLE [dbo].[RequestFileLog] 
		 ADD [FileUniqueId]	[VARCHAR](40) NULL
END
GO

IF OBJECT_ID(N'TransactNoteUpdate',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[TransactNoteUpdate](
			 [NoteUpdateId]			       [INT] IDENTITY(1,1) NOT NULL,
			 [ActionId]					   [INT]			   NOT NULL,
			 [ChangeDate]	               [DATETIME]		   NOT NULL,
			 [TransactUpdate]              [DATETIME]		   NULL
		     CONSTRAINT PK_TransactNoteUpdate_NoteUpdateId PRIMARY KEY ([NoteUpdateId]),
			 CONSTRAINT [Fk_TransactNoteUpdate_ActionId_Diary_ActionId] 
			 FOREIGN KEY([ActionId]) REFERENCES [dbo].[diary] ([ActionId])
			 ON DELETE CASCADE
	) 
END
GO

-- Included Non-Clustered index as per Pino's suggestion
IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_MatterCode'
    WHERE [SO].[name] = 'TransactViewProperty'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX
       [IDX_MatterCode]
    ON [dbo].[TransactViewProperty]
      ([MatterCode])
       INCLUDE ([TransactionId],
                [HandlerCode])
END
GO

IF  EXISTS 
	(SELECT 1 
	   FROM SYS.[objects] SO 
			INNER JOIN [sys].[columns] SC  
					ON SC.[object_id] = SO.[object_id] 
				   AND SC.[name] = 'WidthChars'
	  WHERE SO.[name] = 'WebFields' 
	    AND SO.[type] = 'U')
BEGIN
	 ALTER TABLE [dbo].[WebFields]  ALTER COLUMN [WidthChars] INT;
END 
GO

-- ACTION TYPE DESC DATATYPE CHANGE - FRAMEWORK CHANGE BY PINO
IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          INNER JOIN sys.columns [SC]
                  ON [SC].object_id = [SO].object_id
                 AND [SC].[name] = 'ActionTypeDescription'
                 AND [SC].[system_type_id] <> 231
    WHERE [SO].[name] = 'DiaryDelActionType'
      AND [SO].[type] = 'U')
BEGIN

UPDATE [dbo].[DiaryDelActionType] SET [ActionTypeDescription] = ISNULL([ActionTypeDescription], N'')

ALTER TABLE [dbo].[DiaryDelActionType] ALTER COLUMN [ActionTypeDescription] NVARCHAR(40) NOT NULL

UPDATE [dbo].[DiaryDelActionType] SET [ActionTypeDescription] = RTRIM([ActionTypeDescription])

END
GO


-- Drop Index for CRReport table to insert report id manually
IF EXISTS
  (SELECT TOP 1 1 
	  From sys.indexes 
WHERE name='IX_Name' AND object_id = OBJECT_ID('CRReport'))
Begin
DROP INDEX CRReport.IX_Name;
End
Go


-- Create table KaaS_CRReport if not exist
IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaS_CRReport')
BEGIN
	CREATE TABLE [dbo].[KaaS_CRReport](
	[Reportid] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
	[name] [varchar](500) NULL,
	[filename] [varchar](500) NULL,
	[lastrundate] [datetime] NULL,
	[lastruntime] [datetime] NULL,
	[confrimparameter] [varchar](50) NULL,
	[created_on] [datetime] NULL,
	[modified_on] [datetime] NULL,
	[status] [char](10) NULL,
	[report_type] [char](10) NULL,
	[notes] [varchar](5000) NULL,
	[synopsis] [varchar](5000) NULL,
CONSTRAINT [PK_KaaSCRReport] PRIMARY KEY CLUSTERED 
(
	[Reportid] ASC
))
End
GO


-- Delete Non-Clustered index
IF EXISTS
(SELECT TOP 1 1 
    FROM sys.indexes 
    WHERE name='IDX_ReportId' 
          AND object_id = OBJECT_ID('KaaS_CRReport')
          AND Type_Desc = 'NONCLUSTERED')
BEGIN
  DROP INDEX [KaaS_CRReport].[IDX_ReportId]
END
GO



-- Create table KaaS_CRGroup if not exist
IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaS_CRGroup' )
BEGIN
CREATE TABLE [dbo].[KaaS_CRGroup](
	[groupid] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
	[groupname] [varchar](500) NULL,
	[created_on] [datetime] NULL,
	[modified_on] [datetime] NULL,
	[status] [char](10) NULL,
 CONSTRAINT [PK_KaaSCrystalReport_Group] PRIMARY KEY CLUSTERED 
(
	[groupid] ASC
))
End
GO


-- Delete Non-Clustered index
IF EXISTS
(SELECT TOP 1 1 
    FROM sys.indexes 
    WHERE name='IDX_GroupId' 
          AND object_id = OBJECT_ID('KaaS_CRGroup')
          AND Type_Desc = 'NONCLUSTERED')
BEGIN
  DROP INDEX [KaaS_CRGroup].[IDX_GroupId]
END
GO


-- Create table KaaS_CRParameter if not exist
IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaS_CRParameter' )
BEGIN
CREATE TABLE [dbo].[KaaS_CRParameter](
	[paramid] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
	[reportid] [decimal](18, 0) NOT NULL,
	[userprompt] [varchar](100) NULL,
	[parametername] [varchar](100) NULL,
	[parametervalue] [varchar](100) NULL,
	[parameterformat] [varchar](50) NULL,
	[parametertype] [varchar](50) NULL,
	[parameterlength] [decimal](18, 0) NULL,
	[defaultvalue] [varchar](100) NULL,
	[requiredfield] [char](10) NULL,
	[userrequest] [char](10) NULL,
	[infile] [varchar](50) NULL,
	[controltype] [varchar](50) NULL,
	[listoption] [text] NULL,
	[Seqno] [int] NULL,
 CONSTRAINT [PK_KaaSCRParameter] PRIMARY KEY CLUSTERED 
(
	[paramid] ASC
))
End
GO


-- Delete Non-Clustered index
IF EXISTS
(SELECT TOP 1 1 
    FROM sys.indexes 
    WHERE name='IDX_ParamId' 
          AND object_id = OBJECT_ID('KaaS_CRParameter')
          AND Type_Desc = 'NONCLUSTERED')
BEGIN
  DROP INDEX [KaaS_CRParameter].[IDX_ParamId]
END
GO


-- Create table KaaS_CRReportGroup if not exist
IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KaaS_CRReportGroup' )
BEGIN
CREATE TABLE [dbo].[KaaS_CRReportGroup](
	[Reportid] [decimal](18, 0) NOT NULL,
	[groupid] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
End
GO



-- CREATE A TABLE TO INCLUDE KH PERMISSIONS ALONG WITH THE CONTROLS
IF OBJECT_ID(N'[dbo].[KaasControlMaster]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[KaasControlMaster](
		[ControlMasterId]					[INT] IDENTITY(1,1) NOT NULL,
		[ControlGroupName]					[VARCHAR](100)      NOT NULL,
		[ComponentName]						[VARCHAR](100)      NOT NULL,
		[ElementType]						[VARCHAR](100)      NOT NULL,
		[ElementId]							[VARCHAR](100)      NOT NULL,
		[IsConfigurable]					[BIT]				NOT NULL,
		[PermissionMode]					[INT]				NOT NULL
		CONSTRAINT [PK_ControlMasterId] PRIMARY KEY ([ControlMasterId])
)
END
GO

--ADD DEFAULT VALUE FOR IsConfigurable COLUMN
IF OBJECT_ID(N'DF_KaasControlMaster_IsConfigurable',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[KaasControlMaster]
	ADD CONSTRAINT 
		[DF_KaasControlMaster_IsConfigurable]  
	DEFAULT 
		((0)) 
	FOR 
		[IsConfigurable]
END
GO

--ADD FOREIGH KEY CONSTRAINTS 
IF OBJECT_ID(N'FK_KaasControlMaster_ControlGroupName_KHCtlGroups_GroupName',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[KaasControlMaster] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_KaasControlMaster_ControlGroupName_KHCtlGroups_GroupName]  
	FOREIGN KEY 
		([ControlGroupName])
	REFERENCES 
		[dbo].[KHCtlGroups] ([GroupName])
END
GO

IF OBJECT_ID(N'FK_KaasControlMaster_ControlGroupName_KHCtlGroups_GroupName',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[KaasControlMaster] 
	CHECK CONSTRAINT
		[FK_KaasControlMaster_ControlGroupName_KHCtlGroups_GroupName]
END
GO


-- CREATE A TABLE TO INCLUDE KH PERMISSIONS CONTROLS AND GROUPS MAPPING
IF OBJECT_ID(N'[dbo].[KaasGroupControlsMap]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[KaasGroupControlsMap](
		[GroupControlMapId]					[INT] IDENTITY(1,1) NOT NULL,
		[GroupId]							[INT]				NOT NULL,
		[MasterId]							[INT]				NOT NULL
		CONSTRAINT [PK_GroupControlMapId] PRIMARY KEY ([GroupControlMapId])
)
END
GO


--ADD FOREIGH KEY CONSTRAINTS 
IF OBJECT_ID(N'FK_KaasGroupControlsMap_GroupId_KHCtlGroups_id',N'F') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[KaasGroupControlsMap] 
	WITH NOCHECK ADD CONSTRAINT 
		[FK_KaasGroupControlsMap_GroupId_KHCtlGroups_id]  
	FOREIGN KEY 
		([GroupId])
	REFERENCES 
		[dbo].[KHCtlGroups] ([id])
END
GO

IF OBJECT_ID(N'FK_KaasGroupControlsMap_GroupId_KHCtlGroups_id',N'F') IS NOT NULL
BEGIN
	ALTER TABLE 
		[dbo].[KaasGroupControlsMap] 
	CHECK CONSTRAINT
		[FK_KaasGroupControlsMap_GroupId_KHCtlGroups_id]
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'DIARY' and [NAME] = 'UploadActionId')
BEGIN
 ALTER TABLE [dbo].[DIARY] 
		 ADD [UploadActionId] UNIQUEIDENTIFIER 
END
GO

-- Included Non-Clustered index for upload action id
IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_UploadActionId'
    WHERE [SO].[name] = 'diary'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX
       [IDX_UploadActionId]
    ON [dbo].[diary]
      ([UploadActionId] ASC)
INCLUDE([ACTIONID], [CASECODE]) 
WHERE ([UploadActionId] IS NOT NULL)
END
GO

IF EXISTS
	(SELECT TOP 1 1
	   FROM sys.objects [SO]
	   LEFT OUTER JOIN sys.columns [SC]
	     ON [SC].object_id = [SO].object_id
		AND [SC].[name] = 'Comments'
	  WHERE [SO].[name] = 'VATCodes'
		AND [SO].[type] = 'U'
		AND [SC].[column_id] IS NULL)
BEGIN
	ALTER TABLE [dbo].[VATCodes]
			ADD [Comments] VARCHAR(2000)
	 CONSTRAINT [DF_Comments] DEFAULT('') NOT NULL
END
GO

-- INCLUDED RETIRED COLUMN FOR NOMINAL TYPE 
-- TICKET REF: 17077 Add the "Retired" Column in the "Browse nominal Types" dialog
-- WE WILL USE THIS COLUMN TO MARK THIS RETIRED IF THE USER TRIES TO DELETE A NOMINAL TYPE
IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.columns [SC]
                       on [SC].object_id = [SO].object_id
                      and [SC].[name] = 'RETIRED'
    WHERE [SO].[name] = 'NominalTypes'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
BEGIN

ALTER 
TABLE [dbo].[NominalTypes]
  ADD [RETIRED] CHAR(1) NOT NULL
      CONSTRAINT [DF_NominalTypes_Retired] DEFAULT('N')
      CONSTRAINT [CC_NominalTypes_Retired] CHECK([RETIRED] IN ('N', 'Y'))

END
GO

-- INCLUDED RETIRED COLUMN FOR VAT CODE
-- TICKET REF: 17075 Add the option to "Delete" VAT Codes
-- WE WILL USE THIS COLUMN TO MARK THIS RETIRED IF THE USER TRIES TO DELETE A VAT CODE
IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.columns [SC]
                       on [SC].object_id = [SO].object_id
                      and [SC].[name] = 'RETIRED'
    WHERE [SO].[name] = 'VATCodes'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
BEGIN

ALTER 
TABLE [dbo].[VATCodes]
  ADD [RETIRED] CHAR(1) NOT NULL
      CONSTRAINT [DF_VATCodes_Retired] DEFAULT('N')
      CONSTRAINT [CC_VATCodes_Retired] CHECK([RETIRED] IN ('N', 'Y'))

END
GO


----SAM User Permission DB Scripts

IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'AccountMasterMenu'
      AND SO.[type] = 'U')
BEGIN
	DROP TABLE [dbo].[AccountMasterMenu]
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'KeyhouseMenuList' )
BEGIN

CREATE TABLE [dbo].[KeyhouseMenuList](
	[MenuId]				INT IDENTITY(1,1) NOT NULL,
	[MenuName]				VARCHAR(150)	  NOT NULL,
	[MenuParentId]			INT				  NOT NULL,
	[MenuRoute]				VARCHAR(1000)	  NULL,
	[MenuIconName]			VARCHAR(100)	  NULL,
	[MenuCode]				VARCHAR(20)		  NULL,
	[MenuIsActive]			BIT				  NULL,
	[MenuOrder]				INT				  NULL,
	[MenuOpenNewTab]		BIT				  NULL,
	[MenuOpenDialogBox]		BIT				  NULL,
	[MenuApplicationType]	VARCHAR(1)		  NULL,
 CONSTRAINT [PK_KeyhouseMenuList_MenuId] PRIMARY KEY CLUSTERED ([MenuId] ASC),
 CONSTRAINT [UQ_KeyhouseMenuList_MenuCode] UNIQUE NONCLUSTERED ([MenuCode] ASC)) 
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE [type] = 'D' And [name] = 'DF_KeyhouseMenuList_MenuOpenNewTab')
BEGIN
	ALTER TABLE
		[dbo].[KeyhouseMenuList]
	ADD  CONSTRAINT
		[DF_KeyhouseMenuList_MenuOpenNewTab] 
	DEFAULT ((0))
		FOR [MenuOpenNewTab]
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE [type] = 'D' And [name] = 'DF_KeyhouseMenuList_MenuOpenDialogBox')
BEGIN
	ALTER TABLE
		[dbo].[KeyhouseMenuList]
	ADD CONSTRAINT
		[DF_KeyhouseMenuList_MenuOpenDialogBox] 
	DEFAULT ((0))
		FOR [MenuOpenDialogBox]
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[objects] where OBJECT_NAME(OBJECT_ID) = 'HandlerMenuPermissionMap' )
BEGIN
CREATE TABLE [dbo].[HandlerMenuPermissionMap]
(
	HandlerPermissionMapId INT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_HandlerMenuPermissionMap_HandlerPermissionMapId PRIMARY KEY CLUSTERED (HandlerPermissionMapId),
	HandlerCode			   VARCHAR(10)		 NOT NULL,
	MenuId				   INT				 NOT NULL,
	ReadAccess			   BIT CONSTRAINT DF_HandlerMenuPermissionMap_ReadAccess DEFAULT 0,
	WriteAccess			   BIT CONSTRAINT DF_HandlerMenuPermissionMap_WriteAccess DEFAULT 0,
	LastModifiedBy		   VARCHAR(10),
	LastModifiedDate	   DATETIME
)
END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'HandlerMenuPermissionType')
BEGIN
    CREATE TYPE [dbo].[HandlerMenuPermissionType] AS TABLE
    (
        MenuId INT,
        HandlerPermissionMapId INT,
        ReadAccess BIT,
        WriteAccess BIT
    );
END
GO

IF EXISTS
	(SELECT TOP 1 1 
	   FROM [sys].[objects] 
	  WHERE OBJECT_NAME(OBJECT_ID) = 'CustomSavedFilters' )
BEGIN
	ALTER TABLE 
		dbo.CustomSavedFilters
	ALTER 
		COLUMN [Value] VARCHAR(MAX) NOT NULL

END
GO

IF OBJECT_ID(N'DNDSearchActivityLog',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[DNDSearchActivityLog]
	(
		[ActivityLogId] 	INT IDENTITY(1,1)   NOT NULL,
		[BatchNo] 			INT					NULL,
		[TrackRef] 			INT					NULL,
		[Date] 				DATETIME			NULL,
		[Comments] 			VARCHAR(MAX)		NULL
	)
END
GO



IF OBJECT_ID(N'KaaS_CRReportUserPermission',N'U') IS NULL
BEGIN

CREATE TABLE [dbo].[KaaS_CRReportUserPermission]
   (
	   [ReportUserPermissionId]   [BIGINT]        IDENTITY(1,1) CONSTRAINT pk_KaaS_CRReportUserPermission_ReportUserPermissionId PRIMARY KEY,
	   [ReportId]                 [DECIMAL]                     CONSTRAINT FK_KaaS_CRReports_ReportId_KaaS_CRReportUserPermission_ReportId FOREIGN KEY REFERENCES KaaS_CRReport(ReportId),
	   [HandlerCode]              [VARCHAR](10)   NULL,
	   [HasAccess]                [BIT]           NOT NULL
 )

END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.columns [SC]
                       on [SC].object_id = [SO].object_id
                      and [SC].[name] = 'ReportNameKey'
    WHERE [SO].[name] = 'Kaas_CRReport'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
BEGIN
ALTER TABLE Kaas_CRReport
ADD ReportNameKey VARCHAR(500) NULL
END
GO


-- SPRINT V4.20.0.X
-- ExternalDictations TABLE WILL SERVE AS LOG FOR DICTATION EMAILS BEING SENT OUT TO EXTERNAL TYPIST
-- ExternalDictations TABLE CREATION START
IF NOT EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
    WHERE [SO].[name] = 'ExternalDictations'
      AND [SO].[type] = 'U')
BEGIN

CREATE
 TABLE [dbo].[ExternalDictations]
      ([RecordId]            BIGINT          IDENTITY(1,1)      NOT NULL,
       [TrackReference]      INT                                NOT NULL,
       [Status]              INT                                NOT NULL,
       [DateField]           DATETIME                           NOT NULL
                             CONSTRAINT [DF_ED_DateField]
                                        DEFAULT GETDATE(),
       CONSTRAINT [PK_ExternalDictations]
                  PRIMARY KEY CLUSTERED
                 ([RecordId])
               ON [PRIMARY],
       CONSTRAINT [CK_ED_Status]
                  CHECK ([Status] IN (0,     -- 0 = Created
                                      1,     -- 1 = Sent to Typist
                                      2      -- 2 = Returned by Typist
                                       )))
    ON [PRIMARY]

END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_TrackReference'
    WHERE [SO].[name] = 'ExternalDictations'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
BEGIN

CREATE UNIQUE NONCLUSTERED INDEX
       [IDX_TrackReference]
    ON [dbo].[ExternalDictations]
      ([TrackReference])
       INCLUDE ([Status],
                [DateField])

END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_SentOrReceived'
    WHERE [SO].[name] = 'ExternalDictations'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
BEGIN

CREATE UNIQUE NONCLUSTERED INDEX
       [IDX_SentOrReceived]
    ON [dbo].[ExternalDictations]
      ([Status],
       [TrackReference])
       INCLUDE ([DateField])

END
GO
-- ExternalDictations TABLE CREATION END