-- Migration: Add Sample Repertory Data
-- Date: November 24, 2025
-- Purpose: Populate repertory and repertory_remedies tables with sample data

USE homeopathic_system;

-- ============================================
-- Insert Sample Repertory Rubrics
-- ============================================

-- MIND rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Anxiety', 'mind', 'emotions', 'MIND - ANXIETY', 'Kent'),
('Anxiety, conscience, as if guilty of a crime', 'mind', 'emotions', 'MIND - ANXIETY - conscience, as if guilty of a crime', 'Kent'),
('Anxiety, fear, with', 'mind', 'emotions', 'MIND - ANXIETY - fear, with', 'Kent'),
('Anxiety, health, about', 'mind', 'emotions', 'MIND - ANXIETY - health, about', 'Kent'),
('Fear, death, of', 'mind', 'emotions', 'MIND - FEAR - death, of', 'Kent'),
('Fear, alone, of being', 'mind', 'emotions', 'MIND - FEAR - alone, of being', 'Kent'),
('Irritability', 'mind', 'disposition', 'MIND - IRRITABILITY', 'Kent'),
('Irritability, morning', 'mind', 'disposition', 'MIND - IRRITABILITY - morning', 'Kent'),
('Sadness, grief', 'mind', 'emotions', 'MIND - SADNESS - grief', 'Kent'),
('Weeping, easily', 'mind', 'emotions', 'MIND - WEEPING - easily', 'Kent'),
('Restlessness', 'mind', 'general', 'MIND - RESTLESSNESS', 'Kent'),
('Confusion of mind', 'mind', 'intellect', 'MIND - CONFUSION of mind', 'Kent'),
('Memory, weakness of', 'mind', 'intellect', 'MIND - MEMORY - weakness of', 'Kent');

-- HEAD rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Pain, headache', 'head', 'pain', 'HEAD - PAIN - headache', 'Kent'),
('Pain, forehead', 'head', 'pain', 'HEAD - PAIN - forehead', 'Kent'),
('Pain, temples', 'head', 'pain', 'HEAD - PAIN - temples', 'Kent'),
('Pain, throbbing', 'head', 'pain', 'HEAD - PAIN - throbbing', 'Kent'),
('Pain, bursting', 'head', 'pain', 'HEAD - PAIN - bursting', 'Kent'),
('Pain, worse motion', 'head', 'pain', 'HEAD - PAIN - worse motion', 'Kent'),
('Pain, better pressure', 'head', 'pain', 'HEAD - PAIN - better pressure', 'Kent'),
('Heaviness', 'head', 'sensation', 'HEAD - HEAVINESS', 'Kent'),
('Vertigo', 'head', 'general', 'HEAD - VERTIGO', 'Kent');

-- STOMACH rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Nausea', 'stomach', 'general', 'STOMACH - NAUSEA', 'Kent'),
('Nausea, morning', 'stomach', 'general', 'STOMACH - NAUSEA - morning', 'Kent'),
('Vomiting', 'stomach', 'general', 'STOMACH - VOMITING', 'Kent'),
('Appetite, increased', 'stomach', 'appetite', 'STOMACH - APPETITE - increased', 'Kent'),
('Appetite, wanting', 'stomach', 'appetite', 'STOMACH - APPETITE - wanting', 'Kent'),
('Thirst, extreme', 'stomach', 'thirst', 'STOMACH - THIRST - extreme', 'Kent'),
('Thirstless', 'stomach', 'thirst', 'STOMACH - THIRSTLESS', 'Kent'),
('Pain, burning', 'stomach', 'pain', 'STOMACH - PAIN - burning', 'Kent'),
('Indigestion', 'stomach', 'general', 'STOMACH - INDIGESTION', 'Kent');

