-- ============================================================
-- GMBA 612: Database Design & SQL
-- Week 6: Prescription + Insurance Database
-- Platform: Oracle APEX
-- ============================================================
-- This script creates the complete normalized (3NF) database
-- for the prescription + insurance scenario.
--
-- NAMING CONVENTION: All tables prefixed with "w6_"
-- ============================================================


-- ============================================================
-- STEP 1: DROP TABLES (in reverse dependency order)
-- ============================================================
-- We must drop tables in REVERSE order of creation.
-- Why? Because you can't drop a table that another table
-- depends on. If Patient has children (Prescription,
-- PatientInsurance), Oracle will refuse to drop it unless
-- we drop the children first.
--
-- Think of it like unstacking blocks:
--   Built bottom-up --> Torn down top-down
--
-- Drop order: children first, then parents.
-- ============================================================

-- Level 3: Tables with the most dependencies (grandchildren)
-- w6_prescription_item depends on w6_prescription AND w6_medication
BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_prescription_item CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

-- Level 2: Tables that reference strong entities (children)
-- w6_prescription depends on w6_patient, w6_doctor, w6_insurance
BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_prescription CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

-- w6_patient_insurance depends on w6_patient AND w6_insurance
BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_patient_insurance CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

-- Level 1: Strong entities (parents) - no dependencies on other tables
BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_patient CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_doctor CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_insurance CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/

BEGIN EXECUTE IMMEDIATE 'DROP TABLE w6_medication CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/


-- ============================================================
-- STEP 2: CREATE STRONG ENTITIES (independent tables)
-- ============================================================
-- Strong entities are created FIRST because they don't depend
-- on any other table. They have their own primary key and
-- exist independently in the real world.
--
-- A Patient exists whether or not they have a prescription.
-- A Doctor exists whether or not they've written one.
-- An Insurance company exists whether or not anyone is enrolled.
-- A Medication exists whether or not it's been prescribed.
--
-- These are the "parent" tables. Everything else references them.
-- ============================================================


-- ----------------------------------------
-- PATIENT (Strong Entity)
-- ----------------------------------------
-- A patient exists independently. No foreign keys needed.
-- This is a parent table that will be referenced by
-- w6_prescription and w6_patient_insurance.
-- ----------------------------------------
CREATE TABLE w6_patient (
    patient_id      VARCHAR2(10)    PRIMARY KEY,
    patient_name    VARCHAR2(100)   NOT NULL,
    date_of_birth   DATE            NOT NULL,
    phone           VARCHAR2(15),
    street          VARCHAR2(100),
    city            VARCHAR2(50),
    state           CHAR(2),
    zip_code        VARCHAR2(10)
);


-- ----------------------------------------
-- DOCTOR (Strong Entity)
-- ----------------------------------------
-- A doctor exists independently. No foreign keys needed.
-- DEA number is unique — no two doctors share the same
-- license to prescribe controlled substances.
-- This is a parent table referenced by w6_prescription.
-- ----------------------------------------
CREATE TABLE w6_doctor (
    doctor_id       VARCHAR2(10)    PRIMARY KEY,
    doctor_name     VARCHAR2(100)   NOT NULL,
    dea_number      VARCHAR2(20)    NOT NULL UNIQUE,
    specialty       VARCHAR2(50),
    phone           VARCHAR2(15)
);


-- ----------------------------------------
-- INSURANCE (Strong Entity)
-- ----------------------------------------
-- An insurance company exists independently. No foreign keys.
-- This is a parent table referenced by w6_prescription
-- and w6_patient_insurance.
-- ----------------------------------------
CREATE TABLE w6_insurance (
    insurance_id    VARCHAR2(10)    PRIMARY KEY,
    company_name    VARCHAR2(100)   NOT NULL,
    coverage_type   VARCHAR2(20)    NOT NULL
        CHECK (coverage_type IN ('PPO','HMO','Medicaid','Medicare','Tricare')),
    contact_phone   VARCHAR2(15),
    website         VARCHAR2(100)
);


-- ----------------------------------------
-- MEDICATION (Strong Entity)
-- ----------------------------------------
-- A medication exists independently. No foreign keys.
-- This is a parent table referenced by w6_prescription_item.
-- ----------------------------------------
CREATE TABLE w6_medication (
    medication_id   VARCHAR2(10)    PRIMARY KEY,
    medication_name VARCHAR2(100)   NOT NULL,
    dosage          VARCHAR2(30)    NOT NULL,
    form            VARCHAR2(20)    DEFAULT 'Tablet'
        CHECK (form IN ('Tablet','Capsule','Inhaler','Injection','Liquid','Patch','Cream')),
    manufacturer    VARCHAR2(100)
);


-- ============================================================
-- STEP 3: CREATE TABLES WITH FOREIGN KEYS
-- ============================================================
-- These tables CANNOT be created before the strong entities
-- because they contain FOREIGN KEY constraints that REFERENCE
-- the strong entity tables.
--
-- If we tried to create w6_prescription before w6_patient,
-- Oracle would throw an error:
--   ORA-00942: table or view does not exist
--
-- The foreign key says "this column must point to a real
-- record in another table." That other table must exist first.
--
-- Rule: Parent tables FIRST, child tables AFTER.
-- ============================================================


