-- First level categories
INSERT INTO categories (name, parent_id, level) VALUES
('Cardiovascular', NULL, 1);
SET @cardiovascular_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Mental Health', NULL, 1);
SET @mental_health_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Neurological', NULL, 1);
SET @neurological_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Pain & Inflammation', NULL, 1);
SET @pain_inflammation_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Respiratory', NULL, 1);
SET @respiratory_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Gastrointestinal', NULL, 1);
SET @gi_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Endocrine/Hormonal', NULL, 1);
SET @endocrine_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Preventive Care', NULL, 1);
SET @preventive_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Skin/Dermatological', NULL, 1);
SET @skin_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Immunology & Allergies', NULL, 1);
SET @immunology_id = LAST_INSERT_ID();

-- Second level categories - Cardiovascular
INSERT INTO categories (name, parent_id, level) VALUES
('Heart Conditions', @cardiovascular_id, 2);
SET @heart_conditions_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Blood Pressure', @cardiovascular_id, 2);
SET @blood_pressure_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Anticoagulation', @cardiovascular_id, 2);
SET @anticoagulation_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Cholesterol Management', @cardiovascular_id, 2);
SET @cholesterol_id = LAST_INSERT_ID();

-- Second level categories - Mental Health
INSERT INTO categories (name, parent_id, level) VALUES
('Anxiety Disorders', @mental_health_id, 2);
SET @anxiety_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Depression', @mental_health_id, 2);
SET @depression_id = LAST_INSERT_ID();

-- Second level categories - Neurological (including Migraine for Fioricet)
INSERT INTO categories (name, parent_id, level) VALUES
('Seizures', @neurological_id, 2);
SET @seizures_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Migraine', @pain_inflammation_id, 2);
SET @migraine_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('RLS', @neurological_id, 2);
SET @rls_id = LAST_INSERT_ID();

-- Pain & Inflammation categories
INSERT INTO categories (name, parent_id, level) VALUES
('Musculoskeletal', @pain_inflammation_id, 2);
SET @musculoskeletal_id = LAST_INSERT_ID();

-- Respiratory categories
INSERT INTO categories (name, parent_id, level) VALUES
('Nasal Conditions', @respiratory_id, 2);
SET @nasal_conditions_id = LAST_INSERT_ID();

-- Under Preventive Care
INSERT INTO categories (name, parent_id, level) VALUES
('Surgical Prophylaxis', @preventive_id, 2);
SET @surgical_prophylaxis_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('UTI Prevention', @preventive_id, 2);
SET @uti_prevention_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Stroke Prevention', @preventive_id, 2);
SET @stroke_prevention_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Osteoporosis Prevention', @preventive_id, 2);
SET @osteoporosis_prevention_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Prophylaxis', @preventive_id, 2);
SET @prophylaxis_id = LAST_INSERT_ID();

-- Under Prophylaxis
INSERT INTO categories (name, parent_id, level) VALUES
('Bacterial', @prophylaxis_id, 3);
SET @bacterial_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Fungal', @prophylaxis_id, 3);
SET @fungal_id = LAST_INSERT_ID();

INSERT INTO categories (name, parent_id, level) VALUES
('Viral', @prophylaxis_id, 3);
SET @viral_id = LAST_INSERT_ID();


-- Insert conditions
INSERT INTO conditions (name, therapeutic_use) VALUES
-- Cardiovascular
('Heart Failure', 'Treatment of heart failure'),
('Atrial Fibrillation', 'Management of irregular heartbeat'),

-- Mental Health
('GAD', 'Treatment of generalized anxiety disorder'),
('PTSD', 'Treatment of post-traumatic stress disorder'),
('OCD', 'Treatment of obsessive compulsive disorder'),
('Panic Disorder', 'Treatment of panic attacks and anxiety'),
('Depression', 'Treatment of major depressive disorder'),
('ADHD', 'Treatment of attention deficit hyperactivity disorder'),
('Bipolar', 'Treatment of bipolar disorder'),
('Sleep Disorders', 'Management of sleep disorders'),

-- Neurological
('Parkinson''s', 'Treatment of Parkinson''s disease'),
('Essential Tremor', 'Treatment of tremors'),
('Neuropathic Pain', 'Management of nerve pain'),

-- Pain & Inflammation
('Arthritis', 'Treatment of joint inflammation and pain'),
('Fibromyalgia', 'Management of chronic widespread pain'),
('Chronic Pain', 'Management of long-term pain conditions'),

