-----------------------------------------------------
-- CLEANUP (allows script to run multiple times)
-----------------------------------------------------

BEGIN EXECUTE IMMEDIATE 'DROP TABLE prescription CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE office_visit CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE patient CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE doctor CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE drug CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE insurance CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/

-----------------------------------------------------
-- INSURANCE TABLE
-----------------------------------------------------

CREATE TABLE insurance (
    insurance_company_id NUMBER PRIMARY KEY,
    insurance_company VARCHAR2(100),
    phone VARCHAR2(20)
);

-----------------------------------------------------
-- DOCTOR TABLE
-----------------------------------------------------

CREATE TABLE doctor (
    doctor_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    specialty VARCHAR2(100),
    phone VARCHAR2(20)
);

-----------------------------------------------------
-- PATIENT TABLE
-----------------------------------------------------

CREATE TABLE patient (
    patient_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    dob DATE,
    phone VARCHAR2(20),
    email VARCHAR2(100),
    insurance_company_id NUMBER,
    policy_holder_id NUMBER,

    CONSTRAINT fk_patient_insurance
        FOREIGN KEY (insurance_company_id)
        REFERENCES insurance(insurance_company_id),

    CONSTRAINT fk_policy_holder
        FOREIGN KEY (policy_holder_id)
        REFERENCES patient(patient_id)
);

-----------------------------------------------------
-- DRUG TABLE
-----------------------------------------------------

CREATE TABLE drug (
    drug_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    description VARCHAR2(200)
);

-----------------------------------------------------
-- OFFICE VISIT TABLE
-----------------------------------------------------

CREATE TABLE office_visit (
    visit_id NUMBER PRIMARY KEY,
    patient_id NUMBER,
    doctor_id NUMBER,
    date_of_visit DATE,
    reason VARCHAR2(200),

    CONSTRAINT fk_visit_patient
        FOREIGN KEY (patient_id)
        REFERENCES patient(patient_id),

    CONSTRAINT fk_visit_doctor
        FOREIGN KEY (doctor_id)
        REFERENCES doctor(doctor_id)
);

-----------------------------------------------------
-- PRESCRIPTION TABLE
-----------------------------------------------------

CREATE TABLE prescription (
    prescription_id NUMBER PRIMARY KEY,
    patient_id NUMBER,
    doctor_id NUMBER,
    drug_id NUMBER,
    date_prescribed DATE,
    dosage VARCHAR2(50),
    is_active CHAR(1) DEFAULT 'Y',

    CONSTRAINT fk_rx_patient
        FOREIGN KEY (patient_id)
        REFERENCES patient(patient_id),

    CONSTRAINT fk_rx_doctor
        FOREIGN KEY (doctor_id)
        REFERENCES doctor(doctor_id),

    CONSTRAINT fk_rx_drug
        FOREIGN KEY (drug_id)
        REFERENCES drug(drug_id)
);

-----------------------------------------------------
-- INSERT INSURANCE DATA
-----------------------------------------------------

INSERT INTO insurance VALUES (1,'Blue Cross','800-555-1010');
INSERT INTO insurance VALUES (2,'United Healthcare','800-555-2020');
INSERT INTO insurance VALUES (3,'Aetna','800-555-3030');

-----------------------------------------------------
-- INSERT DOCTOR DATA
-----------------------------------------------------

INSERT INTO doctor VALUES (1,'Dr. Sarah Smith','Cardiology','814-555-1111');
INSERT INTO doctor VALUES (2,'Dr. Michael Lee','Family Medicine','814-555-2222');
INSERT INTO doctor VALUES (3,'Dr. Emily Chen','Dermatology','814-555-3333');

-----------------------------------------------------
-- INSERT PATIENT DATA
-----------------------------------------------------

INSERT INTO patient VALUES (1,'John Miller',DATE '1985-04-12','814-555-1110','john@email.com',1,NULL);
INSERT INTO patient VALUES (2,'Lisa Miller',DATE '1987-07-22','814-555-1111','lisa@email.com',1,1);
INSERT INTO patient VALUES (3,'David Brown',DATE '1975-01-10','814-555-2222','david@email.com',2,NULL);
INSERT INTO patient VALUES (4,'Maria Garcia',DATE '1992-03-05','814-555-3333','maria@email.com',3,NULL);
INSERT INTO patient VALUES (5,'Kevin Patel',DATE '1990-11-19','814-555-4444','kevin@email.com',NULL,NULL);

-----------------------------------------------------
-- INSERT DRUG DATA
-----------------------------------------------------

INSERT INTO drug VALUES (1,'Amoxicillin','Antibiotic');
INSERT INTO drug VALUES (2,'Ibuprofen','Pain reliever');
INSERT INTO drug VALUES (3,'Atorvastatin','Cholesterol medication');

-----------------------------------------------------
-- INSERT OFFICE VISITS
-----------------------------------------------------

INSERT INTO office_visit VALUES (1,1,2,DATE '2025-01-10','Routine checkup');
INSERT INTO office_visit VALUES (2,3,1,DATE '2025-02-15','Heart evaluation');
INSERT INTO office_visit VALUES (3,4,3,DATE '2025-02-20','Skin rash');
INSERT INTO office_visit VALUES (4,2,2,DATE '2025-03-01','Flu symptoms');

-----------------------------------------------------
-- INSERT PRESCRIPTIONS
-----------------------------------------------------

INSERT INTO prescription VALUES (1,1,2,2,DATE '2025-01-10','200mg','Y');
INSERT INTO prescription VALUES (2,3,1,3,DATE '2025-02-15','10mg','Y');
INSERT INTO prescription VALUES (3,4,3,1,DATE '2025-02-20','500mg','Y');
INSERT INTO prescription VALUES (4,2,2,1,DATE '2025-03-01','500mg','Y');

COMMIT;