#!/usr/bin/env python3
"""
Direct database check and update for the problematic product
"""
import sys
import asyncio
sys.path.append('backend')

from sqlalchemy import select, update, text
from app.core.database import get_db, init_db
from app.models.products_master import ProductMaster
from app.utils.text_parser import BrandExtractor

async def direct_check_and_fix():
    """Direct check and potential fix for the product"""
    print("Direct database check and fix...")
    
    try:
        # Initialize database
        await init_db()
        
        async for db in get_db():
            # Find all products with problematic Tods brand names
            problem_query = text("""
                SELECT id, 线上宝贝名称, 品牌, 货号, length(品牌) as brand_length
                FROM products_master 
                WHERE 品牌 LIKE 'Tods%' 
                AND 品牌 != 'Tods'
                ORDER BY id
            """)
            
            result = await db.execute(problem_query)
            problematic_products = result.fetchall()
            
            if problematic_products:
                print(f"\n发现 {len(problematic_products)} 个有问题的Tods产品:")
                print("=" * 80)
                
                for row in problematic_products:
                    print(f"\nID: {row[0]}")
                    print(f"产品名称: {row[1]}")
                    print(f"当前品牌: '{row[2]}'")
                    print(f"品牌长度: {row[4]} 字符")
                    print(f"货号: '{row[3]}'")
                    
                    # Fix the brand
                    update_query = update(ProductMaster).where(
                        ProductMaster.id == row[0]
                    ).values(品牌='Tods')
                    
                    await db.execute(update_query)
                    print(f"✅ 已修正为: 'Tods'")
                
                await db.commit()
                print(f"\n✅ 成功修正 {len(problematic_products)} 个产品的品牌名")
                
            else:
                print("\n✅ 没有发现品牌名有问题的Tods产品")
            
            # Double check all Tods products
            print("\n\n验证所有Tods产品:")
            print("=" * 80)
            
            verify_query = text("""
                SELECT 品牌, COUNT(*) as count, 
                       MIN(length(品牌)) as min_len, 
                       MAX(length(品牌)) as max_len
                FROM products_master 
                WHERE 线上宝贝名称 LIKE '%Tods%'
                   OR 品牌 LIKE '%Tods%'
                GROUP BY 品牌
                ORDER BY count DESC
            """)
            
            verify_result = await db.execute(verify_query)
            brand_stats = verify_result.fetchall()
            
            for stat in brand_stats:
                print(f"品牌: '{stat[0]}' - {stat[1]} 个产品 (长度: {stat[2]}-{stat[3]} 字符)")
            
            # Show a sample of Tods products
            print("\n\nTods产品样本:")
            print("=" * 80)
            
            sample_query = select(ProductMaster).where(
                ProductMaster.品牌 == 'Tods'
            ).limit(5)
            
            sample_result = await db.execute(sample_query)
            samples = sample_result.scalars().all()
            
            for i, product in enumerate(samples, 1):
                print(f"\n{i}. {product.线上宝贝名称[:60]}...")
                print(f"   品牌: '{product.品牌}' ✅")
                print(f"   货号: '{product.货号}'")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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