-- Respiratory
('Asthma', 'Management of asthma symptoms'),
('COPD', 'Treatment of chronic obstructive pulmonary disease'),
('Rhinitis', 'Treatment of nasal inflammation'),
('Polyps', 'Treatment of nasal polyps'),

-- Gastrointestinal
('Acid Reflux', 'Treatment of acid reflux and GERD'),
('IBS', 'Management of irritable bowel syndrome'),
('Gastroparesis', 'Treatment of delayed gastric emptying'),
('H. pylori', 'Treatment of H. pylori infection'),

-- Endocrine
('Diabetes', 'Management of blood sugar levels'),
('Thyroid Conditions', 'Treatment of thyroid disorders'),
('PCOS', 'Management of polycystic ovary syndrome'),
('Menopausal Symptoms', 'Management of menopause symptoms'),

-- Infectious
('MRSA', 'Treatment of resistant staph infections'),
('Anthrax', 'Treatment of anthrax infection'),
('Valley Fever', 'Treatment of coccidioidomycosis'),
('CMV', 'Treatment of cytomegalovirus infection'),

-- Skin
('Acne', 'Treatment of acne vulgaris'),
('Rosacea', 'Treatment of facial redness and inflammation'),
('Eczema', 'Treatment of atopic dermatitis'),
('Psoriasis', 'Treatment of psoriasis');

-- Link categories with conditions
INSERT INTO category_conditions (category_id, condition_id) 
SELECT 11, id FROM conditions WHERE name IN ('Heart Failure', 'Atrial Fibrillation');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 15, id FROM conditions WHERE name IN ('GAD', 'PTSD', 'OCD', 'Panic Disorder');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 16, id FROM conditions WHERE name = 'Depression';

INSERT INTO category_conditions (category_id, condition_id)
SELECT 17, id FROM conditions WHERE name IN ('Seizures', 'Epilepsy');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 18, id FROM conditions WHERE name = 'Migraine';

INSERT INTO category_conditions (category_id, condition_id)
SELECT 19, id FROM conditions WHERE name = 'RLS';

INSERT INTO category_conditions (category_id, condition_id)
SELECT 20, id FROM conditions WHERE name IN ('Arthritis', 'Fibromyalgia');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 21, id FROM conditions WHERE name IN ('Rhinitis', 'Polyps');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 22, id FROM conditions WHERE name IN ('MRSA', 'Anthrax');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 23, id FROM conditions WHERE name = 'Valley Fever';

INSERT INTO category_conditions (category_id, condition_id)
SELECT 24, id FROM conditions WHERE name = 'CMV';

-- Additional category_conditions relationships
INSERT INTO category_conditions (category_id, condition_id)
SELECT 2, id FROM conditions WHERE name IN ('ADHD', 'Bipolar', 'Sleep Disorders');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 3, id FROM conditions WHERE name IN ('Parkinson''s', 'Essential Tremor', 'Neuropathic Pain');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 4, id FROM conditions WHERE name = 'Chronic Pain';

INSERT INTO category_conditions (category_id, condition_id)
SELECT 5, id FROM conditions WHERE name IN ('Asthma', 'COPD');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 6, id FROM conditions WHERE name IN ('Acid Reflux', 'IBS', 'Gastroparesis', 'H. pylori');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 7, id FROM conditions WHERE name IN ('Diabetes', 'Thyroid Conditions', 'PCOS', 'Menopausal Symptoms');

INSERT INTO category_conditions (category_id, condition_id)
SELECT 9, id FROM conditions WHERE name IN ('Acne', 'Rosacea', 'Eczema', 'Psoriasis');


-- Add conditions
INSERT INTO conditions (name, therapeutic_use) VALUES
('Allergic Rhinitis', 'Treatment of hay fever and seasonal allergies'),
('Food Allergies', 'Management of food-related allergic reactions'),
('Hives', 'Treatment of urticaria and allergic skin reactions'),
('Anaphylaxis', 'Emergency treatment of severe allergic reactions');

-- Link conditions to categories
INSERT INTO category_conditions (category_id, condition_id)
SELECT @immunology_id, id FROM conditions 
WHERE name IN ('Allergic Rhinitis', 'Food Allergies', 'Hives', 'Anaphylaxis');

-- Cross-link with other categories
INSERT INTO category_conditions (category_id, condition_id)
SELECT 5, id FROM conditions WHERE name = 'Allergic Rhinitis';  -- Respiratory

INSERT INTO category_conditions (category_id, condition_id)
SELECT 9, id FROM conditions WHERE name = 'Hives';  -- Dermatological

