-- ============================================
-- 修复重复车牌号问题
-- ============================================

-- 1. 查询重复的车牌号
SELECT 
    vehicle_no, 
    COUNT(*) as count,
    GROUP_CONCAT(vehicle_id ORDER BY vehicle_id) as vehicle_ids,
    GROUP_CONCAT(platform_code ORDER BY vehicle_id) as platform_codes,
    GROUP_CONCAT(create_time ORDER BY vehicle_id) as create_times
FROM tb_vehicle_info
GROUP BY vehicle_no
HAVING COUNT(*) > 1
ORDER BY count DESC;

-- 2. 查看具体重复记录的详情
SELECT 
    vehicle_id,
    vehicle_no,
    platform_code,
    device_id,
    car_id,
    status,
    create_time,
    update_time
FROM tb_vehicle_info
WHERE vehicle_no IN (
    SELECT vehicle_no 
    FROM tb_vehicle_info 
    GROUP BY vehicle_no 
    HAVING COUNT(*) > 1
)
ORDER BY vehicle_no, vehicle_id;

-- 3. 备份重复数据（在删除前）
CREATE TABLE IF NOT EXISTS tb_vehicle_info_duplicate_backup AS
SELECT * FROM tb_vehicle_info
WHERE vehicle_no IN (
    SELECT vehicle_no 
    FROM tb_vehicle_info 
    GROUP BY vehicle_no 
    HAVING COUNT(*) > 1
);

-- 4. 删除重复记录（保留最早创建的记录）
-- 注意：执行前请先确认备份完成！
-- 取消下面的注释来执行删除操作：

/*
DELETE v1 FROM tb_vehicle_info v1
INNER JOIN tb_vehicle_info v2 
WHERE v1.vehicle_no = v2.vehicle_no 
  AND v1.vehicle_id > v2.vehicle_id;
*/

-- 5. 验证删除后的结果
SELECT 
    vehicle_no, 
    COUNT(*) as count
FROM tb_vehicle_info
GROUP BY vehicle_no
HAVING COUNT(*) > 1;

-- 6. 添加唯一索引（防止未来出现重复）
-- 注意：只有在确认没有重复数据后才能执行！
-- 取消下面的注释来添加唯一索引：

/*
ALTER TABLE tb_vehicle_info 
ADD UNIQUE INDEX uk_vehicle_no (vehicle_no);
*/

-- 7. 查询统计信息
SELECT 
    '总车辆数' as item,
    COUNT(*) as count
FROM tb_vehicle_info
UNION ALL
SELECT 
    '唯一车牌数' as item,
    COUNT(DISTINCT vehicle_no) as count
FROM tb_vehicle_info
UNION ALL
SELECT 
    'CMS平台车辆' as item,
    COUNT(*) as count
FROM tb_vehicle_info
WHERE platform_code = 'CMS'
UNION ALL
SELECT 
    'GPS51平台车辆' as item,
    COUNT(*) as count
FROM tb_vehicle_info
WHERE platform_code = 'GPS51'
UNION ALL
SELECT 
    'LEGACY平台车辆' as item,
    COUNT(*) as count
FROM tb_vehicle_info
WHERE platform_code = 'LEGACY';