--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 OBJECT_ID(N'KaaSCtlMaster',N'U') IS NULL
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 OBJECT_ID(N'KaaSCtlGroupControls',N'U') IS NULL
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 OBJECT_ID(N'RecentHandlerList',N'U') IS NULL
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 OBJECT_ID(N'Panels',N'U') IS NULL
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 OBJECT_ID(N'Widgets',N'U') IS NULL
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 OBJECT_ID(N'PanelWidgetAssociation',N'U') IS NULL
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 OBJECT_ID(N'UserPanels', N'U') IS NULL
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 OBJECT_ID(N'UserWidgets', N'U') IS NULL
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 OBJECT_ID(N'UITemplates', N'U') IS NULL
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 OBJECT_ID(N'KaaS_CRReport',N'U') IS NULL
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 OBJECT_ID(N'KaaS_CRGroup',N'U') IS NULL
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 OBJECT_ID(N'KaaS_CRParameter',N'U') IS NULL
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 OBJECT_ID(N'KaaS_CRReportGroup',N'U') IS NULL
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 OBJECT_ID(N'KeyhouseMenuList',N'U') IS NULL
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 OBJECT_ID(N'HandlerMenuPermissionMap',N'U') IS NULL
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

-- UPDATED SCRIPT ON EXTERNAL DICTATION START
-- NEW TABLES ADDED TO RETRIEVE RemoteTypist & RemoteTypistDate 
-- Comments By PINO: SCHEMA BOUND VIEW DOESN'T ALLOW JOINS TO SAME TABLE TWICE
IF NOT EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
    WHERE [SO].[name] = 'RemoteTypist'
      AND [SO].[type] = 'U')
BEGIN

CREATE
 TABLE [dbo].[RemoteTypist]
      ([Code]    VARCHAR(10)          NOT NULL,
       CONSTRAINT [PK_RemoteTypist]
                  PRIMARY KEY CLUSTERED
                 ([Code]))

INSERT
  INTO [dbo].[RemoteTypist]
      ([Code])
SELECT [HAN].[Code]
  FROM [dbo].[Settings] [SET]
       INNER JOIN [dbo].[Handlers] [HAN]
               ON [HAN].[CODE] = [SET].[KeyValue]
 WHERE [SET].[KeyName] = 'RemoteTypist'

END
GO

-- EXECPTION SCENARIO FOR DATAPUMP: CANNOT EXECUTE TABLE CREATION SCRIPT WITHOUT UPDATE
IF NOT EXISTS
  (SELECT TOP 1 1
     FROM [dbo].[Settings] [SET]
    WHERE [SET].[KeyName] = 'RemoteTypistStartDate')
BEGIN

INSERT
  INTO [dbo].[Settings]
      ([KeyName],
       [KeyValue])
VALUES('RemoteTypistStartDate',
       CONVERT(VARCHAR, GETDATE(), 112))

END
GO

IF NOT EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
    WHERE [SO].[name] = 'RemoteTypistStartDate'
      AND [SO].[type] = 'U')
BEGIN

CREATE
 TABLE [dbo].[RemoteTypistStartDate]
      ([StartDate]    DATETIME          NOT NULL,
       CONSTRAINT [PK_RemoteTypistStartDate]
                  PRIMARY KEY CLUSTERED
                 ([StartDate]))

INSERT
  INTO [dbo].[RemoteTypistStartDate]
      ([StartDate])
VALUES(CONVERT(DATETIME, CONVERT(DATE, GETDATE())))

END
GO

-- UPDATED SCRIPT ON EXTERNAL DICTATION START

-- INCLUDED DormantClientBalReason COLUMN FOR MATTER LEDGER
-- TICKET REF #18580: New Facility to Record the Reason for Dormant Client Accounts
-- WE WILL USE THIS COLUMN TO MARK A MATTER LEDGER COMMENT AS REASON FOR DORMANT CLIENT ACCOUNT BALANCE
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] = 'IsDormantClientBalReason'
    WHERE [SO].[name] = 'MatterLedger'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
