-- VIN 车辆绑定字段统一改名：appp_user_id -> app_user_id（含索引与注释）
-- 背景：历史拼写错误（三个 p）已废弃，全链路统一为 app_user_id。
-- 目标库：charging_pile_account.t_vin_management
-- 适用：MySQL（含 Mycat 代理下的 MySQL 8 后端）。Mycat 对 PREPARE/INFORMATION_SCHEMA 变量支持不稳定时，
--       可直接执行下方「直接 DDL」小节并回查。
-- 脚本幂等：列/索引若已是 app_user_id 则自动跳过。

USE `charging_pile_account`;

SET @has_old_column := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND COLUMN_NAME = 'appp_user_id'
);
SET @has_new_column := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND COLUMN_NAME = 'app_user_id'
);
SET @rename_column_sql := IF(
  @has_old_column = 1 AND @has_new_column = 0,
  'ALTER TABLE `t_vin_management` CHANGE COLUMN `appp_user_id` `app_user_id` bigint NULL DEFAULT NULL COMMENT ''绑定的小程序用户ID'' AFTER `del_flag`',
  'SELECT 1'
);
PREPARE rename_column_stmt FROM @rename_column_sql;
EXECUTE rename_column_stmt;
DEALLOCATE PREPARE rename_column_stmt;

SET @has_old_index := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND INDEX_NAME = 'idx_appp_user_id'
);
SET @has_new_index := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management' AND INDEX_NAME = 'idx_app_user_id'
);
-- 旧索引改名（MySQL 8 / Mycat 透传不支持时改为 DROP+ADD 两条直接 DDL 手动执行）
SET @rename_index_sql := IF(
  @has_old_index = 1 AND @has_new_index = 0,
  'ALTER TABLE `t_vin_management` RENAME INDEX `idx_appp_user_id` TO `idx_app_user_id`',
  'SELECT 1'
);
PREPARE rename_index_stmt FROM @rename_index_sql;
EXECUTE rename_index_stmt;
DEALLOCATE PREPARE rename_index_stmt;

-- 兜底：新列已存在但缺少索引时补建（兼容旧环境索引名不是 idx_app_user_id 的情况）
SET @ensure_index_sql := IF(
  @has_new_column = 1 AND @has_new_index = 0,
  'ALTER TABLE `t_vin_management` ADD INDEX `idx_app_user_id` (`app_user_id`)',
  'SELECT 1'
);
PREPARE ensure_index_stmt FROM @ensure_index_sql;
EXECUTE ensure_index_stmt;
DEALLOCATE PREPARE ensure_index_stmt;

-- 注释兜底：确保 app_user_id 注释为「绑定的小程序用户ID」（无「三个p」字样）
SET @comment_exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_vin_management'
    AND COLUMN_NAME = 'app_user_id' AND COLUMN_COMMENT LIKE '%三个p%'
);
SET @fix_comment_sql := IF(
  @has_new_column = 1 AND @comment_exists = 1,
  'ALTER TABLE `t_vin_management` MODIFY COLUMN `app_user_id` bigint NULL DEFAULT NULL COMMENT ''绑定的小程序用户ID'' AFTER `del_flag`',
  'SELECT 1'
);
PREPARE fix_comment_stmt FROM @fix_comment_sql;
EXECUTE fix_comment_stmt;
DEALLOCATE PREPARE fix_comment_stmt;

-- ========== 直接 DDL 小节（Mycat 下如上面 PREPARE 报错，可按序手工执行以下三条） ==========
-- ALTER TABLE `charging_pile_account`.`t_vin_management`
--   CHANGE COLUMN `appp_user_id` `app_user_id` bigint NULL DEFAULT NULL COMMENT '绑定的小程序用户ID' AFTER `del_flag`;
-- ALTER TABLE `charging_pile_account`.`t_vin_management`
--   ADD INDEX `idx_app_user_id` (`app_user_id`);