-- 为 trades 表添加“交易规模/风险”字段：
-- 1) 保证金（margin_usdt）
-- 2) 名义下单量（notional_usdt）
-- 3) 实际止损/止盈/分步止盈与ATR（用于仪表板展示真实值）
--
-- 说明：
-- - 可重复执行：列已存在会自动跳过
-- - 默认统计口径使用“入场时”的名义/保证金
--

SET @dbname = DATABASE();
SET @tablename = 'trades';

-- 1) notional_usdt（名义下单量）
SET @columnname = 'notional_usdt';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname)
  ) > 0,
  "SELECT 'Column notional_usdt already exists.' AS result;",
  CONCAT(
    "ALTER TABLE `", @tablename, "` ",
    "ADD COLUMN `", @columnname, "` DECIMAL(20, 8) NULL ",
    "COMMENT '名义下单量（USDT）：入场价×数量（用于统计总交易量）';"
  )
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 2) margin_usdt（保证金）
SET @columnname2 = 'margin_usdt';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname2)
  ) > 0,
  "SELECT 'Column margin_usdt already exists.' AS result;",
  CONCAT(
    "ALTER TABLE `", @tablename, "` ",
    "ADD COLUMN `", @columnname2, "` DECIMAL(20, 8) NULL ",
    "COMMENT '保证金（USDT）：名义下单量/杠杆（用于统计与盈亏口径统一）';"
  )
));
PREPARE alterIfNotExists2 FROM @preparedStatement2;
EXECUTE alterIfNotExists2;
DEALLOCATE PREPARE alterIfNotExists2;

-- 3) take_profit_1（分步止盈1）
SET @columnname3 = 'take_profit_1';
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname3)
  ) > 0,
  "SELECT 'Column take_profit_1 already exists.' AS result;",
  CONCAT(
    "ALTER TABLE `", @tablename, "` ",
    "ADD COLUMN `", @columnname3, "` DECIMAL(20, 8) NULL ",
    "COMMENT '第一目标止盈价（用于展示与分步止盈）';"
  )
));
PREPARE alterIfNotExists3 FROM @preparedStatement3;
EXECUTE alterIfNotExists3;
DEALLOCATE PREPARE alterIfNotExists3;

-- 4) take_profit_2（分步止盈2）
SET @columnname4 = 'take_profit_2';
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname4)
  ) > 0,
  "SELECT 'Column take_profit_2 already exists.' AS result;",
  CONCAT(
    "ALTER TABLE `", @tablename, "` ",
    "ADD COLUMN `", @columnname4, "` DECIMAL(20, 8) NULL ",
    "COMMENT '第二目标止盈价（用于展示与分步止盈）';"
  )
));
PREPARE alterIfNotExists4 FROM @preparedStatement4;
EXECUTE alterIfNotExists4;
DEALLOCATE PREPARE alterIfNotExists4;

-- 5) atr（开仓时使用的ATR值）
SET @columnname5 = 'atr';
SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @columnname5)
  ) > 0,
  "SELECT 'Column atr already exists.' AS result;",
  CONCAT(
    "ALTER TABLE `", @tablename, "` ",
    "ADD COLUMN `", @columnname5, "` DECIMAL(20, 8) NULL ",
    "COMMENT '开仓时使用的ATR（用于展示动态止损止盈依据）';"
  )
));
PREPARE alterIfNotExists5 FROM @preparedStatement5;
EXECUTE alterIfNotExists5;
DEALLOCATE PREPARE alterIfNotExists5;

SELECT 'Migration completed: Added notional_usdt, margin_usdt, take_profit_1, take_profit_2, atr to trades table.' AS result;

