-- Migration: Update Prescriptions Schema
-- Date: November 24, 2025
-- Purpose: Update prescriptions table structure and add prescription_remedies table

USE homeopathic_system;

-- ============================================
-- Update PRESCRIPTIONS TABLE
-- ============================================
ALTER TABLE prescriptions
    -- Add new columns if they don't exist
    ADD COLUMN IF NOT EXISTS diet_advice TEXT AFTER prescription_date,
    ADD COLUMN IF NOT EXISTS lifestyle_advice TEXT AFTER diet_advice,
    ADD COLUMN IF NOT EXISTS follow_up_instructions TEXT AFTER lifestyle_advice,
    ADD COLUMN IF NOT EXISTS general_instructions TEXT AFTER follow_up_instructions,
    ADD COLUMN IF NOT EXISTS notes TEXT AFTER general_instructions,
    
    -- Change prescription_date column type
    MODIFY COLUMN prescription_date DATE;

-- ============================================
-- Create PRESCRIPTION_REMEDIES 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(50) DEFAULT '30C',
    dosage VARCHAR(255) DEFAULT 'TDS',
    duration VARCHAR(100) DEFAULT '7 days',
    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 CASCADE,
    INDEX idx_prescription (prescription_id),
    INDEX idx_remedy (remedy_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert Sample Remedies
-- ============================================
INSERT IGNORE INTO remedies (remedy_name, common_name, family, source, keynote_symptoms, clinical_indications, is_popular) VALUES
-- Constitutional Remedies
('Arsenicum Album', 'Arsenic Trioxide', 'Ranunculaceae', 'Arsenic Trioxide', 
 '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',
 'Depression, headaches, anemia, skin conditions, chronic fatigue'),

('Phosphorus', 'Phosphorus', 'mineral', 'Yellow Phosphorus', 'Bleeding tendency with anxiety',
 'Hemorrhagic tendency, fear of being alone, desires cold drinks, sympathetic nature',
 'Bleeding disorders, respiratory issues, liver disease, anxiety'),

('Pulsatilla Nigricans', 'Wind Flower', 'plant', 'Pulsatilla Plant', 'Changeable symptoms, gentle temperament',
 'Thirstless, desires open air, weeps easily, changeable symptoms, desires sympathy',
 'Hormonal disorders, sinusitis, digestive complaints, emotional issues'),

('Sulphur', 'Sublimated Sulphur', 'mineral', 'Sublimated Sulphur', 'Skin remedy with burning sensations',
 'Burning sensations, hot patient, untidy, philosophical, skin eruptions',
 'Skin conditions, digestive disorders, respiratory issues, chronic diseases'),

-- Acute Remedies
('Aconitum Napellus', 'Monkshood', 'plant', 'Aconitum Plant', 'Sudden violent onset with fear',
 'Sudden onset, fear of death, restlessness, fever with anxiety, after cold dry wind',
 'Acute fever, panic attacks, shock, inflammation'),

('Belladonna', 'Deadly Nightshade', 'plant', 'Belladonna Plant', 'Hot, red, throbbing pains',
 'Sudden violent onset, bright red inflammation, throbbing pains, high fever, dilated pupils',
 'High fever, headaches, sore throat, inflammation, sunstroke'),

('Bryonia Alba', 'White Bryony', 'plant', 'Bryonia Root', 'Dryness and aversion to movement',
 'Worse from motion, better from pressure, dryness of mucous membranes, irritable',
 'Dry cough, constipation, arthritis, headaches, pleurisy'),

('Chamomilla', 'German Chamomile', 'plant', 'Chamomile Flowers', 'Irritability and oversensitivity to pain',
 'Extreme irritability, oversensitive to pain, one cheek red, demanding, teething children',
 'Teething problems, colic, irritability, toothache, earache'),

('Nux Vomica', 'Poison Nut', 'plant', 'Strychnos Nux-vomica Seeds', 'Over-indulgence and digestive complaints',
 'Chilly, irritable, digestive complaints from overeating, competitive, workaholic',
 'Digestive disorders, constipation, hangovers, insomnia, back pain'),

('Rhus Toxicodendron', 'Poison Ivy', 'plant', 'Poison Ivy Leaves', 'Stiffness better from motion',
 'Stiffness worse on first motion then better from continued motion, restless, worse in cold damp',
 'Arthritis, sprains, skin eruptions, flu, back pain'),

-- Respiratory Remedies
('Antimonium Tartaricum', 'Tartar Emetic', 'mineral', 'Antimony Potassium Tartrate', 'Rattling cough with weakness',
 'Rattling chest but difficult expectoration, weakness, drowsiness, white-coated tongue',
 'Bronchitis, pneumonia, respiratory weakness'),

('Drosera Rotundifolia', 'Sundew', 'plant', 'Sundew Plant', 'Deep barking cough',
 'Deep barking cough, worse after midnight, whooping cough, holds chest when coughing',
 'Whooping cough, laryngitis, asthma'),

('Hepar Sulphuris Calcareum', 'Hahnemann\'s Calcium Sulphide', 'mineral', 'Calcium Sulphide', 'Suppuration and extreme sensitivity',
 'Extreme sensitivity to cold and touch, splinter-like pains, suppuration tendency',
 'Abscesses, tonsillitis, skin infections, croup'),

('Kali Bichromicum', 'Potassium Bichromate', 'mineral', 'Potassium Bichromate', 'Stringy thick discharges',
 'Thick, stringy, yellow-green discharges, sinus pain at root of nose, ulcers',
 'Sinusitis, ulcers, rhinitis, migraine'),

('Spongia Tosta', 'Roasted Sponge', 'animal', 'Roasted Sea Sponge', 'Dry barking cough',
 'Dry, barking cough sounds like saw through wood, worse before midnight, croup',
 'Croup, dry cough, thyroid disorders, asthma'),

-- Digestive Remedies
('Argentum Nitricum', 'Silver Nitrate', 'mineral', 'Silver Nitrate', 'Anxiety with digestive issues',
 'Anticipatory anxiety, craving for sweets, flatulence, hurried, claustrophobia',
 'Anxiety, digestive disorders, diarrhea, headaches'),

('Carbo Vegetabilis', 'Vegetable Charcoal', 'plant', 'Charcoal from Beechwood', 'Weakness with flatulence',
 'Weakness, coldness, collapsed state, craves fresh air, flatulence',
 'Indigestion, flatulence, asthma, collapse, varicose veins'),

('Colocynthis', 'Bitter Cucumber', 'plant', 'Bitter Cucumber', 'Cramping pains relieved by pressure',
 'Severe cramping pains, better from hard pressure, worse from anger, doubles up',
 'Colic, sciatica, abdominal pain, neuralgia'),

('Ipecacuanha', 'Ipecac Root', 'plant', 'Ipecac Root', 'Persistent nausea with clean tongue',
 'Persistent nausea not relieved by vomiting, clean tongue, hemorrhages',
 'Nausea, vomiting, asthma, hemorrhage'),

-- Injury & First Aid
('Arnica Montana', 'Leopard\'s Bane', 'plant', 'Arnica Flowers', 'Trauma and bruising',
 'Soreness, bruised feeling, says he is well when ill, fear of being touched, trauma',
 'Trauma, bruises, post-surgery, shock, muscle soreness'),

('Calendula Officinalis', 'Marigold', 'plant', 'Marigold Flowers', 'Wound healing',
 'Promotes healing of wounds, prevents suppuration, lacerated wounds',
 'Wounds, cuts, burns, ulcers, post-surgical healing'),

('Hypericum Perforatum', 'St. John\'s Wort', 'plant', 'St. John\'s Wort', 'Nerve injuries',
 'Nerve injuries, shooting pains along nerves, injuries to fingers, toes, spine',
 'Nerve injuries, dental pain, spinal injuries, puncture wounds'),

('Ledum Palustre', 'Marsh Tea', 'plant', 'Wild Rosemary', 'Puncture wounds',
 'Puncture wounds, insect bites, black eye, cold to touch, better from cold',
 'Puncture wounds, insect bites, gout, black eye'),

('Ruta Graveolens', 'Rue', 'plant', 'Rue Plant', 'Tendon and ligament injuries',
 'Bruised feeling in tendons and periosteum, eyestrain, sprains',
 'Sprains, tendon injuries, eyestrain, carpal tunnel'),

('Symphytum Officinale', 'Comfrey', 'plant', 'Comfrey', 'Bone injuries',
 'Promotes healing of fractured bones, blunt trauma to eye',
 'Fractures, bone injuries, eye injuries'),

-- Women's Health
('Caulophyllum Thalictroides', 'Blue Cohosh', 'plant', 'Blue Cohosh', 'Labor and uterine issues',
 'Weak uterine contractions, false labor, joint pains in small joints',
 'Labor difficulties, irregular menses, joint pains'),

('Cimicifuga Racemosa', 'Black Cohosh', 'plant', 'Black Cohosh', 'Hormonal and muscular issues',
 'Muscular soreness, hormonal issues, talks incessantly, fear of insanity',
 'Menstrual disorders, labor pains, depression, muscle pains'),

('Sepia Officinalis', 'Cuttlefish Ink', 'animal', 'Cuttlefish Ink', 'Hormonal and bearing-down sensations',
 'Bearing-down sensation, indifferent to loved ones, dragged feeling, worse before menses',
 'Menstrual disorders, menopause, prolapse, depression'),

-- Mental/Emotional
('Gelsemium Sempervirens', 'Yellow Jasmine', 'plant', 'Yellow Jasmine', 'Anticipatory anxiety and weakness',
 'Anticipatory anxiety, trembling, drowsiness, drooping eyelids, dullness',
 'Anxiety, flu, stage fright, headaches, vertigo'),

('Ignatia Amara', 'St. Ignatius Bean', 'plant', 'Strychnos Ignatii Seeds', 'Acute grief and contradictory symptoms',
 'Acute grief, sighing, lump in throat, contradictory symptoms, mood swings',
 'Grief, depression, headaches, emotional trauma')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- Add is_popular flag to commonly used remedies
UPDATE remedies SET is_popular = 1 WHERE name IN (
    'Arnica Montana', 'Belladonna', 'Chamomilla', 'Nux Vomica', 
    'Arsenicum Album', 'Pulsatilla Nigricans', 'Sulphur', 'Lycopodium Clavatum',
    'Calcarea Carbonica', 'Natrum Muriaticum'
);
