CREATE TABLE Appointments (
  appointment_id INTEGER PRIMARY KEY AUTOINCREMENT,
  doctor_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  appointment_date DATETIME NOT NULL,
  appointment_type TEXT,
  diagnosis_id INTEGER,
  FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id),
  FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
  FOREIGN KEY (diagnosis_id) REFERENCES Diagnoses(diagnosis_id)
);

CREATE TABLE Diagnoses (
  diagnosis_id INTEGER PRIMARY KEY AUTOINCREMENT,
  diagnosis_severity INTEGER NOT NULL,
  diagnosis_type TEXT NOT NULL
);

CREATE TABLE Doctors (
  doctor_id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  specialization TEXT,
  pharmacy_id INTEGER,
  diagnosable_types TEXT,
  FOREIGN KEY (pharmacy_id) REFERENCES Pharmacies(pharmacy_id)
);

CREATE TABLE Insurance (
  insurance_id INTEGER PRIMARY KEY AUTOINCREMENT,
  insurance_name TEXT NOT NULL,
  policy_number TEXT,
  coverage_start_date DATE,
  coverage_end_date DATE
);

CREATE TABLE MedicalHistory (
  history_id INTEGER PRIMARY KEY AUTOINCREMENT,
  patient_id INTEGER NOT NULL,
  diagnosis_id INTEGER NOT NULL,
  treatment_details TEXT,
  visit_date DATE NOT NULL,
  FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
  FOREIGN KEY (diagnosis_id) REFERENCES Diagnoses(diagnosis_id)
);

CREATE TABLE Medications (
  medication_id INTEGER PRIMARY KEY AUTOINCREMENT,
  medication_name TEXT NOT NULL,
  active_ingredient TEXT NOT NULL,
  quantity INTEGER NOT NULL, 
  quantity_units TEXT NOT NULL, -- e.g. ml, mg, tablets, capsules
  side_effects TEXT,
  usage_instructions TEXT,
  shelf_life DATE,
  opened_lifespan INTEGER, -- in days 
  recommended_selling_price INTEGER,
  covered_by_insurance INTEGER
);

CREATE TABLE Patients (
  patient_id INTEGER PRIMARY KEY AUTOINCREMENT,
  patient_name TEXT NOT NULL,
  date_of_birth DATE NOT NULL,
  gender TEXT,
  address TEXT,
  phone TEXT,
  email TEXT,
  insurance_id INTEGER,
  primary_doctor_id INTEGER,
  FOREIGN KEY (insurance_id) REFERENCES Insurance(insurance_id),
  FOREIGN KEY (primary_doctor_id) REFERENCES Doctors(doctor_id) 
);

CREATE TABLE Pharmacies (
  pharmacy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  pharmacy_name TEXT NOT NULL,
  phone_number CHAR(15),
  location TEXT
);

CREATE TABLE Prescriptions (
  prescription_id INTEGER PRIMARY KEY AUTOINCREMENT,
  doctor_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  medication_id INTEGER NOT NULL,
  dosage INTEGER, -- shared units as Medications.quantity_units
  daily_intake INTEGER,
  prescribing_date DATE NOT NULL,
  FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id),
  FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
  FOREIGN KEY (medication_id) REFERENCES Medications(medication_id)
);

INSERT INTO "Appointments" ("appointment_id", "doctor_id", "patient_id", "appointment_date", "appointment_type", "diagnosis_id") VALUES
('1', '1', '1', '2023-09-10 10:00:00', 'Follow-up', '1'),
('2', '2', '2', '2023-08-25 14:30:00', 'Initial Consultation', '6'),
('3', '3', '3', '2023-11-05 09:15:00', 'Routine Checkup', '3'),
('4', '4', '4', '2023-12-12 11:45:00', 'Follow-up', '4'),
('5', '5', '5', '2023-10-20 15:30:00', 'Initial Consultation', '11'),
('6', '6', '6', '2023-09-28 08:00:00', 'Routine Checkup', '13'),
('7', '7', '7', '2023-07-15 13:15:00', 'Follow-up', '16'),
('8', '8', '8', '2023-06-30 10:30:00', 'Initial Consultation', '2'),
('9', '9', '9', '2023-05-18 14:00:00', 'Routine Checkup', '5'),
('10', '1', '10', '2023-04-10 09:45:00', 'Follow-up', '8'),
('11', '3', '11', '2023-03-25 11:15:00', 'Initial Consultation', '12'),
('12', '5', '12', '2023-02-10 13:30:00', 'Routine Checkup', '14'),
('13', '7', '13', '2023-01-20 08:15:00', 'Follow-up', '17'),
('14', '8', '14', '2023-12-05 10:00:00', 'Initial Consultation', '19'),
('15', '9', '15', '2023-11-20 14:45:00', 'Routine Checkup', '15'),
('16', '1', '16', '2023-10-08 09:30:00', 'Follow-up', '10'),
('17', '4', '17', '2023-09-23 11:00:00', 'Initial Consultation', '7'),
('18', '6', '18', '2023-08-12 13:15:00', 'Routine Checkup', '9'),
('19', '2', '19', '2023-07-01 08:45:00', 'Follow-up', '18'),
('20', '3', '20', '2023-06-15 10:15:00', 'Initial Consultation', '1');

