-- VIN码充电改造（order库）：订单表新增授权/锁定/优惠快照列 + 计费明细新增优惠列
-- 目的：
--   1) t_vin_charging_order 新增 max_end_time（授权截止时间，监控回传超时提前 1 分钟主动停充）、locked_amount（申请锁定金额）、
--      account_mode（账户模式快照）、discount_electrovalence / discount_service_charge（订单优惠合计）
--   2) t_vin_charging_order_accounting_strategy 新增 period_original_electric_price / period_discount_electric_price /
--      period_discount_service_price（时段原始电费与优惠电费、优惠服务费）
-- 创建日期: 2026-09-29
-- 目标库：charging_pile_order（两表均为分片表：TBPARTITION BY MOD_HASH(分片键) TBPARTITIONS 20）
-- 执行方式：
--   1) 首选「小节 A」经数据库代理（MyCat 8066）执行，代理会把 DDL 广播到全部物理分表；
--   2) 代理不支持分片表 DDL 时，改用「小节 B」直连后端 MySQL 实例执行（存储过程，逐物理分表，幂等）；
--   3) 最后执行「小节 C」回查校验。
-- 幂等说明：小节 A 为裸 ALTER，重复执行会报 Duplicate column，忽略即可（以小节 C 回查为准）；
--           小节 B 先查 information_schema 再变更，可重复执行。

-- ============ 小节 A：经代理执行（推荐） ============
USE `charging_pile_order`;

ALTER TABLE `t_vin_charging_order`
  ADD COLUMN `max_end_time` datetime DEFAULT NULL COMMENT '最大充电结束时间（授权截止时间，全天授权为空）';

