-- 1. DROP TABLES (Using a block to ignore "table does not exist" errors)
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ica5_RX_Details'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ica5_RX'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ica5_Drug'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ica5_Patient'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ica5_Doctor'; EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-- 2. CREATE TABLES
CREATE TABLE ica5_Doctor (
    Doctor_ID INT PRIMARY KEY,
    Doctor_Name VARCHAR2(100) NOT NULL,
    Specialization VARCHAR2(100)
);

CREATE TABLE ica5_Patient (
    Patient_ID INT PRIMARY KEY,
    Patient_Name VARCHAR2(100) NOT NULL,
    Patient_DOB DATE
);

CREATE TABLE ica5_Drug (
    Drug_ID INT PRIMARY KEY,
    Drug_Name VARCHAR2(100) NOT NULL,
    Side_Effect CLOB
);

CREATE TABLE ica5_RX (
    RX_ID INT PRIMARY KEY,
    RX_Date DATE NOT NULL,
    Patient_ID INT,
    Doctor_ID INT,
    CONSTRAINT fk_patient FOREIGN KEY (Patient_ID) REFERENCES ica5_Patient(Patient_ID),
    CONSTRAINT fk_doctor FOREIGN KEY (Doctor_ID) REFERENCES ica5_Doctor(Doctor_ID)
);

CREATE TABLE ica5_RX_Details (
    RX_Detail_ID INT PRIMARY KEY,
    Qty INT NOT NULL,
    Drug_ID INT,
    RX_ID INT,
    CONSTRAINT fk_drug FOREIGN KEY (Drug_ID) REFERENCES ica5_Drug(Drug_ID),
    CONSTRAINT fk_rx FOREIGN KEY (RX_ID) REFERENCES ica5_RX(RX_ID)
);

-- 3. INSERT TEST DATA (Oracle specific date format using TO_DATE)
INSERT INTO ica5_Doctor (Doctor_ID, Doctor_Name, Specialization) VALUES (1, 'Dr. Smith', 'Cardiology');
INSERT INTO ica5_Doctor (Doctor_ID, Doctor_Name, Specialization) VALUES (2, 'Dr. Jones', 'General Practice');

INSERT INTO ica5_Patient (Patient_ID, Patient_Name, Patient_DOB) VALUES (101, 'Alice Young', TO_DATE('1985-05-12', 'YYYY-MM-DD'));
INSERT INTO ica5_Patient (Patient_ID, Patient_Name, Patient_DOB) VALUES (102, 'Bob Miller', TO_DATE('1992-11-23', 'YYYY-MM-DD'));

INSERT INTO ica5_Drug (Drug_ID, Drug_Name, Side_Effect) VALUES (501, 'Amoxicillin', 'Nausea');
INSERT INTO ica5_Drug (Drug_ID, Drug_Name, Side_Effect) VALUES (502, 'Lisinopril', 'Dizziness');

INSERT INTO ica5_RX (RX_ID, RX_Date, Patient_ID, Doctor_ID) VALUES (1001, TO_DATE('2023-10-01', 'YYYY-MM-DD'), 101, 1);
INSERT INTO ica5_RX (RX_ID, RX_Date, Patient_ID, Doctor_ID) VALUES (1002, TO_DATE('2023-10-05', 'YYYY-MM-DD'), 102, 2);

INSERT INTO ica5_RX_Details (RX_Detail_ID, Qty, Drug_ID, RX_ID) VALUES (1, 30, 502, 1001);
INSERT INTO ica5_RX_Details (RX_Detail_ID, Qty, Drug_ID, RX_ID) VALUES (2, 10, 501, 1002);

COMMIT;