#!/usr/bin/env python3
"""
检查procurement_orders表的品牌数据
"""
import sys
import asyncio
sys.path.append('backend')

from sqlalchemy import text
from app.core.database import get_db

async def check_procurement_orders_brands():
    """检查procurement_orders表的品牌数据"""
    print("检查procurement_orders表的品牌数据...")
    print("=" * 80)
    
    try:
        async for db in get_db():
            # 1. 检查Tods产品在procurement_orders表中的品牌
            print("1. 检查Tods产品在procurement_orders表中的品牌:")
            print("-" * 40)
            
            query = text("""
                SELECT DISTINCT 线上宝贝名称, 品牌, COUNT(*) as count
                FROM procurement_orders
                WHERE 线上宝贝名称 LIKE '%Tods%'
                GROUP BY 线上宝贝名称, 品牌
                ORDER BY count DESC
                LIMIT 20
            """)
            
            result = await db.execute(query)
            rows = result.fetchall()
            
            print(f"找到 {len(rows)} 个不同的Tods产品")
            
            error_count = 0
            for row in rows:
                product_name = row[0]
                brand = row[1]
                count = row[2]
                
                print(f"\n产品: {product_name[:60]}...")
                print(f"品牌: '{brand}'")
                print(f"记录数: {count}")
                
                if brand != "Tods" and "Tods" in product_name:
                    print(f"❌ 品牌错误！应该是 'Tods'")
                    error_count += 1
                    
                    # 分析错误模式
                    if len(brand) > 20:
                        print(f"   品牌长度异常: {len(brand)} 字符")
                    if "黑色" in brand or "卡其色" in brand or "棕色" in brand:
                        print(f"   品牌包含颜色信息")
                    if "拖鞋" in brand or "凉鞋" in brand or "双肩包" in brand:
                        print(f"   品牌包含产品类型")
                elif brand == "Tods":
                    print(f"✅ 品牌正确")
            
            print(f"\n\n发现 {error_count} 个品牌错误的Tods产品")
            
            # 2. 检查具体的产品
            print("\n2. 检查用户提到的具体产品:")
            print("-" * 40)
            
            specific_products = [
                "Tods女款卡其色麻花拖鞋 XXW70K0GU70MIDM033美国代购8.15 LA",
                "Tods女款黑色一字带平底凉鞋 XXW15L0IH90MIDB999美国代购 8.15LA",
                "Tods女款牛仔拼色镂空锁扣装饰踩鞋XXW00G0GW40R43ZZEU代购8.15LA",
                "小王国 Tods 黑色小Logo双肩包 XBMMDDG0300GECB999 8.14"
            ]
            
            for product_name in specific_products:
                query = text("""
                    SELECT 品牌, COUNT(*) as count
                    FROM procurement_orders
                    WHERE 线上宝贝名称 = :name
                    GROUP BY 品牌
                """)
                
                result = await db.execute(query, {"name": product_name})
                rows = result.fetchall()
                
                print(f"\n产品: {product_name[:60]}...")
                if rows:
                    for row in rows:
                        brand = row[0]
                        count = row[1]
                        print(f"  品牌: '{brand}' ({count} 条记录)")
                        
                        if brand != "Tods":
                            print(f"  ❌ 品牌错误！")
                        else:
                            print(f"  ✅ 品牌正确")
                else:
                    print(f"  未找到此产品")
            
            # 3. 对比两个表的品牌数据
            print("\n3. 对比products_master和procurement_orders表:")
            print("-" * 40)
            
            compare_query = text("""
                SELECT 
                    'products_master' as table_name,
                    品牌,
                    COUNT(*) as count
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%Tods%'
                GROUP BY 品牌
                
                UNION ALL
                
                SELECT 
                    'procurement_orders' as table_name,
                    品牌,
                    COUNT(*) as count
                FROM procurement_orders
                WHERE 线上宝贝名称 LIKE '%Tods%'
                GROUP BY 品牌
                
                ORDER BY table_name, count DESC
            """)
            
            compare_result = await db.execute(compare_query)
            compare_rows = compare_result.fetchall()
            
            pm_brands = {}
            po_brands = {}
            
            for row in compare_rows:
                table = row[0]
                brand = row[1]
                count = row[2]
                
                if table == 'products_master':
                    pm_brands[brand] = count
                else:
                    po_brands[brand] = count
            
            print("products_master表的品牌分布:")
            for brand, count in pm_brands.items():
                print(f"  '{brand}': {count} 个产品")
            
            print("\nprocurement_orders表的品牌分布:")
            for brand, count in po_brands.items():
                print(f"  '{brand}': {count} 条记录")
                
                if brand != "Tods" and "Tods" in brand:
                    print(f"    ❌ 这是错误的品牌值!")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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