/* PR-2440 Increase ExtRef length in T_Document table */
ALTER TABLE T_Document ALTER COLUMN ExtRef NVARCHAR(2000) NULL;
GO
/* End of PR-2440 */

/* Sub-Review */
INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (67, 'SYSTEM', 'ALLOWSUBREVIEWS', 'PERMIT', 'SYSTEM');
GO

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (68, 'SYSTEM', 'AUTOUPDATECHANGESFROMMASTERREVIEW', 'PERMIT', 'SYSTEM');
GO

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (69, 'SYSTEM', 'SUBREVIEWCOMMENTORIGINALLYBY', 'PERMIT', 'SYSTEM');
GO

INSERT INTO T_Subject(ObjectId,FullName,MailAddress,WorkgroupId,SystemRole,LicenseAgreed,CultureName,Active,LastLoginDate,DescriptionNote,UserType,MailAddressL)
VALUES (-1,'Master Review','masterreview@example.com',0,NULL,0,'',1,NULL,NULL,'','masterreview@example.com');
GO

ALTER TABLE T_Review ADD SubReviewParentId INT NOT NULL DEFAULT 0;
GO

ALTER TABLE T_ReviewDocument ADD IsClone INT NOT NULL DEFAULT 0;
GO

ALTER TABLE T_Comment ADD PublishToMaster INT NOT NULL DEFAULT 0;
GO

ALTER TABLE T_Comment ADD SubReviewCommentId INT NOT NULL DEFAULT 0;
GO
ALTER TABLE T_Comment ADD MergedCommentId INT NOT NULL DEFAULT 0;
GO
/* End of Sub-Review */

/* PR-3460 Participant can reply but cant make new comments */

UPDATE T_PermissionSet
   SET PermissionFlags = CAST(PermissionFlags AS NVARCHAR(MAX)) + 'REPLY;'
 WHERE PermissionFlags like '%COMMENT;%';
 
UPDATE T_PermissionSet
   SET PermissionFlags = CAST(PermissionFlags AS NVARCHAR(MAX)) + 'CHANGE;'
 WHERE PermissionFlags like '%COMMENT;%';

GO

/* End of PR-3460 */

/* PR-3461 Archive via PDF */

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (70, 'SYSTEM', 'ALLOWARCHIVING', 'PERMIT', 'SYSTEM');
GO

/* End of PR-3461 */

/* PR-3538 - change DescriptionNote fields from blob to varchar */

ALTER TABLE T_Subject ALTER COLUMN DescriptionNote NVARCHAR(2000);
ALTER TABLE T_Workgroup ALTER COLUMN DescriptionNote NVARCHAR(2000);
GO

/* end of PR-3538 */

/* PR-PR-3507 Comment Category Improvement */
UPDATE T_Comment
   SET CategoryText = ''
 WHERE CategoryText = '<Blank>';
 
 UPDATE T_Comment
   SET ReviewerCategoryText = ''
 WHERE ReviewerCategoryText = '<Blank>';
 GO
/* end of PR-3507 */

/* PR-3521  Configurability (Settings) - Database Changes & Migration Script */
ALTER TABLE T_Workgroup ADD ParentWorkgroupId INT NULL;
GO

IF NOT EXISTS (SELECT ObjectId FROM T_Workgroup WHERE ObjectId = 1)
	BEGIN
		DECLARE @totalWorkgroups int
		SELECT @totalWorkgroups = COUNT(ObjectId)
		FROM T_Workgroup
		
		IF (@totalWorkgroups = 1)
			BEGIN
				DECLARE @originalObjectId int
				SELECT @originalObjectId = ObjectId
				FROM T_Workgroup
				
				UPDATE T_Workgroup
					SET ObjectId = 1
					WHERE ObjectId = @originalObjectId	
				
				UPDATE T_Subject
					SET WorkgroupId = 1
					WHERE WorkgroupId = @originalObjectId						
	
				UPDATE T_WorkgroupMember
					SET WorkgroupId = 1
					WHERE WorkgroupId = @originalObjectId	
					
				UPDATE T_Folder
					SET WorkgroupId = 1
					WHERE WorkgroupId = @originalObjectId		
					
				UPDATE T_Review
					SET WorkgroupId = 1
					WHERE WorkgroupId = @originalObjectId			

				UPDATE T_Document
					SET WorkgroupId = 1
					WHERE WorkgroupId = @originalObjectId	
					
				UPDATE T_DistList
					SET OwnerWorkgroupId = 1
					WHERE OwnerWorkgroupId = @originalObjectId						

				UPDATE T_GenDocAudit
					SET WorkgroupId = 1
					WHERE WorkgroupId = @originalObjectId	
			END
		ELSE
			BEGIN
				INSERT INTO T_Workgroup(ObjectId, WorkgroupName, LicenseId, IsTaskgroup, IsPublic, IsAllMonitor, ParentWorkgroupId)
				VALUES (1,'Root',1,0,0,0, NULL);
			END 
	END 

UPDATE T_Workgroup
	SET ParentWorkgroupId = 1
	WHERE ParentWorkgroupId IS NULL AND ObjectId != 1;
	
