-- =============================================================================
-- ETL 库（etl_feiqiu）/ app schema —— RLS 视图业务日上界裁剪
-- 由 scripts/ops/gen_rls_business_date_migration.py 自动生成。
-- 沙箱模式下，业务读取层只看到 sandbox_date 及之前的数据。
-- =============================================================================

BEGIN;

-- helper：业务日 GUC 读取，缺省回退当前真实日期
CREATE OR REPLACE FUNCTION app.business_date_now()
RETURNS date
LANGUAGE sql
STABLE
AS $$
  SELECT COALESCE(
    NULLIF(current_setting('app.current_business_date', true), '')::date,
    CURRENT_DATE
  );
$$;
COMMENT ON FUNCTION app.business_date_now() IS
'返回当前业务日（GUC app.current_business_date），未设置时回退 CURRENT_DATE。';


-- app.v_dws_finance_area_daily：加业务日上界 → stat_date <= app.business_date_now()
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
   FROM dws.dws_finance_area_daily
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_finance_daily_summary：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_finance_daily_summary AS
 SELECT id,
    site_id,
    tenant_id,
    stat_date,
    gross_amount,
    table_fee_amount,
    goods_amount,
    assistant_pd_amount,
    assistant_cx_amount,
    discount_total,
    discount_groupbuy,
    discount_vip,
    discount_gift_card,
    discount_manual,
    discount_rounding,
    discount_other,
    confirmed_income,
    cash_inflow_total,
    cash_pay_amount,
    cash_paper_amount,
    scan_pay_amount,
    groupbuy_pay_amount,
    platform_settlement_amount,
    platform_fee_amount,
    recharge_cash_inflow,
    card_consume_total,
    recharge_card_consume AS cash_card_consume,
    gift_card_consume,
    cash_outflow_total,
    cash_balance_change,
    recharge_count,
    recharge_total,
    recharge_cash,
    recharge_gift,
    first_recharge_count,
    first_recharge_amount,
    renewal_count,
    renewal_amount,
    order_count,
    member_order_count,
    guest_order_count,
    avg_order_amount,
    created_at,
    updated_at
   FROM dws.dws_finance_daily_summary
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_finance_discount_detail：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_finance_discount_detail AS
 SELECT id,
    site_id,
    tenant_id,
    stat_date,
    discount_type_code,
    discount_type_name,
    discount_amount,
    discount_ratio,
    usage_count,
    affected_orders,
    created_at,
    updated_at
   FROM dws.dws_finance_discount_detail
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_finance_expense_summary：加业务日上界 → expense_month <= date_trunc('month', app.business_date_now())::date
CREATE OR REPLACE VIEW app.v_dws_finance_expense_summary AS
 SELECT id,
    site_id,
    tenant_id,
    expense_month,
    expense_type_code,
    expense_type_name,
    expense_category,
    expense_amount,
    expense_detail,
    import_batch_no,
    import_file_name,
    import_time,
    import_user,
    remark,
    created_at,
    updated_at
   FROM dws.dws_finance_expense_summary
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND expense_month <= date_trunc('month'::text, app.business_date_now()::timestamp with time zone)::date
    AND expense_month <= date_trunc('month', app.business_date_now())::date;


