#!/usr/bin/env python3
"""
批量更新raw_orders表中所有待采购订单的推送状态为"是"
"""

import sys
import os
sys.path.insert(0, os.path.join(os.path.dirname(__file__), 'backend'))

import asyncio
import logging
from sqlalchemy import select, update, and_, func, text
from app.core.database import get_db

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

async def update_all_push_status():
    """将raw_orders表中所有待采购订单的推送状态更新为'是'"""
    
    async for db in get_db():
        try:
            # 首先统计当前的推送状态
            print("\n=== 更新前的统计 ===")
            
            # 使用原生SQL查询
            count_query = text("""
                SELECT 已推送, COUNT(*) as count
                FROM raw_orders
                WHERE procurement_status = 'PENDING'
                GROUP BY 已推送
            """)
            
            result = await db.execute(count_query)
            status_counts = result.fetchall()
            
            unpushed_count = 0
            pushed_count = 0
            
            for row in status_counts:
                if row[0] == '否':
                    unpushed_count = row[1]
                elif row[0] == '是':
                    pushed_count = row[1]
            
            print(f"待采购未推送订单: {unpushed_count} 条")
            print(f"待采购已推送订单: {pushed_count} 条")
            
            if unpushed_count == 0:
                print("\n没有需要更新的订单，所有待采购订单都已标记为已推送。")
                return True
            
            # 确认是否执行更新
            print(f"\n准备将 {unpushed_count} 条待采购订单的推送状态更新为'是'")
            confirm = input("确认执行？(yes/y 确认，其他取消): ")
            
            if confirm.lower() not in ['yes', 'y']:
                print("操作已取消")
                return False
            
            # 执行批量更新
            update_stmt = text("""
                UPDATE raw_orders
                SET 已推送 = '是'
                WHERE procurement_status = 'PENDING' AND 已推送 = '否'
            """)
            
            result = await db.execute(update_stmt)
            await db.commit()
            
            updated_rows = result.rowcount
            print(f"\n✅ 成功更新 {updated_rows} 条订单的推送状态为'是'")
            
            # 再次统计确认结果
            print("\n=== 更新后的统计 ===")
            
            result = await db.execute(count_query)
            status_counts = result.fetchall()
            
            unpushed_count = 0
            pushed_count = 0
            
            for row in status_counts:
                if row[0] == '否':
                    unpushed_count = row[1]
                elif row[0] == '是':
                    pushed_count = row[1]
            
            print(f"待采购未推送订单: {unpushed_count} 条")
            print(f"待采购已推送订单: {pushed_count} 条")
            
            # 显示一些样本数据
            sample_query = text("""
                SELECT 原始订单编号, 线上宝贝名称, procurement_method, 已推送
                FROM raw_orders
                WHERE procurement_status = 'PENDING'
                LIMIT 5
            """)
            
            result = await db.execute(sample_query)
            samples = result.fetchall()
            
            if samples:
                print("\n=== 更新后的订单示例 ===")
                for s in samples:
                    product = s[1][:40] + ".." if len(s[1]) > 40 else s[1]
                    print(f"订单号: {s[0]}, 商品: {product}, 方式: {s[2]}, 已推送: {s[3]}")
            
            return True
            
        except Exception as e:
            logger.error(f"更新推送状态时出错: {e}", exc_info=True)
            await db.rollback()
            print(f"\n❌ 更新失败: {e}")
            return False
        finally:
            await db.close()
            break

if __name__ == "__main__":
    print("批量更新待采购订单推送状态工具")
    print("=" * 50)
    asyncio.run(update_all_push_status())