ALTER TABLE T_EditVersion ADD UploadedBySubjectId INT NULL;
COMMIT;

ALTER TABLE T_EditVersion ADD LastUpdatedBySubjectId INT NULL;
COMMIT;

UPDATE T_Settings SET IntParam1 = IntParam1 * 24 WHERE RefObjectType = 'Review' AND EntryName = 'EmailOptions' AND StringParam1 = 'Reminder';
COMMIT;

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (65, 'SYSTEM', 'REVIEWCOUNTDOWNTIMER', 'PERMIT', 'SYSTEM');
COMMIT;

/* Adding comment sequence */
ALTER TABLE T_Comment ADD (CommentSequence INT NULL);
COMMIT;

UPDATE T_Comment SET CommentSequence = 0;
COMMIT;

ALTER TABLE T_Comment MODIFY (CommentSequence INT NOT NULL);
COMMIT;

/* End of Adding comment sequence */

/* User Types sequence */

ALTER TABLE T_Subject ADD UserType NVARCHAR2(128) Default 'Default' NOT NULL;
COMMIT;

CREATE TABLE T_UserType (
	ObjectId				INT				NOT NULL,
	Title					NVARCHAR2(128)	NOT NULL,
	DisabledFeatureFlags	NVARCHAR2(500)	NULL,
	HelpURL					NVARCHAR2(200)	NULL
); 

CREATE UNIQUE INDEX IX_UserType on T_UserType (ObjectId);
COMMIT;


INSERT INTO T_UserType (ObjectId, Title, DisabledFeatureFlags, HelpURL) VALUES (1, 'Default', '', '');
COMMIT;
    
INSERT INTO T_UserType (ObjectId, Title, DisabledFeatureFlags, HelpURL) VALUES (2, 'Casual', 'GL_USE_BOOKMARK; GL_COMMENT_SELECTION; GL_RECON_REPORT; CP_SET_STATUS; CP_DOWNLOAD_COPY; CP_NOTIFY; CP_SIMPLE_DELEGATE; CP_SUMMARY_COMMENT; RT_NAVIGATION_PANE; RT_NAV_MINIMISED; RT_GENERAL_COMMENT; RT_REVIEW_PROPERTIES; RT_VIEW_ORIGINAL_DOCUMENT; RT_PAGE_LAYOUT; RT_TRACK_CHANGES; RT_USER_SETTING; CMP_NOTIFY; CMP_SIMPLIFY; CMP_TINYMCE_BUTTON; US_CHANGE_OWN_USER_TYPE; CP_WATCH;', '');
COMMIT;

DELETE FROM T_EditRole WHERE NOT EXISTS (SELECT 1 FROM T_Edit e WHERE e.ObjectId = EditId);
COMMIT;

/* End of User Types sequence */

/* Like and dislike */

CREATE TABLE T_CommentLike (
	ObjectId		INT		NOT NULL,
	CommentId		INT		NOT NULL,
	SubjectId		INT		NOT NULL,
	Likes			CHAR(1)	NOT NULL
);

CREATE UNIQUE INDEX IX_CommentLike ON T_CommentLike (ObjectId);
CREATE INDEX IX_CommentLike2 ON T_CommentLike (CommentId);
COMMIT;

/* End of Like and dislike */

/* All Monitor Workgroup */

ALTER TABLE T_Workgroup ADD IsAllMonitor INT Default 0 NOT NULL;
COMMIT;

/* End of All Monitor Workgroup */


/* Schema Versioning */

CREATE TABLE T_SchemaInfo (SchemaVersion NVARCHAR2(128) NOT NULL);
INSERT INTO T_SchemaInfo (SchemaVersion) VALUES ('5.0.0');
COMMIT;

/* End of Schema Versioning */

/* SubjectExtSource support*/
/* now create the required extsource rows from the current subject table*/
CREATE TABLE T_SubjectExtSource  (
	ObjectId		INT	NOT NULL,
	SubjectId		INT	NOT NULL,
	ExtSource		NVARCHAR2(128)	NOT NULL,
	ExtRef			NVARCHAR2(128),
	SubjectRef		NVARCHAR2(128)	NOT NULL,
	SubjectRefL		NVARCHAR2(128)	NOT NULL,
	Active			INT NOT NULL
);
CREATE UNIQUE INDEX IX_SubjectExtSource ON T_SubjectExtSource (ObjectId);
CREATE UNIQUE INDEX IX_SubjectExtSource2 ON T_SubjectExtSource (ExtSource, SubjectRef);


INSERT INTO T_SubjectExtSource (ObjectId,SubjectId,ExtSource,ExtRef,SubjectRef,SubjectRefl,Active)
SELECT ObjectId,ObjectId AS SubjectId, ExtSource, ExtRef, SubjectRef, SUbjectRefl,Active FROM t_subject;

INSERT INTO t_sequencer (RecordType,NextId) VALUES ('SubjectExtSource',(SELECT NextId FROM t_sequencer WHERE RecordType='Subject'));

DROP INDEX IX_Subject2;

ALTER TABLE t_subject DROP COLUMN ExtSource;
COMMIT;
ALTER TABLE t_subject DROP COLUMN ExtRef;
COMMIT;
ALTER TABLE t_subject DROP COLUMN SubjectRef;
COMMIT;
ALTER TABLE t_subject DROP COLUMN SubjectRefl;
COMMIT;

/* End of SubjectExtSource support*/
/* Send welcome emails to syscon users */
INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (66, 'SYSTEM', 'NEVERSENDWELCOMEEMAILFORSYSCON', 'PERMIT', 'SYSTEM');
/* End of send welcome emails to syscon users */

/* PleaseAuthor Rebranding to PleaseCompose */
ALTER TABLE T_WorkgroupMember RENAME COLUMN AuthorRole TO ComposeRole;
COMMIT;
UPDATE T_PermissionSet SET Product = 'PleaseCompose' WHERE ObjectId = 33 AND PermissionType = 'Workgroup';
COMMIT;
UPDATE T_PermissionSet SET Product = 'PleaseCompose' WHERE ObjectId = 34 AND PermissionType = 'Workgroup';
COMMIT;
/* End of PleaseAuthor Rebranding to PleaseCompose */

/* PR-3031 */
ALTER TABLE T_Subject ADD MailAddressL NVARCHAR2(128) NULL;
COMMIT;
UPDATE T_Subject SET MailAddressL = lower(MailAddress);
COMMIT;
ALTER TABLE T_Subject MODIFY MailAddressL NVARCHAR2(128) NOT NULL;
COMMIT;
CREATE UNIQUE INDEX IX_Subject4 ON T_Subject (MailAddressL);
COMMIT;
/* End of PR-3031 */

/* PR-2440 Increase ExtRef length in T_Document table */
ALTER TABLE T_Document MODIFY ExtRef NVARCHAR2(2000);
COMMIT;
/* End of PR-2440 */