<?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.achievementLibrary.mapper.InBlockchainDataMapper">
    
    <resultMap type="InBlockchainData" id="InBlockchainDataResult">
        <result property="id"    column="id"    />
        <result property="type"    column="type"    />
        <result property="typeId"    column="type_id"    />
        <result property="txHash"    column="tx_hash"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
    </resultMap>

    <sql id="selectInBlockchainDataVo">
        select id, type, type_id, tx_hash, create_time, create_by from in_blockchain_data
    </sql>

    <select id="selectInBlockchainDataList" parameterType="InBlockchainData" resultMap="InBlockchainDataResult">
        <include refid="selectInBlockchainDataVo"/>
        <where>  
            <if test="type != null  and type != ''"> and type = #{type}</if>
            <if test="typeId != null  and typeId != ''"> and type_id = #{typeId}</if>
            <if test="txHash != null  and txHash != ''"> and tx_hash = #{txHash}</if>
        </where>
    </select>
    
    <select id="selectInBlockchainDataById" parameterType="Long" resultMap="InBlockchainDataResult">
        <include refid="selectInBlockchainDataVo"/>
        where id = #{id}
    </select>

    <select id="issueDataList" resultType="java.util.Map">
        select i.id as "issue_id",
        i.project_issues_index as "issue_number",
        i.status_id as "action",
        it.name as "issueStatusName",
        i.subject as "title",
        i.subject as "issueName",
        i.project_id,
        r.id as "repo_id",
        p.name as "projectName",
        r.identifier as "reponame",
        i.created_on as "created_at",
        i.updated_on as "updated_at",
        i.description as "content",
        i.author_id as "author_id",
        i.blockchain_token_num as "blockchain_token_num",
        (select IFNULL(NULLIF(nickname,'') ,login) from users where id = i.author_id) as "createBy",
        i.due_date,
        (select GROUP_CONCAT(assigner_id SEPARATOR ', ')
        from issue_assigners
        where issue_id = i.id) as "ownername"
        from issues i
        inner join issue_statuses it on i.status_id = it.id
        inner join projects p on i.project_id = p.id
        inner join repositories r on p.id = r.project_id
        where p.use_blockchain='1' and i.project_issues_index is not null
        <if test="projectName != null  and projectName != ''">and p.name like concat('%', #{projectName}, '%')</if>
        <if test="issueName != null  and issueName != ''">and i.subject like concat('%', #{issueName}, '%')</if>
    </select>


    <select id="commitDataList" resultType="java.util.Map">
        select cl.commit_id as "commit_id",
        re.id as "repo_id",
        cl.user_id as "committer",
        (select IFNULL(NULLIF(nickname, ''),login) from users where id = cl.user_id) as "createBy",
        cl.project_id,p.name as "project_name",
        cl.message as "content",
        cl.created_at as "committer_time",
        cl.message as "content",
        re.user_id as "author",
        ibd.tx_hash as "commitHash",
        cl.ref as "commit_diff"
        from commit_logs cl
        inner join projects p on cl.project_id = p.id
        inner join repositories re on p.id = re.project_id
        inner join in_blockchain_data ibd on cl.commit_id = ibd.type_id
        where p.use_blockchain = '1'
        <if test="projectName != null  and projectName != ''">and p.name like concat('%', #{projectName}, '%')</if>
        order by ibd.create_time desc,cl.created_at desc
    </select>

    <select id="prDataList" resultType="java.util.Map">
        select pr.id as "pull_request_id",
        pr.gitea_number as "pull_request_number",
        pr.status as "prStatus",
        pr.project_id,
        source.id as "repo_id",
        source.url as "reponame",
        pr.user_id as "username",
        pr.title as "title",
        pr.body as "content",
        pr.head "source_branch",
        pr.base as "target_branch",
        target.id as "target_repo_id",
        pr.created_at,
        pr.updated_at,p.name as "projectName",(select IFNULL(NULLIF(nickname, ''),login) from users where id = pr.user_id) as "createBy"
        from pull_requests pr
        inner join projects p on pr.project_id = p.id
        inner join repositories source on p.id = source.project_id
        left join repositories target on pr.fork_project_id = target.project_id
        where p.use_blockchain = '1'
        <if test="projectName != null  and projectName != ''">and p.name like concat('%', #{projectName}, '%')</if>
        <if test="prName != null  and prName != ''">and pr.title like concat('%', #{prName}, '%')</if>
    </select>

    <insert id="insertInBlockchainData" parameterType="InBlockchainData" useGeneratedKeys="true" keyProperty="id">
        insert into in_blockchain_data
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="type != null and type != ''">type,</if>
            <if test="typeId != null and typeId != ''">type_id,</if>
            <if test="txHash != null and txHash != ''">tx_hash,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null and createBy != ''">create_by,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="type != null and type != ''">#{type},</if>
            <if test="typeId != null and typeId != ''">#{typeId},</if>
            <if test="txHash != null and txHash != ''">#{txHash},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null and createBy != ''">#{createBy},</if>
         </trim>
    </insert>

    <update id="updateInBlockchainData" parameterType="InBlockchainData">
        update in_blockchain_data
        <trim prefix="SET" suffixOverrides=",">
            <if test="type != null and type != ''">type = #{type},</if>
            <if test="typeId != null and typeId != ''">type_id = #{typeId},</if>
            <if test="txHash != null and txHash != ''">tx_hash = #{txHash},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null and createBy != ''">create_by = #{createBy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteInBlockchainDataById" parameterType="Long">
        delete from in_blockchain_data where id = #{id}
    </delete>

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

    <select id="getNeedToBlockCommitList" resultType="String">
        select
            cl.commit_id as "commit_id"
        from commit_logs cl
                 inner join projects p on cl.project_id = p.id
                 left join repositories re on cl.repository_id = re.id
                 left join in_blockchain_data ibd on cl.commit_id = ibd.type_id
        where p.use_blockchain = '1' and  ibd.tx_hash is null
    </select>

    <select id="getCommitListById" resultType="com.microservices.dms.common.vo.CommitResultVo">
        select
            cl.commit_id as "commit_id",cl.ref as "commit_diff"
        from commit_logs cl
        inner join projects p on cl.project_id = p.id
        left join in_blockchain_data ibd on cl.commit_id = ibd.type_id
        where p.use_blockchain = '1' and ibd.tx_hash is null and p.id = #{projectId}
    </select>
</mapper>