/*
 * Tara Database Creation Script for Oracle
 */

/*
 * Create Tables
 */

CREATE TABLE T_Sequencer(
	RecordType	NVARCHAR2(128) NOT NULL,
	NextId		INT NOT NULL
);
CREATE UNIQUE INDEX IX_Sequencer ON T_Sequencer(RecordType);

CREATE TABLE T_Workgroup (
	ObjectId	INT NOT NULL,
	WorkgroupName	NVARCHAR2(128)	NOT NULL,
	DescriptionNote NVARCHAR2(2000),
	LicenseId	INT NOT NULL,
	ExtSource	NVARCHAR2(128),
	ExtRef		NVARCHAR2(128),
	IsTaskgroup INT NOT NULL,
	IsPublic	INT NULL,
	IsAllMonitor	INT DEFAULT 0 NOT NULL,
	ParentWorkgroupId INT NULL
);
CREATE UNIQUE INDEX IX_Workgroup ON T_Workgroup (ObjectId);
CREATE INDEX IX_Workgroup2 ON T_Workgroup (LicenseId,WorkgroupName);

CREATE TABLE T_Subject (
	ObjectId	INT				NOT NULL,
	FullName	NVARCHAR2(128)	NOT NULL,
	MailAddress	NVARCHAR2(128)	NOT NULL,
	MailAddressL	NVARCHAR2(128)	NOT NULL,
	WorkgroupId	INT				NOT NULL,
	SystemRole	NVARCHAR2(128),
	DescriptionNote NVARCHAR2(2000),
	LicenseAgreed	INT			NOT NULL,
	CultureName NVARCHAR2(128),
	LastLoginDate TIMESTAMP(3),
	Active		INT NOT NULL,	
	UserType	NVARCHAR2(128)	NOT NULL,
	HomeWorkgroupId INT NOT NULL,
	CONSTRAINT PK_Subject PRIMARY KEY (ObjectId)
);

CREATE UNIQUE INDEX IX_Subject3 ON T_Subject (MailAddress);
CREATE UNIQUE INDEX IX_Subject4 ON T_Subject (MailAddressL);

CREATE TABLE T_WorkgroupMember (
	ObjectId	INT				NOT NULL,
	SubjectId	INT				NOT NULL,
	WorkgroupId	INT	NOT NULL,
	WorkgroupRole	NVARCHAR2(128),
	ComposeRole	NVARCHAR2(128)
);
CREATE UNIQUE INDEX IX_WorkgroupMember ON T_WorkgroupMember (ObjectId);
CREATE UNIQUE INDEX IX_WorkgroupMember2 ON T_WorkgroupMember (SubjectId, WorkgroupId);
CREATE UNIQUE INDEX IX_WorkgroupMember3 ON T_WorkgroupMember (WorkgroupId, SubjectId);

CREATE TABLE T_NativeUser (
	ObjectId	INT NOT NULL,
	Username	NVARCHAR2(128) NOT NULL,
	Password	NVARCHAR2(128) NOT NULL,
	PasswordChangeDate TIMESTAMP(3) NOT NULL
);
CREATE UNIQUE INDEX IX_NativeUser ON T_NativeUser (ObjectId);
CREATE UNIQUE INDEX IX_NativeUser2 ON T_NativeUser (UserName);

CREATE TABLE T_PreviousPassword(
	ObjectId	INT NOT NULL,
	SubjectId	INT NOT NULL,
	Password	NVARCHAR2(128) NOT NULL,
	UpdateDate	TIMESTAMP(3) NOT NULL
);

CREATE UNIQUE INDEX IX_PreviousPassword ON T_PreviousPassword (ObjectId);
CREATE INDEX IX_PreviousPassword2 ON T_PreviousPassword (SubjectId, UpdateDate);


CREATE TABLE T_Folder (
	ObjectId	INT NOT NULL,
	WorkgroupId	INT NOT NULL,
	FolderId	INT NOT NULL,
	Title		NVARCHAR2(128) NOT NULL
);
CREATE UNIQUE INDEX IX_Folder ON T_Folder (ObjectId);
CREATE UNIQUE INDEX IX_Folder2 ON T_Folder (WorkgroupId, FolderId, Title);

CREATE TABLE T_Review (
	ObjectId	INT NOT NULL,
	OwnerId		INT NOT NULL,
	WorkgroupId	INT NOT NULL,
	FolderId	INT NOT NULL,
	ParentReviewId	INT NOT NULL,
	SubReviewParentId	INT NOT NULL,
	Title		NVARCHAR2(128) NOT NULL,
	Description NCLOB,
	StartDate	 TIMESTAMP(3),
	Duration	INT NOT NULL,
	EndDate		 TIMESTAMP(3),
	CloseDate    TIMESTAMP(3),
	CompleteDate TIMESTAMP(3),
	UpdateDate	 TIMESTAMP(3),
	Status		NCHAR(1) NOT NULL,
	Independent INT NOT NULL,
	ReviewType	NVARCHAR2(128) NOT NULL,
	LastCommentNumber INT NOT NULL,
	Deleted INT NOT NULL,
	ReviewTypeId INT NULL,
	ReviewFlow NVARCHAR2(128) NOT NULL,
	IsPublic INT DEFAULT 0 NOT NULL,
	CreateDate		 TIMESTAMP(3),
	CONSTRAINT PK_Review PRIMARY KEY (ObjectId)
);

CREATE INDEX IX_Review2 ON T_Review (WorkgroupId, FolderId);

CREATE TABLE T_StatusCode (
	ObjectId	INT NOT NULL,
	StatusType	NVARCHAR2(128) NOT NULL,
	Status		NVARCHAR2(128) NOT NULL,
	BasicStatus	NVARCHAR2(128) NOT NULL,
	Description NVARCHAR2(128)
);
CREATE UNIQUE INDEX IX_StatusCode ON T_StatusCode (ObjectId);
CREATE UNIQUE INDEX IX_StatusCode2 ON T_StatusCode (StatusType, Status);

