-- ============================================================
-- 就业状态自动感知 - 视图SQL（达梦DM8）
-- ============================================================

-- ============================================================
-- 视图1：企业就业状态感知视图
-- 聚合企业信息 + 岗位信息
-- ============================================================
CREATE OR REPLACE VIEW V_ENTERPRISE_EMPLOYMENT_STATUS AS
SELECT
    ei.ID,
    ei.UNIFIED_CREDIT_CODE,
    ei.COMPANY_NAME,
    ei.REG_ADDR_DISTRICT     AS DISTRICT,
    ei.INDUSTRY,
    ei.STAFF_SIZE             AS ENTERPRISE_SCALE,
    ei.BUSINESS_STATUS        AS ENTERPRISE_STATUS,
    (SELECT COUNT(*) FROM POST_INFO pi WHERE pi.ENTERPRISE_ID = ei.ID) AS TOTAL_POST_COUNT,
    (SELECT COUNT(*) FROM POST_INFO pi WHERE pi.ENTERPRISE_ID = ei.ID
        AND (pi.POST_VALID_DATE IS NULL OR pi.POST_VALID_DATE >= SYSDATE)) AS ACTIVE_POST_COUNT,
    (SELECT COUNT(*) FROM POST_INFO pi WHERE pi.ENTERPRISE_ID = ei.ID
        AND pi.POST_VALID_DATE IS NOT NULL AND pi.POST_VALID_DATE < SYSDATE) AS EXPIRED_POST_COUNT,
    NULL AS REGISTERED_EMPLOYEE_COUNT,
    (SELECT AVG((pi2.SALARY_MIN + pi2.SALARY_MAX) / 2) FROM POST_INFO pi2
        WHERE pi2.ENTERPRISE_ID = ei.ID
          AND (pi2.POST_VALID_DATE IS NULL OR pi2.POST_VALID_DATE >= SYSDATE)
          AND pi2.SALARY_MIN IS NOT NULL AND pi2.SALARY_MAX IS NOT NULL) AS AVERAGE_SALARY,
    esl.CUSTOM_TAGS,
    esl.LAST_NOTICE_TIME,
    ei.CREATE_BY,
    ei.CREATE_TIME,
    ei.UPDATE_BY,
    ei.UPDATE_TIME,
    ei.SYS_ORG_CODE
FROM ENTERPRISE_INFO ei
LEFT JOIN ENTERPRISE_STATUS_LOCAL esl ON ei.ID = esl.ENTERPRISE_ID;
COMMENT ON VIEW V_ENTERPRISE_EMPLOYMENT_STATUS IS '企业就业状态感知视图';

-- ============================================================
-- 视图2：个人就业状态感知视图
-- 聚合个人信息 + 就业/失业登记统计
-- ============================================================
CREATE OR REPLACE VIEW V_PERSONAL_EMPLOYMENT_STATUS AS
SELECT
    pi.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,
    pi.JOB_SEARCH_STATUS,
    CASE
        WHEN er.PERSONAL_ID IS NOT NULL AND er.EMPLOY_COUNT > 0 THEN '就业中'
        WHEN ur.PERSONAL_ID IS NOT NULL AND ur.UNEMPLOY_COUNT > 0 THEN '失业中'
        ELSE '未就业'
    END AS EMPLOYMENT_STATUS,
    NVL(er.EMPLOY_COUNT, 0)   AS EMPLOY_REG_COUNT,
    NVL(ur.UNEMPLOY_COUNT, 0) AS UNEMPLOY_REG_COUNT,
    NULL AS SOCIAL_INSURANCE_STATUS,
    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 PERSONAL_STATUS_LOCAL psl ON pi.ID = psl.PERSONAL_ID
LEFT JOIN (
    SELECT PERSONAL_ID, COUNT(*) AS EMPLOY_COUNT
    FROM EMPLOYMENT_RECORD
    GROUP BY PERSONAL_ID
) er ON pi.ID = er.PERSONAL_ID
LEFT JOIN (
    SELECT PERSONAL_ID, COUNT(*) AS UNEMPLOY_COUNT
    FROM UNEMPLOYMENT_RECORD
    GROUP BY PERSONAL_ID
) ur ON pi.ID = ur.PERSONAL_ID;
COMMENT ON VIEW V_PERSONAL_EMPLOYMENT_STATUS IS '个人就业状态感知视图';