#!/usr/bin/env python3
"""
批量检查多个Tods产品的品牌数据
"""
import sys
import asyncio
sys.path.append('backend')

from sqlalchemy import select, text
from app.core.database import get_db
from app.models.products_master import ProductMaster

async def check_multiple_tods_products():
    """批量检查多个Tods产品"""
    print("批量检查Tods产品的品牌数据...")
    print("=" * 80)
    
    # 用户提到的具体产品列表
    products_to_check = [
        "小王国 Tods 黑色小Logo双肩包 XBMMDDG0300GECB999 8.14",
        "Tods女款牛仔拼色镂空锁扣装饰踩鞋XXW00G0GW40R43ZZEU代购8.15LA",
        "Tods女款黑色雕花细带布洛克皮鞋XXW60C0DE20EB5B999代购8.15LA",
        "Tods女款蓝色牛仔麻花扣乐福鞋XXW79A0DD00QFZZZEU代购8.15LA",
        "Tods女款卡其色麻花拖鞋 XXW70K0GU70MIDM033美国代购8.15 LA",
        "Tods女款棕色鳄鱼压纹麻花扣乐福鞋XXW79A0DD00WENS801代购8.15LA",
        "Tods女款黑色一字带平底凉鞋 XXW15L0IH90MIDB999美国代购 8.15LA"
    ]
    
    try:
        async for db in get_db():
            print("1. 逐个检查产品:")
            print("-" * 80)
            
            found_count = 0
            error_count = 0
            
            for i, product_name in enumerate(products_to_check, 1):
                print(f"\n{i}. 搜索: {product_name}")
                
                # 尝试精确匹配
                exact_query = select(ProductMaster).where(
                    ProductMaster.线上宝贝名称 == product_name
                )
                
                exact_result = await db.execute(exact_query)
                products = exact_result.scalars().all()
                
                if not products:
                    # 尝试模糊匹配（提取关键部分）
                    # 提取货号作为关键搜索词
                    import re
                    code_match = re.search(r'[A-Z0-9]{10,}', product_name)
                    if code_match:
                        code = code_match.group()
                        fuzzy_query = select(ProductMaster).where(
                            ProductMaster.货号 == code
                        )
                        fuzzy_result = await db.execute(fuzzy_query)
                        products = fuzzy_result.scalars().all()
                
                if products:
                    found_count += 1
                    for product in products:
                        print(f"   ✅ 找到产品:")
                        print(f"      ID: {product.id}")
                        print(f"      数据库产品名: {product.线上宝贝名称}")
                        print(f"      数据库品牌: '{product.品牌}'")
                        print(f"      品牌长度: {len(product.品牌)} 字符")
                        print(f"      货号: {product.货号}")
                        
                        # 检查品牌是否正确
                        if product.品牌 == "Tods":
                            print(f"      ✅ 数据库中品牌正确")
                        else:
                            print(f"      ❌ 数据库中品牌错误！")
                            error_count += 1
                            
                            # 分析错误模式
                            if "黑色" in product.品牌:
                                print(f"         ⚠️ 品牌包含颜色")
                            if "双肩包" in product.品牌 or "凉鞋" in product.品牌 or "乐福鞋" in product.品牌:
                                print(f"         ⚠️ 品牌包含产品类型")
                            if "女款" in product.品牌:
                                print(f"         ⚠️ 品牌包含性别标识")
                else:
                    print(f"   ❌ 未找到此产品")
            
            print(f"\n\n2. 统计汇总:")
            print("-" * 80)
            print(f"检查产品总数: {len(products_to_check)}")
            print(f"找到产品数: {found_count}")
            print(f"品牌错误数: {error_count}")
            
            # 3. 直接SQL查询所有Tods产品的品牌情况
            print(f"\n3. SQL查询所有Tods相关产品的品牌分布:")
            print("-" * 80)
            
            sql_query = text("""
                SELECT 品牌, COUNT(*) as count
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%Tods%'
                GROUP BY 品牌
                ORDER BY count DESC
                LIMIT 10
            """)
            
            sql_result = await db.execute(sql_query)
            brand_distribution = sql_result.fetchall()
            
            print(f"品牌分布:")
            for row in brand_distribution:
                brand = row[0]
                count = row[1]
                print(f"   品牌: '{brand}' - {count} 个产品")
                
                if brand != "Tods" and "Tods" in brand:
                    print(f"      ❌ 这是错误的品牌值!")
            
            # 4. 检查是否有品牌包含产品描述的情况
            print(f"\n4. 检查品牌是否包含产品描述:")
            print("-" * 80)
            
            problem_query = text("""
                SELECT id, 线上宝贝名称, 品牌, 货号
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%Tods%'
                AND (
                    品牌 LIKE '%黑色%' OR
                    品牌 LIKE '%蓝色%' OR
                    品牌 LIKE '%棕色%' OR
                    品牌 LIKE '%卡其色%' OR
                    品牌 LIKE '%双肩包%' OR
                    品牌 LIKE '%凉鞋%' OR
                    品牌 LIKE '%乐福鞋%' OR
                    品牌 LIKE '%拖鞋%' OR
                    品牌 LIKE '%女款%'
                )
                LIMIT 10
            """)
            
            problem_result = await db.execute(problem_query)
            problem_products = problem_result.fetchall()
            
            if problem_products:
                print(f"❌ 发现 {len(problem_products)} 个品牌包含产品描述的产品:")
                for row in problem_products:
                    print(f"\n   ID: {row[0]}")
                    print(f"   产品: {row[1]}")
                    print(f"   错误品牌: '{row[2]}'")
                    print(f"   货号: {row[3]}")
            else:
                print("✅ 没有发现品牌包含产品描述的情况")
            
            # 5. 验证具体的货号
            print(f"\n5. 通过货号验证品牌:")
            print("-" * 80)
            
            codes_to_check = [
                "XBMMDDG0300GECB999",
                "XXW00G0GW40R43ZZEU",
                "XXW60C0DE20EB5B999",
                "XXW79A0DD00QFZZZEU",
                "XXW70K0GU70MIDM033",
                "XXW79A0DD00WENS801",
                "XXW15L0IH90MIDB999"
            ]
            
            for code in codes_to_check:
                code_query = select(ProductMaster).where(
                    ProductMaster.货号 == code
                )
                code_result = await db.execute(code_query)
                code_products = code_result.scalars().all()
                
                if code_products:
                    product = code_products[0]  # 取第一个
                    print(f"\n货号 {code}:")
                    print(f"   产品: {product.线上宝贝名称[:60]}...")
                    print(f"   数据库品牌: '{product.品牌}'")
                    
                    if product.品牌 == "Tods":
                        print(f"   ✅ 品牌正确")
                    else:
                        print(f"   ❌ 品牌错误")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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