-- 离职前置条件校验视图（性能优化版）
-- 包含：资格证申报、等级证申报、师资信息校验
-- 优化：使用LEFT JOIN替代EXISTS，消除DEPENDENT SUBQUERY
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER 
VIEW `vm_not_matched_employee_leave_condition` AS 
SELECT DISTINCT
    `e`.`id` AS `id`,
    CONCAT(`u`.`name`, '[', `c`.`number`, ']') AS `info` 
FROM 
    `obpm2`.`tenant_employees` `e`
    JOIN `obpm2`.`tenant_users` `u` ON `e`.`tenant_user_id` = `u`.`id`
    JOIN `obpm2`.`tenant_user_credentials` `c` ON `u`.`selected_credential_id` = `c`.`id`
    LEFT JOIN (
        -- 条件1：资格证申报状态为'0'（申报中）
        SELECT DISTINCT
            `t1`.`ITEM_SECURITYID` AS `employee_id`,
            `t1`.`DOMAINID` AS `organization_id`
        FROM `tms`.`tlk_qualify_person_apply` `t1`
        WHERE `t1`.`ITEM_APPLYSTATUS` = '0'
        
        UNION ALL
        
        -- 条件2：等级证申报状态为'0'（申报中）且成绩状态为'0'（已确认成绩）
        SELECT DISTINCT
            `t2`.`ITEM_securityId` AS `employee_id`,
            `t2`.`DOMAINID` AS `organization_id`
        FROM `tms`.`tlk_grade_registration` `t2`
        JOIN `tms`.`tlk_level_class_student` `t3` 
            ON `t2`.`ITEM_idCardNo` = `t3`.`ITEM_IDCARDNO`
        WHERE `t2`.`ITEM_applyStatus` = '0'
          AND `t3`.`ITEM_SCORESTATE` = '0'
        
        UNION ALL
        
        -- 条件3：师资信息未删除（还在师资库中）
        SELECT DISTINCT
            `t4`.`ITEM_PERSON_ID` AS `employee_id`,
            `t4`.`DOMAINID` AS `organization_id`
        FROM `baibaodunflow`.`tlk_faculty_information` `t4`
        WHERE (`t4`.`ITEM_IS_DELETE` IS NULL OR `t4`.`ITEM_IS_DELETE` != '1')
    ) `conditions` 
        ON `e`.`id` = `conditions`.`employee_id`
        AND `e`.`organization_id` = `conditions`.`organization_id`
WHERE `conditions`.`employee_id` IS NOT NULL;

