#!/usr/bin/env python3
"""데이터베이스에서 실제 ingredient_code 확인"""

import sqlite3

conn = sqlite3.connect('database/kdrug.db')
cur = conn.cursor()

# herb_items와 herb_products를 조인하여 ingredient_code 확인
cur.execute("""
    SELECT DISTINCT
        hi.herb_name,
        COALESCE(hi.ingredient_code, hp.ingredient_code) as ingredient_code,
        hi.insurance_code
    FROM herb_items hi
    LEFT JOIN herb_products hp ON hi.insurance_code = hp.product_code
    WHERE COALESCE(hi.ingredient_code, hp.ingredient_code) IS NOT NULL
    ORDER BY hi.herb_name
""")

print("=== 실제 약재 ingredient_code 목록 ===")
herbs = cur.fetchall()
for herb in herbs:
    print(f"{herb[0]:10s} -> {herb[1]} (보험코드: {herb[2]})")

# 십전대보탕 구성 약재들 확인
target_herbs = ['인삼', '백출', '복령', '감초', '숙지황', '작약', '천궁', '당귀', '황기', '육계']
print(f"\n=== 십전대보탕 구성 약재 ({len(target_herbs)}개) ===")
for target in target_herbs:
    cur.execute("""
        SELECT hi.herb_name,
               COALESCE(hi.ingredient_code, hp.ingredient_code) as code
        FROM herb_items hi
        LEFT JOIN herb_products hp ON hi.insurance_code = hp.product_code
        WHERE hi.herb_name = ?
    """, (target,))
    result = cur.fetchone()
    if result and result[1]:
        print(f"✓ {result[0]:6s} -> {result[1]}")
    else:
        print(f"✗ {target:6s} -> ingredient_code 없음")

conn.close()