180 lines
12 KiB
SQL
180 lines
12 KiB
SQL
-- ============================================
|
||
-- 活动测试数据 - 重新整理
|
||
-- ============================================
|
||
|
||
-- 1. 插入测试明星
|
||
INSERT INTO stars (name, tag, name_en, pic_url, description, identity_id, is_active, created_at, updated_at)
|
||
SELECT 'Lisa', 'BLACKPINK', 'Lalisa Manoban', 'https://example.com/lisa.jpg', 'BLACKPINK成员', 'lisa_test_001', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM stars WHERE identity_id = 'lisa_test_001');
|
||
|
||
INSERT INTO stars (name, tag, name_en, pic_url, description, identity_id, is_active, created_at, updated_at)
|
||
SELECT 'Jennie', 'BLACKPINK', 'Jennie Kim', 'https://example.com/jennie.jpg', 'BLACKPINK成员', 'jennie_test_001', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM stars WHERE identity_id = 'jennie_test_001');
|
||
|
||
INSERT INTO stars (name, tag, name_en, pic_url, description, identity_id, is_active, created_at, updated_at)
|
||
SELECT 'Rosé', 'BLACKPINK', 'Park Chae-young', 'https://example.com/rose.jpg', 'BLACKPINK成员', 'rose_test_001', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM stars WHERE identity_id = 'rose_test_001');
|
||
|
||
-- 2. 插入测试用户
|
||
INSERT INTO users (mobile, password_hash, is_active, created_at, updated_at)
|
||
SELECT '13800000001', '$2a$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE mobile = '13800000001');
|
||
|
||
INSERT INTO users (mobile, password_hash, is_active, created_at, updated_at)
|
||
SELECT '13800000002', '$2a$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE mobile = '13800000002');
|
||
|
||
INSERT INTO users (mobile, password_hash, is_active, created_at, updated_at)
|
||
SELECT '13800000003', '$2a$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE mobile = '13800000003');
|
||
|
||
INSERT INTO users (mobile, password_hash, is_active, created_at, updated_at)
|
||
SELECT '13800000004', '$2a$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE mobile = '13800000004');
|
||
|
||
INSERT INTO users (mobile, password_hash, is_active, created_at, updated_at)
|
||
SELECT '13800000005', '$2a$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', true,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000), (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
WHERE NOT EXISTS (SELECT 1 FROM users WHERE mobile = '13800000005');
|
||
|
||
-- 3. 插入活动数据
|
||
INSERT INTO activities (activity_type, title, description, star_id, start_time, end_time, target_progress, current_progress, status, stage_configs, created_at, updated_at)
|
||
VALUES (
|
||
'birthday',
|
||
'Lisa 生日应援活动',
|
||
'为Lisa庆祝生日，集齐水晶为她送上祝福！',
|
||
(SELECT COALESCE((SELECT star_id FROM stars WHERE identity_id = 'lisa_test_001'), (SELECT MIN(star_id) FROM stars))),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (7*24*60*60*1000),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) + (18*24*60*60*1000),
|
||
100000,
|
||
45678,
|
||
'active',
|
||
'{"cover_image": "https://example.com/lisa_cover.jpg", "banner_image": "https://example.com/lisa_banner.jpg", "stages": [{"threshold": 0, "background": "https://example.com/bg1.jpg", "title": "第一阶段"}, {"threshold": 25000, "background": "https://example.com/bg2.jpg", "title": "第二阶段"}, {"threshold": 50000, "background": "https://example.com/bg3.jpg", "title": "第三阶段"}, {"threshold": 75000, "background": "https://example.com/bg4.jpg", "title": "第四阶段"}, {"threshold": 100000, "background": "https://example.com/bg5.jpg", "title": "终极阶段"}]}'::jsonb,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
);
|
||
|
||
INSERT INTO activities (activity_type, title, description, star_id, start_time, end_time, target_progress, current_progress, status, stage_configs, created_at, updated_at)
|
||
VALUES (
|
||
'concert',
|
||
'Jennie 演唱会应援',
|
||
'支持Jennie的全球演唱会，集齐水晶为她加油！',
|
||
(SELECT COALESCE((SELECT star_id FROM stars WHERE identity_id = 'jennie_test_001'), (SELECT MIN(star_id)+1 FROM stars))),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (3*24*60*60*1000),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) + (28*24*60*60*1000),
|
||
50000,
|
||
12345,
|
||
'active',
|
||
'{"cover_image": "https://example.com/jennie_cover.jpg", "banner_image": "https://example.com/jennie_banner.jpg", "stages": [{"threshold": 0, "background": "https://example.com/concert_bg1.jpg", "title": "热身阶段"}, {"threshold": 12500, "background": "https://example.com/concert_bg2.jpg", "title": "进行中"}, {"threshold": 25000, "background": "https://example.com/concert_bg3.jpg", "title": "高潮阶段"}, {"threshold": 37500, "background": "https://example.com/concert_bg4.jpg", "title": "巅峰阶段"}, {"threshold": 50000, "background": "https://example.com/concert_bg5.jpg", "title": "圆满"}]}'::jsonb,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
);
|
||
|
||
INSERT INTO activities (activity_type, title, description, star_id, start_time, end_time, target_progress, current_progress, status, stage_configs, created_at, updated_at)
|
||
VALUES (
|
||
'bus',
|
||
'Rosé 生日巴士应援',
|
||
'为Rosé生日应援，打造专属巴士！',
|
||
(SELECT COALESCE((SELECT star_id FROM stars WHERE identity_id = 'rose_test_001'), (SELECT MAX(star_id) FROM stars))),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) + (5*24*60*60*1000),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) + (35*24*60*60*1000),
|
||
30000,
|
||
0,
|
||
'pending',
|
||
'{"cover_image": "https://example.com/rose_cover.jpg", "banner_image": "https://example.com/rose_banner.jpg", "stages": [{"threshold": 0, "background": "https://example.com/bus_bg1.jpg", "title": "筹备中"}, {"threshold": 10000, "background": "https://example.com/bus_bg2.jpg", "title": "进行中"}, {"threshold": 20000, "background": "https://example.com/bus_bg3.jpg", "title": "冲刺中"}, {"threshold": 30000, "background": "https://example.com/bus_bg4.jpg", "title": "完成"}]}'::jsonb,
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000),
|
||
(EXTRACT(EPOCH FROM NOW())::BIGINT * 1000)
|
||
);
|
||
|
||
-- 4. 插入活动道具
|
||
DO $$
|
||
DECLARE
|
||
v_ts BIGINT := EXTRACT(EPOCH FROM NOW())::BIGINT * 1000;
|
||
BEGIN
|
||
INSERT INTO activity_items (activity_id, item_type, item_name, icon_url, crystal_cost, contribution_points, sort_order, is_active, created_at, updated_at)
|
||
VALUES (1, 'flower', '小花', 'https://example.com/flower.png', 10, 10, 1, true, v_ts, v_ts),
|
||
(1, 'candle', '蜡烛', 'https://example.com/candle.png', 50, 60, 2, true, v_ts, v_ts),
|
||
(1, 'cake', '蛋糕', 'https://example.com/cake.png', 100, 130, 3, true, v_ts, v_ts),
|
||
(1, 'firework', '烟花', 'https://example.com/firework.png', 300, 400, 4, true, v_ts, v_ts),
|
||
(1, 'crown', '皇冠', 'https://example.com/crown.png', 500, 700, 5, true, v_ts, v_ts),
|
||
(2, 'lightstick', '荧光棒', 'https://example.com/lightstick.png', 20, 20, 1, true, v_ts, v_ts),
|
||
(2, 'banner', '横幅', 'https://example.com/banner.png', 100, 120, 2, true, v_ts, v_ts),
|
||
(2, 'cheer', '加油', 'https://example.com/cheer.png', 200, 260, 3, true, v_ts, v_ts),
|
||
(2, 'spotlight', '聚光灯', 'https://example.com/spotlight.png', 500, 700, 4, true, v_ts, v_ts),
|
||
(3, 'wheel', '轮子', 'https://example.com/wheel.png', 30, 30, 1, true, v_ts, v_ts),
|
||
(3, 'body', '车身', 'https://example.com/body.png', 150, 180, 2, true, v_ts, v_ts),
|
||
(3, 'ad', '广告位', 'https://example.com/ad.png', 300, 400, 3, true, v_ts, v_ts);
|
||
END $$;
|
||
|
||
-- 5. 插入用户贡献记录和统计
|
||
DO $$
|
||
DECLARE
|
||
v_user1 BIGINT;
|
||
v_user2 BIGINT;
|
||
v_user3 BIGINT;
|
||
v_user5 BIGINT;
|
||
v_star1 BIGINT;
|
||
v_star2 BIGINT;
|
||
v_ts BIGINT := EXTRACT(EPOCH FROM NOW())::BIGINT * 1000;
|
||
v_ts_1d_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (1*24*60*60*1000);
|
||
v_ts_12h_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (12*60*60*1000);
|
||
v_ts_20h_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (20*60*60*1000);
|
||
v_ts_1h_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (1*60*60*1000);
|
||
v_ts_2d_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (2*24*60*60*1000);
|
||
v_ts_14h_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (14*60*60*1000);
|
||
v_ts_30m_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (30*60*1000);
|
||
v_ts_15m_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (15*60*1000);
|
||
v_ts_5h_ago BIGINT := (EXTRACT(EPOCH FROM NOW())::BIGINT * 1000) - (5*60*60*1000);
|
||
BEGIN
|
||
SELECT id INTO v_user1 FROM users WHERE mobile = '13800000001' LIMIT 1;
|
||
SELECT id INTO v_user2 FROM users WHERE mobile = '13800000002' LIMIT 1;
|
||
SELECT id INTO v_user3 FROM users WHERE mobile = '13800000003' LIMIT 1;
|
||
SELECT id INTO v_user5 FROM users WHERE mobile = '13800000005' LIMIT 1;
|
||
|
||
SELECT star_id INTO v_star1 FROM activities WHERE id = 1 LIMIT 1;
|
||
SELECT star_id INTO v_star2 FROM activities WHERE id = 2 LIMIT 1;
|
||
|
||
INSERT INTO activity_contributions (activity_id, user_id, star_id, item_id, item_type, quantity, crystal_spent, contribution_points, created_at)
|
||
VALUES
|
||
(1, v_user1, v_star1, 1, 'flower', 5, 50, 50, v_ts_1d_ago),
|
||
(1, v_user1, v_star1, 2, 'candle', 3, 150, 180, v_ts_12h_ago),
|
||
(1, v_user1, v_star1, 4, 'firework', 2, 600, 800, v_ts),
|
||
(1, v_user2, v_star1, 3, 'cake', 5, 500, 650, v_ts_20h_ago),
|
||
(1, v_user2, v_star1, 5, 'crown', 1, 500, 700, v_ts_1h_ago),
|
||
(1, v_user3, v_star1, 1, 'flower', 10, 100, 100, v_ts_2d_ago),
|
||
(1, v_user3, v_star1, 2, 'candle', 2, 100, 120, v_ts_14h_ago),
|
||
(2, v_user1, v_star2, 1, 'lightstick', 10, 200, 200, v_ts_2d_ago),
|
||
(2, v_user1, v_star2, 2, 'banner', 3, 300, 360, v_ts_1d_ago),
|
||
(2, v_user1, v_star2, 4, 'spotlight', 1, 500, 700, v_ts),
|
||
(2, v_user2, v_star2, 3, 'cheer', 5, 1000, 1300, v_ts_5h_ago),
|
||
(1, v_user5, v_star1, 5, 'crown', 10, 5000, 7000, v_ts_30m_ago),
|
||
(1, v_user5, v_star1, 4, 'firework', 20, 6000, 8000, v_ts_15m_ago),
|
||
(1, v_user5, v_star1, 3, 'cake', 30, 3000, 3900, v_ts);
|
||
|
||
INSERT INTO activity_user_stats (activity_id, user_id, star_id, total_contribution, total_crystal_spent, total_items, last_contribute_at, created_at, updated_at)
|
||
VALUES
|
||
(1, v_user1, v_star1, 1030, 800, 10, v_ts, v_ts, v_ts),
|
||
(1, v_user2, v_star1, 1350, 1000, 6, v_ts_1h_ago, v_ts, v_ts),
|
||
(1, v_user3, v_star1, 220, 200, 2, v_ts_14h_ago, v_ts, v_ts),
|
||
(1, v_user5, v_star1, 18900, 14000, 60, v_ts, v_ts, v_ts),
|
||
(2, v_user1, v_star2, 1260, 1000, 14, v_ts, v_ts, v_ts),
|
||
(2, v_user2, v_star2, 1300, 1000, 5, v_ts_5h_ago, v_ts, v_ts);
|
||
|
||
RAISE NOTICE 'Test data inserted successfully!';
|
||
END $$;
|
||
|
||
SELECT 'activities' as table_name, COUNT(*) as count FROM activities
|
||
UNION ALL
|
||
SELECT 'activity_items', COUNT(*) FROM activity_items
|
||
UNION ALL
|
||
SELECT 'activity_contributions', COUNT(*) FROM activity_contributions
|
||
UNION ALL
|
||
SELECT 'activity_user_stats', COUNT(*) FROM activity_user_stats;
|