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

    <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(e.total_payment_premium) * MAX(ef.default_exchange_rate) as totalPremiumAmount,
            count(distinct ef.policy_no) as totalPolicyCount
        from expected_fortune ef
        LEFT JOIN (
            SELECT p.policy_no, p.total_payment_premium
            FROM policy p
            GROUP BY policy_no
        ) e ON ef.policy_no = e.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>
        </where>
    </select>

    <select id="payableReportPage" resultType="com.yd.csf.service.vo.PayableReportVO">
        select
            null as payable_no,
            ef.policy_no as policyNo,
            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.amount), 0) as amount,
            ifnull(sum(ef.hkd_amount), 0) as hkdAmount,
            MAX(ef.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,
            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
            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.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.amount, 0) as amount,
            ifnull(ef.hkd_amount, 0) as hkdAmount,
            ef.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.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>
</mapper>
