/*
 * Tara Database Creation Script for MS Sql Server
 */

/*
 * Create Tables
 */

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

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

CREATE TABLE T_Subject (
	ObjectId	INT				NOT NULL,
	FullName	NVARCHAR(128)	NOT NULL,
	MailAddress	NVARCHAR(128)	NOT NULL,
	MailAddressL	NVARCHAR(128)	NOT NULL,
	WorkgroupId	INT				NOT NULL,
	SystemRole	NVARCHAR(128),
	DescriptionNote NVARCHAR(2000) NULL,
	LicenseAgreed	INT			NOT NULL,
	CultureName NVARCHAR(128),
	LastLoginDate DATETIME,
	Active	INT NOT NULL,
	UserType	NVARCHAR(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	NVARCHAR(128),
	ComposeRole	NVARCHAR(128)
);
CREATE UNIQUE CLUSTERED 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		   NVARCHAR(128) NOT NULL,
	Password		   NVARCHAR(128) NOT NULL,
	PasswordChangeDate DATETIME NOT NULL
);
CREATE UNIQUE CLUSTERED 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	NVARCHAR(128) NOT NULL,
	UpdateDate	DATETIME NOT NULL
);

CREATE UNIQUE CLUSTERED 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		NVARCHAR(128) NOT NULL
);
CREATE UNIQUE CLUSTERED 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		NVARCHAR(128) NOT NULL,
	Description NTEXT,
	StartDate	DATETIME,
	Duration	INT NOT NULL,
	EndDate		DATETIME,
	CloseDate   DATETIME,
	CompleteDate DATETIME,
	UpdateDate DATETIME,
	Status		NCHAR(1) NOT NULL,
	Independent INT NOT NULL,
	ReviewType	NVARCHAR(128) NOT NULL,
	LastCommentNumber INT NOT NULL,
	Deleted INT NOT NULL,
	ReviewTypeId INT NULL,
	ReviewFlow NVARCHAR(128) NOT NULL,
	IsPublic BIT NOT NULL DEFAULT 0,
	CreateDate   DATETIME,
	CONSTRAINT PK_Review PRIMARY KEY (ObjectId)
);
CREATE INDEX IX_Review2 ON T_Review (WorkgroupId, FolderId);

