BMS/bms-back/sql/price/label_package.sql

-- 打单套餐一键脚本（可重复执行）
-- 合并自：pay_product_term.sql + label_package.sql + label_package_create_menu.sql
-- 含：应付有效期表、打单表结构变更、打单/应付相关菜单权限
SET NAMES utf8mb4;

-- ========== 一、pay_product_term 建表 + 存量回填 ==========

CREATE TABLE IF NOT EXISTS `pay_product_term` (
  `term_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '有效期ID',
  `dict_value` varchar(100) NOT NULL COMMENT '产品字典值，同一产品的各版本归在一起',
  `product_id` bigint(20) NOT NULL COMMENT '这一版价格',
  `version_no` int(11) NOT NULL DEFAULT 1 COMMENT '版本号',
  `effective_start` date DEFAULT NULL COMMENT '生效开始',
  `effective_end` date DEFAULT NULL COMMENT '生效结束，空表示不限',
  `file_path` varchar(500) DEFAULT NULL COMMENT '这一版源文件OSS地址，oss://bucket/key',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '0待生效 1生效 2已失效',
  `created_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`term_id`),
  UNIQUE KEY `uk_pay_term_product` (`product_id`),
  KEY `idx_pay_term_dict` (`dict_value`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付产品价格有效期';

-- 已有模板补一条生效记录，不覆盖后来手工改过的有效期
INSERT INTO pay_product_term (
  dict_value, product_id, version_no, effective_start, status, created_by, create_time
)
SELECT ifnull(nullif(dict_value, ''), product_name), product_id, ifnull(version_no, 1), effective_date, '0',
       ifnull(created_by, ''), ifnull(create_time, sysdate())
FROM pay_product p
WHERE (p.channel_id IS NULL OR p.channel_id = '')
  AND NOT EXISTS (SELECT 1 FROM pay_product_term t WHERE t.product_id = p.product_id);

-- ========== 二、打单相关表结构变更 ==========

-- 0) pay_product_term.status：旧 0生效/1失效 → 新 0待生效/1生效/2已失效
-- 仅当列注释仍是旧语义时迁移（可重复执行）
SET @old_status_comment := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND COLUMN_NAME = 'status'
    AND COLUMN_COMMENT LIKE '%0生效%1失效%'
);
SET @sql := IF(@old_status_comment > 0,
  'UPDATE pay_product_term SET status = ''2'' WHERE status = ''1''',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(@old_status_comment > 0,
  'UPDATE pay_product_term SET status = ''1'' WHERE status = ''0''',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE pay_product_term
  MODIFY COLUMN status char(1) NOT NULL DEFAULT '0' COMMENT '0待生效 1生效 2已失效';

-- 1) prc_price_set.set_category 注释增加 LABEL
ALTER TABLE prc_price_set
  MODIFY COLUMN set_category varchar(20) NOT NULL DEFAULT 'PACKAGE'
  COMMENT '分类 PACKAGE=普通套餐 EXCLUSIVE=专属报价 LABEL=打单套餐';

-- 2) pay_product_term.price_set_id
SET @exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND COLUMN_NAME = 'price_set_id'
);
SET @sql := IF(@exists = 0,
  'ALTER TABLE pay_product_term
     ADD COLUMN price_set_id bigint(20) DEFAULT NULL
       COMMENT ''打单套餐ID，对应 prc_price_set.price_set_id；应付公共模板为空''
     AFTER product_id,
     ADD KEY idx_pay_term_price_set (price_set_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3) 去掉一产品一条 term 的唯一约束，支持一产品多周期
SET @uk := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND INDEX_NAME = 'uk_pay_term_product'
);
SET @sql := IF(@uk > 0, 'ALTER TABLE pay_product_term DROP INDEX uk_pay_term_product', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx1 := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND INDEX_NAME = 'idx_pay_term_set_product_time'
);
SET @sql := IF(@idx1 = 0,
  'ALTER TABLE pay_product_term ADD KEY idx_pay_term_set_product_time (price_set_id, product_id, effective_start, effective_end)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx2 := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND INDEX_NAME = 'idx_pay_term_product_time'
);
SET @sql := IF(@idx2 = 0,
  'ALTER TABLE pay_product_term ADD KEY idx_pay_term_product_time (product_id, effective_start, effective_end)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4) 明细表增加 term_id
SET @exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_weight_band' AND COLUMN_NAME = 'term_id'
);
SET @sql := IF(@exists = 0,
  'ALTER TABLE pay_weight_band
     ADD COLUMN term_id bigint(20) DEFAULT NULL
       COMMENT ''价卡版本周期ID，对应 pay_product_term.term_id；与 product_id 组成明细唯一维度''
     AFTER product_id,
     ADD KEY idx_pay_weight_product_term (product_id, term_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_surcharge' AND COLUMN_NAME = 'term_id'
);
SET @sql := IF(@exists = 0,
  'ALTER TABLE pay_surcharge
     ADD COLUMN term_id bigint(20) DEFAULT NULL
       COMMENT ''价卡版本周期ID，对应 pay_product_term.term_id；与 product_id 组成明细唯一维度''
     AFTER product_id,
     ADD KEY idx_pay_surcharge_product_term (product_id, term_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_extra_line' AND COLUMN_NAME = 'term_id'
);
SET @sql := IF(@exists = 0,
  'ALTER TABLE pay_extra_line
     ADD COLUMN term_id bigint(20) DEFAULT NULL
       COMMENT ''价卡版本周期ID，对应 pay_product_term.term_id；与 product_id 组成明细唯一维度''
     AFTER product_id,
     ADD KEY idx_pay_extra_product_term (product_id, term_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5) 存量明细回填 term_id（一产品多 term 时取最新一条）
UPDATE pay_weight_band b
INNER JOIN (
  SELECT t1.product_id, t1.term_id
  FROM pay_product_term t1
  INNER JOIN (
    SELECT product_id, MAX(term_id) AS term_id
    FROM pay_product_term
    GROUP BY product_id
  ) t2 ON t2.product_id = t1.product_id AND t2.term_id = t1.term_id
) t ON t.product_id = b.product_id
SET b.term_id = t.term_id
WHERE b.term_id IS NULL;

UPDATE pay_surcharge s
INNER JOIN (
  SELECT t1.product_id, t1.term_id
  FROM pay_product_term t1
  INNER JOIN (
    SELECT product_id, MAX(term_id) AS term_id
    FROM pay_product_term
    GROUP BY product_id
  ) t2 ON t2.product_id = t1.product_id AND t2.term_id = t1.term_id
) t ON t.product_id = s.product_id
SET s.term_id = t.term_id
WHERE s.term_id IS NULL;

UPDATE pay_extra_line e
INNER JOIN (
  SELECT t1.product_id, t1.term_id
  FROM pay_product_term t1
  INNER JOIN (
    SELECT product_id, MAX(term_id) AS term_id
    FROM pay_product_term
    GROUP BY product_id
  ) t2 ON t2.product_id = t1.product_id AND t2.term_id = t1.term_id
) t ON t.product_id = e.product_id
SET e.term_id = t.term_id
WHERE e.term_id IS NULL;

-- 6) 删除渠道映射上的套餐字段
SET @exists := (
  SELECT COUNT(1) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'srv_channel_product_map'
    AND COLUMN_NAME = 'price_set_id'
);
SET @sql := IF(@exists > 0,
  'ALTER TABLE srv_channel_product_map DROP INDEX idx_channel_product_map_price_set, DROP COLUMN price_set_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ========== 三、应付配置菜单（price:pay:edit） ==========

SET @menuId := (SELECT menu_id FROM sys_menu WHERE perms = 'price:pay:list' LIMIT 1);

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time)
SELECT '应付配置修改', @menuId, 5, '', '', 1, 0, 'F', '0', '0', 'price:pay:edit', '#', 'admin', sysdate()
WHERE @menuId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'price:pay:edit');

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT DISTINCT rm.role_id, m.menu_id
FROM sys_role_menu rm
JOIN sys_menu src ON src.menu_id = rm.menu_id AND src.perms = 'price:pay:list'
JOIN sys_menu m ON m.perms = 'price:pay:edit'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_role_menu x WHERE x.role_id = rm.role_id AND x.menu_id = m.menu_id
);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, m.menu_id
FROM sys_menu m
WHERE m.perms = 'price:pay:edit'
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu x WHERE x.role_id = 1 AND x.menu_id = m.menu_id
  );

