<?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.commission.HqSettleItemMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.commission.HqSettleItemVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.commission.HqSettleItemResp"/>

    <!--
        공통 SELECT 컬럼.
        item_type / status 코드명을 common_code 서브쿼리로 조회.
    -->
    <sql id="itemCols">
        i.item_id,
        i.settle_month,
        i.item_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'HQ_ITEM_TYPE' AND cc.code = i.item_type) AS item_type_name,
        i.item_name,
        i.amount,
        i.memo,
        i.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'MASTER_STATUS' AND cc.code = i.status) AS status_name,
        i.created_at,
        i.created_by,
        i.updated_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="itemCols"/>
        FROM hq_settle_item i
        WHERE i.item_id = #{itemId}
    </select>

    <!-- 목록: 정산월, 항목유형, status 필터 -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="itemCols"/>
        FROM hq_settle_item i
        <where>
            <if test="settleMonth != null and settleMonth != ''">AND i.settle_month = #{settleMonth}</if>
            <if test="itemType != null and itemType != ''">AND i.item_type = #{itemType}</if>
            <if test="status != null and status != ''">AND i.status = #{status}</if>
        </where>
        ORDER BY i.settle_month DESC, i.item_type, i.item_id DESC
    </select>

    <!--
        정산월 + 항목유형별 금액 합계.
        본사손익 = sumByMonthAndType(INCOME) − sumByMonthAndType(EXPENSE).
        신입 참고: Service에서 두 번 호출해 차감하거나, GROUP BY 쿼리로 한번에 집계.
    -->
    <select id="sumByMonthAndType" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0)
          FROM hq_settle_item
         WHERE settle_month = #{settleMonth}
           AND item_type    = #{itemType}
    </select>

    <!-- INSERT: status 기본값 'REGISTERED' -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="itemId">
        INSERT INTO hq_settle_item
            (settle_month, item_type, item_name, amount, memo, status, created_by)
        VALUES
            (#{settleMonth}, #{itemType}, #{itemName}, #{amount}, #{memo},
             COALESCE(#{status}, 'REGISTERED'), #{createdBy})
    </insert>

    <update id="update">
        UPDATE hq_settle_item
           SET item_type  = #{itemType},
               item_name  = #{itemName},
               amount     = #{amount},
               memo       = #{memo},
               status     = COALESCE(#{status}, status),
               updated_at = NOW()
         WHERE item_id = #{itemId}
    </update>

    <delete id="delete">
        DELETE FROM hq_settle_item WHERE item_id = #{itemId}
    </delete>

</mapper>
