/* Delete duplicate records in T_Edit */
DELETE x FROM (
  SELECT T_Edit.*, row_number() OVER (PARTITION BY ReviewId, DocumentId, StartParagraphId, EndParagraphId, Title, SubjectId, Status, ZoneType ORDER BY UpdateDate DESC) rn
  FROM T_Edit WHERE Status <> 'F'
) x 
WHERE rn > 1;

/* Delete orphan records in T_Edit */
DELETE FROM T_Edit WHERE ReviewId NOT IN  (
SELECT ObjectId FROM T_REVIEW);

DELETE FROM T_Edit WHERE DocumentId NOT IN  (
SELECT ObjectId FROM T_DOCUMENT);

/* Clean up for T_EditRole */
DELETE FROM T_EditRole WHERE editId NOT IN  (
SELECT ObjectId FROM T_Edit);

DELETE FROM T_EditRole WHERE SubjectId NOT IN  (
SELECT ObjectId FROM T_SUBJECT);

/* Add missing T_Sequencer record for EditRole */
IF NOT EXISTS (SELECT * FROM T_SEQUENCER  WHERE RecordType = 'EditRole')
	BEGIN
		INSERT INTO T_Sequencer (RecordType, NextId) VALUES ('EditRole', 100)
	END

GO
-- =============================================
-- Description:	Replicating existing records
-- =============================================
--Replicate T_Edit
SELECT * INTO T_Edit_6_3 FROM T_Edit;
GO

--Replicate T_EditRole as backup
SELECT * INTO T_EditRole_6_3 FROM T_EditRole;
GO

--Replicate T_EditVersion as backup
SELECT * INTO T_EditVersion_6_3 FROM T_EditVersion;
GO 

--Add column to T_Edit to track record migration
ALTER TABLE T_Edit_6_3 ADD PrevRZEditId [int] NULL;
GO

-- =============================================
-- Description:	Renaming existing tables for backup
-- =============================================
--Rename existing T_Edit to T_Edit_Old
EXEC sp_rename 'T_Edit', 'T_Edit_Old';
GO

--Rename existing T_EditRole to T_EditRole_Old
EXEC sp_rename 'T_EditRole', 'T_EditRole_Old';
GO

--Rename existing T_EditVersion to T_EditVersion_Old
EXEC sp_rename 'T_EditVersion', 'T_EditVersion_Old';
GO

-- =============================================
-- Description:	Add Primary keys to existing table
-- =============================================
IF EXISTS (SELECT * FROM sys.indexes  WHERE NAME='IX_Subject' AND object_id = OBJECT_ID('T_Subject'))
	BEGIN
		DROP INDEX IX_Subject ON T_Subject;
	END
ALTER TABLE T_Subject ADD CONSTRAINT PK_Subject PRIMARY KEY (ObjectId);
GO

IF EXISTS (SELECT * FROM sys.indexes  WHERE NAME='IX_Review' AND object_id = OBJECT_ID('T_Review'))
	BEGIN
		DROP INDEX IX_Review ON T_Review;
	END
ALTER TABLE T_Review ADD CONSTRAINT PK_Review PRIMARY KEY (ObjectId);
GO

IF EXISTS (SELECT * FROM sys.indexes  WHERE NAME='IX_Document' AND object_id = OBJECT_ID('T_Document'))
	BEGIN
		DROP INDEX IX_Document ON T_Document;
	END
ALTER TABLE T_Document ADD CONSTRAINT PK_Document PRIMARY KEY (ObjectId);
GO