-- ----------------------------------------
-- PATIENT_INSURANCE (Bridge Table)
-- ----------------------------------------
-- This resolves the many-to-many relationship between
-- Patient and Insurance:
--   - One patient can have multiple insurance plans
--   - One insurance company covers many patients
--
-- WHY foreign keys must come after strong entities:
--   patient_id REFERENCES w6_patient  --> w6_patient must exist
--   insurance_id REFERENCES w6_insurance --> w6_insurance must exist
--
-- The composite primary key (patient_id + insurance_id)
-- ensures each patient-insurance combination is unique.
--
-- Policy number, group number, and copay belong HERE because
-- they describe the specific relationship between THIS patient
-- and THIS insurer — not the patient alone or the insurer alone.
-- ----------------------------------------
CREATE TABLE w6_patient_insurance (
    patient_id      VARCHAR2(10)    NOT NULL
        REFERENCES w6_patient(patient_id),
    insurance_id    VARCHAR2(10)    NOT NULL
        REFERENCES w6_insurance(insurance_id),
    policy_number   VARCHAR2(20)    NOT NULL,
    group_number    VARCHAR2(20),
    copay           NUMBER(6,2)     DEFAULT 0
        CHECK (copay >= 0),
    --
    PRIMARY KEY (patient_id, insurance_id)
);


-- ----------------------------------------
-- PRESCRIPTION (Weak Entity)
-- ----------------------------------------
-- A prescription CANNOT exist without a patient, a doctor,
-- and an insurance plan. It depends on three strong entities.
--
-- WHY foreign keys must come after strong entities:
--   patient_id REFERENCES w6_patient     --> w6_patient must exist
--   doctor_id REFERENCES w6_doctor       --> w6_doctor must exist
--   insurance_id REFERENCES w6_insurance --> w6_insurance must exist
--
-- If you try to INSERT a prescription for patient_id 'P999'
-- and that patient doesn't exist in w6_patient, Oracle will
-- reject it: ORA-02291: integrity constraint violated.
--
-- This is referential integrity in action — no orphan records.
-- ----------------------------------------
CREATE TABLE w6_prescription (
    rx_number       VARCHAR2(15)    PRIMARY KEY,
    rx_date         DATE            NOT NULL,
    patient_id      VARCHAR2(10)    NOT NULL
        REFERENCES w6_patient(patient_id),
    doctor_id       VARCHAR2(10)    NOT NULL
        REFERENCES w6_doctor(doctor_id),
    insurance_id    VARCHAR2(10)    NOT NULL
        REFERENCES w6_insurance(insurance_id),
    status          VARCHAR2(20)    DEFAULT 'Active'
        CHECK (status IN ('Active','Completed','Cancelled','Expired'))
);


-- ----------------------------------------
-- PRESCRIPTION_ITEM (Bridge Table / Weakest Entity)
-- ----------------------------------------
-- This resolves the many-to-many relationship between
-- Prescription and Medication:
--   - One prescription can contain multiple medications
--   - One medication can appear on multiple prescriptions
--
-- WHY this table is created LAST:
--   rx_number REFERENCES w6_prescription   --> w6_prescription must exist
--   medication_id REFERENCES w6_medication --> w6_medication must exist
--
-- w6_prescription itself depends on w6_patient, w6_doctor,
-- and w6_insurance — so this table sits at the very bottom
-- of the dependency chain.
--
-- Qty, refills, and instructions depend on the COMBINATION
-- of which prescription and which drug — not on either alone.
-- That's why they live here, not in Prescription or Medication.
-- ----------------------------------------
CREATE TABLE w6_prescription_item (
    rx_number       VARCHAR2(15)    NOT NULL
        REFERENCES w6_prescription(rx_number),
    medication_id   VARCHAR2(10)    NOT NULL
        REFERENCES w6_medication(medication_id),
    quantity        NUMBER(5)       NOT NULL
        CHECK (quantity > 0),
    refills         NUMBER(2)       DEFAULT 0
        CHECK (refills BETWEEN 0 AND 12),
    instructions    VARCHAR2(200),
    --
    PRIMARY KEY (rx_number, medication_id)
);


-- ============================================================
-- STEP 4: INSERT TEST DATA
-- ============================================================
-- Data must be inserted in the SAME ORDER as table creation:
-- strong entities first, then children, then grandchildren.
--
-- You can't insert a prescription for a patient who doesn't
-- exist yet. Referential integrity enforces this at all times.
-- ============================================================


