-- ===================================================================
-- 基于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. 推荐使用独立的商品表设计以获得更好的架构
';