-- 修复「确认收货」卡在"确认身份"：shop_order 缺少 id_code / address 两列。
-- real_name 已存在(real_name 重复报错 1060 已经证实)，无需再加。
-- 后端 OCR 识别到的身份证号无法落库（MyBatis-Plus updateById 会因列不存在而报错）。
-- 部署后端新代码【前】必须先在 yunxinwei 库执行本脚本。
-- 本脚本幂等安全：基于 INFORMATION_SCHEMA 判断列是否存在再决定是否执行 ALTER；
-- 即使重复跑也不会报 1060 Duplicate column。

-- 1) 先看一眼现状，便于核对
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'shop_order'
  AND COLUMN_NAME IN ('id_code', 'real_name', 'address', 'comments', 'receipt_phone')
ORDER BY COLUMN_NAME;

-- 2) 条件加列：id_code
SET @sql_id_code = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shop_order' AND COLUMN_NAME = 'id_code') = 0,
  'ALTER TABLE `shop_order` ADD COLUMN `id_code` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''身份证号'' AFTER `comments`',
  'SELECT ''id_code 已存在,跳过'' AS info'
);
PREPARE stmt FROM @sql_id_code; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3) 条件加列：address
SET @sql_address = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shop_order' AND COLUMN_NAME = 'address') = 0,
  'ALTER TABLE `shop_order` ADD COLUMN `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''详细地址'' AFTER `real_name`',
  'SELECT ''address 已存在,跳过'' AS info'
);
PREPARE stmt FROM @sql_address; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4) 验证最终结果
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'shop_order'
  AND COLUMN_NAME IN ('id_code', 'real_name', 'address')
ORDER BY COLUMN_NAME;