-- ----------------------------------------
-- PATIENT DATA (20 patients)
-- ----------------------------------------
INSERT INTO w6_patient VALUES ('P001', 'Maria Lopez',       TO_DATE('03/22/1985','MM/DD/YYYY'), '(814) 555-0123', '450 Peach St',      'Erie',        'PA', '16501');
INSERT INTO w6_patient VALUES ('P002', 'James Wilson',      TO_DATE('11/05/1972','MM/DD/YYYY'), '(814) 555-0456', '120 State St',      'Erie',        'PA', '16502');
INSERT INTO w6_patient VALUES ('P003', 'Sarah Chen',        TO_DATE('07/14/1990','MM/DD/YYYY'), '(814) 555-0789', '88 Maple Dr',       'Erie',        'PA', '16503');
INSERT INTO w6_patient VALUES ('P004', 'Robert Thompson',   TO_DATE('01/30/1968','MM/DD/YYYY'), '(814) 555-1012', '305 Oak Ave',       'Erie',        'PA', '16504');
INSERT INTO w6_patient VALUES ('P005', 'Jennifer Garcia',   TO_DATE('09/18/1995','MM/DD/YYYY'), '(814) 555-1345', '77 Elm St',         'Meadville',   'PA', '16335');
INSERT INTO w6_patient VALUES ('P006', 'Michael Brown',     TO_DATE('12/03/1980','MM/DD/YYYY'), '(814) 555-1678', '412 Cedar Ln',      'Edinboro',    'PA', '16412');
INSERT INTO w6_patient VALUES ('P007', 'Emily Davis',       TO_DATE('05/22/1988','MM/DD/YYYY'), '(814) 555-1901', '190 Pine St',       'Erie',        'PA', '16505');
INSERT INTO w6_patient VALUES ('P008', 'David Martinez',    TO_DATE('08/11/1975','MM/DD/YYYY'), '(814) 555-2234', '555 Walnut Ave',    'Corry',       'PA', '16407');
INSERT INTO w6_patient VALUES ('P009', 'Lisa Anderson',     TO_DATE('04/07/1992','MM/DD/YYYY'), '(814) 555-2567', '23 Birch Rd',       'Erie',        'PA', '16506');
INSERT INTO w6_patient VALUES ('P010', 'Christopher Lee',   TO_DATE('06/25/1963','MM/DD/YYYY'), '(814) 555-2890', '701 River Dr',      'Fairview',    'PA', '16415');
INSERT INTO w6_patient VALUES ('P011', 'Amanda Taylor',     TO_DATE('10/15/1987','MM/DD/YYYY'), '(814) 555-3123', '88 Highland Ave',   'Erie',        'PA', '16507');
INSERT INTO w6_patient VALUES ('P012', 'Daniel Harris',     TO_DATE('02/28/1970','MM/DD/YYYY'), '(814) 555-3456', '340 Market St',     'Girard',      'PA', '16417');
INSERT INTO w6_patient VALUES ('P013', 'Jessica White',     TO_DATE('11/20/1993','MM/DD/YYYY'), '(814) 555-3789', '15 Lake Rd',        'Erie',        'PA', '16508');
INSERT INTO w6_patient VALUES ('P014', 'Matthew Clark',     TO_DATE('03/09/1982','MM/DD/YYYY'), '(814) 555-4012', '620 Broadway',      'North East',  'PA', '16428');
INSERT INTO w6_patient VALUES ('P015', 'Ashley Robinson',   TO_DATE('07/01/1997','MM/DD/YYYY'), '(814) 555-4345', '42 Park Ave',       'Erie',        'PA', '16501');
INSERT INTO w6_patient VALUES ('P016', 'Kevin Walker',      TO_DATE('09/14/1974','MM/DD/YYYY'), '(814) 555-4678', '180 Church St',     'Waterford',   'PA', '16441');
INSERT INTO w6_patient VALUES ('P017', 'Stephanie King',    TO_DATE('12/22/1989','MM/DD/YYYY'), '(814) 555-4901', '95 Spring St',      'Erie',        'PA', '16509');
INSERT INTO w6_patient VALUES ('P018', 'Brian Scott',       TO_DATE('04/17/1965','MM/DD/YYYY'), '(814) 555-5234', '730 Forest Ave',    'Union City',  'PA', '16438');
INSERT INTO w6_patient VALUES ('P019', 'Nicole Adams',      TO_DATE('08/30/1991','MM/DD/YYYY'), '(814) 555-5567', '50 Sunset Blvd',    'Erie',        'PA', '16510');
INSERT INTO w6_patient VALUES ('P020', 'William Nguyen',    TO_DATE('01/12/1978','MM/DD/YYYY'), '(814) 555-5890', '275 Washington St', 'Harborcreek', 'PA', '16421');