BEGIN

	ALTER 
	TABLE [dbo].[MatterLedger]
	ADD [IsDormantClientBalReason] BIT NOT NULL
      CONSTRAINT [DF_MatterLedger_IsDormantClientBalReason] DEFAULT(0)

END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CaseWorkFlowActionsType')
 BEGIN
CREATE TYPE [dbo].[CaseWorkFlowActionsType] AS TABLE(
	[ACTIONCODE]			VARCHAR(15) NULL,
	[Actioncategory]		VARCHAR(1) NULL,
	[ACTTYPE]				VARCHAR(1) NULL,
	[SASSIGNHANDLER]		VARCHAR(10) NULL,
	[ASSIGNTEAM]			VARCHAR(10) NULL,
	[Arrangement]			VARCHAR(3) NULL ,
	[AssignActionTo]		VARCHAR(1) NULL,
	[Authby]				VARCHAR(10) NULL,
	[AuthorisationRequired] VARCHAR(1) NULL,
	[AutoPopulate]			VARCHAR(1) NULL,
	[BILLABLE]				VARCHAR(1) NULL,
	[BILLDESCRIPTION]		VARCHAR(150) NULL,
	[ChargeInterest]		VARCHAR(1) NULL,
	[ChargeValue]			VARCHAR(9) NULL,
	[ConfirmByPW]			VARCHAR(1) NULL,
	[DESC]					VARCHAR(100) NULL,
	[Description]			VARCHAR(100) NULL,
	[EMAIL]					VARCHAR(1) NULL,
	[HIGHLIGHTED]			VARCHAR(1) NULL,
	[MILESTONETYPE]			VARCHAR(12) NULL,
	[NEXTACTIONOPTIONAL]	VARCHAR(1) NULL,
	[PRIORITY]				VARCHAR(6) NULL,
	[PROCESSTYPE]			VARCHAR(1) NULL,
	[PUBLISHER]				VARCHAR(1) NULL,
	[RequiredAction]		VARCHAR(1) NULL,
	[STATUS]				VARCHAR(3) NULL,
	[UPDATEFIELD]			VARCHAR(30) NULL,
	[UPDATEVALUE]			VARCHAR(255) NULL,
	[WKTCODE]				VARCHAR(4) NULL,
	[AssignTeamActionTo]	VARCHAR(1) NULL,
	[Flag]					TINYINT NULL,
	[RecordStatus]			INT NULL
)
END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanActionMappingDetailsType')
 BEGIN
	CREATE TYPE [dbo].[CasePlanActionMappingDetailsType] AS TABLE(
		[WORKTYPE]	  VARCHAR(10) NULL,
		[ACTIONCODE]  VARCHAR(10) NULL,
		[SEQ]		  INT NULL,
		[ForCopy]     VARCHAR(10) NULL
	)
 END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanDocumentDetailsType')
 BEGIN
CREATE TYPE [dbo].[CasePlanDocumentDetailsType] AS TABLE(
	[CODE]                     VARCHAR(10) NULL,
	[DICTATION]                VARCHAR(10) NULL,
	[DOCCLASS]                 VARCHAR(3) NULL,
	[EndToken]                 VARCHAR(2) NULL,
	[FILEPATH]                 VARCHAR(255) NULL,
	[NAME]		               VARCHAR(100) NULL,
	[OPENDURINGDICTATION]      VARCHAR(1) NULL,
	[OverWrite]				   VARCHAR(1) NULL,
	[STORE]					   VARCHAR(1) NULL,
	[StartToken]			   VARCHAR(2) NULL,
	[TYPE]					   VARCHAR(5) NULL,
	[ACTION]				   VARCHAR(15),
	[DOCUMENT]				   VARCHAR(10) NULL,
	[OPTIONAL]				   VARCHAR(1) NULL,
	[RecordStatus]			   INT NULL
)
 END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanUdfType')
 BEGIN
CREATE TYPE [dbo].[CasePlanUdfType] AS TABLE(
	[IncludeInMatterCopy] VARCHAR(1) NULL,
	[UDFILE]			  VARCHAR(5) NULL,
	[UDSEQ]				  INT NULL,
	[UDUVCODE]			  VARCHAR(50) NULL,
	[UDWKCODE]			  VARCHAR(10) NULL,
	[RecordStatus]		  INT NULL
)
 END
