-- Herbalytics Platform Database Schema
-- MariaDB

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

-- =============================================
-- USERS & AUTHENTICATION
-- =============================================
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','researcher','practitioner','patient','collaborator') NOT NULL DEFAULT 'researcher',
    credential_status ENUM('not_required','pending','verified','rejected') NOT NULL DEFAULT 'not_required',
    credential_license_type VARCHAR(120),
    credential_license_number VARCHAR(120),
    credential_license_region VARCHAR(120),
    credential_expires_at DATE,
    credential_submitted_at TIMESTAMP NULL,
    credential_verified_at TIMESTAMP NULL,
    credential_verified_by INT NULL,
    credential_notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,
    full_name VARCHAR(100),
    title VARCHAR(50),
    specialization VARCHAR(100),
    institution VARCHAR(150),
    bio TEXT,
    avatar_url VARCHAR(255),
    orcid_id VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =============================================
-- PLANT & COMPOUND LIBRARY
-- =============================================
CREATE TABLE IF NOT EXISTS plant_families (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS plants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    common_name VARCHAR(150) NOT NULL,
    scientific_name VARCHAR(200) UNIQUE NOT NULL,
    family_id INT,
    genus VARCHAR(100),
    species VARCHAR(100),
    origin_regions TEXT,
    description TEXT,
    traditional_uses TEXT,
    preparation_methods TEXT,
    safety_notes TEXT,
    image_url VARCHAR(255),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (family_id) REFERENCES plant_families(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS compounds (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plant_id INT,
    name TEXT NOT NULL,
    cas_number VARCHAR(20),
    molecular_formula VARCHAR(100),
    molecular_weight DECIMAL(10,4),
    smiles_notation TEXT,
    iupac_name TEXT,
    compound_class VARCHAR(100),
    bioactivity TEXT,
    mechanism_of_action TEXT,
    known_targets TEXT,
    toxicity_notes TEXT,
    pubchem_cid VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS plant_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    color VARCHAR(7) DEFAULT '#2d9596'
);

CREATE TABLE IF NOT EXISTS plant_tag_map (
    plant_id INT,
    tag_id INT,
    PRIMARY KEY (plant_id, tag_id),
    FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES plant_tags(id) ON DELETE CASCADE
);

-- =============================================
-- RESEARCH STUDIES & PIPELINES
-- =============================================
CREATE TABLE IF NOT EXISTS research_studies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(300) NOT NULL,
    lead_researcher_id INT,
    hypothesis TEXT,
    methodology TEXT,
    study_type ENUM('in_vitro','in_vivo','clinical_trial','computational','review','meta_analysis') DEFAULT 'in_vitro',
    status ENUM('planning','active','on_hold','completed','published','archived') DEFAULT 'planning',
    start_date DATE,
    end_date DATE,
    abstract TEXT,
    keywords TEXT,
    ethics_approval VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_researcher_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS study_compounds (
    study_id INT,
    compound_id INT,
    role VARCHAR(100),
    PRIMARY KEY (study_id, compound_id),
    FOREIGN KEY (study_id) REFERENCES research_studies(id) ON DELETE CASCADE,
    FOREIGN KEY (compound_id) REFERENCES compounds(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS study_members (
    study_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('lead','co_investigator','researcher','analyst','observer') DEFAULT 'researcher',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (study_id, user_id),
    FOREIGN KEY (study_id) REFERENCES research_studies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS study_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    study_id INT NOT NULL,
    finding TEXT NOT NULL,
    evidence_level ENUM('A','B','C','D','expert_opinion') DEFAULT 'C',
    statistical_significance VARCHAR(50),
    publication_url VARCHAR(500),
    publication_doi VARCHAR(100),
    recorded_by INT,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES research_studies(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS research_pipelines (
    id INT PRIMARY KEY AUTO_INCREMENT,
    study_id INT NOT NULL,
    step_name VARCHAR(200) NOT NULL,
    step_order INT NOT NULL,
    description TEXT,
    status ENUM('pending','running','completed','failed','skipped') DEFAULT 'pending',
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    output_summary TEXT,
    FOREIGN KEY (study_id) REFERENCES research_studies(id) ON DELETE CASCADE
);

-- =============================================
-- CLINICAL EMR
-- =============================================
CREATE TABLE IF NOT EXISTS patients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    practitioner_id INT NOT NULL,
    mrn VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    gender ENUM('male','female','non_binary','prefer_not_to_say'),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    emergency_contact TEXT,
    blood_type VARCHAR(5),
    allergies TEXT,
    current_medications TEXT,
    medical_history TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (practitioner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS consultations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    practitioner_id INT NOT NULL,
    consultation_date DATETIME NOT NULL,
    chief_complaint TEXT,
    present_illness TEXT,
    physical_examination TEXT,
    tongue_observation TEXT,
    pulse_diagnosis TEXT,
    tcm_pattern_diagnosis TEXT,
    assessment TEXT,
    plan TEXT,
    follow_up_date DATE,
    notes TEXT,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (practitioner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS prescriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    consultation_id INT NOT NULL,
    plant_id INT,
    herb_name VARCHAR(200) NOT NULL,
    preparation ENUM('decoction','tincture','capsule','powder','tea','topical','extract','oil','tablet') DEFAULT 'tea',
    dosage VARCHAR(100) NOT NULL,
    frequency VARCHAR(100) NOT NULL,
    duration VARCHAR(100),
    instructions TEXT,
    contraindications TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
    FOREIGN KEY (plant_id) REFERENCES plants(id)
);

CREATE TABLE IF NOT EXISTS patient_outcomes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    consultation_id INT,
    condition_treated VARCHAR(200) NOT NULL,
    severity_before TINYINT UNSIGNED CHECK (severity_before BETWEEN 0 AND 10),
    severity_after TINYINT UNSIGNED CHECK (severity_after BETWEEN 0 AND 10),
    outcome ENUM('resolved','improved','unchanged','worsened','unknown') DEFAULT 'unknown',
    patient_reported_score TINYINT UNSIGNED,
    side_effects TEXT,
    notes TEXT,
    assessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id)
);

CREATE TABLE IF NOT EXISTS drug_herb_interactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plant_id INT NOT NULL,
    drug_name VARCHAR(200) NOT NULL,
    drug_class VARCHAR(100),
    interaction_type ENUM('potentiates','inhibits','additive','antagonistic','unknown') DEFAULT 'unknown',
    severity ENUM('mild','moderate','severe','contraindicated') DEFAULT 'moderate',
    mechanism TEXT,
    description TEXT NOT NULL,
    evidence_reference TEXT,
    FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
);

-- =============================================
-- FORMULATION LAB
-- =============================================
CREATE TABLE IF NOT EXISTS formulations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    creator_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    formulation_type ENUM('capsule','tablet','tincture','tea_blend','decoction','topical','oil','syrup','powder','extract') NOT NULL,
    target_condition VARCHAR(200),
    description TEXT,
    preparation_instructions TEXT,
    shelf_life VARCHAR(100),
    dosage_recommendation TEXT,
    contraindications TEXT,
    regulatory_status VARCHAR(100),
    version VARCHAR(20) DEFAULT '1.0',
    is_published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS formulation_ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    formulation_id INT NOT NULL,
    plant_id INT,
    compound_id INT,
    ingredient_name VARCHAR(200) NOT NULL,
    quantity DECIMAL(10,3),
    unit VARCHAR(50),
    percentage DECIMAL(5,2),
    role ENUM('primary','secondary','adjuvant','preservative','carrier','flavoring','excipient') DEFAULT 'primary',
    notes TEXT,
    FOREIGN KEY (formulation_id) REFERENCES formulations(id) ON DELETE CASCADE,
    FOREIGN KEY (plant_id) REFERENCES plants(id),
    FOREIGN KEY (compound_id) REFERENCES compounds(id)
);

-- =============================================
-- COLLABORATION HUB
-- =============================================
CREATE TABLE IF NOT EXISTS projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    lead_id INT NOT NULL,
    status ENUM('open','active','completed','archived') DEFAULT 'open',
    visibility ENUM('public','private','institution') DEFAULT 'private',
    start_date DATE,
    end_date DATE,
    tags VARCHAR(255),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS project_members (
    project_id INT,
    user_id INT,
    role ENUM('lead','co_investigator','researcher','contributor','observer') DEFAULT 'contributor',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (project_id, user_id),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS datasets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    uploader_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    data_type ENUM('genomics','metabolomics','proteomics','clinical','pharmacological','ethnobotanical','other') DEFAULT 'other',
    file_url VARCHAR(500),
    file_type VARCHAR(50),
    file_size_kb INT,
    record_count INT,
    is_public BOOLEAN DEFAULT FALSE,
    license VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (uploader_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS discussions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    author_id INT NOT NULL,
    title VARCHAR(300) NOT NULL,
    content TEXT NOT NULL,
    is_pinned BOOLEAN DEFAULT FALSE,
    is_closed BOOLEAN DEFAULT FALSE,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS discussion_replies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    discussion_id INT NOT NULL,
    author_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (discussion_id) REFERENCES discussions(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

-- =============================================
-- CHALLENGE BOARD
-- =============================================
CREATE TABLE IF NOT EXISTS challenges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(300) NOT NULL,
    description TEXT,
    condition_target VARCHAR(200),
    creator_id INT NOT NULL,
    status ENUM('open','active','judging','completed','archived') NOT NULL DEFAULT 'open',
    reward_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    reward_currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    reward_status ENUM('unpaid','escrowed','awarded','refunded') NOT NULL DEFAULT 'unpaid',
    winner_entry_id INT NULL,
    success_criteria TEXT,
    deadline DATE,
    tags VARCHAR(255),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS challenge_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    challenge_id INT NOT NULL,
    user_id INT NOT NULL,
    project_id INT NULL,
    title VARCHAR(300),
    summary TEXT,
    status ENUM('active','withdrawn','winner','rejected') NOT NULL DEFAULT 'active',
    submitted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_challenge_user (challenge_id, user_id),
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- =============================================
-- ACTIVITY LOG & NOTIFICATIONS
-- =============================================
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- =============================================
-- SEED DATA
-- =============================================

-- Default admin user (password: Admin@123)
INSERT INTO users (username, email, password_hash, role, credential_status, credential_verified_at) VALUES
('admin', 'admin@herbalytics.com', '$2a$10$rJ8pT3mN2kLqV5wXyZaB8.OqH6dFgIiKlMnPrStUvWxYz1234567', 'admin', 'not_required', NULL),
('dr_nguyen', 'nguyen@herbalytics.com', '$2a$10$rJ8pT3mN2kLqV5wXyZaB8.OqH6dFgIiKlMnPrStUvWxYz1234567', 'practitioner', 'verified', CURRENT_TIMESTAMP),
('researcher1', 'researcher@herbalytics.com', '$2a$10$rJ8pT3mN2kLqV5wXyZaB8.OqH6dFgIiKlMnPrStUvWxYz1234567', 'researcher', 'not_required', NULL);

INSERT INTO user_profiles (user_id, full_name, title, specialization, institution) VALUES
(1, 'System Administrator', 'Admin', 'Platform Administration', 'Herbalytics'),
(2, 'Dr. Pham Nguyen', 'PhD, ND', 'Herbal Medicine & Phytotherapy', 'Pham Bioscience Institute'),
(3, 'Dr. Alex Chen', 'PhD', 'Natural Product Chemistry', 'Pham Bioscience Institute');

-- Plant families
INSERT INTO plant_families (name, description) VALUES
('Asteraceae', 'Daisy family — large and diverse'),
('Lamiaceae', 'Mint family — aromatic herbs'),
('Apiaceae', 'Carrot/Parsley family'),
('Fabaceae', 'Legume family'),
('Ranunculaceae', 'Buttercup family'),
('Zingiberaceae', 'Ginger family'),
('Araliaceae', 'Ginseng family');

-- Sample plants
INSERT INTO plants (common_name, scientific_name, family_id, genus, species, origin_regions, description, traditional_uses, preparation_methods, safety_notes, created_by) VALUES
('Turmeric', 'Curcuma longa', 6, 'Curcuma', 'longa', 'South and Southeast Asia', 'Perennial herbaceous plant with aromatic rhizomes. Yellow-orange color from curcuminoids.', 'Anti-inflammatory, digestive aid, wound healing, liver support. Used in Ayurveda for thousands of years.', 'Decoction, powder in food, tincture, capsules, topical paste', 'Generally safe. High doses may cause GI upset. Caution with blood thinners.', 3),
('Ashwagandha', 'Withania somnifera', 1, 'Withania', 'somnifera', 'India, North Africa, Mediterranean', 'Adaptogenic shrub with small greenish flowers and red berry-like fruits.', 'Stress adaptogen, cognitive enhancement, immune support, sleep aid. Core Ayurvedic herb.', 'Root powder, capsules, tincture, milk decoction (Ashwagandha latte)', 'Avoid in pregnancy. May interact with thyroid medications and immunosuppressants.', 3),
('Echinacea', 'Echinacea purpurea', 1, 'Echinacea', 'purpurea', 'North America', 'Purple coneflower native to eastern North America. Widely used in modern herbal medicine.', 'Immune stimulant, cold and flu treatment, anti-inflammatory, wound healing.', 'Tincture, tea, capsules, fresh juice', 'Avoid in autoimmune conditions. Short-term use recommended.', 3),
('Ginger', 'Zingiber officinale', 6, 'Zingiber', 'officinale', 'Southeast Asia', 'Flowering plant with aromatic rhizomes widely used as spice and medicine.', 'Digestive aid, anti-nausea, anti-inflammatory, circulatory stimulant, pain relief.', 'Fresh root, dried powder, tea, tincture, capsules, essential oil', 'High doses may interact with anticoagulants. Safe in culinary amounts.', 3),
('Ginseng', 'Panax ginseng', 7, 'Panax', 'ginseng', 'East Asia', 'Slow-growing perennial plant. Root used medicinally for millennia in Traditional Chinese Medicine.', 'Adaptogen, cognitive enhancement, energy, immune support, sexual health.', 'Root decoction, standardized extracts, capsules, teas', 'Avoid in hypertension. May interact with anticoagulants, diabetes medications.', 3),
('Milk Thistle', 'Silybum marianum', 1, 'Silybum', 'marianum', 'Mediterranean Europe', 'Thorny plant with distinctive white-veined leaves. Seeds contain silymarin complex.', 'Liver protection and regeneration, antioxidant, gallbladder support.', 'Standardized extract (silymarin), seed powder, tea, tincture', 'Very safe. May lower blood sugar. Mild laxative effect in some.', 3),
('Valerian', 'Valeriana officinalis', 1, 'Valeriana', 'officinalis', 'Europe, Asia', 'Perennial plant with white/pink flowers. Root has strong characteristic odor.', 'Sleep aid, anxiety reduction, mild sedative, muscle relaxant.', 'Root tincture, capsules, tea, extract', 'May cause drowsiness. Avoid with sedatives/alcohol. Not for prolonged use.', 3);

-- Sample compounds
INSERT INTO compounds (plant_id, name, molecular_formula, molecular_weight, compound_class, bioactivity, mechanism_of_action, known_targets) VALUES
(1, 'Curcumin', 'C21H20O6', 368.38, 'Polyphenol/Curcuminoid', 'Anti-inflammatory, antioxidant, anticancer, neuroprotective', 'Inhibits NF-κB pathway, COX-2 inhibition, modulates multiple signaling cascades', 'NF-κB, COX-2, STAT3, Nrf2, AP-1'),
(1, 'Bisdemethoxycurcumin', 'C19H16O4', 308.33, 'Curcuminoid', 'Anti-inflammatory, antioxidant', 'NF-κB inhibition, radical scavenging', 'NF-κB, free radicals'),
(2, 'Withanolide A', 'C28H38O6', 470.60, 'Steroidal lactone', 'Adaptogenic, anti-stress, neuroprotective, anticancer', 'Modulates HPA axis, GABAergic activity, inhibits cancer cell growth', 'HSP90, HPA axis, GABA receptors'),
(3, 'Echinacoside', 'C35H46O20', 786.72, 'Caffeic acid glycoside', 'Immunostimulant, antiviral, antioxidant', 'Enhances phagocytosis, stimulates T-cell proliferation', 'Macrophages, T-lymphocytes, NK cells'),
(4, 'Gingerol (6-Gingerol)', 'C17H26O4', 294.38, 'Phenylpropanoid', 'Anti-nausea, anti-inflammatory, antioxidant, anticancer', 'TRPV1 agonist, 5-HT3 antagonism, inhibits prostaglandin synthesis', 'TRPV1, 5-HT3, COX-2, TNF-α'),
(5, 'Ginsenoside Rb1', 'C54H92O23', 1109.27, 'Triterpenoid saponin', 'Cognitive enhancement, anti-stress, anti-fatigue', 'Modulates neurotransmitter systems, promotes neurogenesis, antioxidant', 'NMDA receptors, HPA axis, BDNF'),
(6, 'Silymarin (Silybin)', 'C25H22O10', 482.44, 'Flavonolignan', 'Hepatoprotective, antioxidant, anti-inflammatory', 'Membrane stabilization, antioxidant, inhibits hepatotoxin uptake', 'Liver cell membranes, CYP3A4, NF-κB'),
(7, 'Valerenic acid', 'C15H22O2', 234.33, 'Sesquiterpene acid', 'Sedative, anxiolytic, GABA modulator', 'Inhibits GABA transaminase, partial agonist at GABA-A receptors', 'GABA-A receptors, GABA-T');

-- Plant tags
INSERT INTO plant_tags (name, color) VALUES
('Anti-inflammatory', '#e74c3c'),
('Adaptogen', '#9b59b6'),
('Antioxidant', '#27ae60'),
('Immunomodulator', '#3498db'),
('Hepatoprotective', '#f39c12'),
('Neuroprotective', '#1abc9c'),
('Sedative', '#2c3e50'),
('Digestive', '#e67e22'),
('Anticancer', '#c0392b'),
('TCM', '#d35400'),
('Ayurvedic', '#8e44ad'),
('Western Herbalism', '#2980b9');

-- Sample drug-herb interactions
INSERT INTO drug_herb_interactions (plant_id, drug_name, drug_class, interaction_type, severity, mechanism, description) VALUES
(1, 'Warfarin', 'Anticoagulant', 'potentiates', 'moderate', 'Curcumin inhibits platelet aggregation and may enhance anticoagulant effects', 'Turmeric at high doses may increase bleeding risk when combined with warfarin'),
(2, 'Levothyroxine', 'Thyroid hormone', 'inhibits', 'moderate', 'May affect thyroid hormone metabolism', 'Ashwagandha may alter thyroid function; monitor TSH levels'),
(4, 'Aspirin', 'NSAID/Antiplatelet', 'additive', 'mild', 'Additive antiplatelet effects via different mechanisms', 'Combined use may increase bleeding risk, especially pre-surgery'),
(5, 'Insulin', 'Antidiabetic', 'potentiates', 'moderate', 'Ginseng may enhance insulin sensitivity and lower blood glucose', 'Risk of hypoglycemia; blood glucose monitoring recommended'),
(6, 'Indinavir', 'HIV Protease Inhibitor', 'inhibits', 'severe', 'Silymarin inhibits CYP3A4 which metabolizes indinavir', 'May significantly reduce indinavir plasma levels; avoid combination'),
(7, 'Alprazolam', 'Benzodiazepine', 'additive', 'moderate', 'Additive CNS depression via GABA potentiation', 'Enhanced sedation; avoid operating machinery');

-- Sample research study
INSERT INTO research_studies (title, lead_researcher_id, hypothesis, methodology, study_type, status, start_date, abstract, keywords) VALUES
('Anti-inflammatory Effects of Curcumin Nanoparticles on LPS-Induced Macrophage Activation', 3, 'Curcumin nanoparticles will demonstrate superior anti-inflammatory activity compared to free curcumin in LPS-activated macrophages due to enhanced bioavailability', 'In vitro study using RAW264.7 macrophage cell line. LPS stimulation (1μg/mL), treatment with curcumin (1-50μM) and curcumin nanoparticles. Cytokine profiling (IL-6, TNF-α, IL-1β) by ELISA. Western blot for NF-κB pathway components.', 'in_vitro', 'active', '2026-01-15', 'This study investigates the comparative anti-inflammatory efficacy of curcumin nanoparticles versus free curcumin in an in vitro macrophage activation model. Enhanced bioavailability through nanoformulation may overcome curcumin\'s poor solubility and rapid metabolism.', 'curcumin, nanoparticles, inflammation, NF-κB, macrophage, bioavailability');

INSERT INTO study_compounds (study_id, compound_id, role) VALUES (1, 1, 'Primary test compound');

INSERT INTO research_pipelines (study_id, step_name, step_order, description, status) VALUES
(1, 'Cell culture preparation', 1, 'Maintain RAW264.7 cells in DMEM + 10% FBS', 'completed'),
(1, 'Nanoparticle synthesis', 2, 'Prepare curcumin PLGA nanoparticles via nanoprecipitation', 'completed'),
(1, 'Characterization (DLS, TEM)', 3, 'Size, PDI, zeta potential, morphology analysis', 'completed'),
(1, 'LPS stimulation & treatment', 4, 'LPS activation with compound treatment at 6h', 'running'),
(1, 'Cytokine ELISA', 5, 'IL-6, TNF-α, IL-1β quantification', 'pending'),
(1, 'Western Blot - NF-κB', 6, 'p65, IκB-α, pIκB-α protein analysis', 'pending'),
(1, 'Statistical analysis', 7, 'ANOVA + Tukey post-hoc test', 'pending');

-- Sample collaboration project
INSERT INTO projects (name, description, lead_id, status, visibility, start_date, tags) VALUES
('Global Adaptogen Research Consortium', 'Multi-institutional study on adaptogenic herbs and HPA axis modulation. Pooling clinical and preclinical data from 5 research centers.', 3, 'active', 'private', '2026-02-01', 'adaptogen,HPA axis,clinical,multi-center');

INSERT INTO project_members (project_id, user_id, role) VALUES
(1, 3, 'lead'),
(1, 2, 'co_investigator');

INSERT INTO discussions (project_id, author_id, title, content, is_pinned) VALUES
(1, 3, 'Welcome & Data Standardization Protocol', 'Welcome to the Global Adaptogen Research Consortium. Before submitting data, please review the standardization protocol document. All clinical data should follow the CONSORT guidelines. Compound data should include CAS numbers and validated HPLC quantification.', TRUE);

-- Sample patient
INSERT INTO patients (practitioner_id, mrn, first_name, last_name, date_of_birth, gender, phone, allergies, medical_history) VALUES
(2, 'MRN-001', 'Sarah', 'Johnson', '1985-03-15', 'female', '555-0101', 'Penicillin', 'Chronic fatigue syndrome, anxiety, IBS');

INSERT INTO consultations (patient_id, practitioner_id, consultation_date, chief_complaint, assessment, plan) VALUES
(1, 2, '2026-03-15 10:00:00', 'Persistent fatigue, poor sleep, high stress levels', 'HPA axis dysregulation, adrenal fatigue pattern. Vata-Pitta imbalance (Ayurvedic).', 'Ashwagandha protocol for 3 months. Lifestyle modifications. Follow-up in 6 weeks.');

INSERT INTO prescriptions (consultation_id, plant_id, herb_name, preparation, dosage, frequency, duration, instructions) VALUES
(1, 2, 'Ashwagandha', 'capsule', '300mg standardized extract (5% withanolides)', 'Twice daily', '3 months', 'Take with warm milk or water. Evening dose before bed for sleep support.');

INSERT INTO formulations (creator_id, name, formulation_type, target_condition, description, preparation_instructions, dosage_recommendation, version, is_published) VALUES
(2, 'Calm & Restore Adaptogen Blend', 'capsule', 'Stress, Fatigue, Sleep Disturbance', 'Synergistic adaptogen formula combining ashwagandha, ginseng, and valerian for comprehensive stress support and restorative sleep.', 'Blend standardized extracts to specified ratios. Encapsulate in size 00 vegetarian capsules. 2 capsules = 1 serving.', '2 capsules twice daily with food. Evening dose may be increased to 3 capsules if needed for sleep.', '2.1', TRUE);

INSERT INTO formulation_ingredients (formulation_id, plant_id, ingredient_name, quantity, unit, percentage, role) VALUES
(1, 2, 'Ashwagandha Root Extract (5% withanolides)', 300, 'mg', 50.0, 'primary'),
(1, 5, 'Panax Ginseng Root Extract (20% ginsenosides)', 150, 'mg', 25.0, 'primary'),
(1, 7, 'Valerian Root Extract (0.8% valerenic acid)', 100, 'mg', 16.7, 'secondary'),
(1, NULL, 'Magnesium Glycinate', 50, 'mg', 8.3, 'adjuvant');
