-- 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);