USE [JSDataMiddlePlatform];
GO

/*
  Template: 退款原因分析
  Window: 最近30天 [date_from, date_to)
*/

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

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

WITH chat_rank AS (
    SELECT
        NULLIF(c.orderNo, '') AS order_id,
        ISNULL(NULLIF(c.mainReason, ''), '') AS main_reason,
        ISNULL(NULLIF(c.secondReason, ''), '') AS second_reason,
        ISNULL(NULLIF(c.thirdReason, ''), '') AS third_reason,
        ROW_NUMBER() OVER (
            PARTITION BY NULLIF(c.orderNo, '')
            ORDER BY c.createdAt DESC, c.id DESC
        ) AS rn
    FROM dbo.pc_kfChatLog AS c WITH (NOLOCK)
    WHERE c.createdAt >= @date_from
      AND c.createdAt < @date_to
      AND ISNULL(NULLIF(c.orderNo, ''), '') <> ''
)
SELECT
    order_id,
    main_reason,
    second_reason,
    third_reason
INTO #chat_latest
FROM chat_rank
WHERE rn = 1;

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

SELECT
    NULLIF(a.undefined2, '') AS order_id,
    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,
    ISNULL(NULLIF(a.undefined56, ''), '未标注') AS afs_type,
    ISNULL(NULLIF(a.undefined57, ''), '') AS native_afs_reason,
    ISNULL(cl.main_reason, '') AS chat_main_reason,
    ISNULL(cl.second_reason, '') AS chat_second_reason,
    ISNULL(cl.third_reason, '') AS chat_third_reason,
    CASE
        WHEN ISNULL(NULLIF(a.undefined57, ''), '') <> '' THEN ISNULL(NULLIF(a.undefined57, ''), '')
        WHEN ISNULL(cl.main_reason, '') <> '' THEN cl.main_reason
        ELSE '未标注'
    END AS afs_reason_l1,
    CASE
        WHEN ISNULL(cl.second_reason, '') <> '' THEN cl.second_reason
        ELSE '未标注'
    END AS afs_reason_l2,
    CASE
        WHEN ISNULL(cl.third_reason, '') <> '' THEN cl.third_reason
        ELSE '未标注'
    END AS afs_reason_l3,
    CASE
        WHEN ISNULL(NULLIF(a.undefined57, ''), '') <> '' THEN 'afs_reason'
        WHEN ISNULL(cl.main_reason, '') <> '' THEN 'chat_main_reason'
        ELSE 'unknown'
    END AS reason_source,
    ISNULL(NULLIF(a.undefined58, ''), '未标注') AS afs_result,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined50, '')) AS return_amount,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined52, '')) AS refund_amount,
    TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined26, '')) AS payment_amount,
    ISNULL(NULLIF(a.undefined62, ''), '') AS order_labels
INTO #base
FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
LEFT JOIN #chat_latest AS cl
    ON cl.order_id = NULLIF(a.undefined2, '')
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 '%特殊单%';

-- 1) 全局退款原因 TOP30
SELECT TOP (30)
    afs_reason_l1 AS afs_reason,
    reason_source,
    COUNT(*) AS afs_record_count,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(ISNULL(refund_amount, 0)) AS refund_amount,
    SUM(ISNULL(return_amount, 0)) AS return_amount,
    CASE
        WHEN SUM(ISNULL(payment_amount, 0)) = 0 THEN NULL
        ELSE SUM(ISNULL(refund_amount, 0)) / SUM(ISNULL(payment_amount, 0))
    END AS reason_refund_rate
FROM #base
GROUP BY afs_reason_l1, reason_source
ORDER BY afs_record_count DESC;

-- 2) 平台内原因 TOP10
WITH reason_rank AS (
    SELECT
        platform_name,
        afs_reason_l1 AS afs_reason,
        COUNT(*) AS afs_record_count,
        SUM(ISNULL(refund_amount, 0)) AS refund_amount,
        ROW_NUMBER() OVER (
            PARTITION BY platform_name
            ORDER BY COUNT(*) DESC
        ) AS rn
    FROM #base
    GROUP BY platform_name, afs_reason_l1
)
SELECT
    platform_name,
    afs_reason,
    afs_record_count,
    refund_amount
FROM reason_rank
WHERE rn <= 10
ORDER BY platform_name, afs_record_count DESC;

-- 3) 二级原因 TOP20（可用于更细分原因）
SELECT TOP (20)
    afs_reason_l1,
    afs_reason_l2,
    COUNT(*) AS afs_record_count,
    SUM(ISNULL(refund_amount, 0)) AS refund_amount
FROM #base
GROUP BY afs_reason_l1, afs_reason_l2
ORDER BY afs_record_count DESC;

-- 4) 店铺退款风险清单（订单数>=50）
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,
    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
FROM #base
GROUP BY shop_name, platform_name
HAVING COUNT(DISTINCT order_id) >= 50
ORDER BY refund_rate DESC, payment_amount DESC;

-- 5) 售后类型/结果矩阵
SELECT
    afs_type,
    afs_result,
    COUNT(*) AS afs_record_count,
    SUM(ISNULL(refund_amount, 0)) AS refund_amount
FROM #base
GROUP BY afs_type, afs_result
ORDER BY afs_record_count DESC;