#!/usr/bin/env python3
"""
同步待采购订单表的颜色和尺寸数据
从标准化订单表同步最新的解析结果
"""

import asyncio
import sys
import os
from datetime import datetime

# 添加项目根目录到 Python 路径
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from app.core.database import get_db
from sqlalchemy import text


async def sync_procurement_orders():
    """同步待采购订单的颜色和尺寸数据"""
    print("=== 同步待采购订单颜色和尺寸数据 ===\n")
    
    async for db in get_db():
        try:
            # 1. 查找需要同步的订单
            print("1. 查找需要同步的待采购订单...")
            
            result = await db.execute(text("""
                SELECT po.id, po.original_order_id, po.线上销售属性, 
                       po.颜色 as proc_color, po.尺寸 as proc_size,
                       norm.颜色 as norm_color, norm.尺寸 as norm_size
                FROM procurement_orders po
                JOIN order_items_norm norm ON po.original_order_id = norm.id
                WHERE po.颜色 != norm.颜色 OR po.尺寸 != norm.尺寸
                ORDER BY po.id
            """))
            
            inconsistent_orders = result.fetchall()
            print(f"   找到 {len(inconsistent_orders)} 个需要同步的订单")
            
            if not inconsistent_orders:
                print("   所有订单已同步，无需处理")
                return
            
            # 2. 开始同步
            print(f"\n2. 开始同步数据...")
            updated_count = 0
            
            for i, order in enumerate(inconsistent_orders):
                proc_id = order.id
                original_id = order.original_order_id
                sales_attr = order.线上销售属性
                proc_color = order.proc_color
                proc_size = order.proc_size
                norm_color = order.norm_color
                norm_size = order.norm_size
                
                # 输出前20个更新详情
                if updated_count < 20:
                    print(f"   {updated_count+1:2d}. ID {proc_id}: {sales_attr[:30]}")
                    if proc_color != norm_color:
                        print(f"       颜色: {proc_color} -> {norm_color}")
                    if proc_size != norm_size:
                        print(f"       尺寸: {proc_size} -> {norm_size}")
                elif updated_count == 20:
                    print("   ... (更多更新记录，不再显示)")
                
                # 执行更新
                await db.execute(text("""
                    UPDATE procurement_orders 
                    SET 颜色 = :norm_color, 尺寸 = :norm_size, 更新时间 = :now
                    WHERE id = :proc_id
                """), {
                    'norm_color': norm_color,
                    'norm_size': norm_size,
                    'proc_id': proc_id,
                    'now': datetime.now()
                })
                
                updated_count += 1
                
                # 每100条提交一次
                if updated_count % 100 == 0:
                    await db.commit()
                    print(f"   已同步 {updated_count}/{len(inconsistent_orders)} 条记录")
            
            # 最终提交
            await db.commit()
            
            print(f"\n3. 同步完成")
            print(f"   总处理记录数: {len(inconsistent_orders)}")
            print(f"   成功更新记录数: {updated_count}")
            
            # 4. 验证同步结果
            print(f"\n4. 验证同步结果...")
            
            # 检查关键问题订单
            verify_result = await db.execute(text("""
                SELECT po.id, po.original_order_id, po.线上销售属性, po.颜色, po.尺寸,
                       norm.原始订单编号
                FROM procurement_orders po
                JOIN order_items_norm norm ON po.original_order_id = norm.id
                WHERE norm.原始订单编号 IN ('4693389768932983037', '2886548559516973465')
            """))
            
            print("   关键问题订单同步结果:")
            for row in verify_result.fetchall():
                print(f"   {row.原始订单编号}: {row.线上销售属性} -> 颜色:{row.颜色}, 尺寸:{row.尺寸}")
            
            # 检查是否还有不一致的记录
            final_check = await db.execute(text("""
                SELECT COUNT(*) as count
                FROM procurement_orders po
                JOIN order_items_norm norm ON po.original_order_id = norm.id
                WHERE po.颜色 != norm.颜色 OR po.尺寸 != norm.尺寸
            """))
            
            remaining_inconsistent = final_check.scalar()
            print(f"\n   同步后剩余不一致记录: {remaining_inconsistent} 条")
            
            if remaining_inconsistent == 0:
                print("   ✓ 所有待采购订单已与标准化订单完全同步")
            else:
                print(f"   ⚠ 还有 {remaining_inconsistent} 条记录需要进一步处理")
            
        except Exception as e:
            await db.rollback()
            print(f"同步过程中发生错误: {e}")
            import traceback
            traceback.print_exc()
        
        break


async def preview_sync():
    """预览将要同步的数据"""
    print("=== 预览待采购订单同步 ===\n")
    
    async for db in get_db():
        try:
            result = await db.execute(text("""
                SELECT po.id, po.original_order_id, po.线上销售属性, 
                       po.颜色 as proc_color, po.尺寸 as proc_size,
                       norm.颜色 as norm_color, norm.尺寸 as norm_size
                FROM procurement_orders po
                JOIN order_items_norm norm ON po.original_order_id = norm.id
                WHERE po.颜色 != norm.颜色 OR po.尺寸 != norm.尺寸
                ORDER BY po.id
                LIMIT 30
            """))
            
            inconsistent_orders = result.fetchall()
            
            print("需要同步的订单预览（前30条）:")
            print("序号 | 销售属性 | 当前颜色 | 新颜色 | 当前尺寸 | 新尺寸")
            print("-" * 100)
            
            for i, order in enumerate(inconsistent_orders, 1):
                sales_attr = order.线上销售属性[:20]
                proc_color = order.proc_color[:15]
                norm_color = order.norm_color[:15]
                proc_size = str(order.proc_size)[:8]
                norm_size = str(order.norm_size)[:8]
                
                print(f"{i:2d}   | {sales_attr:20} | {proc_color:15} | {norm_color:15} | {proc_size:8} | {norm_size:8}")
            
            # 统计总数
            count_result = await db.execute(text("""
                SELECT COUNT(*) as total
                FROM procurement_orders po
                JOIN order_items_norm norm ON po.original_order_id = norm.id
                WHERE po.颜色 != norm.颜色 OR po.尺寸 != norm.尺寸
            """))
            
            total_inconsistent = count_result.scalar()
            
            print(f"\n预览结果:")
            print(f"  需要同步的总记录数: {total_inconsistent}")
            print(f"  显示的预览记录数: {min(30, len(inconsistent_orders))}")
            
        except Exception as e:
            print(f"预览过程中发生错误: {e}")
        
        break


async def main():
    """主函数"""
    print("待采购订单同步工具")
    print("=" * 60)
    
    if len(sys.argv) > 1 and sys.argv[1] == '--sync':
        # 执行同步
        print("开始执行同步...")
        await sync_procurement_orders()
    else:
        # 默认预览模式
        print("使用说明:")
        print("  python sync_procurement_orders.py          # 预览同步")
        print("  python sync_procurement_orders.py --sync   # 执行同步")
        print("")
        await preview_sync()


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