-- =============================================================
-- Migration: 20260423__app2a_add_member_order_count.sql
-- Purpose:   为 app2a 区域财务洞察增加 dws 层区域级会员订单数列
-- Related:   docs/ai/app2_finance_multi_app_design.md v2 · Phase B
-- Author:    Claude + Neo
-- =============================================================
--
-- 改动说明：
--   1. dws.dws_finance_area_daily 增加 member_order_count 列（默认 0，允许新历史数据为 0）
--   2. 重建 RLS 视图 app.v_dws_finance_area_daily，暴露新列
--   3. 若存在 dws.v_dws_finance_area_daily 同名镜像视图，同步重建
--
-- 后置依赖：
--   - ETL loader apps/etl/connectors/feiqiu/loaders/dws_finance_area_daily.py
--     需要同步改造：按 (area_code, stat_date) 聚合 DWD 订单时，
--     将 is_member_order = true 的订单数合入 member_order_count
--   - 改造前历史数据 member_order_count = 0；改造后新增数据生效
--   - 如需历史回填：scripts/ops/backfill_area_member_order.py（本迁移不含）
--
-- 回滚策略：
--   见文件末 ROLLBACK 段
--
-- 验证 SQL：
--   见文件末 VERIFICATION 段
-- =============================================================

BEGIN;

-- ------------------------------------------------------------
-- Step 1: dws 层表加列
-- ------------------------------------------------------------
ALTER TABLE dws.dws_finance_area_daily
  ADD COLUMN IF NOT EXISTS member_order_count integer NOT NULL DEFAULT 0;

COMMENT ON COLUMN dws.dws_finance_area_daily.member_order_count IS
  '会员订单数 (区域粒度, 从 DWD 聚合 is_member_order=true 的订单计数, 默认 0)';

-- ------------------------------------------------------------
-- Step 2: 重建 app schema 的 RLS 视图
--   - 保留原 RLS 过滤条件 (current_setting('app.current_site_id'))
--   - PostgreSQL 限制：CREATE OR REPLACE VIEW 仅允许末尾加列，不能中间插入
--   - 故 member_order_count 加到末尾（逻辑归属仍为单位经济，顺序不影响语义）
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW app.v_dws_finance_area_daily AS
SELECT
    id,
    site_id,
    tenant_id,
    stat_date,
    area_code,
    table_fee_amount,
    goods_amount,
    assistant_pd_amount,
    assistant_cx_amount,
    gross_amount,
    discount_groupbuy,
    discount_vip,
    discount_manual,
    discount_gift_card,
    discount_rounding,
    discount_other,
    discount_total,
    confirmed_income,
    cash_pay_amount,
    cash_paper_amount,
    scan_pay_amount,
    groupbuy_pay_amount,
    recharge_cash_inflow,
    cash_inflow_total,
    cash_outflow_total,
    cash_balance_change,
    card_consume_total,
    recharge_card_consume,
    gift_card_consume,
    recharge_cash,
    first_recharge_cash,
    renewal_cash,
    order_count,
    created_at,
    updated_at,
    member_order_count  -- 新增列（PostgreSQL CREATE OR REPLACE VIEW 限制：只能末尾加）
  FROM dws.dws_finance_area_daily
 WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

COMMENT ON VIEW app.v_dws_finance_area_daily IS
  '区域级财务日粒度 RLS 视图 | app2a 区域财务洞察数据源 | 2026-04-23 新增 member_order_count';

-- ------------------------------------------------------------
-- Step 3: 重建 dws schema 镜像 RLS 视图
--   (按 CLAUDE.md "RLS 视图双 Schema 规则" 要求，dws 和 app 两个 schema 的 RLS 视图必须同步)
-- ------------------------------------------------------------
CREATE OR REPLACE VIEW dws.v_dws_finance_area_daily AS
SELECT
    id,
    site_id,
    tenant_id,
    stat_date,
    area_code,
    table_fee_amount,
    goods_amount,
    assistant_pd_amount,
    assistant_cx_amount,
    gross_amount,
    discount_groupbuy,
    discount_vip,
    discount_manual,
    discount_gift_card,
    discount_rounding,
    discount_other,
    discount_total,
    confirmed_income,
    cash_pay_amount,
    cash_paper_amount,
    scan_pay_amount,
    groupbuy_pay_amount,
    recharge_cash_inflow,
    cash_inflow_total,
    cash_outflow_total,
    cash_balance_change,
    card_consume_total,
    recharge_card_consume,
    gift_card_consume,
    recharge_cash,
    first_recharge_cash,
    renewal_cash,
    order_count,
    created_at,
    updated_at,
    member_order_count  -- 新增列（PostgreSQL CREATE OR REPLACE VIEW 限制：只能末尾加）
  FROM dws.dws_finance_area_daily
 WHERE site_id = (current_setting('app.current_site_id'::text))::bigint;

COMMENT ON VIEW dws.v_dws_finance_area_daily IS
  '区域级财务日粒度 RLS 视图 (dws schema 镜像) | 与 app.v_dws_finance_area_daily 双 schema 规则要求下同步';

COMMIT;

-- =============================================================
-- VERIFICATION (事后手工执行)
-- =============================================================
-- 1. 列存在性
--   SELECT column_name, data_type, column_default, is_nullable
--     FROM information_schema.columns
--    WHERE table_schema = 'dws'
--      AND table_name = 'dws_finance_area_daily'
--      AND column_name = 'member_order_count';
--   -- 期望: 1 行, integer, 0, NO
--
-- 2. 视图暴露新列
--   SELECT column_name
--     FROM information_schema.columns
--    WHERE table_schema = 'app'
--      AND table_name = 'v_dws_finance_area_daily'
--      AND column_name = 'member_order_count';
--   -- 期望: 1 行
--
-- 3. 数据校验 (ETL loader 改造并跑一次后执行)
--   SET app.current_site_id = <测试门店 ID>;
--   SELECT s.site_id,
--          s.stat_date,
--          s.member_order_count              AS full_store_member_orders,
--          COALESCE(SUM(a.member_order_count), 0) AS sum_area_member_orders,
--          s.member_order_count - COALESCE(SUM(a.member_order_count), 0) AS diff
--     FROM app.v_dws_finance_daily_summary s
--     LEFT JOIN app.v_dws_finance_area_daily a
--       ON s.site_id = a.site_id AND s.stat_date = a.stat_date
--    WHERE s.stat_date >= CURRENT_DATE - INTERVAL '7 days'
--    GROUP BY s.site_id, s.stat_date, s.member_order_count
--   HAVING s.member_order_count <> COALESCE(SUM(a.member_order_count), 0);
--   -- 期望: 0 行 (全店会员订单数 = 区域之和)

-- =============================================================
-- ROLLBACK
-- =============================================================
-- BEGIN;
-- DROP VIEW IF EXISTS app.v_dws_finance_area_daily CASCADE;
-- DROP VIEW IF EXISTS dws.v_dws_finance_area_daily CASCADE;
-- ALTER TABLE dws.dws_finance_area_daily DROP COLUMN IF EXISTS member_order_count;
-- -- 重新 CREATE VIEW app.v_dws_finance_area_daily (不含新列) - 从 db/etl_feiqiu/schemas/app.sql:714 复制
-- COMMIT;
