-- 充电桩订单（t_charging_order）新增「所属公司 / 所属车队」快照列
-- 目的：充电桩订单列表/汇总支持按公司、车队筛选与展示（订单表冗余归属列 + SQL 直接筛选）
-- 目标库：charging_pile_order（表 t_charging_order，TBPARTITION BY MOD_HASH(app_user_id) TBPARTITIONS 20）
-- 数据来源：白名单用户充电建单时取 t_app_user_whitelist 的 company_id / fleet_id 快照；
--           历史白名单订单回填见同目录 20260929_charging_order_company_fleet_backfill.sql
-- 执行方式：
--   1) 首选「小节 A」经数据库代理（MyCat 8066）执行，代理会把 DDL 广播到全部物理分表；
--   2) 代理不支持分片表 DDL 时，改用「小节 B」直连后端 MySQL 实例执行（存储过程，逐物理分表，幂等）；
--   3) 最后执行「小节 C」回查校验。
-- 幂等说明：小节 A 为裸 ALTER，重复执行会报 Duplicate column，忽略即可（以小节 C 回查为准）；
--           小节 B 先查 information_schema 再变更，可重复执行。
-- 部署提示：必须先执行本脚本再发布订单服务代码；否则列表带公司/车队筛选会报 Column 'company_id' not found。

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

ALTER TABLE `t_charging_order`
  ADD COLUMN `company_id` int DEFAULT NULL COMMENT '所属公司ID（建单时白名单用户归属快照，关联t_group_company.id）';

ALTER TABLE `t_charging_order`
  ADD COLUMN `fleet_id` int DEFAULT NULL COMMENT '所属车队ID（建单时白名单用户归属快照，关联t_fleet.id）';

ALTER TABLE `t_charging_order` ADD KEY `idx_company_id` (`company_id`);
ALTER TABLE `t_charging_order` ADD KEY `idx_fleet_id` (`fleet_id`);

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

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

    -- 覆盖逻辑表（若物理存在）与 TBPARTITION 物理分表 t_charging_order_0..N
    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_charging_order(_[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;

        SELECT COUNT(*) INTO column_exists
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = target_table
          AND column_name = 'company_id';
        IF column_exists = 0 THEN
            SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `company_id` int DEFAULT NULL COMMENT ''所属公司ID（建单时白名单用户归属快照，关联t_group_company.id）''');
            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 = 'fleet_id';
        IF column_exists = 0 THEN
            SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD COLUMN `fleet_id` int DEFAULT NULL COMMENT ''所属车队ID（建单时白名单用户归属快照，关联t_fleet.id）''');
            PREPARE stmt FROM @ddl;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SELECT COUNT(*) INTO index_exists
        FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = target_table
          AND index_name = 'idx_company_id';
        IF index_exists = 0 THEN
            SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD KEY `idx_company_id` (`company_id`)');
            PREPARE stmt FROM @ddl;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SELECT COUNT(*) INTO index_exists
        FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = target_table
          AND index_name = 'idx_fleet_id';
        IF index_exists = 0 THEN
            SET @ddl = CONCAT('ALTER TABLE `', target_table, '` ADD KEY `idx_fleet_id` (`fleet_id`)');
            PREPARE stmt FROM @ddl;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END LOOP;
    CLOSE table_cursor;
END$$
DELIMITER ;

CALL `add_charging_order_company_fleet`();
DROP PROCEDURE `add_charging_order_company_fleet`;

-- ============ 小节 C：回查校验（应看到每个物理分表都存在 company_id / fleet_id 两列） ============
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_charging_order(_[0-9]+)?$'
  AND c.column_name IN ('company_id', 'fleet_id')
ORDER BY t.table_name, c.column_name;

SELECT COUNT(DISTINCT t.table_name) AS shard_count,
       SUM(CASE WHEN c.column_name = 'company_id' THEN 1 ELSE 0 END) AS company_id_columns,
       SUM(CASE WHEN c.column_name = 'fleet_id' THEN 1 ELSE 0 END) AS fleet_id_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_charging_order(_[0-9]+)?$'
  AND c.column_name IN ('company_id', 'fleet_id');