INSERT INTO "Diagnoses" ("diagnosis_id", "diagnosis_severity", "diagnosis_type") VALUES
('1', '2', 'Diabetes Mellitus Type 2'),
('2', '3', 'Hypertension'),
('3', '1', 'Influenza'),
('4', '2', 'Asthma'),
('5', '3', 'Depression'),
('6', '2', 'Anxiety Disorder'),
('7', '1', 'Bronchitis'),
('8', '3', 'Coronary Artery Disease'),
('9', '2', 'Osteoarthritis'),
('10', '1', 'Gastroenteritis'),
('11', '3', 'Breast Cancer'),
('12', '2', 'Prostate Cancer'),
('13', '1', 'Pneumonia'),
('14', '2', 'Chronic Obstructive Pulmonary Disease'),
('15', '3', 'Alzheimer''s Disease'),
('16', '2', 'Parkinson''s Disease'),
('17', '1', 'Migraine'),
('18', '3', 'Multiple Sclerosis'),
('19', '2', 'Rheumatoid Arthritis'),
('20', '1', 'Acute Sinusitis');

INSERT INTO "Doctors" ("doctor_id", "first_name", "last_name", "specialization", "pharmacy_id", "diagnosable_types") VALUES
('1', 'Emma', 'Williams', 'Cardiology', '1', "Hypertension,Coronary Artery Disease"),
('2', 'Liam', 'Brown', 'Pediatric', '2', "Influenza,Asthma"),
('3', 'Olivia', 'Taylor', 'Gynecology', NULL, "Breast Cancer,Prostate Cancer"),
('4', 'Noah', 'Miller', 'Orthopedics', '3', "Osteoarthritis,Rheumatoid Arthritis"),
('5', 'Ava', 'Davis', 'Dermatology', '1', "Diabetes Mellitus Type 2,Anxiety Disorder"),
('6', 'Elijah', 'Garcia', 'Neurology', NULL, "Depression,Parkinson''s Disease,Multiple Sclerosis"),
('7', 'William', 'Martinez', 'Oncology', '4', "Breast Cancer,Prostate Cancer"),
('8', 'Lucas', 'Martinez', 'Hematology', NULL, "Anemia,Leukemia"),
('9', 'Emily', 'Clark', 'Rheumatology', '4', "Rheumatoid Arthritis,Osteoarthritis"),
('10', 'Daniel', 'Lopez', 'Otolaryngology', '2', "Chronic Obstructive Pulmonary Disease,Bronchitis,Migraine");

INSERT INTO "Insurance" ("insurance_id", "insurance_name", "policy_number", "coverage_start_date", "coverage_end_date") VALUES
('1', 'UnitedHealthcare', 'UHCPOL12345', '2023-01-01', '2024-01-01'),
('2', 'Blue Cross Blue Shield', 'BCBSPOL56789', '2023-04-15', '2024-04-15'),
('3', 'Aetna', 'AETNAPOL9876', '2023-02-20', '2024-02-20'),
('4', 'Cigna', 'CIGNAPOL54321', '2023-06-10', '2024-06-10'),
('5', 'Kaiser Permanente', 'KPPOL11111', '2023-05-05', '2024-05-05'),
('6', 'Anthem', 'ANTHPOL22222', '2023-03-15', '2024-03-15'),
('7', 'Humana', 'HUMAPOL33333', '2023-07-20', '2024-07-20'),
('8', 'Centene', 'CNTPOL44444', '2023-09-01', '2024-09-01'),
('9', 'Tufts Health Plan', 'THPPOL55555', '2023-11-15', '2024-11-15'),
('10', 'Kaiser Permanente', 'KPOL39204', '2023-08-10', '2024-08-10'),
('11', 'UnitedHealthCare', 'UHCPOL77777', '2023-10-05', '2024-10-05'),
('12', 'UPMC Health Plan', 'UHPPOL88888', '2023-12-01', '2024-12-01'),
('13', 'HealthPartners', 'HPPOL99999', '2024-01-15', '2025-01-15'),
('14', 'Independence Blue Cross', 'IBCPOL00000', '2024-03-20', '2025-03-20'),
('15', 'Premera Blue Cross', 'PBCPOL10101', '2024-02-10', '2025-02-10');

