#!/usr/bin/env python3
"""
Import Excel data and test the new smart selection logic
"""
import sys
import asyncio
from pathlib import Path
sys.path.append('backend')

from app.core.database import get_db, init_db
from app.services.import_service import ImportService
from app.services.products_master_service import ProductsMasterService
from sqlalchemy import select, func
from app.models.raw_orders import RawOrder
from app.models.products_master import ProductMaster

async def import_and_test():
    """Import Excel data and test new logic"""
    print("Starting import and testing process...")
    
    try:
        # Initialize database
        await init_db()
        
        # Create service instances
        import_service = ImportService()
        products_service = ProductsMasterService()
        
        # Get database session
        async for db in get_db():
            # Check if we already have raw data
            raw_count = await db.scalar(select(func.count(RawOrder.id)))
            print(f"Current raw orders count: {raw_count}")
            
            # Import data if needed
            if raw_count == 0:
                print("No raw data found, importing Excel file...")
                
                import_result = await import_service.import_file(
                    db,
                    Path("/Users/jinjunqian/PycharmProjects/订单处理新版/原始订单导出-2025-08-20.xls")
                )
                print(f"Import result: {import_result}")
                
                # Check new count
                raw_count = await db.scalar(select(func.count(RawOrder.id)))
                print(f"Raw orders after import: {raw_count}")
            
            if raw_count > 0:
                # Generate products with new logic
                print("\nGenerating products with smart selection logic...")
                result = await products_service.generate_products_master(db, force_rebuild=True)
                
                print(f"\n处理结果:")
                for key, value in result.items():
                    print(f"{key}: {value}")
                
                # Check products with brand issues  
                print("\nChecking for brand recognition issues...")
                brand_check_query = select(ProductMaster).where(
                    ProductMaster.品牌.like('MaxMara%') |
                    ProductMaster.品牌.like('Tods%') |
                    ProductMaster.品牌.like('SportMax%')
                ).limit(10)
                
                brand_issues = await db.execute(brand_check_query)
                problem_products = brand_issues.scalars().all()
                
                if problem_products:
                    print(f"\nFound {len(problem_products)} products with potential brand issues:")
                    for i, product in enumerate(problem_products, 1):
                        print(f"{i}. Product: {product.线上宝贝名称}")
                        print(f"   Brand: '{product.品牌}'")
                        print(f"   Product Code: '{product.货号}'")
                        print("")
                else:
                    print("No obvious brand issues found in the limited sample.")
                
                # Show some general samples
                print("\nSample products:")
                sample_query = select(ProductMaster).limit(10)
                sample_result = await db.execute(sample_query)
                products = sample_result.scalars().all()
                
                for i, product in enumerate(products, 1):
                    print(f"{i}. {product.线上宝贝名称}")
                    print(f"   Brand: '{product.品牌 or 'None'}'")
                    print(f"   Product Code: '{product.货号 or 'None'}'")
                    print("")
            
            break
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()

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