﻿/* Add new columns for T_CommentLike */
ALTER TABLE T_CommentLike ADD CreateDate TIMESTAMP(3) NULL;
ALTER TABLE T_CommentLike ADD UpdateDate TIMESTAMP(3) NULL;
COMMIT;

UPDATE T_CommentLike SET CreateDate = TO_DATE(SYSDATE, 'DD-MM-YYYY HH:MI:SS');
COMMIT;

UPDATE T_CommentLike SET UpdateDate = TO_DATE(SYSDATE, 'DD-MM-YYYY HH:MI:SS');
COMMIT;

ALTER TABLE T_CommentLike MODIFY CreateDate TIMESTAMP(3) NOT NULL;
ALTER TABLE T_CommentLike MODIFY UpdateDate TIMESTAMP(3) NOT NULL;
COMMIT;

/* Add new columns for npzr */
ALTER TABLE T_Edit ADD DefaultRoleForZone NVARCHAR2(128) NULL;
COMMIT;

/* Change ReviewZoneRole to Viewer for all participants except Owner when ReviewZone is ReadOnly */
UPDATE T_EditRole SET T_EditRole.ReviewZoneRole = 'Viewer' 
WHERE T_EditRole.EditId IN (SELECT E.objectId FROM T_Edit E WHERE E.ZoneType = 'R' AND E.ReadOnly = 1 AND T_EditRole.SubjectId != E.SubjectId);
COMMIT;

UPDATE T_Edit SET DefaultRoleForZone = 'Viewer' WHERE ZoneType = 'R' AND ReadOnly = 1;
COMMIT;

UPDATE T_Edit SET DefaultRoleForZone = 'Inherit' WHERE ZoneType = 'R' AND ReadOnly = 0;
COMMIT;

ALTER TABLE T_Edit DROP COLUMN ReadOnly;
COMMIT;

/* Change ReviewZoneReadOnly settings to ReviewZoneDefaultRole with default Viewer ReviewZone role as value */
UPDATE T_Settings SET EntryName = 'ReviewZoneDefaultRole', StringParam1 = 'Viewer', IntParam1 = 0
WHERE EntryName = 'ReviewZoneReadOnly' AND IntParam1 = 1;
COMMIT;

DELETE FROM T_Settings WHERE EntryName = 'ReviewZoneReadOnly';
COMMIT;

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

/* PR-4893 - new index for performance */
CREATE UNIQUE INDEX IX_Participant3 ON T_Participant (SubjectId, ReviewId);
COMMIT;