CREATE TABLE T_Edit (
	ObjectId			INT		NOT NULL,
	ReviewId			INT		NOT NULL,
	DocumentId			INT		NOT NULL,
	StartParagraphId	NVARCHAR(128)	NOT NULL,
	EndParagraphId		NVARCHAR(128)	NOT NULL,
	Title				NVARCHAR(128) NOT NULL,
	Description			TEXT,
	SubjectId			INT		NOT NULL,
	Status				NVARCHAR(128)	NOT NULL
);
CREATE UNIQUE INDEX IX_Edit ON T_Edit (ObjectId);
CREATE INDEX IX_Edit2 ON T_Edit (DocumentId);

CREATE TABLE T_EditVersion (
	ObjectId			INT		NOT NULL,
	EditId				INT		NOT NULL,
	Version				INT		NOT NULL,
	UpdateDate			DATETIME NOT NULL,
	ModificationCount	INT		NOT NULL,
	Status				NVARCHAR(128)	NOT NULL
);
CREATE UNIQUE INDEX IX_EditVersion ON T_EditVersion (ObjectId);
CREATE INDEX IX_EditVersion2 ON T_EditVersion (EditId);

CREATE TABLE T_Bookmark (
	ObjectId		INT		NOT NULL,
	DocumentId		INT		NOT NULL,
	ReviewId		INT		NOT NULL,
	ParagraphId		NVARCHAR(128) NOT NULL,
	SubjectId		INT		NOT NULL,
	Title			NVARCHAR(128) NOT NULL,
	BookmarkText	TEXT,
	PublicView		INT		NOT NULL,
	UpdateDate		DATETIME	NOT NULL
);
COMMIT;

CREATE UNIQUE INDEX IX_Bookmark ON T_Bookmark (ObjectId);
COMMIT;

CREATE INDEX IX_Bookmark2 ON T_Bookmark (DocumentId, ParagraphId);
COMMIT;

ALTER TABLE T_Document ADD ModificationCount INT;
COMMIT;

update T_document set ModificationCount  = 0;
COMMIT;

ALTER TABLE T_Comment ADD ReconcileSubjectId INT;
COMMIT;

ALTER TABLE T_Comment ADD AttachDocumentId INT;
COMMIT;

ALTER TABLE T_Comment ADD AttachFileName NVARCHAR(128);
COMMIT;

ALTER TABLE T_Comment ADD CommentData TEXT;
COMMIT;

Update T_Comment c, T_Review r set c.ReconcileSubjectId = r.OwnerId where r.ObjectId=c.ReviewId and c.Status != 'O';
COMMIT;

UPDATE T_WorkgroupMember SET WorkgroupRole='Admin-Author' WHERE WorkgroupRole='Admin';
COMMIT;

Update T_Participant SET Role='Owner', Status='O' WHERE Role='Author';
COMMIT;

DELETE FROM T_PermissionSet;
COMMIT;

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (1,'Review','Viewer',
';DOREVIEW;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (2,'Review','Reviewer',
';DOREVIEW;COMMENT;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (3,'Review','Author',
';DOREVIEW;COMMENT;AUTHOR;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (4,'Review','Owner',
';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (5,'Review','Contributor',
';DOREVIEW;COMMENT;CONTRIBUTE;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (6,'Review','Author-Contributor',
';DOREVIEW;COMMENT;AUTHOR;CONTRIBUTE;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (7,'Review','Owner-Contributor',
';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;');


INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (8,'Workgroup','Reviewer',
';DOREVIEW;COMMENT;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (9,'Workgroup','Author',
';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (10,'Workgroup','Contributor',
';DOREVIEW;COMMENT;CONTRIBUTE;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (11,'Workgroup','Author-Contributor',
';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (12,'Workgroup','Admin Only',
';WORKGROUPADMIN;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (13,'Workgroup','Admin-Reviewer',
';WORKGROUPADMIN;DOREVIEW;COMMENT;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (14,'Workgroup','Admin-Author',
';WORKGROUPADMIN;DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (15,'Workgroup','Admin-Contributor',
';WORKGROUPADMIN;DOREVIEW;COMMENT;CONTRIBUTE;');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags )
VALUES (16,'Workgroup','Admin-Author-Contributor',
';WORKGROUPADMIN;DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;');

COMMIT;


INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (40,'SYSTEM','ALLOWBOOKMARK','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (41,'SYSTEM','OWNERDEFAULTROLE','Owner','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (42,'SYSTEM','PARTICIPANTDEFAULTROLE','Reviewer','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (43,'SYSTEM','PARAGRAPHLEVELNOTIFICATION','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (44,'SYSTEM','AUTOREFRESHDEFAULT','DENY','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (45,'SYSTEM','PDFIMAGEREVIEW','DENY','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (46,'SYSTEM','DEFAULTPDFIMAGE','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (47,'SYSTEM','CANCELCHECKOUTONTRANSFER','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (48,'SYSTEM','NOTRANSFERIFCHECKOUT','DENY','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (49,'SYSTEM','COMMENTATTACHMENT','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (50,'SYSTEM','REMOVEEXTRAAUTONUMLGLTEXT','DENY','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (51,'SYSTEM','DEFAULTREVIEWDURATION','1','SYSTEM');



COMMIT;

/*Edit Status Code*/
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (28, 'E','E','E','Editing');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (29, 'E','F','F','Publish Final');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (30, 'E','G','G','Generating Publish');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (31, 'E','L','L','Generating Final');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (32, 'E','P','P','Publish');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (33, 'E','R','R','Revised Edit');

/*Edit Status History Code*/
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (34, 'H','F','F','Generating Fail');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (35, 'H','G','G','Generating');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (36, 'H','P','P','Publish');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (37, 'H','PF','PF','Complete');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (38, 'H','R','R','Revise');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (39, 'H','S','S','Save');

INSERT INTO T_StatusCode(ObjectId,StatusType,Status,BasicStatus,Description)
VALUES (40,'P','O','O','Review Owner');

COMMIT;

update T_Settings set StringParam1='On' where EntryName='QuickAccept' and IntParam1=1;
update T_Settings set StringParam1='Off' where EntryName='QuickAccept' and IntParam1=0;
COMMIT;

ALTER TABLE T_QueueEntry ADD ServiceId NVARCHAR(128);
COMMIT;
