/**
 * Sets up the database with all the Hospitals, HCPs, Prescriptions, and
 * Lab precedures required to pass the acceptance tests.  Does not set up
 * previous office visists with existing patients.
 */

/**
 * Adding Central, Eastern, and Northern Hospitals
 */
INSERT INTO Hospitals(HospitalID, HospitalName) VALUES

('0000000000','Central Hospital'),
('1111111111','Eastern Hospital'),
('2222222222','Northern Hospital') ON DUPLICATE KEY UPDATE HospitalID = HospitalID;

/**
 * Adding HCP Antonio Medico
 */
INSERT INTO Personnel(
MID,
AMID,
ROLE,
lastName,
firstName,
address1,
address2,
city,
state,
zip,
zip1,
zip2,
phone,
phone1,
phone2,
phone3,
specialty,
email)
VALUES (
9000000012,
NULL,
'hcp',
'Medico',
'Antonio',
'4321 My Road St',
'PO BOX 2',
'CityName',
'NY',
'12345-1234',
'12345',
'1234',
'999-888-7777',
'999',
'888',
'7777',
NULL,
'amedico@iTrust.org'
) ON DUPLICATE KEY UPDATE MID = MID;

INSERT INTO Users(MID, password, ROLE, sQuestion, sAnswer) VALUES(9000000012, 'pw', 'hcp', 'first letter?', 'a')
ON DUPLICATE KEY UPDATE MID = MID;

INSERT INTO HCPAssignedHos(HCPID, HosID) VALUES(9000000012,'9191919191'), (9000000012,'0000000000'))
ON DUPLICATE KEY UPDATE HCPID = HCPID;


/**
 * Adding HCP Sarah Soulcrusher
 */
INSERT INTO Personnel(
MID,
AMID,
ROLE,
lastName,
firstName,
address1,
address2,
city,
state,
zip,
zip1,
zip2,
phone,
phone1,
phone2,
phone3,
specialty,
email)
VALUES (
9000000011,
NULL,
'hcp',
'Soulcrusher',
'Sarah',
'4321 My Road St',
'PO BOX 2',
'CityName',
'NY',
'12345-1234',
'12345',
'1234',
'999-888-7777',
'999',
'888',
'7777',
NULL,
'amedico@iTrust.org'
) ON DUPLICATE KEY UPDATE MID = MID;

INSERT INTO Users(MID, password, ROLE, sQuestion, sAnswer) VALUES(9000000011, 'pw', 'hcp', 'first letter?', 'a')
ON DUPLICATE KEY UPDATE MID = MID;

INSERT INTO HCPAssignedHos(HCPID, HosID) VALUES(9000000011,'9191919191'), (9000000011,'1111111111')
ON DUPLICATE KEY UPDATE HCPID = HCPID;


/**
 * Adding Ibuprofen prescription
 */
INSERT INTO NDCodes(Code, Description) VALUES
('67877-1191', 'Ibuprofen')

ON DUPLICATE KEY UPDATE Code = Code;


/**
 * Adding bacteria identification test lab procedure
 */
INSERT INTO LOINC
(LaboratoryProcedureCode,Component,KindOfProperty,TimeAspect,System,ScaleType,MethodType)
VALUES
('45335-7', 'Bacteria Identification Test', 'ACnc', 'Pt', 'Isolate', 'Ord', 'culture');


/**
 * Adding Aspirin allergy to Andy Programmer
 */
INSERT INTO Allergies(PatientID,Description, FirstFound)
    VALUES (2, 'Aspirin', '1999-03-14 20:33:58');



 /**
 * Adding office visit to Baby Programmer (scenario 3)
 */
INSERT INTO OfficeVisits(id,visitDate,HCPID,notes,HospitalID,PatientID)
VALUES (6001,SELECT CURRDATE() - INTERVAL DAY,9000000003,'Hates getting shots','2222222222',5)
 ON DUPLICATE KEY UPDATE id = id;


 /**
 * Adding office visit to Andy Programmer (scenario 4)
 */
INSERT INTO OfficeVisits(id,visitDate,HCPID,notes,HospitalID,PatientID)
VALUES (6002,'2011-01-2',9000000012,'Second medical visit on two days','0000000000',2)
 ON DUPLICATE KEY UPDATE id = id;


 /**
 * Adding office visit to Random Person (scenario 5)
 */
INSERT INTO OfficeVisits(id,visitDate,HCPID,notes,HospitalID,PatientID)
VALUES (6003,'2011-01-26',9000000011,'Seems fine to me','1111111111',1)
 ON DUPLICATE KEY UPDATE id = id;



/**
 * Adding immunizations to Baby Programmer (scenario 3)
 */
INSERT INTO OVProcedure(CPTCode, VisitID) VALUES ('90707', 6001), ('90371', 6001), ('90712', 6001);


/**
 * Adding lab procedure to Baby Programmer (scenario 3)
 */
INSERT INTO LabProcedure
(PatientMID,LaboratoryProcedureCode,Rights,STATUS,Commentary,Results,OfficeVisitID, UpdatedDate)
VALUES  (5,'13495-7','ALLOWED','COMPLETED','','',6001,SELECT CURRDATE() - INTERVAL DAY);



/**
 * Adding prescription to Andy Programmer (scenario 4)
 */
INSERT INTO OVMedication(VisitID, NDCode, StartDate, EndDate, Dosage, Instructions)
       VALUES (6002, 67877-1191, '2011-02-02', '2011-02-09', 200, 'Take once daily with a meal');


/**
 * Adding diagnosis to Random Person (scenario 5)
 */
INSERT INTO OVDiagnosis(ID, ICDCode, VisitID)
    VALUES  (7395, 11.40, 6003)
     ON DUPLICATE KEY UPDATE id = id;


/**
 * Adding procedures to Random Person (scenario 5)
 */
INSERT INTO OVProcedure(CPTCode, VisitID) VALUES ('1270F', 6003);


/**
 * Adding lab procedure to Random Person (scenario 5)
 */
INSERT INTO LabProcedure
(PatientMID,LaboratoryProcedureCode,Rights,STATUS,Commentary,Results,OfficeVisitID, UpdatedDate)
VALUES  (1,'45335-7','ALLOWED','COMPLETED','','',6003,'2011-01-26');

/**
 * Adding prescription to Random Person (scenario 5)
 */
INSERT INTO OVMedication(VisitID, NDCode, StartDate, EndDate, Dosage, Instructions)
   VALUES (6003, 67877-1191, '2011-01-26', '2011-02-05', 200, 'Take once daily');