CREATE TABLE T_Document (
	ObjectId		INT NOT NULL,
	ReviewId		INT NOT NULL,
	WorkgroupId		INT NOT NULL,
	FolderId		INT NOT NULL,
	FileName		NVARCHAR2(128) NOT NULL,
	FormatName		NVARCHAR2(128) NOT NULL,
	ContentType		NVARCHAR2(128) NOT NULL,
	TocLevel		INT NOT NULL,
	Title			NVARCHAR2(128) NOT NULL,
	CreateDate		TIMESTAMP(3) NOT NULL,
	ConvertStatus	NVARCHAR2(128) NOT NULL,
	ExtSource		NVARCHAR2(128),
	ExtRef			VARCHAR2(4000),
	ServerVersion	NVARCHAR2(128) NOT NULL,
	ModificationCount INT NOT NULL,
	DocSequence INT DEFAULT 0 NOT NULL,
	DocumentType	NVARCHAR2(8) NOT NULL,
	AlwaysCreateReview	INT NULL,
	Tags			NVARCHAR2(1024) NULL,
	Deleted			INT NULL,
	PublishStatus	NVARCHAR2(128) NULL,
	AdvancedSetting NVARCHAR2(5) NULL,
	PccGuid	NVARCHAR2(36) NULL,
	LastParagraphNumber INT DEFAULT 0 NOT NULL,
	CONSTRAINT PK_Document PRIMARY KEY (ObjectId)
);

CREATE INDEX IX_Document2 ON T_Document (ReviewId);
CREATE INDEX IX_Document3 ON T_Document (WorkgroupId, FolderId);

CREATE TABLE T_ReviewDocument (
	ObjectId		INT NOT NULL,
	ReviewId		INT NOT NULL,
	DocumentId		INT NOT NULL,
	ReferenceOnly	INT NOT NULL,
	IsClone			INT NOT NULL
);
CREATE UNIQUE INDEX IX_ReviewDocument ON T_ReviewDocument (ObjectId);
CREATE INDEX IX_ReviewDocument2 ON T_ReviewDocument (ReviewId);
CREATE INDEX IX_ReviewDocument3 ON T_ReviewDocument (DocumentId);

CREATE TABLE T_Comment (
	ObjectId				INT NOT NULL,
	DocumentId				INT	NOT NULL,
	ReviewId				INT NOT NULL,
	ParagraphId				NVARCHAR2(128) NOT NULL,
	Location				NVARCHAR2(128),
	ParentCommentId			INT NOT NULL,
	ThreadCommentId			INT NOT NULL,
	SubjectId				INT NOT NULL,
	ReconcileSubjectId		INT NOT NULL,
	Status					NVARCHAR2(128) NOT NULL,
	CreateDate				TIMESTAMP(3) NOT NULL,
	UpdateDate				TIMESTAMP(3) NOT NULL,
	CommentType				NCHAR(1) NOT NULL,
	CommentText				NCLOB,
	ParagraphText			NCLOB,
	OriginalText			NCLOB,
	ReconcileText			NCLOB,
	CategoryText			NVARCHAR2(2000),
	ReviewerCategoryText	NVARCHAR2(2000),
	CommentNumber			INT NOT NULL,
	AttachDocumentId		INT,
	AttachFileName			NVARCHAR2(128),
	CommentData				NCLOB,
	CommentSequence			INT NOT NULL,
	PublishToMaster			INT NOT NULL,
	SubReviewCommentId		INT NOT NULL,
	MergedCommentId				INT NOT NULL
);
CREATE UNIQUE INDEX IX_Comment ON T_Comment (ObjectId);
CREATE INDEX IX_Comment2 ON T_Comment (DocumentId, ParagraphId);
CREATE INDEX IX_Comment3 ON T_Comment (ReviewId);
CREATE INDEX IX_Comment4 ON T_Comment (DocumentId, ParentCommentId, ThreadCommentId, ParagraphId, UpdateDate);
CREATE INDEX IX_Comment_ParagraphId ON T_Comment (ParagraphId);

CREATE TABLE T_Participant (
	ObjectId	INT NOT NULL,
	ReviewId	INT NOT NULL,
	SubjectId	INT NOT NULL,
	Role		NVARCHAR2(128) NOT NULL,
	Status		NVARCHAR2(128) NOT NULL,
	Description	NCLOB,
	Summary		NCLOB,
	UpdateDate	TIMESTAMP(3) NOT NULL,
	ReviewTime  INT,
	ReviewZoneRole NVARCHAR2(128),
	DelegatedBy	INT NULL,
	ManagerDelegator INT NULL,
	Subteam		NVARCHAR2(128) NULL,
	Capacity NVARCHAR2(2000) NULL,
	Opinion NVARCHAR2(2000) NULL
);
CREATE UNIQUE INDEX IX_Participant ON T_Participant (ObjectId);
CREATE UNIQUE INDEX IX_Participant2 ON T_Participant (ReviewId, SubjectId);
CREATE UNIQUE INDEX IX_Participant3 ON T_Participant (SubjectId, ReviewId);

CREATE TABLE T_DistList (
	ObjectId		INT NOT NULL,
	OwnerSubjectId	INT,
	OwnerWorkgroupId INT NOT NULL,
	Title		NVARCHAR2(128) NOT NULL
);
CREATE UNIQUE INDEX IX_DistList ON T_DistList (ObjectId);
CREATE UNIQUE INDEX IX_DistList2 ON T_DistList (OwnerSubjectId, OwnerWorkgroupId, Title);
CREATE UNIQUE INDEX IX_DistList3 ON T_DistList (OwnerWorkgroupId, OwnerSubjectId, Title);

