-- 按 entry_order_id + symbol 去重：同一开仓订单只保留一条（保留 id 最小的，即最早创建的）
-- 使用前请先备份 trades 表；建议先执行「1. 查看重复」确认后再执行「2. 删除重复」
-- 说明：仅处理 entry_order_id 非空的重复；无开仓订单号的重复记录（如 sync_recovered 脏数据）需人工按 symbol/时间判断后删除。

-- ========== 1. 查看重复（只读，不写库）==========
-- 列出所有 (entry_order_id, symbol) 出现多于一次的组，以及每组中的记录
SELECT
  t.entry_order_id,
  t.symbol,
  COUNT(*) AS cnt,
  GROUP_CONCAT(t.id ORDER BY t.id) AS ids,
  GROUP_CONCAT(CONCAT(t.id, '(', t.status, ',entry=', FROM_UNIXTIME(t.entry_time), ',exit=', IFNULL(FROM_UNIXTIME(t.exit_time), 'NULL'), ')') ORDER BY t.id SEPARATOR ' | ') AS detail
FROM trades t
WHERE t.entry_order_id IS NOT NULL
GROUP BY t.entry_order_id, t.symbol
HAVING COUNT(*) > 1;

-- 若有多账号，按 account_id 也分组查看（可选）：
-- SELECT account_id, entry_order_id, symbol, COUNT(*) AS cnt, GROUP_CONCAT(id ORDER BY id) AS ids
-- FROM trades WHERE entry_order_id IS NOT NULL
-- GROUP BY account_id, entry_order_id, symbol HAVING COUNT(*) > 1;


-- ========== 2. 删除重复（保留每组 id 最小的那条，删除同组其余行）==========
-- 执行前请确认上面查询结果符合预期；建议先备份: CREATE TABLE trades_backup_YYYYMMDD AS SELECT * FROM trades;

DELETE t
FROM trades t
INNER JOIN (
  SELECT entry_order_id, symbol, MIN(id) AS keep_id
  FROM trades
  WHERE entry_order_id IS NOT NULL
  GROUP BY entry_order_id, symbol
  HAVING COUNT(*) > 1
) g ON t.entry_order_id = g.entry_order_id AND t.symbol = g.symbol AND t.id <> g.keep_id;

-- 若有多账号，按 account_id 去重（取消下面注释并注释掉上面的 DELETE）：
/*
DELETE t
FROM trades t
INNER JOIN (
  SELECT account_id, entry_order_id, symbol, MIN(id) AS keep_id
  FROM trades
  WHERE entry_order_id IS NOT NULL
  GROUP BY account_id, entry_order_id, symbol
  HAVING COUNT(*) > 1
) g ON t.account_id = g.account_id AND t.entry_order_id = g.entry_order_id AND t.symbol = g.symbol AND t.id <> g.keep_id;
*/


-- ========== 3. 再次检查（应无重复）==========
SELECT entry_order_id, symbol, COUNT(*) AS cnt
FROM trades
WHERE entry_order_id IS NOT NULL
GROUP BY entry_order_id, symbol
HAVING COUNT(*) > 1;
-- 期望结果：空
