<?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.settle.SettleMasterMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.settle.SettleMasterVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.settle.SettleMasterResp"/>

    <sql id="cols">
        s.settle_id, s.agent_id, a.agent_name, o.org_name, g.grade_name,
        s.settle_month, s.recruit_total, s.maintain_total,
        s.exception_plus, s.exception_minus, s.override_total, s.chargeback_total,
        s.gross_amount, s.tax_amount, s.net_amount,
        s.clawback_recovered, s.clawback_carryover, s.payable_amount,
        s.status,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='SETTLE_STATUS' AND cc.code=s.status) AS status_name,
        s.calc_date, s.confirmed_by, u.user_name AS confirmed_by_name,
        s.confirmed_at, s.hold_reason, s.hold_reason_code,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='HOLD_REASON' AND cc.code=s.hold_reason_code) AS hold_reason_name
    </sql>

    <sql id="joins">
        FROM settle_master s
        LEFT JOIN agent a        ON a.agent_id = s.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        LEFT JOIN grade g        ON g.grade_id = a.grade_id
        LEFT JOIN users u        ON u.user_id = s.confirmed_by
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="cols"/>
        <include refid="joins"/>
        <where>
            <if test="settleMonth != null and settleMonth != ''">AND s.settle_month = #{settleMonth}</if>
            <if test="agentId != null">AND s.agent_id = #{agentId}</if>
            <if test="status != null and status != ''">AND s.status = #{status}</if>
            <if test="gradeId != null">AND a.grade_id = #{gradeId}</if>
            <if test="orgId != null">
                <choose>
                    <when test="includeSubOrgs == true">
                        AND a.org_id IN (
                            WITH RECURSIVE d AS (
                                SELECT org_id FROM organization WHERE org_id = #{orgId}
                                UNION ALL
                                SELECT o2.org_id FROM organization o2 JOIN d ON o2.parent_org_id = d.org_id
                            ) SELECT org_id FROM d
                        )
                    </when>
                    <otherwise>
                        AND a.org_id = #{orgId}
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY s.settle_month DESC, a.agent_id
    </select>

    <select id="selectDetail" resultMap="RespMap">
        SELECT <include refid="cols"/>
        <include refid="joins"/>
        WHERE s.agent_id = #{agentId} AND s.settle_month = #{settleMonth}
    </select>

    <select id="selectByAgentAndMonth" resultMap="VOMap">
        SELECT * FROM settle_master WHERE agent_id = #{agentId} AND settle_month = #{settleMonth}
    </select>

    <select id="selectById" resultMap="VOMap">
        SELECT * FROM settle_master WHERE settle_id = #{settleId}
    </select>

    <insert id="upsert" parameterType="com.ga.core.vo.settle.SettleMasterVO">
        INSERT INTO settle_master (agent_id, settle_month, recruit_total, maintain_total,
            exception_plus, exception_minus, override_total, chargeback_total,
            other_commission_total,
            gross_amount, tax_amount, net_amount,
            clawback_recovered, clawback_carryover, payable_amount,
            status, calc_date)
        VALUES (#{agentId}, #{settleMonth},
            COALESCE(#{recruitTotal}, 0), COALESCE(#{maintainTotal}, 0),
            COALESCE(#{exceptionPlus}, 0), COALESCE(#{exceptionMinus}, 0),
            COALESCE(#{overrideTotal}, 0), COALESCE(#{chargebackTotal}, 0),
            COALESCE(#{otherCommissionTotal}, 0),
            COALESCE(#{grossAmount}, 0), COALESCE(#{taxAmount}, 0), COALESCE(#{netAmount}, 0),
            COALESCE(#{clawbackRecovered}, 0), COALESCE(#{clawbackCarryover}, 0), COALESCE(#{payableAmount}, 0),
            COALESCE(#{status},'CALCULATED'), NOW())
        ON CONFLICT (agent_id, settle_month) DO UPDATE SET
            recruit_total          = EXCLUDED.recruit_total,
            maintain_total         = EXCLUDED.maintain_total,
            exception_plus         = EXCLUDED.exception_plus,
            exception_minus        = EXCLUDED.exception_minus,
            override_total         = EXCLUDED.override_total,
            chargeback_total       = EXCLUDED.chargeback_total,
            other_commission_total = EXCLUDED.other_commission_total,
            gross_amount           = EXCLUDED.gross_amount,
            tax_amount             = EXCLUDED.tax_amount,
            net_amount             = EXCLUDED.net_amount,
            clawback_recovered     = EXCLUDED.clawback_recovered,
            clawback_carryover     = EXCLUDED.clawback_carryover,
            payable_amount         = EXCLUDED.payable_amount,
            status                 = EXCLUDED.status,
            calc_date              = NOW()
        WHERE settle_master.status NOT IN ('CONFIRMED','PAID')
    </insert>

    <update id="updateStatus">
        UPDATE settle_master SET
            status = #{status},
            confirmed_by = #{userId},
            confirmed_at = CASE WHEN #{status} = 'CONFIRMED' THEN NOW() ELSE confirmed_at END
        WHERE settle_id = #{settleId}
    </update>

    <update id="updateHold">
        UPDATE settle_master SET
            status = 'HOLD',
            hold_reason      = #{reason},
            hold_reason_code = #{reasonCode}
        WHERE settle_id = #{settleId}
    </update>

    <select id="selectMonthSummary" resultType="map">
        SELECT
            COUNT(*)                           AS "agentCount",
            SUM(recruit_total)                 AS "recruitTotal",
            SUM(maintain_total)                AS "maintainTotal",
            SUM(exception_plus)                AS "exceptionPlus",
            SUM(exception_minus)               AS "exceptionMinus",
            SUM(override_total)                AS "overrideTotal",
            SUM(chargeback_total)              AS "chargebackTotal",
            SUM(gross_amount)                  AS "grossAmount",
            SUM(tax_amount)                    AS "taxAmount",
            SUM(net_amount)                    AS "netAmount",
            SUM(CASE WHEN status='CONFIRMED' THEN 1 ELSE 0 END) AS "confirmedCount",
            SUM(CASE WHEN status='HOLD'      THEN 1 ELSE 0 END) AS "holdCount",
            SUM(CASE WHEN status='PAID'      THEN 1 ELSE 0 END) AS "paidCount"
        FROM settle_master WHERE settle_month = #{settleMonth}
    </select>

    <select id="selectOrgSummary" resultType="map">
        SELECT o.org_id    AS "orgId",
               o.org_name  AS "orgName",
               COUNT(s.settle_id) AS "agentCount",
               COALESCE(SUM(s.gross_amount),0) AS "grossAmount",
               COALESCE(SUM(s.net_amount),0)   AS "netAmount"
        FROM settle_master s
        JOIN agent a        ON a.agent_id = s.agent_id
        JOIN organization o ON o.org_id = a.org_id
        WHERE s.settle_month = #{settleMonth}
        GROUP BY o.org_id, o.org_name
        ORDER BY o.org_id
    </select>

</mapper>
