-- ============================================================
-- Phase 9: 全量视图参数化
-- 替换 v_store_platform_economics, v_store_category_mix, v_store_member_opportunity
-- 为按月参数化函数
-- ============================================================

-- ============================================================
-- 1. fn_store_platform_economics(p_month)
--    替换 v_store_platform_economics（无月份过滤的全量聚合）
--    数据源: bill_records, 按月份过滤 c175
-- ============================================================
CREATE OR REPLACE FUNCTION analytics.fn_store_platform_economics(p_month date)
RETURNS TABLE (
  store_code text,
  store_name text,
  meituan_received numeric,
  meituan_discount numeric,
  meituan_commission numeric,
  taobao_received numeric,
  taobao_discount numeric,
  taobao_commission numeric,
  jd_received numeric,
  jd_discount numeric,
  jd_commission numeric,
  meituan_cost_rate_pct numeric,
  taobao_cost_rate_pct numeric,
  jd_cost_rate_pct numeric
)
LANGUAGE sql
STABLE
AS $function$
  SELECT
    NULLIF(bill_records.c002, '') AS store_code,
    NULLIF(bill_records.c003, '') AS store_name,
    sum(COALESCE(NULLIF(bill_records.c151, '')::numeric, 0)) AS meituan_received,
    sum(COALESCE(NULLIF(bill_records.c101, '')::numeric, 0)) AS meituan_discount,
    sum(COALESCE(NULLIF(bill_records.c097, '')::numeric, 0)) AS meituan_commission,
    sum(COALESCE(NULLIF(bill_records.c152, '')::numeric, 0)) AS taobao_received,
    sum(COALESCE(NULLIF(bill_records.c102, '')::numeric, 0)) AS taobao_discount,
    sum(COALESCE(NULLIF(bill_records.c098, '')::numeric, 0)) AS taobao_commission,
    sum(COALESCE(NULLIF(bill_records.c150, '')::numeric, 0)) AS jd_received,
    sum(COALESCE(NULLIF(bill_records.c099, '')::numeric, 0)) AS jd_discount,
    sum(COALESCE(NULLIF(bill_records.c100, '')::numeric, 0)) AS jd_commission,
    round((sum(COALESCE(NULLIF(bill_records.c101, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c097, '')::numeric, 0)))
      / NULLIF(sum(COALESCE(NULLIF(bill_records.c151, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c101, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c097, '')::numeric, 0)), 0) * 100, 2) AS meituan_cost_rate_pct,
    round((sum(COALESCE(NULLIF(bill_records.c102, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c098, '')::numeric, 0)))
      / NULLIF(sum(COALESCE(NULLIF(bill_records.c152, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c102, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c098, '')::numeric, 0)), 0) * 100, 2) AS taobao_cost_rate_pct,
    round((sum(COALESCE(NULLIF(bill_records.c099, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c100, '')::numeric, 0)))
      / NULLIF(sum(COALESCE(NULLIF(bill_records.c150, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c099, '')::numeric, 0)) + sum(COALESCE(NULLIF(bill_records.c100, '')::numeric, 0)), 0) * 100, 2) AS jd_cost_rate_pct
  FROM bill_records
  WHERE NULLIF(bill_records.c005, '') IS NOT NULL
    AND bill_records.c175 IS NOT NULL AND bill_records.c175 != ''
    AND bill_records.c175::timestamp >= analytics.fn_month_start_ts(p_month)
    AND bill_records.c175::timestamp < analytics.fn_next_month_start_ts(p_month)
  GROUP BY NULLIF(bill_records.c002, ''), NULLIF(bill_records.c003, '');
$function$;

