<?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.dss.projects.mapper.ProjectsMapper">
    <resultMap id="BaseResultMap" type="com.microservices.dss.projects.domain.Projects" >
        <result column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="homepage" property="homepage" jdbcType="VARCHAR" />
        <result column="is_public" property="isPublic" jdbcType="BIT" />
        <result column="parent_id" property="parentId" jdbcType="INTEGER" />
        <result column="created_on" property="createdOn" jdbcType="TIMESTAMP" />
        <result column="updated_on" property="updatedOn" jdbcType="TIMESTAMP" />
        <result column="identifier" property="identifier" jdbcType="VARCHAR" />
        <result column="status" property="status" jdbcType="INTEGER" />
        <result column="lft" property="lft" jdbcType="INTEGER" />
        <result column="rgt" property="rgt" jdbcType="INTEGER" />
        <result column="inherit_members" property="inheritMembers" jdbcType="BIT" />
        <result column="project_type" property="projectType" jdbcType="INTEGER" />
        <result column="hidden_repo" property="hiddenRepo" jdbcType="BIT" />
        <result column="attachmenttype" property="attachmenttype" jdbcType="INTEGER" />
        <result column="user_id" property="userId" jdbcType="INTEGER" />
        <result column="dts_test" property="dtsTest" jdbcType="INTEGER" />
        <result column="enterprise_name" property="enterpriseName" jdbcType="VARCHAR" />
        <result column="organization_id" property="organizationId" jdbcType="INTEGER" />
        <result column="project_new_type" property="projectNewType" jdbcType="INTEGER" />
        <result column="gpid" property="gpid" jdbcType="INTEGER" />
        <result column="forked_from_project_id" property="forkedFromProjectId" jdbcType="INTEGER" />
        <result column="forked_count" property="forkedCount" jdbcType="INTEGER" />
        <result column="publish_resource" property="publishResource" jdbcType="INTEGER" />
        <result column="visits" property="visits" jdbcType="INTEGER" />
        <result column="hot" property="hot" jdbcType="INTEGER" />
        <result column="invite_code" property="inviteCode" jdbcType="VARCHAR" />
        <result column="qrcode" property="qrcode" jdbcType="VARCHAR" />
        <result column="qrcode_expiretime" property="qrcodeExpiretime" jdbcType="INTEGER" />
        <result column="training_status" property="trainingStatus" jdbcType="TINYINT" />
        <result column="rep_identifier" property="repIdentifier" jdbcType="VARCHAR" />
        <result column="project_category_id" property="projectCategoryId" jdbcType="INTEGER" />
        <result column="project_language_id" property="projectLanguageId" jdbcType="INTEGER" />
        <result column="license_id" property="licenseId" jdbcType="INTEGER" />
        <result column="ignore_id" property="ignoreId" jdbcType="INTEGER" />
        <result column="praises_count" property="praisesCount" jdbcType="INTEGER" />
        <result column="watchers_count" property="watchersCount" jdbcType="INTEGER" />
        <result column="issues_count" property="issuesCount" jdbcType="INTEGER" />
        <result column="pull_requests_count" property="pullRequestsCount" jdbcType="INTEGER" />
        <result column="language" property="language" jdbcType="VARCHAR" />
        <result column="versions_count" property="versionsCount" jdbcType="INTEGER" />
        <result column="issue_tags_count" property="issueTagsCount" jdbcType="INTEGER" />
        <result column="closed_issues_count" property="closedIssuesCount" jdbcType="INTEGER" />
        <result column="open_devops" property="openDevops" jdbcType="BIT" />
        <result column="gitea_webhook_id" property="giteaWebhookId" jdbcType="INTEGER" />
        <result column="open_devops_count" property="openDevopsCount" jdbcType="INTEGER" />
        <result column="recommend" property="recommend" jdbcType="BIT" />
        <result column="platform" property="platform" jdbcType="INTEGER" />
    </resultMap>

    <select id="selectProjectCount" resultType="java.lang.Integer">
        select count(*)
        from projects;
    </select>

    <select id="getNewAddedProjectsByMonth" parameterType="java.lang.String" resultType="java.util.HashMap">
        SELECT
            DATE_FORMAT(created_on, '%Y-%m') AS createTime,
            COUNT(*) AS num
        FROM
            projects
        WHERE
            created_on between #{startTime,jdbcType=VARCHAR}
                AND #{endTime,jdbcType=VARCHAR}
        GROUP BY createTime
    </select>

    <select id="getActiveProjectsByMonth" parameterType="java.lang.String" resultType="java.util.HashMap">
        SELECT
            DATE_FORMAT(updated_on, '%Y-%m') AS createTime,
            COUNT(DISTINCT project_id) AS num
        from (
                 SELECT id as project_id, DATE_FORMAT(updated_on, '%Y-%m-%d' ) as updated_on FROM projects WHERE updated_on BETWEEN #{startTime,jdbcType=VARCHAR} AND #{endTime,jdbcType=VARCHAR}
                 UNION ALL
                 SELECT DISTINCT project_id, DATE_FORMAT(updated_at, '%Y-%m-%d' ) as updated_on FROM commit_logs WHERE updated_at BETWEEN #{startTime,jdbcType=VARCHAR} AND #{endTime,jdbcType=VARCHAR}
                 UNION ALL
                 SELECT DISTINCT project_id, DATE_FORMAT(updated_on, '%Y-%m-%d' ) as updated_on FROM issues WHERE updated_on BETWEEN #{startTime,jdbcType=VARCHAR} AND #{endTime,jdbcType=VARCHAR}
             ) as t
        GROUP BY createTime
    </select>

    <select id="getProjectNumOfDiffProgramLanguage"
            resultType="com.microservices.dss.projects.domain.PieChartNameAndValue">
        select name,projects_count as `value`
        from project_languages
        order by projects_count desc
            limit #{topNumber};
    </select>
    <select id="getProjectNumOfProjectCategory"
            resultType="com.microservices.dss.projects.domain.PieChartNameAndValue">
        select name,projects_count as `value`
        from `project_categories`
        order by projects_count desc
            limit #{topNumber};
    </select>
</mapper>