#Table creation syntax for all tables
Create table Reception(
ReceptionId int not null auto_increment primary key ,
AdmissionsId int null,
Shift bit,
HourlyQueSize int,
EntryTime time
);
Create table Admissions(
AdmissionsId int not null auto_increment primary key,
PatientId int null,
ReceptionId int null,
AdmissionTime time,
Patient_Condition nvarchar(100),
ReleaseTime datetime
);
Create table Patient(
PatientId int not null auto_increment primary key,
AdmissionsId int null,
DoctorId int null,
sex bit,
fName nvarchar(15),
lName nvarchar(15),
Age int,
Illness nvarchar(50),
Symptoms nvarchar(150),
Comments nvarchar (200),
Email nvarchar(25),
PhoneNumber int,
Address nvarchar(75)
);
Create table Doctor(
  DoctorId int not null auto_increment PRIMARY KEY,
  PatientId int null,
  StaffId int null,
  PagerNumber int not null,
  PhoneNumber int,
  Email nvarchar(25),
  Possition nvarchar(25),
  Spesialism nvarchar(25)
  );
Create table Nurse(
 NurseId int not null auto_increment primary key,
 DoctorId int null,
 WardId int null,
 StaffId int null,
 Sex bit,
 Age Date,
 Qualification nvarchar (25),
 NurseSpeciality nvarchar(25)
 );
Create table Xray(
 XrayId int not null auto_increment primary key,
 PatientId int,
 DoctorId int,
 Result nvarchar(50),
 XrayArea nvarchar (25),
 XrayTime DateTime
 );
Create TABLE BloodTest(
 BloodId int not null auto_increment primary key,
 PatientId int null,
 DoctorId int null,
 BloodType nvarchar(15),
 Comment_Blood nvarchar(150),
 Results nvarchar(100),
 Test_Time int
 );
Create table MRI(
 MRI_ID INT NOT NULL auto_increment PRIMARY KEY,
 PatientId int null,
 DoctorId int null,
 Result nvarchar(100),
 MRI_Time int
 );
Create table Beds(
 BedId int not null auto_increment primary key,
 PatientId int null,
 WaitingTime int,
 BedOccupied bit
 );
Create table Ward(
 wardId int not null auto_increment primary key,
 bedId int null,
 No_Beds int,
 wardType nvarchar(50),
 NurseId int null
 );
Create table Staff(
StaffId int not null auto_increment primary key,
fName nvarchar(25),
lName nvarchar(25),
Age int,
Sex bit,
pps nvarchar(10),
ssn nvarchar(15),
Qualification nvarchar(100),
Salary int,
Contract int,
IsOnCall bit
);
Create table Spesialist(
SpesialistId int not null auto_increment primary key,
StaffId int null,
Spesiality nvarchar(50),
Possition nvarchar(40),
Availability nvarchar(25)
);
Create table Medication(
MedicationId int not null auto_increment primary key ,
Quantity int not null,
Medication_Type nvarchar(50),
SideEffects nvarchar(50),
DoctorId int null
);
Create table Treatment(
TreatmentId int not null auto_increment primary key,
MedicationId int null,
TreatmentType nvarchar(50),
Duration nvarchar(25)
);
Create table Patient_Medical(
Patient_MedicalId int not null auto_increment primary key,
PatientId int null,
illness nvarchar(50),
seenToBy nvarchar(50),
DoctorId int null,
Recomendation nvarchar(150),
TreatmentId int null,
MedicationId int null
);
Create table Patient_Insurance(
Patient_InsuranceId int not null auto_increment primary key,
PatientId int null,
InsuranceNo int,
InsuranceDetails nvarchar(150),
paymentType nvarchar(25),
patientCost int
);
Create table Visitor(

VistitorId int not null auto_increment primary key,
PatientId int null,
Length_Stayed nvarchar(50),
fName nvarchar(15),
lName nvarchar(15),
Address nvarchar(40)
);
#Alter table commands to add foreign keys in
Alter table reception
add foreign key (AdmissionsId)
references  Admissions(AdmissionsId);

