CREATE TABLE Patient (
    patient_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    blood_type VARCHAR(5) NOT NULL
);


CREATE TABLE Doctor (
    doctor_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    medical_specialty VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL
);


CREATE TABLE Appointment (
    appointment_id INT PRIMARY KEY 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(10) NOT NULL,
    reason_for_visit VARCHAR(255) NOT NULL,
    check_in_time DATETIME NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES Patient(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
);


INSERT INTO Patient (name, email, date_of_birth, blood_type) 
VALUES ('Godfrie Romero', 'gr.romero0181@student.tsu.edu.ph', '2002-05-19', 'A+');

INSERT INTO Doctor (name, medical_specialty, hire_date) 
VALUES ('Dr. Zhao', 'Otolaryngologist', '2016-09-06');

INSERT INTO Appointment (patient_id, doctor_id, appointment_date, appointment_time, room_number, reason_for_visit, check_in_time) 
VALUES (1, 1, '2026-09-10', '09:00:00', '243', 'Regular Checkup', '2026-09-06 08:45:00');


SELECT * FROM Patient;
SELECT * FROM Doctor;
SELECT * FROM Appointment;