-- 生日祝福模块初始化脚本
-- 适用数据库：MySQL
-- 说明：
-- 1. 后台筛选项复用系统已有字典：UserState、UserType，避免覆盖现有人员口径。
-- 2. 菜单和角色授权采用幂等写法，可重复执行。

CREATE TABLE IF NOT EXISTS `blessing` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `blessingName` varchar(32) DEFAULT NULL COMMENT '祝福名称',
  `activityGroupId` varchar(32) DEFAULT NULL COMMENT '发送范围',
  `blessingMode` varchar(32) DEFAULT NULL COMMENT '祝福模式',
  `isSend` varchar(2) DEFAULT NULL COMMENT '是否发送,0未开启，1进行中，2，已完成',
  `sendTypes` json DEFAULT NULL COMMENT '发送方式',
  `blessingContent` text COMMENT '祝福内容',
  `blessingCron` text COMMENT 'cron表达式',
  `applyDate` varchar(30) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='祝福计划表';

CREATE TABLE IF NOT EXISTS `blessing_user` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `userId` varchar(32) DEFAULT NULL COMMENT '接收人ID',
  `blessingId` varchar(32) DEFAULT NULL COMMENT '祝福ID',
  `applyDate` varchar(32) DEFAULT NULL COMMENT '发送时间',
  PRIMARY KEY (`id`),
  KEY `idx_blessing_user_userId` (`userId`),
  KEY `idx_blessing_user_blessingId` (`blessingId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='祝福发送人员表';

CREATE TABLE IF NOT EXISTS `blessing_gift` (
  `id` varchar(32) NOT NULL COMMENT 'ID',
  `giftName` varchar(50) DEFAULT NULL COMMENT '礼物名称',
  `fileName` varchar(50) DEFAULT NULL COMMENT '礼物文件名称',
  `files` json DEFAULT NULL COMMENT '礼物图片',
  `sortField` varchar(10) DEFAULT NULL COMMENT '排序字段',
  `createBy` varchar(32) DEFAULT NULL COMMENT '创建人',
  `createDate` varchar(50) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='祝福礼物表';

CREATE TABLE IF NOT EXISTS `greeting` (
  `id` varchar(32) NOT NULL COMMENT 'ID',
  `birthday` varchar(4) DEFAULT NULL COMMENT '职工生日',
  `userId` varchar(32) DEFAULT NULL COMMENT '职工ID',
  `username` varchar(32) DEFAULT NULL COMMENT '职工姓名',
  `loginname` varchar(32) DEFAULT NULL COMMENT '职工编号',
  PRIMARY KEY (`id`),
  KEY `idx_greeting_userId` (`userId`),
  KEY `idx_greeting_birthday` (`birthday`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生日祝福中间表';

CREATE TABLE IF NOT EXISTS `greeting_info` (
  `id` varchar(32) NOT NULL COMMENT 'ID',
  `presenter` varchar(32) DEFAULT NULL COMMENT '赠送者ID',
  `received` varchar(32) DEFAULT NULL COMMENT '收到者ID',
  `giftId` varchar(32) DEFAULT NULL COMMENT '礼物ID',
  `giftTime` varchar(32) DEFAULT NULL COMMENT '送礼时间',
  `message` varchar(255) DEFAULT NULL COMMENT '祝福语',
  `isSend` tinyint(1) DEFAULT '0' COMMENT '是否发送贺卡',
  PRIMARY KEY (`id`),
  KEY `idx_greeting_info_received` (`received`),
  KEY `idx_greeting_info_presenter` (`presenter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生日礼物记录表';

CREATE TABLE IF NOT EXISTS `user_birthday_config` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `picUrl` varchar(100) DEFAULT NULL COMMENT '图片地址',
  `birthdayUrl` varchar(100) DEFAULT NULL COMMENT '生日贺卡地址',
  `createdBy` varchar(32) DEFAULT NULL COMMENT '创建人',
  `createdAt` bigint DEFAULT NULL COMMENT '创建时间',
  `updatedBy` varchar(32) DEFAULT NULL COMMENT '修改人',
  `updatedAt` bigint DEFAULT NULL COMMENT '修改时间',
  `delFlag` tinyint(1) DEFAULT '0' COMMENT '删除标记',
  `opBy` varchar(255) DEFAULT NULL COMMENT 'opBy',
  `opAt` varchar(255) DEFAULT NULL COMMENT 'opAt',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生日祝福配置表';

CREATE TABLE IF NOT EXISTS `user_birthday_msg_log` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `pushTime` varchar(50) DEFAULT NULL COMMENT '推送时间',
  `msgTitle` varchar(100) DEFAULT NULL COMMENT '消息标题',
  `msgContent` text COMMENT '消息内容',
  `receiveBy` varchar(50) DEFAULT NULL COMMENT '接收人',
  `receiveName` varchar(50) DEFAULT NULL COMMENT '接收人姓名',
  `pushBy` varchar(50) DEFAULT NULL COMMENT '推送人',
  `pushByName` varchar(50) DEFAULT NULL COMMENT '推送人姓名',
  `pushType` varchar(50) DEFAULT NULL COMMENT '推送人类型',
  `link` varchar(500) DEFAULT NULL COMMENT '卡片链接',
  `createdBy` varchar(32) DEFAULT NULL COMMENT '创建人',
  `createdAt` bigint DEFAULT NULL COMMENT '创建时间',
  `updatedBy` varchar(32) DEFAULT NULL COMMENT '修改人',
  `updatedAt` bigint DEFAULT NULL COMMENT '修改时间',
  `delFlag` tinyint(1) DEFAULT '0' COMMENT '删除标记',
  `opBy` varchar(255) DEFAULT NULL COMMENT 'opBy',
  `opAt` varchar(255) DEFAULT NULL COMMENT 'opAt',
  PRIMARY KEY (`id`),
  KEY `idx_user_birthday_msg_log_receiveBy` (`receiveBy`),
  KEY `idx_user_birthday_msg_log_pushTime` (`pushTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生日祝福消息日志表';

INSERT INTO `sys_config` (`configKey`, `configValue`, `note`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT 'GreetingTime', '15', '生日祝福查询时间范围：当天起未来N天', '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_config` WHERE `configKey` = 'GreetingTime');

INSERT INTO `sys_config` (`configKey`, `configValue`, `note`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT 'BirthdayAutoSendEnabled', '1', '生日祝福是否自动发送', '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_config` WHERE `configKey` = 'BirthdayAutoSendEnabled');

INSERT INTO `sys_config` (`configKey`, `configValue`, `note`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT 'BirthdayAutoSendTitle', '请点击查收您的生日福利', '生日祝福自动发送标题', '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_config` WHERE `configKey` = 'BirthdayAutoSendTitle');

INSERT INTO `sys_config` (`configKey`, `configValue`, `note`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT 'BirthdayAutoSendContent', '校工会祝您：生日快乐！幸福安康', '生日祝福自动发送内容', '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_config` WHERE `configKey` = 'BirthdayAutoSendContent');

INSERT INTO `sys_task` (`id`, `name`, `jobClass`, `note`, `cron`, `data`, `exeAt`, `exeResult`, `disabled`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT 'bd000000000000000000000000000101', 'UserBirthdayJob', 'io.v.nutz.task.job.staffmanage.UserBirthdayJob', '生日当天自动发送生日福利通知', '0 0 9 * * ?', NULL, NULL, NULL, 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_task` WHERE `name` = 'UserBirthdayJob');

INSERT INTO `sys_task` (`id`, `name`, `jobClass`, `note`, `cron`, `data`, `exeAt`, `exeResult`, `disabled`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT 'bd000000000000000000000000000102', 'GreetingJob', 'io.v.nutz.task.job.brithday.GreetingJob', '生成生日祝福中间表数据', '0 0 1 * * ?', NULL, NULL, NULL, 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_task` WHERE `name` = 'GreetingJob');

SET @birthday_root_id := 'bd000000000000000000000000000201';
SET @birthday_manage_id := 'bd000000000000000000000000000202';
SET @birthday_msglog_id := 'bd000000000000000000000000000203';
SET @birthday_config_id := 'bd000000000000000000000000000204';
SET @birthday_autosend_id := 'bd000000000000000000000000000205';
SET @birthday_export_id := 'bd000000000000000000000000000206';
SET @birthday_root_id := IFNULL((SELECT `id` FROM `sys_menu` WHERE `permission` = 'staffManage.birthday' LIMIT 1), @birthday_root_id);
SET @birthday_root_path := (SELECT `path` FROM `sys_menu` WHERE `id` = @birthday_root_id LIMIT 1);
SET @birthday_root_path := IFNULL(@birthday_root_path, LPAD((SELECT IFNULL(MAX(CAST(`path` AS UNSIGNED)), 0) + 1 FROM `sys_menu` WHERE IFNULL(`parentId`, '') = '' AND LENGTH(`path`) = 4), 4, '0'));

INSERT INTO `sys_menu` (`id`, `parentId`, `path`, `name`, `aliasName`, `type`, `href`, `target`, `icon`, `showit`, `disabled`, `permission`, `note`, `location`, `hasChildren`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT @birthday_root_id, '', @birthday_root_path, '生日祝福', 'BirthdayBlessing', 'menu', '', '', 'fa fa-birthday-cake', 1, 0, 'staffManage.birthday', '生日祝福模块', (SELECT IFNULL(MAX(`location`), 0) + 1 FROM (SELECT `location` FROM `sys_menu`) m), 1, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = @birthday_root_id OR `permission` = 'staffManage.birthday');

INSERT INTO `sys_menu` (`id`, `parentId`, `path`, `name`, `aliasName`, `type`, `href`, `target`, `icon`, `showit`, `disabled`, `permission`, `note`, `location`, `hasChildren`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT @birthday_manage_id, @birthday_root_id, CONCAT(@birthday_root_path, '0001'), '生日管理', 'BirthdayManage', 'menu', '/platform/staffManage/birthday/manage', 'data-pjax', '', 1, 0, 'staffManage.birthday.manage', '生日人员查询、贺卡配置和通知发送', (SELECT IFNULL(MAX(`location`), 0) + 1 FROM (SELECT `location` FROM `sys_menu`) m), 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = @birthday_manage_id OR `permission` = 'staffManage.birthday.manage');

INSERT INTO `sys_menu` (`id`, `parentId`, `path`, `name`, `aliasName`, `type`, `href`, `target`, `icon`, `showit`, `disabled`, `permission`, `note`, `location`, `hasChildren`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT @birthday_msglog_id, @birthday_root_id, CONCAT(@birthday_root_path, '0002'), '发送记录', 'BirthdayMsgLog', 'menu', '/platform/staffManage/birthday/msgLog', 'data-pjax', '', 1, 0, 'staffManage.birthday.msgLog', '生日祝福通知发送记录', (SELECT IFNULL(MAX(`location`), 0) + 1 FROM (SELECT `location` FROM `sys_menu`) m), 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = @birthday_msglog_id OR `permission` = 'staffManage.birthday.msgLog');

INSERT INTO `sys_menu` (`id`, `parentId`, `path`, `name`, `aliasName`, `type`, `href`, `target`, `icon`, `showit`, `disabled`, `permission`, `note`, `location`, `hasChildren`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT @birthday_config_id, @birthday_root_id, CONCAT(@birthday_root_path, '0003'), '系统配置', 'BirthdayConfig', 'menu', '/platform/staffManage/birthday/config', 'data-pjax', '', 1, 0, 'staffManage.birthday.config', '生日祝福背景图、贺卡、倒计时和自动发送开关配置', (SELECT IFNULL(MAX(`location`), 0) + 1 FROM (SELECT `location` FROM `sys_menu`) m), 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = @birthday_config_id OR `permission` = 'staffManage.birthday.config');

INSERT INTO `sys_menu` (`id`, `parentId`, `path`, `name`, `aliasName`, `type`, `href`, `target`, `icon`, `showit`, `disabled`, `permission`, `note`, `location`, `hasChildren`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT @birthday_autosend_id, @birthday_root_id, CONCAT(@birthday_root_path, '0004'), '自动发送祝福', 'BirthdayAutoSend', 'menu', '/platform/staffManage/birthday/autoSend', 'data-pjax', '', 1, 0, 'staffManage.birthday.autoSend', '生日祝福自动发送任务配置', (SELECT IFNULL(MAX(`location`), 0) + 1 FROM (SELECT `location` FROM `sys_menu`) m), 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = @birthday_autosend_id OR `permission` = 'staffManage.birthday.autoSend');

INSERT INTO `sys_menu` (`id`, `parentId`, `path`, `name`, `aliasName`, `type`, `href`, `target`, `icon`, `showit`, `disabled`, `permission`, `note`, `location`, `hasChildren`, `createdBy`, `createdAt`, `updatedBy`, `updatedAt`, `delFlag`)
SELECT @birthday_export_id, @birthday_root_id, CONCAT(@birthday_root_path, '0005'), '数据导出', 'BirthdayExport', 'menu', '/platform/staffManage/birthday/export', 'data-pjax', '', 1, 0, 'staffManage.birthday.export', '生日祝福人员名单和签名表导出', (SELECT IFNULL(MAX(`location`), 0) + 1 FROM (SELECT `location` FROM `sys_menu`) m), 0, '', UNIX_TIMESTAMP() * 1000, '', UNIX_TIMESTAMP() * 1000, 0
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = @birthday_export_id OR `permission` = 'staffManage.birthday.export');

INSERT INTO `sys_role_menu` (`roleId`, `menuId`)
SELECT r.`id`, m.`id`
FROM `sys_role` r
JOIN `sys_menu` m ON m.`permission` IN (
  'staffManage.birthday',
  'staffManage.birthday.manage',
  'staffManage.birthday.config',
  'staffManage.birthday.autoSend',
  'staffManage.birthday.msgLog',
  'staffManage.birthday.export'
)
WHERE r.`code` = 'sysadmin'
  AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu` rm
    WHERE rm.`roleId` = r.`id`
      AND rm.`menuId` = m.`id`
  );