DROP INDEX appointment_custno;
DROP INDEX appointment_physicianno;
DROP INDEX cust_medicareno;
DROP INDEX cust_surname;
DROP INDEX drug_drugname;
DROP INDEX phys_surname;
DROP INDEX physcust_custno;
DROP INDEX physcust_physicianno;
DROP INDEX prescription_custno;
DROP INDEX prescription_physicianno;
DROP INDEX prescriptiondrug_drugno;
DROP INDEX prescriptiondrug_prescriptionno;

DROP VIEW drugUsageVW;
DROP VIEW PhysCustListVW;


DROP TABLE Customer;
DROP TABLE Physician;
DROP TABLE Drug;
DROP TABLE physicianCustomer;
DROP TABLE prescription;
DROP TABLE appointment;
DROP TABLE prescriptionDrug;

CREATE TABLE Customer (
	custNo int4 PRIMARY KEY,
	surname varchar(30),
	firstname varchar(30),
	gender char(6) CHECK (gender IN ('Male', 'Female')),
	address varchar(60),
	phoneNo varchar(20),
	dob date,
	medicareNo varchar(12)
);

CREATE TABLE Physician (
  physicianNo varchar(4) PRIMARY KEY,  
 	surname varchar(30),  
 	firstname varchar(30),  
 	title varchar(4),
 	doctoRegoNo varchar(12),
 	specialty varchar(15),  
 	address varchar(60),  
 	phoneNo varchar(20)  
);

CREATE TABLE Drug (
	drugNo varchar(4) PRIMARY KEY,
	drugName varchar(20)
);

 
CREATE TABLE physicianCustomer (    
   physicianNo varchar(4) REFERENCES physician(physicianNo)    
      ON DELETE CASCADE    
      ON UPDATE CASCADE,    
   custNo int4 REFERENCES customer(custNo)     
       ON DELETE CASCADE    
       ON UPDATE CASCADE,    
   PRIMARY KEY (physicianNo, custNo)    
);


CREATE TABLE prescription (
   prescriptionNo int4 PRIMARY KEY,
   custNo int4 REFERENCES customer(custNo)
       ON UPDATE CASCADE
       ON DELETE CASCADE,
   physicianNo varchar(4) REFERENCES physician(physicianNo)  
      ON UPDATE CASCADE   
      ON DELETE RESTRICT,  
   dateIssued date
);

CREATE TABLE appointment (
	physicianNo varchar(4),
	custNo int4,
	appointmentDate date,
	location varchar(20),
	PRIMARY KEY (physicianNo, custNo, appointmentDate),
	CONSTRAINT appointmentFK FOREIGN KEY(physicianNo, custNo)
		REFERENCES physicianCustomer(physicianNo, custNo)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);


CREATE TABLE prescriptionDrug (
	prescriptionNo int4 REFERENCES prescription(prescriptionNo)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	drugNo varchar(4) REFERENCES drug(drugNo) 
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	qty int4 CHECK (qty BETWEEN 1 AND 150),
	charge float CHECK (charge >= 0),
	dosage varchar(50),
	PRIMARY KEY (prescriptionNo, drugNo)
);


GRANT ALL
ON customer, drug, prescription, physician, physiciancustomer, appointment, prescriptionDrug
TO jjcreek;

GRANT ALL 
ON customer, drug, prescription, physician, physiciancustomer, appointment, prescriptionDrug
TO jndonker;

REVOKE ALL 
ON customer, drug, prescription, physician, physiciancustomer, appointment, prescriptionDrug
FROM httpd;

CREATE INDEX phys_surname ON physician(surname);
CREATE INDEX cust_surname ON customer(surname);
CREATE INDEX drug_drugName ON drug(drugname);
CREATE INDEX cust_MedicareNo ON customer(medicareNo);
CREATE INDEX physCust_custNo ON physicianCustomer(custNo);
CREATE INDEX physCust_physicianNo ON physicianCustomer(physicianNo);
CREATE INDEX prescription_custNo ON prescription(custNo);
CREATE INDEX prescription_physicianNo ON prescription(physicianNo);
CREATE INDEX appointment_physicianNo ON appointment(physicianNo);
CREATE INDEX appointment_custNo ON appointment(custNo);
CREATE INDEX prescriptionDrug_prescriptionNo ON prescriptionDrug(prescriptionNo);
CREATE INDEX prescriptionDrug_drugNo ON prescriptionDrug(drugNo);

