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

    <!-- VO ResultMap: INSERT/UPDATE 용 -->
    <resultMap id="VOMap" type="com.ga.core.vo.commission.CommissionMasterVO">
        <id  property="masterId"       column="master_id"/>
        <result property="agentId"         column="agent_id"/>
        <result property="commissionType"  column="commission_type"/>
        <result property="settleMonth"     column="settle_month"/>
        <result property="sourceTable"     column="source_table"/>
        <result property="sourceId"        column="source_id"/>
        <result property="grossAmount"     column="gross_amount"/>
        <result property="taxAmount"       column="tax_amount"/>
        <result property="netAmount"       column="net_amount"/>
        <result property="status"          column="status"/>
        <result property="createdAt"       column="created_at"/>
        <result property="updatedAt"       column="updated_at"/>
    </resultMap>

    <!-- 목록/상세 조회 ResultMap: 조인 필드 포함 -->
    <resultMap id="RespMap" type="com.ga.core.vo.commission.CommissionMasterResp"/>

    <!-- 공통 SELECT 컬럼 — 조인 포함 -->
    <sql id="masterJoinCols">
        cm.master_id, cm.agent_id, a.agent_name,
        o.org_name,
        cm.commission_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'COMMISSION_TYPE' AND cc.code = cm.commission_type) AS commission_type_name,
        cm.settle_month,
        cm.source_table, cm.source_id,
        cm.gross_amount, cm.tax_amount, cm.net_amount,
        cm.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'MASTER_STATUS' AND cc.code = cm.status) AS status_name,
        cm.created_at
    </sql>

    <!-- 공통 JOIN -->
    <sql id="masterJoins">
        FROM commission_master cm
        LEFT JOIN agent a         ON a.agent_id = cm.agent_id
        LEFT JOIN organization o  ON o.org_id = a.org_id
    </sql>

    <!-- 동적 WHERE -->
    <sql id="masterWhere">
        <where>
            <if test="agentId != null">AND cm.agent_id = #{agentId}</if>
            <if test="commissionType != null and commissionType != ''">AND cm.commission_type = #{commissionType}</if>
            <if test="settleMonth != null and settleMonth != ''">AND cm.settle_month = #{settleMonth}</if>
            <if test="sourceTable != null and sourceTable != ''">AND cm.source_table = #{sourceTable}</if>
            <if test="status != null and status != ''">AND cm.status = #{status}</if>
        </where>
    </sql>

    <!-- 단건 조회 -->
    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="masterJoinCols"/>
        <include refid="masterJoins"/>
        WHERE cm.master_id = #{masterId}
    </select>

    <!-- 목록 조회 (PageHelper 페이징) -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="masterJoinCols"/>
        <include refid="masterJoins"/>
        <include refid="masterWhere"/>
        ORDER BY cm.settle_month DESC, cm.commission_type, cm.master_id DESC
    </select>

    <!-- 설계사 + 정산월 기준 목록 -->
    <select id="selectByAgentAndMonth" resultMap="RespMap">
        SELECT <include refid="masterJoinCols"/>
        <include refid="masterJoins"/>
        WHERE cm.agent_id = #{agentId}
          AND cm.settle_month = #{settleMonth}
        ORDER BY cm.commission_type
    </select>

    <!-- 정산월 수수료 종류별 집계 (종류별 SUM) -->
    <select id="selectAggregatedByMonth" resultType="com.ga.core.vo.commission.CommissionMasterResp">
        SELECT cm.commission_type,
               (SELECT cc.code_name FROM common_code cc
                   WHERE cc.group_code = 'COMMISSION_TYPE' AND cc.code = cm.commission_type) AS commission_type_name,
               cm.settle_month,
               SUM(cm.gross_amount) AS gross_amount,
               SUM(cm.tax_amount)   AS tax_amount,
               SUM(cm.net_amount)   AS net_amount,
               COUNT(*)             AS master_id   -- 재사용 필드로 건수 반환
        FROM commission_master cm
        WHERE cm.settle_month = #{settleMonth}
        GROUP BY cm.commission_type, cm.settle_month
        ORDER BY cm.commission_type
    </select>

    <!-- INSERT -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="masterId">
        INSERT INTO commission_master
            (agent_id, commission_type, settle_month, source_table, source_id,
             gross_amount, tax_amount, net_amount, status)
        VALUES
            (#{agentId}, #{commissionType}, #{settleMonth}, #{sourceTable}, #{sourceId},
             #{grossAmount}, #{taxAmount}, #{netAmount},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

    <!--
        UPSERT — source_table + source_id + commission_type + agent_id 의 UNIQUE 제약 기반.
        배치에서 원장 처리 후 commission_master 에 결과를 반영할 때 사용.
        금액이 바뀐 경우에만 updated_at 갱신.
    -->
    <insert id="upsertFromLedger">
        INSERT INTO commission_master
            (agent_id, commission_type, settle_month, source_table, source_id,
             gross_amount, tax_amount, net_amount, status)
        VALUES
            (#{agentId}, #{commissionType}, #{settleMonth}, #{sourceTable}, #{sourceId},
             #{grossAmount}, #{taxAmount}, #{netAmount},
             COALESCE(#{status}, 'CALCULATED'))
        ON CONFLICT (agent_id, commission_type, settle_month, source_id)
        DO UPDATE SET
            gross_amount = EXCLUDED.gross_amount,
            tax_amount   = EXCLUDED.tax_amount,
            net_amount   = EXCLUDED.net_amount,
            status       = EXCLUDED.status,
            updated_at   = NOW()
    </insert>

    <!-- 상태 업데이트 -->
    <update id="updateStatus">
        UPDATE commission_master
           SET status = #{status}, updated_at = NOW()
         WHERE master_id = #{masterId}
    </update>

    <!-- 삭제 (CANCELLED 처리 권장, 물리 삭제는 관리자 전용) -->
    <delete id="delete">
        DELETE FROM commission_master WHERE master_id = #{masterId}
    </delete>

</mapper>