CREATE TABLE T_ReviewType(
	ObjectId			INT			NOT NULL,
	ParentReviewTypeId	INT				NULL,
	WorkgroupId			INT				NOT NULL,
	ReviewTypeName		NVARCHAR(128)	NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX IX_ReviewType ON T_ReviewType (ObjectId);
CREATE INDEX IX_ReviewType2 ON T_ReviewType (WorkgroupId);
CREATE INDEX IX_ReviewType3 ON T_ReviewType (ReviewTypeName);

ALTER TABLE T_Review ADD ReviewTypeId INT NULL

CREATE TABLE T_Config(
	ObjectId INT NOT NULL,
	RefObjectType NVARCHAR(128) NOT NULL,
	RefObjectId INT NOT NULL,
	EntryName NVARCHAR(128) NOT NULL,
	AllowOverride INT NOT NULL,
	StringParam1 NVARCHAR(128) NULL,
	StringParam2 NVARCHAR(128) NULL,
	IntParam1 INT NULL,
	IntParam2 INT NULL,
	DateParam1 DATETIME NULL,
	DateParam2 DATETIME NULL,
	TextParam1 NTEXT NULL
);
CREATE UNIQUE CLUSTERED INDEX IX_Config ON T_Config (ObjectId);
CREATE INDEX IX_Config2 ON T_Config (RefObjectType, RefObjectId);

/* PR-3700 */
ALTER TABLE T_Subject ADD HomeWorkgroupId INT NULL;
GO

UPDATE T_Subject SET HomeWorkgroupId = 1
ALTER TABLE T_Subject ALTER COLUMN HomeWorkgroupId INT NOT NULL;

/*
 * Create Stored Procedure
 */
 
 /* SP_GetParent */
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SP_GetParent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [SP_GetParent]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SP_GetParent
	-- Add the parameters for the stored procedure here
	@objectType nvarchar(50),
	@id int,
	@contextId int = NULL OUTPUT,
	@contextType nvarchar(128) = NULL OUTPUT,
	@parentObjectType nvarchar(50)OUTPUT,
	@parentId int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @currentId AS int
	SET @currentId  = @id
	DECLARE @nextContextId AS int
	SET @nextContextId = @id
	DECLARE @nextContextType AS NVARCHAR(128)
	SET @nextContextType = @objectType
	
	-- get parent id		
	IF @objectType = 'Review'
		BEGIN
			SELECT TOP 1 @parentId = ParentReviewId
			FROM T_Review
			WHERE ObjectId = @currentId
			
			SET @parentObjectType = 'Review'
		
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = ReviewTypeId
					FROM T_Review
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'ReviewType'
				END
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = WorkgroupId
					FROM T_Review
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'
				END
		END
	ELSE IF @objectType = 'ReviewType'
		BEGIN
			SELECT TOP 1 @parentId = ParentReviewTypeId
			FROM T_ReviewType
			WHERE ObjectId = @currentId
			
			SET @parentObjectType = 'ReviewType'
			
			--preserve context
			IF @contextId IS NOT NULL
				BEGIN
					SET @nextContextId = @contextId
					SET @nextContextType = @contextType
				END
		
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					--use context
					IF @contextType = 'Review' AND @contextId IS NOT NULL
						BEGIN
							SET @currentId = @contextId
												
							SELECT TOP 1 @parentId = WorkgroupId
							FROM T_Review
							WHERE ObjectId = @currentId
							
							SET @parentObjectType = 'Workgroup'
						END
					
					--preserve context
					IF @contextId IS NOT NULL
						BEGIN
							SET @nextContextId = @contextId
							SET @nextContextType = @contextType
						END
				END
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = WorkgroupId
					FROM T_ReviewType
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'
					
					--reset context
					IF @contextId IS NOT NULL 
						BEGIN
							SET @nextContextId = @id
							SET @nextContextType = @objectType
						END
				END
		END
	ELSE IF @objectType = 'Workgroup'
		BEGIN
			SELECT TOP 1 @parentId = ParentWorkgroupId
			FROM T_Workgroup
			WHERE ObjectId = @currentId
			
			SET @parentObjectType = 'Workgroup'
		END
	ELSE IF @objectType = 'Subject'
		BEGIN
			SELECT TOP 1 @parentId = U.ObjectId
			FROM T_Subject S
			LEFT JOIN T_UserType U
			ON S.UserType = U.Title
			WHERE S.ObjectId = @currentId
			
			SET @parentObjectType = 'UserType'
		
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = HomeWorkgroupId
					FROM T_Subject
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'	
				END
		END
	ELSE IF @objectType = 'UserType'
		BEGIN
			--use context
			IF @contextType = 'Subject' AND @contextId IS NOT NULL 
				BEGIN
					SET @currentId = @contextId
								
					SELECT TOP 1 @parentId = HomeWorkgroupId
					FROM T_Subject
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'
				END
			
			--preserve context
			IF @contextId IS NOT NULL
				BEGIN
					SET @nextContextId = @contextId
					SET @nextContextType = @contextType
				END
		END
		
	SET @contextId = @nextContextId
	SET @contextType = @nextContextType

END
GO
 
 /* SP_GetConfig */
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SP_GetConfig]') AND type in (N'P', N'PC'))
DROP PROCEDURE [SP_GetConfig]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SP_GetConfig
	-- Add the parameters for the stored procedure here
	@objectType nvarchar(128),
	@id int,
	@entryName nvarchar(128) ,
	@ignoreAllowOverride int = 0,
	@hasPreviousValue int = 0,
	@previousConfigId int = null,
	@previousAllowOverride nvarchar(128) = null,
	@previousObjectType nvarchar(128) = null,
	@previousId int = null,
	@contextId nvarchar(50) = NULL OUTPUT,
	@contextType nvarchar(128) = NULL OUTPUT,
	@lineage nvarchar(MAX) = NULL OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @currentConfigId AS nvarchar(128)
	DECLARE @currentAllowOverride AS nvarchar(128)
	DECLARE @currentObjectType nvarchar(128) 
	DECLARE @currentId nvarchar(50) 
	DECLARE @currentHasPreviousValue int
	SET @currentHasPreviousValue = 0
	DECLARE @currentHasData int
	SET @currentHasData = 0
	DECLARE @parentObjectType nvarchar(50)
	DECLARE @parentId nvarchar(50)
	
	IF @lineage IS NULL
		BEGIN
			SET @lineage = ''
		END
	SET @lineage = @lineage + @objectType + '(Id:' + CONVERT(NVARCHAR(128), @id) + ') '
    SELECT TOP 1 
	   @currentAllowOverride = [AllowOverride]
      ,@currentConfigId = [ObjectId]
      ,@currentObjectType = [RefObjectType]
      ,@currentId = [RefObjectId]
	FROM [T_Config]
	WHERE [RefObjectType] = @objectType AND [RefObjectId] = @id AND [EntryName] = @entryName
	
	SET @currentHasData  = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
	
	IF @currentHasData = 1 -- has current data, set hasPreviousValue to true for next recursion
		BEGIN
			SET @currentHasPreviousValue = 1 
		END
		
	IF @hasPreviousValue = 1 AND (@currentAllowOverride IS NULL OR @currentAllowOverride = 1) -- has previous and current is overridable, disregard current
		BEGIN
			SET @currentConfigId = @previousConfigId
			SET @currentAllowOverride = @previousAllowOverride
			SET @currentObjectType = @previousObjectType
			SET @currentId = @previousId
			SET @currentHasPreviousValue = @hasPreviousValue
		END
	IF @currentHasData = 0 OR @currentHasData = 1 AND @ignoreAllowOverride = 0
		BEGIN
			EXEC SP_GetParent  @objectType, @id, @contextId OUT, @contextType OUT, @parentObjectType OUT, @parentId OUT
		END
		
	IF @parentId IS NOT NULL -- parent exist, get config for parent
		BEGIN
			EXEC SP_GetConfig @parentObjectType, @parentId, @entryName, @ignoreAllowOverride, @currentHasPreviousValue, @currentConfigId, @currentAllowOverride, @currentObjectType, @currentId,	@contextId OUT, @contextType OUT, @lineage OUT
		END
	ELSE -- no parent, set to previous/current value, exit recursion
		BEGIN
			SELECT TOP 1 
			   [StringParam1]
			  ,[StringParam2]
			  ,[IntParam1]
			  ,[IntParam2]
			  ,[DateParam1]
			  ,[DateParam2]
			  ,[TextParam1]
			  ,[AllowOverride]
			  ,[RefObjectType]
			  ,[RefObjectId]
			  ,@lineage AS [Lineage]
			FROM [T_Config]
			WHERE [ObjectId] = @currentConfigId		
		END