INSERT INTO physician VALUES ('L8', 'Tonkin', 'Angie', 'Dr', '763779', 'Sports Physician', '123 Easy St Bendigo Vic 3552', '(03) 5442 3762');
INSERT INTO physician VALUES ('R2', 'Creeky', 'Joshua', 'Dr', '37728', 'Brain Specalist', 'Unit 5 Room 6 LTUB Bendigo Vic 3552', '(03) 5442 3887');
INSERT INTO physician VALUES ('J3', 'Donker', 'Jonny', 'Dr', '73628', 'General', 'Unit 5 Room 6 LTUB Bendigo Vic 3552', '(03) 5442 3528');
INSERT INTO physician VALUES ('S3', 'Fields', 'Sally', 'Dr', '833822', 'General', '53 West Rd Bendigo Vic 3552', '(03) 5442 3906');
INSERT INTO physician VALUES ('G2', 'Smith', 'John', 'Dr', '94993', 'General', '4 Hayway Eaglehawk Vic 3554', '(03) 5444 9681');

INSERT INTO customer VALUES (1, 'Rose', 'Lindsay', 'Male', 'Unit 6 Room 1 LTUB Bendigo Vic 3552', '(03) 5442 6391', '12 Jun 1984','7322 42352 1' );
INSERT INTO customer VALUES (2, 'Spencer', 'Steven', 'Male', 'Unit 8 Room 8 LTUB Bendigo Vic 3552', '(03) 5442 6472', '16 Aug 1982','7381 32986 1' );
INSERT INTO customer VALUES (3, 'Gasko', 'Emily', 'Female', 'Unit 9 Room 12 LTUB Bendigo Vic 3552', '(03) 5442 2691', '27 Mar 1984','7748 48883 1' );
INSERT INTO customer VALUES (4, 'Trev', 'Dodgy', 'Male', 'Unit 3 Room 4 LTUB Bendigo Vic 3552', '(03) 5442 3821', '25 Jul 1984','7322 42352 1' );
INSERT INTO customer VALUES (234, 'Adams', 'Michael', 'Male', '53 Banks Rd Bendigo Vic 3552', '(03) 5442 6318', '23 Apr 1971','7392 48290 1' );

INSERT INTO drug VALUES ('L6', 'LINTUS TUSSINOL');
INSERT INTO drug VALUES ('T35', 'TELDANE');
INSERT INTO drug VALUES ('R1', 'RIDALINE');
INSERT INTO drug VALUES ('P1', 'PENADILNE');
INSERT INTO drug VALUES ('M4', 'Pain Killers - MORPHINE');

INSERT INTO prescription VALUES (1, 3, 'L8', '3 Jan 2004');
	INSERT INTO prescriptionDrug VALUES (1, 'R1', 20, 3.69, 'Take 1 Daily with meal');
	INSERT INTO prescriptionDrug VALUES (1, 'M4', 20, 23.95, 'Take 3 Daily with meals');

INSERT INTO prescription VALUES (2, 4, 'R2', '31 May 2004');
	INSERT INTO prescriptionDrug VALUES (2, 'P1', 40, 30.00, 'Take twice Daily');
	INSERT INTO prescriptionDrug VALUES (2, 'M4', 20, 23.95, 'Take 3 Daily with meals');
	INSERT INTO prescriptionDrug VALUES (2, 'T35', 10, 13.95, 'Take Daily in morning');
	INSERT INTO prescriptionDrug VALUES (2, 'L6', 25, 124.95, 'Take twice Daily');

INSERT INTO prescription VALUES (52, 234, 'S3', '15 Aug 2004');
	INSERT INTO prescriptionDrug VALUES (52, 'L6', 50, 5.93, 'TAKE 10ML BY MEASURE AT NIGHT');

