#!/usr/bin/env python3
"""
采购方式判断逻辑验证测试脚本
测试最近1000个订单，输出控制台摘要和详细Excel报告

运行方式:
    cd ordersys/backend
    python test_procurement_method_validation.py
"""

import asyncio
import sys
import re
from pathlib import Path
from datetime import datetime
from typing import Dict, List, Tuple, Any, Optional
from dataclasses import dataclass, field
from collections import Counter

# 添加项目路径
sys.path.insert(0, str(Path(__file__).parent))

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


@dataclass
class ValidationResult:
    """单条记录的验证结果"""
    raw_order_id: int
    product_name: str
    sales_attributes: str
    brand: str
    db_method: str                    # 数据库中的采购方式
    recalc_method: str                # 重新计算的采购方式
    is_match: bool                    # 是否一致
    rule_triggered: str               # 触发的规则
    suspicion_level: str              # 可疑程度: "正常", "轻微", "严重"
    suspicion_reason: str             # 可疑原因


@dataclass
class ValidationSummary:
    """验证汇总"""
    total_count: int = 0
    match_count: int = 0
    mismatch_count: int = 0
    suspicious_count: int = 0
    method_distribution: Dict[str, int] = field(default_factory=dict)
    mismatch_details: List[ValidationResult] = field(default_factory=list)
    suspicious_details: List[ValidationResult] = field(default_factory=list)
    at_ap_analysis: Dict[str, Any] = field(default_factory=dict)


