<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fs.audit.mapper.AuditMapper">

    <select id="selectPendingAuditsByUserId" resultType="com.fs.audit.domain.Audit">
        SELECT *
        FROM audit a   <!-- 建议添加别名，便于子查询引用 -->
        <where>
            <!-- 待办：需要当前用户审核 + 状态为待审核 -->
            <if test="status != null and status == 0">
                AND JSON_CONTAINS(audit_user_ids, CONCAT('[', #{userId}, ']'))
                AND status = 1
            </if>
            <!-- 已办：当前用户参与审核过 + 状态为已完成(通过或退回) -->
            <if test="status != null and status == 1">
                AND JSON_CONTAINS(audit_user_ids, CONCAT('[', #{userId}, ']'))
                AND status IN (2, 3)
            </if>
            <!-- 我发起的 -->
            <if test="status != null and status == 2">
                AND initiator_id = #{userId}
            </if>
            <if test="companyId != null">
                AND company_id = #{companyId}
            </if>
            <if test="initiatorId != null">
                AND initiator_id = #{initiatorId}
            </if>
            <!-- 修正点：集合判空 + separator -->
            <if test="tempCode != null and tempCode.size() > 0">
                AND audit_type IN
                <foreach collection="tempCode" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="taskStatus != null">
                AND status = #{taskStatus}
            </if>
            <!-- 根据发起人姓名或手机号模糊过滤 -->
            <if test="keywords != null and keywords != ''">
                AND EXISTS (
                SELECT 1
                FROM company_user u
                WHERE u.user_id = a.initiator_id   <!-- 使用别名 a -->
                AND (u.user_name LIKE CONCAT('%', #{keywords}, '%')
                OR u.phonenumber LIKE CONCAT('%', #{keywords}, '%'))
                AND u.del_flag = '0'
                )
            </if>

            AND audit_type NOT IN ('AUDIT_FWDRWSCPZ', 'ADUIT_TXSH')

        </where>
        ORDER BY create_time
        <choose>
            <when test="sort != null and sort == 1">ASC</when>
            <otherwise>DESC</otherwise>
        </choose>
    </select>

    <select id="selectPendingAuditsByUserIdCount" resultType="long">
        SELECT COUNT(*)
        FROM audit a   <!-- 添加别名 -->
        <where>
            <!-- 待办：需要当前用户审核 + 状态为待审核 -->
            <if test="status != null and status == 0">
                AND JSON_CONTAINS(a.audit_user_ids, CONCAT('[', #{userId}, ']'))
                AND a.status = 1
            </if>
            <!-- 已办：当前用户参与审核过 + 状态为已完成(通过或退回) -->
            <if test="status != null and status == 1">
                AND JSON_CONTAINS(a.audit_user_ids, CONCAT('[', #{userId}, ']'))
                AND a.status IN (2, 3)
            </if>
            <!-- 我发起的 -->
            <if test="status != null and status == 2">
                AND a.initiator_id = #{userId}
            </if>
            <if test="companyId != null">
                AND a.company_id = #{companyId}
            </if>
            <if test="initiatorId != null">
                AND a.initiator_id = #{initiatorId}
            </if>
            <!-- 修正点：使用 size() > 0 判断集合非空 -->
            <if test="tempCode != null and tempCode.size() > 0">
                AND a.audit_type IN
                <foreach collection="tempCode" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <!-- 如果 taskStatus 与 status 含义不同，确保使用不同的参数名或合并处理 -->
            <if test="taskStatus != null">
                AND a.status = #{taskStatus}
            </if>
            <!-- 根据发起人姓名或手机号模糊过滤（放在最后，不影响语法） -->
            <if test="keywords != null and keywords != ''">
                AND EXISTS (
                SELECT 1
                FROM company_user u
                WHERE u.user_id = a.initiator_id   <!-- 使用别名 a -->
                AND (u.user_name LIKE CONCAT('%', #{keywords}, '%')
                OR u.phonenumber LIKE CONCAT('%', #{keywords}, '%'))
                AND u.del_flag = '0'
                )
            </if>
            AND audit_type NOT IN ('AUDIT_FWDRWSCPZ', 'ADUIT_TXSH')

        </where>
    </select>
    <select id="selectById" parameterType="java.lang.Long"
            resultType="com.fs.audit.domain.Audit">
        SELECT *
        FROM audit
        WHERE id = #{id}
    </select>

</mapper>
