#!/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")
            
            # Method 1: Exact match
            exact_query = select(ProductMaster).where(
                ProductMaster.线上宝贝名称 == exact_name
            )
            
            exact_result = await db.execute(exact_query)
            products = exact_result.scalars().all()
            
            if products:
                print(f"✅ 精确匹配找到 {len(products)} 个产品:\n")
                for i, product in enumerate(products, 1):
                    print(f"产品 {i}:")
                    print(f"  ID: {product.id}")
                    print(f"  产品名称: {product.线上宝贝名称}")
                    print(f"  品牌: '{product.品牌}' (长度: {len(product.品牌)} 字符)")
                    print(f"  货号: '{product.货号}'")
                    print(f"  颜色: '{product.颜色}'")
                    print(f"  尺码: '{product.尺码}'")
                    print(f"  采购渠道: '{product.采购渠道}'")
                    print(f"  SKU: '{product.sku}'")
                    print(f"  创建时间: {product.created_at}")
                    print(f"  更新时间: {product.updated_at}")
                    print()
                    
                    # Check if brand is correct
                    if product.品牌 == "Tods":
                        print(f"  ✅ 品牌正确")
                    else:
                        print(f"  ❌ 品牌错误！应该是 'Tods' 但显示为 '{product.品牌}'")
                    print("-" * 40)
            else:
                print("❌ 精确匹配未找到，尝试模糊搜索...\n")
            
            # Method 2: Search by product code
            code_query = select(ProductMaster).where(
                ProductMaster.货号 == 'XXW70K0GU70MIDM033'
            )
            
            code_result = await db.execute(code_query)
            code_products = code_result.scalars().all()
            
            if code_products:
                print(f"\n通过货号找到 {len(code_products)} 个产品:")
                for product in code_products:
                    print(f"\nID: {product.id}")
                    print(f"产品名称: {product.线上宝贝名称}")
                    print(f"品牌: '{product.品牌}'")
                    print(f"货号: '{product.货号}'")
            
            # Method 3: Partial search
            partial_query = select(ProductMaster).where(
                ProductMaster.线上宝贝名称.like('%卡其色麻花拖鞋%XXW70K0GU70MIDM033%')
            )
            
            partial_result = await db.execute(partial_query)
            partial_products = partial_result.scalars().all()
            
            if partial_products and not products:  # Only show if exact match didn't find
                print(f"\n部分匹配找到 {len(partial_products)} 个产品:")
                for product in partial_products:
                    print(f"\nID: {product.id}")
                    print(f"产品名称: {product.线上宝贝名称}")
                    print(f"品牌: '{product.品牌}'")
                    print(f"货号: '{product.货号}'")
            
            # Show raw SQL query result for transparency
            print("\n\n直接SQL查询结果:")
            print("-" * 80)
            
            raw_query = text("""
                SELECT id, 线上宝贝名称, 品牌, 货号, 颜色, 尺码
                FROM products_master
                WHERE 线上宝贝名称 LIKE '%卡其色麻花拖鞋%'
                   OR 货号 = 'XXW70K0GU70MIDM033'
            """)
            
            raw_result = await db.execute(raw_query)
            raw_products = raw_result.fetchall()
            
            if raw_products:
                for row in raw_products:
                    print(f"\nID: {row[0]}")
                    print(f"产品名称: {row[1]}")
                    print(f"品牌: '{row[2]}' (数据库实际值)")
                    print(f"货号: '{row[3]}'")
                    print(f"颜色: '{row[4]}'")
                    print(f"尺码: '{row[5]}'")
            else:
                print("未找到相关产品")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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