-- 修复重复订单问题的SQL脚本

-- 1. 查询当前重复订单情况
SELECT '=== 当前重复订单统计 ===' as info;
SELECT 原始订单编号, COUNT(*) as cnt, 
       GROUP_CONCAT(付款时间, ', ') as payment_times
FROM raw_orders 
WHERE 原始订单编号 IS NOT NULL 
GROUP BY 原始订单编号 
HAVING cnt > 1 
ORDER BY cnt DESC 
LIMIT 10;

-- 2. 删除重复订单（保留付款时间正确的记录）
DELETE FROM raw_orders 
WHERE id IN (
    SELECT id FROM (
        SELECT id, 原始订单编号, 付款时间,
               ROW_NUMBER() OVER (
                   PARTITION BY 原始订单编号 
                   ORDER BY 
                       CASE WHEN 付款时间 = '1900-01-01 00:00:00.000000' THEN 1 ELSE 0 END,
                       id
               ) as rn
        FROM raw_orders 
        WHERE 原始订单编号 IS NOT NULL
    ) 
    WHERE rn > 1
);

-- 3. 创建额外的唯一索引（基于订单号）
CREATE UNIQUE INDEX IF NOT EXISTS uk_raw_order_number 
ON raw_orders(原始订单编号) 
WHERE 原始订单编号 IS NOT NULL AND 原始订单编号 != '';

-- 4. 验证清理结果
SELECT '=== 清理后的重复订单统计 ===' as info;
SELECT COUNT(*) as duplicate_order_count
FROM (
    SELECT 原始订单编号, COUNT(*) as cnt
    FROM raw_orders 
    WHERE 原始订单编号 IS NOT NULL 
    GROUP BY 原始订单编号 
    HAVING cnt > 1
);

-- 5. 统计1900年日期的记录
SELECT '=== 异常日期记录统计 ===' as info;
SELECT COUNT(*) as count_1900_dates
FROM raw_orders 
WHERE 付款时间 LIKE '1900-%';