-- ============================================================
-- 政策推送和岗位信息变动推送 - 视图SQL（达梦DM8）
-- ============================================================

-- ============================================================
-- 画像变动管理视图
-- 从个人信息、简历、失败诊断、就业登记、失业登记聚合计算画像状态
-- 注：无独立物理表，自定义标签/通知时间与个人就业状态感知共用 PERSONAL_STATUS_LOCAL
-- ============================================================
CREATE OR REPLACE VIEW V_PROFILE_CHANGE AS
SELECT
    pi.ID                  AS PERSONAL_ID,
    pi.ID_NUMBER,
    pi.FULL_NAME,
    pi.HOUSEHOLD_LOCATION  AS HOUSEHOLD_DISTRICT,
    pi.GENDER,
    pi.EDUCATION,
    FLOOR(MONTHS_BETWEEN(SYSDATE, pi.BIRTH_DATE) / 12) AS AGE,
    CASE
        WHEN pi.JOB_SEARCH_STATUS IN ('离职-随时到岗','在职-月内到岗','在职-考虑机会','应届毕业生')
             AND (r.ID IS NULL OR (r.VALID_DATE IS NOT NULL AND r.VALID_DATE < SYSDATE))
            THEN '求职信息不完善'
        WHEN fd.ID IS NOT NULL THEN '求职失败'
        WHEN er.PERSONAL_ID IS NOT NULL THEN '就业登记'
        WHEN ur.PERSONAL_ID IS NOT NULL THEN '失业登记'
        ELSE '求职信息不完善'
    END AS PROFILE_STATUS,
    CASE
        WHEN pi.JOB_SEARCH_STATUS IN ('离职-随时到岗','在职-月内到岗','在职-考虑机会','应届毕业生')
             AND r.ID IS NULL
            THEN '无简历'
        WHEN pi.JOB_SEARCH_STATUS IN ('离职-随时到岗','在职-月内到岗','在职-考虑机会','应届毕业生')
             AND r.VALID_DATE IS NOT NULL AND r.VALID_DATE < SYSDATE
            THEN '简历过期'
        WHEN fd.ID IS NOT NULL THEN fd.LOCAL_TAGS
        WHEN er.PERSONAL_ID IS NOT NULL THEN '就业中'
        WHEN ur.PERSONAL_ID IS NOT NULL THEN '失业中'
        ELSE ''
    END AS STATUS_DESC,
    COALESCE(fd.UPDATE_TIME, er.CREATE_TIME, ur.CREATE_TIME, pi.UPDATE_TIME) AS DATA_DATE,
    psl.CUSTOM_TAGS,
    psl.LAST_NOTICE_TIME,
    pi.CREATE_BY,
    pi.CREATE_TIME,
    pi.UPDATE_BY,
    pi.UPDATE_TIME,
    pi.SYS_ORG_CODE
FROM PERSONAL_INFO pi
LEFT JOIN RESUME_INFO r ON pi.ID = r.PERSONAL_ID AND (r.IS_DEFAULT IS NULL OR r.IS_DEFAULT = '是')
LEFT JOIN INTERVIEW_FAIL_DIAGNOSIS fd ON pi.ID = fd.PERSONAL_ID
LEFT JOIN (
    SELECT PERSONAL_ID, COUNT(*) AS EMPLOY_COUNT, MIN(CREATE_TIME) AS CREATE_TIME
    FROM EMPLOYMENT_RECORD
    GROUP BY PERSONAL_ID
) er ON pi.ID = er.PERSONAL_ID
LEFT JOIN (
    SELECT PERSONAL_ID, COUNT(*) AS UNEMPLOY_COUNT, MIN(CREATE_TIME) AS CREATE_TIME
    FROM UNEMPLOYMENT_RECORD
    GROUP BY PERSONAL_ID
) ur ON pi.ID = ur.PERSONAL_ID
LEFT JOIN PERSONAL_STATUS_LOCAL psl ON pi.ID = psl.PERSONAL_ID;
COMMENT ON VIEW V_PROFILE_CHANGE IS '画像变动管理视图';