-- ============================================================
-- 就业一湛通服务平台 - 就业登记模块 建表SQL（达梦数据库版）
-- 表: EMPLOYMENT_REGISTRATION（就业登记主表）
--     EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW（服务跟进记录表）
-- ============================================================

-- ============================================================
-- 1. 就业登记主表
-- ============================================================
CREATE TABLE EMPLOYMENT_REGISTRATION (
    ID VARCHAR(32) NOT NULL,
    PERSONAL_ID VARCHAR(32),
    PROVINCE_RECORD_ID VARCHAR(64),
    REGISTRATION_STATUS VARCHAR(32),
    LAST_NOTICE_TIME TIMESTAMP,
    CREATE_BY VARCHAR(50),
    CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY VARCHAR(50),
    UPDATE_TIME TIMESTAMP,
    PRIMARY KEY (ID)
);

COMMENT ON TABLE EMPLOYMENT_REGISTRATION IS '就业登记';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.ID IS '主键';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.PERSONAL_ID IS '关联个人信息ID（关联personal_info表）';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.PROVINCE_RECORD_ID IS '省平台记录ID';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.REGISTRATION_STATUS IS '就业登记状态（应登记、已登记、未登记）';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.LAST_NOTICE_TIME IS '最后通知时间';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.CREATE_BY IS '创建人';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.UPDATE_BY IS '更新人';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION.UPDATE_TIME IS '更新时间';

-- ============================================================
-- 2. 服务跟进记录表
-- ============================================================
CREATE TABLE EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW (
    ID VARCHAR(32) NOT NULL,
    REGISTRATION_ID VARCHAR(32),
    SERVICE_CONTENT VARCHAR(1000),
    SERVICE_TIME TIMESTAMP,
    SERVICE_PROVIDER VARCHAR(100),
    CREATE_BY VARCHAR(50),
    CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY VARCHAR(50),
    UPDATE_TIME TIMESTAMP,
    PRIMARY KEY (ID)
);

COMMENT ON TABLE EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW IS '就业登记-服务跟进记录';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.ID IS '主键';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.REGISTRATION_ID IS '关联就业登记ID';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.SERVICE_CONTENT IS '服务内容';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.SERVICE_TIME IS '服务时间';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.SERVICE_PROVIDER IS '服务人员';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.CREATE_BY IS '创建人';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.UPDATE_BY IS '更新人';
COMMENT ON COLUMN EMPLOYMENT_REGISTRATION_SERVICE_FOLLOW.UPDATE_TIME IS '更新时间';