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

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

    <!-- 목록/단건 조회 컬럼 — 설계사명 조인 포함 (agent_id NULL 허용) -->
    <sql id="summaryJoinCols">
        m.margin_id, m.settle_month,
        m.agent_id, a.agent_name,
        m.income_total, m.payout_total, m.margin_amount, m.margin_rate
    </sql>

    <sql id="summaryJoins">
        FROM margin_summary m
        LEFT JOIN agent a ON a.agent_id = m.agent_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="summaryJoinCols"/>
        <include refid="summaryJoins"/>
        WHERE m.margin_id = #{marginId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="summaryJoinCols"/>
        <include refid="summaryJoins"/>
        <where>
            <if test="settleMonth != null and settleMonth != ''">AND m.settle_month = #{settleMonth}</if>
            <if test="agentId != null">AND m.agent_id = #{agentId}</if>
        </where>
        ORDER BY m.settle_month DESC, m.agent_id NULLS FIRST
    </select>

    <!-- 정산월 + 설계사(NULL=전체) 단위 수지차 1건 조회 (UPSERT 전 존재 확인) -->
    <select id="selectByMonthAndAgent" resultMap="VOMap">
        SELECT m.margin_id, m.settle_month, m.agent_id,
               m.income_total, m.payout_total, m.margin_amount, m.margin_rate, m.created_at
          FROM margin_summary m
         WHERE m.settle_month = #{settleMonth}
        <choose>
            <when test="agentId == null">AND m.agent_id IS NULL</when>
            <otherwise>AND m.agent_id = #{agentId}</otherwise>
        </choose>
         LIMIT 1
    </select>

    <!-- margin_amount / margin_rate 는 Service가 계산 후 VO에 set -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="marginId">
        INSERT INTO margin_summary
            (settle_month, agent_id, income_total, payout_total, margin_amount, margin_rate)
        VALUES
            (#{settleMonth}, #{agentId}, #{incomeTotal}, #{payoutTotal}, #{marginAmount}, #{marginRate})
    </insert>

    <update id="update">
        UPDATE margin_summary
           SET income_total  = #{incomeTotal},
               payout_total  = #{payoutTotal},
               margin_amount = #{marginAmount},
               margin_rate   = #{marginRate}
         WHERE margin_id = #{marginId}
    </update>

    <delete id="delete">
        DELETE FROM margin_summary WHERE margin_id = #{marginId}
    </delete>

    <!-- 정산월 전체 수입수수료 합 (수지차 재계산용) -->
    <select id="sumIncomeByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(income_amount), 0)
          FROM income_commission_ledger
         WHERE settle_month = #{settleMonth}
           AND status != 'CANCELLED'
    </select>

    <!-- 정산월 지급 합 (settle_master 세후 실지급액). agentId NULL=전체 -->
    <select id="sumPayoutByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(net_amount), 0)
          FROM settle_master
         WHERE settle_month = #{settleMonth}
        <if test="agentId != null">AND agent_id = #{agentId}</if>
    </select>

</mapper>
