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

    <select id="page"
            resultType="com.yd.csf.feign.response.premiumreconciliation.ApiPremiumReconciliationPageResponse">
        SELECT
        pr.premium_reconciliation_biz_id,
        pr.current_issue_number,
        pr.reconciliation_type,
        pf.insurance_company AS insuranceCompany,
        pr.policy_no,
        pf.status AS policyFollowStatus,
        pr.reconciliation_status,
        p.status AS policyStatus,
        pr.remaining_unpaid_amount,
        pr.remaining_unpaid_currency,
        COALESCE(prm_total.payment_amount, 0) AS paymentAmount,
        prm_total.payment_currency AS paymentCurrency,
        prm_total.payment_method AS paymentMethod,
        pr.recognized_amount,
        pr.recognized_currency,
        prm_total.payer,
        prm_total.paying_bank,
        pp.name AS policyHolder,
        pp.name_en AS policyHolderEn,
        pi.name AS insured,
        pi.name_en AS insuredEn,
        pb.broker_name AS brokerName,
        pr.create_time,
        pr.update_time,
        pf.reconciliation_company_biz_id
        FROM premium_reconciliation pr
        LEFT JOIN policy p ON p.policy_no = pr.policy_no AND p.is_deleted = 0
        LEFT JOIN policy_follow pf ON pf.policy_no = pr.policy_no AND pf.is_deleted = 0
        LEFT JOIN policy_policyholder pp ON pp.policy_biz_id = pf.policy_biz_id AND pp.is_deleted = 0
        LEFT JOIN policy_insurant pi ON pi.policy_biz_id = pf.policy_biz_id AND pi.is_deleted = 0
        LEFT JOIN (
        SELECT
        premium_reconciliation_biz_id,
        SUM(payment_amount) AS payment_amount,
        MAX(payment_currency) AS payment_currency,
        GROUP_CONCAT(DISTINCT payment_method SEPARATOR ';') AS payment_method,
        GROUP_CONCAT(DISTINCT payer SEPARATOR ';') AS payer,
        GROUP_CONCAT(DISTINCT paying_bank SEPARATOR ';') AS paying_bank
        FROM premium_remittance
        WHERE is_deleted = 0
        <if test="request.paymentDate != null">
            AND payment_date >= DATE(#{request.paymentDate})
            AND payment_date &lt; DATE_ADD(DATE(#{request.paymentDate}), INTERVAL 1 DAY)
        </if>
        GROUP BY premium_reconciliation_biz_id
        ) prm_total ON prm_total.premium_reconciliation_biz_id = pr.premium_reconciliation_biz_id
        LEFT JOIN (
        SELECT
        policy_no,
        GROUP_CONCAT(DISTINCT broker_name SEPARATOR ';') AS broker_name
        FROM policy_broker
        WHERE is_deleted = 0
        GROUP BY policy_no
        ) pb ON pb.policy_no = pr.policy_no
        WHERE pr.is_deleted = 0
        <if test="request.insuranceCompanyBizId != null and request.insuranceCompanyBizId != ''">
            AND p.insurance_company_biz_id = #{request.insuranceCompanyBizId}
        </if>
        <if test="request.payingBank != null and request.payingBank != ''">
            AND prm_total.paying_bank LIKE CONCAT('%', #{request.payingBank}, '%')
        </if>
        <if test="request.payer != null and request.payer != ''">
            AND prm_total.payer LIKE CONCAT('%', #{request.payer}, '%')
        </if>
        <if test="request.policyNo != null and request.policyNo != ''">
            AND pr.policy_no LIKE CONCAT('%', #{request.policyNo}, '%')
        </if>
        <if test="request.appointmentNo != null and request.appointmentNo != ''">
            AND pf.appointment_no LIKE CONCAT('%', #{request.appointmentNo}, '%')
        </if>
        <if test="request.paymentMethod != null and request.paymentMethod != ''">
            AND prm_total.payment_method LIKE CONCAT('%', #{request.paymentMethod}, '%')
        </if>
        <if test="request.policyFollowStatus != null and request.policyFollowStatus != ''">
            AND pf.status = #{request.policyFollowStatus}
        </if>
        <if test="request.policyStatus != null and request.policyStatus != ''">
            AND p.status = #{request.policyStatus}
        </if>
        <if test="request.reconciliationType != null and request.reconciliationType != ''">
            AND pr.reconciliation_type = #{request.reconciliationType}
        </if>
        <!-- 投保人查询：支持中文繁/简体和英文大小写 -->
        <if test="request.policyHolderNameSc != null and request.policyHolderNameSc != ''
              or request.policyHolderNameEnLc != null and request.policyHolderNameEnLc != ''">
            AND (
            <if test="request.policyHolderNameSc != null and request.policyHolderNameSc != ''">
                pp.name LIKE CONCAT('%', #{request.policyHolderNameSc}, '%')
            </if>
            <if test="request.policyHolderNameSc != null and request.policyHolderNameSc != ''
                      and request.policyHolderNameEnLc != null and request.policyHolderNameEnLc != ''">
                OR
            </if>
            <if test="request.policyHolderNameEnLc != null and request.policyHolderNameEnLc != ''">
                pp.name_en LIKE CONCAT('%', #{request.policyHolderNameEnLc}, '%')
            </if>
            )
        </if>
        <!-- 受保人查询：支持中文繁/简体和英文大小写 -->
        <if test="request.insuredNameSc != null and request.insuredNameSc != ''
              or request.insuredNameEnLc != null and request.insuredNameEnLc != ''">
            AND (
            <if test="request.insuredNameSc != null and request.insuredNameSc != ''">
                pi.name LIKE CONCAT('%', #{request.insuredNameSc}, '%')
            </if>
            <if test="request.insuredNameSc != null and request.insuredNameSc != ''
                      and request.insuredNameEnLc != null and request.insuredNameEnLc != ''">
                OR
            </if>
            <if test="request.insuredNameEnLc != null and request.insuredNameEnLc != ''">
                pi.name_en LIKE CONCAT('%', #{request.insuredNameEnLc}, '%')
            </if>
            )
        </if>
        ORDER BY pr.create_time DESC
    </select>
</mapper>