Alter table Admissions
add foreign key (PatientId)
references Patient(PatientId);

alter table admissions
add foreign key (ReceptionId)
references reception(ReceptionId);

alter table patient
add foreign key(AdmissionsId)
references admissions(AdmissionsId);
alter table patient
add foreign key(DoctorId)
references Doctor(DoctorId);

alter table Doctor
add foreign key(PatientId)
references Patient(PatientId);
alter table Doctor
add foreign key(StaffId)
references Staff(StaffId);

alter table nurse
add foreign key(DoctorId)
references Doctor(DoctorId);
alter table nurse
add foreign key(wardId)
references Ward(wardId);
alter table nurse
add foreign key(StaffId)
references Staff (StaffId);

alter table Xray
add foreign key (PatientId)
references Patient(PatientId);
alter table Xray
add foreign key (DoctorId)
references Doctor(DoctorId);

alter table bloodtest
add foreign key(PatientId)
references Patient(PatientId);
alter table bloodtest
add foreign key(DoctorId)
references Doctor(DoctorId);

alter table mri
add foreign key(PatientId)
references Patient(PatientId);
alter table mri
add foreign key (DoctorId)
references Doctor(DoctorId);

alter table beds
ADD foreign key (PatientId)
references Patient(PatientId);

alter table Ward
add foreign key(BedId)
references Beds(BedId);

alter table Spesialist
add foreign key(StaffId)
references Staff(StaffId);

Alter table Medication
add foreign key(DoctorId)
references doctor(DoctorId);

alter table Treatment
add foreign key (MedicationId)
references medication(MedicationId);

alter table patient_medical
add foreign key (PatientId)
references Patient(PatientId);

alter table patient_medical
add foreign key (MedicationId)
references Medication(MedicationId);

alter table patient_insurance
add foreign key (PatientId)
references Patient(PatientId);

alter table visitor
add foreign key (PatientId)
references Patient(PatientId);

#Inserting data into tables
SELECT * FROM STAFF;

insert into staff (StaffId, fName, lName, Age, Sex, pps, ssn, Qualification, Salary, Contract, IsOnCall)
values
(null,'John','Carroll',41, 'M', '283739V' ,NULL,'Trinity', 120000, 'Full time', 1),
(null,'Paul','Carey'  ,32, 'M', '237281V', NULL,'Harvard', 160000, 'Full time', 1),
(null,'Mary','Murphy' ,48, 'F', '564759V', NULL,'St James', 42000, 'Full time', 0),
(null,'Michael','Burn',52, 'M', '243842V', NULL,'University College Dublin', 100000, 'Full time', 0),
(null,'John','Murphy',51, 'M', '298695V', NULL,'University College Dublin', 102340, 'Full time', 0),
(null,'Paula','O,Neill',53, 'F', '182739V',NULL,'St James', 48000, 'Part time', 1);




Insert into Nurse(NurseId, DoctorId, WardId, StaffId, Sex, Age, Qualification, NurseSpeciality)
values
(null, 5, 1, 3, 'F', 48, 'St James' , 'General'),
(null, 6, 2, 4, 'F', 53,'St James', 'General');

select * from doctor;
select * from staff;
select * from nurse;
select * from spesialist;
 set foreign_key_checks = 0;
 set foreign_key_checks = 1;

Insert into Doctor(DoctorId, PatientId, StaffId, PagerNumber, PhoneNumber, Email, Possition, Spesialism)
values
(null, 1, 1, 19283, 0857463722, 'JohnCarroll@gmail.com', 'Full time', 'Cardiac' ),
(null, 2, 2,  12329, 0867364222, 'PCarey@Hotmail.com', 'Full Time','General');
Insert into spesialist(SpesialistId, StaffId, Spesiality, Possition, Availability)
values
(null,4,'Eye and Ear', 'Full time', '4 week waiting list'),
(null, 5, 'Spineoligist', 'Full time', '5 month waiting list');

