<?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.tax.WithholdingTaxReportMapper">

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

    <!-- 목록/상세에서 공통으로 사용하는 SELECT 컬럼 -->
    <sql id="joinCols">
        r.report_id,
        r.agent_id,
        a.agent_name,
        a.org_id,
        o.org_name,
        r.settle_quarter,
        r.total_income,
        r.total_withheld,
        r.total_local_tax,
        r.generated_at,
        r.filed_at,
        r.file_status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'TAX_FILE_STATUS' AND cc.code = r.file_status) AS file_status_name,
        r.created_at,
        r.updated_at
    </sql>

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

    <!-- ===== 단건 상세 ===== -->
    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM withholding_tax_report r
        JOIN agent        a ON a.agent_id = r.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE r.report_id = #{reportId}
    </select>

    <!--
        분기 원천세 UPSERT 집계.
        payment(실지급) + withholding_tax_adjustment(환수 원천세 역분개, V124) 를 합산.
        payment: 해당 분기 설계사별 income_tax_amount / local_tax_amount / pay_amount SUM.
        역분개: 동일 분기 설계사별 음수 income_tax_adj / local_tax_adj / -base_amount 합산
                → 환수 우위 월의 기납부 원천세 과대신고를 차감 보정.
        분기 판단: pay_date 로 EXTRACT(YEAR/QUARTER) / 역분개는 settle_month(YYYYMM) 파싱.
        예) '2025-Q1' → YEAR=2025, QUARTER=1
        ON CONFLICT (agent_id, settle_quarter) DO UPDATE.
    -->
    <insert id="upsertQuarterlyAggregate">
        INSERT INTO withholding_tax_report
            (agent_id, settle_quarter,
             total_income, total_withheld, total_local_tax,
             generated_at)
        SELECT
            u.agent_id,
            #{quarter}                          AS settle_quarter,
            COALESCE(SUM(u.total_income),    0) AS total_income,
            COALESCE(SUM(u.total_withheld),  0) AS total_withheld,
            COALESCE(SUM(u.total_local_tax), 0) AS total_local_tax,
            NOW()                               AS generated_at
        FROM (
            SELECT
                p.agent_id,
                p.pay_amount        AS total_income,
                p.income_tax_amount AS total_withheld,
                p.local_tax_amount  AS total_local_tax
            FROM payment p
            WHERE EXTRACT(YEAR    FROM p.pay_date) = CAST(SPLIT_PART(#{quarter}, '-Q', 1) AS INT)
              AND EXTRACT(QUARTER FROM p.pay_date) = CAST(SPLIT_PART(#{quarter}, '-Q', 2) AS INT)
            UNION ALL
            SELECT
                a.agent_id,
                -a.base_amount   AS total_income,
                a.income_tax_adj AS total_withheld,
                a.local_tax_adj  AS total_local_tax
            FROM withholding_tax_adjustment a
            WHERE CAST(SUBSTRING(a.settle_month, 1, 4) AS INT) = CAST(SPLIT_PART(#{quarter}, '-Q', 1) AS INT)
              AND CEIL(CAST(SUBSTRING(a.settle_month, 5, 2) AS NUMERIC) / 3.0) = CAST(SPLIT_PART(#{quarter}, '-Q', 2) AS INT)
        ) u
        GROUP BY u.agent_id
        ON CONFLICT (agent_id, settle_quarter) DO UPDATE SET
            total_income    = EXCLUDED.total_income,
            total_withheld  = EXCLUDED.total_withheld,
            total_local_tax = EXCLUDED.total_local_tax,
            generated_at    = NOW(),
            updated_at      = NOW()
    </insert>

    <!-- ===== 신고 완료 처리 ===== -->
    <update id="updateFileStatusToFiled">
        UPDATE withholding_tax_report
        SET file_status = 'FILED',
            filed_at    = NOW(),
            updated_at  = NOW()
        WHERE report_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </update>

</mapper>
