#!/usr/bin/env python3
"""
核心业务逻辑测试
专注于测试订单处理系统的关键功能
"""

import asyncio
import logging
from datetime import datetime
from typing import Dict, Any
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__)


class BusinessLogicTester:
    """业务逻辑测试器"""
    
    def __init__(self):
        self.results = []
        
    def log_test(self, name: str, passed: bool, message: str = ""):
        """记录测试结果"""
        icon = "✅" if passed else "❌"
        status = "PASS" if passed else "FAIL"
        logger.info(f"  {icon} {name}: {status} {message}")
        self.results.append({'test': name, 'passed': passed, 'message': message})
    
    async def test_order_import_and_dedup(self, db: AsyncSession):
        """测试1: 订单导入与去重"""
        logger.info("\n📋 测试1: 订单导入与去重")
        
        # 检查订单总数
        result = await db.execute(text("SELECT COUNT(*) FROM raw_orders"))
        total_orders = result.scalar()
        self.log_test("订单导入", total_orders > 0, f"共导入 {total_orders} 条订单")
        
        # 检查去重逻辑（基于5字段唯一约束）
        dup_check = await db.execute(text("""
            SELECT COUNT(*) as dup_count FROM (
                SELECT 原始订单编号, 线上宝贝名称, 线上销售属性, 数量, 付款时间, COUNT(*) as cnt
                FROM raw_orders
                GROUP BY 原始订单编号, 线上宝贝名称, 线上销售属性, 数量, 付款时间
                HAVING COUNT(*) > 1
            )
        """))
        dup_count = dup_check.scalar()
        self.log_test("订单去重", dup_count == 0, 
                     f"发现 {dup_count} 组重复" if dup_count > 0 else "无重复记录")
        
        # 检查更新模式（3个字段更新）
        update_fields_check = await db.execute(text("""
            SELECT COUNT(DISTINCT 交易状态) as trade_states,
                   COUNT(DISTINCT 退款状态) as refund_states
            FROM raw_orders
        """))
        states = update_fields_check.first()
        self.log_test("状态字段更新", states[0] > 1 or states[1] > 1,
                     f"交易状态{states[0]}种, 退款状态{states[1]}种")
    
    async def test_merged_order_detection(self, db: AsyncSession):
        """测试2: 合并订单识别与处理"""
        logger.info("\n📋 测试2: 合并订单识别与处理")
        
        # 检查合并订单标识
        merged_stats = await db.execute(text("""
            SELECT 
                SUM(CASE WHEN 是否合并订单 = 1 THEN 1 ELSE 0 END) as merged_count,
                SUM(CASE WHEN 是否合并订单 = 0 THEN 1 ELSE 0 END) as single_count,
                COUNT(DISTINCT CASE WHEN 是否合并订单 = 1 THEN 原始订单编号 END) as unique_merged
            FROM raw_orders
        """))
        stats = merged_stats.first()
        
        self.log_test("合并订单识别", stats[2] > 0,
                     f"识别 {stats[2]} 个合并订单（{stats[0]} 条记录）")
        self.log_test("单品订单识别", stats[1] > 0,
                     f"识别 {stats[1]} 个单品订单")
        
        # 检查商品小计计算
        subtotal_check = await db.execute(text("""
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN 商品小计 IS NOT NULL THEN 1 ELSE 0 END) as has_subtotal,
                SUM(CASE 
                    WHEN 商品小计 = CAST(数量 AS FLOAT) * CAST(订单单价 AS FLOAT) 
                    THEN 1 ELSE 0 
                END) as correct_calc
            FROM raw_orders
            WHERE 数量 IS NOT NULL AND 订单单价 IS NOT NULL
        """))
        calc_stats = subtotal_check.first()
        
        if calc_stats[0] > 0:
            accuracy = (calc_stats[2] / calc_stats[0]) * 100
            self.log_test("商品小计计算", accuracy >= 99,
                         f"计算准确率 {accuracy:.1f}%")
    
    async def test_products_master_generation(self, db: AsyncSession):
        """测试3: 产品主表生成"""
        logger.info("\n📋 测试3: 产品主表生成")
        
        # 检查产品主表
        master_check = await db.execute(text("""
            SELECT 
                COUNT(*) as total_products,
                COUNT(DISTINCT sku_key) as unique_skus,
                COUNT(DISTINCT 品牌) as brands,
                COUNT(DISTINCT procurement_method) as methods
            FROM products_master
        """))
        stats = master_check.first()
        
        self.log_test("产品主表生成", stats[0] > 0,
                     f"生成 {stats[0]} 个产品")
        self.log_test("SKU唯一性", stats[0] == stats[1],
                     f"SKU唯一性检查通过" if stats[0] == stats[1] else f"SKU重复")
        self.log_test("品牌提取", stats[2] > 0,
                     f"提取 {stats[2]} 个品牌")
        self.log_test("采购方式分配", stats[3] > 0,
                     f"分配 {stats[3]} 种采购方式")
    
    async def test_procurement_orders(self, db: AsyncSession):
        """测试4: 采购订单生成"""
        logger.info("\n📋 测试4: 采购订单生成")
        
        # 检查采购订单
        proc_check = await db.execute(text("""
            SELECT 
                COUNT(*) as total_orders,
                COUNT(DISTINCT procurement_status) as status_types,
                COUNT(DISTINCT procurement_method) as method_types,
                SUM(CASE WHEN original_order_id IS NULL THEN 1 ELSE 0 END) as orphan_orders
            FROM procurement_orders
        """))
        stats = proc_check.first()
        
        self.log_test("采购订单生成", stats[0] > 0,
                     f"生成 {stats[0]} 个采购订单")
        self.log_test("订单关联完整性", stats[3] == 0,
                     "所有采购订单都有原始订单" if stats[3] == 0 else f"{stats[3]} 个孤立订单")
        
        # 检查FIFO逻辑
        fifo_check = await db.execute(text("""
            SELECT COUNT(*) FROM procurement_orders p1
            WHERE EXISTS (
                SELECT 1 FROM procurement_orders p2
                WHERE p2.product_key = p1.product_key
                AND p2.付款时间 < p1.付款时间
                AND p2.procurement_status = 'PENDING'
                AND p1.procurement_status IN ('ORDERED', 'RECEIVED')
            )
        """))
        violations = fifo_check.scalar()
        self.log_test("FIFO逻辑", violations == 0,
                     "FIFO顺序正确" if violations == 0 else f"{violations} 个违规")
    
    async def test_status_sync(self, db: AsyncSession):
        """测试5: 状态同步机制"""
        logger.info("\n📋 测试5: 状态同步机制")
        
        # 检查状态同步
        sync_check = await db.execute(text("""
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN ro.交易状态 != po.交易状态 THEN 1 ELSE 0 END) as trade_diff,
                SUM(CASE WHEN ro.退款状态 != po.退款状态 THEN 1 ELSE 0 END) as refund_diff
            FROM procurement_orders po
            JOIN raw_orders ro ON po.original_order_id = ro.id
        """))
        stats = sync_check.first()
        
        if stats[0] > 0:
            sync_rate = ((stats[0] - stats[1] - stats[2]) / stats[0]) * 100
            self.log_test("状态同步", sync_rate == 100,
                         f"同步率 {sync_rate:.1f}%")
        
        # 检查已取消订单处理
        cancelled_check = await db.execute(text("""
            SELECT COUNT(*) FROM procurement_orders po
            JOIN raw_orders ro ON po.original_order_id = ro.id
            WHERE ro.交易状态 = '已取消' AND po.procurement_status = 'PENDING'
        """))
        cancelled_count = cancelled_check.scalar()
        self.log_test("已取消订单保留", True,
                     f"{cancelled_count} 个已取消订单保留在采购列表（符合需求）")
    
    async def test_data_integrity(self, db: AsyncSession):
        """测试6: 数据完整性"""
        logger.info("\n📋 测试6: 数据完整性")
        
        # 必需字段检查
        null_check = await db.execute(text("""
            SELECT 
                SUM(CASE WHEN 原始订单编号 IS NULL THEN 1 ELSE 0 END) as null_order_id,
                SUM(CASE WHEN 线上宝贝名称 IS NULL THEN 1 ELSE 0 END) as null_product,
                SUM(CASE WHEN 付款时间 IS NULL THEN 1 ELSE 0 END) as null_payment
            FROM raw_orders
        """))
        nulls = null_check.first()
        
        self.log_test("必需字段完整性", sum(nulls) == 0,
                     "所有必需字段完整" if sum(nulls) == 0 else f"缺失值: {nulls}")
        
        # 价格合理性
        price_check = await db.execute(text("""
            SELECT 
                SUM(CASE WHEN CAST(订单单价 AS FLOAT) < 0 THEN 1 ELSE 0 END) as negative,
                SUM(CASE WHEN CAST(订单单价 AS FLOAT) > 100000 THEN 1 ELSE 0 END) as excessive
            FROM raw_orders WHERE 订单单价 IS NOT NULL
        """))
        price_stats = price_check.first()
        
        self.log_test("价格合理性", sum(price_stats) == 0,
                     "所有价格合理" if sum(price_stats) == 0 else f"异常价格: {sum(price_stats)}")
        
        # 数量合理性
        qty_check = await db.execute(text("""
            SELECT 
                SUM(CASE WHEN 数量 <= 0 THEN 1 ELSE 0 END) as zero_qty,
                SUM(CASE WHEN 数量 > 1000 THEN 1 ELSE 0 END) as huge_qty
            FROM raw_orders WHERE 数量 IS NOT NULL
        """))
        qty_stats = qty_check.first()
        
        self.log_test("数量合理性", qty_stats[0] == 0,
                     "所有数量合理" if qty_stats[0] == 0 else f"异常数量: {sum(qty_stats)}")
    
    async def test_performance(self, db: AsyncSession):
        """测试7: 性能指标"""
        logger.info("\n📋 测试7: 性能指标")
        
        # 测试查询性能
        start = datetime.now()
        await db.execute(text("SELECT COUNT(*) FROM raw_orders WHERE 付款时间 >= date('now', '-30 days')"))
        query_time = (datetime.now() - start).total_seconds()
        
        self.log_test("查询性能", query_time < 0.5,
                     f"30天订单查询: {query_time:.3f}秒")
        
        # 检查索引
        index_check = await db.execute(text("""
            SELECT COUNT(*) FROM sqlite_master 
            WHERE type = 'index' AND tbl_name IN ('raw_orders', 'procurement_orders', 'products_master')
        """))
        index_count = index_check.scalar()
        
        self.log_test("索引配置", index_count >= 5,
                     f"配置了 {index_count} 个索引")
    
    def print_summary(self):
        """打印测试总结"""
        passed = sum(1 for r in self.results if r['passed'])
        failed = sum(1 for r in self.results if not r['passed'])
        total = len(self.results)
        
        logger.info("\n" + "=" * 60)
        logger.info("📊 测试总结")
        logger.info("=" * 60)
        logger.info(f"总测试数: {total}")
        logger.info(f"✅ 通过: {passed} ({passed/total*100:.1f}%)")
        logger.info(f"❌ 失败: {failed} ({failed/total*100:.1f}%)")
        
        if failed == 0:
            logger.info("\n🎉 所有核心业务逻辑测试通过！")
        elif passed/total >= 0.8:
            logger.info("\n✅ 大部分核心功能正常运行")
        else:
            logger.info("\n⚠️ 发现一些问题需要关注")
        
        logger.info("=" * 60)


async def main():
    """主测试函数"""
    logger.info("=" * 60)
    logger.info("🚀 核心业务逻辑测试")
    logger.info(f"时间: {datetime.now():%Y-%m-%d %H:%M:%S}")
    logger.info("=" * 60)
    
    tester = BusinessLogicTester()
    
    async for db in get_db():
        try:
            # 执行各项测试
            await tester.test_order_import_and_dedup(db)
            await tester.test_merged_order_detection(db)
            await tester.test_products_master_generation(db)
            await tester.test_procurement_orders(db)
            await tester.test_status_sync(db)
            await tester.test_data_integrity(db)
            await tester.test_performance(db)
            
        except Exception as e:
            logger.error(f"测试异常: {e}", exc_info=True)
        finally:
            await db.close()
            break
    
    # 打印总结
    tester.print_summary()


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