153 lines
12 KiB
Go
153 lines
12 KiB
Go
-- 016_user_notice_job.sql
|
||
-- 用户管理扩展 + 通知模块 + 自动任务模块（建表 + 菜单 + 种子）。
|
||
-- 幂等：ALTER TABLE ADD COLUMN 用 IF NOT EXISTS（MySQL 8 不支持 COLUMN IF NOT EXISTS，用存储过程判断）；
|
||
-- 菜单用 INSERT ... ON DUPLICATE KEY UPDATE；种子用 INSERT IGNORE。
|
||
-- 依赖：service 主库；admin_menu/admin_role_menu/admin_user 已存在。
|
||
|
||
-- ============ 1) 用户表扩展（Bark 设备ID / pushplus token） ============
|
||
SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='admin_user' AND COLUMN_NAME='bark_device_id');
|
||
SET @sql := IF(@has_col=0, 'ALTER TABLE admin_user ADD COLUMN bark_device_id VARCHAR(200) NOT NULL DEFAULT '''' COMMENT ''Bark设备ID'' AFTER status', 'SELECT 1');
|
||
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='admin_user' AND COLUMN_NAME='pushplus_token');
|
||
SET @sql := IF(@has_col=0, 'ALTER TABLE admin_user ADD COLUMN pushplus_token VARCHAR(200) NOT NULL DEFAULT '''' COMMENT ''pushplus token'' AFTER bark_device_id', 'SELECT 1');
|
||
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
-- ============ 2) 通知渠道表 ============
|
||
CREATE TABLE IF NOT EXISTS `notice_channel` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '渠道编码 bark/pushplus/webhook',
|
||
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '渠道名称',
|
||
`enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否 1是',
|
||
`config` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '渠道配置JSON(如 bark 的 baseUrl)',
|
||
`remark` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_code` (`code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知渠道配置';
|
||
|
||
-- ============ 3) 通知规则表 ============
|
||
CREATE TABLE IF NOT EXISTS `notice_rule` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '规则名称',
|
||
`event_type` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '事件类型 job_done/job_fail/security_alert/recruit_daily',
|
||
`channel_codes` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '渠道编码 JSON 数组 ["bark","pushplus"]',
|
||
`user_ids` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '接收用户ID JSON 数组 [1,2]',
|
||
`title_template` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '标题模板(支持 {var} 占位符)',
|
||
`body_template` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '内容模板',
|
||
`enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否 1是',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_event` (`event_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知规则(事件→渠道→人员→启用)';
|
||
|
||
-- ============ 4) 通知发送记录 ============
|
||
CREATE TABLE IF NOT EXISTS `notice_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`rule_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '规则ID',
|
||
`event_type` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '事件类型',
|
||
`channel_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '渠道编码',
|
||
`user_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '接收用户ID',
|
||
`target` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '发送目标(设备key/token)',
|
||
`title` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '标题',
|
||
`body` VARCHAR(2000) NOT NULL DEFAULT '' COMMENT '内容',
|
||
`result` TINYINT NOT NULL DEFAULT 0 COMMENT '1成功 0失败',
|
||
`error` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '错误信息',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_event` (`event_type`),
|
||
KEY `idx_created` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知发送记录';
|
||
|
||
-- ============ 5) 自动任务定义 ============
|
||
CREATE TABLE IF NOT EXISTS `auto_job` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '任务名称',
|
||
`code` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '任务编码(唯一,映射业务函数)',
|
||
`job_type` VARCHAR(32) NOT NULL DEFAULT 'gcron' COMMENT '任务类型',
|
||
`cron_expr` VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'cron 表达式(6段: 秒 分 时 日 月 周)',
|
||
`enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用 0否 1是',
|
||
`remark` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
|
||
`last_run_at` DATETIME NULL DEFAULT NULL COMMENT '上次运行时间',
|
||
`last_result` TINYINT NOT NULL DEFAULT 0 COMMENT '0未运行 1成功 2失败',
|
||
`last_error` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '上次错误',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_code` (`code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自动任务定义';
|
||
|
||
-- ============ 6) 自动任务运行日志 ============
|
||
CREATE TABLE IF NOT EXISTS `auto_job_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`job_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '任务ID',
|
||
`run_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '运行时间',
|
||
`result` TINYINT NOT NULL DEFAULT 0 COMMENT '1成功 2失败',
|
||
`error` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '错误信息',
|
||
`summary` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '运行摘要',
|
||
`duration_ms` INT NOT NULL DEFAULT 0 COMMENT '耗时(毫秒)',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_job` (`job_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自动任务运行日志';
|
||
|
||
-- ============ 7) 种子：通知渠道 ============
|
||
INSERT INTO notice_channel (id, code, name, enabled, config, remark) VALUES
|
||
(1, 'bark', 'Bark(iOS)', 1, '{"baseUrl":"http://bark:8080"}', 'Bark 自建服务，容器网络内访问 bark:8080'),
|
||
(2, 'pushplus', 'pushplus', 1, '{}', '微信推送，token 在用户资料中维护')
|
||
ON DUPLICATE KEY UPDATE name=VALUES(name), enabled=VALUES(enabled), config=VALUES(config), remark=VALUES(remark);
|
||
|
||
-- ============ 8) 种子：通知规则 ============
|
||
INSERT INTO notice_rule (id, name, event_type, channel_codes, user_ids, title_template, body_template, enabled) VALUES
|
||
(1, '安全告警', 'security_alert', '["bark"]', '[1]', '服务器安全告警', '{{summary}}', 1),
|
||
(2, '任务完成', 'job_done', '["bark"]', '[1]', '任务完成: {{jobName}}', '{{summary}}', 1),
|
||
(3, '任务失败', 'job_fail', '["bark","pushplus"]', '[1]', '⚠️ 任务失败: {{jobName}}', '{{summary}}', 1)
|
||
ON DUPLICATE KEY UPDATE name=VALUES(name), event_type=VALUES(event_type), channel_codes=VALUES(channel_codes),
|
||
user_ids=VALUES(user_ids), title_template=VALUES(title_template), body_template=VALUES(body_template), enabled=VALUES(enabled);
|
||
|
||
-- ============ 9) 种子：自动任务（现有 gcron 任务纳管） ============
|
||
INSERT INTO auto_job (id, name, code, job_type, cron_expr, enabled, remark) VALUES
|
||
(1, '招聘公告每日抓取', 'recruit-crawl-daily', 'gcron', '0 0 3 * * *', 1, '每日03:00 增量抓取招聘公告'),
|
||
(2, '招聘公告每日推送', 'recruit-push-daily', 'gcron', '0 0 8 * * *', 1, '每日08:00 推送订阅公告(按 push_subscription 配置)')
|
||
ON DUPLICATE KEY UPDATE name=VALUES(name), cron_expr=VALUES(cron_expr), enabled=VALUES(enabled), remark=VALUES(remark);
|
||
|
||
-- ============ 10) 菜单：人员管理改名 → 用户管理 ============
|
||
UPDATE admin_menu SET name='用户管理' WHERE name='人员管理' AND type=1;
|
||
-- 补充兜底：若没有名为人员管理的菜单（如叫管理员管理），把 component='system/admin/index' 的改名为用户管理
|
||
UPDATE admin_menu SET name='用户管理' WHERE component='system/admin/index' AND type=1 AND name != '用户管理';
|
||
|
||
-- ============ 11) 菜单：通知中心 + 自动任务 ============
|
||
INSERT INTO admin_menu (id, parent_id, name, icon, type, path, component, permission, sort, status, hidden, created_at, updated_at) VALUES
|
||
(98, 0, '通知中心', 'mdi:bell-ring-outline', 1, '/notice', '', 'notice:center', 8, 1, 0, NOW(), NOW()),
|
||
(980, 98, '通知规则', 'mdi:bell-cog-outline', 1, 'rule', 'system/notice/rule/index', 'notice:rule', 1, 1, 0, NOW(), NOW()),
|
||
(981, 98, '通知渠道', 'mdi:router-wireless', 1, 'channel', 'system/notice/channel/index', 'notice:channel', 2, 1, 0, NOW(), NOW()),
|
||
(982, 98, '通知日志', 'mdi:history', 1, 'log', 'system/notice/log/index', 'notice:log', 3, 1, 0, NOW(), NOW()),
|
||
(99, 0, '自动任务', 'mdi:clock-outline', 1, '/auto-job','', 'job:center', 9, 1, 0, NOW(), NOW()),
|
||
(990, 99, '任务管理', 'mdi:calendar-clock', 1, 'list', 'system/auto-job/list/index', 'job:list', 1, 1, 0, NOW(), NOW()),
|
||
(991, 99, '任务日志', 'mdi:file-clock-outline', 1, 'log', 'system/auto-job/log/index', 'job:log', 2, 1, 0, NOW(), NOW())
|
||
ON DUPLICATE KEY UPDATE parent_id=VALUES(parent_id), name=VALUES(name), icon=VALUES(icon), type=VALUES(type),
|
||
path=VALUES(path), component=VALUES(component), permission=VALUES(permission), sort=VALUES(sort),
|
||
status=VALUES(status), hidden=VALUES(hidden), deleted_at=NULL;
|
||
|
||
-- ============ 12) type=2 API 权限 ============
|
||
INSERT INTO admin_menu (id, parent_id, name, icon, type, path, component, permission, sort, status, hidden, created_at, updated_at) VALUES
|
||
(9801, 980, '查询', '', 2, 'POST /api/service/admin/notice/rule/list', '', 'notice:rule:list', 1, 1, 0, NOW(), NOW()),
|
||
(9802, 980, '保存', '', 2, 'POST /api/service/admin/notice/rule/save', '', 'notice:rule:save', 2, 1, 0, NOW(), NOW()),
|
||
(9803, 980, '删除', '', 2, 'POST /api/service/admin/notice/rule/delete', '', 'notice:rule:delete', 3, 1, 0, NOW(), NOW()),
|
||
(9804, 980, '测试', '', 2, 'POST /api/service/admin/notice/test', '', 'notice:test', 4, 1, 0, NOW(), NOW()),
|
||
(9811, 981, '查询', '', 2, 'POST /api/service/admin/notice/channel/list', '', 'notice:channel:list', 1, 1, 0, NOW(), NOW()),
|
||
(9812, 981, '保存', '', 2, 'POST /api/service/admin/notice/channel/save', '', 'notice:channel:save', 2, 1, 0, NOW(), NOW()),
|
||
(9821, 982, '查询', '', 2, 'POST /api/service/admin/notice/log/list', '', 'notice:log:list', 1, 1, 0, NOW(), NOW()),
|
||
(9901, 990, '查询', '', 2, 'POST /api/service/admin/auto-job/list', '', 'job:list:query', 1, 1, 0, NOW(), NOW()),
|
||
(9902, 990, '保存', '', 2, 'POST /api/service/admin/auto-job/save', '', 'job:list:save', 2, 1, 0, NOW(), NOW()),
|
||
(9903, 990, '触发', '', 2, 'POST /api/service/admin/auto-job/trigger', '', 'job:list:trigger', 3, 1, 0, NOW(), NOW()),
|
||
(9911, 991, '查询', '', 2, 'POST /api/service/admin/auto-job/log/list', '', 'job:log:list', 1, 1, 0, NOW(), NOW())
|
||
ON DUPLICATE KEY UPDATE parent_id=VALUES(parent_id), name=VALUES(name), type=VALUES(type),
|
||
path=VALUES(path), permission=VALUES(permission), sort=VALUES(sort), status=VALUES(status), hidden=VALUES(hidden), deleted_at=NULL;
|
||
|
||
-- ============ 13) 超管角色绑定 ============
|
||
INSERT IGNORE INTO admin_role_menu (role_id, menu_id, created_at, updated_at)
|
||
SELECT 1, id, NOW(), NOW() FROM admin_menu WHERE id IN (98,980,981,982,99,990,991,9801,9802,9803,9804,9811,9812,9821,9901,9902,9903,9911);
|