-- Reseau Sante du Nord EHR Database

CREATE TABLE IF NOT EXISTS clinics (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(200) NOT NULL,
    phone VARCHAR(30)
);

CREATE TABLE IF NOT EXISTS staff (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(200) NOT NULL,
    role VARCHAR(50) NOT NULL,
    clinic_id INTEGER REFERENCES clinics(id)
);

CREATE TABLE IF NOT EXISTS patients (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    dob DATE NOT NULL,
    community VARCHAR(100),
    medical_history TEXT,
    hiv_status VARCHAR(20) DEFAULT 'unknown',
    mental_health_notes TEXT,
    pregnancy_status VARCHAR(30) DEFAULT 'n/a'
);

CREATE TABLE IF NOT EXISTS appointments (
    id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(id),
    clinic_id INTEGER REFERENCES clinics(id),
    doctor_name VARCHAR(100),
    date DATE NOT NULL,
    time TIME NOT NULL,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS prescriptions (
    id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(id),
    medication VARCHAR(150) NOT NULL,
    dosage VARCHAR(100) NOT NULL,
    prescriber VARCHAR(100) NOT NULL,
    date DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS lab_results (
    id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(id),
    test_type VARCHAR(100) NOT NULL,
    result TEXT NOT NULL,
    date DATE NOT NULL,
    lab_technician VARCHAR(100)
);

-- Clinics across northern Haiti
INSERT INTO clinics (name, location, phone) VALUES
('Centre de Sante Cap-Haitien', 'Rue 15, Cap-Haitien (Central Office)', '+509 2262-1001'),
('Clinique Milot', 'Route Nationale 3, Milot', '+509 2262-1002'),
('Centre de Sante Limonade', 'Rue Principale, Limonade', '+509 2262-1003'),
('Clinique Quartier Morin', 'Route de Quartier Morin', '+509 2262-1004'),
('Centre de Sante Plaine du Nord', 'Rue du Marche, Plaine du Nord', '+509 2262-1005'),
('Clinique Acul du Nord', 'Route Departementale, Acul du Nord', '+509 2262-1006');

-- Staff accounts
-- Default admin account: admin@reseausantedunord.ht / admin123 (Frantz planned to change this)
INSERT INTO staff (name, email, password_hash, role, clinic_id) VALUES
('Admin', 'admin@reseausantedunord.ht', 'admin123', 'admin', 1),
('Dr. Jean-Baptiste Pierre', 'jb.pierre@reseausantedunord.ht', 'doctor2024!', 'doctor', 1),
('Dr. Magalie Celestin', 'mag.celestin@reseausantedunord.ht', 'doctor2024!', 'doctor', 1),
('Dr. Wilner Augustin', 'w.augustin@reseausantedunord.ht', 'doctor2024!', 'doctor', 2),
('Dr. Roseline Marcelin', 'r.marcelin@reseausantedunord.ht', 'doctor2024!', 'doctor', 3),
('Nurse Guerline Toussaint', 'g.toussaint@reseausantedunord.ht', 'nurse2024!', 'nurse', 1),
('Nurse Fabienne Dormeus', 'f.dormeus@reseausantedunord.ht', 'nurse2024!', 'nurse', 2),
('Nurse Mirlande Joseph', 'm.joseph@reseausantedunord.ht', 'nurse2024!', 'nurse', 4),
('Pharmacist Yvon Bien-Aime', 'y.bienaime@reseausantedunord.ht', 'pharma2024!', 'pharmacist', 1),
('Lab Tech Nadege Belizaire', 'n.belizaire@reseausantedunord.ht', 'lab2024!', 'lab_tech', 1),
('Lab Tech Edmond Charles', 'e.charles@reseausantedunord.ht', 'lab2024!', 'lab_tech', 3),
('CHW Josette Fleurima', 'j.fleurima@reseausantedunord.ht', 'chw2024!', 'community_health', 5),
('CHW Bernadette Estimable', 'b.estimable@reseausantedunord.ht', 'chw2024!', 'community_health', 6),
('IT Frantz Louissaint', 'frantz@reseausantedunord.ht', 'frantz_it_2024', 'it_admin', 1),
('Marie-Claire Desrosiers', 'mc.desrosiers@reseausantedunord.ht', 'director2024!', 'director', 1);

-- Patient records with realistic Haitian names and sensitive medical data
INSERT INTO patients (first_name, last_name, dob, community, medical_history, hiv_status, mental_health_notes, pregnancy_status) VALUES
('Yolande', 'Baptiste', '1985-03-14', 'Cap-Haitien', 'Hypertension diagnosed 2020. Currently on lisinopril 10mg daily.', 'negative', NULL, 'n/a'),
('Pierre-Louis', 'Desir', '1972-08-22', 'Milot', 'Type 2 diabetes since 2018. Metformin 500mg twice daily. History of foot ulcers.', 'negative', NULL, 'n/a'),
('Manoucheka', 'Jean-Charles', '1995-06-01', 'Limonade', 'No significant medical history.', 'positive', 'Anxiety related to HIV diagnosis. Referred to counseling 2023.', 'not pregnant'),
('Edouard', 'Thelusma', '1968-11-30', 'Cap-Haitien', 'Chronic kidney disease stage 3. Hypertension. Gout.', 'negative', NULL, 'n/a'),
('Marie-Ange', 'Occean', '2000-01-15', 'Quartier Morin', 'Sickle cell trait carrier. Iron deficiency anemia.', 'negative', NULL, 'pregnant - 28 weeks'),
('Jeanty', 'Philogene', '1990-04-08', 'Plaine du Nord', 'Tuberculosis treated 2021. Completed 6-month regimen. Cleared.', 'positive', 'Depression. On fluoxetine 20mg since 2022.', 'n/a'),
('Rosemene', 'Alcindor', '1978-12-25', 'Acul du Nord', 'Asthma since childhood. Albuterol inhaler as needed.', 'negative', NULL, 'n/a'),
('Franck', 'Dorvil', '1988-07-17', 'Cap-Haitien', 'Epilepsy. Carbamazepine 200mg twice daily. Seizure-free since 2023.', 'negative', 'PTSD following 2021 earthquake. In group therapy.', 'n/a'),
('Jocelyne', 'Narcisse', '1965-09-03', 'Milot', 'Rheumatoid arthritis. Methotrexate weekly. Osteoporosis.', 'negative', NULL, 'n/a'),
('Wilky', 'Dorce', '1998-02-20', 'Limonade', 'No significant medical history. Sports injury to right knee 2023.', 'negative', NULL, 'n/a'),
('Ginette', 'Romain', '1982-05-11', 'Cap-Haitien', 'Gestational diabetes in 2019. Monitored. Currently normal glucose.', 'negative', NULL, 'not pregnant'),
('Yvrose', 'Casimir', '1975-10-28', 'Quartier Morin', 'Hypertension and hyperlipidemia. On amlodipine and atorvastatin.', 'negative', NULL, 'n/a'),
('Jean-Robert', 'Faustin', '1960-03-06', 'Plaine du Nord', 'COPD from years of charcoal production. On tiotropium inhaler.', 'negative', NULL, 'n/a'),
('Carmelle', 'Altidor', '1993-08-19', 'Acul du Nord', 'Anemia. Iron supplements. History of malaria (treated 2022).', 'positive', NULL, 'pregnant - 16 weeks'),
('Elius', 'Beauvais', '2002-12-12', 'Cap-Haitien', 'No significant medical history.', 'unknown', NULL, 'n/a'),
('Kettlie', 'Charles', '1987-04-30', 'Milot', 'Chronic migraines. Sumatriptan as needed. MRI normal 2023.', 'negative', 'Generalized anxiety disorder. On buspirone.', 'not pregnant'),
('Saintilia', 'Duverger', '1970-01-22', 'Limonade', 'Heart failure NYHA class II. On enalapril and furosemide.', 'negative', NULL, 'n/a'),
('Widline', 'Exantus', '1996-11-08', 'Cap-Haitien', 'Thyroid disorder. Levothyroxine 50mcg daily.', 'negative', NULL, 'pregnant - 34 weeks'),
('Mackenson', 'Pierre', '1983-06-15', 'Quartier Morin', 'Hepatitis B carrier. Monitored quarterly. Liver function stable.', 'negative', NULL, 'n/a'),
('Roseline', 'Michel', '2004-09-25', 'Plaine du Nord', 'Allergic rhinitis. Seasonal. Cetirizine as needed.', 'negative', NULL, 'n/a');

-- Additional patients to reach 80
INSERT INTO patients (first_name, last_name, dob, community, medical_history, hiv_status, mental_health_notes, pregnancy_status) VALUES
('Dieuliphete', 'Sanon', '1976-02-14', 'Cap-Haitien', 'Peptic ulcer disease. On omeprazole. H. pylori treated 2023.', 'negative', NULL, 'n/a'),
('Lamercie', 'Vilfort', '1991-07-03', 'Milot', 'No significant medical history.', 'negative', NULL, 'not pregnant'),
('Ronel', 'Dieujuste', '1980-11-11', 'Limonade', 'Lower back pain chronic. Physical therapy ongoing.', 'negative', NULL, 'n/a'),
('Lunise', 'Petit-Frere', '1969-04-18', 'Acul du Nord', 'Type 2 diabetes. Insulin-dependent since 2022.', 'negative', NULL, 'n/a'),
('Wisly', 'Severe', '1999-01-07', 'Cap-Haitien', 'Appendectomy 2021. No complications.', 'negative', NULL, 'n/a'),
('Beatrice', 'Noel', '1986-08-24', 'Quartier Morin', 'Hypertension. Well controlled on hydrochlorothiazide.', 'negative', NULL, 'n/a'),
('Reginald', 'Estimphil', '1973-05-30', 'Plaine du Nord', 'Gout. Allopurinol daily. Dietary management.', 'negative', NULL, 'n/a'),
('Gerda', 'Joubert', '1994-12-16', 'Milot', 'Iron deficiency anemia. On ferrous sulfate.', 'negative', NULL, 'pregnant - 22 weeks'),
('Enold', 'Prophete', '1967-03-09', 'Cap-Haitien', 'Prostate enlargement. On tamsulosin. PSA monitored.', 'negative', NULL, 'n/a'),
('Nadine', 'Fevrier', '2001-10-05', 'Limonade', 'Contact dermatitis. Topical steroids as needed.', 'negative', NULL, 'n/a'),
('Wesner', 'Germain', '1984-06-21', 'Acul du Nord', 'Malaria treated 2023. Recovered fully.', 'negative', NULL, 'n/a'),
('Judeline', 'Saint-Fleur', '1977-09-13', 'Cap-Haitien', 'Breast cancer survivor. In remission since 2021. Annual mammography.', 'negative', 'Adjustment disorder post-treatment. Resolved.', 'n/a'),
('Sony', 'Jacques', '1992-02-28', 'Quartier Morin', 'No significant medical history. Motorcycle accident 2022, healed fracture.', 'negative', NULL, 'n/a'),
('Marlene', 'Toussaint', '1971-07-04', 'Plaine du Nord', 'Osteoarthritis bilateral knees. On acetaminophen.', 'negative', NULL, 'n/a'),
('Woodly', 'Lamour', '2003-04-17', 'Milot', 'Acne vulgaris. Topical retinoid.', 'negative', NULL, 'n/a'),
('Pascale', 'Alexis', '1989-11-22', 'Cap-Haitien', 'Migraine with aura. On propranolol prophylaxis.', 'negative', NULL, 'not pregnant'),
('Edner', 'Baptiste', '1966-01-30', 'Limonade', 'COPD. Former smoker. On salmeterol/fluticasone inhaler.', 'negative', NULL, 'n/a'),
('Yolaine', 'Dorcely', '1997-08-09', 'Acul du Nord', 'No significant medical history.', 'positive', 'Mild depression. Counseling only, no medication.', 'not pregnant'),
('Claudy', 'Merisier', '1981-12-03', 'Cap-Haitien', 'Chronic hepatitis C. Under treatment. Viral load decreasing.', 'negative', NULL, 'n/a'),
('Mirlene', 'Augustin', '1974-05-16', 'Quartier Morin', 'Fibromyalgia. Pregabalin 75mg daily. Physiotherapy.', 'negative', 'Chronic pain-related depression. On duloxetine.', 'n/a'),
('Frito', 'Jean-Louis', '2000-03-27', 'Plaine du Nord', 'Sickle cell disease. Regular transfusions. Hydroxyurea.', 'negative', NULL, 'n/a'),
('Guerda', 'Metellus', '1988-10-14', 'Milot', 'Gestational hypertension 2022. Monitoring continues.', 'negative', NULL, 'not pregnant'),
('Willy', 'Simeon', '1963-06-08', 'Cap-Haitien', 'Coronary artery disease. Stent placed 2020. On aspirin and clopidogrel.', 'negative', NULL, 'n/a'),
('Rosena', 'Louis', '1995-09-19', 'Limonade', 'Polycystic ovary syndrome. On metformin and oral contraceptive.', 'negative', NULL, 'not pregnant'),
('Jocelerme', 'Privert', '1979-02-05', 'Acul du Nord', 'Cataracts bilateral. Surgery scheduled 2025.', 'negative', NULL, 'n/a'),
('Mirlande', 'Victor', '1990-07-31', 'Cap-Haitien', 'No significant medical history.', 'negative', NULL, 'pregnant - 12 weeks'),
('Ricot', 'Desrosiers', '1968-04-12', 'Quartier Morin', 'Peripheral neuropathy. Diabetes-related. Gabapentin.', 'negative', NULL, 'n/a'),
('Yanick', 'Bellevue', '2005-01-23', 'Plaine du Nord', 'Asthma. Well controlled on budesonide inhaler.', 'negative', NULL, 'n/a'),
('Lourdes', 'Polynice', '1983-11-07', 'Milot', 'Cervical dysplasia. Monitored with annual Pap smear. HPV positive.', 'negative', NULL, 'not pregnant'),
('Edmond', 'Cadet', '1956-08-18', 'Cap-Haitien', 'Parkinson disease early stage. On levodopa/carbidopa.', 'negative', NULL, 'n/a'),
('Fabiola', 'Voltaire', '1998-06-02', 'Limonade', 'No significant medical history. Dental abscess treated 2024.', 'negative', NULL, 'n/a'),
('Herby', 'Sanon', '1985-12-29', 'Acul du Nord', 'Herniated disc L4-L5. Conservative management.', 'negative', NULL, 'n/a'),
('Clermise', 'Fortune', '1972-03-15', 'Cap-Haitien', 'Chronic kidney disease stage 2. Monitoring kidney function.', 'negative', NULL, 'n/a'),
('Ducarmel', 'Pierre-Louis', '1993-09-08', 'Quartier Morin', 'No significant medical history.', 'negative', NULL, 'n/a'),
('Michelene', 'Colas', '1980-04-26', 'Plaine du Nord', 'Hypothyroidism. Levothyroxine 75mcg daily.', 'negative', NULL, 'n/a'),
('Evens', 'Francois', '2001-07-14', 'Milot', 'Sports-related shoulder injury 2024. Rehab ongoing.', 'negative', NULL, 'n/a'),
('Sophonie', 'Andre', '1987-01-19', 'Cap-Haitien', 'Eczema chronic. Topical emollients and steroids.', 'negative', NULL, 'not pregnant'),
('Nestol', 'Deus', '1975-10-03', 'Limonade', 'Benign prostatic hyperplasia. On finasteride.', 'negative', NULL, 'n/a'),
('Berline', 'Rene', '1996-05-28', 'Acul du Nord', 'Urinary tract infections recurrent. Prophylactic antibiotics.', 'negative', NULL, 'not pregnant'),
('Wesly', 'Paul', '1964-08-11', 'Cap-Haitien', 'Cataract right eye. Surgery completed 2024.', 'negative', NULL, 'n/a'),
('Magda', 'Thermidor', '1991-12-06', 'Quartier Morin', 'No significant medical history.', 'negative', NULL, 'pregnant - 8 weeks'),
('Ronald', 'Celestin', '1978-03-22', 'Plaine du Nord', 'Peptic ulcer. Healed. On maintenance omeprazole.', 'negative', NULL, 'n/a'),
('Yolette', 'Moise', '1970-06-17', 'Milot', 'Systemic lupus erythematosus. On hydroxychloroquine.', 'negative', NULL, 'n/a'),
('Herold', 'Jeune', '2004-11-01', 'Cap-Haitien', 'Childhood asthma. Outgrown. No current symptoms.', 'negative', NULL, 'n/a'),
('Sherlie', 'Clermont', '1986-02-08', 'Limonade', 'Fibroids uterine. Monitored. No intervention needed yet.', 'negative', NULL, 'not pregnant'),
('Roodly', 'Jeannot', '1973-07-25', 'Acul du Nord', 'Glaucoma bilateral. On timolol eye drops.', 'negative', NULL, 'n/a'),
('Nathalie', 'Edouard', '1999-04-13', 'Cap-Haitien', 'No significant medical history.', 'negative', NULL, 'n/a'),
('Figaro', 'Hyppolite', '1982-09-30', 'Quartier Morin', 'Chronic back pain. Physical therapy. Naproxen as needed.', 'negative', NULL, 'n/a'),
('Elodie', 'Bertrand', '1994-01-05', 'Plaine du Nord', 'Allergic asthma. Montelukast daily.', 'negative', NULL, 'n/a'),
('Gethro', 'Jeanty', '1961-11-18', 'Milot', 'Atrial fibrillation. On warfarin. INR monitored monthly.', 'negative', NULL, 'n/a'),
('Fabienne', 'Charles', '1989-06-24', 'Cap-Haitien', 'No significant medical history. Annual wellness check.', 'negative', NULL, 'not pregnant'),
('Prenston', 'Regis', '2003-08-07', 'Limonade', 'Fractured tibia 2023. Healed. Full function restored.', 'negative', NULL, 'n/a'),
('Renette', 'Alce', '1976-12-20', 'Acul du Nord', 'Deep vein thrombosis 2022. Completed anticoagulation. Monitoring.', 'negative', NULL, 'n/a'),
('Estimable', 'Jean', '1984-05-09', 'Cap-Haitien', 'HIV. On antiretroviral therapy since 2019. Viral load undetectable.', 'positive', NULL, 'n/a'),
('Lovely', 'Neptune', '1997-10-31', 'Quartier Morin', 'Anemia of chronic disease. Under investigation.', 'negative', NULL, 'not pregnant'),
('Duckens', 'Louissaint', '1971-03-16', 'Plaine du Nord', 'Type 2 diabetes. Well controlled on metformin. HbA1c 6.8%.', 'negative', NULL, 'n/a'),
('Roselore', 'Thermilus', '1993-08-02', 'Milot', 'Hepatitis B vaccinated. Immune.', 'negative', NULL, 'n/a'),
('Ducasse', 'Renard', '1965-12-14', 'Cap-Haitien', 'Stroke 2023. Left-sided weakness. Rehabilitation ongoing. On aspirin.', 'negative', 'Post-stroke depression. On sertraline.', 'n/a'),
('Guerline', 'Thermidor', '2002-04-28', 'Limonade', 'No significant medical history.', 'negative', NULL, 'n/a'),
('Moise', 'Alexandre', '1979-07-11', 'Acul du Nord', 'Chronic otitis media. Recurrent. ENT follow-up.', 'negative', NULL, 'n/a');

-- Appointments (sample across clinics)
INSERT INTO appointments (patient_id, clinic_id, doctor_name, date, time, notes) VALUES
(1, 1, 'Dr. Jean-Baptiste Pierre', '2024-11-15', '08:30', 'Blood pressure check. Stable on current medication.'),
(2, 2, 'Dr. Wilner Augustin', '2024-11-15', '09:00', 'Diabetes follow-up. Foot exam scheduled.'),
(3, 3, 'Dr. Roseline Marcelin', '2024-11-14', '10:00', 'HIV management visit. Labs ordered.'),
(4, 1, 'Dr. Magalie Celestin', '2024-11-14', '11:00', 'Kidney function review. Creatinine recheck.'),
(5, 4, 'Dr. Jean-Baptiste Pierre', '2024-11-13', '08:00', 'Prenatal visit. 28 week checkup.'),
(6, 5, 'Dr. Roseline Marcelin', '2024-11-13', '09:30', 'Depression follow-up. Medication review.'),
(7, 6, 'Dr. Wilner Augustin', '2024-11-12', '10:30', 'Asthma review. Peak flow measurement.'),
(8, 1, 'Dr. Jean-Baptiste Pierre', '2024-11-12', '14:00', 'PTSD group therapy session.'),
(1, 1, 'Dr. Jean-Baptiste Pierre', '2024-10-15', '08:30', 'Routine follow-up. Medication refill.'),
(3, 3, 'Dr. Roseline Marcelin', '2024-10-14', '10:00', 'CD4 count check. Results pending.');

-- Prescriptions
INSERT INTO prescriptions (patient_id, medication, dosage, prescriber, date) VALUES
(1, 'Lisinopril', '10mg daily', 'Dr. Jean-Baptiste Pierre', '2024-11-15'),
(2, 'Metformin', '500mg twice daily', 'Dr. Wilner Augustin', '2024-11-15'),
(3, 'Tenofovir/Emtricitabine/Efavirenz', '1 tablet daily', 'Dr. Roseline Marcelin', '2024-11-14'),
(4, 'Amlodipine', '5mg daily', 'Dr. Magalie Celestin', '2024-11-14'),
(6, 'Fluoxetine', '20mg daily', 'Dr. Roseline Marcelin', '2024-11-13'),
(8, 'Carbamazepine', '200mg twice daily', 'Dr. Jean-Baptiste Pierre', '2024-11-12');

-- Lab results
INSERT INTO lab_results (patient_id, test_type, result, date, lab_technician) VALUES
(1, 'Blood pressure', '138/82 mmHg', '2024-11-15', 'Nadege Belizaire'),
(2, 'HbA1c', '7.2%', '2024-11-15', 'Nadege Belizaire'),
(3, 'CD4 count', '520 cells/mm3', '2024-11-14', 'Edmond Charles'),
(3, 'Viral load', 'Undetectable (<20 copies/mL)', '2024-11-14', 'Edmond Charles'),
(4, 'Creatinine', '1.8 mg/dL', '2024-11-14', 'Nadege Belizaire'),
(5, 'Hemoglobin', '10.2 g/dL', '2024-11-13', 'Nadege Belizaire'),
(14, 'CD4 count', '480 cells/mm3', '2024-11-10', 'Edmond Charles');