1181 lines
48 KiB
PL/PgSQL
1181 lines
48 KiB
PL/PgSQL
-- =====================================================================================
|
||
-- 商城系统模拟数据插入脚本
|
||
-- 依赖: complete_mall_database.sql (必须先执行主数据库脚本)
|
||
-- 重要: 需要先在 Supabase Auth 中创建测试用户，建议使用 create_supabase_auth_users.js
|
||
-- 用途: 为商城系统生成测试数据，便于开发和测试
|
||
-- =====================================================================================
|
||
|
||
-- =====================================================================================
|
||
-- 0. 检查 Supabase Auth 用户环境
|
||
-- =====================================================================================
|
||
|
||
-- 检查是否为 Supabase 环境
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'auth') THEN
|
||
RAISE WARNING '当前环境不是 Supabase，将使用虚拟 auth_id';
|
||
RAISE WARNING '如果是 Supabase 环境，请先使用以下方式创建测试用户：';
|
||
RAISE WARNING '1. 执行 create_supabase_auth_users.js 脚本';
|
||
RAISE WARNING '2. 或在 Supabase Dashboard 中手动创建用户';
|
||
ELSE
|
||
RAISE NOTICE '检测到 Supabase 环境，将尝试关联真实 auth 用户';
|
||
|
||
-- 检查是否有测试用户
|
||
IF EXISTS (SELECT 1 FROM auth.users WHERE email LIKE '%@mall.com') THEN
|
||
RAISE NOTICE '发现测试用户，将使用真实 auth_id';
|
||
ELSE
|
||
RAISE WARNING '未发现测试用户，建议先执行 create_supabase_auth_users.js';
|
||
RAISE WARNING '或手动在 Supabase Dashboard 创建以下测试用户：';
|
||
RAISE WARNING '- admin@mall.com';
|
||
RAISE WARNING '- merchant1@mall.com';
|
||
RAISE WARNING '- merchant2@mall.com';
|
||
RAISE WARNING '- customer1@mall.com';
|
||
RAISE WARNING '- customer2@mall.com';
|
||
RAISE WARNING '- customer3@mall.com';
|
||
RAISE WARNING '- driver1@mall.com';
|
||
RAISE WARNING '- driver2@mall.com';
|
||
END IF;
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 1. 清理现有测试数据 (可选，谨慎使用)
|
||
-- =====================================================================================
|
||
|
||
-- TRUNCATE TABLE public.ml_product_reviews CASCADE;
|
||
-- TRUNCATE TABLE public.ml_order_items CASCADE;
|
||
-- TRUNCATE TABLE public.ml_orders CASCADE;
|
||
-- TRUNCATE TABLE public.ml_shopping_cart CASCADE;
|
||
-- TRUNCATE TABLE public.ml_user_coupons CASCADE;
|
||
-- TRUNCATE TABLE public.ml_coupon_templates CASCADE;
|
||
-- TRUNCATE TABLE public.ml_product_skus CASCADE;
|
||
-- TRUNCATE TABLE public.ml_products CASCADE;
|
||
-- TRUNCATE TABLE public.ml_shops CASCADE;
|
||
-- TRUNCATE TABLE public.ml_brands CASCADE;
|
||
-- TRUNCATE TABLE public.ml_categories CASCADE;
|
||
-- TRUNCATE TABLE public.ml_user_addresses CASCADE;
|
||
-- TRUNCATE TABLE public.ml_user_profiles CASCADE;
|
||
|
||
-- =====================================================================================
|
||
-- 2. Supabase Auth 用户创建 + ak_users 数据
|
||
-- =====================================================================================
|
||
|
||
-- 重要提示：在 Supabase 环境中，建议通过以下方式创建测试用户：
|
||
--
|
||
-- 方法一：使用 Supabase Dashboard 手动创建用户
|
||
-- 1. 进入 Supabase Dashboard -> Authentication -> Users
|
||
-- 2. 点击 "Add user" 创建以下测试用户：
|
||
-- - admin@mall.com (密码: Test123456!)
|
||
-- - merchant1@mall.com (密码: Test123456!)
|
||
-- - merchant2@mall.com (密码: Test123456!)
|
||
-- - customer1@mall.com (密码: Test123456!)
|
||
-- - customer2@mall.com (密码: Test123456!)
|
||
-- - customer3@mall.com (密码: Test123456!)
|
||
-- - driver1@mall.com (密码: Test123456!)
|
||
-- - driver2@mall.com (密码: Test123456!)
|
||
--
|
||
-- 方法二：使用 Supabase Admin API (需要在服务端执行)
|
||
--
|
||
-- 方法三：如果在本地开发环境，可以尝试直接插入 auth.users 表（仅限开发）
|
||
|
||
-- 检查是否为 Supabase 环境
|
||
DO $$
|
||
DECLARE
|
||
is_supabase BOOLEAN := FALSE;
|
||
auth_user_rec RECORD;
|
||
test_emails TEXT[] := ARRAY[
|
||
'admin@mall.com',
|
||
'merchant1@mall.com',
|
||
'merchant2@mall.com',
|
||
'customer1@mall.com',
|
||
'customer2@mall.com',
|
||
'customer3@mall.com',
|
||
'driver1@mall.com',
|
||
'driver2@mall.com'
|
||
];
|
||
email_addr TEXT;
|
||
BEGIN
|
||
-- 检查是否存在 auth schema (Supabase 特征)
|
||
SELECT EXISTS (
|
||
SELECT 1 FROM information_schema.schemata WHERE schema_name = 'auth'
|
||
) INTO is_supabase;
|
||
|
||
IF is_supabase THEN
|
||
RAISE NOTICE '检测到 Supabase 环境，检查 auth.users 表中的测试用户...';
|
||
|
||
-- 检查测试用户是否存在
|
||
FOREACH email_addr IN ARRAY test_emails LOOP
|
||
SELECT id, email INTO auth_user_rec
|
||
FROM auth.users
|
||
WHERE email = email_addr
|
||
LIMIT 1;
|
||
|
||
IF FOUND THEN
|
||
RAISE NOTICE '✓ Auth用户已存在: % (ID: %)', auth_user_rec.email, auth_user_rec.id;
|
||
ELSE
|
||
RAISE NOTICE '✗ Auth用户不存在: %', email_addr;
|
||
RAISE NOTICE '请在 Supabase Dashboard 中创建此用户或使用 Admin API';
|
||
END IF;
|
||
END LOOP;
|
||
|
||
RAISE NOTICE '============================================';
|
||
RAISE NOTICE '如果有缺失的用户，请按以下步骤操作：';
|
||
RAISE NOTICE '1. 登录 Supabase Dashboard';
|
||
RAISE NOTICE '2. 进入 Authentication -> Users';
|
||
RAISE NOTICE '3. 点击 "Add user" 创建缺失的用户';
|
||
RAISE NOTICE '4. 邮箱格式: user@mall.com, 密码: Test123456!';
|
||
RAISE NOTICE '5. 重新执行此脚本';
|
||
RAISE NOTICE '============================================';
|
||
ELSE
|
||
RAISE NOTICE '非 Supabase 环境，将创建虚拟 auth_id...';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 1. 插入用户数据 (ak_users) - 关联 Supabase Auth 用户
|
||
-- =====================================================================================
|
||
|
||
-- 临时函数：获取或创建 auth_id
|
||
CREATE OR REPLACE FUNCTION get_or_create_auth_id(user_email TEXT)
|
||
RETURNS UUID AS $$
|
||
DECLARE
|
||
auth_user_id UUID;
|
||
BEGIN
|
||
-- 尝试从 auth.users 获取真实用户ID
|
||
IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'auth') THEN
|
||
SELECT id INTO auth_user_id FROM auth.users WHERE email = user_email LIMIT 1;
|
||
|
||
IF auth_user_id IS NOT NULL THEN
|
||
RETURN auth_user_id;
|
||
ELSE
|
||
RAISE WARNING '用户 % 在 auth.users 中不存在，使用虚拟UUID', user_email;
|
||
END IF;
|
||
END IF;
|
||
|
||
-- 生成确定性虚拟UUID (基于邮箱)
|
||
RETURN uuid_generate_v5(uuid_ns_dns(), user_email);
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 插入系统管理员
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('admin@mall.com'),
|
||
'系统管理员',
|
||
'admin',
|
||
'admin@mall.com',
|
||
'13800138000',
|
||
'https://example.com/avatars/admin.jpg',
|
||
'active',
|
||
'web',
|
||
NOW() - INTERVAL '365 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'admin@mall.com');
|
||
|
||
-- 插入商家用户
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('merchant1@mall.com'),
|
||
'数码专营店',
|
||
'merchant',
|
||
'merchant1@mall.com',
|
||
'13800138001',
|
||
'https://example.com/avatars/merchant1.jpg',
|
||
'active',
|
||
'web',
|
||
NOW() - INTERVAL '300 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'merchant1@mall.com');
|
||
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('merchant2@mall.com'),
|
||
'时尚服饰店',
|
||
'merchant',
|
||
'merchant2@mall.com',
|
||
'13800138002',
|
||
'https://example.com/avatars/merchant2.jpg',
|
||
'active',
|
||
'web',
|
||
NOW() - INTERVAL '200 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'merchant2@mall.com');
|
||
|
||
-- 插入普通用户
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('customer1@mall.com'),
|
||
'张小明',
|
||
'customer',
|
||
'customer1@mall.com',
|
||
'13800138101',
|
||
'https://example.com/avatars/customer1.jpg',
|
||
'active',
|
||
'mobile',
|
||
NOW() - INTERVAL '180 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'customer1@mall.com');
|
||
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('customer2@mall.com'),
|
||
'李小红',
|
||
'customer',
|
||
'customer2@mall.com',
|
||
'13800138102',
|
||
'https://example.com/avatars/customer2.jpg',
|
||
'active',
|
||
'mobile',
|
||
NOW() - INTERVAL '120 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'customer2@mall.com');
|
||
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('customer3@mall.com'),
|
||
'王小华',
|
||
'customer',
|
||
'customer3@mall.com',
|
||
'13800138103',
|
||
'https://example.com/avatars/customer3.jpg',
|
||
'active',
|
||
'web',
|
||
NOW() - INTERVAL '90 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'customer3@mall.com');
|
||
|
||
-- 插入配送员
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('driver1@mall.com'),
|
||
'快递小哥1',
|
||
'delivery',
|
||
'driver1@mall.com',
|
||
'13800138201',
|
||
'https://example.com/avatars/driver1.jpg',
|
||
'active',
|
||
'mobile',
|
||
NOW() - INTERVAL '150 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'driver1@mall.com');
|
||
|
||
INSERT INTO ak_users (auth_id, username, role, email, phone, avatar_url, status, registration_source, created_at, updated_at)
|
||
SELECT
|
||
get_or_create_auth_id('driver2@mall.com'),
|
||
'快递小哥2',
|
||
'delivery',
|
||
'driver2@mall.com',
|
||
'13800138202',
|
||
'https://example.com/avatars/driver2.jpg',
|
||
'active',
|
||
'mobile',
|
||
NOW() - INTERVAL '100 days',
|
||
NOW()
|
||
WHERE NOT EXISTS (SELECT 1 FROM ak_users WHERE email = 'driver2@mall.com');
|
||
|
||
-- 清理临时函数
|
||
DROP FUNCTION get_or_create_auth_id(TEXT);
|
||
|
||
-- =====================================================================================
|
||
-- 2. 获取用户ID (用于后续插入)
|
||
-- =====================================================================================
|
||
DO $$
|
||
DECLARE
|
||
admin_id UUID;
|
||
merchant1_id UUID;
|
||
merchant2_id UUID;
|
||
customer1_id UUID;
|
||
customer2_id UUID;
|
||
customer3_id UUID;
|
||
driver1_id UUID;
|
||
driver2_id UUID;
|
||
BEGIN
|
||
-- 获取用户ID（使用email字段）
|
||
SELECT id INTO admin_id FROM ak_users WHERE email = 'admin@mall.com';
|
||
SELECT id INTO merchant1_id FROM ak_users WHERE email = 'merchant1@mall.com';
|
||
SELECT id INTO merchant2_id FROM ak_users WHERE email = 'merchant2@mall.com';
|
||
SELECT id INTO customer1_id FROM ak_users WHERE email = 'customer1@mall.com';
|
||
SELECT id INTO customer2_id FROM ak_users WHERE email = 'customer2@mall.com';
|
||
SELECT id INTO customer3_id FROM ak_users WHERE email = 'customer3@mall.com';
|
||
SELECT id INTO driver1_id FROM ak_users WHERE email = 'driver1@mall.com';
|
||
SELECT id INTO driver2_id FROM ak_users WHERE email = 'driver2@mall.com';
|
||
|
||
-- 创建临时表存储ID
|
||
CREATE TEMP TABLE IF NOT EXISTS temp_user_ids (
|
||
role VARCHAR(20),
|
||
user_id UUID
|
||
);
|
||
|
||
INSERT INTO temp_user_ids VALUES
|
||
('admin', admin_id),
|
||
('merchant1', merchant1_id),
|
||
('merchant2', merchant2_id),
|
||
('customer1', customer1_id),
|
||
('customer2', customer2_id),
|
||
('customer3', customer3_id),
|
||
('driver1', driver1_id),
|
||
('driver2', driver2_id);
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 3. 用户档案数据
|
||
-- =====================================================================================
|
||
|
||
-- 插入用户档案
|
||
INSERT INTO public.ml_user_profiles (user_id, status, real_name, credit_score, verification_status, verification_data, preferences)
|
||
SELECT
|
||
t.user_id,
|
||
1, -- 正常状态
|
||
CASE
|
||
WHEN t.role = 'admin' THEN '系统管理员'
|
||
WHEN t.role = 'merchant1' THEN '张三丰'
|
||
WHEN t.role = 'merchant2' THEN '李四海'
|
||
WHEN t.role = 'customer1' THEN '王小明'
|
||
WHEN t.role = 'customer2' THEN '刘小红'
|
||
WHEN t.role = 'customer3' THEN '陈小华'
|
||
WHEN t.role = 'driver1' THEN '赵配送'
|
||
WHEN t.role = 'driver2' THEN '钱师傅'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'admin' THEN 1000
|
||
WHEN t.role IN ('merchant1', 'merchant2') THEN 950
|
||
WHEN t.role IN ('driver1', 'driver2') THEN 900
|
||
ELSE 850
|
||
END,
|
||
CASE
|
||
WHEN t.role IN ('admin', 'merchant1', 'merchant2') THEN 1 -- 已认证
|
||
ELSE 0 -- 未认证
|
||
END,
|
||
'{}', -- 认证数据
|
||
'{"language": "zh-CN", "currency": "CNY", "notifications": true}' -- 偏好设置
|
||
FROM temp_user_ids t
|
||
ON CONFLICT (user_id) DO UPDATE SET
|
||
real_name = EXCLUDED.real_name,
|
||
credit_score = EXCLUDED.credit_score,
|
||
verification_status = EXCLUDED.verification_status;
|
||
|
||
-- =====================================================================================
|
||
-- 4. 用户地址数据
|
||
-- =====================================================================================
|
||
|
||
-- 插入用户地址
|
||
INSERT INTO public.ml_user_addresses (user_id, receiver_name, receiver_phone, province, city, district, address_detail, is_default, label, latitude, longitude)
|
||
SELECT
|
||
t.user_id,
|
||
CASE
|
||
WHEN t.role = 'customer1' THEN '王小明'
|
||
WHEN t.role = 'customer2' THEN '刘小红'
|
||
WHEN t.role = 'customer3' THEN '陈小华'
|
||
WHEN t.role = 'merchant1' THEN '张三丰'
|
||
WHEN t.role = 'merchant2' THEN '李四海'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'customer1' THEN '13800138003'
|
||
WHEN t.role = 'customer2' THEN '13800138004'
|
||
WHEN t.role = 'customer3' THEN '13800138005'
|
||
WHEN t.role = 'merchant1' THEN '13800138001'
|
||
WHEN t.role = 'merchant2' THEN '13800138002'
|
||
END,
|
||
'北京市',
|
||
'朝阳区',
|
||
'望京街道',
|
||
CASE
|
||
WHEN t.role = 'customer1' THEN '望京SOHO T1座 1201室'
|
||
WHEN t.role = 'customer2' THEN '国贸大厦 A座 2301室'
|
||
WHEN t.role = 'customer3' THEN '三里屯太古里 B1-023'
|
||
WHEN t.role = 'merchant1' THEN '中关村创业大街 8号楼'
|
||
WHEN t.role = 'merchant2' THEN '亦庄经济开发区 科创园'
|
||
END,
|
||
TRUE, -- 默认地址
|
||
'home',
|
||
39.9942 + (random() - 0.5) * 0.01, -- 北京纬度附近
|
||
116.3258 + (random() - 0.5) * 0.01 -- 北京经度附近
|
||
FROM temp_user_ids t
|
||
WHERE t.role IN ('customer1', 'customer2', 'customer3', 'merchant1', 'merchant2');
|
||
|
||
-- 为部分用户添加第二个地址
|
||
INSERT INTO public.ml_user_addresses (user_id, receiver_name, receiver_phone, province, city, district, address_detail, is_default, label, latitude, longitude)
|
||
SELECT
|
||
t.user_id,
|
||
'公司收货',
|
||
'010-12345678',
|
||
'北京市',
|
||
'海淀区',
|
||
'中关村街道',
|
||
'中关村大街1号 科技大厦',
|
||
FALSE,
|
||
'office',
|
||
39.9042,
|
||
116.3958
|
||
FROM temp_user_ids t
|
||
WHERE t.role IN ('customer1', 'customer2');
|
||
|
||
-- =====================================================================================
|
||
-- 5. 商品分类数据
|
||
-- =====================================================================================
|
||
|
||
-- 删除默认分类，插入更详细的分类
|
||
DELETE FROM public.ml_categories;
|
||
|
||
-- 一级分类
|
||
INSERT INTO public.ml_categories (id, name, slug, level, path, sort_order, icon_url, description) VALUES
|
||
(uuid_generate_v4(), '数码电器', 'digital', 1, ARRAY['数码电器'], 1, 'icon-digital', '手机、电脑、家电等数码产品'),
|
||
(uuid_generate_v4(), '服装鞋帽', 'fashion', 1, ARRAY['服装鞋帽'], 2, 'icon-fashion', '男装、女装、鞋子、配饰'),
|
||
(uuid_generate_v4(), '家居用品', 'home', 1, ARRAY['家居用品'], 3, 'icon-home', '家具、装饰、生活用品'),
|
||
(uuid_generate_v4(), '食品饮料', 'food', 1, ARRAY['食品饮料'], 4, 'icon-food', '新鲜食材、零食、饮品'),
|
||
(uuid_generate_v4(), '美妆护肤', 'beauty', 1, ARRAY['美妆护肤'], 5, 'icon-beauty', '化妆品、护肤品、个人护理'),
|
||
(uuid_generate_v4(), '运动户外', 'sports', 1, ARRAY['运动户外'], 6, 'icon-sports', '运动器材、户外装备、健身用品'),
|
||
(uuid_generate_v4(), '图书文娱', 'books', 1, ARRAY['图书文娱'], 7, 'icon-books', '图书、音像、文具、玩具'),
|
||
(uuid_generate_v4(), '母婴用品', 'baby', 1, ARRAY['母婴用品'], 8, 'icon-baby', '婴儿用品、孕妇用品、儿童玩具');
|
||
|
||
-- 获取一级分类ID并创建二级分类
|
||
DO $$
|
||
DECLARE
|
||
digital_id UUID;
|
||
fashion_id UUID;
|
||
home_id UUID;
|
||
food_id UUID;
|
||
BEGIN
|
||
-- 获取一级分类ID
|
||
SELECT id INTO digital_id FROM public.ml_categories WHERE slug = 'digital';
|
||
SELECT id INTO fashion_id FROM public.ml_categories WHERE slug = 'fashion';
|
||
SELECT id INTO home_id FROM public.ml_categories WHERE slug = 'home';
|
||
SELECT id INTO food_id FROM public.ml_categories WHERE slug = 'food';
|
||
|
||
-- 数码电器二级分类
|
||
INSERT INTO public.ml_categories (parent_id, name, slug, level, path, sort_order) VALUES
|
||
(digital_id, '手机通讯', 'mobile', 2, ARRAY['数码电器', '手机通讯'], 1),
|
||
(digital_id, '电脑办公', 'computer', 2, ARRAY['数码电器', '电脑办公'], 2),
|
||
(digital_id, '家用电器', 'appliance', 2, ARRAY['数码电器', '家用电器'], 3),
|
||
(digital_id, '数码配件', 'accessories', 2, ARRAY['数码电器', '数码配件'], 4);
|
||
|
||
-- 服装鞋帽二级分类
|
||
INSERT INTO public.ml_categories (parent_id, name, slug, level, path, sort_order) VALUES
|
||
(fashion_id, '男装', 'mens-wear', 2, ARRAY['服装鞋帽', '男装'], 1),
|
||
(fashion_id, '女装', 'womens-wear', 2, ARRAY['服装鞋帽', '女装'], 2),
|
||
(fashion_id, '男鞋', 'mens-shoes', 2, ARRAY['服装鞋帽', '男鞋'], 3),
|
||
(fashion_id, '女鞋', 'womens-shoes', 2, ARRAY['服装鞋帽', '女鞋'], 4);
|
||
|
||
-- 家居用品二级分类
|
||
INSERT INTO public.ml_categories (parent_id, name, slug, level, path, sort_order) VALUES
|
||
(home_id, '家具', 'furniture', 2, ARRAY['家居用品', '家具'], 1),
|
||
(home_id, '家装', 'decoration', 2, ARRAY['家居用品', '家装'], 2),
|
||
(home_id, '厨具', 'kitchen', 2, ARRAY['家居用品', '厨具'], 3),
|
||
(home_id, '生活用品', 'daily', 2, ARRAY['家居用品', '生活用品'], 4);
|
||
|
||
-- 食品饮料二级分类
|
||
INSERT INTO public.ml_categories (parent_id, name, slug, level, path, sort_order) VALUES
|
||
(food_id, '新鲜水果', 'fruits', 2, ARRAY['食品饮料', '新鲜水果'], 1),
|
||
(food_id, '肉禽蛋类', 'meat', 2, ARRAY['食品饮料', '肉禽蛋类'], 2),
|
||
(food_id, '零食坚果', 'snacks', 2, ARRAY['食品饮料', '零食坚果'], 3),
|
||
(food_id, '酒水饮料', 'drinks', 2, ARRAY['食品饮料', '酒水饮料'], 4);
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 6. 品牌数据
|
||
-- =====================================================================================
|
||
|
||
INSERT INTO public.ml_brands (name, logo_url, description, website) VALUES
|
||
('苹果', 'https://cdn.jsdelivr.net/gh/devicons/devicon/icons/apple/apple-original.svg', '创新科技品牌', 'https://www.apple.com'),
|
||
('华为', 'https://logo.clearbit.com/huawei.com', '全球领先的信息与通信技术解决方案供应商', 'https://www.huawei.com'),
|
||
('小米', 'https://logo.clearbit.com/mi.com', '专注于智能硬件和电子产品', 'https://www.mi.com'),
|
||
('三星', 'https://logo.clearbit.com/samsung.com', '全球知名电子产品制造商', 'https://www.samsung.com'),
|
||
('耐克', 'https://logo.clearbit.com/nike.com', '全球著名体育用品品牌', 'https://www.nike.com'),
|
||
('阿迪达斯', 'https://logo.clearbit.com/adidas.com', '德国运动用品制造商', 'https://www.adidas.com'),
|
||
('优衣库', 'https://logo.clearbit.com/uniqlo.com', '日本休闲服饰品牌', 'https://www.uniqlo.com'),
|
||
('宜家', 'https://logo.clearbit.com/ikea.com', '瑞典家居用品零售商', 'https://www.ikea.com'),
|
||
('美的', 'https://logo.clearbit.com/midea.com', '中国家电制造商', 'https://www.midea.com'),
|
||
('海尔', 'https://logo.clearbit.com/haier.com', '全球知名家电品牌', 'https://www.haier.com');
|
||
|
||
-- =====================================================================================
|
||
-- 7. 店铺数据
|
||
-- =====================================================================================
|
||
|
||
-- 获取商家用户ID并创建店铺
|
||
INSERT INTO public.ml_shops (merchant_id, shop_name, shop_logo, shop_banner, description, business_license, contact_name, contact_phone, contact_email, address, business_hours, status, verified_at)
|
||
SELECT
|
||
t.user_id,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN '张三丰数码专营店'
|
||
WHEN t.role = 'merchant2' THEN '李四海时尚小铺'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN 'https://api.dicebear.com/7.x/shapes/svg?seed=shop1'
|
||
WHEN t.role = 'merchant2' THEN 'https://api.dicebear.com/7.x/shapes/svg?seed=shop2'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN 'https://picsum.photos/800/200?random=1'
|
||
WHEN t.role = 'merchant2' THEN 'https://picsum.photos/800/200?random=2'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN '专业销售各类数码产品，品质保证，服务至上'
|
||
WHEN t.role = 'merchant2' THEN '时尚潮流服饰，让您成为街头最亮的星'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN '91110000MA0001234A'
|
||
WHEN t.role = 'merchant2' THEN '91110000MA0005678B'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN '张三丰'
|
||
WHEN t.role = 'merchant2' THEN '李四海'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN '13800138001'
|
||
WHEN t.role = 'merchant2' THEN '13800138002'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'merchant1' THEN 'merchant1@mall.com'
|
||
WHEN t.role = 'merchant2' THEN 'merchant2@mall.com'
|
||
END,
|
||
'{"province": "北京市", "city": "朝阳区", "district": "望京街道", "detail": "望京商业中心"}',
|
||
'{"monday": "09:00-21:00", "tuesday": "09:00-21:00", "wednesday": "09:00-21:00", "thursday": "09:00-21:00", "friday": "09:00-21:00", "saturday": "09:00-22:00", "sunday": "10:00-20:00"}',
|
||
1, -- 正常状态
|
||
NOW() - INTERVAL '30 days' -- 30天前认证
|
||
FROM temp_user_ids t
|
||
WHERE t.role IN ('merchant1', 'merchant2');
|
||
|
||
-- =====================================================================================
|
||
-- 8. 商品数据
|
||
-- =====================================================================================
|
||
|
||
-- 获取分类和品牌ID
|
||
DO $$
|
||
DECLARE
|
||
mobile_cat_id UUID;
|
||
computer_cat_id UUID;
|
||
mens_wear_cat_id UUID;
|
||
womens_wear_cat_id UUID;
|
||
furniture_cat_id UUID;
|
||
fruits_cat_id UUID;
|
||
|
||
apple_brand_id UUID;
|
||
huawei_brand_id UUID;
|
||
xiaomi_brand_id UUID;
|
||
nike_brand_id UUID;
|
||
uniqlo_brand_id UUID;
|
||
ikea_brand_id UUID;
|
||
|
||
merchant1_id UUID;
|
||
merchant2_id UUID;
|
||
BEGIN
|
||
-- 获取分类ID
|
||
SELECT id INTO mobile_cat_id FROM public.ml_categories WHERE slug = 'mobile';
|
||
SELECT id INTO computer_cat_id FROM public.ml_categories WHERE slug = 'computer';
|
||
SELECT id INTO mens_wear_cat_id FROM public.ml_categories WHERE slug = 'mens-wear';
|
||
SELECT id INTO womens_wear_cat_id FROM public.ml_categories WHERE slug = 'womens-wear';
|
||
SELECT id INTO furniture_cat_id FROM public.ml_categories WHERE slug = 'furniture';
|
||
SELECT id INTO fruits_cat_id FROM public.ml_categories WHERE slug = 'fruits';
|
||
|
||
-- 获取品牌ID
|
||
SELECT id INTO apple_brand_id FROM public.ml_brands WHERE name = '苹果';
|
||
SELECT id INTO huawei_brand_id FROM public.ml_brands WHERE name = '华为';
|
||
SELECT id INTO xiaomi_brand_id FROM public.ml_brands WHERE name = '小米';
|
||
SELECT id INTO nike_brand_id FROM public.ml_brands WHERE name = '耐克';
|
||
SELECT id INTO uniqlo_brand_id FROM public.ml_brands WHERE name = '优衣库';
|
||
SELECT id INTO ikea_brand_id FROM public.ml_brands WHERE name = '宜家';
|
||
|
||
-- 获取商家ID
|
||
SELECT user_id INTO merchant1_id FROM temp_user_ids WHERE role = 'merchant1';
|
||
SELECT user_id INTO merchant2_id FROM temp_user_ids WHERE role = 'merchant2';
|
||
|
||
-- 插入商品数据 - 商家1的数码产品
|
||
INSERT INTO public.ml_products (
|
||
merchant_id, category_id, brand_id, product_code, name, subtitle, description,
|
||
main_image_url, image_urls, base_price, market_price, total_stock, available_stock,
|
||
weight, status, is_featured, is_new, is_hot, tags, slug
|
||
) VALUES
|
||
-- iPhone 15 Pro
|
||
(merchant1_id, mobile_cat_id, apple_brand_id, 'IP15P-001',
|
||
'iPhone 15 Pro 256GB 深空黑色',
|
||
'A17 Pro芯片，钛金属设计，专业级摄像头系统',
|
||
'全新iPhone 15 Pro采用航空级钛金属设计，搭载A17 Pro芯片，配备专业级摄像头系统，支持5G网络。48MP主摄像头，2倍变焦，4K视频录制。',
|
||
'https://picsum.photos/600/600?random=10',
|
||
'["https://picsum.photos/600/600?random=11", "https://picsum.photos/600/600?random=12", "https://picsum.photos/600/600?random=13"]',
|
||
8999.00, 9999.00, 50, 45, 0.187, 1, true, true, true,
|
||
ARRAY['手机', '苹果', 'iPhone', '5G', '新品'], 'iphone-15-pro-256gb-black'),
|
||
|
||
-- 华为 Mate 60 Pro
|
||
(merchant1_id, mobile_cat_id, huawei_brand_id, 'HW-M60P-001',
|
||
'华为 Mate 60 Pro 512GB 雅川青',
|
||
'卫星通话，昆仑玻璃，超聚光夜拍',
|
||
'华为Mate 60 Pro支持卫星通话功能，采用昆仑玻璃，配备超聚光夜拍摄像头，5000万像素主摄，支持100倍数字变焦。',
|
||
'https://picsum.photos/600/600?random=20',
|
||
'["https://picsum.photos/600/600?random=21", "https://picsum.photos/600/600?random=22"]',
|
||
6999.00, 7999.00, 30, 28, 0.225, 1, true, false, true,
|
||
ARRAY['手机', '华为', 'Mate', '卫星通话'], 'huawei-mate-60-pro-512gb'),
|
||
|
||
-- 小米笔记本
|
||
(merchant1_id, computer_cat_id, xiaomi_brand_id, 'XM-NB-001',
|
||
'小米笔记本 Pro 14 增强版',
|
||
'2.8K OLED屏幕，12代酷睿处理器',
|
||
'14英寸2.8K OLED全面屏，120Hz刷新率，第12代Intel Core处理器，16GB内存，512GB固态硬盘，雷电4接口。',
|
||
'https://picsum.photos/600/600?random=30',
|
||
'["https://picsum.photos/600/600?random=31", "https://picsum.photos/600/600?random=32"]',
|
||
5999.00, 6999.00, 20, 18, 1.4, 1, false, true, false,
|
||
ARRAY['笔记本', '小米', 'OLED', '办公'], 'xiaomi-notebook-pro-14');
|
||
|
||
-- 插入商品数据 - 商家2的时尚产品
|
||
INSERT INTO public.ml_products (
|
||
merchant_id, category_id, brand_id, product_code, name, subtitle, description,
|
||
main_image_url, image_urls, base_price, market_price, total_stock, available_stock,
|
||
weight, status, is_featured, is_new, is_hot, tags, slug
|
||
) VALUES
|
||
-- 耐克运动鞋
|
||
(merchant2_id, mens_wear_cat_id, nike_brand_id, 'NK-AIR-001',
|
||
'Nike Air Max 270 男士运动鞋',
|
||
'全掌气垫，舒适透气，时尚百搭',
|
||
'Nike Air Max 270采用全新的Air Max气垫设计，提供卓越的缓震效果。透气网面鞋身，轻量化设计，适合日常运动和休闲穿着。',
|
||
'https://picsum.photos/600/600?random=40',
|
||
'["https://picsum.photos/600/600?random=41", "https://picsum.photos/600/600?random=42"]',
|
||
899.00, 1099.00, 100, 95, 0.8, 1, true, false, true,
|
||
ARRAY['运动鞋', '耐克', '气垫', '男鞋'], 'nike-air-max-270-mens'),
|
||
|
||
-- 优衣库T恤
|
||
(merchant2_id, mens_wear_cat_id, uniqlo_brand_id, 'UQ-TEE-001',
|
||
'UNIQLO 优质棉圆领T恤（短袖）',
|
||
'100%纯棉，柔软舒适，多色可选',
|
||
'采用100%精选纯棉制作，触感柔软，吸汗透气。简约设计，多种颜色可选，是日常穿搭的必备单品。',
|
||
'https://picsum.photos/600/600?random=50',
|
||
'["https://picsum.photos/600/600?random=51", "https://picsum.photos/600/600?random=52"]',
|
||
59.00, 79.00, 200, 180, 0.2, 1, false, false, false,
|
||
ARRAY['T恤', '优衣库', '纯棉', '基础款'], 'uniqlo-cotton-tshirt'),
|
||
|
||
-- 女装连衣裙
|
||
(merchant2_id, womens_wear_cat_id, uniqlo_brand_id, 'UQ-DRESS-001',
|
||
'UNIQLO 女装雪纺连衣裙',
|
||
'轻盈雪纺面料，优雅飘逸，职场通勤',
|
||
'采用轻盈雪纺面料，版型优雅，适合职场通勤和日常聚会。A字版型修饰身形，多种花色可选。',
|
||
'https://picsum.photos/600/600?random=60',
|
||
'["https://picsum.photos/600/600?random=61", "https://picsum.photos/600/600?random=62"]',
|
||
299.00, 399.00, 80, 75, 0.3, 1, true, true, false,
|
||
ARRAY['连衣裙', '优衣库', '雪纺', '女装'], 'uniqlo-chiffon-dress');
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 9. 商品SKU数据
|
||
-- =====================================================================================
|
||
|
||
-- 为iPhone添加SKU
|
||
INSERT INTO public.ml_product_skus (product_id, sku_code, specifications, price, market_price, stock, image_url)
|
||
SELECT
|
||
p.id,
|
||
'IP15P-' || color.code || '-' || storage.code,
|
||
jsonb_build_object('颜色', color.name, '存储容量', storage.name),
|
||
p.base_price + storage.price_diff,
|
||
p.market_price + storage.price_diff,
|
||
15,
|
||
'https://picsum.photos/400/400?random=' || (10 + color.id)
|
||
FROM public.ml_products p
|
||
CROSS JOIN (VALUES
|
||
(1, 'BLK', '深空黑色', 0),
|
||
(2, 'WHT', '白色钛金属', 0),
|
||
(3, 'BLU', '蓝色钛金属', 0)
|
||
) AS color(id, code, name, price_diff)
|
||
CROSS JOIN (VALUES
|
||
(1, '128G', '128GB', -1000),
|
||
(2, '256G', '256GB', 0),
|
||
(3, '512G', '512GB', 1500)
|
||
) AS storage(id, code, name, price_diff)
|
||
WHERE p.product_code = 'IP15P-001';
|
||
|
||
-- 为运动鞋添加SKU
|
||
INSERT INTO public.ml_product_skus (product_id, sku_code, specifications, price, market_price, stock, image_url)
|
||
SELECT
|
||
p.id,
|
||
'NK-AIR-' || color.code || '-' || size.code,
|
||
jsonb_build_object('颜色', color.name, '尺码', size.name),
|
||
p.base_price,
|
||
p.market_price,
|
||
10,
|
||
'https://picsum.photos/400/400?random=' || (40 + color.id)
|
||
FROM public.ml_products p
|
||
CROSS JOIN (VALUES
|
||
(1, 'BLK', '黑色'),
|
||
(2, 'WHT', '白色'),
|
||
(3, 'RED', '红色')
|
||
) AS color(id, code, name)
|
||
CROSS JOIN (VALUES
|
||
(1, '40', '40码'),
|
||
(2, '41', '41码'),
|
||
(3, '42', '42码'),
|
||
(4, '43', '43码'),
|
||
(5, '44', '44码')
|
||
) AS size(id, code, name)
|
||
WHERE p.product_code = 'NK-AIR-001';
|
||
|
||
-- 为T恤添加SKU
|
||
INSERT INTO public.ml_product_skus (product_id, sku_code, specifications, price, market_price, stock, image_url)
|
||
SELECT
|
||
p.id,
|
||
'UQ-TEE-' || color.code || '-' || size.code,
|
||
jsonb_build_object('颜色', color.name, '尺码', size.name),
|
||
p.base_price,
|
||
p.market_price,
|
||
25,
|
||
'https://picsum.photos/400/400?random=' || (50 + color.id)
|
||
FROM public.ml_products p
|
||
CROSS JOIN (VALUES
|
||
(1, 'WHT', '白色'),
|
||
(2, 'BLK', '黑色'),
|
||
(3, 'GRY', '灰色'),
|
||
(4, 'NVY', '深蓝色')
|
||
) AS color(id, code, name)
|
||
CROSS JOIN (VALUES
|
||
(1, 'S', 'S'),
|
||
(2, 'M', 'M'),
|
||
(3, 'L', 'L'),
|
||
(4, 'XL', 'XL')
|
||
) AS size(id, code, name)
|
||
WHERE p.product_code = 'UQ-TEE-001';
|
||
|
||
-- =====================================================================================
|
||
-- 10. 购物车数据
|
||
-- =====================================================================================
|
||
|
||
-- 为消费者添加购物车数据
|
||
INSERT INTO public.ml_shopping_cart (user_id, product_id, sku_id, quantity, selected)
|
||
SELECT
|
||
customer.user_id,
|
||
p.id,
|
||
s.id,
|
||
CASE
|
||
WHEN customer.role = 'customer1' THEN 1
|
||
WHEN customer.role = 'customer2' THEN 2
|
||
ELSE 1
|
||
END,
|
||
true
|
||
FROM temp_user_ids customer
|
||
CROSS JOIN public.ml_products p
|
||
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id
|
||
WHERE customer.role IN ('customer1', 'customer2', 'customer3')
|
||
AND p.status = 1
|
||
AND (s.id IS NULL OR s.status = 1)
|
||
AND random() < 0.3 -- 30%的概率添加到购物车
|
||
LIMIT 15;
|
||
|
||
-- =====================================================================================
|
||
-- 11. 优惠券数据
|
||
-- =====================================================================================
|
||
|
||
-- 创建优惠券模板
|
||
INSERT INTO public.ml_coupon_templates (
|
||
merchant_id, name, description, coupon_type, discount_type, discount_value,
|
||
min_order_amount, max_discount_amount, total_quantity, per_user_limit,
|
||
start_time, end_time, status
|
||
) VALUES
|
||
-- 平台券
|
||
(NULL, '新用户专享券', '新用户注册即送50元无门槛券', 1, 1, 50.00, 0, 50.00, 1000, 1,
|
||
NOW() - INTERVAL '1 day', NOW() + INTERVAL '30 days', 1),
|
||
(NULL, '满200减30', '全平台满200元减30元', 1, 1, 30.00, 200.00, 30.00, 500, 3,
|
||
NOW() - INTERVAL '1 day', NOW() + INTERVAL '15 days', 1),
|
||
(NULL, '9折优惠券', '全平台9折优惠，最高减100元', 2, 2, 0.9, 100.00, 100.00, 200, 1,
|
||
NOW() - INTERVAL '1 day', NOW() + INTERVAL '7 days', 1),
|
||
|
||
-- 商家券
|
||
((SELECT user_id FROM temp_user_ids WHERE role = 'merchant1'),
|
||
'数码专营店满1000减100', '店铺满1000元减100元', 1, 1, 100.00, 1000.00, 100.00, 100, 2,
|
||
NOW() - INTERVAL '1 day', NOW() + INTERVAL '20 days', 1),
|
||
((SELECT user_id FROM temp_user_ids WHERE role = 'merchant2'),
|
||
'时尚小铺免运费券', '店铺订单免运费', 3, 1, 15.00, 0, 15.00, 50, 1,
|
||
NOW() - INTERVAL '1 day', NOW() + INTERVAL '10 days', 1);
|
||
|
||
-- 为用户发放优惠券
|
||
INSERT INTO public.ml_user_coupons (user_id, template_id, coupon_code, status, expire_at)
|
||
SELECT
|
||
customer.user_id,
|
||
t.id,
|
||
public.generate_coupon_code(),
|
||
1, -- 未使用
|
||
t.end_time
|
||
FROM temp_user_ids customer
|
||
CROSS JOIN public.ml_coupon_templates t
|
||
WHERE customer.role IN ('customer1', 'customer2', 'customer3')
|
||
AND t.status = 1
|
||
AND random() < 0.6; -- 60%的概率获得优惠券
|
||
|
||
-- =====================================================================================
|
||
-- 12. 订单数据
|
||
-- =====================================================================================
|
||
|
||
-- 生成订单数据
|
||
DO $$
|
||
DECLARE
|
||
customer_rec RECORD;
|
||
selected_merchant_id UUID;
|
||
product_rec RECORD;
|
||
order_id UUID;
|
||
order_no TEXT;
|
||
total_amount DECIMAL;
|
||
BEGIN
|
||
-- 为每个客户生成2-4个订单
|
||
FOR customer_rec IN
|
||
SELECT user_id, role FROM temp_user_ids WHERE role LIKE 'customer%'
|
||
LOOP
|
||
FOR i IN 1..FLOOR(2 + random() * 3)::INTEGER LOOP
|
||
-- 生成订单号
|
||
order_no := public.generate_order_no();
|
||
|
||
-- 随机选择商家
|
||
SELECT user_id INTO selected_merchant_id FROM temp_user_ids
|
||
WHERE role LIKE 'merchant%'
|
||
ORDER BY random() LIMIT 1;
|
||
|
||
-- 计算订单总金额（这里简化，实际应该根据商品计算）
|
||
total_amount := 100 + random() * 2000;
|
||
|
||
-- 创建订单
|
||
INSERT INTO public.ml_orders (
|
||
id, order_no, user_id, merchant_id, product_amount, shipping_fee, total_amount,
|
||
shipping_address, order_status, payment_status, shipping_status,
|
||
paid_at, shipped_at, delivered_at,
|
||
remark, created_at
|
||
) VALUES (
|
||
uuid_generate_v4(), order_no, customer_rec.user_id, selected_merchant_id,
|
||
total_amount - 10, 10.00, total_amount,
|
||
jsonb_build_object(
|
||
'receiver_name', '收货人姓名',
|
||
'receiver_phone', '13800138000',
|
||
'province', '北京市',
|
||
'city', '朝阳区',
|
||
'district', '望京街道',
|
||
'address_detail', '望京SOHO T1座 1201室'
|
||
),
|
||
CASE
|
||
WHEN random() < 0.6 THEN 4 -- 已完成
|
||
WHEN random() < 0.8 THEN 3 -- 待收货
|
||
WHEN random() < 0.9 THEN 2 -- 待发货
|
||
ELSE 1 -- 待付款
|
||
END,
|
||
CASE
|
||
WHEN random() < 0.8 THEN 2 -- 已付款
|
||
ELSE 1 -- 未付款
|
||
END,
|
||
CASE
|
||
WHEN random() < 0.7 THEN 4 -- 已送达
|
||
WHEN random() < 0.85 THEN 3 -- 运输中
|
||
WHEN random() < 0.9 THEN 2 -- 已发货
|
||
ELSE 1 -- 未发货
|
||
END,
|
||
CASE WHEN random() < 0.8 THEN NOW() - INTERVAL '1 day' * FLOOR(random() * 10) END,
|
||
CASE WHEN random() < 0.7 THEN NOW() - INTERVAL '1 day' * FLOOR(random() * 8) END,
|
||
CASE WHEN random() < 0.6 THEN NOW() - INTERVAL '1 day' * FLOOR(random() * 5) END,
|
||
'请尽快发货，谢谢！',
|
||
NOW() - INTERVAL '1 day' * FLOOR(random() * 30)
|
||
) RETURNING id INTO order_id;
|
||
|
||
-- 为订单添加商品
|
||
FOR product_rec IN
|
||
SELECT
|
||
p.id as product_id,
|
||
s.id as sku_id,
|
||
p.name,
|
||
COALESCE(s.price, p.base_price) as price, -- 使用SKU价格，如果没有则使用基础价格
|
||
COALESCE(s.image_url, p.main_image_url) as image_url -- 使用SKU图片，如果没有则使用主图
|
||
FROM public.ml_products p
|
||
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id
|
||
WHERE p.merchant_id = selected_merchant_id
|
||
AND p.status = 1
|
||
AND (s.id IS NULL OR s.status = 1)
|
||
ORDER BY random()
|
||
LIMIT FLOOR(1 + random() * 3)::INTEGER
|
||
LOOP
|
||
DECLARE
|
||
item_quantity INTEGER;
|
||
item_price DECIMAL;
|
||
BEGIN
|
||
item_quantity := FLOOR(1 + random() * 2)::INTEGER;
|
||
item_price := product_rec.price;
|
||
|
||
INSERT INTO public.ml_order_items (
|
||
order_id, product_id, sku_id, product_name,
|
||
price, quantity, total_amount, image_url
|
||
) VALUES (
|
||
order_id, product_rec.product_id, product_rec.sku_id, product_rec.name,
|
||
item_price, item_quantity,
|
||
item_price * item_quantity,
|
||
product_rec.image_url
|
||
);
|
||
END;
|
||
END LOOP;
|
||
END LOOP;
|
||
END LOOP;
|
||
END $$;
|
||
|
||
-- =====================================================================================
|
||
-- 13. 商品评价数据
|
||
-- =====================================================================================
|
||
|
||
-- 为已完成的订单生成评价
|
||
INSERT INTO public.ml_product_reviews (
|
||
order_id, order_item_id, user_id, product_id, merchant_id,
|
||
rating, content, images, is_anonymous, status, created_at
|
||
)
|
||
SELECT
|
||
o.id,
|
||
oi.id,
|
||
o.user_id,
|
||
oi.product_id,
|
||
o.merchant_id,
|
||
FLOOR(3 + random() * 3)::INTEGER, -- 3-5星评价
|
||
CASE
|
||
WHEN random() < 0.3 THEN '商品质量很好，物流很快，推荐购买！'
|
||
WHEN random() < 0.6 THEN '包装完好，商品和描述一致，满意的购物体验。'
|
||
WHEN random() < 0.8 THEN '性价比不错，会再次购买的。'
|
||
ELSE '商品不错，快递也很快，好评！'
|
||
END,
|
||
CASE
|
||
WHEN random() < 0.3 THEN '["https://picsum.photos/300/300?random=' || FLOOR(random() * 100)::INTEGER || '"]'
|
||
ELSE '[]'
|
||
END::JSONB,
|
||
random() < 0.1, -- 10%匿名评价
|
||
1, -- 正常状态
|
||
o.delivered_at + INTERVAL '1 day' * FLOOR(random() * 10)
|
||
FROM public.ml_orders o
|
||
JOIN public.ml_order_items oi ON o.id = oi.order_id
|
||
WHERE o.order_status = 4 -- 已完成的订单
|
||
AND o.delivered_at IS NOT NULL
|
||
AND random() < 0.7; -- 70%的概率有评价
|
||
|
||
-- =====================================================================================
|
||
-- 14. 用户行为数据
|
||
-- =====================================================================================
|
||
|
||
-- 用户收藏数据
|
||
INSERT INTO public.ml_user_favorites (user_id, target_type, target_id)
|
||
SELECT
|
||
customer.user_id,
|
||
1, -- 商品收藏
|
||
p.id
|
||
FROM temp_user_ids customer
|
||
CROSS JOIN public.ml_products p
|
||
WHERE customer.role LIKE 'customer%'
|
||
AND p.status = 1
|
||
AND random() < 0.2 -- 20%的概率收藏
|
||
ON CONFLICT (user_id, target_type, target_id) DO NOTHING;
|
||
|
||
-- 店铺收藏
|
||
INSERT INTO public.ml_user_favorites (user_id, target_type, target_id)
|
||
SELECT
|
||
customer.user_id,
|
||
2, -- 店铺收藏
|
||
s.merchant_id
|
||
FROM temp_user_ids customer
|
||
CROSS JOIN public.ml_shops s
|
||
WHERE customer.role LIKE 'customer%'
|
||
AND random() < 0.3 -- 30%的概率收藏店铺
|
||
ON CONFLICT (user_id, target_type, target_id) DO NOTHING;
|
||
|
||
-- 浏览历史数据
|
||
INSERT INTO public.ml_browse_history (user_id, product_id, browse_duration, created_at, updated_at)
|
||
SELECT
|
||
customer.user_id,
|
||
p.id,
|
||
FLOOR(10 + random() * 300)::INTEGER, -- 10-300秒浏览时长
|
||
NOW() - INTERVAL '1 day' * FLOOR(random() * 30), -- 30天内的浏览记录
|
||
NOW() - INTERVAL '1 day' * FLOOR(random() * 30)
|
||
FROM temp_user_ids customer
|
||
CROSS JOIN public.ml_products p
|
||
WHERE customer.role LIKE 'customer%'
|
||
AND p.status = 1
|
||
AND random() < 0.4 -- 40%的概率有浏览记录
|
||
ON CONFLICT (user_id, product_id) DO UPDATE SET
|
||
browse_duration = EXCLUDED.browse_duration,
|
||
updated_at = EXCLUDED.updated_at;
|
||
|
||
-- 搜索历史数据
|
||
INSERT INTO public.ml_search_history (user_id, keyword, result_count, created_at)
|
||
SELECT
|
||
customer.user_id,
|
||
keyword.word,
|
||
FLOOR(1 + random() * 50)::INTEGER, -- 1-50个结果
|
||
NOW() - INTERVAL '1 day' * FLOOR(random() * 30)
|
||
FROM temp_user_ids customer
|
||
CROSS JOIN (VALUES
|
||
('iPhone'), ('华为手机'), ('笔记本电脑'), ('运动鞋'),
|
||
('T恤'), ('连衣裙'), ('耳机'), ('充电器'), ('数据线'), ('手机壳')
|
||
) AS keyword(word)
|
||
WHERE customer.role LIKE 'customer%'
|
||
AND random() < 0.3; -- 30%的概率有搜索记录
|
||
|
||
-- =====================================================================================
|
||
-- 15. 配送数据
|
||
-- =====================================================================================
|
||
|
||
-- 配送员信息
|
||
INSERT INTO public.ml_delivery_drivers (
|
||
user_id, real_name, id_card, driver_license, vehicle_type, vehicle_number,
|
||
service_areas, work_status, current_lat, current_lng, status
|
||
)
|
||
SELECT
|
||
t.user_id,
|
||
CASE
|
||
WHEN t.role = 'driver1' THEN '赵配送'
|
||
WHEN t.role = 'driver2' THEN '钱师傅'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'driver1' THEN '110101199001011234'
|
||
WHEN t.role = 'driver2' THEN '110101199002022345'
|
||
END,
|
||
CASE
|
||
WHEN t.role = 'driver1' THEN 'D110101199001011234'
|
||
WHEN t.role = 'driver2' THEN 'D110101199002022345'
|
||
END,
|
||
1, -- 电动车
|
||
CASE
|
||
WHEN t.role = 'driver1' THEN '京A12345'
|
||
WHEN t.role = 'driver2' THEN '京A67890'
|
||
END,
|
||
'["朝阳区", "海淀区", "东城区"]'::JSONB,
|
||
1, -- 在线
|
||
39.9042 + (random() - 0.5) * 0.01,
|
||
116.4074 + (random() - 0.5) * 0.01,
|
||
1 -- 正常状态
|
||
FROM temp_user_ids t
|
||
WHERE t.role LIKE 'driver%';
|
||
|
||
-- 配送任务（为部分已发货订单创建配送任务）
|
||
INSERT INTO public.ml_delivery_tasks (
|
||
order_id, driver_id, pickup_address, delivery_address,
|
||
distance, estimated_time, delivery_fee, status,
|
||
assigned_at, picked_at, delivered_at, delivery_code
|
||
)
|
||
SELECT DISTINCT ON (o.id) -- 确保每个订单只有一个配送任务
|
||
o.id,
|
||
d.id,
|
||
jsonb_build_object(
|
||
'name', '商家仓库',
|
||
'phone', '010-12345678',
|
||
'address', '北京市朝阳区望京商业中心'
|
||
),
|
||
o.shipping_address,
|
||
ROUND((5 + random() * 15)::NUMERIC, 2), -- 5-20公里
|
||
FLOOR(20 + random() * 40)::INTEGER, -- 20-60分钟
|
||
CASE
|
||
WHEN (o.shipping_address->>'district') = '朝阳区' THEN 8.00
|
||
ELSE 12.00
|
||
END,
|
||
CASE
|
||
WHEN o.order_status >= 4 THEN 5 -- 已送达
|
||
WHEN o.order_status >= 3 THEN 4 -- 配送中
|
||
WHEN o.shipping_status >= 2 THEN 2 -- 已接单
|
||
ELSE 1 -- 待接单
|
||
END,
|
||
o.shipped_at,
|
||
CASE WHEN o.order_status >= 3 THEN o.shipped_at + INTERVAL '30 minutes' END,
|
||
CASE WHEN o.order_status >= 4 THEN o.delivered_at END,
|
||
LPAD(FLOOR(random() * 10000)::TEXT, 4, '0') -- 4位取货码
|
||
FROM public.ml_orders o
|
||
CROSS JOIN public.ml_delivery_drivers d
|
||
WHERE o.shipping_status >= 2 -- 已发货的订单
|
||
AND random() < 0.8 -- 80%的概率有配送任务
|
||
AND NOT EXISTS ( -- 确保订单尚未有配送任务
|
||
SELECT 1 FROM public.ml_delivery_tasks dt WHERE dt.order_id = o.id
|
||
)
|
||
ORDER BY o.id, random() -- 每个订单随机选择一个配送员
|
||
LIMIT 50; -- 限制配送任务数量，避免过多数据
|
||
|
||
-- =====================================================================================
|
||
-- 16. 更新统计数据
|
||
-- =====================================================================================
|
||
|
||
-- 更新商品统计数据
|
||
UPDATE public.ml_products SET
|
||
view_count = FLOOR(100 + random() * 9900)::INTEGER,
|
||
sale_count = (
|
||
SELECT COALESCE(SUM(oi.quantity), 0)
|
||
FROM public.ml_order_items oi
|
||
JOIN public.ml_orders o ON oi.order_id = o.id
|
||
WHERE oi.product_id = ml_products.id
|
||
AND o.order_status = 4
|
||
),
|
||
favorite_count = (
|
||
SELECT COUNT(*)
|
||
FROM public.ml_user_favorites f
|
||
WHERE f.target_type = 1
|
||
AND f.target_id = ml_products.id
|
||
),
|
||
rating_avg = (
|
||
SELECT COALESCE(AVG(rating), 0)
|
||
FROM public.ml_product_reviews r
|
||
WHERE r.product_id = ml_products.id
|
||
AND r.status = 1
|
||
),
|
||
rating_count = (
|
||
SELECT COUNT(*)
|
||
FROM public.ml_product_reviews r
|
||
WHERE r.product_id = ml_products.id
|
||
AND r.status = 1
|
||
);
|
||
|
||
-- 更新店铺统计数据
|
||
UPDATE public.ml_shops SET
|
||
product_count = (
|
||
SELECT COUNT(*)
|
||
FROM public.ml_products p
|
||
WHERE p.merchant_id = ml_shops.merchant_id
|
||
AND p.status = 1
|
||
),
|
||
order_count = (
|
||
SELECT COUNT(*)
|
||
FROM public.ml_orders o
|
||
WHERE o.merchant_id = ml_shops.merchant_id
|
||
AND o.order_status = 4
|
||
),
|
||
rating_avg = (
|
||
SELECT COALESCE(AVG(r.rating), 0)
|
||
FROM public.ml_product_reviews r
|
||
WHERE r.merchant_id = ml_shops.merchant_id
|
||
AND r.status = 1
|
||
),
|
||
rating_count = (
|
||
SELECT COUNT(*)
|
||
FROM public.ml_product_reviews r
|
||
WHERE r.merchant_id = ml_shops.merchant_id
|
||
AND r.status = 1
|
||
);
|
||
|
||
-- 更新配送员统计数据
|
||
UPDATE public.ml_delivery_drivers SET
|
||
order_count = (
|
||
SELECT COUNT(*)
|
||
FROM public.ml_delivery_tasks dt
|
||
WHERE dt.driver_id = ml_delivery_drivers.id
|
||
AND dt.status = 5
|
||
),
|
||
rating_avg = 4.5 + random() * 0.5, -- 4.5-5.0星评价
|
||
rating_count = FLOOR(10 + random() * 90)::INTEGER; -- 10-100个评价
|
||
|
||
-- =====================================================================================
|
||
-- 17. 清理临时数据
|
||
-- =====================================================================================
|
||
|
||
DROP TABLE IF EXISTS temp_user_ids;
|
||
|
||
-- =====================================================================================
|
||
-- 18. 完成提示
|
||
-- =====================================================================================
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '商城系统模拟数据插入完成！';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '已创建数据概况：';
|
||
RAISE NOTICE '- 测试用户: 8个 (管理员1个, 商家2个, 消费者3个, 配送员2个)';
|
||
RAISE NOTICE '- 用户地址: 7个';
|
||
RAISE NOTICE '- 商品分类: 多级分类体系';
|
||
RAISE NOTICE '- 品牌: 10个知名品牌';
|
||
RAISE NOTICE '- 店铺: 2个商家店铺';
|
||
RAISE NOTICE '- 商品: 6个商品 + 多规格SKU';
|
||
RAISE NOTICE '- 购物车: 随机购物车数据';
|
||
RAISE NOTICE '- 优惠券: 5个优惠券模板 + 用户优惠券';
|
||
RAISE NOTICE '- 订单: 多个测试订单 + 订单商品';
|
||
RAISE NOTICE '- 商品评价: 基于完成订单的评价';
|
||
RAISE NOTICE '- 用户行为: 收藏、浏览、搜索记录';
|
||
RAISE NOTICE '- 配送数据: 配送员 + 配送任务';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '数据状态: 包含各种业务场景的测试数据';
|
||
RAISE NOTICE '建议: 可根据实际需要调整数据量和内容';
|
||
RAISE NOTICE '=======================================================';
|
||
END $$;
|