
USE [VINCI]

--create schema hib

CREATE TABLE [hib].[comment] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64) not null,
[description] varchar(512),
[requestid] int not null,
[previousid] int,
[text] varchar(max)
)

CREATE TABLE [hib].[event] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64) not null,
[description] varchar(512),
[requestid] int,
[initial] bit not null default 0,
[eventtypeid] int
)

CREATE TABLE [hib].[eventtype] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256)
)

CREATE TABLE [hib].[eventgroup] (
[eventid] int not null,
[groupid] int not null
)

CREATE TABLE [hib].[medianwaittime] (
[metric] varchar(64) not null,
[requestormedian] float not null,
[reviewermedian] float not null
)

CREATE TABLE [hib].[datasource] (
[id] int primary key identity,
[name] varchar(128) not null,
[description] varchar(256),
[domainname] varchar(64) not null,
[infosite] varchar(2000),
[evaluator] varchar(255) not null
)

CREATE TABLE [hib].[location] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256)
)

CREATE TABLE [hib].[person] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256),
[fullname] varchar(256) not null,
[locationid] int not null
)

CREATE TABLE [hib].[task] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64) not null,
[description] varchar(256),
[completedOn] datetime,
[requestid] int not null,
[reviewid] int,
[tasktypeid] int not null default 1,
[personownerid] int,
[groupownerid] int,
[workflowid] int
)
    
CREATE TABLE [hib].[tasktype] (
[id] int primary key identity,
[name] varchar(64)
)
  
CREATE TABLE [hib].[group] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256),
[review] bit not null default 0
)

CREATE TABLE [hib].[activity] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(1024) not null,
[description] varchar(256),
[startDate] date,
[endDate] date,
[officialName] varchar(1024),
[type] varchar(31) not null,
[reviewerid] int
)

CREATE TABLE VINCI.[hib].[activitytype] (
[id] int primary key identity,
[name] varchar(255) not null
)

CREATE TABLE [hib].[document] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(256) not null,
[description] varchar(256),
[head] int,
[requestid] int not null,
[documenttemplateid] int,
[contentid] int,
[typesuffix] varchar(32),
[sortorder] int not null default 0,
[versionnumber] int not null default 1
)

CREATE TABLE [hib].[content] (
[id] int primary key identity,
[version] int,
[repositoryid] int not null,
[token] bigint,
[typesuffix] varchar(32),
)

CREATE TABLE [hib].[label] (
[id] int primary key identity,
[version] int,
[documentid] int,
[value] varchar(256),
)

CREATE TABLE [hib].[repository] (
[id] int primary key identity,
[location] varchar(4096),
[name] varchar(256) not null,
[encrypted] bit not null default 0,
[domain] varchar(256),
[password] varchar(256)
)

CREATE TABLE [hib].[attribute] (
[id] int primary key identity,
[version] int,
[documentid] int,
[key] varchar(256),
[value] varchar(2048),
)

CREATE TABLE [hib].[documenttemplate] (
[id] int primary key identity,
[name] varchar(128) not null,
[description] varchar(256),
[location] bit not null default 0,
[participant] bit not null default 0,
[administrator] bit not null default 0,
[principalinvestigator] bit not null default 0,
[sortorder] int not null default 0,
[activitytype] varchar(8)
)

CREATE TABLE [hib].[documentreviewstatus] (
[id] int primary key identity,
[documentid] int not null,
[reviewerid] int not null,
[status] varchar(63) not null
)

CREATE TABLE [hib].[documentreviewnote] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64) not null,
[description] varchar(512),
[documentid] int not null,
[roleid] int not null,
[text] varchar(max)
)

