CREATE TABLE T_Edit (
	ObjectId			INT		NOT NULL,
	ReviewId			INT		NOT NULL,
	DocumentId			INT		NOT NULL,
	StartParagraphId	NVARCHAR2(128)		NOT NULL,
	EndParagraphId		NVARCHAR2(128)		NOT NULL,
	Title				NVARCHAR2(128) NOT NULL,
	Description			NCLOB,
	SubjectId			INT		NOT NULL,
	Status				NVARCHAR2(128)	NOT NULL
);
COMMIT;

CREATE UNIQUE INDEX IX_Edit ON T_Edit (ObjectId);
COMMIT;

CREATE INDEX IX_Edit2 ON T_Edit (DocumentId);
COMMIT;

CREATE TABLE T_EditVersion (
	ObjectId			INT		NOT NULL,
	EditId				INT		NOT NULL,
	Version				INT		NOT NULL,
	UpdateDate			DATE		NOT NULL,
	ModificationCount	INT		NOT NULL,
	Status				NVARCHAR2(128)	NOT NULL
);
COMMIT;

CREATE UNIQUE INDEX IX_EditVersion ON T_EditVersion (ObjectId);
COMMIT;

CREATE INDEX IX_EditVersion2 ON T_EditVersion (EditId);
COMMIT;

CREATE TABLE T_Bookmark (
	ObjectId		INT		NOT NULL,
	DocumentId		INT		NOT NULL,
	ReviewId		INT		NOT NULL,
	ParagraphId		NVARCHAR2(128) NOT NULL,
	SubjectId		INT		NOT NULL,
	Title			NVARCHAR2(128) NOT NULL,
	BookmarkText	NCLOB,
	PublicView		INT		NOT NULL,
	UpdateDate		DATE	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 NVARCHAR2(128);
COMMIT;

ALTER TABLE T_Comment ADD CommentData NCLOB;
COMMIT;

Update T_Comment c set ReconcileSubjectId = (select OwnerId from T_Review r 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;');
COMMIT;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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');
COMMIT;

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

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

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

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

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

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

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

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

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

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

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');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (29, 'E','F','F','Publish Final');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (30, 'E','G','G','Generating Publish');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (31, 'E','L','L','Generating Final');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (32, 'E','P','P','Publish');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (33, 'E','R','R','Revised Edit');
COMMIT;

/*Edit Status History Code*/
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (34, 'H','F','F','Generating Fail');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (35, 'H','G','G','Generating');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (36, 'H','P','P','Publish');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (37, 'H','PF','PF','Complete');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (38, 'H','R','R','Revise');
COMMIT;
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (39, 'H','S','S','Save');
COMMIT;
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;
COMMIT;
update T_Settings set StringParam1='Off' where EntryName='QuickAccept' and IntParam1=0;
COMMIT;

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