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

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

    <!-- 목록/단건 조회 컬럼 — 설계사명·조직명·공통코드명 조인 포함 (V114: clawback_amount, net_amount 추가) -->
    <sql id="ledgerJoinCols">
        l.ledger_id,
        l.agent_id, a.agent_name, o.org_name,
        l.settle_month,
        l.award_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'AWARD_TYPE' AND cc.code = l.award_type) AS award_type_name,
        l.base_amount, l.award_amount,
        l.clawback_amount, l.net_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 award_ledger l
        LEFT JOIN agent a        ON a.agent_id = l.agent_id
        LEFT JOIN organization o ON o.org_id   = a.org_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="agentId != null">AND l.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="settleMonth != null and settleMonth != ''">AND l.settle_month = #{settleMonth}</if>
            <if test="awardType != null and awardType != ''">AND l.award_type = #{awardType}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.agent_id, l.ledger_id DESC
    </select>

    <!-- 설계사 + 정산월 시상금 합계 — CANCELLED 제외 -->
    <select id="sumAwardByAgentAndMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(award_amount), 0)
          FROM award_ledger
         WHERE agent_id    = #{agentId}
           AND settle_month = #{settleMonth}
           AND status      != 'CANCELLED'
    </select>

    <!-- award_amount/net_amount 는 Service가 계산 후 VO에 set, status 기본 CALCULATED (V114: clawback_amount, net_amount 포함) -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO award_ledger
            (agent_id, settle_month, award_type, base_amount, award_amount,
             clawback_amount, net_amount, status)
        VALUES
            (#{agentId}, #{settleMonth}, #{awardType}, #{baseAmount}, #{awardAmount},
             COALESCE(#{clawbackAmount}, 0), #{netAmount},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

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

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

    <!-- 정산월 기준 설계사별 시상금 합계 — net_amount 기준(V114: 환수 자동 차감). CANCELLED 제외. -->
    <select id="aggregateByAgent" resultType="map">
        SELECT agent_id         AS "agentId",
               SUM(net_amount)  AS "total"
          FROM award_ledger
         WHERE settle_month = #{settleMonth}
           AND status != 'CANCELLED'
         GROUP BY agent_id
    </select>

    <!-- 환수 처리 — clawback_amount/net_amount/status/updated_at 갱신 -->
    <update id="applyClawback">
        UPDATE award_ledger
           SET clawback_amount = #{clawbackAmount},
               net_amount      = #{netAmount},
               status          = #{status},
               updated_at      = NOW()
         WHERE ledger_id = #{ledgerId}
    </update>

</mapper>
