#!/usr/bin/env python3
"""
更新的采购方式识别逻辑
基于用户提供的详细业务规则实现正确的采购方式分配
"""

import asyncio
import logging
from datetime import datetime
from sqlalchemy import text, create_engine
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from app.models.products import PendingPurchase

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)


def determine_procurement_method(product_name: str, sales_attributes: str, brand: str = "") -> str:
    """
    根据重新规整的业务规则确定采购方式
    
    规则：
    1. 线上宝贝名称后缀为MC，或者线上销售属性里面颜色是MC的 → MC(库存)
    2. 线上宝贝名称后缀是AT/ATM，且线上销售属性没有MC的 → AT(Atelier)
    3. 结尾是AP/APM，但线上销售属性里面没有MC的 → AP(昌昌)
    4. 线上宝贝名称结尾LA/LAM的 → LA（无论销售属性是否有LA或NY）
    5. 线上宝贝名称结尾是SS/SSM（大写或小写），线上销售属性没有MC的 → SS(Saks)
    6. 线上宝贝名称结尾没有LA/MC/LAM/SS/SSM/AT/ATM，线上销售属性里面有LA的，属于LA（LA后面只能是结束或_）
    7. 线上宝贝名称里面有现货/GN现货，或品牌是GrundZero/Jansport/EMU → GN(国内现货)
    8. 其他情况 → NY
    """
    product_name = product_name.strip() if product_name else ""
    sales_attributes = sales_attributes.strip() if sales_attributes else ""
    brand = brand.strip() if brand else ""
    
    # 转为大写便于匹配
    product_name_upper = product_name.upper()
    sales_attributes_upper = sales_attributes.upper()
    brand_upper = brand.upper()
    
    # 规则1: MC库存
    if (product_name_upper.endswith(' MC') or 
        product_name_upper.endswith('MC') or
        'MC' in sales_attributes_upper):
        return 'MC'
    
    # 规则2: AT/ATM Atelier
    if ((product_name_upper.endswith(' AT') or 
         product_name_upper.endswith('AT') or
         product_name_upper.endswith(' ATM') or
         product_name_upper.endswith('ATM')) and
        'MC' not in sales_attributes_upper):
        return 'AT'
    
    # 规则3: AP/APM 昌昌
    if ((product_name_upper.endswith(' AP') or 
         product_name_upper.endswith('AP') or
         product_name_upper.endswith(' APM') or
         product_name_upper.endswith('APM')) and
        'MC' not in sales_attributes_upper):
        return 'AP'
    
    # 规则4: LA/LAM - 商品名称以LA/LAM结尾的直接归类为LA
    if (product_name_upper.endswith(' LA') or 
        product_name_upper.endswith('LA') or
        product_name_upper.endswith(' LAM') or
        product_name_upper.endswith('LAM')):
        return 'LA'
    
    # 规则5: SS/SSM Saks
    if ((product_name_upper.endswith(' SS') or 
         product_name_upper.endswith('SS') or
         product_name_upper.endswith(' SSM') or
         product_name_upper.endswith('SSM')) and
        'MC' not in sales_attributes_upper):
        return 'SS'
    
    # 规则6: 没有特定后缀，但销售属性有独立的LA
    suffixes_to_exclude = ['LA', 'MC', 'LAM', 'SS', 'SSM', 'AT', 'ATM']
    has_excluded_suffix = any(
        product_name_upper.endswith(f' {suffix}') or 
        product_name_upper.endswith(suffix) 
        for suffix in suffixes_to_exclude
    )
    
    if not has_excluded_suffix and _has_independent_la(sales_attributes_upper):
        return 'LA'
    
    # 规则7: 国内现货 - 现货关键词或特定品牌
    if (_has_stock_keywords(product_name) or 
        _is_domestic_brand(brand_upper)):
        return 'GN'
    
    # 规则8: 默认NY
    return 'NY'


def _has_independent_la(sales_attributes_upper: str) -> bool:
    """检查销售属性中是否有独立的LA（LA后面只能是结束或_）"""
    import re
    # LA后面必须是结束、下划线、空格或数字，不能是字母
    pattern = r'LA(?=_|\s|$|\d)'
    return bool(re.search(pattern, sales_attributes_upper))


def _has_stock_keywords(product_name: str) -> bool:
    """检查商品名称是否包含现货关键词"""
    return ('现货' in product_name or 
            'GN现货' in product_name or 
            '差价' in product_name)


def _is_domestic_brand(brand_upper: str) -> bool:
    """检查是否是国内现货品牌"""
    domestic_brands = ['GRUNDZERO', 'JANSPORT', 'EMU']
    return any(domestic_brand in brand_upper for domestic_brand in domestic_brands)


