<?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.dms.referral.mapper.TalentReferralMapper">

    <select id="selectIssuesCondition" resultType="com.microservices.dms.referral.vo.IssuesVo">
        select id,project_id,`subject`,description,status_id from issues
        <where>
            <if test="id != null ">and id = #{id}</if>
            <if test="projectId != null ">and project_id = #{projectId}</if>
            <if test="subject != null ">and `subject` = #{subject}</if>
            <if test="statusIds != null and statusIds.size() > 0">and status_id in
                <foreach item="item" collection="statusIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectIssuesAssigners" resultType="long">
        select assigner_id from issue_assigners
        where issue_id = #{issueId} and assigner_id is not null
    </select>

    <select id="selectRepositoriesUrl" resultType="string">
        select concat(login, '/', identifier) as url
        from (select identifier, (select login from users where id = user_id) as login
              from repositories
              where project_id = #{id} limit 1) as tmp
    </select>

    <select id="selectIssuesById" resultType="com.microservices.dms.referral.vo.IssuesVo">
        select project_id,`subject`,description,status_id from issues
        where id = #{id}
    </select>

    <select id="selectPRById" resultType="com.microservices.dms.referral.vo.PullRequestVo">
        select project_id,`title`,user_id,`status`,gitea_number from pull_requests
        where id = #{id}
    </select>

    <select id="countIssuesAssigner" resultType="com.microservices.dms.referral.vo.IssuesVo">
        SELECT ia.assigner_id as `id`,
               COUNT(ia.assigner_id) AS num
        FROM issues i
                 JOIN
             issue_assigners ia ON i.id = ia.issue_id
        WHERE i.project_id = #{projectId}
          and i.status_id = 5
        GROUP BY ia.assigner_id
        ORDER BY num DESC;
    </select>

    <select id="countPRUser" resultType="com.microservices.dms.referral.vo.PullRequestVo">
        SELECT i.id as `id`,
               COUNT(i.user_id) AS num
        FROM pull_requests i

        WHERE i.project_id = #{projectId}
          and i.status = 1
        GROUP BY i.user_id
        ORDER BY num DESC;
    </select>

    <select id="selectIdByLogin" resultType="java.lang.Long">
        select id from users where login = #{login} limit 1
    </select>

    <select id="selectTaskByStatus" resultType="com.microservices.dms.referral.vo.TaskVo">
        select t.id,td.task_id, name, status, category_id, description
        from tasks t
                 join task_details td on t.id = td.task_id
        where status in
        <foreach collection="statusIds" item="item" close=")" index="idx" open="(" separator=",">
            #{item}
        </foreach>
    </select>

    <select id="selectUsersById" resultType="java.util.Map">
        select id,login,nickname from users where id = #{id}
    </select>

    <select id="selectTaskById" resultType="com.microservices.dms.referral.vo.TaskVo">
        select t.id,td.task_id, name, status, category_id, description
        from tasks t
                 join task_details td on t.id = td.task_id
        where t.id =#{id}
    </select>

    <select id="selectExpertsBy" resultType="com.microservices.dms.referral.vo.ExpertVo">
        select id,
               user_id,
               review_area_one,
               review_area_two,
               review_area_three,
               (select IFNULL(nickname,login) from users u where u.id = e.user_id) as nickname
        from experts e
        where is_delete = 0
    </select>

    <select id="allTaskWithExpertIds" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select container_id as 'k', group_concat(expert_id) as v from task_expert where  container_type = #{type}
        group by container_id
    </select>

    <select id="selectCompetitionById" resultType="com.microservices.dms.referral.vo.TaskVo">
        select t.id, title as name, status, description
        from competition_infos t
        where t.id =#{id}
    </select>

    <select id="selectCompetitionIdByStatus" resultType="com.microservices.dms.referral.vo.TaskVo">
        select t.id, title as name, status, description,identifier
        from competition_infos t
        where upload_date &lt; now()
    </select>

    <select id="selectPRInfoById" resultType="com.microservices.dms.referral.vo.PrInfoVo">
        select pr.id,pr.title as "title",pr.body as "content"
        from pull_requests pr
        where pr.id= #{id}
    </select>
</mapper>
