<?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,
            MAX(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.payout_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(pf.first_broker) as broker,
            MAX(pf.first_broker_biz_id) as brokerBizId,
            MAX(ef.payout_date) as payoutDate
        from expected_fortune ef
        left join policy p on ef.policy_no = p.policy_no
        left join policy_follow pf on ef.policy_no = pf.policy_no
        <where>
            ef.fortune_biz_type = 'R'
            and ef.is_deleted = 0
            AND ef.is_part in (0,1)
            <if test="ew != null and ew.sqlSegment != null and ew.sqlSegment != ''">
                AND ef.id IN (
                    SELECT ef2.id FROM expected_fortune ef2
                    WHERE ${ew.sqlSegment}
                )
            </if>
        </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.payout_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,
            NULL as broker,
            NULL 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'
            and ef.is_deleted = 0
            AND ef.is_part in (0,1)
            <if test="ew != null and ew.sqlSegment != null and ew.sqlSegment != ''">
                AND ef.id IN (
                    SELECT ef2.id FROM expected_fortune ef2
                    WHERE ${ew.sqlSegment}
                )
            </if>
        </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,
            NULL as currentPaymentRuleAmount,
            NULL as currentPayoutAmount,
            NULL as currentPaymentRatio,
            ef.payable_no,
            ef.policy_no,
            ef.premium,
            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.amount_source_name,
            ef.fortune_name,
            ef.fortune_type,
            ef.broker_ratio,
            ef.rule_amount,
            ef.rule_currency,
            ef.status,
            ef.payout_date,
            NULL as actual_payout_date,
            ef.default_exchange_rate as exchangeRate,
            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,
            ef.hkd_amount as current_payment_hkd_amount,
            ef.paid_amount,
            ef.paid_ratio,
            ef.unpaid_ratio,
            ef.paid_rule_amount as paidRuleAmount,
            ef.unpaid_rule_amount as unpaidRuleAmount,
            ef.unpaid_rule_amount_hkd as unpaidRuleAmounthHkd,
            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.rule_amount as currentPaymentRuleAmount,
            f.current_payment_amount as currentPayoutAmount,
            f.current_payment_ratio as currentPaymentRatio,
            f.payable_no,
            f.policy_no,
            NULL as premium,
            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,
            NULL as amount_source_name,
            f.fortune_name,
            f.fortune_type,
            NULL as broker_ratio,
            f.rule_amount,
            f.rule_currency,
            f.status,
            f.payout_date,
            f.actual_payout_date,
            f.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_hkd_amount,
            NULL as paid_amount,
            NULL as paid_ratio,
            NULL as unpaid_ratio,
            NULL as paidRuleAmount,
            NULL as unpaidRuleAmount,
            NULL as unpaidRuleAmounthHkd,
            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 payable_no ASC, type ASC
        LIMIT #{pageSize} OFFSET #{offset}
    </select>

    <select id="queryListAll"
            resultType="com.yd.csf.feign.response.expectedfortune.ApiExpectedFortunePageResponse">

        SELECT
            ef.id,
            ef.expected_fortune_biz_id,
            ef.fortune_biz_type,
            NULL as currentPaymentRuleAmount,
            NULL as currentPayoutAmount,
            NULL as currentPaymentRatio,
            ef.payable_no,
            ef.policy_no,
            ef.premium,
            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.payout_date,
            ef.actual_payout_date,
            ef.default_exchange_rate as exchangeRate,
            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,
            ef.paid_amount,
            ef.paid_ratio,
            ef.unpaid_ratio,
            ef.paid_rule_amount as paidRuleAmount,
            ef.unpaid_rule_amount as unpaidRuleAmount,
            ef.unpaid_rule_amount_hkd as unpaidRuleAmounthHkd,
            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.rule_amount as currentPaymentRuleAmount,
            f.current_payment_amount as currentPayoutAmount,
            f.current_payment_ratio as currentPaymentRatio,
            f.payable_no,
            f.policy_no,
            NULL as premium,
            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,
            f.payout_date,
            f.actual_payout_date,
            f.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_hkd_amount,
            NULL as paid_amount,
            NULL as paid_ratio,
            NULL as unpaid_ratio,
            NULL as paidRuleAmount,
            NULL as unpaidRuleAmount,
            NULL as unpaidRuleAmounthHkd,
            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 fortune_type ASC, type ASC
    </select>

    <select id="queryListStatistics" resultType="com.yd.csf.service.vo.ExpectedFortuneStatisticsVO">
        WITH filtered_data AS (
            SELECT
                ef.hkd_amount,
                ef.paid_amount,
                ef.unpaid_amount,
                ef.policy_no,
                ef.default_exchange_rate
            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>
        ),
        premium_total AS (
            SELECT SUM(p.total_payment_premium * dp.default_exchange_rate) AS total_premium
            FROM (SELECT DISTINCT policy_no, default_exchange_rate FROM filtered_data) dp
            INNER JOIN policy p ON p.policy_no = dp.policy_no
            )
        SELECT
            COALESCE(SUM(fd.hkd_amount), 0) AS totalExpectedAmount,
            COALESCE(SUM(fd.paid_amount), 0) AS totalPaidAmount,
            COALESCE(SUM(fd.unpaid_amount), 0) AS totalUnpaidAmount,
            COALESCE(pt.total_premium, 0) AS totalPremiumAmount,
            COUNT(DISTINCT fd.policy_no) AS totalPolicyCount
            FROM filtered_data fd
        CROSS JOIN premium_total pt
    </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>
