#!/usr/bin/env python3
"""
월비탕 처방 데이터 검증 스크립트
"""

import sqlite3

def verify_wolbitang():
    """추가된 월비탕 처방 검증"""

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

    print("🔍 월비탕 처방 상세 검증")
    print("="*70)

    # 각 처방별 상세 정보 조회
    cursor.execute("""
        SELECT f.formula_id, f.formula_code, f.formula_name, f.description
        FROM formulas f
        WHERE f.formula_code LIKE 'WBT%'
        ORDER BY f.formula_code
    """)

    formulas = cursor.fetchall()

    for formula_id, formula_code, formula_name, description in formulas:
        print(f"\n📝 {formula_name} ({formula_code})")
        print(f"   설명: {description}")
        print(f"   약재 구성:")

        # 각 처방의 약재 상세 조회
        cursor.execute("""
            SELECT hm.herb_name, fi.grams_per_cheop, fi.notes
            FROM formula_ingredients fi
            JOIN herb_masters hm ON fi.ingredient_code = hm.ingredient_code
            WHERE fi.formula_id = ?
            ORDER BY fi.sort_order
        """, (formula_id,))

        ingredients = cursor.fetchall()
        total_grams = 0

        for herb_name, grams, notes in ingredients:
            print(f"     - {herb_name:8s}: {grams:6.3f}g ({notes})")
            total_grams += grams

        print(f"   총 용량: {total_grams:.3f}g")

    # 단계별 용량 변화 비교
    print(f"\n{'='*70}")
    print("📊 단계별 약재 용량 변화:")
    print("-"*70)

    # 약재별 단계별 용량 조회
    cursor.execute("""
        SELECT DISTINCT hm.herb_name
        FROM formula_ingredients fi
        JOIN herb_masters hm ON fi.ingredient_code = hm.ingredient_code
        JOIN formulas f ON fi.formula_id = f.formula_id
        WHERE f.formula_code LIKE 'WBT%'
        ORDER BY hm.herb_name
    """)

    herbs = [row[0] for row in cursor.fetchall()]

    print(f"{'약재명':10s} | {'1차':>8s} | {'2차':>8s} | {'3차':>8s} | {'4차':>8s}")
    print("-"*50)

    for herb in herbs:
        amounts = []
        for stage in range(1, 5):
            cursor.execute("""
                SELECT fi.grams_per_cheop
                FROM formula_ingredients fi
                JOIN herb_masters hm ON fi.ingredient_code = hm.ingredient_code
                JOIN formulas f ON fi.formula_id = f.formula_id
                WHERE f.formula_code = ? AND hm.herb_name = ?
            """, (f'WBT001-{stage}', herb))

            result = cursor.fetchone()
            amounts.append(f"{result[0]:.3f}g" if result else "-")

        print(f"{herb:10s} | {amounts[0]:>8s} | {amounts[1]:>8s} | {amounts[2]:>8s} | {amounts[3]:>8s}")

    conn.close()

if __name__ == "__main__":
    verify_wolbitang()