desktop-mode / includes / my-wordpress / user-footprint.php

user-footprint.php in OpenStation: Desktop Windows, Dock & Virtual Desktops for WP Admin 1.1.10, at includes/my-wordpress/user-footprint.php

755 lines 27.7 KB

Raw Download Zip

1	<?php
2	/**
3	* OpenStation — My WordPress: per-user activity footprint endpoint.
4	*
5	* `GET /desktop-mode/v1/user-footprint/<id>` returns a deep activity
6	* footprint for one user: a year of day-by-day publishing counts
7	* (GitHub-style calendar heatmap), weekday and hour-of-day
8	* distribution (publishing rhythm), longest publishing streak, and
9	* a recent-events timeline (posts published + comments left, last
10	* 30). The right-click "View activity footprint" action in the My
11	* WordPress users folder paints from this single payload.
12	*
13	* Permission: the My WordPress module's gate,
14	* `openstation_my_wordpress_user_can_use()` (`edit_posts` unless a site
15	* filters it), so a site that narrows WP Explorer narrows this data
16	* with it. Past that gate, `list_users` (or the subject viewing their
17	* own footprint) only decides the profile fields (`roleLabels`,
18	* `registered`), the same split `user-stats.php` uses. Sensitive
19	* fields (email, IP) are NOT returned from this endpoint:
20	* `user-stats.php` carries those for the preview pane, and the
21	* footprint focuses on activity patterns.
22	*
23	* **Activity is gated per post, and a count is gated exactly like
24	* the rows it summarises.** A timeline row is emitted only when
25	* `openstation_my_wordpress_footprint_can_see_post()` lets the viewer
26	* see its post: a public status of a viewable type for everyone,
27	* `read_post` for any other status, `edit_post` for a type with no
28	* readable front end, and the comment dossier's parent gate for
29	* comment rows (`edit_post` while the parent is still sealed by a
30	* password the viewer has not entered, `moderate_comments` once it is
31	* deleted). The counts that can reach those same posts
32	* (`totals.posts`, `totals.pages`, `totals.comments`,
33	* `totals.updates`, and each day's `comments` and `updates`, which
34	* the streak reads) ask that gate of every post they count, so a
35	* plugin filtering `read_post` for a single post moves the counts
36	* with the rows. A Contributor's heatmap and hero stats cannot
37	* report, as numbers, the drafts, private edits or internal records
38	* the timeline withholds, and an Editor's totals include the drafts
39	* their timeline lists. The remaining aggregates (`daily[].posts`,
40	* `weekday`, `hour`, `mostProlificMonth`) count published posts and
41	* pages only. The payload is viewer-dependent: never cache it under
42	* a subject-only key.
43	*
44	* Payload shape:
45	*
46	* {
47	* profile: { id, name, avatarUrl, link, roleLabels?, registered? },
48	* range: { from, to, days }, // YYYY-MM-DD bookends + day count
49	* daily: [ { date, posts, comments, updates } ], // length = range.days; missing days = 0
50	* weekday: [ 0..6 ], // post counts, Sunday-indexed
51	* hour: [ 0..23 ], // post counts, server-local hour
52	* streak: { longest, current, longestRange:{ from, to } },
53	* timeline:[ // 30 most recent activity rows
54	* { kind:'post'\|'comment'\|'post-update', date, title, link, status, postId?, type? }
55	* ],
56	* totals: { posts, pages, comments, updates, mostProlificMonth?:{ ym, n } }
57	* }
58	*
59	* Timeline row fields:
60	* - `kind` — discriminator: `'post'` (publish), `'comment'`, or
61	* `'post-update'` (revision rollup).
62	* - `type` — only set when `kind` is `'post'` or `'post-update'`.
63	* Carries the post's CPT slug (`'post'`, `'page'`, custom
64	* types) so the renderer can pick a Post-vs-Page icon
65	* without a second REST lookup.
66	*
67	* "Updates" are revisions saved by the user AFTER a post's original
68	* creation, i.e. the user opened an existing post and saved it
69	* again. The initial save (which WordPress also writes as a revision)
70	* is excluded so the per-day "updates" count doesn't double up with
71	* the per-day "posts" count. So every revision after a post's first
72	* one is an update, whenever it was saved: while the post was a
73	* draft, before a scheduled post went live, or after. The first
74	* revision counts too when it is newer than the post's date, as when
75	* a post that never had a revision is edited later; a draft or
76	* pending post has no date yet (`post_date_gmt` stays zero), so its
77	* first revision never does.
78	*
79	* @package OpenStation
80	*/
81
82	defined( 'ABSPATH' ) \|\| exit;
83
84	/**
85	* Register the route.
86	*/
87	function openstation_my_wordpress_register_user_footprint_route() {
88	register_rest_route(
89	'desktop-mode/v1',
90	'/user-footprint/(?P<id>\d+)',
91	array(
92	'methods' => WP_REST_Server::READABLE,
93	'callback' => 'openstation_my_wordpress_user_footprint_callback',
94	'permission_callback' => static function () {
95	// The module's gate, so a site that narrows WP Explorer
96	// narrows this data with it. Every per-post check lives in
97	// the callback.
98	return openstation_my_wordpress_user_can_use();
99	},
100	'args' => array(
101	'id' => array(
102	'required' => true,
103	'type' => 'integer',
104	'sanitize_callback' => 'absint',
105	),
106	),
107	)
108	);
109	}
110	add_action( 'rest_api_init', 'openstation_my_wordpress_register_user_footprint_route' );
111
112	/**
113	* Whether the current user may see footprint activity on a post.
114	*
115	* One gate for the timeline rows and for the counts that summarise
116	* them (see openstation_my_wordpress_footprint_visible_counts()), so
117	* the two cannot disagree about what a viewer is allowed to know.
118	*
119	* - A comment's parent goes through
120	* openstation_my_wordpress_can_read_comment_post(), the comment
121	* dossier's gate: an orphaned comment is moderators-only, and a
122	* parent of a non-viewable type needs `edit_post`, as does a parent
123	* still sealed by a password. A viewer who has already entered that
124	* password is not looking at a sealed post (`post_password_required()`
125	* reads the cookie), and reads on `read_post` like anyone else.
126	* - Any other post of a type with no readable front end needs
127	* `edit_post`. Core resolves `read_post` on a published post of such
128	* a type to plain `read`, which every logged-in user holds, so it
129	* would read like public content.
130	* - A viewable post in a public status is public already.
131	* - Anything else is `read_post`, which resolves per status: the
132	* post's author always, `read_private_posts` for a private post, and
133	* `edit_others_posts` for drafts, pending and scheduled posts.
134	*
135	* Core answers a post of an unregistered type or status with
136	* `edit_others_posts`, after a `_doing_it_wrong()` notice. Rows a
137	* deactivated plugin left behind get the same answer here, without
138	* the notice.
139	*
140	* @param WP_Post\|null $post The post, or null when it no longer exists.
141	* @param bool $for_comment Whether the activity is a comment on the post.
142	* @return bool
143	*/
144	function openstation_my_wordpress_footprint_can_see_post( $post, $for_comment = false ) {
145	if ( ! $post ) {
146	return $for_comment && current_user_can( 'moderate_comments' );
147	}
148	$status = get_post_status_object( $post->post_status );
149	if ( ! $status \|\| ! get_post_type_object( $post->post_type ) ) {
150	return current_user_can( 'edit_others_posts' );
151	}
152	if ( $for_comment ) {
153	return openstation_my_wordpress_can_read_comment_post( $post );
154	}
155	if ( ! is_post_type_viewable( $post->post_type ) ) {
156	return current_user_can( 'edit_post', $post->ID );
157	}
158	return $status->public \|\| current_user_can( 'read_post', $post->ID );
159	}
160
161	/**
162	* Sum activity counts, keeping only the rows on posts the viewer may see.
163	*
164	* Each count query returns one row per post it needs decided, so the
165	* gate above runs on every post a count includes, exactly as the
166	* timeline runs it per row: a plugin that filters `read_post` or
167	* `edit_post` for a single post moves the counts with the rows. Two
168	* shapes keep that affordable:
169	*
170	* - Activity on posts anyone may see (a public status of a viewable
171	* type, which the gate allows without a capability check) arrives
172	* collapsed under `post_id` 0, so a prolific author's published
173	* archive is one row rather than one per post.
174	* - Every other post is loaded in one query, and decided once per
175	* request however many days or counts it appears in.
176	*
177	* Comments have no bulk row: their gate asks `read_post` and the
178	* parent's password even on a published post. For comments, `post_id`
179	* 0 is a comment whose post no longer exists.
180	*
181	* @param array[]\|null $rows Rows carrying `post_id` and `n`, plus `d` (Y-m-d) for per-day counts.
182	* @param bool $for_comment Whether the rows count comments on the posts.
183	* @param array $verdicts Gate answers already reached in this request, keyed by kind and post id.
184	* @return array{ total: int, by_day: array<string, int> }
185	*/
186	function openstation_my_wordpress_footprint_visible_counts( $rows, $for_comment, array &$verdicts ) {
187	$rows = (array) $rows;
188	$prefix = $for_comment ? 'comment:' : 'post:';
189	$unseen = array();
190	foreach ( $rows as $row ) {
191	$id = (int) $row['post_id'];
192	if ( $id > 0 && ! isset( $verdicts[ $prefix . $id ] ) ) {
193	$unseen[ $id ] = $id;
194	}
195	}
196	if ( $unseen ) {
197	_prime_post_caches( array_values( $unseen ), false, false );
198	}
199
200	$total = 0;
201	$by_day = array();
202	foreach ( $rows as $row ) {
203	$id = (int) $row['post_id'];
204	if ( $id > 0 \|\| $for_comment ) {
205	$key = $prefix . $id;
206	if ( ! isset( $verdicts[ $key ] ) ) {
207	$verdicts[ $key ] = openstation_my_wordpress_footprint_can_see_post( $id > 0 ? get_post( $id ) : null, $for_comment );
208	}
209	if ( ! $verdicts[ $key ] ) {
210	continue;
211	}
212	}
213	$n = (int) $row['n'];
214	$total += $n;
215	if ( isset( $row['d'] ) ) {
216	$day = (string) $row['d'];
217	$by_day[ $day ] = ( $by_day[ $day ] ?? 0 ) + $n;
218	}
219	}
220	return array(
221	'total' => $total,
222	'by_day' => $by_day,
223	);
224	}
225
226	/**
227	* Aggregator callback. See the file docblock for the payload shape.
228	*
229	* @param WP_REST_Request $request REST request.
230	* @return array\|WP_Error
231	*/
232	function openstation_my_wordpress_user_footprint_callback( $request ) {
233	global $wpdb;
234
235	$user_id = (int) $request->get_param( 'id' );
236	$user = get_userdata( $user_id );
237	if ( ! $user ) {
238	return new WP_Error(
239	'openstation_user_not_found',
240	__( 'User not found.', 'desktop-mode' ),
241	array( 'status' => 404 )
242	);
243	}
244
245	$viewer_id = get_current_user_id();
246	$can_see_private = current_user_can( 'list_users' ) \|\| ( $viewer_id === $user_id );
247
248	// ---- Profile (minimal — the dossier already returned the full one) ----
249	$profile = array(
250	'id' => (int) $user->ID,
251	'name' => (string) $user->display_name,
252	'avatarUrl' => get_avatar_url( $user->ID, array( 'size' => 128 ) ),
253	'link' => get_author_posts_url( $user->ID ),
254	);
255	if ( $can_see_private ) {
256	$role_labels = array();
257	if ( function_exists( 'wp_roles' ) ) {
258	$wp_roles = wp_roles();
259	foreach ( (array) $user->roles as $slug ) {
260	$role_labels[] = isset( $wp_roles->role_names[ $slug ] )
261	? translate_user_role( $wp_roles->role_names[ $slug ] )
262	: $slug;
263	}
264	}
265	$profile['roleLabels'] = $role_labels;
266	if ( '' !== $user->user_registered ) {
267	$profile['registered'] = mysql2date( 'c', $user->user_registered, false );
268	}
269	}
270
271	// ---- Range: rolling 365-day window ending today (UTC bookends) -------
272	$days = 365;
273	$now = time(); // UTC
274	$from_ts = strtotime( '-' . ( $days - 1 ) . ' days', $now );
275	$to_ts = $now;
276	$range = array(
277	'from' => gmdate( 'Y-m-d', $from_ts ),
278	'to' => gmdate( 'Y-m-d', $to_ts ),
279	'days' => $days,
280	);
281
282	// ---- Posts anyone may see ------------------------------------------
283	// A public status of a viewable type. The gate allows those without a
284	// capability check, so the update and content counts total them in
285	// SQL under `post_id` 0 and name every other post for the gate; see
286	// openstation_my_wordpress_footprint_visible_counts(). `$verdicts`
287	// keeps each post's answer for the rest of the request.
288	$open_stati = array_values( get_post_stati( array( 'public' => true ) ) );
289	$open_types = array_values( array_filter( get_post_types(), 'is_post_type_viewable' ) );
290	if ( ! $open_types ) {
291	// Keeps the IN list valid. No row has an empty type, so every post
292	// then goes through the gate.
293	$open_types = array( '' );
294	}
295	$open_stati_in = implode( ', ', array_fill( 0, count( $open_stati ), '%s' ) );
296	$open_types_in = implode( ', ', array_fill( 0, count( $open_types ), '%s' ) );
297	$open_args = array_merge( $open_stati, $open_types );
298	$verdicts = array();
299
300	// ---- Daily counts (posts published, comments LEFT, updates saved) ----
301	// One query per kind, each grouped by `DATE(post_date_gmt)` /
302	// `DATE(comment_date_gmt)`. Then we densify to a full day-by-day
303	// array so the heatmap renders every cell, even empty ones.
304	//
305	// Posts are published posts and pages, which anyone may see. A
306	// comment or an update can land on a post the viewer may not read,
307	// so those two queries name each post the timeline's gate has to
308	// decide, and the rows it refuses are dropped: a heatmap cell
309	// must not report "this user commented on, or edited, something
310	// private on Tuesday" when the timeline withholds the row saying so.
311	$post_rows = $wpdb->get_results(
312	$wpdb->prepare(
313	"SELECT DATE(post_date_gmt) AS d, COUNT(*) AS n
314	FROM {$wpdb->posts}
315	WHERE post_author = %d
316	AND post_status = 'publish'
317	AND post_type IN ( 'post', 'page' )
318	AND post_date_gmt >= %s
319	GROUP BY d
320	ORDER BY d ASC",
321	$user_id,
322	gmdate( 'Y-m-d 00:00:00', $from_ts )
323	),
324	ARRAY_A
325	);
326	$post_by_day = array();
327	foreach ( (array) $post_rows as $row ) {
328	$post_by_day[ (string) $row['d'] ] = (int) $row['n'];
329	}
330
331	$comment_rows = $wpdb->get_results(
332	$wpdb->prepare(
333	"SELECT DATE(c.comment_date_gmt) AS d, p.ID AS post_id, COUNT(*) AS n
334	FROM {$wpdb->comments} c
335	LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
336	WHERE c.user_id = %d
337	AND c.comment_approved = '1'
338	AND c.comment_date_gmt >= %s
339	GROUP BY d, p.ID
340	ORDER BY d ASC",
341	$user_id,
342	gmdate( 'Y-m-d 00:00:00', $from_ts )
343	),
344	ARRAY_A
345	);
346	$comment_by_day = openstation_my_wordpress_footprint_visible_counts( $comment_rows, true, $verdicts )['by_day'];
347
348	// Updates = revisions saved by this user, joined back to the parent
349	// post so we can skip the initial-save revision. `r.post_author`
350	// (not the parent's) tracks who hit Save, so updates an editor makes
351	// to someone else's post show up on the editor's footprint, the same
352	// shape GitHub's contribution graph uses for commits across repos you
353	// don't own.
354	//
355	// "Not the initial save" cannot be a date test alone, because a post's
356	// date is when it goes live. A draft or pending post has none yet
357	// (`post_date_gmt` stays zero) and a scheduled post's is in the
358	// future, so every save made before publication compares as older
359	// than the post and would never count, not even once it is published.
360	// Every revision after the post's first therefore counts, and the
361	// first counts only when it is newer than a real post date, as when a
362	// post that never had a revision is edited later. The lifetime count
363	// and the timeline query below carry the same clause; keep the three
364	// in step.
365	$update_rows = $wpdb->get_results(
366	$wpdb->prepare(
367	"SELECT DATE(r.post_date_gmt) AS d,
368	CASE WHEN p.post_status IN ( {$open_stati_in} ) AND p.post_type IN ( {$open_types_in} ) THEN 0 ELSE p.ID END AS post_id,
369	COUNT(*) AS n
370	FROM {$wpdb->posts} r
371	INNER JOIN {$wpdb->posts} p ON r.post_parent = p.ID
372	WHERE r.post_author = %d
373	AND r.post_type = 'revision'
374	AND r.post_status = 'inherit'
375	AND (
376	( p.post_date_gmt <> '0000-00-00 00:00:00' AND r.post_date_gmt > p.post_date_gmt )
377	OR EXISTS (
378	SELECT 1 FROM {$wpdb->posts} r0
379	WHERE r0.post_parent = p.ID AND r0.post_type = 'revision' AND r0.ID < r.ID
380	)
381	)
382	AND r.post_date_gmt >= %s
383	GROUP BY d, post_id
384	ORDER BY d ASC",
385	array_merge( $open_args, array( $user_id, gmdate( 'Y-m-d 00:00:00', $from_ts ) ) )
386	),
387	ARRAY_A
388	);
389	$update_by_day = openstation_my_wordpress_footprint_visible_counts( $update_rows, false, $verdicts )['by_day'];
390
391	$daily = array();
392	for ( $i = 0; $i < $days; ++$i ) {
393	$ts = strtotime( '+' . $i . ' days', $from_ts );
394	$date = gmdate( 'Y-m-d', $ts );
395	$daily[] = array(
396	'date' => $date,
397	'posts' => isset( $post_by_day[ $date ] ) ? $post_by_day[ $date ] : 0,
398	'comments' => isset( $comment_by_day[ $date ] ) ? $comment_by_day[ $date ] : 0,
399	'updates' => isset( $update_by_day[ $date ] ) ? $update_by_day[ $date ] : 0,
400	);
401	}
402
403	// ---- Weekday distribution (Sunday-indexed) ---------------------------
404	// `DAYOFWEEK` returns 1=Sunday through 7=Saturday in MySQL.
405	$weekday_rows = $wpdb->get_results(
406	$wpdb->prepare(
407	"SELECT DAYOFWEEK(post_date_gmt) AS dow, COUNT(*) AS n
408	FROM {$wpdb->posts}
409	WHERE post_author = %d
410	AND post_status = 'publish'
411	AND post_type IN ( 'post', 'page' )
412	GROUP BY dow",
413	$user_id
414	),
415	ARRAY_A
416	);
417	$weekday = array( 0, 0, 0, 0, 0, 0, 0 );
418	foreach ( (array) $weekday_rows as $row ) {
419	$dow = (int) $row['dow'];
420	if ( $dow >= 1 && $dow <= 7 ) {
421	$weekday[ $dow - 1 ] = (int) $row['n'];
422	}
423	}
424
425	// ---- Hour-of-day distribution (0..23, site timezone) -----------------
426	// `post_date` is already in site timezone — that's the timestamp
427	// the author saw when they hit Publish. Using GMT here would shift
428	// the bars by the offset and feel wrong to anyone in a non-UTC tz.
429	$hour_rows = $wpdb->get_results(
430	$wpdb->prepare(
431	"SELECT HOUR(post_date) AS h, COUNT(*) AS n
432	FROM {$wpdb->posts}
433	WHERE post_author = %d
434	AND post_status = 'publish'
435	AND post_type IN ( 'post', 'page' )
436	GROUP BY h",
437	$user_id
438	),
439	ARRAY_A
440	);
441	$hour = array_fill( 0, 24, 0 );
442	foreach ( (array) $hour_rows as $row ) {
443	$h = (int) $row['h'];
444	if ( $h >= 0 && $h <= 23 ) {
445	$hour[ $h ] = (int) $row['n'];
446	}
447	}
448
449	// ---- Streak (longest consecutive run of days with ≥1 post over the
450	// 365-day window; current run ending today). ------------------------
451	$longest = 0;
452	$current = 0;
453	$longest_run = 0;
454	$longest_from = '';
455	$longest_to = '';
456	$run_start = '';
457	$today_str = $range['to'];
458	$prev_day_active = false;
459
460	// "Active" = published a post, left a comment, or saved a revision.
461	// Pre-0.8.7 this only counted publish days, so an editor doing
462	// daily updates without new posts had a "0 day" streak — wrong
463	// flavour of GitHub-style for a CMS where most work is editing.
464	$is_active = static function ( $entry ) {
465	return $entry['posts'] > 0
466	\|\| ( isset( $entry['updates'] ) && $entry['updates'] > 0 )
467	\|\| ( isset( $entry['comments'] ) && $entry['comments'] > 0 );
468	};
469	foreach ( $daily as $entry ) {
470	if ( $is_active( $entry ) ) {
471	if ( ! $prev_day_active ) {
472	$run_start = $entry['date'];
473	}
474	++$longest_run;
475	if ( $longest_run > $longest ) {
476	$longest = $longest_run;
477	$longest_from = $run_start;
478	$longest_to = $entry['date'];
479	}
480	$prev_day_active = true;
481	} else {
482	$longest_run = 0;
483	$prev_day_active = false;
484	}
485	}
486	// Current streak — walk backward from today.
487	for ( $i = count( $daily ) - 1; $i >= 0; --$i ) {
488	if ( $is_active( $daily[ $i ] ) ) {
489	++$current;
490	} else {
491	break;
492	}
493	}
494	$streak = array(
495	'longest' => $longest,
496	'current' => $current,
497	'longestRange' => array(
498	'from' => $longest_from,
499	'to' => $longest_to,
500	),
501	);
502
503	// ---- Timeline: 30 most recent posts + comments, interleaved by date -
504	// One query per kind, then merge + sort + slice in PHP. Smaller and
505	// simpler than a SQL `UNION ALL`, and each branch already has the
506	// right index.
507	$timeline_posts = $wpdb->get_results(
508	$wpdb->prepare(
509	"SELECT ID, post_title, post_status, post_date_gmt, post_type
510	FROM {$wpdb->posts}
511	WHERE post_author = %d
512	AND post_type IN ( 'post', 'page' )
513	AND post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )
514	ORDER BY post_date_gmt DESC
515	LIMIT 30",
516	$user_id
517	),
518	ARRAY_A
519	);
520	$timeline_comments = $wpdb->get_results(
521	$wpdb->prepare(
522	"SELECT c.comment_ID, c.comment_post_ID, c.comment_date_gmt, c.comment_approved,
523	p.post_title, p.post_status
524	FROM {$wpdb->comments} c
525	LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
526	WHERE c.user_id = %d
527	AND c.comment_approved = '1'
528	ORDER BY c.comment_date_gmt DESC
529	LIMIT 30",
530	$user_id
531	),
532	ARRAY_A
533	);
534	// Recent updates — newest revision per parent post saved by this
535	// user. We collapse per-parent (`GROUP BY r.post_parent`) so a
536	// burst of saves on one post reads as one row in the activity
537	// list (otherwise an editor polishing a single article would push
538	// every other event off the screen). The MAX(r.post_date_gmt)
539	// surfaces the most recent save as the row's timestamp.
540	$timeline_updates = $wpdb->get_results(
541	$wpdb->prepare(
542	"SELECT r.post_parent AS parent_id, MAX(r.post_date_gmt) AS last_save, p.post_title, p.post_status, p.post_type
543	FROM {$wpdb->posts} r
544	INNER JOIN {$wpdb->posts} p ON r.post_parent = p.ID
545	WHERE r.post_author = %d
546	AND r.post_type = 'revision'
547	AND r.post_status = 'inherit'
548	AND (
549	( p.post_date_gmt <> '0000-00-00 00:00:00' AND r.post_date_gmt > p.post_date_gmt )
550	OR EXISTS (
551	SELECT 1 FROM {$wpdb->posts} r0
552	WHERE r0.post_parent = p.ID AND r0.post_type = 'revision' AND r0.ID < r.ID
553	)
554	)
555	AND p.post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )
556	GROUP BY r.post_parent
557	ORDER BY last_save DESC
558	LIMIT 30",
559	$user_id
560	),
561	ARRAY_A
562	);
563	$timeline = array();
564	// Per-row gate: openstation_my_wordpress_footprint_can_see_post(), the
565	// same one the counts above ask. Rows for non-published posts (draft,
566	// pending, private, future, ...) carry titles the viewer may not be
567	// allowed to see, and so do published rows of a type with no readable
568	// front end and a comment's password-protected or deleted parent.
569	// Authors and editors keep their full timeline, while ordinary
570	// logged-in users only see published, viewable work.
571	$timeline_ids = array_filter(
572	array_map(
573	'intval',
574	array_merge(
575	wp_list_pluck( (array) $timeline_posts, 'ID' ),
576	wp_list_pluck( (array) $timeline_comments, 'comment_post_ID' ),
577	wp_list_pluck( (array) $timeline_updates, 'parent_id' )
578	)
579	)
580	);
581	if ( $timeline_ids ) {
582	// Bulk-warm the post cache: the gate and get_permalink() read from it.
583	_prime_post_caches( array_unique( $timeline_ids ), false, false );
584	}
585	foreach ( (array) $timeline_posts as $p ) {
586	$pid = (int) $p['ID'];
587	if ( ! openstation_my_wordpress_footprint_can_see_post( get_post( $pid ) ) ) {
588	continue;
589	}
590	$timeline[] = array(
591	'kind' => 'post',
592	'date' => mysql2date( 'c', $p['post_date_gmt'], false ),
593	'title' => (string) $p['post_title'],
594	'status' => (string) $p['post_status'],
595	'postId' => $pid,
596	'link' => (string) get_permalink( $pid ),
597	'type' => (string) $p['post_type'],
598	);
599	}
600	foreach ( (array) $timeline_comments as $c ) {
601	$pid = (int) $c['comment_post_ID'];
602	if ( ! openstation_my_wordpress_footprint_can_see_post( $pid > 0 ? get_post( $pid ) : null, true ) ) {
603	continue;
604	}
605	$timeline[] = array(
606	'kind' => 'comment',
607	'date' => mysql2date( 'c', $c['comment_date_gmt'], false ),
608	'title' => (string) ( $c['post_title'] ?? '' ),
609	'status' => 'approved',
610	'postId' => $pid,
611	'link' => $pid ? (string) get_permalink( $pid ) : '',
612	);
613	}
614	foreach ( (array) $timeline_updates as $u ) {
615	$pid = (int) $u['parent_id'];
616	if ( ! openstation_my_wordpress_footprint_can_see_post( get_post( $pid ) ) ) {
617	continue;
618	}
619	$timeline[] = array(
620	'kind' => 'post-update',
621	'date' => mysql2date( 'c', $u['last_save'], false ),
622	'title' => (string) $u['post_title'],
623	'status' => (string) $u['post_status'],
624	'postId' => $pid,
625	'link' => $pid ? (string) get_permalink( $pid ) : '',
626	'type' => (string) $u['post_type'],
627	);
628	}
629	usort(
630	$timeline,
631	static function ( $a, $b ) {
632	return strcmp( (string) $b['date'], (string) $a['date'] );
633	}
634	);
635	$timeline = array_slice( $timeline, 0, 30 );
636
637	// ---- Totals + most-prolific month -----------------------------------
638	// Lifetime counts, each decided per post by the timeline's gate. Posts
639	// and pages cover every non-internal status the viewer may read, so a
640	// Subscriber gets published work only and cannot read how many
641	// drafts, pending, private and scheduled posts another user is sitting
642	// on (or watch that number move), while an Editor, whose timeline
643	// lists those drafts, gets them counted too.
644	$content_rows = $wpdb->get_results(
645	$wpdb->prepare(
646	"SELECT post_type,
647	CASE WHEN post_status IN ( {$open_stati_in} ) AND post_type IN ( {$open_types_in} ) THEN 0 ELSE ID END AS post_id,
648	COUNT(*) AS n
649	FROM {$wpdb->posts}
650	WHERE post_author = %d
651	AND post_type IN ( 'post', 'page' )
652	AND post_status NOT IN ( 'auto-draft', 'inherit', 'trash' )
653	GROUP BY post_type, post_id",
654	array_merge( $open_args, array( $user_id ) )
655	),
656	ARRAY_A
657	);
658	$totals_posts = openstation_my_wordpress_footprint_visible_counts(
659	wp_list_filter( (array) $content_rows, array( 'post_type' => 'post' ) ),
660	false,
661	$verdicts
662	)['total'];
663	$totals_pages = openstation_my_wordpress_footprint_visible_counts(
664	wp_list_filter( (array) $content_rows, array( 'post_type' => 'page' ) ),
665	false,
666	$verdicts
667	)['total'];
668	$comment_totals = $wpdb->get_results(
669	$wpdb->prepare(
670	"SELECT p.ID AS post_id, COUNT(*) AS n
671	FROM {$wpdb->comments} c
672	LEFT JOIN {$wpdb->posts} p ON c.comment_post_ID = p.ID
673	WHERE c.user_id = %d
674	AND c.comment_approved = '1'
675	GROUP BY p.ID",
676	$user_id
677	),
678	ARRAY_A
679	);
680	$totals_comments = openstation_my_wordpress_footprint_visible_counts( $comment_totals, true, $verdicts )['total'];
681	// Lifetime updates = revisions this user saved after the initial
682	// creation of the parent post. Matches the per-day `updates`
683	// definition so the hero stat and heatmap rollups agree.
684	$update_totals = $wpdb->get_results(
685	$wpdb->prepare(
686	"SELECT CASE WHEN p.post_status IN ( {$open_stati_in} ) AND p.post_type IN ( {$open_types_in} ) THEN 0 ELSE p.ID END AS post_id,
687	COUNT(*) AS n
688	FROM {$wpdb->posts} r
689	INNER JOIN {$wpdb->posts} p ON r.post_parent = p.ID
690	WHERE r.post_author = %d
691	AND r.post_type = 'revision'
692	AND r.post_status = 'inherit'
693	AND (
694	( p.post_date_gmt <> '0000-00-00 00:00:00' AND r.post_date_gmt > p.post_date_gmt )
695	OR EXISTS (
696	SELECT 1 FROM {$wpdb->posts} r0
697	WHERE r0.post_parent = p.ID AND r0.post_type = 'revision' AND r0.ID < r.ID
698	)
699	)
700	GROUP BY post_id",
701	array_merge( $open_args, array( $user_id ) )
702	),
703	ARRAY_A
704	);
705	$totals_updates = openstation_my_wordpress_footprint_visible_counts( $update_totals, false, $verdicts )['total'];
706	$month_row = $wpdb->get_row(
707	$wpdb->prepare(
708	"SELECT DATE_FORMAT(post_date_gmt, '%%Y-%%m') AS ym, COUNT(*) AS n
709	FROM {$wpdb->posts}
710	WHERE post_author = %d
711	AND post_status = 'publish'
712	AND post_type IN ( 'post', 'page' )
713	GROUP BY ym
714	ORDER BY n DESC
715	LIMIT 1",
716	$user_id
717	),
718	ARRAY_A
719	);
720	$totals = array(
721	'posts' => $totals_posts,
722	'pages' => $totals_pages,
723	'comments' => $totals_comments,
724	'updates' => $totals_updates,
725	);
726	if ( $month_row && isset( $month_row['ym'] ) ) {
727	$totals['mostProlificMonth'] = array(
728	'ym' => (string) $month_row['ym'],
729	'n' => (int) $month_row['n'],
730	);
731	}
732
733	$payload = array(
734	'profile' => $profile,
735	'range' => $range,
736	'daily' => $daily,
737	'weekday' => $weekday,
738	'hour' => $hour,
739	'streak' => $streak,
740	'timeline' => $timeline,
741	'totals' => $totals,
742	);
743
744	/**
745	* Filter the per-user footprint payload before it's returned to
746	* the My WordPress folder window. Plugins can extend the timeline
747	* with their own activity rows, or replace the streak math with
748	* something domain-specific.
749	*
750	* @param array $payload Footprint payload.
751	* @param int $user_id Subject user id.
752	*/
753	return apply_filters( 'openstation_my_wordpress_user_footprint', $payload, $user_id );
754	}
755