/* Add New column for T_ReviewType */
ALTER TABLE T_ReviewType ADD ReviewTypeDescription NVARCHAR2(2000) NULL;
ALTER TABLE T_ReviewType ADD ReviewFlow NVARCHAR2(128) NULL;
ALTER TABLE T_ReviewType ADD IsEnabled INT NULL;
ALTER TABLE T_ReviewType ADD OriginalReviewTypeId INT NULL;
COMMIT;

/* Cannot convert NVARCHAR2 to VARCHAR2 if populated, need to do some manipulation */
ALTER TABLE T_DOCUMENT ADD (EXTREF2 VARCHAR2(4000 CHAR));
UPDATE T_DOCUMENT SET EXTREF2 = substr(EXTREF,1,2000);
COMMIT; 
ALTER TABLE T_DOCUMENT DROP COLUMN EXTREF;  
ALTER TABLE T_DOCUMENT RENAME COLUMN EXTREF2 TO EXTREF;
COMMIT;

UPDATE T_ReviewType SET ReviewFlow = 'Standard', IsEnabled = 1;
COMMIT;

ALTER TABLE T_ReviewType MODIFY ReviewFlow NVARCHAR2(128) NOT NULL;
ALTER TABLE T_ReviewType MODIFY IsEnabled INT NOT NULL;
COMMIT;

/* T_ReviewType table */
INSERT INTO T_ReviewType(ObjectId,ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled)
SELECT 1,  
		0, 
		1, 
		'Standard',
		'The default Review Type used by PleaseReview',
		'Standard',
		1 FROM DUAL;
COMMIT;

INSERT INTO T_ReviewType(ObjectId,ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled)
SELECT 2, 
		0, 
		1, 
		'Multi-phase',
		'A multi-phase review is a sequence of Standard reviews where the Owner has a consolidated view',
		'Multi-phase',
		1 FROM DUAL;
COMMIT;

ALTER TABLE T_Review ADD ReviewFlow NVARCHAR2(128) NULL;
COMMIT;

UPDATE T_Review
SET ReviewFlow =  CASE  
                        WHEN IsCompound = 0 THEN 'Standard' 
                        ELSE 'Multi-phase' 
                    END;
COMMIT;

ALTER TABLE T_Review MODIFY ReviewFlow NVARCHAR2(128) NOT NULL;
COMMIT;

/*
	1. When all reviews are 'Standard', we don't need to do anything as all review types are by default set to 'Standard' review flow.
	2. When all reviews are 'Multi-phase', we just need to update the review flow of the review type to 'Multi-phase'.
*/
UPDATE T_ReviewType SET ReviewFlow = 'Multi-phase'
WHERE ObjectId IN
    (SELECT MP.ReviewTypeId
    FROM 
        (SELECT DISTINCT ReviewTypeId
        FROM T_Review
        WHERE ReviewTypeId > 0
                AND IsCompound = 0) STD FULL
        JOIN 
            (SELECT DISTINCT ReviewTypeId
            FROM T_Review
            WHERE ReviewTypeId > 0
                    AND IsCompound = 1 ) MP
                ON STD.ReviewTypeId = MP.ReviewTypeId
            WHERE STD.ReviewTypeId IS NULL ); 
COMMIT;

/* 3. When reviews are mixture of both, create a copy of the existing review type (set existing as parent to inherit its configs), 
	  change review flow to multiphase, move all multi-phase reviews to the new review type. 
*/
CREATE GLOBAL TEMPORARY TABLE temp_multiphase_reviewtype
( 
  ObjectId			INT				NOT NULL,
	ParentReviewTypeId	INT				NULL,
	WorkgroupId			INT				NOT NULL,
	ReviewTypeName		NVARCHAR2(128)	NOT NULL,
	ReviewTypeDescription 		NVARCHAR2(2000)  NULL,
	ReviewFlow          NVARCHAR2(128)	NOT NULL,
	IsEnabled           INT             NOT NULL
)
ON COMMIT preserve rows;

INSERT INTO temp_multiphase_reviewtype(ObjectId,ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled)
SELECT
  ObjectId,
  ParentReviewTypeId,
  WorkgroupId,
  CONCAT(ReviewTypeName, ' (Multi-phase)'),
  ReviewTypeDescription,
  'Multi-phase',
  1
FROM
   T_ReviewType 
WHERE
   ObjectId IN 
   (
      SELECT
         a.ReviewTypeId 
      FROM
         T_Review a 
         INNER JOIN
            (
               SELECT DISTINCT
                  ReviewTypeId 
               FROM
                  T_Review 
               WHERE
                  ReviewTypeId > 0 
                  AND IsCompound = 1
            ) b 
            ON a.ReviewTypeId = b.ReviewTypeId 
      WHERE
         a.IsCompound = 0
   );
COMMIT;

INSERT INTO
   T_ReviewType(ObjectId, ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled, OriginalReviewTypeId) 
   SELECT 
		(ROW_NUMBER()OVER(ORDER BY a.ObjectId ASC)) + (SELECT MAX(ObjectId) 
      FROM
         T_ReviewType),
         a.ObjectId,
         a.WorkgroupId,
         a.ReviewTypeName,
         a.ReviewTypeDescription,
         a.ReviewFlow,
         a.IsEnabled,
		 a.ObjectId 
      FROM
          temp_multiphase_reviewtype a;
COMMIT;

/* Update T_Sequencer */
UPDATE T_Sequencer SET NextId = (SELECT MAX(ObjectId) FROM T_ReviewType) + 1 WHERE RecordType = 'ReviewType';
COMMIT;

UPDATE T_Review tr 
	SET tr.ReviewTypeId = (SELECT ObjectId FROM T_ReviewType WHERE OriginalReviewTypeId = tr.ReviewTypeId)
WHERE 
	EXISTS (
		SELECT 1 FROM T_Review trev INNER JOIN T_ReviewType trt ON trev.ReviewTypeId = trt.OriginalReviewTypeId 
		 WHERE trev.IsCompound = 1 AND trev.ObjectId = tr.ObjectId);
COMMIT;

/* 4. For all reviews that has no review types, move them to the system default review types of either 'Standard' or 'Multi-phase' */
UPDATE T_Review SET ReviewFlow = 'Standard', ReviewTypeId = 1 WHERE ReviewTypeId = 0 AND IsCompound = 0;
COMMIT;

UPDATE T_Review SET ReviewFlow = 'Multi-phase', ReviewTypeId = 2 WHERE ReviewTypeId = 0 AND IsCompound = 1;
COMMIT;

/* Remove IsCompound flag */
ALTER TABLE T_Review DROP COLUMN IsCompound;
ALTER TABLE T_ReviewType DROP COLUMN OriginalReviewTypeId;
COMMIT;

/* Dropping temporary table without truncating first throws an error in Oracle, so we truncate first. */
TRUNCATE TABLE temp_multiphase_reviewtype;
DROP TABLE temp_multiphase_reviewtype;
COMMIT;

ALTER TABLE T_Comment MODIFY CategoryText NVARCHAR2(2000);
ALTER TABLE T_Comment MODIFY ReviewerCategoryText NVARCHAR2(2000);
COMMIT;

ALTER TABLE T_Config MODIFY StringParam1 NVARCHAR2(2000);
ALTER TABLE T_Config MODIFY StringParam2 NVARCHAR2(2000);
COMMIT;

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