SBrainCO/db/migration_phase5_sku_functions.sql

-- ============================================================
-- Phase 5: SKU/菜品参数化函数
-- 替换 _april 后缀的SKU/菜品视图为参数化函数
-- 依赖: fn_dish_sales(p_month) 已在 Phase 2 中创建
-- ============================================================

-- 1. fn_dish_sku_summary(p_month) — 替换 dish_sku_summary_april
CREATE OR REPLACE FUNCTION analytics.fn_dish_sku_summary(p_month date)
RETURNS TABLE (
  dish_name text,
  category_level1 text,
  category_level2 text,
  detail_rows bigint,
  bill_count bigint,
  store_count bigint,
  sales_quantity numeric,
  gross_amount numeric,
  received_amount numeric,
  discount_amount numeric,
  discount_rate_pct numeric,
  realized_unit_price numeric,
  revenue_share_pct numeric
) AS $$
  WITH sales AS MATERIALIZED (
    SELECT * FROM analytics.fn_dish_sales(p_month)
  ),
  dish_agg AS (
    SELECT
      s.dish_name,
      min(s.category_level1) AS category_level1,
      min(s.category_level2) AS category_level2,
      count(*) AS detail_rows,
      sum(s.sales_quantity) AS sales_quantity,
      sum(s.gross_amount) AS gross_amount,
      sum(s.received_amount) AS received_amount,
      sum(s.dish_discount_amount) AS discount_amount
    FROM sales s
    GROUP BY s.dish_name
  ),
  bill_counts AS (
    SELECT s.dish_name,
      count(DISTINCT s.store_code) AS store_count,
      count(DISTINCT s.store_code || '|' || s.bill_no) AS bill_count
    FROM sales s
    GROUP BY s.dish_name
  )
  SELECT
    a.dish_name,
    a.category_level1,
    a.category_level2,
    a.detail_rows,
    bc.bill_count,
    bc.store_count,
    a.sales_quantity,
    a.gross_amount,
    a.received_amount,
    a.discount_amount,
    round(a.discount_amount / NULLIF(a.gross_amount, 0) * 100, 2) AS discount_rate_pct,
    round(a.received_amount / NULLIF(a.sales_quantity, 0), 2) AS realized_unit_price,
    round(a.received_amount / NULLIF(sum(a.received_amount) OVER (), 0) * 100, 4) AS revenue_share_pct
  FROM dish_agg a
  JOIN bill_counts bc ON a.dish_name = bc.dish_name
$$ LANGUAGE SQL STABLE;

