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

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

    <sql id="joinCols">
        ol.ledger_id,
        ol.from_agent_id, fa.agent_name AS from_agent_name,
        ol.to_agent_id,   ta.agent_name AS to_agent_name,
        o.org_name,
        ol.settle_month,
        ol.source_commission_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'COMMISSION_TYPE' AND cc.code = ol.source_commission_type) AS source_commission_type_name,
        ol.source_ledger_table, ol.source_ledger_id,
        ol.source_amount, ol.override_rate, ol.override_amount,
        ol.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'MASTER_STATUS' AND cc.code = ol.status) AS status_name
    </sql>

    <sql id="joins">
        FROM override_ledger ol
        LEFT JOIN agent fa       ON fa.agent_id = ol.from_agent_id
        LEFT JOIN agent ta       ON ta.agent_id = ol.to_agent_id
        LEFT JOIN organization o ON o.org_id = ol.org_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joins"/>
        WHERE ol.ledger_id = #{ledgerId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joins"/>
        <where>
            <if test="toAgentId != null">AND ol.to_agent_id = #{toAgentId}</if>
            <if test="fromAgentId != null">AND ol.from_agent_id = #{fromAgentId}</if>
            <if test="orgId != null">AND ol.org_id = #{orgId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND ol.settle_month = #{settleMonth}</if>
            <if test="sourceCommissionType != null and sourceCommissionType != ''">
                AND ol.source_commission_type = #{sourceCommissionType}
            </if>
            <if test="status != null and status != ''">AND ol.status = #{status}</if>
        </where>
        ORDER BY ol.settle_month DESC, ol.to_agent_id, ol.ledger_id DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO override_ledger
            (from_agent_id, to_agent_id, org_id, settle_month,
             source_commission_type, source_ledger_table, source_ledger_id,
             source_amount, override_rate, override_amount, status)
        VALUES
            (#{fromAgentId}, #{toAgentId}, #{orgId}, #{settleMonth},
             #{sourceCommissionType}, #{sourceLedgerTable}, #{sourceLedgerId},
             #{sourceAmount}, #{overrideRate}, #{overrideAmount},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

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

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

</mapper>
