app/schemas/hospital.sql

-- Patients Table
CREATE TABLE patients (
    patient_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    gender TEXT CHECK (gender IN ('Male', 'Female', 'Other')),
    dob DATE,
    contact TEXT
);

-- Doctors Table
CREATE TABLE doctors (
    doctor_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    specialty TEXT,
    contact TEXT
);

-- Appointments Table
CREATE TABLE appointments (
    appointment_id INTEGER PRIMARY KEY,
    patient_id INTEGER,
    doctor_id INTEGER,
    appointment_date DATE,
    diagnosis TEXT,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);


INSERT INTO patients VALUES 
(1, 'Aarushi Verma', 'Female', '1995-08-20', '9999888877'),
(2, 'Rohan Mehra', 'Male', '1990-03-15', '9999123456'),
(3, 'Simran Kaur', 'Female', '1985-11-05', '8888445566'),
(4, 'Arjun Kapoor', 'Male', '2000-01-25', '7777333222'),
(5, 'Priya Das', 'Female', '1998-07-14', '9999000011');

INSERT INTO doctors VALUES 
(1, 'Dr. Rakesh Sharma', 'Cardiologist', '8888777766'),
(2, 'Dr. Meena Iyer', 'Dermatologist', '8888123456'),
(3, 'Dr. Alok Mishra', 'Neurologist', '7777888899'),
(4, 'Dr. Nandini Rao', 'Pediatrician', '9999333311');

INSERT INTO appointments VALUES 
(1, 1, 1, '2023-06-20', 'Routine Checkup'),
(2, 2, 2, '2023-06-25', 'Skin Rash'),
(3, 3, 1, '2023-07-02', 'Chest Pain'),
(4, 4, 3, '2023-07-10', 'Headache & Dizziness'),
(5, 5, 4, '2023-07-15', 'Child Vaccination'),
(6, 1, 3, '2023-08-01', 'Memory Loss'),
(7, 2, 1, '2023-08-05', 'High Blood Pressure');