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