#!/usr/bin/env python
"""
手动导入Excel文件并生成产品主表
"""

import asyncio
import sys
import os
from datetime import datetime

# 添加项目路径
sys.path.insert(0, os.path.join(os.path.dirname(__file__), 'backend'))

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from app.services.import_service import ImportService
from app.services.products_master_service import ProductsMasterService
from app.services.procurement_order_service_v2 import ProcurementOrderServiceV2

async def manual_import_and_sync():
    """手动导入并同步"""
    
    # 创建数据库连接
    database_url = "sqlite+aiosqlite:///./ordersys.db"
    engine = create_async_engine(database_url, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
    
    async with async_session() as db:
        print("\n" + "="*60)
        print("手动导入和同步流程")
        print("="*60 + "\n")
        
        try:
            # 步骤1: 导入Excel文件
            print("步骤 1/3: 导入Excel文件...")
            import_service = ImportService()
            
            # 扫描并导入所有待处理文件
            import_results = await import_service.import_all_pending_files(db)
            
            if import_results:
                for result in import_results:
                    print(f"  - 文件: {result.get('file_name')}")
                    print(f"    状态: {result.get('status')}")
                    print(f"    导入行数: {result.get('rows_imported', 0)}")
                    print(f"    失败行数: {result.get('rows_failed', 0)}")
                    if result.get('error_message'):
                        print(f"    错误: {result.get('error_message')}")
                
                total_imported = sum(r.get('rows_imported', 0) for r in import_results)
                print(f"\n  总计导入 {total_imported} 条记录")
                
                if total_imported > 0:
                    # 步骤2: 生成产品主表
                    print("\n步骤 2/3: 生成产品主表...")
                    await asyncio.sleep(2)  # 短暂延迟确保数据一致性
                    
                    products_service = ProductsMasterService()
                    products_result = await products_service.generate_products_master(
                        db,
                        force_rebuild=False,  # 增量生成
                        batch_size=1000
                    )
                    
                    print(f"  - 创建产品: {products_result.get('created', 0)}")
                    print(f"  - 更新产品: {products_result.get('updated', 0)}")
                    print(f"  - 失败: {products_result.get('failed', 0)}")
                    print(f"  - 总产品数: {products_result.get('products_count', 0)}")
                    
                    # 步骤3: 生成采购订单
                    print("\n步骤 3/3: 生成采购订单...")
                    procurement_service = ProcurementOrderServiceV2()
                    procurement_result = await procurement_service.generate_procurement_orders(
                        db=db,
                        force_rebuild=False,
                        batch_size=1000
                    )
                    
                    print(f"  - 创建采购订单: {procurement_result.get('created_orders', 0)}")
                    print(f"  - 更新采购订单: {procurement_result.get('updated_orders', 0)}")
                    
                    print("\n" + "="*60)
                    print("✅ 导入和同步完成！")
                    print("="*60)
                    
                    # 验证特定订单
                    await verify_specific_order(db, "2906687247880694950")
                else:
                    print("\n⚠️  没有新数据需要处理")
            else:
                print("⚠️  没有待处理的文件")
                
        except Exception as e:
            print(f"\n❌ 错误: {e}")
            import traceback
            traceback.print_exc()

async def verify_specific_order(db: AsyncSession, order_number: str):
    """验证特定订单是否存在"""
    from sqlalchemy import select
    from app.models.raw_orders import RawOrder
    from app.models.products_master import ProductMaster, ProductSourceMapping
    
    print(f"\n验证订单 {order_number}...")
    
    # 查询原始订单
    result = await db.execute(
        select(RawOrder).where(RawOrder.原始订单编号 == order_number)
    )
    raw_order = result.scalar_one_or_none()
    
    if raw_order:
        print(f"✅ 订单已导入:")
        print(f"   - 商品名称: {raw_order.线上宝贝名称}")
        print(f"   - 销售属性: {raw_order.线上销售属性}")
        
        # 查询产品映射
        result = await db.execute(
            select(ProductSourceMapping).where(ProductSourceMapping.raw_order_id == raw_order.id)
        )
        mapping = result.scalar_one_or_none()
        
        if mapping:
            # 查询产品主表
            result = await db.execute(
                select(ProductMaster).where(ProductMaster.id == mapping.product_id)
            )
            product = result.scalar_one_or_none()
            
            if product:
                print(f"✅ 产品主表已生成:")
                print(f"   - 品牌: {product.品牌}")
                print(f"   - 货号: {product.货号}")
                print(f"   - 采购方式: {product.procurement_method}")
            else:
                print("❌ 产品主表未生成")
        else:
            print("❌ 产品映射未创建")
    else:
        print(f"❌ 订单 {order_number} 未找到")

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