-- RESPIRATORY rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Cough', 'respiratory', 'general', 'RESPIRATORY - COUGH', 'Kent'),
('Cough, dry', 'respiratory', 'cough', 'RESPIRATORY - COUGH - dry', 'Kent'),
('Cough, night', 'respiratory', 'cough', 'RESPIRATORY - COUGH - night', 'Kent'),
('Cough, spasmodic', 'respiratory', 'cough', 'RESPIRATORY - COUGH - spasmodic', 'Kent'),
('Dyspnea, difficulty breathing', 'respiratory', 'breathing', 'RESPIRATORY - DYSPNEA', 'Kent'),
('Asthma', 'respiratory', 'general', 'RESPIRATORY - ASTHMA', 'Kent'),
('Expectoration, difficult', 'respiratory', 'expectoration', 'RESPIRATORY - EXPECTORATION - difficult', 'Kent');

-- FEVER rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Fever, high', 'fever', 'general', 'FEVER - high', 'Kent'),
('Fever, afternoon', 'fever', 'time', 'FEVER - afternoon', 'Kent'),
('Fever, evening', 'fever', 'time', 'FEVER - evening', 'Kent'),
('Fever, night', 'fever', 'time', 'FEVER - night', 'Kent'),
('Chill, coldness', 'fever', 'chill', 'FEVER - CHILL', 'Kent'),
('Heat, burning', 'fever', 'heat', 'FEVER - HEAT - burning', 'Kent');

-- SKIN rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Eruptions', 'skin', 'general', 'SKIN - ERUPTIONS', 'Kent'),
('Itching', 'skin', 'general', 'SKIN - ITCHING', 'Kent'),
('Burning', 'skin', 'sensation', 'SKIN - BURNING', 'Kent'),
('Dryness', 'skin', 'general', 'SKIN - DRYNESS', 'Kent'),
('Ulcers', 'skin', 'lesions', 'SKIN - ULCERS', 'Kent');

-- SLEEP rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Sleeplessness, insomnia', 'sleep', 'general', 'SLEEP - SLEEPLESSNESS', 'Kent'),
('Sleeplessness, thoughts, from', 'sleep', 'causes', 'SLEEP - SLEEPLESSNESS - thoughts, from', 'Kent'),
('Dreams, vivid', 'sleep', 'dreams', 'SLEEP - DREAMS - vivid', 'Kent'),
('Dreams, nightmare', 'sleep', 'dreams', 'SLEEP - DREAMS - nightmare', 'Kent'),
('Restless sleep', 'sleep', 'general', 'SLEEP - RESTLESS', 'Kent');

-- EXTREMITIES rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Pain, joints', 'extremities', 'pain', 'EXTREMITIES - PAIN - joints', 'Kent'),
('Pain, worse motion', 'extremities', 'pain', 'EXTREMITIES - PAIN - worse motion', 'Kent'),
('Pain, better motion', 'extremities', 'pain', 'EXTREMITIES - PAIN - better motion', 'Kent'),
('Stiffness', 'extremities', 'sensation', 'EXTREMITIES - STIFFNESS', 'Kent'),
('Weakness', 'extremities', 'general', 'EXTREMITIES - WEAKNESS', 'Kent');

-- GENERAL rubrics
INSERT IGNORE INTO repertory (rubric, category, sub_category, complete_rubric, repertory_source) VALUES
('Weakness, general', 'general', 'general', 'GENERAL - WEAKNESS', 'Kent'),
('Chilly patient', 'general', 'temperature', 'GENERAL - CHILLY', 'Kent'),
('Hot patient', 'general', 'temperature', 'GENERAL - HOT', 'Kent'),
('Better cold applications', 'general', 'modalities', 'GENERAL - BETTER - cold applications', 'Kent'),
('Worse cold', 'general', 'modalities', 'GENERAL - WORSE - cold', 'Kent'),
('Better warmth', 'general', 'modalities', 'GENERAL - BETTER - warmth', 'Kent');

-- ============================================
-- Link Remedies to Rubrics with Grades
-- ============================================

