-- ============================================
-- Prescription System Migration
-- ============================================

-- Update prescriptions table structure
ALTER TABLE prescriptions 
    ADD COLUMN IF NOT EXISTS diet_advice TEXT,
    ADD COLUMN IF NOT EXISTS lifestyle_advice TEXT,
    ADD COLUMN IF NOT EXISTS follow_up_instructions TEXT,
    ADD COLUMN IF NOT EXISTS general_instructions TEXT,
    ADD COLUMN IF NOT EXISTS notes TEXT,
    MODIFY COLUMN prescription_date DATE;

-- Create prescription_remedies junction table
CREATE TABLE IF NOT EXISTS prescription_remedies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prescription_id INT NOT NULL,
    remedy_id INT NOT NULL,
    potency VARCHAR(20),
    dosage VARCHAR(100),
    duration VARCHAR(100),
    instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE,
    FOREIGN KEY (remedy_id) REFERENCES remedies(id) ON DELETE RESTRICT,
    
    INDEX idx_prescription (prescription_id),
    INDEX idx_remedy (remedy_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert Sample Remedies (35+)
-- ============================================
INSERT IGNORE INTO remedies (remedy_name, common_name, family, source, keynote_symptoms, clinical_indications, is_popular) VALUES
-- Constitutional Remedies
('Arsenicum Album', 'Arsenic Trioxide', 'Mineral', 'White Arsenic', 
 'Restlessness, anxiety, burning pains relieved by heat, fear of death, fastidious, chilly patient', 
 'Gastroenteritis, food poisoning, asthma, skin conditions, anxiety disorders', 1),

('Calcarea Carbonica', 'Calcium Carbonate', 'Mineral', 'Oyster Shell',
 'Slowness, obesity, profuse sweating, late dentition, craving for eggs, fear of heights',
 'Obesity, hypothyroidism, bone disorders, dental problems, anxiety', 1),

('Lycopodium Clavatum', 'Club Moss', 'Lycopodiaceae', 'Club Moss Spores',
 'Right-sided symptoms, flatulence 4-8 PM, lacks confidence, dictatorial at home',
 'Liver disorders, digestive problems, impotence, baldness, prostate issues', 1),

('Natrum Muriaticum', 'Common Salt', 'Mineral', 'Sodium Chloride',
 'Reserved nature, grief never expressed, craving for salt, worse from consolation, sun aggravates',
 'Chronic headaches, depression, grief, dry skin, cold sores, hair loss', 1),

('Phosphorus', 'Phosphorus', 'Mineral', 'Yellow Phosphorus',
 'Burning pains, thirst for cold water, fear of thunderstorms, sensitive, bleeding tendency',
 'Respiratory infections, pneumonia, bleeding disorders, anxiety, hepatitis', 1),

('Pulsatilla Nigricans', 'Wind Flower', 'Ranunculaceae', 'Pasque Flower',
 'Gentle, yielding nature, changeable symptoms, thirstless, worse heat, desires consolation',
 'Sinusitis, ear infections, menstrual disorders, digestive problems, pregnancy issues', 1),

('Sulphur', 'Sublimated Sulfur', 'Mineral', 'Sublimated Sulfur',
 'Dirty appearance, burning sensations, aggravation from heat/bathing, philosophical, lazy',
 'Skin diseases, hemorrhoids, chronic infections, digestive problems', 1),

-- Acute Remedies
('Aconitum Napellus', 'Monkshood', 'Ranunculaceae', 'Monkshood Root',
 'Sudden onset, fear/anxiety/panic, after exposure to cold dry wind, restlessness, fever',
 'Acute fever, panic attacks, after shock/fright, croup, acute infections', 1),

('Belladonna', 'Deadly Nightshade', 'Solanaceae', 'Deadly Nightshade',
 'Sudden violent onset, high fever, throbbing pains, red hot face, dilated pupils',
 'High fever, tonsillitis, headaches, sunstroke, convulsions', 1),

('Bryonia Alba', 'White Bryony', 'Cucurbitaceae', 'Wild Hops',
 'Worse from any motion, dry mucous membranes, irritability, thirst for large quantities',
 'Dry cough, pleurisy, constipation, rheumatism, headaches', 1),

('Chamomilla', 'German Chamomile', 'Compositae', 'Chamomile',
 'Extreme irritability, one cheek red/one pale, cannot bear pain, angry child demanding things',
 'Teething, colic, ear infections in children, toothache', 1),

('Nux Vomica', 'Poison Nut', 'Loganiaceae', 'Quaker Buttons',
 'Chilly, oversensitive, irritable, digestive issues, worse morning, effects of overindulgence',
 'Hangovers, indigestion, constipation, insomnia, irritability, stress', 1),

('Rhus Toxicodendron', 'Poison Ivy', 'Anacardiaceae', 'Poison Ivy',
 'Worse on initial movement/better continued motion, restless, worse cold/damp, skin eruptions',
 'Arthritis, sprains, shingles, eczema, influenza', 1),

-- Respiratory Remedies
('Antimonium Tartaricum', 'Tartar Emetic', 'Mineral', 'Antimony Potassium Tartrate',
 'Rattling cough, unable to expectorate, drowsiness, pale face, weak respiration',
 'Bronchitis, pneumonia in elderly/children, asthma with mucus', 0),

('Drosera Rotundifolia', 'Sundew', 'Droseraceae', 'Round-Leaved Sundew',
 'Spasmodic barking cough, worse after midnight, tickling in larynx, whooping cough',
 'Whooping cough, dry cough, laryngitis, tuberculosis', 0),

('Hepar Sulphuris Calcareum', 'Calcium Sulphide', 'Mineral', 'Hahnemann Calcium Sulphide',
 'Oversensitive to cold/pain/touch, suppurating conditions, splinter-like pains',
 'Abscesses, tonsillitis, ear infections, croup, skin infections', 0),

('Kali Bichromicum', 'Bichromate of Potash', 'Mineral', 'Potassium Bichromate',
 'Thick, stringy, ropy discharges, sinus pain at root of nose, post-nasal drip',
 'Sinusitis, chronic catarrh, migraine, ulcers', 0),

('Spongia Tosta', 'Roasted Sponge', 'Animal', 'Roasted Sponge',
 'Dry barking cough, croup, sawing respiration, worse before midnight',
 'Croup, thyroid disorders, dry cough, laryngitis', 0),

-- Digestive Remedies
('Argentum Nitricum', 'Silver Nitrate', 'Mineral', 'Silver Nitrate',
 'Anxiety/apprehension, craving sweets that disagree, diarrhea from anticipation',
 'Performance anxiety, stage fright, gastritis, diarrhea', 0),

('Carbo Vegetabilis', 'Vegetable Charcoal', 'Mineral', 'Wood Charcoal',
 'Weakness/collapse, air hunger, better from fanning, flatulence, sluggish circulation',
 'Flatulence, indigestion, collapse states, varicose veins', 0),

('Colocynthis', 'Bitter Cucumber', 'Cucurbitaceae', 'Bitter Apple',
 'Cramping abdominal pains better bending double/pressure, anger/indignation, colic',
 'Colic, abdominal cramps, sciatica, menstrual cramps', 0),

('Ipecacuanha', 'Ipecac Root', 'Rubiaceae', 'Ipecac',
 'Persistent nausea not relieved by vomiting, clean tongue, hemorrhage with nausea',
 'Nausea, vomiting, asthma, whooping cough, hemorrhage', 0),

-- Injury & First Aid
('Arnica Montana', 'Leopard Bane', 'Compositae', 'Mountain Arnica',
 'Says nothing wrong when injured, bruised soreness, trauma, shock, bed feels too hard',
 'Injuries, bruises, trauma, surgery recovery, muscle soreness', 1),

('Calendula Officinalis', 'Marigold', 'Compositae', 'Pot Marigold',
 'Promotes healing, antiseptic, prevents suppuration, injuries with loss of skin',
 'Cuts, wounds, burns, skin injuries, post-surgical healing', 0),

('Hypericum Perforatum', 'St. John Wort', 'Hypericaceae', 'St. John Wort',
 'Nerve injuries, shooting pains along nerves, crushed fingers/toes, spinal injuries',
 'Nerve injuries, dental nerve pain, puncture wounds, coccyx injuries', 0),

('Ledum Palustre', 'Marsh Tea', 'Ericaceae', 'Wild Rosemary',
 'Puncture wounds, insect bites, cold affected parts, better cold applications',
 'Puncture wounds, insect stings, black eye, tetanus prevention', 0),

('Ruta Graveolens', 'Rue', 'Rutaceae', 'Garden Rue',
 'Injuries to tendons/periosteum/cartilage, eyestrain, bruised bone pain',
 'Sprains, tendon injuries, eyestrain, tennis elbow, bone bruises', 0),

('Symphytum Officinale', 'Comfrey', 'Boraginaceae', 'Knitbone',
 'Bone injuries, promotes callus formation, eye injuries, fractures',
 'Fractures, bone injuries, black eye, blunt trauma to eyeball', 0),

-- Women's Health
('Caulophyllum Thalictroides', 'Blue Cohosh', 'Berberidaceae', 'Blue Cohosh',
 'Weak ineffectual labor pains, spasmodic pains, rigid os, habitual abortion',
 'Difficult labor, menstrual irregularities, uterine problems', 0),

('Cimicifuga Racemosa', 'Black Cohosh', 'Ranunculaceae', 'Black Snake Root',
 'Rheumatic pains, menstrual disorders, depression with menses, fear of insanity',
 'Menstrual problems, labor pains, headaches, depression, neck stiffness', 0),

('Sepia Officinalis', 'Cuttlefish Ink', 'Animal', 'Cuttlefish',
 'Indifference to loved ones, bearing down sensation, worse pregnancy/menses, better exercise',
 'Hormonal issues, menopause, pregnancy problems, prolapse, depression', 1),

-- Mental/Emotional
('Gelsemium Sempervirens', 'Yellow Jasmine', 'Loganiaceae', 'Yellow Jasmine',
 'Anticipatory anxiety, drowsy/dizzy/droopy, trembling, weakness, paralysis',
 'Flu, stage fright, exam anxiety, dizziness, vision problems', 0),

('Ignatia Amara', 'St. Ignatius Bean', 'Loganiaceae', 'St. Ignatius Bean',
 'Acute grief, contradictory symptoms, hysterical, sighing, lump in throat',
 'Acute grief, emotional trauma, hysteria, headaches, spasms', 1),

-- Skin Remedies
('Graphites', 'Black Lead', 'Mineral', 'Graphite',
 'Thick sticky discharges, cracks/fissures, obesity, chilly, sluggish',
 'Eczema, psoriasis, keloids, chronic skin conditions', 0),

('Mezereum', 'Spurge Olive', 'Thymelaeaceae', 'Daphne',
 'Neuralgic pains, herpes zoster, eczema with thick crusts, worse cold',
 'Shingles, eczema, neuralgia after shingles', 0),

('Petroleum', 'Rock Oil', 'Mineral', 'Crude Petroleum',
 'Deep cracks in skin especially winter, worse cold, motion sickness',
 'Eczema, psoriasis, motion sickness, chilblains', 0);

-- ============================================
-- Mark Popular Remedies
-- ============================================
UPDATE remedies SET is_popular = 1 WHERE remedy_name IN (
    'Arsenicum Album', 'Calcarea Carbonica', 'Lycopodium Clavatum',
    'Natrum Muriaticum', 'Phosphorus', 'Pulsatilla Nigricans',
    'Sulphur', 'Aconitum Napellus', 'Belladonna', 'Bryonia Alba',
    'Nux Vomica', 'Rhus Toxicodendron', 'Arnica Montana', 'Ignatia Amara', 'Sepia Officinalis'
);
