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

  <!-- ================================================================
       approval_definitions
       ================================================================ -->

  <select id="selectDefinitions" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_DEFINITIONS
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <if test="is_active != null and is_active != ''">
      AND IS_ACTIVE = #{is_active}
    </if>
    <if test="search_like != null">
      AND (DEFINITION_NAME ILIKE #{search_like} OR DEFINITION_NAME_ENG ILIKE #{search_like})
    </if>
    ORDER BY COMPANY_CODE, DEFINITION_ID ASC
  </select>

  <select id="selectDefinitionById" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_DEFINITIONS
    WHERE DEFINITION_ID = #{definition_id}
    <include refid="common.companyCodeFilter"/>
  </select>

  <insert id="insertDefinition" parameterType="map" useGeneratedKeys="true" keyProperty="definitionId" keyColumn="definition_id">
    INSERT INTO APPROVAL_DEFINITIONS (
      DEFINITION_NAME, DEFINITION_NAME_ENG, DESCRIPTION, DEFAULT_TEMPLATE_ID,
      MAX_STEPS, ALLOW_SELF_APPROVAL, ALLOW_CANCEL, IS_ACTIVE,
      COMPANY_CODE, CREATED_BY, UPDATED_BY
    ) VALUES (
      #{definition_name}, #{definition_name_eng}, #{description}, #{default_template_id},
      COALESCE(#{max_steps}, '5'), COALESCE(#{allow_self_approval}, 'false'),
      COALESCE(#{allow_cancel}, 'true'), COALESCE(#{is_active}, 'Y'),
      #{company_code}, #{user_id}, #{user_id}
    )
  </insert>

  <update id="updateDefinition" parameterType="map">
    UPDATE APPROVAL_DEFINITIONS
    <set>
      <if test="definition_name != null">
        DEFINITION_NAME = #{definition_name},
      </if>
      <if test="definition_name_eng != null">
        DEFINITION_NAME_ENG = #{definition_name_eng},
      </if>
      <if test="description != null">
        DESCRIPTION = #{description},
      </if>
      <if test="default_template_id != null">
        DEFAULT_TEMPLATE_ID = #{default_template_id},
      </if>
      <if test="max_steps != null">
        MAX_STEPS = #{max_steps},
      </if>
      <if test="allow_self_approval != null">
        ALLOW_SELF_APPROVAL = #{allow_self_approval},
      </if>
      <if test="allow_cancel != null">
        ALLOW_CANCEL = #{allow_cancel},
      </if>
      <if test="is_active != null">
        IS_ACTIVE = #{is_active},
      </if>
      UPDATED_BY = #{user_id},
      UPDATED_DATE = NOW()
    </set>
    WHERE DEFINITION_ID = #{definition_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <delete id="deleteDefinition" parameterType="map">
    DELETE FROM APPROVAL_DEFINITIONS
    WHERE DEFINITION_ID = #{definition_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </delete>

  <!-- ================================================================
       approval_line_templates
       ================================================================ -->

  <select id="selectTemplates" parameterType="map" resultType="map">
    SELECT T.*, D.DEFINITION_NAME
    FROM APPROVAL_LINE_TEMPLATES T
    LEFT JOIN APPROVAL_DEFINITIONS D
      ON T.DEFINITION_ID = D.DEFINITION_ID AND T.COMPANY_CODE = D.COMPANY_CODE
    WHERE 1=1
    <if test='company_code != null and company_code != "*"'>
      AND T.COMPANY_CODE = #{company_code}
    </if>
    <if test="definition_id != null">
      AND T.DEFINITION_ID = #{definition_id}
    </if>
    <if test="is_active != null and is_active != ''">
      AND T.IS_ACTIVE = #{is_active}
    </if>
    ORDER BY T.COMPANY_CODE, T.TEMPLATE_ID ASC
  </select>

  <select id="selectTemplateById" parameterType="map" resultType="map">
    SELECT T.*, D.DEFINITION_NAME
    FROM APPROVAL_LINE_TEMPLATES T
    LEFT JOIN APPROVAL_DEFINITIONS D
      ON T.DEFINITION_ID = D.DEFINITION_ID AND T.COMPANY_CODE = D.COMPANY_CODE
    WHERE T.TEMPLATE_ID = #{template_id}
    <if test='company_code != null and company_code != "*"'>
      AND T.COMPANY_CODE = #{company_code}
    </if>
  </select>

  <insert id="insertTemplate" parameterType="map" useGeneratedKeys="true" keyProperty="templateId" keyColumn="template_id">
    INSERT INTO APPROVAL_LINE_TEMPLATES (
      TEMPLATE_NAME, DESCRIPTION, DEFINITION_ID, AFTER_APPROVAL_FLOW_ID,
      IS_ACTIVE, COMPANY_CODE, CREATED_BY, UPDATED_BY
    ) VALUES (
      #{template_name}, #{description}, #{definition_id},
      #{after_approval_flow_id},
      COALESCE(#{is_active}, 'Y'), #{company_code}, #{user_id}, #{user_id}
    )
  </insert>

  <update id="updateTemplate" parameterType="map">
    UPDATE APPROVAL_LINE_TEMPLATES
    <set>
      <if test="template_name != null">
        TEMPLATE_NAME = #{template_name},
      </if>
      <if test="description != null">
        DESCRIPTION = #{description},
      </if>
      <if test="definition_id != null">
        DEFINITION_ID = #{definition_id},
      </if>
      <if test="after_approval_flow_id != null">
        AFTER_APPROVAL_FLOW_ID = #{after_approval_flow_id},
      </if>
      <if test="is_active != null">
        IS_ACTIVE = #{is_active},
      </if>
      UPDATED_BY = #{user_id},
      UPDATED_DATE = NOW()
    </set>
    WHERE TEMPLATE_ID = #{template_id}
    AND COMPANY_CODE = #{company_code}
  </update>

  <delete id="deleteTemplate" parameterType="map">
    DELETE FROM APPROVAL_LINE_TEMPLATES
    WHERE TEMPLATE_ID = #{template_id}
    AND COMPANY_CODE = #{company_code}
  </delete>

  <!-- ================================================================
       approval_line_template_steps
       ================================================================ -->

  <select id="selectTemplateSteps" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_LINE_TEMPLATE_STEPS
    WHERE TEMPLATE_ID = #{template_id}
    <if test='company_code != null and company_code != "*"'>
      AND COMPANY_CODE = #{company_code}
    </if>
    ORDER BY STEP_ORDER ASC
  </select>

  <insert id="insertTemplateStep" parameterType="map">
    INSERT INTO APPROVAL_LINE_TEMPLATE_STEPS (
      TEMPLATE_ID, STEP_ORDER, APPROVER_TYPE, APPROVER_USER_ID,
      APPROVER_POSITION, APPROVER_DEPT_CODE, APPROVER_LABEL, COMPANY_CODE
    ) VALUES (
      #{template_id}, #{step_order},
      COALESCE(#{approver_type}, 'user'),
      #{approver_user_id}, #{approver_position}, #{approver_dept_code},
      #{approver_label}, #{company_code}
    )
  </insert>

  <delete id="deleteTemplateStepsByTemplateId" parameterType="map">
    DELETE FROM APPROVAL_LINE_TEMPLATE_STEPS
    WHERE TEMPLATE_ID = #{template_id}
    AND COMPANY_CODE = #{company_code}
  </delete>

  <!-- ================================================================
       approval_requests
       ================================================================ -->

  <select id="selectRequests" parameterType="map" resultType="map">
    SELECT R.*, D.DEFINITION_NAME
    FROM APPROVAL_REQUESTS R
    LEFT JOIN APPROVAL_DEFINITIONS D
      ON R.DEFINITION_ID = D.DEFINITION_ID AND R.COMPANY_CODE = D.COMPANY_CODE
    WHERE 1=1
    <if test='company_code != null and company_code != "*"'>
      AND (R.COMPANY_CODE = #{company_code} OR R.COMPANY_CODE = '*')
    </if>
    <if test="status != null and status != ''">
      AND R.STATUS = #{status}
    </if>
    <if test="target_table != null and target_table != ''">
      AND R.TARGET_TABLE = #{target_table}
    </if>
    <if test="target_record_id != null and target_record_id != ''">
      AND R.TARGET_RECORD_ID = #{target_record_id}
    </if>
    <if test="requester_id != null and requester_id != ''">
      AND R.REQUESTER_ID = #{requester_id}
    </if>
    <if test='my_approvals != null and my_approvals == "true"'>
      AND EXISTS (
        SELECT 1 FROM APPROVAL_LINES L
        WHERE L.REQUEST_ID = R.REQUEST_ID
          AND L.APPROVER_ID IN
          <foreach collection="effective_user_ids" item="uid" open="(" separator="," close=")">
            #{uid}
          </foreach>
          AND L.STATUS = 'pending'
          AND L.COMPANY_CODE = R.COMPANY_CODE
      )
    </if>
    ORDER BY R.CREATED_AT DESC
    <if test="page_limit != null">
      LIMIT #{page_limit} OFFSET #{page_offset}
    </if>
  </select>

  <select id="countRequests" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM APPROVAL_REQUESTS R
    WHERE 1=1
    <if test='company_code != null and company_code != "*"'>
      AND (R.COMPANY_CODE = #{company_code} OR R.COMPANY_CODE = '*')
    </if>
    <if test="status != null and status != ''">
      AND R.STATUS = #{status}
    </if>
    <if test="target_table != null and target_table != ''">
      AND R.TARGET_TABLE = #{target_table}
    </if>
    <if test="target_record_id != null and target_record_id != ''">
      AND R.TARGET_RECORD_ID = #{target_record_id}
    </if>
    <if test="requester_id != null and requester_id != ''">
      AND R.REQUESTER_ID = #{requester_id}
    </if>
    <if test='my_approvals != null and my_approvals == "true"'>
      AND EXISTS (
        SELECT 1 FROM APPROVAL_LINES L
        WHERE L.REQUEST_ID = R.REQUEST_ID
          AND L.APPROVER_ID IN
          <foreach collection="effective_user_ids" item="uid" open="(" separator="," close=")">
            #{uid}
          </foreach>
          AND L.STATUS = 'pending'
          AND L.COMPANY_CODE = R.COMPANY_CODE
      )
    </if>
  </select>

  <select id="selectRequestById" parameterType="map" resultType="map">
    SELECT R.*, D.DEFINITION_NAME,
           R.TARGET_RECORD_DATA::TEXT AS TARGET_RECORD_DATA
    FROM APPROVAL_REQUESTS R
    LEFT JOIN APPROVAL_DEFINITIONS D
      ON R.DEFINITION_ID = D.DEFINITION_ID AND R.COMPANY_CODE = D.COMPANY_CODE
    WHERE R.REQUEST_ID = #{request_id}
    <if test='company_code != null and company_code != "*"'>
      AND (R.COMPANY_CODE = #{company_code} OR R.COMPANY_CODE = '*')
    </if>
  </select>

  <select id="selectRequestByIdForUpdate" parameterType="map" resultType="map">
    SELECT R.*, R.TARGET_RECORD_DATA::TEXT AS TARGET_RECORD_DATA
    FROM APPROVAL_REQUESTS R
    WHERE R.REQUEST_ID = #{request_id}
    AND (R.COMPANY_CODE = #{company_code} OR R.COMPANY_CODE = '*')
    FOR UPDATE
  </select>

  <insert id="insertRequest" parameterType="map" useGeneratedKeys="true" keyProperty="requestId" keyColumn="request_id">
    INSERT INTO APPROVAL_REQUESTS (
      TITLE, DESCRIPTION, DEFINITION_ID, TEMPLATE_ID, TARGET_TABLE, TARGET_RECORD_ID,
      TARGET_RECORD_DATA, STATUS, CURRENT_STEP, TOTAL_STEPS, APPROVAL_TYPE,
      REQUESTER_ID, REQUESTER_NAME, REQUESTER_DEPT,
      SCREEN_ID, BUTTON_COMPONENT_ID, COMPANY_CODE
      <if test="final_approver_id != null">
        , FINAL_APPROVER_ID, COMPLETED_AT
      </if>
    ) VALUES (
      #{title}, #{description}, #{definition_id}, #{template_id}, #{target_table}, #{target_record_id},
      #{target_record_data}::JSONB, #{status}, 1, #{total_steps}, #{approval_type},
      #{requester_id}, #{requester_name}, #{requester_dept},
      #{screen_id}, #{button_component_id}, #{company_code}
      <if test="final_approver_id != null">
        , #{final_approver_id}, NOW()
      </if>
    )
  </insert>

  <update id="completeRequest" parameterType="map">
    UPDATE APPROVAL_REQUESTS
    SET STATUS = 'approved',
        FINAL_APPROVER_ID = #{user_id},
        FINAL_COMMENT = #{comment},
        COMPLETED_AT = NOW(),
        UPDATED_DATE = NOW()
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="rejectRequest" parameterType="map">
    UPDATE APPROVAL_REQUESTS
    SET STATUS = 'rejected',
        FINAL_APPROVER_ID = #{user_id},
        FINAL_COMMENT = #{comment},
        COMPLETED_AT = NOW(),
        UPDATED_DATE = NOW()
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="cancelRequest" parameterType="map">
    UPDATE APPROVAL_REQUESTS
    SET STATUS = 'cancelled',
        UPDATED_DATE = NOW()
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="postApproveRequest" parameterType="map">
    UPDATE APPROVAL_REQUESTS
    SET STATUS = 'approved',
        IS_POST_APPROVED = TRUE,
        POST_APPROVED_AT = NOW(),
        FINAL_APPROVER_ID = #{user_id},
        FINAL_COMMENT = #{comment},
        COMPLETED_AT = NOW(),
        UPDATED_DATE = NOW()
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="updateRequestCurrentStep" parameterType="map">
    UPDATE APPROVAL_REQUESTS
    SET CURRENT_STEP = #{next_step},
        UPDATED_DATE = NOW()
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="updateRequestStatus" parameterType="map">
    UPDATE APPROVAL_REQUESTS
    SET STATUS = #{status},
        UPDATED_DATE = NOW()
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <!-- ================================================================
       approval_lines
       ================================================================ -->

  <select id="selectLinesByRequestId" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_LINES
    WHERE REQUEST_ID = #{request_id}
    <if test='company_code != null and company_code != "*"'>
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
    ORDER BY STEP_ORDER ASC
  </select>

  <select id="selectLineByIdForUpdate" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_LINES
    WHERE LINE_ID = #{line_id}
    <if test='company_code != null and company_code != "*"'>
      AND COMPANY_CODE IN (#{company_code}, '*')
    </if>
    FOR UPDATE
  </select>

  <insert id="insertLine" parameterType="map">
    INSERT INTO APPROVAL_LINES (
      REQUEST_ID, STEP_ORDER, APPROVER_ID, APPROVER_NAME, APPROVER_POSITION,
      APPROVER_DEPT, APPROVER_LABEL, STATUS, STEP_TYPE, COMPANY_CODE
      <if test="processed_at != null">
        , PROCESSED_AT
      </if>
    ) VALUES (
      #{request_id}, #{step_order}, #{approver_id}, #{approver_name}, #{approver_position},
      #{approver_dept}, #{approver_label}, #{status}, #{step_type}, #{company_code}
      <if test="processed_at != null">
        , NOW()
      </if>
    )
  </insert>

  <update id="updateLine" parameterType="map">
    UPDATE APPROVAL_LINES
    SET STATUS = #{status},
        COMMENT = #{comment},
        PROCESSED_AT = NOW(),
        PROXY_FOR = #{proxy_for},
        PROXY_REASON = #{proxy_reason}
    WHERE LINE_ID = #{line_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="skipRemainingLines" parameterType="map">
    UPDATE APPROVAL_LINES
    SET STATUS = 'skipped'
    WHERE REQUEST_ID = #{request_id}
      AND STATUS IN ('pending', 'waiting')
      AND LINE_ID != #{line_id}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <update id="activateNextStepLines" parameterType="map">
    UPDATE APPROVAL_LINES
    SET STATUS = 'pending'
    WHERE REQUEST_ID = #{request_id}
      AND STEP_ORDER = #{next_step}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <select id="selectLinesForStep" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_LINES
    WHERE REQUEST_ID = #{request_id}
      AND STEP_ORDER = #{step_order}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    FOR UPDATE
  </select>

  <select id="countRemainingLinesInStep" parameterType="map" resultType="int">
    SELECT COUNT(*) FROM APPROVAL_LINES
    WHERE REQUEST_ID = #{request_id}
      AND STEP_ORDER = #{step_order}
      AND STATUS NOT IN ('approved', 'skipped')
      AND LINE_ID != #{line_id}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <select id="countPendingLinesForRequest" parameterType="map" resultType="int">
    SELECT COUNT(*) FROM APPROVAL_LINES
    WHERE REQUEST_ID = #{request_id}
      AND STATUS NOT IN ('approved', 'skipped')
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <!-- 통보 단계 라인 일괄 승인 (createRequest 내 첫 step이 notification일 때) -->
  <update id="approveNotificationLines" parameterType="map">
    UPDATE APPROVAL_LINES
    SET STATUS = 'approved',
        COMMENT = '자동 통보 처리',
        PROCESSED_AT = NOW()
    WHERE REQUEST_ID = #{request_id}
      AND STEP_ORDER = #{step_order}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <select id="selectMyPendingLines" parameterType="map" resultType="map">
    SELECT L.*,
           R.TITLE, R.TARGET_TABLE, R.TARGET_RECORD_ID,
           R.REQUESTER_NAME, R.REQUESTER_DEPT,
           R.CREATED_AT AS REQUEST_CREATED_DATE
    FROM APPROVAL_LINES L
    JOIN APPROVAL_REQUESTS R
      ON L.REQUEST_ID = R.REQUEST_ID AND L.COMPANY_CODE = R.COMPANY_CODE
    WHERE L.APPROVER_ID IN
      <foreach collection="effective_user_ids" item="uid" open="(" separator="," close=")">
        #{uid}
      </foreach>
      AND L.STATUS = 'pending'
      AND (L.COMPANY_CODE = #{company_code} OR L.COMPANY_CODE = '*')
    ORDER BY R.CREATED_AT ASC
  </select>

  <!-- ================================================================
       approval_proxy_settings
       ================================================================ -->

  <select id="selectProxySettings" parameterType="map" resultType="map">
    SELECT PS.*,
           U1.USER_NAME AS ORIGINAL_USER_NAME, U1.DEPT_NAME AS ORIGINAL_DEPT_NAME,
           U2.USER_NAME AS PROXY_USER_NAME,    U2.DEPT_NAME AS PROXY_DEPT_NAME
    FROM APPROVAL_PROXY_SETTINGS PS
    LEFT JOIN USER_INFO U1 ON PS.ORIGINAL_USER_ID = U1.USER_ID AND PS.COMPANY_CODE = U1.COMPANY_CODE
    LEFT JOIN USER_INFO U2 ON PS.PROXY_USER_ID    = U2.USER_ID AND PS.COMPANY_CODE = U2.COMPANY_CODE
    WHERE (PS.COMPANY_CODE = #{company_code} OR PS.COMPANY_CODE = '*')
    ORDER BY PS.CREATED_DATE DESC
  </select>

  <select id="selectProxyById" parameterType="map" resultType="map">
    SELECT * FROM APPROVAL_PROXY_SETTINGS
    WHERE ID = #{id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <select id="countProxyOverlap" parameterType="map" resultType="int">
    SELECT COUNT(*) FROM APPROVAL_PROXY_SETTINGS
    WHERE ORIGINAL_USER_ID = #{original_user_id}
      AND IS_ACTIVE = 'Y'
      AND DATERANGE(START_DATE, END_DATE, '[]') &amp;&amp; DATERANGE(#{start_date}::DATE, #{end_date}::DATE, '[]')
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <insert id="insertProxySetting" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO APPROVAL_PROXY_SETTINGS
      (ORIGINAL_USER_ID, PROXY_USER_ID, START_DATE, END_DATE, REASON, IS_ACTIVE, COMPANY_CODE)
    VALUES
      (#{original_user_id}, #{proxy_user_id}, #{start_date}, #{end_date},
       #{reason}, COALESCE(#{is_active}, 'Y'), #{company_code})
  </insert>

  <update id="updateProxySetting" parameterType="map">
    UPDATE APPROVAL_PROXY_SETTINGS
    <set>
      <if test="proxy_user_id != null">
        PROXY_USER_ID = #{proxy_user_id},
      </if>
      <if test="start_date != null">
        START_DATE = #{start_date},
      </if>
      <if test="end_date != null">
        END_DATE = #{end_date},
      </if>
      <if test="reason != null">
        REASON = #{reason},
      </if>
      <if test="is_active != null">
        IS_ACTIVE = #{is_active},
      </if>
      UPDATED_DATE = NOW()
    </set>
    WHERE ID = #{id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </update>

  <delete id="deleteProxySetting" parameterType="map">
    DELETE FROM APPROVAL_PROXY_SETTINGS
    WHERE ID = #{id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </delete>

  <!-- 어댑터: USER_SUBSTITUTES 참조 (T7, 086 마이그레이션 이후). -->
  <select id="selectActiveProxyForLine" parameterType="map" resultType="map">
    SELECT *
    FROM USER_SUBSTITUTES
    WHERE ORIGINAL_USER_ID = #{original_user_id}
      AND PROXY_USER_ID = #{proxy_user_id}
      AND IS_ACTIVE = TRUE
      AND (START_DATE IS NULL OR START_DATE &lt;= CURRENT_DATE)
      AND END_DATE &gt;= CURRENT_DATE
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    LIMIT 1
  </select>

  <select id="selectActiveProxy" parameterType="map" resultType="map">
    SELECT PS.*, U.USER_NAME AS PROXY_USER_NAME
    FROM APPROVAL_PROXY_SETTINGS PS
    LEFT JOIN USER_INFO U ON PS.PROXY_USER_ID = U.USER_ID AND PS.COMPANY_CODE = U.COMPANY_CODE
    WHERE PS.ORIGINAL_USER_ID = #{user_id}
      AND PS.IS_ACTIVE = 'Y'
      AND PS.START_DATE &lt;= CURRENT_DATE
      AND PS.END_DATE &gt;= CURRENT_DATE
      AND (PS.COMPANY_CODE = #{company_code} OR PS.COMPANY_CODE = '*')
  </select>

  <!-- ================================================================
       syncApprovalStatusToTarget — dynamic table update
       ================================================================ -->

  <select id="selectRequestForSync" parameterType="map" resultType="map">
    SELECT TARGET_TABLE, TARGET_RECORD_ID, APPROVAL_TYPE
    FROM APPROVAL_REQUESTS
    WHERE REQUEST_ID = #{request_id}
    AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <select id="checkTargetTableHasApprovalStatusColumn" parameterType="map" resultType="int">
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = #{table_name}
    AND COLUMN_NAME = 'approval_status'
  </select>

  <!-- tableName은 Java에서 정규식으로 sanitize 후 전달 ([a-zA-Z0-9_] only) -->
  <update id="updateTargetTableApprovalStatus" parameterType="map">
    UPDATE ${tableName}
    SET APPROVAL_STATUS = #{approval_status}
    WHERE ID = #{target_record_id}
    <if test='company_code != null and company_code != "*"'>
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

</mapper>
