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..#base') IS NOT NULL
    DROP TABLE #base;

SELECT
    c.id,
    c.orderNo AS order_id,
    ISNULL(NULLIF(c.shopName, ''), 'unknown') AS shop_name,
    ISNULL(NULLIF(e.platform_name, ''), 'unknown') AS platform_name,
    ISNULL(NULLIF(c.mainReason, ''), '未标注') AS main_reason,
    ISNULL(NULLIF(c.secondReason, ''), '未标注') AS second_reason,
    ISNULL(NULLIF(c.thirdReason, ''), '未标注') AS third_reason,
    c.createdAt
INTO #base
FROM dbo.pc_kfChatLog AS c WITH (NOLOCK)
LEFT JOIN dbo.jst_order_express_details AS e WITH (NOLOCK)
    ON e.so_id = c.orderNo
WHERE c.createdAt >= @date_from
  AND c.createdAt < @date_to
  AND ISNULL(NULLIF(c.orderNo, ''), '') <> '';

-- 1) 总览
SELECT
    @date_from AS date_from,
    @date_to AS date_to,
    COUNT(*) AS chat_count,
    COUNT(DISTINCT order_id) AS order_count,
    COUNT(DISTINCT shop_name) AS shop_count
FROM #base;

-- 2) 一级问题 TOP30
SELECT TOP (30)
    platform_name,
    main_reason,
    COUNT(*) AS chat_count,
    COUNT(DISTINCT order_id) AS order_count
FROM #base
GROUP BY platform_name, main_reason
ORDER BY chat_count DESC;

-- 3) 一级+二级问题组合 TOP30
SELECT TOP (30)
    main_reason,
    second_reason,
    COUNT(*) AS chat_count,
    COUNT(DISTINCT order_id) AS order_count
FROM #base
GROUP BY main_reason, second_reason
ORDER BY chat_count DESC;

-- 4) 店铺热点问题
SELECT TOP (50)
    shop_name,
    platform_name,
    main_reason,
    COUNT(*) AS chat_count,
    COUNT(DISTINCT order_id) AS order_count
FROM #base
GROUP BY shop_name, platform_name, main_reason
ORDER BY chat_count DESC;

-- 5) 每日趋势
SELECT
    CAST(createdAt AS date) AS biz_date,
    COUNT(*) AS chat_count,
    COUNT(DISTINCT order_id) AS order_count
FROM #base
GROUP BY CAST(createdAt AS date)
ORDER BY biz_date DESC;