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

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

    <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.reinstatement_date, l.reinstatement_premium,
        l.commission_amount, l.chargeback_reversal, l.net_commission,
        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 reinstatement_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(net_commission), 0)
          FROM reinstatement_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 reinstatement_commission_ledger
            (contract_id, agent_id, rule_id, settle_month, reinstatement_date,
             reinstatement_premium, commission_amount, chargeback_reversal, net_commission,
             pay_type, status)
        VALUES
            (#{contractId}, #{agentId}, #{ruleId}, #{settleMonth}, #{reinstatementDate},
             #{reinstatementPremium}, #{commissionAmount}, #{chargebackReversal}, #{netCommission},
             #{payType}, COALESCE(#{status}, 'CALCULATED'))
    </insert>

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

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

</mapper>
