1378 lines
48 KiB
PL/PgSQL
1378 lines
48 KiB
PL/PgSQL
-- =====================================================================================
|
||
-- 电商商城系统完整数据库设<E5BA93>?(PostgreSQL + Supabase)
|
||
-- 表名前缀: ml_ (mall)
|
||
-- 复用<E5A48D>? ak_users (用户主表)
|
||
-- 包含: 表结构、索引、触发器、RLS策略、视图、函<E38081>?
|
||
-- =====================================================================================
|
||
|
||
-- =====================================================================================
|
||
-- 1. 基础配置和扩<E5928C>?
|
||
-- =====================================================================================
|
||
|
||
-- 启用必要的扩<E79A84>?
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
|
||
CREATE EXTENSION IF NOT EXISTS "btree_gin";
|
||
|
||
-- =====================================================================================
|
||
-- 2. 用户扩展<E689A9>?
|
||
-- =====================================================================================
|
||
|
||
-- 商城用户扩展信息<E4BFA1>?
|
||
CREATE TABLE public.ml_user_profiles (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID UNIQUE NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
status INTEGER DEFAULT 1 NOT NULL, -- 1:正常 2:冻结 3:注销 4:待审核
|
||
real_name VARCHAR(100), -- 真实姓名
|
||
id_card VARCHAR(32), -- 身份证号
|
||
business_license VARCHAR(100), -- 营业执照<E689A7>?
|
||
credit_score INTEGER DEFAULT 100, -- 信用分数 0-1000
|
||
verification_status INTEGER DEFAULT 0, -- 认证状<E8AF81>?0:未认<E69CAA>?1:已认<E5B7B2>?2:认证失败
|
||
verification_data JSONB DEFAULT '{}', -- 认证相关数据
|
||
preferences JSONB DEFAULT '{}', -- 用户偏好设置
|
||
emergency_contact VARCHAR(200), -- 紧急联系人
|
||
service_areas JSONB, -- 服务区域(配送员)
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_user_status CHECK (status IN (1,2,3,4)),
|
||
CONSTRAINT chk_ml_verification_status CHECK (verification_status IN (0,1,2)),
|
||
CONSTRAINT chk_ml_credit_score CHECK (credit_score >= 0 AND credit_score <= 1000)
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_user_profiles IS '商城用户扩展信息表';
|
||
COMMENT ON COLUMN public.ml_user_profiles.status IS '用户状态：1正常 2冻结 3注销 4待审核';
|
||
|
||
-- 用户地址<E59CB0>?
|
||
CREATE TABLE public.ml_user_addresses (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
receiver_name VARCHAR(100) NOT NULL,
|
||
receiver_phone VARCHAR(32) NOT NULL,
|
||
province VARCHAR(100) NOT NULL,
|
||
city VARCHAR(100) NOT NULL,
|
||
district VARCHAR(100) NOT NULL,
|
||
street VARCHAR(200),
|
||
address_detail TEXT NOT NULL,
|
||
postal_code VARCHAR(16),
|
||
is_default BOOLEAN DEFAULT FALSE,
|
||
label VARCHAR(50), -- home/office/school/other
|
||
latitude DECIMAL(10,7),
|
||
longitude DECIMAL(10,7),
|
||
delivery_instructions TEXT,
|
||
business_hours VARCHAR(100),
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:禁用
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_address_status CHECK (status IN (1,2))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_user_addresses IS '用户地址<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 3. 商品管理<E7AEA1>?
|
||
-- =====================================================================================
|
||
|
||
-- 商品分类<E58886>?
|
||
CREATE TABLE public.ml_categories (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
cid SERIAL UNIQUE NOT NULL, -- SEO友好的自增ID
|
||
parent_id UUID REFERENCES public.ml_categories(id),
|
||
name VARCHAR(200) NOT NULL,
|
||
slug VARCHAR(200) UNIQUE,
|
||
description TEXT,
|
||
icon_url TEXT,
|
||
banner_url TEXT,
|
||
sort_order INTEGER DEFAULT 0,
|
||
level INTEGER DEFAULT 1,
|
||
path TEXT[], -- 分类路径
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
seo_title VARCHAR(200),
|
||
seo_description VARCHAR(500),
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_categories IS '商品分类<EFBFBD>?;
|
||
|
||
-- 品牌<E59381>?
|
||
CREATE TABLE public.ml_brands (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
cid SERIAL UNIQUE NOT NULL, -- SEO友好的自增ID
|
||
name VARCHAR(200) NOT NULL,
|
||
logo_url TEXT,
|
||
description TEXT,
|
||
website VARCHAR(500),
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_brands IS '品牌<EFBFBD>?;
|
||
|
||
-- 商品<E59586>?
|
||
CREATE TABLE public.ml_products (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
cid SERIAL UNIQUE NOT NULL, -- SEO友好的自增ID
|
||
merchant_id UUID NOT NULL REFERENCES public.ak_users(id),
|
||
category_id UUID NOT NULL REFERENCES public.ml_categories(id),
|
||
brand_id UUID REFERENCES public.ml_brands(id),
|
||
product_code VARCHAR(100) UNIQUE NOT NULL,
|
||
name VARCHAR(500) NOT NULL,
|
||
subtitle VARCHAR(1000),
|
||
description TEXT,
|
||
main_image_url TEXT,
|
||
image_urls JSONB DEFAULT '[]',
|
||
video_urls JSONB DEFAULT '[]',
|
||
|
||
-- 价格信息
|
||
base_price DECIMAL(12,2) NOT NULL CHECK (base_price >= 0),
|
||
market_price DECIMAL(12,2),
|
||
cost_price DECIMAL(12,2),
|
||
|
||
-- 库存信息
|
||
total_stock INTEGER DEFAULT 0 CHECK (total_stock >= 0),
|
||
available_stock INTEGER DEFAULT 0 CHECK (available_stock >= 0),
|
||
min_order_qty INTEGER DEFAULT 1 CHECK (min_order_qty > 0),
|
||
max_order_qty INTEGER,
|
||
|
||
-- 基础属<E7A180>?
|
||
weight DECIMAL(10,3),
|
||
dimensions JSONB, -- {length, width, height}
|
||
|
||
-- 状<>?
|
||
status INTEGER DEFAULT 1, -- 1:上架 2:下架 3:草稿 4:删除
|
||
is_featured BOOLEAN DEFAULT FALSE,
|
||
is_new BOOLEAN DEFAULT FALSE,
|
||
is_hot BOOLEAN DEFAULT FALSE,
|
||
|
||
-- 统计
|
||
view_count INTEGER DEFAULT 0,
|
||
sale_count INTEGER DEFAULT 0,
|
||
favorite_count INTEGER DEFAULT 0,
|
||
rating_avg DECIMAL(3,2) DEFAULT 0.00 CHECK (rating_avg >= 0 AND rating_avg <= 5),
|
||
rating_count INTEGER DEFAULT 0,
|
||
|
||
-- SEO
|
||
seo_title VARCHAR(200),
|
||
seo_description VARCHAR(500),
|
||
seo_keywords TEXT[],
|
||
slug VARCHAR(200) UNIQUE,
|
||
|
||
-- 其他
|
||
tags TEXT[],
|
||
attributes JSONB DEFAULT '{}',
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
published_at TIMESTAMP WITH TIME ZONE,
|
||
|
||
CONSTRAINT chk_ml_product_status CHECK (status IN (1,2,3,4))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_products IS '商品<EFBFBD>?;
|
||
|
||
-- 商品SKU<4B>?
|
||
CREATE TABLE public.ml_product_skus (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
|
||
sku_code VARCHAR(100) UNIQUE NOT NULL,
|
||
specifications JSONB DEFAULT '{}', -- 规格组合
|
||
price DECIMAL(12,2) NOT NULL CHECK (price >= 0),
|
||
market_price DECIMAL(12,2),
|
||
cost_price DECIMAL(12,2),
|
||
stock INTEGER DEFAULT 0 CHECK (stock >= 0),
|
||
warning_stock INTEGER DEFAULT 10, -- 库存预警
|
||
image_url TEXT,
|
||
weight DECIMAL(10,3),
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:禁用
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_sku_status CHECK (status IN (1,2))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_product_skus IS '商品SKU<EFBFBD>?;
|
||
|
||
-- 商品规格<E8A784>?
|
||
CREATE TABLE public.ml_product_specs (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
product_id UUID NOT NULL REFERENCES public.ml_products(id) ON DELETE CASCADE,
|
||
spec_name VARCHAR(100) NOT NULL, -- 规格名称：颜色、尺寸等
|
||
spec_values JSONB NOT NULL DEFAULT '[]', -- 规格值数<E580BC>?
|
||
sort_order INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_product_specs IS '商品规格<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 4. 店铺管理<E7AEA1>?
|
||
-- =====================================================================================
|
||
|
||
-- 店铺信息<E4BFA1>?
|
||
CREATE TABLE public.ml_shops (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
cid SERIAL UNIQUE NOT NULL, -- SEO友好的自增ID
|
||
merchant_id UUID UNIQUE NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
shop_name VARCHAR(200) NOT NULL,
|
||
shop_logo TEXT,
|
||
shop_banner TEXT,
|
||
description TEXT,
|
||
business_license VARCHAR(100),
|
||
contact_name VARCHAR(100),
|
||
contact_phone VARCHAR(32),
|
||
contact_email VARCHAR(200),
|
||
address JSONB, -- 店铺地址信息
|
||
business_hours JSONB, -- 营业时间
|
||
|
||
-- 状<>?
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:暂停 3:关闭
|
||
|
||
-- 统计
|
||
product_count INTEGER DEFAULT 0,
|
||
order_count INTEGER DEFAULT 0,
|
||
rating_avg DECIMAL(3,2) DEFAULT 0.00,
|
||
rating_count INTEGER DEFAULT 0,
|
||
|
||
-- 认证信息
|
||
verified_at TIMESTAMP WITH TIME ZONE,
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_shop_status CHECK (status IN (1,2,3))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_shops IS '店铺信息<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 5. 订单管理<E7AEA1>?
|
||
-- =====================================================================================
|
||
|
||
-- 订单<E8AEA2>?
|
||
CREATE TABLE public.ml_orders (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
cid SERIAL UNIQUE NOT NULL, -- SEO友好的自增ID
|
||
order_no VARCHAR(50) UNIQUE NOT NULL,
|
||
user_id UUID NOT NULL REFERENCES public.ak_users(id),
|
||
merchant_id UUID NOT NULL REFERENCES public.ak_users(id),
|
||
|
||
-- 金额信息
|
||
product_amount DECIMAL(12,2) NOT NULL DEFAULT 0, -- 商品金额
|
||
discount_amount DECIMAL(12,2) DEFAULT 0, -- 优惠金额
|
||
shipping_fee DECIMAL(12,2) DEFAULT 0, -- 运费
|
||
total_amount DECIMAL(12,2) NOT NULL, -- 总金<E680BB>?
|
||
paid_amount DECIMAL(12,2) DEFAULT 0, -- 已付金额
|
||
|
||
-- 地址信息
|
||
shipping_address JSONB NOT NULL, -- 收货地址
|
||
|
||
-- 状态信<E68081>?
|
||
order_status INTEGER DEFAULT 1, -- 1:待付<E5BE85>?2:待发<E5BE85>?3:待收<E5BE85>?4:已完<E5B7B2>?5:已取<E5B7B2>?6:退款中 7:已退<E5B7B2>?
|
||
payment_status INTEGER DEFAULT 1, -- 1:未付<E69CAA>?2:已付<E5B7B2>?3:部分退<E58886>?4:全额退<E9A29D>?
|
||
shipping_status INTEGER DEFAULT 1, -- 1:未发<E69CAA>?2:已发<E5B7B2>?3:运输<E8BF90>?4:已送达
|
||
|
||
-- 时间信息
|
||
paid_at TIMESTAMP WITH TIME ZONE,
|
||
shipped_at TIMESTAMP WITH TIME ZONE,
|
||
delivered_at TIMESTAMP WITH TIME ZONE,
|
||
completed_at TIMESTAMP WITH TIME ZONE,
|
||
|
||
-- 其他信息
|
||
remark TEXT, -- 买家备注
|
||
merchant_memo TEXT, -- 商家备注
|
||
cancel_reason TEXT, -- 取消原因
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_order_status CHECK (order_status IN (1,2,3,4,5,6,7)),
|
||
CONSTRAINT chk_ml_payment_status CHECK (payment_status IN (1,2,3,4)),
|
||
CONSTRAINT chk_ml_shipping_status CHECK (shipping_status IN (1,2,3,4))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_orders IS '订单<EFBFBD>?;
|
||
|
||
-- 订单商品<E59586>?
|
||
CREATE TABLE public.ml_order_items (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
order_id UUID NOT NULL REFERENCES public.ml_orders(id) ON DELETE CASCADE,
|
||
product_id UUID NOT NULL REFERENCES public.ml_products(id),
|
||
sku_id UUID REFERENCES public.ml_product_skus(id),
|
||
product_name VARCHAR(500) NOT NULL,
|
||
sku_name VARCHAR(500),
|
||
specifications JSONB DEFAULT '{}',
|
||
image_url TEXT,
|
||
price DECIMAL(12,2) NOT NULL,
|
||
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
||
total_amount DECIMAL(12,2) NOT NULL,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_order_items IS '订单商品<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 6. 购物车表
|
||
-- =====================================================================================
|
||
|
||
-- 购物车表
|
||
CREATE TABLE public.ml_shopping_cart (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_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 REFERENCES public.ml_product_skus(id) ON DELETE CASCADE,
|
||
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
||
selected BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
UNIQUE(user_id, product_id, sku_id)
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_shopping_cart IS '购物车表';
|
||
|
||
-- =====================================================================================
|
||
-- 7. 营销管理<E7AEA1>?
|
||
-- =====================================================================================
|
||
|
||
-- 优惠券模板表
|
||
CREATE TABLE public.ml_coupon_templates (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
cid SERIAL UNIQUE NOT NULL, -- SEO友好的自增ID
|
||
merchant_id UUID REFERENCES public.ak_users(id), -- NULL表示平台<E5B9B3>?
|
||
name VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
coupon_type INTEGER NOT NULL, -- 1:满减<E6BBA1>?2:折扣<E68A98>?3:免运费券
|
||
discount_type INTEGER NOT NULL, -- 1:固定金额 2:百分<E799BE>?
|
||
discount_value DECIMAL(12,2) NOT NULL, -- 优惠<E4BC98>?
|
||
min_order_amount DECIMAL(12,2) DEFAULT 0, -- 最低订单金<E58D95>?
|
||
max_discount_amount DECIMAL(12,2), -- 最大优惠金<E683A0>?
|
||
total_quantity INTEGER, -- 总发放数<E694BE>?
|
||
per_user_limit INTEGER DEFAULT 1, -- 每用户限领数<E9A286>?
|
||
usage_limit INTEGER DEFAULT 1, -- 每张券使用次数限<E695B0>?
|
||
|
||
-- 适用范围
|
||
applicable_products JSONB DEFAULT '[]', -- 适用商品ID数组
|
||
applicable_categories JSONB DEFAULT '[]', -- 适用分类ID数组
|
||
|
||
-- 时间限制
|
||
start_time TIMESTAMP WITH TIME ZONE NOT NULL,
|
||
end_time TIMESTAMP WITH TIME ZONE NOT NULL,
|
||
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:暂停 3:已结<E5B7B2>?
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_coupon_type CHECK (coupon_type IN (1,2,3)),
|
||
CONSTRAINT chk_ml_discount_type CHECK (discount_type IN (1,2)),
|
||
CONSTRAINT chk_ml_coupon_status CHECK (status IN (1,2,3))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_coupon_templates IS '优惠券模板表';
|
||
|
||
-- 用户优惠券表
|
||
CREATE TABLE public.ml_user_coupons (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
template_id UUID NOT NULL REFERENCES public.ml_coupon_templates(id),
|
||
coupon_code VARCHAR(50) UNIQUE NOT NULL,
|
||
status INTEGER DEFAULT 1, -- 1:未使<E69CAA>?2:已使<E5B7B2>?3:已过<E5B7B2>?
|
||
used_at TIMESTAMP WITH TIME ZONE,
|
||
order_id UUID REFERENCES public.ml_orders(id),
|
||
received_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
expire_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
||
|
||
CONSTRAINT chk_ml_user_coupon_status CHECK (status IN (1,2,3))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_user_coupons IS '用户优惠券表';
|
||
|
||
-- =====================================================================================
|
||
-- 8. 配送管理表
|
||
-- =====================================================================================
|
||
|
||
-- 配送员信息<E4BFA1>?
|
||
CREATE TABLE public.ml_delivery_drivers (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID UNIQUE NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
real_name VARCHAR(100) NOT NULL,
|
||
id_card VARCHAR(32) NOT NULL,
|
||
driver_license VARCHAR(50),
|
||
vehicle_type INTEGER, -- 1:电动<E794B5>?2:摩托<E691A9>?3:汽车
|
||
vehicle_number VARCHAR(20),
|
||
service_areas JSONB DEFAULT '[]', -- 服务区域
|
||
work_status INTEGER DEFAULT 1, -- 1:在线 2:忙碌 3:离线
|
||
current_lat DECIMAL(10,7),
|
||
current_lng DECIMAL(10,7),
|
||
rating_avg DECIMAL(3,2) DEFAULT 0.00,
|
||
rating_count INTEGER DEFAULT 0,
|
||
order_count INTEGER DEFAULT 0,
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:暂停 3:离职
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_driver_vehicle_type CHECK (vehicle_type IN (1,2,3)),
|
||
CONSTRAINT chk_ml_driver_work_status CHECK (work_status IN (1,2,3)),
|
||
CONSTRAINT chk_ml_driver_status CHECK (status IN (1,2,3))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_delivery_drivers IS '配送员信息<EFBFBD>?;
|
||
|
||
-- 配送任务表
|
||
CREATE TABLE public.ml_delivery_tasks (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
order_id UUID UNIQUE NOT NULL REFERENCES public.ml_orders(id),
|
||
driver_id UUID REFERENCES public.ml_delivery_drivers(id),
|
||
pickup_address JSONB NOT NULL, -- 取货地址
|
||
delivery_address JSONB NOT NULL, -- 配送地址
|
||
distance DECIMAL(8,2), -- 配送距<E98081>?km)
|
||
estimated_time INTEGER, -- 预计配送时<E98081>?分钟)
|
||
delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
|
||
status INTEGER DEFAULT 1, -- 1:待接<E5BE85>?2:已接<E5B7B2>?3:取货<E58F96>?4:配送中 5:已送达 6:配送失<E98081>?
|
||
|
||
-- 时间记录
|
||
assigned_at TIMESTAMP WITH TIME ZONE,
|
||
picked_at TIMESTAMP WITH TIME ZONE,
|
||
delivered_at TIMESTAMP WITH TIME ZONE,
|
||
|
||
-- 其他信息
|
||
delivery_code VARCHAR(10), -- 取货<E58F96>?
|
||
remark TEXT,
|
||
failure_reason TEXT,
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_delivery_status CHECK (status IN (1,2,3,4,5,6))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_delivery_tasks IS '配送任务表';
|
||
|
||
-- =====================================================================================
|
||
-- 9. 评价管理<E7AEA1>?
|
||
-- =====================================================================================
|
||
|
||
-- 商品评价<E8AF84>?
|
||
CREATE TABLE public.ml_product_reviews (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
order_id UUID NOT NULL REFERENCES public.ml_orders(id),
|
||
order_item_id UUID NOT NULL REFERENCES public.ml_order_items(id),
|
||
user_id UUID NOT NULL REFERENCES public.ak_users(id),
|
||
product_id UUID NOT NULL REFERENCES public.ml_products(id),
|
||
merchant_id UUID NOT NULL REFERENCES public.ak_users(id),
|
||
|
||
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
||
content TEXT,
|
||
images JSONB DEFAULT '[]', -- 评价图片
|
||
is_anonymous BOOLEAN DEFAULT FALSE,
|
||
|
||
-- 商家回复
|
||
merchant_reply TEXT,
|
||
merchant_replied_at TIMESTAMP WITH TIME ZONE,
|
||
|
||
status INTEGER DEFAULT 1, -- 1:正常 2:已删<E5B7B2>?3:已隐<E5B7B2>?
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT chk_ml_review_status CHECK (status IN (1,2,3))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_product_reviews IS '商品评价<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 10. 用户行为<E8A18C>?
|
||
-- =====================================================================================
|
||
|
||
-- 用户收藏<E694B6>?
|
||
CREATE TABLE public.ml_user_favorites (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
target_type INTEGER NOT NULL, -- 1:商品 2:店铺
|
||
target_id UUID NOT NULL,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
UNIQUE(user_id, target_type, target_id),
|
||
CONSTRAINT chk_ml_favorite_type CHECK (target_type IN (1,2))
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_user_favorites IS '用户收藏<EFBFBD>?;
|
||
|
||
-- 用户浏览历史<E58E86>?
|
||
CREATE TABLE public.ml_browse_history (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_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,
|
||
browse_duration INTEGER DEFAULT 0, -- 浏览时长(<28>?
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
UNIQUE(user_id, product_id)
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_browse_history IS '用户浏览历史<EFBFBD>?;
|
||
|
||
-- 搜索记录<E8AEB0>?
|
||
CREATE TABLE public.ml_search_history (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
keyword VARCHAR(200) NOT NULL,
|
||
result_count INTEGER DEFAULT 0,
|
||
ip_address INET,
|
||
user_agent TEXT,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_search_history IS '搜索记录<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 11. 系统配置<E9858D>?
|
||
-- =====================================================================================
|
||
|
||
-- 系统配置<E9858D>?
|
||
CREATE TABLE public.ml_system_configs (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
config_key VARCHAR(100) UNIQUE NOT NULL,
|
||
config_value JSONB,
|
||
description TEXT,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_system_configs IS '系统配置<EFBFBD>?;
|
||
|
||
-- 地区<E59CB0>?如果需要独立的地区管理)
|
||
CREATE TABLE public.ml_regions (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
parent_id UUID REFERENCES public.ml_regions(id),
|
||
name VARCHAR(100) NOT NULL,
|
||
code VARCHAR(20),
|
||
level INTEGER NOT NULL, -- 1:省份 2:城市 3:区县 4:街道
|
||
sort_order INTEGER DEFAULT 0,
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
COMMENT ON TABLE public.ml_regions IS '地区<EFBFBD>?;
|
||
|
||
-- =====================================================================================
|
||
-- 12. 创建索引
|
||
-- =====================================================================================
|
||
|
||
-- 用户扩展表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_user_profiles_user_id ON public.ml_user_profiles(user_id);
|
||
|
||
CREATE INDEX idx_ml_user_profiles_status ON public.ml_user_profiles(status);
|
||
|
||
-- 分类表索引
|
||
CREATE INDEX idx_ml_categories_cid ON public.ml_categories(cid);
|
||
CREATE INDEX idx_ml_categories_parent ON public.ml_categories(parent_id);
|
||
CREATE INDEX idx_ml_categories_slug ON public.ml_categories(slug);
|
||
CREATE INDEX idx_ml_categories_level ON public.ml_categories(level, sort_order);
|
||
|
||
-- 品牌表索引
|
||
CREATE INDEX idx_ml_brands_cid ON public.ml_brands(cid);
|
||
CREATE INDEX idx_ml_brands_name ON public.ml_brands(name);
|
||
|
||
-- 地址表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_user_addresses_user_id ON public.ml_user_addresses(user_id);
|
||
CREATE INDEX idx_ml_user_addresses_default ON public.ml_user_addresses(user_id, is_default);
|
||
CREATE INDEX idx_ml_user_addresses_location ON public.ml_user_addresses(city, district);
|
||
|
||
-- 商品表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_products_cid ON public.ml_products(cid);
|
||
CREATE INDEX idx_ml_products_merchant ON public.ml_products(merchant_id, status);
|
||
CREATE INDEX idx_ml_products_category ON public.ml_products(category_id, status);
|
||
CREATE INDEX idx_ml_products_status ON public.ml_products(status, created_at DESC);
|
||
CREATE INDEX idx_ml_products_featured ON public.ml_products(is_featured, status);
|
||
CREATE INDEX idx_ml_products_price ON public.ml_products(base_price);
|
||
CREATE INDEX idx_ml_products_rating ON public.ml_products(rating_avg DESC, rating_count DESC);
|
||
CREATE INDEX idx_ml_products_sale_count ON public.ml_products(sale_count DESC);
|
||
CREATE INDEX idx_ml_products_tags ON public.ml_products USING GIN(tags);
|
||
CREATE INDEX idx_ml_products_slug ON public.ml_products(slug);
|
||
|
||
-- 店铺表索引
|
||
CREATE INDEX idx_ml_shops_cid ON public.ml_shops(cid);
|
||
CREATE INDEX idx_ml_shops_merchant ON public.ml_shops(merchant_id);
|
||
|
||
-- SKU表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_product_skus_product ON public.ml_product_skus(product_id);
|
||
CREATE INDEX idx_ml_product_skus_code ON public.ml_product_skus(sku_code);
|
||
|
||
-- 订单表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_orders_cid ON public.ml_orders(cid);
|
||
CREATE INDEX idx_ml_orders_user ON public.ml_orders(user_id, created_at DESC);
|
||
CREATE INDEX idx_ml_orders_merchant ON public.ml_orders(merchant_id, created_at DESC);
|
||
CREATE INDEX idx_ml_orders_status ON public.ml_orders(order_status, created_at DESC);
|
||
CREATE INDEX idx_ml_orders_no ON public.ml_orders(order_no);
|
||
|
||
-- 订单商品表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_order_items_order ON public.ml_order_items(order_id);
|
||
CREATE INDEX idx_ml_order_items_product ON public.ml_order_items(product_id);
|
||
|
||
-- 购物车表索引
|
||
CREATE INDEX idx_ml_shopping_cart_user ON public.ml_shopping_cart(user_id);
|
||
|
||
-- 优惠券模板表索引
|
||
CREATE INDEX idx_ml_coupon_templates_cid ON public.ml_coupon_templates(cid);
|
||
CREATE INDEX idx_ml_coupon_templates_merchant ON public.ml_coupon_templates(merchant_id);
|
||
|
||
-- 优惠券表索引
|
||
CREATE INDEX idx_ml_user_coupons_user ON public.ml_user_coupons(user_id, status);
|
||
CREATE INDEX idx_ml_user_coupons_code ON public.ml_user_coupons(coupon_code);
|
||
|
||
-- 收藏表索<E8A1A8>?
|
||
CREATE INDEX idx_ml_user_favorites_user ON public.ml_user_favorites(user_id, target_type);
|
||
CREATE INDEX idx_ml_user_favorites_target ON public.ml_user_favorites(target_type, target_id);
|
||
|
||
-- 浏览历史索引
|
||
CREATE INDEX idx_ml_browse_history_user ON public.ml_browse_history(user_id, created_at DESC);
|
||
CREATE INDEX idx_ml_browse_history_product ON public.ml_browse_history(product_id);
|
||
|
||
-- =====================================================================================
|
||
-- 13. 触发器函<E599A8>?
|
||
-- =====================================================================================
|
||
|
||
-- 自动更新 updated_at 字段的函<E79A84>?
|
||
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 为需要的表创<E8A1A8>?updated_at 触发<E8A7A6>?
|
||
CREATE TRIGGER trigger_ml_user_profiles_updated_at
|
||
BEFORE UPDATE ON public.ml_user_profiles
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_ml_user_addresses_updated_at
|
||
BEFORE UPDATE ON public.ml_user_addresses
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_ml_products_updated_at
|
||
BEFORE UPDATE ON public.ml_products
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_ml_product_skus_updated_at
|
||
BEFORE UPDATE ON public.ml_product_skus
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_ml_shops_updated_at
|
||
BEFORE UPDATE ON public.ml_shops
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_ml_orders_updated_at
|
||
BEFORE UPDATE ON public.ml_orders
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_ml_shopping_cart_updated_at
|
||
BEFORE UPDATE ON public.ml_shopping_cart
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
||
|
||
-- 确保每个用户只有一个默认地址的触发器
|
||
CREATE OR REPLACE FUNCTION public.ensure_single_default_address()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF NEW.is_default = TRUE THEN
|
||
UPDATE public.ml_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_ml_single_default_address
|
||
BEFORE INSERT OR UPDATE ON public.ml_user_addresses
|
||
FOR EACH ROW EXECUTE FUNCTION public.ensure_single_default_address();
|
||
|
||
-- 商品库存更新触发<E8A7A6>?
|
||
CREATE OR REPLACE FUNCTION public.update_product_stock()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 更新商品总库<E680BB>?
|
||
UPDATE public.ml_products
|
||
SET
|
||
total_stock = (
|
||
SELECT COALESCE(SUM(stock), 0)
|
||
FROM public.ml_product_skus
|
||
WHERE product_id = NEW.product_id AND status = 1
|
||
),
|
||
available_stock = (
|
||
SELECT COALESCE(SUM(stock), 0)
|
||
FROM public.ml_product_skus
|
||
WHERE product_id = NEW.product_id AND status = 1
|
||
)
|
||
WHERE id = NEW.product_id;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER trigger_ml_update_product_stock
|
||
AFTER INSERT OR UPDATE OR DELETE ON public.ml_product_skus
|
||
FOR EACH ROW EXECUTE FUNCTION public.update_product_stock();
|
||
|
||
-- 订单状态变更时的处<E79A84>?
|
||
CREATE OR REPLACE FUNCTION public.handle_order_status_change()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 如果订单状态变为已付款
|
||
IF NEW.order_status = 2 AND OLD.order_status = 1 THEN
|
||
NEW.paid_at = NOW();
|
||
END IF;
|
||
|
||
-- 如果订单状态变为已发货
|
||
IF NEW.order_status = 3 AND OLD.order_status = 2 THEN
|
||
NEW.shipped_at = NOW();
|
||
END IF;
|
||
|
||
-- 如果订单状态变为已完成
|
||
IF NEW.order_status = 4 AND OLD.order_status = 3 THEN
|
||
NEW.delivered_at = NOW();
|
||
NEW.completed_at = NOW();
|
||
|
||
-- 更新商品销<E59381>?
|
||
UPDATE public.ml_products
|
||
SET sale_count = sale_count + (
|
||
SELECT SUM(quantity)
|
||
FROM public.ml_order_items
|
||
WHERE order_id = NEW.id
|
||
)
|
||
WHERE id IN (
|
||
SELECT product_id
|
||
FROM public.ml_order_items
|
||
WHERE order_id = NEW.id
|
||
);
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER trigger_ml_order_status_change
|
||
BEFORE UPDATE ON public.ml_orders
|
||
FOR EACH ROW EXECUTE FUNCTION public.handle_order_status_change();
|
||
|
||
-- =====================================================================================
|
||
-- 14. 实用函数
|
||
-- =====================================================================================
|
||
|
||
-- 生成订单号的函数
|
||
CREATE OR REPLACE FUNCTION public.generate_order_no()
|
||
RETURNS TEXT AS $$
|
||
DECLARE
|
||
order_no TEXT;
|
||
BEGIN
|
||
order_no := 'ML' || TO_CHAR(NOW(), 'YYYYMMDD') || LPAD(NEXTVAL('ml_order_seq')::TEXT, 6, '0');
|
||
RETURN order_no;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建订单序列
|
||
CREATE SEQUENCE IF NOT EXISTS public.ml_order_seq START 1;
|
||
|
||
-- 生成优惠券码的函<E79A84>?
|
||
CREATE OR REPLACE FUNCTION public.generate_coupon_code()
|
||
RETURNS TEXT AS $$
|
||
DECLARE
|
||
code TEXT;
|
||
chars TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
|
||
result TEXT := '';
|
||
i INTEGER;
|
||
BEGIN
|
||
FOR i IN 1..8 LOOP
|
||
result := result || substr(chars, (random() * length(chars))::integer + 1, 1);
|
||
END LOOP;
|
||
RETURN 'CP' || result;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 获取用户默认地址
|
||
CREATE OR REPLACE FUNCTION public.get_user_default_address(p_user_id UUID)
|
||
RETURNS TABLE (
|
||
id UUID,
|
||
receiver_name VARCHAR,
|
||
receiver_phone VARCHAR,
|
||
full_address TEXT,
|
||
latitude DECIMAL,
|
||
longitude DECIMAL
|
||
) 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.latitude,
|
||
a.longitude
|
||
FROM public.ml_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 public.is_verified_merchant(p_user_id UUID)
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
result BOOLEAN := FALSE;
|
||
BEGIN
|
||
SELECT (u.role = 'merchant' AND p.verification_status = 1) INTO result
|
||
FROM public.ml_user_profiles p
|
||
JOIN public.ak_users u ON p.user_id = u.id
|
||
WHERE p.user_id = p_user_id;
|
||
|
||
RETURN COALESCE(result, FALSE);
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 计算购物车总金<E680BB>?
|
||
CREATE OR REPLACE FUNCTION public.calculate_cart_total(p_user_id UUID)
|
||
RETURNS DECIMAL AS $$
|
||
DECLARE
|
||
total_amount DECIMAL := 0;
|
||
BEGIN
|
||
SELECT COALESCE(SUM(s.price * c.quantity), 0) INTO total_amount
|
||
FROM public.ml_shopping_cart c
|
||
LEFT JOIN public.ml_product_skus s ON c.sku_id = s.id
|
||
LEFT JOIN public.ml_products p ON c.product_id = p.id
|
||
WHERE c.user_id = p_user_id
|
||
AND c.selected = TRUE
|
||
AND p.status = 1
|
||
AND (s.id IS NULL OR s.status = 1);
|
||
|
||
RETURN total_amount;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 获取商品可用库存
|
||
CREATE OR REPLACE FUNCTION public.get_product_available_stock(p_product_id UUID, p_sku_id UUID DEFAULT NULL)
|
||
RETURNS INTEGER AS $$
|
||
DECLARE
|
||
stock_count INTEGER := 0;
|
||
BEGIN
|
||
IF p_sku_id IS NOT NULL THEN
|
||
-- 获取特定SKU库存
|
||
SELECT COALESCE(stock, 0) INTO stock_count
|
||
FROM public.ml_product_skus
|
||
WHERE id = p_sku_id AND product_id = p_product_id AND status = 1;
|
||
ELSE
|
||
-- 获取商品总库<E680BB>?
|
||
SELECT COALESCE(available_stock, 0) INTO stock_count
|
||
FROM public.ml_products
|
||
WHERE id = p_product_id AND status = 1;
|
||
END IF;
|
||
|
||
RETURN stock_count;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- =====================================================================================
|
||
-- 15. 创建视图
|
||
-- =====================================================================================
|
||
|
||
-- 商城用户完整信息视图
|
||
CREATE OR REPLACE VIEW public.ml_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,
|
||
u.role,
|
||
p.status,
|
||
p.real_name,
|
||
p.credit_score,
|
||
p.verification_status,
|
||
p.created_at as profile_created_at,
|
||
p.updated_at as profile_updated_at,
|
||
CASE
|
||
WHEN u.role = 'customer' THEN '消费者'
|
||
WHEN u.role = 'merchant' THEN '商家'
|
||
WHEN u.role = 'delivery' THEN '配送员'
|
||
WHEN u.role = 'service' THEN '客服'
|
||
WHEN u.role = 'admin' THEN '管理员'
|
||
ELSE '未知'
|
||
END as role_name
|
||
FROM public.ak_users u
|
||
LEFT JOIN public.ml_user_profiles p ON u.id = p.user_id;
|
||
|
||
COMMENT ON VIEW public.ml_users_view IS '商城用户完整信息视图';
|
||
|
||
-- 商品详情视图
|
||
CREATE OR REPLACE VIEW public.ml_products_detail_view AS
|
||
SELECT
|
||
p.*,
|
||
c.cid as category_cid,
|
||
c.name as category_name,
|
||
c.path as category_path,
|
||
b.cid as brand_cid,
|
||
b.name as brand_name,
|
||
s.cid as shop_cid,
|
||
s.shop_name,
|
||
u.username as merchant_name,
|
||
CASE
|
||
WHEN p.status = 1 THEN '上架'
|
||
WHEN p.status = 2 THEN '下架'
|
||
WHEN p.status = 3 THEN '草稿'
|
||
WHEN p.status = 4 THEN '删除'
|
||
ELSE '未知'
|
||
END as status_name
|
||
FROM public.ml_products p
|
||
LEFT JOIN public.ml_categories c ON p.category_id = c.id
|
||
LEFT JOIN public.ml_brands b ON p.brand_id = b.id
|
||
LEFT JOIN public.ml_shops s ON p.merchant_id = s.merchant_id
|
||
LEFT JOIN public.ak_users u ON p.merchant_id = u.id;
|
||
|
||
COMMENT ON VIEW public.ml_products_detail_view IS '商品详情视图';
|
||
|
||
-- 订单详情视图
|
||
CREATE OR REPLACE VIEW public.ml_orders_detail_view AS
|
||
SELECT
|
||
o.*,
|
||
u.username as customer_name,
|
||
u.phone as customer_phone,
|
||
m.username as merchant_name,
|
||
s.shop_name,
|
||
CASE
|
||
WHEN o.order_status = 1 THEN '待付<EFBFBD>?
|
||
WHEN o.order_status = 2 THEN '待发<EFBFBD>?
|
||
WHEN o.order_status = 3 THEN '待收<EFBFBD>?
|
||
WHEN o.order_status = 4 THEN '已完<EFBFBD>?
|
||
WHEN o.order_status = 5 THEN '已取<EFBFBD>?
|
||
WHEN o.order_status = 6 THEN '退款中'
|
||
WHEN o.order_status = 7 THEN '已退<EFBFBD>?
|
||
ELSE '未知'
|
||
END as order_status_name,
|
||
CASE
|
||
WHEN o.payment_status = 1 THEN '未付<EFBFBD>?
|
||
WHEN o.payment_status = 2 THEN '已付<EFBFBD>?
|
||
WHEN o.payment_status = 3 THEN '部分退<EFBFBD>?
|
||
WHEN o.payment_status = 4 THEN '全额退<EFBFBD>?
|
||
ELSE '未知'
|
||
END as payment_status_name
|
||
FROM public.ml_orders o
|
||
LEFT JOIN public.ak_users u ON o.user_id = u.id
|
||
LEFT JOIN public.ak_users m ON o.merchant_id = m.id
|
||
LEFT JOIN public.ml_shops s ON o.merchant_id = s.merchant_id;
|
||
|
||
COMMENT ON VIEW public.ml_orders_detail_view IS '订单详情视图';
|
||
|
||
-- =====================================================================================
|
||
-- 16. RLS (Row Level Security) 策略
|
||
-- =====================================================================================
|
||
|
||
-- 启用 RLS
|
||
ALTER TABLE public.ml_user_profiles ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_user_addresses ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_shopping_cart ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_user_favorites ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_browse_history ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_user_coupons ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_orders ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_products ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 用户只能访问自己的数<E79A84>?
|
||
CREATE POLICY ml_user_profiles_select_policy ON public.ml_user_profiles
|
||
FOR SELECT USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_profiles_insert_policy ON public.ml_user_profiles
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_profiles_update_policy ON public.ml_user_profiles
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_profiles_delete_policy ON public.ml_user_profiles
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_addresses_select_policy ON public.ml_user_addresses
|
||
FOR SELECT USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_addresses_insert_policy ON public.ml_user_addresses
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_addresses_update_policy ON public.ml_user_addresses
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_addresses_delete_policy ON public.ml_user_addresses
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_shopping_cart_select_policy ON public.ml_shopping_cart
|
||
FOR SELECT USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_shopping_cart_insert_policy ON public.ml_shopping_cart
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_shopping_cart_update_policy ON public.ml_shopping_cart
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_shopping_cart_delete_policy ON public.ml_shopping_cart
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_favorites_select_policy ON public.ml_user_favorites
|
||
FOR SELECT USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_favorites_insert_policy ON public.ml_user_favorites
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_favorites_update_policy ON public.ml_user_favorites
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_favorites_delete_policy ON public.ml_user_favorites
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_browse_history_select_policy ON public.ml_browse_history
|
||
FOR SELECT USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_browse_history_insert_policy ON public.ml_browse_history
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_browse_history_update_policy ON public.ml_browse_history
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_browse_history_delete_policy ON public.ml_browse_history
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_coupons_select_policy ON public.ml_user_coupons
|
||
FOR SELECT USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_coupons_insert_policy ON public.ml_user_coupons
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_coupons_update_policy ON public.ml_user_coupons
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
CREATE POLICY ml_user_coupons_delete_policy ON public.ml_user_coupons
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = user_id)
|
||
);
|
||
|
||
-- 订单策略：用户可以查看自己的订单，商家可以查看自己店铺的订单
|
||
CREATE POLICY ml_orders_select_policy ON public.ml_orders
|
||
FOR SELECT USING (
|
||
auth.uid() IN (
|
||
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
||
)
|
||
);
|
||
|
||
CREATE POLICY ml_orders_insert_policy ON public.ml_orders
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() IN (
|
||
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
||
)
|
||
);
|
||
|
||
CREATE POLICY ml_orders_update_policy ON public.ml_orders
|
||
FOR UPDATE USING (
|
||
auth.uid() IN (
|
||
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
||
)
|
||
);
|
||
|
||
CREATE POLICY ml_orders_delete_policy ON public.ml_orders
|
||
FOR DELETE USING (
|
||
auth.uid() IN (
|
||
SELECT auth_id FROM public.ak_users WHERE id IN (user_id, merchant_id)
|
||
)
|
||
);
|
||
|
||
-- 商品策略：所有人可以查看上架商品，商家只能管理自己的商品
|
||
CREATE POLICY ml_products_select_policy ON public.ml_products
|
||
FOR SELECT USING (status = 1);
|
||
|
||
CREATE POLICY ml_products_insert_policy ON public.ml_products
|
||
FOR INSERT WITH CHECK (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = merchant_id)
|
||
);
|
||
|
||
CREATE POLICY ml_products_update_policy ON public.ml_products
|
||
FOR UPDATE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = merchant_id)
|
||
);
|
||
|
||
CREATE POLICY ml_products_delete_policy ON public.ml_products
|
||
FOR DELETE USING (
|
||
auth.uid() = (SELECT auth_id FROM public.ak_users WHERE id = merchant_id)
|
||
);
|
||
|
||
-- =====================================================================================
|
||
-- 17. 初始化数<E58C96>?
|
||
-- =====================================================================================
|
||
|
||
-- 插入系统配置
|
||
INSERT INTO public.ml_system_configs (config_key, config_value, description) VALUES
|
||
('shipping_fee', '{"default": 10, "free_threshold": 88}', '配送费配置'),
|
||
('platform_commission', '{"rate": 0.05}', '平台佣金配置'),
|
||
('coupon_settings', '{"max_per_user": 10}', '优惠券设<EFBFBD>?),
|
||
('order_auto_confirm_days', '7', '订单自动确认天数');
|
||
|
||
-- 插入默认分类
|
||
INSERT INTO public.ml_categories (id, name, slug, level, path) VALUES
|
||
(uuid_generate_v4(), '数码电器', 'digital', 1, ARRAY['数码电器']),
|
||
(uuid_generate_v4(), '服装鞋帽', 'fashion', 1, ARRAY['服装鞋帽']),
|
||
(uuid_generate_v4(), '家居用品', 'home', 1, ARRAY['家居用品']),
|
||
(uuid_generate_v4(), '食品饮料', 'food', 1, ARRAY['食品饮料']),
|
||
(uuid_generate_v4(), '美妆护肤', 'beauty', 1, ARRAY['美妆护肤']);
|
||
|
||
-- 为现有 ak_users 用户创建默认商城档案
|
||
INSERT INTO public.ml_user_profiles (user_id, status)
|
||
SELECT
|
||
id,
|
||
1 -- 默认状态正常
|
||
FROM public.ak_users
|
||
WHERE id NOT IN (SELECT user_id FROM public.ml_user_profiles WHERE user_id IS NOT NULL);
|
||
|
||
-- =====================================================================================
|
||
-- 18. 完成提示
|
||
-- =====================================================================================
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '商城数据库创建完成！';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '已创建表数量: 20+ 张表';
|
||
RAISE NOTICE '已创建索<EFBFBD>? 30+ 个索<EFBFBD>?;
|
||
RAISE NOTICE '已创建触发器: 8 个触发器';
|
||
RAISE NOTICE '已创建函<EFBFBD>? 10+ 个函<E4B8AA>?;
|
||
RAISE NOTICE '已创建视<EFBFBD>? 3 个视<EFBFBD>?;
|
||
RAISE NOTICE '已设置RLS策略: 多个策略';
|
||
RAISE NOTICE '已为现有用户创建默认档案';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '表名前缀: ml_';
|
||
RAISE NOTICE '复用<EFBFBD>? ak_users';
|
||
RAISE NOTICE '兼容: Supabase';
|
||
RAISE NOTICE '=======================================================';
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- SEO 优化相关函数
|
||
-- =====================================================================================
|
||
|
||
-- 根据 cid 获取商品信息 (SEO 友好)
|
||
CREATE OR REPLACE FUNCTION public.get_product_by_cid(p_cid INTEGER)
|
||
RETURNS TABLE (
|
||
id UUID,
|
||
cid INTEGER,
|
||
name VARCHAR,
|
||
slug VARCHAR,
|
||
description TEXT,
|
||
main_image_url TEXT,
|
||
base_price DECIMAL,
|
||
rating_avg DECIMAL,
|
||
sale_count INTEGER,
|
||
category_name VARCHAR,
|
||
brand_name VARCHAR,
|
||
shop_name VARCHAR
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
p.id,
|
||
p.cid,
|
||
p.name,
|
||
p.slug,
|
||
p.description,
|
||
p.main_image_url,
|
||
p.base_price,
|
||
p.rating_avg,
|
||
p.sale_count,
|
||
c.name as category_name,
|
||
b.name as brand_name,
|
||
s.shop_name
|
||
FROM public.ml_products p
|
||
LEFT JOIN public.ml_categories c ON p.category_id = c.id
|
||
LEFT JOIN public.ml_brands b ON p.brand_id = b.id
|
||
LEFT JOIN public.ml_shops s ON p.merchant_id = s.merchant_id
|
||
WHERE p.cid = p_cid AND p.status = 1;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 根据 cid 获取分类信息 (SEO 友好)
|
||
CREATE OR REPLACE FUNCTION public.get_category_by_cid(p_cid INTEGER)
|
||
RETURNS TABLE (
|
||
id UUID,
|
||
cid INTEGER,
|
||
name VARCHAR,
|
||
slug VARCHAR,
|
||
description TEXT,
|
||
icon_url TEXT,
|
||
path TEXT[]
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
c.id,
|
||
c.cid,
|
||
c.name,
|
||
c.slug,
|
||
c.description,
|
||
c.icon_url,
|
||
c.path
|
||
FROM public.ml_categories c
|
||
WHERE c.cid = p_cid AND c.is_active = TRUE;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 根据 cid 获取品牌信息 (SEO 友好)
|
||
CREATE OR REPLACE FUNCTION public.get_brand_by_cid(p_cid INTEGER)
|
||
RETURNS TABLE (
|
||
id UUID,
|
||
cid INTEGER,
|
||
name VARCHAR,
|
||
logo_url TEXT,
|
||
description TEXT
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
b.id,
|
||
b.cid,
|
||
b.name,
|
||
b.logo_url,
|
||
b.description
|
||
FROM public.ml_brands b
|
||
WHERE b.cid = p_cid AND b.is_active = TRUE;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 根据 cid 获取店铺信息 (SEO 友好)
|
||
CREATE OR REPLACE FUNCTION public.get_shop_by_cid(p_cid INTEGER)
|
||
RETURNS TABLE (
|
||
id UUID,
|
||
cid INTEGER,
|
||
shop_name VARCHAR,
|
||
description TEXT,
|
||
shop_logo TEXT,
|
||
rating_avg DECIMAL,
|
||
product_count INTEGER
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
s.id,
|
||
s.cid,
|
||
s.shop_name,
|
||
s.description,
|
||
s.shop_logo,
|
||
s.rating_avg,
|
||
s.product_count
|
||
FROM public.ml_shops s
|
||
WHERE s.cid = p_cid AND s.status = 1;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 生成 SEO 友好的 URL 路径
|
||
CREATE OR REPLACE FUNCTION public.generate_seo_url(
|
||
p_type VARCHAR, -- 'product', 'category', 'brand', 'shop'
|
||
p_cid INTEGER,
|
||
p_slug VARCHAR DEFAULT NULL
|
||
)
|
||
RETURNS TEXT AS $$
|
||
DECLARE
|
||
url_path TEXT;
|
||
BEGIN
|
||
CASE p_type
|
||
WHEN 'product' THEN
|
||
url_path := '/product/' || p_cid;
|
||
IF p_slug IS NOT NULL THEN
|
||
url_path := url_path || '/' || p_slug;
|
||
END IF;
|
||
WHEN 'category' THEN
|
||
url_path := '/category/' || p_cid;
|
||
IF p_slug IS NOT NULL THEN
|
||
url_path := url_path || '/' || p_slug;
|
||
END IF;
|
||
WHEN 'brand' THEN
|
||
url_path := '/brand/' || p_cid;
|
||
IF p_slug IS NOT NULL THEN
|
||
url_path := url_path || '/' || p_slug;
|
||
END IF;
|
||
WHEN 'shop' THEN
|
||
url_path := '/shop/' || p_cid;
|
||
IF p_slug IS NOT NULL THEN
|
||
url_path := url_path || '/' || p_slug;
|
||
END IF;
|
||
ELSE
|
||
url_path := '/' || p_type || '/' || p_cid;
|
||
END CASE;
|
||
|
||
RETURN url_path;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 批量更新 slug 字段（用于现有数据）
|
||
CREATE OR REPLACE FUNCTION public.update_seo_slugs()
|
||
RETURNS VOID AS $$
|
||
BEGIN
|
||
-- 更新商品 slug
|
||
UPDATE public.ml_products
|
||
SET slug = LOWER(REGEXP_REPLACE(name, '[^a-zA-Z0-9\u4e00-\u9fa5]+', '-', 'g'))
|
||
WHERE slug IS NULL OR slug = '';
|
||
|
||
-- 更新分类 slug
|
||
UPDATE public.ml_categories
|
||
SET slug = LOWER(REGEXP_REPLACE(name, '[^a-zA-Z0-9\u4e00-\u9fa5]+', '-', 'g'))
|
||
WHERE slug IS NULL OR slug = '';
|
||
|
||
RAISE NOTICE 'SEO slugs updated successfully';
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|