#!/usr/bin/env python3
"""Fix escaped !! in main_image_path field"""

import asyncio
from sqlalchemy import select, update
from app.core.database import AsyncSessionLocal
from app.models.products_master import ProductMaster
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

async def fix_escaped_urls():
    """修复main_image_path中的转义问题"""
    async with AsyncSessionLocal() as db:
        # 查找所有包含转义 \!\! 的记录
        stmt = select(ProductMaster).where(
            ProductMaster.main_image_path.like('%\\!\\!%')
        )
        result = await db.execute(stmt)
        products = result.scalars().all()
        
        logger.info(f"找到 {len(products)} 个需要修复的产品")
        
        fixed_count = 0
        for product in products:
            old_path = product.main_image_path
            # 修复转义：将 \!\! 替换为 !!
            new_path = old_path.replace('\\!\\!', '!!')
            
            # 更新数据库
            update_stmt = (
                update(ProductMaster)
                .where(ProductMaster.id == product.id)
                .values(main_image_path=new_path)
            )
            await db.execute(update_stmt)
            fixed_count += 1
            
            logger.info(f"修复产品 {product.sku_key[:8]}...")
            logger.info(f"  旧URL: {old_path}")
            logger.info(f"  新URL: {new_path}")
        
        # 另外，确保main_image_path与original_image_urls保持一致
        # 查找所有有original_image_urls但main_image_path不一致的记录
        stmt2 = select(ProductMaster).where(
            ProductMaster.original_image_urls.isnot(None)
        )
        result2 = await db.execute(stmt2)
        products2 = result2.scalars().all()
        
        sync_count = 0
        for product in products2:
            if product.original_image_urls and len(product.original_image_urls) > 0:
                first_url = product.original_image_urls[0]
                if product.main_image_path != first_url:
                    # 更新main_image_path为第一个original_image_url
                    update_stmt = (
                        update(ProductMaster)
                        .where(ProductMaster.id == product.id)
                        .values(main_image_path=first_url)
                    )
                    await db.execute(update_stmt)
                    sync_count += 1
                    
                    if sync_count <= 5:  # 只显示前5个
                        logger.info(f"同步产品 {product.sku_key[:8]}... 的main_image_path")
                        logger.info(f"  从: {product.main_image_path}")
                        logger.info(f"  到: {first_url}")
        
        await db.commit()
        
        logger.info(f"\n修复完成:")
        logger.info(f"  - 修复转义问题: {fixed_count} 个产品")
        logger.info(f"  - 同步main_image_path: {sync_count} 个产品")
        
        # 验证修复结果
        stmt3 = select(ProductMaster).where(
            ProductMaster.品牌 == "Gucci",
            ProductMaster.货号 == "693449 XJECA 4697"
        )
        result3 = await db.execute(stmt3)
        gucci_product = result3.scalar_one_or_none()
        
        if gucci_product:
            logger.info(f"\nGucci产品验证:")
            logger.info(f"  main_image_path: {gucci_product.main_image_path}")
            if '!!' in gucci_product.main_image_path:
                logger.info("  ✓ URL已修复，包含正确的 '!!' ")
            else:
                logger.info("  ✗ URL仍有问题")

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