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

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

    <!-- 목록/단건 조회 컬럼 — 도입자·피도입 설계사명·공통코드명 조인 포함 -->
    <sql id="ledgerJoinCols">
        l.ledger_id,
        l.recruiter_agent_id,
        ra.agent_name  AS recruiter_agent_name,
        l.recruit_agent_id,
        na.agent_name  AS recruit_agent_name,
        l.settle_month,
        l.recruiter_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'RECRUITER_TYPE' AND cc.code = l.recruiter_type) AS recruiter_type_name,
        l.base_amount,
        l.commission_amount,
        l.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'INCOME_STATUS' AND cc.code = l.status) AS status_name
    </sql>

    <sql id="ledgerJoins">
        FROM recruiter_commission_ledger l
        LEFT JOIN agent ra ON ra.agent_id = l.recruiter_agent_id
        LEFT JOIN agent na ON na.agent_id = l.recruit_agent_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        WHERE l.ledger_id = #{ledgerId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        <where>
            <if test="recruiterAgentId != null">AND l.recruiter_agent_id = #{recruiterAgentId}</if>
            <if test="recruitAgentId != null">AND l.recruit_agent_id = #{recruitAgentId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND l.settle_month = #{settleMonth}</if>
            <if test="recruiterType != null and recruiterType != ''">AND l.recruiter_type = #{recruiterType}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.recruiter_agent_id, l.ledger_id DESC
    </select>

    <!-- status 기본값 'CALCULATED' -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO recruiter_commission_ledger
            (recruiter_agent_id, recruit_agent_id, settle_month, recruiter_type,
             base_amount, commission_amount, status, created_by, created_at)
        VALUES
            (#{recruiterAgentId}, #{recruitAgentId}, #{settleMonth}, #{recruiterType},
             #{baseAmount}, #{commissionAmount},
             COALESCE(#{status}, 'CALCULATED'), #{createdBy}, NOW())
    </insert>

    <update id="updateStatus">
        UPDATE recruiter_commission_ledger
           SET status = #{status}, updated_at = NOW()
         WHERE ledger_id = #{ledgerId}
    </update>

    <delete id="delete">
        DELETE FROM recruiter_commission_ledger WHERE ledger_id = #{ledgerId}
    </delete>

    <!-- 정산월 기준 도입자 설계사별 도입수수료 합계 — CANCELLED 제외, agentId alias -->
    <select id="aggregateByAgent" resultType="map">
        SELECT recruiter_agent_id  AS "agentId",
               SUM(commission_amount) AS "total"
          FROM recruiter_commission_ledger
         WHERE settle_month = #{settleMonth}
           AND status != 'CANCELLED'
         GROUP BY recruiter_agent_id
    </select>

</mapper>
