package com.talentcard.server.mapper;

/**
 * 政府补贴金额（分）：人才折扣仅在对应「是否政府补贴」开关打开时计入，
 * 另加政府补贴券面额；商家自行让利不计入。
 * 工作台按已支付订单计入，不要求核销。
 * <p>
 * 兼容 MySQL 5.7：不用 JSON_TABLE，也不用会引用外层列的派生表 UNION。
 * 依赖当前语句中的表别名：{@code o} benefit_order、{@code p} merchant_product、
 * {@code m} merchant、{@code c} merchant_coupon。
 */
final class GovernmentSubsidySql {
    private static final String CFG = "if(o.product_id is not null, p.rights_config, m.merchant_rights_config)";
    private static final String OL = "coalesce(nullif(o.card_level_snapshot, ''), (select tc.card_level from talent_card tc where tc.id = o.card_id and tc.deleted = 0 limit 1), (select talent.talent_level from talent_user talent where talent.id = o.talent_id and talent.deleted = 0 limit 1), '')";
    private static final String AREA = "upper(coalesce((select talent.source_area_type from talent_user talent where talent.id = o.talent_id and talent.deleted = 0 limit 1), ''))";
    private static final String LN_OL = lv(OL);

    /** 有效下单时间：已支付取支付成功时间，零元单取创建时间。 */
    static final String ORDER_TIME = "coalesce("
            + "(select max(pt.paid_at) from payment_transaction pt"
            + " where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),"
            + " o.created_at)";

    static final String AMOUNT_CENTS = "("
            + "case " + whenThen(0) + whenThen(1) + whenThen(2) + whenThen(3)
            + whenThen(4) + whenThen(5) + whenThen(6) + whenThen(7)
            + whenThen(8) + whenThen(9) + whenThen(10) + whenThen(11)
            + " else 0 end"
            + " + case when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1 then coalesce(o.coupon_amount, 0) else 0 end)";

    private static String whenThen(int i) {
        return " when " + matches(i) + " then if(" + governmentFlag(i)
                + ", greatest(coalesce(o.subsidy_amount, 0) - coalesce(o.coupon_amount, 0), 0), 0)";
    }

    private static String matches(int i) {
        String p = "$[" + i + "]";
        String level = "json_unquote(json_extract(" + CFG + ", '" + p + ".level'))";
        return "json_extract(" + CFG + ", '" + p + ".level') is not null"
                + " and " + fl(p + ".enabled")
                + " and " + lv(level) + " = " + LN_OL
                + " and (" + LN_OL + " <> 'EXCELLENT'"
                + " or (" + AREA + " = 'LOCAL' and " + level + " not like '%外地%')"
                + " or (" + AREA + " in ('OUTSIDE', '外地') and " + level + " not like '%本地%')"
                + " or (" + AREA + " not in ('LOCAL', 'OUTSIDE', '外地') and " + level + " not like '%本地%' and " + level + " not like '%外地%'))";
    }

    private static String governmentFlag(int i) {
        String p = "$[" + i + "]";
        return "(case when o.product_id is not null then"
                + " case when coalesce(o.base_pay_amount, coalesce(o.pay_amount, 0) + coalesce(o.coupon_amount, 0)) = 0 then "
                + fl(p + ".subsidy")
                + " when " + fl(p + ".free") + " then " + fl(p + ".exceedSubsidy")
                + " else " + fl(p + ".subsidy") + " end"
                + " else " + fl(p + ".governmentSubsidy") + " end)";
    }

    private static String fl(String path) {
        return "lower(replace(cast(json_extract(" + CFG + ", '" + path + "') as char), '\"', '')) in ('true', '1', 'yes')";
    }

    private static String lv(String expr) {
        return "(case when coalesce(" + expr + ", '') like '%卓越%' or upper(coalesce(" + expr + ", '')) like '%EXCELLENT%' then 'EXCELLENT'"
                + " when coalesce(" + expr + ", '') like '%杰出%' or upper(coalesce(" + expr + ", '')) like '%OUTSTANDING%' then 'OUTSTANDING'"
                + " when coalesce(" + expr + ", '') like '%菁英%' or coalesce(" + expr + ", '') like '%精英%' or upper(coalesce(" + expr + ", '')) like '%ELITE%' then 'ELITE'"
                + " else upper(trim(coalesce(" + expr + ", ''))) end)";
    }

    private GovernmentSubsidySql() {
    }
}