-- 岗位推荐列表视图
-- 将 job_recommend、personal_info 和 post_info 的关联查询封装为视图
-- 创建前先删除已存在的同名视图（兼容重复执行）
DROP VIEW IF EXISTS v_job_recommend_list;

CREATE VIEW v_job_recommend_list AS
SELECT
    job_recommend.id,
    job_recommend.personal_id,
    job_recommend.post_id,
    job_recommend.recommend_type,
    job_recommend.recommend_opinion,
    job_recommend.recommend_status,
    personal_info.full_name,
    personal_info.gender,
    personal_info.contact_phone,
    personal_info.education,
    post_info.post_name,
    post_info.work_location,
    enterprise_info.company_name
FROM job_recommend
LEFT JOIN personal_info ON job_recommend.personal_id = personal_info.id
LEFT JOIN post_info ON job_recommend.post_id = post_info.id
LEFT JOIN enterprise_info ON post_info.enterprise_id = enterprise_info.id;