<?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.regulatory.RegulatoryLimitMapper">

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

    <!-- regulatory_limit 테이블 1:1 매핑 (CUD용) -->
    <resultMap id="VOMap" type="com.ga.core.vo.regulatory.RegulatoryLimitVO">
        <id  property="limitId"          column="limit_id"/>
        <result property="limitType"         column="limit_type"/>
        <result property="productCategory"   column="product_category"/>
        <result property="limitValue"        column="limit_value"/>
        <result property="unit"              column="unit"/>
        <result property="effectiveFrom"     column="effective_from"/>
        <result property="effectiveTo"       column="effective_to"/>
        <result property="regulationRef"     column="regulation_ref"/>
        <result property="description"       column="description"/>
        <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.regulatory.RegulatoryLimitResp">
        <id  property="limitId"              column="limit_id"/>
        <result property="limitType"             column="limit_type"/>
        <result property="limitTypeName"         column="limit_type_name"/>
        <result property="productCategory"       column="product_category"/>
        <result property="productCategoryName"   column="product_category_name"/>
        <result property="limitValue"            column="limit_value"/>
        <result property="unit"                  column="unit"/>
        <result property="unitName"              column="unit_name"/>
        <result property="effectiveFrom"         column="effective_from"/>
        <result property="effectiveTo"           column="effective_to"/>
        <result property="regulationRef"         column="regulation_ref"/>
        <result property="description"           column="description"/>
        <result property="isActive"              column="is_active"/>
    </resultMap>

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

    <sql id="selectRespCols">
        r.limit_id,
        r.limit_type,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'LIMIT_TYPE' AND cc.code = r.limit_type) AS limit_type_name,
        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.limit_value,
        r.unit,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'LIMIT_UNIT' AND cc.code = r.unit) AS unit_name,
        r.effective_from,
        r.effective_to,
        r.regulation_ref,
        r.description,
        r.is_active
    </sql>

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

    <!-- 목록 조회 (검색 조건 + 페이징) -->
    <select id="selectList" parameterType="com.ga.core.vo.regulatory.RegulatoryLimitSearchParam"
            resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM regulatory_limit r
        <where>
            <if test="limitType != null and limitType != ''">
                AND r.limit_type = #{limitType}
            </if>
            <if test="productCategory != null and productCategory != ''">
                AND r.product_category = #{productCategory}
            </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.limit_type, r.effective_from DESC
    </select>

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

    <!-- 단건 조회 (VO: CUD용) -->
    <select id="selectById" resultMap="VOMap">
        SELECT limit_id, limit_type, product_category, limit_value, unit,
               effective_from, effective_to, regulation_ref, description, is_active,
               created_at, created_by, updated_at, updated_by
        FROM regulatory_limit
        WHERE limit_id = #{limitId}
    </select>

    <!--
        특정 날짜에 유효한 한도 1건 조회.
        product_category가 NULL인 행은 전체 상품 적용(ALL)이므로
        COALESCE로 NULL을 'ALL'과 동일하게 매핑하여 조회한다.
        effective_from DESC 정렬로 가장 최근 개정본을 우선 반환.
    -->
    <select id="selectActiveByType" resultMap="VOMap">
        SELECT limit_id, limit_type, product_category, limit_value, unit,
               effective_from, effective_to, regulation_ref, description, is_active,
               created_at, created_by, updated_at, updated_by
        FROM regulatory_limit
        WHERE limit_type = #{limitType}
          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>

    <!-- 특정 날짜에 유효한 모든 활성 한도 조회 (배치 검증/1200%룰 로드용) -->
    <select id="selectAllActive" resultMap="VOMap">
        SELECT limit_id, limit_type, product_category, limit_value, unit,
               effective_from, effective_to, regulation_ref, description, is_active,
               created_at, created_by, updated_at, updated_by
        FROM regulatory_limit
        WHERE is_active = TRUE
          AND effective_from &lt;= #{baseDate}::DATE
          AND (effective_to IS NULL OR effective_to >= #{baseDate}::DATE)
        ORDER BY limit_type, effective_from DESC
    </select>

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

    <insert id="insert" parameterType="com.ga.core.vo.regulatory.RegulatoryLimitVO"
            useGeneratedKeys="true" keyProperty="limitId">
        INSERT INTO regulatory_limit (
            limit_type, product_category, limit_value, unit,
            effective_from, effective_to, regulation_ref, description,
            is_active, created_at, created_by
        ) VALUES (
            #{limitType}, #{productCategory}, #{limitValue}, #{unit},
            #{effectiveFrom}, #{effectiveTo}, #{regulationRef}, #{description},
            COALESCE(#{isActive}, TRUE), NOW(), #{createdBy}
        )
    </insert>

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

    <update id="update" parameterType="com.ga.core.vo.regulatory.RegulatoryLimitVO">
        UPDATE regulatory_limit
        <set>
            <if test="limitValue != null">limit_value = #{limitValue},</if>
            <if test="unit != null and unit != ''">unit = #{unit},</if>
            <if test="effectiveFrom != null">effective_from = #{effectiveFrom},</if>
            <if test="effectiveTo != null">effective_to = #{effectiveTo},</if>
            <if test="regulationRef != null">regulation_ref = #{regulationRef},</if>
            <if test="description != null">description = #{description},</if>
            <if test="isActive != null">is_active = #{isActive},</if>
            updated_at = NOW(),
            updated_by = #{updatedBy}
        </set>
        WHERE limit_id = #{limitId}
    </update>

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

</mapper>
