-- Homeopathic Doctor's Assistant Database Schema
-- Author: AI Assistant
-- Date: November 24, 2025

CREATE DATABASE IF NOT EXISTS homeopathic_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE homeopathic_system;

-- ============================================
-- 2. PATIENTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS patients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT NOT NULL,
    patient_name VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('male', 'female', 'other'),
    phone VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    blood_group VARCHAR(10),
    occupation VARCHAR(100),
    marital_status ENUM('single', 'married', 'divorced', 'widowed'),
    emergency_contact VARCHAR(255),
    emergency_phone VARCHAR(20),
    medical_history TEXT,
    surgical_history TEXT,
    family_history TEXT,
    allergies TEXT,
    current_medications TEXT,
    diet ENUM('vegetarian', 'non-vegetarian', 'vegan', 'mixed'),
    exercise ENUM('none', 'light', 'moderate', 'active'),
    sleep_pattern ENUM('poor', 'average', 'good', 'excellent'),
    addictions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
    INDEX idx_doctor_patient (doctor_id, patient_name),
    INDEX idx_phone (phone),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. CONSULTATIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS consultations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    consultation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    chief_complaint TEXT,
    present_illness TEXT,
    past_history TEXT,
    physical_examination TEXT,
    mental_state TEXT,
    general_symptoms TEXT,
    particular_symptoms TEXT,
    modalities TEXT,
    causation TEXT,
    thermal_state ENUM('chilly', 'hot', 'ambithermal'),
    thirst ENUM('thirstless', 'moderate', 'excessive'),
    appetite ENUM('poor', 'normal', 'increased', 'decreased'),
    sleep_pattern TEXT,
    dreams TEXT,
    diagnosis TEXT,
    notes TEXT,
    status ENUM('active', 'completed', 'follow_up') DEFAULT 'active',
    follow_up_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
    INDEX idx_patient_consultation (patient_id, consultation_date),
    INDEX idx_doctor_date (doctor_id, consultation_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. SYMPTOMS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS symptoms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consultation_id INT NOT NULL,
    symptom_text TEXT NOT NULL,
    location VARCHAR(255),
    sensation VARCHAR(255),
    modality VARCHAR(255),
    intensity ENUM('mild', 'moderate', 'severe'),
    duration VARCHAR(100),
    category ENUM('mind', 'head', 'eye', 'ear', 'nose', 'face', 'mouth', 'throat', 'stomach', 'abdomen', 'rectum', 'urinary', 'male', 'female', 'respiratory', 'heart', 'back', 'extremities', 'skin', 'sleep', 'fever', 'general') DEFAULT 'general',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
    INDEX idx_consultation (consultation_id),
    INDEX idx_category (category),
    FULLTEXT idx_symptom_search (symptom_text, location, sensation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. LAB REPORTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS lab_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consultation_id INT NOT NULL,
    report_name VARCHAR(255) NOT NULL,
    report_type VARCHAR(100),
    file_path VARCHAR(255),
    file_type ENUM('pdf', 'image', 'document'),
    report_date DATE,
    notes TEXT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
    INDEX idx_consultation (consultation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. REMEDIES TABLE (Materia Medica)
-- ============================================
CREATE TABLE IF NOT EXISTS remedies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    remedy_name VARCHAR(255) NOT NULL UNIQUE,
    remedy_short_name VARCHAR(100),
    common_name VARCHAR(255),
    family VARCHAR(255),
    source TEXT,
    preparation TEXT,
    keynote_symptoms TEXT,
    clinical_indications TEXT,
    characteristic_symptoms TEXT,
    mind_symptoms TEXT,
    head_symptoms TEXT,
    eye_symptoms TEXT,
    ear_symptoms TEXT,
    nose_symptoms TEXT,
    face_symptoms TEXT,
    mouth_symptoms TEXT,
    throat_symptoms TEXT,
    stomach_symptoms TEXT,
    abdomen_symptoms TEXT,
    rectum_symptoms TEXT,
    urinary_symptoms TEXT,
    male_symptoms TEXT,
    female_symptoms TEXT,
    respiratory_symptoms TEXT,
    heart_symptoms TEXT,
    back_symptoms TEXT,
    extremities_symptoms TEXT,
    skin_symptoms TEXT,
    sleep_symptoms TEXT,
    fever_symptoms TEXT,
    modalities TEXT,
    aggravation TEXT,
    amelioration TEXT,
    relationship TEXT,
    dosage_notes TEXT,
    potency_range VARCHAR(100),
    is_popular BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_remedy_name (remedy_name),
    INDEX idx_popular (is_popular),
    FULLTEXT idx_remedy_search (remedy_name, common_name, keynote_symptoms, clinical_indications)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. REPERTORY TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS repertory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rubric TEXT NOT NULL,
    category ENUM('mind', 'head', 'eye', 'ear', 'nose', 'face', 'mouth', 'throat', 'stomach', 'abdomen', 'rectum', 'urinary', 'male', 'female', 'respiratory', 'heart', 'back', 'extremities', 'skin', 'sleep', 'fever', 'general') NOT NULL,
    sub_category VARCHAR(255),
    complete_rubric TEXT,
    repertory_source VARCHAR(100) DEFAULT 'Kent',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_source (repertory_source),
    FULLTEXT idx_rubric_search (rubric, complete_rubric)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8. REPERTORY REMEDIES (Junction Table)
-- ============================================
CREATE TABLE IF NOT EXISTS repertory_remedies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    repertory_id INT NOT NULL,
    remedy_id INT NOT NULL,
    grade ENUM('1', '2', '3') DEFAULT '1',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (repertory_id) REFERENCES repertory(id) ON DELETE CASCADE,
    FOREIGN KEY (remedy_id) REFERENCES remedies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_repertory_remedy (repertory_id, remedy_id),
    INDEX idx_grade (grade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9. PRESCRIPTIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS prescriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consultation_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    prescription_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    diagnosis TEXT,
    prescription_notes TEXT,
    advice TEXT,
    diet_restrictions TEXT,
    lifestyle_changes TEXT,
    follow_up_date DATE,
    follow_up_notes TEXT,
    ai_suggestions TEXT,
    ai_analysis TEXT,
    status ENUM('draft', 'finalized', 'dispensed') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    INDEX idx_consultation (consultation_id),
    INDEX idx_patient_date (patient_id, prescription_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 10. PRESCRIPTION ITEMS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS prescription_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prescription_id INT NOT NULL,
    remedy_id INT NOT NULL,
    potency VARCHAR(50),
    dosage VARCHAR(255),
    frequency VARCHAR(255),
    duration VARCHAR(100),
    timing VARCHAR(255),
    special_instructions TEXT,
    display_order INT DEFAULT 0,
    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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 11. AI SUGGESTIONS LOG TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS ai_suggestions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consultation_id INT NOT NULL,
    doctor_id INT NOT NULL,
    symptoms_sent TEXT,
    ai_response TEXT,
    suggested_remedies JSON,
    differential_diagnosis TEXT,
    confidence_score DECIMAL(5,2),
    api_used VARCHAR(50) DEFAULT 'Gemini',
    processing_time DECIMAL(10,3),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
    INDEX idx_consultation (consultation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 12. SYSTEM SETTINGS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(50),
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INSERT DEFAULT SYSTEM SETTINGS
-- ============================================
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('gemini_api_key', '', 'text', 'Google Gemini API Key for AI suggestions'),
('app_name', 'Homeopathic Assistant', 'text', 'Application name'),
('app_version', '1.0.0', 'text', 'Current application version'),
('ai_enabled', '1', 'boolean', 'Enable/Disable AI suggestions'),
('max_upload_size', '5', 'number', 'Maximum file upload size in MB'),
('session_timeout', '3600', 'number', 'Session timeout in seconds'),
('default_potency', '30C', 'text', 'Default potency for prescriptions');

-- View for popular remedies
CREATE OR REPLACE VIEW v_popular_remedies AS
SELECT 
    r.id,
    r.remedy_name,
    r.remedy_short_name,
    r.common_name,
    r.keynote_symptoms,
    r.clinical_indications,
    COUNT(DISTINCT pi.prescription_id) AS usage_count
FROM remedies r
LEFT JOIN prescription_items pi ON r.id = pi.remedy_id
GROUP BY r.id
ORDER BY usage_count DESC, r.remedy_name ASC;

-- View for repertory with remedy counts
CREATE OR REPLACE VIEW v_repertory_with_remedies AS
SELECT 
    rp.id,
    rp.rubric,
    rp.category,
    rp.complete_rubric,
    rp.repertory_source,
    COUNT(rr.remedy_id) AS remedy_count,
    GROUP_CONCAT(CONCAT(rm.remedy_short_name, ' (', rr.grade, ')') ORDER BY rr.grade DESC, rm.remedy_name SEPARATOR ', ') AS remedies_list
FROM repertory rp
LEFT JOIN repertory_remedies rr ON rp.id = rr.repertory_id
LEFT JOIN remedies rm ON rr.remedy_id = rm.id
GROUP BY rp.id;

-- ============================================
-- TRIGGERS
-- ============================================

-- Trigger to update patient's updated_at when consultation is added
DELIMITER //
CREATE TRIGGER after_consultation_insert 
AFTER INSERT ON consultations
FOR EACH ROW
BEGIN
    UPDATE patients SET updated_at = NOW() WHERE id = NEW.patient_id;
END//
DELIMITER ;

-- ============================================
-- END OF SCHEMA
-- ============================================
