454 lines
16 KiB
PL/PgSQL
454 lines
16 KiB
PL/PgSQL
-- =====================================================
|
||
-- 推销模式功能 - 数据库脚本 V2（商家级别）
|
||
-- 包含：商家推销配置、用户余额系统、分享免单系统、会员等级系统
|
||
-- 创建日期: 2026-03-09
|
||
-- 重要变更：推销模式改为商家级别，每个商家独立配置
|
||
-- =====================================================
|
||
|
||
-- =====================================================
|
||
-- 0. 启用必要扩展
|
||
-- =====================================================
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
||
|
||
-- =====================================================
|
||
-- 一、商家推销配置表
|
||
-- =====================================================
|
||
|
||
DROP TABLE IF EXISTS ml_merchant_promotion_config CASCADE;
|
||
CREATE TABLE ml_merchant_promotion_config (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
merchant_id UUID NOT NULL UNIQUE,
|
||
promotion_enabled BOOLEAN DEFAULT FALSE,
|
||
share_free_enabled BOOLEAN DEFAULT FALSE,
|
||
distribution_enabled BOOLEAN DEFAULT FALSE,
|
||
required_count INT DEFAULT 4,
|
||
reward_type VARCHAR(20) DEFAULT 'product_price',
|
||
fixed_reward_amount DECIMAL(10,2) DEFAULT 0,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_merchant_promotion_merchant_id ON ml_merchant_promotion_config(merchant_id);
|
||
|
||
COMMENT ON TABLE ml_merchant_promotion_config IS '商家推销模式配置表';
|
||
COMMENT ON COLUMN ml_merchant_promotion_config.promotion_enabled IS '是否开启推销模式';
|
||
COMMENT ON COLUMN ml_merchant_promotion_config.share_free_enabled IS '是否开启分享免单';
|
||
COMMENT ON COLUMN ml_merchant_promotion_config.distribution_enabled IS '是否开启经销点返利';
|
||
COMMENT ON COLUMN ml_merchant_promotion_config.required_count IS '分享免单所需购买数';
|
||
COMMENT ON COLUMN ml_merchant_promotion_config.reward_type IS '奖励类型：product_price-商品价格，fixed-固定金额';
|
||
|
||
-- =====================================================
|
||
-- 二、用户余额系统（按商家区分）
|
||
-- =====================================================
|
||
|
||
-- 1. 商家用户余额表
|
||
DROP TABLE IF EXISTS ml_merchant_user_balance CASCADE;
|
||
CREATE TABLE ml_merchant_user_balance (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
merchant_id UUID NOT NULL,
|
||
user_id UUID NOT NULL,
|
||
balance DECIMAL(10,2) DEFAULT 0,
|
||
frozen_balance DECIMAL(10,2) DEFAULT 0,
|
||
total_earned DECIMAL(10,2) DEFAULT 0,
|
||
total_withdrawn DECIMAL(10,2) DEFAULT 0,
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(merchant_id, user_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_merchant_user_balance_merchant_id ON ml_merchant_user_balance(merchant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_merchant_user_balance_user_id ON ml_merchant_user_balance(user_id);
|
||
|
||
COMMENT ON TABLE ml_merchant_user_balance IS '商家用户余额表（每个商家独立余额）';
|
||
|
||
-- 2. 余额变动记录表
|
||
DROP TABLE IF EXISTS ml_balance_records CASCADE;
|
||
CREATE TABLE ml_balance_records (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
merchant_id UUID NOT NULL,
|
||
user_id UUID NOT NULL,
|
||
type VARCHAR(50) NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
balance_before DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
balance_after DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
related_id UUID,
|
||
description VARCHAR(200),
|
||
operator_id UUID,
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_balance_records_merchant_id ON ml_balance_records(merchant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_balance_records_user_id ON ml_balance_records(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_balance_records_type ON ml_balance_records(type);
|
||
CREATE INDEX IF NOT EXISTS idx_balance_records_created_at ON ml_balance_records(created_at);
|
||
|
||
COMMENT ON TABLE ml_balance_records IS '余额变动记录表';
|
||
COMMENT ON COLUMN ml_balance_records.type IS '类型：free_order-免单奖励，rebate-返利，withdraw-提现，clear-清零，manual-手动调整';
|
||
|
||
-- =====================================================
|
||
-- 三、分享免单系统
|
||
-- =====================================================
|
||
|
||
-- 1. 分享记录表
|
||
DROP TABLE IF EXISTS ml_share_records CASCADE;
|
||
CREATE TABLE ml_share_records (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
merchant_id UUID NOT NULL,
|
||
user_id UUID NOT NULL,
|
||
product_id UUID NOT NULL,
|
||
order_id UUID NOT NULL,
|
||
order_item_id UUID,
|
||
share_code VARCHAR(20) NOT NULL UNIQUE,
|
||
product_name VARCHAR(200),
|
||
product_image VARCHAR(500),
|
||
product_price DECIMAL(10,2),
|
||
required_count INT DEFAULT 4,
|
||
current_count INT DEFAULT 0,
|
||
status INT DEFAULT 0,
|
||
reward_amount DECIMAL(10,2),
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
completed_at TIMESTAMPTZ,
|
||
expired_at TIMESTAMPTZ
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_share_records_merchant_id ON ml_share_records(merchant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_share_records_user_id ON ml_share_records(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_share_records_share_code ON ml_share_records(share_code);
|
||
CREATE INDEX IF NOT EXISTS idx_share_records_status ON ml_share_records(status);
|
||
|
||
COMMENT ON TABLE ml_share_records IS '分享免单记录表';
|
||
COMMENT ON COLUMN ml_share_records.status IS '状态：0-进行中，1-已完成，2-已失效，3-已过期';
|
||
|
||
-- 2. 二级购买记录表
|
||
DROP TABLE IF EXISTS ml_secondary_purchases CASCADE;
|
||
CREATE TABLE ml_secondary_purchases (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
share_record_id UUID NOT NULL,
|
||
buyer_id UUID NOT NULL,
|
||
order_id UUID NOT NULL,
|
||
quantity INT DEFAULT 1,
|
||
unit_price DECIMAL(10,2),
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
UNIQUE(order_id, share_record_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_secondary_purchases_share_record_id ON ml_secondary_purchases(share_record_id);
|
||
CREATE INDEX IF NOT EXISTS idx_secondary_purchases_buyer_id ON ml_secondary_purchases(buyer_id);
|
||
|
||
COMMENT ON TABLE ml_secondary_purchases IS '二级用户购买记录表';
|
||
|
||
-- 3. 免单奖励记录表
|
||
DROP TABLE IF EXISTS ml_free_order_rewards CASCADE;
|
||
CREATE TABLE ml_free_order_rewards (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
merchant_id UUID NOT NULL,
|
||
user_id UUID NOT NULL,
|
||
share_record_id UUID NOT NULL,
|
||
amount DECIMAL(10,2) NOT NULL,
|
||
status INT DEFAULT 0,
|
||
balance_record_id UUID,
|
||
cleared_at TIMESTAMPTZ,
|
||
cleared_by UUID,
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_free_order_rewards_merchant_id ON ml_free_order_rewards(merchant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_free_order_rewards_user_id ON ml_free_order_rewards(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_free_order_rewards_status ON ml_free_order_rewards(status);
|
||
|
||
COMMENT ON TABLE ml_free_order_rewards IS '免单奖励记录表';
|
||
COMMENT ON COLUMN ml_free_order_rewards.status IS '状态：0-待发放，1-已发放，2-已清零';
|
||
|
||
-- =====================================================
|
||
-- 四、会员等级系统（全局）
|
||
-- =====================================================
|
||
|
||
-- 1. 会员等级配置表
|
||
DROP TABLE IF EXISTS ml_member_levels CASCADE;
|
||
CREATE TABLE ml_member_levels (
|
||
id INT PRIMARY KEY,
|
||
name VARCHAR(50) NOT NULL,
|
||
min_amount DECIMAL(10,2) DEFAULT 0,
|
||
discount DECIMAL(5,4) DEFAULT 1.0000,
|
||
icon VARCHAR(200),
|
||
description TEXT,
|
||
sort_order INT DEFAULT 0,
|
||
status INT DEFAULT 1,
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE ml_member_levels IS '会员等级配置表';
|
||
COMMENT ON COLUMN ml_member_levels.discount IS '折扣率，0.85表示85折';
|
||
|
||
-- 初始化会员等级数据
|
||
INSERT INTO ml_member_levels (id, name, min_amount, discount, description, sort_order) VALUES
|
||
(0, '普通会员', 0, 1.0000, '注册即可成为普通会员', 0),
|
||
(1, '铜牌会员', 500, 0.9800, '累计消费500元升级', 1),
|
||
(2, '银牌会员', 2000, 0.9500, '累计消费2000元升级', 2),
|
||
(3, '金牌会员', 5000, 0.9200, '累计消费5000元升级', 3),
|
||
(4, '钻石会员', 10000, 0.8800, '累计消费10000元升级', 4),
|
||
(5, 'VIP会员', 0, 0.8500, '商家特邀会员', 5);
|
||
|
||
-- 2. 会员等级变更记录表
|
||
DROP TABLE IF EXISTS ml_member_level_logs CASCADE;
|
||
CREATE TABLE ml_member_level_logs (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
user_id UUID NOT NULL,
|
||
old_level INT DEFAULT 0,
|
||
new_level INT NOT NULL,
|
||
reason VARCHAR(200),
|
||
operator_id UUID,
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_member_level_logs_user_id ON ml_member_level_logs(user_id);
|
||
|
||
COMMENT ON TABLE ml_member_level_logs IS '会员等级变更记录表';
|
||
|
||
-- =====================================================
|
||
-- 五、RLS 策略
|
||
-- =====================================================
|
||
|
||
-- 商家推销配置表 RLS
|
||
ALTER TABLE ml_merchant_promotion_config ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Anyone can view merchant promotion config" ON ml_merchant_promotion_config;
|
||
CREATE POLICY "Anyone can view merchant promotion config"
|
||
ON ml_merchant_promotion_config FOR SELECT
|
||
TO authenticated, anon
|
||
USING (true);
|
||
|
||
-- 商家用户余额表 RLS
|
||
ALTER TABLE ml_merchant_user_balance ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Users can view own merchant balance" ON ml_merchant_user_balance;
|
||
CREATE POLICY "Users can view own merchant balance"
|
||
ON ml_merchant_user_balance FOR SELECT
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
DROP POLICY IF EXISTS "Users can insert own merchant balance" ON ml_merchant_user_balance;
|
||
CREATE POLICY "Users can insert own merchant balance"
|
||
ON ml_merchant_user_balance FOR INSERT
|
||
TO authenticated
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
DROP POLICY IF EXISTS "Users can update own merchant balance" ON ml_merchant_user_balance;
|
||
CREATE POLICY "Users can update own merchant balance"
|
||
ON ml_merchant_user_balance FOR UPDATE
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- 余额记录表 RLS
|
||
ALTER TABLE ml_balance_records ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Users can view own balance records" ON ml_balance_records;
|
||
CREATE POLICY "Users can view own balance records"
|
||
ON ml_balance_records FOR SELECT
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
DROP POLICY IF EXISTS "Users can insert own balance records" ON ml_balance_records;
|
||
CREATE POLICY "Users can insert own balance records"
|
||
ON ml_balance_records FOR INSERT
|
||
TO authenticated
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
-- 分享记录表 RLS
|
||
ALTER TABLE ml_share_records ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Users can view own share records" ON ml_share_records;
|
||
CREATE POLICY "Users can view own share records"
|
||
ON ml_share_records FOR SELECT
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
DROP POLICY IF EXISTS "Users can insert own share records" ON ml_share_records;
|
||
CREATE POLICY "Users can insert own share records"
|
||
ON ml_share_records FOR INSERT
|
||
TO authenticated
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
DROP POLICY IF EXISTS "Users can update own share records" ON ml_share_records;
|
||
CREATE POLICY "Users can update own share records"
|
||
ON ml_share_records FOR UPDATE
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- 允许通过分享码查询
|
||
DROP POLICY IF EXISTS "Anyone can view by share code" ON ml_share_records;
|
||
CREATE POLICY "Anyone can view by share code"
|
||
ON ml_share_records FOR SELECT
|
||
TO authenticated, anon
|
||
USING (share_code IS NOT NULL);
|
||
|
||
-- 二级购买记录表 RLS
|
||
ALTER TABLE ml_secondary_purchases ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Users can view own secondary purchases" ON ml_secondary_purchases;
|
||
CREATE POLICY "Users can view own secondary purchases"
|
||
ON ml_secondary_purchases FOR SELECT
|
||
TO authenticated
|
||
USING (buyer_id = auth.uid() OR EXISTS (
|
||
SELECT 1 FROM ml_share_records WHERE id = share_record_id AND user_id = auth.uid()
|
||
));
|
||
|
||
DROP POLICY IF EXISTS "Users can insert secondary purchases" ON ml_secondary_purchases;
|
||
CREATE POLICY "Users can insert secondary purchases"
|
||
ON ml_secondary_purchases FOR INSERT
|
||
TO authenticated
|
||
WITH CHECK (true);
|
||
|
||
-- 免单奖励记录表 RLS
|
||
ALTER TABLE ml_free_order_rewards ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Users can view own rewards" ON ml_free_order_rewards;
|
||
CREATE POLICY "Users can view own rewards"
|
||
ON ml_free_order_rewards FOR SELECT
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- 会员等级配置表 RLS
|
||
ALTER TABLE ml_member_levels ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Anyone can view member levels" ON ml_member_levels;
|
||
CREATE POLICY "Anyone can view member levels"
|
||
ON ml_member_levels FOR SELECT
|
||
TO authenticated, anon
|
||
USING (status = 1);
|
||
|
||
-- 会员等级变更记录表 RLS
|
||
ALTER TABLE ml_member_level_logs ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Users can view own level logs" ON ml_member_level_logs;
|
||
CREATE POLICY "Users can view own level logs"
|
||
ON ml_member_level_logs FOR SELECT
|
||
TO authenticated
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- =====================================================
|
||
-- 六、数据库函数
|
||
-- =====================================================
|
||
|
||
-- 1. 获取商家推销配置
|
||
CREATE OR REPLACE FUNCTION get_merchant_promotion_config(p_merchant_id UUID)
|
||
RETURNS TABLE (
|
||
promotion_enabled BOOLEAN,
|
||
share_free_enabled BOOLEAN,
|
||
distribution_enabled BOOLEAN,
|
||
required_count INT,
|
||
reward_type VARCHAR,
|
||
fixed_reward_amount DECIMAL
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
mpc.promotion_enabled,
|
||
mpc.share_free_enabled,
|
||
mpc.distribution_enabled,
|
||
mpc.required_count,
|
||
mpc.reward_type,
|
||
mpc.fixed_reward_amount
|
||
FROM ml_merchant_promotion_config mpc
|
||
WHERE mpc.merchant_id = p_merchant_id;
|
||
|
||
IF NOT FOUND THEN
|
||
RETURN QUERY SELECT FALSE, FALSE, FALSE, 4, 'product_price'::VARCHAR, 0::DECIMAL;
|
||
END IF;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 2. 检查商家是否开启分享免单
|
||
CREATE OR REPLACE FUNCTION is_share_free_enabled(p_merchant_id UUID)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
v_enabled BOOLEAN;
|
||
BEGIN
|
||
SELECT promotion_enabled AND share_free_enabled INTO v_enabled
|
||
FROM ml_merchant_promotion_config
|
||
WHERE merchant_id = p_merchant_id;
|
||
|
||
RETURN COALESCE(v_enabled, FALSE);
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 3. 增加商家用户余额
|
||
CREATE OR REPLACE FUNCTION add_merchant_user_balance(
|
||
p_merchant_id UUID,
|
||
p_user_id UUID,
|
||
p_amount DECIMAL(10,2),
|
||
p_type VARCHAR(50),
|
||
p_related_id UUID DEFAULT NULL,
|
||
p_description VARCHAR(200) DEFAULT NULL,
|
||
p_operator_id UUID DEFAULT NULL
|
||
)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
v_balance_before DECIMAL(10,2);
|
||
v_balance_after DECIMAL(10,2);
|
||
BEGIN
|
||
SELECT COALESCE(balance, 0) INTO v_balance_before
|
||
FROM ml_merchant_user_balance
|
||
WHERE merchant_id = p_merchant_id AND user_id = p_user_id;
|
||
|
||
IF v_balance_before IS NULL THEN
|
||
INSERT INTO ml_merchant_user_balance (merchant_id, user_id, balance, total_earned)
|
||
VALUES (p_merchant_id, p_user_id, p_amount, p_amount);
|
||
v_balance_before := 0;
|
||
v_balance_after := p_amount;
|
||
ELSE
|
||
UPDATE ml_merchant_user_balance
|
||
SET balance = balance + p_amount,
|
||
total_earned = total_earned + CASE WHEN p_amount > 0 THEN p_amount ELSE 0 END,
|
||
updated_at = NOW()
|
||
WHERE merchant_id = p_merchant_id AND user_id = p_user_id;
|
||
v_balance_after := v_balance_before + p_amount;
|
||
END IF;
|
||
|
||
INSERT INTO ml_balance_records (merchant_id, user_id, type, amount, balance_before, balance_after, related_id, description, operator_id)
|
||
VALUES (p_merchant_id, p_user_id, p_type, p_amount, v_balance_before, v_balance_after, p_related_id, p_description, p_operator_id);
|
||
|
||
RETURN TRUE;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 4. 清零商家用户余额
|
||
CREATE OR REPLACE FUNCTION clear_merchant_user_balance(
|
||
p_merchant_id UUID,
|
||
p_user_id UUID,
|
||
p_operator_id UUID,
|
||
p_description VARCHAR(200) DEFAULT '商家清零余额'
|
||
)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
v_balance_before DECIMAL(10,2);
|
||
BEGIN
|
||
SELECT balance INTO v_balance_before
|
||
FROM ml_merchant_user_balance
|
||
WHERE merchant_id = p_merchant_id AND user_id = p_user_id;
|
||
|
||
IF v_balance_before IS NULL OR v_balance_before = 0 THEN
|
||
RETURN FALSE;
|
||
END IF;
|
||
|
||
UPDATE ml_merchant_user_balance
|
||
SET balance = 0,
|
||
updated_at = NOW()
|
||
WHERE merchant_id = p_merchant_id AND user_id = p_user_id;
|
||
|
||
INSERT INTO ml_balance_records (merchant_id, user_id, type, amount, balance_before, balance_after, description, operator_id)
|
||
VALUES (p_merchant_id, p_user_id, 'clear', -v_balance_before, v_balance_before, 0, p_description, p_operator_id);
|
||
|
||
RETURN TRUE;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- =====================================================
|
||
-- 七、测试数据（可选）
|
||
-- =====================================================
|
||
|
||
-- 为某个商家开启推销模式（替换YOUR_MERCHANT_ID为实际商家ID）
|
||
-- INSERT INTO ml_merchant_promotion_config (merchant_id, promotion_enabled, share_free_enabled, required_count)
|
||
-- VALUES ('YOUR_MERCHANT_ID', TRUE, TRUE, 4);
|