CREATE TABLE T_StatusCode (
	ObjectId	INT NOT NULL,
	StatusType	NVARCHAR(128) NOT NULL,
	Status		NVARCHAR(128) NOT NULL,
	BasicStatus	NVARCHAR(128) NOT NULL,
	Description NVARCHAR(128)
);
CREATE UNIQUE CLUSTERED 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		NVARCHAR(128) NOT NULL,
	FormatName		NVARCHAR(128) NOT NULL,
	ContentType		NVARCHAR(128) NOT NULL,
	TocLevel		INT NOT NULL,
	Title			NVARCHAR(128) NOT NULL,
	CreateDate		DATETIME NOT NULL,
	ConvertStatus	NVARCHAR(128) NOT NULL,
	ExtSource		NVARCHAR(128),
	ExtRef			NVARCHAR(MAX),
	ServerVersion	NVARCHAR(128) NOT NULL,
	ModificationCount INT NOT NULL,
	DocSequence INT NOT NULL DEFAULT 0,
	DocumentType	NVARCHAR(8) NOT NULL,
	AlwaysCreateReview	INT NULL,
	Tags			NVARCHAR (1024) NULL,
	Deleted 	INT NULL,
	PublishStatus NVARCHAR(128) NULL,
	AdvancedSetting NVARCHAR(5) NULL,
	PccGuid NVARCHAR(36) NULL,
	LastParagraphNumber INT NOT NULL DEFAULT 0
	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 CLUSTERED 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				NVARCHAR(128) NOT NULL,
	Location				NVARCHAR(128),
	ParentCommentId			INT NOT NULL,
	ThreadCommentId			INT NOT NULL,
	SubjectId				INT NOT NULL,
	ReconcileSubjectId		INT NOT NULL,
	Status					NVARCHAR(128) NOT NULL,
	CreateDate				DATETIME NOT NULL,
	UpdateDate				DATETIME NOT NULL,
	CommentType				NCHAR(1) NOT NULL,
	CommentText				NTEXT,
	ParagraphText			NTEXT,
	OriginalText			NTEXT,
	ReconcileText			NTEXT,
	CategoryText			NVARCHAR(2000),
	ReviewerCategoryText	NVARCHAR(2000),
	CommentNumber			INT NOT NULL,
	AttachDocumentId		INT,
	AttachFileName			NVARCHAR(128),
	CommentData				NTEXT,
	CommentSequence			INT NOT NULL,
	PublishToMaster			INT NOT NULL,
	SubReviewCommentId		INT NOT NULL,
	MergedCommentId			INT NOT NULL
);
CREATE UNIQUE CLUSTERED 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]) INCLUDE ([ParagraphId],[UpdateDate]);
CREATE NONCLUSTERED 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		NVARCHAR(128) NOT NULL,
	Status		NVARCHAR(128) NOT NULL,
	Description	NTEXT,
	Summary		NTEXT,
	UpdateDate	DATETIME NOT NULL,
	ReviewTime  INT,
	ReviewZoneRole NVARCHAR(128),
	DelegatedBy INT NULL,
	ManagerDelegator INT NULL,
	Subteam NVARCHAR(128) NULL,
	Capacity NVARCHAR(2000) NULL,
	Opinion NVARCHAR(2000) NULL
);
CREATE UNIQUE CLUSTERED 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		NVARCHAR(128) NOT NULL
);
CREATE UNIQUE CLUSTERED 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		NVARCHAR(128),
	ManagerDelegator INT NULL
);
CREATE UNIQUE CLUSTERED 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	NVARCHAR(128) NOT NULL,
	Title			NVARCHAR(128) NOT NULL,
	PermissionFlags	NTEXT NOT NULL,
	Product NVARCHAR(128) NULL,
	Internal		INT NULL
);
CREATE UNIQUE CLUSTERED 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			DATETIME NOT NULL,
	StartDate		DATETIME,
	EndDate			DATETIME,
	Status			NVARCHAR(128) NOT NULL,
	ServiceId		NVARCHAR(128),
	RepeatInterval	INT,
	QueueEntryType	NVARCHAR(128) NOT NULL,
	ReviewId		INT,
	DocumentId		INT,
	SubjectId		INT,
	ParticipantId	INT,
	IntParam1		INT,
	IntParam2		INT,
	StringParam1	NVARCHAR(128),
	StringParam2	NVARCHAR(128),
	StringParam3	NVARCHAR(128),
	StringParam4	NVARCHAR(128),
	TextParam1		NTEXT,
	OutputText		NTEXT
);
CREATE UNIQUE CLUSTERED 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	NVARCHAR(128) NOT NULL,
	RefObjectId		INT NOT NULL,
	EntryName		NVARCHAR(128) NOT NULL,
	StringParam1	NVARCHAR(128),
	StringParam2	NVARCHAR(128),
	IntParam1		INT,
	IntParam2		INT,
	DateParam1		DATETIME,
	DateParam2		DATETIME,
	TextParam1		NTEXT
);
CREATE UNIQUE CLUSTERED 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			NVARCHAR(128) NOT NULL,
	AccessKey		NVARCHAR(128) NOT NULL,
	ServiceRef		NVARCHAR(128) NOT NULL,
	IPAddress		NVARCHAR(128),
	Netmask			NVARCHAR(128)
);
CREATE UNIQUE CLUSTERED 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		DATETIME	NOT NULL,
	CommentAction		NVARCHAR(128)	NOT NULL,
	Status			NVARCHAR(128)	NOT NULL
);
CREATE UNIQUE CLUSTERED 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	NVARCHAR(128)	NOT NULL,
	EndParagraphId		NVARCHAR(128)	NOT NULL,
	Title				NVARCHAR(128) NOT NULL,
	Description			NVARCHAR(Max) NULL,
	EditInWordStatus    NVARCHAR(3) NULL,
	IsEditInWordEnabled BIT NOT NULL DEFAULT 0,
	CreateDate 			DATETIME NULL,
	UpdateDate 			DATETIME NULL,
	DefaultRoleForZone	NVARCHAR(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 NONCLUSTERED INDEX IX_T_Edit_DocumentId ON T_Edit (DocumentId);
CREATE NONCLUSTERED 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			DATETIME NOT NULL,
	ModificationCount	INT		NOT NULL,
	Status				NVARCHAR(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 NONCLUSTERED 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	NVARCHAR(128) NULL,
	IsContributor	BIT		NOT NULL  DEFAULT 0,
	CheckOut		BIT		NOT NULL  DEFAULT 0,
	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 NONCLUSTERED INDEX IX_T_EditRole_EditId ON T_EditRole (EditId);
CREATE NONCLUSTERED INDEX IX_T_EditRole_SubjectId ON T_EditRole (SubjectId);
CREATE UNIQUE NONCLUSTERED INDEX IX_T_EditRole_CheckOut ON T_EditRole (EditId, CheckOut) WHERE CHECKOUT = 1;

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	NTEXT,
	PublicView		INT			NOT NULL,
	UpdateDate		DATETIME	NOT NULL
);
CREATE UNIQUE CLUSTERED 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			DATETIME	NOT NULL,
	GenDocTitle				NVARCHAR(128) NOT NULL,
	TemplateDocumentId		INT			NOT NULL,
	TemplateDocumentTitle	NVARCHAR(128)
);
CREATE UNIQUE CLUSTERED 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	NVARCHAR(128)
);
CREATE UNIQUE CLUSTERED 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					NVARCHAR(128)	NOT NULL,
	DisabledFeatureFlags			NVARCHAR(500)	NULL,
	HelpURL					NVARCHAR(200)	NULL
); 
CREATE UNIQUE CLUSTERED INDEX IX_UserType ON T_UserType (ObjectId);


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

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

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