END
GO

 /* new Config entry */
INSERT INTO T_Config(ObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
VALUES(41, 'WORKGROUP', 1, 'SubReviewOwnerReadOnlyInMasterReview', 0, 0);


BEGIN TRAN t1;

DECLARE @BackupDateTime DATETIME
SET @BackupDateTime = GETDATE()

/* STEP 1: CREATING TEMPORARY TABLE */
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmpT_SystemPolicy]') AND type in (N'U'))
BEGIN
	CREATE TABLE tmpT_SystemPolicy (
		ObjectId	INT NOT NULL,
		Principal	NVARCHAR(128),
		Permission	NVARCHAR(128),
		PolicyAction		NVARCHAR(128),
		PolicyObject		NVARCHAR(128),
		BackupDateTime DATETIME NOT NULL
	);
	CREATE INDEX IX_tmpT_SystemPolicy ON tmpT_SystemPolicy (ObjectId);
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmpT_Settings]') AND type in (N'U'))
BEGIN
	CREATE TABLE [tmpT_Settings](
		[ObjectId] [int] NOT NULL,
		[RefObjectType] [nvarchar](128) NOT NULL,
		[RefObjectId] [int] NOT NULL,
		[EntryName] [nvarchar](128) NOT NULL,
		[StringParam1] [nvarchar](128) NULL,
		[StringParam2] [nvarchar](128) NULL,
		[IntParam1] [int] NULL,
		[IntParam2] [int] NULL,
		[DateParam1] [datetime] NULL,
		[DateParam2] [datetime] NULL,
		[TextParam1] [ntext] NULL,
		[BackupDateTime] [datetime] NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmpT_Config]') AND type in (N'U'))
BEGIN
	CREATE TABLE [tmpT_Config](
		ObjectId INT NOT NULL,
		ExistingObjectId INT NOT NULL,
		RefObjectType NVARCHAR(128) NOT NULL,
		RefObjectId INT NOT NULL,
		EntryName NVARCHAR(128) NOT NULL,
		AllowOverride INT NOT NULL,
		StringParam1 NVARCHAR(128) NULL,
		StringParam2 NVARCHAR(128) NULL,
		IntParam1 INT NULL,
		IntParam2 INT NULL,
		DateParam1 DATETIME NULL,
		DateParam2 DATETIME NULL,
		TextParam1 NTEXT NULL,
		BackupDateTime DATETIME NOT NULL,
		IsMigrated TINYINT NOT NULL
	);
	CREATE UNIQUE INDEX IX_tmpConfig1 ON tmpT_Config (ObjectId);
	CREATE INDEX IX_tmpConfig3 ON tmpT_Config (RefObjectType, RefObjectId);
END

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tmpT_Config]') AND name = N'IX_tmpConfig2')
DROP INDEX [IX_tmpConfig2] ON [tmpT_Config] WITH ( ONLINE = OFF )


--Inserting the t_sequencer record type next id for config if it is not exists
IF NOT EXISTS (SELECT 1 FROM T_Sequencer WHERE RecordType = 'Config')
	INSERT INTO T_Sequencer (RecordType, NextId)VALUES('Config', 10000)

--Drop the transfer table if already exists, cater for second migration sql execution
IF OBJECT_ID('transferT_Config', 'U') IS NOT NULL
  DROP TABLE transferT_Config

--Must be encapsulated into try and catch, so any error will rollback the transaction
BEGIN TRY
    DECLARE @query AS NVARCHAR(MAX)
	DECLARE @TConfigNextId INT
    
    IF NOT EXISTS (SELECT NextId + 1 FROM T_Sequencer WHERE RecordType = 'Config')
	BEGIN
		ROLLBACK TRAN t1
		RETURN
	END
	
    SET @TConfigNextId = (SELECT NextId + 1 FROM T_Sequencer WHERE RecordType = 'Config')
    
    SET @query = 'CREATE TABLE [transferT_Config](
		ObjectId INT IDENTITY(' + CAST(@TConfigNextId AS VARCHAR(10)) + ', 1) PRIMARY KEY,
		ExistingObjectId INT NOT NULL,
		RefObjectType NVARCHAR(128) NOT NULL,
		RefObjectId INT NOT NULL,
		EntryName NVARCHAR(128) NOT NULL,
		AllowOverride INT NOT NULL,
		StringParam1 NVARCHAR(128) NULL,
		StringParam2 NVARCHAR(128) NULL,
		IntParam1 INT NULL,
		IntParam2 INT NULL,
		DateParam1 DATETIME NULL,
		DateParam2 DATETIME NULL,
		TextParam1 NTEXT NULL
	);
	CREATE INDEX IX_transferT_Config2 ON transferT_Config (RefObjectType, RefObjectId);'

	execute(@query)
	
END TRY
BEGIN CATCH
    PRINT N'Create table "transferT_Config" error';
    ROLLBACK TRANSACTION;
    RETURN;
END CATCH; 

/* END OF CREATING TEMPORARY TABLE */

/* STEP 2: BACKUP */
-- T_Settings BACKUP
INSERT INTO tmpT_Settings
SELECT *, @BackupDateTime AS BackupDateTime FROM T_Settings

