-- Data warehouse schema for the entertainment chain (ODS -> DWD -> DWS) -- ASCII only to keep cross-platform friendly. DROP SCHEMA IF EXISTS billiards_ods CASCADE; CREATE SCHEMA IF NOT EXISTS billiards_ods; CREATE SCHEMA IF NOT EXISTS billiards_dwd; CREATE SCHEMA IF NOT EXISTS billiards_dws; -- ---------- ODS (raw, lightly typed) ---------- -- Each ODS table keeps the source payload for replay/debug while exposing key fields. CREATE TABLE IF NOT EXISTS billiards_ods.ods_member_profile ( tenant_id BIGINT, site_id BIGINT NOT NULL, member_id BIGINT NOT NULL, system_member_id BIGINT, register_site_id BIGINT, site_name TEXT, member_name TEXT, nickname TEXT, mobile TEXT, gender TEXT, birthday DATE, register_time TIMESTAMPTZ, member_type_id BIGINT, member_type_name TEXT, member_card_grade_code TEXT, status TEXT, user_status TEXT, balance NUMERIC(18,2), points NUMERIC(18,2), growth_value NUMERIC(18,2), last_visit_time TIMESTAMPTZ, wechat_id TEXT, alipay_id TEXT, member_card_no TEXT, referrer_member_id BIGINT, remarks TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, member_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_member_card ( tenant_id BIGINT, site_id BIGINT NOT NULL, card_id BIGINT NOT NULL, member_id BIGINT, tenant_member_id BIGINT, card_type_id BIGINT, card_type_name TEXT, card_no TEXT, card_physics_type TEXT, card_balance NUMERIC(18,2), denomination NUMERIC(18,2), discount_rate NUMERIC(8,4), table_discount NUMERIC(18,2), goods_discount NUMERIC(18,2), assistant_discount NUMERIC(18,2), assistant_reward_discount NUMERIC(18,2), valid_start_date DATE, valid_end_date DATE, disable_start_date DATE, disable_end_date DATE, last_consume_time TIMESTAMPTZ, status TEXT, is_delete BOOLEAN, activate_time TIMESTAMPTZ, deactivate_time TIMESTAMPTZ, register_site_id BIGINT, issuer_id BIGINT, issuer_name TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, card_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_balance_change ( tenant_id BIGINT, site_id BIGINT NOT NULL, change_id BIGINT NOT NULL, member_id BIGINT, tenant_member_id BIGINT, tenant_member_card_id BIGINT, member_name TEXT, member_mobile TEXT, change_amount NUMERIC(18,2), balance_before NUMERIC(18,2), balance_after NUMERIC(18,2), change_type INT, payment_method INT, refund_amount NUMERIC(18,2), relate_id BIGINT, register_site_id BIGINT, register_site_name TEXT, pay_site_name TEXT, remark TEXT, operator_id BIGINT, operator_name TEXT, change_time TIMESTAMPTZ, is_deleted BOOLEAN DEFAULT FALSE, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, change_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_recharge_record ( tenant_id BIGINT, site_id BIGINT NOT NULL, recharge_id BIGINT NOT NULL, member_id BIGINT, recharge_amount NUMERIC(18,2), gift_amount NUMERIC(18,2), pay_method INT, pay_trade_no TEXT, order_trade_no TEXT, recharge_time TIMESTAMPTZ, status TEXT, operator_id BIGINT, operator_name TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, recharge_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_product ( tenant_id BIGINT, site_id BIGINT NOT NULL, goods_id BIGINT NOT NULL, goods_name TEXT, goods_code TEXT, category_id BIGINT, category_name TEXT, goods_second_category_id BIGINT, unit TEXT, price NUMERIC(18,2), cost_price NUMERIC(18,2), market_price NUMERIC(18,2), goods_state TEXT, goods_cover TEXT, goods_bar_code TEXT, able_discount BOOLEAN, is_delete BOOLEAN, status TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, goods_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_store_product ( tenant_id BIGINT, site_id BIGINT NOT NULL, site_goods_id BIGINT NOT NULL, goods_id BIGINT, goods_name TEXT, category_id BIGINT, category_name TEXT, unit TEXT, sale_price NUMERIC(18,2), cost_price NUMERIC(18,2), sale_num NUMERIC(18,2), stock_a NUMERIC(18,2), stock NUMERIC(18,2), provisional_total_cost NUMERIC(18,2), total_purchase_cost NUMERIC(18,2), batch_stock_quantity NUMERIC(18,2), goods_state TEXT, status TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, site_goods_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_store_sale_item ( tenant_id BIGINT, site_id BIGINT NOT NULL, sale_item_id BIGINT NOT NULL, order_goods_id BIGINT, order_trade_no TEXT, order_settle_id BIGINT, site_goods_id BIGINT, goods_id BIGINT, goods_name TEXT, category_id BIGINT, quantity NUMERIC(18,4), unit_price NUMERIC(18,2), original_amount NUMERIC(18,2), discount_amount NUMERIC(18,2), final_amount NUMERIC(18,2), is_gift BOOLEAN DEFAULT FALSE, sale_time TIMESTAMPTZ, member_id BIGINT, salesman_id BIGINT, operator_id BIGINT, is_refunded BOOLEAN DEFAULT FALSE, discount_price NUMERIC(18,2), cost_money NUMERIC(18,2), coupon_deduct_amount NUMERIC(18,2), member_discount_amount NUMERIC(18,2), point_discount_money NUMERIC(18,2), point_discount_cost NUMERIC(18,2), sales_type TEXT, goods_remark TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, sale_item_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_table_info ( tenant_id BIGINT, site_id BIGINT NOT NULL, table_id BIGINT NOT NULL, table_code TEXT, table_name TEXT, table_type TEXT, area_name TEXT, site_table_area_id BIGINT, tenant_table_area_id BIGINT, table_price NUMERIC(18,2), table_status TEXT, audit_status INT, show_status INT, light_status INT, virtual_table BOOLEAN, is_rest_area BOOLEAN, charge_free BOOLEAN, table_cloth_use_time INT, table_cloth_use_cycle INT, status TEXT, created_time TIMESTAMPTZ, updated_time TIMESTAMPTZ, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, table_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_table_use_log ( tenant_id BIGINT, site_id BIGINT NOT NULL, ledger_id BIGINT NOT NULL, table_id BIGINT, table_name TEXT, order_trade_no TEXT, order_settle_id BIGINT, start_time TIMESTAMPTZ, end_time TIMESTAMPTZ, duration_seconds INT, billing_unit_price NUMERIC(18,4), billing_count NUMERIC(18,4), original_table_fee NUMERIC(18,2), member_discount_amount NUMERIC(18,2), coupon_discount_amount NUMERIC(18,2), manual_discount_amount NUMERIC(18,2), service_fee NUMERIC(18,2), final_table_fee NUMERIC(18,2), member_id BIGINT, operator_id BIGINT, salesman_id BIGINT, is_canceled BOOLEAN DEFAULT FALSE, cancel_time TIMESTAMPTZ, site_table_area_id BIGINT, tenant_table_area_id BIGINT, site_table_area_name TEXT, is_single_order BOOLEAN, used_card_amount NUMERIC(18,2), adjust_amount NUMERIC(18,2), coupon_promotion_amount NUMERIC(18,2), service_money NUMERIC(18,2), mgmt_fee NUMERIC(18,2), fee_total NUMERIC(18,2), real_table_use_seconds INT, last_use_time TIMESTAMPTZ, ledger_start_time TIMESTAMPTZ, ledger_end_time TIMESTAMPTZ, ledger_status INT, start_use_time TIMESTAMPTZ, add_clock_seconds INT, status TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, ledger_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_table_fee_adjust ( tenant_id BIGINT, site_id BIGINT NOT NULL, adjust_id BIGINT NOT NULL, ledger_id BIGINT, order_trade_no TEXT, discount_amount NUMERIC(18,2), reason TEXT, operator_id BIGINT, operator_name TEXT, created_at TIMESTAMPTZ, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, adjust_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_assistant_account ( tenant_id BIGINT, site_id BIGINT NOT NULL, assistant_id BIGINT NOT NULL, assistant_name TEXT, mobile TEXT, assistant_no INT, team_id BIGINT, team_name TEXT, group_id BIGINT, group_name TEXT, job_num TEXT, entry_type TEXT, leave_status TEXT, assistant_status TEXT, allow_cx BOOLEAN, status TEXT, hired_date DATE, left_date DATE, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, assistant_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_assistant_service_log ( tenant_id BIGINT, site_id BIGINT NOT NULL, ledger_id BIGINT NOT NULL, assistant_id BIGINT, assistant_name TEXT, service_type TEXT, order_trade_no TEXT, order_settle_id BIGINT, start_time TIMESTAMPTZ, end_time TIMESTAMPTZ, duration_seconds INT, original_fee NUMERIC(18,2), member_discount_amount NUMERIC(18,2), manual_discount_amount NUMERIC(18,2), coupon_discount_amount NUMERIC(18,2), final_fee NUMERIC(18,2), member_id BIGINT, operator_id BIGINT, salesman_id BIGINT, is_canceled BOOLEAN DEFAULT FALSE, cancel_time TIMESTAMPTZ, skill_grade NUMERIC(10,2), service_grade NUMERIC(10,2), composite_grade NUMERIC(10,2), overall_score NUMERIC(10,2), status TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, ledger_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_assistant_cancel_log ( tenant_id BIGINT, site_id BIGINT NOT NULL, cancel_id BIGINT NOT NULL, ledger_id BIGINT, assistant_id BIGINT, order_trade_no TEXT, table_id BIGINT, table_area_id BIGINT, table_area_name TEXT, table_name TEXT, assistant_on INT, pd_charge_minutes INT, assistant_abolish_amount NUMERIC(18,2), reason TEXT, cancel_time TIMESTAMPTZ, operator_id BIGINT, operator_name TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, cancel_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_group_package ( tenant_id BIGINT, site_id BIGINT NOT NULL, package_id BIGINT NOT NULL, package_name TEXT, table_area_id BIGINT, table_area_name TEXT, platform_code TEXT, status TEXT, face_price NUMERIC(18,2), settle_price NUMERIC(18,2), selling_price NUMERIC(18,2), duration INT, valid_from DATE, valid_to DATE, start_time TIMESTAMPTZ, end_time TIMESTAMPTZ, is_enabled BOOLEAN, is_delete BOOLEAN, package_type TEXT, usable_count INT, creator_name TEXT, tenant_table_area_id BIGINT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, package_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_group_package_log ( tenant_id BIGINT, site_id BIGINT NOT NULL, usage_id BIGINT NOT NULL, package_id BIGINT, coupon_id BIGINT, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, status TEXT, used_time TIMESTAMPTZ, deduct_amount NUMERIC(18,2), settle_price NUMERIC(18,2), coupon_code TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, usage_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_platform_coupon_log ( tenant_id BIGINT, site_id BIGINT NOT NULL, coupon_id BIGINT NOT NULL, platform_code TEXT, verify_code TEXT, coupon_code TEXT, coupon_channel TEXT, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, status TEXT, used_time TIMESTAMPTZ, deduct_amount NUMERIC(18,2), settle_price NUMERIC(18,2), coupon_money NUMERIC(18,2), source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, coupon_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_inventory_change ( tenant_id BIGINT, site_id BIGINT NOT NULL, change_id BIGINT NOT NULL, site_goods_id BIGINT, goods_id BIGINT, stock_type TEXT, change_amount NUMERIC(18,2), before_stock NUMERIC(18,2), after_stock NUMERIC(18,2), change_amount_alt NUMERIC(18,2), before_stock_alt NUMERIC(18,2), after_stock_alt NUMERIC(18,2), change_type TEXT, relate_id BIGINT, unit TEXT, price NUMERIC(18,2), goods_category_id BIGINT, goods_second_category_id BIGINT, remark TEXT, operator_id BIGINT, operator_name TEXT, change_time TIMESTAMPTZ, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, change_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_inventory_stock ( tenant_id BIGINT, site_id BIGINT NOT NULL, site_goods_id BIGINT NOT NULL, goods_id BIGINT, goods_name TEXT, goods_unit TEXT, goods_category_id BIGINT, goods_second_category_id BIGINT, range_start_stock NUMERIC(18,2), range_end_stock NUMERIC(18,2), range_in NUMERIC(18,2), range_out NUMERIC(18,2), range_inventory NUMERIC(18,2), range_sale NUMERIC(18,2), range_sale_money NUMERIC(18,2), current_stock NUMERIC(18,2), cost_price NUMERIC(18,2), category_name TEXT, snapshot_key TEXT NOT NULL DEFAULT 'default', source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, site_goods_id, snapshot_key) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_order_settle ( tenant_id BIGINT, site_id BIGINT NOT NULL, order_settle_id BIGINT NOT NULL, settle_relate_id BIGINT, settle_name TEXT, settle_type INT, settle_status INT, member_id BIGINT, member_name TEXT, member_phone TEXT, table_id BIGINT, consume_money NUMERIC(18,2), table_charge_money NUMERIC(18,2), goods_money NUMERIC(18,2), service_money NUMERIC(18,2), assistant_pd_money NUMERIC(18,2), assistant_cx_money NUMERIC(18,2), pay_amount NUMERIC(18,2), cash_amount NUMERIC(18,2), online_amount NUMERIC(18,2), point_amount NUMERIC(18,2), coupon_amount NUMERIC(18,2), card_amount NUMERIC(18,2), balance_amount NUMERIC(18,2), refund_amount NUMERIC(18,2), prepay_money NUMERIC(18,2), adjust_amount NUMERIC(18,2), rounding_amount NUMERIC(18,2), member_discount_amount NUMERIC(18,2), coupon_sale_amount NUMERIC(18,2), goods_promotion_money NUMERIC(18,2), assistant_promotion_money NUMERIC(18,2), point_discount_price NUMERIC(18,2), point_discount_cost NUMERIC(18,2), real_goods_money NUMERIC(18,2), assistant_manual_discount NUMERIC(18,2), all_coupon_discount NUMERIC(18,2), is_use_coupon BOOLEAN, is_use_discount BOOLEAN, is_activity BOOLEAN, is_bind_member BOOLEAN, is_first BOOLEAN, recharge_card_amount NUMERIC(18,2), gift_card_amount NUMERIC(18,2), payment_method INT, create_time TIMESTAMPTZ, pay_time TIMESTAMPTZ, revoke_order_id BIGINT, revoke_order_name TEXT, revoke_time TIMESTAMPTZ, can_be_revoked BOOLEAN, serial_number TEXT, sales_man_name TEXT, sales_man_user_id BIGINT, order_remark TEXT, operator_id BIGINT, operator_name TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, order_settle_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_goods_category ( tenant_id BIGINT, site_id BIGINT NOT NULL, category_id BIGINT NOT NULL, category_name TEXT, parent_id BIGINT, level_no INT, status TEXT, remark TEXT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, category_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_payment_record ( tenant_id BIGINT, site_id BIGINT NOT NULL, pay_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, pay_method_code TEXT, pay_method_name TEXT, pay_status INT, pay_amount NUMERIC(18,2), pay_time TIMESTAMPTZ, online_pay_channel TEXT, transaction_id TEXT, operator_id BIGINT, remark TEXT, relate_type TEXT, relate_id BIGINT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, pay_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_refund_record ( tenant_id BIGINT, site_id BIGINT NOT NULL, refund_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, pay_sn TEXT, pay_amount NUMERIC(18,2), pay_status INT, is_revoke BOOLEAN, is_delete BOOLEAN, online_pay_channel TEXT, pay_method_code TEXT, refund_amount NUMERIC(18,2), refund_time TIMESTAMPTZ, action_type INT, pay_terminal INT, pay_config_id BIGINT, cashier_point_id BIGINT, operator_id BIGINT, member_card_id BIGINT, balance_frozen_amount NUMERIC(18,2), card_frozen_amount NUMERIC(18,2), round_amount NUMERIC(18,2), online_pay_type INT, channel_payer_id TEXT, channel_pay_no TEXT, check_status INT, channel_fee NUMERIC(18,2), relate_type TEXT, relate_id BIGINT, status TEXT, reason TEXT, related_payment_id BIGINT, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, refund_id) ); CREATE TABLE IF NOT EXISTS billiards_ods.ods_order_receipt_detail ( tenant_id BIGINT, site_id BIGINT NOT NULL, order_settle_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_number TEXT, settle_type INT, receipt_no TEXT, receipt_time TIMESTAMPTZ, total_amount NUMERIC(18,2), discount_amount NUMERIC(18,2), final_amount NUMERIC(18,2), actual_payment NUMERIC(18,2), ledger_amount NUMERIC(18,2), member_offer_amount NUMERIC(18,2), delivery_fee NUMERIC(18,2), adjust_amount NUMERIC(18,2), payment_method INT, pay_time TIMESTAMPTZ, member_id BIGINT, order_remark TEXT, cashier_name TEXT, ticket_remark TEXT, ticket_custom_content TEXT, voucher_money NUMERIC(18,2), reward_name TEXT, consume_money NUMERIC(18,2), refund_amount NUMERIC(18,2), balance_amount NUMERIC(18,2), coupon_amount NUMERIC(18,2), member_deduct_amount NUMERIC(18,2), prepay_money NUMERIC(18,2), delivery_address TEXT, snapshot_raw JSONB, member_snapshot JSONB, source_file TEXT, source_endpoint TEXT, fetched_at TIMESTAMPTZ DEFAULT now(), payload JSONB NOT NULL, PRIMARY KEY (site_id, order_settle_id) ); -- ---------- DWD Dimensions ---------- CREATE TABLE IF NOT EXISTS billiards_dwd.dim_tenant ( tenant_id BIGINT PRIMARY KEY, tenant_name TEXT, short_name TEXT, status TEXT, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_site ( site_id BIGINT PRIMARY KEY, tenant_id BIGINT, site_code TEXT, site_name TEXT, city TEXT, region TEXT, status TEXT, open_date DATE, close_date DATE, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_member_card_type ( card_type_id BIGINT PRIMARY KEY, card_type_name TEXT, discount_rate NUMERIC(8,4), description TEXT, remark TEXT ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_product_category ( category_id BIGINT PRIMARY KEY, category_name TEXT, parent_id BIGINT, level_no INT, status TEXT, remark TEXT ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_product ( goods_id BIGINT PRIMARY KEY, goods_name TEXT, goods_code TEXT, category_id BIGINT REFERENCES billiards_dwd.dim_product_category (category_id), category_name TEXT, unit TEXT, default_price NUMERIC(18,2), status TEXT, updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_table ( table_id BIGINT PRIMARY KEY, site_id BIGINT, table_code TEXT, table_name TEXT, table_type TEXT, area_name TEXT, status TEXT, created_time TIMESTAMPTZ, updated_time TIMESTAMPTZ ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_assistant_team ( team_id BIGINT PRIMARY KEY, team_name TEXT, remark TEXT, updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_assistant ( assistant_id BIGINT PRIMARY KEY, assistant_name TEXT, mobile TEXT, team_id BIGINT REFERENCES billiards_dwd.dim_assistant_team (team_id), status TEXT, hired_date DATE, left_date DATE, updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_pay_method ( pay_method_code TEXT PRIMARY KEY, pay_method_name TEXT, is_stored_value BOOLEAN DEFAULT FALSE, status TEXT, updated_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_order_assist_type ( assist_type_code TEXT PRIMARY KEY, assist_type_name TEXT, description TEXT ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_coupon_platform ( platform_code TEXT PRIMARY KEY, platform_name TEXT, description TEXT ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_date ( date_key DATE PRIMARY KEY, year_no INT, month_no INT, day_no INT, week_no INT, day_of_week INT, month_name TEXT ); CREATE TABLE IF NOT EXISTS billiards_dwd.dim_member ( site_id BIGINT, member_id BIGINT, tenant_id BIGINT, member_name TEXT, nickname TEXT, gender TEXT, birthday DATE, mobile TEXT, member_type_id BIGINT REFERENCES billiards_dwd.dim_member_card_type (card_type_id), member_type_name TEXT, status TEXT, register_time TIMESTAMPTZ, valid_from DATE, valid_to DATE, last_visit_time TIMESTAMPTZ, balance NUMERIC(18,2), total_recharge_amount NUMERIC(18,2), total_consumed_amount NUMERIC(18,2), wechat_id TEXT, alipay_id TEXT, remark TEXT, updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, member_id) ); -- ---------- DWD Facts ---------- CREATE TABLE IF NOT EXISTS billiards_dwd.fact_sale_item ( site_id BIGINT NOT NULL, sale_item_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, goods_id BIGINT REFERENCES billiards_dwd.dim_product (goods_id), category_id BIGINT REFERENCES billiards_dwd.dim_product_category (category_id), quantity NUMERIC(18,4), original_amount NUMERIC(18,2), discount_amount NUMERIC(18,2), final_amount NUMERIC(18,2), is_gift BOOLEAN DEFAULT FALSE, sale_time TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, sale_item_id) ); CREATE TABLE IF NOT EXISTS billiards_dwd.fact_table_usage ( site_id BIGINT NOT NULL, ledger_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, table_id BIGINT REFERENCES billiards_dwd.dim_table (table_id), member_id BIGINT, start_time TIMESTAMPTZ, end_time TIMESTAMPTZ, duration_minutes INT, original_table_fee NUMERIC(18,2), member_discount_amount NUMERIC(18,2), manual_discount_amount NUMERIC(18,2), final_table_fee NUMERIC(18,2), is_canceled BOOLEAN DEFAULT FALSE, cancel_time TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, ledger_id) ); CREATE TABLE IF NOT EXISTS billiards_dwd.fact_assistant_service ( site_id BIGINT NOT NULL, ledger_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, assistant_id BIGINT REFERENCES billiards_dwd.dim_assistant (assistant_id), assist_type_code TEXT REFERENCES billiards_dwd.dim_order_assist_type (assist_type_code), member_id BIGINT, start_time TIMESTAMPTZ, end_time TIMESTAMPTZ, duration_minutes INT, original_fee NUMERIC(18,2), member_discount_amount NUMERIC(18,2), manual_discount_amount NUMERIC(18,2), final_fee NUMERIC(18,2), is_canceled BOOLEAN DEFAULT FALSE, cancel_time TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, ledger_id) ); CREATE TABLE IF NOT EXISTS billiards_dwd.fact_coupon_usage ( site_id BIGINT NOT NULL, coupon_id BIGINT NOT NULL, package_id BIGINT, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, platform_code TEXT REFERENCES billiards_dwd.dim_coupon_platform (platform_code), status TEXT, deduct_amount NUMERIC(18,2), settle_price NUMERIC(18,2), used_time TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, coupon_id) ); CREATE TABLE IF NOT EXISTS billiards_dwd.fact_payment ( site_id BIGINT NOT NULL, pay_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, pay_method_code TEXT REFERENCES billiards_dwd.dim_pay_method (pay_method_code), pay_amount NUMERIC(18,2), pay_time TIMESTAMPTZ, relate_type TEXT, relate_id BIGINT, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, pay_id) ); CREATE TABLE IF NOT EXISTS billiards_dwd.fact_refund ( site_id BIGINT NOT NULL, refund_id BIGINT NOT NULL, order_trade_no TEXT, order_settle_id BIGINT, member_id BIGINT, pay_method_code TEXT REFERENCES billiards_dwd.dim_pay_method (pay_method_code), refund_amount NUMERIC(18,2), refund_time TIMESTAMPTZ, status TEXT, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, refund_id) ); CREATE TABLE IF NOT EXISTS billiards_dwd.fact_balance_change ( site_id BIGINT NOT NULL, change_id BIGINT NOT NULL, member_id BIGINT, change_type INT, relate_type TEXT, relate_id BIGINT, pay_method_code TEXT REFERENCES billiards_dwd.dim_pay_method (pay_method_code), change_amount NUMERIC(18,2), balance_before NUMERIC(18,2), balance_after NUMERIC(18,2), change_time TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, change_id) ); -- ---------- DWS (serving layers) ---------- CREATE TABLE IF NOT EXISTS billiards_dws.dws_order_summary ( site_id BIGINT NOT NULL, order_settle_id BIGINT NOT NULL, order_trade_no TEXT, order_date DATE, tenant_id BIGINT, member_id BIGINT, member_flag BOOLEAN DEFAULT FALSE, recharge_order_flag BOOLEAN DEFAULT FALSE, item_count INT, total_item_quantity NUMERIC(18,4), table_fee_amount NUMERIC(18,2), assistant_service_amount NUMERIC(18,2), goods_amount NUMERIC(18,2), group_amount NUMERIC(18,2), total_coupon_deduction NUMERIC(18,2), member_discount_amount NUMERIC(18,2), manual_discount_amount NUMERIC(18,2), order_original_amount NUMERIC(18,2), order_final_amount NUMERIC(18,2), stored_card_deduct NUMERIC(18,2), external_paid_amount NUMERIC(18,2), total_paid_amount NUMERIC(18,2), book_table_flow NUMERIC(18,2), book_assistant_flow NUMERIC(18,2), book_goods_flow NUMERIC(18,2), book_group_flow NUMERIC(18,2), book_order_flow NUMERIC(18,2), order_effective_consume_cash NUMERIC(18,2), order_effective_recharge_cash NUMERIC(18,2), order_effective_flow NUMERIC(18,2), refund_amount NUMERIC(18,2), net_income NUMERIC(18,2), created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (site_id, order_settle_id) ); -- ---------- etl_admin (scheduler, cursor, run tracking) ---------- CREATE SCHEMA IF NOT EXISTS etl_admin; CREATE TABLE IF NOT EXISTS etl_admin.etl_task ( task_id BIGSERIAL PRIMARY KEY, task_code TEXT NOT NULL, store_id BIGINT NOT NULL, enabled BOOLEAN DEFAULT TRUE, cursor_field TEXT, window_minutes_default INT DEFAULT 30, overlap_seconds INT DEFAULT 120, page_size INT DEFAULT 200, retry_max INT DEFAULT 3, params JSONB DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), UNIQUE (task_code, store_id) ); CREATE TABLE IF NOT EXISTS etl_admin.etl_cursor ( cursor_id BIGSERIAL PRIMARY KEY, task_id BIGINT NOT NULL REFERENCES etl_admin.etl_task(task_id) ON DELETE CASCADE, store_id BIGINT NOT NULL, last_start TIMESTAMPTZ, last_end TIMESTAMPTZ, last_id BIGINT, last_run_id BIGINT, extra JSONB DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), UNIQUE (task_id, store_id) ); CREATE TABLE IF NOT EXISTS etl_admin.etl_run ( run_id BIGSERIAL PRIMARY KEY, run_uuid TEXT NOT NULL, task_id BIGINT NOT NULL REFERENCES etl_admin.etl_task(task_id) ON DELETE CASCADE, store_id BIGINT NOT NULL, status TEXT NOT NULL, started_at TIMESTAMPTZ DEFAULT now(), ended_at TIMESTAMPTZ, window_start TIMESTAMPTZ, window_end TIMESTAMPTZ, window_minutes INT, overlap_seconds INT, fetched_count INT DEFAULT 0, loaded_count INT DEFAULT 0, updated_count INT DEFAULT 0, skipped_count INT DEFAULT 0, error_count INT DEFAULT 0, unknown_fields INT DEFAULT 0, export_dir TEXT, log_path TEXT, request_params JSONB DEFAULT '{}'::jsonb, manifest JSONB DEFAULT '{}'::jsonb, error_message TEXT, extra JSONB DEFAULT '{}'::jsonb ); -- Default task registry seed (idempotent) INSERT INTO etl_admin.etl_task (task_code, store_id, enabled) VALUES ('PRODUCTS', 2790685415443269, TRUE), ('TABLES', 2790685415443269, TRUE), ('MEMBERS', 2790685415443269, TRUE), ('ASSISTANTS', 2790685415443269, TRUE), ('PACKAGES_DEF', 2790685415443269, TRUE), ('ORDERS', 2790685415443269, TRUE), ('PAYMENTS', 2790685415443269, TRUE), ('REFUNDS', 2790685415443269, TRUE), ('COUPON_USAGE', 2790685415443269, TRUE), ('INVENTORY_CHANGE', 2790685415443269, TRUE), ('TOPUPS', 2790685415443269, TRUE), ('TABLE_DISCOUNT', 2790685415443269, TRUE), ('ASSISTANT_ABOLISH', 2790685415443269, TRUE), ('LEDGER', 2790685415443269, TRUE), ('TICKET_DWD', 2790685415443269, TRUE), ('PAYMENTS_DWD', 2790685415443269, TRUE), ('MEMBERS_DWD', 2790685415443269, TRUE), ('MANUAL_INGEST', 2790685415443269, TRUE), ('ODS_ORDER_SETTLE', 2790685415443269, TRUE), ('ODS_TABLE_USE', 2790685415443269, TRUE), ('ODS_ASSISTANT_LEDGER', 2790685415443269, TRUE), ('ODS_ASSISTANT_ABOLISH', 2790685415443269, TRUE), ('ODS_GOODS_LEDGER', 2790685415443269, TRUE), ('ODS_PAYMENT', 2790685415443269, TRUE), ('ODS_REFUND', 2790685415443269, TRUE), ('ODS_COUPON_VERIFY', 2790685415443269, TRUE), ('ODS_MEMBER', 2790685415443269, TRUE), ('ODS_MEMBER_CARD', 2790685415443269, TRUE), ('ODS_PACKAGE', 2790685415443269, TRUE), ('ODS_INVENTORY_STOCK', 2790685415443269, TRUE), ('ODS_INVENTORY_CHANGE', 2790685415443269, TRUE) ON CONFLICT (task_code, store_id) DO NOTHING;