﻿/* Add New column for T_ReviewType */
ALTER TABLE T_ReviewType ADD ReviewTypeDescription NVARCHAR(2000) NULL;
ALTER TABLE T_ReviewType ADD ReviewFlow NVARCHAR(128) NULL;
ALTER TABLE T_ReviewType ADD IsEnabled INT NULL;
ALTER TABLE T_ReviewType ADD OriginalReviewTypeId INT NULL;
ALTER TABLE T_Document ALTER COLUMN ExtRef NVARCHAR(MAX);
GO

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

ALTER TABLE T_ReviewType ALTER COLUMN ReviewFlow NVARCHAR(128) NOT NULL;
ALTER TABLE T_ReviewType ALTER COLUMN IsEnabled INT NOT NULL;

GO

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

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;
		
ALTER TABLE T_Review ADD ReviewFlow NVARCHAR(128) NULL;
GO

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

ALTER TABLE T_Review ALTER COLUMN ReviewFlow NVARCHAR(128) NOT NULL;
GO

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

/* 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. 
*/
IF OBJECT_ID('tempdb..#temp_multiphase_reviewtype') IS NOT NULL
DROP TABLE #temp_multiphase_reviewtype;
GO

CREATE TABLE #temp_multiphase_reviewtype
( 
	ObjectId			INT				NOT NULL,
	ParentReviewTypeId	INT				NULL,
	WorkgroupId			INT				NOT NULL,
	ReviewTypeName		NVARCHAR(128)	NOT NULL,
	ReviewTypeDescription 		NVARCHAR(2000)  NULL,
	ReviewFlow          NVARCHAR(128)	NOT NULL,
	IsEnabled           INT             NOT NULL
);

INSERT INTO #temp_multiphase_reviewtype(ObjectId,ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled)
SELECT
   ObjectId,
   ParentReviewTypeId,
   WorkgroupId,
   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
   );
GO

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;
GO

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

UPDATE
   tr 
SET
   tr.ReviewTypeId = trt.ObjectId 
FROM
   T_Review tr 
   INNER JOIN
      T_ReviewType trt 
      ON trt.OriginalReviewTypeId = tr.ReviewTypeId
WHERE
   tr.IsCompound = 1;
GO

/* 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;
GO

UPDATE T_Review SET ReviewFlow = 'Multi-phase', ReviewTypeId = 2 WHERE ReviewTypeId = 0 AND IsCompound = 1;
GO
			
/* Remove IsCompound flag */
ALTER TABLE T_Review DROP COLUMN IsCompound;
ALTER TABLE T_ReviewType DROP COLUMN OriginalReviewTypeId;
GO

/* Remove temp table */
DROP TABLE #temp_multiphase_reviewtype;
GO

ALTER TABLE T_Comment ALTER COLUMN CategoryText NVARCHAR(2000) NULL;
ALTER TABLE T_Comment ALTER COLUMN ReviewerCategoryText NVARCHAR(2000) NULL;

GO

ALTER TABLE T_Config ALTER COLUMN StringParam1 NVARCHAR(2000) NULL;
ALTER TABLE T_Config ALTER COLUMN StringParam2 NVARCHAR(2000) NULL;

GO

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