huangzhenbao/medical-mall
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
huangzhenbao-merchant
medical-mall/docs/sql/all_schema_merged.sql

2151 lines
92 KiB
PL/PgSQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- =====================================================================================
-- Migration: 全量软删除 (Soft Delete) 基础设施补齐
-- 位置：docs/sql/10_schema/99_soft_delete_migration_v1.sql
-- 对象类型：ALTER TABLE
-- 说明：为所有核心业务表补齐 deleted_at, deleted_by, restored_at, restored_by 字段
-- 涵盖：权限、配置、内容、装修、物流、分销、财务、客服、营销、商品、用户、订单
-- =====================================================================================
DO $$
DECLARE
-- 需补齐软删除字段的业务表全量清单
t_names TEXT[] := ARRAY[
-- 1. 系统与权限 (Auth/System)
'ak_roles', 'ak_permissions', 'ak_admin_roles', 'ak_role_permissions', 'ml_system_configs',
-- 2. 内容与装修 (CMS/Decoration)
'ml_articles', 'ml_article_categories', 'ak_diy_pages',
-- 3. 物流资源 (Delivery)
'ml_delivery_staff', 'ml_delivery_stations', 'ak_shipping_templates',
-- 4. 分销体系 (Distribution)
'ak_distribution_agents', 'ak_distribution_divisions',
'ak_distribution_agent_applications', 'ak_distribution_division_applications',
'ak_promoter_relations', 'ak_commission_logs', 'ak_distribution_level', 'ak_distribution_config',
-- 5. 财务管理 (Finance)
'ml_extract', 'ml_invoices', 'ml_user_bill', 'ml_user_recharge',
-- 6. 客服系统 (Kefu)
'ml_kefu_accounts', 'ml_kefu_words', 'ml_kefu_word_categories', 'ml_kefu_auto_replies', 'ml_kefu_feedbacks',
-- 7. 营销活动 (Marketing)
'ak_advanced_marketing', 'ak_bargain_groupbuy', 'ak_live_products', 'ak_lottery_live',
'ak_marketing_checkin_configs', 'ak_marketing_newcomer_config', 'ak_marketing_signin_logs',
'ak_member_management', 'ak_recharge_management', 'ak_signin_configs',
-- 8. 商品中心 (Product)
'ml_products', 'ml_product_skus', 'ml_categories',
'ak_product_labels', 'ak_product_member_prices', 'ak_product_protections', 'ak_product_templates',
-- 9. 用户管理 (User)
'ak_user_labels', 'ak_user_groups', 'ak_user_levels', 'ak_users',
-- 10. 订单中心 (Order)
'ml_orders'
];
t_name TEXT;
BEGIN
FOREACH t_name IN ARRAY t_names LOOP
-- 检查表是否存在
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = t_name) THEN
-- 1. 增加 deleted_at 字段
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'deleted_at') THEN
EXECUTE format('ALTER TABLE public.%I ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL', t_name);
END IF;
-- 2. 增加 deleted_by 字段
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'deleted_by') THEN
EXECUTE format('ALTER TABLE public.%I ADD COLUMN deleted_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL', t_name);
END IF;
-- 3. 增加 restored_at 字段
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'restored_at') THEN
EXECUTE format('ALTER TABLE public.%I ADD COLUMN restored_at TIMESTAMPTZ DEFAULT NULL', t_name);
END IF;
-- 4. 增加 restored_by 字段
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name = 'restored_by') THEN
EXECUTE format('ALTER TABLE public.%I ADD COLUMN restored_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL', t_name);
END IF;
-- 5. 建立软删除索引
EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON public.%I (deleted_at) WHERE deleted_at IS NULL', 'idx_' || t_name || '_soft_delete', t_name);
END IF;
END LOOP;
END $$;
-- =====================================================================================
-- Schema: 系统配置表
-- 位置：docs/sql/10_schema/admin/ml_system_configs_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 说明：统一存储系统、应用、维护等模块的 Key-Value 配置项
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ml_system_configs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
config_key TEXT UNIQUE NOT NULL,
config_value JSONB NOT NULL DEFAULT '{}'::jsonb,
description TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_system_configs_key ON public.ml_system_configs (config_key);
-- 注释
COMMENT ON TABLE public.ml_system_configs IS '系统全局配置表';
COMMENT ON COLUMN public.ml_system_configs.config_key IS '配置唯一标识键';
COMMENT ON COLUMN public.ml_system_configs.config_value IS '配置内容 (JSONB)';
-- =====================================================================================
-- Migration: Auth 安全约束增强
-- 位置：docs/sql/10_schema/auth/ak_auth_security_constraints_v1.sql
-- 对象类型：ALTER TABLE / CONSTRAINT
-- 说明：增强 ak_users 与 auth.users 的关联安全性，防止孤儿数据
-- =====================================================================================
-- 1. 确保 ak_users.auth_id 存在外键约束指向 auth.users
-- 注意：Supabase 的 auth.users 表在 auth schema 下，需要确保权限正确
DO $$
BEGIN
-- 检查是否已存在外键约束
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'fk_ak_users_auth_id'
AND table_name = 'ak_users'
) THEN
-- 添加外键约束，当 auth.users 被删除时自动删除对应的 profile
ALTER TABLE public.ak_users
ADD CONSTRAINT fk_ak_users_auth_id
FOREIGN KEY (auth_id) REFERENCES auth.users(id)
ON DELETE CASCADE;
END IF;
END $$;
-- 2. 为 auth_id 建立唯一索引，确保一个 auth 用户只有一个 profile
CREATE UNIQUE INDEX IF NOT EXISTS idx_ak_users_auth_id_unique
ON public.ak_users(auth_id);
-- 3. 为 role 字段建立索引，加速权限查询
CREATE INDEX IF NOT EXISTS idx_ak_users_role
ON public.ak_users(role);
-- 4. 添加检查约束，确保 role 字段只能是有效值
ALTER TABLE public.ak_users
DROP CONSTRAINT IF EXISTS chk_ak_users_role_valid;
ALTER TABLE public.ak_users
ADD CONSTRAINT chk_ak_users_role_valid
CHECK (role IN ('user', 'admin', 'staff', 'agent', 'kefu') OR role IS NULL);
-- 5. 为 ak_admin_roles 添加约束确保关联有效性
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'fk_ak_admin_roles_admin_id'
AND table_name = 'ak_admin_roles'
) THEN
ALTER TABLE public.ak_admin_roles
ADD CONSTRAINT fk_ak_admin_roles_admin_id
FOREIGN KEY (admin_id) REFERENCES public.ak_users(id)
ON DELETE CASCADE;
END IF;
END $$;
-- 6. 为 ak_role_permissions 添加约束确保关联有效性
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'fk_ak_role_permissions_role_id'
AND table_name = 'ak_role_permissions'
) THEN
ALTER TABLE public.ak_role_permissions
ADD CONSTRAINT fk_ak_role_permissions_role_id
FOREIGN KEY (role_id) REFERENCES public.ak_roles(id)
ON DELETE CASCADE;
END IF;
END $$;
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'fk_ak_role_permissions_permission_id'
AND table_name = 'ak_role_permissions'
) THEN
ALTER TABLE public.ak_role_permissions
ADD CONSTRAINT fk_ak_role_permissions_permission_id
FOREIGN KEY (permission_id) REFERENCES public.ak_permissions(id)
ON DELETE CASCADE;
END IF;
END $$;-- =====================================================================================
-- Schema: 内容管理模块核心表
-- 位置：docs/sql/10_schema/cms/ml_cms_tables_v1.sql
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：包含文章分类及文章主表定义
-- =====================================================================================
-- 1. 文章分类表
CREATE TABLE IF NOT EXISTS public.ml_article_categories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
icon TEXT NULL,
sort INTEGER NOT NULL DEFAULT 0,
status SMALLINT NOT NULL DEFAULT 1, -- 1: 启用, 0: 禁用
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 唯一性约束
CREATE UNIQUE INDEX IF NOT EXISTS ml_article_categories_name_uniq ON public.ml_article_categories (name);
-- 2. 文章主表
CREATE TABLE IF NOT EXISTS public.ml_articles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
category_id UUID NOT NULL REFERENCES public.ml_article_categories(id),
title TEXT NOT NULL,
author TEXT NULL,
image TEXT NULL, -- 文章封面图
description TEXT NULL, -- 文章简介
content TEXT NOT NULL, -- 文章内容 (富文本)
status SMALLINT NOT NULL DEFAULT 0, -- 0: 未发布, 1: 已发布
views INTEGER NOT NULL DEFAULT 0, -- 浏览量
is_banner BOOLEAN NOT NULL DEFAULT FALSE, -- 是否展示在 banner
is_hot BOOLEAN NOT NULL DEFAULT FALSE, -- 是否热门
linked_product_id UUID NULL, -- 关联商品ID (可选)
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 常用查询索引
CREATE INDEX IF NOT EXISTS ml_articles_category_id_idx ON public.ml_articles (category_id);
CREATE INDEX IF NOT EXISTS ml_articles_status_idx ON public.ml_articles (status);
CREATE INDEX IF NOT EXISTS ml_articles_created_at_idx ON public.ml_articles (created_at DESC);
-- =====================================================================================
-- Schema: 装修模块 - DIY 页面配置表
-- 位置：docs/sql/10_schema/decoration/ak_diy_pages_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 说明：存储首页、专题页及个人中心的 DIY 布局 JSON 配置
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_diy_pages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
type TEXT NOT NULL, -- home: 首页, topic: 专题页, user: 个人中心
config JSONB NOT NULL DEFAULT '{}'::jsonb, -- 核心布局配置 (组件列表及参数)
is_home BOOLEAN NOT NULL DEFAULT FALSE, -- 是否为生效首页
is_active BOOLEAN NOT NULL DEFAULT TRUE, -- 是否启用
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_by UUID REFERENCES public.ak_users(id),
updated_by UUID REFERENCES public.ak_users(id)
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_diy_pages_type ON public.ak_diy_pages (type);
CREATE INDEX IF NOT EXISTS idx_diy_pages_is_home ON public.ak_diy_pages (is_home) WHERE is_home = TRUE;
-- 注释
COMMENT ON TABLE public.ak_diy_pages IS 'DIY 页面装修配置表';
COMMENT ON COLUMN public.ak_diy_pages.type IS '页面类型: home(首页), topic(专题), user(个人中心)';
COMMENT ON COLUMN public.ak_diy_pages.config IS 'DIY 布局配置 JSON';
-- =====================================================================================
-- Schema: 物流设置 (Delivery) 核心表
-- 位置：docs/sql/10_schema/delivery/ak_delivery_system_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 说明：包含配送员管理表、提货点/核销点管理表
-- =====================================================================================
-- 1. 配送员管理表
CREATE TABLE IF NOT EXISTS public.ml_delivery_staff (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID REFERENCES public.ak_users(id) ON DELETE SET NULL, -- 关联用户(可选)
nickname TEXT NOT NULL, -- 配送员名称
avatar TEXT, -- 头像
phone TEXT NOT NULL, -- 手机号
status SMALLINT NOT NULL DEFAULT 1, -- 状态: 1-启用, 0-禁用
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 2. 提货点/核销点管理表
CREATE TABLE IF NOT EXISTS public.ml_delivery_stations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL, -- 提货点名称
phone TEXT NOT NULL, -- 联系电话
address TEXT NOT NULL, -- 详细地址
image TEXT, -- 门店图片
lng NUMERIC(10,7), -- 经度
lat NUMERIC(10,7), -- 纬度
status SMALLINT NOT NULL DEFAULT 1, -- 状态: 1-显示, 0-隐藏
sort_order INTEGER DEFAULT 0,
business_hours JSONB, -- 营业时间 (如: {"start": "09:00", "end": "21:00"})
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_delivery_staff_phone ON public.ml_delivery_staff(phone);
CREATE INDEX IF NOT EXISTS idx_delivery_stations_status ON public.ml_delivery_stations(status);
-- 注释
COMMENT ON TABLE public.ml_delivery_staff IS '配送员信息表';
COMMENT ON TABLE public.ml_delivery_stations IS '提货点/核销点信息表';
-- =====================================================================================
-- Schema: 医养执行端 Delivery 基础表升级
-- 位置：docs/sql/10_schema/delivery/ak_delivery_system_v2.sql
-- 对象类型：ALTER TABLE / INDEX / TRIGGER
-- 版本：v2
-- 说明：在 v1 基础上最小升级为“医养上门服务执行端”可用模型。
-- 补齐 uid 唯一索引、软删除、机构关联、staff_no、在线状态、资质状态。
-- =====================================================================================
-- 0. 通用 updated_at 触发器
CREATE OR REPLACE FUNCTION public.tg_set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;
-- 1. 配送/执行人员表升级
ALTER TABLE public.ml_delivery_staff
ADD COLUMN IF NOT EXISTS station_id UUID REFERENCES public.ml_delivery_stations(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS staff_no TEXT,
ADD COLUMN IF NOT EXISTS online_status TEXT NOT NULL DEFAULT 'resting',
ADD COLUMN IF NOT EXISTS certificate_status TEXT NOT NULL DEFAULT 'pending',
ADD COLUMN IF NOT EXISTS certificate_expire_at DATE,
ADD COLUMN IF NOT EXISTS service_area TEXT NOT NULL DEFAULT '',
ADD COLUMN IF NOT EXISTS skills JSONB NOT NULL DEFAULT '[]'::jsonb,
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS deleted_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL;
ALTER TABLE public.ml_delivery_staff
DROP CONSTRAINT IF EXISTS chk_ml_delivery_staff_online_status;
ALTER TABLE public.ml_delivery_staff
ADD CONSTRAINT chk_ml_delivery_staff_online_status
CHECK (online_status IN ('online', 'resting', 'busy'));
ALTER TABLE public.ml_delivery_staff
DROP CONSTRAINT IF EXISTS chk_ml_delivery_staff_certificate_status;
ALTER TABLE public.ml_delivery_staff
ADD CONSTRAINT chk_ml_delivery_staff_certificate_status
CHECK (certificate_status IN ('valid', 'expired', 'pending'));
-- 2. 站点/机构表升级
ALTER TABLE public.ml_delivery_stations
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS deleted_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL;
-- 3. 索引
CREATE UNIQUE INDEX IF NOT EXISTS uq_ml_delivery_staff_uid_active
ON public.ml_delivery_staff(uid)
WHERE uid IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_ml_delivery_staff_staff_no_active
ON public.ml_delivery_staff(staff_no)
WHERE staff_no IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_ml_delivery_staff_station_id
ON public.ml_delivery_staff(station_id)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_ml_delivery_staff_deleted_at
ON public.ml_delivery_staff(deleted_at);
CREATE INDEX IF NOT EXISTS idx_ml_delivery_staff_status_active
ON public.ml_delivery_staff(status, is_active)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_ml_delivery_stations_deleted_at
ON public.ml_delivery_stations(deleted_at);
CREATE INDEX IF NOT EXISTS idx_ml_delivery_stations_status_active
ON public.ml_delivery_stations(status)
WHERE deleted_at IS NULL;
-- 4. 自动维护 updated_at
DROP TRIGGER IF EXISTS trg_ml_delivery_staff_set_updated_at ON public.ml_delivery_staff;
CREATE TRIGGER trg_ml_delivery_staff_set_updated_at
BEFORE UPDATE ON public.ml_delivery_staff
FOR EACH ROW
EXECUTE FUNCTION public.tg_set_updated_at();
DROP TRIGGER IF EXISTS trg_ml_delivery_stations_set_updated_at ON public.ml_delivery_stations;
CREATE TRIGGER trg_ml_delivery_stations_set_updated_at
BEFORE UPDATE ON public.ml_delivery_stations
FOR EACH ROW
EXECUTE FUNCTION public.tg_set_updated_at();
-- 5. 注释
COMMENT ON COLUMN public.ml_delivery_staff.station_id IS '所属机构/服务站点 ID';
COMMENT ON COLUMN public.ml_delivery_staff.staff_no IS '服务人员编号';
COMMENT ON COLUMN public.ml_delivery_staff.online_status IS '在线状态：online/resting/busy';
COMMENT ON COLUMN public.ml_delivery_staff.certificate_status IS '资质状态：valid/expired/pending';
COMMENT ON COLUMN public.ml_delivery_staff.certificate_expire_at IS '主要资质到期日期';
COMMENT ON COLUMN public.ml_delivery_staff.service_area IS '服务区域描述';
COMMENT ON COLUMN public.ml_delivery_staff.skills IS '技能标签 JSON 数组';
COMMENT ON COLUMN public.ml_delivery_staff.deleted_at IS '软删除时间';
COMMENT ON COLUMN public.ml_delivery_staff.deleted_by IS '软删除操作人';
COMMENT ON COLUMN public.ml_delivery_stations.deleted_at IS '软删除时间';
COMMENT ON COLUMN public.ml_delivery_stations.deleted_by IS '软删除操作人';
-- 佣金流水表：记录每一笔佣金变动（冻结/可用/已提现/取消）
CREATE TABLE IF NOT EXISTS public.ak_commission_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- 佣金获得者
uid UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
-- 佣金来源用户（下单用户/被推广用户，可为空）
source_uid UUID REFERENCES public.ak_users(id) ON DELETE SET NULL,
-- 关联订单
order_id UUID REFERENCES public.ml_orders(id) ON DELETE SET NULL,
order_no VARCHAR(50),
-- 金额与状态
amount DECIMAL(12,2) NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'frozen', -- frozen/available/withdrawn/canceled
-- 冻结到期时间（用于解冻逻辑）
frozen_until TIMESTAMPTZ,
remark TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT chk_ak_commission_amount_nonneg CHECK (amount >= 0),
CONSTRAINT chk_ak_commission_status CHECK (status IN ('frozen','available','withdrawn','canceled'))
);
CREATE INDEX IF NOT EXISTS idx_ak_commission_logs_uid ON public.ak_commission_logs(uid);
CREATE INDEX IF NOT EXISTS idx_ak_commission_logs_order_id ON public.ak_commission_logs(order_id);
CREATE INDEX IF NOT EXISTS idx_ak_commission_logs_status ON public.ak_commission_logs(status);
CREATE INDEX IF NOT EXISTS idx_ak_commission_logs_created_at ON public.ak_commission_logs(created_at);
-- 启用 RLS
ALTER TABLE public.ak_commission_logs ENABLE ROW LEVEL SECURITY;
-- Admin 可读写
CREATE POLICY "Admins can manage commission logs"
ON public.ak_commission_logs
FOR ALL
TO authenticated
USING (
EXISTS (
SELECT 1 FROM public.ak_users
WHERE id = auth.uid() AND role = 'admin'
)
)
WITH CHECK (
EXISTS (
SELECT 1 FROM public.ak_users
WHERE id = auth.uid() AND role = 'admin'
)
);
-- 用户仅可查看自己的佣金流水
CREATE POLICY "Users can view own commission logs"
ON public.ak_commission_logs
FOR SELECT
TO authenticated
USING (uid = auth.uid());
-- =====================================================================================
-- Schema: 分销代理商申请表
-- 位置：docs/sql/10_schema/distribution/ak_distribution_agent_applications_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 依赖：ak_users, ak_distribution_divisions
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_distribution_agent_applications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
division_uid UUID NOT NULL REFERENCES public.ak_distribution_divisions(uid),
agent_name TEXT NOT NULL,
agent_phone TEXT NULL,
proof_images JSONB NULL, -- 申请凭证图片列表
status TEXT NOT NULL DEFAULT 'pending', -- pending/approved/rejected
refusal_reason TEXT NULL,
approved_at TIMESTAMPTZ NULL,
approved_by UUID NULL REFERENCES public.ak_users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_dist_agent_applications_uid ON public.ak_distribution_agent_applications(uid);
CREATE INDEX IF NOT EXISTS idx_dist_agent_applications_division_uid ON public.ak_distribution_agent_applications(division_uid);
CREATE INDEX IF NOT EXISTS idx_dist_agent_applications_status ON public.ak_distribution_agent_applications(status);
COMMENT ON TABLE public.ak_distribution_agent_applications IS '分销代理商申请记录表';
COMMENT ON COLUMN public.ak_distribution_agent_applications.proof_images IS '申请图片列表(JSON)';
-- =====================================================================================
-- Schema: 分销代理商管理表
-- 位置：docs/sql/10_schema/distribution/ak_distribution_agents_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 依赖：ak_users, ak_distribution_divisions
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_distribution_agents (
uid UUID PRIMARY KEY REFERENCES public.ak_users(id) ON DELETE CASCADE,
division_uid UUID NOT NULL REFERENCES public.ak_distribution_divisions(uid), -- 所属事业部
name TEXT NOT NULL,
commission_ratio NUMERIC(5,2) DEFAULT 0 CHECK (commission_ratio >= 0 AND commission_ratio <= 100),
is_enabled BOOLEAN DEFAULT TRUE,
end_time TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
created_by UUID REFERENCES public.ak_users(id),
updated_by UUID REFERENCES public.ak_users(id)
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_distribution_agents_division_uid ON public.ak_distribution_agents(division_uid);
-- 注释
COMMENT ON TABLE public.ak_distribution_agents IS '分销代理商信息表';
COMMENT ON COLUMN public.ak_distribution_agents.uid IS '用户ID（关联代理商本人）';
COMMENT ON COLUMN public.ak_distribution_agents.division_uid IS '所属事业部UID';
COMMENT ON COLUMN public.ak_distribution_agents.commission_ratio IS '代理商固定分佣比例(%)';
-- 1. 创建分销配置表
CREATE TABLE IF NOT EXISTS public.ak_distribution_config (
id TEXT PRIMARY KEY DEFAULT 'global_config',
is_enabled BOOLEAN DEFAULT true, -- 分销启用
extract_type TEXT DEFAULT '2', -- 分销模式: 1指定, 2人人, 3满额
bind_type TEXT DEFAULT '2', -- 绑定关系: 1所有用户, 2新用户
store_brokerage_binding_status TEXT DEFAULT '1', -- 绑定模式: 1永久, 2有效期, 3临时
brokerage_poster_status TEXT, -- 分销海报图路径/URL
brokerage_level INTEGER DEFAULT 2, -- 分销层级: 1, 2
is_area_manager BOOLEAN DEFAULT true, -- 事业部开关
is_agent_apply BOOLEAN DEFAULT true, -- 代理商申请开关
is_commission_window BOOLEAN DEFAULT true, -- 佣金悬浮窗开关
-- 返佣设置
is_self_brokerage BOOLEAN DEFAULT true, -- 自购返佣
is_member_brokerage BOOLEAN DEFAULT false, -- 购买会员返佣
brokerage_type TEXT DEFAULT '1', -- 返佣类型: 1价格, 2实付
is_promoter_brokerage BOOLEAN DEFAULT true, -- 推广用户返佣
promoter_brokerage_price DECIMAL(10,2) DEFAULT 2.00,
promoter_brokerage_day_max DECIMAL(10,2) DEFAULT -1.00,
store_brokerage_ratio DECIMAL(10,2) DEFAULT 20.00,
store_brokerage_two_ratio DECIMAL(10,2) DEFAULT 2.00,
extract_frozen_time INTEGER DEFAULT 1,
-- 提现设置
user_extract_min_price DECIMAL(10,2) DEFAULT 1.00,
extract_bank_list TEXT DEFAULT '中国银行',
extract_type_list TEXT[] DEFAULT ARRAY['bank', 'wechat', 'alipay'],
wechat_extract_type TEXT DEFAULT '1',
alipay_extract_type TEXT DEFAULT '1',
user_extract_fee DECIMAL(10,2) DEFAULT 0.00,
updated_at TIMESTAMPTZ DEFAULT now(),
updated_by UUID REFERENCES auth.users(id)
);
-- 2. 启用 RLS
ALTER TABLE public.ak_distribution_config ENABLE ROW LEVEL SECURITY;
-- 3. 创建权限策略 (基于公共角色函数或直接查询 ak_users)
-- 允许 Admin 查看配置
CREATE POLICY "Admins can view distribution config"
ON public.ak_distribution_config FOR SELECT
TO authenticated
USING (
EXISTS (
SELECT 1 FROM public.ak_users
WHERE auth_id = auth.uid() AND role = 'admin'
)
);
-- 允许 Admin 修改配置
CREATE POLICY "Admins can update distribution config"
ON public.ak_distribution_config 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_config (id)
VALUES ('global_config')
ON CONFLICT (id) DO NOTHING;
-- =====================================================================================
-- Schema: 分销事业部申请表
-- 位置：docs/sql/10_schema/distribution/ak_distribution_division_applications_v1.sql
-- 说明：记录用户申请加入事业部成为代理商的流水，支持审核流转，按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_distribution_division_applications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
uid UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
division_id UUID NOT NULL REFERENCES public.ak_distribution_divisions(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 申请人填写的代理商名称
phone TEXT NOT NULL, -- 申请人联系电话
images JSONB DEFAULT '[]'::jsonb, -- 申请附件图片 (数组)
status INTEGER DEFAULT 1, -- 状态: 1待审核, 2已同意, 3已拒绝
admin_remark TEXT, -- 审核备注
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 启用 RLS
ALTER TABLE public.ak_distribution_division_applications ENABLE ROW LEVEL SECURITY;
-- 权限策略
CREATE POLICY "Merchants manage their own applications"
ON public.ak_distribution_division_applications FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许用户提交和查看自己的申请
CREATE POLICY "Users handle own applications"
ON public.ak_distribution_division_applications FOR ALL
TO authenticated
USING (uid = auth.uid())
WITH CHECK (uid = auth.uid());
-- 索引
CREATE INDEX IF NOT EXISTS idx_div_app_merchant ON public.ak_distribution_division_applications(merchant_id);
CREATE INDEX IF NOT EXISTS idx_div_app_uid ON public.ak_distribution_division_applications(uid);
CREATE INDEX IF NOT EXISTS idx_div_app_status ON public.ak_distribution_division_applications(status);
-- =====================================================================================
-- Schema: 分销事业部管理表
-- 位置：docs/sql/10_schema/distribution/ak_distribution_divisions_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 依赖：ak_users
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_distribution_divisions (
uid UUID PRIMARY KEY REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
invite_code TEXT UNIQUE NOT NULL,
commission_ratio NUMERIC(5,2) DEFAULT 0 CHECK (commission_ratio >= 0 AND commission_ratio <= 100),
is_enabled BOOLEAN DEFAULT TRUE,
end_time TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
created_by UUID REFERENCES public.ak_users(id),
updated_by UUID REFERENCES public.ak_users(id)
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_distribution_divisions_invite_code ON public.ak_distribution_divisions(invite_code);
-- 注释
COMMENT ON TABLE public.ak_distribution_divisions IS '分销事业部信息表';
COMMENT ON COLUMN public.ak_distribution_divisions.uid IS '用户ID（关联事业部负责人）';
COMMENT ON COLUMN public.ak_distribution_divisions.invite_code IS '事业部专属邀请码';
COMMENT ON COLUMN public.ak_distribution_divisions.commission_ratio IS '事业部固定分佣比例(%)';
COMMENT ON COLUMN public.ak_distribution_divisions.end_time IS '事业部有效截止时间';
-- 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;
-- 推广员关系表：记录下级与上级（邀请人）的绑定关系
CREATE TABLE IF NOT EXISTS public.ak_promoter_relations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
inviter_uid UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
bind_time TIMESTAMPTZ DEFAULT now(),
created_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT chk_ak_promoter_relations_no_self CHECK (uid <> inviter_uid),
CONSTRAINT uq_ak_promoter_relations_uid UNIQUE (uid)
);
CREATE INDEX IF NOT EXISTS idx_ak_promoter_relations_inviter_uid ON public.ak_promoter_relations(inviter_uid);
-- 启用 RLS
ALTER TABLE public.ak_promoter_relations ENABLE ROW LEVEL SECURITY;
-- Admin 可读写
CREATE POLICY "Admins can manage promoter relations"
ON public.ak_promoter_relations
FOR ALL
TO authenticated
USING (
EXISTS (
SELECT 1 FROM public.ak_users
WHERE id = auth.uid() AND role = 'admin'
)
)
WITH CHECK (
EXISTS (
SELECT 1 FROM public.ak_users
WHERE id = auth.uid() AND role = 'admin'
)
);
-- 普通用户可查看与自己相关的关系（可选，便于移动端展示上级/下级）
CREATE POLICY "Users can view their promoter relation"
ON public.ak_promoter_relations
FOR SELECT
TO authenticated
USING (uid = auth.uid() OR inviter_uid = auth.uid());
-- =====================================================================================
-- Schema: 用户提现申请表
-- 位置：docs/sql/10_schema/finance/
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：管理用户发起的提现申请（佣金/余额），支持多种提现方式及快照信息
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ml_extract (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID NOT NULL REFERENCES public.ak_users(id),
real_name TEXT NULL, -- 提现人姓名快照
extract_type TEXT NOT NULL, -- 提现方式: alipay, wechat, bank
-- 账号快照信息
alipay_code TEXT NULL, -- 支付宝账号
wechat_code TEXT NULL, -- 微信账号
bank_code TEXT NULL, -- 银行卡号
bank_address TEXT NULL, -- 开户行地址
extract_price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 申请提现金额
service_fee DECIMAL(12,2) NOT NULL DEFAULT 0, -- 提现手续费
balance DECIMAL(12,2) NOT NULL DEFAULT 0, -- 提现时的余额快照
status SMALLINT NOT NULL DEFAULT 0, -- 状态: 0:待审核, 1:已通过, -1:已驳回
refusal_reason TEXT NULL, -- 驳回原因
admin_id UUID NULL REFERENCES public.ak_users(id), -- 审核人ID
payment_time TIMESTAMPTZ NULL, -- 打款/到账时间
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引
CREATE INDEX IF NOT EXISTS ml_extract_uid_idx ON public.ml_extract (uid);
CREATE INDEX IF NOT EXISTS ml_extract_status_idx ON public.ml_extract (status);
CREATE INDEX IF NOT EXISTS ml_extract_created_at_idx ON public.ml_extract (created_at DESC);
-- =====================================================================================
-- Schema: 发票管理表
-- 位置：docs/sql/10_schema/finance/ml_invoices_v1.sql
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：记录用户提交的开票申请及其处理状态
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ml_invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID NOT NULL REFERENCES public.ak_users(id),
order_no TEXT NOT NULL, -- 关联订单号
order_amount DECIMAL(12,2) NOT NULL, -- 订单金额
invoice_type SMALLINT NOT NULL DEFAULT 1, -- 1: 电子普通发票, 2: 增值税专用发票
header_type SMALLINT NOT NULL DEFAULT 1, -- 1: 个人, 2: 企业
header_name TEXT NOT NULL, -- 发票抬头
tax_id TEXT NULL, -- 企业税号
email TEXT NULL, -- 接收邮箱
remark TEXT NULL, -- 备注
status SMALLINT NOT NULL DEFAULT 0, -- 0: 待开票, 1: 已开票, -1: 已拒绝
refusal_reason TEXT NULL, -- 驳回原因
invoice_url TEXT NULL, -- 电子发票文件路径/URL
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引
CREATE INDEX IF NOT EXISTS ml_invoices_uid_idx ON public.ml_invoices (uid);
CREATE INDEX IF NOT EXISTS ml_invoices_order_no_idx ON public.ml_invoices (order_no);
CREATE INDEX IF NOT EXISTS ml_invoices_status_idx ON public.ml_invoices (status);
-- =====================================================================================
-- Schema: 用户资金流水表
-- 位置：docs/sql/10_schema/finance/
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：记录用户余额、积分、佣金的所有增减流水（原子日志）
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ml_user_bill (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID NOT NULL REFERENCES public.ak_users(id),
link_id TEXT NULL, -- 关联业务ID（订单号、提现ID、充值ID等）
pm SMALLINT NOT NULL DEFAULT 1, -- 0:支出, 1:收入
title TEXT NOT NULL, -- 流水标题（如：商品购买、充值、提现）
category TEXT NOT NULL, -- 业务大类（如：balance-余额, integral-积分, brokerage-佣金）
type TEXT NOT NULL, -- 业务子类型（如：recharge, extract, pay, refund, system_add, system_sub）
number DECIMAL(12,2) NOT NULL DEFAULT 0, -- 变动金额
balance DECIMAL(12,2) NOT NULL DEFAULT 0, -- 变动后的余额快照
mark TEXT NULL, -- 备注
status SMALLINT NOT NULL DEFAULT 1, -- 状态（1:有效, 0:无效/冲正）
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 常用查询索引
CREATE INDEX IF NOT EXISTS ml_user_bill_uid_idx ON public.ml_user_bill (uid);
CREATE INDEX IF NOT EXISTS ml_user_bill_category_type_idx ON public.ml_user_bill (category, type);
CREATE INDEX IF NOT EXISTS ml_user_bill_created_at_idx ON public.ml_user_bill (created_at DESC);
-- =====================================================================================
-- Schema: 用户充值记录表
-- 位置：docs/sql/10_schema/finance/
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：记录用户主动发起的充值申请及支付状态
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ml_user_recharge (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid UUID NOT NULL REFERENCES public.ak_users(id),
order_no TEXT UNIQUE NOT NULL, -- 充值订单号（cz开头）
recharge_type TEXT NOT NULL, -- 充值渠道: wechat, alipay, system (后台补单)
price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 实际充值金额
give_price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 赠送金额
paid SMALLINT NOT NULL DEFAULT 0, -- 支付状态: 0:未支付, 1:已支付
pay_time TIMESTAMPTZ NULL, -- 支付时间
channel_trade_no TEXT NULL, -- 外部渠道流水号
status SMALLINT NOT NULL DEFAULT 1, -- 记录状态: 1:正常, 0:逻辑删除
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引
CREATE INDEX IF NOT EXISTS ml_user_recharge_uid_idx ON public.ml_user_recharge (uid);
CREATE INDEX IF NOT EXISTS ml_user_recharge_order_no_idx ON public.ml_user_recharge (order_no);
CREATE INDEX IF NOT EXISTS ml_user_recharge_created_at_idx ON public.ml_user_recharge (created_at DESC);
-- =====================================================================================
-- Schema: homecare foundation
-- Version: v1
-- Purpose: 为 ec_service_requests / ec_care_tasks / ec_care_records / hc_* 新链补齐最小结构、索引与基础 RLS。
-- Coverage:
-- 1. consumer 下单与自动派单字段
-- 2. consumer 验收 / 退回整改 / 评价字段
-- 3. delivery 执行记录、异常、证据、事件字段
-- =====================================================================================
-- 执行稿见：mall_sql/migrations/20260526_homecare_foundation_v1.sql-- =====================================================================================
-- Schema: 客服模块核心表
-- 位置：docs/sql/10_schema/kefu/ml_kefu_tables_v1.sql
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：包含客服账号、话术、留言及自动回复逻辑
-- =====================================================================================
-- 1. 客服人员表
CREATE TABLE IF NOT EXISTS public.ml_kefu_accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.ak_users(id), -- 关联主用户表
nickname TEXT NOT NULL, -- 客服昵称
avatar TEXT NULL, -- 客服头像
status SMALLINT NOT NULL DEFAULT 1, -- 1:启用, 0:禁用
is_online BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 2. 话术分类表
CREATE TABLE IF NOT EXISTS public.ml_kefu_word_categories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
sort INT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 3. 客服快捷话术表
CREATE TABLE IF NOT EXISTS public.ml_kefu_words (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
category_id UUID NOT NULL REFERENCES public.ml_kefu_word_categories(id) ON DELETE CASCADE,
title TEXT NOT NULL,
content TEXT NOT NULL,
sort INT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 4. 用户留言反馈表
CREATE TABLE IF NOT EXISTS public.ml_kefu_feedbacks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NULL REFERENCES public.ak_users(id), -- 允许匿名留言
nickname TEXT NULL,
phone TEXT NULL,
content TEXT NOT NULL,
status SMALLINT NOT NULL DEFAULT 0, -- 0:未处理, 1:已处理
reply_content TEXT NULL, -- 管理员回复内容
processed_at TIMESTAMPTZ NULL, -- 处理时间
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 5. 关键词自动回复表
CREATE TABLE IF NOT EXISTS public.ml_kefu_auto_replies (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
keyword TEXT NOT NULL,
content TEXT NOT NULL,
reply_type TEXT NOT NULL DEFAULT 'text', -- text, image
status SMALLINT NOT NULL DEFAULT 1, -- 1:开启, 0:关闭
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引
CREATE INDEX IF NOT EXISTS ml_kefu_accounts_user_id_idx ON public.ml_kefu_accounts (user_id);
CREATE INDEX IF NOT EXISTS ml_kefu_words_category_id_idx ON public.ml_kefu_words (category_id);
CREATE INDEX IF NOT EXISTS ml_kefu_feedbacks_status_idx ON public.ml_kefu_feedbacks (status);
CREATE INDEX IF NOT EXISTS ml_kefu_auto_replies_keyword_idx ON public.ml_kefu_auto_replies (keyword);
-- =====================================================================================
-- Schema: 秒杀与拼团活动表
-- 位置：docs/sql/10_schema/marketing/ak_advanced_marketing_v1.sql
-- 说明：管理秒杀活动与拼团活动，按商家隔离。
-- =====================================================================================
-- 1. 秒杀活动表
CREATE TABLE IF NOT EXISTS public.ak_seckill_activities (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
title TEXT NOT NULL, -- 活动标题
single_limit INTEGER DEFAULT 1, -- 单次限购
total_limit INTEGER DEFAULT 10, -- 总购买数量限制
product_count INTEGER DEFAULT 0, -- 包含商品数量
time_range TEXT NOT NULL, -- 活动时段 (如 "06:00-24:00")
start_date TIMESTAMPTZ NOT NULL, -- 开始日期
end_date TIMESTAMPTZ NOT NULL, -- 结束日期
status BOOLEAN DEFAULT true, -- 状态: true开启, false关闭
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 2. 拼团活动表 (开团记录)
CREATE TABLE IF NOT EXISTS public.ak_combination_activities (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
uid UUID NOT NULL REFERENCES public.ak_users(id), -- 开团团长
product_id UUID NOT NULL REFERENCES public.ml_products(id), -- 拼团商品
people INTEGER DEFAULT 2, -- 几人团
count_people INTEGER DEFAULT 1, -- 当前几人参加
start_time TIMESTAMPTZ DEFAULT now(), -- 开团时间
stop_time TIMESTAMPTZ NOT NULL, -- 结束时间
status TEXT NOT NULL DEFAULT 'ongoing', -- ongoing进行中, pending未完成, ended已成功
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT chk_comb_status CHECK (status IN ('ongoing', 'pending', 'ended'))
);
-- 3. 启用 RLS
ALTER TABLE public.ak_seckill_activities ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_combination_activities ENABLE ROW LEVEL SECURITY;
-- 4. 创建权限策略 (按 merchant_id 隔离)
-- 秒杀策略
CREATE POLICY "Merchants can manage their own seckill activities"
ON public.ak_seckill_activities FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 拼团策略
CREATE POLICY "Merchants can manage their own combination activities"
ON public.ak_combination_activities FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看（用于移动端展示）
CREATE POLICY "Anyone can view active marketing activities"
ON public.ak_seckill_activities FOR SELECT
TO authenticated
USING (status = true);
CREATE POLICY "Anyone can view ongoing combinations"
ON public.ak_combination_activities FOR SELECT
TO authenticated
USING (true);
-- =====================================================================================
-- Schema: 砍价与团购活动表
-- 位置：docs/sql/10_schema/marketing/ak_bargain_groupbuy_v1.sql
-- 说明：管理砍价与团购活动，按商家隔离。
-- =====================================================================================
-- 1. 砍价活动表
CREATE TABLE IF NOT EXISTS public.ak_marketing_bargains (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
title TEXT NOT NULL, -- 活动标题
min_price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 砍价最低价
stock INTEGER DEFAULT 0, -- 活动库存
start_time TIMESTAMPTZ NOT NULL, -- 开始时间
stop_time TIMESTAMPTZ NOT NULL, -- 结束时间
status BOOLEAN DEFAULT true, -- 状态: true开启, false关闭
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 2. 团购活动表
CREATE TABLE IF NOT EXISTS public.ak_marketing_groupbuys (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
title TEXT NOT NULL, -- 活动标题
price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 团购价格
people INTEGER DEFAULT 2, -- 成团人数要求
stock INTEGER DEFAULT 0, -- 活动库存
start_time TIMESTAMPTZ NOT NULL, -- 开始时间
stop_time TIMESTAMPTZ NOT NULL, -- 结束时间
status BOOLEAN DEFAULT true, -- 状态: true开启, false关闭
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 3. 启用 RLS
ALTER TABLE public.ak_marketing_bargains ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_marketing_groupbuys ENABLE ROW LEVEL SECURITY;
-- 4. 创建权限策略 (按 merchant_id 隔离)
-- 砍价策略
CREATE POLICY "Merchants can manage their own bargains"
ON public.ak_marketing_bargains FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 团购策略
CREATE POLICY "Merchants can manage their own groupbuys"
ON public.ak_marketing_groupbuys FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看（用于移动端展示）
CREATE POLICY "Anyone can view active marketing activities"
ON public.ak_marketing_bargains FOR SELECT
TO authenticated
USING (status = true);
CREATE POLICY "Anyone can view active groupbuys"
ON public.ak_marketing_groupbuys FOR SELECT
TO authenticated
USING (status = true);
-- 5. 索引
CREATE INDEX IF NOT EXISTS idx_bargains_merchant ON public.ak_marketing_bargains(merchant_id);
CREATE INDEX IF NOT EXISTS idx_groupbuys_merchant ON public.ak_marketing_groupbuys(merchant_id);
-- =====================================================================================
-- Schema: 直播商品管理表
-- 位置：docs/sql/10_schema/marketing/ak_live_products_v1.sql
-- 说明：管理直播活动关联的商品，支持直播价设置与审核状态，按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_marketing_live_products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
live_price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 直播专属价
stock INTEGER DEFAULT 0, -- 直播可用库存
audit_status INTEGER DEFAULT 1, -- 审核状态: 1待审核, 2审核通过, 3审核驳回
is_show BOOLEAN DEFAULT true, -- 是否在直播间显示
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 启用 RLS
ALTER TABLE public.ak_marketing_live_products ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家仅能管理自己的直播商品
CREATE POLICY "Merchants can manage their own live products"
ON public.ak_marketing_live_products FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许查看审核通过的商品
CREATE POLICY "Anyone can view approved live products"
ON public.ak_marketing_live_products FOR SELECT
TO authenticated
USING (audit_status = 2 AND is_show = true);
-- 索引
CREATE INDEX IF NOT EXISTS idx_live_products_merchant ON public.ak_marketing_live_products(merchant_id);
CREATE INDEX IF NOT EXISTS idx_live_products_product ON public.ak_marketing_live_products(product_id);
-- =====================================================================================
-- Schema: 抽奖与直播管理表
-- 位置：docs/sql/10_schema/marketing/ak_lottery_live_v1.sql
-- 说明：管理抽奖活动、奖品、主播及直播间，按商家隔离。
-- =====================================================================================
-- 1. 抽奖活动表
CREATE TABLE IF NOT EXISTS public.ak_marketing_lotteries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 活动名称
type INTEGER DEFAULT 1, -- 活动类型: 1积分抽奖, 2订单评价, 3订单支付
start_time TIMESTAMPTZ NOT NULL, -- 开始时间
end_time TIMESTAMPTZ NOT NULL, -- 结束时间
is_open BOOLEAN DEFAULT true, -- 是否开启
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 2. 抽奖奖品表
CREATE TABLE IF NOT EXISTS public.ak_marketing_lottery_prizes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
lottery_id UUID NOT NULL REFERENCES public.ak_marketing_lotteries(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 奖品名称
prize_type TEXT NOT NULL, -- 奖品类型: points, balance, coupon, physical
amount DECIMAL(12,2) DEFAULT 0, -- 奖励面值/数量
stock INTEGER DEFAULT 0, -- 奖品库存
probability DECIMAL(5,2) DEFAULT 0, -- 中奖概率 (0-100)
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now()
);
-- 3. 直播主播表
CREATE TABLE IF NOT EXISTS public.ak_marketing_live_anchors (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
nickname TEXT NOT NULL, -- 主播昵称
wechat TEXT, -- 微信号
phone TEXT, -- 联系电话
avatar_url TEXT, -- 头像
status BOOLEAN DEFAULT true, -- 状态
created_at TIMESTAMPTZ DEFAULT now()
);
-- 4. 直播间管理表
CREATE TABLE IF NOT EXISTS public.ak_marketing_live_rooms (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
anchor_id UUID REFERENCES public.ak_marketing_live_anchors(id) ON DELETE SET NULL,
name TEXT NOT NULL, -- 直播间名称
background_url TEXT, -- 背景图
share_img_url TEXT, -- 分享图
start_time TIMESTAMPTZ NOT NULL, -- 开始时间
end_time TIMESTAMPTZ NOT NULL, -- 计划结束时间
sort INTEGER DEFAULT 0, -- 排序
type TEXT DEFAULT 'phone', -- 类型: phone手机直播等
like_enabled BOOLEAN DEFAULT true, -- 开启点赞
sale_enabled BOOLEAN DEFAULT true, -- 开启卖货
comment_enabled BOOLEAN DEFAULT true, -- 开启评论
is_show BOOLEAN DEFAULT true, -- 是否显示
live_status INTEGER DEFAULT 1, -- 1未开始, 2直播中, 3暂停, 4已结束
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 5. 启用 RLS
ALTER TABLE public.ak_marketing_lotteries ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_marketing_lottery_prizes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_marketing_live_anchors ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_marketing_live_rooms ENABLE ROW LEVEL SECURITY;
-- 6. 创建权限策略 (按 merchant_id 隔离)
CREATE POLICY "Merchants manage their own lotteries" ON public.ak_marketing_lotteries FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
CREATE POLICY "Merchants manage their own anchors" ON public.ak_marketing_live_anchors FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
CREATE POLICY "Merchants manage their own rooms" ON public.ak_marketing_live_rooms FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
-- 允许查看
CREATE POLICY "Users can view lotteries" ON public.ak_marketing_lotteries FOR SELECT TO authenticated USING (is_open = true);
CREATE POLICY "Users can view active rooms" ON public.ak_marketing_live_rooms FOR SELECT TO authenticated USING (is_show = true);
-- =====================================================================================
-- Schema: 打卡/签到增强配置表
-- 位置：docs/sql/10_schema/marketing/ak_marketing_checkin_configs_v1.sql
-- 说明：管理打卡开关、模式、提醒及基础奖励（积分/经验），按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_marketing_checkin_configs (
id TEXT PRIMARY KEY DEFAULT 'checkin_config',
merchant_id UUID NOT NULL UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE,
is_open BOOLEAN DEFAULT true, -- 签到开关
mode TEXT DEFAULT 'none', -- 签到模式: none(无限制), week(周循环), month(月循环)
notice_enabled BOOLEAN DEFAULT false, -- 签到提醒开关
integral_reward INTEGER DEFAULT 10, -- 每日签到赠送积分
exp_reward INTEGER DEFAULT 1, -- 每日签到赠送经验
updated_at TIMESTAMPTZ DEFAULT now(),
updated_by UUID REFERENCES auth.users(id)
);
-- 启用 RLS
ALTER TABLE public.ak_marketing_checkin_configs ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家仅能管理自己的打卡配置
CREATE POLICY "Merchants manage their own checkin configs"
ON public.ak_marketing_checkin_configs FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看（用于前台展示）
CREATE POLICY "Anyone can view checkin config"
ON public.ak_marketing_checkin_configs FOR SELECT
TO authenticated
USING (true);
-- 插入初始化数据（为每个管理员/商家初始化一条）
-- 实际应在商家创建时触发，此处先预留
-- =====================================================================================
-- Schema: 新人礼配置表
-- 位置：docs/sql/10_schema/marketing/ak_marketing_newcomer_config_v1.sql
-- 说明：管理新用户注册后的奖励（余额、积分、优惠券），按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_marketing_newcomer_config (
id TEXT PRIMARY KEY DEFAULT 'newcomer_config',
merchant_id UUID NOT NULL UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE,
balance_reward DECIMAL(12,2) DEFAULT 0.00, -- 赠送余额
integral_reward INTEGER DEFAULT 0, -- 赠送积分
-- 赠送优惠券 (JSONB 格式): [{ "id": "coupon_uuid", "name": "显示名称", "desc": "发放描述" }]
coupons_json JSONB DEFAULT '[]'::jsonb,
updated_at TIMESTAMPTZ DEFAULT now(),
updated_by UUID REFERENCES auth.users(id)
);
-- 启用 RLS
ALTER TABLE public.ak_marketing_newcomer_config ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家仅能管理自己的新人礼配置
CREATE POLICY "Merchants manage their own newcomer configs"
ON public.ak_marketing_newcomer_config FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许查看配置（用于移动端展示）
CREATE POLICY "Anyone can view newcomer config"
ON public.ak_marketing_newcomer_config FOR SELECT
TO authenticated
USING (true);
-- =====================================================================================
-- Schema: 签到记录表
-- 位置：docs/sql/10_schema/marketing/ak_marketing_signin_logs_v1.sql
-- 说明：记录用户每日签到的详细流水，按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_marketing_signin_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
uid UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
points INTEGER NOT NULL DEFAULT 0, -- 本次签到获得的积分
is_continuous_reward BOOLEAN DEFAULT false, -- 是否包含连续签到额外奖励
created_at TIMESTAMPTZ DEFAULT now()
);
-- 启用 RLS
ALTER TABLE public.ak_marketing_signin_logs ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家仅能管理/查看自己的签到记录
CREATE POLICY "Merchants manage their own signin logs"
ON public.ak_marketing_signin_logs FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许用户查看自己的签到记录
CREATE POLICY "Users view own signin logs"
ON public.ak_marketing_signin_logs FOR SELECT
TO authenticated
USING (uid = auth.uid());
-- 索引
CREATE INDEX IF NOT EXISTS idx_signin_logs_merchant ON public.ak_marketing_signin_logs(merchant_id);
CREATE INDEX IF NOT EXISTS idx_signin_logs_uid ON public.ak_marketing_signin_logs(uid, created_at DESC);
-- =====================================================================================
-- Schema: 付费会员管理相关表
-- 位置：docs/sql/10_schema/marketing/ak_member_management_v1.sql
-- 说明：管理会员卡类型、权益内容及基础配置，按商家隔离。
-- =====================================================================================
-- 1. 会员卡类型表
CREATE TABLE IF NOT EXISTS public.ak_marketing_member_types (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 会员名 (如: 月卡, 年卡)
duration_days INTEGER DEFAULT 30, -- 有效期（天），0表示永久
price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 原价
discount_price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 优惠价/实际支付价
is_open BOOLEAN DEFAULT true, -- 是否开启
sort_order INTEGER DEFAULT 0, -- 排序
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 2. 会员权益表
CREATE TABLE IF NOT EXISTS public.ak_marketing_member_rights (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 权益名称
description TEXT, -- 权益简介
icon_url TEXT, -- 权益图标
is_show BOOLEAN DEFAULT true, -- 是否展示
sort_order INTEGER DEFAULT 0, -- 排序
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 3. 会员基础配置表 (每个商家一条记录)
CREATE TABLE IF NOT EXISTS public.ak_marketing_member_config (
id TEXT PRIMARY KEY DEFAULT 'member_config',
merchant_id UUID NOT NULL UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE,
is_enabled BOOLEAN DEFAULT true, -- 是否开启付费会员功能
bg_img_url TEXT, -- 会员期内背景图
expire_bg_img_url TEXT, -- 会员到期背景图
rules_description TEXT, -- 会员规则说明文本
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 4. 启用 RLS
ALTER TABLE public.ak_marketing_member_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_marketing_member_rights ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_marketing_member_config ENABLE ROW LEVEL SECURITY;
-- 5. 创建权限策略 (按 merchant_id 隔离)
-- 商家管理自己的数据
CREATE POLICY "Merchants manage their own member types" ON public.ak_marketing_member_types FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
CREATE POLICY "Merchants manage their own member rights" ON public.ak_marketing_member_rights FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
CREATE POLICY "Merchants manage their own member config" ON public.ak_marketing_member_config FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看 (移动端展示)
CREATE POLICY "Users can view active member types" ON public.ak_marketing_member_types FOR SELECT TO authenticated USING (is_open = true);
CREATE POLICY "Users can view active member rights" ON public.ak_marketing_member_rights FOR SELECT TO authenticated USING (is_show = true);
CREATE POLICY "Users can view member config" ON public.ak_marketing_member_config FOR SELECT TO authenticated USING (true);
-- 6. 索引
CREATE INDEX IF NOT EXISTS idx_member_types_merchant ON public.ak_marketing_member_types(merchant_id);
CREATE INDEX IF NOT EXISTS idx_member_rights_merchant ON public.ak_marketing_member_rights(merchant_id);
-- =====================================================================================
-- Schema: 充值配置与额度模板表
-- 位置：docs/sql/10_schema/marketing/ak_recharge_management_v1.sql
-- 说明：管理用户充值开关、最低金额及预设额度，按商家隔离。
-- =====================================================================================
-- 1. 充值基础配置表 (每个商家一条记录)
CREATE TABLE IF NOT EXISTS public.ak_recharge_configs (
id TEXT PRIMARY KEY DEFAULT 'recharge_config',
merchant_id UUID NOT NULL UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE,
balance_enabled BOOLEAN DEFAULT true, -- 余额功能是否启用
recharge_notice TEXT, -- 充值注意事项说明
mp_recharge_enabled BOOLEAN DEFAULT false, -- 小程序充值开关
min_recharge_amount DECIMAL(12,2) DEFAULT 0.01, -- 最低充值金额
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 2. 充值额度模板表
CREATE TABLE IF NOT EXISTS public.ak_recharge_quotas (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 售价（实际充值金额）
bonus_price DECIMAL(12,2) NOT NULL DEFAULT 0, -- 赠送金额
is_open BOOLEAN DEFAULT true, -- 是否可用
sort_order INTEGER DEFAULT 0, -- 排序
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 3. 启用 RLS
ALTER TABLE public.ak_recharge_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_recharge_quotas ENABLE ROW LEVEL SECURITY;
-- 4. 创建权限策略 (按 merchant_id 隔离)
CREATE POLICY "Merchants manage their own recharge configs" ON public.ak_recharge_configs FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
CREATE POLICY "Merchants manage their own recharge quotas" ON public.ak_recharge_quotas FOR ALL TO authenticated USING (merchant_id = auth.uid()) WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看
CREATE POLICY "Users can view recharge configs" ON public.ak_recharge_configs FOR SELECT TO authenticated USING (true);
CREATE POLICY "Users can view active recharge quotas" ON public.ak_recharge_quotas FOR SELECT TO authenticated USING (is_open = true);
-- 5. 索引
CREATE INDEX IF NOT EXISTS idx_recharge_quotas_merchant ON public.ak_recharge_quotas(merchant_id);
-- =====================================================================================
-- Schema: 签到规则配置表
-- 位置：docs/sql/10_schema/marketing/ak_signin_configs_v1.sql
-- 说明：记录每日签到积分、连续签到奖励及规则说明，按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_signin_configs (
id TEXT PRIMARY KEY DEFAULT 'signin_config', -- 每个商家一个配置记录
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
is_enabled BOOLEAN DEFAULT true, -- 签到功能是否启用
daily_points INTEGER DEFAULT 10, -- 每日签到固定奖励积分
-- 连续签到奖励 (JSONB 格式): [{ "day": 3, "points": 20 }, { "day": 7, "points": 50 }]
continuous_rewards JSONB DEFAULT '[]'::jsonb,
rules_description TEXT DEFAULT '1.每日签到可获得积分奖励；\n2.连续签到满足天数可获得额外阶梯奖励；\n3.签到中断将重新从第一天开始计算。',
updated_at TIMESTAMPTZ DEFAULT now(),
updated_by UUID REFERENCES auth.users(id),
-- 约束：同一个商家只有一个签到配置记录
UNIQUE(merchant_id)
);
-- 启用 RLS
ALTER TABLE public.ak_signin_configs ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家仅能管理自己的签到配置
CREATE POLICY "Merchants can manage their own signin configs"
ON public.ak_signin_configs
FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看配置（用于移动端签到展示）
CREATE POLICY "Authenticated users can view signin configs"
ON public.ak_signin_configs
FOR SELECT
TO authenticated
USING (true);
-- =====================================================================================
-- Schema Update: ml_orders 字段补齐
-- 位置：docs/sql/10_schema/order/
-- 对象类型：Schema (ALTER TABLE)
-- 版本：v1
-- 说明：为订单主表补齐核销记录 RPC 所依赖的业务字段（order_type, verified_at, verifier_id）
-- =====================================================================================
DO $$
BEGIN
-- 1. 补齐 order_type (1:普通, 2:收银, 3:核销)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_orders' AND column_name = 'order_type') THEN
ALTER TABLE public.ml_orders ADD COLUMN order_type INTEGER DEFAULT 1;
COMMENT ON COLUMN public.ml_orders.order_type IS '订单类型: 1:普通, 2:收银, 3:核销';
END IF;
-- 2. 补齐 verified_at (核销时间)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_orders' AND column_name = 'verified_at') THEN
ALTER TABLE public.ml_orders ADD COLUMN verified_at TIMESTAMP WITH TIME ZONE;
COMMENT ON COLUMN public.ml_orders.verified_at IS '核销时间';
END IF;
-- 3. 补齐 verifier_id (核销员ID)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_orders' AND column_name = 'verifier_id') THEN
ALTER TABLE public.ml_orders ADD COLUMN verifier_id UUID REFERENCES public.ak_users(id);
COMMENT ON COLUMN public.ml_orders.verifier_id IS '核销员ID';
END IF;
-- 4. 补齐 pay_type (支付方式: 1:余额, 2:微信, 3:支付宝, 4:线下支付)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_orders' AND column_name = 'pay_type') THEN
ALTER TABLE public.ml_orders ADD COLUMN pay_type INTEGER DEFAULT 1;
COMMENT ON COLUMN public.ml_orders.pay_type IS '支付方式: 1:余额, 2:微信, 3:支付宝, 4:线下支付';
END IF;
-- 5. 补齐 channel_type (订单渠道: 1:公众号, 2:小程序, 3:H5, 4:PC, 5:APP)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_orders' AND column_name = 'channel_type') THEN
ALTER TABLE public.ml_orders ADD COLUMN channel_type INTEGER DEFAULT 1;
COMMENT ON COLUMN public.ml_orders.channel_type IS '订单渠道: 1:公众号, 2:小程序, 3:H5, 4:PC, 5:APP';
END IF;
END $$;
-- 1. 商品标签分组表
CREATE TABLE IF NOT EXISTS public.ak_product_label_groups (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 2. 商品标签表
CREATE TABLE IF NOT EXISTS public.ak_product_labels (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
group_id UUID REFERENCES public.ak_product_label_groups(id) ON DELETE SET NULL,
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
is_active BOOLEAN DEFAULT true,
show_in_mobile BOOLEAN DEFAULT true,
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 3. 启用 RLS
ALTER TABLE public.ak_product_label_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_product_labels ENABLE ROW LEVEL SECURITY;
-- 4. 创建权限策略 (按 merchant_id 隔离)
-- 分组策略
CREATE POLICY "Users can manage their own label groups"
ON public.ak_product_label_groups FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 标签策略
CREATE POLICY "Users can manage their own labels"
ON public.ak_product_labels FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 5. 索引
CREATE INDEX IF NOT EXISTS idx_label_groups_merchant ON public.ak_product_label_groups(merchant_id);
CREATE INDEX IF NOT EXISTS idx_labels_group ON public.ak_product_labels(group_id);
CREATE INDEX IF NOT EXISTS idx_labels_merchant ON public.ak_product_labels(merchant_id);
-- =====================================================================================
-- Schema: 商品会员价表 (按 SKU + 等级 维度)
-- 位置：docs/sql/10_schema/product/ak_product_member_prices_v1.sql
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：记录特定商品 SKU 在不同会员等级下的专享价格，按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_product_member_prices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
sku_id UUID NOT NULL REFERENCES public.ml_product_skus(id) ON DELETE CASCADE,
level_id UUID NOT NULL REFERENCES public.ak_user_levels(id) ON DELETE CASCADE,
member_price DECIMAL(12,2) NOT NULL CHECK (member_price >= 0),
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
-- 约束：同一个商家的同一个 SKU 在同一个等级下只能有一个会员价
UNIQUE(merchant_id, sku_id, level_id)
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_prod_member_prices_product ON public.ak_product_member_prices(product_id);
CREATE INDEX IF NOT EXISTS idx_prod_member_prices_sku ON public.ak_product_member_prices(sku_id);
CREATE INDEX IF NOT EXISTS idx_prod_member_prices_level ON public.ak_product_member_prices(level_id);
CREATE INDEX IF NOT EXISTS idx_prod_member_prices_merchant ON public.ak_product_member_prices(merchant_id);
-- 启用 RLS
ALTER TABLE public.ak_product_member_prices ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家仅能管理自己的商品会员价
CREATE POLICY "Merchants can manage their own product member prices"
ON public.ak_product_member_prices
FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许所有认证用户查看会员价（前台下单需计算）
CREATE POLICY "Authenticated users can view product member prices"
ON public.ak_product_member_prices
FOR SELECT
TO authenticated
USING (true);
-- 商品保障/服务条款（按 merchant_id 隔离）
CREATE TABLE IF NOT EXISTS public.ak_product_protections (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
icon_url TEXT,
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_ak_product_protections_merchant ON public.ak_product_protections(merchant_id);
CREATE INDEX IF NOT EXISTS idx_ak_product_protections_active ON public.ak_product_protections(is_active);
ALTER TABLE public.ak_product_protections ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own product protections"
ON public.ak_product_protections
FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 商品规格模板表 + 商品参数模板表（按 merchant_id 隔离）
-- 1) 商品规格模板表
CREATE TABLE IF NOT EXISTS public.ak_product_spec_templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
specs TEXT NOT NULL DEFAULT '',
attrs TEXT NOT NULL DEFAULT '',
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_spec_templates_merchant ON public.ak_product_spec_templates(merchant_id);
ALTER TABLE public.ak_product_spec_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own spec templates"
ON public.ak_product_spec_templates
FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 2) 商品参数模板表
CREATE TABLE IF NOT EXISTS public.ak_product_param_templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
params JSONB NOT NULL DEFAULT '[]'::jsonb,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_param_templates_merchant ON public.ak_product_param_templates(merchant_id);
ALTER TABLE public.ak_product_param_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own param templates"
ON public.ak_product_param_templates
FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- =====================================================================================
-- Schema: 运费模板表
-- 位置：docs/sql/10_schema/product/ak_shipping_templates_v1.sql
-- 说明：管理商家的运费计算规则，按商家隔离。
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_shipping_templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
merchant_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 模板名称
calc_method TEXT DEFAULT 'piece', -- 计费方式: piece(件数), weight(重量), volume(体积)
is_free_shipping BOOLEAN DEFAULT false, -- 是否包邮
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 启用 RLS
ALTER TABLE public.ak_shipping_templates ENABLE ROW LEVEL SECURITY;
-- 权限策略：商家管理自己的模板
CREATE POLICY "Merchants manage own shipping templates"
ON public.ak_shipping_templates FOR ALL
TO authenticated
USING (merchant_id = auth.uid())
WITH CHECK (merchant_id = auth.uid());
-- 允许查看
CREATE POLICY "Authenticated users view shipping templates"
ON public.ak_shipping_templates FOR SELECT
TO authenticated
USING (true);
-- 索引
CREATE INDEX IF NOT EXISTS idx_shipping_templates_merchant ON public.ak_shipping_templates(merchant_id);
-- Schema: 医疗商城独立分类与商品分类关联
-- 执行阶段: 10_schema
CREATE TABLE IF NOT EXISTS public.medical_mall_categories (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
parent_id TEXT NULL REFERENCES public.medical_mall_categories(id),
level INTEGER NOT NULL CHECK (level IN (1, 2)),
sort_order INTEGER NOT NULL DEFAULT 0,
icon TEXT,
image_url TEXT,
description TEXT,
scene TEXT NOT NULL DEFAULT 'medical_mall',
category_type TEXT NOT NULL DEFAULT 'normal',
compliance_type TEXT NOT NULL DEFAULT 'normal',
is_active BOOLEAN NOT NULL DEFAULT TRUE,
deleted_at TIMESTAMPTZ NULL,
deleted_by UUID NULL REFERENCES public.ak_users(id),
restored_at TIMESTAMPTZ NULL,
restored_by UUID NULL REFERENCES public.ak_users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE public.medical_mall_categories IS '医疗商城独立分类表';
COMMENT ON COLUMN public.medical_mall_categories.scene IS '分类场景，如 medical_mall / home_mall';
COMMENT ON COLUMN public.medical_mall_categories.category_type IS '分类类型：device / otc / care / rehab / elderly / nutrition / protection / tcm / all';
COMMENT ON COLUMN public.medical_mall_categories.compliance_type IS '合规类型：normal / otc / device_class_i / device_class_ii / device_class_iii / rx_hidden';
CREATE INDEX IF NOT EXISTS idx_medical_mall_categories_parent
ON public.medical_mall_categories(parent_id)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_medical_mall_categories_level_sort
ON public.medical_mall_categories(level, sort_order)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_medical_mall_categories_scene_active
ON public.medical_mall_categories(scene, is_active)
WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS public.medical_mall_product_categories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
category_id TEXT NOT NULL REFERENCES public.medical_mall_categories(id),
is_primary BOOLEAN NOT NULL DEFAULT FALSE,
sort_order INTEGER NOT NULL DEFAULT 0,
deleted_at TIMESTAMPTZ NULL,
deleted_by UUID NULL REFERENCES public.ak_users(id),
restored_at TIMESTAMPTZ NULL,
restored_by UUID NULL REFERENCES public.ak_users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (product_id, category_id)
);
COMMENT ON TABLE public.medical_mall_product_categories IS '医疗商城商品与分类关联表';
CREATE INDEX IF NOT EXISTS idx_medical_mall_product_categories_category
ON public.medical_mall_product_categories(category_id, sort_order)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_medical_mall_product_categories_product
ON public.medical_mall_product_categories(product_id)
WHERE deleted_at IS NULL;-- =====================================================================================
-- Schema Update: ml_products 扩展字段 (物流、营销、高级设置)
-- 位置：docs/sql/10_schema/product/ml_products_ext_v1.sql
-- 说明：补齐商品编辑页 Step 3-6 所需的持久化字段。
-- =====================================================================================
DO $$
BEGIN
-- 1. 物流设置：关联运费模板
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_products' AND column_name = 'shipping_template_id') THEN
ALTER TABLE public.ml_products ADD COLUMN shipping_template_id UUID REFERENCES public.ak_shipping_templates(id) ON DELETE SET NULL;
COMMENT ON COLUMN public.ml_products.shipping_template_id IS '关联运费模板ID';
END IF;
-- 2. 营销设置：赠送积分
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_products' AND column_name = 'give_integral') THEN
ALTER TABLE public.ml_products ADD COLUMN give_integral INTEGER DEFAULT 0;
COMMENT ON COLUMN public.ml_products.give_integral IS '购买赠送积分';
END IF;
-- 3. 高级设置：警戒库存
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_products' AND column_name = 'stock_warning') THEN
ALTER TABLE public.ml_products ADD COLUMN stock_warning INTEGER DEFAULT 10;
COMMENT ON COLUMN public.ml_products.stock_warning IS '库存报警数值';
END IF;
-- 4. 高级设置：虚拟销量
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_products' AND column_name = 'virtual_sales') THEN
ALTER TABLE public.ml_products ADD COLUMN virtual_sales INTEGER DEFAULT 0;
COMMENT ON COLUMN public.ml_products.virtual_sales IS '虚拟销量（展示用）';
END IF;
-- 5. 高级设置：排序
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ml_products' AND column_name = 'sort_order') THEN
ALTER TABLE public.ml_products ADD COLUMN sort_order INTEGER DEFAULT 0;
COMMENT ON COLUMN public.ml_products.sort_order IS '商品排序权重';
END IF;
END $$;
-- =====================================================================================
-- Schema: 权限管理 (RBAC) 核心表
-- 位置：docs/sql/10_schema/user/ak_auth_system_v1.sql
-- 对象类型：TABLE
-- 版本：v1
-- 说明：包含角色表、权限/菜单表及用户角色关联表
-- =====================================================================================
-- 1. 角色表
CREATE TABLE IF NOT EXISTS public.ak_roles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL UNIQUE, -- 角色名称 (如: 超级管理员)
code TEXT NOT NULL UNIQUE, -- 角色编码 (如: super_admin)
description TEXT, -- 角色描述
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 2. 权限/菜单表
CREATE TABLE IF NOT EXISTS public.ak_permissions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
parent_id UUID REFERENCES public.ak_permissions(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 权限/菜单名称
code TEXT NOT NULL UNIQUE, -- 权限编码 (如: order_view)
type TEXT NOT NULL, -- 类型: menu(菜单), button(按钮/接口)
path TEXT, -- 前端路由路径 (仅针对 menu)
icon TEXT, -- 图标
sort_order INTEGER DEFAULT 0, -- 排序
is_visible BOOLEAN DEFAULT TRUE, -- 菜单是否在左侧可见
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 3. 用户-角色关联表
-- 映射管理员 (ak_users) 与角色
CREATE TABLE IF NOT EXISTS public.ak_admin_roles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
role_id UUID NOT NULL REFERENCES public.ak_roles(id) ON DELETE CASCADE,
assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
assigned_by UUID REFERENCES public.ak_users(id),
UNIQUE(user_id, role_id)
);
-- 4. 角色-权限关联表
CREATE TABLE IF NOT EXISTS public.ak_role_permissions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
role_id UUID NOT NULL REFERENCES public.ak_roles(id) ON DELETE CASCADE,
permission_id UUID NOT NULL REFERENCES public.ak_permissions(id) ON DELETE CASCADE,
UNIQUE(role_id, permission_id)
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_permissions_parent_id ON public.ak_permissions(parent_id);
CREATE INDEX IF NOT EXISTS idx_admin_roles_user_id ON public.ak_admin_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON public.ak_role_permissions(role_id);
-- 注释
COMMENT ON TABLE public.ak_roles IS '后台管理角色表';
COMMENT ON TABLE public.ak_permissions IS '功能权限与菜单定义表';
COMMENT ON TABLE public.ak_admin_roles IS '管理员角色分配表';
COMMENT ON TABLE public.ak_role_permissions IS '角色权限映射表';
-- =====================================================================================
-- Schema: 用户分组表
-- 位置：docs/sql/10_schema/user/ak_user_groups_v1.sql
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：用户分组定义，支持逻辑删除和状态管理
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_user_groups (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
remark TEXT NULL,
status INT NOT NULL DEFAULT 1, -- 1:启用, 0:禁用
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ NULL,
CONSTRAINT ak_user_groups_name_length CHECK (char_length(name) >= 1)
);
-- 唯一性约束（仅对未删除记录生效）
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_groups_name_uniq_active
ON public.ak_user_groups (name)
WHERE deleted_at IS NULL;
-- 常用查询索引
CREATE INDEX IF NOT EXISTS ak_user_groups_status_idx ON public.ak_user_groups (status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ak_user_groups_created_at_idx ON public.ak_user_groups (created_at DESC);
-- =====================================================================================
-- Schema: 用户标签表
-- 位置：docs/sql/10_schema/user/ak_user_labels_v1.sql
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：用户标签定义，支持逻辑删除与状态管理
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_user_labels (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
color TEXT NULL,
remark TEXT NULL,
status INT NOT NULL DEFAULT 1,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ NULL,
CONSTRAINT ak_user_labels_name_length CHECK (char_length(name) >= 1)
);
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_labels_name_uniq_active
ON public.ak_user_labels (name)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ak_user_labels_status_idx
ON public.ak_user_labels (status)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ak_user_labels_created_at_idx
ON public.ak_user_labels (created_at DESC);
-- =====================================================================================
-- Schema: 用户等级表
-- 位置：docs/sql/10_schema/user/
-- 对象类型：Schema (DDL)
-- 版本：v1
-- 说明：用户等级（经验值/折扣/展示/状态），支持逻辑删除
-- =====================================================================================
CREATE TABLE IF NOT EXISTS public.ak_user_levels (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
level_weight INT NOT NULL,
min_experience INT NOT NULL DEFAULT 0,
discount_percent INT NOT NULL DEFAULT 100,
is_visible BOOLEAN NOT NULL DEFAULT TRUE,
status INT NOT NULL DEFAULT 1,
icon_url TEXT NULL,
bg_image_url TEXT NULL,
bg_style_json JSONB NULL,
remark TEXT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ NULL,
CONSTRAINT ak_user_levels_level_weight_nonnegative CHECK (level_weight >= 0),
CONSTRAINT ak_user_levels_min_experience_nonnegative CHECK (min_experience >= 0),
CONSTRAINT ak_user_levels_discount_percent_range CHECK (discount_percent BETWEEN 1 AND 100)
);
-- 唯一性（仅对未删除记录生效）
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_levels_name_uniq_active
ON public.ak_user_levels (name)
WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_levels_level_weight_uniq_active
ON public.ak_user_levels (level_weight)
WHERE deleted_at IS NULL;
-- 常用查询索引
CREATE INDEX IF NOT EXISTS ak_user_levels_active_filter_idx
ON public.ak_user_levels (status, is_visible)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ak_user_levels_min_experience_idx
ON public.ak_user_levels (min_experience)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ak_user_levels_level_weight_desc_idx
ON public.ak_user_levels (level_weight DESC)
WHERE deleted_at IS NULL;
-- Schema Update: public.ak_users 增加真实姓名与手机号字段（用于分销/推广员等管理端展示）
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'ak_users' AND column_name = 'real_name'
) THEN
ALTER TABLE public.ak_users ADD COLUMN real_name text;
COMMENT ON COLUMN public.ak_users.real_name IS '真实姓名（可选）';
END IF;
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'ak_users' AND column_name = 'phone'
) THEN
ALTER TABLE public.ak_users ADD COLUMN phone text;
COMMENT ON COLUMN public.ak_users.phone IS '手机号（可选）';
END IF;
END $$;
-- =====================================================================================
-- Schema: public.ak_users
-- Version: v1
-- Purpose: 修复 auth.users -> ak_users 自动同步的写入协调问题
-- Change: 放宽 username 和 email 的 NOT NULL 约束，以允许数据库触发器成功插入新用户记录。
-- 同时，将 role 的默认值更新为 'customer' 以符合业务逻辑。
-- =====================================================================================
BEGIN;
-- 步骤 1 & 2: 允许 username/email 为空，并更新 role 默认值
-- 这样数据库的自动用户同步触发器就不会因为缺少 NOT NULL 的值而失败。
-- 前端代码 (ensureUserProfile) 会在用户首次登录时尝试填充这些值。
ALTER TABLE public.ak_users
ALTER COLUMN username DROP NOT NULL,
ALTER COLUMN email DROP NOT NULL,
ALTER COLUMN role SET DEFAULT 'customer';
COMMIT;
-- =====================================================================================
-- User 模块扩展 - 财务字段补全
-- 位置：docs/sql/10_schema/user/
-- 版本：v1
-- 描述：为 ak_users 增加余额与佣金字段，支持财务业务。
-- =====================================================================================
ALTER TABLE public.ak_users
ADD COLUMN IF NOT EXISTS now_money DECIMAL(12,2) DEFAULT 0,
ADD COLUMN IF NOT EXISTS brokerage_price DECIMAL(12,2) DEFAULT 0;
COMMENT ON COLUMN public.ak_users.now_money IS '用户当前余额';
COMMENT ON COLUMN public.ak_users.brokerage_price IS '用户当前佣金';
Reference in New Issue View Git Blame Copy Permalink