GrowthLoop/supabase/seed.sql at main · tinylion1024/GrowthLoop · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
-- Deterministic demo data. Safe to re-run after the initial migration.
-- The generated series creates 240 events (plus conversions/referrals) spanning 30 days.

insert into public.campaigns (
  id, slug, name, description, headline, status, destination_url, reward_config, starts_at
) values (
  '10000000-0000-4000-8000-000000000001',
  'ai-coding-meetup',
  'AI Coding 线下分享会',
  '通过内容分享与好友邀请，让更多开发者相聚在线下交流 AI Coding 实践。',
  '与一线实践者面对面交流，并获得完整案例资料',
  'active',
  'https://growthloop.example.com/join',
  '{"type":"membership_days","referrer":7,"referred":7}',
  now() - interval '45 days'
) on conflict (id) do update set
  name = excluded.name, description = excluded.description, headline = excluded.headline,
  status = excluded.status, destination_url = excluded.destination_url,
  reward_config = excluded.reward_config, starts_at = excluded.starts_at;

insert into public.visitors (id, campaign_id, anonymous_id, external_user_id, email, first_seen_at, last_seen_at, first_touch, last_touch)
select
  ('20000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  '10000000-0000-4000-8000-000000000001',
  'demo-visitor-' || n,
  case when n <= 8 then 'user-' || n end,
  case when n <= 8 then 'demo+' || n || '@example.com' end,
  now() - make_interval(days => 31 - n),
  now() - make_interval(hours => n),
  jsonb_build_object('source', case when n % 3 = 0 then 'linkedin' when n % 3 = 1 then 'newsletter' else 'referral' end),
  jsonb_build_object('source', case when n % 2 = 0 then 'referral' else 'direct' end)
from generate_series(1, 30) n
on conflict (campaign_id, anonymous_id) do update set
  external_user_id = excluded.external_user_id, email = excluded.email,
  first_seen_at = excluded.first_seen_at, last_seen_at = excluded.last_seen_at,
  first_touch = excluded.first_touch, last_touch = excluded.last_touch;

insert into public.participants (id, campaign_id, visitor_id, referral_code, display_name, email, status)
select
  ('30000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  '10000000-0000-4000-8000-000000000001',
  ('20000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  'DEMO' || lpad(n::text, 2, '0'),
  (array['林一', '陈夏', '周舟', '安然', '苏木', '小满', '程野', '顾北', '江晚', '陆遥', '白露', '乔伊', '宋青', '许愿', '唐果', '沈星', '温言', '叶子', '方糖', '简宁'])[n],
  'demo+' || n || '@example.com',
  'active'
from generate_series(1, 20) n
on conflict (id) do update set display_name = excluded.display_name, email = excluded.email, status = excluded.status;

insert into public.tracking_links (id, campaign_id, participant_id, code, destination_url, source, medium, content, click_count)
select
  ('40000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  '10000000-0000-4000-8000-000000000001',
  ('30000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  'share-demo-' || n,
  'https://growthloop.example.com/join',
  (array['wechat', 'xiaohongshu', 'douyin', 'newsletter', 'website'])[n],
  (array['social', 'social', 'video', 'email', 'direct'])[n],
  'participant-' || n,
  18 + n * 7
from generate_series(1, 5) n
on conflict (id) do update set click_count = excluded.click_count, source = excluded.source, content = excluded.content;

insert into public.events (
  id, campaign_id, visitor_id, tracking_link_id, event_name, session_id,
  idempotency_key, page_url, referrer_url, properties, occurred_at
)
select
  ('50000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  '10000000-0000-4000-8000-000000000001',
  ('20000000-0000-4000-8000-' || lpad((((n - 1) % 30) + 1)::text, 12, '0'))::uuid,
  case when n % 4 <> 0 then ('40000000-0000-4000-8000-' || lpad((((n - 1) % 5) + 1)::text, 12, '0'))::uuid end,
  (array['page_view', 'cta_click', 'form_submit', 'creative_view'])[((n - 1) % 4) + 1],
  'demo-session-' || ((n - 1) / 3 + 1),
  'demo-event-' || n,
  'https://growthloop.example.com/join',
  case when n % 3 = 0 then 'https://www.xiaohongshu.com/' else 'https://growthloop.example.com/' end,
  jsonb_build_object('device', case when n % 2 = 0 then 'mobile' else 'desktop' end, 'demo', true),
  now() - make_interval(hours => (240 - n) * 3)
from generate_series(1, 240) n
on conflict (id) do update set occurred_at = excluded.occurred_at, properties = excluded.properties;

insert into public.conversions (
  id, campaign_id, visitor_id, event_id, external_id, conversion_type, status,
  value, currency, first_touch_link_id, last_touch_link_id, converted_at
)
select
  ('60000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  '10000000-0000-4000-8000-000000000001',
  ('20000000-0000-4000-8000-' || lpad((n + 5)::text, 12, '0'))::uuid,
  ('50000000-0000-4000-8000-' || lpad((n * 4 - 1)::text, 12, '0'))::uuid,
  'demo-conversion-' || n,
  case when n % 3 = 0 then 'purchase' else 'signup' end,
  'confirmed',
  case when n % 3 = 0 then 49 else 0 end,
  'USD',
  ('40000000-0000-4000-8000-' || lpad((((n - 1) % 5) + 1)::text, 12, '0'))::uuid,
  ('40000000-0000-4000-8000-' || lpad(((n % 5) + 1)::text, 12, '0'))::uuid,
  now() - make_interval(days => 15 - n)
from generate_series(1, 14) n
on conflict (id) do update set status = excluded.status, value = excluded.value, converted_at = excluded.converted_at;

insert into public.referrals (
  id, campaign_id, referrer_participant_id, referred_visitor_id, referred_participant_id, tracking_link_id,
  conversion_id, status, reward_amount, rewarded_at
)
select
  ('70000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid,
  '10000000-0000-4000-8000-000000000001',
  ('30000000-0000-4000-8000-' || lpad((((n - 1) % 5) + 1)::text, 12, '0'))::uuid,
  ('20000000-0000-4000-8000-' || lpad((n + 5)::text, 12, '0'))::uuid,
  ('30000000-0000-4000-8000-' || lpad((n + 5)::text, 12, '0'))::uuid,
  ('40000000-0000-4000-8000-' || lpad((((n - 1) % 5) + 1)::text, 12, '0'))::uuid,
  case when n <= 14 then ('60000000-0000-4000-8000-' || lpad(n::text, 12, '0'))::uuid end,
  case when n <= 7 then 'rewarded' when n <= 12 then 'qualified' else 'invited' end,
  case when n <= 7 then 7 else 0 end,
  case when n <= 7 then now() - make_interval(days => 10 - n) end
from generate_series(1, 15) n
on conflict (id) do update set status = excluded.status, reward_amount = excluded.reward_amount, rewarded_at = excluded.rewarded_at;

insert into public.creative_assets (
  id, campaign_id, name, asset_type, status, public_url, prompt, content, metadata
) values
  ('80000000-0000-4000-8000-000000000001', '10000000-0000-4000-8000-000000000001', '盛夏好友礼', 'image', 'ready', 'https://images.unsplash.com/photo-1500530855697-b586d89ba3ee', '明亮活力的夏日好友邀请海报', null, '{"channel":"wechat","format":"poster","demo":true}'),
  ('80000000-0000-4000-8000-000000000002', '10000000-0000-4000-8000-000000000001', '三步解锁增长', 'social', 'ready', null, '自然真实的小红书种草文案', '三步参与 AI Coding 线下分享会，分享专属链接，与朋友一起解锁完整案例资料。', '{"channel":"xiaohongshu","format":"copy","demo":true}'),
  ('80000000-0000-4000-8000-000000000003', '10000000-0000-4000-8000-000000000001', '15 秒挑战', 'video', 'ready', null, '15 秒竖屏邀请挑战脚本', '开场展示效率痛点，结尾邀请好友领取 7 天会员。', '{"channel":"douyin","format":"video","demo":true}'),
  ('80000000-0000-4000-8000-000000000004', '10000000-0000-4000-8000-000000000001', '老朋友专属回归礼', 'email', 'ready', null, '温暖克制的用户召回邮件', '这个夏天，邀请一位朋友回来，你们都将获得 7 天会员。', '{"channel":"email","format":"email","demo":true}'),
  ('80000000-0000-4000-8000-000000000005', '10000000-0000-4000-8000-000000000001', '真实用户故事', 'image', 'ready', 'https://images.unsplash.com/photo-1522202176988-66273c2fd55f', '真实用户协作场景海报', null, '{"channel":"wechat","format":"poster","demo":true}'),
  ('80000000-0000-4000-8000-000000000006', '10000000-0000-4000-8000-000000000001', '效率玩家的一天', 'video', 'ready', null, '效率工具使用场景短视频', '从早晨计划到晚间复盘，用一天展示产品价值。', '{"channel":"douyin","format":"video","demo":true}'),
  ('80000000-0000-4000-8000-000000000007', '10000000-0000-4000-8000-000000000001', '本周灵感清单', 'social', 'ready', null, '清单体小红书文案', '本周 5 个增长灵感，最后一个可以和朋友一起完成。', '{"channel":"xiaohongshu","format":"copy","demo":true}'),
  ('80000000-0000-4000-8000-000000000008', '10000000-0000-4000-8000-000000000001', '最后 48 小时', 'email', 'draft', null, '活动倒计时邮件', 'AI Coding 线下分享会报名即将结束，别忘了使用你的专属邀请链接。', '{"channel":"email","format":"email","demo":true}')
on conflict (id) do update set name = excluded.name, status = excluded.status, content = excluded.content, metadata = excluded.metadata;

-- Keep cached demo counters aligned with the deterministic referral rows.
update public.participants p set
  referral_count = x.referral_count,
  conversion_count = x.conversion_count,
  reward_total = x.reward_total
from (
  select referrer_participant_id,
    count(*) filter (where status in ('qualified', 'rewarded'))::integer as referral_count,
    count(conversion_id)::integer as conversion_count,
    coalesce(sum(reward_amount) filter (where status = 'rewarded'), 0) as reward_total
  from public.referrals group by referrer_participant_id
) x where p.id = x.referrer_participant_id;