veryroundbird.house
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "net/url"
8 "slices"
9 "strings"
10 "time"
11
12 "github.com/bluesky-social/indigo/atproto/syntax"
13 "tangled.sh/tangled.sh/core/api/tangled"
14)
15
16type RepoEvent struct {
17 Repo *Repo
18 Source *Repo
19}
20
21type ProfileTimeline struct {
22 ByMonth []ByMonth
23}
24
25func (p *ProfileTimeline) IsEmpty() bool {
26 if p == nil {
27 return true
28 }
29
30 for _, m := range p.ByMonth {
31 if !m.IsEmpty() {
32 return false
33 }
34 }
35
36 return true
37}
38
39type ByMonth struct {
40 RepoEvents []RepoEvent
41 IssueEvents IssueEvents
42 PullEvents PullEvents
43}
44
45func (b ByMonth) IsEmpty() bool {
46 return len(b.RepoEvents) == 0 &&
47 len(b.IssueEvents.Items) == 0 &&
48 len(b.PullEvents.Items) == 0
49}
50
51type IssueEvents struct {
52 Items []*Issue
53}
54
55type IssueEventStats struct {
56 Open int
57 Closed int
58}
59
60func (i IssueEvents) Stats() IssueEventStats {
61 var open, closed int
62 for _, issue := range i.Items {
63 if issue.Open {
64 open += 1
65 } else {
66 closed += 1
67 }
68 }
69
70 return IssueEventStats{
71 Open: open,
72 Closed: closed,
73 }
74}
75
76type PullEvents struct {
77 Items []*Pull
78}
79
80func (p PullEvents) Stats() PullEventStats {
81 var open, merged, closed int
82 for _, pull := range p.Items {
83 switch pull.State {
84 case PullOpen:
85 open += 1
86 case PullMerged:
87 merged += 1
88 case PullClosed:
89 closed += 1
90 }
91 }
92
93 return PullEventStats{
94 Open: open,
95 Merged: merged,
96 Closed: closed,
97 }
98}
99
100type PullEventStats struct {
101 Closed int
102 Open int
103 Merged int
104}
105
106const TimeframeMonths = 7
107
108func MakeProfileTimeline(e Execer, forDid string) (*ProfileTimeline, error) {
109 timeline := ProfileTimeline{
110 ByMonth: make([]ByMonth, TimeframeMonths),
111 }
112 currentMonth := time.Now().Month()
113 timeframe := fmt.Sprintf("-%d months", TimeframeMonths)
114
115 pulls, err := GetPullsByOwnerDid(e, forDid, timeframe)
116 if err != nil {
117 return nil, fmt.Errorf("error getting pulls by owner did: %w", err)
118 }
119
120 // group pulls by month
121 for _, pull := range pulls {
122 pullMonth := pull.Created.Month()
123
124 if currentMonth-pullMonth >= TimeframeMonths {
125 // shouldn't happen; but times are weird
126 continue
127 }
128
129 idx := currentMonth - pullMonth
130 items := &timeline.ByMonth[idx].PullEvents.Items
131
132 *items = append(*items, &pull)
133 }
134
135 issues, err := GetIssues(
136 e,
137 FilterEq("did", forDid),
138 FilterGte("created", time.Now().AddDate(0, -TimeframeMonths, 0)),
139 )
140 if err != nil {
141 return nil, fmt.Errorf("error getting issues by owner did: %w", err)
142 }
143
144 for _, issue := range issues {
145 issueMonth := issue.Created.Month()
146
147 if currentMonth-issueMonth >= TimeframeMonths {
148 // shouldn't happen; but times are weird
149 continue
150 }
151
152 idx := currentMonth - issueMonth
153 items := &timeline.ByMonth[idx].IssueEvents.Items
154
155 *items = append(*items, &issue)
156 }
157
158 repos, err := GetRepos(e, 0, FilterEq("did", forDid))
159 if err != nil {
160 return nil, fmt.Errorf("error getting all repos by did: %w", err)
161 }
162
163 for _, repo := range repos {
164 // TODO: get this in the original query; requires COALESCE because nullable
165 var sourceRepo *Repo
166 if repo.Source != "" {
167 sourceRepo, err = GetRepoByAtUri(e, repo.Source)
168 if err != nil {
169 return nil, err
170 }
171 }
172
173 repoMonth := repo.Created.Month()
174
175 if currentMonth-repoMonth >= TimeframeMonths {
176 // shouldn't happen; but times are weird
177 continue
178 }
179
180 idx := currentMonth - repoMonth
181
182 items := &timeline.ByMonth[idx].RepoEvents
183 *items = append(*items, RepoEvent{
184 Repo: &repo,
185 Source: sourceRepo,
186 })
187 }
188
189 return &timeline, nil
190}
191
192type Profile struct {
193 // ids
194 ID int
195 Did string
196
197 // data
198 Description string
199 IncludeBluesky bool
200 Location string
201 Links [5]string
202 Stats [2]VanityStat
203 PinnedRepos [6]syntax.ATURI
204}
205
206func (p Profile) IsLinksEmpty() bool {
207 for _, l := range p.Links {
208 if l != "" {
209 return false
210 }
211 }
212 return true
213}
214
215func (p Profile) IsStatsEmpty() bool {
216 for _, s := range p.Stats {
217 if s.Kind != "" {
218 return false
219 }
220 }
221 return true
222}
223
224func (p Profile) IsPinnedReposEmpty() bool {
225 for _, r := range p.PinnedRepos {
226 if r != "" {
227 return false
228 }
229 }
230 return true
231}
232
233type VanityStatKind string
234
235const (
236 VanityStatMergedPRCount VanityStatKind = "merged-pull-request-count"
237 VanityStatClosedPRCount VanityStatKind = "closed-pull-request-count"
238 VanityStatOpenPRCount VanityStatKind = "open-pull-request-count"
239 VanityStatOpenIssueCount VanityStatKind = "open-issue-count"
240 VanityStatClosedIssueCount VanityStatKind = "closed-issue-count"
241 VanityStatRepositoryCount VanityStatKind = "repository-count"
242)
243
244func (v VanityStatKind) String() string {
245 switch v {
246 case VanityStatMergedPRCount:
247 return "Merged PRs"
248 case VanityStatClosedPRCount:
249 return "Closed PRs"
250 case VanityStatOpenPRCount:
251 return "Open PRs"
252 case VanityStatOpenIssueCount:
253 return "Open Issues"
254 case VanityStatClosedIssueCount:
255 return "Closed Issues"
256 case VanityStatRepositoryCount:
257 return "Repositories"
258 }
259 return ""
260}
261
262type VanityStat struct {
263 Kind VanityStatKind
264 Value uint64
265}
266
267func (p *Profile) ProfileAt() syntax.ATURI {
268 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", p.Did, tangled.ActorProfileNSID, "self"))
269}
270
271func UpsertProfile(tx *sql.Tx, profile *Profile) error {
272 defer tx.Rollback()
273
274 // update links
275 _, err := tx.Exec(`delete from profile_links where did = ?`, profile.Did)
276 if err != nil {
277 return err
278 }
279 // update vanity stats
280 _, err = tx.Exec(`delete from profile_stats where did = ?`, profile.Did)
281 if err != nil {
282 return err
283 }
284
285 // update pinned repos
286 _, err = tx.Exec(`delete from profile_pinned_repositories where did = ?`, profile.Did)
287 if err != nil {
288 return err
289 }
290
291 includeBskyValue := 0
292 if profile.IncludeBluesky {
293 includeBskyValue = 1
294 }
295
296 _, err = tx.Exec(
297 `insert or replace into profile (
298 did,
299 description,
300 include_bluesky,
301 location
302 )
303 values (?, ?, ?, ?)`,
304 profile.Did,
305 profile.Description,
306 includeBskyValue,
307 profile.Location,
308 )
309
310 if err != nil {
311 log.Println("profile", "err", err)
312 return err
313 }
314
315 for _, link := range profile.Links {
316 if link == "" {
317 continue
318 }
319
320 _, err := tx.Exec(
321 `insert into profile_links (did, link) values (?, ?)`,
322 profile.Did,
323 link,
324 )
325
326 if err != nil {
327 log.Println("profile_links", "err", err)
328 return err
329 }
330 }
331
332 for _, v := range profile.Stats {
333 if v.Kind == "" {
334 continue
335 }
336
337 _, err := tx.Exec(
338 `insert into profile_stats (did, kind) values (?, ?)`,
339 profile.Did,
340 v.Kind,
341 )
342
343 if err != nil {
344 log.Println("profile_stats", "err", err)
345 return err
346 }
347 }
348
349 for _, pin := range profile.PinnedRepos {
350 if pin == "" {
351 continue
352 }
353
354 _, err := tx.Exec(
355 `insert into profile_pinned_repositories (did, at_uri) values (?, ?)`,
356 profile.Did,
357 pin,
358 )
359
360 if err != nil {
361 log.Println("profile_pinned_repositories", "err", err)
362 return err
363 }
364 }
365
366 return tx.Commit()
367}
368
369func GetProfiles(e Execer, filters ...filter) (map[string]*Profile, error) {
370 var conditions []string
371 var args []any
372 for _, filter := range filters {
373 conditions = append(conditions, filter.Condition())
374 args = append(args, filter.Arg()...)
375 }
376
377 whereClause := ""
378 if conditions != nil {
379 whereClause = " where " + strings.Join(conditions, " and ")
380 }
381
382 profilesQuery := fmt.Sprintf(
383 `select
384 id,
385 did,
386 description,
387 include_bluesky,
388 location
389 from
390 profile
391 %s`,
392 whereClause,
393 )
394 rows, err := e.Query(profilesQuery, args...)
395 if err != nil {
396 return nil, err
397 }
398
399 profileMap := make(map[string]*Profile)
400 for rows.Next() {
401 var profile Profile
402 var includeBluesky int
403
404 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location)
405 if err != nil {
406 return nil, err
407 }
408
409 if includeBluesky != 0 {
410 profile.IncludeBluesky = true
411 }
412
413 profileMap[profile.Did] = &profile
414 }
415 if err = rows.Err(); err != nil {
416 return nil, err
417 }
418
419 // populate profile links
420 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
421 args = make([]any, len(profileMap))
422 i := 0
423 for did := range profileMap {
424 args[i] = did
425 i++
426 }
427
428 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
429 rows, err = e.Query(linksQuery, args...)
430 if err != nil {
431 return nil, err
432 }
433 idxs := make(map[string]int)
434 for did := range profileMap {
435 idxs[did] = 0
436 }
437 for rows.Next() {
438 var link, did string
439 if err = rows.Scan(&link, &did); err != nil {
440 return nil, err
441 }
442
443 idx := idxs[did]
444 profileMap[did].Links[idx] = link
445 idxs[did] = idx + 1
446 }
447
448 pinsQuery := fmt.Sprintf("select at_uri, did from profile_pinned_repositories where did in (%s)", inClause)
449 rows, err = e.Query(pinsQuery, args...)
450 if err != nil {
451 return nil, err
452 }
453 idxs = make(map[string]int)
454 for did := range profileMap {
455 idxs[did] = 0
456 }
457 for rows.Next() {
458 var link syntax.ATURI
459 var did string
460 if err = rows.Scan(&link, &did); err != nil {
461 return nil, err
462 }
463
464 idx := idxs[did]
465 profileMap[did].PinnedRepos[idx] = link
466 idxs[did] = idx + 1
467 }
468
469 return profileMap, nil
470}
471
472func GetProfile(e Execer, did string) (*Profile, error) {
473 var profile Profile
474 profile.Did = did
475
476 includeBluesky := 0
477 err := e.QueryRow(
478 `select description, include_bluesky, location from profile where did = ?`,
479 did,
480 ).Scan(&profile.Description, &includeBluesky, &profile.Location)
481 if err == sql.ErrNoRows {
482 profile := Profile{}
483 profile.Did = did
484 return &profile, nil
485 }
486
487 if err != nil {
488 return nil, err
489 }
490
491 if includeBluesky != 0 {
492 profile.IncludeBluesky = true
493 }
494
495 rows, err := e.Query(`select link from profile_links where did = ?`, did)
496 if err != nil {
497 return nil, err
498 }
499 defer rows.Close()
500 i := 0
501 for rows.Next() {
502 if err := rows.Scan(&profile.Links[i]); err != nil {
503 return nil, err
504 }
505 i++
506 }
507
508 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
509 if err != nil {
510 return nil, err
511 }
512 defer rows.Close()
513 i = 0
514 for rows.Next() {
515 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
516 return nil, err
517 }
518 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
519 if err != nil {
520 return nil, err
521 }
522 profile.Stats[i].Value = value
523 i++
524 }
525
526 rows, err = e.Query(`select at_uri from profile_pinned_repositories where did = ?`, did)
527 if err != nil {
528 return nil, err
529 }
530 defer rows.Close()
531 i = 0
532 for rows.Next() {
533 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
534 return nil, err
535 }
536 i++
537 }
538
539 return &profile, nil
540}
541
542func GetVanityStat(e Execer, did string, stat VanityStatKind) (uint64, error) {
543 query := ""
544 var args []any
545 switch stat {
546 case VanityStatMergedPRCount:
547 query = `select count(id) from pulls where owner_did = ? and state = ?`
548 args = append(args, did, PullMerged)
549 case VanityStatClosedPRCount:
550 query = `select count(id) from pulls where owner_did = ? and state = ?`
551 args = append(args, did, PullClosed)
552 case VanityStatOpenPRCount:
553 query = `select count(id) from pulls where owner_did = ? and state = ?`
554 args = append(args, did, PullOpen)
555 case VanityStatOpenIssueCount:
556 query = `select count(id) from issues where did = ? and open = 1`
557 args = append(args, did)
558 case VanityStatClosedIssueCount:
559 query = `select count(id) from issues where did = ? and open = 0`
560 args = append(args, did)
561 case VanityStatRepositoryCount:
562 query = `select count(id) from repos where did = ?`
563 args = append(args, did)
564 }
565
566 var result uint64
567 err := e.QueryRow(query, args...).Scan(&result)
568 if err != nil {
569 return 0, err
570 }
571
572 return result, nil
573}
574
575func ValidateProfile(e Execer, profile *Profile) error {
576 // ensure description is not too long
577 if len(profile.Description) > 256 {
578 return fmt.Errorf("Entered bio is too long.")
579 }
580
581 // ensure description is not too long
582 if len(profile.Location) > 40 {
583 return fmt.Errorf("Entered location is too long.")
584 }
585
586 // ensure links are in order
587 err := validateLinks(profile)
588 if err != nil {
589 return err
590 }
591
592 // ensure all pinned repos are either own repos or collaborating repos
593 repos, err := GetRepos(e, 0, FilterEq("did", profile.Did))
594 if err != nil {
595 log.Printf("getting repos for %s: %s", profile.Did, err)
596 }
597
598 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
599 if err != nil {
600 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
601 }
602
603 var validRepos []syntax.ATURI
604 for _, r := range repos {
605 validRepos = append(validRepos, r.RepoAt())
606 }
607 for _, r := range collaboratingRepos {
608 validRepos = append(validRepos, r.RepoAt())
609 }
610
611 for _, pinned := range profile.PinnedRepos {
612 if pinned == "" {
613 continue
614 }
615 if !slices.Contains(validRepos, pinned) {
616 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
617 }
618 }
619
620 return nil
621}
622
623func validateLinks(profile *Profile) error {
624 for i, link := range profile.Links {
625 if link == "" {
626 continue
627 }
628
629 parsedURL, err := url.Parse(link)
630 if err != nil {
631 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
632 }
633
634 if parsedURL.Scheme == "" {
635 if strings.HasPrefix(link, "//") {
636 profile.Links[i] = "https:" + link
637 } else {
638 profile.Links[i] = "https://" + link
639 }
640 continue
641 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
642 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
643 }
644
645 // catch relative paths
646 if parsedURL.Host == "" {
647 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
648 }
649 }
650 return nil
651}