-- 刷卡充电 V0.2 定稿：卡片密码配置、充电期间锁定、订单结算幂等扣款（允许负余额）
-- 目标库：charging_pile_account（t_charging_card、t_charging_card_balance_record）
-- 适用：MySQL（含 Mycat 代理下的 MySQL 8 后端）。Mycat 对 PREPARE/INFORMATION_SCHEMA 变量支持不稳定时，
--       可直接执行下方「直接 DDL 小节」并回查。
-- 脚本幂等：字段/索引已存在则自动跳过，可重复执行。

USE `charging_pile_account`;

-- ============ 1. t_charging_card 新增 password / charging_flag / charging_order_code ============
SET @has_password_col := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card' AND COLUMN_NAME = 'password'
);
SET @add_password_sql := IF(
  @has_password_col = 0,
  'ALTER TABLE `t_charging_card` ADD COLUMN `password` varchar(100) DEFAULT NULL COMMENT ''卡密码(BCrypt密文,空=未配置不校验)'' AFTER `card_no`',
  'SELECT 1'
);
PREPARE add_password_stmt FROM @add_password_sql;
EXECUTE add_password_stmt;
DEALLOCATE PREPARE add_password_stmt;

SET @has_charging_flag_col := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card' AND COLUMN_NAME = 'charging_flag'
);
SET @add_charging_flag_sql := IF(
  @has_charging_flag_col = 0,
  'ALTER TABLE `t_charging_card` ADD COLUMN `charging_flag` tinyint NOT NULL DEFAULT 0 COMMENT ''充电锁定:0未充电,1充电中锁定'' AFTER `status`',
  'SELECT 1'
);
PREPARE add_charging_flag_stmt FROM @add_charging_flag_sql;
EXECUTE add_charging_flag_stmt;
DEALLOCATE PREPARE add_charging_flag_stmt;

SET @has_charging_order_code_col := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card' AND COLUMN_NAME = 'charging_order_code'
);
SET @add_charging_order_code_sql := IF(
  @has_charging_order_code_col = 0,
  'ALTER TABLE `t_charging_card` ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT ''充电锁定订单号'' AFTER `charging_flag`',
  'SELECT 1'
);
PREPARE add_charging_order_code_stmt FROM @add_charging_order_code_sql;
EXECUTE add_charging_order_code_stmt;
DEALLOCATE PREPARE add_charging_order_code_stmt;

-- ============ 2. t_charging_card_balance_record 新增充电订单号（扣款幂等键）与唯一索引 ============
SET @has_record_order_col := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card_balance_record' AND COLUMN_NAME = 'charging_order_code'
);
SET @add_record_order_sql := IF(
  @has_record_order_col = 0,
  'ALTER TABLE `t_charging_card_balance_record` ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT ''关联订单号(刷卡充电订单结算扣款,幂等键)'' AFTER `remark`',
  'SELECT 1'
);
PREPARE add_record_order_stmt FROM @add_record_order_sql;
EXECUTE add_record_order_stmt;
DEALLOCATE PREPARE add_record_order_stmt;

SET @has_record_uk := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = 'charging_pile_account' AND TABLE_NAME = 't_charging_card_balance_record' AND INDEX_NAME = 'uk_order_code'
);
SET @add_record_uk_sql := IF(
  @has_record_uk = 0,
  'ALTER TABLE `t_charging_card_balance_record` ADD UNIQUE KEY `uk_order_code` (`charging_order_code`)',
  'SELECT 1'
);
PREPARE add_record_uk_stmt FROM @add_record_uk_sql;
EXECUTE add_record_uk_stmt;
DEALLOCATE PREPARE add_record_uk_stmt;

-- ========== 直接 DDL 小节（Mycat 下如上面 PREPARE 报错，可按序手工执行以下四条） ==========
-- ALTER TABLE `charging_pile_account`.`t_charging_card`
--   ADD COLUMN `password` varchar(100) DEFAULT NULL COMMENT '卡密码(BCrypt密文,空=未配置不校验)' AFTER `card_no`;
-- ALTER TABLE `charging_pile_account`.`t_charging_card`
--   ADD COLUMN `charging_flag` tinyint NOT NULL DEFAULT 0 COMMENT '充电锁定:0未充电,1充电中锁定' AFTER `status`;
-- ALTER TABLE `charging_pile_account`.`t_charging_card`
--   ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT '充电锁定订单号' AFTER `charging_flag`;
-- ALTER TABLE `charging_pile_account`.`t_charging_card_balance_record`
--   ADD COLUMN `charging_order_code` varchar(64) DEFAULT NULL COMMENT '关联订单号(刷卡充电订单结算扣款,幂等键)' AFTER `remark`;
-- ALTER TABLE `charging_pile_account`.`t_charging_card_balance_record`
--   ADD UNIQUE KEY `uk_order_code` (`charging_order_code`);