<?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.ledger.ExceptionLedgerMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.ledger.ExceptionLedgerVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.ledger.ExceptionLedgerResp"/>

    <sql id="cols">
        l.ledger_id, l.contract_id, l.agent_id, a.agent_name, o.org_name,
        l.exception_code, ec.exception_name, ec.direction,
        l.policy_no, l.company_code, l.insurance_type,
        l.amount, l.tax_amount, l.settle_month, l.description,
        l.approve_status,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='APPROVE_STATUS' AND cc.code=l.approve_status) AS approve_status_name,
        l.approver_id, u.user_name AS approver_name, l.approve_date,
        l.recurring_yn, l.recurring_left, l.status
    </sql>

    <sql id="joins">
        FROM exception_ledger l
        LEFT JOIN agent a            ON a.agent_id = l.agent_id
        LEFT JOIN organization o     ON o.org_id = a.org_id
        LEFT JOIN exception_type_code ec ON ec.exception_code = l.exception_code
        LEFT JOIN users u            ON u.user_id = l.approver_id
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="cols"/> <include refid="joins"/>
        <where>
            <if test="settleMonth != null and settleMonth != ''">AND l.settle_month = #{settleMonth}</if>
            <if test="agentId != null">AND l.agent_id = #{agentId}</if>
            <if test="orgId != null">AND a.org_id = #{orgId}</if>
            <if test="status != null and status != ''">AND l.approve_status = #{status}</if>
        </where>
        ORDER BY l.created_at DESC, l.ledger_id DESC
    </select>

    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="cols"/> <include refid="joins"/>
        WHERE l.ledger_id = #{ledgerId}
    </select>

    <select id="selectById" resultMap="VOMap">
        SELECT * FROM exception_ledger WHERE ledger_id = #{ledgerId}
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.ledger.ExceptionLedgerVO"
            useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO exception_ledger (contract_id, agent_id, exception_code, policy_no,
            company_code, insurance_type, commission_year, amount, tax_amount,
            settle_month, description, approve_status, recurring_yn, recurring_months,
            recurring_left, status, version, created_at)
        VALUES (#{contractId}, #{agentId}, #{exceptionCode}, #{policyNo},
                #{companyCode}, #{insuranceType}, #{commissionYear}, #{amount}, #{taxAmount},
                #{settleMonth}, #{description}, COALESCE(#{approveStatus},'PENDING'),
                COALESCE(#{recurringYn},'N'), #{recurringMonths}, #{recurringLeft},
                COALESCE(#{status},'NEW'), 1, #{createdAt})
    </insert>

    <update id="update" parameterType="com.ga.core.vo.ledger.ExceptionLedgerVO">
        UPDATE exception_ledger SET
            amount = #{amount}, tax_amount = #{taxAmount},
            description = #{description}, status = #{status},
            version = version + 1, updated_at = NOW()
        WHERE ledger_id = #{ledgerId} AND version = #{version}
    </update>

    <update id="updateApprove">
        UPDATE exception_ledger SET
            approve_status = #{status},
            approver_id = #{approverId},
            approve_date = NOW(),
            updated_at = NOW()
        WHERE ledger_id = #{ledgerId}
    </update>

    <update id="decrementRecurring">
        UPDATE exception_ledger SET recurring_left = recurring_left - 1
        WHERE ledger_id = #{ledgerId} AND recurring_left &gt; 0
    </update>

    <select id="aggregateByAgent" resultType="map">
        SELECT l.agent_id AS "agentId",
               SUM(CASE WHEN ec.direction = 'PLUS'  THEN l.amount ELSE 0 END) AS "plus",
               SUM(CASE WHEN ec.direction = 'MINUS' THEN l.amount ELSE 0 END) AS "minus"
        FROM exception_ledger l
        JOIN exception_type_code ec ON ec.exception_code = l.exception_code
        WHERE l.settle_month = #{settleMonth} AND l.approve_status = 'APPROVED'
        GROUP BY l.agent_id
    </select>

    <select id="sumPlusByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(l.amount), 0)
        FROM exception_ledger l
        JOIN exception_type_code ec ON ec.exception_code = l.exception_code
        WHERE l.settle_month = #{settleMonth} AND ec.direction = 'PLUS' AND l.approve_status = 'APPROVED'
    </select>

    <select id="sumMinusByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(l.amount), 0)
        FROM exception_ledger l
        JOIN exception_type_code ec ON ec.exception_code = l.exception_code
        WHERE l.settle_month = #{settleMonth} AND ec.direction = 'MINUS' AND l.approve_status = 'APPROVED'
    </select>

</mapper>
