#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
휴먼허브 약재와 한의사랑 제품명 직접 매핑
"""

import sqlite3

def get_connection():
    """데이터베이스 연결"""
    return sqlite3.connect('database/kdrug.db')

def create_direct_mapping():
    """약재명 기준으로 직접 매핑"""

    # 매핑 테이블 (약재명 순서대로)
    mapping = {
        '신흥숙지황': '숙지황(9증)(신흥.1kg)[완]',
        '휴먼갈근': '갈근.각',
        '휴먼감초': '감초.1호[야생](1kg)',
        '휴먼건강': {  # 가격으로 구분
            12.4: '건강',  # 페루산 저가
            51.4: '건강.土'  # 한국산 고가
        },
        '휴먼계지': '계지',
        '휴먼구기자': '구기자(영하)(1kg)',
        '휴먼길경': '길경.片[특]',
        '휴먼대추': '대추(절편)(1kg)',
        '휴먼마황': '마황(1kg)',
        '휴먼반하생강백반제': '반하생강백반제(1kg)',
        '휴먼백출': '백출.당[1kg]',
        '휴먼복령': '복령(1kg)',
        '휴먼석고': '석고[통포장](kg)',
        '휴먼세신': '세신.中',
        '휴먼오미자': '오미자<토매지>(1kg)',
        '휴먼용안육': '용안육.名品(1kg)',
        '휴먼육계': '육계.YB',
        '휴먼일당귀': '일당귀.中(1kg)',
        '휴먼자소엽': '자소엽.土',
        '휴먼작약': '작약(1kg)',
        '휴먼작약주자': '작약주자.土[酒炙]',
        '휴먼전호': '전호[재배]',
        '휴먼지각': '지각',
        '휴먼지황': '지황.건[회](1kg)',
        '휴먼진피(陳皮)': '진피.비열[非熱](1kg)',
        '휴먼창출': '창출[북창출.재배](1kg)',
        '휴먼천궁': '천궁.일<토매지>(1kg)',
        '휴먼황기': '황기(직절.小)(1kg)'
    }

    return mapping

def apply_mapping():
    """매핑 적용"""
    conn = get_connection()
    cursor = conn.cursor()

    print("\n" + "="*80)
    print("휴먼허브 → 한의사랑 제품명 직접 매핑")
    print("="*80)

    mapping = create_direct_mapping()

    # 모든 inventory_lots 조회
    cursor.execute("""
        SELECT
            l.lot_id,
            h.herb_name,
            l.unit_price_per_g,
            l.origin_country
        FROM inventory_lots l
        JOIN herb_items h ON l.herb_item_id = h.herb_item_id
        ORDER BY h.herb_name
    """)

    lots = cursor.fetchall()
    success_count = 0

    for lot in lots:
        lot_id, herb_name, unit_price, origin = lot

        display_name = None

        # 매핑 찾기
        if herb_name in mapping:
            mapped = mapping[herb_name]

            # 건강처럼 가격으로 구분하는 경우
            if isinstance(mapped, dict):
                # 가장 가까운 가격 찾기
                closest_price = min(mapped.keys(), key=lambda x: abs(x - unit_price))
                if abs(closest_price - unit_price) < 5:  # 5원 이내 차이만 허용
                    display_name = mapped[closest_price]
            else:
                display_name = mapped

        if display_name:
            # display_name 업데이트
            cursor.execute("""
                UPDATE inventory_lots
                SET display_name = ?
                WHERE lot_id = ?
            """, (display_name, lot_id))

            # lot_variants 추가/업데이트
            try:
                cursor.execute("""
                    INSERT INTO lot_variants
                    (lot_id, raw_name, parsed_at, parsed_method)
                    VALUES (?, ?, datetime('now'), 'direct_mapping')
                """, (lot_id, display_name))
            except sqlite3.IntegrityError:
                cursor.execute("""
                    UPDATE lot_variants
                    SET raw_name = ?, parsed_at = datetime('now'), parsed_method = 'direct_mapping'
                    WHERE lot_id = ?
                """, (display_name, lot_id))

            print(f"✓ Lot #{lot_id:3d}: {herb_name:20s} → {display_name}")
            success_count += 1
        else:
            print(f"✗ Lot #{lot_id:3d}: {herb_name:20s} - 매핑 실패")

    conn.commit()

    print("\n" + "="*80)
    print(f"매핑 완료: {success_count}/{len(lots)}개")
    print("="*80)

    # 결과 확인
    cursor.execute("""
        SELECT
            h.herb_name,
            l.display_name,
            l.unit_price_per_g,
            l.origin_country
        FROM inventory_lots l
        JOIN herb_items h ON l.herb_item_id = h.herb_item_id
        WHERE l.display_name IS NOT NULL
        ORDER BY h.herb_name
    """)

    results = cursor.fetchall()

    print("\n설정된 Display Names:")
    print("-" * 80)
    for res in results:
        print(f"{res[0]:20s} → {res[1]:30s} ({res[2]:.1f}원/g, {res[3]})")

    conn.close()

def main():
    """메인 실행"""
    apply_mapping()

if __name__ == "__main__":
    main()