-- 重点关注人员列表视图
-- 将 focus_personnel 和 personal_info 的关联查询封装为视图
-- 创建前先删除已存在的同名视图（兼容重复执行）
DROP VIEW IF EXISTS v_focus_personnel_list;

CREATE VIEW v_focus_personnel_list AS
SELECT
    focus_personnel.id,
    focus_personnel.personal_id,
    focus_personnel.major_tag,
    focus_personnel.minor_tag,
    focus_personnel.custom_tag,
    personal_info.full_name,
    personal_info.gender,
    TIMESTAMPDIFF(YEAR, personal_info.birth_date, CURDATE()) AS age,
    personal_info.education,
    personal_info.household_location,
    personal_info.current_residence,
    personal_info.job_seeker_category,
    personal_info.contact_phone,
    personal_info.job_search_status
FROM focus_personnel
LEFT JOIN personal_info ON focus_personnel.personal_id = personal_info.id;