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

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

    <sql id="joinCols">
        os.summary_id, os.to_agent_id,
        a.agent_name AS to_agent_name, org.org_name,
        os.settle_month,
        os.total_source_amount, os.total_override_amount, os.ledger_count,
        os.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'MASTER_STATUS' AND cc.code = os.status) AS status_name,
        os.calc_date
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM override_summary os
        LEFT JOIN agent a        ON a.agent_id = os.to_agent_id
        LEFT JOIN organization org ON org.org_id = a.org_id
        WHERE os.summary_id = #{summaryId}
    </select>

    <select id="selectByAgentAndMonth" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM override_summary os
        LEFT JOIN agent a        ON a.agent_id = os.to_agent_id
        LEFT JOIN organization org ON org.org_id = a.org_id
        WHERE os.to_agent_id  = #{toAgentId}
          AND os.settle_month = #{settleMonth}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM override_summary os
        LEFT JOIN agent a        ON a.agent_id = os.to_agent_id
        LEFT JOIN organization org ON org.org_id = a.org_id
        <where>
            <if test="toAgentId != null">AND os.to_agent_id = #{toAgentId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND os.settle_month = #{settleMonth}</if>
            <if test="status != null and status != ''">AND os.status = #{status}</if>
        </where>
        ORDER BY os.settle_month DESC, os.to_agent_id
    </select>

    <!--
        UPSERT — UNIQUE(to_agent_id, settle_month).
        배치 집계 결과를 저장. calc_date는 NOW()로 갱신.
    -->
    <insert id="upsert">
        INSERT INTO override_summary
            (to_agent_id, settle_month, total_source_amount, total_override_amount, ledger_count, status)
        VALUES
            (#{toAgentId}, #{settleMonth}, #{totalSourceAmount}, #{totalOverrideAmount}, #{ledgerCount},
             COALESCE(#{status}, 'CALCULATED'))
        ON CONFLICT (to_agent_id, settle_month)
        DO UPDATE SET
            total_source_amount   = EXCLUDED.total_source_amount,
            total_override_amount = EXCLUDED.total_override_amount,
            ledger_count          = EXCLUDED.ledger_count,
            status                = EXCLUDED.status,
            calc_date             = NOW(),
            updated_at            = NOW()
    </insert>

    <update id="updateStatus">
        UPDATE override_summary
           SET status = #{status}, updated_at = NOW()
         WHERE summary_id = #{summaryId}
    </update>

</mapper>