CREATE TABLE T_DistEntry (
	ObjectId	INT NOT NULL,
	DistListId	INT NOT NULL,
	SubjectId	INT NOT NULL,
	Role		NVARCHAR2(128),
    ManagerDelegator INT NULL
);
CREATE UNIQUE INDEX IX_DistEntry ON T_DistEntry (ObjectId);
CREATE UNIQUE INDEX IX_DistEntry2 ON T_DistEntry (DistListId, SubjectId);

CREATE TABLE T_PermissionSet (
	ObjectId		INT NOT NULL,
	PermissionType	NVARCHAR2(128) NOT NULL,
	Title			NVARCHAR2(128) NOT NULL,
	PermissionFlags	NCLOB NOT NULL,
	Product NVARCHAR2(128) NULL,
	Internal		INT NULL
);
CREATE UNIQUE INDEX IX_PermissionSet ON T_PermissionSet (ObjectId);
CREATE UNIQUE INDEX IX_PermissionSet2 ON T_PermissionSet (PermissionType, Title);


CREATE TABLE T_QueueEntry (
	ObjectId		INT NOT NULL,
	DueDate			TIMESTAMP(3) NOT NULL,
	StartDate		TIMESTAMP(3),
	EndDate			TIMESTAMP(3),
	Status			NVARCHAR2(128) NOT NULL,
	ServiceId		NVARCHAR2(128),
	RepeatInterval	INT,
	QueueEntryType	NVARCHAR2(128) NOT NULL,
	ReviewId		INT,
	DocumentId		INT,
	SubjectId		INT,
	ParticipantId	INT,
	IntParam1		INT,
	IntParam2		INT,
	StringParam1	NVARCHAR2(128),
	StringParam2	NVARCHAR2(128),
	StringParam3	NVARCHAR2(128),
	StringParam4	NVARCHAR2(128),
	TextParam1		NCLOB,
	OutputText		NCLOB
);
CREATE UNIQUE INDEX IX_QueueEntry ON T_QueueEntry (ObjectId);
CREATE INDEX IX_QueueEntry2 ON T_QueueEntry (Status, DueDate);
CREATE INDEX IX_QueueEntry3 ON T_QueueEntry (ReviewId);

CREATE TABLE T_Settings (
	ObjectId		INT NOT NULL,
	RefObjectType	NVARCHAR2(128) NOT NULL,
	RefObjectId		INT NOT NULL,
	EntryName		NVARCHAR2(128) NOT NULL,
	StringParam1	NVARCHAR2(128),
	StringParam2	NVARCHAR2(128),
	IntParam1		INT,
	IntParam2		INT,
	DateParam1		TIMESTAMP(3),
	DateParam2		TIMESTAMP(3),
	TextParam1		NCLOB
);
CREATE UNIQUE INDEX IX_Settings ON T_Settings (ObjectId);
CREATE INDEX IX_Settings2 ON T_Settings (RefObjectType, RefObjectId, EntryName);

CREATE TABLE T_TrustedClient (
	ObjectId		INT NOT NULL,
	Title			NVARCHAR2(128) NOT NULL,
	AccessKey		NVARCHAR2(128) NOT NULL,
	ServiceRef		NVARCHAR2(128) NOT NULL,
	IPAddress		NVARCHAR2(128),
	Netmask			NVARCHAR2(128)
);
CREATE UNIQUE INDEX IX_TrustedClient ON T_TrustedClient (ObjectId);
CREATE INDEX IX_TrustedClient2 ON T_TrustedClient (AccessKey);

CREATE TABLE T_CommentHistory (
	ObjectId		INT		NOT NULL,
	CommentId		INT		NOT NULL,
	SubjectId		INT		NOT NULL,
	UpdateDate		TIMESTAMP(3)	NOT NULL,
	CommentAction	NVARCHAR2(128)	NOT NULL,
	Status			NVARCHAR2(128)	NOT NULL
);
CREATE UNIQUE INDEX IX_CommentHistory ON T_CommentHistory (ObjectId);
CREATE INDEX IX_CommentHistory2 ON T_CommentHistory (CommentId);

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,
	EditInWordStatus    NVARCHAR2(3) NULL,
	IsEditInWordEnabled INT DEFAULT 0 NOT NULL,
	CreateDate 			TIMESTAMP(3) NULL,
	UpdateDate 			TIMESTAMP(3) NULL,
	DefaultRoleForZone	NVARCHAR2(128)	NULL,
    CONSTRAINT PK_Edit PRIMARY KEY (ObjectId),
	CONSTRAINT FK_T_Edit_T_Review FOREIGN KEY (ReviewId) REFERENCES T_Review(ObjectId),
	CONSTRAINT FK_T_Edit_T_Document FOREIGN KEY (DocumentId) REFERENCES T_Document(ObjectId)	
);
CREATE INDEX IX_T_Edit_DocumentId ON T_Edit (DocumentId);
CREATE INDEX IX_T_Edit_ReviewId ON T_Edit (ReviewId);

CREATE TABLE T_EditVersion (
	ObjectId			INT		NOT NULL,
	EditId				INT		NOT NULL,
	Version				INT		NOT NULL,
	UpdateDate			TIMESTAMP(3) NOT NULL,
	ModificationCount	INT		NOT NULL,
	Status				NVARCHAR2(128)	NOT NULL,
	ReplaceDocumentId	INT		NULL,
	UploadedBySubjectId	INT		NULL,
	LastUpdatedBySubjectId	INT	NULL,
	CONSTRAINT PK_EditVersion PRIMARY KEY (ObjectId),
	CONSTRAINT FK_T_EditVersion_T_Edit FOREIGN KEY (EditId) REFERENCES T_Edit(ObjectId),
	CONSTRAINT UC_T_EditVersion UNIQUE (EditId, Version)
);
CREATE INDEX IX_T_EditVersion_EditId ON T_EditVersion (EditId);

