#!/usr/bin/env python3
"""
Final comprehensive check of all brand data
"""
import sys
import asyncio
sys.path.append('backend')

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

async def final_brand_check():
    """Final comprehensive brand check"""
    print("最终全面检查品牌数据...")
    print("=" * 80)
    
    try:
        async for db in get_db():
            # 1. Summary of all brands
            print("\n1. 品牌汇总统计:")
            summary_query = text("""
                SELECT 品牌, COUNT(*) as count, 
                       MIN(length(品牌)) as min_len,
                       MAX(length(品牌)) as max_len,
                       AVG(length(品牌)) as avg_len
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%Tods%'
                GROUP BY 品牌
                ORDER BY count DESC
            """)
            
            result = await db.execute(summary_query)
            brands = result.fetchall()
            
            for brand in brands:
                print(f"品牌: '{brand[0]}'")
                print(f"  产品数: {brand[1]}")
                print(f"  品牌名长度: 最小={brand[2]}, 最大={brand[3]}, 平均={brand[4]:.1f}")
                
                if brand[0] != "Tods" and "Tods" in brand[0]:
                    print(f"  ❌ 错误的品牌名!")
                else:
                    print(f"  ✅ 正确")
                print()
            
            # 2. Check specific problematic patterns
            print("\n2. 检查特定错误模式:")
            patterns_to_check = [
                ('Tods%黑色%', 'Tods+黑色'),
                ('Tods%棕色%', 'Tods+棕色'),
                ('Tods%牛仔%', 'Tods+牛仔'),
                ('Tods%拼色%', 'Tods+拼色'),
                ('Tods%一字带%', 'Tods+一字带'),
                ('Tods%鳄鱼压纹%', 'Tods+鳄鱼压纹')
            ]
            
            for pattern, description in patterns_to_check:
                check_query = text(f"""
                    SELECT COUNT(*) FROM products_master 
                    WHERE 品牌 LIKE :pattern
                """)
                result = await db.execute(check_query, {"pattern": pattern})
                count = result.scalar()
                
                if count > 0:
                    print(f"❌ 发现 {count} 个产品的品牌包含 '{description}'")
                    # Show examples
                    example_query = text(f"""
                        SELECT id, 线上宝贝名称, 品牌 
                        FROM products_master 
                        WHERE 品牌 LIKE :pattern
                        LIMIT 3
                    """)
                    examples = await db.execute(example_query, {"pattern": pattern})
                    for ex in examples:
                        print(f"   - ID:{ex[0]}, 品牌:'{ex[2]}'")
                else:
                    print(f"✅ 没有产品的品牌包含 '{description}'")
            
            # 3. Total statistics
            print("\n3. 总体统计:")
            total_query = text("""
                SELECT 
                    COUNT(*) as total_products,
                    SUM(CASE WHEN 品牌 = 'Tods' THEN 1 ELSE 0 END) as correct_tods,
                    SUM(CASE WHEN 品牌 LIKE 'Tods%' AND 品牌 != 'Tods' THEN 1 ELSE 0 END) as incorrect_tods,
                    MAX(CASE WHEN 品牌 LIKE 'Tods%' AND 品牌 != 'Tods' THEN 品牌 ELSE '' END) as example_bad_brand
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%Tods%'
            """)
            
            stats = await db.execute(total_query)
            stat = stats.first()
            
            print(f"总Tods产品数: {stat[0]}")
            print(f"正确的Tods品牌: {stat[1]} 个")
            print(f"错误的Tods品牌: {stat[2]} 个")
            
            if stat[2] > 0:
                print(f"❌ 发现错误品牌示例: '{stat[3]}'")
            else:
                print(f"✅ 所有Tods产品品牌都正确!")
            
            # 4. Double-check the specific products mentioned by user
            print("\n4. 再次确认用户提到的具体产品:")
            specific_products = [
                "Tods女款棕色鳄鱼压纹麻花扣乐福鞋XXW79A0DD00WENS801代购8.15LA",
                "Tods女款黑色一字带平底凉鞋 XXW15L0IH90MIDB999美国代购 8.15LA",
                "Tods女款牛仔拼色镂空锁扣装饰踩鞋XXW00G0GW40R43ZZEU代购8.15LA"
            ]
            
            for product_name in specific_products:
                check_query = text("""
                    SELECT id, 品牌, 货号 
                    FROM products_master 
                    WHERE 线上宝贝名称 = :name
                """)
                result = await db.execute(check_query, {"name": product_name})
                products = result.fetchall()
                
                print(f"\n产品: {product_name[:50]}...")
                if products:
                    for p in products:
                        print(f"  ID: {p[0]}, 品牌: '{p[1]}', 货号: '{p[2]}'")
                        if p[1] == "Tods":
                            print(f"  ✅ 品牌正确")
                        else:
                            print(f"  ❌ 品牌错误!")
                else:
                    print(f"  未找到此产品")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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