<?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.resourceLibrary.mapper.ProjectResourceLibraryMapper">

    <select id="selectProjectListCondition" resultType="com.microservices.dms.resourceLibrary.domain.vo.ProjectListVo">
        select name,id,
        (select version_releases_count from repositories where project_id=projects.id) as versionReleasesCount,
        (select name from project_languages where id=project_language_id) as projectLanguages,
        (select c.name from project_categories c where id = project_category_id) as projectCategories,
        forked_count,praises_count,watchers_count,issues_count,pull_requests_count,license_id,visits,
        (select count(*) from searchers where search_type='Project' and search_id=projects.id) as
        projectSearchCount,created_on,
        (select count(*) from commit_logs where project_id=projects.id) as commitsCount,
        (select count(*) from members where project_id=projects.id) as membersCount,
        (select count(*) from members where (created_on >= NOW() - INTERVAL 1 MONTH ) and project_id=projects.id) as
        membersMonthAddCount
        from projects
        <where>
            <if test="categoryId != null">
                AND project_category_id = #{categoryId}
            </if>
            <if test="name != null and name.trim() != ''">
                AND `name` like concat('%', #{name}, '%')
            </if>
        </where>
    </select>

    <select id="selectCodeCommitCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select '' as itemType, project_id as itemId, user_id as userId, created_at as createAt,
        (select login from users where id = user_id) as userName from commit_logs
        <where>
            <if test="itemId != null">
                AND project_id = #{itemId}
            </if>
        </where>
    </select>

    <select id="selectForkCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select '' as itemType, fork_project_id as itemId, user_id as userId, created_at as createAt,
        (select login from users where id = user_id) as userName from fork_users
        <where>
            <if test="itemId != null">
                AND fork_project_id = #{itemId}
            </if>
        </where>
    </select>

    <select id="selectIssueCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select '' as itemType, project_id as itemId, assigned_to_id as userId, created_on as createAt,
        (select login from users where id = assigned_to_id) as userName from issues
        <where>
            <if test="itemId != null">
                AND project_id = #{itemId}
            </if>
        </where>
    </select>


    <select id="selectPRCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select '' as itemType, project_id as itemId, user_id as userId, created_on as createAt,
        (select login from users where id = user_id) as userName from pull_requests
        <where>
            <if test="itemId != null">
                AND project_id = #{itemId}
            </if>
        </where>
    </select>

    <select id="selectPraisesCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select praise_tread_object_type as itemType, project_id as itemId, user_id as userId, created_at as createAt,
        (select login from users where id = user_id) as userName from praise_treads
        <where>
            <if test="itemId != null">
                AND project_id = #{itemId}
            </if>
            <if test="itemType != null and itemType.trim() != ''">
                AND praise_tread_object_type = #{itemType}
            </if>
        </where>
    </select>

    <select id="selectSearchCondition"
            resultType="com.microservices.dms.resourceLibrary.domain.vo.UserActionDataVo">
        select search_type as itemType, search_id as itemId, user_id as userId, created_at as createAt,
        (select login from users where id = user_id) as userName from searchers
        <where>
            <if test="itemId != null">
                AND search_id = #{itemId}
            </if>
            <if test="itemType != null and itemType.trim() != ''">
                AND search_type = #{itemType}
            </if>
        </where>
    </select>


    <resultMap type="ProjectResourceLibrary" id="ProjectResourceLibraryResult">
        <result property="id" column="id"/>
        <result property="openSourceProjectId" column="open_source_project_id"/>
        <result property="projectId" column="project_id"/>
        <result property="repositoryId" column="repository_id"/>
        <result property="versionReleasesId" column="version_releases_id"/>
        <result property="isPublic" column="is_public"/>
        <result property="releaseName" column="release_name"/>
        <result property="releaseDate" column="release_date"/>
        <result property="releasePerson" column="release_person"/>
        <result property="releaseType" column="release_type"/>
        <result property="releaseUnit" column="release_unit"/>
        <result property="releaseSummary" column="release_summary"/>
        <result property="releaseDetails" column="release_details"/>
        <result property="contactPerson" column="contact_person"/>
        <result property="contactPhone" column="contact_phone"/>
        <result property="isTransferredToAchievement" column="is_transferred_to_achievement"/>
        <result property="transferDate" column="transfer_date"/>
        <result property="isSelectedAchievement" column="is_selected_achievement"/>
        <result property="images" column="images"/>
        <result property="attachments" column="attachments"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="projectDomain" column="project_domain"/>
    </resultMap>


    <sql id="selectProjectResourceLibraryVo">
        select id,
               project_id,
               repository_id,
               version_releases_id,
               is_public,
               release_name,
               release_date,
               release_person,
               release_type,
               release_unit,
               release_summary,
               release_details,
               contact_person,
               contact_phone,
               is_transferred_to_achievement,
               transfer_date,
               is_selected_achievement,
               images,
               attachments,
               create_by,
               create_time,
               update_by,
               update_time,project_domain
        from project_resource_library
    </sql>

    <select id="selectProjectResourceLibraryList" parameterType="ProjectResourceLibrary"
            resultType="com.microservices.dms.resourceLibrary.domain.ProjectResourceLibrary">
        select pl.id,
        p.name as project_name,
        p.identifier,
        p.id as project_id,
        r.id as repository_id,
        v.id as version_releases_id,
        p.is_public as is_public,
        v.name as release_name,
        v.updated_at as release_date,
        (select login from users where id=v.user_id) as release_person,
        (select login from users where id=v.user_id) as user_identifier,
        IFNULL(pl.project_domain,p.project_category_id) as project_domain,
        pl.release_type,
        pl.release_unit,
        v.body,
        IFNULL( pl.release_summary,v.body) as release_summary,
        IFNULL( pl.release_details,v.body) as release_details,
        p.description as release_summary,
        p.description as release_details,
        pl.contact_person,
        pl.contact_phone,
        pl.is_transferred_to_achievement,
        pl.transfer_date,
        pl.is_selected_achievement,
        pl.images,
        v.zipball_url as attachments,
        pl.create_by,
        pl.create_time,
        pl.update_by,
        pl.update_time
        from projects p
        join repositories r on p.id = r.project_id
        join version_releases v on r.id = v.repository_id
        left join project_resource_library pl on v.id = pl.version_releases_id
        <where>
            <if test="projectName != null and projectName != ''">and p.name like concat('%', #{projectName}, '%')</if>
            <if test="versionReleasesId != null ">and version_releases_id = #{versionReleasesId}</if>
            <if test="isPublic != null ">and pl.is_public = #{isPublic}</if>
            <if test="releaseName != null  and releaseName != ''">and release_name like concat('%', #{releaseName},
                '%')
            </if>
            <if test="releaseDate != null ">and release_date = #{releaseDate}</if>
            <if test="releasePerson != null  and releasePerson != ''">and release_person = #{releasePerson}</if>
            <if test="releaseType != null  and releaseType != ''">and release_type = #{releaseType}</if>
            <if test="releaseUnit != null  and releaseUnit != ''">and release_unit = #{releaseUnit}</if>
            <if test="releaseSummary != null  and releaseSummary != ''">and release_summary = #{releaseSummary}</if>
            <if test="releaseDetails != null  and releaseDetails != ''">and release_details = #{releaseDetails}</if>
            <if test="contactPerson != null  and contactPerson != ''">and contact_person = #{contactPerson}</if>
            <if test="contactPhone != null  and contactPhone != ''">and contact_phone = #{contactPhone}</if>
            <if test="isTransferredToAchievement != null ">and is_transferred_to_achievement =
                #{isTransferredToAchievement}
            </if>
            <if test="transferDate != null ">and transfer_date = #{transferDate}</if>
            <if test="isSelectedAchievement != null ">and is_selected_achievement = #{isSelectedAchievement}</if>
            <if test="images != null  and images != ''">and images = #{images}</if>
            <if test="attachments != null  and attachments != ''">and attachments = #{attachments}</if>
            <if test="projectDomain != null  and projectDomain != ''">and project_domain = #{projectDomain}</if>
        </where>
        order by is_transferred_to_achievement,create_time desc,release_date desc
    </select>

    <select id="selectProjectResourceLibraryById" parameterType="Long" resultMap="ProjectResourceLibraryResult">
        <include refid="selectProjectResourceLibraryVo"/>
        where id = #{id}
    </select>

    <insert id="insertProjectResourceLibrary" parameterType="ProjectResourceLibrary" useGeneratedKeys="true"
            keyProperty="id">
        insert into project_resource_library
        <trim prefix="(" suffix=")" suffixOverrides=",">

            <if test="projectId != null">project_id,</if>
            <if test="repositoryId != null">repository_id,</if>
            <if test="versionReleasesId != null">version_releases_id,</if>
            <if test="isPublic != null">is_public,</if>
            <if test="releaseName != null">release_name,</if>
            <if test="releaseDate != null">release_date,</if>
            <if test="releasePerson != null">release_person,</if>
            <if test="releaseType != null">release_type,</if>
            <if test="releaseUnit != null">release_unit,</if>
            <if test="releaseSummary != null">release_summary,</if>
            <if test="releaseDetails != null">release_details,</if>
            <if test="contactPerson != null">contact_person,</if>
            <if test="contactPhone != null">contact_phone,</if>
            <if test="isTransferredToAchievement != null">is_transferred_to_achievement,</if>
            <if test="transferDate != null">transfer_date,</if>
            <if test="isSelectedAchievement != null">is_selected_achievement,</if>
            <if test="images != null">images,</if>
            <if test="attachments != null">attachments,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="projectDomain != null">project_domain,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">

            <if test="projectId != null">#{projectId},</if>
            <if test="repositoryId != null">#{repositoryId},</if>
            <if test="versionReleasesId != null">#{versionReleasesId},</if>
            <if test="isPublic != null">#{isPublic},</if>
            <if test="releaseName != null">#{releaseName},</if>
            <if test="releaseDate != null">#{releaseDate},</if>
            <if test="releasePerson != null">#{releasePerson},</if>
            <if test="releaseType != null">#{releaseType},</if>
            <if test="releaseUnit != null">#{releaseUnit},</if>
            <if test="releaseSummary != null">#{releaseSummary},</if>
            <if test="releaseDetails != null">#{releaseDetails},</if>
            <if test="contactPerson != null">#{contactPerson},</if>
            <if test="contactPhone != null">#{contactPhone},</if>
            <if test="isTransferredToAchievement != null">#{isTransferredToAchievement},</if>
            <if test="transferDate != null">#{transferDate},</if>
            <if test="isSelectedAchievement != null">#{isSelectedAchievement},</if>
            <if test="images != null">#{images},</if>
            <if test="attachments != null">#{attachments},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="projectDomain != null">#{projectDomain},</if>
        </trim>
    </insert>

    <update id="updateProjectResourceLibrary" parameterType="ProjectResourceLibrary">
        update project_resource_library
        <trim prefix="SET" suffixOverrides=",">
            <if test="projectId != null">project_id = #{projectId},</if>
            <if test="repositoryId != null">repository_id = #{repositoryId},</if>
            <if test="versionReleasesId != null">version_releases_id = #{versionReleasesId},</if>
            <if test="isPublic != null">is_public = #{isPublic},</if>
            <if test="releaseName != null">release_name = #{releaseName},</if>
            <if test="releaseDate != null">release_date = #{releaseDate},</if>
            <if test="releasePerson != null">release_person = #{releasePerson},</if>
            <if test="releaseType != null">release_type = #{releaseType},</if>
            <if test="releaseUnit != null">release_unit = #{releaseUnit},</if>
            <if test="releaseSummary != null">release_summary = #{releaseSummary},</if>
            <if test="releaseDetails != null">release_details = #{releaseDetails},</if>
            <if test="contactPerson != null">contact_person = #{contactPerson},</if>
            <if test="contactPhone != null">contact_phone = #{contactPhone},</if>
            <if test="isTransferredToAchievement != null">is_transferred_to_achievement =
                #{isTransferredToAchievement},
            </if>
            <if test="transferDate != null">transfer_date = #{transferDate},</if>
            <if test="isSelectedAchievement != null">is_selected_achievement = #{isSelectedAchievement},</if>
            <if test="images != null">images = #{images},</if>
            <if test="attachments != null">attachments = #{attachments},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="projectDomain != null">project_domain = #{projectDomain},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteProjectResourceLibraryById" parameterType="Long">
        delete
        from project_resource_library
        where id = #{id}
    </delete>

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

    <select id="getTotalProjectCount" resultType="java.lang.Long">
        select COUNT(distinct p.id)
        from projects p
                 join repositories r on p.id = r.project_id
                 join version_releases v on r.id = v.repository_id
    </select>
    <select id="getPublicProjectCount" resultType="java.lang.Long">
        select COUNT(distinct p.id)
        from projects p
                 join repositories r on p.id = r.project_id
                 join version_releases v on r.id = v.repository_id
        where p.is_public = 1
    </select>
    <select id="getTotalReleaseCount" resultType="java.lang.Long">
        select COUNT(*)
        from version_releases
    </select>
    <select id="getTotalConversionCount" resultType="java.lang.Long">
        select COUNT(*)
        from project_resource_library
        where is_transferred_to_achievement = 1
    </select>

    <select id="getAttachments" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select uuid as v, filename as k
        from attachments
        where container_type = 'VersionRelease'
          and container_id = #{containerId}
        union
        select tarball_url as v,concat(tag_name,'.tar.gz') as k
        from version_releases
        where id = #{containerId}
        union
        select  zipball_url as v,concat(tag_name,'.zip') as k
        from version_releases
        where id = #{containerId}

    </select>

    <select id="getProjectDomainNameByKey" resultType="String">
        select name from project_categories where id=#{projectDomain}
    </select>

    <select id="selectProjectInfos" resultType="com.microservices.dms.achievementLibrary.domain.TokenListVo">
        select p.id, p.name, created_on createdOn, r.identifier repositoryName, r.id as repositoryId,p.user_id as "userId",
               (select IFNULL(NULLIF(nickname,''),login) from users where id = p.user_id) as createBy
        from projects p
        join repositories r on p.id = r.project_id
        where p.use_blockchain = 1
        <if test="name != null  and name != ''">and p.name like concat('%', #{name}, '%')</if>
    </select>

    <select id="sumProjectIssueToken" resultType="java.lang.Long">
        select IFNULL(sum(i.blockchain_token_num),0)
        from issues i
        inner join projects p on i.project_id = p.id
        where p.id=#{id}
    </select>

    <select id="selectCommitInfo" resultType="java.util.Map">
        select cl.commit_id     as "commit_hash",
               cl.repository_id as "repo_id",
               cl.user_id       as "committer",
               cl.project_id,
               cl.message       as "content",
               cl.created_at    as "committer_time",
               cl.message       as "content",
               re.user_id       as "author",
               ''               as "committer_time",
               cl.ref           as "commit_diff"
        from commit_logs cl
                 inner join projects p on cl.project_id = p.id
                 left join repositories re on cl.repository_id = re.id
        where p.use_blockchain = '1'
          and cl.commit_id = #{commitId} and cl.ref = #{commitDiff}
    </select>

    <select id="selectPrInfo" resultType="java.util.Map">
        select pr.id           as "pull_request_id",
               pr.gitea_number as "pull_request_number",
               pr.status       as "action",
               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",
               pr.issue_id     as "issue_id",
               target.id       as "target_repo_id",
               pr.created_at,
               pr.updated_at,
               ''              as "merge_user"
        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'
          and pr.id = #{prId}
    </select>

    <select id="selectIssueInfo" resultType="java.util.Map">
        select i.id                                                                                         as "issue_id",
               i.status_id                                                                                  as "action",
               i.subject                                                                                    as "title",
               i.project_id,
               r.id                                                                                         as "repo_id",
               r.identifier                                                                                 as "reponame",
               i.created_on                                                                                 as "created_at",
               i.updated_on                                                                                 as "updated_at",
               i.description                                                                                as "content",
               i.author_id                                                                                  as "username",
               i.due_date,
               (select GROUP_CONCAT(assigner_id SEPARATOR ', ')
                from issue_assigners
                where issue_id = i.id)                                                                      as "ownername"
        from issues i
                 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.id = #{issueId}
    </select>

    <select id="getProjectUserIdById" resultType="String">
        select u.id from projects p
        inner join users u on p.user_id=u.id
        where p.id = #{tokenName}
    </select>

    <select id="getUsedApplyToken" resultType="Long">
        select IFNULL(sum(token),0) as "totalSum" from user_project_transfer_records where project_id =#{tokenName} and payee_id !=#{userName}
    </select>

    <select id="getUserIdBYLogin" resultType="String">
        select id from users where login=#{userId}
    </select>
</mapper>