INSERT INTO "MedicalHistory" ("history_id", "patient_id", "diagnosis_id", "treatment_details", "visit_date") VALUES
('1', '1', '2', 'Prescribed blood pressure medication and advised lifestyle changes.', '2023-08-10'),
('2', '2', '1', 'Given medication to manage blood sugar levels and educated on diabetes management.', '2023-07-15'),
('3', '3', '5', 'Started on antidepressants and referred for counseling.', '2023-09-02'),
('4', '4', '3', 'Treated with antiviral medication and advised rest.', '2023-06-20'),
('5', '5', '11', 'Prescribed painkillers and physical therapy for joint pain.', '2023-05-10'),
('6', '6', '13', 'Underwent surgery and is now undergoing chemotherapy.', '2023-03-05'),
('7', '7', '15', 'Given inhalers and advised to avoid triggers.', '2023-04-12'),
('8', '8', '7', 'Treated with antibiotics and advised hydration.', '2023-02-20'),
('9', '9', '19', 'Started on disease-modifying medications.', '2023-01-05'),
('10', '10', '17', 'Prescribed medication to manage symptoms and slow disease progression.', '2022-12-10');

INSERT INTO "Medications" ("medication_id", "medication_name", "active_ingredient", "quantity", "quantity_units", "side_effects", "usage_instructions", "shelf_life", "opened_lifespan", "recommended_selling_price", "covered_by_insurance") VALUES
('1', 'Insulin', 'Insulin', '10', 'ml', 'Low blood sugar, injection site reactions', 'Inject subcutaneously as directed by your doctor.', '2025-01-01', '30', '150', '1'),
('2', 'Metformin', 'Metformin Hydrochloride', '500', 'mg', 'Nausea, vomiting, diarrhea', 'Take orally with meals, exactly as directed by your doctor.', '2024-12-31', '60', '20', '1'),
('3', 'Paracetamol', 'Acetaminophen', '500', 'mg', 'Liver damage (rare)', 'Take orally with water. Do not exceed the recommended dose.', '2025-06-30', '90', '5', '1'),
('4', 'Ibuprofen', 'Ibuprofen', '200', 'mg', 'Stomach upset, increased bleeding risk', 'Take orally with food to minimize stomach irritation.', '2025-03-31', '120', '8', '1'),
('5', 'Amoxicillin', 'Amoxicillin Trihydrate', '500', 'mg', 'Diarrhea, nausea, allergic reaction', 'Take orally with a full glass of water. Finish the entire course.', '2024-11-30', '45', '30', '1'),
('6', 'Losartan', 'Losartan Potassium', '50', 'mg', 'Dizziness, fatigue, kidney problems', 'Take orally once daily, preferably at the same time each day.', '2025-05-31', '90', '45', '1'),
('7', 'Simvastatin', 'Simvastatin', '20', 'mg', 'Headache, muscle pain, liver damage (rare)', 'Take orally once daily in the evening.', '2025-02-28', '120', '60', '1'),
('8', 'Levothyroxine', 'Levothyroxine Sodium', '50', 'mcg', 'Heart palpitations, nervousness, insomnia', 'Take orally on an empty stomach, at least 30 minutes before breakfast.', '2024-10-31', '180', '25', '1'),
('9', 'Fluticasone Propionate Nasal Spray', 'Fluticasone Propionate', '50', 'mcg', 'Nose bleeds, nasal irritation', 'Spray into each nostril once daily.', '2025-04-30', '150', '35', '1'),
('10', 'Beclomethasone Inhaler', 'Beclomethasone Dipropionate', '250', 'mcg', 'Throat irritation, hoarseness', 'Inhale as directed by your doctor.', '2025-07-31', '210', '50', '1'),
('11', 'Salbutamol Inhaler', 'Albuterol', '100', 'mcg', 'Tremors, increased heart rate', 'Inhale as needed, up to 3 times per day.', '2025-08-31', '240', '10', '1'),
('12', 'Fluoxetine', 'Fluoxetine Hydrochloride', '20', 'mg', 'Nausea, insomnia, decreased libido', 'Take orally once daily, preferably in the morning.', '2025-05-31', '180', '40', '1'),
('13', 'Sertraline', 'Sertraline Hydrochloride', '50', 'mg', 'Drowsiness, decreased appetite', 'Take orally once daily, may be taken with or without food.', '2025-06-30', '270', '60', '1'),
('14', 'Doxycycline', 'Doxycycline Hyclate', '100', 'mg', 'Nausea, photosensitivity', 'Take orally with a full glass of water. Avoid sunlight and dairy products.', '2025-03-31', '60', '25', '1'),
('15', 'Azithromycin', 'Azithromycin Dihydrate', '250', 'mg', 'Nausea, vomiting, diarrhea', 'Take orally as directed by your doctor. May be taken with or without food.', '2025-02-28', '90', '40', '1'),
('16', 'Omeprazole', 'Omeprazole Magnesium', '20', 'mg', 'Headache, diarrhea', 'Take orally once daily, preferably in the morning.', '2025-01-31', '180', '15', '1'),
('17', 'Lansoprazole', 'Lansoprazole', '30', 'mg', 'Headache, nausea', 'Take orally before eating. Swallow the capsule whole.', '2024-12-31', '270', '20', '1'),
('18', 'Cetirizine', 'Cetirizine Hydrochloride', '10', 'mg', 'Drowsiness, dry mouth', 'Take orally with water. Do not exceed the recommended dose.', '2025-04-30', '365', '10', '1'),
('19', 'Loratadine', 'Loratadine', '10', 'mg', 'Drowsiness, headache', 'Take orally with water. May be taken with or without food.', '2025-05-31', '540', '8', '1'),
('20', 'Tamsulosin', 'Tamsulosin Hydrochloride', '400', 'mcg', 'Dizziness, abnormal ejaculation', 'Take orally once daily, preferably after the same meal each day.', '2025-06-30', '60', '30', '1'),
('21', 'Finasteride', 'Finasteride', '5', 'mg', 'Sexual side effects, breast tenderness', 'Take orally once daily, with or without food.', '2025-07-31', '90', '45', '1'),
('22', 'Ketoconazole Cream', 'Ketoconazole', '2', 'g', 'Skin irritation, itching', 'Apply a thin layer to the affected area(s) once or twice daily.', '2025-08-31', '180', '25', '1'),
('23', 'Clotrimazole Cream', 'Clotrimazole', '1', 'g', 'Skin irritation, burning sensation', 'Apply a thin layer to the affected area(s) twice daily.', '2025-09-30', '270', '15', '1'),
('24', 'Tretinoin Cream', 'Tretinoin', '50', 'mg', 'Skin redness, peeling, irritation', 'Apply a thin layer to the affected area(s) once daily before bedtime.', '2025-10-31', '365', '50', '1'),
('25', 'Hydroquinone Cream', 'Hydroquinone', '4', 'g', 'Skin dryness, irritation', 'Apply a thin layer to the affected area(s) twice daily.', '2025-11-30', '45', '30', '1');

