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

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

    <sql id="joinCols">
        r.rule_id, r.grade_id, g.grade_name,
        r.retention_month,
        r.retention_rate_threshold,
        r.bonus_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'BONUS_TYPE' AND cc.code = r.bonus_type) AS bonus_type_name,
        r.bonus_value,
        r.effective_from, r.effective_to
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM persistency_bonus_rule r
        LEFT JOIN grade g ON g.grade_id = r.grade_id
        WHERE r.rule_id = #{ruleId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM persistency_bonus_rule r
        LEFT JOIN grade g ON g.grade_id = r.grade_id
        <where>
            <if test="gradeId != null">AND r.grade_id = #{gradeId}</if>
            <if test="retentionMonth != null">AND r.retention_month = #{retentionMonth}</if>
            <if test="bonusType != null and bonusType != ''">AND r.bonus_type = #{bonusType}</if>
        </where>
        ORDER BY r.retention_month, r.grade_id, r.effective_from DESC
    </select>

    <!--
        유효 룰 조회: gradeId NULL이면 전 직급 공통 룰(grade_id IS NULL) 우선,
        없으면 직급별 룰로 fallback. COALESCE 패턴 또는 두 번 조회로 처리.
        단순화: grade_id = #{gradeId} OR grade_id IS NULL, 직급 매칭 우선 ORDER BY
    -->
    <select id="selectActiveRule" resultMap="VOMap">
        SELECT r.rule_id, r.grade_id, r.retention_month,
               r.retention_rate_threshold, r.bonus_type, r.bonus_value,
               r.effective_from, r.effective_to, r.created_at, r.created_by, r.updated_at
        FROM persistency_bonus_rule r
        WHERE r.retention_month = #{retentionMonth}
          AND r.effective_from &lt;= #{baseDate}
          AND (r.effective_to IS NULL OR r.effective_to &gt;= #{baseDate})
          AND (r.grade_id = #{gradeId} OR r.grade_id IS NULL)
        ORDER BY r.grade_id DESC NULLS LAST  -- 직급 매칭 룰 우선
        LIMIT 1
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="ruleId">
        INSERT INTO persistency_bonus_rule
            (grade_id, retention_month, retention_rate_threshold, bonus_type, bonus_value,
             effective_from, effective_to, created_by)
        VALUES
            (#{gradeId}, #{retentionMonth}, #{retentionRateThreshold}, #{bonusType}, #{bonusValue},
             #{effectiveFrom}, #{effectiveTo}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE persistency_bonus_rule
           SET retention_rate_threshold = #{retentionRateThreshold},
               bonus_type               = #{bonusType},
               bonus_value              = #{bonusValue},
               effective_to             = #{effectiveTo},
               updated_at               = NOW()
         WHERE rule_id = #{ruleId}
    </update>

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

</mapper>
