USE [JSDataMiddlePlatform];
SET NOCOUNT ON;

/*
  Template: 商品ID推广费用周报（2026可用）
  目标: 费用 + 发货件数 + 缺货风险 + 负责人
  默认窗口: 上一个自然周 [week_start, week_end)
*/

DECLARE @week_end datetime2 = CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS datetime2);
DECLARE @week_start datetime2 = CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 0) AS datetime2);
DECLARE @lookback_30d datetime2 = DATEADD(day, -30, @week_end);
DECLARE @lead_days int = 10;
DECLARE @warn_days int = 15;

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

WITH src AS (
    SELECT
        NULLIF(a.undefined2, '') AS order_id,
        NULLIF(a.undefined12, '') AS product_id,
        NULLIF(a.undefined10, '') AS sku_id,
        ISNULL(NULLIF(a.undefined8, ''), 'unknown') AS shop_name,
        CASE
            WHEN CHARINDEX('-', ISNULL(NULLIF(a.undefined8, ''), '')) > 0
                THEN LEFT(ISNULL(NULLIF(a.undefined8, ''), ''), CHARINDEX('-', ISNULL(NULLIF(a.undefined8, ''), '')) - 1)
            ELSE 'unknown'
        END AS shop_channel,
        COALESCE(
            TRY_CONVERT(datetime2, NULLIF(a.undefined6, ''), 120),
            TRY_CONVERT(datetime2, REPLACE(NULLIF(a.undefined6, ''), '/', '-'), 120),
            TRY_CONVERT(datetime2, NULLIF(a.undefined6, ''), 111),
            TRY_CONVERT(datetime2, a.createdAt)
        ) AS ship_dt,
        COALESCE(
            TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined30, '')),
            TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined20, '')),
            CAST(0 AS decimal(18, 2))
        ) AS shipped_qty,
        TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined97, '')) AS promo_fee_8001201,
        (
            ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined108, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined109, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined110, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined111, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined112, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined113, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined114, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined115, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined116, '')), 0)
          + ISNULL(TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined117, '')), 0)
        ) AS commission_fee_sum,
        ISNULL(NULLIF(a.undefined62, ''), '') AS order_labels
    FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
)
SELECT
    order_id,
    product_id,
    sku_id,
    shop_name,
    shop_channel,
    ship_dt,
    shipped_qty,
    ISNULL(promo_fee_8001201, 0) AS promo_fee_8001201,
    ISNULL(commission_fee_sum, 0) AS commission_fee_sum
INTO #base_week
FROM src
WHERE ship_dt >= @week_start
  AND ship_dt < @week_end
  AND ISNULL(product_id, '') <> ''
  AND ISNULL(sku_id, '') <> ''
  AND order_labels NOT LIKE '%配件%'
  AND order_labels NOT LIKE '%特殊单%';

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

WITH src_30d AS (
    SELECT
        NULLIF(a.undefined12, '') AS product_id,
        NULLIF(a.undefined10, '') AS sku_id,
        ISNULL(NULLIF(a.undefined8, ''), 'unknown') AS shop_name,
        COALESCE(
            TRY_CONVERT(datetime2, NULLIF(a.undefined6, ''), 120),
            TRY_CONVERT(datetime2, REPLACE(NULLIF(a.undefined6, ''), '/', '-'), 120),
            TRY_CONVERT(datetime2, NULLIF(a.undefined6, ''), 111),
            TRY_CONVERT(datetime2, a.createdAt)
        ) AS ship_dt,
        COALESCE(
            TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined30, '')),
            TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined20, '')),
            CAST(0 AS decimal(18, 2))
        ) AS shipped_qty,
        ISNULL(NULLIF(a.undefined62, ''), '') AS order_labels
    FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
)
SELECT
    product_id,
    sku_id,
    shop_name,
    SUM(ISNULL(shipped_qty, 0)) AS shipped_qty_30d
INTO #ship_30d
FROM src_30d
WHERE ship_dt >= @lookback_30d
  AND ship_dt < @week_end
  AND ISNULL(product_id, '') <> ''
  AND ISNULL(sku_id, '') <> ''
  AND order_labels NOT LIKE '%配件%'
  AND order_labels NOT LIKE '%特殊单%'
GROUP BY product_id, sku_id, shop_name;

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

