#!/usr/bin/env python3
"""
기존 조제 데이터의 커스텀 처방 여부를 재검사하여 업데이트
"""

import sqlite3
from datetime import datetime

def get_connection():
    return sqlite3.connect('database/kdrug.db')

def update_custom_prescriptions():
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # formula_id가 있는 모든 조제 조회
        cursor.execute("""
            SELECT compound_id, formula_id
            FROM compounds
            WHERE formula_id IS NOT NULL
        """)
        compounds = cursor.fetchall()

        print(f"검사할 조제: {len(compounds)}개")

        updated_count = 0
        for compound_id, formula_id in compounds:
            # 원 처방 구성 조회 (ingredient_code 기준)
            cursor.execute("""
                SELECT fi.ingredient_code, hm.herb_name, fi.grams_per_cheop
                FROM formula_ingredients fi
                JOIN herb_masters hm ON fi.ingredient_code = hm.ingredient_code
                WHERE fi.formula_id = ?
            """, (formula_id,))

            original_by_code = {}
            for row in cursor.fetchall():
                ingredient_code = row[0]
                herb_name = row[1]
                grams = row[2]
                original_by_code[ingredient_code] = {
                    'herb_name': herb_name,
                    'grams': grams
                }

            # 실제 조제 구성 조회 (ingredient_code 기준)
            cursor.execute("""
                SELECT hi.ingredient_code, hi.herb_name, ci.grams_per_cheop
                FROM compound_ingredients ci
                JOIN herb_items hi ON ci.herb_item_id = hi.herb_item_id
                WHERE ci.compound_id = ?
            """, (compound_id,))

            actual_by_code = {}
            for row in cursor.fetchall():
                ingredient_code = row[0]
                herb_name = row[1]
                grams = row[2]
                if ingredient_code not in actual_by_code:
                    actual_by_code[ingredient_code] = {
                        'herb_name': herb_name,
                        'grams': grams
                    }

            # 커스텀 여부 판단
            is_custom = False
            custom_details = []

            # 추가된 약재 확인
            for code, info in actual_by_code.items():
                if code not in original_by_code:
                    custom_details.append(f"{info['herb_name']} {info['grams']}g 추가")
                    is_custom = True

            # 제거된 약재 확인
            for code, info in original_by_code.items():
                if code not in actual_by_code:
                    custom_details.append(f"{info['herb_name']} 제거")
                    is_custom = True

            # 용량 변경된 약재 확인
            for code in original_by_code:
                if code in actual_by_code:
                    original_grams = original_by_code[code]['grams']
                    actual_grams = actual_by_code[code]['grams']
                    if abs(original_grams - actual_grams) > 0.01:
                        herb_name = original_by_code[code]['herb_name']
                        custom_details.append(f"{herb_name} {original_grams}g→{actual_grams}g")
                        is_custom = True

            # 커스텀인 경우 업데이트
            if is_custom:
                custom_summary = " | ".join(custom_details)
                cursor.execute("""
                    UPDATE compounds
                    SET is_custom = 1,
                        custom_summary = ?,
                        custom_type = 'custom'
                    WHERE compound_id = ?
                """, (custom_summary, compound_id))

                # 처방명 조회
                cursor.execute("""
                    SELECT f.formula_name
                    FROM compounds c
                    JOIN formulas f ON c.formula_id = f.formula_id
                    WHERE c.compound_id = ?
                """, (compound_id,))
                formula_name = cursor.fetchone()[0]

                print(f"  - Compound #{compound_id} ({formula_name}): 가감방으로 업데이트")
                print(f"    변경사항: {custom_summary}")
                updated_count += 1

        conn.commit()
        print(f"\n완료! {updated_count}개의 조제가 가감방으로 업데이트되었습니다.")

    except Exception as e:
        conn.rollback()
        print(f"오류 발생: {e}")
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    update_custom_prescriptions()