<?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.income.AgentAnnualIncomeMapper">

    <!-- agent 조인 포함 응답 매핑 -->
    <resultMap id="RespMap" type="com.ga.core.vo.income.AgentAnnualIncomeResp"/>

    <!-- 목록/상세에서 공통으로 사용하는 SELECT 컬럼 -->
    <sql id="joinCols">
        i.agent_id,
        a.agent_name,
        a.org_id,
        o.org_name,
        i.settle_year,
        i.total_commission,
        i.total_tax_withheld,
        i.total_local_tax,
        i.business_income,
        i.generated_at,
        i.created_at,
        i.updated_at
    </sql>

    <!-- ===== 목록 조회 ===== -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_annual_income i
        JOIN agent       a ON a.agent_id = i.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        <where>
            <if test="settleYear != null">AND i.settle_year = #{settleYear}</if>
            <if test="agentId   != null">AND i.agent_id    = #{agentId}</if>
            <if test="orgId     != null">AND a.org_id      = #{orgId}</if>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND a.agent_name LIKE CONCAT('%', #{searchKeyword}, '%')
            </if>
        </where>
        ORDER BY i.settle_year DESC, i.agent_id ASC
    </select>

    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_annual_income i
        JOIN agent       a ON a.agent_id = i.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE i.agent_id   = #{agentId}
          AND i.settle_year = #{settleYear}
    </select>

    <!-- 연도 전체 UPSERT 집계: payment 테이블 기준 설계사별 SUM 후 UPSERT -->
    <insert id="upsertAggregate">
        INSERT INTO agent_annual_income
            (agent_id, settle_year,
             total_commission, total_tax_withheld, total_local_tax, business_income,
             generated_at)
        SELECT
            p.agent_id,
            #{year}                                          AS settle_year,
            COALESCE(SUM(p.pay_amount),        0)           AS total_commission,
            COALESCE(SUM(p.income_tax_amount), 0)           AS total_tax_withheld,
            COALESCE(SUM(p.local_tax_amount),  0)           AS total_local_tax,
            COALESCE(SUM(p.pay_amount)
                   - SUM(p.income_tax_amount)
                   - SUM(p.local_tax_amount),  0)           AS business_income,
            NOW()                                           AS generated_at
        FROM payment p
        WHERE EXTRACT(YEAR FROM p.pay_date) = #{year}
        GROUP BY p.agent_id
        ON CONFLICT (agent_id, settle_year) DO UPDATE SET
            total_commission   = EXCLUDED.total_commission,
            total_tax_withheld = EXCLUDED.total_tax_withheld,
            total_local_tax    = EXCLUDED.total_local_tax,
            business_income    = EXCLUDED.business_income,
            generated_at       = NOW(),
            updated_at         = NOW()
    </insert>


</mapper>