INSERT INTO prescription VALUES (78, 234, 'G2', '20 Sep 2004');
	INSERT INTO prescriptionDrug VALUES (78, 'L6', 100, 9.95, 'GIVE 5MLS 3 TIMES A DAY');
	INSERT INTO prescriptionDrug VALUES (78, 'T35', 40, 25.66, 'TAKE 1 TABLET TWICE A DAY');


	
INSERT INTO physicianCustomer VALUES ('L8', 1);
	INSERT INTO appointment VALUES ('L8', 1, '4 Feb 2004', 'Medical Clinic');
	INSERT INTO appointment VALUES ('L8', 1, '12 Mar 2004', 'Home Visit');

INSERT INTO physicianCustomer VALUES ('L8', 2);
	INSERT INTO appointment VALUES ('L8', 2, '23 Apr 2004', 'Medical Clinic');
	INSERT INTO appointment VALUES ('L8', 2, '28 Apr 2004', 'Medical Clinic');

INSERT INTO physicianCustomer VALUES ('L8', 4);
	INSERT INTO appointment VALUES ('L8', 4, '15 Aug 2003', 'Home Visit');
	INSERT INTO appointment VALUES ('L8', 4, '2 Sep 2003', 'Medical Clinic');
INSERT INTO physicianCustomer VALUES ('L8', 234);
	INSERT INTO appointment VALUES ('L8', 234, '13 May 2004', 'Medical Clinic');
	INSERT INTO appointment VALUES ('L8', 234, '4 Jun 2004', 'Surgery');
INSERT INTO physicianCustomer VALUES ('S3', 234);
	INSERT INTO appointment VALUES ('S3', 234, '1 Feb 2004', 'Medical Clinic');
	INSERT INTO appointment VALUES ('S3', 234, '28 Feb 2004', 'Medical Clinic');
INSERT INTO physicianCustomer VALUES ('G2', 234);
	INSERT INTO appointment VALUES ('G2', 234, '23 Apr 2004', 'Medical Clinic');
INSERT INTO physicianCustomer VALUES ('J3', 1);
	INSERT INTO appointment VALUES ('J3', 1, '27 Jan 2004', 'Medical Clinic');
	INSERT INTO appointment VALUES ('J3', 1, '16 Feb 2004', 'Home Visit');
	INSERT INTO appointment VALUES ('J3', 1, '1  Apr 2004', 'Surgery');
	INSERT INTO appointment VALUES ('J3', 1, '14 Apr 2004', 'Medical Clinic');
INSERT INTO physicianCustomer VALUES ('J3', 3);
	INSERT INTO appointment VALUES ('J3', 3, '31 Dec 2003', 'Home Visit');
	INSERT INTO appointment VALUES ('J3', 3, '23 Mar 2004', 'Medical Clinic');
INSERT INTO physicianCustomer VALUES ('J3', 4);
	INSERT INTO appointment VALUES ('J3', 4, '29 Apr 2004', 'Surgery');
	INSERT INTO appointment VALUES ('J3', 4, '30 Apr 2004', 'Hospital Visit');
	INSERT INTO appointment VALUES ('J3', 4, '30 Jun 2004', 'Surgery');
INSERT INTO physicianCustomer VALUES ('R2', 1);
	INSERT INTO appointment VALUES ('R2', 1, '14 Aug 2004', 'Medical Clinic');
INSERT INTO physicianCustomer VALUES ('R2', 2);
	INSERT INTO appointment VALUES ('R2', 2, '20 May 2004', 'Surgery');
	INSERT INTO appointment VALUES ('R2', 2, '1 Jun 2004', 'Hospital Visit');
	INSERT INTO appointment VALUES ('R2', 2, '3 Jun 2004', 'Hospital Visit');
	INSERT INTO appointment VALUES ('R2', 2, '13 Jun 2004', 'Home Visit');
INSERT INTO physicianCustomer VALUES ('R2', 4);
	INSERT INTO appointment VALUES ('R2', 4, '15 Nov 2003', 'Medical Clinic');


SELECT *
from appointment
ORDER BY physicianNo, custNo;

SELECT *
FROM customer
ORDER BY custno;

SELECT *
FROM drug
ORDER BY drugNo;

select *
from prescription
order by prescriptionNo;

SELECT *
from prescriptiondrug
ORDER BY prescriptionno, drugno;

select *
from physician
ORDER BY physicianNo;

