-- ============================================================================
-- t_device_inventory 去重 + 添加唯一约束
--
-- 背景：同一设备同一商品存在多条记录，原因是：
--   1. ReplenishmentOrderServiceImpl.completeOrder 用 productCode 查重，
--      与其他路径（用 productId）不一致
--   2. 数据库层缺少 (device_id, product_id) 唯一约束，并发场景下查重失效
--
-- 执行前建议：先 SELECT 确认重复数据量
--   SELECT device_id, product_id, COUNT(*) cnt FROM t_device_inventory
--   WHERE product_id IS NOT NULL GROUP BY device_id, product_id HAVING cnt > 1;
-- ============================================================================

-- Step 1: 清理重复记录
-- 策略：每组 (device_id, product_id) 保留 id 最大的那条（最新插入的），删除其余
DELETE FROM t_device_inventory
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM t_device_inventory
        WHERE product_id IS NOT NULL
        GROUP BY device_id, product_id
    ) AS kept
)
AND product_id IS NOT NULL;

-- Step 2: 添加唯一约束，从根本上杜绝重复
-- 如果该约束已存在会报错 Duplicate key name，这是预期行为
ALTER TABLE t_device_inventory
ADD UNIQUE KEY uk_device_product (device_id, product_id);