GO


IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanFollowUpActionType')
 BEGIN
CREATE TYPE [dbo].[CasePlanFollowUpActionType] AS TABLE(
	[ACTION]      VARCHAR(50) NULL,
	[NEXTACTION]  VARCHAR(50) NULL,
	[DECISION]    VARCHAR(50) NULL,
	[OPTIONAL]    VARCHAR(50) NULL,
	[WHENTYPE]    VARCHAR(50) NULL,
	[WHEN]		  DECIMAL(10) NULL
)
 END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanDependentActionType')
 BEGIN
CREATE TYPE [dbo].[CasePlanDependentActionType] AS TABLE(
	[ActionCode]			VARCHAR(20)   NULL,
	[DependentActionCode]   VARCHAR(20) NULL,
	[Status]				VARCHAR(1) NULL,
	[TimeFramAmount]		INT NULL,
	[TimeFrame]				VARCHAR(10) NULL
)
 END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanDocClassType')
 BEGIN
	CREATE TYPE [dbo].[CasePlanDocClassType] AS TABLE(
		[CLASSCODE]			    VARCHAR(100)   NULL,
		[CLASSDESCRIPTION]		VARCHAR(100) NULL,
		[Retired]				VARCHAR(1) NULL
	)
 END
GO


IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanEmailTemplateType')
 BEGIN
	CREATE TYPE [dbo].[CasePlanEmailTemplateType] AS TABLE(
	[ACTIONID]					VARCHAR(15) NULL,
	[EMAILTO]					VARCHAR(MAX) NULL,
	[EMAILCC]					VARCHAR(MAX) NULL,
	[EMAILBCC]					VARCHAR(MAX) NULL,
	[SUBJECT]					VARCHAR(MAX) NULL,
	[INCLUDEATTACHMENTS]		VARCHAR(1) NULL,
	[REQUESTREADRECIPT]			VARCHAR(1) NULL,
	[PRIORITY]					VARCHAR(1) NULL,
	[BODY]						VARCHAR(MAX) NULL,
	[IncludeAttachmentsPDF]		VARCHAR(1) NULL
	)
 END
GO

IF NOT EXISTS(SELECT 1 FROM [sys].[types] WHERE name = 'CasePlanTemplateCheckListType')
 BEGIN
		CREATE TYPE [dbo].[CasePlanTemplateCheckListType] AS TABLE(
		[ACTIONCODE]		VARCHAR(15) NULL,
		[Sequence]			INT NULL,
		[ChecklistItem]		VARCHAR(1000) NULL,
		[Optional]			BIT NULL
		)
 END
GO



-- CREATE A TABLE TO FETCH TIMEZONE LIST
IF OBJECT_ID(N'[dbo].[TimeZones]',N'U') IS NULL
BEGIN
	CREATE TABLE [dbo].[TimeZones](
		TimeZoneName VARCHAR(200) NOT NULL
)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'ControlAccountLog' and [NAME] = 'NominalControlTotal')
BEGIN
	ALTER TABLE [dbo].[ControlAccountLog]
	        ADD [NominalControlTotal] DECIMAL(17,2) 
	 CONSTRAINT [DF_ControlAccountLog_NominalControlTotal] DEFAULT(0.00) WITH VALUES;
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'ControlAccountLog' and [NAME] = 'ClientBankTotal')
BEGIN
	 ALTER TABLE [dbo].[ControlAccountLog]
			 ADD [ClientBankTotal] DECIMAL(17,2) 
	  CONSTRAINT [DF_ControlAccountLog_ClientBankTotal] DEFAULT(0.00) WITH VALUES;
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'ControlAccountLog' and [NAME] = 'OfficeBankTotal')
BEGIN
  ALTER TABLE [dbo].[ControlAccountLog]
	      ADD [OfficeBankTotal] DECIMAL(17,2) 
   CONSTRAINT [DF_ControlAccountLog_OfficeBankTotal] DEFAULT(0.00) WITH VALUES;
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BatchH' and [NAME] = 'PrintChequeStatus')
BEGIN
	 ALTER TABLE [dbo].[BatchH]
			 ADD [PrintChequeStatus] INT
	  CONSTRAINT [DF_BatchH_PrintChequeStatus] DEFAULT(0) WITH VALUES;
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BatchDetails' and [NAME] = 'PrintChequeStatus')
BEGIN
	 ALTER TABLE [dbo].[BatchDetails]
			 ADD [PrintChequeStatus] INT
	  CONSTRAINT [DF_BatchDetails_PrintChequeStatus] DEFAULT(0) WITH VALUES;
