internal/store/inbox.go

  1package store
  2
  3import (
  4	"strconv"
  5	"strings"
  6)
  7
  8// Notice is one inbox row: what happened, where, and whether it has been
  9// read. Path is the web path without a leading slash, so a client turns it
 10// into a link by prefixing the site URL.
 11type Notice struct {
 12	ID        int64
 13	RepoPath  string
 14	Kind      string
 15	Actor     string
 16	Summary   string
 17	Path      string
 18	CreatedAt string
 19	ReadAt    string
 20}
 21
 22// inboxSelect resolves the repository path the same way the dashboard
 23// queries do, since repos.owner_id is polymorphic over users and orgs.
 24const inboxSelect = `
 25	SELECT n.id, COALESCE(u.username, o.name) || '/' || r.name,
 26	       n.kind, n.actor, n.summary, n.path, n.created_at, COALESCE(n.read_at, '')
 27	FROM inbox n
 28	JOIN repos r ON r.id = n.repo_id
 29	LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
 30	LEFT JOIN orgs o  ON r.owner_kind = 'org'  AND o.id = r.owner_id
 31	WHERE n.user_id = ?1`
 32
 33// AddNotice files one inbox row. Best-effort like the mail it accompanies:
 34// the action it reports has already succeeded.
 35func (s *Store) AddNotice(userID, repoID int64, kind, actor, summary, path string) error {
 36	_, err := s.DB.Exec(`
 37		INSERT INTO inbox (user_id, repo_id, kind, actor, summary, path)
 38		VALUES (?, ?, ?, ?, ?, ?)`,
 39		userID, repoID, kind, actor, summary, path)
 40	return err
 41}
 42
 43// Inbox returns a user's notices, newest first. unreadOnly drops what has
 44// been read; afterID pages backwards from a previous page's last row.
 45func (s *Store) Inbox(userID int64, unreadOnly bool, limit int, afterID int64) ([]Notice, error) {
 46	q, args := inboxSelect, []any{userID}
 47	if unreadOnly {
 48		q += " AND n.read_at IS NULL"
 49	}
 50	if afterID > 0 {
 51		q += " AND n.id < ?2"
 52		args = append(args, afterID)
 53	}
 54	q += " ORDER BY n.id DESC"
 55	if limit > 0 {
 56		q += " LIMIT ?" + strconv.Itoa(len(args)+1)
 57		args = append(args, limit)
 58	}
 59	rows, err := s.DB.Query(q, args...)
 60	if err != nil {
 61		return nil, err
 62	}
 63	defer rows.Close()
 64	var out []Notice
 65	for rows.Next() {
 66		var n Notice
 67		if err := rows.Scan(&n.ID, &n.RepoPath, &n.Kind, &n.Actor, &n.Summary,
 68			&n.Path, &n.CreatedAt, &n.ReadAt); err != nil {
 69			return nil, err
 70		}
 71		out = append(out, n)
 72	}
 73	return out, rows.Err()
 74}
 75
 76// UnreadNotices counts what the badge shows.
 77func (s *Store) UnreadNotices(userID int64) int {
 78	var n int
 79	s.DB.QueryRow("SELECT COUNT(*) FROM inbox WHERE user_id = ? AND read_at IS NULL",
 80		userID).Scan(&n)
 81	return n
 82}
 83
 84// MarkNoticesRead marks the given ids read, or every unread notice when
 85// ids is empty. It returns how many rows changed. Ids belonging to another
 86// user match nothing, so one user cannot touch another's inbox.
 87func (s *Store) MarkNoticesRead(userID int64, ids []int64) (int64, error) {
 88	const set = "UPDATE inbox SET read_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE user_id = ? AND read_at IS NULL"
 89	args := []any{userID}
 90	q := set
 91	if len(ids) > 0 {
 92		q += " AND id IN (" + strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",") + ")"
 93		for _, id := range ids {
 94			args = append(args, id)
 95		}
 96	}
 97	res, err := s.DB.Exec(q, args...)
 98	if err != nil {
 99		return 0, err
100	}
101	return res.RowsAffected()
102}
103
104// SetRepoWatch records an explicit watch or mute. Re-running it with the
105// other state replaces the row.
106func (s *Store) SetRepoWatch(repoID, userID int64, state string) error {
107	_, err := s.DB.Exec(`
108		INSERT INTO repo_watchers (repo_id, user_id, state) VALUES (?, ?, ?)
109		ON CONFLICT (repo_id, user_id) DO UPDATE SET state = excluded.state`,
110		repoID, userID, state)
111	return err
112}
113
114// ClearRepoWatch returns the user to the default: notified about work
115// they are part of, nothing more.
116func (s *Store) ClearRepoWatch(repoID, userID int64) error {
117	_, err := s.DB.Exec("DELETE FROM repo_watchers WHERE repo_id = ? AND user_id = ?", repoID, userID)
118	return err
119}
120
121// RepoWatchState returns "watching", "muted", or "" for the default.
122func (s *Store) RepoWatchState(repoID, userID int64) string {
123	var state string
124	s.DB.QueryRow("SELECT state FROM repo_watchers WHERE repo_id = ? AND user_id = ?",
125		repoID, userID).Scan(&state)
126	return state
127}
128
129// NotifyRecipients is who actually hears about something on a repository:
130// the callers targets — owners, or a thread's participants — widened by
131// the repository's watchers, minus the actor and minus anyone who muted
132// it. Muting wins over every other reason to be told, including owning
133// the repository or having written the thread.
134func (s *Store) NotifyRecipients(repoID, actorID int64, targets []int64) ([]int64, error) {
135	rows, err := s.DB.Query("SELECT user_id, state FROM repo_watchers WHERE repo_id = ?", repoID)
136	if err != nil {
137		return nil, err
138	}
139	defer rows.Close()
140	skip := map[int64]bool{actorID: true}
141	var watching []int64
142	for rows.Next() {
143		var id int64
144		var state string
145		if err := rows.Scan(&id, &state); err != nil {
146			return nil, err
147		}
148		if state == "muted" {
149			skip[id] = true
150		} else {
151			watching = append(watching, id)
152		}
153	}
154	if err := rows.Err(); err != nil {
155		return nil, err
156	}
157	var out []int64
158	seen := map[int64]bool{}
159	for _, id := range append(append([]int64{}, targets...), watching...) {
160		if skip[id] || seen[id] {
161			continue
162		}
163		seen[id] = true
164		out = append(out, id)
165	}
166	return out, nil
167}