-- ============================================================
-- 就业一湛通服务平台 - 职业指导服务 视图年龄修复
-- 数据库：达梦数据库 (DM8)
-- 创建日期：2026-06-12
-- 修复：年龄计算 YEARS_BETWEEN → FLOOR(MONTHS_BETWEEN/12)
-- 原因：YEARS_BETWEEN 可能返回负数或不准确，导致列表年龄显示为负
-- ============================================================

CREATE OR REPLACE VIEW V_CAREER_GUIDANCE_SERVICE AS
SELECT
    cgs.ID,
    cgs.PERSONAL_ID,
    cgs.APPLICATION_REASON,
    cgs.APPLICATION_DATE,
    cgs.IS_FOLLOWED_UP,
    cgs.LAST_NOTIFY_TIME,
    pi.FULL_NAME AS NAME,
    pi.ID_NUMBER AS ID_CARD,
    pi.GENDER,
    FLOOR(MONTHS_BETWEEN(SYSDATE, pi.BIRTH_DATE) / 12) AS AGE,
    pi.EDUCATION,
    pi.HOUSEHOLD_LOCATION,
    pi.CONTACT_PHONE,
    cgs.CREATE_TIME
FROM CAREER_GUIDANCE_SERVICE cgs
LEFT JOIN ZJRS_JEECG_BOOT.PERSONAL_INFO pi ON cgs.PERSONAL_ID = pi.ID;