pharmacy-pos-qr-system/backend/db/schema_food_biomarker.sql

-- ============================================================
-- PostgreSQL + Apache AGE 스키마 확장
-- Food (식품) + Biomarker (바이오마커) 노드 추가
-- ============================================================

-- 1. 식품 테이블
CREATE TABLE IF NOT EXISTS foods (
    food_id SERIAL PRIMARY KEY,
    food_name TEXT NOT NULL,
    food_name_en TEXT,
    category TEXT NOT NULL,  -- 'pro_inflammatory', 'anti_inflammatory', 'neutral'
    subcategory TEXT,        -- 'high_fat', 'processed_meat', 'sugar', 'alcohol', 'omega3', 'antioxidant'
    description TEXT,
    serving_size TEXT,       -- '100g', '1컵' 등
    kcal_per_serving REAL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 인덱스
CREATE INDEX idx_foods_category ON foods(category);
CREATE INDEX idx_foods_subcategory ON foods(subcategory);

-- 샘플 데이터
INSERT INTO foods (food_name, food_name_en, category, subcategory, description) VALUES
('고지방 식품', 'High-fat foods', 'pro_inflammatory', 'high_fat', '튀김, 패스트푸드 등'),
('포화지방', 'Saturated fat', 'pro_inflammatory', 'high_fat', '동물성 지방, 버터 등'),
('가공육', 'Processed meat', 'pro_inflammatory', 'processed_meat', '베이컨, 소시지, 햄'),
('적색육', 'Red meat', 'pro_inflammatory', 'red_meat', '소고기, 돼지고기'),
('알코올', 'Alcohol', 'pro_inflammatory', 'alcohol', '소주, 맥주, 와인'),
('설탕', 'Sugar', 'pro_inflammatory', 'sugar', '단 음료, 과자, 케이크'),
('트랜스지방', 'Trans fat', 'pro_inflammatory', 'trans_fat', '마가린, 쇼트닝'),
('오메가-3', 'Omega-3', 'anti_inflammatory', 'omega3', '등푸른 생선, 들기름'),
('커큐민', 'Curcumin', 'anti_inflammatory', 'antioxidant', '강황 추출물'),
('블루베리', 'Blueberry', 'anti_inflammatory', 'antioxidant', '항산화 과일')
ON CONFLICT DO NOTHING;


-- 2. 바이오마커 테이블
CREATE TABLE IF NOT EXISTS biomarkers (
    biomarker_id SERIAL PRIMARY KEY,
    biomarker_name TEXT UNIQUE NOT NULL,
    biomarker_type TEXT NOT NULL,  -- 'inflammatory_cytokine', 'lipid', 'glucose', 'hormone'
    normal_range_min REAL,
    normal_range_max REAL,
    unit TEXT,                     -- 'pg/mL', 'mg/dL' 등
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 인덱스
CREATE INDEX idx_biomarkers_type ON biomarkers(biomarker_type);

-- 샘플 데이터
INSERT INTO biomarkers (biomarker_name, biomarker_type, normal_range_min, normal_range_max, unit, description) VALUES
('IL-1β', 'inflammatory_cytokine', 0, 5, 'pg/mL', 'Interleukin-1 beta, 염증성 사이토카인'),
('IL-6', 'inflammatory_cytokine', 0, 7, 'pg/mL', 'Interleukin-6, 염증성 사이토카인'),
('TNF-α', 'inflammatory_cytokine', 0, 8.1, 'pg/mL', 'Tumor Necrosis Factor alpha'),
('CRP', 'inflammatory_marker', 0, 3, 'mg/L', 'C-Reactive Protein, 염증 지표'),
('LDL', 'lipid', 0, 130, 'mg/dL', 'Low-Density Lipoprotein, 나쁜 콜레스테롤'),
('HDL', 'lipid', 40, 200, 'mg/dL', 'High-Density Lipoprotein, 좋은 콜레스테롤')
ON CONFLICT DO NOTHING;


-- 3. 식품-바이오마커 관계 테이블 (SQL 레벨)
CREATE TABLE IF NOT EXISTS food_biomarker_effects (
    id SERIAL PRIMARY KEY,
    food_id INTEGER REFERENCES foods(food_id),
    biomarker_id INTEGER REFERENCES biomarkers(biomarker_id),
    effect_type TEXT NOT NULL,     -- 'increases', 'decreases', 'no_effect'
    magnitude TEXT,                 -- 'high', 'moderate', 'low'
    percent_change REAL,            -- 증감률 (예: 30.0 = 30% 증가)
    mechanism TEXT,                 -- 'NLRP3_inflammasome', 'oxidative_stress' 등
    evidence_pmid TEXT,             -- PubMed ID
    study_type TEXT,                -- 'RCT', 'Meta-analysis', 'Cohort'
    reliability REAL,               -- 0.0 ~ 1.0
    created_at TIMESTAMP DEFAULT NOW()
);

-- 인덱스
CREATE INDEX idx_food_biomarker_effect ON food_biomarker_effects(effect_type);
CREATE INDEX idx_food_biomarker_pmid ON food_biomarker_effects(evidence_pmid);

-- 샘플 데이터 (IL-1β 증가시키는 식품)
INSERT INTO food_biomarker_effects (food_id, biomarker_id, effect_type, magnitude, percent_change, mechanism, evidence_pmid, study_type, reliability) VALUES
-- 고지방 식품 → IL-1β 증가
((SELECT food_id FROM foods WHERE food_name = '고지방 식품'),
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 'increases', 'high', 50.0, 'NLRP3_inflammasome_activation', '36776889', 'RCT', 0.95),

-- 포화지방 → IL-1β 증가
((SELECT food_id FROM foods WHERE food_name = '포화지방'),
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 'increases', 'moderate', 35.0, 'myeloid_inflammasome', '40864681', 'RCT', 0.90),

-- 가공육 → IL-1β 증가
((SELECT food_id FROM foods WHERE food_name = '가공육'),
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 'increases', 'moderate', 30.0, 'AGE_formation', '40952033', 'Cohort', 0.85),

-- 알코올 → IL-1β 증가
((SELECT food_id FROM foods WHERE food_name = '알코올'),
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 'increases', 'high', 45.0, 'autophagy_inhibition', '30964198', 'RCT', 0.92),

-- 오메가-3 → IL-1β 감소
((SELECT food_id FROM foods WHERE food_name = '오메가-3'),
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 'decreases', 'moderate', -30.0, 'anti_inflammatory', '12345678', 'Meta-analysis', 0.95)
ON CONFLICT DO NOTHING;


-- 4. 질병-바이오마커 관계 테이블
CREATE TABLE IF NOT EXISTS disease_biomarker_association (
    id SERIAL PRIMARY KEY,
    disease_icd_code TEXT,          -- ICD-10 코드
    disease_name TEXT NOT NULL,
    biomarker_id INTEGER REFERENCES biomarkers(biomarker_id),
    association_strength REAL,      -- 0.0 ~ 1.0
    threshold_value REAL,           -- 위험 기준값
    description TEXT,
    evidence_pmid TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 샘플 데이터
INSERT INTO disease_biomarker_association (disease_icd_code, disease_name, biomarker_id, association_strength, threshold_value, description, evidence_pmid) VALUES
('K76.0', 'NAFLD (비알코올성 지방간)',
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 0.85, 10.0, 'IL-1β 10 pg/mL 이상 시 NAFLD 위험 증가', '36776889'),

('I25', '죽상동맥경화증',
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 0.90, 8.0, 'IL-1β 상승 시 심혈관 질환 위험', '39232165'),

('M06', '류마티스 관절염',
 (SELECT biomarker_id FROM biomarkers WHERE biomarker_name = 'IL-1β'),
 0.92, 7.0, 'IL-1β가 관절 염증 악화 인자', '12345678')
ON CONFLICT DO NOTHING;


-- 5. 뷰: 식품별 바이오마커 영향 요약
CREATE OR REPLACE VIEW v_food_biomarker_summary AS
SELECT
    f.food_name,
    f.category,
    b.biomarker_name,
    fbe.effect_type,
    fbe.magnitude,
    fbe.percent_change,
    fbe.mechanism,
    fbe.evidence_pmid,
    fbe.reliability
FROM foods f
JOIN food_biomarker_effects fbe ON f.food_id = fbe.food_id
JOIN biomarkers b ON fbe.biomarker_id = b.biomarker_id
ORDER BY f.category, fbe.effect_type, fbe.magnitude DESC;


-- 6. 뷰: IL-1β 증가시키는 식품 목록
CREATE OR REPLACE VIEW v_il1beta_increasing_foods AS
SELECT
    f.food_name,
    f.subcategory,
    fbe.magnitude AS 위험도,
    fbe.percent_change AS 증가율,
    fbe.mechanism AS 메커니즘,
    fbe.evidence_pmid AS 근거논문,
    fbe.reliability AS 신뢰도
FROM foods f
JOIN food_biomarker_effects fbe ON f.food_id = fbe.food_id
JOIN biomarkers b ON fbe.biomarker_id = b.biomarker_id
WHERE b.biomarker_name = 'IL-1β'
  AND fbe.effect_type = 'increases'
ORDER BY
    CASE fbe.magnitude
        WHEN 'high' THEN 1
        WHEN 'moderate' THEN 2
        WHEN 'low' THEN 3
    END,
    fbe.percent_change DESC;


-- 7. 함수: 특정 질병 환자가 피해야 할 식품 목록
CREATE OR REPLACE FUNCTION get_foods_to_avoid(disease_icd TEXT)
RETURNS TABLE (
    food_name TEXT,
    reason TEXT,
    biomarker TEXT,
    evidence_pmid TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT DISTINCT
        f.food_name,
        '바이오마커 ' || b.biomarker_name || ' 증가로 ' || dba.disease_name || ' 위험' AS reason,
        b.biomarker_name AS biomarker,
        fbe.evidence_pmid
    FROM foods f
    JOIN food_biomarker_effects fbe ON f.food_id = fbe.food_id
    JOIN biomarkers b ON fbe.biomarker_id = b.biomarker_id
    JOIN disease_biomarker_association dba ON b.biomarker_id = dba.biomarker_id
    WHERE dba.disease_icd_code = disease_icd
      AND fbe.effect_type = 'increases'
    ORDER BY f.food_name;
END;
$$ LANGUAGE plpgsql;


-- 8. 검색 최적화를 위한 전문 검색 인덱스
ALTER TABLE foods ADD COLUMN IF NOT EXISTS search_vector tsvector;
UPDATE foods SET search_vector = to_tsvector('korean', coalesce(food_name, '') || ' ' || coalesce(description, ''));
CREATE INDEX IF NOT EXISTS idx_foods_search ON foods USING GIN(search_vector);


-- 완료 메시지
DO $$
BEGIN
    RAISE NOTICE '✅ 식품-바이오마커 스키마 확장 완료';
    RAISE NOTICE '   - foods 테이블: 식품 마스터';
    RAISE NOTICE '   - biomarkers 테이블: 바이오마커';
    RAISE NOTICE '   - food_biomarker_effects 테이블: 식품-바이오마커 관계';
    RAISE NOTICE '   - disease_biomarker_association 테이블: 질병-바이오마커 관계';
    RAISE NOTICE '   - v_il1beta_increasing_foods 뷰: IL-1β 증가 식품';
    RAISE NOTICE '   - get_foods_to_avoid(disease_icd) 함수: 질병별 피해야 할 식품';
END $$;