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

  <!-- ═══════════════════════════════════════════════════════
       Flow Definitions
       ═══════════════════════════════════════════════════════ -->

  <select id="getFlowDefinitionList" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_DEFINITIONS
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <if test="table_name != null and table_name != ''">
      AND TABLE_NAME = #{table_name}
    </if>
    <if test="is_active != null">
      AND IS_ACTIVE = #{is_active}
    </if>
    ORDER BY CREATED_DATE DESC
  </select>

  <select id="getFlowDefinitionInfo" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_DEFINITIONS
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
    LIMIT 1
  </select>

  <insert id="insertFlowDefinition" parameterType="map"
          useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO FLOW_DEFINITIONS (
        NAME
        , DESCRIPTION
        , TABLE_NAME
        , DB_SOURCE_TYPE
        , DB_CONNECTION_ID
        , REST_API_CONNECTION_ID
        , REST_API_ENDPOINT
        , REST_API_JSON_PATH
        , REST_API_CONNECTIONS
        , COMPANY_CODE
        , IS_ACTIVE
        , CREATED_BY
        , UPDATED_BY
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{name}
        , #{description}
        , #{table_name}
        , #{db_source_type}
        , #{db_connection_id}
        , #{rest_api_connection_id}
        , #{rest_api_endpoint}
        , #{rest_api_json_path}
        , <choose>
            <when test="rest_api_connections != null">#{rest_api_connections}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , #{company_code}
        , COALESCE(#{is_active}, TRUE)
        , #{created_by}
        , #{created_by}
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateFlowDefinition" parameterType="map">
    UPDATE FLOW_DEFINITIONS
    <set>
      UPDATED_DATE = NOW(),
      <if test="name != null">NAME = #{name},</if>
      <if test="description != null">DESCRIPTION = #{description},</if>
      <if test="is_active != null">IS_ACTIVE = #{is_active},</if>
    </set>
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </update>

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

  <select id="checkTableExists" parameterType="map" resultType="int">
    SELECT
        COUNT(*)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'public'
      AND TABLE_NAME = #{table_name}
  </select>

  <!-- ═══════════════════════════════════════════════════════
       Flow Steps
       ═══════════════════════════════════════════════════════ -->

  <select id="getFlowStepList" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_STEPS
    WHERE FLOW_DEFINITION_ID = #{flow_id}
    ORDER BY STEP_ORDER ASC
  </select>

  <select id="getFlowStepInfo" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_STEPS
    WHERE ID = #{id}
    LIMIT 1
  </select>

  <insert id="insertFlowStep" parameterType="map"
          useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO FLOW_STEPS (
        FLOW_DEFINITION_ID
        , STEP_NAME
        , STEP_ORDER
        , TABLE_NAME
        , CONDITION_JSON
        , COLOR
        , POSITION_X
        , POSITION_Y
        , MOVE_TYPE
        , STATUS_COLUMN
        , STATUS_VALUE
        , TARGET_TABLE
        , FIELD_MAPPINGS
        , INTEGRATION_TYPE
        , INTEGRATION_CONFIG
        , DISPLAY_CONFIG
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{flow_definition_id}
        , #{step_name}
        , #{step_order}
        , #{table_name}
        , <choose>
            <when test="condition_json != null">#{condition_json}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , #{color}
        , #{position_x}
        , #{position_y}
        , #{move_type}
        , #{status_column}
        , #{status_value}
        , #{target_table}
        , <choose>
            <when test="field_mappings != null">#{field_mappings}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , #{integration_type}
        , <choose>
            <when test="integration_config != null">#{integration_config}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , <choose>
            <when test="display_config != null">#{display_config}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateFlowStep" parameterType="map">
    UPDATE FLOW_STEPS
    <set>
      UPDATED_DATE = NOW(),
      <if test="step_name       != null">STEP_NAME = #{step_name},</if>
      <if test="step_order      != null">STEP_ORDER = #{step_order},</if>
      <if test="table_name      != null">TABLE_NAME = #{table_name},</if>
      <if test="condition_json  != null">CONDITION_JSON = #{condition_json}::JSONB,</if>
      <if test="color          != null">COLOR = #{color},</if>
      <if test="position_x      != null">POSITION_X = #{position_x},</if>
      <if test="position_y      != null">POSITION_Y = #{position_y},</if>
      <if test="move_type       != null">MOVE_TYPE = #{move_type},</if>
      <if test="status_column   != null">STATUS_COLUMN = #{status_column},</if>
      <if test="status_value    != null">STATUS_VALUE = #{status_value},</if>
      <if test="target_table    != null">TARGET_TABLE = #{target_table},</if>
      <if test="field_mappings  != null">FIELD_MAPPINGS = #{field_mappings}::JSONB,</if>
      <if test="integration_type!= null">INTEGRATION_TYPE = #{integration_type},</if>
      <if test="integration_config != null">INTEGRATION_CONFIG = #{integration_config}::JSONB,</if>
      <if test="display_config  != null">DISPLAY_CONFIG = #{display_config}::JSONB,</if>
    </set>
    WHERE ID = #{id}
  </update>

  <delete id="deleteFlowStep" parameterType="map">
    DELETE FROM FLOW_STEPS
    WHERE ID = #{id}
  </delete>

  <!-- ═══════════════════════════════════════════════════════
       Flow Connections
       ═══════════════════════════════════════════════════════ -->

  <select id="getFlowConnectionList" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_CONNECTIONS
    WHERE FLOW_DEFINITION_ID = #{flow_id}
    ORDER BY ID ASC
  </select>

  <select id="getFlowConnectionInfo" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_CONNECTIONS
    WHERE ID = #{id}
    LIMIT 1
  </select>

  <insert id="insertFlowConnection" parameterType="map"
          useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO FLOW_CONNECTIONS (
        FLOW_DEFINITION_ID
        , FROM_STEP_ID
        , TO_STEP_ID
        , LABEL
        , CREATED_DATE
    ) VALUES (
        #{flow_definition_id}
        , #{from_step_id}
        , #{to_step_id}
        , #{label}
        , NOW()
    )
  </insert>

  <delete id="deleteFlowConnection" parameterType="map">
    DELETE FROM FLOW_CONNECTIONS
    WHERE ID = #{id}
  </delete>

  <!-- ═══════════════════════════════════════════════════════
       Execution: Dynamic user table
       tableName / statusColumn / statusValue 는 Service 에서
       information_schema 로 검증 후 전달
       ═══════════════════════════════════════════════════════ -->

  <select id="getFlowStepDataListCnt" parameterType="map" resultType="int">
    SELECT
        COUNT(*)
    FROM ${tableName}
    WHERE 1=1
    <if test="status_column != null and status_value != null">
      AND ${statusColumn} = #{status_value}
    </if>
  </select>

  <select id="getFlowStepDataList" parameterType="map" resultType="map">
    SELECT *
    FROM ${tableName}
    WHERE 1=1
    <if test="status_column != null and status_value != null">
      AND ${statusColumn} = #{status_value}
    </if>
    ORDER BY ID DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getFlowColumnLabelList" parameterType="map" resultType="map">
    SELECT
        COLUMN_NAME
        , COLUMN_LABEL
    FROM TABLE_TYPE_COLUMNS
    WHERE TABLE_NAME = #{table_name}
      AND COLUMN_LABEL IS NOT NULL
      AND COMPANY_CODE = '*'
  </select>

  <!--
    updateFlowRecordStatus: 레코드의 상태 컬럼 값을 변경 (데이터 이동)
    statusColumn 은 Service 에서 information_schema 검증 후 전달
  -->
  <update id="updateFlowRecordStatus" parameterType="map">
    UPDATE ${tableName}
    SET ${statusColumn} = #{to_status_value}
    WHERE ID = #{record_id}
  </update>

  <!-- ═══════════════════════════════════════════════════════
       Audit Logs  (flow_step_audit_logs)
       ═══════════════════════════════════════════════════════ -->

  <select id="getFlowAuditLogListByRecord" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_STEP_AUDIT_LOGS
    WHERE FLOW_DEFINITION_ID = #{flow_id}
      AND RECORD_ID = #{record_id}
    ORDER BY CREATED_DATE DESC
  </select>

  <select id="getFlowAuditLogList" parameterType="map" resultType="map">
    SELECT *
    FROM FLOW_STEP_AUDIT_LOGS
    WHERE FLOW_DEFINITION_ID = #{flow_id}
    ORDER BY CREATED_DATE DESC
    <if test="limit != null">
      LIMIT #{limit}
    </if>
  </select>

  <insert id="insertFlowAuditLog" parameterType="map">
    INSERT INTO FLOW_STEP_AUDIT_LOGS (
        FLOW_DEFINITION_ID
        , RECORD_ID
        , FROM_STEP_ID
        , TO_STEP_ID
        , FROM_STEP_NAME
        , TO_STEP_NAME
        , MOVED_BY
        , NOTE
        , METADATA
        , CREATED_DATE
    ) VALUES (
        #{flow_definition_id}
        , #{record_id}
        , #{from_step_id}
        , #{to_step_id}
        , #{from_step_name}
        , #{to_step_name}
        , #{moved_by}
        , #{note}
        , <choose>
            <when test="metadata != null">#{metadata}::JSONB</when>
            <otherwise>NULL</otherwise>
          </choose>
        , NOW()
    )
  </insert>

  <!-- ═══════════════════════════════════════════════════════
       Procedures  (information_schema — internal DB only)
       ═══════════════════════════════════════════════════════ -->

  <select id="getFlowProcedureList" parameterType="map" resultType="map">
    SELECT
        ROUTINE_NAME AS NAME
        , ROUTINE_TYPE AS TYPE
        , ROUTINE_SCHEMA AS SCHEMA
        , DATA_TYPE AS RETURN_TYPE
        , ROUTINE_DEFINITION IS NOT NULL AS HAS_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE IN ('FUNCTION', 'PROCEDURE')
      AND ROUTINE_SCHEMA NOT IN ('pg_catalog', 'information_schema')
    <if test="schema != null and schema != ''">
      AND ROUTINE_SCHEMA = #{schema}
    </if>
    ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME
  </select>

  <select id="getFlowProcedureParameterList" parameterType="map" resultType="map">
    SELECT
        P.PARAMETER_NAME AS NAME
        , P.DATA_TYPE AS DATA_TYPE
        , P.PARAMETER_MODE AS MODE
        , P.ORDINAL_POSITION AS POSITION
    FROM INFORMATION_SCHEMA.PARAMETERS P
    JOIN INFORMATION_SCHEMA.ROUTINES R
        ON P.SPECIFIC_NAME = R.SPECIFIC_NAME
        AND P.SPECIFIC_SCHEMA = R.ROUTINE_SCHEMA
    WHERE R.ROUTINE_NAME = #{name}
    <if test="schema != null and schema != ''">
      AND R.ROUTINE_SCHEMA = #{schema}
    </if>
    ORDER BY P.ORDINAL_POSITION
  </select>

</mapper>
