<?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.ApplicationReleaseDAO">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.orionsec.ops.entity.domain.ApplicationReleaseDO">
        <id column="id" property="id"/>
        <result column="release_title" property="releaseTitle"/>
        <result column="release_description" property="releaseDescription"/>
        <result column="build_id" property="buildId"/>
        <result column="build_seq" property="buildSeq"/>
        <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="release_type" property="releaseType"/>
        <result column="release_status" property="releaseStatus"/>
        <result column="release_serialize" property="releaseSerialize"/>
        <result column="exception_handler" property="exceptionHandler"/>
        <result column="bundle_path" property="bundlePath"/>
        <result column="transfer_path" property="transferPath"/>
        <result column="transfer_mode" property="transferMode"/>
        <result column="rollback_release_id" property="rollbackReleaseId"/>
        <result column="timed_release" property="timedRelease"/>
        <result column="timed_release_time" property="timedReleaseTime"/>
        <result column="create_user_id" property="createUserId"/>
        <result column="create_user_name" property="createUserName"/>
        <result column="audit_user_id" property="auditUserId"/>
        <result column="audit_user_name" property="auditUserName"/>
        <result column="audit_time" property="auditTime"/>
        <result column="audit_reason" property="auditReason"/>
        <result column="release_start_time" property="releaseStartTime"/>
        <result column="release_end_time" property="releaseEndTime"/>
        <result column="release_user_id" property="releaseUserId"/>
        <result column="release_user_name" property="releaseUserName"/>
        <result column="action_config" property="actionConfig"/>
        <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.ApplicationReleaseStatisticsDTO">
        <result column="release_count" property="releaseCount"/>
        <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, release_title, release_description, build_id, build_seq, app_id, app_name, app_tag, profile_id, profile_name, profile_tag,
        release_type, release_status, release_serialize, exception_handler, bundle_path, transfer_path, transfer_mode, rollback_release_id, timed_release, timed_release_time, create_user_id, create_user_name, audit_user_id, audit_user_name, audit_time, audit_reason,
        release_start_time, release_end_time, release_user_id, release_user_name, action_config, deleted, create_time, update_time
    </sql>

    <sql id="Base_Status">
        id, release_status, release_start_time, release_end_time
    </sql>

    <update id="setTimedReleaseTimeNull">
        UPDATE application_release
        SET timed_release_time = NULL
        WHERE id = #{id}
    </update>

    <select id="selectStatusById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Status"/>
        FROM application_release
        WHERE id = #{id}
        AND deleted = 1
    </select>

    <select id="selectStatusByIdList" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Status"/>
        FROM application_release
        WHERE id IN
        <foreach collection="idList" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
        AND deleted = 1
    </select>

    <select id="getReleaseStatistics" resultMap="StatisticsResultMap">
        SELECT
            COUNT(1) release_count,
            COUNT(release_status = 50 OR NULL) success_count,
            COUNT(release_status = 70 OR NULL) failure_count,
            IFNULL(
                FLOOR(
                    AVG(
                        IF(
                            release_status = 50
                            AND release_end_time IS NOT NULL
                            AND release_start_time IS NOT NULL,
                            UNIX_TIMESTAMP(release_end_time) * 1000 - UNIX_TIMESTAMP(release_start_time) * 1000,
                            NULL
                        )
                    )
                ), 0
            ) avg_used
        FROM application_release
        WHERE deleted = 1
        AND app_id = #{appId}
        AND profile_id = #{profileId}
        <if test="rangeStartDate != null">
            AND release_start_time >= #{rangeStartDate}
        </if>
    </select>

    <select id="getReleaseDateStatistics" resultMap="StatisticsResultMap">
        SELECT
            COUNT(1) release_count,
            COUNT(release_status = 50 OR NULL) success_count,
            COUNT(release_status = 70 OR NULL) failure_count,
            DATE_FORMAT(release_start_time, '%Y-%m-%d') date
        FROM application_release
        WHERE deleted = 1
        AND app_id = #{appId}
        AND profile_id = #{profileId}
        AND release_start_time >= #{rangeStartDate}
        GROUP BY date
    </select>

</mapper>