INSERT INTO "Patients" ("patient_id", "patient_name", "date_of_birth", "gender", "address", "phone", "email", "insurance_id", "primary_doctor_id") VALUES
('1', 'John Smith', '2000-05-15', 'Male', '123 Main St', '555-123-4567', 'john.smith@example.com', '1', '1'),
('2', 'Jane Doe', '2002-09-20', 'Female', '456 Pine Ave', '555-987-6543', 'jane.doe@example.com', '2', '2'),
('3', 'Michael Johnson', '2001-03-30', 'Male', '789 Elm Rd', '555-555-5555', 'michael.johnson@example.com', '3', '3'),
('4', 'Emily Brown', '2003-11-12', 'Female', '101 Maple Ln', '555-111-2222', 'emily.brown@example.com', '4', '4'),
('5', 'William Davis', '1999-07-05', 'Male', '567 Oak St', '555-444-3333', 'william.davis@example.com', '8', '5'),
('6', 'Sophia Miller', '2002-12-25', 'Female', '890 Cherry Ave', '555-777-8888', 'sophia.miller@example.com', '6', '6'),
('7', 'Benjamin Wilson', '2000-10-10', 'Male', '901 Birch Rd', '555-666-7777', 'benjamin.wilson@example.com', '7', '7'),
('8', 'Olivia Thompson', '2001-04-30', 'Female', '234 Evergreen Ct', '555-888-9999', 'olivia.thompson@example.com', '5', '1'),
('9', 'Ethan Anderson', '2002-01-18', 'Male', '678 Ivy Blvd', '555-999-8888', 'ethan.anderson@example.com', '9', '2'),
('10', 'Emma Roberts', '2003-06-22', 'Female', '129 Rose Ln', '555-777-6666', 'emma.roberts@example.com', '10', '3'),
('11', 'Liam Johnson', '2000-11-03', 'Male', '451 Lakeview Dr', '555-666-5555', 'liam.johnson@example.com', NULL, '4'),
('12', 'Isabella Martinez', '2002-05-29', 'Female', '783 Riverside Ave', '555-555-4444', 'isabella.martinez@example.com', '11', '5'),
('13', 'Noah Taylor', '2001-02-10', 'Male', '102 Sunset Blvd', '555-444-5555', 'noah.taylor@example.com', '12', '6'),
('14', 'Ava Lee', '2003-08-17', 'Female', '345 Mountain Rd', '555-666-7777', 'ava.lee@example.com', '13', '7'),
('15', 'Elijah Wilson', '2000-12-06', 'Male', '679 Valley View Dr', '555-777-8888', 'elijah.wilson@example.com', '14', '8'),
('16', 'Sophia Anderson', '2002-03-24', 'Female', '902 Meadow Ln', '555-888-9999', 'sophia.anderson@example.com', NULL, '9'),
('17', 'William Thompson', '2001-10-11', 'Male', '231 Brookside Ave', '555-999-8888', 'william.thompson@example.com', '15', '1'),
('18', 'Amelia Martinez', '2003-05-02', 'Female', '563 Riverside Dr', '555-666-7777', 'amelia.martinez@example.com', NULL, NULL),
('19', 'Sebastian Garcia', '2000-09-19', 'Male', '892 Oakdale Rd', '555-777-8888', 'sebastian.garcia@example.com', NULL, '10'),
('20', 'Emily Perez', '2002-01-07', 'Female', '123 Sunshine Ave', '555-888-9999', 'emily.perez@example.com', NULL, NULL);