-- T_SystemPolicy BACKUP
INSERT INTO tmpT_SystemPolicy(ObjectId, Principal, Permission, PolicyAction, PolicyObject, BackupDateTime)
SELECT ObjectId, Principal, Permission, PolicyAction, PolicyObject, @BackupDateTime
FROM T_SystemPolicy sp
WHERE Principal = 'SYSTEM' AND PolicyObject = 'SYSTEM' AND
	(Permission = 'ALLOWDOWNLOAD' OR
	 Permission = 'ALLOWINLINEEDITING' OR
	 Permission = 'ALLOWOFFLINE' OR
	 Permission = 'ALLOWSUBREVIEWS' OR
	 Permission = 'PDFIMAGEREVIEW' OR
	 Permission = 'DEFAULTPDFIMAGE' OR
	 Permission = 'AUTOUPDATECHANGESFROMMASTERREVIEW' OR
	 Permission = 'SUBREVIEWCOMMENTORIGINALLYBY' OR
	 Permission = 'REVIEWCOUNTDOWNTIMER' OR
	 Permission = 'ADDDOCUMENTAFTERSTART' OR
	 Permission = 'AUTODELETEDAYS' OR
	 Permission = 'DEFAULTREVIEWDURATION' OR
	 Permission = 'ALLOWBOOKMARK' OR
	 Permission = 'DEFAULTINDEPENDENT' OR
	 Permission = 'COMMENTATTACHMENT' OR
	 Permission = 'COMMENTHISTORY' OR
	 Permission = 'COMMENTNUMBER' OR
	 Permission = 'OWNERDEFAULTROLE' OR
	 Permission = 'DELETECOMMENTS' OR
	 Permission = 'HIDEREVIEWNAVIGATORFORPDF' OR
	 Permission = 'PARAGRAPHLEVELNOTIFICATION' OR
	 Permission = 'ALLOWPROPOSEDCHANGE' OR
	 Permission = 'AUTODELETEAUTHOROVERRIDE' OR
	 Permission = 'REMOVEEXTRAAUTONUMLGLTEXT' OR
	 Permission = 'QUICKACCEPT' OR
	 Permission = 'DEFAULTWATCH' OR
	 Permission = 'REOPENCOMMENT' OR
	 Permission = 'REOPENREVIEW' OR
	 Permission = 'AUTOREFRESH' OR
	 Permission = 'REVIEWFAILEDDOCUMENT' OR
	 Permission = 'REVIEWTIME' OR
	 Permission = 'TAKECONTROLOFREVIEW' OR
	 Permission = 'USEREMAILNOTIFY' OR
	 Permission = 'FORCEPARTICIPANTSTATUS' OR 
	 Permission = 'PARTICIPANTDEFAULTROLE' OR
	 Permission = 'SENDSYSTEMEMAILS' OR
	 Permission = 'RECYCLEPURGEDAYS' OR
	 Permission = 'RECYCLEPURGEDAYSAUTHOR' OR
	 Permission = 'USERPREFERENCES' OR
	 Permission = 'CANCELCHECKOUTONTRANSFER' OR
	 Permission = 'CHECKINAVAILABLE' OR
	 Permission = 'CHECKINWHENREVIEWOPEN' OR
	 Permission = 'AUTOCOMPLETEDAYS' OR
	 Permission = 'AUTODELETEREMINDER' OR
	 Permission = 'LOCALFILEUPLOAD' OR
	 Permission = 'UNLOCKEDWORKGROUPS' OR
	 Permission = 'PASSWORDPOLICY' OR
	 Permission = 'NEVERSENDWELCOMEEMAILFORSYSCON' OR
	 Permission = 'NOTRANSFERIFCHECKOUT' OR
	 Permission = 'SENDPASSWORDEMAILS' OR
	 Permission = 'CREATEREVIEWONLYINTASKGROUPS' OR
	 Permission = 'TASKGROUPSFORREVIEW' OR
	 Permission = 'PROTECTDOWNLOADEDDOCUMENT'
	);
	
INSERT INTO tmpT_SystemPolicy(ObjectId, Principal, Permission, PolicyAction, PolicyObject, BackupDateTime)
SELECT ObjectId, Principal, Permission, PolicyAction, PolicyObject, @BackupDateTime
FROM T_SystemPolicy sp
WHERE Principal = 'Sysadmin' AND PolicyObject = 'SYSTEM' AND
	(Permission = 'ENTERANYREVIEW' OR
	 Permission = 'CHANGEPASSWORD'
	);

INSERT INTO tmpT_SystemPolicy(ObjectId, Principal, Permission, PolicyAction, PolicyObject, BackupDateTime)
SELECT ObjectId, Principal, Permission, PolicyAction, PolicyObject, @BackupDateTime
FROM T_SystemPolicy sp
WHERE Principal = 'Admin' AND PolicyObject = 'SYSTEM' AND
	(Permission = 'ADDNEWUSER' OR
	 Permission = 'EDITALLUSERS' OR
	 Permission = 'CHANGEPASSWORD'
	);
/* END OF BACKUP */


/* STEP 3: MIGRATING SYSTEM POLICY (NON-USER DATA) */

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 1, 7, 'WORKGROUP', '1', 'AllowDownload', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWDOWNLOAD'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 1 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 2, 59, 'WORKGROUP', '1', 'InlineEditing', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, 'ToolbarOff', @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWINLINEEDITING'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 2 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 3, 6, 'WORKGROUP', '1', 'AllowOffline', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWOFFLINE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 3 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 4, 67, 'WORKGROUP', '1', 'AllowSubReviews', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWSUBREVIEWS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 4 AND c.IsMigrated = 1);

/* AllowPdfImage and DefaultPdfImage system policy will be combined for objectype = workgroup
   4 possibilities:
   AllowPdfImage = PERMIT DefaultPdfImage = PERMIT  >>> AllowOverride = 1, StringParam1 = image
   AllowPdfImage = PERMIT DefaultPdfImage = DENY    >>> AllowOverride = 1, StringParam1 = plugin
   AllowPdfImage = DENY   DefaultPdfImage = PERMIT  >>> AllowOverride = 0, StringParam1 = image
   AllowPdfImage = DENY   DefaultPdfImage = DENY    >>> AllowOverride = 0, StringParam1 = plugin
*/
INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 5, 45, 'WORKGROUP', '1', 'AllowPdfReview', 
	CASE WHEN PolicyAction = 'PERMIT'
	     THEN 1
	     ELSE 0 END,
	CASE WHEN (SELECT TOP 1 PolicyAction
			   FROM T_SystemPolicy
			   WHERE Principal = 'SYSTEM'
			   AND PolicyObject = 'SYSTEM'
			   AND Permission = 'DEFAULTPDFIMAGE'
	           ) = 'PERMIT'
	     THEN 'image' ELSE 'plugin' END, @BackupDateTime, 0
