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

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

    <sql id="joinCols">
        pb.bonus_id, pb.agent_id, a.agent_name, o.org_name,
        pb.rule_id, pb.base_month, pb.retention_month, pb.settle_month,
        pb.retention_rate, pb.bonus_amount, pb.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'MASTER_STATUS' AND cc.code = pb.status) AS status_name
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM persistency_bonus pb
        LEFT JOIN agent a        ON a.agent_id = pb.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE pb.bonus_id = #{bonusId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM persistency_bonus pb
        LEFT JOIN agent a        ON a.agent_id = pb.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        <where>
            <if test="agentId != null">AND pb.agent_id = #{agentId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND pb.settle_month = #{settleMonth}</if>
            <if test="retentionMonth != null">AND pb.retention_month = #{retentionMonth}</if>
            <if test="status != null and status != ''">AND pb.status = #{status}</if>
        </where>
        ORDER BY pb.settle_month DESC, pb.agent_id, pb.retention_month
    </select>

    <!--
        UPSERT — UNIQUE(agent_id, base_month, retention_month) 기반.
        배치 유지율 평가 완료 후 중복 방지하며 저장.
    -->
    <insert id="upsert">
        INSERT INTO persistency_bonus
            (agent_id, rule_id, base_month, retention_month, settle_month,
             retention_rate, bonus_amount, status)
        VALUES
            (#{agentId}, #{ruleId}, #{baseMonth}, #{retentionMonth}, #{settleMonth},
             #{retentionRate}, #{bonusAmount}, COALESCE(#{status}, 'CALCULATED'))
        ON CONFLICT (agent_id, base_month, retention_month)
        DO UPDATE SET
            rule_id        = EXCLUDED.rule_id,
            settle_month   = EXCLUDED.settle_month,
            retention_rate = EXCLUDED.retention_rate,
            bonus_amount   = EXCLUDED.bonus_amount,
            status         = EXCLUDED.status,
            updated_at     = NOW()
    </insert>

    <insert id="insert" useGeneratedKeys="true" keyProperty="bonusId">
        INSERT INTO persistency_bonus
            (agent_id, rule_id, base_month, retention_month, settle_month,
             retention_rate, bonus_amount, status)
        VALUES
            (#{agentId}, #{ruleId}, #{baseMonth}, #{retentionMonth}, #{settleMonth},
             #{retentionRate}, #{bonusAmount}, COALESCE(#{status}, 'CALCULATED'))
    </insert>

    <update id="updateStatus">
        UPDATE persistency_bonus
           SET status = #{status}, updated_at = NOW()
         WHERE bonus_id = #{bonusId}
    </update>

    <delete id="delete">
        DELETE FROM persistency_bonus WHERE bonus_id = #{bonusId}
    </delete>

    <!-- 정산월 기준 설계사별 유지보너스 합계 -->
    <select id="aggregateByAgent" resultType="map">
        SELECT agent_id        AS "agentId",
               SUM(bonus_amount) AS "total"
          FROM persistency_bonus
         WHERE settle_month = #{settleMonth}
           AND status != 'CANCELLED'
         GROUP BY agent_id
    </select>

</mapper>
