zjrs-jeecgBoot/.docs/sql/见习人员管理_全部重建.sql

-- ============================================================
-- 湛江市智慧人社运营运维（2025-2027年）项目
-- 就业一湛通服务平台 - 见习人员管理 全部表重建脚本
--
-- 说明：删除并重建所有见习人员相关表和视图，包含最新字段
-- 数据库：达梦数据库 (DM8) 兼容
-- 创建日期：2026-06-04
-- 注意：此脚本需手动执行，不在Flyway中自动运行
-- ============================================================

-- ============================================================
-- 第1步：删除视图和表（按依赖顺序）
-- 达梦数据库 DROP ... IF EXISTS 语法
-- ============================================================
DROP VIEW IF EXISTS v_internship_personnel_list;
DROP TABLE IF EXISTS internship_personnel_service_follow;
DROP TABLE IF EXISTS internship_personnel;

-- ============================================================
-- 第2步：创建见习人员信息表
-- 达梦兼容：COMMENT 不内联，使用 COMMENT ON COLUMN 单独语句
-- ============================================================
CREATE TABLE INTERNSHIP_PERSONNEL
(
    ID                      VARCHAR(36)   NOT NULL,
    PERSONAL_ID             VARCHAR(36)   NOT NULL,
    INTERNSHIP_POST_ID      VARCHAR(36),
    INTERNSHIP_STATUS       VARCHAR(50),
    START_DATE              DATE,
    END_DATE                DATE,
    AUDIT_STATUS            VARCHAR(50)   DEFAULT '待审核',
    AUDIT_OPINION           VARCHAR(500),
    MAJOR_TAG               VARCHAR(50),
    MINOR_TAG               VARCHAR(200),
    CUSTOM_TAG              VARCHAR(500),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERNSHIP_PERSONNEL IS '见习人员信息表';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.ID IS '主键ID';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.PERSONAL_ID IS '关联个人信息ID';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.INTERNSHIP_POST_ID IS '关联见习岗位ID';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.INTERNSHIP_STATUS IS '见习状态（见习中/已期满/已退出）';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.START_DATE IS '见习开始日期';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.END_DATE IS '见习结束日期';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.AUDIT_STATUS IS '审核状态（待审核/已通过/未通过）';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.AUDIT_OPINION IS '审核意见';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.MAJOR_TAG IS '人员大类标签（见习人员）';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.MINOR_TAG IS '人员小类标签';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL.CUSTOM_TAG IS '自定义标签（逗号分隔，字典编码：internship_custom_tag）';

-- ============================================================
-- 第3步：创建见习人员服务跟进记录表
-- ============================================================
CREATE TABLE INTERNSHIP_PERSONNEL_SERVICE_FOLLOW
(
    ID                          VARCHAR(36)      NOT NULL,
    INTERNSHIP_PERSONNEL_ID     VARCHAR(36)      NOT NULL,
    SERVICE_CONTENT             VARCHAR(2000),
    SERVICE_TIME                DATETIME,
    SERVICE_PROVIDER            VARCHAR(100),
    CREATE_BY                   VARCHAR(50),
    CREATE_TIME                 DATETIME,
    UPDATE_BY                   VARCHAR(50),
    UPDATE_TIME                 DATETIME,
    DEL_FLAG                    INT DEFAULT 0,
    PRIMARY KEY (ID)
);

COMMENT ON TABLE INTERNSHIP_PERSONNEL_SERVICE_FOLLOW IS '见习人员服务跟进记录表';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.ID IS '主键ID';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.INTERNSHIP_PERSONNEL_ID IS '关联见习人员ID';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.SERVICE_CONTENT IS '服务内容';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.SERVICE_TIME IS '服务时间';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.SERVICE_PROVIDER IS '服务人员';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.CREATE_BY IS '创建人';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.UPDATE_BY IS '更新人';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.DEL_FLAG IS '删除标识（0未删除，1已删除）';

-- ============================================================
-- 第4步：创建见习人员列表视图
-- 达梦兼容：使用 MONTHS_BETWEEN + FLOOR 替代 TIMESTAMPDIFF
--           使用 CURRENT_DATE 替代 CURDATE()
-- ============================================================
CREATE VIEW v_internship_personnel_list AS
SELECT
    internship_personnel.id,
    internship_personnel.personal_id,
    internship_personnel.internship_post_id,
    internship_personnel.internship_status,
    internship_personnel.start_date,
    internship_personnel.end_date,
    internship_personnel.audit_status,
    internship_personnel.audit_opinion,
    internship_personnel.major_tag,
    internship_personnel.minor_tag,
    internship_personnel.custom_tag,
    personal_info.full_name,
    personal_info.gender,
    FLOOR(MONTHS_BETWEEN(CURRENT_DATE, personal_info.birth_date) / 12) AS age,
    personal_info.contact_phone,
    personal_info.education,
    personal_info.id_number,
    personal_info.household_location,
    personal_info.current_residence,
    personal_info.job_seeker_category,
    personal_info.job_search_status,
    internship_post.post_name,
    internship_post.company_name
FROM internship_personnel
LEFT JOIN personal_info ON internship_personnel.personal_id = personal_info.id
LEFT JOIN internship_post ON internship_personnel.internship_post_id = internship_post.id;

-- ============================================================
-- 第5步：插入测试数据
-- 关联规则：
--   周九(206, 应届高校毕业生) → 软件测试见习生(恒兴科技)
--   吴十(207, 新成长失业青年) → 行政文员见习生(博文教育)
--   郑十一(208, 农村转移就业劳动者) → 机械维修见习生(港湾实业)
--   周九(206) → 农业技术见习生(绿源农业)
--   吴十(207) → 物业管理见习生(阳光物业, 待审核)
-- ============================================================
INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000330, 178060100000206, 178060100000320, '见习中', '2026-04-01', '2026-09-30', '已通过', '符合见习条件，审核通过', '1', '1', NULL);
INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000331, 178060100000207, 178060100000322, '见习中', '2026-05-01', '2026-07-31', '已通过', '符合见习条件，审核通过', '1', '2', NULL);
INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000332, 178060100000208, 178060100000321, '已期满', '2025-12-01', '2026-05-31', '已通过', '符合见习条件，审核通过', '1', '1', NULL);
INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000333, 178060100000206, 178060100000323, '见习中', '2026-03-15', '2026-09-14', '已通过', '计算机专业背景，推荐到软件测试岗位见习', '1', '1', NULL);
INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000334, 178060100000207, 178060100000324, '待审核', NULL, NULL, '待审核', NULL, '1', '2', NULL);