274 lines
11 KiB
PL/PgSQL
274 lines
11 KiB
PL/PgSQL
-- 商城系统用户兼容性实施方案
|
||
-- 基于混合方案：复用 ak_users 主表 + 商城扩展表
|
||
|
||
-- 1. 商城用户扩展表
|
||
CREATE TABLE public.mall_user_profiles (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
user_type INTEGER DEFAULT 1, -- 1:消费者 2:商家 3:配送员 4:客服 5:管理员
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:冻结 3:注销 4:待审核
|
||
real_name VARCHAR(64), -- 真实姓名(商家认证、配送员必填)
|
||
id_card VARCHAR(32), -- 身份证号(商家认证、配送员必填)
|
||
credit_score INTEGER DEFAULT 100, -- 信用分数 0-1000
|
||
mall_role VARCHAR(32) DEFAULT 'consumer', -- 商城角色标识
|
||
verification_status INTEGER DEFAULT 0, -- 认证状态 0:未认证 1:已认证 2:认证失败
|
||
verification_data JSONB, -- 认证相关数据
|
||
business_license VARCHAR(128), -- 营业执照号(商家)
|
||
shop_category VARCHAR(64), -- 店铺类别(商家)
|
||
service_areas JSONB, -- 服务区域(配送员)
|
||
emergency_contact VARCHAR(128), -- 紧急联系人(配送员)
|
||
preferences JSONB, -- 用户偏好设置
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE public.mall_user_profiles IS '商城用户扩展信息表';
|
||
COMMENT ON COLUMN public.mall_user_profiles.user_id IS '关联ak_users表的用户ID';
|
||
COMMENT ON COLUMN public.mall_user_profiles.user_type IS '用户类型：1消费者 2商家 3配送员 4客服 5管理员';
|
||
COMMENT ON COLUMN public.mall_user_profiles.status IS '用户状态：1正常 2冻结 3注销 4待审核';
|
||
COMMENT ON COLUMN public.mall_user_profiles.credit_score IS '信用分数，影响交易权限';
|
||
COMMENT ON COLUMN public.mall_user_profiles.verification_status IS '认证状态：0未认证 1已认证 2认证失败';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_mall_user_profiles_user_id ON public.mall_user_profiles(user_id);
|
||
CREATE INDEX idx_mall_user_profiles_user_type ON public.mall_user_profiles(user_type);
|
||
CREATE INDEX idx_mall_user_profiles_status ON public.mall_user_profiles(status);
|
||
CREATE INDEX idx_mall_user_profiles_mall_role ON public.mall_user_profiles(mall_role);
|
||
|
||
-- 2. 用户地址表
|
||
CREATE TABLE public.ak_user_addresses (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
receiver_name VARCHAR(64) NOT NULL, -- 收货人姓名
|
||
receiver_phone VARCHAR(32) NOT NULL, -- 收货人手机
|
||
province VARCHAR(64) NOT NULL, -- 省份
|
||
city VARCHAR(64) NOT NULL, -- 城市
|
||
district VARCHAR(64) NOT NULL, -- 区县
|
||
address_detail TEXT NOT NULL, -- 详细地址
|
||
postal_code VARCHAR(16), -- 邮编
|
||
is_default BOOLEAN DEFAULT false, -- 是否默认地址
|
||
label VARCHAR(32), -- 地址标签：home/office/school/other
|
||
coordinates POINT, -- 经纬度坐标，用于配送距离计算
|
||
delivery_instructions TEXT, -- 配送说明
|
||
business_hours VARCHAR(128), -- 可配送时间(如：9:00-18:00)
|
||
status INTEGER DEFAULT 1, -- 地址状态：1正常 2禁用
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ak_user_addresses IS '用户地址表';
|
||
COMMENT ON COLUMN public.ak_user_addresses.coordinates IS '经纬度坐标，格式：POINT(longitude latitude)';
|
||
COMMENT ON COLUMN public.ak_user_addresses.label IS '地址标签：home家 office公司 school学校 other其他';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_user_addresses_user_id ON public.ak_user_addresses(user_id);
|
||
CREATE INDEX idx_user_addresses_city ON public.ak_user_addresses(city);
|
||
CREATE INDEX idx_user_addresses_district ON public.ak_user_addresses(district);
|
||
CREATE INDEX idx_user_addresses_is_default ON public.ak_user_addresses(is_default);
|
||
|
||
-- 创建地理位置索引(用于附近配送查询)
|
||
CREATE INDEX idx_user_addresses_coordinates ON public.ak_user_addresses USING GIST(coordinates);
|
||
|
||
-- 3. 用户收藏表
|
||
CREATE TABLE public.mall_user_favorites (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
target_type VARCHAR(32) NOT NULL, -- 收藏类型：product/shop
|
||
target_id uuid NOT NULL, -- 目标ID
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE public.mall_user_favorites IS '用户收藏表';
|
||
COMMENT ON COLUMN public.mall_user_favorites.target_type IS '收藏类型：product商品 shop店铺';
|
||
|
||
-- 创建索引和唯一约束
|
||
CREATE INDEX idx_mall_user_favorites_user_id ON public.mall_user_favorites(user_id);
|
||
CREATE INDEX idx_mall_user_favorites_target ON public.mall_user_favorites(target_type, target_id);
|
||
CREATE UNIQUE INDEX idx_mall_user_favorites_unique ON public.mall_user_favorites(user_id, target_type, target_id);
|
||
|
||
-- 4. 用户搜索历史表
|
||
CREATE TABLE public.mall_user_search_history (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
keyword VARCHAR(256) NOT NULL, -- 搜索关键词
|
||
search_count INTEGER DEFAULT 1, -- 搜索次数
|
||
last_search_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE public.mall_user_search_history IS '用户搜索历史表';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_mall_search_history_user_id ON public.mall_user_search_history(user_id);
|
||
CREATE INDEX idx_mall_search_history_keyword ON public.mall_user_search_history(keyword);
|
||
CREATE UNIQUE INDEX idx_mall_search_history_unique ON public.mall_user_search_history(user_id, keyword);
|
||
|
||
-- 5. 用户浏览历史表
|
||
CREATE TABLE public.mall_user_browse_history (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
product_id uuid NOT NULL, -- 浏览的商品ID
|
||
browse_count INTEGER DEFAULT 1, -- 浏览次数
|
||
browse_duration INTEGER DEFAULT 0, -- 浏览时长(秒)
|
||
last_browse_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE public.mall_user_browse_history IS '用户浏览历史表';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_mall_browse_history_user_id ON public.mall_user_browse_history(user_id);
|
||
CREATE INDEX idx_mall_browse_history_product_id ON public.mall_user_browse_history(product_id);
|
||
CREATE INDEX idx_mall_browse_history_last_browse ON public.mall_user_browse_history(last_browse_at);
|
||
CREATE UNIQUE INDEX idx_mall_browse_history_unique ON public.mall_user_browse_history(user_id, product_id);
|
||
|
||
-- 6. 触发器：确保每个用户只有一个默认地址
|
||
CREATE OR REPLACE FUNCTION ensure_single_default_address()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 如果新插入/更新的地址设为默认
|
||
IF NEW.is_default = true THEN
|
||
-- 将该用户的其他地址的默认状态设为false
|
||
UPDATE public.ak_user_addresses
|
||
SET is_default = false
|
||
WHERE user_id = NEW.user_id AND id != NEW.id;
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建触发器
|
||
CREATE TRIGGER trigger_ensure_single_default_address
|
||
BEFORE INSERT OR UPDATE ON public.ak_user_addresses
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION ensure_single_default_address();
|
||
|
||
-- 7. 触发器：自动更新 updated_at 字段
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = now();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 为相关表创建更新时间触发器
|
||
CREATE TRIGGER trigger_mall_user_profiles_updated_at
|
||
BEFORE UPDATE ON public.mall_user_profiles
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_user_addresses_updated_at
|
||
BEFORE UPDATE ON public.ak_user_addresses
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 8. 数据迁移：为现有 ak_users 用户创建默认商城档案
|
||
INSERT INTO public.mall_user_profiles (user_id, user_type, status, mall_role)
|
||
SELECT
|
||
id,
|
||
1, -- 默认为消费者
|
||
1, -- 默认状态正常
|
||
'consumer' -- 默认角色消费者
|
||
FROM public.ak_users
|
||
WHERE id NOT IN (SELECT user_id FROM public.mall_user_profiles WHERE user_id IS NOT NULL);
|
||
|
||
-- 9. 创建视图：商城用户完整信息视图
|
||
CREATE VIEW public.mall_users_view AS
|
||
SELECT
|
||
u.id,
|
||
u.username,
|
||
u.email,
|
||
u.phone,
|
||
u.avatar_url,
|
||
u.gender,
|
||
u.birthday,
|
||
u.bio,
|
||
u.created_at as user_created_at,
|
||
u.updated_at as user_updated_at,
|
||
mp.user_type,
|
||
mp.status,
|
||
mp.real_name,
|
||
mp.credit_score,
|
||
mp.mall_role,
|
||
mp.verification_status,
|
||
mp.created_at as profile_created_at,
|
||
mp.updated_at as profile_updated_at
|
||
FROM public.ak_users u
|
||
INNER JOIN public.mall_user_profiles mp ON u.id = mp.user_id;
|
||
|
||
COMMENT ON VIEW public.mall_users_view IS '商城用户完整信息视图';
|
||
|
||
-- 10. 权限设置(根据实际需要调整)
|
||
-- 创建商城相关的RLS策略
|
||
ALTER TABLE public.mall_user_profiles ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ak_user_addresses ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.mall_user_favorites ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.mall_user_search_history ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.mall_user_browse_history ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 用户只能访问自己的数据
|
||
CREATE POLICY mall_user_profiles_policy ON public.mall_user_profiles
|
||
FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));
|
||
|
||
CREATE POLICY user_addresses_policy ON public.ak_user_addresses
|
||
FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));
|
||
|
||
CREATE POLICY mall_user_favorites_policy ON public.mall_user_favorites
|
||
FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));
|
||
|
||
CREATE POLICY mall_user_search_history_policy ON public.mall_user_search_history
|
||
FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));
|
||
|
||
CREATE POLICY mall_user_browse_history_policy ON public.mall_user_browse_history
|
||
FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));
|
||
|
||
-- 11. 示例查询函数
|
||
-- 获取用户默认地址
|
||
CREATE OR REPLACE FUNCTION get_user_default_address(p_user_id uuid)
|
||
RETURNS TABLE (
|
||
id uuid,
|
||
receiver_name varchar,
|
||
receiver_phone varchar,
|
||
full_address text,
|
||
coordinates point
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
a.id,
|
||
a.receiver_name,
|
||
a.receiver_phone,
|
||
(a.province || a.city || a.district || a.address_detail) as full_address,
|
||
a.coordinates
|
||
FROM public.ak_user_addresses a
|
||
WHERE a.user_id = p_user_id AND a.is_default = true AND a.status = 1
|
||
LIMIT 1;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 检查用户是否为商城认证商家
|
||
CREATE OR REPLACE FUNCTION is_verified_merchant(p_user_id uuid)
|
||
RETURNS boolean AS $$
|
||
DECLARE
|
||
result boolean := false;
|
||
BEGIN
|
||
SELECT (user_type = 2 AND verification_status = 1) INTO result
|
||
FROM public.mall_user_profiles
|
||
WHERE user_id = p_user_id;
|
||
|
||
RETURN COALESCE(result, false);
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 12. 完成提示
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '商城用户兼容性方案部署完成！';
|
||
RAISE NOTICE '已创建表：mall_user_profiles, ak_user_addresses, mall_user_favorites, mall_user_search_history, mall_user_browse_history';
|
||
RAISE NOTICE '已创建视图：mall_users_view';
|
||
RAISE NOTICE '已设置触发器和RLS策略';
|
||
RAISE NOTICE '已为现有用户创建默认商城档案';
|
||
END $$;
|