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 CheckValidVerificationCode(e Execer, did string, email string, code string) (bool, error) {
163 query := `
164 select count(*)
165 from emails
166 where did = ? and email = ? and verification_code = ?
167 `
168 var count int
169 err := e.QueryRow(query, did, email, code).Scan(&count)
170 if err != nil {
171 return false, err
172 }
173 return count > 0, nil
174}
175
176func AddEmail(e Execer, email Email) error {
177 // Check if this is the first email for this DID
178 countQuery := `
179 select count(*)
180 from emails
181 where did = ?
182 `
183 var count int
184 err := e.QueryRow(countQuery, email.Did).Scan(&count)
185 if err != nil {
186 return err
187 }
188
189 // If this is the first email, mark it as primary
190 if count == 0 {
191 email.Primary = true
192 }
193
194 query := `
195 insert into emails (did, email, verified, is_primary, verification_code)
196 values (?, ?, ?, ?, ?)
197 `
198 _, err = e.Exec(query, email.Did, email.Address, email.Verified, email.Primary, email.VerificationCode)
199 return err
200}
201
202func DeleteEmail(e Execer, did string, email string) error {
203 query := `
204 delete from emails
205 where did = ? and email = ?
206 `
207 _, err := e.Exec(query, did, email)
208 return err
209}
210
211func MarkEmailVerified(e Execer, did string, email string) error {
212 query := `
213 update emails
214 set verified = true
215 where did = ? and email = ?
216 `
217 _, err := e.Exec(query, did, email)
218 return err
219}
220
221func MakeEmailPrimary(e Execer, did string, email string) error {
222 // First, unset all primary emails for this DID
223 query1 := `
224 update emails
225 set is_primary = false
226 where did = ?
227 `
228 _, err := e.Exec(query1, did)
229 if err != nil {
230 return err
231 }
232
233 // Then, set the specified email as primary
234 query2 := `
235 update emails
236 set is_primary = true
237 where did = ? and email = ?
238 `
239 _, err = e.Exec(query2, did, email)
240 return err
241}
242
243func GetAllEmails(e Execer, did string) ([]Email, error) {
244 query := `
245 select did, email, verified, is_primary, verification_code, last_sent, created
246 from emails
247 where did = ?
248 `
249 rows, err := e.Query(query, did)
250 if err != nil {
251 return nil, err
252 }
253 defer rows.Close()
254
255 var emails []Email
256 for rows.Next() {
257 var email Email
258 var createdStr string
259 var lastSent string
260 err := rows.Scan(&email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &lastSent, &createdStr)
261 if err != nil {
262 return nil, err
263 }
264 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
265 if err != nil {
266 return nil, err
267 }
268 parsedTime, err := time.Parse(time.RFC3339, lastSent)
269 if err != nil {
270 return nil, err
271 }
272 email.LastSent = &parsedTime
273 emails = append(emails, email)
274 }
275 return emails, nil
276}
277
278func UpdateVerificationCode(e Execer, did string, email string, code string) error {
279 query := `
280 update emails
281 set verification_code = ?,
282 last_sent = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
283 where did = ? and email = ?
284 `
285 _, err := e.Exec(query, code, did, email)
286 return err
287}