<?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.RenewalCommissionRuleMapper">

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

    <sql id="joinCols">
        r.rule_id, r.product_id, p.product_name,
        r.pay_type, r.rate_value, r.fixed_amount,
        r.effective_from, r.effective_to
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM renewal_commission_rule r
        LEFT JOIN product p ON p.product_id = r.product_id
        WHERE r.rule_id = #{ruleId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM renewal_commission_rule r
        LEFT JOIN product p ON p.product_id = r.product_id
        <where>
            <if test="productId != null">AND r.product_id = #{productId}</if>
            <if test="payType != null and payType != ''">AND r.pay_type = #{payType}</if>
        </where>
        ORDER BY r.product_id, r.effective_from DESC
    </select>

    <!-- 기준일 기준 유효 룰 (최신 1건) -->
    <select id="selectActiveRule" resultMap="VOMap">
        SELECT r.rule_id, r.product_id, r.pay_type, r.rate_value, r.fixed_amount,
               r.effective_from, r.effective_to, r.created_at, r.created_by, r.updated_at
        FROM renewal_commission_rule r
        WHERE r.product_id = #{productId}
          AND r.effective_from &lt;= #{baseDate}
          AND (r.effective_to IS NULL OR r.effective_to &gt;= #{baseDate})
        ORDER BY r.effective_from DESC
        LIMIT 1
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="ruleId">
        INSERT INTO renewal_commission_rule
            (product_id, pay_type, rate_value, fixed_amount, effective_from, effective_to, created_by)
        VALUES
            (#{productId}, #{payType}, #{rateValue}, #{fixedAmount}, #{effectiveFrom}, #{effectiveTo}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE renewal_commission_rule
           SET pay_type      = #{payType},
               rate_value    = #{rateValue},
               fixed_amount  = #{fixedAmount},
               effective_to  = #{effectiveTo},
               updated_at    = NOW()
         WHERE rule_id = #{ruleId}
    </update>

    <delete id="delete">
        DELETE FROM renewal_commission_rule WHERE rule_id = #{ruleId}
    </delete>

</mapper>
