#!/usr/bin/env python
"""
测试验证系统的导入功能
"""
import asyncio
import sys
from pathlib import Path
from typing import Dict, Any
import json

# 添加backend到Python路径
sys.path.insert(0, '/Users/jinjunqian/PycharmProjects/订单处理新版/ordersys/backend')

from app.services.import_service import ImportService
from app.core.database import get_db
from sqlalchemy import select, text
from app.models.raw_orders import ProcessedFile

async def test_file_import(file_path: Path, expected_result: str) -> Dict[str, Any]:
    """
    测试单个文件的导入

    Args:
        file_path: 文件路径
        expected_result: 期望的结果 ('success', 'error', 'warning')
    """
    print(f"\n{'='*60}")
    print(f"Testing: {file_path.name}")
    print(f"Expected: {expected_result}")
    print('-' * 60)

    result = {
        'file': file_path.name,
        'expected': expected_result,
        'actual': None,
        'message': None,
        'validation_errors': [],
        'validation_warnings': []
    }

    try:
        # 获取数据库会话
        async for db in get_db():
            # 创建导入服务
            service = ImportService()

            # 尝试导入文件
            import_result = await service.import_file(db, file_path)

            # 检查processed_files表中的记录
            processed_file = await db.execute(
                select(ProcessedFile).where(ProcessedFile.file_name == file_path.name).order_by(ProcessedFile.process_time.desc())
            )
            latest_file = processed_file.scalars().first()

            if latest_file:
                result['actual'] = latest_file.status
                result['message'] = latest_file.error_message

                # 解析验证详情
                if latest_file.validation_result:
                    validation_data = json.loads(latest_file.validation_result)
                    result['validation_errors'] = validation_data.get('errors', [])
                    result['validation_warnings'] = validation_data.get('warnings', [])

                    print(f"Status: {latest_file.status}")
                    if validation_data.get('errors'):
                        print(f"Validation Errors:")
                        for error in validation_data['errors']:
                            print(f"  - {error}")

                    if validation_data.get('warnings'):
                        print(f"Validation Warnings:")
                        for warning in validation_data['warnings']:
                            print(f"  - {warning}")
            else:
                result['actual'] = 'success' if import_result else 'error'
                print(f"Status: {result['actual']}")

            # 验证是否符合预期
            if expected_result == 'error':
                success = result['actual'] in ['failed', 'error'] or len(result['validation_errors']) > 0
            elif expected_result == 'warning':
                success = result['actual'] == 'success' and len(result['validation_warnings']) > 0
            else:  # success
                success = result['actual'] == 'success'

            result['test_passed'] = success
            print(f"Test Result: {'✅ PASSED' if success else '❌ FAILED'}")

            break

    except Exception as e:
        result['actual'] = 'error'
        result['message'] = str(e)
        result['test_passed'] = (expected_result == 'error')
        print(f"Exception: {e}")
        print(f"Test Result: {'✅ PASSED' if result['test_passed'] else '❌ FAILED'}")

    return result

async def run_all_tests():
    """运行所有测试"""
    test_dir = Path('/Users/jinjunqian/PycharmProjects/订单处理新版/ordersys/test_files')

    # 定义测试用例
    test_cases = [
        # 应该失败的文件（错误级别）
        ('test_missing_columns.xlsx', 'error'),         # 缺少必需列
        ('test_too_many_nulls.xlsx', 'error'),         # 空值过多
        ('test_invalid_data_types.xlsx', 'error'),      # 数据类型错误
        ('test_business_logic_errors.xlsx', 'error'),   # 业务逻辑错误
        ('test_negative_values.xlsx', 'error'),         # 负数值
        ('test_future_dates.xlsx', 'error'),            # 未来日期
        ('test_empty.xlsx', 'error'),                   # 空文件
        ('test_too_few_valid_rows.xlsx', 'error'),      # 有效行过少

        # 应该有警告但成功的文件
        ('test_warning_level.xlsx', 'warning'),         # 有警告但可导入

        # 应该成功的文件
        ('test_valid.xlsx', 'success'),                 # 完全有效
    ]

    results = []
    print("\n" + "="*60)
    print("STARTING VALIDATION SYSTEM TESTS")
    print("="*60)

    for file_name, expected_result in test_cases:
        file_path = test_dir / file_name
        if file_path.exists():
            result = await test_file_import(file_path, expected_result)
            results.append(result)
        else:
            print(f"\n❌ File not found: {file_path}")

    # 打印总结
    print("\n" + "="*60)
    print("TEST SUMMARY")
    print("="*60)

    passed = sum(1 for r in results if r['test_passed'])
    total = len(results)

    for result in results:
        status = '✅' if result['test_passed'] else '❌'
        print(f"{status} {result['file']}: Expected {result['expected']}, Got {result['actual']}")

    print(f"\nTotal: {passed}/{total} tests passed")

    if passed == total:
        print("\n🎉 ALL TESTS PASSED! 验证系统工作正常！")
    else:
        print(f"\n⚠️ {total - passed} tests failed. 需要检查验证系统。")

    return results

async def check_database_records():
    """检查数据库中的记录"""
    async for db in get_db():
        # 检查processed_files表
        result = await db.execute(text("""
            SELECT file_name, status, validation_result IS NOT NULL as has_validation,
                   substr(error_message, 1, 50) as error_preview
            FROM processed_files
            WHERE file_name LIKE 'test_%.xlsx'
            ORDER BY process_time DESC
            LIMIT 20
        """))

        print("\n" + "="*60)
        print("DATABASE RECORDS CHECK")
        print("="*60)

        records = result.fetchall()
        if records:
            print(f"Found {len(records)} test file records:")
            for record in records:
                print(f"  - {record[0]}: status={record[1]}, has_validation={bool(record[2])}")
                if record[3]:
                    print(f"    Error: {record[3]}...")
        else:
            print("No test file records found in database")

        break

# 主程序
if __name__ == "__main__":
    print("Testing File Validation System")
    print("This will test importing various Excel files with validation issues")

    # 运行所有测试
    asyncio.run(run_all_tests())

    # 检查数据库记录
    asyncio.run(check_database_records())