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

  <sql id="collectionSearchCondition">
    <if test="config_name != null and config_name != ''">
      AND CONFIG_NAME ILIKE '%' || #{config_name} || '%'
    </if>
    <if test="source_connection_id != null and source_connection_id != ''">
      AND SOURCE_CONNECTION_ID = #{source_connection_id}
    </if>
    <if test="collection_type != null and collection_type != ''">
      AND COLLECTION_TYPE = #{collection_type}
    </if>
    <if test="is_active != null and is_active != ''">
      AND IS_ACTIVE = (#{is_active} = 'Y')
    </if>
    <if test="search != null and search != ''">
      AND (CONFIG_NAME ILIKE '%' || #{search} || '%'
        OR DESCRIPTION ILIKE '%' || #{search} || '%')
    </if>
    <if test="keyword != null and keyword != ''">
      AND (CONFIG_NAME ILIKE '%' || #{keyword} || '%'
        OR DESCRIPTION ILIKE '%' || #{keyword} || '%')
    </if>
  </sql>

  <select id="getCollectionList" parameterType="map" resultType="map">
    SELECT
        ID
        , CONFIG_NAME
        , COMPANY_CODE
        , SOURCE_CONNECTION_ID
        , COLLECTION_TYPE
        , COLLECTION_OPTIONS
        , SCHEDULE_CRON
        , CASE WHEN IS_ACTIVE = 'true' THEN 'Y' ELSE 'N' END AS IS_ACTIVE
        , DESCRIPTION
        , CREATED_BY
        , UPDATED_BY
        , CREATED_DATE
        , UPDATED_DATE
    FROM DATA_COLLECTION_CONFIGS
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="collectionSearchCondition"/>
    <include refid="common.dynamicOrderBy"/>
    <include refid="common.pagination"/>
  </select>

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

  <select id="getCollectionInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , CONFIG_NAME
        , COMPANY_CODE
        , SOURCE_CONNECTION_ID
        , COLLECTION_TYPE
        , COLLECTION_OPTIONS
        , SCHEDULE_CRON
        , CASE WHEN IS_ACTIVE = 'true' THEN 'Y' ELSE 'N' END AS IS_ACTIVE
        , DESCRIPTION
        , CREATED_BY
        , UPDATED_BY
        , CREATED_DATE
        , UPDATED_DATE
    FROM DATA_COLLECTION_CONFIGS
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
    LIMIT 1
  </select>

  <insert id="insertCollection" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO DATA_COLLECTION_CONFIGS (
        COMPANY_CODE
        , CONFIG_NAME
        , DESCRIPTION
        , SOURCE_CONNECTION_ID
        , COLLECTION_TYPE
        , COLLECTION_OPTIONS
        , SCHEDULE_CRON
        , IS_ACTIVE
        , CREATED_BY
        , UPDATED_BY
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{company_code}
        , #{config_name}
        , #{description}
        , #{source_connection_id, jdbcType=INTEGER}
        , #{collection_type}
        , <choose>
            <when test="collection_options != null">#{collection_options}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , #{schedule_cron}
        , <choose>
            <when test="is_active != null">(#{is_active} = 'Y')</when>
            <otherwise>TRUE</otherwise>
          </choose>
        , #{created_by}
        , #{updated_by}
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateCollection" parameterType="map">
    UPDATE DATA_COLLECTION_CONFIGS
    SET
        CONFIG_NAME = COALESCE(#{config_name}, CONFIG_NAME)
        , DESCRIPTION = #{description}
        , SOURCE_CONNECTION_ID = COALESCE(#{source_connection_id, jdbcType=INTEGER}, SOURCE_CONNECTION_ID)
        , COLLECTION_TYPE = COALESCE(#{collection_type}, COLLECTION_TYPE)
        , COLLECTION_OPTIONS = CASE WHEN #{collection_options} IS NOT NULL
                                    THEN #{collection_options}::JSONB
                                    ELSE COLLECTION_OPTIONS END
        , SCHEDULE_CRON = #{schedule_cron}
        , IS_ACTIVE = CASE WHEN #{is_active} IS NOT NULL
                           THEN (#{is_active} = 'Y')
                           ELSE IS_ACTIVE END
        , UPDATED_BY = #{updated_by}
        , UPDATED_DATE = NOW()
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </update>

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

  <insert id="insertCollectionJob" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO DATA_COLLECTION_JOBS (
        CONFIG_ID
        , JOB_STATUS
        , STARTED_AT
        , CREATED_DATE
    ) VALUES (
        #{config_id}
        , #{job_status}
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateCollectionJob" parameterType="map">
    UPDATE DATA_COLLECTION_JOBS
    SET
        JOB_STATUS = #{job_status}
        , COMPLETED_AT = NOW()
    <if test="records_processed != null">, RECORDS_PROCESSED = #{records_processed}</if>
    <if test="error_message != null">, ERROR_MESSAGE = #{error_message}</if>
    WHERE ID = #{id}
  </update>

  <select id="getCollectionJobList" parameterType="map" resultType="map">
    SELECT
        J.ID
        , J.CONFIG_ID
        , C.CONFIG_NAME
        , J.JOB_STATUS
        , J.STARTED_AT
        , J.COMPLETED_AT
        , J.RECORDS_PROCESSED
        , J.ERROR_MESSAGE
        , J.CREATED_DATE
    FROM DATA_COLLECTION_JOBS J
    INNER JOIN DATA_COLLECTION_CONFIGS C
      ON C.ID = J.CONFIG_ID
    WHERE 1=1
    <if test="config_id != null">AND J.CONFIG_ID = #{config_id}</if>
    ORDER BY J.STARTED_AT DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getCollectionHistory" parameterType="map" resultType="map">
    SELECT
        ID
        , CONFIG_ID
        , JOB_STATUS
        , STARTED_AT
        , COMPLETED_AT
        , RECORDS_PROCESSED
        , ERROR_MESSAGE
        , CREATED_DATE
        , CASE WHEN COMPLETED_AT IS NOT NULL AND STARTED_AT IS NOT NULL
               THEN EXTRACT(EPOCH FROM (COMPLETED_AT - STARTED_AT)) * 1000
               ELSE NULL END AS EXECUTION_TIME_MS
    FROM DATA_COLLECTION_JOBS
    WHERE CONFIG_ID = #{config_id}
    ORDER BY STARTED_AT DESC
    LIMIT 50
  </select>

</mapper>