END
GO


IF OBJECT_ID(N'[dbo].[ChequesForPrinting]',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[ChequesForPrinting](
	[CHEQUENO]	[CHAR](8) CONSTRAINT [PK__ChequesForPrinting__CHEQUENO] PRIMARY KEY CLUSTERED ([CHEQUENO]),
	[BATCHNO]	[INT] NULL,
	[DATE]		[DATETIME] NULL,
	[PAYEE]		[CHAR](40) NULL,
	[MATTER]	[VARCHAR](20) NULL,
	[CURRENCY]	[CHAR](1) NULL,
	[STATUS]	[CHAR](1) NULL,
	[TENMILS]	[CHAR](5) NULL,
	[ONEMILS]	[CHAR](5) NULL,
	[HUNDTHOU]	[CHAR](5) NULL,
	[TENTHOU]	[CHAR](5) NULL,
	[THOU]		[CHAR](5) NULL,
	[HUND]		[CHAR](5) NULL,
	[TENS]		[CHAR](5) NULL,
	[ONES]		[CHAR](5) NULL,
	[ADD]		[CHAR](120) NULL,
	[YOURREF]	[CHAR](20) NULL,
	[NARR]		[CHAR](120) NULL,
	[INVDATE]	[DATETIME] NULL,
	[INVNO]		[CHAR](20) NULL,
	[INVAMOUNT] [DECIMAL](13, 2) NULL,
	[BANK]		[VARCHAR](10) NOT NULL,
	[VALUE]		[DECIMAL](13, 2) NULL
	)

END
GO

-- INCREASE THE SIZE OF BANK COLUMN IN EXISTING STRUCTURE
IF  EXISTS 
	(SELECT 1 
	   FROM SYS.[objects] SO 
			INNER JOIN [sys].[columns] SC  
					ON SC.[object_id] = SO.[object_id] 
				   AND SC.[name] = 'BANK'
	  WHERE SO.[name] = 'ChequesForPrinting' 
	    AND SO.[type] = 'U')
BEGIN
	-- STEP1: DROP THE EXISTING INDEX ON BANK COLUMN
	IF EXISTS
		(SELECT TOP 1 1 
		   FROM sys.indexes 
		  WHERE name='KeyBank' 
			AND object_id = OBJECT_ID('ChequesForPrinting')
			AND Type_Desc = 'NONCLUSTERED')
	BEGIN
		DROP INDEX [KeyBank] ON [dbo].[ChequesForPrinting]
		--DROP INDEX [KaaS_CRReport].[IDX_ReportId]
	END
	
	-- STEP2: ALTER COLUMN SIZE
	 ALTER TABLE
		[dbo].[ChequesForPrinting]
	 ALTER COLUMN [BANK] VARCHAR(10);

	 -- STEP3: CREATE INDEX AGAIN
	 IF NOT EXISTS
		(SELECT TOP 1 1 
		   FROM sys.indexes 
		  WHERE name='KeyBank' 
			AND object_id = OBJECT_ID('ChequesForPrinting')
			AND Type_Desc = 'NONCLUSTERED')
	BEGIN
		CREATE NONCLUSTERED INDEX [KeyBank] ON [dbo].[ChequesForPrinting]
		([BANK] ASC,
		 [DATE] ASC)
		
		--DROP INDEX [KeyBank] ON [dbo].[ChequesForPrinting]
		--DROP INDEX [KaaS_CRReport].[IDX_ReportId]
	END
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] = 'VALUE'
	  WHERE SO.[name] = 'ChequesForPrinting' 
	    AND SO.[type] = 'U')