-- Get remedy IDs (assuming remedies from prescription migration exist)
SET @arsenicum = (SELECT id FROM remedies WHERE remedy_name = 'Arsenicum Album' LIMIT 1);
SET @belladonna = (SELECT id FROM remedies WHERE remedy_name = 'Belladonna' LIMIT 1);
SET @bryonia = (SELECT id FROM remedies WHERE remedy_name = 'Bryonia Alba' LIMIT 1);
SET @nux = (SELECT id FROM remedies WHERE remedy_name = 'Nux Vomica' LIMIT 1);
SET @pulsatilla = (SELECT id FROM remedies WHERE remedy_name = 'Pulsatilla Nigricans' LIMIT 1);
SET @phosphorus = (SELECT id FROM remedies WHERE remedy_name = 'Phosphorus' LIMIT 1);
SET @sulphur = (SELECT id FROM remedies WHERE remedy_name = 'Sulphur' LIMIT 1);
SET @aconitum = (SELECT id FROM remedies WHERE remedy_name = 'Aconitum Napellus' LIMIT 1);
SET @chamomilla = (SELECT id FROM remedies WHERE remedy_name = 'Chamomilla' LIMIT 1);
SET @gelsemium = (SELECT id FROM remedies WHERE remedy_name = 'Gelsemium Sempervirens' LIMIT 1);
SET @ignatia = (SELECT id FROM remedies WHERE remedy_name = 'Ignatia Amara' LIMIT 1);
SET @natrum_mur = (SELECT id FROM remedies WHERE remedy_name = 'Natrum Muriaticum' LIMIT 1);
SET @rhus_tox = (SELECT id FROM remedies WHERE remedy_name = 'Rhus Toxicodendron' LIMIT 1);
SET @ipecac = (SELECT id FROM remedies WHERE remedy_name = 'Ipecacuanha' LIMIT 1);

-- MIND - Anxiety
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Anxiety' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @aconitum, 3 FROM repertory WHERE rubric = 'Anxiety' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @phosphorus, 2 FROM repertory WHERE rubric = 'Anxiety' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @gelsemium, 2 FROM repertory WHERE rubric = 'Anxiety' LIMIT 1;

-- MIND - Fear of death
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @aconitum, 3 FROM repertory WHERE rubric = 'Fear, death, of' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Fear, death, of' LIMIT 1;

-- MIND - Fear of being alone
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @phosphorus, 3 FROM repertory WHERE rubric = 'Fear, alone, of being' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 2 FROM repertory WHERE rubric = 'Fear, alone, of being' LIMIT 1;

-- MIND - Irritability
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @nux, 3 FROM repertory WHERE rubric = 'Irritability' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @chamomilla, 3 FROM repertory WHERE rubric = 'Irritability' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @bryonia, 2 FROM repertory WHERE rubric = 'Irritability' LIMIT 1;

-- MIND - Sadness, grief
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @ignatia, 3 FROM repertory WHERE rubric = 'Sadness, grief' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @natrum_mur, 3 FROM repertory WHERE rubric = 'Sadness, grief' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @pulsatilla, 2 FROM repertory WHERE rubric = 'Sadness, grief' LIMIT 1;

-- MIND - Weeping easily
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @pulsatilla, 3 FROM repertory WHERE rubric = 'Weeping, easily' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @natrum_mur, 2 FROM repertory WHERE rubric = 'Weeping, easily' LIMIT 1;

-- MIND - Restlessness
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Restlessness' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @rhus_tox, 3 FROM repertory WHERE rubric = 'Restlessness' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @aconitum, 2 FROM repertory WHERE rubric = 'Restlessness' LIMIT 1;

-- HEAD - Pain, headache
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @belladonna, 3 FROM repertory WHERE rubric = 'Pain, headache' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @bryonia, 3 FROM repertory WHERE rubric = 'Pain, headache' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @nux, 2 FROM repertory WHERE rubric = 'Pain, headache' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @gelsemium, 2 FROM repertory WHERE rubric = 'Pain, headache' LIMIT 1;