CREATE TABLE T_ReviewType(
	ObjectId			INT				NOT NULL,
	ParentReviewTypeId	INT				NULL,
	WorkgroupId			INT				NOT NULL,
	ReviewTypeName				NVARCHAR(128)	NOT NULL,
	ReviewTypeDescription 		NVARCHAR(2000)  NULL,
	ReviewFlow          NVARCHAR(128)	NOT NULL,
	IsEnabled           INT             NOT NULL
);
CREATE UNIQUE CLUSTERED 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 NVARCHAR(128) NOT NULL,
	RefObjectId INT NOT NULL,
	EntryName NVARCHAR(128) NOT NULL,
	AllowOverride INT NOT NULL,
	StringParam1 NVARCHAR(2000) NULL,
	StringParam2 NVARCHAR(2000) NULL,
	IntParam1 INT NULL,
	IntParam2 INT NULL,
	DateParam1 DATETIME NULL,
	DateParam2 DATETIME NULL,
	TextParam1 NTEXT NULL
);
CREATE UNIQUE CLUSTERED 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 ntext NULL,
	UpdateDate datetime NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX IX_LicenseFile ON T_LicenseFile(ObjectId);

CREATE TABLE T_ReviewMetrics(
	ObjectId		INT				NOT NULL,
	WorkgroupId		INT				NOT NULL,
	Sequence		INT				NOT NULL,
	Name			NVARCHAR(128)	NOT NULL,
	Description		NVARCHAR(500)	NULL,
	IsMandatory		BIT				NOT NULL,
	Type			INT				NOT NULL,
	Definition		NTEXT			NOT NULL
);
CREATE UNIQUE CLUSTERED 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			NVARCHAR(128)	NOT NULL,
	Description		NVARCHAR(500)	NULL,
	IsMandatory		BIT				NOT NULL,
	Type			INT				NOT NULL,
	Definition		NTEXT			NOT NULL,
	MetricsValue	NTEXT			NULL
);
CREATE UNIQUE CLUSTERED INDEX IX_ReviewMetricsValue ON T_ReviewMetricsValue (ObjectId);

