#!/usr/bin/env python3
"""
测试最近的修复
包括：更新逻辑、合并订单处理、价格计算修复
"""

import asyncio
import logging
from datetime import datetime
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.database import get_db

logging.basicConfig(level=logging.INFO, format='%(message)s')
logger = logging.getLogger(__name__)


async def test_recent_fixes(db: AsyncSession):
    """测试最近实施的修复"""
    
    logger.info("=" * 60)
    logger.info("🔧 测试最近的修复")
    logger.info(f"时间: {datetime.now():%Y-%m-%d %H:%M:%S}")
    logger.info("=" * 60)
    
    passed = 0
    failed = 0
    
    # 1. 测试更新逻辑（只更新3个字段）
    logger.info("\n📋 测试1: 更新逻辑验证")
    try:
        # 检查是否有不同的交易状态和退款状态
        result = await db.execute(text("""
            SELECT 
                COUNT(DISTINCT 交易状态) as trade_states,
                COUNT(DISTINCT 退款状态) as refund_states,
                COUNT(DISTINCT 卖家备注) as seller_notes
            FROM raw_orders
        """))
        stats = result.first()
        
        if stats[0] > 1:
            logger.info(f"  ✅ 交易状态更新: 发现 {stats[0]} 种不同状态")
            passed += 1
        else:
            logger.info(f"  ⚠️ 交易状态更新: 只有 {stats[0]} 种状态")
        
        if stats[1] > 1:
            logger.info(f"  ✅ 退款状态更新: 发现 {stats[1]} 种不同状态")
            passed += 1
        else:
            logger.info(f"  ⚠️ 退款状态更新: 只有 {stats[1]} 种状态")
        
        logger.info(f"  ℹ️ 卖家备注: {stats[2]} 种不同备注")
        
    except Exception as e:
        logger.error(f"  ❌ 更新逻辑测试失败: {e}")
        failed += 1
    
    # 2. 测试合并订单处理
    logger.info("\n📋 测试2: 合并订单处理")
    try:
        # 检查合并订单标识和商品小计
        result = await db.execute(text("""
            SELECT 
                COUNT(*) as total_orders,
                SUM(CASE WHEN 是否合并订单 IS NOT NULL THEN 1 ELSE 0 END) as has_flag,
                SUM(CASE WHEN 是否合并订单 = 1 THEN 1 ELSE 0 END) as merged,
                SUM(CASE WHEN 是否合并订单 = 0 THEN 1 ELSE 0 END) as single,
                SUM(CASE WHEN 商品小计 IS NOT NULL THEN 1 ELSE 0 END) as has_subtotal
            FROM raw_orders
        """))
        stats = result.first()
        
        if stats[1] == stats[0]:
            logger.info(f"  ✅ 合并订单标识: 所有 {stats[0]} 条记录都有标识")
            passed += 1
        else:
            logger.info(f"  ❌ 合并订单标识: {stats[0] - stats[1]} 条记录缺少标识")
            failed += 1
        
        logger.info(f"  ℹ️ 合并订单: {stats[2]} 条")
        logger.info(f"  ℹ️ 单品订单: {stats[3]} 条")
        
        coverage = (stats[4] / stats[0]) * 100 if stats[0] > 0 else 0
        if coverage > 95:
            logger.info(f"  ✅ 商品小计: {coverage:.1f}% 覆盖率")
            passed += 1
        else:
            logger.info(f"  ⚠️ 商品小计: {coverage:.1f}% 覆盖率")
        
    except Exception as e:
        logger.error(f"  ❌ 合并订单测试失败: {e}")
        failed += 1
    
    # 3. 测试505个价格计算错误的修复
    logger.info("\n📋 测试3: 价格计算错误修复")
    try:
        # 原始问题：数量×单价≠订单金额
        # 修复后：单品订单应该数量×单价=商品小计，合并订单商品小计独立计算
        
        # 检查单品订单的价格计算
        result = await db.execute(text("""
            SELECT COUNT(*) as error_count
            FROM raw_orders
            WHERE 是否合并订单 = 0
            AND 数量 IS NOT NULL 
            AND 订单单价 IS NOT NULL
            AND 商品小计 IS NOT NULL
            AND ABS(商品小计 - (CAST(数量 AS FLOAT) * CAST(订单单价 AS FLOAT))) > 0.01
        """))
        errors = result.scalar()
        
        if errors == 0:
            logger.info(f"  ✅ 单品订单价格计算: 全部正确")
            passed += 1
        else:
            logger.info(f"  ❌ 单品订单价格计算: {errors} 个错误")
            failed += 1
        
        # 检查合并订单的处理
        result = await db.execute(text("""
            WITH merged_orders AS (
                SELECT 原始订单编号, COUNT(*) as item_count
                FROM raw_orders
                WHERE 是否合并订单 = 1
                GROUP BY 原始订单编号
            )
            SELECT COUNT(DISTINCT 原始订单编号) as merged_count,
                   AVG(item_count) as avg_items
            FROM merged_orders
        """))
        merged_stats = result.first()
        
        if merged_stats[0] > 0:
            logger.info(f"  ✅ 合并订单识别: {merged_stats[0]} 个订单，平均 {merged_stats[1]:.1f} 个商品")
            passed += 1
        else:
            logger.info(f"  ⚠️ 未发现合并订单")
        
    except Exception as e:
        logger.error(f"  ❌ 价格计算测试失败: {e}")
        failed += 1
    
    # 4. 测试采购订单状态同步
    logger.info("\n📋 测试4: 采购订单状态同步")
    try:
        # 检查原始订单和采购订单的状态一致性
        result = await db.execute(text("""
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN ro.交易状态 = po.交易状态 THEN 1 ELSE 0 END) as trade_match,
                SUM(CASE WHEN ro.退款状态 = po.退款状态 THEN 1 ELSE 0 END) as refund_match
            FROM procurement_orders po
            JOIN raw_orders ro ON po.original_order_id = ro.id
        """))
        sync_stats = result.first()
        
        if sync_stats[0] > 0:
            trade_sync_rate = (sync_stats[1] / sync_stats[0]) * 100
            refund_sync_rate = (sync_stats[2] / sync_stats[0]) * 100
            
            if trade_sync_rate == 100:
                logger.info(f"  ✅ 交易状态同步: 100% 一致")
                passed += 1
            else:
                logger.info(f"  ⚠️ 交易状态同步: {trade_sync_rate:.1f}% 一致")
            
            if refund_sync_rate == 100:
                logger.info(f"  ✅ 退款状态同步: 100% 一致")
                passed += 1
            else:
                logger.info(f"  ⚠️ 退款状态同步: {refund_sync_rate:.1f}% 一致")
        
        # 检查已取消订单是否被保留
        result = await db.execute(text("""
            SELECT COUNT(*) as cancelled_retained
            FROM procurement_orders po
            JOIN raw_orders ro ON po.original_order_id = ro.id
            WHERE (ro.交易状态 = '已取消' OR ro.退款状态 = '退款成功')
            AND po.procurement_status = 'PENDING'
        """))
        cancelled = result.scalar()
        
        logger.info(f"  ℹ️ 已取消/退款订单保留: {cancelled} 个（符合需求）")
        passed += 1
        
    except Exception as e:
        logger.error(f"  ❌ 状态同步测试失败: {e}")
        failed += 1
    
    # 5. 测试数据完整性
    logger.info("\n📋 测试5: 数据完整性验证")
    try:
        # 检查新字段是否已添加
        result = await db.execute(text("""
            SELECT sql FROM sqlite_master 
            WHERE type='table' AND name='raw_orders'
        """))
        schema = result.scalar()
        
        if '商品小计' in schema:
            logger.info(f"  ✅ 商品小计字段: 已添加")
            passed += 1
        else:
            logger.info(f"  ❌ 商品小计字段: 未找到")
            failed += 1
        
        if '是否合并订单' in schema:
            logger.info(f"  ✅ 合并订单标识字段: 已添加")
            passed += 1
        else:
            logger.info(f"  ❌ 合并订单标识字段: 未找到")
            failed += 1
        
    except Exception as e:
        logger.error(f"  ❌ 数据完整性测试失败: {e}")
        failed += 1
    
    # 总结
    total = passed + failed
    logger.info("\n" + "=" * 60)
    logger.info("📊 测试总结")
    logger.info("=" * 60)
    logger.info(f"总测试项: {total}")
    logger.info(f"✅ 通过: {passed} ({passed/total*100:.1f}%)" if total > 0 else "✅ 通过: 0")
    logger.info(f"❌ 失败: {failed} ({failed/total*100:.1f}%)" if total > 0 else "❌ 失败: 0")
    
    if failed == 0 and passed > 0:
        logger.info("\n🎉 所有最近的修复都正常工作！")
    elif passed > failed:
        logger.info("\n✅ 大部分修复正常工作")
    else:
        logger.info("\n⚠️ 发现一些问题需要检查")
    
    logger.info("=" * 60)


async def main():
    """主函数"""
    async for db in get_db():
        try:
            await test_recent_fixes(db)
        except Exception as e:
            logger.error(f"测试失败: {e}", exc_info=True)
        finally:
            await db.close()
            break


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