-- 初步符合条件人员列表视图（达梦数据库版）
-- 将 preliminary_eligible 和 personal_info 的关联查询封装为视图
-- 创建前先删除已存在的同名视图（兼容重复执行）
DROP VIEW IF EXISTS v_preliminary_eligible_list;

CREATE VIEW v_preliminary_eligible_list AS
SELECT
    pe.id,
    pe.personal_id,
    pe.applied_aid,
    pe.last_notice_time,
    pe.data_source,
    pi.full_name,
    pi.gender,
    FLOOR(MONTHS_BETWEEN(CURRENT_DATE, pi.birth_date) / 12) AS age,
    pi.education,
    pi.household_location,
    pi.current_residence,
    pi.job_seeker_category,
    pi.contact_phone,
    pi.job_search_status
FROM preliminary_eligible pe
LEFT JOIN personal_info pi ON pe.personal_id = pi.id;