
IF OBJECT_ID('QueryWithOnlineOrOffline','P') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[QueryWithOnlineOrOffline]
END
GO
-- Helper stored procedure to execute cluster index changes with online or offline based on SQL Server Edition
CREATE PROCEDURE [dbo].[QueryWithOnlineOrOffline]
	@Query NVARCHAR(MAX)
AS
BEGIN
	DECLARE @OnlineState AS NVARCHAR(5) = 'OFF'
	
	-- Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql
	IF 3 = SERVERPROPERTY('EngineEdition')
		SET @OnlineState = 'ON'

	DECLARE @FinalQuery AS NVARCHAR(MAX) = REPLACE(@Query, '<@OnlineState>', @OnlineState)

	EXEC (@FinalQuery)
END
GO

IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('IDTable'))
BEGIN
	EXEC na_repldroptable 'IDTable', 0
	ALTER TABLE IDTable
	ADD CONSTRAINT temp1 UNIQUE(Name);

	ALTER TABLE IDTable DROP CONSTRAINT PK_IDTABLE;

	EXEC QueryWithOnlineOrOffline 
		'ALTER TABLE IDTable 
		ADD CONSTRAINT PK_IDTABLE PRIMARY KEY CLUSTERED(Name) WITH (ONLINE=<@OnlineState>)'
	ALTER TABLE IDTable
	DROP CONSTRAINT temp1;

	EXEC na_addarticles 'IDTable', '', 0
END
GO

IF NOT EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'CX_MsgInfoExternals' AND id = Object_Id('dbo.MsgInfoExternals'))
BEGIN
	EXEC na_repldroptable 'MsgInfoExternals', 0
	EXEC QueryWithOnlineOrOffline 
		'CREATE CLUSTERED INDEX [CX_MsgInfoExternals] ON [dbo].[MsgInfoExternals]
		(
			[Instance] ASC,
			[ObjId] ASC
		) WITH (ONLINE=<@OnlineState>)'
	IF EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'IX_MSGINFOEXTERNALS' AND id = Object_Id('dbo.MsgInfoExternals'))
	DROP INDEX [IX_MSGINFOEXTERNALS] ON [dbo].[MsgInfoExternals]

	EXEC na_addarticles 'MsgInfoExternals', '', 0
END
GO


IF NOT EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'CX_MailReadyToSend' AND id = Object_Id('dbo.MailReadyToSend'))

CREATE CLUSTERED INDEX [CX_MailReadyToSend] ON [dbo].[MailReadyToSend]
(
	[mailboxid] ASC
)

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'IX_MAILREADYTOSEND' AND id = Object_Id('dbo.MailReadyToSend'))
	DROP INDEX [IX_MAILREADYTOSEND] ON [dbo].[MailReadyToSend]
GO


IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('UserRoleOptions'))
BEGIN
	EXEC na_repldroptable 'UserRoleOptions', 0
	ALTER TABLE UserRoleOptions
	ADD CONSTRAINT temp1 UNIQUE(DepartmentID, UserId);
	
	ALTER TABLE [dbo].[UserRoleOptions] DROP CONSTRAINT PK_USERROLEOPTIONS;
	
	EXEC QueryWithOnlineOrOffline 
		'ALTER TABLE [dbo].[UserRoleOptions] 
		ADD CONSTRAINT PK_USERROLEOPTIONS PRIMARY KEY CLUSTERED(DepartmentID, UserId) WITH (ONLINE=<@OnlineState>)'
	
	ALTER TABLE [dbo].[UserRoleOptions]
	DROP CONSTRAINT temp1;
	Execute na_addarticles 'UserRoleOptions', '', 1
END
GO


IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('UserServicelinePermissions'))
BEGIN
	EXEC na_repldroptable 'UserServicelinePermissions', 0
	ALTER TABLE UserServicelinePermissions
	ADD CONSTRAINT temp1 UNIQUE(UserID, DepartmentID, ServiceLineID);
	
	ALTER TABLE UserServicelinePermissions DROP CONSTRAINT PK_USERSERVICELINEPERMISSIONS;
	
	EXEC QueryWithOnlineOrOffline 
		'ALTER TABLE UserServicelinePermissions
		ADD CONSTRAINT PK_USERSERVICELINEPERMISSIONS PRIMARY KEY CLUSTERED(UserID, DepartmentID, ServiceLineID) WITH (ONLINE=<@OnlineState>)'
	
	ALTER TABLE UserServicelinePermissions
	DROP CONSTRAINT temp1;
	EXEC na_addarticles 'UserServicelinePermissions', '', 1
END
GO


IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('UserAllPermissions'))
BEGIN
	EXEC na_repldroptable 'UserAllPermissions', 0
	ALTER TABLE UserAllPermissions
	ADD CONSTRAINT temp1 UNIQUE(UserID, DepartmentID);
	
	ALTER TABLE UserAllPermissions DROP CONSTRAINT PK_USERALLPERMISSIONS;
	
	EXEC QueryWithOnlineOrOffline 
		'ALTER TABLE UserAllPermissions
		ADD CONSTRAINT PK_USERALLPERMISSIONS PRIMARY KEY CLUSTERED(UserID, DepartmentID) WITH (ONLINE=<@OnlineState>)'
	
	ALTER TABLE UserAllPermissions
	DROP CONSTRAINT temp1;
	EXEC na_addarticles 'UserAllPermissions', '', 1
END
GO


IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('MailsFailedInParse'))
BEGIN
	EXEC na_repldroptable 'MailsFailedInParse', 0
	ALTER TABLE MailsFailedInParse
	ADD CONSTRAINT temp1 UNIQUE(FailedMsgID);
	
	ALTER TABLE MailsFailedInParse DROP CONSTRAINT PK_MAILFAILEDINPARSE;
	
	ALTER TABLE MailsFailedInParse
	ADD CONSTRAINT PK_MAILFAILEDINPARSE PRIMARY KEY CLUSTERED(FailedMsgID)
	
	ALTER TABLE MailsFailedInParse
	DROP CONSTRAINT temp1;
	EXEC na_addarticles 'MailsFailedInParse', '', 1
END
GO


IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('UserMailboxPermissions'))
BEGIN
	EXEC na_repldroptable 'UserMailboxPermissions', 0
	ALTER TABLE UserMailboxPermissions
	ADD CONSTRAINT temp1 UNIQUE(UserID, DepartmentID, MailboxID);
	
	ALTER TABLE UserMailboxPermissions DROP CONSTRAINT PK_USERMAILBOXPERMISSIONS;
	
	EXEC QueryWithOnlineOrOffline 
		'ALTER TABLE UserMailboxPermissions
		ADD CONSTRAINT PK_USERMAILBOXPERMISSIONS PRIMARY KEY CLUSTERED(UserID, DepartmentID, MailboxID) WITH (ONLINE=<@OnlineState>)'
	
	ALTER TABLE UserMailboxPermissions
	DROP CONSTRAINT temp1;
	EXEC na_addarticles 'UserMailboxPermissions', '', 1
END
GO


IF NOT EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'CX_WorkflowAction' AND id = Object_Id('dbo.WorkflowAction'))
	EXEC QueryWithOnlineOrOffline 
		'CREATE CLUSTERED INDEX [CX_WorkflowAction] ON [dbo].[WorkflowAction]
		(
			[workflowid] ASC,
			[filterid] ASC,
			[status] ASC
		) WITH (ONLINE=<@OnlineState>)'
GO

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'ix_WorkflowAction_workflowid' AND id = Object_Id('dbo.WorkflowAction'))
	DROP INDEX [ix_WorkflowAction_workflowid] ON [dbo].[WorkflowAction]
GO