def test_procurement_method_logic():
    """测试采购方式识别逻辑"""
    test_cases = [
        # MC库存测试
        ("某某商品MC", "Black_M", "", "MC"),
        ("某某商品 MC", "Blue_L", "", "MC"),
        ("某某商品", "MC_Red_S", "", "MC"),
        
        # AT Atelier测试
        ("某某商品AT", "Black_M", "", "AT"),
        ("某某商品 ATM", "Blue_L", "", "AT"),
        ("某某商品ATM", "Blue_L", "", "AT"),
        ("某某商品AT", "MC_Red_S", "", "MC"),  # 有MC优先
        
        # AP昌昌测试
        ("某某商品AP", "Black_M", "", "AP"),
        ("某某商品 APM", "Blue_L", "", "AP"),
        ("某某商品AP", "MC_Red_S", "", "MC"),  # 有MC优先
        
        # LA测试 - 商品名称以LA/LAM结尾的直接归类为LA（无论销售属性是否有LA或NY）
        ("某某商品LA", "Black_M", "", "LA"),    # 以LA结尾直接归类为LA
        ("某某商品 LAM", "Blue_L", "", "LA"),   # 以LAM结尾直接归类为LA
        ("某某商品LA", "LA_Red_S", "", "LA"),   # 以LA结尾直接归类为LA，不管销售属性有LA
        ("某某商品LA", "NY_Red_S", "", "LA"),   # 以LA结尾直接归类为LA，不管销售属性有NY
        
        # SS Saks测试
        ("某某商品SS", "Black_M", "", "SS"),
        ("某某商品 SSM", "Blue_L", "", "SS"),
        ("某某商品ss", "Blue_L", "", "SS"),  # 小写
        ("某某商品SS", "MC_Red_S", "", "MC"),  # 有MC优先
        
        # 规则6测试 - 没有特定后缀但销售属性有独立的LA
        ("某某普通商品", "LA_Black_M", "", "LA"),      # 独立的LA_
        ("某某普通商品", "白色LA_6/36", "", "LA"),      # 独立的LA_
        ("某某普通商品", "蓝色LA_6[/36.5]", "", "LA"), # 独立的LA_
        ("某某普通商品", "BLACK_M", "", "NY"),         # BLACK中的LA不是独立的
        
        # 规则7测试 - 国内现货
        ("现货Reveal 男女款格纹水洗口袋拼色", "Black_M", "", "GN"),
        ("GN现货 某某商品", "Blue_L", "", "GN"),
        ("某某商品", "Black_M", "GrundZero", "GN"),     # 品牌GrundZero
        ("某某商品", "Blue_L", "Jansport", "GN"),       # 品牌Jansport  
        ("某某商品", "Red_S", "EMU", "GN"),             # 品牌EMU
        
        # 规则8测试 - 默认NY
        ("其他普通商品", "Green_M", "", "NY"),          # 默认NY
        ("另一个普通商品", "Blue_L", "", "NY"),         # 默认NY
    ]
    
    print("=== 采购方式识别逻辑测试 ===")
    for i, (product_name, sales_attr, brand, expected) in enumerate(test_cases, 1):
        result = determine_procurement_method(product_name, sales_attr, brand)
        status = "✅" if result == expected else "❌"
        print(f"{i:2d}. {status} 商品:{product_name[:20]:20} 属性:{sales_attr[:15]:15} 品牌:{brand[:10]:10} 期望:{expected} 结果:{result}")
    
    print()


