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

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

    <!-- 목록/단건 조회 컬럼 — 상품명 조인 포함 -->
    <sql id="rateJoinCols">
        r.rate_id, r.product_id, p.product_name,
        r.conversion_rate,
        r.effective_from, r.effective_to
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="rateJoinCols"/>
        FROM product_conversion_rate r
        LEFT JOIN product p ON p.product_id = r.product_id
        WHERE r.rate_id = #{rateId}
    </select>

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

    <!-- 유효 환산율: effective_from &lt;= baseDate AND (effective_to IS NULL OR effective_to >= baseDate) -->
    <select id="selectActiveRate" resultMap="VOMap">
        SELECT r.rate_id, r.product_id, r.conversion_rate,
               r.effective_from, r.effective_to, r.created_at, r.created_by, r.updated_at
          FROM product_conversion_rate 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="rateId">
        INSERT INTO product_conversion_rate
            (product_id, conversion_rate, effective_from, effective_to, created_by)
        VALUES
            (#{productId}, #{conversionRate}, #{effectiveFrom}, #{effectiveTo}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE product_conversion_rate
           SET conversion_rate = #{conversionRate},
               effective_to    = #{effectiveTo},
               updated_at      = NOW()
         WHERE rate_id = #{rateId}
    </update>

    <delete id="delete">
        DELETE FROM product_conversion_rate WHERE rate_id = #{rateId}
    </delete>

</mapper>