FROM T_SystemPolicy
WHERE Permission = 'PDFIMAGEREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 5 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 6, 70, 'WORKGROUP', '1', 'AllowArchiving', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWARCHIVING'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 6 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 7, 68, 'WORKGROUP', '1', 'AutoUpdateChangesFromMasterReview', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'AUTOUPDATECHANGESFROMMASTERREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 7 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 8, 69, 'WORKGROUP', '1', 'ShowSubreviewCommentOriginallyBy', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'SUBREVIEWCOMMENTORIGINALLYBY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 8 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 9, 65, 'WORKGROUP', '1', 'AllowReviewCountDownTimer', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'REVIEWCOUNTDOWNTIMER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 9 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 10, 12, 'WORKGROUP', '1', 'AllowAddDocumentAfterStart', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ADDDOCUMENTAFTERSTART'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 10 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 11, 54, 'WORKGROUP', '1', 'AutoDeleteDays', 0, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'AUTODELETEDAYS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 11 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 12, 51, 'WORKGROUP', '1', 'DefaultReviewDuration', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'DEFAULTREVIEWDURATION'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 12 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 13, 40, 'WORKGROUP', '1', 'AllowBookmark', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWBOOKMARK'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 13 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 14, 17, 'WORKGROUP', '1', 'DefaultIndependent', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'DEFAULTINDEPENDENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 14 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 15, 56, 'WORKGROUP', '1', 'AllowProposedChange', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ALLOWPROPOSEDCHANGE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 15 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 16, 49, 'WORKGROUP', '1', 'CommentAttachment', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'COMMENTATTACHMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 16 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 17, 36, 'WORKGROUP', '1', 'CommentHistory', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'COMMENTHISTORY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 17 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 18, 13, 'WORKGROUP', '1', 'DefaultWatch', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'DEFAULTWATCH'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 18 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 19, 50, 'WORKGROUP', '1', 'RemoveExtraAutonumlglText', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'REMOVEEXTRAAUTONUMLGLTEXT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 19 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 20, 37, 'WORKGROUP', '1', 'CommentNumber', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'COMMENTNUMBER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 20 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 21, 41, 'WORKGROUP', '1', 'OwnerDefaultRole', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'OWNERDEFAULTROLE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 21 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 22, 33, 'WORKGROUP', '1', 'EnterAnyReview', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ENTERANYREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 22 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 23, 38, 'WORKGROUP', '1', 'DeleteComments', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'DELETECOMMENTS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 23 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 24, 39, 'WORKGROUP', '1', 'HideReviewNavigatorForPDF', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'HIDEREVIEWNAVIGATORFORPDF'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 24 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 25, 43, 'WORKGROUP', '1', 'ParagraphLevelNotification', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'PARAGRAPHLEVELNOTIFICATION'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 25 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 26, 63, 'WORKGROUP', '1', 'AutoDelete', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'AUTODELETEAUTHOROVERRIDE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 26 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 27, 11, 'WORKGROUP', '1', 'AutoRefresh', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'AUTOREFRESH'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 27 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 28, 27, 'WORKGROUP', '1', 'QuickAccept', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, 'Off', @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'QUICKACCEPT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 28 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 29, 26, 'WORKGROUP', '1', 'ReOpenComment', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'REOPENCOMMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 29 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 30, 25, 'WORKGROUP', '1', 'ReOpenReview', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'REOPENREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 30 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 31, 60, 'WORKGROUP', '1', 'ReviewFailedDocument', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'REVIEWFAILEDDOCUMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 31 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 32, 32, 'WORKGROUP', '1', 'ReviewTime', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'REVIEWTIME'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 32 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 38, 14, 'WORKGROUP', '1', 'TakeControlOfReview', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'TAKECONTROLOFREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 38 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 33, 15, 'WORKGROUP', '1', 'UserEmailNotify', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'USEREMAILNOTIFY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 33 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 34, 28, 'WORKGROUP', '1', 'ForceParticipantStatus', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'FORCEPARTICIPANTSTATUS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 34 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 35, 42, 'WORKGROUP', '1', 'ParticipantDefaultRole', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'PARTICIPANTDEFAULTROLE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 35 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 36, 29, 'WORKGROUP', '1', 'SendSystemEmails', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'SENDSYSTEMEMAILS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 36 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 37, 64, 'WORKGROUP', '1', 'ProtectDownloadedDocument', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'PROTECTDOWNLOADEDDOCUMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 37 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 39, 52, 'WORKGROUP', '1', 'RecyclePurgeDays', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'RECYCLEPURGEDAYS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 39 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 40, 62, 'WORKGROUP', '1', 'RecyclePurgeDaysPleaseCompose', 1, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'RECYCLEPURGEDAYSAUTHOR'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 40 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 42, 61, 'WORKGROUP', '1', 'AddNewUser', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'ADDNEWUSER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 42 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 43, 1, 'WORKGROUP', '1', 'EditAllUsers', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'EDITALLUSERS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 43 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 44, 30, 'WORKGROUP', '1', 'UserPreferences', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'USERPREFERENCES'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 44 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 45, 47, 'WORKGROUP', '1', 'CancelCheckOutOnTransfer', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'CANCELCHECKOUTONTRANSFER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 45 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 46, 24, 'WORKGROUP', '1', 'CheckInAvailable', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'CHECKINAVAILABLE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 46 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 47, 23, 'WORKGROUP', '1', 'CheckInWhenReviewOpen', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'CHECKINWHENREVIEWOPEN'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 47 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 48, 53, 'WORKGROUP', '1', 'AutoCompleteDays', 0, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'AUTOCOMPLETEDAYS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 48 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 49, 55, 'WORKGROUP', '1', 'AutoDeleteReminder', 0, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'AUTODELETEREMINDER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 49 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
VALUES (50, 8, 'WORKGROUP', '1', 'LocalFileUpload', 1, 1, @BackupDateTime, 0);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 51, 3, 'WORKGROUP', '1', 'SysAdminChangePassword', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'CHANGEPASSWORD' AND Principal = 'Sysadmin'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 51 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 52, 2, 'WORKGROUP', '1', 'AdminChangePassword', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'CHANGEPASSWORD'  AND Principal = 'Admin'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 52 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
VALUES (53, 9, 'WORKGROUP', '1', 'UnlockedWorkgroups', 0, 1, @BackupDateTime, 0);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 54, 5, 'WORKGROUP', '1', 'PasswordPolicy', 0, PolicyAction, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'PASSWORDPOLICY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 54 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 55, 66, 'WORKGROUP', '1', 'NeverSendWelcomeEmailForSyscon', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'NEVERSENDWELCOMEEMAILFORSYSCON'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 55 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 56, 48, 'WORKGROUP', '1', 'NoTransferIfCheckout', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'NOTRANSFERIFCHECKOUT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 56 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 57, 0, 'WORKGROUP', '1', 'CommentLocation', 1, 'popup', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 57 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 58, 0, 'WORKGROUP', '1', 'CommentWindowSize', 1, 555, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 58 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 59, 0, 'WORKGROUP', '1', 'ReviewDefaultNavigation', 0, 'contents', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 59 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 60, 0, 'WORKGROUP', '1', 'EmbedReview', 0, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 60 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 61, 0, 'WORKGROUP', '1', 'ReviewDefaultNavigationPleaseCompose', 0, 'contents', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 61 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 62, 0, 'WORKGROUP', '1', 'DefaultTrack', 1, 'open', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 62 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 63, 0, 'WORKGROUP', '1', 'HighlightParagraph', 1, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 63 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 64, 0, 'WORKGROUP', '1', 'ShowPageBorders', 1, 1, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 64 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 65, 0, 'WORKGROUP', '1', 'DefaultView', 1, 'normal', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 65 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 66, 0, 'WORKGROUP', '1', 'Digest', 1, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 66 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 67,0,'WORKGROUP',1,'EmailTemplateAdminError',1,'AdminError',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 67 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 75,0,'WORKGROUP',1,'EmailTemplateChangeEmail',1,'ChangeEmail',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 75 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 76,0,'WORKGROUP',1,'EmailTemplateDigest',1,'Digest',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 76 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 77,0,'WORKGROUP',1,'EmailTemplateEditAllocate',1,'EditAllocate',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 77 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 78,0,'WORKGROUP',1,'EmailTemplateEditPublish',1,'EditPublish',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 78 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 79,0,'WORKGROUP',1,'EmailTemplateEditRevise',1,'EditRevise',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 79 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 80,0,'WORKGROUP',1,'EmailTemplateFinished',1,'Finished',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 80 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 82,0,'WORKGROUP',1,'EmailTemplateInvitation',1,'Invitation',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 82 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 85,0,'WORKGROUP',1,'EmailTemplateMasterReviewDelete',1,'MasterReviewDelete',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 85 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 86,0,'WORKGROUP',1,'EmailTemplateNewUser',1,'NewUser',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 86 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 87,0,'WORKGROUP',1,'EmailTemplateParagraphNotify',1,'ParagraphNotify',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 87 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 88,0,'WORKGROUP',1,'EmailTemplatePasswordReset',1,'PasswordReset',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 88 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 89,0,'WORKGROUP',1,'EmailTemplateReconcile',1,'Reconcile',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 89 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 90,0,'WORKGROUP',1,'EmailTemplateReminder',1,'Reminder',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 90 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 91,0,'WORKGROUP',1,'EmailTemplateReviewAutoComplete',1,'ReviewAutoComplete',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 91 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 92,0,'WORKGROUP',1,'EmailTemplateReviewAutoDelete',1,'ReviewAutoDelete',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 92 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 94,0,'WORKGROUP',1,'EmailTemplateReviewError',1,'ReviewError',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 94 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 95,0,'WORKGROUP',1,'EmailTemplateReviewNotify',1,'ReviewNotify',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 95 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 96,0,'WORKGROUP',1,'EmailTemplateReviewOverdue',1,'ReviewOverdue',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 96 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 97,0,'WORKGROUP',1,'EmailTemplateSupport',1,'Support',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 97 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 98,0,'WORKGROUP',1,'EmailTemplateSystemError',1,'SystemError',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 98 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 99,0,'WORKGROUP',1,'EmailTemplateWatch',1,'Watch',@BackupDateTime,0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 99 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 100, 34, 'WORKGROUP', '1', 'DeleteParticipantWithComments', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'DELETEPARTICIPANTWITHCOMMENTS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 100 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 101, 31, 'WORKGROUP', '1', 'StartOnSysconFail', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'STARTONSYSCONFAIL'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 101 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 102, 4, 'WORKGROUP', '1', 'SendPasswordEmails', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'SENDPASSWORDEMAILS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 102 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 103, 58, 'WORKGROUP', '1', 'CreateReviewOnlyInTaskgroups', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'CREATEREVIEWONLYINTASKGROUPS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 103 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 104, 57, 'WORKGROUP', '1', 'TaskgroupsForReview', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, @BackupDateTime, 0
FROM T_SystemPolicy sp
WHERE Permission = 'TASKGROUPSFORREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 104 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 105, 0, 'WORKGROUP', '1', 'DefaultReminder', 1, '3', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 105 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 106, 0, 'WORKGROUP', '1', 'AutoProgressReviewersComplete', 1, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 106 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 107, 0, 'WORKGROUP', '1', 'AutoProgressDueDate', 1, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 107 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 108, 0, 'WORKGROUP', '1', 'MandatoryCategorization', 1, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 108 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 109, 0, 'WORKGROUP', '1', 'TransferComments', 1, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 109 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 110, 0, 'WORKGROUP', '1', 'HomeWorkgroupRoot', 0, 0, @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 110 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 111, 0, 'WORKGROUP', '1', 'DefaultReviewType', 1,'', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 111 AND c.IsMigrated = 1);

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 112, 0, 'WORKGROUP', '1', 'ControlSetGroup', 1,'', @BackupDateTime, 0
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 112 AND c.IsMigrated = 1);

