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