-- ========== 四、打单套餐菜单权限（挂在套餐管理下） ==========

SET @pkgParentId := (
  SELECT menu_id FROM sys_menu
  WHERE menu_id = 2200
     OR (menu_name = N'套餐管理' AND parent_id = 0 AND menu_type = 'M')
  ORDER BY CASE WHEN menu_id = 2200 THEN 0 ELSE 1 END, menu_id
  LIMIT 1
);

-- 列表
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2620, N'打单套餐', @pkgParentId, 3, 'label', 'price/label/labelPackage', '', 'LabelPackage', 1, 0, 'C', '0', '0', 'price:label:list', 'list', 'admin', sysdate(), N'打单套餐列表'
FROM DUAL WHERE @pkgParentId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2620 OR perms = 'price:label:list');

UPDATE sys_menu
SET menu_name = N'打单套餐',
    parent_id = @pkgParentId,
    order_num = 3,
    path = 'label',
    component = 'price/label/labelPackage',
    route_name = 'LabelPackage',
    perms = 'price:label:list',
    remark = N'打单套餐列表',
    update_by = 'admin',
    update_time = sysdate()
WHERE @pkgParentId IS NOT NULL AND (menu_id = 2620 OR perms = 'price:label:list');

-- 隐藏新建页
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2628, N'打单套餐新建', @pkgParentId, 7, 'labelCreate', 'price/label/create', '', 'LabelPackageCreate', 1, 0, 'C', '1', '0', 'price:label:add', '#', 'admin', sysdate(), N'打单套餐新建（基本信息+Excel导入）'
FROM DUAL WHERE @pkgParentId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2628 OR path = 'labelCreate');