CREATE TABLE T_EditRole(
	ObjectId INT NOT NULL,
	EditId INT NOT NULL,
	SubjectId INT NOT NULL,
	ReviewZoneRole NVARCHAR2(128) NULL,
	IsContributor	INT DEFAULT 0 NOT NULL,
	CheckOut		INT DEFAULT 0 NOT NULL,
	CONSTRAINT PK_EditRole PRIMARY KEY (ObjectId),
	CONSTRAINT FK_T_EditRole_T_Edit FOREIGN KEY (EditId) REFERENCES T_Edit(ObjectId),
	CONSTRAINT FK_T_EditRole_T_Subject FOREIGN KEY (SubjectId) REFERENCES T_Subject(ObjectId),
	CONSTRAINT UC_T_EditRole UNIQUE (EditId, SubjectId)
);
CREATE INDEX IX_T_EditRole_EditId ON T_EditRole (EditId);
CREATE INDEX IX_T_EditRole_SubjectId ON T_EditRole (SubjectId);
CREATE UNIQUE INDEX IX_T_EditRole_CheckOut ON T_EditRole(CASE WHEN CheckOut = 1 THEN EditId END);

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		TIMESTAMP(3)	NOT NULL
);
CREATE UNIQUE INDEX IX_Bookmark ON T_Bookmark (ObjectId);
CREATE INDEX IX_Bookmark2 ON T_Bookmark (DocumentId, ParagraphId);

Create Table T_GenDocAudit (
	ObjectId				INT			NOT NULL,
	SubjectId				INT			NOT NULL,
	WorkgroupId				INT			NOT NULL,
	GenerateDate			TIMESTAMP(3)	NOT NULL,
	GenDocTitle				NVARCHAR2(128) NOT NULL,
	TemplateDocumentId		INT			NOT NULL,
	TemplateDocumentTitle	NVARCHAR2(128)
);
CREATE UNIQUE INDEX IX_GenDocAudit ON T_GenDocAudit (ObjectId);
CREATE INDEX IX_GenDocAudit2 ON T_GenDocAudit (SubjectId);
CREATE INDEX IX_GenDocAudit3 ON T_GenDocAudit (WorkgroupId);

Create Table T_GenDocFragmentAudit (
	ObjectId				INT			NOT NULL,
	GenDocAuditId			INT			NOT NULL,
	FragmentDocumentId		INT			NOT NULL,
	FragmentDocumentTitle	NVARCHAR2(128)
);
CREATE UNIQUE INDEX IX_GenDocFragmentAudit ON T_GenDocFragmentAudit (ObjectId);
CREATE INDEX IX_GenDocFragmentAudit2 ON T_GenDocFragmentAudit (GenDocAuditId, FragmentDocumentId);

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

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


CREATE TABLE T_CommentLike (
	ObjectId		INT		NOT NULL,
	CommentId		INT		NOT NULL,
	SubjectId		INT		NOT NULL,
	Likes			CHAR(1)	NOT NULL,
	CreateDate		TIMESTAMP(3) NOT NULL,
	UpdateDate		TIMESTAMP(3) NOT NULL
);
CREATE UNIQUE INDEX IX_CommentLike ON T_CommentLike (ObjectId);
CREATE INDEX IX_CommentLike2 ON T_CommentLike (CommentId);

CREATE TABLE T_SchemaInfo (SchemaVersion NVARCHAR2(128) NOT NULL);

CREATE TABLE T_ReviewType(
	ObjectId			INT				NOT NULL,
	ParentReviewTypeId	INT				NULL,
	WorkgroupId			INT				NOT NULL,
	ReviewTypeName		NVARCHAR2(128)	NOT NULL,
	ReviewTypeDescription 		NVARCHAR2(2000)  NULL,
	ReviewFlow          NVARCHAR2(128)	NOT NULL,
	IsEnabled           INT             NOT NULL
);
CREATE UNIQUE INDEX IX_ReviewType ON T_ReviewType (ObjectId);
CREATE INDEX IX_ReviewType2 ON T_ReviewType (WorkgroupId);
CREATE INDEX IX_ReviewType3 ON T_ReviewType (ReviewTypeName);

CREATE TABLE T_Config(
	ObjectId		INT NOT NULL,
	RefObjectType	NVARCHAR2(128) NOT NULL,
	RefObjectId		INT NOT NULL,
	EntryName		NVARCHAR2(128) NOT NULL,
	AllowOverride 	INT NOT NULL,
	StringParam1	NVARCHAR2(2000),
	StringParam2	NVARCHAR2(2000),
	IntParam1		INT,
	IntParam2		INT,
	DateParam1		TIMESTAMP(3),
	DateParam2		TIMESTAMP(3),
	TextParam1		NCLOB
);
CREATE UNIQUE INDEX IX_Config ON T_Config (ObjectId);
CREATE UNIQUE INDEX IX_Config2 ON T_Config (RefObjectType, RefObjectId, EntryName);

CREATE TABLE T_LicenseFile(
	ObjectId INT NOT NULL,
	ParentLicenseFileId INT NULL,
	LicenseDetailsXml CLOB NULL,
	UpdateDate TIMESTAMP(3) NOT NULL
);
CREATE UNIQUE INDEX IX_LicenseFile ON T_LicenseFile(ObjectId);

CREATE TABLE T_ReviewMetrics(
	ObjectId		INT				NOT NULL,
	WorkgroupId		INT				NOT NULL,
	Sequence		INT				NOT NULL,
	Name			NVARCHAR2(128)	NOT NULL,
	Description		NVARCHAR2(500)	NULL,
	IsMandatory		INT				NOT NULL,
	Type			INT				NOT NULL,
	Definition		NCLOB			NOT NULL
);
CREATE UNIQUE INDEX IX_ReviewMetrics ON T_ReviewMetrics (ObjectId);

