1139 lines
38 KiB
SQL
1139 lines
38 KiB
SQL
-- =============================================================================
|
||
-- etl_feiqiu / ods（原始数据层）
|
||
-- 生成日期：2026-03-15
|
||
-- 来源：测试库（通过脚本自动导出）
|
||
-- =============================================================================
|
||
|
||
CREATE SCHEMA IF NOT EXISTS ods;
|
||
|
||
-- 表
|
||
CREATE TABLE ods.assistant_accounts_master (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
assistant_no text,
|
||
nickname text,
|
||
real_name text,
|
||
mobile text,
|
||
team_id bigint,
|
||
team_name text,
|
||
user_id bigint,
|
||
level text,
|
||
assistant_status integer,
|
||
work_status integer,
|
||
leave_status integer,
|
||
entry_time timestamp without time zone,
|
||
resign_time timestamp without time zone,
|
||
start_time timestamp without time zone,
|
||
end_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
update_time timestamp without time zone,
|
||
order_trade_no text,
|
||
staff_id bigint,
|
||
staff_profile_id bigint,
|
||
system_role_id bigint,
|
||
avatar text,
|
||
birth_date timestamp without time zone,
|
||
gender integer,
|
||
height numeric(18,2),
|
||
weight numeric(18,2),
|
||
job_num text,
|
||
show_status integer,
|
||
show_sort integer,
|
||
sum_grade numeric(18,2),
|
||
assistant_grade numeric(18,2),
|
||
get_grade_times integer,
|
||
introduce text,
|
||
video_introduction_url text,
|
||
group_id bigint,
|
||
group_name text,
|
||
shop_name text,
|
||
charge_way integer,
|
||
entry_type integer,
|
||
allow_cx integer,
|
||
is_guaranteed integer,
|
||
salary_grant_enabled integer,
|
||
light_status integer,
|
||
online_status integer,
|
||
is_delete integer,
|
||
cx_unit_price numeric(18,2),
|
||
pd_unit_price numeric(18,2),
|
||
last_table_id bigint,
|
||
last_table_name text,
|
||
person_org_id bigint,
|
||
serial_number bigint,
|
||
is_team_leader integer,
|
||
criticism_status integer,
|
||
last_update_name text,
|
||
ding_talk_synced integer,
|
||
site_light_cfg_id bigint,
|
||
light_equipment_id text,
|
||
entry_sign_status integer,
|
||
resign_sign_status integer,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.assistant_service_records (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
siteprofile jsonb,
|
||
site_table_id bigint,
|
||
order_settle_id bigint,
|
||
order_trade_no text,
|
||
order_pay_id bigint,
|
||
order_assistant_id bigint,
|
||
order_assistant_type integer,
|
||
assistantname text,
|
||
assistantno text,
|
||
assistant_level text,
|
||
levelname text,
|
||
site_assistant_id bigint,
|
||
skill_id bigint,
|
||
skillname text,
|
||
system_member_id bigint,
|
||
tablename text,
|
||
tenant_member_id bigint,
|
||
user_id bigint,
|
||
assistant_team_id bigint,
|
||
nickname text,
|
||
ledger_name text,
|
||
ledger_group_name text,
|
||
ledger_amount numeric(18,2),
|
||
ledger_count numeric(18,4),
|
||
ledger_unit_price numeric(18,4),
|
||
ledger_status integer,
|
||
ledger_start_time timestamp without time zone,
|
||
ledger_end_time timestamp without time zone,
|
||
manual_discount_amount numeric(18,2),
|
||
member_discount_amount numeric(18,2),
|
||
coupon_deduct_money numeric(18,2),
|
||
service_money numeric(18,2),
|
||
projected_income numeric(18,2),
|
||
real_use_seconds integer,
|
||
income_seconds integer,
|
||
start_use_time timestamp without time zone,
|
||
last_use_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
is_single_order integer,
|
||
is_delete integer,
|
||
is_trash integer,
|
||
trash_reason text,
|
||
trash_applicant_id bigint,
|
||
trash_applicant_name text,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
salesman_name text,
|
||
salesman_org_id bigint,
|
||
salesman_user_id bigint,
|
||
person_org_id bigint,
|
||
add_clock integer,
|
||
returns_clock integer,
|
||
composite_grade numeric(10,2),
|
||
composite_grade_time timestamp without time zone,
|
||
skill_grade numeric(10,2),
|
||
service_grade numeric(10,2),
|
||
sum_grade numeric(10,2),
|
||
grade_status integer,
|
||
get_grade_times integer,
|
||
is_not_responding integer,
|
||
is_confirm integer,
|
||
assistantteamname text,
|
||
real_service_money numeric(18,2),
|
||
payload jsonb NOT NULL,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE ods.goods_stock_movements (
|
||
sitegoodsstockid bigint NOT NULL,
|
||
tenantid bigint,
|
||
siteid bigint,
|
||
sitegoodsid bigint,
|
||
goodsname text,
|
||
goodscategoryid bigint,
|
||
goodssecondcategoryid bigint,
|
||
unit text,
|
||
price numeric(18,4),
|
||
stocktype integer,
|
||
changenum numeric(18,4),
|
||
startnum numeric(18,4),
|
||
endnum numeric(18,4),
|
||
changenuma numeric(18,4),
|
||
startnuma numeric(18,4),
|
||
endnuma numeric(18,4),
|
||
remark text,
|
||
operatorname text,
|
||
createtime timestamp without time zone,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.goods_stock_summary (
|
||
sitegoodsid bigint NOT NULL,
|
||
goodsname text,
|
||
goodsunit text,
|
||
goodscategoryid bigint,
|
||
goodscategorysecondid bigint,
|
||
categoryname text,
|
||
rangestartstock numeric(18,4),
|
||
rangeendstock numeric(18,4),
|
||
rangein numeric(18,4),
|
||
rangeout numeric(18,4),
|
||
rangesale numeric(18,4),
|
||
rangesalemoney numeric(18,2),
|
||
rangeinventory numeric(18,4),
|
||
currentstock numeric(18,4),
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL,
|
||
siteid bigint
|
||
);
|
||
|
||
CREATE TABLE ods.group_buy_package_details (
|
||
coupon_id bigint NOT NULL,
|
||
package_name text,
|
||
duration integer,
|
||
start_time timestamp with time zone,
|
||
end_time timestamp with time zone,
|
||
add_start_clock text,
|
||
add_end_clock text,
|
||
is_enabled integer,
|
||
is_delete integer,
|
||
site_id bigint,
|
||
tenant_id bigint,
|
||
create_time timestamp with time zone,
|
||
creator_name text,
|
||
table_area_ids jsonb,
|
||
table_area_names jsonb,
|
||
assistant_services jsonb,
|
||
groupon_site_infos jsonb,
|
||
package_services jsonb,
|
||
coupon_details_list jsonb,
|
||
content_hash text,
|
||
payload jsonb,
|
||
fetched_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE ods.group_buy_packages (
|
||
id bigint NOT NULL,
|
||
package_id bigint,
|
||
package_name text,
|
||
selling_price numeric(18,2),
|
||
coupon_money numeric(18,2),
|
||
date_type integer,
|
||
date_info text,
|
||
start_time timestamp without time zone,
|
||
end_time timestamp without time zone,
|
||
start_clock text,
|
||
end_clock text,
|
||
add_start_clock text,
|
||
add_end_clock text,
|
||
duration integer,
|
||
usable_count integer,
|
||
usable_range integer,
|
||
table_area_id bigint,
|
||
table_area_name text,
|
||
table_area_id_list jsonb,
|
||
tenant_table_area_id bigint,
|
||
tenant_table_area_id_list jsonb,
|
||
site_id bigint,
|
||
site_name text,
|
||
tenant_id bigint,
|
||
card_type_ids jsonb,
|
||
group_type integer,
|
||
system_group_type integer,
|
||
type integer,
|
||
effective_status integer,
|
||
is_enabled integer,
|
||
is_delete integer,
|
||
max_selectable_categories integer,
|
||
area_tag_type integer,
|
||
creator_name text,
|
||
create_time timestamp without time zone,
|
||
is_first_limit boolean,
|
||
sort integer,
|
||
tenantcouponsaleorderitemid bigint,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.group_buy_redemption_records (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
sitename text,
|
||
table_id bigint,
|
||
tablename text,
|
||
tableareaname text,
|
||
tenant_table_area_id bigint,
|
||
order_trade_no text,
|
||
order_settle_id bigint,
|
||
order_pay_id bigint,
|
||
order_coupon_id bigint,
|
||
order_coupon_channel integer,
|
||
coupon_code text,
|
||
coupon_money numeric(18,2),
|
||
coupon_origin_id bigint,
|
||
ledger_name text,
|
||
ledger_group_name text,
|
||
ledger_amount numeric(18,2),
|
||
ledger_count numeric(18,4),
|
||
ledger_unit_price numeric(18,4),
|
||
ledger_status integer,
|
||
table_charge_seconds integer,
|
||
promotion_activity_id bigint,
|
||
promotion_coupon_id bigint,
|
||
promotion_seconds integer,
|
||
offer_type integer,
|
||
assistant_promotion_money numeric(18,2),
|
||
assistant_service_promotion_money numeric(18,2),
|
||
table_service_promotion_money numeric(18,2),
|
||
goods_promotion_money numeric(18,2),
|
||
recharge_promotion_money numeric(18,2),
|
||
reward_promotion_money numeric(18,2),
|
||
goodsoptionprice numeric(18,2),
|
||
salesman_name text,
|
||
sales_man_org_id bigint,
|
||
salesman_role_id bigint,
|
||
salesman_user_id bigint,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
is_single_order integer,
|
||
is_delete integer,
|
||
create_time timestamp without time zone,
|
||
assistant_service_share_money numeric(18,2),
|
||
assistant_share_money numeric(18,2),
|
||
coupon_sale_id bigint,
|
||
good_service_share_money numeric(18,2),
|
||
goods_share_money numeric(18,2),
|
||
member_discount_money numeric(18,2),
|
||
recharge_share_money numeric(18,2),
|
||
table_service_share_money numeric(18,2),
|
||
table_share_money numeric(18,2),
|
||
payload jsonb NOT NULL,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE ods.member_balance_changes (
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
register_site_id bigint,
|
||
registersitename text,
|
||
paysitename text,
|
||
id bigint NOT NULL,
|
||
tenant_member_id bigint,
|
||
tenant_member_card_id bigint,
|
||
system_member_id bigint,
|
||
membername text,
|
||
membermobile text,
|
||
card_type_id bigint,
|
||
membercardtypename text,
|
||
account_data numeric(18,2),
|
||
before numeric(18,2),
|
||
after numeric(18,2),
|
||
refund_amount numeric(18,2),
|
||
from_type integer,
|
||
payment_method integer,
|
||
relate_id bigint,
|
||
remark text,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
is_delete integer,
|
||
create_time timestamp without time zone,
|
||
principal_after numeric(18,2),
|
||
principal_before numeric(18,2),
|
||
principal_data text,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.member_profiles (
|
||
tenant_id bigint,
|
||
register_site_id bigint,
|
||
site_name text,
|
||
id bigint NOT NULL,
|
||
system_member_id bigint,
|
||
member_card_grade_code bigint,
|
||
member_card_grade_name text,
|
||
mobile text,
|
||
nickname text,
|
||
point numeric(18,2),
|
||
growth_value numeric(18,2),
|
||
referrer_member_id bigint,
|
||
status integer,
|
||
user_status integer,
|
||
create_time timestamp without time zone,
|
||
pay_money_sum numeric(18,2),
|
||
person_tenant_org_id bigint,
|
||
person_tenant_org_name text,
|
||
recharge_money_sum numeric(18,2),
|
||
register_source text,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL,
|
||
birthday date
|
||
);
|
||
|
||
CREATE TABLE ods.member_stored_value_cards (
|
||
tenant_id bigint,
|
||
tenant_member_id bigint,
|
||
system_member_id bigint,
|
||
register_site_id bigint,
|
||
site_name text,
|
||
id bigint NOT NULL,
|
||
member_card_grade_code bigint,
|
||
member_card_grade_code_name text,
|
||
member_card_type_name text,
|
||
member_name text,
|
||
member_mobile text,
|
||
card_type_id bigint,
|
||
card_no text,
|
||
card_physics_type text,
|
||
balance numeric(18,2),
|
||
denomination numeric(18,2),
|
||
table_discount numeric(10,4),
|
||
goods_discount numeric(10,4),
|
||
assistant_discount numeric(10,4),
|
||
assistant_reward_discount numeric(10,4),
|
||
table_service_discount numeric(10,4),
|
||
assistant_service_discount numeric(10,4),
|
||
coupon_discount numeric(10,4),
|
||
goods_service_discount numeric(10,4),
|
||
assistant_discount_sub_switch integer,
|
||
table_discount_sub_switch integer,
|
||
goods_discount_sub_switch integer,
|
||
assistant_reward_discount_sub_switch integer,
|
||
table_service_deduct_radio numeric(10,4),
|
||
assistant_service_deduct_radio numeric(10,4),
|
||
goods_service_deduct_radio numeric(10,4),
|
||
assistant_deduct_radio numeric(10,4),
|
||
table_deduct_radio numeric(10,4),
|
||
goods_deduct_radio numeric(10,4),
|
||
coupon_deduct_radio numeric(10,4),
|
||
assistant_reward_deduct_radio numeric(10,4),
|
||
tablecarddeduct numeric(18,2),
|
||
tableservicecarddeduct numeric(18,2),
|
||
goodscardeduct numeric(18,2),
|
||
goodsservicecarddeduct numeric(18,2),
|
||
assistantcarddeduct numeric(18,2),
|
||
assistantservicecarddeduct numeric(18,2),
|
||
assistantrewardcarddeduct numeric(18,2),
|
||
cardsettlededuct numeric(18,2),
|
||
couponcarddeduct numeric(18,2),
|
||
deliveryfeededuct numeric(18,2),
|
||
use_scene integer,
|
||
able_cross_site integer,
|
||
is_allow_give integer,
|
||
is_allow_order_deduct integer,
|
||
is_delete integer,
|
||
bind_password text,
|
||
goods_discount_range_type integer,
|
||
goodscategoryid bigint,
|
||
tableareaid bigint,
|
||
effect_site_id bigint,
|
||
start_time timestamp without time zone,
|
||
end_time timestamp without time zone,
|
||
disable_start_time timestamp without time zone,
|
||
disable_end_time timestamp without time zone,
|
||
last_consume_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
status integer,
|
||
sort integer,
|
||
tenantavatar text,
|
||
tenantname text,
|
||
pdassisnatlevel text,
|
||
cxassisnatlevel text,
|
||
able_share_member_discount boolean,
|
||
electricity_deduct_radio numeric(18,4),
|
||
electricity_discount numeric(18,4),
|
||
electricitycarddeduct boolean,
|
||
member_grade bigint,
|
||
principal_balance numeric(18,2),
|
||
rechargefreezebalance numeric(18,2),
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.payment_transactions (
|
||
id bigint NOT NULL,
|
||
site_id bigint,
|
||
siteprofile jsonb,
|
||
relate_type integer,
|
||
relate_id bigint,
|
||
pay_amount numeric(18,2),
|
||
pay_status integer,
|
||
pay_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
payment_method integer,
|
||
online_pay_channel integer,
|
||
tenant_id bigint,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.platform_coupon_redemption_records (
|
||
id bigint NOT NULL,
|
||
verify_id bigint,
|
||
certificate_id text,
|
||
coupon_code text,
|
||
coupon_name text,
|
||
coupon_channel integer,
|
||
groupon_type integer,
|
||
group_package_id bigint,
|
||
sale_price numeric(18,2),
|
||
coupon_money numeric(18,2),
|
||
coupon_free_time numeric(18,2),
|
||
coupon_cover text,
|
||
coupon_remark text,
|
||
use_status integer,
|
||
consume_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
deal_id text,
|
||
channel_deal_id text,
|
||
site_id bigint,
|
||
site_order_id bigint,
|
||
table_id bigint,
|
||
tenant_id bigint,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
is_delete integer,
|
||
siteprofile jsonb,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.recharge_settlements (
|
||
id bigint NOT NULL,
|
||
tenantid bigint,
|
||
siteid bigint,
|
||
sitename text,
|
||
balanceamount numeric(18,2),
|
||
cardamount numeric(18,2),
|
||
cashamount numeric(18,2),
|
||
couponamount numeric(18,2),
|
||
createtime timestamp with time zone,
|
||
memberid bigint,
|
||
membername text,
|
||
tenantmembercardid bigint,
|
||
membercardtypename text,
|
||
memberphone text,
|
||
tableid bigint,
|
||
consumemoney numeric(18,2),
|
||
onlineamount numeric(18,2),
|
||
operatorid bigint,
|
||
operatorname text,
|
||
revokeorderid bigint,
|
||
revokeordername text,
|
||
revoketime timestamp with time zone,
|
||
payamount numeric(18,2),
|
||
pointamount numeric(18,2),
|
||
refundamount numeric(18,2),
|
||
settlename text,
|
||
settlerelateid bigint,
|
||
settlestatus integer,
|
||
settletype integer,
|
||
paytime timestamp with time zone,
|
||
roundingamount numeric(18,2),
|
||
paymentmethod integer,
|
||
adjustamount numeric(18,2),
|
||
assistantcxmoney numeric(18,2),
|
||
assistantpdmoney numeric(18,2),
|
||
couponsaleamount numeric(18,2),
|
||
memberdiscountamount numeric(18,2),
|
||
tablechargemoney numeric(18,2),
|
||
goodsmoney numeric(18,2),
|
||
realgoodsmoney numeric(18,2),
|
||
servicemoney numeric(18,2),
|
||
prepaymoney numeric(18,2),
|
||
salesmanname text,
|
||
orderremark text,
|
||
salesmanuserid bigint,
|
||
canberevoked boolean,
|
||
pointdiscountprice numeric(18,2),
|
||
pointdiscountcost numeric(18,2),
|
||
activitydiscount numeric(18,2),
|
||
serialnumber bigint,
|
||
assistantmanualdiscount numeric(18,2),
|
||
allcoupondiscount numeric(18,2),
|
||
goodspromotionmoney numeric(18,2),
|
||
assistantpromotionmoney numeric(18,2),
|
||
isusecoupon boolean,
|
||
isusediscount boolean,
|
||
isactivity boolean,
|
||
isbindmember boolean,
|
||
isfirst integer,
|
||
rechargecardamount numeric(18,2),
|
||
giftcardamount numeric(18,2),
|
||
electricityadjustmoney numeric(18,2),
|
||
electricitymoney numeric(18,2),
|
||
mervousalesamount numeric(18,2),
|
||
plcouponsaleamount numeric(18,2),
|
||
realelectricitymoney numeric(18,2),
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.refund_transactions (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
tenantname text,
|
||
site_id bigint,
|
||
siteprofile jsonb,
|
||
relate_type integer,
|
||
relate_id bigint,
|
||
pay_sn text,
|
||
pay_amount numeric(18,2),
|
||
refund_amount numeric(18,2),
|
||
round_amount numeric(18,2),
|
||
pay_status integer,
|
||
pay_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
payment_method integer,
|
||
pay_terminal integer,
|
||
pay_config_id bigint,
|
||
online_pay_channel integer,
|
||
online_pay_type integer,
|
||
channel_fee numeric(18,2),
|
||
channel_payer_id text,
|
||
channel_pay_no text,
|
||
member_id bigint,
|
||
member_card_id bigint,
|
||
cashier_point_id bigint,
|
||
operator_id bigint,
|
||
action_type integer,
|
||
check_status integer,
|
||
is_revoke integer,
|
||
is_delete integer,
|
||
balance_frozen_amount numeric(18,2),
|
||
card_frozen_amount numeric(18,2),
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.settlement_records (
|
||
id bigint NOT NULL,
|
||
tenantid bigint,
|
||
siteid bigint,
|
||
sitename text,
|
||
balanceamount numeric(18,2),
|
||
cardamount numeric(18,2),
|
||
cashamount numeric(18,2),
|
||
couponamount numeric(18,2),
|
||
createtime timestamp with time zone,
|
||
memberid bigint,
|
||
membername text,
|
||
tenantmembercardid bigint,
|
||
membercardtypename text,
|
||
memberphone text,
|
||
tableid bigint,
|
||
consumemoney numeric(18,2),
|
||
onlineamount numeric(18,2),
|
||
operatorid bigint,
|
||
operatorname text,
|
||
revokeorderid bigint,
|
||
revokeordername text,
|
||
revoketime timestamp with time zone,
|
||
payamount numeric(18,2),
|
||
pointamount numeric(18,2),
|
||
refundamount numeric(18,2),
|
||
settlename text,
|
||
settlerelateid bigint,
|
||
settlestatus integer,
|
||
settletype integer,
|
||
paytime timestamp with time zone,
|
||
roundingamount numeric(18,2),
|
||
paymentmethod integer,
|
||
adjustamount numeric(18,2),
|
||
assistantcxmoney numeric(18,2),
|
||
assistantpdmoney numeric(18,2),
|
||
couponsaleamount numeric(18,2),
|
||
memberdiscountamount numeric(18,2),
|
||
tablechargemoney numeric(18,2),
|
||
goodsmoney numeric(18,2),
|
||
realgoodsmoney numeric(18,2),
|
||
servicemoney numeric(18,2),
|
||
prepaymoney numeric(18,2),
|
||
salesmanname text,
|
||
orderremark text,
|
||
salesmanuserid bigint,
|
||
canberevoked boolean,
|
||
pointdiscountprice numeric(18,2),
|
||
pointdiscountcost numeric(18,2),
|
||
activitydiscount numeric(18,2),
|
||
serialnumber bigint,
|
||
assistantmanualdiscount numeric(18,2),
|
||
allcoupondiscount numeric(18,2),
|
||
goodspromotionmoney numeric(18,2),
|
||
assistantpromotionmoney numeric(18,2),
|
||
isusecoupon boolean,
|
||
isusediscount boolean,
|
||
isactivity boolean,
|
||
isbindmember boolean,
|
||
isfirst integer,
|
||
rechargecardamount numeric(18,2),
|
||
giftcardamount numeric(18,2),
|
||
electricityadjustmoney numeric(18,2),
|
||
electricitymoney numeric(18,2),
|
||
mervousalesamount numeric(18,2),
|
||
plcouponsaleamount numeric(18,2),
|
||
realelectricitymoney numeric(18,2),
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.site_tables_master (
|
||
id bigint NOT NULL,
|
||
site_id bigint,
|
||
sitename text,
|
||
"appletQrCodeUrl" text,
|
||
areaname text,
|
||
audit_status integer,
|
||
charge_free integer,
|
||
create_time timestamp without time zone,
|
||
delay_lights_time integer,
|
||
is_online_reservation integer,
|
||
is_rest_area integer,
|
||
light_status integer,
|
||
only_allow_groupon integer,
|
||
order_delay_time integer,
|
||
self_table integer,
|
||
show_status integer,
|
||
site_table_area_id bigint,
|
||
tablestatusname text,
|
||
table_cloth_use_cycle integer,
|
||
table_cloth_use_time timestamp without time zone,
|
||
table_name text,
|
||
table_price numeric(18,2),
|
||
table_status integer,
|
||
temporary_light_second integer,
|
||
virtual_table integer,
|
||
order_id bigint,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.staff_info_master (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
tenant_org_id bigint,
|
||
system_user_id bigint,
|
||
staff_name text,
|
||
alias_name text,
|
||
mobile text,
|
||
avatar text,
|
||
gender integer,
|
||
job text,
|
||
job_num text,
|
||
staff_identity integer,
|
||
status integer,
|
||
account_status integer,
|
||
system_role_id integer,
|
||
rank_id integer,
|
||
rank_name text,
|
||
new_rank_id integer,
|
||
new_staff_identity integer,
|
||
leave_status integer,
|
||
entry_time timestamp without time zone,
|
||
resign_time timestamp without time zone,
|
||
create_time timestamp without time zone,
|
||
is_delete integer,
|
||
is_reserve integer,
|
||
shop_name text,
|
||
site_label text,
|
||
cashier_point_id bigint,
|
||
cashier_point_name text,
|
||
group_id bigint,
|
||
group_name text,
|
||
staff_profile_id bigint,
|
||
auth_code text,
|
||
auth_code_create timestamp without time zone,
|
||
ding_talk_synced integer,
|
||
salary_grant_enabled integer,
|
||
entry_type integer,
|
||
entry_sign_status integer,
|
||
resign_sign_status integer,
|
||
criticism_status integer,
|
||
user_roles jsonb,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.stock_goods_category_tree (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
category_name text,
|
||
alias_name text,
|
||
pid bigint,
|
||
business_name text,
|
||
tenant_goods_business_id bigint,
|
||
open_salesman integer,
|
||
categoryboxes jsonb,
|
||
sort integer,
|
||
is_warehousing integer,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.store_goods_master (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
sitename text,
|
||
tenant_goods_id bigint,
|
||
goods_name text,
|
||
goods_bar_code text,
|
||
goods_category_id bigint,
|
||
goods_second_category_id bigint,
|
||
onecategoryname text,
|
||
twocategoryname text,
|
||
unit text,
|
||
sale_price numeric(18,4),
|
||
cost_price numeric(18,4),
|
||
cost_price_type integer,
|
||
min_discount_price numeric(18,4),
|
||
safe_stock numeric(18,4),
|
||
stock numeric(18,4),
|
||
stock_a numeric(18,4),
|
||
sale_num numeric(18,4),
|
||
total_purchase_cost numeric(18,4),
|
||
total_sales numeric(18,4),
|
||
average_monthly_sales numeric(18,4),
|
||
batch_stock_quantity numeric(18,2),
|
||
days_available integer,
|
||
provisional_total_cost numeric(18,2),
|
||
enable_status integer,
|
||
audit_status integer,
|
||
goods_state integer,
|
||
is_delete integer,
|
||
is_warehousing integer,
|
||
able_discount integer,
|
||
able_site_transfer integer,
|
||
forbid_sell_status integer,
|
||
"freeze" integer,
|
||
send_state integer,
|
||
custom_label_type integer,
|
||
option_required integer,
|
||
sale_channel integer,
|
||
sort integer,
|
||
remark text,
|
||
pinyin_initial text,
|
||
goods_cover text,
|
||
create_time timestamp without time zone,
|
||
update_time timestamp without time zone,
|
||
commodity_code text,
|
||
not_sale integer,
|
||
payload jsonb NOT NULL,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
time_slot_sale integer,
|
||
warning_sales_day numeric(18,2),
|
||
warning_day_max integer,
|
||
warning_day_min integer
|
||
);
|
||
|
||
CREATE TABLE ods.store_goods_sales_records (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
siteid bigint,
|
||
sitename text,
|
||
site_goods_id bigint,
|
||
tenant_goods_id bigint,
|
||
order_settle_id bigint,
|
||
order_trade_no text,
|
||
order_goods_id bigint,
|
||
ordergoodsid bigint,
|
||
order_pay_id bigint,
|
||
order_coupon_id bigint,
|
||
ledger_name text,
|
||
ledger_group_name text,
|
||
ledger_amount numeric(18,2),
|
||
ledger_count numeric(18,4),
|
||
ledger_unit_price numeric(18,4),
|
||
ledger_status integer,
|
||
discount_money numeric(18,2),
|
||
discount_price numeric(18,2),
|
||
coupon_deduct_money numeric(18,2),
|
||
member_discount_amount numeric(18,2),
|
||
option_coupon_deduct_money numeric(18,2),
|
||
option_member_discount_money numeric(18,2),
|
||
point_discount_money numeric(18,2),
|
||
point_discount_money_cost numeric(18,2),
|
||
real_goods_money numeric(18,2),
|
||
cost_money numeric(18,2),
|
||
push_money numeric(18,2),
|
||
sales_type integer,
|
||
is_single_order integer,
|
||
is_delete integer,
|
||
goods_remark text,
|
||
option_price numeric(18,2),
|
||
option_value_name text,
|
||
member_coupon_id bigint,
|
||
package_coupon_id bigint,
|
||
sales_man_org_id bigint,
|
||
salesman_name text,
|
||
salesman_role_id bigint,
|
||
salesman_user_id bigint,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
opensalesman text,
|
||
returns_number integer,
|
||
site_table_id bigint,
|
||
tenant_goods_business_id bigint,
|
||
tenant_goods_category_id bigint,
|
||
create_time timestamp without time zone,
|
||
coupon_share_money numeric(18,2),
|
||
payload jsonb NOT NULL,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE ods.table_fee_discount_records (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
siteprofile jsonb,
|
||
site_table_id bigint,
|
||
tableprofile jsonb,
|
||
tenant_table_area_id bigint,
|
||
adjust_type integer,
|
||
ledger_amount numeric(18,2),
|
||
ledger_count numeric(18,4),
|
||
ledger_name text,
|
||
ledger_status integer,
|
||
applicant_id bigint,
|
||
applicant_name text,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
order_settle_id bigint,
|
||
order_trade_no text,
|
||
is_delete integer,
|
||
create_time timestamp without time zone,
|
||
area_type_id bigint,
|
||
charge_free boolean,
|
||
site_table_area_id bigint,
|
||
site_table_area_name text,
|
||
sitename text,
|
||
table_name text,
|
||
table_price numeric(18,2),
|
||
tenant_name text,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now(),
|
||
payload jsonb NOT NULL
|
||
);
|
||
|
||
CREATE TABLE ods.table_fee_transactions (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
siteprofile jsonb,
|
||
site_table_id bigint,
|
||
site_table_area_id bigint,
|
||
site_table_area_name text,
|
||
tenant_table_area_id bigint,
|
||
order_trade_no text,
|
||
order_pay_id bigint,
|
||
order_settle_id bigint,
|
||
ledger_name text,
|
||
ledger_amount numeric(18,2),
|
||
ledger_count numeric(18,4),
|
||
ledger_unit_price numeric(18,4),
|
||
ledger_status integer,
|
||
ledger_start_time timestamp without time zone,
|
||
ledger_end_time timestamp without time zone,
|
||
start_use_time timestamp without time zone,
|
||
last_use_time timestamp without time zone,
|
||
real_table_use_seconds integer,
|
||
real_table_charge_money numeric(18,2),
|
||
add_clock_seconds integer,
|
||
adjust_amount numeric(18,2),
|
||
coupon_promotion_amount numeric(18,2),
|
||
member_discount_amount numeric(18,2),
|
||
used_card_amount numeric(18,2),
|
||
mgmt_fee numeric(18,2),
|
||
service_money numeric(18,2),
|
||
fee_total numeric(18,2),
|
||
is_single_order integer,
|
||
is_delete integer,
|
||
member_id bigint,
|
||
operator_id bigint,
|
||
operator_name text,
|
||
salesman_name text,
|
||
salesman_org_id bigint,
|
||
salesman_user_id bigint,
|
||
create_time timestamp without time zone,
|
||
activity_discount_amount numeric(18,2),
|
||
order_consumption_type integer,
|
||
real_service_money numeric(18,2),
|
||
payload jsonb NOT NULL,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE ods.tenant_goods_master (
|
||
id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
goods_name text,
|
||
goods_bar_code text,
|
||
goods_category_id bigint,
|
||
goods_second_category_id bigint,
|
||
categoryname text,
|
||
unit text,
|
||
goods_number text,
|
||
out_goods_id text,
|
||
goods_state integer,
|
||
sale_channel integer,
|
||
able_discount integer,
|
||
able_site_transfer integer,
|
||
is_delete integer,
|
||
is_warehousing integer,
|
||
isinsite integer,
|
||
cost_price numeric(18,4),
|
||
cost_price_type integer,
|
||
market_price numeric(18,4),
|
||
min_discount_price numeric(18,4),
|
||
common_sale_royalty numeric(18,4),
|
||
point_sale_royalty numeric(18,4),
|
||
pinyin_initial text,
|
||
commoditycode text,
|
||
commodity_code text,
|
||
goods_cover text,
|
||
supplier_id bigint,
|
||
remark_name text,
|
||
create_time timestamp without time zone,
|
||
update_time timestamp without time zone,
|
||
not_sale integer,
|
||
payload jsonb NOT NULL,
|
||
content_hash text NOT NULL,
|
||
source_file text,
|
||
source_endpoint text,
|
||
fetched_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
-- 约束（主键 / 唯一 / 外键）
|
||
ALTER TABLE ods.assistant_accounts_master ADD CONSTRAINT assistant_accounts_master_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.assistant_service_records ADD CONSTRAINT assistant_service_records_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.goods_stock_movements ADD CONSTRAINT goods_stock_movements_pkey PRIMARY KEY (sitegoodsstockid, content_hash);
|
||
ALTER TABLE ods.goods_stock_summary ADD CONSTRAINT goods_stock_summary_pkey PRIMARY KEY (sitegoodsid, content_hash);
|
||
ALTER TABLE ods.group_buy_package_details ADD CONSTRAINT pk_group_buy_package_details PRIMARY KEY (coupon_id);
|
||
ALTER TABLE ods.group_buy_packages ADD CONSTRAINT group_buy_packages_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.group_buy_redemption_records ADD CONSTRAINT group_buy_redemption_records_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.member_balance_changes ADD CONSTRAINT member_balance_changes_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.member_profiles ADD CONSTRAINT member_profiles_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.member_stored_value_cards ADD CONSTRAINT member_stored_value_cards_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.payment_transactions ADD CONSTRAINT payment_transactions_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.platform_coupon_redemption_records ADD CONSTRAINT platform_coupon_redemption_records_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.recharge_settlements ADD CONSTRAINT recharge_settlements_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.refund_transactions ADD CONSTRAINT refund_transactions_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.settlement_records ADD CONSTRAINT settlement_records_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.site_tables_master ADD CONSTRAINT site_tables_master_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.stock_goods_category_tree ADD CONSTRAINT stock_goods_category_tree_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.store_goods_master ADD CONSTRAINT store_goods_master_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.store_goods_sales_records ADD CONSTRAINT store_goods_sales_records_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.table_fee_discount_records ADD CONSTRAINT table_fee_discount_records_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.table_fee_transactions ADD CONSTRAINT table_fee_transactions_pkey PRIMARY KEY (id, content_hash);
|
||
ALTER TABLE ods.tenant_goods_master ADD CONSTRAINT tenant_goods_master_pkey PRIMARY KEY (id, content_hash);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_assistant_accounts_master_fetched_at_fetched_at ON ods.assistant_accounts_master USING btree (fetched_at);
|
||
CREATE INDEX idx_assistant_accounts_master_fetched_pk_d986993f ON ods.assistant_accounts_master USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_assistant_service_records_fetched_at_fetched_at ON ods.assistant_service_records USING btree (fetched_at);
|
||
CREATE INDEX idx_assistant_service_records_fetched_pk_e200787c ON ods.assistant_service_records USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_goods_stock_movements_fetched_at_fetched_at ON ods.goods_stock_movements USING btree (fetched_at);
|
||
CREATE INDEX idx_goods_stock_movements_fetched_pk_359eaab5 ON ods.goods_stock_movements USING btree (fetched_at, sitegoodsstockid, content_hash);
|
||
CREATE INDEX idx_goods_stock_summary_fetched_at_fetched_at ON ods.goods_stock_summary USING btree (fetched_at);
|
||
CREATE INDEX idx_goods_stock_summary_fetched_pk_258b3627 ON ods.goods_stock_summary USING btree (fetched_at, sitegoodsid, content_hash);
|
||
CREATE INDEX idx_group_buy_packages_fetched_at_fetched_at ON ods.group_buy_packages USING btree (fetched_at);
|
||
CREATE INDEX idx_group_buy_packages_fetched_pk_fetched_at_id_content_hash ON ods.group_buy_packages USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_group_buy_redemption_records_fetched_at_fetched_at ON ods.group_buy_redemption_records USING btree (fetched_at);
|
||
CREATE INDEX idx_group_buy_redemption_records_fetched_pk_3b2c101e ON ods.group_buy_redemption_records USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_member_balance_changes_fetched_at_fetched_at ON ods.member_balance_changes USING btree (fetched_at);
|
||
CREATE INDEX idx_member_balance_changes_fetched_pk_75e1139e ON ods.member_balance_changes USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_member_profiles_fetched_at_fetched_at ON ods.member_profiles USING btree (fetched_at);
|
||
CREATE INDEX idx_member_profiles_fetched_pk_fetched_at_id_content_hash ON ods.member_profiles USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_member_stored_value_cards_fetched_at_fetched_at ON ods.member_stored_value_cards USING btree (fetched_at);
|
||
CREATE INDEX idx_member_stored_value_cards_fetched_pk_71896d86 ON ods.member_stored_value_cards USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_payment_transactions_fetched_at_fetched_at ON ods.payment_transactions USING btree (fetched_at);
|
||
CREATE INDEX idx_payment_transactions_fetched_pk_fetched_at_id_content_hash ON ods.payment_transactions USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_platform_coupon_redemption_records_fetched_at_fetched_at ON ods.platform_coupon_redemption_records USING btree (fetched_at);
|
||
CREATE INDEX idx_platform_coupon_redemption_records_fetched_pk_d31bf839 ON ods.platform_coupon_redemption_records USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_recharge_settlements_fetched_at_fetched_at ON ods.recharge_settlements USING btree (fetched_at);
|
||
CREATE INDEX idx_recharge_settlements_fetched_pk_fetched_at_id_content_hash ON ods.recharge_settlements USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_refund_transactions_fetched_at_fetched_at ON ods.refund_transactions USING btree (fetched_at);
|
||
CREATE INDEX idx_refund_transactions_fetched_pk_fetched_at_id_content_hash ON ods.refund_transactions USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_settlement_records_fetched_at_fetched_at ON ods.settlement_records USING btree (fetched_at);
|
||
CREATE INDEX idx_settlement_records_fetched_pk_fetched_at_id_content_hash ON ods.settlement_records USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_site_tables_master_fetched_at_fetched_at ON ods.site_tables_master USING btree (fetched_at);
|
||
CREATE INDEX idx_site_tables_master_fetched_pk_fetched_at_id_content_hash ON ods.site_tables_master USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_stock_goods_category_tree_fetched_at_fetched_at ON ods.stock_goods_category_tree USING btree (fetched_at);
|
||
CREATE INDEX idx_stock_goods_category_tree_fetched_pk_77975d0e ON ods.stock_goods_category_tree USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_store_goods_master_fetched_at_fetched_at ON ods.store_goods_master USING btree (fetched_at);
|
||
CREATE INDEX idx_store_goods_master_fetched_pk_fetched_at_id_content_hash ON ods.store_goods_master USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_store_goods_sales_records_fetched_at_fetched_at ON ods.store_goods_sales_records USING btree (fetched_at);
|
||
CREATE INDEX idx_store_goods_sales_records_fetched_pk_a245052b ON ods.store_goods_sales_records USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_table_fee_discount_records_fetched_at_fetched_at ON ods.table_fee_discount_records USING btree (fetched_at);
|
||
CREATE INDEX idx_table_fee_discount_records_fetched_pk_6fe7a1d2 ON ods.table_fee_discount_records USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_table_fee_transactions_fetched_at_fetched_at ON ods.table_fee_transactions USING btree (fetched_at);
|
||
CREATE INDEX idx_table_fee_transactions_fetched_pk_91baff23 ON ods.table_fee_transactions USING btree (fetched_at, id, content_hash);
|
||
CREATE INDEX idx_tenant_goods_master_fetched_at_fetched_at ON ods.tenant_goods_master USING btree (fetched_at);
|
||
CREATE INDEX idx_tenant_goods_master_fetched_pk_fetched_at_id_content_hash ON ods.tenant_goods_master USING btree (fetched_at, id, content_hash);
|
||
|