-- =============================================
-- Description:	Creating new T_Edit table
-- =============================================
CREATE TABLE T_Edit (
	ObjectId			INT		NOT NULL,
	ReviewId			INT		NOT NULL,
	DocumentId			INT		NOT NULL,
	StartParagraphId	NVARCHAR(128)	NOT NULL,
	EndParagraphId		NVARCHAR(128)	NOT NULL,
	Title				NVARCHAR(128) NOT NULL,
	Description			NVARCHAR(Max) NULL,
	EditInWordStatus    NVARCHAR(3) NULL,
	IsEditInWordEnabled BIT NOT NULL DEFAULT 0,
	CreateDate 			DATETIME NULL,
	UpdateDate 			DATETIME NULL,
	DefaultRoleForZone	NVARCHAR(128)	NULL,
	CONSTRAINT PK_Edit PRIMARY KEY (ObjectId),
	CONSTRAINT FK_T_Edit_T_Review FOREIGN KEY (ReviewId) REFERENCES T_Review(ObjectId),
	CONSTRAINT FK_T_Edit_T_Document FOREIGN KEY (DocumentId) REFERENCES T_Document(ObjectId)	
);
CREATE NONCLUSTERED INDEX IX_T_Edit_DocumentId ON T_Edit (DocumentId);
CREATE NONCLUSTERED INDEX IX_T_Edit_ReviewId ON T_Edit (ReviewId);
GO

-- =============================================
-- Description:	Creating new T_EditRole table
-- =============================================
CREATE TABLE T_EditRole(
	ObjectId		INT		NOT NULL,
	EditId			INT		NOT NULL,
	SubjectId		INT		NOT NULL,
	ReviewZoneRole	NVARCHAR(128) NULL,
	IsContributor	BIT		NOT NULL  DEFAULT 0,
	CheckOut		BIT		NOT NULL  DEFAULT 0,
	CONSTRAINT PK_EditRole PRIMARY KEY (ObjectId),
	CONSTRAINT FK_T_EditRole_T_Edit FOREIGN KEY (EditId) REFERENCES T_Edit(ObjectId),
	CONSTRAINT FK_T_EditRole_T_Subject FOREIGN KEY (SubjectId) REFERENCES T_Subject(ObjectId),
	CONSTRAINT UC_T_EditRole UNIQUE (EditId, SubjectId)
);
CREATE NONCLUSTERED INDEX IX_T_EditRole_EditId ON T_EditRole (EditId);
CREATE NONCLUSTERED INDEX IX_T_EditRole_SubjectId ON T_EditRole (SubjectId);
CREATE UNIQUE NONCLUSTERED INDEX IX_T_EditRole_CheckOut ON T_EditRole (EditId, CheckOut) WHERE CHECKOUT = 1;
GO

-- =============================================
-- Description:	Creating new T_EditVersion table
-- =============================================
CREATE TABLE T_EditVersion (
	ObjectId			INT		NOT NULL,
	EditId				INT		NOT NULL,
	Version				INT		NOT NULL,
	UpdateDate			DATETIME NOT NULL,
	ModificationCount	INT		NOT NULL,
	Status				NVARCHAR(128)	NOT NULL,
	ReplaceDocumentId	INT		NULL,
	UploadedBySubjectId	INT		NULL,
	LastUpdatedBySubjectId	INT	NULL,
	CONSTRAINT PK_EditVersion PRIMARY KEY (ObjectId),
	CONSTRAINT FK_T_EditVersion_T_Edit FOREIGN KEY (EditId) REFERENCES T_Edit(ObjectId),
	CONSTRAINT UC_T_EditVersion UNIQUE (EditId, Version)	
);
CREATE NONCLUSTERED INDEX IX_T_EditVersion_EditId ON T_EditVersion (EditId);
GO