-- ----------------------------------------
-- DOCTOR DATA (10 doctors)
-- ----------------------------------------
INSERT INTO w6_doctor VALUES ('D001', 'Dr. Angela Chen',     'AC1234563', 'Internal Medicine',  '(814) 555-9000');
INSERT INTO w6_doctor VALUES ('D002', 'Dr. Raj Patel',       'BP7891234', 'Family Medicine',    '(814) 555-9100');
INSERT INTO w6_doctor VALUES ('D003', 'Dr. Susan Miller',    'SM4567890', 'Cardiology',         '(814) 555-9200');
INSERT INTO w6_doctor VALUES ('D004', 'Dr. Marcus Johnson',  'MJ2345678', 'Endocrinology',      '(814) 555-9300');
INSERT INTO w6_doctor VALUES ('D005', 'Dr. Karen Williams',  'KW8901234', 'Psychiatry',         '(814) 555-9400');
INSERT INTO w6_doctor VALUES ('D006', 'Dr. David Park',      'DP3456789', 'Pulmonology',        '(814) 555-9500');
INSERT INTO w6_doctor VALUES ('D007', 'Dr. Maria Santos',    'MS6789012', 'Dermatology',        '(814) 555-9600');
INSERT INTO w6_doctor VALUES ('D008', 'Dr. James O''Brien',  'JO1234567', 'Orthopedics',        '(814) 555-9700');
INSERT INTO w6_doctor VALUES ('D009', 'Dr. Linda Kim',       'LK5678901', 'Gastroenterology',   '(814) 555-9800');
INSERT INTO w6_doctor VALUES ('D010', 'Dr. Ahmed Hassan',    'AH9012345', 'Neurology',          '(814) 555-9900');


-- ----------------------------------------
-- INSURANCE DATA (8 plans)
-- ----------------------------------------
INSERT INTO w6_insurance VALUES ('INS-001', 'BlueCross BlueShield PA', 'PPO',      '(800) 555-2583', 'www.bcbspa.com');
INSERT INTO w6_insurance VALUES ('INS-002', 'BlueCross BlueShield PA', 'HMO',      '(800) 555-2583', 'www.bcbspa.com');
INSERT INTO w6_insurance VALUES ('INS-003', 'Aetna Health',            'PPO',      '(800) 555-3264', 'www.aetna.com');
INSERT INTO w6_insurance VALUES ('INS-004', 'UnitedHealthcare',        'PPO',      '(800) 555-4195', 'www.uhc.com');
INSERT INTO w6_insurance VALUES ('INS-005', 'UnitedHealthcare',        'HMO',      '(800) 555-4195', 'www.uhc.com');
INSERT INTO w6_insurance VALUES ('INS-006', 'UPMC Health Plan',        'PPO',      '(800) 555-5826', 'www.upmchealthplan.com');
INSERT INTO w6_insurance VALUES ('INS-007', 'Highmark',                'HMO',      '(800) 555-6457', 'www.highmark.com');
INSERT INTO w6_insurance VALUES ('INS-008', 'Medicare',                'Medicare', '(800) 555-7088', 'www.medicare.gov');


-- ----------------------------------------
-- MEDICATION DATA (20 medications)
-- ----------------------------------------
INSERT INTO w6_medication VALUES ('M001', 'Lisinopril',          '10mg',  'Tablet',  'Lupin Pharmaceuticals');
INSERT INTO w6_medication VALUES ('M002', 'Lisinopril',          '20mg',  'Tablet',  'Lupin Pharmaceuticals');
INSERT INTO w6_medication VALUES ('M003', 'Metformin',           '500mg', 'Tablet',  'Teva Pharmaceutical');
INSERT INTO w6_medication VALUES ('M004', 'Metformin',           '1000mg','Tablet',  'Teva Pharmaceutical');
INSERT INTO w6_medication VALUES ('M005', 'Atorvastatin',        '20mg',  'Tablet',  'Pfizer');
INSERT INTO w6_medication VALUES ('M006', 'Atorvastatin',        '40mg',  'Tablet',  'Pfizer');
INSERT INTO w6_medication VALUES ('M007', 'Amlodipine',          '5mg',   'Tablet',  'Pfizer');
INSERT INTO w6_medication VALUES ('M008', 'Amlodipine',          '10mg',  'Tablet',  'Pfizer');
INSERT INTO w6_medication VALUES ('M009', 'Metoprolol',          '50mg',  'Tablet',  'AstraZeneca');
INSERT INTO w6_medication VALUES ('M010', 'Omeprazole',          '20mg',  'Capsule', 'AstraZeneca');
INSERT INTO w6_medication VALUES ('M011', 'Losartan',            '50mg',  'Tablet',  'Merck');
INSERT INTO w6_medication VALUES ('M012', 'Amoxicillin',         '500mg', 'Capsule', 'GlaxoSmithKline');
INSERT INTO w6_medication VALUES ('M013', 'Azithromycin',        '250mg', 'Tablet',  'Pfizer');
INSERT INTO w6_medication VALUES ('M014', 'Gabapentin',          '300mg', 'Capsule', 'Greenstone');
INSERT INTO w6_medication VALUES ('M015', 'Levothyroxine',       '50mcg', 'Tablet',  'AbbVie');
INSERT INTO w6_medication VALUES ('M016', 'Sertraline',          '50mg',  'Tablet',  'Pfizer');
INSERT INTO w6_medication VALUES ('M017', 'Fluoxetine',          '20mg',  'Capsule', 'Eli Lilly');
INSERT INTO w6_medication VALUES ('M018', 'Prednisone',          '10mg',  'Tablet',  'Hikma Pharmaceuticals');
INSERT INTO w6_medication VALUES ('M019', 'Albuterol',           '90mcg', 'Inhaler', 'Teva Pharmaceutical');
INSERT INTO w6_medication VALUES ('M020', 'Hydrochlorothiazide', '25mg',  'Tablet',  'Aurobindo Pharma');


