Use Master
GO

CREATE DATABASE Neuroworks
GO

USE Neuroworks
GO

declare @STR_DBUser nvarchar(132), @STR_DBPassword nvarchar(132) select @STR_DBUser = N'xltek', @STR_DBPassword = N'xltek' 
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Neuroworks', @loginlang = N'us_english'	
if not exists (select * from master..syslogins where name = @STR_DBUser)
begin
	if (@@microsoftversion/0x1000000 <= 8) 
		exec sp_addlogin @STR_DBUser, @STR_DBPassword, @logindb, @loginlang
	else
	begin
		exec ('CREATE LOGIN ' + @STR_DBUser + ' WITH PASSWORD= ''' + @STR_DBPassword + ''' , DEFAULT_DATABASE= ' + @logindb + ', CHECK_POLICY=OFF')
	end
end
EXEC sp_grantdbaccess @STR_DBUser,@STR_DBUser
exec sp_addrolemember N'db_datareader', @STR_DBUser
exec sp_addrolemember N'db_datawriter', @STR_DBUser
exec sp_addrolemember N'db_ddladmin', @STR_DBUser

USE Neuroworks
GO

GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Neuroworks].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Neuroworks] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Neuroworks] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Neuroworks] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Neuroworks] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Neuroworks] SET ARITHABORT OFF
GO
ALTER DATABASE [Neuroworks] SET AUTO_CLOSE ON
GO
ALTER DATABASE [Neuroworks] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Neuroworks] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Neuroworks] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Neuroworks] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Neuroworks] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [Neuroworks] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Neuroworks] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Neuroworks] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Neuroworks] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Neuroworks] SET  ENABLE_BROKER
GO
ALTER DATABASE [Neuroworks] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Neuroworks] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Neuroworks] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Neuroworks] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Neuroworks] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Neuroworks] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Neuroworks] SET  READ_WRITE
GO
ALTER DATABASE [Neuroworks] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Neuroworks] SET  MULTI_USER
GO
ALTER DATABASE [Neuroworks] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Neuroworks] SET DB_CHAINING OFF
GO
USE [Neuroworks]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPatient](
	[PatientGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[PatientID] [int] NULL,
	[LastName] [varchar](256) NULL,
	[FirstName] [varchar](256) NULL,
	[MiddleName] [varchar](256) NULL,
	[Initial] [varchar](256) NULL,
	[Title] [varchar](256) NULL,
	[BirthDate] [datetime] NULL,
	[BirthDateLabel] [varchar](256) NULL,
	[Gender] [varchar](256) NULL,
	[MaritalStatus] [varchar](256) NULL,
	[Weight] [int] NULL,
	[Height] [int] NULL,
	[Hand] [varchar](256) NULL,
	[RefPhys] [varchar](256) NULL,
	[Address1] [varchar](256) NULL,
	[Address2] [varchar](256) NULL,
	[City] [varchar](256) NULL,
	[State] [varchar](256) NULL,
	[StateLabel] [varchar](256) NULL,
	[Country] [varchar](256) NULL,
	[ZIP] [varchar](256) NULL,
	[ZIPLabel] [varchar](256) NULL,
	[Telephone] [varchar](256) NULL,
	[TelephoneLabel] [varchar](256) NULL,
	[ID] [varchar](256) NULL,
	[IDLabel] [varchar](256) NULL,
	[BillingID] [varchar](256) NULL,
	[BillingIDLabel] [varchar](256) NULL,
	[ChartNo] [varchar](256) NULL,
	[History] [image] NULL,
	[FamPhysID] [varchar](256) NULL,
	[PatCatID] [varchar](256) NULL,
	[PatTypeID] [varchar](256) NULL,
	[ResidueEegKt] [image] NULL,
	[Locker] [varchar](256) NULL,
 CONSTRAINT [PatientGUID_pk] PRIMARY KEY CLUSTERED 
(
	[PatientGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblStudyType](
	[StudyTypeGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[StudyTypeID] [int] NULL,
	[StudyTypeName] [varchar](256) NULL,
 CONSTRAINT [StudyTypeGUID_pk] PRIMARY KEY CLUSTERED 
(
	[StudyTypeGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblVersion](
	[Version] [float] NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblDiagnosisConfiguration](
	[DiagnosisGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[DiagnosisText] [varchar](256) NULL,
	[DiagnosisCode] [varchar](16) NULL,
	[ShortcutCode] [varchar](10) NULL,
	[DiagnosisType] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDiagnosis](
	[id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[StudyGUID] [uniqueidentifier] NOT NULL,
	[DiagnosisGUID] [uniqueidentifier] NOT NULL,
	[DiagnosisLevel] [int] NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblArchive](
	[ArchGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ArchID] [int] NULL,
	[ArchLabel] [varchar](256) NULL,
	[Institution] [varchar](256) NULL,
 CONSTRAINT [ArchGUID_pk] PRIMARY KEY CLUSTERED 
(
	[ArchGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblStudy](
	[StudyGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[StudyID] [int] NULL,
	[PatientID] [int] NULL,
	[PatientGUID] [uniqueidentifier] NULL,
	[Creator] [varchar](256) NULL,
	[HeadBoxID] [int] NULL,
	[RiserCardID] [int] NULL,
	[NeuroWorksID] [int] NULL,
	[StudyTypeID] [int] NULL,
	[StudyTypeGUID] [uniqueidentifier] NULL,
	[RecordTime] [int] NULL,
	[VideoRecorded] [int] NULL,
	[ArchID] [int] NULL,
	[ArchGUID] [uniqueidentifier] NULL,
	[AcquiredStatus] [int] NULL,
	[ArchivedStatus] [int] NULL,
	[PrunedStatus] [int] NULL,
	[RawDataStatus] [int] NULL,
	[ReportedStatus] [int] NULL,
	[ScheduledStatus] [int] NULL,
	[Locker] [varchar](256) NULL,
	[FilePath] [varchar](256) NULL,
	[CreationTime] [datetime] NULL,
	[Duration] [int] NULL,
	[EegNo] [varchar](256) NULL,
	[Reviewer] [varchar](256) NULL,
	[LastAttack] [datetime] NULL,
	[LastMeal] [datetime] NULL,
	[AcquisitionInstrument] [varchar](256) NULL,
	[ResidueStudyKt] [image] NULL,
	[PS_Normal] [int] NULL,
	[PS_MentallyChallenged] [int] NULL,
	[PS_Awake] [int] NULL,
	[PS_Drowsy] [int] NULL,
	[PS_Asleep] [int] NULL,
	[PS_Uncooperative] [int] NULL,
	[PS_Tense] [int] NULL,
	[PS_Confused] [int] NULL,
	[PS_BehaviorDifficulty] [int] NULL,
	[PS_Aphasic] [int] NULL,
	[PS_SemiComa] [int] NULL,
	[PS_Coma] [int] NULL,
	[PS_StatusEpilepticus] [int] NULL,
	[PS_Other] [varchar](128) NULL,
	[ElectrodeType] [int] NULL,
	[ElectrodeOtherType] [varchar](128) NULL,
	[ElectrodePlacement] [int] NULL,
	[ElectrodeSpecialLeads] [varchar](128) NULL,
	[ElectrodeImpedance] [varchar](128) NULL,
	[PhysField0] [text] NULL,
	[PhysField1] [text] NULL,
	[PhysField2] [text] NULL,
	[PhysField3] [text] NULL,
	[PhysField4] [text] NULL,
	[PhysField5] [text] NULL,
	[PhysField6] [text] NULL,
	[PhysField7] [text] NULL,
	[PhysField8] [text] NULL,
	[PhysField9] [text] NULL,
	[PhysField0Label] [varchar](64) NULL,
	[PhysField1Label] [varchar](64) NULL,
	[PhysField2Label] [varchar](64) NULL,
	[PhysField3Label] [varchar](64) NULL,
	[PhysField4Label] [varchar](64) NULL,
	[PhysField5Label] [varchar](64) NULL,
	[PhysField6Label] [varchar](64) NULL,
	[PhysField7Label] [varchar](64) NULL,
	[PhysField8Label] [varchar](64) NULL,
	[PhysField9Label] [varchar](64) NULL,
	[TechField0] [text] NULL,
	[TechField1] [text] NULL,
	[TechField2] [text] NULL,
	[TechField3] [text] NULL,
	[TechField4] [text] NULL,
	[TechField5] [text] NULL,
	[TechField6] [text] NULL,
	[TechField7] [text] NULL,
	[TechField8] [text] NULL,
	[TechField9] [text] NULL,
	[TechField10] [text] NULL,
	[TechField11] [text] NULL,
	[TechField12] [text] NULL,
	[TechField13] [text] NULL,
	[TechField14] [text] NULL,
	[TechField0Label] [varchar](64) NULL,
	[TechField1Label] [varchar](64) NULL,
	[TechField2Label] [varchar](64) NULL,
	[TechField3Label] [varchar](64) NULL,
	[TechField4Label] [varchar](64) NULL,
	[TechField5Label] [varchar](64) NULL,
	[TechField6Label] [varchar](64) NULL,
	[TechField7Label] [varchar](64) NULL,
	[TechField8Label] [varchar](64) NULL,
	[TechField9Label] [varchar](64) NULL,
	[TechField10Label] [varchar](64) NULL,
	[TechField11Label] [varchar](64) NULL,
	[TechField12Label] [varchar](64) NULL,
	[TechField13Label] [varchar](64) NULL,
	[TechField14Label] [varchar](64) NULL,
	[FileContents] [int] NULL,
	[ParentStudy] [varchar](64) NULL,
	[ToBeReviewedBy] [varchar](256) NULL,
	[StudyGroup] [int] NOT NULL,
	[StagingType] [int] NULL,
 CONSTRAINT [StudyGUID_pk] PRIMARY KEY CLUSTERED 
(
	[StudyGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblUploaded](
	[UploadedGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[Uploaded] [int] NULL,
	[StudyGUID] [uniqueidentifier] NULL,
	[HBSerialNumber] [char](64) NULL,
	[IsTrueUpload] [int] NULL,
	[HBStudyID] [uniqueidentifier] NULL,
 CONSTRAINT [UploadedGUID_pk] PRIMARY KEY CLUSTERED 
(
	[UploadedGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblStudyReview](
	[RevGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[RevID] [int] NULL,
	[StudyID] [int] NULL,
	[StudyGUID] [uniqueidentifier] NULL,
	[Reviewer] [varchar](256) NULL,
	[ReviewedStatus] [int] NULL,
	[ReviewTime] [datetime] NULL,
	[ResidueStudyRevKt] [image] NULL,
 CONSTRAINT [RevGUID_pk] PRIMARY KEY CLUSTERED 
(
	[RevGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblNote](
	[NoteGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[NoteID] [int] NULL,
	[StudyID] [int] NULL,
	[StudyGUID] [uniqueidentifier] NULL,
	[Type] [varchar](256) NULL,
	[ID] [int] NULL,
	[Text] [varchar](256) NULL,
	[Comment] [image] NULL,
	[Stamp] [int] NULL,
	[Origin] [varchar](256) NULL,
	[Host] [varchar](256) NULL,
	[ReadOnly] [int] NULL,
	[DataType] [varchar](256) NULL,
	[User] [varchar](256) NULL,
	[CreationTime] [datetime] NULL,
	[ModificationTime] [datetime] NULL,
	[ResidueNoteKt] [image] NULL,
 CONSTRAINT [NoteGUID_pk] PRIMARY KEY CLUSTERED 
(
	[NoteGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblPatient] ADD  DEFAULT (newid()) FOR [PatientGUID]
GO

ALTER TABLE [dbo].[tblStudyType] ADD  DEFAULT (newid()) FOR [StudyTypeGUID]
GO

ALTER TABLE [dbo].[tblDiagnosisConfiguration] ADD  DEFAULT (newid()) FOR [DiagnosisGUID]
GO

ALTER TABLE [dbo].[tblDiagnosis] ADD  DEFAULT (newid()) FOR [id]
GO

ALTER TABLE [dbo].[tblArchive] ADD  DEFAULT (newid()) FOR [ArchGUID]
GO

ALTER TABLE [dbo].[tblStudy] ADD  DEFAULT (newid()) FOR [StudyGUID]
GO

ALTER TABLE [dbo].[tblStudy] ADD  DEFAULT ((1)) FOR [StudyGroup]
GO

ALTER TABLE [dbo].[tblUploaded] ADD  DEFAULT (newid()) FOR [UploadedGUID]
GO

ALTER TABLE [dbo].[tblStudyReview] ADD  DEFAULT (newid()) FOR [RevGUID]
GO

ALTER TABLE [dbo].[tblNote] ADD  DEFAULT (newid()) FOR [NoteGUID]
GO

ALTER TABLE [dbo].[tblStudy]  WITH CHECK ADD FOREIGN KEY([ArchGUID])
REFERENCES [dbo].[tblArchive] ([ArchGUID])
GO

ALTER TABLE [dbo].[tblStudy]  WITH CHECK ADD FOREIGN KEY([PatientGUID])
REFERENCES [dbo].[tblPatient] ([PatientGUID])
GO

ALTER TABLE [dbo].[tblStudy]  WITH CHECK ADD FOREIGN KEY([StudyTypeGUID])
REFERENCES [dbo].[tblStudyType] ([StudyTypeGUID])
GO

ALTER TABLE [dbo].[tblUploaded]  WITH CHECK ADD FOREIGN KEY([StudyGUID])
REFERENCES [dbo].[tblStudy] ([StudyGUID])
GO

ALTER TABLE [dbo].[tblStudyReview]  WITH CHECK ADD FOREIGN KEY([StudyGUID])
REFERENCES [dbo].[tblStudy] ([StudyGUID])
GO

ALTER TABLE [dbo].[tblNote]  WITH CHECK ADD FOREIGN KEY([StudyGUID])
REFERENCES [dbo].[tblStudy] ([StudyGUID])
GO

INSERT INTO tblVersion (version)
VALUES ('4.1')
GO