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

    <!-- 부가세 분기신고 응답 매핑 (조인 없음, 분기 집계 단위) -->
    <resultMap id="RespMap" type="com.ga.core.vo.tax.VatReportResp"/>

    <!-- 목록/상세 공통 SELECT 컬럼 -->
    <sql id="cols">
        r.vat_id,
        r.settle_quarter,
        r.total_sales,
        r.total_purchase,
        r.total_vat_payable,
        r.tax_invoice_count,
        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="cols"/>
        FROM vat_report r
        <where>
            <if test="settleQuarter != null and settleQuarter != ''">AND r.settle_quarter = #{settleQuarter}</if>
            <if test="fileStatus    != null and fileStatus != ''">AND r.file_status    = #{fileStatus}</if>
        </where>
        ORDER BY r.settle_quarter DESC
    </select>

    <!-- ===== 단건 상세 ===== -->
    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="cols"/>
        FROM vat_report r
        WHERE r.vat_id = #{vatId}
    </select>

    <!--
        분기 부가세 UPSERT 집계.
        tax_invoice 테이블에서 해당 분기 SUPPLY(매출)/PURCHASE(매입) SUM.
        - total_sales     = SUM(supply_amount) WHERE invoice_type='SUPPLY'
        - total_purchase  = SUM(supply_amount) WHERE invoice_type='PURCHASE'
        - total_vat_payable = total_sales * 0.1 - (SUM(tax_amount) WHERE PURCHASE)
        - tax_invoice_count = 건수 (취소 제외)
        분기 판단: issue_date 로 EXTRACT(YEAR/QUARTER) 사용.
        ON CONFLICT (settle_quarter) DO UPDATE.
    -->
    <insert id="upsertAggregate">
        INSERT INTO vat_report
            (settle_quarter,
             total_sales, total_purchase, total_vat_payable,
             tax_invoice_count,
             generated_at)
        SELECT
            #{quarter}                                                       AS settle_quarter,
            COALESCE(SUM(CASE WHEN ti.invoice_type = 'SUPPLY'
                              THEN ti.supply_amount ELSE 0 END), 0)         AS total_sales,
            COALESCE(SUM(CASE WHEN ti.invoice_type = 'PURCHASE'
                              THEN ti.supply_amount ELSE 0 END), 0)         AS total_purchase,
            COALESCE(SUM(CASE WHEN ti.invoice_type = 'SUPPLY'
                              THEN ti.supply_amount ELSE 0 END), 0) * 0.1
          - COALESCE(SUM(CASE WHEN ti.invoice_type = 'PURCHASE'
                              THEN ti.tax_amount ELSE 0 END), 0)            AS total_vat_payable,
            COUNT(*)                                                        AS tax_invoice_count,
            NOW()                                                           AS generated_at
        FROM tax_invoice ti
        WHERE EXTRACT(YEAR    FROM ti.issue_date) = CAST(SPLIT_PART(#{quarter}, '-Q', 1) AS INT)
          AND EXTRACT(QUARTER FROM ti.issue_date) = CAST(SPLIT_PART(#{quarter}, '-Q', 2) AS INT)
          AND ti.cancel_date IS NULL
        ON CONFLICT (settle_quarter) DO UPDATE SET
            total_sales        = EXCLUDED.total_sales,
            total_purchase     = EXCLUDED.total_purchase,
            total_vat_payable  = EXCLUDED.total_vat_payable,
            tax_invoice_count  = EXCLUDED.tax_invoice_count,
            generated_at       = NOW(),
            updated_at         = NOW()
    </insert>

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

</mapper>
