-- =========================
-- PATIENT TABLE
-- =========================
CREATE TABLE IF NOT EXISTS Patient (
    Patient_ID INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100),
    Date_of_Birth DATE,
    Blood_Type VARCHAR(5),
    PRIMARY KEY (Patient_ID)
);


-- =========================
-- DOCTOR TABLE
-- =========================
CREATE TABLE IF NOT EXISTS Doctor (
    Doctor_ID INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Medical_Specialty VARCHAR(100),
    Hire_Date DATE,
    PRIMARY KEY (Doctor_ID)
);


-- =========================
-- APPOINTMENT TABLE
-- =========================
CREATE TABLE IF NOT EXISTS Appointment (
    Appointment_ID INT NOT NULL AUTO_INCREMENT,
    Patient_ID INT NOT NULL,
    Doctor_ID INT NOT NULL,
    Appointment_Date DATE NOT NULL,
    Appointment_Time TIME NOT NULL,
    Room_Number VARCHAR(20),
    Reason_for_Visit VARCHAR(255),
    Check_in_Time TIME,

    PRIMARY KEY (Appointment_ID),

    CONSTRAINT fk_appointment_patient
        FOREIGN KEY (Patient_ID)
        REFERENCES Patient(Patient_ID)
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT fk_appointment_doctor
        FOREIGN KEY (Doctor_ID)
        REFERENCES Doctor(Doctor_ID)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


-- =========================
-- INSERT PATIENT DATA
-- =========================
INSERT INTO Patient
    (Name, Email, Date_of_Birth, Blood_Type)
VALUES
    ('Juan Dela Cruz', 'juan@gmail.com', '2000-05-15', 'O+'),
    ('Maria Santos', 'maria@gmail.com', '1998-10-20', 'A+');


-- =========================
-- INSERT DOCTOR DATA
-- =========================
INSERT INTO Doctor
    (Name, Medical_Specialty, Hire_Date)
VALUES
    ('Dr. Pedro Reyes', 'Cardiology', '2020-06-01'),
    ('Dr. Ana Garcia', 'Pediatrics', '2021-03-15');


-- =========================
-- INSERT APPOINTMENT DATA
-- =========================
INSERT INTO Appointment
(
    Patient_ID,
    Doctor_ID,
    Appointment_Date,
    Appointment_Time,
    Room_Number,
    Reason_for_Visit,
    Check_in_Time
)
VALUES
(
    1,
    1,
    '2026-09-20',
    '09:00:00',
    'Room 101',
    'Regular Checkup',
    '08:45:00'
),
(
    2,
    2,
    '2026-09-21',
    '10:30:00',
    'Room 102',
    'Medical Consultation',
    '10:20:00'
);


-- =========================
-- VIEW THE DATA
-- =========================
SELECT * FROM Patient;
SELECT * FROM Doctor;
SELECT * FROM Appointment;