-- =============================================
-- Description:	Data migration from old structure to new structure.
-- =============================================
BEGIN TRANSACTION;
BEGIN	
	/* Insert all RZ into new T_Edit */
	INSERT INTO T_Edit 
	(ObjectId, ReviewId, DocumentId, StartParagraphId, EndParagraphId, Title, Description, EditInWordStatus, IsEditInWordEnabled, CreateDate, UpdateDate, DefaultRoleForZone)
	SELECT ObjectId, ReviewId, DocumentId, StartParagraphId, EndParagraphId, Title, Description, 'P', 0, UpdateDate, UpdateDate, DefaultRoleForZone
	FROM T_Edit_6_3
	WHERE ZoneType = 'R';
	
	/* Get reference to new ObjectId for RZ that has been combined with EZ */
	UPDATE T SET 
		T.PrevRZEditId = S.ObjectId
	FROM T_Edit_6_3 AS T INNER JOIN T_Edit AS S
	ON T.ReviewId = S.ReviewId AND T.DocumentId = S.DocumentId AND T.StartParagraphId = S.StartParagraphId AND T.EndParagraphId = S.EndParagraphId
	WHERE T.Status <> 'F' AND T.ZoneType = 'E';
	
	/* Update RZ with details from EZ where EZ is having same StartParagraphId and EndParagraphId as EZ and is of status other than 'Publish and Final' */
	UPDATE T SET 
		T.ObjectId = S.ObjectId, 
		T.IsEditInWordEnabled = 1, 
		T.EditInWordStatus = S.Status, 
		T.Title = S.Title, 
		T.Description = S.Description,
		T.UpdateDate = S.UpdateDate,
		T.CreateDate = CASE WHEN DATEDIFF(second, S.UpdateDate, T.UpdateDate) > 0 THEN S.UpdateDate ELSE T.CreateDate END /* EZ (S.UpdateDate) is created before RZ (T.CreateDate) */
	FROM T_Edit AS T INNER JOIN T_Edit_6_3 AS S
	ON T.ReviewId = S.ReviewId AND T.DocumentId = S.DocumentId AND T.StartParagraphId = S.StartParagraphId AND T.EndParagraphId = S.EndParagraphId
	WHERE S.Status <> 'F' AND S.ZoneType = 'E';
	
	/* INSERT EZ records into new T_Edit where EZ is NOT having same StartParagraphId and EndParagraphId as any RZ or having same StartParagraphId and EndParagraphId as RZ */
	/* but is of status 'Publish and Final' */
	INSERT INTO T_Edit
	(ObjectId, ReviewId, DocumentId, StartParagraphId, EndParagraphId, Title, Description, EditInWordStatus, IsEditInWordEnabled, CreateDate, UpdateDate, DefaultRoleForZone)
	SELECT L.ObjectId, L.ReviewId, L.DocumentId, L.StartParagraphId, L.EndParagraphId, L.Title, L.Description, L.Status, 1, L.UpdateDate, L.UpdateDate, L.DefaultRoleForZone
	FROM T_Edit_6_3 AS L LEFT JOIN T_Edit AS R 
	ON L.ObjectId = R.ObjectId 
	WHERE R.ObjectId IS NULL AND L.ZoneType = 'E';

	/* Update EditId of records in T_EditRole_6_3 with new ObjectId of RZ that has been combined with EZ */
	UPDATE T SET T.EditId = S.ObjectId 
	FROM T_EditRole_6_3 AS T INNER JOIN T_Edit_6_3 AS S
	ON T.EditId = S.PrevRZEditId;
	
	/* Create new EZ related records in T_EditRole_6_3 for EZ records with only single contributor */
	INSERT INTO T_EditRole_6_3
	(ObjectId, EditId, SubjectId, ReviewZoneRole)
	SELECT ((SELECT NextId FROM T_Sequencer WHERE RecordType = 'EditRole') + ROW_NUMBER() OVER (ORDER BY R.ObjectId)), 
	R.ObjectId, R.SubjectId, NULL
	FROM T_EditRole_6_3 AS L RIGHT JOIN T_Edit_6_3 AS R
	ON L.EditId = R.ObjectId LEFT JOIN T_Edit AS R2 
	ON R.ObjectId = R2.ObjectId 
	WHERE L.EditId IS NULL AND R2.ObjectId IS NOT NULL;
	
	/* Update T_Sequencer table with new NextId for RecordType = EditRole */
	DECLARE @NewNextId INT;
	SELECT @NewNextId = COALESCE(MAX(ObjectId), 100) FROM T_EditRole_6_3;
	UPDATE T_Sequencer SET NextId = @NewNextId WHERE RecordType = 'EditRole';
	
	/* Insert all EZ contributors into new T_EditRole */
	INSERT INTO T_EditRole
	(ObjectId, EditId, SubjectId, ReviewZoneRole, IsContributor, CheckOut)
	SELECT ObjectId, EditId, SubjectId, NULL, 1, 0
	FROM T_EditRole_6_3
	WHERE ReviewZoneRole IS NULL;
		
	/* Update EZ contributors with RZ participants detail */
	/* RZ participant is also an EZ contributor */
	UPDATE T_EditRole SET 
	ReviewZoneRole = (SELECT S.ReviewZoneRole FROM T_EditRole_6_3 S WHERE T_EditRole.EditId = S.EditId AND T_EditRole.SubjectId = S.SubjectId AND ReviewZoneRole IS NOT Null)
	WHERE EXISTS 
	(SELECT 1 FROM T_EditRole_6_3 S WHERE T_EditRole.EditId = S.EditId AND T_EditRole.SubjectId = S.SubjectId);	
	
	/* RZ participant is not an EZ contributor */
	INSERT INTO T_EditRole 
	SELECT S.ObjectId, S.EditId, S.SubjectId, S.ReviewZoneRole, 0, 0
	FROM T_EditRole_6_3 S LEFT JOIN T_EditRole T
	ON S.EditId = T.EditId AND S.SubjectId = T.SubjectId
	WHERE T.EditId IS NULL AND T.SubjectId IS NULL;			
	
	/* Update CheckOut column to highlight contributors who have downloaded document in EZ */
	UPDATE T SET 
		T.CheckOut = 1,
		IsContributor = 1
	FROM T_EditRole AS T INNER JOIN T_Edit_6_3 AS S 
	ON T.EditId = S.ObjectId AND T.SubjectId = S.SubjectId 
	WHERE S.ZoneType = 'E' AND S.Status <> 'F';
	
	/* Update CheckOut status to 0 (to indicate zone not downloaded) for Zone with "Not Started" status */
	UPDATE T SET
		T.CheckOut = 0
	FROM T_EditRole AS T INNER JOIN (
		SELECT L.ObjectId, L.IsEditInWordEnabled, L.EditInWordStatus FROM T_Edit AS L LEFT JOIN 
		T_EditVersion AS R ON L.ObjectId = R.EditId 
		WHERE R.EditId IS NULL AND L.EditInWordStatus = 'P' AND L.IsEditInWordEnabled = 1) AS S
	ON T.EditId = S.ObjectId;
	
	/* Insert all into T_EditVersion */
	INSERT INTO T_EditVersion
	(ObjectId, EditId, Version, UpdateDate, ModificationCount, Status, ReplaceDocumentId, UploadedBySubjectId, LastUpdatedBySubjectId) 
	SELECT ObjectId, EditId, Version, UpdateDate, ModificationCount, Status, ReplaceDocumentId, UploadedBySubjectId, LastUpdatedBySubjectId 
	FROM T_EditVersion_6_3;	
