-- =====================================================================================
-- 商城系统增量升级脚本 (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 $$;