-- ----------------------------------------
-- PATIENT_INSURANCE DATA
-- ----------------------------------------
-- Each row represents one patient's enrollment in one plan.
-- Some patients have multiple insurance plans (e.g., P001 has
-- both a primary PPO and Medicare as secondary).
-- ----------------------------------------
INSERT INTO w6_patient_insurance VALUES ('P001', 'INS-001', 'BC-90421',  'GRP-5500', 15.00);
INSERT INTO w6_patient_insurance VALUES ('P002', 'INS-003', 'AE-77130',  'GRP-8800', 25.00);
INSERT INTO w6_patient_insurance VALUES ('P003', 'INS-004', 'UH-44210',  'GRP-3300', 20.00);
INSERT INTO w6_patient_insurance VALUES ('P004', 'INS-006', 'UP-88315',  'GRP-1100', 30.00);
INSERT INTO w6_patient_insurance VALUES ('P004', 'INS-008', 'MC-55120',  NULL,        0.00);
INSERT INTO w6_patient_insurance VALUES ('P005', 'INS-001', 'BC-61034',  'GRP-5500', 15.00);
INSERT INTO w6_patient_insurance VALUES ('P006', 'INS-005', 'UH-93201',  'GRP-7700', 20.00);
INSERT INTO w6_patient_insurance VALUES ('P007', 'INS-007', 'HM-12045',  'GRP-4400', 25.00);
INSERT INTO w6_patient_insurance VALUES ('P008', 'INS-002', 'BC-33762',  'GRP-5500', 10.00);
INSERT INTO w6_patient_insurance VALUES ('P009', 'INS-001', 'BC-45098',  'GRP-2200', 15.00);
INSERT INTO w6_patient_insurance VALUES ('P010', 'INS-008', 'MC-70215',  NULL,        0.00);
INSERT INTO w6_patient_insurance VALUES ('P010', 'INS-006', 'UP-22104',  'GRP-1100', 30.00);
INSERT INTO w6_patient_insurance VALUES ('P011', 'INS-003', 'AE-50983',  'GRP-8800', 25.00);
INSERT INTO w6_patient_insurance VALUES ('P012', 'INS-004', 'UH-18456',  'GRP-6600', 20.00);
INSERT INTO w6_patient_insurance VALUES ('P013', 'INS-007', 'HM-62310',  'GRP-4400', 25.00);
INSERT INTO w6_patient_insurance VALUES ('P014', 'INS-001', 'BC-80175',  'GRP-9900', 15.00);
INSERT INTO w6_patient_insurance VALUES ('P015', 'INS-005', 'UH-71024',  'GRP-3300', 20.00);
INSERT INTO w6_patient_insurance VALUES ('P016', 'INS-006', 'UP-40583',  'GRP-1100', 30.00);
INSERT INTO w6_patient_insurance VALUES ('P017', 'INS-002', 'BC-92641',  'GRP-5500', 10.00);
INSERT INTO w6_patient_insurance VALUES ('P018', 'INS-008', 'MC-31098',  NULL,        0.00);
INSERT INTO w6_patient_insurance VALUES ('P019', 'INS-003', 'AE-25417',  'GRP-8800', 25.00);
INSERT INTO w6_patient_insurance VALUES ('P020', 'INS-004', 'UH-63089',  'GRP-6600', 20.00);
INSERT INTO w6_patient_insurance VALUES ('P001', 'INS-008', 'MC-11234',  NULL,        0.00);


