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

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

    <!--
        공통 SELECT 컬럼: grade 테이블 조인으로 grade_name 포함.
    -->
    <sql id="ruleCols">
        r.rule_id,
        r.grade_id,
        g.grade_name,
        r.min_score,
        r.effective_from,
        r.effective_to,
        r.created_at
    </sql>

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

    <!-- 목록: gradeId, baseDate(유효 기간 필터) -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ruleCols"/>
        FROM grade_eval_rule r
        LEFT JOIN grade g ON g.grade_id = r.grade_id
        <where>
            <if test="gradeId != null">AND r.grade_id = #{gradeId}</if>
            <!--
                baseDate 가 있으면 해당 일자가 effective_from~effective_to 범위 안인 룰만.
                typed-null 주의: effective_to IS NULL 분기는 <choose> 불필요(조건식이 상수).
            -->
            <if test="baseDate != null and baseDate != ''">
                AND r.effective_from &lt;= #{baseDate}::DATE
                AND (r.effective_to IS NULL OR r.effective_to &gt;= #{baseDate}::DATE)
            </if>
        </where>
        ORDER BY r.min_score DESC, r.effective_from DESC
    </select>

    <!--
        등급 산정 전용 쿼리: score 이상인 최대 min_score 룰 1건.
        기준일(baseDate) 기준 유효한 룰 중에서 min_score &lt;= score 인 것 중 min_score 최대값.
        신입 참고: "score 이상인 최대 min_score" = 점수 구간의 가장 높은 bracket 이 설계사 등급.
    -->
    <select id="selectActiveRuleByScore" resultMap="VOMap">
        SELECT r.rule_id, r.grade_id, r.min_score, r.effective_from, r.effective_to, r.created_at
        FROM grade_eval_rule r
        WHERE r.min_score      &lt;= #{score}
          AND r.effective_from &lt;= #{baseDate}
          AND (r.effective_to IS NULL OR r.effective_to &gt;= #{baseDate})
        ORDER BY r.min_score DESC
        LIMIT 1
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="ruleId">
        INSERT INTO grade_eval_rule
            (grade_id, min_score, effective_from, effective_to)
        VALUES
            (#{gradeId}, #{minScore}, #{effectiveFrom}, #{effectiveTo})
    </insert>

    <update id="update">
        UPDATE grade_eval_rule
           SET grade_id       = #{gradeId},
               min_score      = #{minScore},
               effective_from = #{effectiveFrom},
               effective_to   = #{effectiveTo}
         WHERE rule_id = #{ruleId}
    </update>

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

</mapper>
