<?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.settle.CommissionStatementMapper">

    <!-- ==================== ResultMap ==================== -->

    <!-- commission_statement 테이블 1:1 매핑 (CUD용) -->
    <resultMap id="VOMap" type="com.ga.core.vo.settle.CommissionStatementVO">
        <id  property="statementId"      column="statement_id"/>
        <result property="agentId"           column="agent_id"/>
        <result property="settleMonth"       column="settle_month"/>
        <result property="statementType"     column="statement_type"/>
        <result property="grossAmount"       column="gross_amount"/>
        <result property="incomeTaxAmount"   column="income_tax_amount"/>
        <result property="localTaxAmount"    column="local_tax_amount"/>
        <result property="vatAmount"         column="vat_amount"/>
        <result property="deductionAmount"   column="deduction_amount"/>
        <result property="netAmount"         column="net_amount"/>
        <result property="issuedAt"          column="issued_at"/>
        <result property="issuedBy"          column="issued_by"/>
        <result property="filePath"          column="file_path"/>
        <result property="fileFormat"        column="file_format"/>
        <result property="issueStatus"       column="issue_status"/>
        <result property="createdAt"         column="created_at"/>
        <result property="createdBy"         column="created_by"/>
        <result property="updatedAt"         column="updated_at"/>
        <result property="updatedBy"         column="updated_by"/>
    </resultMap>

    <!-- 조인 결과 포함 API 응답용 -->
    <resultMap id="RespMap" type="com.ga.core.vo.settle.CommissionStatementResp">
        <id  property="statementId"          column="statement_id"/>
        <result property="agentId"               column="agent_id"/>
        <result property="agentName"             column="agent_name"/>
        <result property="settleMonth"           column="settle_month"/>
        <result property="statementType"         column="statement_type"/>
        <result property="statementTypeName"     column="statement_type_name"/>
        <result property="grossAmount"           column="gross_amount"/>
        <result property="incomeTaxAmount"       column="income_tax_amount"/>
        <result property="localTaxAmount"        column="local_tax_amount"/>
        <result property="vatAmount"             column="vat_amount"/>
        <result property="deductionAmount"       column="deduction_amount"/>
        <result property="netAmount"             column="net_amount"/>
        <result property="issuedAt"              column="issued_at"/>
        <result property="issuedBy"              column="issued_by"/>
        <result property="filePath"              column="file_path"/>
        <result property="fileFormat"            column="file_format"/>
        <result property="issueStatus"           column="issue_status"/>
        <result property="issueStatusName"       column="issue_status_name"/>
    </resultMap>

    <!-- ==================== 공통 SELECT 컬럼 ==================== -->

    <sql id="selectRespCols">
        s.statement_id,
        s.agent_id,
        a.agent_name,
        s.settle_month,
        s.statement_type,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'STATEMENT_TYPE' AND cc.code = s.statement_type) AS statement_type_name,
        s.gross_amount,
        s.income_tax_amount,
        s.local_tax_amount,
        s.vat_amount,
        s.deduction_amount,
        s.net_amount,
        s.issued_at,
        s.issued_by,
        s.file_path,
        s.file_format,
        s.issue_status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'STATEMENT_STATUS' AND cc.code = s.issue_status) AS issue_status_name
    </sql>

    <!-- ==================== SELECT ==================== -->

    <!-- 목록 조회 (검색 조건 + 페이징) -->
    <select id="selectList" parameterType="com.ga.core.vo.settle.CommissionStatementSearchParam"
            resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM commission_statement s
        JOIN agent a ON a.agent_id = s.agent_id
        <where>
            <if test="agentId != null">
                AND s.agent_id = #{agentId}
            </if>
            <if test="settleMonth != null and settleMonth != ''">
                AND s.settle_month = #{settleMonth}
            </if>
            <if test="statementType != null and statementType != ''">
                AND s.statement_type = #{statementType}
            </if>
            <if test="issueStatus != null and issueStatus != ''">
                AND s.issue_status = #{issueStatus}
            </if>
            <if test="fromDate != null and fromDate != ''">
                AND s.issued_at >= #{fromDate}::DATE
            </if>
            <if test="toDate != null and toDate != ''">
                AND s.issued_at &lt; (#{toDate}::DATE + INTERVAL '1 day')
            </if>
        </where>
        ORDER BY s.statement_id DESC
    </select>

    <!-- 단건 조회 (Resp: 조인 포함) -->
    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM commission_statement s
        JOIN agent a ON a.agent_id = s.agent_id
        WHERE s.statement_id = #{statementId}
    </select>

    <!-- 단건 조회 (VO: CUD용) -->
    <select id="selectById" resultMap="VOMap">
        SELECT statement_id, agent_id, settle_month, statement_type,
               gross_amount, income_tax_amount, local_tax_amount,
               vat_amount, deduction_amount, net_amount,
               issued_at, issued_by, file_path, file_format, issue_status,
               created_at, created_by, updated_at, updated_by
        FROM commission_statement
        WHERE statement_id = #{statementId}
    </select>

    <!-- UNIQUE 키 조회 — 재발급 여부 판단 및 upsert 로직 지원 -->
    <select id="selectByAgentMonth" resultMap="VOMap">
        SELECT statement_id, agent_id, settle_month, statement_type,
               gross_amount, income_tax_amount, local_tax_amount,
               vat_amount, deduction_amount, net_amount,
               issued_at, issued_by, file_path, file_format, issue_status,
               created_at, created_by, updated_at, updated_by
        FROM commission_statement
        WHERE agent_id       = #{agentId}
          AND settle_month   = #{settleMonth}
          AND statement_type = #{statementType}
    </select>

    <!-- ==================== INSERT ==================== -->

    <insert id="insert" parameterType="com.ga.core.vo.settle.CommissionStatementVO"
            useGeneratedKeys="true" keyProperty="statementId">
        INSERT INTO commission_statement (
            agent_id, settle_month, statement_type,
            gross_amount, income_tax_amount, local_tax_amount,
            vat_amount, deduction_amount, net_amount,
            issued_at, issued_by, file_path, file_format, issue_status,
            created_at, created_by
        ) VALUES (
            #{agentId}, #{settleMonth}, #{statementType},
            #{grossAmount}, #{incomeTaxAmount}, #{localTaxAmount},
            COALESCE(#{vatAmount}, 0), COALESCE(#{deductionAmount}, 0), #{netAmount},
            #{issuedAt}, #{issuedBy}, #{filePath}, #{fileFormat},
            COALESCE(#{issueStatus}, 'GENERATED'),
            NOW(), #{createdBy}
        )
    </insert>

    <!-- ==================== UPDATE ==================== -->

    <!-- 재발급 시 스냅샷 + 발급 정보 전체 갱신 -->
    <update id="update" parameterType="com.ga.core.vo.settle.CommissionStatementVO">
        UPDATE commission_statement
        <set>
            <if test="grossAmount != null">gross_amount = #{grossAmount},</if>
            <if test="incomeTaxAmount != null">income_tax_amount = #{incomeTaxAmount},</if>
            <if test="localTaxAmount != null">local_tax_amount = #{localTaxAmount},</if>
            <if test="vatAmount != null">vat_amount = #{vatAmount},</if>
            <if test="deductionAmount != null">deduction_amount = #{deductionAmount},</if>
            <if test="netAmount != null">net_amount = #{netAmount},</if>
            <if test="issuedAt != null">issued_at = #{issuedAt},</if>
            <if test="issuedBy != null">issued_by = #{issuedBy},</if>
            <if test="filePath != null">file_path = #{filePath},</if>
            <if test="fileFormat != null">file_format = #{fileFormat},</if>
            <if test="issueStatus != null and issueStatus != ''">issue_status = #{issueStatus},</if>
            updated_at = NOW(),
            updated_by = #{updatedBy}
        </set>
        WHERE statement_id = #{statementId}
    </update>

    <!-- 상태만 변경 (SENT / DOWNLOADED / CANCELLED) -->
    <update id="updateStatus">
        UPDATE commission_statement
        SET issue_status = #{issueStatus}, updated_at = NOW()
        WHERE statement_id = #{statementId}
    </update>

</mapper>
