111 lines
4.6 KiB
SQL
111 lines
4.6 KiB
SQL
-- ===================================================================
|
||
-- 基于ak_contents的商品扩展方案（不推荐但可行）
|
||
-- ===================================================================
|
||
|
||
-- 1. 为ak_contents添加商品相关字段
|
||
DO $$
|
||
BEGIN
|
||
-- 添加内容类型字段区分资讯和商品
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='ak_contents' AND column_name='content_type') THEN
|
||
ALTER TABLE public.ak_contents ADD COLUMN content_type VARCHAR(50) DEFAULT 'article';
|
||
COMMENT ON COLUMN public.ak_contents.content_type IS '内容类型：article=资讯，product=商品';
|
||
END IF;
|
||
|
||
-- 添加商品特有字段
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='ak_contents' AND column_name='product_code') THEN
|
||
ALTER TABLE public.ak_contents ADD COLUMN product_code VARCHAR(50);
|
||
COMMENT ON COLUMN public.ak_contents.product_code IS '商品编码（仅商品类型使用）';
|
||
END IF;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='ak_contents' AND column_name='base_price') THEN
|
||
ALTER TABLE public.ak_contents ADD COLUMN base_price DECIMAL(12,2);
|
||
COMMENT ON COLUMN public.ak_contents.base_price IS '商品基础价格（仅商品类型使用）';
|
||
END IF;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='ak_contents' AND column_name='stock_quantity') THEN
|
||
ALTER TABLE public.ak_contents ADD COLUMN stock_quantity INTEGER DEFAULT 0;
|
||
COMMENT ON COLUMN public.ak_contents.stock_quantity IS '库存数量（仅商品类型使用）';
|
||
END IF;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='ak_contents' AND column_name='merchant_id') THEN
|
||
ALTER TABLE public.ak_contents ADD COLUMN merchant_id UUID;
|
||
COMMENT ON COLUMN public.ak_contents.merchant_id IS '商家ID（仅商品类型使用）';
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
-- 2. 创建商品特有的附加表
|
||
|
||
-- 商品SKU表
|
||
CREATE TABLE IF NOT EXISTS public.ak_product_skus (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
content_id UUID NOT NULL REFERENCES public.ak_contents(id) ON DELETE CASCADE,
|
||
sku_code VARCHAR(100) UNIQUE NOT NULL,
|
||
specification_values JSONB NOT NULL DEFAULT '{}',
|
||
price DECIMAL(12,2) NOT NULL,
|
||
stock_quantity INTEGER DEFAULT 0,
|
||
is_default BOOLEAN DEFAULT false,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 商品规格表
|
||
CREATE TABLE IF NOT EXISTS public.ak_product_specifications (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
content_id UUID NOT NULL REFERENCES public.ak_contents(id) ON DELETE CASCADE,
|
||
spec_name VARCHAR(100) NOT NULL,
|
||
spec_values TEXT[] NOT NULL,
|
||
is_required BOOLEAN DEFAULT false,
|
||
sort_order INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 商品属性表
|
||
CREATE TABLE IF NOT EXISTS public.ak_product_attributes (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
content_id UUID NOT NULL REFERENCES public.ak_contents(id) ON DELETE CASCADE,
|
||
attribute_name VARCHAR(200) NOT NULL,
|
||
attribute_value TEXT NOT NULL,
|
||
attribute_group VARCHAR(100),
|
||
sort_order INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 3. 创建专门的商品视图
|
||
CREATE OR REPLACE VIEW public.vw_products AS
|
||
SELECT
|
||
id,
|
||
title as product_name,
|
||
content as product_description,
|
||
product_code,
|
||
base_price,
|
||
stock_quantity,
|
||
merchant_id,
|
||
category_id,
|
||
status,
|
||
tags,
|
||
published_at as listed_at,
|
||
created_at,
|
||
updated_at,
|
||
-- 排除资讯相关字段
|
||
NULL as ai_analysis_result,
|
||
NULL as translation_status
|
||
FROM public.ak_contents
|
||
WHERE content_type = 'product';
|
||
|
||
-- 4. 添加约束和索引
|
||
ALTER TABLE public.ak_contents ADD CONSTRAINT chk_product_code_unique
|
||
CHECK (content_type != 'product' OR product_code IS NOT NULL);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ak_contents_product_type ON public.ak_contents(content_type) WHERE content_type = 'product';
|
||
CREATE INDEX IF NOT EXISTS idx_ak_contents_product_code ON public.ak_contents(product_code) WHERE content_type = 'product';
|
||
CREATE INDEX IF NOT EXISTS idx_ak_contents_merchant ON public.ak_contents(merchant_id) WHERE content_type = 'product';
|
||
|
||
-- 注意事项和限制
|
||
COMMENT ON VIEW public.vw_products IS '
|
||
使用ak_contents作为商品表的注意事项：
|
||
1. 必须设置content_type=product来区分商品和资讯
|
||
2. 许多ak_contents的字段对商品无意义（如AI翻译相关）
|
||
3. 查询性能可能受影响，建议添加分区
|
||
4. 推荐使用独立的商品表设计以获得更好的架构
|
||
';
|