CREATE TABLE T_ReviewMetricsValue(
	ObjectId		INT				NOT NULL,
	ReviewId		INT				NOT NULL,
	ReviewMetricsId	INT				NOT NULL,
	WorkgroupId		INT				NOT NULL,
	Sequence		INT				NOT NULL,
	Name			NVARCHAR2(128)	NOT NULL,
	Description		NVARCHAR2(500)	NULL,
	IsMandatory		INT				NOT NULL,
	Type			INT				NOT NULL,
	Definition		NCLOB			NOT NULL,
	MetricsValue	NCLOB			NULL
);
CREATE UNIQUE INDEX IX_ReviewMetricsValue ON T_ReviewMetricsValue (ObjectId);

CREATE TABLE T_Paragraph(
	ObjectId		INT				NOT NULL,
	DocumentId		INT				NOT NULL,
	ReviewId		INT				NOT NULL,
	ParentParagraphId	NVARCHAR2(256)	NOT NULL,
	SourceParagraphId		NVARCHAR2(256)	NULL,
	ParagraphId		NVARCHAR2(256)	NOT NULL,
	ParagraphNumber 	INT 		NOT NULL,
	ParagraphViewHtml	NCLOB			NOT NULL,
	ParagraphEditHtml	NCLOB			NOT NULL,
	Position		INT				NOT NULL,
	ParagraphType	NCHAR(3)		NULL,
	ModificationCount 	INT 		NOT NULL,
	SubjectId 	INT 		NOT NULL,
	CreateDate		TIMESTAMP(3) NOT NULL,
	UpdateDate		TIMESTAMP(3) NOT NULL
);
CREATE UNIQUE INDEX IX_Paragraph ON T_Paragraph (ObjectId);
CREATE UNIQUE INDEX IX_Paragraph2 ON T_Paragraph (ReviewId, DocumentId, ParagraphId);