-- app.v_dws_finance_income_structure：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_finance_income_structure AS
 SELECT id,
    site_id,
    tenant_id,
    stat_date,
    structure_type,
    category_code,
    category_name,
    income_amount,
    income_ratio,
    order_count,
    duration_minutes,
    created_at,
    updated_at
   FROM dws.dws_finance_income_structure
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_finance_recharge_summary：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_finance_recharge_summary AS
 SELECT id,
    site_id,
    tenant_id,
    stat_date,
    recharge_count,
    recharge_total,
    recharge_cash,
    recharge_gift,
    first_recharge_count,
    first_recharge_cash,
    first_recharge_gift,
    first_recharge_total,
    renewal_count,
    renewal_cash,
    renewal_gift,
    renewal_total,
    recharge_member_count,
    new_member_count,
    total_card_balance,
    cash_card_balance,
    gift_card_balance,
    gift_liquor_balance,
    gift_table_fee_balance,
    gift_voucher_balance,
    gift_liquor_recharge,
    gift_table_fee_recharge,
    gift_voucher_recharge,
    created_at,
    updated_at
   FROM dws.dws_finance_recharge_summary
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_assistant_daily：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_assistant_daily AS
 SELECT id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    stat_date,
    total_service_count,
    total_hours,
    base_hours,
    bonus_hours,
    room_hours,
    total_ledger_amount,
    unique_customers,
    unique_tables,
    created_at
   FROM dws.dws_assistant_daily_detail d
  WHERE stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_assistant_daily_detail：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_assistant_daily_detail AS
 SELECT id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    stat_date,
    assistant_level_code,
    assistant_level_name,
    total_service_count,
    base_service_count,
    bonus_service_count,
    room_service_count,
    total_seconds,
    base_seconds,
    bonus_seconds,
    room_seconds,
    total_hours,
    base_hours,
    bonus_hours,
    room_hours,
    total_ledger_amount,
    base_ledger_amount,
    bonus_ledger_amount,
    room_ledger_amount,
    unique_customers,
    unique_tables,
    trashed_seconds,
    trashed_count,
    created_at,
    updated_at,
    penalty_minutes,
    penalty_reason,
    is_exempt,
    per_hour_contribution
   FROM dws.dws_assistant_daily_detail
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_assistant_finance_analysis：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_assistant_finance_analysis AS
 SELECT id,
    site_id,
    tenant_id,
    stat_date,
    assistant_id,
    assistant_nickname,
    revenue_total,
    revenue_base,
    revenue_bonus,
    revenue_room,
    cost_daily,
    gross_profit,
    gross_margin,
    service_count,
    service_hours,
    room_service_count,
    room_service_hours,
    unique_customers,
    created_at,
    updated_at
   FROM dws.dws_assistant_finance_analysis
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now();


-- app.v_dws_assistant_monthly_summary：加业务日上界 → stat_month <= date_trunc('month', app.business_date_now())::date
CREATE OR REPLACE VIEW app.v_dws_assistant_monthly_summary AS
 SELECT id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    stat_month,
    assistant_level_code,
    assistant_level_name,
    hire_date,
    is_new_hire,
    work_days,
    total_service_count,
    base_service_count,
    bonus_service_count,
    room_service_count,
    total_hours,
    base_hours,
    bonus_hours,
    room_hours,
    effective_hours,
    trashed_hours,
    total_ledger_amount,
    base_ledger_amount,
    bonus_ledger_amount,
    room_ledger_amount,
    unique_customers,
    unique_tables,
    avg_service_seconds,
    tier_id,
    tier_code,
    tier_name,
    rank_by_hours,
    rank_with_ties,
    created_at,
    updated_at
   FROM dws.dws_assistant_monthly_summary
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_month <= date_trunc('month'::text, app.business_date_now()::timestamp with time zone)::date
    AND stat_month <= date_trunc('month', app.business_date_now())::date;


-- app.v_dws_assistant_salary_calc：加业务日上界 → salary_month <= date_trunc('month', app.business_date_now())::date
CREATE OR REPLACE VIEW app.v_dws_assistant_salary_calc AS
 SELECT id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    salary_month,
    assistant_level_code,
    assistant_level_name,
    hire_date,
    is_new_hire,
    effective_hours,
    base_hours,
    bonus_hours,
    room_hours,
    tier_id,
    tier_code,
    tier_name,
    rank_with_ties,
    base_course_price,
    bonus_course_price,
    base_deduction,
    bonus_deduction_ratio,
    base_income,
    bonus_income,
    room_income,
    total_course_income,
    sprint_bonus,
    top_rank_bonus,
    recharge_commission,
    other_bonus,
    total_bonus,
    gross_salary,
    vacation_days,
    vacation_unlimited,
    calc_notes,
    created_at,
    updated_at
   FROM dws.dws_assistant_salary_calc
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND salary_month <= date_trunc('month'::text, app.business_date_now()::timestamp with time zone)::date
    AND salary_month <= date_trunc('month', app.business_date_now())::date;


