46 lines
1.6 KiB
SQL
46 lines
1.6 KiB
SQL
-- 1. 创建分销等级表
|
||
CREATE TABLE IF NOT EXISTS public.ak_distribution_level (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name TEXT NOT NULL, -- 等级名称
|
||
level INTEGER NOT NULL UNIQUE, -- 等级权重/数字（如1, 2, 3）
|
||
percent1 DECIMAL(10,2) DEFAULT 0, -- 一级分佣比例 (%)
|
||
percent2 DECIMAL(10,2) DEFAULT 0, -- 二级分佣比例 (%)
|
||
task_total INTEGER DEFAULT 0, -- 任务总数
|
||
task_finish INTEGER DEFAULT 0, -- 需完成数量（升级门槛）
|
||
is_visible BOOLEAN DEFAULT true, -- 是否显示
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
|
||
-- 2. 启用 RLS
|
||
ALTER TABLE public.ak_distribution_level ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 3. 创建权限策略
|
||
-- 允许所有认证用户查看等级（用于前端展示）
|
||
CREATE POLICY "Anyone can view levels"
|
||
ON public.ak_distribution_level FOR SELECT
|
||
TO authenticated
|
||
USING (true);
|
||
|
||
-- 仅允许 Admin 进行管理 (INSERT/UPDATE/DELETE)
|
||
CREATE POLICY "Admins can manage levels"
|
||
ON public.ak_distribution_level FOR ALL
|
||
TO authenticated
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM public.ak_users
|
||
WHERE auth_id = auth.uid() AND role = 'admin'
|
||
)
|
||
)
|
||
WITH CHECK (
|
||
EXISTS (
|
||
SELECT 1 FROM public.ak_users
|
||
WHERE auth_id = auth.uid() AND role = 'admin'
|
||
)
|
||
);
|
||
|
||
-- 4. 插入初始化示例数据
|
||
INSERT INTO public.ak_distribution_level (name, level, percent1, percent2, is_visible)
|
||
VALUES ('普通分销员', 1, 10.00, 5.00, true)
|
||
ON CONFLICT (level) DO NOTHING;
|