51 lines
2.0 KiB
SQL
51 lines
2.0 KiB
SQL
-- 1. 用户钱包表 (余额)
|
|
create table if not exists ml_user_wallets (
|
|
user_id uuid not null primary key,
|
|
balance decimal(10,2) default 0.00 not null,
|
|
frozen_balance decimal(10,2) default 0.00 not null,
|
|
currency varchar(10) default 'CNY',
|
|
status int default 1, -- 1: 正常, 0: 冻结
|
|
pay_password varchar(100), -- 支付密码 (加密)
|
|
created_at timestamptz default now(),
|
|
updated_at timestamptz default now()
|
|
);
|
|
|
|
-- 2. 钱包交易记录表
|
|
create table if not exists ml_wallet_transactions (
|
|
id uuid default gen_random_uuid() primary key,
|
|
user_id uuid not null,
|
|
amount decimal(10,2) not null, -- 正数表示收入，负数表示支出
|
|
balance_after decimal(10,2) not null, -- 变动后余额
|
|
type varchar(50) not null, -- 交易类型: recharge(充值), payment(支付), refund(退款), withdrawal(提现)
|
|
description text,
|
|
order_id varchar(100), -- 关联订单ID (可选)
|
|
created_at timestamptz default now()
|
|
);
|
|
|
|
-- 3. 用户积分表
|
|
create table if not exists ml_user_points (
|
|
user_id uuid not null primary key,
|
|
points int default 0 not null, -- 当前可用积分
|
|
total_earned int default 0, -- 历史累计获得积分
|
|
updated_at timestamptz default now()
|
|
);
|
|
|
|
-- 4. 积分变动记录表
|
|
create table if not exists ml_point_records (
|
|
id uuid default gen_random_uuid() primary key,
|
|
user_id uuid not null,
|
|
points int not null, -- 变动积分 (正/负)
|
|
type varchar(50) not null, -- 类型: signin(签到), shopping(购物), redeem(兑换), expire(过期)
|
|
description text,
|
|
created_at timestamptz default now()
|
|
);
|
|
|
|
-- 添加说明
|
|
comment on table ml_user_wallets is '用户钱包余额表';
|
|
comment on table ml_wallet_transactions is '钱包余额变动明细';
|
|
comment on table ml_user_points is '用户积分汇总表';
|
|
comment on table ml_point_records is '积分变动明细表';
|
|
|
|
-- 插入一些测试数据 (可选，仅用于开发环境)
|
|
-- insert into ml_user_points (user_id, points, total_earned) values ('YOUR_USER_ID', 1000, 1000);
|