-- =====================================================
-- HealthOneDB — MySQL / phpMyAdmin Version
-- Converted from Oracle APEX SQL
-- GMBA 612: Database Design & SQL
-- =====================================================

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

DROP TABLE IF EXISTS prescription;
DROP TABLE IF EXISTS office_visit;
DROP TABLE IF EXISTS patient;
DROP TABLE IF EXISTS doctor;
DROP TABLE IF EXISTS drug;
DROP TABLE IF EXISTS insurance;

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

CREATE TABLE insurance (
    insurance_company_id INT PRIMARY KEY,
    insurance_company VARCHAR(100),
    phone VARCHAR(20)
);

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

CREATE TABLE doctor (
    doctor_id INT PRIMARY KEY,
    name VARCHAR(100),
    specialty VARCHAR(100),
    phone VARCHAR(20)
);

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

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

    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 INT PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(200)
);

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

CREATE TABLE office_visit (
    visit_id INT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    date_of_visit DATE,
    reason VARCHAR(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 INT PRIMARY KEY,
    patient_id INT,
    doctor_id INT,
    drug_id INT,
    date_prescribed DATE,
    dosage VARCHAR(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', '1985-04-12', '814-555-1110', 'john@email.com', 1, NULL);
INSERT INTO patient VALUES (2, 'Lisa Miller', '1987-07-22', '814-555-1111', 'lisa@email.com', 1, 1);
INSERT INTO patient VALUES (3, 'David Brown', '1975-01-10', '814-555-2222', 'david@email.com', 2, NULL);
INSERT INTO patient VALUES (4, 'Maria Garcia', '1992-03-05', '814-555-3333', 'maria@email.com', 3, NULL);
INSERT INTO patient VALUES (5, 'Kevin Patel', '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, '2025-01-10', 'Routine checkup');
INSERT INTO office_visit VALUES (2, 3, 1, '2025-02-15', 'Heart evaluation');
INSERT INTO office_visit VALUES (3, 4, 3, '2025-02-20', 'Skin rash');
INSERT INTO office_visit VALUES (4, 2, 2, '2025-03-01', 'Flu symptoms');

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

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