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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.orionsec.ops.entity.domain.ApplicationPipelineTaskDO">
        <id column="id" property="id"/>
        <result column="pipeline_id" property="pipelineId"/>
        <result column="pipeline_name" property="pipelineName"/>
        <result column="profile_id" property="profileId"/>
        <result column="profile_name" property="profileName"/>
        <result column="profile_tag" property="profileTag"/>
        <result column="exec_title" property="execTitle"/>
        <result column="exec_description" property="execDescription"/>
        <result column="exec_status" property="execStatus"/>
        <result column="timed_exec" property="timedExec"/>
        <result column="timed_exec_time" property="timedExecTime"/>
        <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="exec_user_id" property="execUserId"/>
        <result column="exec_user_name" property="execUserName"/>
        <result column="exec_start_time" property="execStartTime"/>
        <result column="exec_end_time" property="execEndTime"/>
        <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.ApplicationPipelineTaskStatisticsDTO">
        <result column="exec_count" property="execCount"/>
        <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, pipeline_id, pipeline_name, profile_id, profile_name, profile_tag, exec_title, exec_description, exec_status, timed_exec, timed_exec_time, create_user_id, create_user_name, audit_user_id, audit_user_name, audit_time, audit_reason, exec_user_id, exec_user_name, exec_start_time, exec_end_time, deleted, create_time, update_time
    </sql>

    <sql id="Base_Status_Columns">
        id, exec_status, exec_start_time, exec_end_time
    </sql>

    <update id="setTimedExecTimeNull">
        UPDATE application_pipeline_task
        SET timed_exec_time = NULL
        WHERE id = #{id}
    </update>

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

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

    <select id="getPipelineTaskStatistics" resultMap="StatisticsResultMap">
        SELECT
            COUNT(1) exec_count,
            COUNT(exec_status = 50 OR NULL) success_count,
            COUNT(exec_status = 70 OR NULL) failure_count,
            IFNULL(
                FLOOR(
                    AVG(
                        IF(
                            exec_status = 50
                            AND exec_end_time IS NOT NULL
                            AND exec_start_time IS NOT NULL,
                            UNIX_TIMESTAMP(exec_end_time) * 1000 - UNIX_TIMESTAMP(exec_start_time) * 1000,
                            NULL
                        )
                    )
                ), 0
            ) avg_used
        FROM application_pipeline_task
        WHERE deleted = 1
        AND pipeline_id = #{pipelineId}
        <if test="rangeStartDate != null">
            AND exec_start_time >= #{rangeStartDate}
        </if>
    </select>

    <select id="getPipelineTaskDateStatistics" resultMap="StatisticsResultMap">
        SELECT
            COUNT(1) exec_count,
            COUNT(exec_status = 50 OR NULL) success_count,
            COUNT(exec_status = 70 OR NULL) failure_count,
            DATE_FORMAT(exec_start_time, '%Y-%m-%d') date
        FROM application_pipeline_task
        WHERE deleted = 1
        AND pipeline_id = #{pipelineId}
        AND exec_start_time >= #{rangeStartDate}
        GROUP BY date
    </select>

</mapper>
