-- ============================================================
-- 湛江市智慧人社运营运维（2025-2027年）项目
-- 就业一湛通服务平台 - 招聘业务三表（投递/面试/录用）
-- 达梦(DM)数据库建表脚本
--
-- 说明：打通"投递→筛选→面试→录用"完整招聘链路
-- 关联表：PERSONAL_INFO, RESUME_INFO, ENTERPRISE_INFO, POST_INFO
-- 数据库：达梦数据库 (DM8)
-- 创建日期：2026-06-15
-- ============================================================

-- ============================================================
-- 1. 简历投递记录表 JOB_APPLICATION
-- ============================================================
CREATE TABLE IF NOT EXISTS JOB_APPLICATION (
    ID                VARCHAR(36)   NOT NULL,
    PERSONAL_ID       VARCHAR(36)   NOT NULL,
    RESUME_ID         VARCHAR(36),
    ENTERPRISE_ID     VARCHAR(36)   NOT NULL,
    POST_ID           VARCHAR(36)   NOT NULL,
    POST_NAME         VARCHAR(200),
    ENTERPRISE_NAME   VARCHAR(200),
    PERSONAL_NAME     VARCHAR(100),
    APPLY_TIME        TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    STATUS            VARCHAR(10)   DEFAULT '0',
    REMARK            VARCHAR(500),
    CREATE_BY         VARCHAR(50),
    CREATE_TIME       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY         VARCHAR(50),
    UPDATE_TIME       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE      VARCHAR(64),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE JOB_APPLICATION IS '简历投递记录表';
COMMENT ON COLUMN JOB_APPLICATION.ID IS '主键ID';
COMMENT ON COLUMN JOB_APPLICATION.PERSONAL_ID IS '投递人ID（关联PERSONAL_INFO）';
COMMENT ON COLUMN JOB_APPLICATION.RESUME_ID IS '投递的简历ID（关联RESUME_INFO）';
COMMENT ON COLUMN JOB_APPLICATION.ENTERPRISE_ID IS '投递的企业ID（关联ENTERPRISE_INFO）';
COMMENT ON COLUMN JOB_APPLICATION.POST_ID IS '投递的岗位ID（关联POST_INFO）';
COMMENT ON COLUMN JOB_APPLICATION.POST_NAME IS '岗位名称（冗余）';
COMMENT ON COLUMN JOB_APPLICATION.ENTERPRISE_NAME IS '企业名称（冗余）';
COMMENT ON COLUMN JOB_APPLICATION.PERSONAL_NAME IS '求职者姓名（冗余）';
COMMENT ON COLUMN JOB_APPLICATION.APPLY_TIME IS '投递时间';
COMMENT ON COLUMN JOB_APPLICATION.STATUS IS '投递状态：0-待查看，1-筛选通过，2-筛选不通过，3-已邀请面试，4-已取消';
COMMENT ON COLUMN JOB_APPLICATION.REMARK IS '企业备注';
COMMENT ON COLUMN JOB_APPLICATION.CREATE_BY IS '创建人';
COMMENT ON COLUMN JOB_APPLICATION.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN JOB_APPLICATION.UPDATE_BY IS '修改人';
COMMENT ON COLUMN JOB_APPLICATION.UPDATE_TIME IS '修改时间';
COMMENT ON COLUMN JOB_APPLICATION.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_JA_PERSONAL_ID ON JOB_APPLICATION(PERSONAL_ID);
CREATE INDEX IDX_JA_ENTERPRISE_ID ON JOB_APPLICATION(ENTERPRISE_ID);
CREATE INDEX IDX_JA_POST_ID ON JOB_APPLICATION(POST_ID);
CREATE INDEX IDX_JA_STATUS ON JOB_APPLICATION(STATUS);

-- ============================================================
-- 2. 面试记录表 INTERVIEW_RECORD
-- ============================================================
CREATE TABLE IF NOT EXISTS INTERVIEW_RECORD (
    ID                VARCHAR(36)   NOT NULL,
    APPLICATION_ID    VARCHAR(36)   NOT NULL,
    PERSONAL_ID       VARCHAR(36)   NOT NULL,
    ENTERPRISE_ID     VARCHAR(36)   NOT NULL,
    POST_ID           VARCHAR(36)   NOT NULL,
    RESUME_ID         VARCHAR(36),
    PERSONAL_NAME     VARCHAR(100),
    ENTERPRISE_NAME   VARCHAR(200),
    POST_NAME         VARCHAR(200),
    INTERVIEW_TIME    TIMESTAMP,
    INTERVIEW_METHOD  VARCHAR(50),
    INTERVIEW_ADDRESS VARCHAR(500),
    ONLINE_LINK       VARCHAR(500),
    CONTACT_PERSON    VARCHAR(100),
    CONTACT_PHONE     VARCHAR(20),
    INTERVIEW_RESULT  VARCHAR(10)   DEFAULT '0',
    RESULT_REMARK     VARCHAR(500),
    CREATE_BY         VARCHAR(50),
    CREATE_TIME       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY         VARCHAR(50),
    UPDATE_TIME       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE      VARCHAR(64),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERVIEW_RECORD IS '面试记录表';
COMMENT ON COLUMN INTERVIEW_RECORD.ID IS '主键ID';
COMMENT ON COLUMN INTERVIEW_RECORD.APPLICATION_ID IS '关联投递记录ID（关联JOB_APPLICATION）';
COMMENT ON COLUMN INTERVIEW_RECORD.PERSONAL_ID IS '求职者ID（关联PERSONAL_INFO）';
COMMENT ON COLUMN INTERVIEW_RECORD.ENTERPRISE_ID IS '企业ID（关联ENTERPRISE_INFO）';
COMMENT ON COLUMN INTERVIEW_RECORD.POST_ID IS '岗位ID（关联POST_INFO）';
COMMENT ON COLUMN INTERVIEW_RECORD.RESUME_ID IS '简历ID（关联RESUME_INFO）';
COMMENT ON COLUMN INTERVIEW_RECORD.PERSONAL_NAME IS '求职者姓名（冗余）';
COMMENT ON COLUMN INTERVIEW_RECORD.ENTERPRISE_NAME IS '企业名称（冗余）';
COMMENT ON COLUMN INTERVIEW_RECORD.POST_NAME IS '岗位名称（冗余）';
COMMENT ON COLUMN INTERVIEW_RECORD.INTERVIEW_TIME IS '面试时间';
COMMENT ON COLUMN INTERVIEW_RECORD.INTERVIEW_METHOD IS '面试方式（字典interview_method）';
COMMENT ON COLUMN INTERVIEW_RECORD.INTERVIEW_ADDRESS IS '面试地点';
COMMENT ON COLUMN INTERVIEW_RECORD.ONLINE_LINK IS '线上面试链接';
COMMENT ON COLUMN INTERVIEW_RECORD.CONTACT_PERSON IS '面试联系人';
COMMENT ON COLUMN INTERVIEW_RECORD.CONTACT_PHONE IS '联系电话';
COMMENT ON COLUMN INTERVIEW_RECORD.INTERVIEW_RESULT IS '面试结果：0-待面试，1-面试通过，2-面试不通过';
COMMENT ON COLUMN INTERVIEW_RECORD.RESULT_REMARK IS '面试结果备注';
COMMENT ON COLUMN INTERVIEW_RECORD.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERVIEW_RECORD.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERVIEW_RECORD.UPDATE_BY IS '修改人';
COMMENT ON COLUMN INTERVIEW_RECORD.UPDATE_TIME IS '修改时间';
COMMENT ON COLUMN INTERVIEW_RECORD.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_IR_APPLICATION_ID ON INTERVIEW_RECORD(APPLICATION_ID);
CREATE INDEX IDX_IR_PERSONAL_ID ON INTERVIEW_RECORD(PERSONAL_ID);
CREATE INDEX IDX_IR_ENTERPRISE_ID ON INTERVIEW_RECORD(ENTERPRISE_ID);
CREATE INDEX IDX_IR_RESULT ON INTERVIEW_RECORD(INTERVIEW_RESULT);

-- ============================================================
-- 3. 录用记录表 EMPLOYMENT_OFFER
-- ============================================================
CREATE TABLE IF NOT EXISTS EMPLOYMENT_OFFER (
    ID                VARCHAR(36)   NOT NULL,
    INTERVIEW_ID      VARCHAR(36)   NOT NULL,
    APPLICATION_ID    VARCHAR(36)   NOT NULL,
    PERSONAL_ID       VARCHAR(36)   NOT NULL,
    ENTERPRISE_ID     VARCHAR(36)   NOT NULL,
    POST_ID           VARCHAR(36)   NOT NULL,
    PERSONAL_NAME     VARCHAR(100),
    ENTERPRISE_NAME   VARCHAR(200),
    POST_NAME         VARCHAR(200),
    OFFER_SALARY      DECIMAL(12,2),
    ENTRY_DATE        DATE,
    CONTRACT_PERIOD   VARCHAR(50),
    OFFER_REMARK      VARCHAR(500),
    STATUS            VARCHAR(10)   DEFAULT '0',
    CREATE_BY         VARCHAR(50),
    CREATE_TIME       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY         VARCHAR(50),
    UPDATE_TIME       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE      VARCHAR(64),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE EMPLOYMENT_OFFER IS '录用记录表';
COMMENT ON COLUMN EMPLOYMENT_OFFER.ID IS '主键ID';
COMMENT ON COLUMN EMPLOYMENT_OFFER.INTERVIEW_ID IS '关联面试记录ID（关联INTERVIEW_RECORD）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.APPLICATION_ID IS '关联投递记录ID（关联JOB_APPLICATION）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.PERSONAL_ID IS '求职者ID（关联PERSONAL_INFO）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.ENTERPRISE_ID IS '企业ID（关联ENTERPRISE_INFO）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.POST_ID IS '岗位ID（关联POST_INFO）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.PERSONAL_NAME IS '求职者姓名（冗余）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.ENTERPRISE_NAME IS '企业名称（冗余）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.POST_NAME IS '岗位名称（冗余）';
COMMENT ON COLUMN EMPLOYMENT_OFFER.OFFER_SALARY IS '录用薪资';
COMMENT ON COLUMN EMPLOYMENT_OFFER.ENTRY_DATE IS '预计入职日期';
COMMENT ON COLUMN EMPLOYMENT_OFFER.CONTRACT_PERIOD IS '合同期限';
COMMENT ON COLUMN EMPLOYMENT_OFFER.OFFER_REMARK IS '录用备注';
COMMENT ON COLUMN EMPLOYMENT_OFFER.STATUS IS '录用状态：0-待签约，1-签订中，2-已签约，3-已结束，4-已拒绝';
COMMENT ON COLUMN EMPLOYMENT_OFFER.CREATE_BY IS '创建人';
COMMENT ON COLUMN EMPLOYMENT_OFFER.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN EMPLOYMENT_OFFER.UPDATE_BY IS '修改人';
COMMENT ON COLUMN EMPLOYMENT_OFFER.UPDATE_TIME IS '修改时间';
COMMENT ON COLUMN EMPLOYMENT_OFFER.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_EO_INTERVIEW_ID ON EMPLOYMENT_OFFER(INTERVIEW_ID);
CREATE INDEX IDX_EO_PERSONAL_ID ON EMPLOYMENT_OFFER(PERSONAL_ID);
CREATE INDEX IDX_EO_ENTERPRISE_ID ON EMPLOYMENT_OFFER(ENTERPRISE_ID);
CREATE INDEX IDX_EO_STATUS ON EMPLOYMENT_OFFER(STATUS);

-- ============================================================
-- 4. 字典数据 - 投递状态 (application_status)
-- ============================================================
INSERT INTO "DICTIONARY" VALUES ('application_status', '投递状态', 38, 1, 0);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000111', '', 'application_status', 0, '待查看', 1, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000112', '', 'application_status', 1, '筛选通过', 2, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000113', '', 'application_status', 2, '筛选不通过', 3, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000114', '', 'application_status', 3, '已邀请面试', 4, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000115', '', 'application_status', 4, '已取消', 5, 1, 1, NULL);

-- ============================================================
-- 5. 字典数据 - 面试方式 (interview_method)
-- ============================================================
INSERT INTO "DICTIONARY" VALUES ('interview_method', '面试方式', 39, 1, 0);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000121', '', 'interview_method', 1, '现场面试', 1, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000122', '', 'interview_method', 2, '视频面试', 2, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000123', '', 'interview_method', 3, '电话面试', 3, 1, 1, NULL);

-- ============================================================
-- 6. 字典数据 - 面试结果 (interview_result)
-- ============================================================
INSERT INTO "DICTIONARY" VALUES ('interview_result', '面试结果', 40, 1, 0);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000131', '', 'interview_result', 0, '待面试', 1, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000132', '', 'interview_result', 1, '面试通过', 2, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000133', '', 'interview_result', 2, '面试不通过', 3, 1, 1, NULL);

-- ============================================================
-- 7. 字典数据 - 录用状态 (offer_status)
-- ============================================================
INSERT INTO "DICTIONARY" VALUES ('offer_status', '录用状态', 41, 1, 0);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000141', '', 'offer_status', 0, '待签约', 1, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000142', '', 'offer_status', 1, '签订中', 2, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000143', '', 'offer_status', 2, '已签约', 3, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000144', '', 'offer_status', 3, '已结束', 4, 1, 1, NULL);
INSERT INTO "DICTIONARY_ITEM" VALUES ('178060500000145', '', 'offer_status', 4, '已拒绝', 5, 1, 1, NULL);