create database VHR
	go
	use VHR
	go

	create table Patient(
	    IdPatient char(18) not null unique,
	    sex char(1) not null,
	    birthdate date not null,
	    fname varchar(20) not null,
	    lname varchar(20) not null,
	    bloodType varchar(3) not null,
	    email varchar(30) not null,
	    telefono varchar(15)
	    Primary Key (IdPatient),
	);

	create table Allergies(
	    IdAllergie int IDENTITY(1,1) not null unique,
	    name varchar(20) not null,
	    IdPatient char(18)
	    Primary Key (IdAllergie)
	    Foreign Key (IdPatient) references Patient(IdPatient)
	);

create table Doctor(
    cedulaProfesional char(7) not null unique,
    fname varchar(20) not null,
    lname varchar(20) not null,
    sex char(1) not null,
    speciality varchar(20) null,
    Primary Key (cedulaProfesional)
);

	create table DoctorVisit(
	    IdDoctorVisit int IDENTITY(1,1) not null unique,
	    cedulaProfesional char(7) null,
	    IdPatient char(18) null,
	    visitDate date not null,
	    comments text null,
	    temp int not null,
	    height decimal(5,2) not null,
	    weight decimal(5,2) not null,
	    bloodPressure int not null,
	    Primary Key (IdDoctorVisit),
	    Foreign Key (cedulaProfesional) references Doctor(cedulaProfesional),
	    Foreign Key (IdPatient) references Patient(IdPatient),
	);

create table Prescription(
    IdPrescription int IDENTITY(1,1) not null unique,
    IdDoctorVisit int null,
    prescriptionDate date not null,
    Primary Key (IdPrescription),
    Foreign Key (IdDoctorVisit) references DoctorVisit(IdDoctorVisit)
);

	create table DrugCatalog(
	    IdDrugCatalog int IDENTITY(1,1) not null unique,
	    genericName varchar(30) not null,
	    comments text null,
	    sideEffects varchar(255) null,
	    Primary Key (IdDrugCatalog)
	);

create table PrescriptionDetails(
    IdPrescription int not null,
    IdDrugCatalog int not null,
    instructions text not null,
    startDate Date not null,
    endDate Date not null,
    Foreign Key (IdPrescription) references Prescription (IdPrescription),
    Foreign Key (IdDrugCatalog) references DrugCatalog (IdDrugCatalog),
    constraint PK_PrescriptionDetails primary key (IdPrescription,IdDrugCatalog)
);

	create table LabTestCatalog(
	    IdLabTestCatalog int IDENTITY(1,1) not null unique,
	    name varchar(30) not null,
	    Primary Key (name)
	);

	create table LabTest(
	    IdLabTest int IDENTITY(1,1) not null unique,
	    labDate date not null,
	    IdLabTestCatalog int null,
	    IdPatient char(18) null,
	    Primary Key(IdLabTest),
	    Foreign Key(IdLabTestCatalog) references LabTestCatalog(IdLabTestCatalog),
	    Foreign Key(IdPatient) references Patient(IdPatient),
	);

	create table LabTestIndicatorCatalog(
	    IdLabTestIndicatorCatalog int IDENTITY(1,1) not null unique,
	    indicator varchar(20) null,
	    minLTIC decimal(5,2) not null,
	    maxLTIC decimal(5,2) not null,
	    Primary key(IdLabTestIndicatorCatalog)
	);

	create table LabTestResults(
	    IdLabTestResults int IDENTITY(1,1) not null unique,
	    value decimal(5,2) null,
	    isAnormal bit not null,
	    comment text null,
	    IdLabTest int null,
	    IdLabTestIndicatorCatalog int null,
	    Primary Key(IdLabTestResults),
	    Foreign Key(IdLabTest) references LabTest(IdLabTest),
	    Foreign Key(IdLabTestIndicatorCatalog) references LabTestIndicatorCatalog(IdLabTestIndicatorCatalog)
	);



INSERT INTO Patient VALUES ('PEBA940625HNLXRD05', 'M', '1994-06-25', 'Adrian', 'Peña', 'O+', 'adrianpena_94@hotmail.com', '83565991')

INSERT INTO Patient VALUES ('GASJ971105HHGRNN06', 'M', '1997-11-05', 'Juan', 'García', 'O+', 'juanpablo@hotmail.com', '535345')

INSERT INTO Patient VALUES ('OOGB960601HNLRRR06', 'M', '1996-06-01', 'Bernardo', 'Orozco', 'O+', 'berny.orozco@hotmail.com', '4232343')

INSERT INTO Patient VALUES ('LOMS950423HNEPDR01', 'M', '1995-04-23', 'Sergio', 'López', 'O+', 'sergio@hotmail.com', '322123432')

INSERT INTO Allergies VALUES ('Pimientos', 'LOMS950423HNEPDR01')

INSERT INTO Allergies VALUES ('Penicilina', 'OOGB960601HNLRRR06')

INSERT INTO Allergies VALUES ('Cacahuate', 'PEBA940625HNLXRD05')

INSERT INTO Allergies VALUES ('Cacahuate', 'LOMS950423HNEPDR01')

INSERT INTO Allergies VALUES ('Picaduras de insecto', 'GASJ971105HHGRNN06')