CREATE TABLE [hib].[request] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[irbnumber] varchar(256),
[irbexpiration] date,
[headid] int,
[previousid] int,
[currentflag] bit not null default 1,
[name] varchar(1024) not null,
[description] varchar(256),
[activityid] int not null,
[amendment] bit not null default 0,
[datasetstartdate] date,
[datasetenddate] date,
[hipaaconsent] bit not null default 0,
[hipaawaiver] bit not null default 0,
[type] varchar(255) not null,
[state] int not null default 0,
[requesttype] int not null default 1,
[requestorid] int not null,
[trackingnumber] varchar(32),
[submittedon] date,
[primarysiteid] int,
[datasourcelocation] varchar(64),
[datamart] bit not null default 0,
[localserver] bit not null default 0,
[localserverlocation] varchar(128),
[localserveraddress] varchar(128),
[localserverbuilding] varchar(128),
[localserverroomnumber] varchar(128),
[transferredexternal] bit not null default 0,
[externaldestination] varchar(128),
[externalstreet] varchar(128),
[externalcity] varchar(128),
[externalstate] varchar(20),
[externalzipcode] varchar(20),
[realssnflag] bit not null default 0,
[scrambledssnflag] bit not null default 0,
[phidataflag] bit not null default 0,
[reviewid] int,
[workflowid] int,	--workflowtypeid
[workflowstate] int,
[workflowmask] bigint,
[programoffice] varchar(1024),
[justification] varchar(max)
)

CREATE TABLE [hib].[requestworkflow] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64),
[description] varchar(256),
[workflowtemplateid] int not null,
[workflowstate] int,
[workflowmask] bigint,
[requeststate] int not null default 0,
[requestid] int not null
)

CREATE TABLE [hib].[workflowtype] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256)
)

CREATE TABLE [hib].[workflowtemplate] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256),
[groupid] int not null,
[workflowtypeid] int not null
)

CREATE TABLE [hib].[datasourceworkflowtemplate] (
[datasourceid] int not null,
[workflowtemplateid] int not null
)

CREATE TABLE [hib].[documenttemplateworkflowtemplate] (
[documenttemplateid] int not null,
[workflowtemplateid] int not null
)

CREATE TABLE [hib].[amendmentdocumenttemplateworkflowtemplate] (
[documenttemplateid] int not null,
[workflowtemplateid] int not null
)

CREATE TABLE [hib].[documenttemplatereviewgroup] (
[documenttemplateid] int not null,
[groupid] int not null
)

CREATE TABLE [hib].[showdocumenttemplateworkflowtemplate] (
[documenttemplateid] int not null,
[workflowtemplateid] int not null
)

CREATE TABLE [hib].[hidedatasourceworkflowtemplate] (
[datasourceid] int not null,
[workflowtemplateid] int not null
)

CREATE TABLE [hib].[disableddatasource] (
[requesttypeid] int not null,
[datasourceid] int not null
)

CREATE TABLE [hib].[datasourcedisplayheader] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256),
[headid] int,
[sortorder] int not null default 0,
[requesttypeid] int not null
)

CREATE TABLE [hib].[datasourcedisplay] (
[requesttypeid] int not null,
[datasourceheaderid] int not null,
[datasourceid] int not null,
[sortorder] int not null default 0
)

CREATE TABLE [hib].[contentmap] (
[origcontentid] int not null,
[mappedcontentid] int not null
)

CREATE TABLE [hib].[requeststatus] (
[id] int primary key identity,
[name] varchar(255) not null
)

CREATE TABLE [hib].[requesttype] (
[id] int primary key identity,
[name] varchar(255) not null
)

CREATE TABLE [hib].[narrative] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64),
[description] varchar(256),
[requestid] int not null,
[text] varchar(max),
)

CREATE TABLE [hib].[onlinedata] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64) not null,
[description] varchar(256),
[formid] int not null,
[requestid] int not null,
[approvalid] int not null,
[approvaltemplateid] int not null,
[templateid] int not null
)

CREATE TABLE [hib].[onlinedatatemplate] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64) not null,
[description] varchar(256),
[approvalid] int not null,
[requestid] int not null,
[approvaltemplateid] int not null
)

