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

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

    <sql id="joinCols">
        l.ledger_id, l.contract_id, ct.policy_no,
        l.agent_id, a.agent_name, o.org_name,
        l.settle_month, l.renewal_date, l.renewal_premium, l.commission_amount,
        l.pay_type, l.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'MASTER_STATUS' AND cc.code = l.status) AS status_name
    </sql>

    <sql id="joins">
        FROM renewal_commission_ledger l
        LEFT JOIN agent a        ON a.agent_id = l.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        LEFT JOIN contract ct    ON ct.contract_id = l.contract_id
    </sql>

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

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joins"/>
        <where>
            <if test="agentId != null">AND l.agent_id = #{agentId}</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="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.ledger_id DESC
    </select>

    <select id="sumByAgentAndMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(commission_amount), 0)
          FROM renewal_commission_ledger
         WHERE agent_id     = #{agentId}
           AND settle_month = #{settleMonth}
           AND status NOT IN ('CANCELLED')
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO renewal_commission_ledger
            (contract_id, agent_id, rule_id, settle_month, renewal_date,
             renewal_premium, commission_amount, pay_type, status)
        VALUES
            (#{contractId}, #{agentId}, #{ruleId}, #{settleMonth}, #{renewalDate},
             #{renewalPremium}, #{commissionAmount}, #{payType},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

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

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

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

</mapper>