alter table patient modify column sex nvarchar(1);
insert into Patient(PatientId, AdmissionsId,DoctorId,Sex,fname, lname,Age, illness,symptoms, Comments,Email, PhoneNumber, address)
values
(null,1,2, 'F', 'Mary', 'Connely', 53, 'High blood pressure', 'Restless', 'Needs new treatment', 'mconally@gmail.com', 0856271662,'12 Briot Grove, Waterford' ),
(null,2, 1 , 'M', 'John', 'Purcell', 42, 'knee pain', 'Cant bend knee', 'Needs surgery', 'jPurcell@hotmail.com', 0878273661,'13 Bullock park, Carlow'),
(null, 3, 1, 'M', 'Michael', 'Kearney', 34, 'Sharp stomac pain', 'Cant keep food down', 'High dose of Anti-biotics', 'kearny132@gmail.com',0892633998, 'Kileshin road Carlow');

insert into Patient_medical(Patient_MedicalId, PatientId, illness,seenToBy,DoctorId,Recomendation,TreatmentId, MedicationId)
values
(null, 1, 'High blood pressure', 'Dr Carey', 2, 'New treatment plan needed', 1,1),
(null, 2, 'Knee pain', 'Dr Carroll', 1, 'Needs key hole surgery',  2,2),
(null, 3, 'Sharp stomac pain', 'Dr Carey', 2, 'Neutralising diet', 3,3);

insert into patient_insurance(patient_insuranceId, PatientId, InsuranceNo, InsuranceDetails,PaymentType, PatientCost)
values
(null, 1, 23726199, 'No health care', 'Credit card', 600),
(null, 2, 27368839, 'Laya Health care', 'Bank Transfer', 15000 ),
(null, 3, 23771622, 'Medcial Card Cover', 'Goverment Payment' ,5000);

insert into medication(medicationId, Quantity, Medication_Type, SideEffects, DoctorId)
values
(null, 3, 'High dose of h3hh7', 'May cause headaches', 2),
(null, 2, 'Strong pain killers', 'Can be addictive if abused', 1 ),
(null, 5, 'Neutralising tablets', 'May cause nausia', 2);

insert into Treatment(TreatmentId, medicationId, TreatmentType, Duration)
values
(null,1,'New schedule of medication', '6-8 weeks'),
(null, 2, 'Surgery needed', '5-6 hours'),
(null, 3, 'Neutralisation', '1-2 weeks');
ALTER TABLE RECEPTION MODIFY COLUMN SHIFT NVARCHAR(5);
insert into reception(ReceptionId, AdmissionsId,Shift, HourlyQueSize, EntryTime)
values(NULL, 1, 'Day',4, 16.00),
(null, 2, 'Night', 2, 02.30),
(null, 3, 'Day', 8, 19.26);
alter table admissions modify column ReleaseTime time;
insert into Admissions(AdmissionsId, PatientId,ReceptionId, AdmissionTime,Patient_Condition,ReleaseTime)
values(null,1,1,16.30,'Restless', 15.43),
(null, 2, 2, 02.40, 'Cant bend knee', 13.20),
(null, 3, 3,20.12,'Sharp pain', 22.16);

insert into visitor(VistitorId, PatientId,Length_Stayed, fname, lname, Address)
values
(null, 1, '20 minutes', 'Hannah', 'Dunne', 'Cois Na Coill, Waterford'),
(null, 2 , '1 hour 20 minutes', 'Rebecca', 'Burke', 'Cannons Quarter, Tullow'),
(null, 1, '22 Minutes', 'Alan', 'Carroll', '12 Bullock Park, Carlow'),
(null, 3, '1 Hour 2 minutes', 'John', 'Moran', 'Old Dublin road, Carlow');
select * from admissions;
select * from reception;


set foreign_key_checks = 0;
select * from Xray;

select * from Mri;




select s.fname, s.lname, sp.possition
from staff s
inner join spesialist sp on s.StaffId = sp.staffid;