class ProcurementMethodValidator:
    """采购方式判断逻辑验证器"""

    def __init__(self):
        self.results: List[ValidationResult] = []
        self.summary = ValidationSummary()

        # AT/AP 可能误判的词汇模式
        self.at_false_positive_patterns = [
            'FLAT', 'BEAT', 'SEAT', 'HEAT', 'NEAT',
            'TREAT', 'GREAT', 'PLEAT', 'SWEAT', 'MEAT'
        ]

        self.ap_false_positive_patterns = [
            'CAP', 'MAP', 'TAP', 'WRAP', 'SNAP',
            'STRAP', 'TRAP', 'CLAP', 'FLAP', 'SLAP'
        ]

    # ================== 采购方式判断逻辑完整复现 ==================

    def _determine_procurement_method(self, product_name: str,
                                       sales_attributes: str = "",
                                       brand: str = "") -> Tuple[str, str]:
        """
        复现采购方式判断逻辑 (来自 products_master_service.py:342-483)
        返回: (采购方式, 触发的规则描述)
        """
        product_name = product_name.strip() if product_name else ""
        sales_attributes = sales_attributes.strip() if sales_attributes else ""
        brand = brand.strip() if brand else ""

        product_name_upper = product_name.upper()
        sales_attributes_upper = sales_attributes.upper()
        brand_upper = brand.upper()

        # 规则1: MC库存 - 最高优先级
        if (product_name_upper.endswith(' MC') or
            product_name_upper.endswith('MC') or
            product_name_upper.endswith(' ZT') or
            product_name_upper.endswith('ZT') or
            'MC' in sales_attributes_upper or
            'ZT' in sales_attributes_upper):
            return "MC", "规则1: MC/ZT后缀或属性含MC/ZT"

        # 规则2: SF旧金山
        if self._has_sf_suffix(product_name_upper):
            if self._has_independent_la(sales_attributes_upper):
                return "LA", "规则2: SF后缀但属性含独立LA"
            elif self._has_independent_ny(sales_attributes_upper):
                return "NY", "规则2: SF后缀但属性含独立NY"
            elif 'MC' in sales_attributes_upper or 'ZT' in sales_attributes_upper:
                return "MC", "规则2: SF后缀但属性含MC/ZT"
            else:
                return "SF", "规则2: SF/SFM/SF M后缀"

        # 规则3: FL佛罗里达
        if self._has_fl_suffix(product_name_upper):
            if self._has_independent_la(sales_attributes_upper):
                return "LA", "规则3: FL后缀但属性含独立LA"
            elif self._has_independent_ny(sales_attributes_upper):
                return "NY", "规则3: FL后缀但属性含独立NY"
            elif 'MC' in sales_attributes_upper or 'ZT' in sales_attributes_upper:
                return "MC", "规则3: FL后缀但属性含MC/ZT"
            else:
                return "FL", "规则3: FL/FLM/FL M后缀"

        # 规则3.5: CA加拿大
        if self._has_ca_suffix(product_name_upper):
            if self._has_independent_la(sales_attributes_upper):
                return "LA", "规则3.5: CA后缀但属性含独立LA"
            elif self._has_independent_ny(sales_attributes_upper):
                return "NY", "规则3.5: CA后缀但属性含独立NY"
            elif 'MC' in sales_attributes_upper or 'ZT' in sales_attributes_upper:
                return "MC", "规则3.5: CA后缀但属性含MC/ZT"
            else:
                return "CA", "规则3.5: CA/CAM/CA M后缀"

        # 规则4: NY属性优先
        if self._has_independent_ny(sales_attributes_upper):
            return "NY", "规则4: 属性含独立NY标记"

        # 规则4.5: CA属性
        if self._has_independent_ca(sales_attributes_upper):
            return "CA", "规则4.5: 属性含独立CA标记"

        # 规则5: AT/ATM (重点检查区域)
        if (self._has_at_suffix(product_name_upper) and
            'MC' not in sales_attributes_upper and
            'ZT' not in sales_attributes_upper):
            return "AT", "规则5: AT/ATM后缀且属性无MC/ZT"

        # 规则6: AP/APM
        if ((product_name_upper.endswith(' AP') or
             product_name_upper.endswith('AP') or
             product_name_upper.endswith(' APM') or
             product_name_upper.endswith('APM')) and
            'MC' not in sales_attributes_upper and
            'ZT' not in sales_attributes_upper):
            return "AP", "规则6: AP/APM后缀且属性无MC/ZT"

        # 规则7: LA/LAM
        if (product_name_upper.endswith(' LA') or
            product_name_upper.endswith('LA') or
            product_name_upper.endswith(' LAM') or
            product_name_upper.endswith('LAM')):
            return "LA", "规则7: LA/LAM后缀"

        # 规则8: SS/SSM
        if ((product_name_upper.endswith(' SS') or
             product_name_upper.endswith('SS') or
             product_name_upper.endswith(' SSM') or
             product_name_upper.endswith('SSM')) and
            'MC' not in sales_attributes_upper and
            'ZT' not in sales_attributes_upper):
            return "SS", "规则8: SS/SSM后缀且属性无MC/ZT"

        # 检查是否有已处理后缀（用于后续属性判断）
        suffixes_to_exclude = ['LA', 'MC', 'ZT', 'LAM', 'SS', 'SSM', 'AT', 'ATM',
                               'AP', 'APM', 'SF', 'SFM', 'FL', 'FLM', 'CA', 'CAM']
        has_excluded_suffix = any(
            product_name_upper.endswith(f' {suffix}') or
            product_name_upper.endswith(suffix)
            for suffix in suffixes_to_exclude
        )

        # 规则9: 无特定后缀 + 独立LA属性
        if not has_excluded_suffix and self._has_independent_la(sales_attributes_upper):
            return "LA", "规则9: 无特定后缀+属性含独立LA"

        # 规则10: 无特定后缀 + 独立SF属性
        if not has_excluded_suffix and self._has_independent_sf(sales_attributes_upper):
            return "SF", "规则10: 无特定后缀+属性含独立SF"

        # 规则11: 无特定后缀 + 独立FL属性
        if not has_excluded_suffix and self._has_independent_fl(sales_attributes_upper):
            return "FL", "规则11: 无特定后缀+属性含独立FL"

        # 规则12: 无特定后缀 + 独立CA属性
        if not has_excluded_suffix and self._has_independent_ca(sales_attributes_upper):
            return "CA", "规则12: 无特定后缀+属性含独立CA"

        # 规则13: 国内现货
        if (self._has_stock_keywords(product_name) or
            self._is_domestic_brand(brand_upper)):
            return "GN", "规则13: 现货关键词或国内品牌"

        # 规则14: 默认NY
        return "NY", "规则14: 默认NY"

    # ================== 辅助判断函数 ==================

    def _has_sf_suffix(self, text_upper: str) -> bool:
        """检查SF相关后缀"""
        return (text_upper.endswith(' SF') or text_upper.endswith('SF') or
                text_upper.endswith(' SFM') or text_upper.endswith('SFM') or
                text_upper.endswith(' SF M') or text_upper.endswith('SF M'))

    def _has_fl_suffix(self, text_upper: str) -> bool:
        """检查FL相关后缀"""
        return (text_upper.endswith(' FL') or text_upper.endswith('FL') or
                text_upper.endswith(' FLM') or text_upper.endswith('FLM') or
                text_upper.endswith(' FL M') or text_upper.endswith('FL M'))

    def _has_ca_suffix(self, text_upper: str) -> bool:
        """检查CA相关后缀"""
        return (text_upper.endswith(' CA') or text_upper.endswith('CA') or
                text_upper.endswith(' CAM') or text_upper.endswith('CAM') or
                text_upper.endswith(' CA M') or text_upper.endswith('CA M'))

    def _has_at_suffix(self, text_upper: str) -> bool:
        """
        关键函数: AT后缀检查
        需要避免 FLAT, BEAT, SEAT 等词误判
        """
        # 基础后缀检查（有空格分隔，确定是AT后缀）
        if (text_upper.endswith(' AT') or text_upper.endswith(' ATM') or
            text_upper.endswith(' AT M') or text_upper.endswith('AT M')):
            return True

        # 检查纯AT/ATM结尾（需确保前面是空格或中文字符）
        if text_upper.endswith('AT') or text_upper.endswith('ATM'):
            suffix_len = 3 if text_upper.endswith('ATM') else 2
            if len(text_upper) > suffix_len:
                char_before = text_upper[-(suffix_len + 1)]
                # 前面是空格或中文字符（Unicode > 127）才认为是AT后缀
                if char_before == ' ' or ord(char_before) > 127:
                    return True
                # 新增: 检查 日期+AT 模式（如 1.3AT, 12.20AT）
                # 前面是数字时，检查是否为日期格式
                if char_before.isdigit():
                    # 匹配模式: 空格或中文 + 数字.数字 + AT/ATM
                    pattern_date_before = r'(?:^|[\s\u4e00-\u9fff])\d{1,2}\.\d{1,2}ATM?$'
                    if re.search(pattern_date_before, text_upper):
                        return True
            elif len(text_upper) == suffix_len:
                # 整个字符串就是AT或ATM
                return True

        # 检查 ATM/AT + 日期模式（如 ATM11.27, AT 11.27）
        pattern = r'(?:^|[\s\u4e00-\u9fff])ATM?\s*\d{1,2}\.\d{1,2}$'
        if re.search(pattern, text_upper):
            return True

        return False

    def _has_independent_la(self, text_upper: str) -> bool:
        """检查独立LA标记（LA后面必须是分隔符）"""
        pattern = r'LA(?=_|\s|$|\d|\[)'
        return bool(re.search(pattern, text_upper))

    def _has_independent_ny(self, text_upper: str) -> bool:
        """检查独立NY标记（避免匹配NY91等编码或NYLON等词）"""
        pattern = r'NY(?=_|\s|$|\[)'
        return bool(re.search(pattern, text_upper))

    def _has_independent_sf(self, text_upper: str) -> bool:
        """检查独立SF标记"""
        pattern = r'SF(?=_|\s|$|\[)'
        return bool(re.search(pattern, text_upper))

    def _has_independent_fl(self, text_upper: str) -> bool:
        """检查独立FL标记"""
        pattern = r'FL(?=_|\s|$|\[)'
        return bool(re.search(pattern, text_upper))

    def _has_independent_ca(self, text_upper: str) -> bool:
        """检查独立CA标记（避免匹配CANADA等词）"""
        pattern = r'CA(?=_|\s|$|\[)'
        return bool(re.search(pattern, text_upper))

    def _has_stock_keywords(self, text: str) -> bool:
        """检查现货关键词"""
        return ('现货' in text or 'GN现货' in text or '差价' in text)

    def _is_domestic_brand(self, brand_upper: str) -> bool:
        """检查国内品牌"""
        domestic_brands = ['GRUNDZERO', 'JANSPORT', 'EMU']
        return any(b in brand_upper for b in domestic_brands)

    # ================== 可疑性检测 ==================

    def _detect_suspicion(self, product_name: str, sales_attr: str,
                          db_method: str, recalc_method: str) -> Tuple[str, str]:
        """
        检测可疑情况
        返回: (可疑程度, 可疑原因)
        """
        product_upper = product_name.upper()
        reasons = []
        level = "正常"

        # 1. 检查AT误判风险（包含FLAT等词但被判为AT）
        for pattern in self.at_false_positive_patterns:
            if pattern in product_upper:
                if db_method == 'AT' or recalc_method == 'AT':
                    reasons.append(f"商品名含'{pattern}'但被判为AT，可能误判")
                    level = "严重"

        # 2. 检查AP误判风险（包含CAP等词但被判为AP）
        for pattern in self.ap_false_positive_patterns:
            if pattern in product_upper:
                if db_method == 'AP' or recalc_method == 'AP':
                    reasons.append(f"商品名含'{pattern}'但被判为AP，可能误判")
                    level = "严重"

        # 3. 检查AT M结尾但未被判为AT（非MC属性情况下）
        if (' AT M' in product_upper or product_upper.endswith('AT M')):
            if db_method != 'AT' and recalc_method != 'AT':
                if 'MC' not in sales_attr.upper() and 'ZT' not in sales_attr.upper():
                    reasons.append("商品名含'AT M'后缀但未判为AT(非MC属性)")
                    level = "轻微" if level == "正常" else level

        # 4. 检查日期模式AT但未被判为AT
        if re.search(r'ATM?\s*\d{1,2}\.\d{1,2}$', product_upper):
            if db_method != 'AT' and recalc_method != 'AT':
                if 'MC' not in sales_attr.upper() and 'ZT' not in sales_attr.upper():
                    reasons.append("商品名含AT+日期后缀但未判为AT(非MC属性)")
                    level = "轻微" if level == "正常" else level

        # 5. 数据库与重算结果不一致
        if db_method != recalc_method:
            reasons.append(f"判断不一致: 数据库={db_method}, 重算={recalc_method}")
            level = "严重"

        return level, "; ".join(reasons) if reasons else ""

    # ================== 数据加载与验证 ==================

    async def load_and_validate(self, db, limit: int = 1000):
        """加载最近的订单数据并验证"""

        print(f"正在查询最近 {limit} 条订单记录...")

        # 查询最近订单并关联产品主表获取数据库中的采购方式
        query = text("""
            SELECT
                r.id as raw_order_id,
                r.线上宝贝名称 as product_name,
                r.线上销售属性 as sales_attributes,
                p.品牌 as brand,
                p.procurement_method as db_method
            FROM raw_orders r
            LEFT JOIN products_master p
                ON r.线上宝贝名称 = p.线上宝贝名称
                AND (r.线上销售属性 = p.线上销售属性
                     OR (r.线上销售属性 IS NULL AND p.线上销售属性 IS NULL))
            ORDER BY r.id DESC
            LIMIT :limit
        """)

        result = await db.execute(query, {"limit": limit})
        rows = result.fetchall()

        print(f"成功加载 {len(rows)} 条订单记录，开始验证...")

        for i, row in enumerate(rows):
            if (i + 1) % 200 == 0:
                print(f"  已验证 {i + 1}/{len(rows)} 条...")

            product_name = row.product_name or ""
            sales_attr = row.sales_attributes or ""
            brand = row.brand or ""
            db_method = row.db_method or "未关联"

            # 重新计算采购方式
            recalc_method, rule = self._determine_procurement_method(
                product_name, sales_attr, brand
            )

            # 检测可疑性
            suspicion_level, suspicion_reason = self._detect_suspicion(
                product_name, sales_attr, db_method, recalc_method
            )

            # 记录结果
            result_item = ValidationResult(
                raw_order_id=row.raw_order_id,
                product_name=product_name,
                sales_attributes=sales_attr,
                brand=brand,
                db_method=db_method,
                recalc_method=recalc_method,
                is_match=(db_method == recalc_method),
                rule_triggered=rule,
                suspicion_level=suspicion_level,
                suspicion_reason=suspicion_reason
            )

            self.results.append(result_item)

        print(f"验证完成，共处理 {len(self.results)} 条记录")
        self._calculate_summary()

    # ================== 统计汇总 ==================

    def _calculate_summary(self):
        """计算验证汇总"""
        self.summary.total_count = len(self.results)
        self.summary.match_count = sum(1 for r in self.results if r.is_match)
        self.summary.mismatch_count = sum(1 for r in self.results if not r.is_match)
        self.summary.suspicious_count = sum(
            1 for r in self.results if r.suspicion_level != "正常"
        )

        # 采购方式分布（按数据库中的方式统计）
        self.summary.method_distribution = dict(Counter(r.db_method for r in self.results))

        # 不一致记录详情
        self.summary.mismatch_details = [r for r in self.results if not r.is_match]

        # 可疑记录详情
        self.summary.suspicious_details = [
            r for r in self.results if r.suspicion_level != "正常"
        ]

        # AT/AP专项分析
        at_records = [r for r in self.results
                      if r.db_method == 'AT' or r.recalc_method == 'AT']
        ap_records = [r for r in self.results
                      if r.db_method == 'AP' or r.recalc_method == 'AP']

        self.summary.at_ap_analysis = {
            'at_total': len(at_records),
            'at_match': sum(1 for r in at_records if r.is_match),
            'at_mismatch': sum(1 for r in at_records if not r.is_match),
            'at_suspicious': sum(1 for r in at_records if r.suspicion_level != "正常"),
            'ap_total': len(ap_records),
            'ap_match': sum(1 for r in ap_records if r.is_match),
            'ap_mismatch': sum(1 for r in ap_records if not r.is_match),
            'ap_suspicious': sum(1 for r in ap_records if r.suspicion_level != "正常"),
        }

    # ================== 控制台输出 ==================

    def print_console_summary(self):
        """输出控制台摘要"""
        print("\n" + "=" * 70)
        print("           采购方式判断逻辑验证报告")
        print("=" * 70)
        print(f"验证时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"验证订单数: {self.summary.total_count}")
        print()

        # 一致性统计
        print("【一致性统计】")
        if self.summary.total_count > 0:
            match_pct = self.summary.match_count / self.summary.total_count * 100
            mismatch_pct = self.summary.mismatch_count / self.summary.total_count * 100
            suspicious_pct = self.summary.suspicious_count / self.summary.total_count * 100
        else:
            match_pct = mismatch_pct = suspicious_pct = 0

        print(f"  一致记录:   {self.summary.match_count:5d} ({match_pct:5.1f}%)")
        print(f"  不一致记录: {self.summary.mismatch_count:5d} ({mismatch_pct:5.1f}%)")
        print(f"  可疑记录:   {self.summary.suspicious_count:5d} ({suspicious_pct:5.1f}%)")
        print()

        # 采购方式分布
        print("【采购方式分布】(按数据库记录)")
        for method, count in sorted(self.summary.method_distribution.items(),
                                    key=lambda x: -x[1]):
            if self.summary.total_count > 0:
                pct = count / self.summary.total_count * 100
            else:
                pct = 0
            bar_len = int(pct / 2)
            bar = "█" * bar_len
            print(f"  {method:8s}: {count:5d} ({pct:5.1f}%) {bar}")
        print()

        # AT/AP专项分析（重点）
        print("【AT/AP专项分析】★ 重点关注 ★")
        at = self.summary.at_ap_analysis
        print(f"  AT供应商:")
        print(f"    - 涉及记录: {at['at_total']:4d}")
        print(f"    - 判断一致: {at['at_match']:4d}")
        print(f"    - 判断不一致: {at['at_mismatch']:4d}")
        print(f"    - 可疑记录: {at['at_suspicious']:4d}")
        print(f"  AP供应商:")
        print(f"    - 涉及记录: {at['ap_total']:4d}")
        print(f"    - 判断一致: {at['ap_match']:4d}")
        print(f"    - 判断不一致: {at['ap_mismatch']:4d}")
        print(f"    - 可疑记录: {at['ap_suspicious']:4d}")
        print()

        # 不一致记录摘要
        if self.summary.mismatch_details:
            print("【不一致记录摘要】(前10条)")
            print("-" * 70)
            for i, r in enumerate(self.summary.mismatch_details[:10], 1):
                print(f"  {i}. 订单ID: {r.raw_order_id}")
                print(f"     商品: {r.product_name[:50]}{'...' if len(r.product_name) > 50 else ''}")
                print(f"     属性: {r.sales_attributes[:30] if r.sales_attributes else '无'}")
                print(f"     DB判断: {r.db_method}, 重算: {r.recalc_method}")
                print(f"     触发规则: {r.rule_triggered}")
                print()
        else:
            print("【不一致记录】无")
            print()

        # 可疑记录摘要（严重级别）
        severe_suspicious = [r for r in self.summary.suspicious_details
                            if r.suspicion_level == "严重"]
        if severe_suspicious:
            print("【严重可疑记录】(需人工复核)")
            print("-" * 70)
            for i, r in enumerate(severe_suspicious[:10], 1):
                print(f"  {i}. 订单ID: {r.raw_order_id}")
                print(f"     商品: {r.product_name[:50]}{'...' if len(r.product_name) > 50 else ''}")
                print(f"     DB方式: {r.db_method}, 重算: {r.recalc_method}")
                print(f"     原因: {r.suspicion_reason}")
                print()
        else:
            print("【严重可疑记录】无")
            print()

        print("=" * 70)

    # ================== Excel报告生成 ==================

    def export_excel_report(self, filename: str = None) -> str:
        """生成详细Excel报告"""
        try:
            import pandas as pd
            from openpyxl import Workbook
            from openpyxl.styles import PatternFill, Font, Alignment
            from openpyxl.utils.dataframe import dataframe_to_rows
        except ImportError:
            print("警告: 缺少 pandas 或 openpyxl，跳过Excel报告生成")
            print("      可运行: pip install pandas openpyxl")
            return None

        if filename is None:
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f"procurement_method_validation_{timestamp}.xlsx"

        print(f"正在生成Excel报告: {filename}")

        # 准备数据
        data = []
        for r in self.results:
            data.append({
                '订单ID': r.raw_order_id,
                '商品名称': r.product_name,
                '销售属性': r.sales_attributes,
                '品牌': r.brand,
                'DB采购方式': r.db_method,
                '重算采购方式': r.recalc_method,
                '是否一致': '是' if r.is_match else '否',
                '触发规则': r.rule_triggered,
                '可疑程度': r.suspicion_level,
                '可疑原因': r.suspicion_reason,
            })

        df = pd.DataFrame(data)

        # 创建Excel工作簿
        wb = Workbook()

        # Sheet 1: 所有记录
        ws_all = wb.active
        ws_all.title = "全部记录"
        self._write_dataframe_to_sheet(ws_all, df)

        # Sheet 2: 不一致记录
        ws_mismatch = wb.create_sheet("不一致记录")
        df_mismatch = df[df['是否一致'] == '否']
        if len(df_mismatch) > 0:
            self._write_dataframe_to_sheet(ws_mismatch, df_mismatch)
        else:
            ws_mismatch.cell(1, 1, "无不一致记录")

        # Sheet 3: 可疑记录
        ws_suspicious = wb.create_sheet("可疑记录")
        df_suspicious = df[df['可疑程度'] != '正常']
        if len(df_suspicious) > 0:
            self._write_dataframe_to_sheet(ws_suspicious, df_suspicious)
        else:
            ws_suspicious.cell(1, 1, "无可疑记录")

        # Sheet 4: AT/AP专项
        ws_at_ap = wb.create_sheet("AT_AP专项")
        df_at_ap = df[(df['DB采购方式'].isin(['AT', 'AP'])) |
                      (df['重算采购方式'].isin(['AT', 'AP']))]
        if len(df_at_ap) > 0:
            self._write_dataframe_to_sheet(ws_at_ap, df_at_ap)
        else:
            ws_at_ap.cell(1, 1, "无AT/AP相关记录")

        # Sheet 5: 统计汇总
        ws_summary = wb.create_sheet("统计汇总")
        self._write_summary_sheet(ws_summary)

        # 保存
        wb.save(filename)
        print(f"Excel报告已保存: {filename}")
        return filename

    def _write_dataframe_to_sheet(self, ws, df):
        """将DataFrame写入工作表并设置样式"""
        from openpyxl.styles import PatternFill, Font, Alignment
        from openpyxl.utils.dataframe import dataframe_to_rows

        # 表头样式
        header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        header_font = Font(color="FFFFFF", bold=True)

        # 高亮样式
        mismatch_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
        severe_fill = PatternFill(start_color="FF6B6B", end_color="FF6B6B", fill_type="solid")
        severe_font = Font(color="FFFFFF", bold=True)

        # 写入数据
        for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True)):
            for c_idx, value in enumerate(row, 1):
                cell = ws.cell(row=r_idx + 1, column=c_idx, value=value)

                if r_idx == 0:
                    # 表头样式
                    cell.fill = header_fill
                    cell.font = header_font
                    cell.alignment = Alignment(horizontal='center')
                else:
                    # 数据行高亮
                    # 是否一致列（第7列）为"否"时高亮整行
                    if c_idx == 7 and value == '否':
                        for col in range(1, len(row) + 1):
                            ws.cell(row=r_idx + 1, column=col).fill = mismatch_fill
                    # 可疑程度列（第9列）为"严重"时特殊高亮
                    if c_idx == 9 and value == '严重':
                        cell.fill = severe_fill
                        cell.font = severe_font

        # 自动调整列宽
        for col_cells in ws.columns:
            max_length = 0
            col_letter = col_cells[0].column_letter
            for cell in col_cells:
                try:
                    cell_len = len(str(cell.value or ""))
                    if cell_len > max_length:
                        max_length = cell_len
                except:
                    pass
            # 限制最大列宽
            ws.column_dimensions[col_letter].width = min(max_length + 2, 50)

    def _write_summary_sheet(self, ws):
        """写入统计汇总工作表"""
        from openpyxl.styles import Font

        rows = [
            ["采购方式判断逻辑验证统计汇总", "", ""],
            [f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}", "", ""],
            ["", "", ""],
            ["基本统计", "", ""],
            ["验证订单总数", self.summary.total_count, ""],
            ["一致记录数", self.summary.match_count,
             f"{self.summary.match_count/max(self.summary.total_count,1)*100:.2f}%"],
            ["不一致记录数", self.summary.mismatch_count,
             f"{self.summary.mismatch_count/max(self.summary.total_count,1)*100:.2f}%"],
            ["可疑记录数", self.summary.suspicious_count,
             f"{self.summary.suspicious_count/max(self.summary.total_count,1)*100:.2f}%"],
            ["", "", ""],
            ["采购方式分布", "数量", "占比"],
        ]

        for method, count in sorted(self.summary.method_distribution.items(),
                                    key=lambda x: -x[1]):
            rows.append([method, count, f"{count/max(self.summary.total_count,1)*100:.1f}%"])

        rows.extend([
            ["", "", ""],
            ["AT/AP专项分析", "", ""],
            ["AT记录总数", self.summary.at_ap_analysis['at_total'], ""],
            ["AT判断一致", self.summary.at_ap_analysis['at_match'], ""],
            ["AT判断不一致", self.summary.at_ap_analysis['at_mismatch'], ""],
            ["AT可疑记录", self.summary.at_ap_analysis['at_suspicious'], ""],
            ["", "", ""],
            ["AP记录总数", self.summary.at_ap_analysis['ap_total'], ""],
            ["AP判断一致", self.summary.at_ap_analysis['ap_match'], ""],
            ["AP判断不一致", self.summary.at_ap_analysis['ap_mismatch'], ""],
            ["AP可疑记录", self.summary.at_ap_analysis['ap_suspicious'], ""],
        ])

        for r_idx, row_data in enumerate(rows, 1):
            for c_idx, value in enumerate(row_data, 1):
                cell = ws.cell(row=r_idx, column=c_idx, value=value)
                # 标题行加粗
                if r_idx in [1, 4, 10, 13]:
                    cell.font = Font(bold=True, size=12)

        # 调整列宽
        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 15
        ws.column_dimensions['C'].width = 15


# ================== 主函数 ==================

async def main():
    """主函数"""
    print("=" * 70)
    print("   采购方式判断逻辑验证工具")
    print("   测试最近1000个订单的采购方式判断正确性")
    print("=" * 70)
    print()

    validator = ProcurementMethodValidator()

    async for db in get_db():
        try:
            # 加载并验证数据
            await validator.load_and_validate(db, limit=1000)

            # 输出控制台摘要
            validator.print_console_summary()

            # 生成Excel报告
            report_file = validator.export_excel_report()

            print()
            print("=" * 70)
            print("验证完成!")
            if report_file:
                print(f"详细报告文件: {report_file}")
            print("=" * 70)

        except Exception as e:
            print(f"\n错误: 验证过程中发生异常: {e}")
            import traceback
            traceback.print_exc()

        break


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