BEGIN
	 ALTER TABLE
		[dbo].[ChequesForPrinting]
	 ALTER COLUMN [VALUE] DECIMAL(13,2);
END 
GO


IF OBJECT_ID(N'DF_ChequesForPrinting_INVAMOUNT',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ChequesForPrinting] 
	ADD CONSTRAINT 
		[DF_ChequesForPrinting_INVAMOUNT]  
	DEFAULT 
		((0)) 
	FOR 
		[INVAMOUNT]
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] = 'LastModifiedDate'
    WHERE [SO].[name] = 'ChequesForPrinting'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
BEGIN
	ALTER TABLE ChequesForPrinting
		ADD LastModifiedDate DATETIME NULL
END
GO

IF OBJECT_ID(N'DF_ChequesForPrinting_LastModifiedDate',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[ChequesForPrinting] 
	ADD CONSTRAINT 
		[DF_ChequesForPrinting_LastModifiedDate]  
	DEFAULT 
		(GETUTCDATE()) 
	FOR 
		[LastModifiedDate]
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] = 'LastModifiedBy'
    WHERE [SO].[name] = 'ChequesForPrinting'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
BEGIN
	ALTER TABLE ChequesForPrinting
		ADD LastModifiedBy VARCHAR(10) NULL
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'BatchH' and [NAME] = 'IsChequesPrintedManually')
BEGIN
	 ALTER TABLE [dbo].[BatchH]
			 ADD [IsChequesPrintedManually] CHAR(1)
	  CONSTRAINT [DF_BatchH_IsChequesPrintedManually] DEFAULT('N') WITH VALUES;
END
GO

--Added to save Sorting in views 
IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'CustomFilterbyUserandPage' and [NAME] = 'SortColumn')
BEGIN
	ALTER TABLE [dbo].[CustomFilterbyUserandPage]
	ADD  SortColumn VARCHAR(50) NULL;
	
END
GO

IF NOT EXISTS(SELECT top 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'CustomFilterbyUserandPage' and [NAME] = 'SortOrder')
BEGIN
	ALTER TABLE [dbo].[CustomFilterbyUserandPage]
	ADD  SortOrder VARCHAR(20) NULL;
	
END
GO