UPDATE sys_menu
SET menu_name = N'打单套餐新建',
    parent_id = @pkgParentId,
    order_num = 7,
    path = 'labelCreate',
    component = 'price/label/create',
    route_name = 'LabelPackageCreate',
    perms = 'price:label:add',
    visible = '1',
    remark = N'打单套餐新建（基本信息+Excel导入）',
    update_by = 'admin',
    update_time = sysdate()
WHERE @pkgParentId IS NOT NULL AND (menu_id = 2628 OR path = 'labelCreate');

-- 隐藏详情页
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2627, N'打单套餐详情', @pkgParentId, 8, 'labelDetail', 'price/label/detail', '', 'LabelPackageDetail', 1, 0, 'C', '1', '0', 'price:label:query', '#', 'admin', sysdate(), N'打单套餐产品明细'
FROM DUAL WHERE @pkgParentId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2627 OR path = 'labelDetail');

UPDATE sys_menu
SET menu_name = N'打单套餐详情',
    parent_id = @pkgParentId,
    order_num = 8,
    path = 'labelDetail',
    component = 'price/label/detail',
    route_name = 'LabelPackageDetail',
    perms = 'price:label:query',
    visible = '1',
    remark = N'打单套餐产品明细',
    update_by = 'admin',
    update_time = sysdate()
WHERE @pkgParentId IS NOT NULL AND (menu_id = 2627 OR path = 'labelDetail');