-- app.v_dws_member_consumption_summary：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_consumption_summary AS
 SELECT DISTINCT ON (member_id) id,
    site_id,
    tenant_id,
    member_id,
    stat_date,
    member_nickname,
    member_mobile,
    card_grade_name,
    register_date,
    first_consume_date,
    last_consume_date,
    total_visit_count,
    total_consume_amount,
    total_recharge_amount,
    total_table_fee,
    total_goods_amount,
    total_assistant_amount,
    visit_count_7d,
    visit_count_10d,
    visit_count_15d,
    visit_count_30d,
    visit_count_60d,
    visit_count_90d,
    consume_amount_7d,
    consume_amount_10d,
    consume_amount_15d,
    consume_amount_30d,
    consume_amount_60d,
    consume_amount_90d,
    cash_card_balance,
    gift_card_balance,
    total_card_balance,
    days_since_last,
    is_active_7d,
    is_active_30d,
    is_active_90d,
    customer_tier,
    created_at,
    updated_at,
    recharge_count_30d,
    recharge_count_60d,
    recharge_count_90d,
    recharge_amount_30d,
    recharge_amount_60d,
    recharge_amount_90d,
    avg_ticket_amount
   FROM dws.dws_member_consumption_summary
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND stat_date <= app.business_date_now()
    AND stat_date <= app.business_date_now()
  ORDER BY member_id, stat_date DESC;


-- app.v_dws_member_visit_detail：加业务日上界 → visit_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_visit_detail AS
 SELECT id,
    site_id,
    tenant_id,
    member_id,
    order_settle_id,
    visit_date,
    visit_time,
    member_nickname,
    member_mobile,
    member_birthday,
    table_id,
    table_name,
    area_name,
    area_category,
    table_fee,
    goods_amount,
    assistant_amount,
    total_consume,
    total_discount,
    actual_pay,
    cash_pay,
    balance_pay AS cash_card_pay,
    gift_card_pay,
    groupbuy_pay,
    table_duration_min,
    assistant_duration_min,
    assistant_services,
    created_at,
    updated_at
   FROM dws.dws_member_visit_detail
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND visit_date <= app.business_date_now()
    AND visit_date <= app.business_date_now();