IF OBJECT_ID(N'[dbo].[DocumentLogger]',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[DocumentLogger](
	[DocumentLoggerId]	[INT] IDENTITY(1,1) CONSTRAINT [PK__DocumentLoggerId__DocumentLoggerId] PRIMARY KEY CLUSTERED ([DocumentLoggerId]),
	[DocInsertGUID]     [VARCHAR](32) NOT NULL,
	[CaseCode]			[VARCHAR](15) NOT NULL,
	[TrackReference]	[INT] NOT NULL,
	[FileName]			[VARCHAR](20) NOT NULL,
	[UploadedDate]		[DATETIME] NOT NULL,
	[Status]			[BIT] NOT NULL,
	[Reason]			[VARCHAR](100) NULL
	)

END
GO

IF OBJECT_ID(N'DF_DocumentLogger_UploadedDate',N'D') IS NULL
BEGIN
	ALTER TABLE 
		[dbo].[DocumentLogger] 
	ADD CONSTRAINT 
		[DF_DocumentLogger_UploadedDate]  
	DEFAULT 
		(GETUTCDATE()) 
	FOR 
		[UploadedDate]
END
GO


IF OBJECT_ID(N'[dbo].[AIChatHistory]',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[AIChatHistory](
	[AIChatHistoryId]				[INT] IDENTITY(1,1) CONSTRAINT [PK__AIChatHistory__AIChatHistoryId] PRIMARY KEY CLUSTERED ([AIChatHistoryId]),
	[HandlerCode]					[VARCHAR](10) NOT NULL,
	[TrackReference]				[INT] ,
	[Messages]						[NVARCHAR](MAX) ,
	[LastInteractedOn]				[DATETIME] NOT NULL
	)

END
GO

----BANK IMPORT (SAM) DB SCRIPTS

IF OBJECT_ID(N'ImportBatchTemplate',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[ImportBatchTemplate] (
	[TemplateID]		INT IDENTITY(1,1), CONSTRAINT PK_ImportBatchTemplate_TemplateID PRIMARY KEY CLUSTERED (TemplateID),
    [TemplateName]		VARCHAR(100),
    [TemplateColumnMap] NVARCHAR(MAX),
    [HasHeaderColumn]	BIT,
    [CreatedOn]			DATETIME,
    [CreatedBy]			VARCHAR(10),
	[ModifiedOn]		DATETIME,
    [ModifiedBy]		VARCHAR(10),
    [NominalCode]		VARCHAR(10),
    [IsClientAccount]	CHAR(1),
    [IsActive]			BIT
)
END
GO

IF OBJECT_ID(N'ImportBatchDetail',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[ImportBatchDetail] (
    [BatchDetailID]   INT IDENTITY(1,1), CONSTRAINT PK_ImportBatchDetail_BatchDetailID PRIMARY KEY CLUSTERED (BatchDetailID),
    [TemplateID]	  INT NOT NULL,CONSTRAINT FK_ImportBatchDetail_Template FOREIGN KEY (TemplateID) REFERENCES [ImportBatchTemplate](TemplateID),
    [BatchDate]		  DATETIME	    NOT NULL,
    [BatchRef]		  VARCHAR(10)   NULL,
    [Narrative]		  VARCHAR(100)  NULL,
    [MatterCode]	  VARCHAR(20)   NULL,
    [NominalCode]	  VARCHAR(10)   NULL,
    [Amount]		  DECIMAL(17,2) NOT NULL,
    [BatchNo]		  INT,
    [BatchStatus]	  TINYINT
);
END
GO

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

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

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

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = 'ImportBatchDetail' and [NAME] = 'AllocValue')
	BEGIN
		ALTER TABLE [dbo].[ImportBatchDetail] ADD [AllocValue] DECIMAL(17,2) NULL
	END
GO

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

IF OBJECT_ID(N'ImportBatchLog',N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[ImportBatchLog] (
    [LogID]       BIGINT IDENTITY(1,1),CONSTRAINT PK_ImportBatchLog_LogID PRIMARY KEY CLUSTERED (LogID),
    [TemplateID]  INT NOT NULL,CONSTRAINT FK_ImportBatchLog_Template FOREIGN KEY (TemplateID) REFERENCES [ImportBatchTemplate](TemplateID),
    [ModifiedBy]  VARCHAR(10),
    [ModifiedOn]  DATETIME,
    [Comments]    VARCHAR(500)
);
END
GO

IF EXISTS(SELECT TOP 1 1 FROM [sys].[types] WHERE name = 'ImportBatchDetailType')
BEGIN
	IF OBJECT_ID(N'KAAS_BankImportDeleteDraft',N'P')IS NOT NULL
	 BEGIN
	   DROP PROCEDURE [dbo].[KAAS_BankImportDeleteDraft]
	 END

	IF OBJECT_ID(N'KAAS_BankImportSaveAsDraft', N'P') IS NOT NULL
	 BEGIN
      DROP PROCEDURE [dbo].[KAAS_BankImportSaveAsDraft]
	 END

	 DROP TYPE [dbo].[ImportBatchDetailType]

END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [sys].[types] WHERE name = 'ImportBatchDetailType')
 BEGIN
	CREATE TYPE [dbo].[ImportBatchDetailType] AS TABLE(
    [BatchDetailID]   INT			NULL,
    [TemplateID]	  INT			NULL,
    [BatchDate]		  DATETIME		NULL,
    [BatchRef]		  VARCHAR(10)   NULL,
    [Narrative]		  VARCHAR(100)  NULL,
    [MatterCode]	  VARCHAR(20)   NULL,
    [NominalCode]	  VARCHAR(10)   NULL,
    [Amount]		  DECIMAL(17,2) NULL,
    [BatchNo]		  INT			NULL,
    [BatchStatus]	  TINYINT		NULL,
	[AllocBatch]	  INT			NULL,
	[AllocPref]		  INT			NULL,
	[AllocRef]		  VARCHAR(10)	NULL,
	[AllocValue]	  DECIMAL(17,2) NULL,
	[AllocStatus]	  BIT			NULL
	)
 END
GO

--REMOVED THIS BLOCK; IT WAS USED TO FIX SCHEMA IN STAGING SITE;
--IF OBJECT_ID(N'[dbo].[ChequesForPrintingWeb]',N'U') IS NOT NULL
--BEGIN
--	DROP TABLE ChequesForPrintingWeb;
--END
--GO
	
IF OBJECT_ID(N'[dbo].[ChequesForPrintingWeb]',N'U') IS NULL
BEGIN

CREATE TABLE [dbo].[ChequesForPrintingWeb](
    [PrintID]			[INT] IDENTITY(1,1) NOT NULL,
	[ChequeNo]			[VARCHAR](8) NULL,
	[BatchNo]			[INT] NOT NULL,
	[Date]				[DATETIME] NOT NULL,
	[Payee]				[VARCHAR](100) NULL,
	[Matter]			[VARCHAR](20) NULL,
	[Currency]			[CHAR](1) NOT NULL,
	[Status]			[CHAR](1) NOT NULL,
	[TenMils]			[VARCHAR](5) NOT NULL,
	[OneMils]			[VARCHAR](5) NOT NULL,
	[HundThou]			[VARCHAR](5) NOT NULL,
	[TenThou]			[VARCHAR](5) NOT NULL,
	[Thou]				[VARCHAR](5) NOT NULL,
	[Hund]				[VARCHAR](5) NOT NULL,
	[Tens]				[VARCHAR](5) NOT NULL,
	[Ones]				[VARCHAR](5) NOT NULL,
	[Address]			[VARCHAR](500) NULL,
	[YourRef]			[VARCHAR](50) NULL,
	[Narrative]			[VARCHAR](500) NULL,
	[InvDate]			[DATETIME] NULL,
	[InvNo]				[VARCHAR](20) NOT NULL,
	[InvAmount]			[DECIMAL](17, 2) NULL, 
	[Bank]				[VARCHAR](10) NOT NULL, 
	[Value]				[DECIMAL](17, 2) NULL,
	[LastModifiedDate]	[DATETIME] NOT NULL,
	[LastModifiedBy]	[VARCHAR](10) NOT NULL ,
	CONSTRAINT PK_ChequesForPrintingWeb_PrintID PRIMARY KEY (PrintID),
	)

	IF NOT EXISTS
	(SELECT TOP 1 1 
		FROM sys.objects 
		WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Date')
			BEGIN
				ALTER TABLE [ChequesForPrintingWeb] 
				ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Date]  
				DEFAULT ((GETUTCDATE())) FOR [Date]
			END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Currency')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Currency]  
			DEFAULT (('E')) FOR [Currency]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_TenMils')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_TenMils]  
			DEFAULT (('Zero')) FOR [TenMils]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_OneMils')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_OneMils]  
			DEFAULT (('Zero')) FOR [OneMils]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_HundThou')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_HundThou]  
			DEFAULT (('Zero')) FOR [HundThou]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_TenThou')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_TenThou]  
			DEFAULT (('Zero')) FOR [TenThou]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Thou')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Thou]  
			DEFAULT (('Zero')) FOR [Thou]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Hund')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Hund]  
			DEFAULT (('Zero')) FOR [Hund]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Tens')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Tens]  
			DEFAULT (('Zero')) FOR [Tens]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Ones')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Ones]  
			DEFAULT (('Zero')) FOR [Ones]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_InvNo')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_InvNo]  
			DEFAULT (('0')) FOR [InvNo]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_InvAmount')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_InvAmount]  
			DEFAULT ((0.0)) FOR [InvAmount]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_Value')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_Value]  
			DEFAULT ((0.0)) FOR [Value]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE [type] = 'D' And [name] = 'DF_ChequesForPrintingWeb_LastModifiedDate')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [DF_ChequesForPrintingWeb_LastModifiedDate]  
			DEFAULT ((GETUTCDATE())) FOR [LastModifiedDate]
		END

	IF NOT EXISTS
	(SELECT TOP 1 1 
	FROM sys.objects 
	WHERE  [name] = 'CHK_ChequesForPrintingWeb_Status')
		BEGIN
			ALTER TABLE [ChequesForPrintingWeb] 
			ADD  CONSTRAINT [CHK_ChequesForPrintingWeb_Status]   
			CHECK (Status IN ('1', '2', '3'));
		END

--Existing nonclustered index Added
	 IF NOT EXISTS
		(SELECT TOP 1 1 
		   FROM sys.indexes 
		  WHERE name='KeyBank' 
			AND object_id = OBJECT_ID('ChequesForPrintingWeb')
			AND Type_Desc = 'NONCLUSTERED')
	BEGIN
		CREATE NONCLUSTERED INDEX [KeyBank] ON [dbo].[ChequesForPrintingWeb]
		([Bank] ASC,
		 [Date] DESC)
	END
	

	--Added the Non clustered index on the BatchNo
	 IF NOT EXISTS
		(SELECT TOP 1 1 
		   FROM sys.indexes 
		  WHERE name='KeyBatchNo' 
			AND object_id = OBJECT_ID('ChequesForPrintingWeb')
			AND Type_Desc = 'NONCLUSTERED')
	BEGIN
		CREATE NONCLUSTERED INDEX [KeyBatchNo] ON [dbo].[ChequesForPrintingWeb]
		([BatchNo] ASC)
	END
	
	END
GO


--COMMENTED OUT THIS BLOCK; IT WAS INCLUDED TO MIGRATE DATA TO NEW TABLE IN STAGING SITE;
--If ChequeForPrinting table have data then loading this into the new ChequesForPrintingWeb table
--IF ChequeForPrinting TABLE HAS DATA THEN PUSH THIS TO NEW ChequesForPrintingWeb TABLE
--DECLARE @isDataAvailable BIT = 0;
--IF EXISTS (SELECT TOP 1 1 FROM [dbo].[ChequesForPrinting])
--BEGIN
--	SET @isDataAvailable = 1
--END

--IF (@isDataAvailable = 1)
--BEGIN
--INSERT
--  INTO [dbo].[ChequesForPrintingWeb]     
--  (
--		[ChequeNo],
--		[BatchNo],
--		[Date]	,
--		[Payee],
--		[Matter],
--		[Currency],
--		[Status],
--		[TenMils],
--		[OneMils],
--		[HundThou],
--		[TenThou],
--		[Thou],
--		[Hund],
--		[Tens],
--		[Ones],
--		[Address],
--		[YourRef],
--		[Narrative],
--		[InvDate],
--		[InvNo],
--		[InvAmount],
--		[Bank],
--		[Value],
--		[LASTMODIFIEDDate],
--		[LASTMODIFIEDBY]
--)
--SELECT 
--        [ChequeNo]	,
--		[BatchNo],
--		ISNULL([Date], GETUTCDATE()),
--		[Payee],
--		[Matter],
--		[Currency],
--		[Status],
--		ISNULL([TenMils], 'Zero'),
--		ISNULL([OneMils],'Zero'),
--		ISNULL([HundThou],'Zero'),
--		ISNULL([TenThou],'Zero'),
--		ISNULL([Thou],'Zero'),
--		ISNULL([Hund],'Zero'),
--		ISNULL([Tens],'Zero'),
--		ISNULL([Ones],'Zero'),
--		[Add],
--		[YourRef],
--		[Narr],
--		[InvDate],
--		ISNULL([InvNo],'0'),
--		ISNULL([InvAmount],0.0),
--		[Bank],
--		ISNULL([Value],0.0),
--		ISNULL([LastModifiedDate], GETUTCDATE()),
--		[LastModifiedBy]
--  FROM [dbo].[ChequesForPrinting]
--END
--GO