-- --------------------------------------------------------------------------------
-- Step #2.1: Create Tables
-- --------------------------------------------------------------------------------
CREATE TABLE TPatients
(
	 intPatientID							INTEGER			NOT NULL
	,strFirstName							VARCHAR(50)		NOT NULL
	,strLastName							VARCHAR(50)		NOT NULL
	,CONSTRAINT TPatients_PK PRIMARY KEY ( intPatientID )
)

CREATE TABLE TPatientVisits
(
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,dtmVisitDate							DATE			NOT NULL
	,strVisitNotes							VARCHAR(8000)	NOT NULL
	,CONSTRAINT TPatientVisits_PK PRIMARY KEY ( intPatientID, intVisitIndex )
)

CREATE TABLE TVisitProcedures
(	 
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,intProcedureIndex						INTEGER			NOT NULL
	,strProcedureName						VARCHAR(50)		NOT NULL
	,CONSTRAINT TVisitProcedures_PK PRIMARY KEY ( intVisitIndex, intProcedureIndex )
)

CREATE TABLE TVisitMedications
(
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,intMedicationIndex						INTEGER			NOT NULL
	,strMedicationName						VARCHAR(50)		NOT NULL
	,CONSTRAINT TVisitMedications_PK PRIMARY KEY ( intVisitIndex, intMedicationIndex )
)

CREATE TABLE TVisitLabTests
(
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,intLabTestIndex						INTEGER			NOT NULL
	,intResultIndex							INTEGER			NOT NULL
	,strLabTestName							VARCHAR(50)		NOT NULL
	,CONSTRAINT TVisitLabTests_PK PRIMARY KEY ( intVisitIndex, intLabTestIndex )
)

CREATE TABLE TLabTestResults
(
	 intVisitIndex							INTEGER			NOT NULL
	,intLabTestIndex						INTEGER			NOT NULL
	,intResultIndex							INTEGER			NOT NULL
	,strResultName							VARCHAR(50)		NOT NULL
	,CONSTRAINT TLabTestResults_PK PRIMARY KEY ( intVisitIndex, intLabTestIndex, intResultIndex )
)

-- --------------------------------------------------------------------------------
-- Step #2.2: Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------
--
-- #	Child					Parent			Column(s)
--		-----					------			---------
-- 1	TPatientVisits			TPatients		intPatientID
-- 2	TVisitProcedures		TPatientVisits	intVisitIndex
-- 3	TVisitMedications		TPatientVisits	intVisitIndex
-- 4	TVisitLabTests			TPatientVisits	intVisitIndex
-- 5	TLabTestResults			TVisitLabTests	intLabTestIndex

-- 1
ALTER TABLE TPatientVisits ADD CONSTRAINT TPatientVisits_TPatients_FK
FOREIGN KEY ( intPatientID ) REFERENCES TPatients ( intPatientID )

-- 2
ALTER TABLE TVisitProcedures ADD CONSTRAINT TVisitProcedures_TPatientVisits_FK
FOREIGN KEY ( intPatientID,intVisitIndex ) REFERENCES TPatientVisits ( intPatientID,intVisitIndex )

-- 3
ALTER TABLE TVisitMedications ADD CONSTRAINT TVisitMedications_TPatientVisits_FK
FOREIGN KEY ( intPatientID,intVisitIndex ) REFERENCES TPatientVisits ( intPatientID,intVisitIndex )

-- 4
ALTER TABLE TVisitLabTests ADD CONSTRAINT TVisitLabTests_TPatientVisits_FK
FOREIGN KEY ( intPatientID,intVisitIndex ) REFERENCES TPatientVisits ( intPatientID,intVisitIndex )

---- 5 -- I was slightly obssesed to figure out this constraint I hope I got it right!
--ALTER TABLE TLabTestResults ADD CONSTRAINT TLabTestResults_TVisitLabTests_FK
--FOREIGN KEY ( intLabTestIndex ) REFERENCES TVisitLabTests ( intLabTestIndex )