-- app.v_dws_member_winback_index：加业务日上界 → COALESCE(last_visit_time::date, '0001-01-01'::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_winback_index AS
 SELECT DISTINCT ON (member_id) winback_id,
    site_id,
    tenant_id,
    member_id,
    status,
    segment,
    member_create_time,
    first_visit_time,
    last_visit_time,
    last_recharge_time,
    t_v,
    t_r,
    t_a,
    visits_14d,
    visits_30d,
    visits_60d,
    visits_total,
    spend_30d,
    spend_180d,
    sv_balance,
    recharge_60d_amt,
    interval_count,
    overdue_old,
    drop_old,
    recharge_old,
    value_old,
    raw_score,
    display_score,
    last_wechat_touch_time,
    calc_time,
    calc_version,
    created_at,
    updated_at,
    overdue_cdf_p,
    ideal_interval_days,
    ideal_next_visit_date,
    stat_date
   FROM dws.dws_member_winback_index
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND COALESCE(last_visit_time::date, '0001-01-01'::date) <= app.business_date_now()
    AND COALESCE(last_visit_time::date, '0001-01-01'::date) <= app.business_date_now()
  ORDER BY member_id, stat_date DESC;


-- app.v_dwd_settlement_head：加业务日上界 → COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dwd_settlement_head AS
 SELECT order_settle_id,
    tenant_id,
    site_id,
    site_name,
    table_id,
    settle_name,
    order_trade_no,
    create_time,
    pay_time,
    settle_type,
    revoke_order_id,
    member_id,
    member_name,
    member_phone,
    member_card_account_id,
    member_card_type_name,
    is_bind_member,
    member_discount_amount,
    consume_money,
    table_charge_money,
    goods_money,
    real_goods_money,
    assistant_pd_money,
    assistant_cx_money,
    adjust_amount,
    pay_amount,
    balance_amount,
    recharge_card_amount,
    gift_card_amount,
    coupon_amount,
    rounding_amount,
    point_amount,
    electricity_money,
    real_electricity_money,
    electricity_adjust_money,
    pl_coupon_sale_amount,
    mervou_sales_amount
   FROM dwd.dwd_settlement_head
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
    AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now();


-- app.v_dwd_assistant_service_log：加业务日上界 → COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dwd_assistant_service_log AS
 SELECT assistant_service_id,
    order_trade_no,
    order_settle_id,
    order_pay_id,
    order_assistant_id,
    order_assistant_type,
    tenant_id,
    site_id,
    site_table_id,
    tenant_member_id,
    system_member_id,
    assistant_no,
    nickname,
    site_assistant_id,
    user_id,
    assistant_team_id,
    person_org_id,
    assistant_level,
    level_name,
    skill_id,
    skill_name,
    ledger_unit_price,
    ledger_amount,
    projected_income,
    coupon_deduct_money,
    income_seconds,
    real_use_seconds,
    add_clock,
    create_time,
    start_use_time,
    last_use_time,
    is_delete,
    real_service_money
   FROM dwd.dwd_assistant_service_log
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
    AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now();


-- app.v_dwd_recharge_order：加业务日上界 → COALESCE(pay_time::date, '0001-01-01'::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dwd_recharge_order AS
 SELECT recharge_order_id,
    tenant_id,
    site_id,
    member_id,
    member_name_snapshot,
    member_phone_snapshot,
    tenant_member_card_id,
    member_card_type_name,
    settle_relate_id,
    settle_type,
    settle_name,
    is_first,
    pay_amount,
    refund_amount,
    point_amount,
    cash_amount,
    payment_method,
    create_time,
    pay_time,
    pl_coupon_sale_amount,
    mervou_sales_amount,
    electricity_money,
    real_electricity_money,
    electricity_adjust_money
   FROM dwd.dwd_recharge_order
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND COALESCE(pay_time::date, '0001-01-01'::date) <= app.business_date_now()
    AND COALESCE(pay_time::date, '0001-01-01'::date) <= app.business_date_now();


-- app.v_dwd_store_goods_sale：加业务日上界 → COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dwd_store_goods_sale AS
 SELECT store_goods_sale_id,
    order_trade_no,
    order_settle_id,
    order_pay_id,
    order_goods_id,
    site_id,
    tenant_id,
    site_goods_id,
    tenant_goods_id,
    tenant_goods_category_id,
    tenant_goods_business_id,
    site_table_id,
    ledger_name,
    ledger_group_name,
    ledger_unit_price,
    ledger_count,
    ledger_amount,
    discount_money,
    real_goods_money,
    cost_money,
    ledger_status,
    is_delete,
    create_time,
    coupon_share_money,
    discount_price
   FROM dwd.dwd_store_goods_sale
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
    AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now();


-- app.v_dwd_table_fee_log：加业务日上界 → COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dwd_table_fee_log AS
 SELECT table_fee_log_id,
    order_trade_no,
    order_settle_id,
    order_pay_id,
    tenant_id,
    site_id,
    site_table_id,
    site_table_area_id,
    site_table_area_name,
    tenant_table_area_id,
    member_id,
    ledger_name,
    ledger_unit_price,
    ledger_count,
    ledger_amount,
    real_table_charge_money,
    coupon_promotion_amount,
    member_discount_amount,
    adjust_amount,
    real_table_use_seconds,
    add_clock_seconds,
    start_use_time,
    ledger_end_time,
    create_time,
    ledger_status,
    is_single_order,
    is_delete,
    activity_discount_amount,
    real_service_money
   FROM dwd.dwd_table_fee_log
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now()
    AND COALESCE(create_time::date, '0001-01-01'::date) <= app.business_date_now();


-- app.v_cfg_assistant_level_price：加业务日上界 → effective_from <= app.business_date_now() AND effective_to >= app.business_date_now()
CREATE OR REPLACE VIEW app.v_cfg_assistant_level_price AS
 SELECT price_id,
    level_code,
    level_name,
    base_course_price,
    bonus_course_price,
    effective_from,
    effective_to,
    description,
    created_at,
    updated_at
   FROM dws.cfg_assistant_level_price
  WHERE effective_from <= app.business_date_now() AND effective_to >= app.business_date_now()
    AND effective_from <= app.business_date_now() AND effective_to >= app.business_date_now();


-- app.v_cfg_performance_tier：加业务日上界 → effective_from <= app.business_date_now() AND effective_to >= app.business_date_now()
CREATE OR REPLACE VIEW app.v_cfg_performance_tier AS
 SELECT tier_id,
    tier_code,
    tier_name,
    tier_level,
    min_hours,
    max_hours,
    base_deduction,
    bonus_deduction_ratio,
    vacation_days,
    vacation_unlimited,
    is_new_hire_tier,
    effective_from,
    effective_to,
    description,
    created_at,
    updated_at
   FROM dws.cfg_performance_tier
  WHERE effective_from <= app.business_date_now() AND effective_to >= app.business_date_now()
    AND effective_from <= app.business_date_now() AND effective_to >= app.business_date_now();


-- app.v_cfg_bonus_rules：加业务日上界 → effective_from <= app.business_date_now() AND effective_to >= app.business_date_now()
CREATE OR REPLACE VIEW app.v_cfg_bonus_rules AS
 SELECT rule_id,
    rule_type,
    rule_code,
    rule_name,
    threshold_hours,
    rank_position,
    bonus_amount,
    is_cumulative,
    priority,
    effective_from,
    effective_to,
    description,
    created_at,
    updated_at
   FROM dws.cfg_bonus_rules
  WHERE effective_from <= app.business_date_now() AND effective_to >= app.business_date_now();


-- app.v_cfg_index_parameters：加业务日上界 → effective_from <= app.business_date_now() AND effective_to >= app.business_date_now()
CREATE OR REPLACE VIEW app.v_cfg_index_parameters AS
 SELECT param_id,
    index_type,
    param_name,
    param_value,
    description,
    effective_from,
    effective_to,
    created_at,
    updated_at
   FROM dws.cfg_index_parameters
  WHERE effective_from <= app.business_date_now() AND effective_to >= app.business_date_now();


-- app.v_dws_assistant_customer_stats：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_assistant_customer_stats AS
 SELECT DISTINCT ON (assistant_id, member_id) id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    member_id,
    member_nickname,
    member_mobile,
    stat_date,
    first_service_date,
    last_service_date,
    total_service_count,
    total_service_hours,
    total_service_amount,
    service_count_7d,
    service_count_10d,
    service_count_15d,
    service_count_30d,
    service_count_60d,
    service_count_90d,
    service_hours_7d,
    service_hours_10d,
    service_hours_15d,
    service_hours_30d,
    service_hours_60d,
    service_hours_90d,
    service_amount_7d,
    service_amount_10d,
    service_amount_15d,
    service_amount_30d,
    service_amount_60d,
    service_amount_90d,
    days_since_last,
    is_active_7d,
    is_active_30d,
    created_at,
    updated_at
   FROM dws.dws_assistant_customer_stats
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND stat_date <= app.business_date_now()
  ORDER BY assistant_id, member_id, stat_date DESC;


-- app.v_dws_assistant_order_contribution：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_assistant_order_contribution AS
 SELECT contribution_id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    stat_date,
    order_gross_revenue,
    order_net_revenue,
    time_weighted_revenue,
    time_weighted_net_revenue,
    order_count,
    total_service_seconds,
    created_at,
    updated_at
   FROM dws.dws_assistant_order_contribution
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND stat_date <= app.business_date_now();


-- app.v_dws_assistant_project_tag：加业务日上界 → computed_at::date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_assistant_project_tag AS
 SELECT id,
    site_id,
    tenant_id,
    assistant_id,
    time_window,
    category_code,
    category_name,
    short_name,
    duration_seconds,
    total_seconds,
    percentage,
    is_tagged,
    computed_at,
    created_at,
    updated_at
   FROM dws.dws_assistant_project_tag
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND computed_at::date <= app.business_date_now();


-- app.v_dws_assistant_recharge_commission：加业务日上界 → commission_month <= date_trunc('month', app.business_date_now())::date
CREATE OR REPLACE VIEW app.v_dws_assistant_recharge_commission AS
 SELECT id,
    site_id,
    tenant_id,
    assistant_id,
    assistant_nickname,
    commission_month,
    recharge_order_id,
    recharge_order_no,
    recharge_amount,
    commission_amount,
    commission_ratio,
    import_batch_no,
    import_file_name,
    import_time,
    import_user,
    remark,
    created_at,
    updated_at
   FROM dws.dws_assistant_recharge_commission
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND commission_month <= date_trunc('month', app.business_date_now())::date;


-- app.v_dws_coach_area_hours：加业务日上界 → stat_month <= date_trunc('month', app.business_date_now())::date
CREATE OR REPLACE VIEW app.v_dws_coach_area_hours AS
 SELECT id,
    site_id,
    tenant_id,
    stat_month,
    assistant_id,
    area_code,
    base_hours,
    bonus_hours,
    room_hours,
    effective_hours,
    trashed_hours,
    base_service_count,
    bonus_service_count,
    room_service_count,
    created_at,
    updated_at
   FROM dws.dws_coach_area_hours
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND stat_month <= date_trunc('month', app.business_date_now())::date;


-- app.v_dws_finance_board_cache：加业务日上界 → computed_at::date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_finance_board_cache AS
 SELECT id,
    site_id,
    time_range,
    area_code,
    start_date,
    end_date,
    prev_start_date,
    prev_end_date,
    occurrence,
    discount,
    discount_rate,
    confirmed_revenue,
    cash_in,
    cash_out,
    cash_balance,
    balance_rate,
    data_fingerprint,
    computed_at,
    created_at,
    updated_at
   FROM dws.dws_finance_board_cache
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND computed_at::date <= app.business_date_now();


-- app.v_dws_member_assistant_intimacy：加业务日上界 → calc_time::date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_assistant_intimacy AS
 SELECT intimacy_id,
    site_id,
    tenant_id,
    member_id,
    assistant_id,
    session_count,
    total_duration_minutes,
    basic_session_count,
    incentive_session_count,
    days_since_last_session,
    attributed_recharge_count,
    attributed_recharge_amount,
    score_frequency,
    score_recency,
    score_recharge,
    score_duration,
    burst_multiplier,
    raw_score,
    display_score,
    calc_time,
    calc_version,
    created_at,
    updated_at
   FROM dws.dws_member_assistant_intimacy
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND calc_time::date <= app.business_date_now();


-- app.v_dws_member_assistant_relation_index：加业务日上界 → COALESCE(stat_date, calc_time::date) <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_assistant_relation_index AS
 SELECT DISTINCT ON (member_id, assistant_id) relation_id,
    site_id,
    tenant_id,
    member_id,
    assistant_id,
    session_count,
    total_duration_minutes,
    basic_session_count,
    incentive_session_count,
    days_since_last_session,
    rs_f,
    rs_d,
    rs_r,
    rs_raw,
    rs_display,
    os_share,
    os_label,
    os_rank,
    ms_f_short,
    ms_f_long,
    ms_raw,
    ms_display,
    ml_order_count,
    ml_allocated_amount,
    ml_raw,
    ml_display,
    calc_time,
    created_at,
    updated_at,
    recall_created_total,
    recall_completed_total,
    follow_up_created_total,
    follow_up_completed_total,
    total_created,
    total_completed,
    stat_date
   FROM dws.dws_member_assistant_relation_index
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND COALESCE(stat_date, calc_time::date) <= app.business_date_now()
  ORDER BY member_id, assistant_id, stat_date DESC;


-- app.v_dws_member_newconv_index：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_newconv_index AS
 SELECT DISTINCT ON (member_id) newconv_id,
    site_id,
    tenant_id,
    member_id,
    status,
    segment,
    member_create_time,
    first_visit_time,
    last_visit_time,
    last_recharge_time,
    t_v,
    t_r,
    t_a,
    visits_14d,
    visits_30d,
    visits_60d,
    visits_total,
    spend_30d,
    spend_180d,
    sv_balance,
    recharge_60d_amt,
    interval_count,
    need_new,
    salvage_new,
    recharge_new,
    value_new,
    welcome_new,
    raw_score_welcome,
    raw_score_convert,
    raw_score,
    display_score_welcome,
    display_score_convert,
    display_score,
    last_wechat_touch_time,
    calc_time,
    calc_version,
    created_at,
    updated_at,
    stat_date
   FROM dws.dws_member_newconv_index
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND stat_date <= app.business_date_now()
  ORDER BY member_id, stat_date DESC;


-- app.v_dws_member_project_tag：加业务日上界 → computed_at::date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_project_tag AS
 SELECT id,
    site_id,
    tenant_id,
    member_id,
    time_window,
    category_code,
    category_name,
    short_name,
    duration_seconds,
    total_seconds,
    percentage,
    is_tagged,
    computed_at,
    created_at,
    updated_at
   FROM dws.dws_member_project_tag
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND computed_at::date <= app.business_date_now();


-- app.v_dws_member_spending_power_index：加业务日上界 → calc_time::date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_member_spending_power_index AS
 SELECT spi_id,
    site_id,
    member_id,
    spend_30,
    spend_90,
    recharge_90,
    orders_30,
    orders_90,
    visit_days_30,
    visit_days_90,
    avg_ticket_90,
    active_weeks_90,
    daily_spend_ewma_90,
    score_level_raw,
    score_speed_raw,
    score_stability_raw,
    score_level_display,
    score_speed_display,
    score_stability_display,
    raw_score,
    display_score,
    calc_time,
    created_at,
    updated_at
   FROM dws.dws_member_spending_power_index
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND calc_time::date <= app.business_date_now();


-- app.v_dws_order_summary：加业务日上界 → order_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_order_summary AS
 SELECT site_id,
    order_settle_id,
    order_trade_no,
    order_date,
    tenant_id,
    member_id,
    member_flag,
    recharge_order_flag,
    item_count,
    total_item_quantity,
    table_fee_amount,
    assistant_service_amount,
    goods_amount,
    group_amount,
    total_coupon_deduction,
    member_discount_amount,
    manual_discount_amount,
    order_original_amount,
    order_final_amount,
    stored_card_deduct,
    external_paid_amount,
    total_paid_amount,
    book_table_flow,
    book_assistant_flow,
    book_goods_flow,
    book_group_flow,
    book_order_flow,
    order_effective_consume_cash,
    order_effective_recharge_cash,
    order_effective_flow,
    refund_amount,
    net_income,
    created_at,
    updated_at
   FROM dws.dws_order_summary
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND order_date <= app.business_date_now();


-- app.v_dws_platform_settlement：加业务日上界 → settlement_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_dws_platform_settlement AS
 SELECT id,
    site_id,
    tenant_id,
    settlement_date,
    platform_type,
    platform_name,
    platform_order_no,
    order_settle_id,
    settlement_amount,
    commission_amount,
    service_fee,
    gross_amount,
    import_batch_no,
    import_file_name,
    import_time,
    import_user,
    remark,
    created_at,
    updated_at
   FROM dws.dws_platform_settlement
  WHERE site_id = current_setting('app.current_site_id'::text)::bigint
    AND settlement_date <= app.business_date_now();


-- app.v_finance_daily：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_finance_daily AS
 SELECT id,
    site_id,
    tenant_id,
    stat_date,
    gross_amount,
    table_fee_amount,
    goods_amount,
    assistant_pd_amount,
    assistant_cx_amount,
    discount_total,
    confirmed_income,
    cash_inflow_total,
    recharge_count,
    recharge_total,
    order_count,
    member_order_count,
    guest_order_count,
    avg_order_amount,
    created_at
   FROM dws.dws_finance_daily_summary f
  WHERE stat_date <= app.business_date_now();


-- app.v_member_consumption：加业务日上界 → stat_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_member_consumption AS
 SELECT id,
    site_id,
    tenant_id,
    member_id,
    stat_date,
    member_nickname,
    card_grade_name,
    total_visit_count,
    total_consume_amount,
    total_recharge_amount,
    last_consume_date,
    first_consume_date,
    days_since_last,
    customer_tier,
    created_at
   FROM dws.dws_member_consumption_summary mc
  WHERE stat_date <= app.business_date_now();


-- app.v_order_summary：加业务日上界 → order_date <= app.business_date_now()
CREATE OR REPLACE VIEW app.v_order_summary AS
 SELECT site_id,
    order_settle_id,
    order_trade_no,
    order_date,
    tenant_id,
    member_id,
    member_flag,
    order_original_amount,
    order_final_amount,
    total_paid_amount,
    refund_amount,
    net_income,
    created_at
   FROM dws.dws_order_summary os
  WHERE order_date <= app.business_date_now();


COMMIT;


-- 回滚：DROP FUNCTION app.business_date_now() CASCADE;
-- 然后重新执行 db/etl_feiqiu/schemas/app.sql 即可恢复 live 行为
