#!/usr/bin/env python3
"""
修正Canada Goose品牌的历史货号数据
"""

import asyncio
import sys
sys.path.append('/Users/jinjunqian/PycharmProjects/订单处理新版/ordersys')

from backend.app.core.database import get_db
from backend.app.services.products_master_service import ProductsMasterService
from sqlalchemy import select, update
from backend.app.models.products_master import ProductMaster
from backend.app.models.raw_orders import RawOrder


async def fix_canada_goose_codes():
    """修正Canada Goose品牌的货号"""
    service = ProductsMasterService()

    async for db in get_db():
        try:
            print("开始修正Canada Goose产品货号...")
            print("=" * 80)

            # 1. 获取所有Canada Goose产品
            query = select(ProductMaster).where(ProductMaster.品牌 == 'Canada Goose')
            result = await db.execute(query)
            cg_products = result.scalars().all()

            print(f"找到 {len(cg_products)} 个Canada Goose产品")
            print("-" * 80)

            # 初始化解析器
            await service.initialize_parsers(db)

            fixed_count = 0
            unchanged_count = 0
            failed_count = 0

            # 记录修正详情
            fix_details = []

            for product in cg_products:
                old_code = product.货号

                # 使用新的提取逻辑重新提取货号
                new_code = service.product_code_extractor.extract_product_code(
                    product.线上宝贝名称,
                    product.品牌
                )

                # 如果货号有变化，则更新
                if new_code and new_code != old_code:
                    product.货号 = new_code
                    fixed_count += 1
                    fix_details.append({
                        'product': product.线上宝贝名称[:60] + '...' if len(product.线上宝贝名称) > 60 else product.线上宝贝名称,
                        'old_code': old_code,
                        'new_code': new_code
                    })
                    print(f"✅ 修正: {old_code or '无'} → {new_code}")
                    print(f"   产品: {product.线上宝贝名称[:60]}...")
                elif new_code == old_code:
                    unchanged_count += 1
                else:
                    failed_count += 1
                    print(f"⚠️ 无法提取货号: {product.线上宝贝名称[:60]}...")

            # 提交更改
            await db.commit()

            print("\n" + "=" * 80)
            print("修正结果汇总：")
            print(f"修正: {fixed_count} 个产品")
            print(f"保持不变: {unchanged_count} 个产品")
            print(f"提取失败: {failed_count} 个产品")
            print(f"总计: {len(cg_products)} 个产品")

            # 显示修正详情
            if fix_details:
                print("\n修正详情：")
                print("-" * 80)
                for detail in fix_details[:20]:  # 只显示前20个
                    print(f"产品: {detail['product']}")
                    print(f"  旧货号: {detail['old_code'] or '无'}")
                    print(f"  新货号: {detail['new_code']}")
                    print("-" * 80)

                if len(fix_details) > 20:
                    print(f"... 还有 {len(fix_details) - 20} 个产品被修正")

            # 2. 同步更新procurement_orders表中的货号
            print("\n更新procurement_orders表...")

            # 获取所有Canada Goose的采购订单
            from backend.app.models.procurement_orders import ProcurementOrder
            query = select(ProcurementOrder).where(ProcurementOrder.品牌 == 'Canada Goose')
            result = await db.execute(query)
            procurement_orders = result.scalars().all()

            po_fixed = 0
            for po in procurement_orders:
                # 重新提取货号
                new_code = service.product_code_extractor.extract_product_code(
                    po.线上宝贝名称,
                    'Canada Goose'
                )
                if new_code and new_code != po.货号:
                    po.货号 = new_code
                    po_fixed += 1

            await db.commit()
            print(f"更新了 {po_fixed} 个采购订单的货号")

            print("\n✅ Canada Goose货号修正完成！")

            return fixed_count, unchanged_count, failed_count

        except Exception as e:
            print(f"❌ 修正过程中发生错误: {e}")
            await db.rollback()
            raise
        finally:
            await db.close()
        break


async def verify_fixes():
    """验证修正结果"""
    async for db in get_db():
        try:
            print("\n验证修正结果...")
            print("=" * 80)

            # 检查一些常见的错误货号是否还存在
            bad_patterns = ['CA 4.29 MC', 'Expedition Parka', 'CYPRESS PUFFER',
                          'Chilliwack Puffer', 'Grandview Cropped', 'Lyndale Puffer']

            for pattern in bad_patterns:
                query = select(ProductMaster).where(
                    (ProductMaster.品牌 == 'Canada Goose') &
                    (ProductMaster.货号 == pattern)
                )
                result = await db.execute(query)
                count = len(result.scalars().all())

                if count > 0:
                    print(f"⚠️ 仍有 {count} 个产品使用错误货号: {pattern}")
                else:
                    print(f"✅ 已清理错误货号: {pattern}")

            # 显示一些正确的货号示例
            query = select(ProductMaster.货号).where(
                ProductMaster.品牌 == 'Canada Goose'
            ).distinct().limit(20)
            result = await db.execute(query)
            codes = [r[0] for r in result.all() if r[0]]

            print("\n当前Canada Goose货号示例：")
            for code in codes:
                print(f"  • {code}")

        finally:
            await db.close()
        break


if __name__ == "__main__":
    # 运行修正
    loop = asyncio.get_event_loop()
    fixed, unchanged, failed = loop.run_until_complete(fix_canada_goose_codes())

    # 验证结果
    loop.run_until_complete(verify_fixes())