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

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

    <!-- 목록/단건 조회 컬럼 — 등급명 공통코드 서브쿼리 포함 -->
    <sql id="ruleJoinCols">
        r.rule_id,
        r.agent_grade,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'AGENT_GRADE' AND cc.code = r.agent_grade) AS agent_grade_name,
        r.support_rate,
        r.effective_from, r.effective_to
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="ruleJoinCols"/>
        FROM life_support_rule r
        WHERE r.rule_id = #{ruleId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ruleJoinCols"/>
        FROM life_support_rule r
        <where>
            <if test="agentGrade != null and agentGrade != ''">AND r.agent_grade = #{agentGrade}</if>
        </where>
        ORDER BY r.agent_grade, r.effective_from DESC
    </select>

    <!-- 유효 룰: effective_from &lt;= baseDate AND (effective_to IS NULL OR effective_to >= baseDate) -->
    <select id="selectActiveRule" resultMap="VOMap">
        SELECT r.rule_id, r.agent_grade, r.support_rate,
               r.effective_from, r.effective_to, r.created_at, r.created_by, r.updated_at
          FROM life_support_rule r
         WHERE r.agent_grade    = #{agentGrade}
           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 life_support_rule
            (agent_grade, support_rate, effective_from, effective_to, created_by)
        VALUES
            (#{agentGrade}, #{supportRate}, #{effectiveFrom}, #{effectiveTo}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE life_support_rule
           SET support_rate  = #{supportRate},
               effective_to  = #{effectiveTo},
               updated_at    = NOW()
         WHERE rule_id = #{ruleId}
    </update>

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

</mapper>