async def update_procurement_methods():
    """更新数据库中的采购方式"""
    # 创建数据库连接
    engine = create_async_engine('sqlite+aiosqlite:///./ordersys.db')
    async_session = async_sessionmaker(engine, expire_on_commit=False)
    
    async with async_session() as session:
        logger.info("开始更新采购方式...")
        
        # 1. 获取所有待采购订单的商品信息
        # 直接从products表获取，因为products表的线上宝贝名称就是我们需要的
        result = await session.execute(text('''
            SELECT DISTINCT
                pp.id,
                p.线上宝贝名称,
                oin.线上销售属性,
                p.品牌,
                pp.procurement_method as current_method
            FROM pending_purchases pp
            JOIN products p ON pp.product_id = p.id
            LEFT JOIN order_items_norm oin ON p.线上宝贝名称 = oin.线上宝贝名称
            WHERE oin.procurement_status = 'WAITING'
            ORDER BY pp.id
        '''))
        
        items = result.fetchall()
        logger.info(f"找到 {len(items)} 个待更新的采购需求")
        
        # 2. 统计更新前的分布
        old_method_stats = await session.execute(text('''
            SELECT procurement_method, COUNT(*) 
            FROM pending_purchases 
            GROUP BY procurement_method 
            ORDER BY COUNT(*) DESC
        '''))
        
        print("\n=== 更新前的采购方式分布 ===")
        for row in old_method_stats:
            print(f"{row[0]}: {row[1]} 个SKU")
        
        # 3. 逐个更新采购方式
        updated_count = 0
        method_changes = {}
        
        for item in items:
            item_id = item.id
            product_name = item.线上宝贝名称 or ""
            sales_attributes = item.线上销售属性 or ""
            brand = item.品牌 or ""
            old_method = item.current_method
            
            # 使用新逻辑确定采购方式
            new_method = determine_procurement_method(product_name, sales_attributes, brand)
            
            # 如果方式有变化，更新数据库
            if new_method != old_method:
                await session.execute(text('''
                    UPDATE pending_purchases 
                    SET procurement_method = :new_method,
                        last_updated = :now
                    WHERE id = :item_id
                '''), {
                    'new_method': new_method,
                    'now': datetime.now(),
                    'item_id': item_id
                })
                
                # 记录变化统计
                change_key = f"{old_method} → {new_method}"
                method_changes[change_key] = method_changes.get(change_key, 0) + 1
                updated_count += 1
                
                # 每100个提交一次
                if updated_count % 100 == 0:
                    await session.commit()
                    logger.info(f"已更新 {updated_count} 个采购需求...")
        
        # 最终提交
        await session.commit()
        
        # 4. 统计更新后的分布
        new_method_stats = await session.execute(text('''
            SELECT procurement_method, COUNT(*) 
            FROM pending_purchases 
            GROUP BY procurement_method 
            ORDER BY COUNT(*) DESC
        '''))
        
        print(f"\n=== 采购方式更新完成 ===")
        print(f"总处理: {len(items)} 个SKU")
        print(f"实际更新: {updated_count} 个SKU")
        print(f"无需更新: {len(items) - updated_count} 个SKU")
        
        if method_changes:
            print(f"\n=== 更新变化统计 ===")
            for change, count in sorted(method_changes.items(), key=lambda x: x[1], reverse=True):
                print(f"{change}: {count} 个SKU")
        
        print(f"\n=== 更新后的采购方式分布 ===")
        method_descriptions = {
            'GN': 'GN(国内现货)',
            'MC': 'MC(库存)',
            'AT': 'AT(Atelier)',
            'AP': 'AP(昌昌)',
            'LA': 'LA(洛杉矶)',
            'SS': 'SS(Saks)',
            'NY': 'NY(纽约默认)'
        }
        
        for row in new_method_stats:
            desc = method_descriptions.get(row[0], row[0])
            print(f"{row[0]} - {desc}: {row[1]} 个SKU")
        
        return {
            "total_items": len(items),
            "updated_count": updated_count,
            "changes": method_changes
        }


async def show_procurement_examples():
    """展示不同采购方式的商品示例"""
    engine = create_async_engine('sqlite+aiosqlite:///./ordersys.db')
    async_session = async_sessionmaker(engine, expire_on_commit=False)
    
    async with async_session() as session:
        print("\n=== 各采购方式商品示例 ===")
        
        methods = ['GN', 'MC', 'AT', 'AP', 'LA', 'SS', 'NY']
        method_descriptions = {
            'GN': '国内现货',
            'MC': '库存',
            'AT': 'Atelier',
            'AP': '昌昌',
            'LA': '洛杉矶',
            'SS': 'Saks',
            'NY': '纽约默认'
        }
        
        for method in methods:
            examples = await session.execute(text('''
                SELECT p.线上宝贝名称, pp.需求数量, COUNT(*) as sku_count
                FROM pending_purchases pp
                JOIN products p ON pp.product_id = p.id
                WHERE pp.procurement_method = :method
                GROUP BY p.线上宝贝名称, pp.需求数量
                ORDER BY pp.需求数量 DESC
                LIMIT 3
            '''), {'method': method})
            
            results = examples.fetchall()
            if results:
                print(f"\n【{method} - {method_descriptions[method]}】:")
                for i, row in enumerate(results, 1):
                    product_name = row[0][:40] + "..." if len(row[0]) > 40 else row[0]
                    print(f"  {i}. {product_name}")
                    print(f"     需求数量: {row[1]}")
                    print(f"     SKU数: {row[2]}")


async def main():
    """主函数"""
    print("=" * 60)
    print("更新的采购方式识别逻辑")
    print("=" * 60)
    
    # 1. 测试识别逻辑
    test_procurement_method_logic()
    
    # 2. 更新数据库中的采购方式
    result = await update_procurement_methods()
    
    # 3. 展示不同采购方式的示例
    await show_procurement_examples()
    
    print(f"\n🎯 采购方式更新完成!")
    print(f"   总共处理: {result['total_items']} 个SKU")
    print(f"   实际更新: {result['updated_count']} 个SKU")
    print(f"\n📍 现在可以访问：")
    print(f"   待采购订单列表: http://localhost:3000/procurement/orders")
    print("=" * 60)


if __name__ == "__main__":
    asyncio.run(main())