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

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

    <!-- 목록/단건 조회 컬럼 — 지점장명·조직명·공통코드명 조인 포함 -->
    <sql id="ledgerJoinCols">
        l.ledger_id,
        l.manager_agent_id, a.agent_name AS manager_agent_name,
        l.org_id, o.org_name,
        l.settle_month,
        l.base_amount, l.allowance_rate, l.allowance_amount,
        l.clawback_amount,
        l.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'SUPPORT_STATUS' AND cc.code = l.status) AS status_name
    </sql>

    <sql id="ledgerJoins">
        FROM branch_mgr_allowance_ledger l
        LEFT JOIN agent a        ON a.agent_id = l.manager_agent_id
        LEFT JOIN organization o ON o.org_id   = l.org_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="managerAgentId != null">AND l.manager_agent_id = #{managerAgentId}</if>
            <if test="managerAgentName != null and managerAgentName != ''">AND a.agent_name LIKE '%' || #{managerAgentName} || '%'</if>
            <if test="orgId != null">AND l.org_id = #{orgId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND l.settle_month = #{settleMonth}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.manager_agent_id, l.ledger_id DESC
    </select>

    <!-- 지점장 + 정산월 수당 합계 — CANCELLED 제외 -->
    <select id="sumAllowanceByManagerAndMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(allowance_amount), 0)
          FROM branch_mgr_allowance_ledger
         WHERE manager_agent_id = #{managerAgentId}
           AND settle_month     = #{settleMonth}
           AND status           != 'CANCELLED'
    </select>

    <!-- allowance_amount 는 Service가 계산(LEAST 적용) 후 VO에 set, status 기본 CALCULATED -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO branch_mgr_allowance_ledger
            (manager_agent_id, org_id, settle_month, base_amount,
             allowance_rate, allowance_amount, clawback_amount, status)
        VALUES
            (#{managerAgentId}, #{orgId}, #{settleMonth}, #{baseAmount},
             #{allowanceRate}, #{allowanceAmount}, COALESCE(#{clawbackAmount}, 0),
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

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

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

    <!-- 정산월 기준 지점장별 순수당 합계 (allowance_amount - COALESCE(clawback_amount,0)) -->
    <select id="aggregateByAgent" resultType="map">
        SELECT manager_agent_id AS "agentId",
               SUM(allowance_amount - COALESCE(clawback_amount, 0)) AS "total"
          FROM branch_mgr_allowance_ledger
         WHERE settle_month = #{settleMonth}
           AND status != 'CANCELLED'
         GROUP BY manager_agent_id
    </select>

</mapper>
