<?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.installment.InstallmentRatioMapper">

    <!-- ==================== ResultMap ==================== -->

    <!-- installment_ratio 테이블 1:1 매핑 (CUD용) -->
    <resultMap id="VOMap" type="com.ga.core.vo.installment.InstallmentRatioVO">
        <id  property="ratioId"          column="ratio_id"/>
        <result property="productCategory"   column="product_category"/>
        <result property="planYear"          column="plan_year"/>
        <result property="planMonth"         column="plan_month"/>
        <result property="ratioPercent"      column="ratio_percent"/>
        <result property="effectiveFrom"     column="effective_from"/>
        <result property="effectiveTo"       column="effective_to"/>
        <result property="isActive"          column="is_active"/>
        <result property="createdAt"         column="created_at"/>
        <result property="createdBy"         column="created_by"/>
        <result property="updatedAt"         column="updated_at"/>
        <result property="updatedBy"         column="updated_by"/>
    </resultMap>

    <!-- 코드명 포함 API 응답용 -->
    <resultMap id="RespMap" type="com.ga.core.vo.installment.InstallmentRatioResp">
        <id  property="ratioId"              column="ratio_id"/>
        <result property="productCategory"       column="product_category"/>
        <result property="productCategoryName"   column="product_category_name"/>
        <result property="planYear"              column="plan_year"/>
        <result property="planMonth"             column="plan_month"/>
        <result property="ratioPercent"          column="ratio_percent"/>
        <result property="effectiveFrom"         column="effective_from"/>
        <result property="effectiveTo"           column="effective_to"/>
        <result property="isActive"              column="is_active"/>
    </resultMap>

    <!-- ==================== 공통 SELECT 컬럼 ==================== -->

    <sql id="selectRespCols">
        r.ratio_id,
        r.product_category,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'PRODUCT_CATEGORY' AND cc.code = r.product_category) AS product_category_name,
        r.plan_year,
        r.plan_month,
        r.ratio_percent,
        r.effective_from,
        r.effective_to,
        r.is_active
    </sql>

    <!-- ==================== SELECT ==================== -->

    <!-- 목록 조회 (검색 조건 + 페이징) -->
    <select id="selectList" parameterType="com.ga.core.vo.installment.InstallmentRatioSearchParam"
            resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM installment_ratio r
        <where>
            <if test="productCategory != null and productCategory != ''">
                AND r.product_category = #{productCategory}
            </if>
            <if test="planYear != null">
                AND r.plan_year = #{planYear}
            </if>
            <if test="planMonth != null">
                AND r.plan_month = #{planMonth}
            </if>
            <if test="effectiveDate != null and effectiveDate != ''">
                AND r.effective_from &lt;= #{effectiveDate}::DATE
                AND (r.effective_to IS NULL OR r.effective_to >= #{effectiveDate}::DATE)
            </if>
        </where>
        ORDER BY r.plan_year, r.plan_month NULLS FIRST, r.effective_from DESC
    </select>

    <!-- 단건 조회 (Resp: 코드명 포함) -->
    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM installment_ratio r
        WHERE r.ratio_id = #{ratioId}
    </select>

    <!-- 단건 조회 (VO: CUD용) -->
    <select id="selectById" resultMap="VOMap">
        SELECT ratio_id, product_category, plan_year, plan_month, ratio_percent,
               effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM installment_ratio
        WHERE ratio_id = #{ratioId}
    </select>

    <!--
        특정 날짜에 유효한 분급 비율 1건 조회.
        product_category NULL은 전체 상품 적용 행이므로
        NULL 매개변수가 들어오면 IS NULL 조건으로 처리.
        effective_from DESC 정렬로 최신 개정본 우선 반환.
    -->
    <select id="selectActive" resultMap="VOMap">
        SELECT ratio_id, product_category, plan_year, plan_month, ratio_percent,
               effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM installment_ratio
        WHERE plan_year = #{planYear}
          AND (product_category = #{productCategory,jdbcType=VARCHAR}
               OR (#{productCategory,jdbcType=VARCHAR} IS NULL AND product_category IS NULL))
          AND is_active = TRUE
          AND effective_from &lt;= #{baseDate}::DATE
          AND (effective_to IS NULL OR effective_to >= #{baseDate}::DATE)
        ORDER BY effective_from DESC
        LIMIT 1
    </select>

    <!-- 1~7년 차 전체 비율 조회 (분급 계획 일괄 생성 시 사용) -->
    <select id="selectAllActiveRatios" resultMap="VOMap">
        SELECT ratio_id, product_category, plan_year, plan_month, ratio_percent,
               effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM installment_ratio
        WHERE (product_category = #{productCategory,jdbcType=VARCHAR}
               OR (#{productCategory,jdbcType=VARCHAR} IS NULL AND product_category IS NULL))
          AND is_active = TRUE
          AND effective_from &lt;= #{baseDate}::DATE
          AND (effective_to IS NULL OR effective_to >= #{baseDate}::DATE)
        ORDER BY plan_year, plan_month NULLS FIRST
    </select>

    <!-- ==================== INSERT ==================== -->

    <insert id="insert" parameterType="com.ga.core.vo.installment.InstallmentRatioVO"
            useGeneratedKeys="true" keyProperty="ratioId">
        INSERT INTO installment_ratio (
            product_category, plan_year, plan_month, ratio_percent,
            effective_from, effective_to, is_active,
            created_at, created_by
        ) VALUES (
            #{productCategory,jdbcType=VARCHAR}, #{planYear}, #{planMonth,jdbcType=INTEGER}, #{ratioPercent},
            #{effectiveFrom}, #{effectiveTo,jdbcType=DATE}, COALESCE(#{isActive}, TRUE),
            NOW(), #{createdBy,jdbcType=BIGINT}
        )
    </insert>

    <!-- ==================== UPDATE ==================== -->

    <update id="update" parameterType="com.ga.core.vo.installment.InstallmentRatioVO">
        UPDATE installment_ratio
        <set>
            <if test="ratioPercent != null">ratio_percent = #{ratioPercent},</if>
            <if test="effectiveFrom != null">effective_from = #{effectiveFrom},</if>
            <if test="effectiveTo != null">effective_to = #{effectiveTo},</if>
            <if test="isActive != null">is_active = #{isActive},</if>
            updated_at = NOW(),
            updated_by = #{updatedBy}
        </set>
        WHERE ratio_id = #{ratioId}
    </update>

</mapper>
