#!/usr/bin/env python3
"""
修复重复订单物流信息同步问题

问题描述：
    同一订单号在 pending_shipments 表中存在两条记录：
    - 一条 is_deleted=1，有物流信息
    - 一条 is_deleted=0，无物流信息

解决方案：
    将已删除记录的物流信息同步到活跃记录，然后删除重复的已删除记录

用法：
    cd ordersys/backend
    python scripts/fix_duplicate_shipping_orders.py [--dry-run]

参数：
    --dry-run   仅显示将要修复的记录，不实际执行更新
"""

import asyncio
import sys
from pathlib import Path

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

from sqlalchemy import select, and_, text
from sqlalchemy.ext.asyncio import AsyncSession
from app.core.database import AsyncSessionLocal


async def find_duplicate_orders(db: AsyncSession):
    """查找需要同步物流信息的重复订单"""

    # 使用原生 SQL 查找重复订单
    # 条件：活跃记录无物流信息，删除记录有物流信息
    sql = text("""
        SELECT
            active.id as active_id,
            deleted.id as deleted_id,
            active.子订单编号,
            active.网店名称,
            deleted.系统快递单号,
            deleted.系统快递公司,
            deleted.系统发货状态,
            deleted.发货时间,
            deleted.系统发货时间,
            deleted.发货状态详情
        FROM pending_shipments active
        JOIN pending_shipments deleted
            ON active.子订单编号 = deleted.子订单编号
            AND active.网店名称 = deleted.网店名称
        WHERE active.is_deleted = 0
          AND deleted.is_deleted = 1
          AND deleted.系统快递单号 IS NOT NULL
          AND deleted.系统快递单号 != ''
          AND (active.系统快递单号 IS NULL OR active.系统快递单号 = '')
        ORDER BY active.子订单编号
    """)

    result = await db.execute(sql)
    return result.fetchall()


async def sync_shipping_info(db: AsyncSession, records: list, dry_run: bool = False):
    """同步物流信息到活跃记录"""

    if not records:
        print("没有找到需要修复的重复订单")
        return

    print(f"\n找到 {len(records)} 条需要修复的记录：\n")
    print("-" * 100)
    print(f"{'活跃ID':<10} {'删除ID':<10} {'子订单编号':<25} {'快递单号':<20} {'快递公司':<10} {'状态':<10}")
    print("-" * 100)

    for record in records:
        print(f"{record.active_id:<10} {record.deleted_id:<10} {record.子订单编号:<25} "
              f"{record.系统快递单号 or '':<20} {record.系统快递公司 or '':<10} {record.系统发货状态 or '':<10}")

    print("-" * 100)

    if dry_run:
        print("\n[DRY RUN] 以上记录将被修复，实际未执行任何更新")
        return

    # 执行更新
    updated_count = 0
    deleted_count = 0

    for record in records:
        # 1. 更新活跃记录的物流信息（包括系统发货时间和发货状态详情）
        update_sql = text("""
            UPDATE pending_shipments
            SET
                系统快递单号 = :tracking_no,
                系统快递公司 = :courier,
                系统发货状态 = :status,
                发货时间 = :ship_time,
                系统发货时间 = :system_ship_time,
                发货状态详情 = :status_detail,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :active_id
        """)

        await db.execute(update_sql, {
            'tracking_no': record.系统快递单号,
            'courier': record.系统快递公司,
            'status': record.系统发货状态,
            'ship_time': record.发货时间,
            'system_ship_time': record.系统发货时间,
            'status_detail': record.发货状态详情,
            'active_id': record.active_id
        })
        updated_count += 1

        # 2. 删除重复的已删除记录（可选，取消注释以启用）
        # delete_sql = text("DELETE FROM pending_shipments WHERE id = :deleted_id")
        # await db.execute(delete_sql, {'deleted_id': record.deleted_id})
        # deleted_count += 1

    await db.commit()

    print(f"\n修复完成！")
    print(f"  - 更新了 {updated_count} 条活跃记录的物流信息")
    if deleted_count > 0:
        print(f"  - 删除了 {deleted_count} 条重复的已删除记录")


async def verify_specific_order(db: AsyncSession, sub_order_no: str):
    """验证特定订单的修复结果"""

    sql = text("""
        SELECT id, 子订单编号, 系统发货状态, 系统快递单号, 系统快递公司, is_deleted
        FROM pending_shipments
        WHERE 子订单编号 = :sub_order_no
        ORDER BY is_deleted, id
    """)

    result = await db.execute(sql, {'sub_order_no': sub_order_no})
    records = result.fetchall()

    print(f"\n订单 {sub_order_no} 的当前状态：")
    print("-" * 80)
    print(f"{'ID':<10} {'发货状态':<15} {'快递单号':<20} {'快递公司':<15} {'是否删除':<10}")
    print("-" * 80)

    for r in records:
        print(f"{r.id:<10} {r.系统发货状态 or 'N/A':<15} {r.系统快递单号 or 'N/A':<20} "
              f"{r.系统快递公司 or 'N/A':<15} {'是' if r.is_deleted else '否':<10}")

    print("-" * 80)


async def main():
    dry_run = '--dry-run' in sys.argv

    if dry_run:
        print("=" * 50)
        print("  DRY RUN 模式 - 不会执行实际更新")
        print("=" * 50)

    async with AsyncSessionLocal() as db:
        # 查找重复订单
        records = await find_duplicate_orders(db)

        # 同步物流信息
        await sync_shipping_info(db, records, dry_run=dry_run)

        # 验证示例订单（用户报告的问题订单）
        if not dry_run and records:
            # 验证第一条修复的订单
            await verify_specific_order(db, records[0].子订单编号)


if __name__ == '__main__':
    print("\n" + "=" * 60)
    print("  修复重复订单物流信息同步问题")
    print("=" * 60)

    asyncio.run(main())