#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
JOIN으로 인한 중복 문제 찾기
"""

import sqlite3

def find_duplicate_issue():
    conn = sqlite3.connect('database/kdrug.db')
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    print("=" * 80)
    print("JOIN으로 인한 중복 문제 분석")
    print("=" * 80)
    print()

    # 1. 효능 태그 JOIN 없이 계산
    print("1. 효능 태그 JOIN 없이 계산")
    print("-" * 60)

    cursor.execute("""
        SELECT
            h.herb_item_id,
            h.herb_name,
            COALESCE(SUM(il.quantity_onhand * il.unit_price_per_g), 0) as total_value
        FROM herb_items h
        LEFT JOIN inventory_lots il ON h.herb_item_id = il.herb_item_id AND il.is_depleted = 0
        GROUP BY h.herb_item_id, h.herb_name
        HAVING total_value > 0
        ORDER BY total_value DESC
        LIMIT 5
    """)

    simple_results = cursor.fetchall()
    simple_total = 0

    for item in simple_results:
        simple_total += item['total_value']
        print(f"  {item['herb_name']:15} ₩{item['total_value']:10,.0f}")

    # 전체 합계
    cursor.execute("""
        SELECT SUM(total_value) as grand_total
        FROM (
            SELECT
                h.herb_item_id,
                COALESCE(SUM(il.quantity_onhand * il.unit_price_per_g), 0) as total_value
            FROM herb_items h
            LEFT JOIN inventory_lots il ON h.herb_item_id = il.herb_item_id AND il.is_depleted = 0
            GROUP BY h.herb_item_id
            HAVING total_value > 0
        )
    """)

    simple_grand_total = cursor.fetchone()['grand_total'] or 0
    print(f"\n  총합: ₩{simple_grand_total:,.0f}")

    # 2. 효능 태그 JOIN 포함 계산 (API와 동일)
    print("\n2. 효능 태그 JOIN 포함 계산 (API 쿼리)")
    print("-" * 60)

    cursor.execute("""
        SELECT
            h.herb_item_id,
            h.herb_name,
            COALESCE(SUM(il.quantity_onhand * il.unit_price_per_g), 0) as total_value,
            COUNT(*) as row_count
        FROM herb_items h
        LEFT JOIN inventory_lots il ON h.herb_item_id = il.herb_item_id AND il.is_depleted = 0
        LEFT JOIN herb_products hp ON h.insurance_code = hp.product_code
        LEFT JOIN herb_item_tags hit ON COALESCE(h.ingredient_code, hp.ingredient_code) = hit.ingredient_code
        LEFT JOIN herb_efficacy_tags et ON hit.tag_id = et.tag_id
        GROUP BY h.herb_item_id, h.herb_name
        HAVING total_value > 0
        ORDER BY total_value DESC
        LIMIT 5
    """)

    api_results = cursor.fetchall()

    for item in api_results:
        print(f"  {item['herb_name']:15} ₩{item['total_value']:10,.0f} (행수: {item['row_count']})")

    # 전체 합계 (API 방식)
    cursor.execute("""
        SELECT SUM(total_value) as grand_total
        FROM (
            SELECT
                h.herb_item_id,
                COALESCE(SUM(il.quantity_onhand * il.unit_price_per_g), 0) as total_value
            FROM herb_items h
            LEFT JOIN inventory_lots il ON h.herb_item_id = il.herb_item_id AND il.is_depleted = 0
            LEFT JOIN herb_products hp ON h.insurance_code = hp.product_code
            LEFT JOIN herb_item_tags hit ON COALESCE(h.ingredient_code, hp.ingredient_code) = hit.ingredient_code
            LEFT JOIN herb_efficacy_tags et ON hit.tag_id = et.tag_id
            GROUP BY h.herb_item_id
            HAVING total_value > 0
        )
    """)

    api_grand_total = cursor.fetchone()['grand_total'] or 0
    print(f"\n  총합: ₩{api_grand_total:,.0f}")

    # 3. 중복 원인 분석
    print("\n3. 중복 원인 분석")
    print("-" * 60)

    print(f"  ✅ 정상 계산: ₩{simple_grand_total:,.0f}")
    print(f"  ❌ API 계산: ₩{api_grand_total:,.0f}")
    print(f"  차이: ₩{api_grand_total - simple_grand_total:,.0f}")

    if api_grand_total > simple_grand_total:
        ratio = api_grand_total / simple_grand_total if simple_grand_total > 0 else 0
        print(f"  배율: {ratio:.2f}배")

    # 4. 효능 태그 중복 확인
    print("\n4. 효능 태그로 인한 중복 확인")
    print("-" * 60)

    cursor.execute("""
        SELECT
            h.herb_name,
            h.ingredient_code,
            COUNT(DISTINCT hit.tag_id) as tag_count
        FROM herb_items h
        LEFT JOIN herb_products hp ON h.insurance_code = hp.product_code
        LEFT JOIN herb_item_tags hit ON COALESCE(h.ingredient_code, hp.ingredient_code) = hit.ingredient_code
        WHERE h.herb_item_id IN (
            SELECT herb_item_id FROM inventory_lots
            WHERE is_depleted = 0 AND quantity_onhand > 0
        )
        GROUP BY h.herb_item_id
        HAVING tag_count > 1
        ORDER BY tag_count DESC
        LIMIT 5
    """)

    multi_tags = cursor.fetchall()

    if multi_tags:
        print("  여러 효능 태그를 가진 약재:")
        for herb in multi_tags:
            print(f"    - {herb['herb_name']}: {herb['tag_count']}개 태그")

    # 5. 특정 약재 상세 분석 (휴먼감초)
    print("\n5. 휴먼감초 상세 분석")
    print("-" * 60)

    # 정상 계산
    cursor.execute("""
        SELECT
            il.lot_id,
            il.quantity_onhand,
            il.unit_price_per_g,
            il.quantity_onhand * il.unit_price_per_g as value
        FROM inventory_lots il
        JOIN herb_items h ON il.herb_item_id = h.herb_item_id
        WHERE h.herb_name = '휴먼감초' AND il.is_depleted = 0
    """)

    gamcho_lots = cursor.fetchall()
    actual_total = sum(lot['value'] for lot in gamcho_lots)

    print(f"  실제 LOT 수: {len(gamcho_lots)}개")
    for lot in gamcho_lots:
        print(f"    LOT {lot['lot_id']}: {lot['quantity_onhand']}g × ₩{lot['unit_price_per_g']} = ₩{lot['value']:,.0f}")
    print(f"  실제 합계: ₩{actual_total:,.0f}")

    # JOIN 포함 계산
    cursor.execute("""
        SELECT COUNT(*) as join_rows
        FROM herb_items h
        LEFT JOIN inventory_lots il ON h.herb_item_id = il.herb_item_id AND il.is_depleted = 0
        LEFT JOIN herb_products hp ON h.insurance_code = hp.product_code
        LEFT JOIN herb_item_tags hit ON COALESCE(h.ingredient_code, hp.ingredient_code) = hit.ingredient_code
        LEFT JOIN herb_efficacy_tags et ON hit.tag_id = et.tag_id
        WHERE h.herb_name = '휴먼감초' AND il.lot_id IS NOT NULL
    """)

    join_rows = cursor.fetchone()['join_rows']
    print(f"\n  JOIN 후 행 수: {join_rows}행")

    if join_rows > len(gamcho_lots):
        print(f"  ⚠️ 중복 발생! {join_rows / len(gamcho_lots):.1f}배로 뻥튀기됨")

    conn.close()

if __name__ == "__main__":
    find_duplicate_issue()