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

    <resultMap id="VOMap"   type="com.ga.core.vo.ledger.LedgerVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.ledger.LedgerResp"/>
    <resultMap id="ExcelMap" type="com.ga.core.vo.ledger.LedgerExcelVO"/>

    <sql id="ledgerJoinCols">
        l.ledger_id, l.contract_id, l.agent_id, a.agent_name, o.org_name,
        l.policy_no, ct.contractor_name, ct.contract_date,
        l.company_code, ic.company_name,
        l.product_code, p.product_name,
        l.insurance_type,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='INSURANCE_TYPE' AND cc.code=l.insurance_type) AS insurance_type_name,
        l.commission_year, l.premium, l.pay_method,
        l.company_rate, l.company_amount, l.payout_rate, l.agent_amount, l.tax_amount,
        l.reconcile_status, l.reconcile_diff, l.advance_yn, l.chargeback_yn,
        l.settle_month, l.status,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='LEDGER_STATUS' AND cc.code=l.status) AS status_name
    </sql>

    <sql id="ledgerJoins">
        FROM recruit_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 contract ct        ON ct.contract_id = l.contract_id
        LEFT JOIN insurance_company ic ON ic.company_code = l.company_code
        LEFT JOIN product p          ON p.product_id = ct.product_id
    </sql>

    <sql id="ledgerWhere">
        <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="companyCode != null and companyCode != ''">AND l.company_code = #{companyCode}</if>
            <if test="insuranceType != null and insuranceType != ''">AND l.insurance_type = #{insuranceType}</if>
            <if test="reconcileStatus != null and reconcileStatus != ''">AND l.reconcile_status = #{reconcileStatus}</if>
            <if test="chargebackYn != null and chargebackYn != ''">AND l.chargeback_yn = #{chargebackYn}</if>
            <if test="advanceYn != null and advanceYn != ''">AND l.advance_yn = #{advanceYn}</if>
            <if test="policyNo != null and policyNo != ''">AND l.policy_no = #{policyNo}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        <include refid="ledgerWhere"/>
        ORDER BY l.settle_month DESC, l.agent_id, l.ledger_id DESC
    </select>

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

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

    <select id="selectCursorForExcel" resultMap="ExcelMap" fetchSize="500" resultSetType="FORWARD_ONLY">
        SELECT l.settle_month, l.policy_no, a.agent_name, o.org_name,
               ic.company_name, p.product_name, l.insurance_type, l.premium,
               l.company_rate, l.company_amount, l.payout_rate, l.agent_amount, l.tax_amount
        <include refid="ledgerJoins"/>
        <include refid="ledgerWhere"/>
        ORDER BY l.ledger_id
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.ledger.LedgerVO" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO recruit_ledger (contract_id, agent_id, policy_no, company_code, product_code,
            insurance_type, commission_year, premium, pay_method, company_rate, company_amount,
            payout_rate, agent_amount, tax_amount, reconcile_status, reconcile_diff,
            advance_yn, advance_amount, chargeback_yn, settle_month, status, version, created_at)
        VALUES (#{contractId}, #{agentId}, #{policyNo}, #{companyCode}, #{productCode},
                #{insuranceType}, #{commissionYear}, #{premium}, #{payMethod},
                #{companyRate}, #{companyAmount}, #{payoutRate}, #{agentAmount}, #{taxAmount},
                COALESCE(#{reconcileStatus},'PENDING'), #{reconcileDiff},
                COALESCE(#{advanceYn},'N'), #{advanceAmount}, COALESCE(#{chargebackYn},'N'),
                #{settleMonth}, COALESCE(#{status},'CALCULATED'), COALESCE(#{version},1),
                #{createdAt})
    </insert>

    <insert id="batchInsert">
        INSERT INTO recruit_ledger (contract_id, agent_id, policy_no, company_code, product_code,
            insurance_type, commission_year, premium, pay_method, company_rate, company_amount,
            payout_rate, agent_amount, tax_amount, settle_month, status, version, created_at)
        VALUES
        <foreach collection="list" item="r" separator=",">
            (#{r.contractId}, #{r.agentId}, #{r.policyNo}, #{r.companyCode}, #{r.productCode},
             #{r.insuranceType}, #{r.commissionYear}, #{r.premium}, #{r.payMethod},
             #{r.companyRate}, #{r.companyAmount}, #{r.payoutRate}, #{r.agentAmount}, #{r.taxAmount},
             #{r.settleMonth}, COALESCE(#{r.status},'CALCULATED'), 1, NOW())
        </foreach>
    </insert>

    <update id="update" parameterType="com.ga.core.vo.ledger.LedgerVO">
        UPDATE recruit_ledger SET
            company_rate = #{companyRate}, company_amount = #{companyAmount},
            payout_rate = #{payoutRate}, agent_amount = #{agentAmount}, tax_amount = #{taxAmount},
            reconcile_status = #{reconcileStatus}, reconcile_diff = #{reconcileDiff},
            chargeback_yn = #{chargebackYn}, status = #{status},
            version = version + 1, updated_at = NOW()
        WHERE ledger_id = #{ledgerId} AND version = #{version}
    </update>

    <delete id="deleteBySettleMonth">
        DELETE FROM recruit_ledger WHERE settle_month = #{settleMonth} AND status = 'CALCULATED'
    </delete>

    <select id="aggregateByAgent" resultType="map">
        SELECT agent_id   AS "agentId",
               SUM(agent_amount) AS "total"
        FROM recruit_ledger
        WHERE settle_month = #{settleMonth}
        GROUP BY agent_id
    </select>

    <select id="sumByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(agent_amount), 0) FROM recruit_ledger WHERE settle_month = #{settleMonth}
    </select>

    <select id="sumByAgentMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(agent_amount), 0) FROM recruit_ledger
        WHERE agent_id = #{agentId} AND settle_month = #{settleMonth}
    </select>

    <select id="sumByAgentMonthAndType" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(agent_amount), 0) FROM recruit_ledger
        WHERE agent_id = #{agentId} AND settle_month = #{settleMonth}
          AND insurance_type = #{insuranceType}
    </select>

    <select id="sumByContract" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(agent_amount), 0) FROM recruit_ledger
        WHERE contract_id = #{contractId}
    </select>

    <select id="sumFirstYearByContract" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(agent_amount), 0) FROM recruit_ledger
        WHERE contract_id = #{contractId} AND commission_year = 1
    </select>

</mapper>
