-- ============================================================
-- 职业指导服务 - DDL 修复脚本
-- 用途：修复视图年龄计算
-- ============================================================

-- 视图年龄修复（YEARS_BETWEEN → MONTHS_BETWEEN/12）
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;