--fix inline editing
UPDATE T_Config
   SET EntryName = 'InlineEditing'
      ,StringParam1 = 'ToolbarOff'
WHERE EntryName = 'AllowInlineEditing';

--fix AutoDeleteDays
UPDATE T_Config
   SET IntParam1 = StringParam1
      ,StringParam1 = NULL
WHERE EntryName = 'AutoDeleteDays' AND StringParam1 IS NOT NULL;

--fix RecyclePurgeDaysPleaseCompose
UPDATE T_Config
   SET EntryName = 'RecyclePurgeDaysPleaseCompose'
WHERE EntryName = 'RecyclePurgeDaysPleaseAuthor' ;

--fix ReviewDefaultNavigationPleaseCompose
UPDATE T_Config
   SET EntryName = 'ReviewDefaultNavigationPleaseCompose'
WHERE EntryName = 'ReviewDefaultNavigationPleaseAuthor' ;

/* END OF MIGRATING SYSTEM POLICY (NON-USER DATA) */


/* STEP 4 : MIGRATION FROM T_SETTINGS ---> TRANST_CONFIG */
INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'AllowPdfReview', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultPdfReview'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'AllowPdfReview');

/* IntParam1 actually indicate allow delegation, by default, when user created a review, delegation is TRUE by default */
INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'DisallowDelegate', 0, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'DisallowDelegate'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'DisallowDelegate');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AllowProposedChange', 1, CASE WHEN s.IntParam1 = 1 THEN 0 ELSE 1 END
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AllowProposedChange'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AllowProposedChange');