-- --------------------------------------------------------------------------------
-- Step #2.3: Add at least 2 patients
-- --------------------------------------------------------------------------------
INSERT INTO TPatients( intPatientID, strFirstName, strLastName )
VALUES		 (1,'Mary','Hagert')
			,(2,'Bob','Dylan')

-- --------------------------------------------------------------------------------
-- Step #2.4: Add at least 2 visits per patient
-- --------------------------------------------------------------------------------
INSERT INTO TPatientVisits( 
							 intPatientID
							,intVisitIndex
							,dtmVisitDate
							,strVisitNotes
						    )
VALUES		 (1,1,'09/25/2013','Patient complained of tiredness and fatigue')
			,(1,2,'09/29/2013','Patient responded well to extra sleep and coffee')
			,(2,1,'09/25/2013','Patient and I go WAY back ol timer all the way back to the boot scoot..')
			,(2,2,'09/25/2013','To whom ever that is making funny in my notes better knock it off!')

-- --------------------------------------------------------------------------------
-- Step #2.5: Add at least 2 procedures per patient
-- --------------------------------------------------------------------------------
INSERT INTO TVisitProcedures(
							 intPatientID
							,intVisitIndex
							,intProcedureIndex
							,strProcedureName
							)
VALUES		 (1, 1, 1,'The embarassing one')
			,(1, 1, 4,'NO This one is even worse!')
			,(1, 2, 8,'Okay you got me.. This is the one..')
			,(1, 2, 12,'Nothing to see here everyone just go home...')
			,(2, 1, 2,'Check blood pressure')
			,(2, 1, 5,'Check blood sugar')
			,(2, 2, 9,'Obamacare drug screen')
			,(2, 2, 13,'Just kidding great idea, purposely designed to fail policy so we can continue SSDD..')

-- --------------------------------------------------------------------------------
-- Step #2.6: Add at least 2 medications per patient visit
-- --------------------------------------------------------------------------------
INSERT INTO TVisitMedications(
							 intPatientID
							,intVisitIndex
							,intMedicationIndex
							,strMedicationName
							)
VALUES		 (1, 1, 1,'This is illegal ya know')
			,(1, 1, 4,'Setting quotas on the pills are we?')
			,(1, 2, 8,'Okay just cut me in')
			,(1, 2, 12,'What! The margins are to small you quit?')
			,(2, 1, 2,'Adderall')
			,(2, 1, 5,'Xanax')
			,(2, 2, 9,'Hydroxyzine Pamoate')
			,(2, 2, 13,'Alcohol')

-- --------------------------------------------------------------------------------
-- Step #2.7: Add at least 2 lab tests per patient visit
-- --------------------------------------------------------------------------------
INSERT INTO TVisitLabTests(
							 intPatientID
							,intVisitIndex
							,intLabTestIndex
							,intResultIndex
							,strLabTestName
							)
VALUES		 (1, 1, 1, 1, 'Hemoglobin count')
			,(1, 1, 4, 1, 'Iron count')
			,(1, 2, 8, 1, 'Allergy screen')
			,(1, 2, 12,1, 'Skin biopsy')
			,(2, 1, 2, 1, 'Blood Type')
			,(2, 1, 5, 1, 'Electrolytes')
			,(2, 2, 9, 1, 'Enzymes & Proteins')
			,(2, 2, 13,1, 'Lipid blood test')

-- --------------------------------------------------------------------------------
-- Step #2.8: Add at least 2 results per lab tests
-- --------------------------------------------------------------------------------
INSERT INTO TLabTestResults(
							 intVisitIndex
							,intLabTestIndex
							,intResultIndex
							,strResultName
							)
VALUES		 (1, 1, 1,'Without a doubt')
			,(1, 1, 4,'Yes definitely')
			,(1, 2, 8,'Most likely')
			,(1, 2, 12,'Signs point to yes')
			,(2, 1, 2,'Outlook not certain')
			,(2, 1, 5,'It is certain')
			,(2, 2, 9,'Reply hazy try again')
			,(2, 2, 13,'Cannot predict now')