intelligent-etmf-system/script/sql/business/create.sql

CREATE TABLE `project`
(
    `id`          bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `code`        varchar(128)                NOT NULL COMMENT '项目编号',
    `name`        varchar(128)                NOT NULL COMMENT '名称',
    `icon`        bigint                      NOT NULL COMMENT '图标',
    `language`    char(1)                     NOT NULL DEFAULT '0' COMMENT '项目语言',
    `type`        char(1)                     NOT NULL DEFAULT '0' COMMENT '项目类型',
    `status`      tinyint(1)                  NOT NULL DEFAULT 0 COMMENT '状态',
    `pd_gpd`      varchar(128) COMMENT 'PD/GPD',
    `pm_gpm`      varchar(128) COMMENT 'PM/GPM',
    `cta_gcta`    varchar(128) COMMENT 'CTA/GCTA',
    `sponsor`     varchar(128) COMMENT '申办方',
    `cro`         varchar(128) COMMENT 'CRO',
    `note`        varchar(255) COMMENT '备注',
    `start_time`  datetime COMMENT '开始时间',
    `end_time`    datetime COMMENT '结束时间',
    `create_dept` bigint(20) COMMENT '创建部门',
    `create_by`   bigint(20) COMMENT '创建者',
    `create_time` datetime COMMENT '创建时间',
    `update_by`   bigint(20) COMMENT '更新者',
    `update_time` datetime COMMENT '更新时间',
    `del_flag`    char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`   varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='项目';

CREATE TABLE `sys_user_projects`
(
    `user_id`  bigint       NOT NULL PRIMARY KEY COMMENT '用户ID',
    `projects` varchar(255) NOT NULL COMMENT '项目ID'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户项目数据权限表';

CREATE TABLE `sys_user_center`
(
    `user_id`   bigint NOT NULL COMMENT '用户ID',
    `folder_id` bigint NOT NULL COMMENT '文件夹ID',
    PRIMARY KEY (`user_id`, `folder_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='用户中心关联表';

CREATE TABLE `folder`
(
    `id`          bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `project_id`  bigint unsigned             NOT NULL COMMENT '所属项目',
    `parent_id`   bigint unsigned             NOT NULL COMMENT '父级',
    `type`        tinyint(1)                  NOT NULL COMMENT '类型',
    `name`        varchar(128)                NOT NULL COMMENT '名称',
    `status`      tinyint(1)                  NOT NULL DEFAULT 0 COMMENT '状态',
    `note`        varchar(255) COMMENT '备注',
    `create_dept` bigint(20) COMMENT '创建部门',
    `create_by`   bigint(20) COMMENT '创建者',
    `create_time` datetime COMMENT '创建时间',
    `update_by`   bigint(20) COMMENT '更新者',
    `update_time` datetime COMMENT '更新时间',
    `del_flag`    char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`   varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='文件夹';

CREATE TABLE `document`
(
    `id`                    bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `folder_id`             bigint unsigned             NOT NULL COMMENT '所属文件夹',
    `file_specification`    char(1)                     NOT NULL COMMENT '文档标识',
    `name`                  varchar(128)                NOT NULL COMMENT '计划名称',
    `status`                tinyint(1)                  NOT NULL DEFAULT 0 COMMENT '状态',
    `scheduled_upload_time` date                        NOT NULL COMMENT '计划上传时间',
    `oss_id`                bigint COMMENT '文档本体',
    `note`                  varchar(255) COMMENT '备注',
    `create_dept`           bigint(20) COMMENT '创建部门',
    `create_by`             bigint(20) COMMENT '创建者',
    `create_time`           datetime COMMENT '创建时间',
    `update_by`             bigint(20) COMMENT '更新者',
    `update_time`           datetime COMMENT '更新时间',
    `del_flag`              char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`             varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='文档';

CREATE TABLE `document_audit_log`
(
    `id`            bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `document_id`   bigint unsigned             NOT NULL COMMENT '审核文档',
    `oss_id`        bigint unsigned             NOT NULL COMMENT '历史版本',
    `auditor_type`  char(1)                     NOT NULL COMMENT '审核人类型',
    `auditor_id`    bigint unsigned             NOT NULL COMMENT '审核人',
    `result`        tinyint(1)                  NOT NULL COMMENT '审核结果(0 -> 通过; 1 -> 驳回)',
    `reject_reason` varchar(255) COMMENT '驳回理由',
    `audit_time`    datetime                    NOT NULL COMMENT '审核时间',
    `create_dept`   bigint(20) COMMENT '创建部门',
    `create_by`     bigint(20) COMMENT '创建者',
    `create_time`   datetime COMMENT '创建时间',
    `update_by`     bigint(20) COMMENT '更新者',
    `update_time`   datetime COMMENT '更新时间',
    `del_flag`      char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`     varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='文档审核记录';

CREATE TABLE `document_quality_control_task`
(
    `id`           bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `name`         varchar(128)                NOT NULL COMMENT '质控名称',
    `initiator`    bigint unsigned             NOT NULL COMMENT '发起人',
    `project_id`   bigint unsigned             NOT NULL COMMENT '质控项目',
    `start_date`   date COMMENT '开始时间',
    `deadline`     date COMMENT '截止时间',
    `status`       tinyint(1)                  NOT NULL DEFAULT 0 COMMENT '状态',
    `note`         varchar(255) COMMENT '备注',
    `create_dept`  bigint(20) COMMENT '创建部门',
    `create_by`    bigint(20) COMMENT '创建者',
    `create_time`  datetime COMMENT '创建时间',
    `update_by`    bigint(20) COMMENT '更新者',
    `update_time`  datetime COMMENT '更新时间',
    `del_flag`     char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`    varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='文档质控任务';

CREATE TABLE `document_quality_control_task_detail`
(
    `id`          bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `task_id`     bigint unsigned             NOT NULL COMMENT '所属任务',
    `document_id` bigint unsigned             NOT NULL COMMENT '指控文件',
    `status`      tinyint(1)                  NOT NULL DEFAULT 0 COMMENT '状态',
    `note`        varchar(255) COMMENT '备注',
    `create_dept` bigint(20) COMMENT '创建部门',
    `create_by`   bigint(20) COMMENT '创建者',
    `create_time` datetime COMMENT '创建时间',
    `update_by`   bigint(20) COMMENT '更新者',
    `update_time` datetime COMMENT '更新时间',
    `del_flag`    char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`   varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='文档质控细节';

CREATE TABLE `document_quality_control_task_log`
(
    `id`            bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `task_id`       bigint unsigned             NOT NULL COMMENT '质控任务',
    `detail_id`     bigint unsigned             NOT NULL COMMENT '指控环节',
    `executor`      bigint unsigned             NOT NULL COMMENT '执行人',
    `execute_time`  datetime COMMENT '执行时间',
    `result`        tinyint(1) unsigned COMMENT '质控结果',
    `question_type` char(1) COMMENT '问题分类',
    `option`        varchar(255) COMMENT '质控意见',
    `dealer`        bigint COMMENT '指定处理人',
    `deadline`      date COMMENT '处理截止日期',
    `create_dept`   bigint(20) COMMENT '创建部门',
    `create_by`     bigint(20) COMMENT '创建者',
    `create_time`   datetime COMMENT '创建时间',
    `update_by`     bigint(20) COMMENT '更新者',
    `update_time`   datetime COMMENT '更新时间',
    `del_flag`      char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`     varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='文档质控日志';

CREATE TABLE `agreement_setting`
(
    `id`                bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `user_agreement`    text COMMENT '用户协议',
    `privacy_agreement` text COMMENT '隐私协议',
    `create_dept`       bigint(20) COMMENT '创建部门',
    `create_by`         bigint(20) COMMENT '创建者',
    `create_time`       datetime COMMENT '创建时间',
    `update_by`         bigint(20) COMMENT '更新者',
    `update_time`       datetime COMMENT '更新时间',
    `del_flag`          char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`         varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='协议设置';

CREATE TABLE `ai_setting`
(
    `id`           bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `enabled_flag` tinyint(1)                  NOT NULL DEFAULT 0 COMMENT '是否开启',
    `create_dept`  bigint(20) COMMENT '创建部门',
    `create_by`    bigint(20) COMMENT '创建者',
    `create_time`  datetime COMMENT '创建时间',
    `update_by`    bigint(20) COMMENT '更新者',
    `update_time`  datetime COMMENT '更新时间',
    `del_flag`     char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`    varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='AI设置';

CREATE TABLE `carousel_setting`
(
    `id`           bigint unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '序号',
    `ossId`        bigint unsigned             NOT NULL COMMENT '图片',
    `sort`         int    unsigned             NOT NULL DEFAULT 0 COMMENT '排序',
    `note`         varchar(255)                COMMENT '备注',
    `create_dept`  bigint(20) COMMENT '创建部门',
    `create_by`    bigint(20) COMMENT '创建者',
    `create_time`  datetime COMMENT '创建时间',
    `update_by`    bigint(20) COMMENT '更新者',
    `update_time`  datetime COMMENT '更新时间',
    `del_flag`     char(1)                              DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `tenant_id`    varchar(40) COMMENT '租户id'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='轮播图设置';