veryroundbird.house
1package db
2
3import (
4 "strings"
5 "time"
6)
7
8type Email struct {
9 ID int64
10 Did string
11 Address string
12 Verified bool
13 Primary bool
14 VerificationCode string
15 LastSent *time.Time
16 CreatedAt time.Time
17}
18
19func GetPrimaryEmail(e Execer, did string) (Email, error) {
20 query := `
21 select id, did, email, verified, is_primary, verification_code, last_sent, created
22 from emails
23 where did = ? and is_primary = true
24 `
25 var email Email
26 var createdStr string
27 var lastSent *string
28 err := e.QueryRow(query, did).Scan(&email.ID, &email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &lastSent, &createdStr)
29 if err != nil {
30 return Email{}, err
31 }
32 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
33 if err != nil {
34 return Email{}, err
35 }
36 if lastSent != nil {
37 parsedTime, err := time.Parse(time.RFC3339, *lastSent)
38 if err != nil {
39 return Email{}, err
40 }
41 email.LastSent = &parsedTime
42 }
43 return email, nil
44}
45
46func GetEmail(e Execer, did string, em string) (Email, error) {
47 query := `
48 select id, did, email, verified, is_primary, verification_code, last_sent, created
49 from emails
50 where did = ? and email = ?
51 `
52 var email Email
53 var createdStr string
54 var lastSent *string
55 err := e.QueryRow(query, did, em).Scan(&email.ID, &email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &lastSent, &createdStr)
56 if err != nil {
57 return Email{}, err
58 }
59 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
60 if err != nil {
61 return Email{}, err
62 }
63 if lastSent != nil {
64 parsedTime, err := time.Parse(time.RFC3339, *lastSent)
65 if err != nil {
66 return Email{}, err
67 }
68 email.LastSent = &parsedTime
69 }
70 return email, nil
71}
72
73func GetDidForEmail(e Execer, em string) (string, error) {
74 query := `
75 select did
76 from emails
77 where email = ?
78 `
79 var did string
80 err := e.QueryRow(query, em).Scan(&did)
81 if err != nil {
82 return "", err
83 }
84 return did, nil
85}
86
87func GetEmailToDid(e Execer, ems []string, isVerifiedFilter bool) (map[string]string, error) {
88 if len(ems) == 0 {
89 return make(map[string]string), nil
90 }
91
92 verifiedFilter := 0
93 if isVerifiedFilter {
94 verifiedFilter = 1
95 }
96
97 // Create placeholders for the IN clause
98 placeholders := make([]string, len(ems))
99 args := make([]any, len(ems)+1)
100
101 args[0] = verifiedFilter
102 for i, em := range ems {
103 placeholders[i] = "?"
104 args[i+1] = em
105 }
106
107 query := `
108 select email, did
109 from emails
110 where
111 verified = ?
112 and email in (` + strings.Join(placeholders, ",") + `)
113 `
114
115 rows, err := e.Query(query, args...)
116 if err != nil {
117 return nil, err
118 }
119 defer rows.Close()
120
121 assoc := make(map[string]string)
122
123 for rows.Next() {
124 var email, did string
125 if err := rows.Scan(&email, &did); err != nil {
126 return nil, err
127 }
128 assoc[email] = did
129 }
130
131 if err := rows.Err(); err != nil {
132 return nil, err
133 }
134
135 return assoc, nil
136}
137
138func GetVerificationCodeForEmail(e Execer, did string, email string) (string, error) {
139 query := `
140 select verification_code
141 from emails
142 where did = ? and email = ?
143 `
144 var code string
145 err := e.QueryRow(query, did, email).Scan(&code)
146 if err != nil {
147 return "", err
148 }
149 return code, nil
150}
151
152func CheckEmailExists(e Execer, did string, email string) (bool, error) {
153 query := `
154 select count(*)
155 from emails
156 where did = ? and email = ?
157 `
158 var count int
159 err := e.QueryRow(query, did, email).Scan(&count)
160 if err != nil {
161 return false, err
162 }
163 return count > 0, nil
164}
165
166func CheckValidVerificationCode(e Execer, did string, email string, code string) (bool, error) {
167 query := `
168 select count(*)
169 from emails
170 where did = ? and email = ? and verification_code = ?
171 `
172 var count int
173 err := e.QueryRow(query, did, email, code).Scan(&count)
174 if err != nil {
175 return false, err
176 }
177 return count > 0, nil
178}
179
180func AddEmail(e Execer, email Email) error {
181 // Check if this is the first email for this DID
182 countQuery := `
183 select count(*)
184 from emails
185 where did = ?
186 `
187 var count int
188 err := e.QueryRow(countQuery, email.Did).Scan(&count)
189 if err != nil {
190 return err
191 }
192
193 // If this is the first email, mark it as primary
194 if count == 0 {
195 email.Primary = true
196 }
197
198 query := `
199 insert into emails (did, email, verified, is_primary, verification_code)
200 values (?, ?, ?, ?, ?)
201 `
202 _, err = e.Exec(query, email.Did, email.Address, email.Verified, email.Primary, email.VerificationCode)
203 return err
204}
205
206func DeleteEmail(e Execer, did string, email string) error {
207 query := `
208 delete from emails
209 where did = ? and email = ?
210 `
211 _, err := e.Exec(query, did, email)
212 return err
213}
214
215func MarkEmailVerified(e Execer, did string, email string) error {
216 query := `
217 update emails
218 set verified = true
219 where did = ? and email = ?
220 `
221 _, err := e.Exec(query, did, email)
222 return err
223}
224
225func MakeEmailPrimary(e Execer, did string, email string) error {
226 // First, unset all primary emails for this DID
227 query1 := `
228 update emails
229 set is_primary = false
230 where did = ?
231 `
232 _, err := e.Exec(query1, did)
233 if err != nil {
234 return err
235 }
236
237 // Then, set the specified email as primary
238 query2 := `
239 update emails
240 set is_primary = true
241 where did = ? and email = ?
242 `
243 _, err = e.Exec(query2, did, email)
244 return err
245}
246
247func GetAllEmails(e Execer, did string) ([]Email, error) {
248 query := `
249 select did, email, verified, is_primary, verification_code, last_sent, created
250 from emails
251 where did = ?
252 `
253 rows, err := e.Query(query, did)
254 if err != nil {
255 return nil, err
256 }
257 defer rows.Close()
258
259 var emails []Email
260 for rows.Next() {
261 var email Email
262 var createdStr string
263 var lastSent *string
264 err := rows.Scan(&email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &lastSent, &createdStr)
265 if err != nil {
266 return nil, err
267 }
268 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
269 if err != nil {
270 return nil, err
271 }
272 if lastSent != nil {
273 parsedTime, err := time.Parse(time.RFC3339, *lastSent)
274 if err != nil {
275 return nil, err
276 }
277 email.LastSent = &parsedTime
278 }
279 emails = append(emails, email)
280 }
281 return emails, nil
282}
283
284func UpdateVerificationCode(e Execer, did string, email string, code string) error {
285 query := `
286 update emails
287 set verification_code = ?
288 where did = ? and email = ?
289 `
290 _, err := e.Exec(query, code, did, email)
291 return err
292}
293
294func UpdateLastSent(e Execer, did string, email string, lastSent time.Time) error {
295 query := `
296 update emails
297 set last_sent = ?
298 where did = ? and email = ?
299 `
300 _, err := e.Exec(query, lastSent.Format(time.RFC3339), did, email)
301 return err
302}