<?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.microservices.pms.product.mapper.PmsProductRequirementMapper">

    <resultMap type="com.microservices.pms.product.domain.PmsProductRequirement" id="PmsProductRequirementResult">
        <result property="id" column="id"/>
        <result property="index" column="index"/>
        <result property="title" column="title"/>
        <result property="status" column="status"/>
        <result property="priorityId" column="priority_id"/>
        <result property="pmsProductModuleId" column="pms_product_module_id"/>
        <result property="pmsProductPlanId" column="pms_product_plan_id"/>
        <result property="pmsProductIdentifier" column="pms_product_identifier"/>
        <result property="content" column="content"/>
        <result property="tagIds" column="tag_ids"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="fileIdentifiers" column="file_identifiers"/>
        <result property="sort" column="sort"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="delFlag" column="del_flag"/>
        <result property="requirementAssigneeGitlinkIds" column="requirement_assignee_gitlink_ids"/>
        <result property="pmsProjectId" column="pms_project_id"/>
        <result property="fromId" column="from_id"/>
        <result property="projectReqId" column="project_req_id"/>
        <result property="type" column="type"/>
        <result property="identifier" column="identifier"/>
        <result property="sourceUser" column="source_user"/>
        <result property="sourceCompany" column="source_company"/>
        <result property="reviewTime" column="review_time"/>
        <result property="reviewContent" column="review_content"/>
    </resultMap>

    <sql id="selectPmsProductRequirementVo">
        select id,
               title,
               `index`,
               `status`,
               priority_id,
               pms_product_module_id,
               pms_product_plan_id,
               pms_product_identifier,
               content,
               tag_ids,
               start_time,
               end_time,
               file_identifiers,
               sort,
               update_time,
               update_by,
               create_time,
               create_by,
               del_flag,
               requirement_assignee_gitlink_ids,
               pms_project_id,
               from_id,
               project_req_id,
               type,
               source_user,
               source_company,
               identifier
        from pms_product_requirement
    </sql>


    <sql id="selectUserReqAndRecord">
        select ppr.id,
               ppr.title,
               ppr.`index`,
               ppr.`status`,
               ppr.priority_id,
               ppr.pms_product_module_id,
               ppr.pms_product_plan_id,
               ppr.pms_product_identifier,
               ppr.content,
               ppr.tag_ids,
               ppr.start_time,
               ppr.end_time,
               ppr.file_identifiers,
               ppr.sort,
               ppr.update_time,
               ppr.update_by,
               ppr.create_time,
               ppr.create_by,
               ppr.del_flag,
               ppr.requirement_assignee_gitlink_ids,
               ppr.pms_project_id,
               ppr.from_id,
               ppr.project_req_id,
               ppr.type,
               ppr.source_user,
               ppr.source_company,
               ppr.identifier,
               record.review_time,
               record.content as review_content
        from pms_product_requirement as ppr
                 left join (select product_requirement_id, MAX(create_time) as review_time, content
                            from pms_product_req_operation_record
                            where type = 'reviewUserReq'
                              and operation = 'pass'
                            group by product_requirement_id, content) as record
                           on record.product_requirement_id = ppr.id
    </sql>

    <select id="selectPmsProductRequirementList" parameterType="PmsProductRequirement"
            resultMap="PmsProductRequirementResult">
        /*keep orderby*/
        <include refid="selectPmsProductRequirementVo"/>
        <include refid="selectWhere"/>
        order by update_time desc
    </select>
    <select id="selectProductReqSpecsCount" resultType="java.lang.Integer">
        select count(0) from pms_product_requirement
        <include refid="selectReqSpecsWhere"/>
    </select>

    <sql id="selectWhere">
        <where>
            <if test="title != null  and title != ''">and title like concat('%', #{title}, '%')</if>
            <if test="priorityId != null ">and priority_id = #{priorityId}</if>
            <if test="pmsProductPlanId != null ">and pms_product_plan_id = #{pmsProductPlanId}</if>
            <if test="pmsProductIdentifier != null ">and pms_product_identifier = #{pmsProductIdentifier}</if>
            <if test="content != null  and content != ''">and content = #{content}</if>
            <if test="tagId != null  and tagId != ''">and FIND_IN_SET(#{tagId},tag_ids)</if>
            <if test="createBy != null  and createBy != ''">and create_by = #{createBy}</if>
            <if test="startTime != null ">and start_time = #{startTime}</if>
            <if test="endTime != null ">and end_time = #{endTime}</if>
            <if test="fileIdentifiers != null  and fileIdentifiers != ''">and file_identifiers = #{fileIdentifiers}</if>
            <if test="sort != null ">and sort = #{sort}</if>
            <if test="requirementAssigneeGitlinkId != null  and requirementAssigneeGitlinkId != ''">
                and FIND_IN_SET(#{requirementAssigneeGitlinkId},requirement_assignee_gitlink_ids)
            </if>
            <if test="pmsProjectId != null  and pmsProjectId != ''">and pms_project_id = #{pmsProjectId}</if>
            <if test="productIdentifierList != null">
                and pms_product_identifier in
                <foreach item="productIdentifier" collection="productIdentifierList" open="(" separator="," close=")">
                    #{productIdentifier}
                </foreach>
            </if>
            <choose>
                <when test="childrenModuleIdList != null">
                    <if test="childrenModuleIdList != null ">
                        and pms_product_module_id in
                        <foreach item="moduleId" collection="childrenModuleIdList" open="(" separator="," close=")">
                            #{moduleId}
                        </foreach>
                    </if>
                </when>
                <otherwise>
                    <if test="pmsProductModuleId != null ">
                        <if test="pmsProductModuleId != 0">and pms_product_module_id = #{pmsProductModuleId}</if>
                        <if test="pmsProductModuleId == 0">and pms_product_module_id is null</if>
                    </if>
                </otherwise>
            </choose>

        </where>
    </sql>

    <select id="selectPmsProductRequirementById" parameterType="Long" resultMap="PmsProductRequirementResult">
        <include refid="selectPmsProductRequirementVo"/>
        where id = #{id}
    </select>
    <select id="selectPmsProductRequirementMaxIndexByProductIdentifier" resultType="java.lang.Long">
        select max(`index`)
        from pms_product_requirement
        where pms_product_identifier = #{pmsProductIdentifier}
    </select>
    <select id="selectPmsProductRequirementByProductIdentifierAndIndex" resultMap="PmsProductRequirementResult">
        <include refid="selectPmsProductRequirementVo"/>
        where pms_product_identifier = #{productIdentifier}
        and `index` = #{index}
    </select>
    <select id="selectProductIdentifierListInRequirementIdList" resultType="java.lang.String">
        SELECT distinct pms_product_identifier FROM `pms_product_requirement` where id in
        <foreach item="id" collection="idList" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="selectUserReqList" resultMap="PmsProductRequirementResult">
        /*keep orderby*/
        <include refid="selectUserReqAndRecord"/>
        <where>
            <if test="search != null  and search != ''">and
                (
                title like concat('%', #{search}, '%')
                or source_user like concat('%', #{search}, '%')
                or source_company like concat('%', #{search}, '%')
                )
            </if>
            <if test="pmsProductIdentifier != null ">and pms_product_identifier = #{pmsProductIdentifier}</if>
            <if test="status != null  and status != ''">and `status` = #{status}</if>
            and (`status`= 'pendingReviewUserReq' or `status`= 'rejectedUserReq' or `status` = 'reviewedUserReq')
        </where>
        order by update_time desc
    </select>
    <select id="selectReqSpecsList" resultMap="PmsProductRequirementResult">
        /*keep orderby*/
        <include refid="selectPmsProductRequirementVo"/>
        <include refid="selectReqSpecsWhere"/>
        order by update_time desc
    </select>

    <sql id="selectReqSpecsWhere">
        <where>
            <if test="title != null  and title != ''">and title like concat('%', #{title}, '%')</if>
            <if test="createBy != null  and createBy != ''">and create_by = #{createBy}</if>
            <if test="pmsProductIdentifier != null ">and pms_product_identifier = #{pmsProductIdentifier}</if>
            <if test="pmsProductPlanId != null ">and pms_product_plan_id = #{pmsProductPlanId}</if>
            <if test="status != null  and status != ''">and `status` in
                <foreach item="item" collection="statusList" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="type != null  and type != ''">and `type` = #{type}</if>
            <choose>
                <when test="childrenModuleIdList != null">
                    <if test="childrenModuleIdList != null ">
                        and pms_product_module_id in
                        <foreach item="moduleId" collection="childrenModuleIdList" open="(" separator="," close=")">
                            #{moduleId}
                        </foreach>
                    </if>
                </when>
                <otherwise>
                    <if test="pmsProductModuleId != null ">
                        <if test="pmsProductModuleId != -1">and pms_product_module_id = #{pmsProductModuleId}</if>
                        <if test="pmsProductModuleId == -1">and pms_product_module_id is null</if>
                    </if>
                </otherwise>
            </choose>
            and (`status`= 'pendingReviewReqSpecs' or `status`= 'plannedReqSpecs' or `status` = 'rejectedReqSpecs' or
            `status` = 'reviewedReqSpecs' or `status` = 'completedReqSpecs' or `status` = 'changingReqSpecs')
        </where>
    </sql>

    <select id="selectReqSpecsCountByIdentifier" resultType="java.lang.Integer">
        select count(1)
        from pms_product_requirement
        where identifier = #{identifier}
    </select>
    <select id="selectReqSpecsByProjectReqId" resultMap="PmsProductRequirementResult">
        <include refid="selectPmsProductRequirementVo"/>
        where project_req_id = #{projectReqId}
        and (`status`= 'pendingReviewReqSpecs' or `status`= 'plannedReqSpecs' or `status` = 'rejectedReqSpecs' or
        `status` = 'reviewedReqSpecs' or `status` = 'completedReqSpecs' or `status` = 'changingReqSpecs')
    </select>

    <insert id="insertPmsProductRequirement" parameterType="PmsProductRequirement" useGeneratedKeys="true"
            keyProperty="id">
        insert into pms_product_requirement
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="index != null">`index`,</if>
            <if test="title != null and title != ''">title,</if>
            <if test="status != null">`status`,</if>
            <if test="priorityId != null">priority_id,</if>
            <if test="pmsProductModuleId != null">pms_product_module_id,</if>
            <if test="pmsProductPlanId != null">pms_product_plan_id,</if>
            <if test="pmsProductIdentifier != null">pms_product_identifier,</if>
            <if test="content != null and content != ''">content,</if>
            <if test="tagIds != null">tag_ids,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="fileIdentifiers != null">file_identifiers,</if>
            <if test="sort != null">sort,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="requirementAssigneeGitlinkIds != null">requirement_assignee_gitlink_ids,</if>
            <if test="pmsProjectId != null">pms_project_id,</if>
            <if test="fromId != null">from_id,</if>
            <if test="projectReqId != null">project_req_id,</if>
            <if test="type != null and type != ''">type,</if>
            <if test="identifier != null and identifier != ''">identifier,</if>
            <if test="sourceUser != null and sourceUser != ''">source_user,</if>
            <if test="sourceCompany != null and sourceCompany != ''">source_company,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="index != null">#{index},</if>
            <if test="title != null and title != ''">#{title},</if>
            <if test="status != null">#{status},</if>
            <if test="priorityId != null">#{priorityId},</if>
            <if test="pmsProductModuleId != null">#{pmsProductModuleId},</if>
            <if test="pmsProductPlanId != null">#{pmsProductPlanId},</if>
            <if test="pmsProductIdentifier != null">#{pmsProductIdentifier},</if>
            <if test="content != null and content != ''">#{content},</if>
            <if test="tagIds != null">#{tagIds},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="fileIdentifiers != null">#{fileIdentifiers},</if>
            <if test="sort != null">#{sort},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="requirementAssigneeGitlinkIds != null">#{requirementAssigneeGitlinkIds},</if>
            <if test="pmsProjectId != null">#{pmsProjectId},</if>
            <if test="fromId != null">#{fromId},</if>
            <if test="projectReqId != null">#{projectReqId},</if>
            <if test="type != null and type != ''">#{type},</if>
            <if test="identifier != null and identifier != ''">#{identifier},</if>
            <if test="sourceUser != null and sourceUser != ''">#{sourceUser},</if>
            <if test="sourceCompany != null and sourceCompany != ''">#{sourceCompany},</if>
        </trim>
    </insert>

    <update id="updatePmsProductRequirement" parameterType="PmsProductRequirement">
        update pms_product_requirement
        <trim prefix="SET" suffixOverrides=",">
            <if test="index != null">`index` = #{index},</if>
            <if test="title != null and title != ''">title = #{title},</if>
            <if test="status != null">`status` = #{status},</if>
            <if test="priorityId != null">priority_id = #{priorityId},</if>
            pms_product_module_id = #{pmsProductModuleId},
            pms_product_plan_id = #{pmsProductPlanId},
            <if test="pmsProductIdentifier != null">pms_product_identifier = #{pmsProductIdentifier},</if>
            <if test="content != null and content != ''">content = #{content},</if>
            tag_ids = #{tagIds},
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="fileIdentifiers != null">file_identifiers = #{fileIdentifiers},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            requirement_assignee_gitlink_ids = #{requirementAssigneeGitlinkIds},
            <if test="pmsProjectId != null and pmsProjectId != ''">pms_project_id = #{pmsProjectId},</if>
            <if test="fromId != null and fromId != ''">from_id = #{fromId},</if>
            <if test="projectReqId != null and projectReqId != ''">project_req_id = #{projectReqId},</if>
            <if test="type != null and type != ''">type = #{type},</if>
            <if test="identifier != null and identifier != ''">identifier = #{identifier},</if>
            <if test="sourceUser != null and sourceUser != ''">source_user = #{sourceUser},</if>
            <if test="sourceCompany != null and sourceCompany != ''">source_company = #{sourceCompany},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="batchUpdatePmsProductRequirement">
        update pms_product_requirement
        <trim prefix="SET" suffixOverrides=",">
            <if test="priorityIdIsChange">priority_id = #{priorityId},</if>
            <if test="pmsProductModuleIdIsChange">pms_product_module_id = #{pmsProductModuleId},</if>
            <if test="pmsProductPlanIdIsChange">pms_product_plan_id = #{pmsProductPlanId},</if>
            <if test="tagIdsIsChange">tag_ids = #{tagIds},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="pmsProjectIdIsChange">pms_project_id = #{pmsProjectId},</if>
            <if test="requirementAssigneeGitlinkIdsIsChange">requirement_assignee_gitlink_ids =
                #{requirementAssigneeGitlinkIds},
            </if>
        </trim>
        where id in
        <foreach item="id" collection="idList" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <delete id="deletePmsProductRequirementById" parameterType="Long">
        delete
        from pms_product_requirement
        where id = #{id}
    </delete>

    <delete id="deletePmsProductRequirementByIds" parameterType="String">
        delete from pms_product_requirement where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="countByIdentifierAndStatus" resultType="java.lang.Integer">
        select count(0) from pms_product_requirement
        where pms_product_identifier = #{pmsProductIdentifier} and `status` in
         <foreach collection="statusList" item="item" index="idx" close=")" open="(" separator=",">
             #{item}
         </foreach>
    </select>

    <select id="selectReqCountGroupByStatus" resultType="com.microservices.pms.product.domain.vo.ReqStatusStatVo">
        select count(1) as num, `status` as `key` from pms_product_requirement
        where pms_product_identifier = #{pmsProductIdentifier} and `status` in
         <foreach collection="statusList" item="item" index="idx" close=")" open="(" separator=",">
             #{item}
         </foreach>
        group by `status`
    </select>
    <select id="selectNotIncludeNoModuleAndFixedProductReqSpecsCount" resultType="java.lang.Integer">
        select count(1)
        from pms_product_requirement ppr
                 left join pms_product_module ppm on ppr.pms_product_module_id = ppm.id
        where ppr.pms_product_identifier = #{pmsProductIdentifier}
          and ppr.pms_product_module_id is not null
          and ppm.is_fixed_module = false
          and (`status` = 'pendingReviewReqSpecs' or `status` = 'plannedReqSpecs' or `status` = 'rejectedReqSpecs' or
               `status` = 'reviewedReqSpecs' or `status` = 'completedReqSpecs' or `status` = 'changingReqSpecs')
    </select>
</mapper>