#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
쌍화탕 처방 등록 스크립트
"""

import sqlite3
from datetime import datetime

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

    try:
        # 1. 쌍화탕 처방 등록
        cursor.execute("""
            INSERT INTO formulas (
                formula_code,
                formula_name,
                formula_type,
                base_cheop,
                base_pouches,
                description
            ) VALUES (?, ?, ?, ?, ?, ?)
        """, (
            'SST001',
            '쌍화탕',
            'CUSTOM',
            20,
            30,
            '기혈을 보하고 원기를 회복시키는 대표적인 보약 처방'
        ))

        formula_id = cursor.lastrowid
        print(f"✅ 쌍화탕 처방 등록 완료 (ID: {formula_id})")

        # 2. 쌍화탕 구성 약재 등록
        # 전형적인 쌍화탕 구성 (1첩 기준 용량)
        ingredients = [
            ('숙지황', 8.0, '보혈'),
            ('당귀', 6.0, '보혈'),
            ('백작약', 6.0, '보혈'),
            ('천궁', 4.0, '활혈'),
            ('황기', 6.0, '보기'),
            ('인삼', 4.0, '대보원기'),
            ('백출', 4.0, '보기건비'),
            ('감초', 3.0, '조화제약'),
            ('생강', 5.0, '온중'),
            ('대추', 3.0, '보중익기'),
            ('육계', 2.0, '온양'),
            ('건강', 2.0, '온중')  # 건강 포함!
        ]

        for herb_name, grams, notes in ingredients:
            # 약재 ID 찾기
            cursor.execute("""
                SELECT herb_item_id FROM herb_items
                WHERE herb_name = ?
            """, (herb_name,))

            result = cursor.fetchone()
            if result:
                herb_id = result[0]
                cursor.execute("""
                    INSERT INTO formula_ingredients (
                        formula_id,
                        herb_item_id,
                        grams_per_cheop,
                        notes
                    ) VALUES (?, ?, ?, ?)
                """, (formula_id, herb_id, grams, notes))
                print(f"  - {herb_name}: {grams}g ({notes})")
            else:
                print(f"  ⚠️  {herb_name} 약재를 찾을 수 없음")

        conn.commit()
        print("\n✅ 쌍화탕 처방 구성 완료!")

        # 3. 등록 확인
        cursor.execute("""
            SELECT
                f.formula_name,
                COUNT(fi.ingredient_id) as ingredient_count,
                SUM(fi.grams_per_cheop) as total_grams_per_cheop
            FROM formulas f
            LEFT JOIN formula_ingredients fi ON f.formula_id = fi.formula_id
            WHERE f.formula_id = ?
            GROUP BY f.formula_id
        """, (formula_id,))

        result = cursor.fetchone()
        if result:
            print(f"\n📋 등록 결과:")
            print(f"  처방명: {result[0]}")
            print(f"  구성 약재: {result[1]}종")
            print(f"  1첩 총량: {result[2]}g")
            print(f"  20첩 총량: {result[2] * 20}g")

    except sqlite3.IntegrityError as e:
        print(f"⚠️ 이미 등록된 처방이거나 오류 발생: {e}")
        conn.rollback()
    except Exception as e:
        print(f"❌ 오류 발생: {e}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == "__main__":
    insert_ssanghwa_formula()