byzs-bjdx/sql/mysql/20261010_tenant_ai_point.sql

-- 租户共享 AI 积分：MySQL 8.0+
-- 执行前请备份数据库；默认给所有现有租户创建 0 积分账户。

CREATE TABLE IF NOT EXISTS `system_tenant_ai_point` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `tenant_id` bigint NOT NULL COMMENT '租户编号',
  `balance` int NOT NULL DEFAULT 0 COMMENT '当前可用积分',
  `total_recharge` int NOT NULL DEFAULT 0 COMMENT '累计充值积分',
  `total_consume` int NOT NULL DEFAULT 0 COMMENT '累计成功消耗积分',
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1启用',
  `version` int NOT NULL DEFAULT 0 COMMENT '并发版本号',
  `creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tenant_id` (`tenant_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户 AI 积分账户';

CREATE TABLE IF NOT EXISTS `ai_point_consume_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `request_no` varchar(64) NOT NULL COMMENT '唯一请求号',
  `tenant_id` bigint NOT NULL COMMENT '租户编号',
  `user_id` bigint DEFAULT NULL COMMENT '消耗用户编号',
  `user_nickname` varchar(64) DEFAULT NULL COMMENT '用户昵称快照',
  `biz_type` varchar(32) NOT NULL COMMENT 'IMAGE/VIDEO/MANUAL_RECHARGE',
  `biz_id` bigint DEFAULT NULL COMMENT '图片或视频任务编号',
  `consume_point` int NOT NULL COMMENT '消耗积分；人工充值记录为负数',
  `balance_before` int NOT NULL COMMENT '变动前余额',
  `balance_after` int NOT NULL COMMENT '变动后余额',
  `status` tinyint NOT NULL COMMENT '0生成中 1成功 2失败已退款 3人工调整',
  `result_url` varchar(2048) DEFAULT NULL COMMENT '生成结果路径',
  `failure_reason` varchar(500) DEFAULT NULL COMMENT '失败或调整说明',
  `refund_point` int NOT NULL DEFAULT 0 COMMENT '退款积分',
  `creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) NOT NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_request_no` (`request_no`),
  KEY `idx_tenant_create_time` (`tenant_id`,`create_time`),
  KEY `idx_user_create_time` (`user_id`,`create_time`),
  KEY `idx_biz` (`biz_type`,`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI 积分消耗流水';

-- 为已有租户初始化积分账户：默认 0 分。将 0 改为目标初始积分再执行即可批量发放。
INSERT INTO `system_tenant_ai_point`
  (`tenant_id`,`balance`,`total_recharge`,`total_consume`,`status`,`version`,`creator`,`updater`)
SELECT t.`id`, 0, 0, 0, 1, 0, 'system', 'system'
FROM `system_tenant` t
WHERE t.`deleted` = b'0'
ON DUPLICATE KEY UPDATE `tenant_id` = VALUES(`tenant_id`);

-- 历史人工充值流水在旧版本中未保存昵称时，补充为通用标识。
-- 无法从积分表可靠还原实际操作人，因此不虚构 user_id；新产生的人工充值会记录实际管理员 ID 与昵称。
UPDATE `ai_point_consume_record`
SET `user_nickname` = '管理员'
WHERE `biz_type` = 'MANUAL_RECHARGE'
  AND (`user_nickname` IS NULL OR `user_nickname` = '')
  AND `deleted` = b'0';

-- 管理端菜单（父菜单为“系统管理” id=1；如你的库中 id 不同，请替换 parent_id）。
-- 该段可重复执行：已执行过旧版 SQL 时，会把原来的两个一级菜单移动到 AI积分管理目录下。
INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`updater`,`deleted`)
SELECT 9000,'AI积分管理','',1,9,1,'ai-point-management','ep:coin',NULL,NULL,0,b'1',b'1',b'1','admin','admin',b'0'
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id`=9000);

UPDATE `system_menu`
SET `name` = 'AI积分管理', `permission` = '', `type` = 1, `sort` = 9, `parent_id` = 1,
    `path` = 'ai-point-management', `icon` = 'ep:coin', `component` = NULL, `component_name` = NULL,
    `status` = 0, `visible` = b'1', `keep_alive` = b'1', `always_show` = b'1', `deleted` = b'0'
WHERE `id` = 9000;

INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`updater`,`deleted`)
SELECT 9001,'租户AI积分','system:tenant-ai-point:query',2,1,9000,'tenant-point','ep:coin','system/aiPoint/index','SystemAiPoint',0,b'1',b'1',b'1','admin','admin',b'0'
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id`=9001);

UPDATE `system_menu`
SET `name` = '租户AI积分', `permission` = 'system:tenant-ai-point:query', `type` = 2, `sort` = 1, `parent_id` = 9000,
    `path` = 'tenant-point', `icon` = 'ep:coin', `component` = 'system/aiPoint/index', `component_name` = 'SystemAiPoint',
    `status` = 0, `visible` = b'1', `keep_alive` = b'1', `always_show` = b'1', `deleted` = b'0'
WHERE `id` = 9001;

INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`updater`,`deleted`)
SELECT 9002,'AI积分消耗历史','system:ai-point-record:query',2,2,9000,'consume-history','ep:document','system/aiPointRecord/index','SystemAiPointRecord',0,b'1',b'1',b'1','admin','admin',b'0'
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id`=9002);

UPDATE `system_menu`
SET `name` = 'AI积分消耗历史', `permission` = 'system:ai-point-record:query', `type` = 2, `sort` = 2, `parent_id` = 9000,
    `path` = 'consume-history', `icon` = 'ep:document', `component` = 'system/aiPointRecord/index', `component_name` = 'SystemAiPointRecord',
    `status` = 0, `visible` = b'1', `keep_alive` = b'1', `always_show` = b'1', `deleted` = b'0'
WHERE `id` = 9002;

INSERT INTO `system_menu`
(`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`updater`,`deleted`)
SELECT 9003,'AI积分充值','system:tenant-ai-point:update',3,1,9001,'','','',NULL,0,b'1',b'1',b'1','admin','admin',b'0'
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id`=9003);

-- 给超级管理员角色授权。若管理员角色不是 id=1，请替换为实际 role_id。
INSERT INTO `system_role_menu` (`role_id`,`menu_id`) SELECT 1,9001 WHERE NOT EXISTS (SELECT 1 FROM `system_role_menu` WHERE `role_id`=1 AND `menu_id`=9001);
INSERT INTO `system_role_menu` (`role_id`,`menu_id`) SELECT 1,9002 WHERE NOT EXISTS (SELECT 1 FROM `system_role_menu` WHERE `role_id`=1 AND `menu_id`=9002);
INSERT INTO `system_role_menu` (`role_id`,`menu_id`) SELECT 1,9003 WHERE NOT EXISTS (SELECT 1 FROM `system_role_menu` WHERE `role_id`=1 AND `menu_id`=9003);
INSERT INTO `system_role_menu` (`role_id`,`menu_id`) SELECT 1,9000 WHERE NOT EXISTS (SELECT 1 FROM `system_role_menu` WHERE `role_id`=1 AND `menu_id`=9000);