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 '发送模块（五大计划之一）',
    org_id                bigint               null comment '发送目标组织机构',
    org_name              varchar(125)         not null comment '发送目标组织机构',
    org_path              varchar(125)         not null comment '发送目标组织机构',
    org_type              int                  null comment '发送目标机构类型',
    advance_notice_period int                  not null comment '临期时间（以天数表示）',
    overdue_notice_period int                  not 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 '短信提醒内容模板表';




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