-- ============================================================
-- 湛江市智慧人社运营运维（2025-2027年）项目
-- 就业一湛通服务平台 - 就业援助模块（初步符合条件人员 + 就业援助管理）
-- 达梦数据库建表脚本
--
-- 依据：《需求规格说明书》第3.7节 - 就业援助
-- 3.7.1 初步符合条件人员
-- 3.7.2 就业援助管理
-- 数据库：达梦数据库 (DM8)
-- 注意：达梦不支持行内COMMENT语法，统一使用COMMENT ON
-- 创建日期：2026-06-09
-- ============================================================

-- ============================================================
-- 3.7.1 初步符合条件人员表
-- ============================================================
DROP TABLE IF EXISTS PRELIMINARY_ELIGIBLE;
CREATE TABLE PRELIMINARY_ELIGIBLE
(
    ID               VARCHAR(36)  NOT NULL,
    PERSONAL_ID      VARCHAR(36)  NOT NULL,
    APPLIED_AID      VARCHAR(10),
    LAST_NOTICE_TIME DATETIME,
    DATA_SOURCE      VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE PRELIMINARY_ELIGIBLE IS '初步符合条件人员信息表';
COMMENT ON COLUMN PRELIMINARY_ELIGIBLE.ID IS '主键ID';
COMMENT ON COLUMN PRELIMINARY_ELIGIBLE.PERSONAL_ID IS '关联个人信息ID';
COMMENT ON COLUMN PRELIMINARY_ELIGIBLE.APPLIED_AID IS '是否已申请援助（是/否）';
COMMENT ON COLUMN PRELIMINARY_ELIGIBLE.LAST_NOTICE_TIME IS '最后通知时间';
COMMENT ON COLUMN PRELIMINARY_ELIGIBLE.DATA_SOURCE IS '数据来源';

-- ============================================================
-- 3.7.2 就业援助管理表
-- ============================================================
DROP TABLE IF EXISTS EMPLOYMENT_ASSISTANCE;
CREATE TABLE EMPLOYMENT_ASSISTANCE
(
    ID                 VARCHAR(36)  NOT NULL,
    PERSONAL_ID        VARCHAR(36)  NOT NULL,
    PROVINCE_RECORD_ID VARCHAR(100),
    APPLY_NUMBER       VARCHAR(100),
    AID_TYPE           VARCHAR(100),
    AID_STATUS         VARCHAR(100),
    LAST_NOTICE_TIME   DATETIME,
    PRIMARY KEY (ID)
);

COMMENT ON TABLE EMPLOYMENT_ASSISTANCE IS '就业援助管理信息表';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.ID IS '主键ID';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.PERSONAL_ID IS '关联个人信息ID';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.PROVINCE_RECORD_ID IS '省平台记录ID';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.APPLY_NUMBER IS '申请编号';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.AID_TYPE IS '援助认定类型';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.AID_STATUS IS '援助认定状态';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE.LAST_NOTICE_TIME IS '最后通知时间';

-- ============================================================
-- 就业援助管理-服务跟进记录表
-- ============================================================
DROP TABLE IF EXISTS EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW;
CREATE TABLE EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW
(
    ID                       VARCHAR(36)  NOT NULL,
    EMPLOYMENT_ASSISTANCE_ID VARCHAR(36)  NOT NULL COMMENT '关联就业援助管理ID',
    SERVICE_CONTENT          TEXT         COMMENT '服务内容',
    SERVICE_TIME             DATETIME     COMMENT '服务时间',
    SERVICE_PROVIDER         VARCHAR(100) COMMENT '服务人员',
    PRIMARY KEY (ID)
);

COMMENT ON TABLE EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW IS '就业援助管理-服务跟进记录表';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW.ID IS '主键ID';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW.EMPLOYMENT_ASSISTANCE_ID IS '关联就业援助管理ID';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW.SERVICE_CONTENT IS '服务内容';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW.SERVICE_TIME IS '服务时间';
COMMENT ON COLUMN EMPLOYMENT_ASSISTANCE_SERVICE_FOLLOW.SERVICE_PROVIDER IS '服务人员';