/*
 * Insert Standard Data
 */

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (17,'Workgroup','Monitor Only', ';MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (18,'Workgroup','Reviewer-Monitor',
';DOREVIEW;COMMENT;REPLY;CHANGE;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (19,'Workgroup','Author-Monitor',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (20,'Workgroup','Contributor-Monitor',
';DOREVIEW;COMMENT;REPLY;CHANGE;CONTRIBUTE;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (21,'Workgroup','Author-Contributor-Monitor',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (22,'Workgroup','Admin-Monitor',
';WORKGROUPADMIN;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (23,'Workgroup','Admin-Reviewer-Monitor',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (24,'Workgroup','Admin-Author-Monitor',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (25,'Workgroup','Admin-Contributor-Monitor',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;CONTRIBUTE;MONITOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (26,'Workgroup','Admin-Author-Contributor-Monitor',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags, Product)
VALUES ('28', 'ReviewZone', 'Hidden', ';', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags, Product)
VALUES ('29', 'ReviewZone', 'Viewer', ';DOREVIEW;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags, Product)
VALUES ('30', 'ReviewZone', 'Reviewer', ';DOREVIEW;COMMENT;REPLY;CHANGE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags, Product)
VALUES ('31', 'ReviewZone', 'Author', ';DOREVIEW;COMMENT;AUTHOR;REPLY;CHANGE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (32,'Review','Super-Contributor',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CONTRIBUTE;DEFINEEDITZONE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (33,'Workgroup','Document Initiator',
';GENERATEDOCUMENT;', 'PleaseCompose');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (34,'Workgroup','Content Administrator',
';GENERATEDOCUMENT;MANAGECONTENT;MANAGEFOLDER;MANAGETEMPLATE;', 'PleaseCompose');


/* --- BEGIN _ Standard Data for Managerial Delegation implementation --- */

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (35,'Workgroup','Reviewer-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;DELEGATOR;', 'PleaseReview');

-- Author-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (36,'Workgroup','Author-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;DELEGATOR;', 'PleaseReview');

-- Contributor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (37,'Workgroup','Contributor-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;CONTRIBUTE;DELEGATOR;', 'PleaseReview');

-- Author-Contributor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (38,'Workgroup','Author-Contributor-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;DELEGATOR;', 'PleaseReview');

-- Admin-Reviewer-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (39,'Workgroup','Admin-Reviewer-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;DELEGATOR;', 'PleaseReview');

-- Admin-Author-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (40,'Workgroup','Admin-Author-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;DELEGATOR;', 'PleaseReview');

-- Admin-Contributor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (41,'Workgroup','Admin-Contributor-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;CONTRIBUTE;DELEGATOR;', 'PleaseReview');

-- Reviewer-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (42,'Workgroup','Reviewer-Monitor-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;MONITOR;DELEGATOR;', 'PleaseReview');

-- Author-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (43,'Workgroup','Author-Monitor-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;MONITOR;DELEGATOR;', 'PleaseReview');

-- Contributor-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (44,'Workgroup','Contributor-Monitor-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;CONTRIBUTE;MONITOR;DELEGATOR;', 'PleaseReview');

-- Admin-Author-Contributor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (45,'Workgroup','Admin-Author-Contributor-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;DELEGATOR;', 'PleaseReview');

-- Author-Contributor-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (46,'Workgroup','Author-Contributor-Monitor-Delegator',
';DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;DELEGATOR;', 'PleaseReview');

-- Admin-Reviewer-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (47,'Workgroup','Admin-Reviewer-Monitor-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;MONITOR;DELEGATOR;', 'PleaseReview');

-- Admin-Author-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (48,'Workgroup','Admin-Author-Monitor-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;MONITOR;DELEGATOR;', 'PleaseReview');

-- Admin-Contributor-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (49,'Workgroup','Admin-Contributor-Monitor-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;CONTRIBUTE;MONITOR;DELEGATOR;', 'PleaseReview');

-- Admin-Author-Contributor-Monitor-Delegator
INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, 		PermissionFlags, Product)
VALUES (50,'Workgroup','Admin-Author-Contributor-Monitor-Delegator',
';WORKGROUPADMIN;DOREVIEW;COMMENT;REPLY;CHANGE;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;DELEGATOR;', 'PleaseReview');

/* --- END OF Standard Data for Managerial Delegation implementation --- */

/* --- BEGIN _ Standard Data for Simple Delegation implementation --- */

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, PermissionFlags, Product, Internal)
VALUES (51,'Review','Delegated', ';DOREVIEW;', 'PleaseReview', 1);

/* --- END OF Standard Data for Simple Delegation implementation --- */


/* --- BEGIN _ Optional roles - reply only, comment only, and proposed change only --- */

/* NOTE: these roles are commented out by default */

/*

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, PermissionFlags, Product)
VALUES (52,'Review','Reply Only', ';DOREVIEW;REPLY;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,	PermissionType, Title, PermissionFlags, Product)
VALUES (53,'ReviewZone','Reply Only', ';DOREVIEW;REPLY;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,              PermissionType, Title, PermissionFlags, Product)
VALUES (54,'Review','Comment Only', ';DOREVIEW;COMMENT;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,              PermissionType, Title, PermissionFlags, Product)
VALUES (55,'ReviewZone','Comment Only', ';DOREVIEW;COMMENT;', 'PleaseReview');
 
INSERT INTO T_PermissionSet (ObjectId,              PermissionType, Title, PermissionFlags, Product)
VALUES (56,'Review','Propose Change Only', ';DOREVIEW;CHANGE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,              PermissionType, Title, PermissionFlags, Product)
VALUES (57,'ReviewZone','Propose Change Only', ';DOREVIEW;CHANGE;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,              PermissionType, Title, PermissionFlags, Product)
VALUES (58,'Review','Close Only', ';DOREVIEW;AUTHOR;', 'PleaseReview');

INSERT INTO T_PermissionSet (ObjectId,              PermissionType, Title, PermissionFlags, Product)
VALUES (59,'ReviewZone','Close Only', ';DOREVIEW;AUTHOR;', 'PleaseReview');

*/
 
/* --- END OF Optional roles  --- */




INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (1, 'R', 'N', 'N', 'Not Started');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (2, 'R', 'H', 'H', 'On Hold');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (3, 'R', 'S', 'S', 'Starting');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (4, 'R', 'P', 'P', 'In Progress');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (5, 'R', 'C', 'C', 'Closed');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (6, 'R', 'O', 'O', 'Overdue');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (7, 'P', 'N', 'N', 'Not Started');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (8, 'P', 'P', 'P', 'In Progress');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (9, 'P', 'F', 'F', 'Offline');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (10, 'P', 'C', 'C', 'Completed');
INSERT INTO T_StatusCode (ObjectId, StatusType, Status, BasicStatus, Description)
VALUES (11, 'R', 'T', 'T', 'Completed');

/* 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');

/* Password is kcentrix1 */
INSERT INTO T_NativeUser(ObjectId,Username,Password, PasswordChangeDate)
VALUES (1, 'sysadmin', '63ca3518581cb075f1a140fa8f4c32e', SYSTIMESTAMP);
INSERT INTO T_Workgroup(ObjectId, WorkgroupName,LicenseId,IsTaskgroup,IsPublic,IsAllMonitor, ParentWorkgroupId)
VALUES (1,'Root',1,0,0,0, NULL);
INSERT INTO T_Subject(ObjectId,FullName,MailAddress,WorkgroupId,SystemRole,LicenseAgreed,CultureName,Active,LastLoginDate,DescriptionNote,UserType,MailAddressL,HomeWorkgroupId)
VALUES (-1,'Master Review','masterreview@example.com',0,NULL,0,'',1,NULL,NULL,' ','masterreview@example.com', 1);
INSERT INTO T_Subject(ObjectId,FullName,MailAddress,MailAddressL, WorkgroupId,SystemRole, LicenseAgreed,Active,UserType,HomeWorkgroupId)
VALUES (1,'PleaseReview Sysadmin User','nobody@example.com','nobody@example.com',1,'SYSADMIN', 0,1,'Default', 1);
INSERT INTO T_SubjectExtSource(ObjectId,SubjectId,ExtSource,SubjectRef,SubjectRefL,Active)
VALUES (1,1,'PleaseReview:NativeUser','sysadmin','sysadmin',1);
INSERT INTO T_Subject(ObjectId,FullName,MailAddress,WorkgroupId,SystemRole,LicenseAgreed,CultureName,Active,LastLoginDate,DescriptionNote,UserType,MailAddressL,HomeWorkgroupId)
VALUES (-2,'Imported Comment','importedcomment@example.com',0,NULL,0,'',1,NULL,NULL,' ','importedcomment@example.com', 1);


INSERT INTO T_QueueEntry (ObjectId, DueDate, Status, RepeatInterval, QueueEntryType)
VALUES (1, TO_TIMESTAMP('2005-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'Q', 3600, 'Periodic');
INSERT INTO T_QueueEntry (ObjectId, DueDate, Status, RepeatInterval, QueueEntryType)
VALUES (2, TO_TIMESTAMP('2005-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'Q', 86400, 'ExternalSync');
INSERT INTO T_QueueEntry (ObjectId, DueDate, Status, RepeatInterval, QueueEntryType)
VALUES (3, TO_TIMESTAMP('2005-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'Q', 86400, 'Digest');
COMMIT;

/* T_UserType table */
INSERT INTO T_UserType (ObjectId, Title, DisabledFeatureFlags, HelpURL) VALUES (1, 'Default', '', '');
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;', '');

/* T_SchemaInfo table */
INSERT INTO T_SchemaInfo (SchemaVersion) VALUES ('7.2.0');
COMMIT;

/* T_ReviewType table */
INSERT INTO T_ReviewType(ObjectId,ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled)
SELECT 1,  
		0, 
		(SELECT ObjectId FROM T_Workgroup WHERE WorkgroupName = 'Root'), 
		'Standard',
		'The default Review Type used by PleaseReview',
		'Standard',
		1 FROM DUAL;
COMMIT;

INSERT INTO T_ReviewType(ObjectId,ParentReviewTypeId, WorkgroupId, ReviewTypeName, ReviewTypeDescription, ReviewFlow, IsEnabled)
SELECT 2, 
		0, 
		(SELECT ObjectId FROM T_Workgroup WHERE WorkgroupName = 'Root'), 
		'Multi-phase',
		'A multi-phase review is a sequence of Standard reviews where the Owner has a consolidated view',
		'Multi-phase',
		1 FROM DUAL;
COMMIT;

CREATE OR REPLACE PACKAGE pr_storedproc AS
	PROCEDURE SP_GetParent
	(objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	contextId IN OUT NUMBER/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	parentObjectType OUT NVARCHAR2,
	parentId OUT NUMBER);
	PROCEDURE SP_GetConfig
	(objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	entryName IN NVARCHAR2 DEFAULT NULL ,
	ignoreAllowOverride IN NUMBER DEFAULT 0 ,
	hasPreviousValue IN NUMBER DEFAULT 0 ,
	previousConfigId IN NUMBER DEFAULT NULL ,
	previousAllowOverride IN NVARCHAR2 DEFAULT NULL ,
	previousObjectType IN NVARCHAR2 DEFAULT NULL ,
	previousId IN NUMBER DEFAULT NULL ,
	contextId IN OUT NVARCHAR2/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	lineage IN OUT NVARCHAR2/* DEFAULT NULL*/,
	cv_1 IN OUT SYS_REFCURSOR);
END pr_storedproc;
/

CREATE OR REPLACE PACKAGE BODY pr_storedproc AS
/*
 * Create Stored Procedures
 */
 
 /* SP_GetParent */

PROCEDURE SP_GetParent
(
	-- Add the parameters for the stored procedure here
	objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	contextId IN OUT NUMBER/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	parentObjectType OUT NVARCHAR2,
	parentId OUT NUMBER
)
AS
	currentId NUMBER(10,0);
	nextContextId NUMBER(10,0);
	nextContextType NVARCHAR2(128);

BEGIN

	currentId := id ;
	nextContextId := id ;
	nextContextType := objectType ;
	-- get parent id
	IF UPPER(objectType) = 'REVIEW' THEN

	BEGIN
		SELECT ParentReviewId 

			INTO parentId
			FROM T_Review 
			WHERE ObjectId = currentId AND ROWNUM <= 1;
		parentObjectType := 'REVIEW' ;
		IF parentId IS NULL
			OR parentId < 1 THEN

		BEGIN
			SELECT ReviewTypeId 

				INTO parentId
				FROM T_Review 
				WHERE ObjectId = currentId AND ROWNUM <= 1;
			parentObjectType := 'REVIEWTYPE' ;
		END;
		END IF;
		IF parentId IS NULL
			OR parentId < 1 THEN

		BEGIN
			SELECT WorkgroupId 

				INTO parentId
				FROM T_Review 
				WHERE ObjectId = currentId AND ROWNUM <= 1;
			parentObjectType := 'WORKGROUP' ;
		END;
		END IF;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.put_line('SP_GetParent REVIEW No data found ***');
	END;
	ELSE
		IF UPPER(objectType) = 'REVIEWTYPE' THEN

		BEGIN
			SELECT ParentReviewTypeId 

				INTO parentId
				FROM T_ReviewType 
				WHERE ObjectId = currentId AND ROWNUM <= 1;
			parentObjectType := 'REVIEWTYPE' ;
			--preserve context
			IF contextId IS NOT NULL THEN

			BEGIN
				nextContextId := contextId ;
				nextContextType := contextType ;
			END;
			END IF;
			IF parentId IS NULL
				OR parentId < 1 THEN

			BEGIN
				--use context
				IF UPPER(contextType) = 'REVIEW'
					AND contextId IS NOT NULL THEN

				BEGIN
					currentId := contextId ;
					SELECT WorkgroupId 

						INTO parentId
						FROM T_Review 
						WHERE ObjectId = currentId AND ROWNUM <= 1;
					parentObjectType := 'WORKGROUP' ;
				END;
				END IF;
				--preserve context
				IF contextId IS NOT NULL THEN

				BEGIN
					nextContextId := contextId ;
					nextContextType := contextType ;
				END;
				END IF;
			END;
			END IF;
			IF parentId IS NULL
				OR parentId < 1 THEN

			BEGIN
				SELECT WorkgroupId 

					INTO parentId
					FROM T_ReviewType 
					WHERE ObjectId = currentId AND ROWNUM <= 1;
				parentObjectType := 'WORKGROUP' ;
				--reset context
				IF contextId IS NOT NULL THEN

				BEGIN
					nextContextId := id ;
					nextContextType := objectType ;
				END;
				END IF;
			END;
			END IF;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					DBMS_OUTPUT.put_line('SP_GetParent REVIEWTYPE No data found ***');
		END;
		ELSE
			IF UPPER(objectType) = 'WORKGROUP' THEN

			BEGIN
				SELECT ParentWorkgroupId 

					INTO parentId
					FROM T_Workgroup 
					WHERE ObjectId = currentId AND ROWNUM <= 1;
				parentObjectType := 'WORKGROUP' ;
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
						DBMS_OUTPUT.put_line('SP_GetParent WORKGROUP No data found ***');
			END;
			ELSE
				IF UPPER(objectType) = 'SUBJECT' THEN

				BEGIN
					SELECT U.ObjectId 

						INTO parentId
						FROM T_Subject S
							LEFT JOIN T_UserType U   ON UPPER(S.UserType) = UPPER(U.Title)
						WHERE S.ObjectId = currentId AND ROWNUM <= 1;
					parentObjectType := 'USERTYPE' ;
					IF parentId IS NULL
						OR parentId < 1 THEN

					BEGIN
						SELECT HomeWorkgroupId 

							INTO parentId
							FROM T_Subject 
							WHERE ObjectId = currentId AND ROWNUM <= 1;
						parentObjectType := 'WORKGROUP' ;
					END;
					END IF;
					EXCEPTION
						WHEN NO_DATA_FOUND THEN
							DBMS_OUTPUT.put_line('SP_GetParent SUBJECT No data found ***');
				END;
				ELSE
					IF UPPER(objectType) = 'USERTYPE' THEN

					BEGIN
					--use context
						IF UPPER(contextType) = 'SUBJECT'
							AND contextId IS NOT NULL THEN

						BEGIN
							currentId := contextId ;
							SELECT HomeWorkgroupId 

								INTO parentId
								FROM T_Subject 
								WHERE ObjectId = currentId AND ROWNUM <= 1;
							parentObjectType := 'WORKGROUP' ;
						END;
						END IF;
						--preserve context
						IF contextId IS NOT NULL THEN

						BEGIN
							nextContextId := contextId ;
							nextContextType := contextType ;
						END;
						END IF;
						EXCEPTION
							WHEN NO_DATA_FOUND THEN
								DBMS_OUTPUT.put_line('SP_GetParent USERTYPE No data found ***');
					END;
					END IF;
				END IF;
			END IF;
		END IF;
	END IF;
	contextId := nextContextId ;
	contextType := nextContextType ;
END;

 /* SP_GetConfig */

PROCEDURE SP_GetConfig
(
	-- Add the parameters for the stored procedure here
	objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	entryName IN NVARCHAR2 DEFAULT NULL ,
	ignoreAllowOverride IN NUMBER DEFAULT 0 ,
	hasPreviousValue IN NUMBER DEFAULT 0 ,
	previousConfigId IN NUMBER DEFAULT NULL ,
	previousAllowOverride IN NVARCHAR2 DEFAULT NULL ,
	previousObjectType IN NVARCHAR2 DEFAULT NULL ,
	previousId IN NUMBER DEFAULT NULL ,
	contextId IN OUT NVARCHAR2/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	lineage IN OUT NVARCHAR2/* DEFAULT NULL*/,
	cv_1 IN OUT SYS_REFCURSOR
)
AS
	currentConfigId NVARCHAR2(128);
	currentAllowOverride NVARCHAR2(128);
	currentObjectType NVARCHAR2(128);
	currentId NVARCHAR2(50);
	currentHasPreviousValue NUMBER(10,0);
	currentHasData NUMBER(10,0);
	parentObjectType NVARCHAR2(50);
	parentId NVARCHAR2(50);

BEGIN

	currentHasPreviousValue := 0 ;
	currentHasData := 0 ;
	IF lineage IS NULL THEN

	BEGIN
		lineage := ' ' ;
	END;
	END IF;
	lineage := lineage || objectType || '(Id:' || CAST(id AS NVARCHAR2) || ') ' ;
	BEGIN
		SELECT AllowOverride ,
			ObjectId ,
			RefObjectType ,
			RefObjectId 

			INTO currentAllowOverride,
				currentConfigId,
				currentObjectType,
				currentId
			FROM T_Config 
			WHERE UPPER(RefObjectType) = UPPER(objectType)
				AND RefObjectId = id
				AND UPPER(EntryName) = UPPER(SP_GetConfig.entryName) AND ROWNUM <= 1;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.put_line('SELECT T_CONFIG No data found ***');
	END;
	currentHasData := CASE 
		WHEN SQL%ROWCOUNT > 0 THEN 1
		ELSE 0
	END ;
	IF currentHasData = 1 THEN

	-- has current data, set hasPreviousValue to true for next recursion
	BEGIN
		currentHasPreviousValue := 1 ;
	END;
	END IF;
	IF hasPreviousValue = 1
		AND ( currentAllowOverride IS NULL
		OR currentAllowOverride = 1 ) THEN

	-- has previous and current is overridable, disregard current
	BEGIN
		currentConfigId := previousConfigId ;
		currentAllowOverride := previousAllowOverride ;
		currentObjectType := previousObjectType ;
		currentId := previousId ;
		currentHasPreviousValue := hasPreviousValue ;
	END;
	END IF;
	IF currentHasData = 0
		OR currentHasData = 1
		AND ignoreAllowOverride = 0 THEN

	BEGIN
		SP_GetParent(objectType,
			id,
			contextId,
			contextType,
			parentObjectType,
			parentId) ;
	END;
	END IF;
	IF parentId IS NOT NULL THEN

	-- parent exist, get config for parent
	BEGIN
		SP_GetConfig(parentObjectType,
			parentId,
			SP_GetConfig.entryName,
			ignoreAllowOverride,
			currentHasPreviousValue,
			currentConfigId,
			currentAllowOverride,
			currentObjectType,
			currentId,
			contextId,
			contextType,
			lineage,
			cv_1) ;
	END;
	ELSE

	-- no parent, set to previous/current value, exit recursion
	BEGIN
		OPEN  cv_1 FOR
			SELECT StringParam1 ,
				StringParam2 ,
				IntParam1 ,
				IntParam2 ,
				DateParam1 ,
				DateParam2 ,
				TextParam1 ,
				AllowOverride ,
				RefObjectType ,
				RefObjectId ,
				lineage Lineage  
			FROM T_Config 
			WHERE ObjectId = currentConfigId AND ROWNUM <= 1 ;
	END;
	END IF;
END;

END pr_storedproc;
/
COMMIT;
