<?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.yd.csf.service.dao.ExpectedFortuneMapper">
    <update id="updateBatchByBizId">
        update expected_fortune ef
        set ef.status = #{status}
        where ef.expected_fortune_biz_id in
        <foreach collection="expectedFortuneBizIdList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </update>

    <select id="page" resultType="com.yd.csf.feign.response.expectedfortune.ApiExpectedFortunePageResponse">
        select * from expected_fortune ef
        <where>
            <if test="request.broker != null and request.broker != ''">
                and ef.broker like concat('%', #{request.broker}, '%')
            </if>

            <if test="request.fortunePeriod != null">
                and ef.fortune_period = #{request.fortunePeriod}
            </if>

            and ef.policy_no = #{request.policyNo}
            and ef.is_deleted = 0
        </where>
    </select>

    <select id="listByPolicyBizId" resultType="com.yd.csf.service.model.ExpectedFortune">
        select *
        from expected_fortune ef
        where ef.policy_no = (select policy_no from policy where policy_biz_id = #{policyBizId})
    </select>

    <select id="getStatistics" resultType="com.yd.csf.service.vo.ExpectedFortuneStatisticsVO">
        select
            ifnull(sum(ef.hkd_amount),0) as totalExpectedAmount,
            ifnull(sum(ef.paid_amount),0) as totalPaidAmount,
            ifnull(sum(ef.unpaid_amount),0) as totalUnpaidAmount,
            MAX(p.total_payment_premium) * MAX(ef.default_exchange_rate) as totalPremiumAmount,
            count(distinct ef.policy_no) as totalPolicyCount
        from expected_fortune ef
        LEFT JOIN policy p ON ef.policy_no = p.policy_no
        <where>
            <if test="expectedFortuneIds != null and expectedFortuneIds.size > 0">
                ef.id in
                <foreach collection="expectedFortuneIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            AND ef.is_part in (0,1)
        </where>
    </select>

    <select id="payableReportPage" resultType="com.yd.csf.service.vo.PayableReportVO">
        select
            null as payable_no,
            ef.policy_no as policyNo,
            ef.policy_currency as policyCurrency,
            (select pp.name from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolder,
            (select pp.name_en from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolderEn,
            MAX(ef.premium) as premium,
            MAX(ef.insurance_company_biz_id) as insuranceCompanyBizId,
            MAX(p.insurance_company) as insuranceCompany,
            MAX(ef.product_launch_biz_id) as productLaunchBizId,
            MAX(p.product_name) as productName,
            ef.fortune_period as fortunePeriod,
            MAX(ef.fortune_total_period) as fortuneTotalPeriod,
            ifnull(sum(ef.original_amount), 0) as amount,
            ifnull(sum(ef.hkd_amount), 0) as hkdAmount,
            MAX(ef.original_currency) as currency,
            ifnull(sum(ef.paid_amount), 0) as paidAmount,
            ifnull(sum(ef.unpaid_amount), 0) as unpaidAmount,
            case when ifnull(sum(ef.hkd_amount), 0) > 0
                then round(ifnull(sum(ef.paid_amount), 0) / ifnull(sum(ef.hkd_amount), 0) * 100, 2)
                else 0
            end as paidRatio,
            case when ifnull(sum(ef.hkd_amount), 0) > 0
                then round(100 - (ifnull(sum(ef.paid_amount), 0) / ifnull(sum(ef.hkd_amount), 0) * 100), 2)
                else 100
            end as unpaidRatio,
            MAX(first_broker.broker_name) as broker,
            MAX(first_broker.broker_biz_id) as brokerBizId,
            DATE_ADD(DATE_ADD(MAX(p.effective_date), INTERVAL 2 MONTH), INTERVAL (ef.fortune_period - 1) YEAR) as payoutDate
        from expected_fortune ef
        left join policy p on ef.policy_no = p.policy_no
        left join (
            SELECT pb1.policy_no, pb1.broker_name,pb1.broker_biz_id
            FROM policy_broker pb1
            WHERE pb1.id = (
                SELECT pb2.id
                FROM policy_broker pb2
                WHERE pb2.policy_no = pb1.policy_no
                AND pb2.is_deleted = 0
                ORDER BY pb2.id ASC
                LIMIT 1
            )
            AND pb1.is_deleted = 0
        ) first_broker on ef.policy_no = first_broker.policy_no
        <where>
            ef.fortune_biz_type = 'R'
            <if test="expectedFortuneIds != null and expectedFortuneIds.size > 0">
                and ef.id in
                <foreach collection="expectedFortuneIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            and ef.is_deleted = 0
        </where>
        group by ef.policy_no, ef.fortune_period
        union all
        select
            ef.payable_no as payable_no,
            ef.policy_no as policyNo,
            ef.policy_currency as policyCurrency,
            (select pp.name from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolder,
            (select pp.name_en from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolderEn,
            ef.premium as premium,
            ef.insurance_company_biz_id as insuranceCompanyBizId,
            p.insurance_company as insuranceCompany,
            ef.product_launch_biz_id as productLaunchBizId,
            p.product_name as productName,
            ef.fortune_period as fortunePeriod,
            ef.fortune_total_period as fortuneTotalPeriod,
            ifnull(ef.original_amount, 0) as amount,
            ifnull(ef.hkd_amount, 0) as hkdAmount,
            ef.original_currency as currency,
            ifnull(ef.paid_amount, 0) as paidAmount,
            ifnull(ef.unpaid_amount, 0) as unpaidAmount,
            case when ifnull(ef.hkd_amount, 0) > 0
                then round(ifnull(ef.paid_amount, 0) / ifnull(ef.hkd_amount, 0) * 100, 2)
                else 0
            end as paidRatio,
            case when ifnull(ef.hkd_amount, 0) > 0
                then round(100 - (ifnull(ef.paid_amount, 0) / ifnull(ef.hkd_amount, 0) * 100), 2)
                else 100
            end as unpaidRatio,
            ef.broker      as broker,
            ef.broker_biz_id      as brokerBizId,
            ef.payout_date as payoutDate
        from expected_fortune ef
        left join policy p on ef.policy_no = p.policy_no
        <where>
            ef.fortune_biz_type = 'U'
            <if test="expectedFortuneIds != null and expectedFortuneIds.size > 0">
                and ef.id in
                <foreach collection="expectedFortuneIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            and ef.is_deleted = 0
        </where>
    </select>

    <select id="queryListNew"
            resultType="com.yd.csf.feign.response.expectedfortune.ApiExpectedFortunePageResponse">
        <bind name="offset" value="(pageNo - 1) * pageSize" />

        SELECT
            ef.id,
            ef.expected_fortune_biz_id,
            ef.fortune_biz_type,
            ef.is_part,
            ef.payable_no,
            ef.policy_no,
            ef.premium,
            ef.policy_currency,
            ef.insurance_company_biz_id,
            ef.product_launch_biz_id,
            ef.fortune_period,
            ef.fortune_total_period,
            ef.broker,
            ef.broker_biz_id,
            ef.team,
            ef.team_biz_id,
            ef.fortune_name,
            ef.fortune_type,
            ef.broker_ratio,
            ef.rule_amount,
            ef.rule_currency,
            ef.status,
            ef.status_desc,
            ef.payout_date,
            ef.actual_payout_date,
            ef.default_exchange_rate,
            ef.original_currency,
            ef.original_amount,
            ef.original_to_hkd_rate,
            ef.payout_currency,
            ef.payout_amount,
            ef.hkd_to_payout_rate,
            ef.hkd_amount,
            NULL as current_payment_hkd_amount,
            NULL as exchange_rate,
            ef.paid_amount,
            ef.unpaid_amount,
            ef.paid_ratio,
            ef.unpaid_ratio,
            ef.is_tax,
            ef.tax_amount,
            ef.net_amount,
            ef.rule_item_biz_id,
            ef.remark,
            ef.create_time,
            ef.creator_name,
            ef.update_time,
            1 as type
        FROM expected_fortune ef
        <where>
            ef.is_deleted = 0
            AND ef.is_part IN (0, 1)
            <if test="ew != null">
                <if test="ew.sqlSegment != null and ew.sqlSegment != ''">
                    AND ${ew.sqlSegment}
                </if>
            </if>
        </where>

        UNION ALL

        SELECT
            f.id,
            f.expected_fortune_biz_id,
            f.fortune_biz_type,
            f.is_part,
            f.payable_no,
            f.policy_no,
            NULL as premium,
            f.policy_currency,
            NULL as insurance_company_biz_id,
            NULL as product_launch_biz_id,
            f.fortune_period,
            f.fortune_total_period,
            f.broker,
            f.broker_biz_id,
            f.team,
            f.team_biz_id,
            f.fortune_name,
            f.fortune_type,
            NULL as broker_ratio,
            f.rule_amount,
            f.rule_currency,
            f.status,
            NULL as status_desc,
            f.payout_date,
            f.actual_payout_date,
            NUll as default_exchange_rate,
            f.original_currency,
            f.original_amount,
            f.original_to_hkd_rate,
            f.payout_currency,
            f.payout_amount,
            f.hkd_to_payout_rate,
            f.hkd_amount,
            f.current_payment_amount,
            f.exchange_rate,
            NULL as paid_amount,
            NULL as unpaid_amount,
            NULL as paid_ratio,
            NULL as unpaid_ratio,
            f.is_tax,
            f.tax_amount,
            f.net_amount,
            NULL as rule_item_biz_id,
            f.remark,
            f.create_time,
            f.reconciliation_operator as creator_name,
            f.update_time,
            2 as type
        FROM fortune f
        <where>
            f.is_deleted = 0
            AND f.is_part IN (0, 1)
            <if test="ew != null">
                <if test="ew.sqlSegment != null and ew.sqlSegment != ''">
                    AND ${ew.sqlSegment}
                </if>
            </if>
        </where>

        ORDER BY id ASC
        LIMIT #{pageSize} OFFSET #{offset}
    </select>

    <select id="queryListStatistics" resultType="com.yd.csf.service.vo.ExpectedFortuneStatisticsVO">
        SELECT
            COALESCE(SUM(hkd_amount), 0) AS totalExpectedAmount,
            COALESCE(SUM(paid_amount), 0) AS totalPaidAmount,
            COALESCE(SUM(unpaid_amount), 0) AS totalUnpaidAmount,
            COALESCE(totalPremiumAmount, 0) AS totalPremiumAmount,
            COUNT(DISTINCT policy_no) AS totalPolicyCount
        FROM (
            SELECT
                ef.hkd_amount,
                ef.paid_amount,
                ef.unpaid_amount,
                ef.policy_no,
                (SELECT p.total_payment_premium * ef.default_exchange_rate
                 FROM policy p
                 WHERE p.policy_no = ef.policy_no
                ) AS totalPremiumAmount
            FROM expected_fortune ef
            WHERE ef.is_deleted = 0
              AND ef.is_part IN (0, 1)
            <if test="ew != null">
                <if test="ew.sqlSegment != null and ew.sqlSegment != ''">
                    AND ${ew.sqlSegment}
                </if>
            </if>
        ) AS statistics
    </select>

    <select id="queryListNewCount" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM (
            SELECT ef.id
            FROM expected_fortune ef
            <where>
                ef.is_deleted = 0
                AND ef.is_part IN (0, 1)
                <if test="ew != null">
                    <if test="ew.sqlSegment != null and ew.sqlSegment != ''">
                        AND ${ew.sqlSegment}
                    </if>
                </if>
            </where>

            UNION ALL

            SELECT f.id
            FROM fortune f
            <where>
                f.is_deleted = 0
                AND f.is_part IN (0, 1)
                <if test="ew != null">
                    <if test="ew.sqlSegment != null and ew.sqlSegment != ''">
                        AND ${ew.sqlSegment}
                    </if>
                </if>
            </where>
        ) AS combined
    </select>
</mapper>
