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

  <sql id="batchSearchCondition">
    <if test="search != null and search != ''">
      AND (BATCH_NAME ILIKE '%' || #{search} || '%'
           OR DESCRIPTION ILIKE '%' || #{search} || '%')
    </if>
    <if test="is_active != null and is_active != ''">
      AND IS_ACTIVE = #{is_active}
    </if>
  </sql>

  <select id="getBatchList" parameterType="map" resultType="map">
    SELECT *
    FROM BATCH_CONFIGS
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="batchSearchCondition"/>
    ORDER BY CREATED_DATE DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getBatchListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM BATCH_CONFIGS
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="batchSearchCondition"/>
  </select>

  <select id="getBatchInfo" parameterType="map" resultType="map">
    SELECT *
    FROM BATCH_CONFIGS
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </select>

  <insert id="insertBatch" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO BATCH_CONFIGS (
        BATCH_NAME
        , DESCRIPTION
        , CRON_SCHEDULE
        , IS_ACTIVE
        , COMPANY_CODE
        , CREATED_BY
        , CREATED_DATE
        , UPDATED_DATE
        <if test="save_mode != null">, SAVE_MODE</if>
        <if test="conflict_key != null">, CONFLICT_KEY</if>
        <if test="auth_service_name != null">, AUTH_SERVICE_NAME</if>
        <if test="data_array_path != null">, DATA_ARRAY_PATH</if>
        <if test="node_flow_id != null">, NODE_FLOW_ID</if>
        <if test="node_flow_context != null">, NODE_FLOW_CONTEXT</if>
    ) VALUES (
        #{batch_name}
        , #{description}
        , #{cron_schedule}
        , <choose>
            <when test="is_active != null">#{is_active}</when>
            <otherwise>'Y'</otherwise>
          </choose>
        , #{company_code}
        , #{created_by}
        , NOW()
        , NOW()
        <if test="save_mode != null">, #{save_mode}</if>
        <if test="conflict_key != null">, #{conflict_key}</if>
        <if test="auth_service_name != null">, #{auth_service_name}</if>
        <if test="data_array_path != null">, #{data_array_path}</if>
        <if test="node_flow_id != null">, #{node_flow_id}</if>
        <if test="node_flow_context != null">, #{node_flow_context}::jsonb</if>
    )
  </insert>

  <update id="updateBatch" parameterType="map">
    UPDATE BATCH_CONFIGS
    <set>
      <if test="batch_name != null">BATCH_NAME = #{batch_name},</if>
      <if test="description != null">DESCRIPTION = #{description},</if>
      <if test="cron_schedule != null">CRON_SCHEDULE = #{cron_schedule},</if>
      <if test="is_active != null">IS_ACTIVE = #{is_active},</if>
      <if test="save_mode != null">SAVE_MODE = #{save_mode},</if>
      <if test="conflict_key != null">CONFLICT_KEY = #{conflict_key},</if>
      <if test="auth_service_name != null">AUTH_SERVICE_NAME = #{auth_service_name},</if>
      <if test="data_array_path != null">DATA_ARRAY_PATH = #{data_array_path},</if>
      <!-- execution_type column removed: does not exist in DB -->
      <if test="node_flow_id != null">NODE_FLOW_ID = #{node_flow_id},</if>
      <if test="node_flow_context != null">NODE_FLOW_CONTEXT = #{node_flow_context}::jsonb,</if>
      <if test="updated_by != null">UPDATED_BY = #{updated_by},</if>
      UPDATED_DATE = NOW()
    </set>
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </update>

  <delete id="deleteBatch" parameterType="map">
    DELETE FROM BATCH_CONFIGS
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <!-- batch_mappings: 특정 batch_config_id 의 매핑 행들 조회 -->
  <select id="getBatchMappingsByConfigId" parameterType="map" resultType="map">
    SELECT
        ID
        , BATCH_CONFIG_ID
        , COMPANY_CODE
        , FROM_CONNECTION_TYPE
        , FROM_CONNECTION_ID
        , FROM_TABLE_NAME
        , FROM_COLUMN_NAME
        , FROM_COLUMN_TYPE
        , FROM_API_URL
        , FROM_API_KEY
        , FROM_API_METHOD
        , FROM_API_PARAM_TYPE
        , FROM_API_PARAM_NAME
        , FROM_API_PARAM_VALUE
        , FROM_API_PARAM_SOURCE
        , FROM_API_BODY
        , TO_CONNECTION_TYPE
        , TO_CONNECTION_ID
        , TO_TABLE_NAME
        , TO_COLUMN_NAME
        , TO_COLUMN_TYPE
        , TO_API_URL
        , TO_API_KEY
        , TO_API_METHOD
        , TO_API_BODY
        , MAPPING_ORDER
        , MAPPING_TYPE
        , MAPPING_CONFIG::TEXT AS MAPPING_CONFIG
        , CREATED_BY
        , CREATED_DATE
    FROM BATCH_MAPPINGS
    WHERE BATCH_CONFIG_ID = #{batch_config_id}::varchar
    ORDER BY MAPPING_ORDER, ID
  </select>

  <!-- batch_mappings: 단건 INSERT (replace-all 패턴이라 INSERT/DELETE 만 사용) -->
  <insert id="insertBatchMapping" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO BATCH_MAPPINGS (
        BATCH_CONFIG_ID
        , COMPANY_CODE
        , FROM_CONNECTION_TYPE
        , FROM_CONNECTION_ID
        , FROM_TABLE_NAME
        , FROM_COLUMN_NAME
        , FROM_COLUMN_TYPE
        , FROM_API_URL
        , FROM_API_KEY
        , FROM_API_METHOD
        , FROM_API_PARAM_TYPE
        , FROM_API_PARAM_NAME
        , FROM_API_PARAM_VALUE
        , FROM_API_PARAM_SOURCE
        , FROM_API_BODY
        , TO_CONNECTION_TYPE
        , TO_CONNECTION_ID
        , TO_TABLE_NAME
        , TO_COLUMN_NAME
        , TO_COLUMN_TYPE
        , TO_API_URL
        , TO_API_KEY
        , TO_API_METHOD
        , TO_API_BODY
        , MAPPING_ORDER
        , MAPPING_TYPE
        , MAPPING_CONFIG
        , CREATED_BY
        , CREATED_DATE
    ) VALUES (
        #{batch_config_id}::varchar
        , #{company_code}
        , #{from_connection_type}
        , #{from_connection_id}
        , #{from_table_name}
        , #{from_column_name}
        , #{from_column_type}
        , #{from_api_url}
        , #{from_api_key}
        , #{from_api_method}
        , #{from_api_param_type}
        , #{from_api_param_name}
        , #{from_api_param_value}
        , #{from_api_param_source}
        , #{from_api_body}
        , #{to_connection_type}
        , #{to_connection_id}
        , #{to_table_name}
        , #{to_column_name}
        , #{to_column_type}
        , #{to_api_url}
        , #{to_api_key}
        , #{to_api_method}
        , #{to_api_body}
        , #{mapping_order}
        , <choose>
            <when test="mapping_type != null and mapping_type != ''">#{mapping_type}</when>
            <otherwise>'direct'</otherwise>
          </choose>
        , #{mapping_config,jdbcType=OTHER}::jsonb
        , #{created_by}
        , NOW()
    )
  </insert>

  <!-- batch_mappings: 특정 batch_config_id 의 매핑 전부 삭제 (replace-all 의 앞단계) -->
  <delete id="deleteBatchMappingsByConfigId" parameterType="map">
    DELETE FROM BATCH_MAPPINGS WHERE BATCH_CONFIG_ID = #{batch_config_id}::varchar
  </delete>

  <!-- 내부 DB 테이블 목록 조회 -->
  <select id="getInternalTables" resultType="map">
    SELECT
        TABLE_NAME
        , TABLE_TYPE
        , TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'public'
      AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY TABLE_NAME
  </select>

  <!-- 내부 DB 테이블 컬럼 목록 조회 -->
  <select id="getInternalTableColumns" parameterType="map" resultType="map">
    SELECT
        COLUMN_NAME
        , DATA_TYPE
        , IS_NULLABLE
        , COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'public'
      AND TABLE_NAME = #{table_name}
    ORDER BY ORDINAL_POSITION
  </select>

</mapper>
