USE [JSDataMiddlePlatform];
GO

/*
  Template: 费比异常预警
  Window: 最近14天 [date_from, date_to)
*/

DECLARE @date_from datetime2 = CAST(DATEADD(DAY, -14, CAST(GETDATE() AS date)) AS datetime2);
DECLARE @date_to datetime2 = CAST(DATEADD(DAY, 1, CAST(GETDATE() AS date)) AS datetime2);

IF OBJECT_ID('tempdb..#base') IS NOT NULL
    DROP TABLE #base;

SELECT
    CAST(a.createdAt AS date) AS biz_date,
    COALESCE(NULLIF(a.undefined8, ''), 'unknown') AS shop_name,
    CASE
        WHEN CHARINDEX('-', ISNULL(a.undefined8, '')) > 1
            THEN LEFT(a.undefined8, CHARINDEX('-', a.undefined8) - 1)
        ELSE 'unknown'
    END AS platform_name,
    NULLIF(a.undefined2, '') AS order_id,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined26, '')) AS payment_amount,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined52, '')) AS refund_amount,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined82, '')) AS fee_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined85, '')) AS fee_taoke,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined86, '')) AS fee_freight_insurance,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined97, '')) AS fee_promotion,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined102, '')) AS fee_software_service,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined108, '')) AS fee_taobao_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined109, '')) AS fee_jd_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined110, '')) AS fee_pdd_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined111, '')) AS fee_taogongchang_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined112, '')) AS fee_alibaba_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined113, '')) AS fee_douyin_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined114, '')) AS fee_maoning_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined115, '')) AS fee_vip_mp_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined116, '')) AS fee_vip_commission,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined117, '')) AS fee_dewu_commission,
    ISNULL(NULLIF(a.undefined62, ''), '') AS order_labels
INTO #base
FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
WHERE a.createdAt >= @date_from
  AND a.createdAt < @date_to
  AND ISNULL(NULLIF(a.undefined2, ''), '') <> ''
  AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%配件%'
  AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%特殊单%';

IF OBJECT_ID('tempdb..#agg') IS NOT NULL
    DROP TABLE #agg;

SELECT
    shop_name,
    platform_name,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(ISNULL(payment_amount, 0)) AS payment_amount,
    SUM(ISNULL(refund_amount, 0)) AS refund_amount,
    SUM(
        ABS(ISNULL(fee_commission, 0))
        + ABS(ISNULL(fee_taoke, 0))
        + ABS(ISNULL(fee_freight_insurance, 0))
        + ABS(ISNULL(fee_promotion, 0))
        + ABS(ISNULL(fee_software_service, 0))
        + ABS(ISNULL(fee_taobao_commission, 0))
        + ABS(ISNULL(fee_jd_commission, 0))
        + ABS(ISNULL(fee_pdd_commission, 0))
        + ABS(ISNULL(fee_taogongchang_commission, 0))
        + ABS(ISNULL(fee_alibaba_commission, 0))
        + ABS(ISNULL(fee_douyin_commission, 0))
        + ABS(ISNULL(fee_maoning_commission, 0))
        + ABS(ISNULL(fee_vip_mp_commission, 0))
        + ABS(ISNULL(fee_vip_commission, 0))
        + ABS(ISNULL(fee_dewu_commission, 0))
    ) AS fee_amount_abs
INTO #agg
FROM #base
GROUP BY shop_name, platform_name;

-- 1) 全局预警总览
SELECT
    @date_from AS date_from,
    @date_to AS date_to,
    COUNT(*) AS shop_count,
    SUM(order_count) AS order_count,
    SUM(payment_amount) AS payment_amount,
    SUM(refund_amount) AS refund_amount,
    SUM(fee_amount_abs) AS fee_amount_abs,
    CASE
        WHEN SUM(payment_amount) = 0 THEN NULL
        ELSE SUM(refund_amount) / SUM(payment_amount)
    END AS refund_rate,
    CASE
        WHEN SUM(payment_amount) = 0 THEN NULL
        ELSE SUM(fee_amount_abs) / SUM(payment_amount)
    END AS fee_abs_rate
