#!/usr/bin/env python3
"""
compound_ingredients 테이블 확인 - 직접조제 데이터 분석
"""

import sqlite3

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

    print("=" * 80)
    print("Compound_ingredients 테이블 분석")
    print("=" * 80)

    # 테이블 구조
    print("\n1. 테이블 구조:")
    print("-" * 80)
    cursor.execute("PRAGMA table_info(compound_ingredients)")
    for col in cursor.fetchall():
        print(f"  {col[1]:25s} {col[2]:15s} {'NOT NULL' if col[3] else 'NULL':10s}")

    # formula_id가 NULL인 compound의 약재 구성
    print("\n2. Formula_id가 NULL인 조제건의 약재 구성:")
    print("-" * 80)

    cursor.execute("""
        SELECT
            c.compound_id,
            c.formula_id,
            c.is_custom,
            c.custom_type,
            COUNT(ci.compound_ingredient_id) as ingredient_count,
            GROUP_CONCAT(h.herb_name || ':' || ci.grams_per_cheop || 'g', ', ') as ingredients
        FROM compounds c
        LEFT JOIN compound_ingredients ci ON c.compound_id = ci.compound_id
        LEFT JOIN herb_items h ON ci.herb_item_id = h.herb_item_id
        WHERE c.formula_id IS NULL
        GROUP BY c.compound_id
        ORDER BY c.compound_id DESC
    """)

    results = cursor.fetchall()
    for row in results:
        print(f"\n  Compound ID: {row[0]}")
        print(f"    Formula ID: {row[1]}, is_custom: {row[2]}, custom_type: {row[3]}")
        print(f"    약재 수: {row[4]}개")
        print(f"    구성: {row[5]}")

    # 전체 통계
    print("\n3. 전체 조제 통계:")
    print("-" * 80)

    cursor.execute("""
        SELECT
            c.formula_id IS NOT NULL as has_formula,
            c.is_custom,
            COUNT(DISTINCT c.compound_id) as compound_count,
            COUNT(ci.compound_ingredient_id) as total_ingredients
        FROM compounds c
        LEFT JOIN compound_ingredients ci ON c.compound_id = ci.compound_id
        GROUP BY has_formula, is_custom
    """)

    print(f"  {'처방있음':10s} {'커스텀':8s} {'조제수':8s} {'총약재수':10s}")
    print("  " + "-" * 40)
    for row in cursor.fetchall():
        has_formula = "예" if row[0] else "아니오"
        is_custom = "예" if row[1] else "아니오"
        print(f"  {has_formula:10s} {is_custom:8s} {row[2]:8d} {row[3]:10d}")

    # 특정 조제건 상세
    print("\n4. Compound ID 10번 상세 (formula_id=NULL):")
    print("-" * 80)

    cursor.execute("""
        SELECT
            ci.herb_item_id,
            h.herb_name,
            ci.grams_per_cheop,
            c.cheop_total,
            ci.total_grams,
            ci.notes
        FROM compound_ingredients ci
        LEFT JOIN herb_items h ON ci.herb_item_id = h.herb_item_id
        LEFT JOIN compounds c ON ci.compound_id = c.compound_id
        WHERE ci.compound_id = 10
        ORDER BY ci.compound_ingredient_id
    """)

    ingredients = cursor.fetchall()
    if ingredients:
        print(f"  {'약재코드':15s} {'약재명':15s} {'1첩용량':10s} {'첩수':8s} {'총용량':10s}")
        print("  " + "-" * 60)
        for ing in ingredients:
            print(f"  {ing[0]:15s} {ing[1] or 'Unknown':15s} {ing[2]:10.1f}g {ing[3]:8.0f} {ing[4]:10.1f}g")
    else:
        print("  약재 데이터 없음")

    conn.close()

if __name__ == "__main__":
    analyze_compound_ingredients()