693 lines
26 KiB
PL/PgSQL
693 lines
26 KiB
PL/PgSQL
-- =====================================================================================
|
|
-- 商城系统增量升级脚本 (ALTER方式)
|
|
-- 用于在现有数据库基础上添加商城功能
|
|
-- 表名前缀: ml_ (mall)
|
|
-- 复用表: ak_users (用户主表)
|
|
-- 兼容: PostgreSQL + Supabase
|
|
-- =====================================================================================
|
|
|
|
-- =====================================================================================
|
|
-- 1. 启用必要的扩展
|
|
-- =====================================================================================
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
|
|
CREATE EXTENSION IF NOT EXISTS "btree_gin";
|
|
|
|
-- =====================================================================================
|
|
-- 2. 检查并创建商城核心表（如果不存在）
|
|
-- =====================================================================================
|
|
|
|
-- 商城用户扩展信息表
|
|
CREATE TABLE IF NOT EXISTS 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,
|
|
user_type INTEGER DEFAULT 1 NOT NULL,
|
|
status INTEGER DEFAULT 1 NOT NULL,
|
|
real_name VARCHAR(100),
|
|
id_card VARCHAR(32),
|
|
business_license VARCHAR(100),
|
|
credit_score INTEGER DEFAULT 100,
|
|
verification_status INTEGER DEFAULT 0,
|
|
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_type CHECK (user_type IN (1,2,3,4,5)),
|
|
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)
|
|
);
|
|
|
|
-- 用户地址表
|
|
CREATE TABLE IF NOT EXISTS 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),
|
|
latitude DECIMAL(10,7),
|
|
longitude DECIMAL(10,7),
|
|
delivery_instructions TEXT,
|
|
business_hours VARCHAR(100),
|
|
status INTEGER DEFAULT 1,
|
|
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))
|
|
);
|
|
|
|
-- 商品分类表
|
|
CREATE TABLE IF NOT EXISTS public.ml_categories (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
cid SERIAL UNIQUE NOT NULL,
|
|
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()
|
|
);
|
|
|
|
-- 品牌表
|
|
CREATE TABLE IF NOT EXISTS public.ml_brands (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
cid SERIAL UNIQUE NOT NULL,
|
|
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()
|
|
);
|
|
|
|
-- 商品表
|
|
CREATE TABLE IF NOT EXISTS public.ml_products (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
cid SERIAL UNIQUE NOT NULL,
|
|
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,
|
|
weight DECIMAL(10,3),
|
|
dimensions JSONB,
|
|
status INTEGER DEFAULT 1,
|
|
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_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))
|
|
);
|
|
|
|
-- 商品SKU表
|
|
CREATE TABLE IF NOT EXISTS 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,
|
|
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))
|
|
);
|
|
|
|
-- 店铺信息表
|
|
CREATE TABLE IF NOT EXISTS public.ml_shops (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
cid SERIAL UNIQUE NOT NULL,
|
|
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,
|
|
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))
|
|
);
|
|
|
|
-- 订单表
|
|
CREATE TABLE IF NOT EXISTS public.ml_orders (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
cid SERIAL UNIQUE NOT NULL,
|
|
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,
|
|
paid_amount DECIMAL(12,2) DEFAULT 0,
|
|
shipping_address JSONB NOT NULL,
|
|
order_status INTEGER DEFAULT 1,
|
|
payment_status INTEGER DEFAULT 1,
|
|
shipping_status INTEGER DEFAULT 1,
|
|
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))
|
|
);
|
|
|
|
-- 购物车表
|
|
CREATE TABLE IF NOT EXISTS 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)
|
|
);
|
|
|
|
-- =====================================================================================
|
|
-- 3. ALTER 语句：为现有表添加商城相关字段
|
|
-- =====================================================================================
|
|
|
|
-- 为 ak_users 表添加商城相关字段（如果不存在）
|
|
DO $$
|
|
BEGIN
|
|
-- 添加商城相关字段
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = 'mall_status') THEN
|
|
ALTER TABLE public.ak_users ADD COLUMN mall_status INTEGER DEFAULT 1; -- 1:正常 2:禁用
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = 'mall_type') THEN
|
|
ALTER TABLE public.ak_users ADD COLUMN mall_type INTEGER DEFAULT 1; -- 1:消费者 2:商家
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = 'last_login_ip') THEN
|
|
ALTER TABLE public.ak_users ADD COLUMN last_login_ip INET;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = 'total_orders') THEN
|
|
ALTER TABLE public.ak_users ADD COLUMN total_orders INTEGER DEFAULT 0;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = 'total_spent') THEN
|
|
ALTER TABLE public.ak_users ADD COLUMN total_spent DECIMAL(12,2) DEFAULT 0.00;
|
|
END IF;
|
|
|
|
RAISE NOTICE 'ak_users 表字段添加完成';
|
|
END $$;
|
|
|
|
-- =====================================================================================
|
|
-- 4. 创建索引
|
|
-- =====================================================================================
|
|
|
|
-- 用户扩展表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_user_profiles_user_id ON public.ml_user_profiles(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_user_profiles_type ON public.ml_user_profiles(user_type);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_user_profiles_status ON public.ml_user_profiles(status);
|
|
|
|
-- 分类表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_categories_cid ON public.ml_categories(cid);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_categories_parent ON public.ml_categories(parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_categories_slug ON public.ml_categories(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_categories_level ON public.ml_categories(level, sort_order);
|
|
|
|
-- 品牌表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_brands_cid ON public.ml_brands(cid);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_brands_name ON public.ml_brands(name);
|
|
|
|
-- 地址表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_user_addresses_user_id ON public.ml_user_addresses(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_user_addresses_default ON public.ml_user_addresses(user_id, is_default);
|
|
|
|
-- 商品表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_cid ON public.ml_products(cid);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_merchant ON public.ml_products(merchant_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_category ON public.ml_products(category_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_status ON public.ml_products(status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_featured ON public.ml_products(is_featured, status);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_price ON public.ml_products(base_price);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_rating ON public.ml_products(rating_avg DESC, rating_count DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_tags ON public.ml_products USING GIN(tags);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_products_slug ON public.ml_products(slug);
|
|
|
|
-- 店铺表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_shops_cid ON public.ml_shops(cid);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_shops_merchant ON public.ml_shops(merchant_id);
|
|
|
|
-- SKU表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_product_skus_product ON public.ml_product_skus(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_product_skus_code ON public.ml_product_skus(sku_code);
|
|
|
|
-- 订单表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_orders_cid ON public.ml_orders(cid);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_orders_user ON public.ml_orders(user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_orders_merchant ON public.ml_orders(merchant_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_orders_status ON public.ml_orders(order_status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ml_orders_no ON public.ml_orders(order_no);
|
|
|
|
-- 购物车表索引
|
|
CREATE INDEX IF NOT EXISTS idx_ml_shopping_cart_user ON public.ml_shopping_cart(user_id);
|
|
|
|
-- ak_users 表新增字段索引
|
|
CREATE INDEX IF NOT EXISTS idx_ak_users_mall_status ON public.ak_users(mall_status);
|
|
CREATE INDEX IF NOT EXISTS idx_ak_users_mall_type ON public.ak_users(mall_type);
|
|
CREATE INDEX IF NOT EXISTS idx_ak_users_total_orders ON public.ak_users(total_orders DESC);
|
|
|
|
-- =====================================================================================
|
|
-- 5. 创建序列（如果不存在）
|
|
-- =====================================================================================
|
|
|
|
CREATE SEQUENCE IF NOT EXISTS public.ml_order_seq START 1;
|
|
|
|
-- =====================================================================================
|
|
-- 6. 创建或替换触发器函数
|
|
-- =====================================================================================
|
|
|
|
-- 自动更新 updated_at 字段的函数
|
|
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 确保每个用户只有一个默认地址的触发器函数
|
|
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 OR REPLACE FUNCTION public.update_product_stock()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- 更新商品总库存
|
|
UPDATE public.ml_products
|
|
SET
|
|
total_stock = (
|
|
SELECT COALESCE(SUM(stock), 0)
|
|
FROM public.ml_product_skus
|
|
WHERE product_id = COALESCE(NEW.product_id, OLD.product_id) AND status = 1
|
|
),
|
|
available_stock = (
|
|
SELECT COALESCE(SUM(stock), 0)
|
|
FROM public.ml_product_skus
|
|
WHERE product_id = COALESCE(NEW.product_id, OLD.product_id) AND status = 1
|
|
)
|
|
WHERE id = COALESCE(NEW.product_id, OLD.product_id);
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 订单状态变更处理函数
|
|
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();
|
|
|
|
-- 更新用户统计数据
|
|
UPDATE public.ak_users
|
|
SET
|
|
total_orders = total_orders + 1,
|
|
total_spent = total_spent + NEW.total_amount
|
|
WHERE id = NEW.user_id;
|
|
|
|
-- 更新商品销量
|
|
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;
|
|
|
|
-- =====================================================================================
|
|
-- 7. 创建触发器
|
|
-- =====================================================================================
|
|
|
|
-- 删除可能存在的同名触发器，然后重新创建
|
|
DROP TRIGGER IF EXISTS trigger_ml_user_profiles_updated_at ON public.ml_user_profiles;
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_user_addresses_updated_at ON public.ml_user_addresses;
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_products_updated_at ON public.ml_products;
|
|
CREATE TRIGGER trigger_ml_products_updated_at
|
|
BEFORE UPDATE ON public.ml_products
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_product_skus_updated_at ON public.ml_product_skus;
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_shops_updated_at ON public.ml_shops;
|
|
CREATE TRIGGER trigger_ml_shops_updated_at
|
|
BEFORE UPDATE ON public.ml_shops
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_orders_updated_at ON public.ml_orders;
|
|
CREATE TRIGGER trigger_ml_orders_updated_at
|
|
BEFORE UPDATE ON public.ml_orders
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_shopping_cart_updated_at ON public.ml_shopping_cart;
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_single_default_address ON public.ml_user_addresses;
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_update_product_stock ON public.ml_product_skus;
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ml_order_status_change ON public.ml_orders;
|
|
CREATE TRIGGER trigger_ml_order_status_change
|
|
BEFORE UPDATE ON public.ml_orders
|
|
FOR EACH ROW EXECUTE FUNCTION public.handle_order_status_change();
|
|
|
|
-- =====================================================================================
|
|
-- 8. 创建实用函数
|
|
-- =====================================================================================
|
|
|
|
-- 生成订单号的函数
|
|
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 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.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.ml_user_profiles
|
|
WHERE user_id = p_user_id;
|
|
|
|
RETURN COALESCE(result, FALSE);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 计算购物车总金额
|
|
CREATE OR REPLACE FUNCTION public.calculate_cart_total(p_user_id UUID)
|
|
RETURNS DECIMAL AS $$
|
|
DECLARE
|
|
total_amount DECIMAL := 0;
|
|
BEGIN
|
|
SELECT COALESCE(SUM(
|
|
CASE
|
|
WHEN c.sku_id IS NOT NULL THEN s.price * c.quantity
|
|
ELSE p.base_price * c.quantity
|
|
END
|
|
), 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;
|
|
|
|
-- 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
|
|
) 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
|
|
FROM public.ml_products p
|
|
WHERE p.cid = p_cid AND p.status = 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- =====================================================================================
|
|
-- 9. 创建视图
|
|
-- =====================================================================================
|
|
|
|
-- 商城用户完整信息视图
|
|
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.mall_status,
|
|
u.mall_type,
|
|
u.total_orders,
|
|
u.total_spent,
|
|
p.user_type,
|
|
p.status,
|
|
p.real_name,
|
|
p.credit_score,
|
|
p.verification_status,
|
|
CASE
|
|
WHEN p.user_type = 1 THEN '消费者'
|
|
WHEN p.user_type = 2 THEN '商家'
|
|
WHEN p.user_type = 3 THEN '配送员'
|
|
WHEN p.user_type = 4 THEN '客服'
|
|
WHEN p.user_type = 5 THEN '管理员'
|
|
ELSE '未知'
|
|
END as user_type_name
|
|
FROM public.ak_users u
|
|
LEFT JOIN public.ml_user_profiles p ON u.id = p.user_id;
|
|
|
|
-- 商品详情视图
|
|
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;
|
|
|
|
-- =====================================================================================
|
|
-- 10. 初始化基础数据
|
|
-- =====================================================================================
|
|
|
|
-- 插入默认分类（如果不存在）
|
|
INSERT INTO public.ml_categories (id, name, slug, level, path)
|
|
SELECT * FROM (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['美妆护肤'])
|
|
) AS v(id, name, slug, level, path)
|
|
WHERE NOT EXISTS (SELECT 1 FROM public.ml_categories WHERE slug = v.slug);
|
|
|
|
-- 为现有 ak_users 用户创建默认商城档案（如果不存在）
|
|
INSERT INTO public.ml_user_profiles (user_id, user_type, status)
|
|
SELECT
|
|
id,
|
|
1, -- 默认为消费者
|
|
1 -- 默认状态正常
|
|
FROM public.ak_users
|
|
WHERE id NOT IN (SELECT user_id FROM public.ml_user_profiles WHERE user_id IS NOT NULL);
|
|
|
|
-- =====================================================================================
|
|
-- 11. 完成提示
|
|
-- =====================================================================================
|
|
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE '=======================================================';
|
|
RAISE NOTICE '商城系统增量升级完成！';
|
|
RAISE NOTICE '=======================================================';
|
|
RAISE NOTICE '✓ 扩展创建完成';
|
|
RAISE NOTICE '✓ 商城表结构创建/检查完成';
|
|
RAISE NOTICE '✓ ak_users 表字段添加完成';
|
|
RAISE NOTICE '✓ 索引创建完成';
|
|
RAISE NOTICE '✓ 触发器创建完成';
|
|
RAISE NOTICE '✓ 实用函数创建完成';
|
|
RAISE NOTICE '✓ 视图创建完成';
|
|
RAISE NOTICE '✓ 基础数据初始化完成';
|
|
RAISE NOTICE '=======================================================';
|
|
RAISE NOTICE '使用说明：';
|
|
RAISE NOTICE '1. 此脚本安全执行，不会覆盖现有数据';
|
|
RAISE NOTICE '2. 使用 IF NOT EXISTS 和 IF EXISTS 检查避免重复';
|
|
RAISE NOTICE '3. 为现有用户自动创建商城档案';
|
|
RAISE NOTICE '4. 所有新表前缀: ml_';
|
|
RAISE NOTICE '5. 复用表: ak_users';
|
|
RAISE NOTICE '=======================================================';
|
|
END $$;
|