CREATE TABLE T_Paragraph(
	ObjectId		INT				NOT NULL,
	DocumentId		INT				NOT NULL,
	ReviewId		INT				NOT NULL,
	ParentParagraphId	NVARCHAR(256)	NOT NULL,
	SourceParagraphId		NVARCHAR(256)	NULL,
	ParagraphId		NVARCHAR(256)	NOT NULL,
	ParagraphNumber 	INT 		NOT NULL,
	ParagraphViewHtml	NTEXT			NOT NULL,
	ParagraphEditHtml	NTEXT			NOT NULL,
	Position		INT				NOT NULL,
	ParagraphType	NCHAR(3)		NULL,
	ModificationCount 	INT 		NOT NULL,
	SubjectId 	INT 		NOT NULL,
	CreateDate		DATETIME NOT NULL,
	UpdateDate		DATETIME NOT NULL,
);
CREATE UNIQUE CLUSTERED INDEX IX_Paragraph ON T_Paragraph (ObjectId);
CREATE UNIQUE NONCLUSTERED 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', GETDATE());
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, '1 Jan 2005 00:00:00', 'Q', 3600, 'Periodic');
INSERT INTO T_QueueEntry (ObjectId, DueDate, Status, RepeatInterval, QueueEntryType)
VALUES (2, '1 Jan 2005 00:00:00', 'Q', 86400, 'ExternalSync');
INSERT INTO T_QueueEntry (ObjectId, DueDate, Status, RepeatInterval, QueueEntryType)
VALUES (3, '1 Jan 2005 00:00:00', 'Q', 86400, 'Digest');

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

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

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;

/*
 * Create Stored Procedure
 */
 
 /* SP_GetParent */
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SP_GetParent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [SP_GetParent]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SP_GetParent
	-- Add the parameters for the stored procedure here
	@objectType nvarchar(50),
	@id int,
	@contextId int = NULL OUTPUT,
	@contextType nvarchar(128) = NULL OUTPUT,
	@parentObjectType nvarchar(50)OUTPUT,
	@parentId int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @currentId AS int
	SET @currentId  = @id
	DECLARE @nextContextId AS int
	SET @nextContextId = @id
	DECLARE @nextContextType AS NVARCHAR(128)
	SET @nextContextType = @objectType
	
	-- get parent id		
	IF @objectType = 'Review'
		BEGIN
			SELECT TOP 1 @parentId = ParentReviewId
			FROM T_Review
			WHERE ObjectId = @currentId
			
			SET @parentObjectType = 'Review'
		
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = ReviewTypeId
					FROM T_Review
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'ReviewType'
				END
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = WorkgroupId
					FROM T_Review
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'
				END
		END
	ELSE IF @objectType = 'ReviewType'
		BEGIN
			SELECT TOP 1 @parentId = ParentReviewTypeId
			FROM T_ReviewType
			WHERE ObjectId = @currentId
			
			SET @parentObjectType = 'ReviewType'
			
			--preserve context
			IF @contextId IS NOT NULL
				BEGIN
					SET @nextContextId = @contextId
					SET @nextContextType = @contextType
				END
		
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					--use context
					IF @contextType = 'Review' AND @contextId IS NOT NULL
						BEGIN
							SET @currentId = @contextId
												
							SELECT TOP 1 @parentId = WorkgroupId
							FROM T_Review
							WHERE ObjectId = @currentId
							
							SET @parentObjectType = 'Workgroup'
						END
					
					--preserve context
					IF @contextId IS NOT NULL
						BEGIN
							SET @nextContextId = @contextId
							SET @nextContextType = @contextType
						END
				END
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = WorkgroupId
					FROM T_ReviewType
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'
					
					--reset context
					IF @contextId IS NOT NULL 
						BEGIN
							SET @nextContextId = @id
							SET @nextContextType = @objectType
						END
				END
		END
	ELSE IF @objectType = 'Workgroup'
		BEGIN
			SELECT TOP 1 @parentId = ParentWorkgroupId
			FROM T_Workgroup
			WHERE ObjectId = @currentId
			
			SET @parentObjectType = 'Workgroup'
		END
	ELSE IF @objectType = 'Subject'
		BEGIN
			SELECT TOP 1 @parentId = U.ObjectId
			FROM T_Subject S
			LEFT JOIN T_UserType U
			ON S.UserType = U.Title
			WHERE S.ObjectId = @currentId
			
			SET @parentObjectType = 'UserType'
		
			IF @parentId IS NULL OR @parentId < 1
				BEGIN
					SELECT TOP 1 @parentId = HomeWorkgroupId
					FROM T_Subject
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'	
				END
		END
	ELSE IF @objectType = 'UserType'
		BEGIN
			--use context
			IF @contextType = 'Subject' AND @contextId IS NOT NULL 
				BEGIN
					SET @currentId = @contextId
								
					SELECT TOP 1 @parentId = HomeWorkgroupId
					FROM T_Subject
					WHERE ObjectId = @currentId
					
					SET @parentObjectType = 'Workgroup'
				END
			
			--preserve context
			IF @contextId IS NOT NULL
				BEGIN
					SET @nextContextId = @contextId
					SET @nextContextType = @contextType
				END
		END
		
	SET @contextId = @nextContextId
	SET @contextType = @nextContextType

