Impact-Graph/seed-sample-data.sql at staging · GiveVN/Impact-Graph · 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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
-- Seed sample data for Giveth development environment

-- Insert project status data if not exists
INSERT INTO project_status (id, symbol, name, description)
SELECT * FROM (VALUES
  (1, 'pending', 'pending', 'pending'),
  (2, 'clarification', 'clarification', 'clarification'),
  (3, 'verification', 'verification', 'verification'),
  (4, 'listed', 'listed', 'listed'),
  (5, 'unlisted', 'unlisted', 'unlisted')
) AS v(id, symbol, name, description)
WHERE NOT EXISTS (SELECT 1 FROM project_status WHERE id = v.id);

-- Create sample admin user if not exists
INSERT INTO "user" (
  id, "firstName", "lastName", name, "walletAddress",
  email, avatar, url, "loginType", "createdAt", "updatedAt",
  "isEmailVerified", "giveBacks", "qualityScore", role
)
SELECT * FROM (VALUES (
  999,
  'Sample',
  'Admin',
  'Sample Admin',
  '0x1234567890123456789012345678901234567890',
  'sample-admin@giveth.io',
  'https://images.unsplash.com/photo-1472099645785-5658abf4ff4e?w=400',
  NULL,
  'wallet',
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP,
  true,
  true,
  85,
  'admin'
)) AS v(id, "firstName", "lastName", name, "walletAddress", email, avatar, url, "loginType", "createdAt", "updatedAt", "isEmailVerified", "giveBacks", "qualityScore", role)
WHERE NOT EXISTS (SELECT 1 FROM "user" WHERE id = 999);

-- Create sample projects if database is empty
INSERT INTO project (
  id, title, slug, description, "descriptionSummary", image,
  "creationDate", "updatedAt", verified, "statusId", "adminUserId",
  "totalDonations", "totalReactions", "qualityScore", listed,
  "isGivbackEligible", "reviewStatus", "giveBacks"
)
SELECT * FROM (VALUES
  (
    1001,
    'Clean Water Initiative',
    'clean-water-initiative',
    'Providing clean water access to underserved communities through sustainable water filtration systems and education programs.',
    'Clean water access for underserved communities',
    'https://images.unsplash.com/photo-1582719508461-905c673771fd?w=800',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    true,
    5,
    999,
    0,
    0,
    85,
    true,
    true,
    'Listed',
    true
  ),
  (
    1002,
    'Educational Technology for Rural Schools',
    'educational-technology-rural-schools',
    'Bringing modern educational technology and internet connectivity to rural schools to bridge the digital divide.',
    'Educational technology for rural schools',
    'https://images.unsplash.com/photo-1503676260728-1c00da094a0b?w=800',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    true,
    5,
    999,
    0,
    0,
    90,
    true,
    true,
    'Listed',
    true
  ),
  (
    1003,
    'Renewable Energy for Communities',
    'renewable-energy-communities',
    'Installing solar panels and wind turbines in remote communities to provide sustainable, clean energy access.',
    'Renewable energy for remote communities',
    'https://images.unsplash.com/photo-1509391366360-2e959784a276?w=800',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    false,
    5,
    999,
    0,
    0,
    80,
    true,
    true,
    'Listed',
    true
  ),
  (
    1004,
    'Food Security Program',
    'food-security-program',
    'Supporting local farmers and food banks to ensure food security and nutrition for vulnerable populations.',
    'Food security and nutrition programs',
    'https://images.unsplash.com/photo-1488459716781-31db52582fe9?w=800',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    true,
    5,
    999,
    0,
    0,
    88,
    true,
    true,
    'Listed',
    true
  ),
  (
    1005,
    'Healthcare Access Initiative',
    'healthcare-access-initiative',
    'Providing mobile healthcare services and telemedicine access to remote and underserved areas.',
    'Mobile healthcare and telemedicine access',
    'https://images.unsplash.com/photo-1559757148-5c350d0d3c56?w=800',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    false,
    5,
    999,
    0,
    0,
    82,
    true,
    true,
    'Listed',
    true
  )
) AS v(id, title, slug, description, "descriptionSummary", image, "creationDate", "updatedAt", verified, "statusId", "adminUserId", "totalDonations", "totalReactions", "qualityScore", listed, "isGivbackEligible", "reviewStatus", "giveBacks")
WHERE NOT EXISTS (SELECT 1 FROM project WHERE id BETWEEN 1001 AND 1005);

-- Add project addresses for sample projects
INSERT INTO project_address (
  id, title, "networkId", "chainType", address, "projectId",
  "userId", "isRecipient", "createdAt", "updatedAt"
)
SELECT * FROM (VALUES
  (2001, 'Ethereum Mainnet', 1, 'EVM', '0x1111111111111111111111111111111111111111', 1001, 999, true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (2002, 'Ethereum Mainnet', 1, 'EVM', '0x2222222222222222222222222222222222222222', 1002, 999, true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (2003, 'Ethereum Mainnet', 1, 'EVM', '0x3333333333333333333333333333333333333333', 1003, 999, true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (2004, 'Ethereum Mainnet', 1, 'EVM', '0x4444444444444444444444444444444444444444', 1004, 999, true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (2005, 'Ethereum Mainnet', 1, 'EVM', '0x5555555555555555555555555555555555555555', 1005, 999, true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
) AS v(id, title, "networkId", "chainType", address, "projectId", "userId", "isRecipient", "createdAt", "updatedAt")
WHERE NOT EXISTS (SELECT 1 FROM project_address WHERE id BETWEEN 2001 AND 2005);

-- Create some sample power boosting data to populate views
INSERT INTO instant_power_balance ("userId", balance, "balanceAggregatorUpdatedAt")
SELECT * FROM (VALUES
  (999, 1000.0, CURRENT_TIMESTAMP)
) AS v("userId", balance, "balanceAggregatorUpdatedAt")
WHERE NOT EXISTS (SELECT 1 FROM instant_power_balance WHERE "userId" = 999);

INSERT INTO power_boosting (id, "userId", "projectId", percentage, "updatedAt", "createdAt")
SELECT * FROM (VALUES
  (3001, 999, 1001, 20, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (3002, 999, 1002, 25, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (3003, 999, 1003, 15, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (3004, 999, 1004, 30, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  (3005, 999, 1005, 10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
) AS v(id, "userId", "projectId", percentage, "updatedAt", "createdAt")
WHERE NOT EXISTS (SELECT 1 FROM power_boosting WHERE id BETWEEN 3001 AND 3005);

-- Refresh materialized views to include new data
REFRESH MATERIALIZED VIEW CONCURRENTLY project_instant_power_view;

-- Also refresh other power views if they exist
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'project_power_view') THEN
    REFRESH MATERIALIZED VIEW CONCURRENTLY project_power_view;
  END IF;

  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'last_snapshot_project_power_view') THEN
    REFRESH MATERIALIZED VIEW CONCURRENTLY last_snapshot_project_power_view;
  END IF;
END $$;