dunkirk.sh
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 CreatedAt time.Time
16}
17
18func GetPrimaryEmail(e Execer, did string) (Email, error) {
19 query := `
20 select id, did, email, verified, is_primary, verification_code, created
21 from emails
22 where did = ? and is_primary = true
23 `
24 var email Email
25 var createdStr string
26 err := e.QueryRow(query, did).Scan(&email.ID, &email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &createdStr)
27 if err != nil {
28 return Email{}, err
29 }
30 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
31 if err != nil {
32 return Email{}, err
33 }
34 return email, nil
35}
36
37func GetEmail(e Execer, did string, em string) (Email, error) {
38 query := `
39 select id, did, email, verified, is_primary, verification_code, created
40 from emails
41 where did = ? and email = ?
42 `
43 var email Email
44 var createdStr string
45 err := e.QueryRow(query, did, em).Scan(&email.ID, &email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &createdStr)
46 if err != nil {
47 return Email{}, err
48 }
49 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
50 if err != nil {
51 return Email{}, err
52 }
53 return email, nil
54}
55
56func GetDidForEmail(e Execer, em string) (string, error) {
57 query := `
58 select did
59 from emails
60 where email = ?
61 `
62 var did string
63 err := e.QueryRow(query, em).Scan(&did)
64 if err != nil {
65 return "", err
66 }
67 return did, nil
68}
69
70func GetEmailToDid(e Execer, ems []string) (map[string]string, error) {
71 if len(ems) == 0 {
72 return make(map[string]string), nil
73 }
74
75 // Create placeholders for the IN clause
76 placeholders := make([]string, len(ems))
77 args := make([]any, len(ems))
78 for i, em := range ems {
79 placeholders[i] = "?"
80 args[i] = em
81 }
82
83 query := `
84 select email, did
85 from emails
86 where email in (` + strings.Join(placeholders, ",") + `)
87 `
88
89 rows, err := e.Query(query, args...)
90 if err != nil {
91 return nil, err
92 }
93 defer rows.Close()
94
95 assoc := make(map[string]string)
96
97 for rows.Next() {
98 var email, did string
99 if err := rows.Scan(&email, &did); err != nil {
100 return nil, err
101 }
102 assoc[email] = did
103 }
104
105 if err := rows.Err(); err != nil {
106 return nil, err
107 }
108
109 return assoc, nil
110}
111
112func GetVerificationCodeForEmail(e Execer, did string, email string) (string, error) {
113 query := `
114 select verification_code
115 from emails
116 where did = ? and email = ?
117 `
118 var code string
119 err := e.QueryRow(query, did, email).Scan(&code)
120 if err != nil {
121 return "", err
122 }
123 return code, nil
124}
125
126func CheckEmailExists(e Execer, did string, email string) (bool, error) {
127 query := `
128 select count(*)
129 from emails
130 where did = ? and email = ?
131 `
132 var count int
133 err := e.QueryRow(query, did, email).Scan(&count)
134 if err != nil {
135 return false, err
136 }
137 return count > 0, nil
138}
139
140func CheckValidVerificationCode(e Execer, did string, email string, code string) (bool, error) {
141 query := `
142 select count(*)
143 from emails
144 where did = ? and email = ? and verification_code = ?
145 `
146 var count int
147 err := e.QueryRow(query, did, email, code).Scan(&count)
148 if err != nil {
149 return false, err
150 }
151 return count > 0, nil
152}
153
154func AddEmail(e Execer, email Email) error {
155 // Check if this is the first email for this DID
156 countQuery := `
157 select count(*)
158 from emails
159 where did = ?
160 `
161 var count int
162 err := e.QueryRow(countQuery, email.Did).Scan(&count)
163 if err != nil {
164 return err
165 }
166
167 // If this is the first email, mark it as primary
168 if count == 0 {
169 email.Primary = true
170 }
171
172 query := `
173 insert into emails (did, email, verified, is_primary, verification_code)
174 values (?, ?, ?, ?, ?)
175 `
176 _, err = e.Exec(query, email.Did, email.Address, email.Verified, email.Primary, email.VerificationCode)
177 return err
178}
179
180func DeleteEmail(e Execer, did string, email string) error {
181 query := `
182 delete from emails
183 where did = ? and email = ?
184 `
185 _, err := e.Exec(query, did, email)
186 return err
187}
188
189func MarkEmailVerified(e Execer, did string, email string) error {
190 query := `
191 update emails
192 set verified = true
193 where did = ? and email = ?
194 `
195 _, err := e.Exec(query, did, email)
196 return err
197}
198
199func MakeEmailPrimary(e Execer, did string, email string) error {
200 // First, unset all primary emails for this DID
201 query1 := `
202 update emails
203 set is_primary = false
204 where did = ?
205 `
206 _, err := e.Exec(query1, did)
207 if err != nil {
208 return err
209 }
210
211 // Then, set the specified email as primary
212 query2 := `
213 update emails
214 set is_primary = true
215 where did = ? and email = ?
216 `
217 _, err = e.Exec(query2, did, email)
218 return err
219}
220
221func GetAllEmails(e Execer, did string) ([]Email, error) {
222 query := `
223 select did, email, verified, is_primary, verification_code, created
224 from emails
225 where did = ?
226 `
227 rows, err := e.Query(query, did)
228 if err != nil {
229 return nil, err
230 }
231 defer rows.Close()
232
233 var emails []Email
234 for rows.Next() {
235 var email Email
236 var createdStr string
237 err := rows.Scan(&email.Did, &email.Address, &email.Verified, &email.Primary, &email.VerificationCode, &createdStr)
238 if err != nil {
239 return nil, err
240 }
241 email.CreatedAt, err = time.Parse(time.RFC3339, createdStr)
242 if err != nil {
243 return nil, err
244 }
245 emails = append(emails, email)
246 }
247 return emails, nil
248}