WITH inv_rank AS (
    SELECT
        NULLIF(i.sku_id, '') AS sku_id,
        TRY_CONVERT(decimal(18, 2), i.qty) AS stock_qty,
        TRY_CONVERT(decimal(18, 2), i.order_lock) AS order_lock_qty,
        TRY_CONVERT(decimal(18, 2), i.pick_lock) AS pick_lock_qty,
        TRY_CONVERT(decimal(18, 2), i.lock_qty) AS lock_qty,
        i.name AS inventory_note,
        COALESCE(i.updateTime, i.modified, i.createTime) AS inventory_snapshot_time,
        ROW_NUMBER() OVER (
            PARTITION BY NULLIF(i.sku_id, '')
            ORDER BY COALESCE(i.updateTime, i.modified, i.createTime) DESC, i.Id DESC
        ) AS rn
    FROM dbo.jst_inventoryInfo AS i WITH (NOLOCK)
    WHERE NULLIF(i.sku_id, '') IN (SELECT DISTINCT sku_id FROM #base_week)
)
SELECT
    sku_id,
    stock_qty,
    order_lock_qty,
    pick_lock_qty,
    lock_qty,
    inventory_note,
    inventory_snapshot_time,
    CASE
        WHEN ISNULL(stock_qty, 0) - ISNULL(order_lock_qty, 0) - ISNULL(pick_lock_qty, 0) - ISNULL(lock_qty, 0) < 0
            THEN CAST(0 AS decimal(18, 2))
        ELSE ISNULL(stock_qty, 0) - ISNULL(order_lock_qty, 0) - ISNULL(pick_lock_qty, 0) - ISNULL(lock_qty, 0)
    END AS available_qty
INTO #inv_latest
FROM inv_rank
WHERE rn = 1;

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

SELECT
    s.product_id,
    s.shop_name,
    s.sku_id,
    s.shipped_qty_30d,
    CAST(ISNULL(s.shipped_qty_30d, 0) / 30.0 AS decimal(18, 2)) AS avg_daily_shipped_30d,
    inv.available_qty,
    CAST(
        CASE
            WHEN ISNULL(s.shipped_qty_30d, 0) <= 0 THEN NULL
            ELSE inv.available_qty / NULLIF(ISNULL(s.shipped_qty_30d, 0) / 30.0, 0)
        END AS decimal(18, 2)
    ) AS estimated_stockout_days,
    CAST(
        CASE
            WHEN ISNULL(s.shipped_qty_30d, 0) <= 0 THEN 0
            WHEN inv.available_qty >= (ISNULL(s.shipped_qty_30d, 0) / 30.0) * @lead_days THEN 0
            ELSE ((ISNULL(s.shipped_qty_30d, 0) / 30.0) * @lead_days) - inv.available_qty
        END AS decimal(18, 2)
    ) AS suggest_replenish_qty_to_10d,
    CASE
        WHEN inv.sku_id IS NULL THEN '无库存快照'
        WHEN ISNULL(s.shipped_qty_30d, 0) <= 0 THEN '近30天无发货'
        WHEN inv.available_qty <= 0 THEN '缺货/可用库存<=0'
        WHEN (inv.available_qty / NULLIF(ISNULL(s.shipped_qty_30d, 0) / 30.0, 0)) <= @lead_days THEN '补货紧急（<=10天）'
        WHEN (inv.available_qty / NULLIF(ISNULL(s.shipped_qty_30d, 0) / 30.0, 0)) <= @warn_days THEN '补货预警（10-15天）'
        ELSE '库存安全'
    END AS stock_risk_level
INTO #sku_risk
FROM #ship_30d AS s
LEFT JOIN #inv_latest AS inv
    ON inv.sku_id = s.sku_id;

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

WITH owner_src AS (
    SELECT
        bw.product_id,
        bw.shop_name,
        bw.sku_id,
        SUM(ISNULL(bw.shipped_qty, 0)) AS week_shipped_qty,
        NULLIF(js.other_1, '') AS spu,
        NULLIF(js.i_id, '') AS style_code,
        NULLIF(tm.oper, '') AS tm_oper,
        NULLIF(opm.oper, '') AS opm_oper,
        NULLIF(spu_cfg.oper, '') AS spu_cfg_oper,
        NULLIF(spu_cfg.purchaser, '') AS purchaser
    FROM #base_week AS bw
    LEFT JOIN dbo.jst_sku AS js WITH (NOLOCK)
        ON js.sku_id = bw.sku_id
    LEFT JOIN dbo.op_TargetManagement AS tm WITH (NOLOCK)
        ON NULLIF(tm.shopproid, '') = bw.product_id
       AND ISNULL(NULLIF(tm.shopname, ''), '') = ISNULL(NULLIF(bw.shop_name, ''), '')
    OUTER APPLY (
        SELECT TOP 1 o.oper
        FROM dbo.op_operManage AS o WITH (NOLOCK)
        WHERE NULLIF(o.spu, '') IN (
            NULLIF(js.other_1, ''),
            NULLIF(js.i_id, ''),
            CASE
                WHEN CHARINDEX('K', ISNULL(js.other_1, '')) > 0
                    THEN LEFT(js.other_1, CHARINDEX('K', js.other_1) - 1)
                ELSE NULL
            END
        )
        ORDER BY o.Id DESC
    ) AS opm
    OUTER APPLY (
        SELECT TOP 1 c.oper, c.purchaser
        FROM dbo.fys_SkuDataLibrary_SpuConfig AS c WITH (NOLOCK)
        WHERE NULLIF(c.spu, '') IN (
            NULLIF(js.other_1, ''),
            NULLIF(js.i_id, ''),
            CASE
                WHEN CHARINDEX('K', ISNULL(js.other_1, '')) > 0
                    THEN LEFT(js.other_1, CHARINDEX('K', js.other_1) - 1)
                ELSE NULL
            END
        )
        ORDER BY c.Id DESC
    ) AS spu_cfg
    GROUP BY
        bw.product_id,
        bw.shop_name,
        bw.sku_id,
        NULLIF(js.other_1, ''),
        NULLIF(js.i_id, ''),
        NULLIF(tm.oper, ''),
        NULLIF(opm.oper, ''),
        NULLIF(spu_cfg.oper, ''),
        NULLIF(spu_cfg.purchaser, '')
), owner_rank AS (
    SELECT
        product_id,
        shop_name,
        COALESCE(tm_oper, opm_oper, spu_cfg_oper, '未分配') AS operator_name,
        COALESCE(purchaser, '未分配') AS purchaser_name,
        ROW_NUMBER() OVER (
            PARTITION BY product_id, shop_name
            ORDER BY week_shipped_qty DESC, sku_id ASC
        ) AS rn
    FROM owner_src
)
SELECT
    product_id,
    shop_name,
    operator_name,
    purchaser_name
INTO #owner_pick
FROM owner_rank
WHERE rn = 1;

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

SELECT
    product_id,
    shop_name,
    COUNT(DISTINCT CASE WHEN stock_risk_level LIKE '补货紧急%' OR stock_risk_level LIKE '缺货%' THEN sku_id END) AS urgent_risk_sku_count,
    COUNT(DISTINCT CASE WHEN stock_risk_level LIKE '补货预警%' THEN sku_id END) AS warning_risk_sku_count,
    MIN(estimated_stockout_days) AS min_estimated_stockout_days,
    SUM(CASE WHEN stock_risk_level LIKE '补货紧急%' OR stock_risk_level LIKE '缺货%' THEN ISNULL(suggest_replenish_qty_to_10d, 0) ELSE 0 END) AS suggest_replenish_qty_10d
INTO #risk_agg
FROM #sku_risk
GROUP BY product_id, shop_name;

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

SELECT
    bw.product_id,
    bw.shop_channel,
    bw.shop_name,
    COUNT(DISTINCT bw.sku_id) AS sku_count,
    COUNT(DISTINCT bw.order_id) AS order_count,
    SUM(ISNULL(bw.shipped_qty, 0)) AS shipped_qty_sum,
    SUM(ISNULL(bw.promo_fee_8001201, 0)) AS promo_fee_sum,
    SUM(ISNULL(bw.commission_fee_sum, 0)) AS commission_fee_sum,
    SUM(ISNULL(bw.promo_fee_8001201, 0) + ISNULL(bw.commission_fee_sum, 0)) AS marketing_fee_sum,
    CASE
        WHEN SUM(ISNULL(bw.shipped_qty, 0)) <= 0 THEN NULL
        ELSE (SUM(ISNULL(bw.promo_fee_8001201, 0) + ISNULL(bw.commission_fee_sum, 0)) / SUM(ISNULL(bw.shipped_qty, 0)))
    END AS marketing_fee_per_piece,
    ISNULL(ra.urgent_risk_sku_count, 0) AS urgent_risk_sku_count,
    ISNULL(ra.warning_risk_sku_count, 0) AS warning_risk_sku_count,
    ra.min_estimated_stockout_days,
    ISNULL(ra.suggest_replenish_qty_10d, 0) AS suggest_replenish_qty_10d,
    CASE
        WHEN ISNULL(ra.urgent_risk_sku_count, 0) > 0 THEN '补货紧急'
        WHEN ISNULL(ra.warning_risk_sku_count, 0) > 0 THEN '补货预警'
        ELSE '库存安全/无快照'
    END AS product_stock_risk_level,
    ISNULL(op.purchaser_name, '未分配') AS purchaser_name,
    ISNULL(op.operator_name, '未分配') AS operator_name
INTO #product_week
FROM #base_week AS bw
LEFT JOIN #risk_agg AS ra
    ON ra.product_id = bw.product_id
   AND ra.shop_name = bw.shop_name
LEFT JOIN #owner_pick AS op
    ON op.product_id = bw.product_id
   AND op.shop_name = bw.shop_name
GROUP BY
    bw.product_id,
    bw.shop_channel,
    bw.shop_name,
    ISNULL(ra.urgent_risk_sku_count, 0),
    ISNULL(ra.warning_risk_sku_count, 0),
    ra.min_estimated_stockout_days,
    ISNULL(ra.suggest_replenish_qty_10d, 0),
    CASE
        WHEN ISNULL(ra.urgent_risk_sku_count, 0) > 0 THEN '补货紧急'
        WHEN ISNULL(ra.warning_risk_sku_count, 0) > 0 THEN '补货预警'
        ELSE '库存安全/无快照'
    END,
    ISNULL(op.purchaser_name, '未分配'),
    ISNULL(op.operator_name, '未分配');

-- 1) 周汇总（商品ID维度）
SELECT
    @week_start AS week_start,
    @week_end AS week_end,
    COUNT(*) AS product_shop_count,
    COUNT(DISTINCT product_id) AS product_count,
    COUNT(DISTINCT shop_name) AS shop_count,
    SUM(ISNULL(order_count, 0)) AS order_count_sum,
    SUM(ISNULL(shipped_qty_sum, 0)) AS shipped_qty_sum,
    SUM(ISNULL(promo_fee_sum, 0)) AS promo_fee_sum,
    SUM(ISNULL(commission_fee_sum, 0)) AS commission_fee_sum,
    SUM(ISNULL(marketing_fee_sum, 0)) AS marketing_fee_sum,
    CASE
        WHEN SUM(ISNULL(shipped_qty_sum, 0)) <= 0 THEN NULL
        ELSE SUM(ISNULL(marketing_fee_sum, 0)) / SUM(ISNULL(shipped_qty_sum, 0))
    END AS marketing_fee_per_piece_avg,
    SUM(CASE WHEN product_stock_risk_level = '补货紧急' THEN 1 ELSE 0 END) AS urgent_product_count,
    SUM(CASE WHEN product_stock_risk_level = '补货预警' THEN 1 ELSE 0 END) AS warning_product_count
