<?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="cn.orionsec.ops.dao.ApplicationBuildDAO">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.orionsec.ops.entity.domain.ApplicationBuildDO">
        <id column="id" property="id"/>
        <result column="app_id" property="appId"/>
        <result column="app_name" property="appName"/>
        <result column="app_tag" property="appTag"/>
        <result column="profile_id" property="profileId"/>
        <result column="profile_name" property="profileName"/>
        <result column="profile_tag" property="profileTag"/>
        <result column="build_seq" property="buildSeq"/>
        <result column="branch_name" property="branchName"/>
        <result column="commit_id" property="commitId"/>
        <result column="repo_id" property="repoId"/>
        <result column="log_path" property="logPath"/>
        <result column="bundle_path" property="bundlePath"/>
        <result column="build_status" property="buildStatus"/>
        <result column="description" property="description"/>
        <result column="create_user_id" property="createUserId"/>
        <result column="create_user_name" property="createUserName"/>
        <result column="build_start_time" property="buildStartTime"/>
        <result column="build_end_time" property="buildEndTime"/>
        <result column="deleted" property="deleted"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <!-- 统计映射结果 -->
    <resultMap id="StatisticsResultMap" type="cn.orionsec.ops.entity.dto.ApplicationBuildStatisticsDTO">
        <result column="build_count" property="buildCount"/>
        <result column="success_count" property="successCount"/>
        <result column="failure_count" property="failureCount"/>
        <result column="date" property="date"/>
        <result column="avg_used" property="avgUsed"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, app_id, app_name, app_tag, profile_id, profile_name, profile_tag,
        build_seq, branch_name, commit_id, repo_id, log_path, bundle_path, build_status, description,
        create_user_id, create_user_name, build_start_time, build_end_time, deleted, create_time, update_time
    </sql>

    <select id="selectStatusById" resultType="java.lang.Integer">
        SELECT build_status
        FROM application_build
        WHERE id = #{id}
        AND deleted = 1
    </select>

    <select id="selectStatusInfoById" resultMap="BaseResultMap">
        SELECT id, build_status, build_start_time, build_end_time
        FROM application_build
        WHERE id = #{id}
        AND deleted = 1
    </select>

    <select id="selectStatusInfoByIdList" resultMap="BaseResultMap">
        SELECT id, build_status, build_start_time, build_end_time
        FROM application_build
        WHERE id IN
        <foreach collection="idList" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
        AND deleted = 1
    </select>

    <select id="selectLastBuildBranch" resultType="java.lang.String">
        SELECT branch_name
        FROM application_build
        WHERE repo_id = #{repoId}
        <if test="appId != null">
            AND app_id = #{appId}
        </if>
        <if test="profileId != null">
            AND profile_id = #{profileId}
        </if>
        AND branch_name IS NOT NULL
        AND deleted = 1
        ORDER BY id DESC
        LIMIT 1
    </select>

    <select id="selectBuildSeq" resultType="java.lang.Integer">
        SELECT build_seq
        FROM application_build
        WHERE id = #{id}
        AND deleted = 1
    </select>

    <select id="selectBuildReleaseList" resultMap="BaseResultMap">
        SELECT id, build_seq, description, create_time
        FROM application_build
        WHERE app_id = #{appId}
        AND profile_id = #{profileId}
        AND build_status = 30
        AND deleted = 1
        ORDER by id DESC
        LIMIT #{limit}
    </select>

    <select id="selectBuildReleaseGroupList" resultMap="BaseResultMap">
        SELECT id, app_id,
        FROM application_build
        WHERE app_id IN
        <foreach collection="appIdList" item="appId" open="(" separator="," close=")">
            #{appId}
        </foreach>
        AND profile_id = #{profileId}
        AND build_status = 30
        AND deleted = 1
        GROUP BY app_id
        ORDER by id DESC
    </select>

    <select id="getBuildStatistics" resultMap="StatisticsResultMap">
        SELECT
            COUNT(1) build_count,
            COUNT(build_status = 30 OR NULL) success_count,
            COUNT(build_status = 40 OR NULL) failure_count,
            IFNULL(
                FLOOR(
                    AVG(
                        IF(
                            build_status = 30
                            AND build_end_time IS NOT NULL
                            AND build_start_time IS NOT NULL,
                            UNIX_TIMESTAMP(build_end_time) * 1000 - UNIX_TIMESTAMP(build_start_time) * 1000,
                            NULL
                        )
                    )
                ), 0
            ) avg_used
        FROM application_build
        WHERE deleted = 1
        AND app_id = #{appId}
        AND profile_id = #{profileId}
        <if test="rangeStartDate != null">
            AND create_time >= #{rangeStartDate}
        </if>
    </select>

    <select id="getBuildDateStatistics" resultMap="StatisticsResultMap">
        SELECT
            COUNT(1) build_count,
            COUNT(build_status = 30 OR NULL) success_count,
            COUNT(build_status = 40 OR NULL) failure_count,
            DATE_FORMAT(create_time, '%Y-%m-%d') date
        FROM application_build
        WHERE deleted = 1
        AND app_id = #{appId}
        AND profile_id = #{profileId}
        AND create_time >= #{rangeStartDate}
        GROUP BY date
    </select>

</mapper>