select *
from physiciancustomer
ORDER BY physicianNo, custNo;


CREATE VIEW drugUsageVW AS
SELECT c.custno, c.surname AS custsurname, c.firstname AS custfirstname, pre.prescriptionno, dateissued, title || ' ' || phys.firstname || ' ' || phys.surname AS docname, phys.physicianno, drugname, drug.drugno, qty, charge, dosage
From customer c, prescription pre, physician phys, drug, prescriptiondrug pd
WHERE pd.prescriptionno = pre.prescriptionno
AND pre.physicianno = phys.physicianno
AND pre.custno = c.custno
AND drug.drugno = pd.drugno;


CREATE VIEW PhysCustListVW AS
SELECT phys.physicianno, phys.surname || ', ' || title || ' ' || phys.firstname AS docname, specialty, doctoregono, phys.address AS docadd, phys.phoneno AS docph, cust.custno, cust.surname, cust.firstname, gender, medicareno, dob, cust.address AS custadd, cust.phoneno AS custph, appointmentdate, location
FROM physician phys, customer cust, physiciancustomer pc, appointment app
WHERE phys.physicianno = app.physicianno
AND phys.physicianno = pc.physicianno
AND cust.custno = pc.custno
AND app.custno = cust.custno;

GRANT SELECT ON drugusagevw TO retallic;
GRANT SELECT ON drugusagevw TO noel;
GRANT SELECT ON physcustlistvw TO retallic;
GRANT SELECT ON physcustlistvw TO noel;

GRANT ALL ON drugusagevw TO jndonker;
GRANT ALL ON drugusagevw TO jjcreek;
GRANT All ON physcustlistvw TO jndonker;
GRANT All ON physcustlistvw TO jjcreek;


SELECT * 
FROM prescription;

SELECT * 
FROM prescriptionDrug;

/*
	This is the Transaction for inserting a new prescription to the Database.
	It has to insert data into two separate tables, prescription and prescriptionDrug for the new prescription to be completely entered.
	
	TRY
		BEGIN
		INSERT INTO prescription VALUES (prescNo, custNo, physNo, date);
		
		FOR EACH drug that has to be inserted into prescNo
			INSERT INTO prescriptionDrug VALUES (prescNo, drugNo, qty, charge, dosage);
		END FOR
		COMMIT
	CATCH dbException AS Exception
		ROLLBACK
	END TRY
*/

BEGIN work;
	INSERT INTO prescription VALUES (93, 2, 'R2', '13 Jun 2004');
		INSERT INTO prescriptionDrug VALUES (93, 'P1', 70, 29.95, 'TWO TABLETS TWICE DAILY');
		INSERT INTO prescriptionDrug VALUES (93, 'T35', 50, 19.90, 'TAKE 1 TABLET FOUR TIMES A DAY');
COMMIT;

SELECT * 
FROM prescription;

SELECT * 
FROM prescriptionDrug;

SELECT * 
FROM drug;

SELECT * 
FROM prescriptiondrug;

/*
This is the transaction for inserting a new drug that is able to be prescribed and using it instead of one of the other drugs after a certain date.
It has to insert into the drug table and then update the prescription table based upon the set date for the changeover.
	TRY
		BEGIN
		INSERT INTO drug VALUES (drugNo, drugName);
		
		FOR EACH DRUG BEING REPLACED
			UPDATE prescriptiondrug 
				SET drugNo = 'drugNo' 
				WHERE drugNo = 'OLDdrugNo' AND prescriptionNo IN
				(SELECT prescriptionNo 
					FROM prescription 
					WHERE dateIssued > changeOverDate);
		END FOR
		
		COMMIT
	CATCH dbException AS Exception
		ROLLBACK
	END TRY
*/

BEGIN work;
	INSERT INTO drug VALUES ('A1', 'VALIUMN A');
	
	UPDATE prescriptiondrug 
	SET drugNo = 'A1' 
	WHERE drugNo = 'T35' AND prescriptionNo IN
	  (SELECT prescriptionNo 
		FROM prescription 
		WHERE dateIssued > '1 June 2004'); 
COMMIT;

SELECT * 
FROM drug;

SELECT * 
FROM prescriptiondrug;