49 lines
2.3 KiB
SQL
49 lines
2.3 KiB
SQL
-- 创建代理钱包主表
|
||
-- 用于管理店铺级别的主钱包和分佣钱包
|
||
|
||
CREATE TABLE IF NOT EXISTS tb_agent_wallet (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
shop_id BIGINT NOT NULL,
|
||
wallet_type VARCHAR(20) NOT NULL,
|
||
balance BIGINT NOT NULL DEFAULT 0,
|
||
frozen_balance BIGINT NOT NULL DEFAULT 0,
|
||
currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
|
||
status INT NOT NULL DEFAULT 1,
|
||
version INT NOT NULL DEFAULT 0,
|
||
shop_id_tag BIGINT NOT NULL,
|
||
enterprise_id_tag BIGINT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP,
|
||
|
||
-- 约束
|
||
CONSTRAINT chk_agent_wallet_balance CHECK (balance >= 0),
|
||
CONSTRAINT chk_agent_wallet_frozen_balance CHECK (frozen_balance >= 0 AND frozen_balance <= balance),
|
||
CONSTRAINT chk_agent_wallet_status CHECK (status IN (1, 2, 3)),
|
||
CONSTRAINT chk_agent_wallet_type CHECK (wallet_type IN ('main', 'commission'))
|
||
);
|
||
|
||
-- 唯一索引：shop_id + wallet_type 在未删除时唯一
|
||
CREATE UNIQUE INDEX idx_agent_wallet_shop_type ON tb_agent_wallet (shop_id, wallet_type) WHERE deleted_at IS NULL;
|
||
|
||
-- 状态索引
|
||
CREATE INDEX idx_agent_wallet_status ON tb_agent_wallet (status);
|
||
|
||
-- 多租户过滤索引
|
||
CREATE INDEX idx_agent_wallet_shop_tag ON tb_agent_wallet (shop_id_tag);
|
||
|
||
-- 企业标签索引（用于多租户过滤）
|
||
CREATE INDEX idx_agent_wallet_enterprise_tag ON tb_agent_wallet (enterprise_id_tag) WHERE enterprise_id_tag IS NOT NULL;
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE tb_agent_wallet IS '代理钱包主表';
|
||
COMMENT ON COLUMN tb_agent_wallet.shop_id IS '店铺 ID';
|
||
COMMENT ON COLUMN tb_agent_wallet.wallet_type IS '钱包类型：main-主钱包 | commission-分佣钱包';
|
||
COMMENT ON COLUMN tb_agent_wallet.balance IS '余额（单位：分）';
|
||
COMMENT ON COLUMN tb_agent_wallet.frozen_balance IS '冻结余额（单位：分）';
|
||
COMMENT ON COLUMN tb_agent_wallet.currency IS '币种';
|
||
COMMENT ON COLUMN tb_agent_wallet.status IS '钱包状态：1-正常 2-冻结 3-关闭';
|
||
COMMENT ON COLUMN tb_agent_wallet.version IS '版本号（乐观锁）';
|
||
COMMENT ON COLUMN tb_agent_wallet.shop_id_tag IS '店铺 ID 标签（多租户过滤）';
|
||
COMMENT ON COLUMN tb_agent_wallet.enterprise_id_tag IS '企业 ID 标签（多租户过滤）';
|