INSERT INTO Doctor VALUES ('3273546','Miguel Angel', 'Zapata Martinez', 'M', 'cirugia general')

INSERT INTO Doctor VALUES ('0026363','Luisa Irene', 'Salinas Pulido', 'F', 'Ginecologia')

INSERT INTO Doctor VALUES ('3816929','Gerardo', 'Gonzales Trevino', 'M', 'cirugia general')

INSERT INTO Doctor VALUES ('5040228','Eduardo', 'Plancarte Lozano', 'M', 'Ginecologia')

INSERT INTO Doctor VALUES ('3861864','Cynthia Marisol', 'Rodríguez Treviño', 'F', 'Cirujano partero')

INSERT INTO DrugCatalog VALUES ('ACIDO ACETILSALICILICO','muy recomendado','Prolongacion del tiempo de sangrado, tinnitus, perdida de la audicion, nausea, vomito')

INSERT INTO DrugCatalog VALUES ('IBUPROFENO','tomar 3 cada semana como maximo','Epigastralgias, nauseas, mareos, pirosis, sensacion de plenitud en tracto gastrointestinal')

INSERT INTO DrugCatalog VALUES ('PARACETAMOL','no tomar todo los dias solo cuando hay dolor','Reacciones de hipersensibilidad: erupcion cutanea, neutropenia, pancitopenia')

INSERT INTO DrugCatalog VALUES ('TRAMADOL','no usar tan seguido','Nausea, mareos, bochornos, taquicardia, hipotension arteria')

INSERT INTO DrugCatalog VALUES ('FENTANILO','no recetar a embarazadas','Depresion respiratoria, vomito, rigidez muscular, euforia, broncoconstriccion')

INSERT INTO DrugCatalog VALUES ('METFORMINA','no comer antes de la comida si no provoca vomito','Diarrea, nausea, dispepsia, flatulencia, vomito, cefalea, hipoglucemia.')

INSERT INTO DrugCatalog VALUES ('METADONA','solo si tienen mucho dolor','Mareo, sedacion, nauseas y vomitos. Otros incluyen confusion mental, somnolencia, letargia')

INSERT INTO DrugCatalog VALUES ('BUPRENORFINA','comprar generico ya que es mas varato','Sedacion, mareo, cefalea, miosis, nausea, sudoracion y depresion respiratoria')

INSERT INTO DrugCatalog VALUES ('NIFEDIPINO','dificil de conseguir','Nausea, mareo, cefalea, rubor, hipotension arterial, estrenimiento y edema.')

INSERT INTO DrugCatalog VALUES ('ADENOSINA','minimo 4 tabletas por 2 dias','Disnea, enrojecimiento facial, dolor toracico, hipotension, nausea, ansiedad.')

INSERT INTO DoctorVisit VALUES ('3273546','OOGB960601HNLRRR06','2016-07-12','apendicitis',36,77.00,61.00,86)

INSERT INTO DoctorVisit VALUES ('3273546','OOGB960601HNLRRR06','2016-07-28','dado de alta de operacion de apendicitis',37,77.00,60.70,75)


--top 5 allergies
select top(5) name, count(name)
from Allergies
group by (name)
order by count(name) desc

--how many patient a doctor has
select d.fname, d.lname, count(DISTINCT p.IdPatient)
from Doctor d join DoctorVisit dv on d.cedulaProfesional=dv.cedulaProfesional
join Patient p on dv.IdPatient = p.IdPatient
group by d.fname, d.lname
order by d.fname

--get previous appointments of a pattient
select dv.visitDate, d.fname, d.lname,dv.temp, dv.weight, dv.height, dv.bloodPressure
from Doctor d join DoctorVisit dv on d.cedulaProfesional=dv.cedulaProfesional
join Patient p on dv.IdPatient = p.IdPatient
where p.IdPatient = (select IdPatient from Patient where fname='Bernardo' and lname='Orozco' and birthdate='1996-06-01')

--create prescription (template params: date, doctor name, patient name, name of drug)
INSERT INTO Prescription VALUES ((select IdDoctorVisit
                                    from DoctorVisit
                                    where visitDate='2016-07-12'
                                    and cedulaProfesional=(select cedulaProfesional from Doctor where fname='Miguel Angel' and lname='Zapata Martinez')
                                    and IdPatient=(select IdPatient from Patient where fname='Bernardo' and lname='Orozco' and birthdate='1996-06-01')),
                                '2016-07-12')

INSERT INTO PrescriptionDetails VALUES ((select IdPrescription from Prescription
                                        where prescriptionDate='2016-07-12'
                                        and IdDoctorVisit=(select IdDoctorVisit from DoctorVisit
                                                            where visitDate='2016-07-12'
                                                            and cedulaProfesional=(select cedulaProfesional from Doctor where fname='Miguel Angel' and lname='Zapata Martinez')
                                                            and IdPatient=(select IdPatient from Patient where fname='Bernardo' and lname='Orozco' and birthdate='1996-06-01'))),
                                        (select IdDrugCatalog from DrugCatalog where genericName='name of drug'),
                                        'instructions',
                                        '2016-07-12',
                                        'enddate')