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

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

    <!-- agent_deduction 테이블 1:1 매핑 -->
    <resultMap id="VOMap" type="com.ga.core.vo.settle.AgentDeductionVO">
        <id  property="deductionId"  column="deduction_id"/>
        <result property="agentId"       column="agent_id"/>
        <result property="settleMonth"   column="settle_month"/>
        <result property="deductionType" column="deduction_type"/>
        <result property="amount"        column="amount"/>
        <result property="description"   column="description"/>
        <result property="status"        column="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.AgentDeductionResp">
        <id  property="deductionId"      column="deduction_id"/>
        <result property="agentId"           column="agent_id"/>
        <result property="agentName"         column="agent_name"/>
        <result property="settleMonth"       column="settle_month"/>
        <result property="deductionType"     column="deduction_type"/>
        <result property="deductionTypeName" column="deduction_type_name"/>
        <result property="amount"            column="amount"/>
        <result property="description"       column="description"/>
        <result property="status"            column="status"/>
        <result property="statusName"        column="status_name"/>
        <result property="createdAt"         column="created_at"/>
    </resultMap>

    <!-- ==================== 공통 SELECT 컬럼 (목록/상세 공유) ==================== -->

    <sql id="selectRespCols">
        d.deduction_id,
        d.agent_id,
        a.agent_name,
        d.settle_month,
        d.deduction_type,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'DEDUCTION_TYPE' AND cc.code = d.deduction_type) AS deduction_type_name,
        d.amount,
        d.description,
        d.status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'DEDUCTION_STATUS' AND cc.code = d.status) AS status_name,
        d.created_at
    </sql>

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

    <!-- 목록 조회 (검색 조건 + 페이징) -->
    <select id="selectList" parameterType="com.ga.core.vo.settle.AgentDeductionSearchParam"
            resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM agent_deduction d
        JOIN agent a ON a.agent_id = d.agent_id
        <where>
            <if test="agentId != null">
                AND d.agent_id = #{agentId}
            </if>
            <if test="settleMonth != null and settleMonth != ''">
                AND d.settle_month = #{settleMonth}
            </if>
            <if test="deductionType != null and deductionType != ''">
                AND d.deduction_type = #{deductionType}
            </if>
            <if test="status != null and status != ''">
                AND d.status = #{status}
            </if>
        </where>
        ORDER BY d.deduction_id DESC
    </select>

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

    <!-- 단건 조회 (VO: CUD용) -->
    <select id="selectById" resultMap="VOMap">
        SELECT deduction_id, agent_id, settle_month, deduction_type,
               amount, description, status,
               created_at, created_by, updated_at, updated_by
        FROM agent_deduction
        WHERE deduction_id = #{deductionId}
    </select>

    <!-- 정산월 전체 PENDING 목록 (배치 처리용) -->
    <select id="selectPendingByMonth" resultMap="VOMap">
        SELECT deduction_id, agent_id, settle_month, deduction_type,
               amount, description, status,
               created_at, created_by, updated_at, updated_by
        FROM agent_deduction
        WHERE settle_month = #{settleMonth}
          AND status = 'PENDING'
        ORDER BY agent_id, deduction_id
    </select>

    <!-- ==================== 집계 ==================== -->

    <!-- 정산월 + 설계사의 PENDING 차감 합계 (BigDecimal 반환) -->
    <select id="sumPendingByAgent" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0)
        FROM agent_deduction
        WHERE agent_id   = #{agentId}
          AND settle_month = #{settleMonth}
          AND status     = 'PENDING'
    </select>

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

    <insert id="insert" parameterType="com.ga.core.vo.settle.AgentDeductionVO"
            useGeneratedKeys="true" keyProperty="deductionId">
        INSERT INTO agent_deduction (agent_id, settle_month, deduction_type,
                                     amount, description, status,
                                     created_at, created_by)
        VALUES (#{agentId}, #{settleMonth}, #{deductionType},
                #{amount}, #{description}, COALESCE(#{status}, 'PENDING'),
                NOW(), #{createdBy})
    </insert>

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

    <update id="update" parameterType="com.ga.core.vo.settle.AgentDeductionVO">
        UPDATE agent_deduction
        <set>
            <if test="deductionType != null and deductionType != ''">deduction_type = #{deductionType},</if>
            <if test="amount != null">amount = #{amount},</if>
            <if test="description != null">description = #{description},</if>
            updated_at = NOW(),
            updated_by = #{updatedBy}
        </set>
        WHERE deduction_id = #{deductionId}
    </update>

    <!-- 상태만 변경 (배치 적용/취소 시 단순 호출) -->
    <update id="updateStatus">
        UPDATE agent_deduction
        SET status = #{status}, updated_at = NOW()
        WHERE deduction_id = #{deductionId}
    </update>

</mapper>
