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

    <resultMap id="RespMap"  type="com.ga.core.vo.income.YearEndStatementResp"/>
    <resultMap id="ExcelMap" type="com.ga.core.vo.income.YearEndStatementExcelVO"/>

    <sql id="joinCols">
        yes.statement_id,
        yes.agent_id,
        a.agent_name,
        a.business_no,
        a.org_id,
        o.org_name,
        yes.statement_year,
        yes.statement_type,
        CASE yes.statement_type WHEN 'INCOME_DEDUCTION' THEN '사업소득 지급명세서' ELSE yes.statement_type END
            AS statement_type_name,
        yes.total_income,
        yes.total_withheld,
        yes.total_local_tax,
        yes.file_path,
        yes.file_format,
        yes.file_status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'YES_STATUS' AND cc.code = yes.file_status) AS file_status_name,
        yes.generated_at,
        yes.sent_at,
        yes.sent_to,
        yes.created_at,
        yes.updated_at
    </sql>

    <sql id="joinFrom">
        FROM year_end_statement yes
        JOIN agent        a ON a.agent_id = yes.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joinFrom"/>
        <where>
            <if test="statementYear != null">AND yes.statement_year = #{statementYear}</if>
            <if test="agentId       != null">AND yes.agent_id       = #{agentId}</if>
            <if test="orgId         != null">AND a.org_id           = #{orgId}</if>
            <if test="fileStatus    != null and fileStatus != ''">AND yes.file_status = #{fileStatus}</if>
            <if test="fileFormat    != null and fileFormat != ''">AND yes.file_format = #{fileFormat}</if>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND a.agent_name LIKE CONCAT('%', #{searchKeyword}, '%')
            </if>
        </where>
        ORDER BY yes.statement_year DESC, a.agent_id
    </select>

    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joinFrom"/>
        WHERE yes.statement_id = #{statementId}
    </select>

    <select id="selectExcelCursor" resultMap="ExcelMap" fetchSize="500" resultSetType="FORWARD_ONLY">
        SELECT a.agent_name,
               o.org_name,
               a.business_no,
               yes.statement_year,
               yes.total_income,
               yes.total_withheld,
               yes.total_local_tax,
               (SELECT cc.code_name FROM common_code cc
                WHERE cc.group_code = 'YES_STATUS' AND cc.code = yes.file_status) AS file_status_name
        <include refid="joinFrom"/>
        <where>
            <if test="statementYear != null">AND yes.statement_year = #{statementYear}</if>
            <if test="agentId       != null">AND yes.agent_id       = #{agentId}</if>
            <if test="orgId         != null">AND a.org_id           = #{orgId}</if>
            <if test="fileStatus    != null and fileStatus != ''">AND yes.file_status = #{fileStatus}</if>
        </where>
        ORDER BY a.agent_id
    </select>

    <!--
        agent_annual_income(V63)를 기반으로 UPSERT.
        agent_annual_income 컬럼: agent_id / settle_year / total_income / total_withheld / generated_at
        지방소득세 = total_withheld * 0.1 (한국 표준)
    -->
    <insert id="upsertByYear">
        INSERT INTO year_end_statement
            (agent_id, statement_year, statement_type,
             total_income, total_withheld, total_local_tax, file_status, created_at)
        SELECT ai.agent_id,
               #{year},
               'INCOME_DEDUCTION',
               COALESCE(ai.total_commission,   0),
               COALESCE(ai.total_tax_withheld, 0),
               COALESCE(ai.total_local_tax,    0),
               'DRAFT',
               now()
          FROM agent_annual_income ai
         WHERE ai.settle_year = #{year}
        <if test="agentIds != null and agentIds.size() > 0">
            AND ai.agent_id IN
            <foreach collection="agentIds" item="aid" open="(" separator="," close=")">#{aid}</foreach>
        </if>
        ON CONFLICT (agent_id, statement_year, statement_type) DO UPDATE SET
            total_income    = EXCLUDED.total_income,
            total_withheld  = EXCLUDED.total_withheld,
            total_local_tax = EXCLUDED.total_local_tax,
            updated_at      = now()
    </insert>

    <update id="updateGenerated">
        UPDATE year_end_statement
           SET file_path    = #{filePath},
               file_format  = #{fileFormat},
               file_status  = 'GENERATED',
               generated_at = now(),
               updated_at   = now()
         WHERE statement_id = #{statementId}
    </update>

    <update id="updateSent">
        UPDATE year_end_statement
           SET file_status = 'SENT',
               sent_at     = now(),
               sent_to     = #{sentTo},
               updated_at  = now()
         WHERE statement_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
           AND file_status = 'GENERATED'
    </update>

</mapper>