-- 2. fn_dish_sku_abc(p_month) — 替换 v_dish_sku_abc_april
CREATE OR REPLACE FUNCTION analytics.fn_dish_sku_abc(p_month date)
RETURNS TABLE (
  dish_name text,
  category_level1 text,
  category_level2 text,
  detail_rows bigint,
  bill_count bigint,
  store_count bigint,
  sales_quantity numeric,
  gross_amount numeric,
  received_amount numeric,
  discount_amount numeric,
  discount_rate_pct numeric,
  realized_unit_price numeric,
  revenue_share_pct numeric,
  cumulative_revenue_share numeric,
  median_quantity numeric,
  median_revenue numeric,
  abc_class text,
  sales_quadrant text
) AS $$
  WITH sku_summary AS MATERIALIZED (
    SELECT * FROM analytics.fn_dish_sku_summary(p_month)
  ), medians AS (
    SELECT
      percentile_cont(0.5) WITHIN GROUP (ORDER BY s.sales_quantity::double precision) AS median_quantity,
      percentile_cont(0.5) WITHIN GROUP (ORDER BY s.received_amount::double precision) AS median_revenue
    FROM sku_summary s
  ), ranked AS (
    SELECT
      s.dish_name,
      s.category_level1,
      s.category_level2,
      s.detail_rows,
      s.bill_count,
      s.store_count,
      s.sales_quantity,
      s.gross_amount,
      s.received_amount,
      s.discount_amount,
      s.discount_rate_pct,
      s.realized_unit_price,
      s.revenue_share_pct,
      sum(s.received_amount) OVER (ORDER BY s.received_amount DESC, s.dish_name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        / NULLIF(sum(s.received_amount) OVER (), 0) AS cumulative_revenue_share,
      m.median_quantity,
      m.median_revenue
    FROM sku_summary s
    CROSS JOIN medians m
  )
  SELECT
    ranked.dish_name,
    ranked.category_level1,
    ranked.category_level2,
    ranked.detail_rows,
    ranked.bill_count,
    ranked.store_count,
    ranked.sales_quantity,
    ranked.gross_amount,
    ranked.received_amount,
    ranked.discount_amount,
    ranked.discount_rate_pct,
    ranked.realized_unit_price,
    ranked.revenue_share_pct,
    ranked.cumulative_revenue_share,
    ranked.median_quantity,
    ranked.median_revenue,
    CASE
      WHEN ranked.cumulative_revenue_share <= 0.70 THEN 'A-核心'
      WHEN ranked.cumulative_revenue_share <= 0.90 THEN 'B-成长'
      ELSE 'C-长尾'
    END AS abc_class,
    CASE
      WHEN ranked.sales_quantity::double precision >= ranked.median_quantity AND ranked.received_amount::double precision >= ranked.median_revenue THEN '明星菜品'
      WHEN ranked.sales_quantity::double precision >= ranked.median_quantity AND ranked.received_amount::double precision < ranked.median_revenue THEN '引流菜品'
      WHEN ranked.sales_quantity::double precision < ranked.median_quantity AND ranked.received_amount::double precision >= ranked.median_revenue THEN '潜力菜品'
      ELSE '淘汰观察品'
    END AS sales_quadrant
  FROM ranked
$$ LANGUAGE SQL STABLE;

-- 3. fn_dish_category_summary(p_month) — 替换 dish_category_summary_april
CREATE OR REPLACE FUNCTION analytics.fn_dish_category_summary(p_month date)
RETURNS TABLE (
  category_level1 text,
  category_level2 text,
  sku_count bigint,
  bill_count bigint,
  store_count bigint,
  sales_quantity numeric,
  gross_amount numeric,
  received_amount numeric,
  discount_amount numeric,
  discount_rate_pct numeric,
  revenue_share_pct numeric
) AS $$
  SELECT
    s.category_level1,
    s.category_level2,
    count(DISTINCT s.dish_name) AS sku_count,
    count(DISTINCT ROW(s.store_code, s.bill_no)) AS bill_count,
    count(DISTINCT s.store_code) AS store_count,
    sum(s.sales_quantity) AS sales_quantity,
    sum(s.gross_amount) AS gross_amount,
    sum(s.received_amount) AS received_amount,
    sum(s.dish_discount_amount) AS discount_amount,
    round(sum(s.dish_discount_amount) / NULLIF(sum(s.gross_amount), 0) * 100, 2) AS discount_rate_pct,
    round(sum(s.received_amount) / NULLIF(sum(sum(s.received_amount)) OVER (), 0) * 100, 2) AS revenue_share_pct
  FROM analytics.fn_dish_sales(p_month) s
  GROUP BY s.category_level1, s.category_level2
$$ LANGUAGE SQL STABLE;

-- 4. fn_dish_member_sku(p_month) — 替换 dish_member_sku_april
CREATE OR REPLACE FUNCTION analytics.fn_dish_member_sku(p_month date)
RETURNS TABLE (
  member_id text,
  dish_name text,
  order_count bigint,
  purchase_days bigint,
  store_count bigint,
  sales_quantity numeric,
  received_amount numeric
) AS $$
  SELECT
    s.member_id,
    s.dish_name,
    count(DISTINCT ROW(s.store_code, s.bill_no)) AS order_count,
    count(DISTINCT s.business_date) AS purchase_days,
    count(DISTINCT s.store_code) AS store_count,
    sum(s.sales_quantity) AS sales_quantity,
    sum(s.received_amount) AS received_amount
  FROM analytics.fn_dish_sales(p_month) s
  WHERE s.member_id IS NOT NULL AND s.dish_name IS NOT NULL AND s.sales_quantity > 0
  GROUP BY s.member_id, s.dish_name
$$ LANGUAGE SQL STABLE;

-- 5. fn_dish_store_sku(p_month) — 替换 dish_store_sku_april
CREATE OR REPLACE FUNCTION analytics.fn_dish_store_sku(p_month date)
RETURNS TABLE (
  store_code text,
  store_name text,
  dish_name text,
  category_level1 text,
  category_level2 text,
  bill_count bigint,
  sales_quantity numeric,
  gross_amount numeric,
  received_amount numeric,
  discount_amount numeric,
  discount_rate_pct numeric
) AS $$
  SELECT
    s.store_code,
    min(s.store_name) AS store_name,
    s.dish_name,
    min(s.category_level1) AS category_level1,
    min(s.category_level2) AS category_level2,
    count(DISTINCT s.bill_no) AS bill_count,
    sum(s.sales_quantity) AS sales_quantity,
    sum(s.gross_amount) AS gross_amount,
    sum(s.received_amount) AS received_amount,
    sum(s.dish_discount_amount) AS discount_amount,
    round(sum(s.dish_discount_amount) / NULLIF(sum(s.gross_amount), 0) * 100, 2) AS discount_rate_pct
  FROM analytics.fn_dish_sales(p_month) s
  WHERE s.dish_name IS NOT NULL
  GROUP BY s.store_code, s.dish_name
$$ LANGUAGE SQL STABLE;

-- 6. fn_dish_pair_summary(p_month) — 替换 dish_pair_summary_april (普通表)
-- 注意: 此表由外部脚本填充，函数版本从 dish_sales_details 实时计算
CREATE OR REPLACE FUNCTION analytics.fn_dish_pair_summary(p_month date)
RETURNS TABLE (
  dish_a text,
  dish_b text,
  pair_count bigint
) AS $$
  WITH pairs AS (
    SELECT
      LEAST(a.dish_name, b.dish_name) AS dish_a,
      GREATEST(a.dish_name, b.dish_name) AS dish_b,
      count(DISTINCT a.bill_no) AS pair_count
    FROM dish_sales_details a
    JOIN dish_sales_details b
      ON a.store_code = b.store_code
      AND a.bill_no = b.bill_no
      AND a.dish_name < b.dish_name
    WHERE a.opened_at >= p_month
      AND a.opened_at < p_month + INTERVAL '1 month'
      AND b.opened_at >= p_month
      AND b.opened_at < p_month + INTERVAL '1 month'
      AND a.dish_name IS NOT NULL
      AND b.dish_name IS NOT NULL
    GROUP BY LEAST(a.dish_name, b.dish_name), GREATEST(a.dish_name, b.dish_name)
  )
  SELECT dish_a, dish_b, pair_count
  FROM pairs
  ORDER BY pair_count DESC
$$ LANGUAGE SQL STABLE;

-- 7. fn_c_sku_governance(p_month) — 替换 v_c_sku_governance_april
CREATE OR REPLACE FUNCTION analytics.fn_c_sku_governance(p_month date)
RETURNS TABLE (
  dish_name text,
  category_level1 text,
  category_level2 text,
  detail_rows bigint,
  bill_count bigint,
  store_count bigint,
  sales_quantity numeric,
  gross_amount numeric,
  received_amount numeric,
  discount_amount numeric,
  discount_rate_pct numeric,
  realized_unit_price numeric,
  revenue_share_pct numeric,
  cumulative_revenue_share numeric,
  median_quantity numeric,
  median_revenue numeric,
  abc_class text,
  sales_quadrant text,
  item_types text,
  is_combo_header boolean,
  is_combo_component boolean,
  is_single_item boolean,
  dish_code text,
  cost_source_group_count bigint,
  cost_report_sales_amount numeric,
  theoretical_cost numeric,
  actual_cost numeric,
  cost_variance_amount numeric,
  cost_report_theoretical_cost_rate_pct numeric,
  cost_report_actual_cost_rate_pct numeric,
  material_count bigint,
  exclusive_material_count bigint,
  governance_group text,
  additional_risk text
) AS $$
  WITH item_types AS (
    SELECT
      d.dish_name,
      string_agg(DISTINCT COALESCE(d.item_type, '未分类'), '、' ORDER BY (COALESCE(d.item_type, '未分类'))) AS item_types,
      bool_or(d.item_type = '套餐') AS is_combo_header,
      bool_or(d.item_type = '套餐明细菜') AS is_combo_component,
      bool_or(d.item_type = '单点') AS is_single_item
    FROM dish_sales_details d
    WHERE d.opened_at >= p_month AND d.opened_at < p_month + INTERVAL '1 month'
    GROUP BY d.dish_name
  ), material_usage AS (
    SELECT
      m.material_name,
      count(DISTINCT m.dish_name) AS used_by_dish_count
    FROM analytics.v_dish_cost_analysis_latest_material_detail m
    GROUP BY m.material_name
  ), material_profile AS (
    SELECT
      d.dish_name,
      count(DISTINCT d.material_name) AS material_count,
      count(DISTINCT d.material_name) FILTER (WHERE u.used_by_dish_count = 1) AS exclusive_material_count
    FROM analytics.v_dish_cost_analysis_latest_material_detail d
    JOIN material_usage u USING (material_name)
    GROUP BY d.dish_name
  )
  SELECT
    c.dish_name,
    c.category_level1,
    c.category_level2,
    c.detail_rows,
    c.bill_count,
    c.store_count,
    c.sales_quantity,
    c.gross_amount,
    c.received_amount,
    c.discount_amount,
    c.discount_rate_pct,
    c.realized_unit_price,
    c.revenue_share_pct,
    c.cumulative_revenue_share,
    c.median_quantity,
    c.median_revenue,
    c.abc_class,
    c.sales_quadrant,
    t.item_types,
    t.is_combo_header,
    t.is_combo_component,
    t.is_single_item,
    cost.dish_code,
    cost.source_group_count AS cost_source_group_count,
    cost.sales_amount AS cost_report_sales_amount,
    cost.theoretical_cost,
    cost.actual_cost,
    cost.cost_variance_amount,
    cost.theoretical_cost_rate_pct AS cost_report_theoretical_cost_rate_pct,
    cost.actual_cost_rate_pct AS cost_report_actual_cost_rate_pct,
    COALESCE(mp.material_count, 0) AS material_count,
    COALESCE(mp.exclusive_material_count, 0) AS exclusive_material_count,
    CASE
      WHEN c.received_amount <= 0 AND COALESCE(t.is_combo_header, false) THEN 'T1-套餐/技术项目治理'
      WHEN c.received_amount <= 0 THEN 'T2-零收入单点核查'
      WHEN c.store_count = 1 AND c.bill_count < 30 AND c.received_amount < 1000 THEN 'S1-首批停用评审'
      WHEN c.store_count <= 3 AND c.bill_count < 60 AND c.received_amount < 3000 THEN 'S2-区域低效评审'
      WHEN c.store_count > 3 AND c.bill_count < 30 AND c.received_amount < 1000 THEN 'S3-铺店不动销评审'
      WHEN c.sales_quadrant IN ('明星菜品', '潜力菜品') THEN 'K1-保留并优化'
      ELSE 'K2-继续观察'
    END AS governance_group,
    CASE
      WHEN COALESCE(mp.exclusive_material_count, 0) > 0 AND c.received_amount < 3000 THEN '高：低收入且占用独有原料'
      WHEN cost.cost_variance_amount > 0 AND cost.actual_cost > (cost.theoretical_cost * 1.2) THEN '高：成本报表显示明显超理论'
      WHEN c.discount_rate_pct >= 35 THEN '中：高折扣依赖'
      ELSE '常规'
    END AS additional_risk
  FROM analytics.fn_dish_sku_abc(p_month) c
  LEFT JOIN item_types t USING (dish_name)
  LEFT JOIN analytics.v_dish_cost_analysis_latest_dish_rollup cost USING (dish_name)
  LEFT JOIN material_profile mp USING (dish_name)
  WHERE c.abc_class = 'C-长尾'
$$ LANGUAGE SQL STABLE;