﻿/* Delete duplicate records in T_Edit */
DELETE FROM T_Edit WHERE ROWID IN(SELECT rid FROM (
  SELECT T_Edit.*, ROWID rid, 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 */
DECLARE 
iCountEditRoleSequencer INTEGER;
BEGIN
    SELECT COUNT(*) INTO iCountEditRoleSequencer FROM T_SEQUENCER WHERE RecordType = 'EditRole';   
    IF iCountEditRoleSequencer = 0 THEN
        EXECUTE IMMEDIATE 'INSERT INTO T_Sequencer (RecordType, NextId) VALUES (''EditRole'', 100)';
    END IF;
END;
/
COMMIT;

-- =============================================
-- Description:	Replicating existing records
-- =============================================
--Replicate T_Edit
CREATE TABLE T_Edit_6_3 AS SELECT * FROM T_Edit;

--Replicate T_EditRole as backup
CREATE TABLE T_EditRole_6_3 AS SELECT * FROM T_EditRole;

--Replicate T_EditVersion as backup
CREATE TABLE T_EditVersion_6_3 AS SELECT * FROM T_EditVersion;

--Add column to T_Edit to track record migration
ALTER TABLE T_Edit_6_3 ADD PrevRZEditId NUMBER NULL;
COMMIT;

-- =============================================
-- Description:	Renaming existing tables for backup
-- =============================================
--Rename existing T_Edit to T_Edit_Old
ALTER TABLE T_Edit RENAME TO T_Edit_Old;

--Rename existing T_EditRole to T_EditRole_Old
ALTER TABLE T_EditRole RENAME TO T_EditRole_Old;

--Rename existing T_EditVersion to T_EditVersion_Old
ALTER TABLE T_EditVersion RENAME TO T_EditVersion_Old;
COMMIT;

-- =============================================
-- Description:	Add Primary keys to existing table
-- =============================================
DECLARE 
iCountIndexSubject INTEGER;
iCountIndexReview INTEGER;
iCountIndexDocument INTEGER;
BEGIN
    SELECT COUNT(*) INTO iCountIndexSubject FROM user_indexes WHERE index_name = 'IX_SUBJECT';   
    IF iCountIndexSubject > 0 THEN
        EXECUTE IMMEDIATE 'DROP INDEX IX_SUBJECT';
    END IF;
    SELECT COUNT(*) INTO iCountIndexReview FROM user_indexes WHERE index_name = 'IX_REVIEW';
    IF iCountIndexReview > 0 THEN
        EXECUTE IMMEDIATE 'DROP INDEX IX_REVIEW';
    END IF;
    SELECT COUNT(*) INTO iCountIndexDocument FROM user_indexes WHERE index_name = 'IX_DOCUMENT';
     IF iCountIndexDocument > 0 THEN
        EXECUTE IMMEDIATE 'DROP INDEX IX_DOCUMENT';
    END IF;
END;
/
ALTER TABLE T_Subject ADD CONSTRAINT PK_Subject PRIMARY KEY (ObjectId);
ALTER TABLE T_Review ADD CONSTRAINT PK_Review PRIMARY KEY (ObjectId);
ALTER TABLE T_Document ADD CONSTRAINT PK_Document PRIMARY KEY (ObjectId);
COMMIT;

-- =============================================
-- Description:	Creating new T_Edit table
-- =============================================
CREATE TABLE T_Edit (
	ObjectId			INT		NOT NULL,
	ReviewId			INT		NOT NULL,
	DocumentId			INT		NOT NULL,
	StartParagraphId	NVARCHAR2(128)	NOT NULL,
	EndParagraphId		NVARCHAR2(128)	NOT NULL,
	Title				NVARCHAR2(128) NOT NULL,
	Description			NCLOB,
	EditInWordStatus    NVARCHAR2(3) NULL,
	IsEditInWordEnabled INT DEFAULT 0 NOT NULL,
	CreateDate 			TIMESTAMP(3) NULL,
	UpdateDate 			TIMESTAMP(3) NULL,
	DefaultRoleForZone	NVARCHAR2(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 INDEX IX_T_Edit_DocumentId ON T_Edit (DocumentId);
CREATE INDEX IX_T_Edit_ReviewId ON T_Edit (ReviewId);

-- =============================================
-- Description:	Creating new T_EditRole table
-- =============================================
CREATE TABLE T_EditRole(
	ObjectId INT NOT NULL,
	EditId INT NOT NULL,
	SubjectId INT NOT NULL,
	ReviewZoneRole NVARCHAR2(128) NULL,
	IsContributor	INT DEFAULT 0 NOT NULL,
	CheckOut		INT DEFAULT 0 NOT NULL,
	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 INDEX IX_T_EditRole_EditId ON T_EditRole (EditId);
CREATE INDEX IX_T_EditRole_SubjectId ON T_EditRole (SubjectId);
CREATE UNIQUE INDEX IX_T_EditRole_CheckOut ON T_EditRole(CASE WHEN CheckOut = 1 THEN EditId END);

-- =============================================
-- Description:	Creating new T_EditVersion table
-- =============================================
CREATE TABLE T_EditVersion (
	ObjectId			INT		NOT NULL,
	EditId				INT		NOT NULL,
	Version				INT		NOT NULL,
	UpdateDate			TIMESTAMP(3) NOT NULL,
	ModificationCount	INT		NOT NULL,
	Status				NVARCHAR2(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 INDEX IX_T_EditVersion_EditId ON T_EditVersion (EditId);
COMMIT;

-- =============================================
-- Description:	Data migration from old structure to new structure.
-- =============================================
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_Edit_6_3 SET 
	PrevRZEditId = (SELECT S.ObjectId FROM T_Edit S
	WHERE T_Edit_6_3.ReviewId = S.ReviewId AND T_Edit_6_3.DocumentId = S.DocumentId AND T_Edit_6_3.StartParagraphId = S.StartParagraphId AND T_Edit_6_3.EndParagraphId = S.EndParagraphId
	AND T_Edit_6_3.Status <> 'F' AND T_Edit_6_3.ZoneType = 'E')
	WHERE EXISTS 
	(SELECT 1 FROM T_Edit S WHERE T_Edit_6_3.ReviewId = S.ReviewId AND T_Edit_6_3.DocumentId = S.DocumentId 
  AND T_Edit_6_3.StartParagraphId = S.StartParagraphId AND T_Edit_6_3.EndParagraphId = S.EndParagraphId AND T_Edit_6_3.Status <> 'F' AND T_Edit_6_3.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_Edit SET 
	(ObjectId, IsEditInWordEnabled, EditInWordStatus, Title, Description, UpdateDate, CreateDate) = 
  (SELECT S.ObjectId, 1, S.Status, S.Title, S.Description, S.UpdateDate,
  CASE WHEN S.UpdateDate > T_Edit.UpdateDate THEN S.UpdateDate ELSE T_Edit.CreateDate END FROM T_Edit_6_3 S /* EZ (S.UpdateDate) is created before RZ (T.CreateDate) */
	WHERE T_Edit.ReviewId = S.ReviewId AND T_Edit.DocumentId = S.DocumentId AND T_Edit.StartParagraphId = S.StartParagraphId AND T_Edit.EndParagraphId = S.EndParagraphId
	AND S.Status <> 'F' AND S.ZoneType = 'E')
	WHERE EXISTS 
	(SELECT 1 FROM T_Edit_6_3 S WHERE T_Edit.ReviewId = S.ReviewId AND T_Edit.DocumentId = S.DocumentId AND T_Edit.StartParagraphId = S.StartParagraphId AND T_Edit.EndParagraphId = S.EndParagraphId
	AND 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 L LEFT JOIN T_Edit 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_EditRole_6_3 SET EditId = (SELECT S.ObjectId FROM T_Edit_6_3 S WHERE T_EditRole_6_3.EditId = S.PrevRZEditId)
  WHERE EXISTS 
	(SELECT 1 FROM T_Edit_6_3 S WHERE T_EditRole_6_3.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 L RIGHT JOIN T_Edit_6_3 R
	ON L.EditId = R.ObjectId LEFT JOIN T_Edit 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;
  BEGIN
    SELECT COALESCE(MAX(ObjectId), 100) INTO NewNextId FROM T_EditRole_6_3;
    UPDATE T_Sequencer SET NextId = NewNextId WHERE RecordType = 'EditRole';
	END;
  
	/* 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_EditRole SET 
	CheckOut = 1,	IsContributor = 1	
	WHERE EXISTS 
	(SELECT 1 FROM T_Edit_6_3 S 
	WHERE T_EditRole.EditId = S.ObjectId AND T_EditRole.SubjectId = S.SubjectId 
	AND 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_EditRole SET 
		CheckOut = 0 
	WHERE EXISTS
	(SELECT 1 FROM 
		(SELECT L.ObjectId, L.IsEditInWordEnabled, L.EditInWordStatus FROM T_Edit L LEFT JOIN 
		 T_EditVersion R ON L.ObjectId = R.EditId WHERE R.EditId IS NULL AND L.EditInWordStatus = 'P' AND L.IsEditInWordEnabled = 1) S 
	 WHERE T_EditRole.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;	
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK TO before_migration;
END;
/
COMMIT;
-- =============================================
-- Description:	Clean up tables.
-- =============================================
DROP TABLE T_Edit_6_3;

DROP TABLE T_EditRole_6_3;

DROP TABLE T_EditVersion_6_3;

DROP TABLE T_Edit_Old;

DROP TABLE T_EditRole_Old;

DROP TABLE T_EditVersion_Old;

COMMIT;

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

/* 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);
COMMIT;

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

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

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