<?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.chargeback.ChargebackGradeMapper">

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

    <!-- chargeback_grade 테이블 1:1 매핑 (CUD용) -->
    <resultMap id="VOMap" type="com.ga.core.vo.chargeback.ChargebackGradeVO">
        <id  property="gradeId"           column="grade_id"/>
        <result property="productCategory"    column="product_category"/>
        <result property="monthsFrom"         column="months_from"/>
        <result property="monthsTo"           column="months_to"/>
        <result property="chargebackPercent"  column="chargeback_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>

    <!-- 코드명 + monthsRange 포함 API 응답용 -->
    <resultMap id="RespMap" type="com.ga.core.vo.chargeback.ChargebackGradeResp">
        <id  property="gradeId"               column="grade_id"/>
        <result property="productCategory"        column="product_category"/>
        <result property="productCategoryName"    column="product_category_name"/>
        <result property="monthsFrom"             column="months_from"/>
        <result property="monthsTo"               column="months_to"/>
        <result property="monthsRange"            column="months_range"/>
        <result property="chargebackPercent"      column="chargeback_percent"/>
        <result property="effectiveFrom"          column="effective_from"/>
        <result property="effectiveTo"            column="effective_to"/>
        <result property="isActive"               column="is_active"/>
    </resultMap>

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

    <sql id="selectRespCols">
        g.grade_id,
        g.product_category,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'PRODUCT_CATEGORY' AND cc.code = g.product_category) AS product_category_name,
        g.months_from,
        g.months_to,
        CASE
            WHEN g.months_to IS NULL THEN g.months_from || '개월 이상'
            ELSE g.months_from || '~' || g.months_to || '개월'
        END AS months_range,
        g.chargeback_percent,
        g.effective_from,
        g.effective_to,
        g.is_active
    </sql>

    <!-- 활성 유효기간 공통 조건 -->
    <sql id="activeCondition">
        AND g.is_active = TRUE
        AND g.effective_from &lt;= #{baseDate}::DATE
        AND (g.effective_to IS NULL OR g.effective_to >= #{baseDate}::DATE)
    </sql>

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

    <!-- 목록 조회 (검색 조건 + 페이징) -->
    <select id="selectList" parameterType="com.ga.core.vo.chargeback.ChargebackGradeSearchParam"
            resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM chargeback_grade g
        <where>
            <if test="productCategory != null and productCategory != ''">
                AND g.product_category = #{productCategory}
            </if>
            <if test="monthsElapsed != null">
                AND g.months_from &lt;= #{monthsElapsed}
                AND (g.months_to IS NULL OR g.months_to >= #{monthsElapsed})
            </if>
            <if test="effectiveDate != null and effectiveDate != ''">
                AND g.effective_from &lt;= #{effectiveDate}::DATE
                AND (g.effective_to IS NULL OR g.effective_to >= #{effectiveDate}::DATE)
            </if>
        </where>
        ORDER BY g.months_from, g.effective_from DESC
    </select>

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

    <!-- 단건 조회 (VO: CUD용) -->
    <select id="selectById" resultMap="VOMap">
        SELECT grade_id, product_category, months_from, months_to,
               chargeback_percent, effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM chargeback_grade
        WHERE grade_id = #{gradeId}
    </select>

    <!--
        해약 경과월에 적용되는 환수 비율 구간 1건 조회.
        months_from <= monthsElapsed AND (months_to IS NULL OR months_to >= monthsElapsed)
        product_category NULL은 전체 상품 적용 행이므로 NULL 안전 비교 처리.
        effective_from DESC로 최신 개정본 우선 반환.
    -->
    <select id="selectByMonths" resultMap="VOMap">
        SELECT grade_id, product_category, months_from, months_to,
               chargeback_percent, effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM chargeback_grade g
        WHERE g.months_from &lt;= #{monthsElapsed}
          AND (g.months_to IS NULL OR g.months_to >= #{monthsElapsed})
          AND (g.product_category = #{productCategory,jdbcType=VARCHAR}
               OR (#{productCategory,jdbcType=VARCHAR} IS NULL AND g.product_category IS NULL))
          <include refid="activeCondition"/>
        ORDER BY g.effective_from DESC
        LIMIT 1
    </select>

    <!-- 특정 시점 활성 전체 구간 (months_from ASC 정렬) -->
    <select id="selectAll" resultMap="VOMap">
        SELECT grade_id, product_category, months_from, months_to,
               chargeback_percent, effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM chargeback_grade g
        WHERE 1=1
          <include refid="activeCondition"/>
        ORDER BY g.product_category NULLS FIRST, g.months_from
    </select>

    <!-- 카테고리별 활성 구간 (months_from ASC) -->
    <select id="selectByCategory" resultMap="VOMap">
        SELECT grade_id, product_category, months_from, months_to,
               chargeback_percent, effective_from, effective_to, is_active,
               created_at, created_by, updated_at, updated_by
        FROM chargeback_grade g
        WHERE (g.product_category = #{productCategory,jdbcType=VARCHAR}
               OR (#{productCategory,jdbcType=VARCHAR} IS NULL AND g.product_category IS NULL))
          <include refid="activeCondition"/>
        ORDER BY g.months_from
    </select>

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

    <insert id="insert" parameterType="com.ga.core.vo.chargeback.ChargebackGradeVO"
            useGeneratedKeys="true" keyProperty="gradeId">
        INSERT INTO chargeback_grade (
            product_category, months_from, months_to,
            chargeback_percent, effective_from, effective_to,
            is_active, created_at, created_by
        ) VALUES (
            #{productCategory}, #{monthsFrom}, #{monthsTo},
            #{chargebackPercent}, #{effectiveFrom}, #{effectiveTo},
            COALESCE(#{isActive}, TRUE), NOW(), #{createdBy}
        )
    </insert>

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

    <update id="update" parameterType="com.ga.core.vo.chargeback.ChargebackGradeVO">
        UPDATE chargeback_grade
        <set>
            <if test="monthsFrom != null">months_from = #{monthsFrom},</if>
            <if test="monthsTo != null">months_to = #{monthsTo},</if>
            <if test="chargebackPercent != null">chargeback_percent = #{chargebackPercent},</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 grade_id = #{gradeId}
    </update>

    <!-- 활성 여부만 변경 -->
    <update id="updateStatus">
        UPDATE chargeback_grade
        SET is_active = #{isActive}, updated_at = NOW()
        WHERE grade_id = #{gradeId}
    </update>

</mapper>
