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

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

    <!-- 목록/단건 조회 컬럼 — 설계사명·계약번호·공통코드명 조인 포함 -->
    <sql id="ledgerJoinCols">
        l.ledger_id, l.contract_id, ct.policy_no,
        l.referrer_agent_id, a.agent_name AS referrer_agent_name,
        l.settle_month,
        l.referral_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'REFERRAL_TYPE' AND cc.code = l.referral_type) AS referral_type_name,
        l.base_amount, l.commission_rate, l.commission_amount,
        l.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'INCOME_STATUS' AND cc.code = l.status) AS status_name
    </sql>

    <sql id="ledgerJoins">
        FROM referral_commission_ledger l
        LEFT JOIN agent a      ON a.agent_id = l.referrer_agent_id
        LEFT JOIN contract ct  ON ct.contract_id = l.contract_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        WHERE l.ledger_id = #{ledgerId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        <where>
            <if test="referrerAgentId != null">AND l.referrer_agent_id = #{referrerAgentId}</if>
            <if test="contractId != null">AND l.contract_id = #{contractId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND l.settle_month = #{settleMonth}</if>
            <if test="referralType != null and referralType != ''">AND l.referral_type = #{referralType}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.referrer_agent_id, l.ledger_id DESC
    </select>

    <!-- 설계사 + 정산월 소개·이관수수료 합계 — CANCELLED 제외 -->
    <select id="sumByAgentAndMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(commission_amount), 0)
          FROM referral_commission_ledger
         WHERE referrer_agent_id = #{referrerAgentId}
           AND settle_month      = #{settleMonth}
           AND status            != 'CANCELLED'
    </select>

    <!-- commission_amount 는 Service가 계산 후 VO에 set, status 기본 CALCULATED -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO referral_commission_ledger
            (contract_id, referrer_agent_id, settle_month, referral_type,
             base_amount, commission_rate, commission_amount, status)
        VALUES
            (#{contractId}, #{referrerAgentId}, #{settleMonth}, #{referralType},
             #{baseAmount}, #{commissionRate}, #{commissionAmount},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

    <update id="updateStatus">
        UPDATE referral_commission_ledger
           SET status = #{status}, updated_at = NOW()
         WHERE ledger_id = #{ledgerId}
    </update>

    <delete id="delete">
        DELETE FROM referral_commission_ledger WHERE ledger_id = #{ledgerId}
    </delete>

    <!-- 정산월 기준 설계사별 소개·이관수수료 합계 -->
    <select id="aggregateByAgent" resultType="map">
        SELECT referrer_agent_id  AS "agentId",
               SUM(commission_amount) AS "total"
          FROM referral_commission_ledger
         WHERE settle_month = #{settleMonth}
           AND status != 'CANCELLED'
         GROUP BY referrer_agent_id
    </select>

</mapper>