INSERT INTO "Pharmacies" ("pharmacy_id", "pharmacy_name", "phone_number", "location") VALUES
('1', 'Green Cross Pharmacy', '555-123-4567', '123 Main St'),
('2', 'Medicine Shop', '555-987-6543', '456 Pine Ave'),
('3', 'Health Plus Drugs', '555-555-5555', '789 Elm Rd'),
('4', 'Corner Drugstore', '555-111-2222', '101 Maple Ln');

INSERT INTO "Prescriptions" ("prescription_id", "doctor_id", "patient_id", "medication_id", "dosage", "daily_intake", "prescribing_date") VALUES
('1', '1', '1', '1', '10', '2', '2023-09-10 10:00:00'),
('2', '2', '2', '2', '500', '1', '2023-08-25 14:30:00'),
('3', '3', '3', '3', '500', '2', '2023-11-05 09:15:00'),
('4', '4', '4', '4', '200', '3', '2023-12-12 11:45:00'),
('5', '5', '5', '5', '50', '1', '2023-10-20 15:30:00'),
('6', '6', '6', '6', '20', '1', '2023-09-28 08:00:00'),
('7', '7', '7', '7', '50', '1', '2023-07-15 13:15:00'),
('8', '8', '8', '8', '50', '2', '2023-06-30 10:30:00'),
('9', '9', '9', '9', '20', '1', '2023-05-18 14:00:00'),
('10', '1', '10', '10', '50', '3', '2023-04-10 09:45:00'),
('11', '3', '11', '11', '250', '2', '2023-03-25 11:15:00'),
('12', '4', '12', '12', '100', '1', '2023-03-22 11:02:00'),
('13', '5', '13', '13', '20', '2', '2023-04-09 09:32:00'),
('14', '6', '14', '14', '50', '1', '2023-11-25 09:15:00'),
('15', '7', '15', '15', '10', '1', '2023-10-29 15:40:00'),
('16', '8', '16', '16', '20', '2', '2023-09-10 10:00:00'),
('17', '9', '17', '17', '10', '1', '2023-09-17 10:00:00'),
('18', '1', '1', '19', '10', '1', '2023-09-10 10:00:00'),
('19', '2', '2', '20', '30', '2', '2023-08-25 14:30:00'),
('20', '3', '3', '21', '20', '1', '2023-11-05 09:15:00'),
('21', '4', '4', '22', '5', '1', '2023-12-12 11:45:00'),
('22', '5', '5', '23', '100', '2', '2023-10-20 15:30:00');

SELECT
    d.specialization,
    COUNT(DISTINCT a.patient_id) AS distinct_patients,
    COUNT(a.appointment_id) AS total_appointments
FROM
    Doctors d
    JOIN Appointments a ON d.doctor_id = a.doctor_id
WHERE
    a.appointment_date >= DATE('now', '-12 months')
GROUP BY
    d.specialization
ORDER BY
    distinct_patients DESC
LIMIT 1;