<?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="com.ga.core.mapper.withdraw.WithdrawRequestMapper">

    <!--
        VO ResultMap: account_no 는 EncryptTypeHandler 로 복호화.
        펌뱅킹 송신 배치에서 selectVOById 로 복호화된 계좌번호 획득.
    -->
    <resultMap id="VOMap" type="com.ga.core.vo.withdraw.WithdrawRequestVO">
        <id  property="requestId"         column="request_id"/>
        <result property="settleMasterId"     column="settle_master_id"/>
        <result property="agentId"            column="agent_id"/>
        <result property="amount"             column="amount"/>
        <result property="requestedBy"        column="requested_by"/>
        <result property="requestedAt"        column="requested_at"/>
        <result property="approvalRequestId"  column="approval_request_id"/>
        <result property="bankCode"           column="bank_code"/>
        <result property="accountNo"          column="account_no"
                typeHandler="com.ga.common.mybatis.EncryptTypeHandler"/>
        <result property="status"             column="status"/>
        <result property="sentAt"             column="sent_at"/>
        <result property="completedAt"        column="completed_at"/>
        <result property="failReason"         column="fail_reason"/>
        <result property="transferTxId"       column="transfer_tx_id"/>
        <result property="transferStatus"     column="transfer_status"/>
        <result property="transferAt"         column="transfer_at"/>
        <result property="createdAt"          column="created_at"/>
        <result property="updatedAt"          column="updated_at"/>
    </resultMap>

    <!-- 응답 ResultMap: account_no 마스킹은 Service 또는 화면 처리 -->
    <resultMap id="RespMap" type="com.ga.core.vo.withdraw.WithdrawRequestResp">
        <id  property="requestId"        column="request_id"/>
        <result property="settleMasterId"    column="settle_master_id"/>
        <result property="settleMonth"       column="settle_month"/>
        <result property="agentId"           column="agent_id"/>
        <result property="agentName"         column="agent_name"/>
        <result property="amount"            column="amount"/>
        <result property="requestedBy"       column="requested_by"/>
        <result property="requestedAt"       column="requested_at"/>
        <result property="approvalRequestId" column="approval_request_id"/>
        <result property="bankCode"          column="bank_code"/>
        <!-- account_no: 복호화 후 Service에서 마스킹 처리 권장 -->
        <result property="accountNo"         column="account_no"
                typeHandler="com.ga.common.mybatis.EncryptTypeHandler"/>
        <result property="status"            column="status"/>
        <result property="statusName"        column="status_name"/>
        <result property="sentAt"            column="sent_at"/>
        <result property="completedAt"       column="completed_at"/>
        <result property="failReason"        column="fail_reason"/>
        <result property="transferTxId"      column="transfer_tx_id"/>
        <result property="transferStatus"    column="transfer_status"/>
        <result property="transferAt"        column="transfer_at"/>
    </resultMap>

    <sql id="joinCols">
        wr.request_id, wr.settle_master_id,
        sm.settle_month,
        wr.agent_id, a.agent_name,
        wr.amount, wr.requested_by, wr.requested_at,
        wr.approval_request_id, wr.bank_code, wr.account_no,
        wr.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'WITHDRAW_STATUS' AND cc.code = wr.status) AS status_name,
        wr.sent_at, wr.completed_at, wr.fail_reason,
        wr.transfer_tx_id, wr.transfer_status, wr.transfer_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM withdraw_request wr
        LEFT JOIN settle_master sm ON sm.settle_id = wr.settle_master_id
        LEFT JOIN agent a          ON a.agent_id = wr.agent_id
        WHERE wr.request_id = #{requestId}
    </select>

    <!-- account_no 복호화 포함 VO 조회 — 펌뱅킹 배치 전용 -->
    <select id="selectVOById" resultMap="VOMap">
        SELECT wr.request_id, wr.settle_master_id, wr.agent_id, wr.amount,
               wr.requested_by, wr.requested_at, wr.approval_request_id,
               wr.bank_code, wr.account_no, wr.status,
               wr.sent_at, wr.completed_at, wr.fail_reason,
               wr.transfer_tx_id, wr.transfer_status, wr.transfer_at,
               wr.created_at, wr.updated_at
          FROM withdraw_request wr
         WHERE wr.request_id = #{requestId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM withdraw_request wr
        LEFT JOIN settle_master sm ON sm.settle_id = wr.settle_master_id
        LEFT JOIN agent a          ON a.agent_id = wr.agent_id
        <where>
            <if test="agentId != null">AND wr.agent_id = #{agentId}</if>
            <if test="settleMasterId != null">AND wr.settle_master_id = #{settleMasterId}</if>
            <if test="status != null and status != ''">AND wr.status = #{status}</if>
        </where>
        ORDER BY wr.requested_at DESC, wr.request_id DESC
    </select>

    <!--
        INSERT: account_no 는 EncryptTypeHandler 가 INSERT 시 암호화.
        @TypeHandler 선언이 있으므로 MyBatis가 자동으로 AES 암호화 처리.
    -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="requestId">
        INSERT INTO withdraw_request
            (settle_master_id, agent_id, amount, requested_by, requested_at,
             approval_request_id, bank_code, account_no, status)
        VALUES
            (#{settleMasterId}, #{agentId}, #{amount}, #{requestedBy},
             COALESCE(#{requestedAt}, NOW()),
             #{approvalRequestId}, #{bankCode},
             #{accountNo, typeHandler=com.ga.common.mybatis.EncryptTypeHandler},
             COALESCE(#{status}, 'REQUESTED'))
    </insert>

    <update id="updateStatus">
        UPDATE withdraw_request
           SET status      = #{status},
               fail_reason = #{failReason},
               updated_at  = NOW()
         WHERE request_id = #{requestId}
    </update>

    <!-- 펌뱅킹 송신 완료 -->
    <update id="markSent">
        UPDATE withdraw_request
           SET status = 'SENT', sent_at = NOW(), updated_at = NOW()
         WHERE request_id = #{requestId}
           AND status = 'APPROVED'
    </update>

    <!-- 입금 확인 완료 -->
    <update id="markCompleted">
        UPDATE withdraw_request
           SET status = 'COMPLETED', completed_at = NOW(), updated_at = NOW()
         WHERE request_id = #{requestId}
           AND status = 'SENT'
    </update>

    <!-- 취소 — REQUESTED/APPROVED 상태에서만 가능 -->
    <update id="cancel">
        UPDATE withdraw_request
           SET status = 'CANCELLED', updated_at = NOW()
         WHERE request_id = #{requestId}
           AND status IN ('REQUESTED', 'APPROVED')
    </update>

    <!-- 펌뱅킹 재시도 대상 — 어댑터 실패로 transfer_status='FAILED' 상태인 출금 -->
    <select id="selectFailedTransfers" resultMap="VOMap">
        SELECT wr.request_id, wr.settle_master_id, wr.agent_id, wr.amount,
               wr.requested_by, wr.requested_at, wr.approval_request_id,
               wr.bank_code, wr.account_no, wr.status,
               wr.sent_at, wr.completed_at, wr.fail_reason,
               wr.transfer_tx_id, wr.transfer_status, wr.transfer_at,
               wr.created_at, wr.updated_at
          FROM withdraw_request wr
         WHERE wr.transfer_status = 'FAILED'
           AND wr.status = 'APPROVED'
         ORDER BY wr.transfer_at NULLS FIRST, wr.request_id
         LIMIT #{limit}
    </select>

    <!-- 펌뱅킹 어댑터 응답 저장 -->
    <update id="updateTransferResult">
        UPDATE withdraw_request
           SET transfer_tx_id  = #{txId,jdbcType=VARCHAR},
               transfer_status = #{transferStatus,jdbcType=VARCHAR},
               transfer_at     = NOW(),
               status          = #{nextStatus},
               fail_reason     = #{failReason,jdbcType=VARCHAR},
               <if test="nextStatus == 'SENT'">sent_at = NOW(),</if>
               updated_at      = NOW()
         WHERE request_id = #{requestId}
    </update>

    <!-- SETTLED 동기화 대상 — 펌뱅킹 어댑터로 보냈고(SENT) 외부 응답이 접수만(ACCEPTED) 된 건 -->
    <select id="selectAcceptedTransfers" resultMap="VOMap">
        SELECT wr.request_id, wr.settle_master_id, wr.agent_id, wr.amount,
               wr.requested_by, wr.requested_at, wr.approval_request_id,
               wr.bank_code, wr.account_no, wr.status,
               wr.sent_at, wr.completed_at, wr.fail_reason,
               wr.transfer_tx_id, wr.transfer_status, wr.transfer_at,
               wr.created_at, wr.updated_at
          FROM withdraw_request wr
         WHERE wr.transfer_status = 'ACCEPTED'
           AND wr.status = 'SENT'
           AND wr.transfer_tx_id IS NOT NULL
         ORDER BY wr.transfer_at NULLS FIRST, wr.request_id
         LIMIT #{limit}
    </select>

    <!-- 입금 확정 — SETTLED -->
    <update id="markSettled">
        UPDATE withdraw_request
           SET transfer_status = 'SETTLED',
               status          = 'COMPLETED',
               completed_at    = NOW(),
               fail_reason     = NULL,
               updated_at      = NOW()
         WHERE request_id = #{requestId}
           AND status = 'SENT'
    </update>

    <!-- 입금 실패 동기화 — 재시도 풀(APPROVED + FAILED) 로 회귀 -->
    <update id="markSettleFailed">
        UPDATE withdraw_request
           SET transfer_status = 'FAILED',
               status          = 'APPROVED',
               fail_reason     = #{failReason,jdbcType=VARCHAR},
               updated_at      = NOW()
         WHERE request_id = #{requestId}
           AND status = 'SENT'
    </update>

    <!-- 잔여 한도 검증용: 특정 settle_master 의 활성(CANCELLED 외) 출금 amount 합계 -->
    <select id="sumActiveAmountByMaster" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0)
          FROM withdraw_request
         WHERE settle_master_id = #{settleMasterId}
           AND status != 'CANCELLED'
    </select>

</mapper>