INSERT INTO category_conditions (category_id, condition_id)
SELECT 6, id FROM conditions WHERE name = 'Food Allergies';  -- Gastrointestinal

-- Surgical Prophylaxis
INSERT INTO conditions (name, therapeutic_use) VALUES
('Infection Prevention in Surgery', 'Prophylactic antibiotics to prevent infections after surgery'),
('Postoperative Complications', 'Prevention of blood clots, wound infections, and other complications');

-- UTI Prevention
INSERT INTO conditions (name, therapeutic_use) VALUES
('Recurrent Urinary Tract Infections (UTIs)', 'Prophylactic antibiotics to reduce recurrent UTIs'),
('Catheter-Associated Urinary Tract Infections', 'Prevention strategies for patients with indwelling catheters');

-- Stroke Prevention
INSERT INTO conditions (name, therapeutic_use) VALUES
('Atrial Fibrillation', 'Anticoagulant therapy to prevent strokes caused by blood clots in AFib'),
('Hypertension', 'Preventive treatment to manage high blood pressure and reduce stroke risk'),
('Carotid Artery Stenosis', 'Prevention of stroke through surgery or medical management of blocked carotid arteries');

-- Osteoporosis Prevention
INSERT INTO conditions (name, therapeutic_use) VALUES
('Osteopenia', 'Preventive treatment to stop progression to osteoporosis'),
('Vitamin D and Calcium Deficiency', 'Preventive measures to maintain bone health');

-- Insert conditions under Prophylaxis (Bacterial, Fungal, Viral)

-- Bacterial Prophylaxis
INSERT INTO conditions (name, therapeutic_use) VALUES
('Bacterial Endocarditis', 'Prophylactic antibiotics before dental or surgical procedures for high-risk patients'),
('Bacterial Pneumonia', 'Vaccination (Pneumococcal) to prevent pneumonia'),
('Meningococcal Disease', 'Vaccination to prevent bacterial meningitis');

-- Fungal Prophylaxis
INSERT INTO conditions (name, therapeutic_use) VALUES
('Invasive Candidiasis', 'Antifungal prophylaxis in immunocompromised patients'),
('Aspergillosis', 'Prophylactic antifungal treatment for high-risk patients'),
('Cryptococcal Meningitis', 'Prophylaxis with antifungals for patients with HIV/AIDS');

-- Viral Prophylaxis
INSERT INTO conditions (name, therapeutic_use) VALUES
('HIV Prophylaxis (PrEP)', 'Pre-exposure prophylaxis for HIV prevention'),
('Influenza Prevention', 'Annual flu vaccination to prevent infection'),
('Hepatitis B', 'Vaccination for at-risk individuals to prevent infection');

-- Link Preventive Care categories with conditions

-- UTI Prevention
INSERT INTO category_conditions (category_id, condition_id)
SELECT @uti_prevention_id, id FROM conditions WHERE name IN ('Recurrent Urinary Tract Infections (UTIs)', 'Catheter-Associated Urinary Tract Infections');

-- Stroke Prevention
INSERT INTO category_conditions (category_id, condition_id)
SELECT @stroke_prevention_id, id FROM conditions WHERE name IN ('Atrial Fibrillation', 'Hypertension', 'Carotid Artery Stenosis');

-- Osteoporosis Prevention
INSERT INTO category_conditions (category_id, condition_id)
SELECT @osteoporosis_prevention_id, id FROM conditions WHERE name IN ('Osteopenia', 'Vitamin D and Calcium Deficiency');

-- Surgical Prophylaxis
INSERT INTO category_conditions (category_id, condition_id)
SELECT @surgical_prophylaxis_id, id FROM conditions WHERE name IN ('Infection Prevention in Surgery', 'Postoperative Complications');

-- Link Prophylaxis categories with conditions

-- Bacterial Prophylaxis
INSERT INTO category_conditions (category_id, condition_id)
SELECT @bacterial_id, id FROM conditions WHERE name IN ('Bacterial Endocarditis', 'Bacterial Pneumonia', 'Meningococcal Disease');

-- Fungal Prophylaxis
INSERT INTO category_conditions (category_id, condition_id)
SELECT @fungal_id, id FROM conditions WHERE name IN ('Invasive Candidiasis', 'Aspergillosis', 'Cryptococcal Meningitis');

-- Viral Prophylaxis
INSERT INTO category_conditions (category_id, condition_id)
SELECT @viral_id, id FROM conditions WHERE name IN ('HIV Prophylaxis (PrEP)', 'Influenza Prevention', 'Hepatitis B');