CREATE TABLE [hib].[review] (
[id] int primary key identity,
[version] int,
[createdby] varchar(32) not null,
[createdon] datetime not null,
[updatedby] varchar(32),
[updatedon] datetime,
[name] varchar(64),
[description] varchar(256),
[requestid] int not null,
[groupid] int not null,
[approved] datetime,
[rejected] datetime,
[changerequested] datetime,
[workflowid] int
)

CREATE TABLE [hib].[reviewtemplate] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256),
[groupid] int not null,
[active] bit not null default 1	--active for the NDS workflow?
)

CREATE TABLE [hib].[reviewcomment] (
[reviewid] int not null,
[commentid] int not null
)

CREATE TABLE [hib].[requestcomment] (
[requestid] int not null,
[commentid] int not null
)

CREATE TABLE [hib].[participant] (
[id] int primary key identity,
[personid] int not null,
[requestid] int not null,
[locationid] int not null,
[notification] bit not null,
[dataaccess] bit not null,
[principalinvestigator] bit not null
)

CREATE TABLE [hib].[role] (
[id] int primary key identity,
[name] varchar(64) not null,
[description] varchar(256)
)

CREATE TABLE [hib].[personrole] (
[personid] int not null,
[roleid] int not null
)

CREATE TABLE [hib].[grouprole] (
[groupid] int not null,
[roleid] int not null
)

CREATE TABLE [hib].[persongroup] (
[personid] int not null,
[groupid] int not null,
[notification] bit not null default 1
)

CREATE TABLE [hib].[groupmailbox] (
[groupid] int not null,
[email] varchar(64) not null,
[notification] bit not null default 0
)

CREATE TABLE [hib].[researchstudyrequest] (
[researchstudyid] int not null,
[requestid] int not null
)

CREATE TABLE [hib].[researchstudydatasource] (
[researchstudyid] int not null,
[datasourceid] int not null
)

CREATE TABLE [hib].[requestlocation] (
[requestid] int not null,
[locationid] int not null
)

CREATE TABLE [hib].[requestonlinedata] (
[requestid] int not null,
[onlinedataid] int not null
)

CREATE TABLE [hib].[requestlocationdocument] (
[id] int primary key identity,
[requestid] int not null,
[documentid] int not null,
[locationid] int not null
)

CREATE TABLE [hib].[requestadminlocationdocument] (
[id] int primary key identity,
[requestid] int not null,
[documentid] int not null,
[locationid] int not null,
[groupid] int not null
)

CREATE TABLE [hib].[requestparticipantdocument] (
[id] int primary key identity,
[requestid] int not null,
[documentid] int not null,
[participantid] int not null
)

CREATE TABLE [hib].[requestadminparticipantdocument] (
[id] int primary key identity,
[requestid] int not null,
[documentid] int not null,
[participantid] int not null,
[groupid] int not null
)

CREATE TABLE [hib].[datasourcedocumenttemplate] (
[datasourceid] int not null,
[documenttemplateid] int not null
)


--ALTER TABLE [hib].[researchstudyrequest]
--	ADD CONSTRAINT [FK_researchstudyrequest_activity] FOREIGN KEY ([activityid])
--	REFERENCES [hib].[activity] ([id])
ALTER TABLE [hib].[request]
	ADD CONSTRAINT [FK_request_activity] FOREIGN KEY ([activityid])
	REFERENCES [hib].[activity] ([id])


ALTER TABLE [hib].[researchstudyrequest]
	ADD CONSTRAINT [FK_researchstudyrequest_request] FOREIGN KEY ([requestid])
	REFERENCES [hib].[request] ([id])

ALTER TABLE [hib].[person] 
	ADD CONSTRAINT [FK_person_location] FOREIGN KEY ([locationid])
	REFERENCES [hib].[location] ([id])

