<?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="batchExecutionLog">

  <sql id="batchExecutionLogSearchCondition">
    <if test="batch_config_id != null">
      AND bel.batch_config_id = #{batch_config_id}::varchar
    </if>
    <if test="execution_status != null and execution_status != ''">
      AND bel.execution_status = #{execution_status}
    </if>
    <if test="start_date != null and start_date != ''">
      AND bel.start_time &gt;= #{start_date}::timestamp
    </if>
    <if test="end_date != null and end_date != ''">
      AND bel.start_time &lt;= #{end_date}::timestamp
    </if>
  </sql>

  <select id="getBatchExecutionLogList" parameterType="map" resultType="map">
    SELECT
      bel.*,
      bc.batch_name,
      bc.description AS batch_description,
      bc.cron_schedule,
      bc.is_active AS batch_is_active

    FROM batch_execution_logs bel

    LEFT JOIN batch_configs bc ON bel.batch_config_id::INTEGER = bc.id

    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (bel.company_code = #{company_code} OR bel.company_code = '*')
    </if>
    <include refid="batchExecutionLogSearchCondition"/>
    <choose>
      <when test="sort_column != null and sort_column != ''">
        ORDER BY ${sortColumn}
        <if test="sort_direction != null and sort_direction != ''">
          ${sortDirection}
        </if>
      </when>
      <otherwise>
        ORDER BY bel.start_time DESC
      </otherwise>
    </choose>
    <include refid="common.pagination"/>
  </select>

  <select id="getBatchExecutionLogListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM batch_execution_logs bel

    LEFT JOIN batch_configs bc ON bel.batch_config_id::INTEGER = bc.id

    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (bel.company_code = #{company_code} OR bel.company_code = '*')
    </if>
    <include refid="batchExecutionLogSearchCondition"/>
  </select>

  <select id="getBatchExecutionLogInfo" parameterType="map" resultType="map">
    SELECT
      bel.*,
      bc.batch_name,
      bc.description AS batch_description,
      bc.cron_schedule,
      bc.is_active AS batch_is_active

    FROM batch_execution_logs bel

    LEFT JOIN batch_configs bc ON bel.batch_config_id::INTEGER = bc.id

    WHERE bel.id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (bel.company_code = #{company_code} OR bel.company_code = '*')
    </if>
  </select>

  <select id="getBatchExecutionLogLatest" parameterType="map" resultType="map">
    SELECT * FROM batch_execution_logs

    WHERE batch_config_id = #{batch_config_id}::varchar

    ORDER BY start_time DESC
    LIMIT 1
  </select>

  <select id="getBatchExecutionLogStats" parameterType="map" resultType="map">
    SELECT
      COUNT(*) AS total_executions,
      COUNT(CASE WHEN execution_status = 'SUCCESS' THEN 1 END) AS success_count,
      COUNT(CASE WHEN execution_status = 'FAILED' THEN 1 END) AS failed_count,
      ROUND(
        COUNT(CASE WHEN execution_status = 'SUCCESS' THEN 1 END)::numeric
        / NULLIF(COUNT(*), 0) * 100, 2
      ) AS success_rate,
      COALESCE(AVG(duration_ms), 0) AS average_duration_ms,
      COALESCE(SUM(total_records), 0) AS total_records_processed

    FROM batch_execution_logs

    WHERE 1=1
    <if test="batch_config_id != null">
      AND batch_config_id = #{batch_config_id}::varchar
    </if>
    <if test="start_date != null and start_date != ''">
      AND start_time &gt;= #{start_date}::timestamp
    </if>
    <if test="end_date != null and end_date != ''">
      AND start_time &lt;= #{end_date}::timestamp
    </if>
  </select>

  <insert id="insertBatchExecutionLog" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO batch_execution_logs (
      batch_config_id, company_code, execution_status,
      start_time, end_time, duration_ms,
      total_records, success_records, failed_records,
      error_message, error_details, server_name, process_id
    ) VALUES (
      #{batch_config_id}::varchar, #{company_code}, #{execution_status},
      COALESCE(#{start_time}::timestamp, NOW()),
      #{end_time}::timestamp,
      #{duration_ms},
      COALESCE(#{total_records}, '0'),
      COALESCE(#{success_records}, '0'),
      COALESCE(#{failed_records}, '0'),
      #{error_message}, #{error_details},
      #{server_name}, #{process_id}
    )
  </insert>

  <update id="updateBatchExecutionLog" parameterType="map">
    UPDATE batch_execution_logs
    <set>
      <if test="execution_status != null">execution_status = #{execution_status},</if>
      <if test="end_time != null">end_time = #{end_time}::timestamp,</if>
      <if test="duration_ms != null">duration_ms = #{duration_ms},</if>
      <if test="total_records != null">total_records = #{total_records},</if>
      <if test="success_records != null">success_records = #{success_records},</if>
      <if test="failed_records != null">failed_records = #{failed_records},</if>
      <if test="error_message != null">error_message = #{error_message},</if>
      <if test="error_details != null">error_details = #{error_details},</if>
    </set>

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </update>

  <delete id="deleteBatchExecutionLog" parameterType="map">
    DELETE FROM batch_execution_logs

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </delete>

</mapper>
