#!/usr/bin/env python3
import sys
import os
import sqlite3
from datetime import datetime
from pathlib import Path

# Add backend to path
sys.path.insert(0, 'backend')

# Connect to database
db_path = 'backend/ordersys.db'
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

print("=" * 80)
print("订单状态检查报告")
print("=" * 80)

# 1. Check pending procurement orders
print("\n📦 待采购订单列表:")
print("-" * 40)

query = """
SELECT 
    po.id,
    po."原始订单编号",
    po."品牌",
    po."颜色",
    po."尺寸",
    po."数量",
    po.procurement_method,
    po.procurement_status,
    po."创建时间",
    po."线上宝贝名称"
FROM procurement_orders po
WHERE po.procurement_status = 'pending' OR po.procurement_status IS NULL
ORDER BY po."创建时间" DESC
LIMIT 20
"""

cursor.execute(query)
pending_orders = cursor.fetchall()

if pending_orders:
    print(f"找到 {len(pending_orders)} 个待采购订单:\n")
    for order in pending_orders:
        print(f"  订单ID: {order[0]}")
        print(f"  订单号: {order[1]}")
        print(f"  产品: {order[2]} - {order[3]} - {order[4]}")
        print(f"  宝贝名称: {order[9][:50]}...")
        print(f"  数量: {order[5]}")
        print(f"  采购方式: {order[6] or '未设置'}")
        print(f"  状态: {order[7] or 'pending'}")
        print(f"  创建时间: {order[8]}")
        print("-" * 30)
else:
    print("✅ 没有待采购的订单")

# 2. Check for new Excel files that haven't been imported
print("\n📊 检查未导入的原始订单文件:")
print("-" * 40)

# Get list of Excel files
excel_dir = Path("/Volumes/DDD/订单系统订单存档")
if excel_dir.exists():
    excel_files = list(excel_dir.glob("*.xls")) + list(excel_dir.glob("*.xlsx"))
    
    # Get imported file names from processed_files table
    cursor.execute("SELECT filename FROM processed_files")
    imported_files = {row[0] for row in cursor.fetchall()}
    
    # Check for unimported files
    import hashlib
    unimported = []
    
    for file_path in excel_files:
        # Calculate file hash
        with open(file_path, 'rb') as f:
            file_hash = hashlib.sha1(f.read()).hexdigest()
        
        if file_hash not in imported_hashes:
            # Check if this file has any import attempt
            cursor.execute(
                "SELECT status, created_at FROM import_logs WHERE file_hash = ? ORDER BY created_at DESC LIMIT 1",
                (file_hash,)
            )
            result = cursor.fetchone()
            
            if not result:
                unimported.append((file_path.name, "从未导入"))
            elif result[0] == 'failed':
                unimported.append((file_path.name, f"导入失败 ({result[1]})"))
    
    if unimported:
        print(f"⚠️ 发现 {len(unimported)} 个未成功导入的文件:\n")
        for filename, status in unimported:
            print(f"  • {filename}: {status}")
    else:
        print("✅ 所有Excel文件都已成功导入")
else:
    print(f"⚠️ 找不到Excel文件目录: {excel_dir}")

# 3. Statistics summary
print("\n📈 统计摘要:")
print("-" * 40)

# Total orders by status
cursor.execute("""
SELECT procurement_status, COUNT(*) 
FROM procurement_orders 
GROUP BY procurement_status
""")

status_counts = cursor.fetchall()
print("采购订单状态分布:")
for status, count in status_counts:
    status_display = status if status else "pending"
    print(f"  • {status_display}: {count} 个订单")

# Recent imports
cursor.execute("""
SELECT filename, status, created_at, records_processed
FROM import_logs
ORDER BY created_at DESC
LIMIT 5
""")

recent_imports = cursor.fetchall()
if recent_imports:
    print("\n最近的导入记录:")
    for filename, status, created_at, records in recent_imports:
        print(f"  • {filename}: {status} ({records or 0} 条记录) - {created_at}")

conn.close()
print("\n" + "=" * 80)