-- ============================================================
-- 设备保养计划明细去重（数据修复，非结构变更）
-- 生成日期：2026-11-06
--
-- 背景：历史版本补设备/改日期时，可能给同一计划、同一设备、
--   同一保养月份留下多条明细，导致导出文档重复。
--
-- 去重口径：同一 maintenance_plan_id + device_id + 保养月份
--   只保留一条。
--   优先保留已有保养记录的明细；都没有记录时保留最新一条。
--   为保证数据安全，只删除“没有任何保养记录”的重复行；
--   若同一组里有多条都已有保养记录，不自动删除，需人工确认。
--
-- 执行前必须先备份（脚本会自动创建备份表，已存在则不覆盖）。
-- 脚本可重复执行（幂等）。
-- ============================================================

-- 1. 备份明细表（首次执行创建，重复执行不覆盖）
CREATE TABLE IF NOT EXISTS `device_maintenance_plan_details_bak_20261106` AS
SELECT * FROM `device_maintenance_plan_details`;

-- 2. 查看去重前的重复情况
SELECT maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m') AS maintenance_month,
       COUNT(*) AS duplicate_count,
       GROUP_CONCAT(maintenance_plan_detail_id ORDER BY maintenance_plan_detail_id) AS detail_ids
FROM device_maintenance_plan_details
GROUP BY maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m')
HAVING COUNT(*) > 1;

-- 3. 删除重复行：按“有保养记录优先、最新优先”排序，仅删除无保养记录的重复行
START TRANSACTION;

DELETE d
FROM device_maintenance_plan_details d
JOIN (
    SELECT maintenance_plan_detail_id
    FROM (
        SELECT dmpd.maintenance_plan_detail_id,
               ROW_NUMBER() OVER (
                   PARTITION BY dmpd.maintenance_plan_id, dmpd.device_id, DATE_FORMAT(dmpd.maintenance_date, '%Y-%m')
                   ORDER BY (SELECT COUNT(*) FROM device_maintenance dm
                             WHERE dm.maintenance_plan_detail_id = dmpd.maintenance_plan_detail_id) DESC,
                            dmpd.maintenance_plan_detail_id DESC
               ) AS rn
        FROM device_maintenance_plan_details dmpd
    ) ranked
    WHERE ranked.rn > 1
) dup ON d.maintenance_plan_detail_id = dup.maintenance_plan_detail_id
WHERE NOT EXISTS (
    SELECT 1 FROM device_maintenance dm
    WHERE dm.maintenance_plan_detail_id = d.maintenance_plan_detail_id
);

COMMIT;

-- 4. 校验（执行完可人工核对）
-- 4.1 应为 0 行：同一计划 + 同一设备 + 同一保养月份仍有重复
-- SELECT maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m') AS maintenance_month, COUNT(*) AS c
-- FROM device_maintenance_plan_details
-- GROUP BY maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m')
-- HAVING c > 1;
--
-- 4.2 应为 0 行：保养记录关联的计划明细已不存在（防止误删导致记录孤儿）
-- SELECT dm.id, dm.maintenance_plan_detail_id
-- FROM device_maintenance dm
-- LEFT JOIN device_maintenance_plan_details dmpd
--   ON dmpd.maintenance_plan_detail_id = dm.maintenance_plan_detail_id
-- WHERE dmpd.maintenance_plan_detail_id IS NULL;
--
-- 4.3 清理前后数量对比：
-- SELECT COUNT(*) FROM device_maintenance_plan_details;
-- SELECT COUNT(*) FROM device_maintenance_plan_details_bak_20261106;