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

from sqlalchemy import select, func, text
from app.core.database import get_db
from app.models.products_master import ProductMaster

async def final_brand_check():
    """Final comprehensive brand check"""
    print("Final comprehensive brand check...")
    
    try:
        async for db in get_db():
            # Check for remaining brand issues
            print("1. Checking for remaining long brand names (potential issues):")
            long_brand_query = select(ProductMaster).where(
                func.length(ProductMaster.品牌) > 15
            ).limit(10)
            
            long_brand_result = await db.execute(long_brand_query)
            long_brands = long_brand_result.scalars().all()
            
            if long_brands:
                for i, product in enumerate(long_brands, 1):
                    print(f"{i}. Product: {product.线上宝贝名称[:60]}...")
                    print(f"   Brand: '{product.品牌}' (Length: {len(product.品牌)})")
                    print("")
            else:
                print("   No problematic long brand names found!")
            
            # Check for None brands
            print("2. Checking for products with no brand:")
            none_brand_query = select(ProductMaster).where(
                ProductMaster.品牌.is_(None)
            ).limit(5)
            
            none_brand_result = await db.execute(none_brand_query)
            none_brands = none_brand_result.scalars().all()
            
            if none_brands:
                for i, product in enumerate(none_brands, 1):
                    print(f"{i}. Product: {product.线上宝贝名称[:80]}...")
                    print(f"   Brand: None")
                    print("")
            else:
                print("   No products with missing brands in sample!")
            
            # Show success cases
            print("3. Success cases - properly identified brands:")
            success_brands = ['ST JOHN', 'Tods', 'SportMax', 'Stuart Weitzman', 'MaxMara']
            
            for brand in success_brands:
                count_query = select(func.count(ProductMaster.id)).where(
                    ProductMaster.品牌 == brand
                )
                count = await db.scalar(count_query)
                print(f"   {brand}: {count} products")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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