-- 按钮
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2621, N'查询', 2620, 1, '', NULL, 1, 0, 'F', '0', '0', 'price:label:query', '#', 'admin', sysdate(), N''
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2621 OR (parent_id = 2620 AND perms = 'price:label:query' AND menu_type = 'F'));

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2622, N'新增', 2620, 2, '', NULL, 1, 0, 'F', '0', '0', 'price:label:add', '#', 'admin', sysdate(), N''
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2622 OR (parent_id = 2620 AND perms = 'price:label:add' AND menu_type = 'F'));

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2623, N'修改', 2620, 3, '', NULL, 1, 0, 'F', '0', '0', 'price:label:edit', '#', 'admin', sysdate(), N''
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2623 OR (parent_id = 2620 AND perms = 'price:label:edit' AND menu_type = 'F'));

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2624, N'删除', 2620, 4, '', NULL, 1, 0, 'F', '0', '0', 'price:label:remove', '#', 'admin', sysdate(), N''
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2624 OR (parent_id = 2620 AND perms = 'price:label:remove' AND menu_type = 'F'));

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2625, N'导入', 2620, 5, '', NULL, 1, 0, 'F', '0', '0', 'price:label:import', '#', 'admin', sysdate(), N''
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2625 OR (parent_id = 2620 AND perms = 'price:label:import' AND menu_type = 'F'));

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT 2626, N'启停', 2620, 6, '', NULL, 1, 0, 'F', '0', '0', 'price:label:audit', '#', 'admin', sysdate(), N''
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2626 OR (parent_id = 2620 AND perms = 'price:label:audit' AND menu_type = 'F'));

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, m.menu_id FROM (
  SELECT 2620 AS menu_id UNION SELECT 2621 UNION SELECT 2622
  UNION SELECT 2623 UNION SELECT 2624 UNION SELECT 2625 UNION SELECT 2626
  UNION SELECT 2627 UNION SELECT 2628
) m
WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = 1 AND rm.menu_id = m.menu_id);

-- ========== 五、打单计费热路径索引（可重复执行） ==========
-- 命中顺序：客户绑套餐 → 渠道 AR 映产品 → term 按套餐+产品+日期 → 明细按 term_id

-- 5.1 客户按 customer_code 取绑定（selectEffectiveByCustomerCode）
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'prc_customer_price_set'
    AND INDEX_NAME = 'idx_cps_customer_code'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE prc_customer_price_set ADD KEY idx_cps_customer_code (customer_code)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.2 套餐壳按分类+状态（列表/启停过滤；计费按 PK 取，顺带加速 LABEL 筛）
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'prc_price_set'
    AND INDEX_NAME = 'idx_ps_category_status'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE prc_price_set ADD KEY idx_ps_category_status (set_category, status)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.3 渠道 AR 映射：uk_channel_pay_type(channel_id, pay_type) 已存在，补 product_id 侧已有；确保 channel_code 索引
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'srv_channel_product_map'
    AND INDEX_NAME = 'idx_channel_product_map_channel_code'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE srv_channel_product_map ADD KEY idx_channel_product_map_channel_code (channel_code)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.4 term 计费命中：套餐+产品+生效窗（selectTermIdBySetProductAt）— 已有则跳过
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND INDEX_NAME = 'idx_pay_term_set_product_time'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE pay_product_term ADD KEY idx_pay_term_set_product_time (price_set_id, product_id, effective_start, effective_end)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.5 term 按产品查是否挂套餐（selectLabelProductIdByDict 的 exists）
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product_term'
    AND INDEX_NAME = 'idx_pay_term_product_set'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE pay_product_term ADD KEY idx_pay_term_product_set (product_id, price_set_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.6 打单产品字典命中（dict_value + AR）
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_product'
    AND INDEX_NAME = 'idx_pay_product_dict_type'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE pay_product ADD KEY idx_pay_product_dict_type (dict_value, product_type)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.7 明细按 term_id 装载（select*ByTerm）；原索引以 product_id 领先，补 term 单列
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_weight_band'
    AND INDEX_NAME = 'idx_pay_weight_term'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE pay_weight_band ADD KEY idx_pay_weight_term (term_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_surcharge'
    AND INDEX_NAME = 'idx_pay_surcharge_term'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE pay_surcharge ADD KEY idx_pay_surcharge_term (term_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pay_extra_line'
    AND INDEX_NAME = 'idx_pay_extra_term'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE pay_extra_line ADD KEY idx_pay_extra_term (term_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5.8 订单扩展按渠道反查（可选批量重计时）；bill_id / order_no 已有唯一键
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'prc_order_bill_ext'
    AND INDEX_NAME = 'idx_order_bill_ext_channel'
);
SET @sql := IF(@idx = 0,
  'ALTER TABLE prc_order_bill_ext ADD KEY idx_order_bill_ext_channel (product_channel_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;