FROM #agg;

-- 2) 店铺级异常清单
SELECT
    shop_name,
    platform_name,
    order_count,
    payment_amount,
    refund_amount,
    fee_amount_abs,
    CASE
        WHEN payment_amount = 0 THEN NULL
        ELSE refund_amount / payment_amount
    END AS refund_rate,
    CASE
        WHEN payment_amount = 0 THEN NULL
        ELSE fee_amount_abs / payment_amount
    END AS fee_abs_rate,
    CASE
        WHEN payment_amount = 0 THEN 'P3'
        WHEN (fee_amount_abs / payment_amount) >= 0.35 OR (refund_amount / payment_amount) >= 0.25 THEN 'P1'
        WHEN (fee_amount_abs / payment_amount) >= 0.25 OR (refund_amount / payment_amount) >= 0.18 THEN 'P2'
        ELSE 'P3'
    END AS alert_level
FROM #agg
WHERE order_count >= 30
ORDER BY
    CASE
        WHEN payment_amount = 0 THEN 0
        ELSE fee_amount_abs / payment_amount
    END DESC,
    refund_amount DESC;

-- 3) 日趋势（全局）
SELECT
    biz_date,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(ISNULL(payment_amount, 0)) AS payment_amount,
    SUM(ISNULL(refund_amount, 0)) AS refund_amount,
    SUM(
        ABS(ISNULL(fee_commission, 0))
        + ABS(ISNULL(fee_taoke, 0))
        + ABS(ISNULL(fee_freight_insurance, 0))
        + ABS(ISNULL(fee_promotion, 0))
        + ABS(ISNULL(fee_software_service, 0))
        + ABS(ISNULL(fee_taobao_commission, 0))
        + ABS(ISNULL(fee_jd_commission, 0))
        + ABS(ISNULL(fee_pdd_commission, 0))
        + ABS(ISNULL(fee_taogongchang_commission, 0))
        + ABS(ISNULL(fee_alibaba_commission, 0))
        + ABS(ISNULL(fee_douyin_commission, 0))
        + ABS(ISNULL(fee_maoning_commission, 0))
        + ABS(ISNULL(fee_vip_mp_commission, 0))
        + ABS(ISNULL(fee_vip_commission, 0))
        + ABS(ISNULL(fee_dewu_commission, 0))
    ) AS fee_amount_abs,
    CASE
        WHEN SUM(ISNULL(payment_amount, 0)) = 0 THEN NULL
        ELSE SUM(ISNULL(refund_amount, 0)) / SUM(ISNULL(payment_amount, 0))
    END AS refund_rate,
    CASE
        WHEN SUM(ISNULL(payment_amount, 0)) = 0 THEN NULL
        ELSE SUM(
            ABS(ISNULL(fee_commission, 0))
            + ABS(ISNULL(fee_taoke, 0))
            + ABS(ISNULL(fee_freight_insurance, 0))
            + ABS(ISNULL(fee_promotion, 0))
            + ABS(ISNULL(fee_software_service, 0))
            + ABS(ISNULL(fee_taobao_commission, 0))
            + ABS(ISNULL(fee_jd_commission, 0))
            + ABS(ISNULL(fee_pdd_commission, 0))
            + ABS(ISNULL(fee_taogongchang_commission, 0))
            + ABS(ISNULL(fee_alibaba_commission, 0))
            + ABS(ISNULL(fee_douyin_commission, 0))
            + ABS(ISNULL(fee_maoning_commission, 0))
            + ABS(ISNULL(fee_vip_mp_commission, 0))
            + ABS(ISNULL(fee_vip_commission, 0))
            + ABS(ISNULL(fee_dewu_commission, 0))
          ) / SUM(ISNULL(payment_amount, 0))
    END AS fee_abs_rate
FROM #base
GROUP BY biz_date
ORDER BY biz_date DESC;