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

CREATE VIEW v_internship_personnel_list AS
SELECT
    internship_personnel.id,
    internship_personnel.personal_id,
    internship_personnel.internship_post_id,
    internship_personnel.internship_status,
    internship_personnel.start_date,
    internship_personnel.end_date,
    internship_personnel.audit_status,
    internship_personnel.audit_opinion,
    internship_personnel.major_tag,
    internship_personnel.minor_tag,
    internship_personnel.custom_tag,
    personal_info.full_name,
    personal_info.gender,
    TIMESTAMPDIFF(YEAR, personal_info.birth_date, CURDATE()) AS age,
    personal_info.contact_phone,
    personal_info.education,
    personal_info.id_number,
    personal_info.household_location,
    personal_info.current_residence,
    personal_info.job_seeker_category,
    personal_info.job_search_status,
    internship_post.post_name,
    internship_post.company_name
FROM internship_personnel
LEFT JOIN personal_info ON internship_personnel.personal_id = personal_info.id
LEFT JOIN internship_post ON internship_personnel.internship_post_id = internship_post.id;