<?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.commission.AgentReserveFundMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.commission.AgentReserveFundVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.commission.AgentReserveFundResp"/>

    <!--
        공통 SELECT 컬럼 (Resp용 — 설계사명/조직명 조인 포함).
    -->
    <sql id="fundRespCols">
        rf.reserve_id,
        rf.agent_id,
        a.agent_name,
        o.org_name,
        rf.settle_month,
        rf.reserve_in,
        rf.reserve_out,
        rf.balance,
        rf.created_at
    </sql>

    <!--
        VO용 컬럼 (내부 처리 전용, 조인 없음).
    -->
    <sql id="fundVOCols">
        rf.reserve_id,
        rf.agent_id,
        rf.settle_month,
        rf.reserve_in,
        rf.reserve_out,
        rf.balance,
        rf.created_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="fundRespCols"/>
        FROM agent_reserve_fund rf
        LEFT JOIN agent        a ON a.agent_id = rf.agent_id
        LEFT JOIN organization o ON o.org_id   = a.org_id
        WHERE rf.reserve_id = #{reserveId}
    </select>

    <!-- 목록: agentId, agentName, settleMonth 필터 -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="fundRespCols"/>
        FROM agent_reserve_fund rf
        LEFT JOIN agent        a ON a.agent_id = rf.agent_id
        LEFT JOIN organization o ON o.org_id   = a.org_id
        <where>
            <if test="agentId != null">AND rf.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="settleMonth != null and settleMonth != ''">AND rf.settle_month = #{settleMonth}</if>
        </where>
        ORDER BY rf.settle_month DESC, rf.agent_id
    </select>

    <!--
        설계사 + 정산월 단건 조회 (VO 매핑).
        중복 체크 및 해당 월 잔액 확인에 사용.
    -->
    <select id="selectByAgentAndMonth" resultMap="VOMap">
        SELECT <include refid="fundVOCols"/>
        FROM agent_reserve_fund rf
        WHERE rf.agent_id    = #{agentId}
          AND rf.settle_month = #{settleMonth}
    </select>

    <!--
        직전월 잔액 조회 (balance 누적 계산용).
        settleMonth 미만인 데이터 중 settle_month 내림차순 1건.
        Service는 이 결과의 balance 를 전월잔액으로 사용.
    -->
    <select id="selectLatestBefore" resultMap="VOMap">
        SELECT <include refid="fundVOCols"/>
        FROM agent_reserve_fund rf
        WHERE rf.agent_id    = #{agentId}
          AND rf.settle_month &lt; #{settleMonth}
        ORDER BY rf.settle_month DESC
        LIMIT 1
    </select>

    <!--
        INSERT: balance 는 Service가 계산 후 VO에 세팅하여 전달.
        reserve_in/reserve_out 기본값 0 은 DB DEFAULT이므로 NULL 전달 가능하나
        NotNull 검증으로 항상 값이 들어옴.
    -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="reserveId">
        INSERT INTO agent_reserve_fund
            (agent_id, settle_month, reserve_in, reserve_out, balance)
        VALUES
            (#{agentId}, #{settleMonth}, #{reserveIn}, #{reserveOut}, #{balance})
    </insert>

    <update id="update">
        UPDATE agent_reserve_fund
           SET reserve_in  = #{reserveIn},
               reserve_out = #{reserveOut},
               balance     = #{balance}
         WHERE reserve_id = #{reserveId}
    </update>

    <delete id="delete">
        DELETE FROM agent_reserve_fund WHERE reserve_id = #{reserveId}
    </delete>

</mapper>
