zjrs-jeecgBoot/.docs/sql/失败诊断模块建表语句.sql

-- ============================================================
-- 湛江市智慧人社运营运维（2025-2027年）项目
-- 就业一湛通服务平台 - 失败诊断 达梦数据库建表脚本
--
-- 依据：《需求规格说明书》第3.3节 - 失败诊断
--   3.3.1 失败原因明细
--   3.3.2 失败诊断深度分析（含失败诊断、服务跟进、发送消息）
-- 数据库：达梦数据库 (DM8)
-- 创建日期：2025-06-02
-- ============================================================

-- ============================================================
-- 表1：失败原因明细表
-- 说明：记录求职人员的求职失败原因明细数据
-- ============================================================
CREATE TABLE INTERVIEW_FAIL_REASON (
    ID                              VARCHAR(36)     NOT NULL,
    PERSONAL_ID                     VARCHAR(36)     NOT NULL,
    PROVINCE_PERSONAL_ID            VARCHAR(36),
    ENTERPRISE_NAME                 VARCHAR(200),
    JOB_NAME                        VARCHAR(200),
    INTERVIEW_METHOD                VARCHAR(50),
    DELIVERY_TIME                   DATETIME,
    INTERVIEW_TIME                  DATETIME,
    FAIL_REASON                     TEXT,
    DATA_SOURCE                     VARCHAR(50),
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERVIEW_FAIL_REASON IS '失败原因明细表';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.ID IS '主键ID';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.PERSONAL_ID IS '关联本系统个人基本信息ID（PERSONAL_INFO主键）';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.PROVINCE_PERSONAL_ID IS '省系统人员表ID';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.ENTERPRISE_NAME IS '企业名称';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.JOB_NAME IS '应聘岗位名称';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.INTERVIEW_METHOD IS '面试方式';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.DELIVERY_TIME IS '投递时间';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.INTERVIEW_TIME IS '面试时间';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.FAIL_REASON IS '面试不通过原因（原始数据回流）';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.DATA_SOURCE IS '数据来源（省系统/市系统/其他）';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.UPDATE_BY IS '修改人';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN INTERVIEW_FAIL_REASON.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_INTERVIEW_FAIL_REASON_PERSONAL ON INTERVIEW_FAIL_REASON(PERSONAL_ID);
CREATE INDEX IDX_INTERVIEW_FAIL_REASON_ENTERPRISE ON INTERVIEW_FAIL_REASON(ENTERPRISE_NAME);


-- ============================================================
-- 表2：失败诊断分析表
-- 说明：对求职者的失败原因进行次数汇总统计分析
-- ============================================================
CREATE TABLE INTERVIEW_FAIL_DIAGNOSIS (
    ID                              VARCHAR(36)     NOT NULL,
    PERSONAL_ID                     VARCHAR(36)     NOT NULL,
    PROVINCE_PERSONAL_ID            VARCHAR(36),
    FAIL_COUNT                      INTEGER         NOT NULL DEFAULT 0,
    LOCAL_TAGS                      VARCHAR(500),
    IS_FOLLOWED                     VARCHAR(10),
    LAST_NOTIFY_TIME                DATE,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERVIEW_FAIL_DIAGNOSIS IS '失败诊断分析表';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.ID IS '主键ID';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.PERSONAL_ID IS '关联本系统个人基本信息ID（PERSONAL_INFO主键）';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.PROVINCE_PERSONAL_ID IS '省系统人员表ID';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.FAIL_COUNT IS '面试失败次数（累计次数）';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.LOCAL_TAGS IS '本地标签（可多个，支持本地修改）';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.IS_FOLLOWED IS '是否已跟进（有本地服务记录则视为已跟进）';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.LAST_NOTIFY_TIME IS '最后通知时间';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.UPDATE_BY IS '修改人';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_INTERVIEW_FAIL_DIAGNOSIS_PERSONAL ON INTERVIEW_FAIL_DIAGNOSIS(PERSONAL_ID);
CREATE INDEX IDX_INTERVIEW_FAIL_DIAGNOSIS_COUNT ON INTERVIEW_FAIL_DIAGNOSIS(FAIL_COUNT);


-- ============================================================
-- 表3：服务跟进表
-- 说明：记录对求职失败人员的服务跟进情况
-- ============================================================
CREATE TABLE INTERVIEW_FAIL_FOLLOW_UP (
    ID                              VARCHAR(36)     NOT NULL,
    DIAGNOSIS_ID                    VARCHAR(36)     NOT NULL,
    FULL_NAME                       VARCHAR(100),
    SERVICE_CONTENT                 TEXT            NOT NULL,
    SERVICE_TIME                    DATE            NOT NULL,
    SERVICE_PERSON                  VARCHAR(100),
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERVIEW_FAIL_FOLLOW_UP IS '服务跟进表';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.ID IS '主键ID';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.DIAGNOSIS_ID IS '关联失败诊断分析ID（INTERVIEW_FAIL_DIAGNOSIS主键）';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.FULL_NAME IS '姓名';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SERVICE_CONTENT IS '服务内容';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SERVICE_TIME IS '服务时间（年月日，默认当天日期，支持修改）';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SERVICE_PERSON IS '服务人员（系统自动加载用户姓名）';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.UPDATE_BY IS '修改人';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_INTERVIEW_FAIL_FOLLOW_DIAGNOSIS ON INTERVIEW_FAIL_FOLLOW_UP(DIAGNOSIS_ID);


-- ============================================================
-- 表4：发送消息表
-- 说明：记录向求职失败人员发送消息的情况
-- ============================================================
CREATE TABLE INTERVIEW_FAIL_MESSAGE (
    ID                              VARCHAR(36)     NOT NULL,
    DIAGNOSIS_ID                    VARCHAR(36)     NOT NULL,
    MESSAGE_SUBJECT                 VARCHAR(200)    NOT NULL,
    MESSAGE_CONTENT                 TEXT            NOT NULL,
    SENDER                          VARCHAR(100),
    SEND_TIME                       DATETIME,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERVIEW_FAIL_MESSAGE IS '发送消息表';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.ID IS '主键ID';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.DIAGNOSIS_ID IS '关联失败诊断分析ID（INTERVIEW_FAIL_DIAGNOSIS主键）';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.MESSAGE_SUBJECT IS '消息主题';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.MESSAGE_CONTENT IS '消息内容';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.SENDER IS '推送人（系统自动加载用户姓名）';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.SEND_TIME IS '推送时间（系统自动记录）';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.UPDATE_BY IS '修改人';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_INTERVIEW_FAIL_MESSAGE_DIAGNOSIS ON INTERVIEW_FAIL_MESSAGE(DIAGNOSIS_ID);


-- ============================================================
-- 表5：失败标签配置表
-- 说明：失败原因关键字匹配的本地标签编码映射配置
-- ============================================================
CREATE TABLE INTERVIEW_FAIL_TAG (
    ID                              VARCHAR(36)     NOT NULL,
    TAG_CODE                        VARCHAR(20)     NOT NULL,
    TAG_NAME                        VARCHAR(200)    NOT NULL,
    TAG_DESCRIPTION                 VARCHAR(500),
    MATCH_KEYWORDS                  VARCHAR(1000),
    SORT_ORDER                      INTEGER,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERVIEW_FAIL_TAG IS '失败标签配置表';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.ID IS '主键ID';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.TAG_CODE IS '标签编码（FAIL001~FAIL008）';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.TAG_NAME IS '标签名称';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.TAG_DESCRIPTION IS '标签说明';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.MATCH_KEYWORDS IS '关键字匹配规则';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.SORT_ORDER IS '排序序号';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.UPDATE_BY IS '修改人';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN INTERVIEW_FAIL_TAG.SYS_ORG_CODE IS '组织机构编号';

CREATE UNIQUE INDEX IDX_INTERVIEW_FAIL_TAG_CODE ON INTERVIEW_FAIL_TAG(TAG_CODE);


-- ============================================================
-- 初始数据：失败标签配置
-- ============================================================
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000001', 'FAIL001', '硬性基础条件不符', '企业因年龄/性别/学历等硬性要求拒绝求职者', '年龄,性别,学历,超龄,超岁,岁数,文化程度,文凭', 1);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000002', 'FAIL002', '岗位技能证书不足', '企业因求职者操作不熟练/无证书/无行业经验拒绝', '证书,技能,经验,不熟练,无证,没证,不会操作,无经验,经验不足', 2);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000003', 'FAIL003', '薪资福利期望不匹配', '企业/求职者因薪资、福利、补贴等未达成一致', '薪资,工资,薪酬,福利,补贴,待遇,钱少,太低,给不起,达不到预期', 3);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000004', 'FAIL004', '地域通勤住宿不方便', '企业因求职者嫌距离远/不愿驻外/住宿差等拒绝', '距离,太远,通勤,住宿,宿舍,驻外,外派,交通不便,地点不合适', 4);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000005', 'FAIL005', '体检合规背景不通过', '企业因求职者体检不合格/征信/背景审核失败拒绝', '体检,不合格,征信,背景,背调,政审,无犯罪,证明,健康证,不通过', 5);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000006', 'FAIL006', '面试沟通形象不合适', '企业因求职者沟通能力/仪容形象等拒绝', '沟通,表达,形象,仪表,气质,谈吐,面试,不合适,不匹配,不符', 6);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000007', 'FAIL007', '求职不稳定意愿不足', '企业因求职者短期过渡/拒绝加班倒班等拒绝', '短期,过渡,不稳定,加班,倒班,夜班,两班倒,拒绝加班,不想加班', 7);
INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES
    ('FT0000000000000000000000000000008', 'FAIL008', '其他模糊不明原因', '企业录入内容模糊（如"不合适""不行"），无法归为以上7类的所有情况', '不合适,不行,其他,综合,个人原因,不符合要求', 8);


-- ============================================================
-- 表6：失败诊断分析标签关联表
-- 说明：INTERVIEW_FAIL_DIAGNOSIS 与 INTERVIEW_FAIL_TAG 的多对多关联
-- ============================================================
CREATE TABLE INTERVIEW_FAIL_DIAGNOSIS_TAG (
    DIAGNOSIS_ID                    VARCHAR(36)     NOT NULL    COMMENT '失败诊断分析ID',
    TAG_ID                          VARCHAR(36)     NOT NULL    COMMENT '失败标签配置ID',
    PRIMARY KEY (DIAGNOSIS_ID, TAG_ID)
);
COMMENT ON TABLE INTERVIEW_FAIL_DIAGNOSIS_TAG IS '失败诊断分析标签关联表';

-- ============================================================
-- 外键约束（可选启用）
-- ============================================================
-- ALTER TABLE INTERVIEW_FAIL_REASON ADD CONSTRAINT FK_INTERVIEW_FAIL_REASON_PERSONAL FOREIGN KEY (PERSONAL_ID) REFERENCES PERSONAL_INFO(ID);
-- ALTER TABLE INTERVIEW_FAIL_DIAGNOSIS ADD CONSTRAINT FK_INTERVIEW_FAIL_DIAGNOSIS_PERSONAL FOREIGN KEY (PERSONAL_ID) REFERENCES PERSONAL_INFO(ID);
-- ALTER TABLE INTERVIEW_FAIL_FOLLOW_UP ADD CONSTRAINT FK_INTERVIEW_FAIL_FOLLOW_DIAGNOSIS FOREIGN KEY (DIAGNOSIS_ID) REFERENCES INTERVIEW_FAIL_DIAGNOSIS(ID);
-- ALTER TABLE INTERVIEW_FAIL_MESSAGE ADD CONSTRAINT FK_INTERVIEW_FAIL_MESSAGE_DIAGNOSIS FOREIGN KEY (DIAGNOSIS_ID) REFERENCES INTERVIEW_FAIL_DIAGNOSIS(ID);