yoten.app
Yōten: A social tracker for your language learning journey built on the atproto.
brookjeynes.dev
1package db
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "log"
8 "strings"
9 "time"
10
11 "github.com/bluesky-social/indigo/atproto/syntax"
12
13 "yoten.app/api/yoten"
14 "yoten.app/internal/types"
15 "yoten.app/internal/utils"
16)
17
18var (
19 ErrSessionDescriptionTooLong = errors.New("study session description exceeds max length")
20 ErrSessionInvalidDuration = errors.New("study session duration must be positive")
21 ErrSessionInvalidDurationHours = errors.New("study session hours duration must be between 0-24")
22 ErrSessionInvalidDurationMinutes = errors.New("study session minutes duration must be between 0-60")
23 ErrSessionInvalidDurationSeconds = errors.New("study session seconds duration must be between 0-60")
24 ErrSessionMissingActivity = errors.New("study session must have an associated activity")
25 ErrSessionMissingLanguage = errors.New("study session must have an associated language")
26)
27
28type StudySession struct {
29 Did string
30 Rkey string
31 // 256 characters
32 Description string
33 Activity Activity
34 Resource *Resource
35 Language Language
36 XpGained int
37 Duration time.Duration
38 Date time.Time
39 Reactions []ReactionEvent
40 CommentCount int64
41 CreatedAt time.Time
42}
43
44func (ss StudySession) GetRkey() string {
45 return ss.Rkey
46}
47
48func (s StudySession) StudySessionAt() syntax.ATURI {
49 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", s.Did, yoten.FeedSessionNSID, s.Rkey))
50}
51
52type StudySessionFeedItem struct {
53 StudySession
54 ProfileDisplayName string
55 ProfileLevel int
56 BskyProfile types.BskyProfile
57}
58
59func GetStudySessionFeed(e Execer, limit, offset int) ([]*StudySessionFeedItem, error) {
60 query := `
61 select
62 ss.did, ss.rkey, ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
63 p.display_name, p.level,
64 x.xp_gained
65 from study_sessions as ss
66 join profiles as p on ss.did = p.did
67 left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
68 order by ss.created desc limit ? offset ?`
69 return getFeed(e, query, limit, offset)
70}
71
72func GetFriendsStudySessionFeed(e Execer, userDid string, limit, offset int) ([]*StudySessionFeedItem, error) {
73 query := `
74 select
75 ss.did, ss.rkey, ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
76 p.display_name, p.level,
77 x.xp_gained
78 from study_sessions as ss
79 join profiles as p on ss.did = p.did
80 join follows as f on ss.did = f.subject_did
81 left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
82 where f.user_did = ?
83 order by ss.created desc limit ? offset ?`
84 return getFeed(e, query, userDid, limit, offset)
85}
86
87func UpsertStudySession(e Execer, session *StudySession, rkey string) error {
88 var resourceId *int = nil
89 if session.Resource != nil {
90 resourceId = &session.Resource.ID
91 }
92
93 _, err := e.Exec(`
94 insert into study_sessions (
95 did,
96 rkey,
97 activity_id,
98 resource_id,
99 description,
100 duration,
101 language_code,
102 date,
103 created
104 )
105 values (?, ?, ?, ?, ?, ?, ?, ?, ?)
106 on conflict(did, rkey) do update set
107 activity_id = excluded.activity_id,
108 resource_id = excluded.resource_id,
109 description = excluded.description,
110 duration = excluded.duration,
111 language_code = excluded.language_code,
112 date = excluded.date`,
113 session.Did,
114 rkey,
115 session.Activity.ID,
116 resourceId,
117 session.Description,
118 session.Duration.Seconds(),
119 session.Language.Code,
120 session.Date.Format(time.RFC3339),
121 session.CreatedAt.Format(time.RFC3339),
122 )
123 if err != nil {
124 return fmt.Errorf("failed to insert or update study session: %w", err)
125 }
126
127 return nil
128}
129
130func GetStudySessionLogs(e Execer, did string, limit, offset int) ([]*StudySession, error) {
131 query := `
132 select
133 ss.did, ss.rkey, ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
134 x.xp_gained
135 from study_sessions ss
136 left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
137 where ss.did = ?
138 order by ss.created desc
139 limit ? offset ?`
140
141 rows, err := e.Query(query, did, limit, offset)
142 if err != nil {
143 return nil, fmt.Errorf("failed to query for study sessions: %w", err)
144 }
145 defer rows.Close()
146
147 var studySessions []*StudySession
148
149 for rows.Next() {
150 var language Language
151 var activity Activity = Activity{}
152 var dateStr string
153 var duration int64
154 var session StudySession
155 var createdAtStr string
156 // TODO: Are these needed? Can they be used within the structs themselves.
157 var activityId int
158 var resourceId sql.NullInt64
159 var xp_gained sql.NullInt64
160
161 err := rows.Scan(
162 &session.Did, &session.Rkey, &activityId, &resourceId,
163 &session.Description, &duration, &language.Code, &dateStr,
164 &createdAtStr, &xp_gained,
165 )
166 if err != nil {
167 return nil, fmt.Errorf("failed to scan study session row: %w", err)
168 }
169
170 session.XpGained = 0
171 if xp_gained.Valid {
172 session.XpGained = int(xp_gained.Int64)
173 }
174
175 parsedTime, err := time.Parse(time.RFC3339, dateStr)
176 if err != nil {
177 return nil, fmt.Errorf("failed to parse date string '%s': %w", dateStr, err)
178 }
179 session.Date = parsedTime
180 session.Duration = time.Duration(duration) * time.Second
181
182 language, ok := Languages[language.Code]
183 if !ok {
184 log.Printf("failed to find language '%s'", language.Code)
185 continue
186 }
187 session.Language = language
188
189 // TODO: Optimise this request - should not fetch for each session
190 activity, err = GetActivity(e, activityId)
191 if err != nil {
192 return nil, fmt.Errorf("failed to find activity '%d': %w", activityId, err)
193 }
194 session.Activity = activity
195
196 if resourceId.Valid {
197 var resource Resource = Resource{}
198 // TODO: Optimise this request - should not fetch for each session
199 resource, err = GetResource(e, int(resourceId.Int64))
200 if err != nil {
201 return nil, fmt.Errorf("failed to find resource '%d': %w", resourceId.Int64, err)
202 }
203 session.Resource = &resource
204 }
205
206 // TODO: Optimise this request - should not fetch for each session
207 reactions, err := GetReactionsForSession(e, session.Did, session.Rkey)
208 if err != nil {
209 return nil, fmt.Errorf("failed to get reactions: %w", err)
210 }
211 session.Reactions = reactions
212
213 createdAt, err := time.Parse(time.RFC3339, createdAtStr)
214 if err != nil {
215 return nil, fmt.Errorf("failed to parse createdAt string '%s': %w", createdAtStr, err)
216 }
217 session.CreatedAt = createdAt
218
219 studySessions = append(studySessions, &session)
220 }
221 if err = rows.Err(); err != nil {
222 return nil, fmt.Errorf("failed to iterate study session rows: %w", err)
223 }
224
225 uris := utils.Map(studySessions, func(session *StudySession) string {
226 return string(session.StudySessionAt())
227 })
228 commentCounts, err := GetCommentCountsForSessions(e, uris)
229 if err != nil {
230 log.Println("failed to get comment count:", err)
231 }
232 for _, item := range studySessions {
233 if count, ok := commentCounts[string(item.StudySessionAt())]; ok {
234 item.CommentCount = count
235 } else {
236 item.CommentCount = 0
237 }
238 }
239
240 return studySessions, nil
241}
242
243func DeleteStudySessionByRkey(e Execer, did string, rkey string) error {
244 _, err := e.Exec(`delete from study_sessions where did = ? and rkey = ?`, did, rkey)
245 return err
246}
247
248func GetStudySessionByRkey(e Execer, did string, rkey string) (*StudySession, error) {
249 var language Language
250 var activity Activity = Activity{}
251 var dateStr string
252 var createdAtStr string
253 var duration int64
254 var activityId int
255 var resourceId sql.NullInt64
256 var xp_gained sql.NullInt64
257
258 var session StudySession
259 session.Did = did
260 session.Rkey = rkey
261
262 err := e.QueryRow(`
263 select
264 ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
265 xp_gained
266 from study_sessions ss
267 left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
268 where ss.did = ? and ss.rkey = ?`,
269 did, rkey,
270 ).Scan(&activityId, &resourceId, &session.Description, &duration, &language.Code, &dateStr, &createdAtStr, &xp_gained)
271 if err != nil {
272 if err == sql.ErrNoRows {
273 return nil, fmt.Errorf("study session does not exist")
274 }
275 return nil, err
276 }
277
278 session.XpGained = 0
279 if xp_gained.Valid {
280 session.XpGained = int(xp_gained.Int64)
281 }
282
283 activity, err = GetActivity(e, activityId)
284 if err != nil {
285 return nil, fmt.Errorf("failed to find activity '%d': %w", activityId, err)
286 }
287 session.Activity = activity
288
289 if resourceId.Valid {
290 var resource Resource = Resource{}
291 resource, err = GetResource(e, int(resourceId.Int64))
292 if err != nil {
293 return nil, fmt.Errorf("failed to find resource '%d': %w", resourceId.Int64, err)
294 }
295 session.Resource = &resource
296 }
297
298 date, err := time.Parse(time.RFC3339, dateStr)
299 if err != nil {
300 return nil, fmt.Errorf("failed to parse date string '%s': %w", dateStr, err)
301 }
302 session.Date = date
303 session.Duration = time.Duration(duration) * time.Second
304 created, err := time.Parse(time.RFC3339, createdAtStr)
305 if err != nil {
306 return nil, fmt.Errorf("failed to parse created string '%s': %w", createdAtStr, err)
307 }
308 session.CreatedAt = created
309
310 language, ok := Languages[language.Code]
311 if !ok {
312 return nil, fmt.Errorf("failed to find language '%s'", language.Code)
313 }
314 session.Language = language
315
316 reactions, err := GetReactionsForSession(e, session.Did, session.Rkey)
317 if err != nil {
318 log.Println("failed to retrieve reactions", err)
319 }
320 session.Reactions = reactions
321
322 commentCount, err := GetCommentCountForSession(e, string(session.StudySessionAt()))
323 if err != nil {
324 log.Println("failed to retrieve comment count", err)
325 }
326 session.CommentCount = commentCount
327
328 return &session, nil
329}
330
331func ValidateStudySessionDuration(hours int64, minutes int64, seconds int64) error {
332 if hours < 0 || hours > 24 {
333 return ErrSessionInvalidDurationHours
334 }
335 if minutes < 0 || minutes > 60 {
336 return ErrSessionInvalidDurationMinutes
337 }
338 if seconds < 0 || seconds > 60 {
339 return ErrSessionInvalidDurationSeconds
340 }
341
342 return nil
343}
344
345func ValidateStudySession(session StudySession) error {
346 if len(session.Description) > 256 {
347 return ErrSessionDescriptionTooLong
348 }
349
350 if session.Duration.Seconds() <= 0 {
351 return ErrSessionInvalidDuration
352 }
353
354 if session.Activity.ID == 0 {
355 return ErrSessionMissingActivity
356 }
357
358 if session.Language.Code == "" {
359 return ErrSessionMissingLanguage
360 }
361
362 return nil
363}
364
365func scanSessionFeedItem(rows *sql.Rows) (StudySessionFeedItem, int, error) {
366 var item StudySessionFeedItem
367 var session StudySession
368 var activityId int
369 var resourceId sql.NullInt64
370 var duration int64
371 var dateStr, createdAtStr, langCode string
372 var xp_gained sql.NullInt64
373
374 err := rows.Scan(
375 &session.Did, &session.Rkey, &activityId, &resourceId, &session.Description,
376 &duration, &langCode, &dateStr, &createdAtStr, &item.ProfileDisplayName,
377 &item.ProfileLevel, &xp_gained,
378 )
379 if err != nil {
380 return StudySessionFeedItem{}, 0, err
381 }
382
383 session.XpGained = 0
384 if xp_gained.Valid {
385 session.XpGained = int(xp_gained.Int64)
386 }
387
388 session.Date, err = time.Parse(time.RFC3339, dateStr)
389 if err != nil {
390 return StudySessionFeedItem{}, 0, fmt.Errorf("failed to parse date string '%s': %w", dateStr, err)
391 }
392 session.CreatedAt, err = time.Parse(time.RFC3339, createdAtStr)
393 if err != nil {
394 return StudySessionFeedItem{}, 0, fmt.Errorf("failed to parse created at string '%s': %w", dateStr, err)
395 }
396 session.Duration = time.Duration(duration) * time.Second
397 var ok bool
398 session.Language, ok = Languages[LanguageCode(langCode)]
399 if !ok {
400 return StudySessionFeedItem{}, 0, fmt.Errorf("failed to find language '%s'", langCode)
401 }
402
403 if resourceId.Valid {
404 item.Resource = &Resource{ID: int(resourceId.Int64)}
405 }
406
407 item.StudySession = session
408 item.Activity.ID = activityId
409
410 return item, activityId, nil
411}
412
413func populateResourcesForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
414 if len(feedItems) == 0 {
415 return nil
416 }
417
418 sessionMap := make(map[string]*StudySessionFeedItem)
419 var keyArgs []any
420 var placeholders []string
421 for _, item := range feedItems {
422 compositeKey := item.Did + ":" + item.Rkey
423 sessionMap[compositeKey] = item
424 placeholders = append(placeholders, "(?, ?)")
425 keyArgs = append(keyArgs, item.Did, item.Rkey)
426 }
427
428 query := fmt.Sprintf(`
429 select
430 ss.did,
431 ss.rkey,
432 ur.id,
433 ur.title,
434 ur.author,
435 ur.type,
436 ur.link
437 from
438 study_sessions ss
439 join
440 resources ur on ss.resource_id = ur.id
441 where
442 (ss.did, ss.rkey) in (%s)
443 `, strings.Join(placeholders, ","))
444
445 rows, err := e.Query(query, keyArgs...)
446 if err != nil {
447 return fmt.Errorf("failed to query for session resources: %w", err)
448 }
449 defer rows.Close()
450
451 for rows.Next() {
452 var sessionDid, sessionRkey string
453 var resource Resource
454 var resourceTypeStr string
455 var link sql.NullString
456
457 err := rows.Scan(
458 &sessionDid, &sessionRkey,
459 &resource.ID, &resource.Title, &resource.Author, &resourceTypeStr, &link,
460 )
461 if err != nil {
462 return fmt.Errorf("failed to scan session resource: %w", err)
463 }
464
465 if link.Valid {
466 resource.Link = ToPtr(link.String)
467 }
468
469 resourceType, err := ResourceTypeFromString(resourceTypeStr)
470 if err != nil {
471 return fmt.Errorf("failed to parse resource type string '%s': %w", resourceTypeStr, err)
472 }
473 resource.Type = resourceType
474
475 compositeKey := sessionDid + ":" + sessionRkey
476 if session, ok := sessionMap[compositeKey]; ok {
477 session.StudySession.Resource = &resource
478 }
479 }
480 return rows.Err()
481}
482
483func populateActivitiesForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
484 if len(feedItems) == 0 {
485 return nil
486 }
487
488 activityIDs := make([]int, 0, len(feedItems))
489 for _, item := range feedItems {
490 activityIDs = append(activityIDs, item.Activity.ID)
491 }
492
493 activities, err := GetActivitiesByIDs(e, activityIDs)
494 if err != nil {
495 return fmt.Errorf("failed to get activities: %w", err)
496 }
497
498 activitiesMap := make(map[int]Activity)
499 for _, a := range activities {
500 activitiesMap[a.ID] = a
501 }
502
503 for _, item := range feedItems {
504 if activity, ok := activitiesMap[item.Activity.ID]; ok {
505 item.StudySession.Activity = activity
506 }
507 }
508
509 return nil
510}
511
512func populateReactionsForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
513 if len(feedItems) == 0 {
514 return nil
515 }
516
517 sessionMap := make(map[string]*StudySessionFeedItem)
518 var keyArgs []any
519 var placeholders []string
520 for _, item := range feedItems {
521 compositeKey := item.Did + ":" + item.Rkey
522 sessionMap[compositeKey] = item
523 placeholders = append(placeholders, "(?, ?)")
524 keyArgs = append(keyArgs, item.Did, item.Rkey)
525 }
526
527 query := fmt.Sprintf(`
528 select session_did, session_rkey, id, did, rkey, reaction_id
529 from study_session_reactions
530 where (session_did, session_rkey) in (%s)
531 `, strings.Join(placeholders, ","))
532
533 rows, err := e.Query(query, keyArgs...)
534 if err != nil {
535 return fmt.Errorf("failed to query for session reactions: %w", err)
536 }
537 defer rows.Close()
538
539 for rows.Next() {
540 var event ReactionEvent
541 err := rows.Scan(
542 &event.SessionDid, &event.SessionRkey, &event.ID, &event.Did,
543 &event.Rkey, &event.Reaction.ID,
544 )
545 if err != nil {
546 return fmt.Errorf("failed to scan reaction event: %w", err)
547 }
548 reaction, ok := Reactions[event.Reaction.ID.String()]
549 if !ok {
550 log.Println("failed to find reaction")
551 continue
552 }
553 event.Reaction = reaction
554 compositeKey := event.SessionDid + ":" + event.SessionRkey
555 if session, ok := sessionMap[compositeKey]; ok {
556 session.Reactions = append(session.Reactions, event)
557 }
558 }
559 return rows.Err()
560}
561
562func populateCommentsForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
563 if len(feedItems) == 0 {
564 return nil
565 }
566
567 uris := utils.Map(feedItems, func(item *StudySessionFeedItem) string {
568 return item.StudySessionAt().String()
569 })
570 commentCounts, err := GetCommentCountsForSessions(e, uris)
571 if err != nil {
572 return fmt.Errorf("failed to get comment counts: %w", err)
573 }
574
575 for _, item := range feedItems {
576 if count, ok := commentCounts[string(item.StudySessionAt())]; ok {
577 item.CommentCount = count
578 } else {
579 item.CommentCount = 0
580 }
581 }
582
583 return nil
584}
585
586func getFeed(e Execer, query string, args ...any) ([]*StudySessionFeedItem, error) {
587 rows, err := e.Query(query, args...)
588 if err != nil {
589 return nil, err
590 }
591 defer rows.Close()
592
593 var feedItems []*StudySessionFeedItem
594 for rows.Next() {
595 item, _, err := scanSessionFeedItem(rows)
596 if err != nil {
597 log.Printf("failed to scan study session row: %v", err)
598 continue
599 }
600 feedItems = append(feedItems, &item)
601 }
602 if err := rows.Err(); err != nil {
603 return nil, err
604 }
605
606 if err := populateActivitiesForFeed(e, feedItems); err != nil {
607 return nil, err
608 }
609
610 if err := populateReactionsForFeed(e, feedItems); err != nil {
611 return nil, err
612 }
613
614 if err := populateResourcesForFeed(e, feedItems); err != nil {
615 return nil, err
616 }
617
618 if err := populateCommentsForFeed(e, feedItems); err != nil {
619 return nil, err
620 }
621
622 return feedItems, nil
623}