ALTER TABLE [hib].[persongroup]
	ADD CONSTRAINT [FK_persongroup_person] FOREIGN KEY ([personid])
	REFERENCES [hib].[person] ([id])

ALTER TABLE [hib].[persongroup]
	ADD CONSTRAINT [FK_persongroup_group] FOREIGN KEY ([groupid])
	REFERENCES [hib].[group] ([id])

--ALTER TABLE [hib].[approval]
--	ADD CONSTRAINT [FK_approval_person] FOREIGN KEY ([approverid])
--	REFERENCES [hib].[person] ([id])

--ALTER TABLE [hib].[approval]
--	ADD CONSTRAINT [FK_approval_approvaltemplate] FOREIGN KEY ([templateid])
--	REFERENCES [hib].[approvaltemplate] ([id])

--ALTER TABLE [hib].[approvaltemplate]
--	ADD CONSTRAINT [FK_approvaltemplate_group] FOREIGN KEY ([groupid])
--	REFERENCES [hib].[group] ([id])

--ALTER TABLE [hib].[document]
	--ADD CONSTRAINT [FK_document_form] FOREIGN KEY ([formid])
	--REFERENCES [hib].[form] ([id])

--ALTER TABLE [hib].[document]
--	ADD CONSTRAINT [FK_document_approvaltemplate] FOREIGN KEY ([approvaltemplateid])
--	REFERENCES [hib].[approvaltemplate] ([id])

--ALTER TABLE [hib].[document]
--	ADD CONSTRAINT [FK_document_approval] FOREIGN KEY ([approvalid])
--	REFERENCES [hib].[approval] ([id])

ALTER TABLE [hib].[document]
	ADD CONSTRAINT [FK_document_request] FOREIGN KEY ([requestid])
	REFERENCES [hib].[request] ([id])

ALTER TABLE [hib].[document]
	ADD CONSTRAINT [FK_document_documenttemplate] FOREIGN KEY ([documenttemplateid])
	REFERENCES [hib].[documenttemplate] ([id])

--ALTER TABLE [hib].[documenttemplate]
--	ADD CONSTRAINT [FK_documenttemplate_repository] FOREIGN KEY ([repositoryid])
--	REFERENCES [hib].[repository] ([id])

--ALTER TABLE [hib].[documenttemplate]
--	ADD CONSTRAINT [FK_documenttemplate_approvaltemplate] FOREIGN KEY ([approvaltemplateid])
--	REFERENCES [hib].[approvaltemplate] ([id])

--ALTER TABLE [hib].[documenttemplate]
--	ADD CONSTRAINT [FK_documenttemplate_approval] FOREIGN KEY ([approvalid])
--	REFERENCES [hib].[approval] ([id])

--ALTER TABLE [hib].[onlinedata]
	--ADD CONSTRAINT [FK_onlinedata_form] FOREIGN KEY ([formid])
	--REFERENCES [hib].[form] ([id])

ALTER TABLE [hib].[onlinedata]
	ADD CONSTRAINT [FK_onlinedata_request] FOREIGN KEY ([requestid])
	REFERENCES [hib].[request] ([id])

--ALTER TABLE [hib].[onlinedata]
--	ADD CONSTRAINT [FK_onlinedata_repository] FOREIGN KEY ([repositoryid])
--	REFERENCES [hib].[repository] ([id])

--ALTER TABLE [hib].[onlinedata]
--	ADD CONSTRAINT [FK_onlinedata_approvaltemplate] FOREIGN KEY ([approvaltemplateid])
--	REFERENCES [hib].[approvaltemplate] ([id])

--ALTER TABLE [hib].[onlinedata]
--	ADD CONSTRAINT [FK_onlinedata_approval] FOREIGN KEY ([approvalid])
--	REFERENCES [hib].[approval] ([id])

--ALTER TABLE [hib].[onlinedatatemplate]
--	ADD CONSTRAINT [FK_onlinedatatemplate_repository] FOREIGN KEY ([repositoryid])
--	REFERENCES [hib].[repository] ([id])