ALTER TABLE `t_vin_charging_order`
  ADD COLUMN `locked_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '申请时锁定金额（共享=企业锁定额，分配=VIN锁定额）';

ALTER TABLE `t_vin_charging_order`
  ADD COLUMN `account_mode` tinyint DEFAULT NULL COMMENT '账户模式快照（1=共享，2=分配）';

ALTER TABLE `t_vin_charging_order`
  ADD COLUMN `discount_electrovalence` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '优惠电费合计（原始电费-最终电费）';

ALTER TABLE `t_vin_charging_order`
  ADD COLUMN `discount_service_charge` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '优惠服务费合计（原始服务费-最终服务费）';

ALTER TABLE `t_vin_charging_order_accounting_strategy`
  ADD COLUMN `period_original_electric_price` decimal(10,4) DEFAULT NULL COMMENT '时段原始电费（未优惠）';

ALTER TABLE `t_vin_charging_order_accounting_strategy`
  ADD COLUMN `period_discount_electric_price` decimal(10,4) DEFAULT NULL COMMENT '时段优惠电费（原始-最终）';

ALTER TABLE `t_vin_charging_order_accounting_strategy`
  ADD COLUMN `period_discount_service_price` decimal(10,4) DEFAULT NULL COMMENT '时段优惠服务费（原始-最终）';

-- ============ 小节 B：直连后端 MySQL 实例执行（代理不可用时分表执行，幂等） ============
USE `charging_pile_order`;

DROP PROCEDURE IF EXISTS `add_vin_charge_lock_columns`;
DELIMITER $$
CREATE PROCEDURE `add_vin_charge_lock_columns`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE target_table VARCHAR(64);
    DECLARE column_exists INT DEFAULT 0;

    -- 覆盖逻辑表（若物理存在）与 TBPARTITION 物理分表
    DECLARE table_cursor CURSOR FOR
        SELECT t.table_name
        FROM information_schema.tables t
        WHERE t.table_schema = DATABASE()
          AND (t.table_name REGEXP '^t_vin_charging_order(_[0-9]+)?$'
            OR t.table_name REGEXP '^t_vin_charging_order_accounting_strategy(_[0-9]+)?$');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN table_cursor;
    table_loop: LOOP
        FETCH table_cursor INTO target_table;
        IF done = 1 THEN
            LEAVE table_loop;
        END IF;

        -- ---------- t_vin_charging_order 系列 ----------
        IF target_table REGEXP '^t_vin_charging_order(_[0-9]+)?$' THEN
            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'max_end_time';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `max_end_time` datetime DEFAULT NULL COMMENT ''最大充电结束时间（授权截止时间，全天授权为空）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;

            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'locked_amount';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `locked_amount` decimal(12,2) NOT NULL DEFAULT ''0.00'' COMMENT ''申请时锁定金额（共享=企业锁定额，分配=VIN锁定额）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;

            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'account_mode';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `account_mode` tinyint DEFAULT NULL COMMENT ''账户模式快照（1=共享，2=分配）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;

            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'discount_electrovalence';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `discount_electrovalence` decimal(12,2) NOT NULL DEFAULT ''0.00'' COMMENT ''优惠电费合计（原始电费-最终电费）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;

            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'discount_service_charge';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `discount_service_charge` decimal(12,2) NOT NULL DEFAULT ''0.00'' COMMENT ''优惠服务费合计（原始服务费-最终服务费）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;

        -- ---------- t_vin_charging_order_accounting_strategy 系列 ----------
        IF target_table REGEXP '^t_vin_charging_order_accounting_strategy(_[0-9]+)?$' THEN
            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'period_original_electric_price';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `period_original_electric_price` decimal(10,4) DEFAULT NULL COMMENT ''时段原始电费（未优惠）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;

            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'period_discount_electric_price';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `period_discount_electric_price` decimal(10,4) DEFAULT NULL COMMENT ''时段优惠电费（原始-最终）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;

            SELECT COUNT(*) INTO column_exists
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = target_table
              AND column_name = 'period_discount_service_price';
            IF column_exists = 0 THEN
                SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `period_discount_service_price` decimal(10,4) DEFAULT NULL COMMENT ''时段优惠服务费（原始-最终）''');
                PREPARE stmt FROM @ddl;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;
    END LOOP;
    CLOSE table_cursor;
END$$
DELIMITER ;

CALL `add_vin_charge_lock_columns`();
DROP PROCEDURE `add_vin_charge_lock_columns`;

-- ============ 小节 C：回查校验 ============
SELECT t.table_name, c.column_name, c.column_type, c.column_comment
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
WHERE t.table_schema = DATABASE()
  AND t.table_name REGEXP '^t_vin_charging_order(_[0-9]+)?$'
  AND c.column_name IN ('max_end_time', 'locked_amount', 'account_mode', 'discount_electrovalence', 'discount_service_charge')
ORDER BY t.table_name, c.column_name;

SELECT t.table_name, c.column_name, c.column_type, c.column_comment
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
WHERE t.table_schema = DATABASE()
  AND t.table_name REGEXP '^t_vin_charging_order_accounting_strategy(_[0-9]+)?$'
  AND c.column_name IN ('period_original_electric_price', 'period_discount_electric_price', 'period_discount_service_price')
ORDER BY t.table_name, c.column_name;

SELECT COUNT(DISTINCT t.table_name) AS order_shard_count,
       SUM(CASE WHEN c.column_name = 'max_end_time' THEN 1 ELSE 0 END) AS max_end_time_columns,
       SUM(CASE WHEN c.column_name = 'locked_amount' THEN 1 ELSE 0 END) AS locked_amount_columns,
       SUM(CASE WHEN c.column_name = 'account_mode' THEN 1 ELSE 0 END) AS account_mode_columns,
       SUM(CASE WHEN c.column_name = 'discount_electrovalence' THEN 1 ELSE 0 END) AS discount_electrovalence_columns,
       SUM(CASE WHEN c.column_name = 'discount_service_charge' THEN 1 ELSE 0 END) AS discount_service_charge_columns
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
WHERE t.table_schema = DATABASE()
  AND t.table_name REGEXP '^t_vin_charging_order(_[0-9]+)?$'
  AND c.column_name IN ('max_end_time', 'locked_amount', 'account_mode', 'discount_electrovalence', 'discount_service_charge');