IF NOT EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'CX_SchedulingQueueCollection' AND id = Object_Id('dbo.SchedulingQueueCollection'))
	EXEC QueryWithOnlineOrOffline 
		'CREATE CLUSTERED INDEX [CX_SchedulingQueueCollection] ON [dbo].[SchedulingQueueCollection]
		(
			[queue_id] ASC,
			[ext] ASC
		) WITH (ONLINE=<@OnlineState>)'
GO


IF EXISTS(SELECT TOP 1 1 FROM sysobjects where xtype = 'PK' and parent_obj = object_id('SchedulingShifts'))
BEGIN
	EXEC na_repldroptable 'SchedulingShifts', 0
	ALTER TABLE SchedulingShifts
	ADD CONSTRAINT temp1 UNIQUE(id);
	
	ALTER TABLE SchedulingShifts DROP CONSTRAINT PK_SCHEDULINGSHIFTS;
	
	EXEC QueryWithOnlineOrOffline 
		'ALTER TABLE SchedulingShifts
		ADD CONSTRAINT PK_SCHEDULINGSHIFTS PRIMARY KEY CLUSTERED(id) WITH (ONLINE=<@OnlineState>)'

	ALTER TABLE SchedulingShifts
	DROP CONSTRAINT temp1;
	EXEC na_addarticles 'SchedulingShifts', '', 1
END
GO


IF NOT EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE NAME = 'CX_Services' AND id = Object_Id('dbo.Services'))
	EXEC QueryWithOnlineOrOffline 
		'CREATE CLUSTERED INDEX [CX_Services] ON [dbo].[Services]
		(
			[ServiceID] ASC,
			[ServiceType] ASC,
			[ServiceGroupID] ASC
		) WITH (ONLINE=<@OnlineState>)'
GO


IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_DOWNLOADEDMAIL_MAILBOX' AND id = OBJECT_ID('dbo.Downloadedmail'))
	DROP INDEX [IX_DOWNLOADEDMAIL_MAILBOX] ON [dbo].[DownloadedMail]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_DOWNLOADEDMAILATTACHMENT' AND id = OBJECT_ID('dbo.DownloadedmailAttachment'))
	DROP INDEX [IX_DOWNLOADEDMAILATTACHMENT] ON [dbo].[DownloadedMailAttachment]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_DOWNLOADEDMAILEX' AND id = OBJECT_ID('dbo.DownloadedMailEx'))
	DROP INDEX [IX_DOWNLOADEDMAILEX] ON [dbo].[DownloadedMailEx]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_MAILNEW_NEW_PARENT_TO' AND id = OBJECT_ID('dbo.MailNew'))
	DROP INDEX [IX_MAILNEW_NEW_PARENT_TO] ON [dbo].[MailNew]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'NCI_MailTProp_TID_PID' AND id = OBJECT_ID('dbo.MailTemplateProperties'))
	DROP INDEX [NCI_MailTProp_TID_PID] ON [dbo].[MailTemplateProperties]
GO

IF OBJECT_ID('UK_MAILWEBSERVICE') IS NOT NULL
	ALTER TABLE [dbo].[MailWebService] DROP CONSTRAINT [UK_MAILWEBSERVICE]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_SESSIONINFO_GUID' AND id = OBJECT_ID('dbo.SessionInfo'))
	DROP INDEX [IX_SESSIONINFO_GUID] ON [dbo].[SessionInfo]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_SESSIONSUMMARY' AND id = OBJECT_ID('dbo.SessionSummary'))
	DROP INDEX [IX_SESSIONSUMMARY] ON [dbo].[SessionSummary]
GO

IF EXISTS (SELECT 1 FROM sysindexes WITH(NOLOCK) WHERE name = 'IX_POSITION_DEPT' AND id = OBJECT_ID('Position'))
	DROP INDEX [IX_POSITION_DEPT] ON [dbo].[Position]
GO