CREATE TABLE Patients (
    PatientID INT PRIMARY KEY AUTO_INCREMENT,  
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    DOB DATE NOT NULL,
    Gender VARCHAR(10),
    Phone VARCHAR(15),
    Email VARCHAR(100) UNIQUE,
    Address VARCHAR(255)
);


CREATE TABLE Doctors (
    DoctorID INT PRIMARY KEY AUTO_INCREMENT, 
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    Specialization VARCHAR(100),
    Phone VARCHAR(15),
    Email VARCHAR(100) UNIQUE
);


CREATE TABLE Diagnoses (
    DiagnosisID INT PRIMARY KEY AUTO_INCREMENT,
    PatientID INT,
    DoctorID INT,
    DiagnosisDate DATE NOT NULL,
    Diagnosis VARCHAR(255),
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);


CREATE TABLE Appointments (
    AppointmentID INT PRIMARY KEY AUTO_INCREMENT,
    PatientID INT,
    DoctorID INT,
    AppointmentDate DATE NOT NULL,
    AppointmentTime TIME NOT NULL,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);


CREATE TABLE Treatments (
    TreatmentID INT PRIMARY KEY AUTO_INCREMENT,
    PatientID INT,
    DoctorID INT,
    TreatmentDate DATE NOT NULL,
    TreatmentDetails VARCHAR(255),
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);


INSERT INTO Patients (FirstName, LastName, DOB, Gender, Phone, Email, Address)
VALUES 
('John', 'Doe', '1985-06-15', 'Male', '123-456-7890', 'john.doe@example.com', '123 Main St'),
('Jane', 'Smith', '1990-11-22', 'Female', '987-654-3210', 'jane.smith@example.com', '456 Oak Ave'),
('Mark', 'Johnson', '1978-02-13', 'Male', '555-123-4567', 'mark.johnson@example.com', '789 Pine Rd');


INSERT INTO Doctors (FirstName, LastName, Specialization, Phone, Email)
VALUES 
('Dr. Sarah', 'Brown', 'Cardiology', '111-222-3333', 'sarah.brown@example.com'),
('Dr. Emily', 'Davis', 'Orthopedics', '444-555-6666', 'emily.davis@example.com'),
('Dr. Michael', 'Clark', 'Pediatrics', '777-888-9999', 'michael.clark@example.com');


INSERT INTO Diagnoses (PatientID, DoctorID, DiagnosisDate, Diagnosis)
VALUES 
(1, 1, '2023-01-10', 'Heart Disease'),
(2, 2, '2023-02-15', 'Fractured Arm'),
(3, 3, '2023-03-20', 'Viral Infection');


INSERT INTO Appointments (PatientID, DoctorID, AppointmentDate, AppointmentTime)
VALUES 
(1, 1, '2023-01-10', '09:00:00'),
(2, 2, '2023-02-15', '10:00:00'),
(3, 3, '2023-03-20', '11:00:00');


INSERT INTO Treatments (PatientID, DoctorID, TreatmentDate, TreatmentDetails)
VALUES 
(1, 1, '2023-01-15', 'Cardiac Surgery'),
(2, 2, '2023-02-20', 'Arm Casting'),
(3, 3, '2023-03-25', 'Antiviral Medication');


SELECT * FROM Patients;


SELECT d.DiagnosisID, d.DiagnosisDate, d.Diagnosis, doc.FirstName AS DoctorFirstName, doc.LastName AS DoctorLastName
FROM Diagnoses d
JOIN Doctors doc ON d.DoctorID = doc.DoctorID
WHERE d.PatientID = 1;


SELECT a.AppointmentID, p.FirstName AS PatientFirstName, p.LastName AS PatientLastName, a.AppointmentDate, a.AppointmentTime
FROM Appointments a
JOIN Patients p ON a.PatientID = p.PatientID
WHERE a.DoctorID = 1;


SELECT PatientID, COUNT(*) AS TotalTreatments
FROM Treatments
GROUP BY PatientID;


SELECT Diagnosis, COUNT(*) AS Frequency
FROM Diagnoses
GROUP BY Diagnosis
ORDER BY Frequency DESC
LIMIT 1;