-- HEAD - Pain, throbbing
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @belladonna, 3 FROM repertory WHERE rubric = 'Pain, throbbing' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @gelsemium, 2 FROM repertory WHERE rubric = 'Pain, throbbing' LIMIT 1;

-- HEAD - Pain, worse motion
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @bryonia, 3 FROM repertory WHERE rubric = 'Pain, worse motion' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @belladonna, 2 FROM repertory WHERE rubric = 'Pain, worse motion' LIMIT 1;

-- STOMACH - Nausea
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @ipecac, 3 FROM repertory WHERE rubric = 'Nausea' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @nux, 3 FROM repertory WHERE rubric = 'Nausea' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @pulsatilla, 2 FROM repertory WHERE rubric = 'Nausea' LIMIT 1;

-- STOMACH - Vomiting
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @ipecac, 3 FROM repertory WHERE rubric = 'Vomiting' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 2 FROM repertory WHERE rubric = 'Vomiting' LIMIT 1;

-- STOMACH - Thirst, extreme
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @bryonia, 3 FROM repertory WHERE rubric = 'Thirst, extreme' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @phosphorus, 3 FROM repertory WHERE rubric = 'Thirst, extreme' LIMIT 1;

-- STOMACH - Thirstless
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @pulsatilla, 3 FROM repertory WHERE rubric = 'Thirstless' LIMIT 1;

-- STOMACH - Pain, burning
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Pain, burning' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @sulphur, 2 FROM repertory WHERE rubric = 'Pain, burning' LIMIT 1;

-- RESPIRATORY - Cough, dry
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @bryonia, 3 FROM repertory WHERE rubric = 'Cough, dry' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @phosphorus, 2 FROM repertory WHERE rubric = 'Cough, dry' LIMIT 1;

-- FEVER - High fever
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @belladonna, 3 FROM repertory WHERE rubric = 'Fever, high' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @aconitum, 3 FROM repertory WHERE rubric = 'Fever, high' LIMIT 1;

-- SKIN - Burning
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Burning' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @sulphur, 3 FROM repertory WHERE rubric = 'Burning' LIMIT 1;

-- SKIN - Itching
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @sulphur, 3 FROM repertory WHERE rubric = 'Itching' LIMIT 1;

-- SLEEP - Sleeplessness, insomnia
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @nux, 3 FROM repertory WHERE rubric = 'Sleeplessness, insomnia' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 2 FROM repertory WHERE rubric = 'Sleeplessness, insomnia' LIMIT 1;

-- EXTREMITIES - Pain, better motion
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @rhus_tox, 3 FROM repertory WHERE rubric = 'Pain, better motion' LIMIT 1;

-- EXTREMITIES - Pain, worse motion
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @bryonia, 3 FROM repertory WHERE rubric = 'Pain, worse motion' LIMIT 1;

-- GENERAL - Chilly patient
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Chilly patient' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @nux, 3 FROM repertory WHERE rubric = 'Chilly patient' LIMIT 1;

-- GENERAL - Hot patient
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @belladonna, 3 FROM repertory WHERE rubric = 'Hot patient' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @sulphur, 3 FROM repertory WHERE rubric = 'Hot patient' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @pulsatilla, 2 FROM repertory WHERE rubric = 'Hot patient' LIMIT 1;

-- GENERAL - Better cold applications
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @belladonna, 3 FROM repertory WHERE rubric = 'Better cold applications' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @pulsatilla, 3 FROM repertory WHERE rubric = 'Better cold applications' LIMIT 1;

-- GENERAL - Better warmth
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @arsenicum, 3 FROM repertory WHERE rubric = 'Better warmth' LIMIT 1;
INSERT IGNORE INTO repertory_remedies (repertory_id, remedy_id, grade)
SELECT id, @nux, 3 FROM repertory WHERE rubric = 'Better warmth' LIMIT 1;
