1// What is happening on the site, newest first: the jev panel's activity
2// tab (website/src/jev/ActivityTab.jsx), from GET /jev/data/activity
3// (data.ts). Every source is a bounded query, newest first, from before a
4// cursor; the merge takes the newest `limit` of them all and answers the
5// cursor for the next page (`next`, the oldest item's time), so paging
6// never skips or repeats more than items sharing one millisecond.
7//
8//   revision    a listener published a song or its next revision, with
9//               Jev's verdict as it stands (pending, fine, spam, abuse)
10//   scored      the art critic scored a fine revision
11//   comment     a comment on a song, with its verdict
12//   pitch       a song idea, with its verdict
13//   take        someone played a song as Jev arranged it (a stored
14//               performance): who (null when signed out), the song, how
15//               many sections it played and how it ended, and the take's
16//               id, for its replay link
17//
18// Held and pending text is not in the feed: an item says
19// Jev held it and why, and the data tab has its text; the feed carries the
20// body of fine comments and pitches only.
21export const ACTIVITY_MAX = 50;
22export const ACTIVITY_DEFAULT = 30;
23
24type Author = { id: string; name: string };
25export type Activity =
26  | { kind: 'revision'; at: number; song: string; rev: number; title: string; author: Author; screen: string; art: number | null }
27  | { kind: 'scored'; at: number; song: string; rev: number; title: string; author: Author; art: number }
28  | {
29      kind: 'comment';
30      at: number;
31      id: string;
32      author: Author;
33      on: { site: string } | { listener: string; title: string | null };
34      screen: string;
35      body: string | null;
36    }
37  | { kind: 'pitch'; at: number; id: string; author: Author; screen: string; body: string | null }
38  | {
39      kind: 'take';
40      at: number;
41      id: string;
42      song: string;
43      // a listener song's newest title (a site song's the page has)
44      title: string | null;
45      player: Author | null;
46      sections: number;
47      ended: string;
48    };
49
50type Row = Record<string, unknown>;
51const author = (r: Row): Author => ({ id: r.author_id as string, name: r.author as string });
52
53// The newest `limit` of every source, before `before`.
54export function merge(sources: Activity[][], limit: number): { items: Activity[]; next: number | null } {
55  const items = sources
56    .flat()
57    .sort((a, b) => b.at - a.at)
58    .slice(0, limit);
59  return { items, next: items.length === limit ? items[items.length - 1].at : null };
60}
61
62export async function activity(db: D1Database, before: number, limit: number) {
63  const [revisions, scored, comments, pitches, played] = (await db.batch([
64    db
65      .prepare(
66        `SELECT r.song, r.rev, r.title, r.created_at AS at, r.screen, r.art, u.id AS author_id, u.display_name AS author
67         FROM song_revisions r JOIN listener_songs s ON s.id = r.song JOIN users u ON u.id = s.user_id
68         WHERE r.created_at < ? ORDER BY r.created_at DESC LIMIT ?`,
69      )
70      .bind(before, limit),
71    db
72      .prepare(
73        `SELECT r.song, r.rev, r.title, r.scored_at AS at, r.art, u.id AS author_id, u.display_name AS author
74         FROM song_revisions r JOIN listener_songs s ON s.id = r.song JOIN users u ON u.id = s.user_id
75         WHERE r.scored_at IS NOT NULL AND r.scored_at < ? ORDER BY r.scored_at DESC LIMIT ?`,
76      )
77      .bind(before, limit),
78    db
79      .prepare(
80        `SELECT c.id, c.site_song, c.listener_song, c.body, c.created_at AS at, c.screen,
81           u.id AS author_id, u.display_name AS author,
82           (SELECT title FROM song_revisions r WHERE r.song = c.listener_song AND r.screen = 'fine'
83              ORDER BY r.rev DESC LIMIT 1) AS listener_title
84         FROM comments c JOIN users u ON u.id = c.user_id
85         WHERE c.created_at < ? ORDER BY c.created_at DESC LIMIT ?`,
86      )
87      .bind(before, limit),
88    db
89      .prepare(
90        `SELECT p.id, p.body, p.created_at AS at, p.screen, u.id AS author_id, u.display_name AS author
91         FROM pitches p JOIN users u ON u.id = p.user_id
92         WHERE p.created_at < ? ORDER BY p.created_at DESC LIMIT ?`,
93      )
94      .bind(before, limit),
95    db
96      .prepare(
97        `SELECT p.id, p.song, p.recorded_at AS at, p.ended, u.id AS author_id, u.display_name AS author,
98           (SELECT count(*) FROM performance_segments s WHERE s.performance = p.id AND s.jev = coalesce(p.form_jev, 0)) AS sections,
99           (SELECT r.title FROM song_revisions r WHERE p.song = 'listener:' || r.song AND r.screen = 'fine' ORDER BY r.rev DESC LIMIT 1) AS title
100         FROM performances p LEFT JOIN users u ON u.id = p.user_id
101         WHERE p.recorded_at < ? ORDER BY p.recorded_at DESC LIMIT ?`,
102      )
103      .bind(before, limit),
104  ])) as D1Result<Row>[];
105
106  const sources: Activity[][] = [
107    revisions.results.map((r) => ({
108      kind: 'revision' as const,
109      at: r.at as number,
110      song: r.song as string,
111      rev: r.rev as number,
112      title: r.title as string,
113      author: author(r),
114      screen: r.screen as string,
115      art: (r.art as number | null) ?? null,
116    })),
117    scored.results.map((r) => ({
118      kind: 'scored' as const,
119      at: r.at as number,
120      song: r.song as string,
121      rev: r.rev as number,
122      title: r.title as string,
123      author: author(r),
124      art: r.art as number,
125    })),
126    comments.results.map((r) => ({
127      kind: 'comment' as const,
128      at: r.at as number,
129      id: r.id as string,
130      author: author(r),
131      on: r.site_song
132        ? { site: r.site_song as string }
133        : { listener: r.listener_song as string, title: (r.listener_title as string | null) ?? null },
134      screen: r.screen as string,
135      body: r.screen === 'fine' ? (r.body as string) : null,
136    })),
137    pitches.results.map((r) => ({
138      kind: 'pitch' as const,
139      at: r.at as number,
140      id: r.id as string,
141      author: author(r),
142      screen: r.screen as string,
143      body: r.screen === 'fine' ? (r.body as string) : null,
144    })),
145    played.results.map((r) => ({
146      kind: 'take' as const,
147      at: r.at as number,
148      id: r.id as string,
149      song: r.song as string,
150      title: (r.title as string | null) ?? null,
151      player: typeof r.author_id === 'string' ? author(r) : null,
152      sections: r.sections as number,
153      ended: r.ended as string,
154    })),
155  ];
156  return merge(sources, limit);
157}