soc/project_data/sql/0.0.8/soc/soc.sql

create table if not exists core_reminder_configuration
(
    config_id         bigint                  not null comment '配置ID'
        primary key,
    reminder_type     int                     not null comment '提醒类型（0:逾期提醒、1:临期提醒）',
    send_module       varchar(50)             not null comment '发送模块（五大计划之一）',
    plan_id           bigint       default 1  not null comment '计划id',
    plan_name         varchar(125) default '' not null comment '计划名称',
    compare_time_type int                     null comment '对比时间类型,0:计划开始时间,1:计划结束时间',
    time_unit         varchar(25)             null comment '时间单位',
    reminder_time     int                     null comment '提醒时间',
    enabled           tinyint(1)   default 1  not null comment '是否启用该提醒配置'
)
    comment '短信提醒配置表';
-- auto-generated definition
create table if not exists core_reminder_configuration_role(
    config_id        bigint not null,
    target_role_id   bigint not null,
    target_role_name bigint not null,
    primary key (config_id, target_role_id)
)
    comment '短信提醒配置角色关联表';

create table if not exists core_reminder_configuration_org(
    config_id        bigint not null comment '配置id',
    target_org_id   bigint not null comment '发送目标机构id',
    target_org_name varchar(125) not null comment '发送目标机构名称',
    target_org_type int not null comment '发送目标机构类型',
    target_org_path varchar(125) not null comment '发送目标机构路径',
    primary key (config_id, target_org_id)
)
    comment '短信提醒配置机构关联表';

-- auto-generated definition
create table if not exists core_reminder_log(
    log_id             bigint      not null comment '日志ID'
        primary key,
    schedule_id        bigint      not null comment '关联的提醒任务ID',
    recipient_id       bigint      not null comment '目标接收者id',
    recipient_name     varchar(50) not null comment '目标接收者',
    message_content    text        not null comment '实际发送的短信内容',
    send_time          datetime    not null comment '发送时间',
    result_code        varchar(50) null comment '发送结果代码',
    result_description text        null comment '发送结果描述'
)
    comment '短信提醒事件日志表';

-- auto-generated definition
create table if not exists core_reminder_schedule(
    schedule_id    bigint        not null comment '任务ID'
        primary key,
    config_id      bigint        not null comment '关联的提醒配置ID',
    recipient      varchar(50)   not null comment '目标接收者（手机号码或用户ID）',
    scheduled_time datetime      null comment '计划发送时间',
    status         varchar(20)   not null comment '任务状态（未发送、已发送、发送失败等）',
    sent_time      datetime      null comment '实际发送时间（若已发送）',
    retry_count    int default 0 null comment '重试次数（用于处理发送失败情况）'
)
    comment '短信提醒事件(任务)表';

-- auto-generated definition
create table if not exists core_reminder_template(
    template_id      bigint       not null comment '模板ID'
        primary key,
    reminder_type    int          not null comment '提醒类型（与 core_reminder_configuration 关联）',
    template_name    varchar(100) not null comment '模板名称',
    template_content text         not null comment '提醒短信具体内容（支持变量替换）',
    language         varchar(20)  not null comment '模板语言'
)
    comment '短信提醒内容模板表';


-- 新增短信提醒配置菜单
delete from sys_menu where id = 1778733750137446402;
INSERT INTO sys_menu (id,menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, platform_type, icon, image_path, create_by, create_time, update_by, update_time, remark) VALUES (1778733750137446402,'短信提醒配置', 1, 50, 'reminder', 'core/reminder/index', null, 1, 0, 'C', '0', '0', 'system:reminder:list', '1', 'email', null, null, now(), null, now(), null);
DELETE from sys_role_menu WHERE menu_id in (1778733750137446402);
INSERT into sys_role_menu SELECT id ,1778733750137446402 from sys_role WHERE sys_role.org_type in(1,3);

