ALTER TABLE T_EditVersion ADD UploadedBySubjectId INT NULL
GO

ALTER TABLE T_EditVersion ADD LastUpdatedBySubjectId INT NULL
GO

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

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

/* Adding comment sequence */
ALTER TABLE T_Comment ADD CommentSequence INT NULL
GO

UPDATE T_Comment SET CommentSequence = 0
GO

ALTER TABLE T_Comment ALTER COLUMN CommentSequence INT NOT NULL
GO

/* End of Adding comment sequence */

/* User Types sequence */

ALTER TABLE T_Subject ADD UserType NVARCHAR(128) NOT NULL Default 'Default'
GO

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


CREATE UNIQUE CLUSTERED INDEX IX_T_UserType on T_UserType (ObjectId)
GO


INSERT INTO T_UserType (ObjectId, Title, DisabledFeatureFlags, HelpURL) VALUES (1, N'Default', N'', N'');
GO
    
INSERT INTO T_UserType (ObjectId, Title, DisabledFeatureFlags, HelpURL) VALUES (2, N'Casual', N'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;', N'');
GO

DELETE FROM T_EditRole from T_EditRole e left join t_Edit f on e.EditId = f.ObjectId where f.objectid is null;
GO
/* 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 CLUSTERED INDEX IX_CommentLike ON T_CommentLike (ObjectId)
GO

CREATE INDEX IX_CommentLike2 ON T_CommentLike (CommentId)
GO

/* End of Like and dislike */

/* All Monitor Workgroup */

ALTER TABLE T_Workgroup ADD IsAllMonitor INT NOT NULL DEFAULT 0
GO

/* End of All Monitor Workgroup */

/* Schema Versioning */

CREATE TABLE T_SchemaInfo (SchemaVersion NVARCHAR(128) NOT NULL)
GO
INSERT INTO T_SchemaInfo (SchemaVersion) VALUES ('5.0.0')
GO

/* 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		NVARCHAR(128)	NOT NULL,
	ExtRef			NVARCHAR(128),
	SubjectRef		NVARCHAR(128)	NOT NULL,
	SubjectRefL		NVARCHAR(128)	NOT NULL,
	Active			INT NOT NULL
);
CREATE UNIQUE CLUSTERED 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) select 'SubjectExtSource',NextId from  t_sequencer WHERE RecordType='Subject';

DROP INDEX IX_Subject2 ON T_Subject;

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

/* 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');
GO
/* End of send welcome emails to syscon users */

/* PleaseAuthor Rebranding to PleaseCompose */
sp_rename 'T_WorkgroupMember.AuthorRole', 'ComposeRole', 'COLUMN';
GO
UPDATE T_PermissionSet SET Product = 'PleaseCompose' WHERE ObjectId = 33 AND PermissionType = 'Workgroup';
UPDATE T_PermissionSet SET Product = 'PleaseCompose' WHERE ObjectId = 34 AND PermissionType = 'Workgroup';
GO
/* End of PleaseAuthor Rebranding to PleaseCompose */

/* PR-3031 */
ALTER TABLE T_Subject ADD MailAddressL NVARCHAR(128) NULL
GO
UPDATE T_Subject SET MailAddressL = LOWER(MailAddress)
GO
ALTER TABLE T_Subject ALTER COLUMN MailAddressL NVARCHAR(128) NOT NULL
GO
CREATE UNIQUE INDEX IX_Subject4 ON T_Subject (MailAddressL)
GO
/* End of PR-3031 */

/* PR-2440 Increase ExtRef length in T_Document table */
ALTER TABLE T_Document ALTER COLUMN ExtRef NVARCHAR(2000) NULL
GO
/* End of PR-2440 */
