#!/usr/bin/env python3
"""
Check the specific Tods卡其色麻花拖鞋 product in database
"""
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_kaki_tods():
    """Check the specific product in database"""
    print("检查具体产品: Tods女款卡其色麻花拖鞋")
    print("=" * 80)
    
    try:
        async for db in get_db():
            # Search by exact name
            exact_name = "Tods女款卡其色麻花拖鞋 XXW70K0GU70MIDM033美国代购8.15 LA"
            
            print(f"搜索: {exact_name}\n")
            
            # Direct SQL query for complete information
            raw_query = text("""
                SELECT id, 线上宝贝名称, 品牌, 货号, 颜色, 采购渠道, sku,
                       length(品牌) as brand_length,
                       created_at, updated_at
                FROM products_master
                WHERE 线上宝贝名称 = :name
            """)
            
            result = await db.execute(raw_query, {"name": exact_name})
            products = result.fetchall()
            
            if products:
                print(f"✅ 找到 {len(products)} 个产品:\n")
                for i, row in enumerate(products, 1):
                    print(f"产品 {i}:")
                    print(f"  数据库ID: {row[0]}")
                    print(f"  产品名称: {row[1]}")
                    print(f"  品牌: '{row[2]}' (长度: {row[7]} 字符)")
                    print(f"  货号: '{row[3]}'")
                    print(f"  颜色: '{row[4]}'")
                    print(f"  采购渠道: '{row[5]}'")
                    print(f"  SKU: '{row[6]}'")
                    print(f"  创建时间: {row[8]}")
                    print(f"  更新时间: {row[9]}")
                    print()
                    
                    # Check if brand is correct
                    if row[2] == "Tods":
                        print(f"  ✅ 品牌正确 - 数据库中存储为 'Tods'")
                    else:
                        print(f"  ❌ 品牌错误！数据库中存储为 '{row[2]}'")
                    print("-" * 40)
            else:
                print("❌ 未找到该产品，尝试模糊搜索...\n")
                
                # Fuzzy search
                fuzzy_query = text("""
                    SELECT id, 线上宝贝名称, 品牌, 货号, length(品牌) as brand_length
                    FROM products_master
                    WHERE 线上宝贝名称 LIKE '%卡其色麻花拖鞋%'
                       OR 货号 = 'XXW70K0GU70MIDM033'
                    ORDER BY id
                """)
                
                fuzzy_result = await db.execute(fuzzy_query)
                fuzzy_products = fuzzy_result.fetchall()
                
                if fuzzy_products:
                    print(f"模糊搜索找到 {len(fuzzy_products)} 个相关产品:")
                    for row in fuzzy_products:
                        print(f"\n  ID: {row[0]}")
                        print(f"  产品名称: {row[1]}")
                        print(f"  品牌: '{row[2]}' (长度: {row[4]} 字符)")
                        print(f"  货号: '{row[3]}'")
                        
                        if row[2] == "Tods":
                            print(f"  ✅ 品牌正确")
                        else:
                            print(f"  ❌ 品牌异常: '{row[2]}'")
            
            # Summary statistics
            print("\n\n数据库中所有Tods产品品牌状态汇总:")
            print("-" * 80)
            
            stats_query = text("""
                SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN 品牌 = 'Tods' THEN 1 ELSE 0 END) as correct,
                    SUM(CASE WHEN 品牌 != 'Tods' THEN 1 ELSE 0 END) as incorrect
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%Tods%'
            """)
            
            stats_result = await db.execute(stats_query)
            stats = stats_result.first()
            
            print(f"总Tods产品: {stats[0]} 个")
            print(f"品牌正确 (='Tods'): {stats[1]} 个")
            print(f"品牌错误 (!='Tods'): {stats[2]} 个")
            
            if stats[2] == 0:
                print("\n✅ 数据库中所有Tods产品的品牌都是正确的！")
            else:
                print(f"\n❌ 发现 {stats[2]} 个品牌错误的产品")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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