-- 新增提醒类型字典
delete from sys_dict_type where dict_type = 'reminder_type';
delete from sys_dict_data where dict_type = 'reminder_type';
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES ('提醒类型', 'reminder_type', '0', '超级管理员', '2024-04-12 18:02:23', '', null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (0, '逾期提醒', '0', 'reminder_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (0, '临期提醒', '1', 'reminder_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);


-- 新增提醒业务类型字典
delete from sys_dict_type where dict_type = 'reminder_business_type';
delete from sys_dict_data where dict_type = 'reminder_business_type';
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES ('提醒业务类型', 'reminder_business_type', '0', null, now(), null, null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (0, '安保履职', 'resumption', 'reminder_business_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (1, '安全检查', 'safetyCheck', 'reminder_business_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (2, '教育培训', 'eduTraining', 'reminder_business_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (3, '预案演练', 'drill', 'reminder_business_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (4, '监控调阅', 'monitoringRetrieval', 'reminder_business_type', null, 'default', 'N', '0', '超级管理员', now(), null, null, null);

DROP TABLE IF EXISTS `core_safety_work_summary`;
CREATE TABLE `core_safety_work_summary` (
                                            `id` bigint NOT NULL,
                                            `title` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工作主题',
                                            `date` datetime DEFAULT NULL COMMENT '记录日期',
                                            `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '内容',
                                            `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                            `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                            `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                            `update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                            `images` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '照片',
                                            `org_id` bigint DEFAULT NULL COMMENT '机构id',
                                            `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件种类',
                                            `year` int DEFAULT NULL COMMENT '工作年度',
                                            `files` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '总结文件',
                                            PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='安全保卫部年度工作总结';

DROP TABLE IF EXISTS `sys_user_account_mapper`;
CREATE TABLE `sys_user_account_mapper` (
                                           `master_user_id` bigint NOT NULL COMMENT '主账号用户Id',
                                           `sub_user_id` bigint NOT NULL COMMENT '可切换子账号用户Id',
                                           `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                           `modified_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人名称',
                                           `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                           `modified_by` bigint DEFAULT NULL COMMENT '修改人id',
                                           PRIMARY KEY (`master_user_id`,`sub_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='主账号与子账号对应关系表';


DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change ??
CREATE PROCEDURE schema_change () BEGIN


END ??
DELIMITER ;
CALL schema_change ();

-- 新增菜单 年度工作总结
DELETE from `sys_menu` where id in(01774689203604049922,01774690857464254465,01774691065061330945,01774691126533050369,01774691223434055681);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01774689203604049922, '年度工作总结', 1711912586825461761, 4, 'worksummary', 'safetyBook/workSummary/index', NULL, 1, 0, 'C', '0', '0', 'core:worksummary:query', '1', 'documentation', NULL, '超级管理员', '2024-04-01 14:44:12', '超级管理员', '2024-04-01 14:51:17', '');
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01774690857464254465, '查询', 1774689203604049922, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'core:worksummary:query', '1', NULL, NULL, '超级管理员', '2024-04-01 14:50:47', '', NULL, '');
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01774691065061330945, '新增', 1774689203604049922, 2, '', NULL, NULL, 1, 0, 'F', '0', '0', 'core:worksummary:add', '1', NULL, NULL, '超级管理员', '2024-04-01 14:51:36', '', NULL, '');
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01774691126533050369, '编辑', 1774689203604049922, 3, '', NULL, NULL, 1, 0, 'F', '0', '0', 'core:worksummary:edit', '1', NULL, NULL, '超级管理员', '2024-04-01 14:51:51', '', NULL, '');
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01774691223434055681, '删除', 1774689203604049922, 4, '', NULL, NULL, 1, 0, 'F', '0', '0', 'core:worksummary:delete', '1', NULL, NULL, '超级管理员', '2024-04-01 14:52:14', '', NULL, '');

DELETE from `sys_menu` where id in(01780130457928527874);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (01780130457928527874, '关联账号', 100, 124, '', NULL, NULL, 1, 0, 'F', '0', '0', 'system:user:mapperUser', '1', NULL, NULL, '超级管理员', '2024-04-16 15:06:56', '', NULL, '');