#!/usr/bin/env python
"""
诊断脚本：检查特定订单在各个表中的同步状态
"""

import asyncio
import sys
import os
from datetime import datetime
from sqlalchemy import select, and_
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

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

from app.models.raw_orders import RawOrder
from app.models.products_master import ProductMaster, ProductSourceMapping

async def check_order_sync(order_number: str):
    """检查订单同步状态"""
    
    # 直接使用数据库文件路径
    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(f"\n{'='*60}")
        print(f"检查订单同步状态: {order_number}")
        print(f"{'='*60}\n")
        
        # 1. 检查原始订单表
        print("1. 检查 raw_orders 表...")
        result = await db.execute(
            select(RawOrder).where(RawOrder.原始订单编号 == order_number)
        )
        raw_order = result.scalar_one_or_none()
        
        if not raw_order:
            print(f"   ❌ 订单 {order_number} 在 raw_orders 表中不存在")
            return
        
        print(f"   ✅ 找到订单:")
        print(f"      - ID: {raw_order.id}")
        print(f"      - 商品名称: {raw_order.线上宝贝名称}")
        print(f"      - 销售属性: {raw_order.线上销售属性}")
        print(f"      - 单价: {raw_order.订单单价}")
        print(f"      - 数量: {raw_order.订单总数量}")
        print(f"      - 导入时间: {raw_order.created_at}")
        
        # 2. 检查产品源映射表
        print("\n2. 检查 product_source_mappings 表...")
        result = await db.execute(
            select(ProductSourceMapping).where(ProductSourceMapping.raw_order_id == raw_order.id)
        )
        mapping = result.scalar_one_or_none()
        
        if not mapping:
            print(f"   ❌ 该订单没有产品映射记录")
            print(f"   ⚠️  这说明订单还未被处理生成产品主表")
        else:
            print(f"   ✅ 找到映射记录:")
            print(f"      - 产品ID: {mapping.product_id}")
            print(f"      - 创建时间: {mapping.created_at}")
            
            # 3. 检查产品主表
            print("\n3. 检查 products_master 表...")
            result = await db.execute(
                select(ProductMaster).where(ProductMaster.id == mapping.product_id)
            )
            product = result.scalar_one_or_none()
            
            if not product:
                print(f"   ❌ 产品主表中没有找到对应产品 (ID: {mapping.product_id})")
                print(f"   ⚠️  数据不一致：映射存在但产品不存在！")
            else:
                print(f"   ✅ 找到产品记录:")
                print(f"      - SKU Key: {product.sku_key}")
                print(f"      - 品牌: {product.品牌}")
                print(f"      - 货号: {product.货号}")
                print(f"      - 颜色: {product.颜色}")
                print(f"      - 尺寸: {product.尺寸}")
                print(f"      - 采购方式: {product.procurement_method}")
                print(f"      - 创建时间: {product.created_at}")
        
        # 4. 分析问题原因
        print("\n" + "="*60)
        print("问题分析:")
        print("="*60)
        
        if not mapping:
            print("\n⚠️  问题原因：订单已导入但未生成产品主表")
            print("可能的原因：")
            print("1. 自动同步任务未运行或失败")
            print("2. 订单数据不满足生成条件（缺少必要字段）")
            print("3. 处理过程中发生错误")
            
            # 检查是否满足生成条件
            issues = []
            if not raw_order.线上宝贝名称:
                issues.append("缺少商品名称")
            if raw_order.订单单价 is None:
                issues.append("缺少单价")
            if '合计' in str(raw_order.原始订单编号) or '汇总' in str(raw_order.原始订单编号):
                issues.append("订单号包含'合计'或'汇总'关键字")
            
            if issues:
                print("\n发现以下问题：")
                for issue in issues:
                    print(f"  - {issue}")
            else:
                print("\n订单数据满足生成条件，应该可以生成产品主表")
        
        elif mapping and not product:
            print("\n❗ 严重问题：数据不一致")
            print("映射记录存在但产品不存在，需要修复数据或重新生成")
        
        # 5. 建议解决方案
        print("\n" + "="*60)
        print("建议解决方案:")
        print("="*60)
        
        if not mapping:
            print("\n1. 手动触发产品主表生成:")
            print("   - 使用API: POST /api/v1/sync/trigger")
            print("   - 参数: {\"sync_type\": \"products_only\"}")
            print("\n2. 或运行命令:")
            print("   make import-now")
            print("\n3. 检查日志文件查看是否有错误:")
            print("   tail -f backend/logs/ordersys.log")
        
        elif mapping and not product:
            print("\n1. 强制重建产品主表:")
            print("   - 使用API: POST /api/v1/products-master/generate")
            print("   - 参数: {\"force_rebuild\": true}")
            print("\n2. 或删除孤立的映射记录后重新生成")

if __name__ == "__main__":
    import sys
    
    if len(sys.argv) < 2:
        print("用法: python check_order_sync.py <订单编号>")
        sys.exit(1)
    
    order_number = sys.argv[1]
    asyncio.run(check_order_sync(order_number))