END
GO
 
 /* SP_GetConfig */
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SP_GetConfig]') AND type in (N'P', N'PC'))
DROP PROCEDURE [SP_GetConfig]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SP_GetConfig
	-- Add the parameters for the stored procedure here
	@objectType nvarchar(128),
	@id int,
	@entryName nvarchar(128) ,
	@ignoreAllowOverride int = 0,
	@hasPreviousValue int = 0,
	@previousConfigId int = null,
	@previousAllowOverride nvarchar(128) = null,
	@previousObjectType nvarchar(128) = null,
	@previousId int = null,
	@contextId nvarchar(50) = NULL OUTPUT,
	@contextType nvarchar(128) = NULL OUTPUT,
	@lineage nvarchar(MAX) = NULL OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @currentConfigId AS nvarchar(128)
	DECLARE @currentAllowOverride AS nvarchar(128)
	DECLARE @currentObjectType nvarchar(128) 
	DECLARE @currentId nvarchar(50) 
	DECLARE @currentHasPreviousValue int
	SET @currentHasPreviousValue = 0
	DECLARE @currentHasData int
	SET @currentHasData = 0
	DECLARE @parentObjectType nvarchar(50)
	DECLARE @parentId nvarchar(50)
	
	IF @lineage IS NULL
		BEGIN
			SET @lineage = ''
		END
	SET @lineage = @lineage + @objectType + '(Id:' + CONVERT(NVARCHAR(128), @id) + ') '
    SELECT TOP 1 
	   @currentAllowOverride = [AllowOverride]
      ,@currentConfigId = [ObjectId]
      ,@currentObjectType = [RefObjectType]
      ,@currentId = [RefObjectId]
	FROM [T_Config]
	WHERE [RefObjectType] = @objectType AND [RefObjectId] = @id AND [EntryName] = @entryName
	
	SET @currentHasData  = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
	
	IF @currentHasData = 1 -- has current data, set hasPreviousValue to true for next recursion
		BEGIN
			SET @currentHasPreviousValue = 1 
		END
		
	IF @hasPreviousValue = 1 AND (@currentAllowOverride IS NULL OR @currentAllowOverride = 1) -- has previous and current is overridable, disregard current
		BEGIN
			SET @currentConfigId = @previousConfigId
			SET @currentAllowOverride = @previousAllowOverride
			SET @currentObjectType = @previousObjectType
			SET @currentId = @previousId
			SET @currentHasPreviousValue = @hasPreviousValue
		END
	IF @currentHasData = 0 OR @currentHasData = 1 AND @ignoreAllowOverride = 0
		BEGIN
			EXEC SP_GetParent  @objectType, @id, @contextId OUT, @contextType OUT, @parentObjectType OUT, @parentId OUT
		END
		
	IF @parentId IS NOT NULL -- parent exist, get config for parent
		BEGIN
			EXEC SP_GetConfig @parentObjectType, @parentId, @entryName, @ignoreAllowOverride, @currentHasPreviousValue, @currentConfigId, @currentAllowOverride, @currentObjectType, @currentId,	@contextId OUT, @contextType OUT, @lineage OUT
		END
	ELSE -- no parent, set to previous/current value, exit recursion
		BEGIN
			SELECT TOP 1 
			   [StringParam1]
			  ,[StringParam2]
			  ,[IntParam1]
			  ,[IntParam2]
			  ,[DateParam1]
			  ,[DateParam2]
			  ,[TextParam1]
			  ,[AllowOverride]
			  ,[RefObjectType]
			  ,[RefObjectId]
			  ,@lineage AS [Lineage]
			FROM [T_Config]
			WHERE [ObjectId] = @currentConfigId		
		END
END
GO
