<?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.rule.RuleMapper">

    <resultMap id="CommRateMap" type="com.ga.core.vo.rule.CommissionRateVO"/>
    <resultMap id="PayoutMap"   type="com.ga.core.vo.rule.PayoutRuleVO"/>
    <resultMap id="OverrideMap" type="com.ga.core.vo.rule.OverrideRuleVO"/>
    <resultMap id="ChargebackMap" type="com.ga.core.vo.rule.ChargebackRuleVO"/>
    <resultMap id="ExceptionMap" type="com.ga.core.vo.rule.ExceptionTypeCodeVO"/>

    <!-- ============ commission_rate ============ -->
    <select id="selectCommissionRates" resultMap="CommRateMap">
        SELECT * FROM commission_rate
        <where>
            <if test="productId != null">AND product_id = #{productId}</if>
            <if test="year != null">AND commission_year = #{year}</if>
        </where>
        ORDER BY product_id, commission_year, effective_from DESC, version DESC
    </select>

    <select id="findCompanyRate" resultType="java.math.BigDecimal">
        SELECT rate_pct FROM commission_rate
        WHERE product_id = #{productId} AND commission_year = #{year}
          AND effective_from &lt;= #{baseDate}
          AND (effective_to IS NULL OR effective_to &gt;= #{baseDate})
          AND (sales_channel = #{salesChannel} OR sales_channel IS NULL)
        ORDER BY (sales_channel IS NULL) ASC, effective_from DESC, version DESC
        LIMIT 1
    </select>

    <insert id="insertCommissionRate" parameterType="com.ga.core.vo.rule.CommissionRateVO"
            useGeneratedKeys="true" keyProperty="rateId">
        INSERT INTO commission_rate (product_id, commission_year, rate_pct, pay_method_cond,
                                     min_premium, effective_from, effective_to, version,
                                     created_at, created_by)
        VALUES (#{productId}, #{commissionYear}, #{ratePct}, #{payMethodCond},
                #{minPremium}, #{effectiveFrom}, #{effectiveTo}, COALESCE(#{version},1),
                #{createdAt}, #{createdBy})
    </insert>

    <update id="updateCommissionRate" parameterType="com.ga.core.vo.rule.CommissionRateVO">
        UPDATE commission_rate SET
            rate_pct = #{ratePct}, pay_method_cond = #{payMethodCond},
            min_premium = #{minPremium}, effective_from = #{effectiveFrom},
            effective_to = #{effectiveTo}, version = #{version}
        WHERE rate_id = #{rateId}
    </update>

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

    <!-- ============ payout_rule ============ -->
    <select id="selectPayoutRules" resultMap="PayoutMap">
        SELECT * FROM payout_rule
        <where>
            <if test="gradeId != null">AND grade_id = #{gradeId}</if>
            <if test="insuranceType != null and insuranceType != ''">AND insurance_type = #{insuranceType}</if>
        </where>
        ORDER BY grade_id, insurance_type, effective_from DESC
    </select>

    <select id="findPayoutRate" resultType="java.math.BigDecimal">
        SELECT payout_pct FROM payout_rule
        WHERE grade_id = #{gradeId}
          AND insurance_type = #{insuranceType}
          AND commission_year = #{year}
          AND effective_from &lt;= #{baseDate}
          AND (effective_to IS NULL OR effective_to &gt;= #{baseDate})
          AND (sales_channel = #{salesChannel} OR sales_channel IS NULL)
        ORDER BY (sales_channel IS NULL) ASC, effective_from DESC, version DESC
        LIMIT 1
    </select>

    <insert id="insertPayoutRule" parameterType="com.ga.core.vo.rule.PayoutRuleVO"
            useGeneratedKeys="true" keyProperty="ruleId">
        INSERT INTO payout_rule (grade_id, insurance_type, commission_year, payout_pct,
                                 pay_method_cond, performance_grade, effective_from, effective_to,
                                 version, created_at, created_by)
        VALUES (#{gradeId}, #{insuranceType}, #{commissionYear}, #{payoutPct},
                #{payMethodCond}, #{performanceGrade}, #{effectiveFrom}, #{effectiveTo},
                COALESCE(#{version},1), #{createdAt}, #{createdBy})
    </insert>

    <update id="updatePayoutRule" parameterType="com.ga.core.vo.rule.PayoutRuleVO">
        UPDATE payout_rule SET
            payout_pct = #{payoutPct},
            pay_method_cond = #{payMethodCond},
            performance_grade = #{performanceGrade},
            effective_from = #{effectiveFrom},
            effective_to = #{effectiveTo},
            version = #{version}
        WHERE rule_id = #{ruleId}
    </update>

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

    <!-- ============ override_rule ============ -->
    <select id="selectOverrideRules" resultMap="OverrideMap">
        SELECT * FROM override_rule ORDER BY from_grade, to_grade, effective_from DESC
    </select>

    <insert id="insertOverrideRule" parameterType="com.ga.core.vo.rule.OverrideRuleVO"
            useGeneratedKeys="true" keyProperty="overrideId">
        INSERT INTO override_rule (from_grade, to_grade, override_pct, calc_type,
                                   insurance_type_cond, cap_amount, effective_from, effective_to)
        VALUES (#{fromGrade}, #{toGrade}, #{overridePct}, #{calcType},
                #{insuranceTypeCond}, #{capAmount}, #{effectiveFrom}, #{effectiveTo})
    </insert>

    <update id="updateOverrideRule" parameterType="com.ga.core.vo.rule.OverrideRuleVO">
        UPDATE override_rule SET
            override_pct = #{overridePct},
            calc_type = #{calcType},
            insurance_type_cond = #{insuranceTypeCond},
            cap_amount = #{capAmount},
            effective_from = #{effectiveFrom},
            effective_to = #{effectiveTo}
        WHERE override_id = #{overrideId}
    </update>

    <delete id="deleteOverrideRule">DELETE FROM override_rule WHERE override_id = #{overrideId}</delete>

    <!-- ============ chargeback_rule ============ -->
    <select id="selectChargebackRules" resultMap="ChargebackMap">
        SELECT * FROM chargeback_rule
        <where>
            <if test="companyCode != null and companyCode != ''">AND company_code = #{companyCode}</if>
        </where>
        ORDER BY company_code NULLS FIRST, insurance_type, lapse_month_from, effective_from DESC
    </select>

    <select id="findChargebackRate" resultType="java.math.BigDecimal">
        SELECT cb_rate FROM chargeback_rule
        WHERE (company_code = #{companyCode} OR company_code IS NULL)
          AND (insurance_type = #{insuranceType} OR insurance_type IS NULL)
          AND #{lapseMonth} BETWEEN lapse_month_from AND lapse_month_to
          AND effective_from &lt;= #{baseDate}
          AND (effective_to IS NULL OR effective_to &gt;= #{baseDate})
        ORDER BY (company_code IS NULL) ASC, (insurance_type IS NULL) ASC, effective_from DESC
        LIMIT 1
    </select>

    <insert id="insertChargebackRule" parameterType="com.ga.core.vo.rule.ChargebackRuleVO"
            useGeneratedKeys="true" keyProperty="cbRuleId">
        INSERT INTO chargeback_rule (company_code, insurance_type, lapse_month_from, lapse_month_to,
                                     cb_rate, include_override, installment_allowed, max_installments,
                                     effective_from, effective_to)
        VALUES (#{companyCode}, #{insuranceType}, #{lapseMonthFrom}, #{lapseMonthTo},
                #{cbRate}, COALESCE(#{includeOverride},'N'),
                COALESCE(#{installmentAllowed},'N'), #{maxInstallments},
                #{effectiveFrom}, #{effectiveTo})
    </insert>

    <update id="updateChargebackRule" parameterType="com.ga.core.vo.rule.ChargebackRuleVO">
        UPDATE chargeback_rule SET
            cb_rate = #{cbRate},
            include_override = #{includeOverride},
            installment_allowed = #{installmentAllowed},
            max_installments = #{maxInstallments},
            effective_from = #{effectiveFrom},
            effective_to = #{effectiveTo}
        WHERE cb_rule_id = #{cbRuleId}
    </update>

    <delete id="deleteChargebackRule">DELETE FROM chargeback_rule WHERE cb_rule_id = #{cbRuleId}</delete>

    <!-- ============ exception_type_code ============ -->
    <select id="selectExceptionCodes" resultMap="ExceptionMap">
        SELECT * FROM exception_type_code WHERE is_active = 'Y' ORDER BY direction, exception_code
    </select>

    <select id="selectExceptionCode" resultMap="ExceptionMap">
        SELECT * FROM exception_type_code WHERE exception_code = #{exceptionCode}
    </select>

    <insert id="insertExceptionCode" parameterType="com.ga.core.vo.rule.ExceptionTypeCodeVO">
        INSERT INTO exception_type_code (exception_code, exception_name, category, direction,
                                         auto_calc_yn, approval_required, tax_included,
                                         description, is_active)
        VALUES (#{exceptionCode}, #{exceptionName}, #{category}, #{direction},
                COALESCE(#{autoCalcYn},'N'), COALESCE(#{approvalRequired},'N'),
                COALESCE(#{taxIncluded},'N'), #{description}, COALESCE(#{isActive},'Y'))
    </insert>

    <update id="updateExceptionCode" parameterType="com.ga.core.vo.rule.ExceptionTypeCodeVO">
        UPDATE exception_type_code SET
            exception_name = #{exceptionName},
            category = #{category},
            direction = #{direction},
            auto_calc_yn = #{autoCalcYn},
            approval_required = #{approvalRequired},
            tax_included = #{taxIncluded},
            description = #{description},
            is_active = #{isActive}
        WHERE exception_code = #{exceptionCode}
    </update>

</mapper>
