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