1129 lines
40 KiB
SQL
1129 lines
40 KiB
SQL
-- 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;
|