-- ----------------------------------------
-- PRESCRIPTION DATA (30 prescriptions)
-- ----------------------------------------
-- Each prescription references a patient, a doctor, and
-- the insurance plan used for billing.
--
-- Notice: we can only insert these AFTER the patient, doctor,
-- and insurance records exist. Try inserting a prescription
-- for patient 'P999' — Oracle will reject it because P999
-- doesn't exist in w6_patient. That's referential integrity.
-- ----------------------------------------
INSERT INTO w6_prescription VALUES ('RX-10001', TO_DATE('01/20/2025','MM/DD/YYYY'), 'P001', 'D001', 'INS-001', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10002', TO_DATE('01/22/2025','MM/DD/YYYY'), 'P002', 'D002', 'INS-003', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10003', TO_DATE('01/25/2025','MM/DD/YYYY'), 'P001', 'D002', 'INS-001', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10004', TO_DATE('01/26/2025','MM/DD/YYYY'), 'P003', 'D001', 'INS-004', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10005', TO_DATE('01/28/2025','MM/DD/YYYY'), 'P004', 'D003', 'INS-006', 'Completed');
INSERT INTO w6_prescription VALUES ('RX-10006', TO_DATE('01/29/2025','MM/DD/YYYY'), 'P005', 'D002', 'INS-001', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10007', TO_DATE('02/01/2025','MM/DD/YYYY'), 'P006', 'D004', 'INS-005', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10008', TO_DATE('02/02/2025','MM/DD/YYYY'), 'P007', 'D005', 'INS-007', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10009', TO_DATE('02/03/2025','MM/DD/YYYY'), 'P008', 'D001', 'INS-002', 'Completed');
INSERT INTO w6_prescription VALUES ('RX-10010', TO_DATE('02/05/2025','MM/DD/YYYY'), 'P009', 'D006', 'INS-001', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10011', TO_DATE('02/06/2025','MM/DD/YYYY'), 'P010', 'D003', 'INS-008', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10012', TO_DATE('02/07/2025','MM/DD/YYYY'), 'P011', 'D002', 'INS-003', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10013', TO_DATE('02/08/2025','MM/DD/YYYY'), 'P012', 'D007', 'INS-004', 'Cancelled');
INSERT INTO w6_prescription VALUES ('RX-10014', TO_DATE('02/10/2025','MM/DD/YYYY'), 'P013', 'D005', 'INS-007', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10015', TO_DATE('02/11/2025','MM/DD/YYYY'), 'P014', 'D008', 'INS-001', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10016', TO_DATE('02/12/2025','MM/DD/YYYY'), 'P015', 'D002', 'INS-005', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10017', TO_DATE('02/14/2025','MM/DD/YYYY'), 'P016', 'D009', 'INS-006', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10018', TO_DATE('02/15/2025','MM/DD/YYYY'), 'P017', 'D001', 'INS-002', 'Expired');
INSERT INTO w6_prescription VALUES ('RX-10019', TO_DATE('02/17/2025','MM/DD/YYYY'), 'P018', 'D010', 'INS-008', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10020', TO_DATE('02/18/2025','MM/DD/YYYY'), 'P019', 'D004', 'INS-003', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10021', TO_DATE('02/20/2025','MM/DD/YYYY'), 'P020', 'D002', 'INS-004', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10022', TO_DATE('02/21/2025','MM/DD/YYYY'), 'P001', 'D003', 'INS-001', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10023', TO_DATE('02/22/2025','MM/DD/YYYY'), 'P004', 'D004', 'INS-008', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10024', TO_DATE('02/24/2025','MM/DD/YYYY'), 'P006', 'D001', 'INS-005', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10025', TO_DATE('02/25/2025','MM/DD/YYYY'), 'P009', 'D006', 'INS-001', 'Completed');
INSERT INTO w6_prescription VALUES ('RX-10026', TO_DATE('02/26/2025','MM/DD/YYYY'), 'P002', 'D002', 'INS-003', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10027', TO_DATE('02/27/2025','MM/DD/YYYY'), 'P013', 'D009', 'INS-007', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10028', TO_DATE('02/28/2025','MM/DD/YYYY'), 'P007', 'D005', 'INS-007', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10029', TO_DATE('03/01/2025','MM/DD/YYYY'), 'P015', 'D002', 'INS-005', 'Active');
INSERT INTO w6_prescription VALUES ('RX-10030', TO_DATE('03/02/2025','MM/DD/YYYY'), 'P010', 'D003', 'INS-006', 'Active');


-- ----------------------------------------
-- PRESCRIPTION_ITEM DATA (55 items)
-- ----------------------------------------
-- This is the LAST table to receive data because it depends
-- on both w6_prescription (which itself depends on patient,
-- doctor, and insurance) and w6_medication.
--
-- Each row = one medication on one prescription.
-- Quantity, refills, and instructions are specific to THIS
-- drug on THIS prescription — that's why they live here.
-- ----------------------------------------

-- RX-10001: Maria Lopez - Internal Medicine (Dr. Chen)
INSERT INTO w6_prescription_item VALUES ('RX-10001', 'M001', 30, 3,  'Take once daily in the morning');
INSERT INTO w6_prescription_item VALUES ('RX-10001', 'M003', 60, 5,  'Take twice daily with meals');

-- RX-10002: James Wilson - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10002', 'M012', 21, 0,  'Take 3 times daily for 7 days');

-- RX-10003: Maria Lopez - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10003', 'M005', 30, 6,  'Take once daily at bedtime');
INSERT INTO w6_prescription_item VALUES ('RX-10003', 'M003', 60, 5,  'Take twice daily with meals');

-- RX-10004: Sarah Chen - Internal Medicine (Dr. Chen)
INSERT INTO w6_prescription_item VALUES ('RX-10004', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast');
INSERT INTO w6_prescription_item VALUES ('RX-10004', 'M016', 30, 5,  'Take once daily in the morning');

-- RX-10005: Robert Thompson - Cardiology (Dr. Miller)
INSERT INTO w6_prescription_item VALUES ('RX-10005', 'M009', 30, 3,  'Take once daily with food');
INSERT INTO w6_prescription_item VALUES ('RX-10005', 'M001', 30, 3,  'Take once daily in the morning');
INSERT INTO w6_prescription_item VALUES ('RX-10005', 'M020', 30, 6,  'Take once daily in the morning');

-- RX-10006: Jennifer Garcia - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10006', 'M013', 6,  0,  'Take 2 tablets day 1, then 1 daily for 4 days');
INSERT INTO w6_prescription_item VALUES ('RX-10006', 'M010', 30, 2,  'Take once daily before breakfast');

-- RX-10007: Michael Brown - Endocrinology (Dr. Johnson)
INSERT INTO w6_prescription_item VALUES ('RX-10007', 'M004', 60, 5,  'Take twice daily with meals');
INSERT INTO w6_prescription_item VALUES ('RX-10007', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast');

-- RX-10008: Emily Davis - Psychiatry (Dr. Williams)
INSERT INTO w6_prescription_item VALUES ('RX-10008', 'M016', 30, 5,  'Take once daily in the morning');
INSERT INTO w6_prescription_item VALUES ('RX-10008', 'M014', 90, 3,  'Take 3 times daily as needed for nerve pain');

-- RX-10009: David Martinez - Internal Medicine (Dr. Chen)
INSERT INTO w6_prescription_item VALUES ('RX-10009', 'M011', 30, 6,  'Take once daily');
INSERT INTO w6_prescription_item VALUES ('RX-10009', 'M007', 30, 6,  'Take once daily');

-- RX-10010: Lisa Anderson - Pulmonology (Dr. Park)
INSERT INTO w6_prescription_item VALUES ('RX-10010', 'M019', 1,  3,  '2 puffs every 4-6 hours as needed');
INSERT INTO w6_prescription_item VALUES ('RX-10010', 'M018', 20, 0,  'Take as directed, taper over 10 days');

-- RX-10011: Christopher Lee - Cardiology (Dr. Miller)
INSERT INTO w6_prescription_item VALUES ('RX-10011', 'M006', 30, 6,  'Take once daily at bedtime');
INSERT INTO w6_prescription_item VALUES ('RX-10011', 'M009', 30, 3,  'Take once daily with food');
INSERT INTO w6_prescription_item VALUES ('RX-10011', 'M007', 30, 6,  'Take once daily');

-- RX-10012: Amanda Taylor - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10012', 'M017', 30, 5,  'Take once daily in the morning');

-- RX-10013: Daniel Harris - Dermatology (Dr. Santos) [Cancelled]
INSERT INTO w6_prescription_item VALUES ('RX-10013', 'M018', 30, 0,  'Take once daily for 14 days');

-- RX-10014: Jessica White - Psychiatry (Dr. Williams)
INSERT INTO w6_prescription_item VALUES ('RX-10014', 'M016', 30, 5,  'Take once daily in the morning');

-- RX-10015: Matthew Clark - Orthopedics (Dr. O'Brien)
INSERT INTO w6_prescription_item VALUES ('RX-10015', 'M014', 90, 2,  'Take 3 times daily for pain');
INSERT INTO w6_prescription_item VALUES ('RX-10015', 'M018', 15, 0,  'Take as directed, taper over 5 days');

-- RX-10016: Ashley Robinson - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10016', 'M012', 30, 1,  'Take twice daily for 10 days');
INSERT INTO w6_prescription_item VALUES ('RX-10016', 'M010', 30, 2,  'Take once daily before breakfast');

-- RX-10017: Kevin Walker - Gastroenterology (Dr. Kim)
INSERT INTO w6_prescription_item VALUES ('RX-10017', 'M010', 30, 6,  'Take once daily before breakfast');

-- RX-10018: Stephanie King - Internal Medicine (Dr. Chen) [Expired]
INSERT INTO w6_prescription_item VALUES ('RX-10018', 'M001', 30, 3,  'Take once daily in the morning');
INSERT INTO w6_prescription_item VALUES ('RX-10018', 'M003', 60, 5,  'Take twice daily with meals');

-- RX-10019: Brian Scott - Neurology (Dr. Hassan)
INSERT INTO w6_prescription_item VALUES ('RX-10019', 'M014', 90, 3,  'Take 3 times daily');

-- RX-10020: Nicole Adams - Endocrinology (Dr. Johnson)
INSERT INTO w6_prescription_item VALUES ('RX-10020', 'M003', 60, 5,  'Take twice daily with meals');
INSERT INTO w6_prescription_item VALUES ('RX-10020', 'M005', 30, 6,  'Take once daily at bedtime');

-- RX-10021: William Nguyen - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10021', 'M008', 30, 6,  'Take once daily');
INSERT INTO w6_prescription_item VALUES ('RX-10021', 'M011', 30, 6,  'Take once daily');

-- RX-10022: Maria Lopez - Cardiology (Dr. Miller)
INSERT INTO w6_prescription_item VALUES ('RX-10022', 'M009', 30, 3,  'Take once daily with food');

-- RX-10023: Robert Thompson - Endocrinology (Dr. Johnson)
INSERT INTO w6_prescription_item VALUES ('RX-10023', 'M004', 60, 5,  'Take twice daily with meals');
INSERT INTO w6_prescription_item VALUES ('RX-10023', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast');

-- RX-10024: Michael Brown - Internal Medicine (Dr. Chen)
INSERT INTO w6_prescription_item VALUES ('RX-10024', 'M002', 30, 6,  'Take once daily in the morning');
INSERT INTO w6_prescription_item VALUES ('RX-10024', 'M020', 30, 6,  'Take once daily in the morning');

-- RX-10025: Lisa Anderson - Pulmonology (Dr. Park)
INSERT INTO w6_prescription_item VALUES ('RX-10025', 'M019', 1,  3,  '2 puffs every 4-6 hours as needed');

-- RX-10026: James Wilson - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10026', 'M005', 30, 6,  'Take once daily at bedtime');
INSERT INTO w6_prescription_item VALUES ('RX-10026', 'M007', 30, 6,  'Take once daily');

-- RX-10027: Jessica White - Gastroenterology (Dr. Kim)
INSERT INTO w6_prescription_item VALUES ('RX-10027', 'M010', 30, 6,  'Take once daily before breakfast');

-- RX-10028: Emily Davis - Psychiatry (Dr. Williams)
INSERT INTO w6_prescription_item VALUES ('RX-10028', 'M017', 30, 5,  'Take once daily in the morning');

-- RX-10029: Ashley Robinson - Family Medicine (Dr. Patel)
INSERT INTO w6_prescription_item VALUES ('RX-10029', 'M015', 30, 11, 'Take on empty stomach 30 min before breakfast');

-- RX-10030: Christopher Lee - Cardiology (Dr. Miller)
INSERT INTO w6_prescription_item VALUES ('RX-10030', 'M006', 30, 6,  'Take once daily at bedtime');
INSERT INTO w6_prescription_item VALUES ('RX-10030', 'M020', 30, 6,  'Take once daily in the morning');


-- ============================================================
-- STEP 5: VERIFY THE DATA
-- ============================================================

-- Quick counts to confirm everything loaded
SELECT 'w6_patient' AS table_name,            COUNT(*) AS row_count FROM w6_patient
UNION ALL
SELECT 'w6_doctor',                           COUNT(*) FROM w6_doctor
UNION ALL
SELECT 'w6_insurance',                        COUNT(*) FROM w6_insurance
UNION ALL
SELECT 'w6_medication',                       COUNT(*) FROM w6_medication
UNION ALL
SELECT 'w6_patient_insurance',                COUNT(*) FROM w6_patient_insurance
UNION ALL
SELECT 'w6_prescription',                     COUNT(*) FROM w6_prescription
UNION ALL
SELECT 'w6_prescription_item',                COUNT(*) FROM w6_prescription_item
ORDER BY table_name;


-- ============================================================
-- SAMPLE QUERIES TO TEST THE DATABASE
-- ============================================================

-- 1. Show all medications on Maria Lopez's prescriptions
SELECT p.patient_name, rx.rx_number, rx.rx_date, 
       m.medication_name, m.dosage, pi.quantity, pi.refills, pi.instructions
FROM w6_patient p
JOIN w6_prescription rx ON p.patient_id = rx.patient_id
JOIN w6_prescription_item pi ON rx.rx_number = pi.rx_number
JOIN w6_medication m ON pi.medication_id = m.medication_id
WHERE p.patient_name = 'Maria Lopez'
ORDER BY rx.rx_date, m.medication_name;

-- 2. Show each patient with their insurance info
SELECT p.patient_name, i.company_name, i.coverage_type, 
       pi.policy_number, pi.copay
FROM w6_patient p
JOIN w6_patient_insurance pi ON p.patient_id = pi.patient_id
JOIN w6_insurance i ON pi.insurance_id = i.insurance_id
ORDER BY p.patient_name, i.company_name;

-- 3. Which doctors prescribed the most medications?
SELECT d.doctor_name, d.specialty, COUNT(*) AS total_items_prescribed
FROM w6_doctor d
JOIN w6_prescription rx ON d.doctor_id = rx.doctor_id
JOIN w6_prescription_item pi ON rx.rx_number = pi.rx_number
GROUP BY d.doctor_name, d.specialty
ORDER BY total_items_prescribed DESC;

-- 4. Most commonly prescribed medications
SELECT m.medication_name, m.dosage, COUNT(*) AS times_prescribed
FROM w6_medication m
JOIN w6_prescription_item pi ON m.medication_id = pi.medication_id
GROUP BY m.medication_name, m.dosage
ORDER BY times_prescribed DESC;

-- 5. Patients with multiple insurance plans
SELECT p.patient_name, COUNT(*) AS plan_count
FROM w6_patient p
JOIN w6_patient_insurance pi ON p.patient_id = pi.patient_id
GROUP BY p.patient_name
HAVING COUNT(*) > 1
ORDER BY plan_count DESC;
