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

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

    <!-- 목록/단건 조회 컬럼 — 시상유형·계산방식 공통코드명 서브쿼리 포함 -->
    <sql id="ruleJoinCols">
        r.rule_id,
        r.award_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'AWARD_TYPE' AND cc.code = r.award_type) AS award_type_name,
        r.calc_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'CALC_TYPE' AND cc.code = r.calc_type) AS calc_type_name,
        r.threshold_amount, r.award_rate, r.award_fixed,
        r.effective_from, r.effective_to
    </sql>

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

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ruleJoinCols"/>
        FROM award_rule r
        <where>
            <if test="awardType != null and awardType != ''">AND r.award_type = #{awardType}</if>
            <if test="calcType != null and calcType != ''">AND r.calc_type = #{calcType}</if>
        </where>
        ORDER BY r.award_type, 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.award_type, r.calc_type, r.threshold_amount,
               r.award_rate, r.award_fixed,
               r.effective_from, r.effective_to, r.created_at, r.created_by, r.updated_at
          FROM award_rule r
         WHERE r.award_type     = #{awardType}
           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 award_rule
            (award_type, calc_type, threshold_amount, award_rate, award_fixed,
             effective_from, effective_to, created_by)
        VALUES
            (#{awardType}, #{calcType}, #{thresholdAmount}, #{awardRate}, #{awardFixed},
             #{effectiveFrom}, #{effectiveTo}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE award_rule
           SET calc_type        = #{calcType},
               threshold_amount = #{thresholdAmount},
               award_rate       = #{awardRate},
               award_fixed      = #{awardFixed},
               effective_to     = #{effectiveTo},
               updated_at       = NOW()
         WHERE rule_id = #{ruleId}
    </update>

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

</mapper>