DECLARE @allowDownloadAllowOverride INT;
SET @allowDownloadAllowOverride = 1
SELECT @allowDownloadAllowOverride = CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
FROM T_SystemPolicy
WHERE Permission = 'ALLOWDOWNLOAD' 
SELECT @allowDownloadAllowOverride = AllowOverride
FROM tmpT_Config
WHERE EntryName = 'AllowDownload' AND RefObjectType = 'Workgroup' AND RefObjectId = 1

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AllowDownload', @allowDownloadAllowOverride, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'OfflineOptions'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AllowDownload');

DECLARE @allowOfflineAllowOverride INT;
SET @allowOfflineAllowOverride = 1
SELECT @allowOfflineAllowOverride = CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
FROM T_SystemPolicy
WHERE Permission = 'ALLOWOFFLINE' 
SELECT @allowOfflineAllowOverride = AllowOverride
FROM tmpT_Config
WHERE EntryName = 'AllowOffline' AND RefObjectType = 'Workgroup' AND RefObjectId = 1

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AllowOffline', @allowOfflineAllowOverride, s.IntParam2
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'OfflineOptions'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AllowOffline');

DECLARE @inlinEditingAllowOverride INT;
SET @inlinEditingAllowOverride = 1
SELECT @inlinEditingAllowOverride = CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
FROM T_SystemPolicy
WHERE Permission = 'ALLOWINLINEEDITING' 
SELECT @inlinEditingAllowOverride = AllowOverride
FROM tmpT_Config
WHERE EntryName = 'InlineEditing' AND RefObjectType = 'Workgroup' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'InlineEditing', @inlinEditingAllowOverride, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'InlineEditing'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'InlineEditing');

DECLARE @autoDeleteAllowOverride INT;
SET @autoDeleteAllowOverride = 1
SELECT @autoDeleteAllowOverride = CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
FROM T_SystemPolicy
WHERE Permission = 'AUTODELETEAUTHOROVERRIDE' 
SELECT @autoDeleteAllowOverride = AllowOverride
FROM tmpT_Config
WHERE EntryName = 'AutoDelete' AND RefObjectType = 'Workgroup' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AutoDelete', @autoDeleteAllowOverride, CASE WHEN s.IntParam1 = 0 THEN 1 ELSE 0 END 
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AutoDelete'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AutoDelete');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'TransferComments', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'TransferComments'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'TransferComments');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'AutoRefresh', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'AutoRefresh'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'AutoRefresh');

DECLARE @quickAcceptAllowOverride INT;
SET @quickAcceptAllowOverride = 1
SELECT @quickAcceptAllowOverride = CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
FROM T_SystemPolicy
WHERE Permission = 'QUICKACCEPT' 
SELECT @quickAcceptAllowOverride = AllowOverride
FROM tmpT_Config
WHERE EntryName = 'QuickAccept' AND RefObjectType = 'Workgroup' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'QuickAccept', @quickAcceptAllowOverride, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'QuickAccept'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'QuickAccept');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AutoProgressReviewersComplete', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AutoProgress'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AutoProgressReviewersComplete');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AutoProgressDueDate', 1, s.IntParam2
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AutoProgress'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AutoProgressDueDate');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'MandatoryCategorization', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'MandatoryCategorization'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'MandatoryCategorization');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'CommentLocation', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'CommentLocation'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'CommentLocation');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'CommentWindowSize', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'CommentWindowSize'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'CommentWindowSize');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'ReviewDefaultNavigation', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultNavigation'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'ReviewDefaultNavigation');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'ReviewDefaultNavigationPleaseCompose', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'PA_DefaultNavigation'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'ReviewDefaultNavigationPleaseCompose');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'DefaultTrack', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultTrack'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'DefaultTrack');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'HighlightParagraph', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'HighlighParagraph'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'HighlightParagraph');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'ShowPageBorders', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'ShowPageBorders'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'ShowPageBorders');


INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'DefaultView', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultView'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'DefaultView');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'Digest', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'Digest'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'Digest');

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'EmbedReview', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'EmbedReview'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'EmbedReview');


/* END OF MIGRATION FROM T_SETTINGS ---> TMPT_CONFIG */



/* STEP 5 : MIGRATION FROM TRANST_CONFIG ---> TMPT_CONFIG
		              AND  TMPT_CONFIG ---> T_CONFIG
*/
INSERT INTO tmpT_Config(ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, StringParam2, IntParam1, IntParam2, DateParam1, DateParam2, TextParam1, BackupDateTime, IsMigrated)
SELECT ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, StringParam2, IntParam1, IntParam2, DateParam1, DateParam2, TextParam1, @BackupDateTime, 0
FROM transferT_Config t