END	
IF @@TRANCOUNT > 0
	COMMIT TRANSACTION;

-- =============================================
-- Description:	Clean up tables.
-- =============================================
DROP TABLE T_Edit_6_3;
GO

DROP TABLE T_EditRole_6_3;
GO

DROP TABLE T_EditVersion_6_3;
GO	

DROP TABLE T_Edit_Old;
GO

DROP TABLE T_EditRole_Old;
GO

DROP TABLE T_EditVersion_Old;
GO

/* Add new columns for T_Participant */
ALTER TABLE T_Participant ADD Capacity NVARCHAR(2000) NULL;
ALTER TABLE T_Participant ADD Opinion NVARCHAR(2000) NULL;
GO

/* Add 'Imported Comment' subject */
INSERT INTO T_Subject(ObjectId,FullName,MailAddress,WorkgroupId,SystemRole,LicenseAgreed,CultureName,Active,LastLoginDate,DescriptionNote,UserType,MailAddressL,HomeWorkgroupId)
VALUES (-2,'Imported Comment','importedcomment@example.com',0,NULL,0,'',1,NULL,NULL,'','importedcomment@example.com', 1);
GO

UPDATE T_Config SET IntParam1 = CASE WHEN IntParam1 = 3 THEN 0 WHEN IntParam1 = 4 THEN 1 ELSE IntParam1 END WHERE EntryName = 'DefaultWatch';
GO

UPDATE T_Settings SET IntParam1 = CASE WHEN IntParam1 = 3 THEN 0 WHEN IntParam1 = 4 THEN 1 ELSE IntParam1 END WHERE EntryName = 'Watch';
GO

/* Schema Versioning */
UPDATE T_SchemaInfo SET SchemaVersion = '6.4.0';
GO