-- ============================================================
-- 2. fn_store_category_mix(p_month)
--    替换 v_store_category_mix（无月份过滤的全量聚合）
--    数据源: bill_records, 按月份过滤 c175
-- ============================================================
CREATE OR REPLACE FUNCTION analytics.fn_store_category_mix(p_month date)
RETURNS TABLE (
  store_code text,
  store_name text,
  consumption numeric,
  lanzhou_noodle numeric,
  western_staple numeric,
  delivery_package numeric,
  night_bbq numeric,
  cold_dishes numeric,
  silk_road_food numeric,
  noodle_share_pct numeric,
  delivery_package_share_pct numeric,
  top_category_share_pct numeric,
  top_category text
)
LANGUAGE sql
STABLE
AS $function$
  SELECT
    NULLIF(bill_records.c002, '') AS store_code,
    NULLIF(bill_records.c003, '') AS store_name,
    sum(COALESCE(NULLIF(bill_records.c009, '')::numeric, 0)) AS consumption,
    sum(COALESCE(NULLIF(bill_records.c010, '')::numeric, 0)) AS lanzhou_noodle,
    sum(COALESCE(NULLIF(bill_records.c016, '')::numeric, 0)) AS western_staple,
    sum(COALESCE(NULLIF(bill_records.c027, '')::numeric, 0)) AS delivery_package,
    sum(COALESCE(NULLIF(bill_records.c013, '')::numeric, 0)) AS night_bbq,
    sum(COALESCE(NULLIF(bill_records.c015, '')::numeric, 0)) AS cold_dishes,
    sum(COALESCE(NULLIF(bill_records.c019, '')::numeric, 0)) AS silk_road_food,
    round(sum(COALESCE(NULLIF(bill_records.c010, '')::numeric, 0))
      / NULLIF(sum(COALESCE(NULLIF(bill_records.c009, '')::numeric, 0)), 0) * 100, 2) AS noodle_share_pct,
    round(sum(COALESCE(NULLIF(bill_records.c027, '')::numeric, 0))
      / NULLIF(sum(COALESCE(NULLIF(bill_records.c009, '')::numeric, 0)), 0) * 100, 2) AS delivery_package_share_pct,
    round(GREATEST(
        sum(COALESCE(NULLIF(bill_records.c010, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c016, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c027, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c013, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c015, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c019, '')::numeric, 0))
      ) / NULLIF(sum(COALESCE(NULLIF(bill_records.c009, '')::numeric, 0)), 0) * 100, 2) AS top_category_share_pct,
    CASE GREATEST(
        sum(COALESCE(NULLIF(bill_records.c010, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c016, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c027, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c013, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c015, '')::numeric, 0)),
        sum(COALESCE(NULLIF(bill_records.c019, '')::numeric, 0))
      )
      WHEN sum(COALESCE(NULLIF(bill_records.c010, '')::numeric, 0)) THEN '兰州牛肉面'
      WHEN sum(COALESCE(NULLIF(bill_records.c016, '')::numeric, 0)) THEN '西部主食'
      WHEN sum(COALESCE(NULLIF(bill_records.c027, '')::numeric, 0)) THEN '外卖套餐'
      WHEN sum(COALESCE(NULLIF(bill_records.c013, '')::numeric, 0)) THEN '夜市烧烤'
      WHEN sum(COALESCE(NULLIF(bill_records.c015, '')::numeric, 0)) THEN '爽口凉菜'
      ELSE '丝路美食'
    END AS top_category
  FROM bill_records
  WHERE NULLIF(bill_records.c005, '') IS NOT NULL
    AND bill_records.c175 IS NOT NULL AND bill_records.c175 != ''
    AND bill_records.c175::timestamp >= analytics.fn_month_start_ts(p_month)
    AND bill_records.c175::timestamp < analytics.fn_next_month_start_ts(p_month)
  GROUP BY NULLIF(bill_records.c002, ''), NULLIF(bill_records.c003, '');
$function$;

-- ============================================================
-- 3. fn_store_member_opportunity(p_month)
--    替换 v_store_member_opportunity（无月份过滤的全量聚合）
--    数据源: analytics.bill_fact, 按月份过滤 closed_at
-- ============================================================
CREATE OR REPLACE FUNCTION analytics.fn_store_member_opportunity(p_month date)
RETURNS TABLE (
  store_code text,
  store_name text,
  bill_count bigint,
  received numeric,
  member_share_pct numeric,
  company_member_share_pct numeric,
  conversion_bill_scenario numeric,
  revenue_uplift_scenario numeric
)
LANGUAGE sql
STABLE
AS $function$
  WITH company AS (
    SELECT
      count(*) FILTER (WHERE bill_fact.member_id IS NOT NULL)::numeric / count(*)::numeric AS member_share,
      avg(bill_fact.received_total) FILTER (WHERE bill_fact.member_id IS NOT NULL) AS member_avg_bill,
      avg(bill_fact.received_total) FILTER (WHERE bill_fact.member_id IS NULL) AS nonmember_avg_bill
    FROM analytics.bill_fact
    WHERE bill_fact.closed_at >= analytics.fn_month_start_ts(p_month)
      AND bill_fact.closed_at < analytics.fn_next_month_start_ts(p_month)
  ), stores AS (
    SELECT
      bill_fact.store_code,
      bill_fact.store_name,
      count(*) AS bill_count,
      count(*) FILTER (WHERE bill_fact.member_id IS NOT NULL)::numeric / count(*)::numeric AS member_share,
      sum(bill_fact.received_total) AS received
    FROM analytics.bill_fact
    WHERE bill_fact.closed_at >= analytics.fn_month_start_ts(p_month)
      AND bill_fact.closed_at < analytics.fn_next_month_start_ts(p_month)
    GROUP BY bill_fact.store_code, bill_fact.store_name
  )
  SELECT
    s.store_code,
    s.store_name,
    s.bill_count,
    round(s.received, 2) AS received,
    round(s.member_share * 100, 2) AS member_share_pct,
    round(c.member_share * 100, 2) AS company_member_share_pct,
    round(GREATEST(c.member_share - s.member_share, 0) * s.bill_count::numeric, 0) AS conversion_bill_scenario,
    round(GREATEST(c.member_share - s.member_share, 0) * s.bill_count::numeric * (c.member_avg_bill - c.nonmember_avg_bill), 2) AS revenue_uplift_scenario
  FROM stores s
  CROSS JOIN company c;
$function$;