FROM #product_week;

-- 2) 商品ID周报TOP100（费用 + 发货件数 + 缺货风险 + 负责人）
SELECT TOP (100)
    product_id,
    shop_channel,
    shop_name,
    sku_count,
    order_count,
    shipped_qty_sum,
    promo_fee_sum,
    commission_fee_sum,
    marketing_fee_sum,
    marketing_fee_per_piece,
    product_stock_risk_level,
    min_estimated_stockout_days,
    suggest_replenish_qty_10d,
    urgent_risk_sku_count,
    warning_risk_sku_count,
    purchaser_name,
    operator_name
FROM #product_week
ORDER BY marketing_fee_sum DESC, shipped_qty_sum DESC, product_id ASC;

-- 3) 高风险商品ID清单（运营动作优先）
SELECT TOP (100)
    product_id,
    shop_channel,
    shop_name,
    shipped_qty_sum,
    marketing_fee_sum,
    product_stock_risk_level,
    min_estimated_stockout_days,
    suggest_replenish_qty_10d,
    purchaser_name,
    operator_name
FROM #product_week
WHERE product_stock_risk_level IN ('补货紧急', '补货预警')
ORDER BY
    CASE product_stock_risk_level WHEN '补货紧急' THEN 1 ELSE 2 END,
    min_estimated_stockout_days ASC,
    marketing_fee_sum DESC;

-- 4) 负责人缺失清单（数据治理）
SELECT TOP (100)
    product_id,
    shop_channel,
    shop_name,
    shipped_qty_sum,
    marketing_fee_sum,
    purchaser_name,
    operator_name
FROM #product_week
WHERE purchaser_name = '未分配'
   OR operator_name = '未分配'
ORDER BY marketing_fee_sum DESC, shipped_qty_sum DESC;