-- =============================================================================
-- FDW 外部表扩展 — DWS 层新表 + 已有表新增字段
-- 目标库：test_zqyy_app（测试）/ zqyy_app（生产）
-- 前提：ETL 库 app schema 已创建/更新对应 RLS 视图
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. 新建：助教订单流水统计 FDW 外部表
-- -----------------------------------------------------------------------------
CREATE FOREIGN TABLE IF NOT EXISTS fdw_etl.v_dws_assistant_order_contribution (
    contribution_id     BIGINT,
    site_id             INTEGER,
    tenant_id           INTEGER,
    assistant_id        BIGINT,
    assistant_nickname  VARCHAR(100),
    stat_date           DATE,
    order_gross_revenue         NUMERIC(14,2),
    order_net_revenue           NUMERIC(14,2),
    time_weighted_revenue       NUMERIC(14,2),
    time_weighted_net_revenue   NUMERIC(14,2),
    order_count                 INTEGER,
    total_service_seconds       INTEGER,
    created_at  TIMESTAMP WITH TIME ZONE,
    updated_at  TIMESTAMP WITH TIME ZONE
) SERVER etl_server
OPTIONS (schema_name 'app', table_name 'v_dws_assistant_order_contribution');

-- -----------------------------------------------------------------------------
-- 2. 重建：会员消费汇总 FDW 外部表（新增充值窗口 + 次均消费字段）
-- -----------------------------------------------------------------------------
DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_member_consumption_summary;

CREATE FOREIGN TABLE fdw_etl.v_dws_member_consumption_summary (
    id                      BIGINT,
    site_id                 BIGINT,
    tenant_id               BIGINT,
    member_id               BIGINT,
    stat_date               DATE,
    member_nickname         VARCHAR(100),
    member_mobile           VARCHAR(20),
    card_grade_name         VARCHAR(50),
    register_date           DATE,
    first_consume_date      DATE,
    last_consume_date       DATE,
    total_visit_count       INTEGER,
    total_consume_amount    NUMERIC(14,2),
    total_recharge_amount   NUMERIC(14,2),
    total_table_fee         NUMERIC(14,2),
    total_goods_amount      NUMERIC(14,2),
    total_assistant_amount  NUMERIC(14,2),
    visit_count_7d          INTEGER,
    visit_count_10d         INTEGER,
    visit_count_15d         INTEGER,
    visit_count_30d         INTEGER,
    visit_count_60d         INTEGER,
    visit_count_90d         INTEGER,
    consume_amount_7d       NUMERIC(14,2),
    consume_amount_10d      NUMERIC(14,2),
    consume_amount_15d      NUMERIC(14,2),
    consume_amount_30d      NUMERIC(14,2),
    consume_amount_60d      NUMERIC(14,2),
    consume_amount_90d      NUMERIC(14,2),
    cash_card_balance       NUMERIC(14,2),
    gift_card_balance       NUMERIC(14,2),
    total_card_balance      NUMERIC(14,2),
    days_since_last         INTEGER,
    is_active_7d            BOOLEAN,
    is_active_30d           BOOLEAN,
    is_active_90d           BOOLEAN,
    customer_tier           VARCHAR(20),
    created_at              TIMESTAMP WITH TIME ZONE,
    updated_at              TIMESTAMP WITH TIME ZONE,
    recharge_count_30d      INTEGER,
    recharge_count_60d      INTEGER,
    recharge_count_90d      INTEGER,
    recharge_amount_30d     NUMERIC(14,2),
    recharge_amount_60d     NUMERIC(14,2),
    recharge_amount_90d     NUMERIC(14,2),
    avg_ticket_amount       NUMERIC(14,2)
) SERVER etl_server
OPTIONS (schema_name 'app', table_name 'v_dws_member_consumption_summary');


-- -----------------------------------------------------------------------------
-- 3. 重建：助教日度明细 FDW 外部表（新增惩罚字段）
-- -----------------------------------------------------------------------------
DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_assistant_daily_detail;

CREATE FOREIGN TABLE fdw_etl.v_dws_assistant_daily_detail (
    id                      BIGINT,
    site_id                 BIGINT,
    tenant_id               BIGINT,
    assistant_id            BIGINT,
    assistant_nickname      VARCHAR(50),
    stat_date               DATE,
    assistant_level_code    INTEGER,
    assistant_level_name    VARCHAR(20),
    total_service_count     INTEGER,
    base_service_count      INTEGER,
    bonus_service_count     INTEGER,
    room_service_count      INTEGER,
    total_seconds           INTEGER,
    base_seconds            INTEGER,
    bonus_seconds           INTEGER,
    room_seconds            INTEGER,
    total_hours             NUMERIC(10,2),
    base_hours              NUMERIC(10,2),
    bonus_hours             NUMERIC(10,2),
    room_hours              NUMERIC(10,2),
    total_ledger_amount     NUMERIC(12,2),
    base_ledger_amount      NUMERIC(12,2),
    bonus_ledger_amount     NUMERIC(12,2),
    room_ledger_amount      NUMERIC(12,2),
    unique_customers        INTEGER,
    unique_tables           INTEGER,
    trashed_seconds         INTEGER,
    trashed_count           INTEGER,
    created_at              TIMESTAMP WITH TIME ZONE,
    updated_at              TIMESTAMP WITH TIME ZONE,
    penalty_minutes         NUMERIC(10,2),
    penalty_reason          TEXT,
    is_exempt               BOOLEAN,
    per_hour_contribution   NUMERIC(14,2)
) SERVER etl_server
OPTIONS (schema_name 'app', table_name 'v_dws_assistant_daily_detail');

-- -----------------------------------------------------------------------------
-- 授权
-- -----------------------------------------------------------------------------
GRANT SELECT ON fdw_etl.v_dws_assistant_order_contribution TO app_user;

-- =============================================================================
-- 回滚脚本（按逆序执行）
-- =============================================================================
-- REVOKE SELECT ON fdw_etl.v_dws_assistant_order_contribution FROM app_user;
-- DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_assistant_daily_detail;
-- DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_member_consumption_summary;
-- DROP FOREIGN TABLE IF EXISTS fdw_etl.v_dws_assistant_order_contribution;
-- 然后重建旧版本的 v_dws_assistant_daily_detail 和 v_dws_member_consumption_summary（不含新字段）