--ALTER TABLE [hib].[onlinedatatemplate]
--	ADD CONSTRAINT [FK_onlinedatatemplate_approvaltemplate] FOREIGN KEY ([approvaltemplateid])
--	REFERENCES [hib].[approvaltemplate] ([id])

--ALTER TABLE [hib].[onlinedatatemplate]
--	ADD CONSTRAINT [FK_onlinedatatemplate_approval] FOREIGN KEY ([approvalid])
--	REFERENCES [hib].[approval] ([id])

--ALTER TABLE [hib].[review]
--	ADD CONSTRAINT [FK_review_reviewer] FOREIGN KEY ([reviewerid])
--	REFERENCES [hib].[person] ([id])
ALTER TABLE [hib].[review]
	ADD CONSTRAINT [FK_review_group] FOREIGN KEY ([groupid])
	REFERENCES [hib].[group] ([id])

ALTER TABLE [hib].[reviewcomment]
	ADD CONSTRAINT [FK_reviewcomment_review] FOREIGN KEY ([reviewid])
	REFERENCES [hib].[review] ([id])

ALTER TABLE [hib].[reviewcomment]
	ADD CONSTRAINT [FK_reviewcomment_comment] FOREIGN KEY ([commentid])
	REFERENCES [hib].[comment] ([id])

--ALTER TABLE [hib].[participant]
--	ADD CONSTRAINT [FK_participant_activity] FOREIGN KEY ([activityid])
--	REFERENCES [hib].[activity] ([id])
ALTER TABLE [hib].[participant]
	ADD CONSTRAINT [FK_participant_request] FOREIGN KEY ([requestid])
	REFERENCES [hib].[request] ([id])

ALTER TABLE [hib].[participant]
	ADD CONSTRAINT [FK_participant_person] FOREIGN KEY ([personid])
	REFERENCES [hib].[person] ([id])

--TODO: switched from activity to request (and using primarysiteid now), but not sure that we want this foreign key constraint
--ALTER TABLE [hib].[activity]
--	ADD CONSTRAINT [FK_activity_location] FOREIGN KEY ([siteid])
--	REFERENCES [hib].[location] ([id])
ALTER TABLE [hib].[request]
	ADD CONSTRAINT [FK_request_location] FOREIGN KEY ([primarysiteid])
	REFERENCES [hib].[location] ([id])
	
--ALTER TABLE [hib].[activitycomment]
--	ADD CONSTRAINT [FK_activitycomment_activity] FOREIGN KEY ([activityid])
--	REFERENCES [hib].[activity] ([id])

--ALTER TABLE [hib].[activitycomment]
--	ADD CONSTRAINT [FK_activitycomment_comment] FOREIGN KEY ([commentid])
--	REFERENCES [hib].[comment] ([id])
	
--ALTER TABLE [hib].[researchstudy]
--	ADD CONSTRAINT [FK_researchstudy_activity] FOREIGN KEY ([activityid])
--	REFERENCES [hib].[activity] ([id])

--ALTER TABLE [hib].[operationalstudy]
--	ADD CONSTRAINT [FK_operationalstudy_activity] FOREIGN KEY ([activityid])
--	REFERENCES [hib].[activity] ([id])

ALTER TABLE [hib].[request]
	ADD CONSTRAINT [FK_request_review] FOREIGN KEY ([reviewid])
	REFERENCES [hib].[review] ([id])

ALTER TABLE [hib].[eventgroup]
	ADD CONSTRAINT [FK_eventgroup_event] FOREIGN KEY ([eventid])
	REFERENCES [hib].[event] ([id])

ALTER TABLE [hib].[eventgroup]
	ADD CONSTRAINT [FK_eventgroup_group] FOREIGN KEY ([groupid])
	REFERENCES [hib].[group] ([id])