INSERT INTO T_Config(ObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, StringParam2, IntParam1, IntParam2, DateParam1, DateParam2, TextParam1)
SELECT t.ObjectId, t.RefObjectType, t.RefObjectId, t.EntryName, t.AllowOverride, t.StringParam1, t.StringParam2, t.IntParam1, t.IntParam2, t.DateParam1, t.DateParam2, t.TextParam1
FROM tmpT_Config t LEFT JOIN T_Config c ON t.ObjectId = c.ObjectId
WHERE IsMigrated = 0
/* END OF MIGRATION FROM TMPT_CONFIG ---> T_CONFIG */




/* STEP 6 : UPDATING BACK MIGRATED for IsMigrated */
UPDATE t
SET t.IsMigrated = 1
FROM tmpT_Config t INNER JOIN T_Config trans ON t.ObjectId = trans.ObjectId
WHERE t.BackupDateTime = @BackupDateTime
/* END OF UPDATING BACK MIGRATED for IsMigrated */




/* STEP 7 : DELETING EXISTING DATA AT T_SETTINGS TABLE */
DELETE s FROM T_Settings s INNER JOIN tmpT_Config trans ON s.ObjectId = trans.ExistingObjectId WHERE IsMigrated = 1
/* END OF DELETING EXISTING DATA AT T_SETTINGS TABLE */





/* STEP 8 : DELETING FROM EXISTING SYSTEM POLICY (Add according to ObjectId sequence please */
/* see PR-3822, script removed from this section because T_SystemPolicy is dropped in the script below */
/* END OF DELETING FROM SYSTEM POLICY */





/* STEP 9 : UPDATING BACK T_SEQUENCER FOR T_Config TABLE */
IF (SELECT MAX(ObjectId) FROM T_Config) >= (SELECT TOP 1 NEXTID FROM T_Sequencer WHERE RecordType = 'Config')
BEGIN
	UPDATE T_Sequencer SET NEXTID = (SELECT MAX(ObjectId) FROM T_Config) + 1 WHERE RecordType = 'Config';
END
/* END OF UPDATING BACK T_SEQUENCER FOR T_Config TABLE */

COMMIT TRAN t1
GO
/* End of PR-3521 */

CREATE TABLE T_LicenseFile(
	ObjectId int NOT NULL,
	ParentLicenseFileId int NULL,
	LicenseDetailsXml ntext NULL,
	UpdateDate datetime NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX IX_LicenseFile ON T_LicenseFile(ObjectId);

/* */
/* PR-3525 - Workgroup Management Enhancement */
/* TO SUPPORT MULTIPLE WORKGROUP WITH THE SAME NAME, THEREFORE THE UNIQUE CONTRAINTS MUST BE REMOVED. */
BEGIN TRAN TWorkgroupTRANS
BEGIN TRY
	/* STEP 1.0 - CREATE A NEW TABLE [T_WorkgroupNew] */
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
	CREATE TABLE T_WorkgroupNew(
		ObjectId int NOT NULL,
		WorkgroupName nvarchar(128) NOT NULL,
		DescriptionNote nvarchar(2000) NULL,
		LicenseId int NOT NULL,
		ExtSource nvarchar(128) NULL,
		ExtRef nvarchar(128) NULL,
		IsTaskgroup int NOT NULL,
		IsPublic int NULL,
		IsAllMonitor int NOT NULL,
		ParentWorkgroupId int NULL
	) ON [PRIMARY]

	/* STEP 2.0 - EXISTING UNIQUE INDEX ON [ObjectId] */
	CREATE UNIQUE CLUSTERED INDEX IX_T_Workgroup ON T_WorkgroupNew
	(
		ObjectId ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	/* ##### STEP 3.0 - THIS IS THE INDEX THAT CAUSED THE UNIQUE KEY CONSTRAINT ERROR, RECREATE WITH NON UNIQUE WITH [LicenseId], [WorkgroupName] */
	SET ANSI_PADDING ON
	CREATE NONCLUSTERED INDEX IX_T_Workgroup2 ON T_WorkgroupNew
	(
		LicenseId ASC,
		WorkgroupName ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	/* STEP 4.0 - COPY DATA FROM [T_Workgroup] TO [T_WorkgroupNew] */
	INSERT INTO T_WorkgroupNew (ObjectId, WorkgroupName, DescriptionNote, LicenseId, ExtSource, ExtRef, IsTaskgroup, IsPublic, IsAllMonitor, ParentWorkgroupId)
	SELECT ObjectId, WorkgroupName, DescriptionNote, LicenseId, ExtSource, ExtRef, IsTaskgroup, IsPublic, IsAllMonitor, ParentWorkgroupId FROM T_Workgroup;

	/* STEP 5.0 - DROP TABLE [T_Workgroup] */
	DROP TABLE T_Workgroup;

	/* STEP 6.0 - RENAME THE TABLE BACK TO [T_Workgroup] */
	EXEC sp_rename 'T_WorkgroupNew', 'T_Workgroup';

	/* COMMIT TRANSACTION ONLY IF IT IS SUCCESSFUL. */
	COMMIT TRANSACTION TWorkgroupTRANS
END TRY
BEGIN CATCH
	SELECT 'PR-3525 SCRIPT ERROR ' + ERROR_MESSAGE();
	ROLLBACK TRANSACTION TWorkgroupTRANS
END CATCH;
/* END OF PR-3525. */


DELETE FROM T_Config WHERE ObjectId in (68,69,70,71,72,73,74,81);
GO
DELETE FROM T_StatusCode WHERE StatusType in ('A','V');
GO



/* PR-3803 - Slow Document Preparation. 
- Column to track whether a document is uploaded with advanced setting. (e.g. Complex Document). */
ALTER TABLE T_Document
ADD AdvancedSetting NVARCHAR(5) NULL;

/* PR-3822 - Remove T_SystemPolicy table and obsolete system policies */
DROP TABLE T_SystemPolicy;
GO


/* PR-3523 - Configurability (Settings) - Database Changes & Migration Script */
/* Drop All Temp Tables. */
DROP TABLE tmpT_Config;
GO
DROP TABLE tmpT_Settings;
GO
DROP TABLE tmpT_SystemPolicy;
GO
DROP TABLE transferT_Config;
GO

/* Schema Versioning */
UPDATE T_SchemaInfo SET SchemaVersion = '5.1.0';
GO
/* End of Schema Versioning */
