#!/usr/bin/env python3
"""
简单修正Canada Goose货号
直接调用新的提取逻辑更新数据库
"""

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

import sqlite3
from app.utils.text_parser import ProductCodeExtractor

def fix_canada_goose_codes():
    """修正Canada Goose产品货号"""

    # 连接数据库
    conn = sqlite3.connect('backend/ordersys.db')
    cursor = conn.cursor()

    # 初始化提取器
    extractor = ProductCodeExtractor()

    print("开始修正Canada Goose产品货号...")
    print("=" * 80)

    # 获取所有Canada Goose产品
    cursor.execute("""
        SELECT sku_key, 线上宝贝名称, 货号, 品牌
        FROM products_master
        WHERE 品牌 = 'Canada Goose'
    """)

    products = cursor.fetchall()
    print(f"找到 {len(products)} 个Canada Goose产品")
    print("-" * 80)

    fixed_count = 0
    unchanged_count = 0
    fix_details = []

    for sku_key, product_name, old_code, brand in products:
        # 使用新逻辑提取货号
        new_code = extractor.extract_product_code(product_name, brand)

        if new_code and new_code != old_code:
            # 更新数据库
            cursor.execute("""
                UPDATE products_master
                SET 货号 = ?
                WHERE sku_key = ?
            """, (new_code, sku_key))

            fixed_count += 1
            fix_details.append({
                'product': product_name[:60] + '...' if len(product_name) > 60 else product_name,
                'old_code': old_code,
                'new_code': new_code
            })
            print(f"✅ 修正: {old_code or '无'} → {new_code}")
            print(f"   产品: {product_name[:60]}...")
        elif new_code == old_code:
            unchanged_count += 1
        else:
            print(f"⚠️ 无法提取货号: {product_name[:60]}...")

    # 提交更改
    conn.commit()

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

    # 显示修正详情
    if fix_details:
        print("\n修正详情（前20个）：")
        print("-" * 80)
        for detail in fix_details[: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} 个产品被修正")

    # 同样更新procurement_orders表
    print("\n更新procurement_orders表...")
    cursor.execute("""
        SELECT id, 线上宝贝名称, 货号, 品牌
        FROM procurement_orders
        WHERE 品牌 = 'Canada Goose'
    """)

    po_records = cursor.fetchall()
    po_fixed = 0

    for po_id, product_name, old_code, brand in po_records:
        new_code = extractor.extract_product_code(product_name, 'Canada Goose')
        if new_code and new_code != old_code:
            cursor.execute("""
                UPDATE procurement_orders
                SET 货号 = ?
                WHERE id = ?
            """, (new_code, po_id))
            po_fixed += 1

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

    # 验证结果
    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:
        cursor.execute("""
            SELECT COUNT(*) FROM products_master
            WHERE 品牌 = 'Canada Goose' AND 货号 = ?
        """, (pattern,))
        count = cursor.fetchone()[0]

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

    # 显示一些正确的货号示例
    cursor.execute("""
        SELECT DISTINCT 货号 FROM products_master
        WHERE 品牌 = 'Canada Goose' AND 货号 IS NOT NULL
        LIMIT 20
    """)
    codes = [row[0] for row in cursor.fetchall()]

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

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

    return fixed_count, unchanged_count


if __name__ == "__main__":
    fix_canada_goose_codes()