<?php

/**
 * PeepSoActivityRankingEngine
 *
 * Pure SQL / math engine that (re)computes the engagement scores stored in the
 * `peepso_activity_ranking` table. It has NO hooks of its own — it is driven by
 * PeepSo3_Maintenance_Activity_Ranking on the dedicated 1-minute ranking cron and by
 * PeepSoActivityRanking::mark_dirty() flagging rows for recompute.
 *
 * Every score is anchored to the ENGAGEMENT time of individual events, never to
 * the post creation time. Consequences:
 *   - Popular windows (1d / 7d / 30d) aggregate the timestamps of reactions,
 *     likes, comments and reposts — a 2-year-old post that sparks a discussion
 *     this week tops "Popular - 7 days".
 *   - Hot is an exponentially time-decayed engagement sum:
 *         score_hot = W_seed * 0.5^(post_age/H)
 *                   + SUM( weight * valence * 0.5^(event_age/H) )
 *     with half-life H (default 36h). recompute() stores this value together with
 *     the reference time it is anchored to (rank_hot_ts = the moment it was
 *     computed). Hot is then decayed LAZILY AT READ TIME via hot_order_sql():
 *         comparable_hot = rank_score_hot_7d * 0.5^((now - rank_hot_ts)/H)
 *     This is exact for ordering regardless of how stale the stored value is, and
 *     avoids a periodic full-table multiply (which on a large site would rewrite
 *     the whole working set — and its index — every cron tick). Idle rows are
 *     zeroed by recompute() itself once they fall below HOT_FLOOR, so they leave
 *     the reconcile working set.
 *
 * TIME-DOMAIN RULE (do not "simplify" this):
 *   - `like_timestamp` / `reaction_timestamp` are MySQL TIMESTAMP columns; MySQL
 *     returns them in the session timezone, so they must be compared against NOW().
 *   - comment / repost / seed times come from `wp_posts.post_date_gmt`, which is
 *     UTC, so they must be compared against UTC_TIMESTAMP().
 *   Mixing the two would offset ages by the server timezone. The legacy
 *   `rank_act_date` column (blog-local time) is never used for scoring.
 *
 * PEEPSO TIMESTAMP RULE OF THUMB (applies wherever these columns are written or
 * compared — not just in this engine):
 *   - WordPress content: use the `*_gmt` DATETIME column (UTC) and compare it to
 *     `UTC_TIMESTAMP()` / PHP `gmdate()`.
 *   - PeepSo engagement `TIMESTAMP` columns (`reaction_timestamp`, `like_timestamp`):
 *     they round-trip in the DB session timezone (their DEFAULT CURRENT_TIMESTAMP), so
 *     compare them to `NOW()` and NEVER inject a value computed in another frame (a UTC
 *     `gmdate()` string, `post_date_gmt`, etc.). Any writer that backdates them — e.g.
 *     the demo-content seeder — must express the instant in the session frame
 *     (see the PDDC seeder's random_after_ts() + its session-offset shift).
 */
class PeepSoActivityRankingEngine
{
	// Window sizes, in seconds.
	const DAY   = 86400;
	const WEEK  = 604800;
	const MONTH = 2592000;
	const YEAR  = 31536000;

	// A hot score below this (at compute time) is treated as gone and zeroed, so
	// long-idle rows drop out of the reconcile working set.
	const HOT_FLOOR = 0.001;

	// Weights "generation" pointer (plain WP option, autoload no). Bumped whenever an
	// admin changes any ranking weight; recompute() stamps each row's rank_rev with the
	// value it was scored under, and a row with rank_rev < this pointer is stale (dirty)
	// and gets re-scored by the ordinary cron sweeps — O(1) invalidation, no write storm.
	const WEIGHTS_REV_OPTION = 'peepso_ranking_weights_rev';

	// Sticky "hidden rollout" flag (plain WP option). Flipped to 1 once the ONE-TIME initial index
	// build reaches the reveal threshold; the frontend Top/Hot sort picker stays hidden until then.
	// Never unset — later rebuilds / weight changes do not re-gate.
	const ROLLOUT_READY_OPTION = 'peepso_ranking_rollout_ready';

	/**
	 * Base engagement weights. Retained for (a) the legacy `peepso_activity_ranking_weights`
	 * filter and (b) the single seed weight, which is NOT admin-tunable and is shared by
	 * both families (it only feeds the short Hot windows anyway). The per-family comment /
	 * react / repost weights come from weights_top() / weights_hot().
	 * @return array
	 */
	public static function weights()
	{
		$w = array(
			'comment' => 8,	// a comment or reply, credited to the thread root
			'repost'  => 6,	// a repost, credited to the reposted root
			'react'   => 4,	// a reaction or classic like (x valence)
			'seed'    => 2,	// the post's own creation (short-Hot only: 1d/7d; a fading newness boost)
		);

		return apply_filters('peepso_activity_ranking_weights', $w);
	}

	/**
	 * #8037: Top and Hot are weighted differently on purpose. TOP = "most approved"
	 * (Reddit "top voted") → reactions outweigh comments. HOT = "most discussed right
	 * now" → comments outweigh reactions (Hot's read-time decay already favours recency).
	 * Each family's comment/react/repost weights are admin-tunable (Config › Navigation &
	 * Filters › Content ranking), clamped 0-10, and filter-overridable. Seed stays a single
	 * shared value from weights() (Hot-only).
	 *
	 * @return array comment/react/repost/seed
	 */
	public static function weights_top()
	{
		$w = array(
			'comment' => self::_weight_opt('ranking_weight_top_comment', 4),
			'react'   => self::_weight_opt('ranking_weight_top_react', 8),
			'repost'  => self::_weight_opt('ranking_weight_top_repost', 2),
			'seed'    => 0,	// the creation seed never feeds Top (windows gate on for_windows = 1)
		);

		return apply_filters('peepso_activity_ranking_weights_top', $w);
	}

	/**
	 * Hot-family weights (see weights_top()). Comments outweigh reactions here.
	 * @return array comment/react/repost/seed
	 */
	public static function weights_hot()
	{
		$base = self::weights();	// legacy filter + the shared seed weight
		$w = array(
			'comment' => self::_weight_opt('ranking_weight_hot_comment', 8),
			'react'   => self::_weight_opt('ranking_weight_hot_react', 4),
			'repost'  => self::_weight_opt('ranking_weight_hot_repost', 2),
			'seed'    => intval($base['seed']),
		);

		return apply_filters('peepso_activity_ranking_weights_hot', $w);
	}

	/**
	 * Read one admin-tunable weight, falling back to $default when unset / non-numeric,
	 * clamped to 0-10 (the admin UI only offers that range; clamp defends against a raw
	 * filter/DB value).
	 *
	 * @param  string $name    config option name
	 * @param  int    $default engine default when the option is unset
	 * @return int
	 */
	private static function _weight_opt($name, $default)
	{
		$v = PeepSo::get_option_new($name);
		if (NULL === $v || '' === $v || !is_numeric($v)) {
			$v = $default;
		}

		return max(0, min(10, intval($v)));
	}

	/**
	 * Current weights "generation" pointer (see WEIGHTS_REV_OPTION). Floored at 1 so a
	 * never-scored row (rank_rev 0) is always stale relative to it.
	 * @return int
	 */
	public static function weights_rev()
	{
		return max(1, intval(get_option(self::WEIGHTS_REV_OPTION, 1)));
	}

	/**
	 * Hot half-life, in seconds. Default 56h (7d/3). Filter-overridable, floored at 1h.
	 * @return int
	 */
	public static function half_life()
	{
		$h = intval(apply_filters('peepso_activity_ranking_hot_half_life', 201600));

		return max(3600, $h);
	}

	/**
	 * Per-period Hot half-lives (seconds), one per Top window.
	 *
	 * #8121 The divisor is GRADUATED (period/2, /3, /4, /5), not flat. What matters is
	 * contamination in ABSOLUTE time, and that scales with the period: a flat divisor would give
	 * the 365-day variant a tail a hundred times longer in real days than the 7-day one. Grading
	 * it tightens the discipline exactly where absolute time gets long, and buys the short
	 * variants the room they badly needed — at the old 4.8h, an older post needed 64 points (eight
	 * comments) merely to tie a brand-new empty post a day fresher, so engagement was decorative
	 * and hot_1d was pure recency. At 12h that costs 8 points, i.e. one comment.
	 *
	 * Resilience is retained: even at the loosest setting an event at twice its nominal period
	 * contributes 6.3%, and 0.1% at five times. hot_365d is deliberately left at /5 — it already
	 * sits within ~7% of a raw all-time count over a week-wide candidate set, and loosening it
	 * further would blur it into Top.
	 *
	 * NB the shape cannot be changed, only the constant: recompute() stores a pre-decayed score
	 * and hot_order_sql() re-projects it at read time, which works only because
	 * 0.5^(a/H) * 0.5^(b/H) = 0.5^((a+b)/H). That identity needs f(a+b) = f(a)f(b), whose only
	 * continuous solutions are exponentials — and every decaying exponential is steepest at age
	 * zero, so no plateau-shaped curve can keep the lazy projection.
	 *
	 * All filter-overridable, floored at 1h.
	 */
	public static function half_life_1d()   { return max(3600, intval(apply_filters('peepso_activity_ranking_hot_half_life_1d', 43200)));     }	// 12h  = 1d/2
	public static function half_life_30d()  { return max(3600, intval(apply_filters('peepso_activity_ranking_hot_half_life_30d', 648000)));   }	// 7.5d = 30d/4
	public static function half_life_365d() { return max(3600, intval(apply_filters('peepso_activity_ranking_hot_half_life_365d', 6307200))); }	// 73d  = 365d/5

	/**
	 * Valence multiplier for a reaction's weight in the ranking score.
	 *
	 * #8037: reaction "emotions" are intentionally NOT used by the algo yet — they are reserved
	 * for the future Controversial sort. Every reaction counts as plain positive engagement
	 * (valence 1), regardless of any emotion assigned to a reaction definition or the (removed)
	 * `reactions_emotions` preference. When Controversial lands, this method resolves the
	 * per-reaction emotion (-1 / 0 / +1) instead — hence it still takes the reaction-type column.
	 *
	 * @param  string $react_col SQL column holding the reaction type (unused for now)
	 * @return string SQL expression evaluating to the valence (currently always "1")
	 */
	public static function valence_sql($react_col)
	{
		return '1';
	}

	/* ---------------------------------------------------------------------- *
	 * Cron passes
	 * ---------------------------------------------------------------------- */

	/**
	 * SQL expression that decays a stored hot score to "now" at read time. This is
	 * how Hot must be consumed (e.g. the stream ORDER BY): the stored value is only
	 * meaningful relative to its rank_hot_ts anchor, so it has to be re-projected
	 * onto the current moment before rows can be compared. NULL anchors (rows not
	 * yet computed) collapse to 0.
	 *
	 * @param  string $alias Table alias holding the hot column + rank_hot_ts
	 * @return string SQL numeric expression
	 */
	public static function hot_order_sql($alias = 'psrank', $col = 'rank_score_hot_7d', $H = NULL)
	{
		if (NULL === $H) {
			$H = self::half_life();
		}
		$H   = max(3600, intval($H));
		$col = preg_replace('/[^a-z0-9_]/', '', $col);	// column-name allowlist (defensive)

		// #8122 The COALESCE must wrap the whole product, not just the score. A NULL rank_hot_ts
		// makes TIMESTAMPDIFF NULL -> GREATEST(NULL,0) NULL -> POW(0.5,NULL) NULL -> 0*NULL NULL,
		// and ORDER BY ... DESC sorts NULL LAST - so an unscored row landed below every genuinely
		// zero-scored post instead of tying at 0 and falling to the recency tie-breaker. That hits
		// every brand-new post: add_new() inserts the row without an anchor (schema default NULL)
		// until the first recompute, which is the exact opposite of the creation seed's intent.
		// Coalescing the timestamp difference instead would be wrong - POW(0.5,0) is 1, which would
		// return the stored score completely undecayed.
		return "COALESCE(`$alias`.`$col` * POW(0.5,
			GREATEST(TIMESTAMPDIFF(SECOND, `$alias`.rank_hot_ts, UTC_TIMESTAMP()), 0) / $H), 0)";
	}

	/**
	 * Safety-net pass: catch engagement written to the source tables OUTSIDE the
	 * mark_dirty() feeders — the demo-content seeder, SQL imports, migrations, or
	 * any plugin that raw-INSERTs into peepso_reactions / peepso_likes /
	 * peepso_activities. Keeps an insertion-id cursor per source table and each tick
	 * flags the thread-root of every new row dirty, so process_dirty() (run right
	 * after this) rescores them in the same tick.
	 *
	 * Keyed on the AUTO_INCREMENT id, NOT the event timestamp: seeders/imports
	 * commonly BACKDATE timestamps, so a "rows newer than time T" scan would miss
	 * them, whereas "rows with id > cursor" catches every insert regardless of its
	 * (possibly backdated) timestamp. recompute() then reads the real timestamps and
	 * places each event in the correct window.
	 *
	 * Cursors lazily initialise to the current MAX(id) on first run, so only rows
	 * inserted after the feature goes live are processed here (historical rows are
	 * the backfill's job). COMPLEMENTS mark_dirty — an insert-id scan cannot see
	 * removals (un-like / un-react / delete), which stay covered by the feeders and
	 * the reconcile pass.
	 *
	 * @return int Rows affected (approximate; for the maintenance debug timer)
	 */
	public static function process_event_tail()
	{
		global $wpdb;
		$rank  = $wpdb->prefix . PeepSoActivityRanking::TABLE;
		$act   = $wpdb->prefix . PeepSoActivity::TABLE_NAME;
		$likes = $wpdb->prefix . PeepSoLike::TABLE;
		$react = $wpdb->prefix . PeepSoReactionsModel::TABLE;
		$batch = max(1, intval(apply_filters('peepso_filter_ranking_count_batch', 500)));

		// Resolve any activity (alias `a`) to its thread root — the set-based twin of
		// PeepSoActivityRanking::resolve_root_act_id() (2 levels: comment -> reply),
		// parent linked via act_external_id = child.act_comment_object_id.
		// root = COALESCE(p2.act_id, p1.act_id, a.act_id).
		$rjoin =
			" LEFT JOIN `$act` p1 ON a.act_comment_object_id > 0
					AND p1.act_external_id = a.act_comment_object_id
					AND p1.act_module_id   = a.act_comment_module_id
			  LEFT JOIN `$act` p2 ON p1.act_comment_object_id > 0
					AND p2.act_external_id = p1.act_comment_object_id
					AND p2.act_module_id   = p1.act_comment_module_id ";

		$flagged = 0;

		// Reactions: reaction_act_id -> activity -> root.
		$flagged += self::_tail_advance('reaction_id', $react, 'peepso_ranking_tail_reaction_id', $batch,
			function ($lo, $hi) use ($wpdb, $rank, $react, $act, $rjoin) {
				return $wpdb->query($wpdb->prepare(
					"INSERT INTO `$rank` (rank_act_id, rank_act_date, rank_dirty)
						SELECT DISTINCT COALESCE(p2.act_id, p1.act_id, a.act_id), UTC_TIMESTAMP(), 1
						FROM `$react` r
						JOIN `$act` a ON a.act_id = r.reaction_act_id
						$rjoin
						WHERE r.reaction_id > %d AND r.reaction_id <= %d
						ON DUPLICATE KEY UPDATE rank_dirty = 1",
					$lo, $hi
				));
			});

		// Classic likes: like_external_id + like_module_id -> activity -> root.
		$flagged += self::_tail_advance('like_id', $likes, 'peepso_ranking_tail_like_id', $batch,
			function ($lo, $hi) use ($wpdb, $rank, $likes, $act, $rjoin) {
				return $wpdb->query($wpdb->prepare(
					"INSERT INTO `$rank` (rank_act_id, rank_act_date, rank_dirty)
						SELECT DISTINCT COALESCE(p2.act_id, p1.act_id, a.act_id), UTC_TIMESTAMP(), 1
						FROM `$likes` l
						JOIN `$act` a ON a.act_external_id = l.like_external_id AND a.act_module_id = l.like_module_id
						$rjoin
						WHERE l.like_id > %d AND l.like_id <= %d
						ON DUPLICATE KEY UPDATE rank_dirty = 1",
					$lo, $hi
				));
			});

		// New activities: own root (new post = self; comment/reply = its post) plus,
		// for reposts, the reposted original (act_repost_id already stores the root).
		$flagged += self::_tail_advance('act_id', $act, 'peepso_ranking_tail_act_id', $batch,
			function ($lo, $hi) use ($wpdb, $rank, $act, $rjoin) {
				$n = $wpdb->query($wpdb->prepare(
					"INSERT INTO `$rank` (rank_act_id, rank_act_date, rank_dirty)
						SELECT DISTINCT COALESCE(p2.act_id, p1.act_id, a.act_id), UTC_TIMESTAMP(), 1
						FROM `$act` a
						$rjoin
						WHERE a.act_id > %d AND a.act_id <= %d
						ON DUPLICATE KEY UPDATE rank_dirty = 1",
					$lo, $hi
				));
				$n += $wpdb->query($wpdb->prepare(
					"INSERT INTO `$rank` (rank_act_id, rank_act_date, rank_dirty)
						SELECT DISTINCT a.act_repost_id, UTC_TIMESTAMP(), 1
						FROM `$act` a
						WHERE a.act_id > %d AND a.act_id <= %d AND a.act_repost_id > 0
						ON DUPLICATE KEY UPDATE rank_dirty = 1",
					$lo, $hi
				));
				return intval($n);
			});

		return intval($flagged);
	}

	/**
	 * Advance one event-tail source by up to $batch new rows (id > cursor). Lazily
	 * seeds the cursor at the current MAX(id) so only future inserts are processed.
	 * $insert_cb($lo, $hi) runs the flag-roots-dirty INSERT for the (lo, hi] window.
	 *
	 * @return int Rows affected by the callback
	 */
	private static function _tail_advance($id_col, $src_table, $option_key, $batch, $insert_cb)
	{
		global $wpdb;

		$cursor = get_option($option_key, FALSE);
		if (FALSE === $cursor) {
			// First run: watch only rows inserted from now on.
			$max = intval($wpdb->get_var("SELECT COALESCE(MAX(`$id_col`), 0) FROM `$src_table`"));
			add_option($option_key, $max, '', 'no');

			return 0;
		}
		$cursor = intval($cursor);

		$new_max = $wpdb->get_var($wpdb->prepare(
			"SELECT MAX(t.id) FROM (SELECT `$id_col` AS id FROM `$src_table` WHERE `$id_col` > %d ORDER BY `$id_col` ASC LIMIT %d) t",
			$cursor,
			$batch
		));
		if (NULL === $new_max) {
			return 0;	// nothing new
		}
		$new_max = intval($new_max);

		$affected = intval(call_user_func($insert_cb, $cursor, $new_max));

		update_option($option_key, $new_max);

		return $affected;
	}

	/**
	 * Freshness pass: recompute every row flagged dirty by an event feeder.
	 * Catches additions AND removals (un-like, deletes) that a timestamp scan
	 * cannot. recompute() clears the dirty flag on the rows it touches.
	 *
	 * @return int Rows recomputed
	 */
	public static function process_dirty()
	{
		global $wpdb;
		$table = $wpdb->prefix . PeepSoActivityRanking::TABLE;
		$limit = max(1, intval(apply_filters('peepso_filter_ranking_count_batch', 500)));

		// A row is dirty if flagged by an event feeder OR scored under an older weights
		// generation (rank_rev < current) — the latter drains an admin weight change.
		$cur = self::weights_rev();

		$ids = $wpdb->get_col($wpdb->prepare(
			"SELECT rank_act_id FROM `$table` WHERE (rank_dirty = 1 OR rank_rev < %d) ORDER BY rank_act_id ASC LIMIT %d",
			$cur,
			$limit
		));

		return self::recompute($ids);
	}

	/**
	 * Correctness pass: cursor-batched wrap-around over the working set
	 * (recent engagement OR any non-zero score). Windowed scores age out with no
	 * new events, and this self-heals counter drift or bulk operations
	 * (user deletion / moderation) that fire no per-event hooks.
	 *
	 * @return int Rows recomputed
	 */
	public static function process_reconcile()
	{
		global $wpdb;
		$table = $wpdb->prefix . PeepSoActivityRanking::TABLE;
		$limit = max(1, intval(apply_filters('peepso_filter_ranking_count_batch', 500)));

		$cursor = intval(get_option('peepso_ranking_reconcile_cursor', 0));
		$cur    = self::weights_rev();	// stale-rev rows (weight change) join the sweep

		$ids = $wpdb->get_col($wpdb->prepare(
			"SELECT rank_act_id FROM `$table`
				WHERE rank_act_id > %d
				AND (
					rank_last_engagement >= (UTC_TIMESTAMP() - INTERVAL 31 DAY)
					OR rank_score_1d <> 0 OR rank_score_7d <> 0 OR rank_score_30d <> 0 OR rank_score_hot_7d <> 0
					OR rank_rev < %d
				)
				ORDER BY rank_act_id ASC LIMIT %d",
			$cursor,
			$cur,
			$limit
		));

		if (empty($ids)) {
			// Reached the end of the working set — wrap back to the start.
			update_option('peepso_ranking_reconcile_cursor', 0);

			return 0;
		}

		update_option('peepso_ranking_reconcile_cursor', intval(end($ids)));

		return self::recompute($ids);
	}

	/**
	 * Slow long-window pass: a separate, rate-limited cursor wrap over the 1-YEAR working
	 * set (engagement in the last ~366d, or any non-zero long score). Keeps rank_score_365d
	 * and the long-half-life Hot columns fresh for posts whose engagement is older than the
	 * 30-day fast reconcile set, WITHOUT enlarging that fast set — so short-window freshness
	 * / aging-out is unaffected. Its own cursor + smaller batch spread the year-tail over
	 * many ticks; recompute() refreshes every column, so nothing diverges.
	 *
	 * @return int Rows recomputed
	 */
	public static function process_reconcile_long()
	{
		global $wpdb;
		$table = $wpdb->prefix . PeepSoActivityRanking::TABLE;
		$limit = max(1, intval(apply_filters('peepso_filter_ranking_count_batch', 500)));

		$cursor = intval(get_option('peepso_ranking_reconcile_long_cursor', 0));
		$cur    = self::weights_rev();	// stale-rev rows in the year tail join this sweep too

		$ids = $wpdb->get_col($wpdb->prepare(
			"SELECT rank_act_id FROM `$table`
				WHERE rank_act_id > %d
				AND (
					rank_last_engagement >= (UTC_TIMESTAMP() - INTERVAL 366 DAY)
					OR rank_score_365d <> 0 OR rank_score_hot_365d <> 0 OR rank_score_hot_30d <> 0
					OR rank_rev < %d
				)
				ORDER BY rank_act_id ASC LIMIT %d",
			$cursor,
			$cur,
			$limit
		));

		if (empty($ids)) {
			// Reached the end of the year working set — wrap back to the start.
			update_option('peepso_ranking_reconcile_long_cursor', 0);

			return 0;
		}

		update_option('peepso_ranking_reconcile_long_cursor', intval(end($ids)));

		return self::recompute($ids);
	}

	/**
	 * One-time backfill (replaces the old broken rebuild_rank). Walks root activities
	 * newest-first in fixed act_id windows and hydrates a ranking row for EVERY published
	 * post — including zero-engagement ones. recompute() then scores each (0 when there is
	 * no engagement; the short-Hot creation seed still applies), so every post has a visible
	 * row and nothing is ever stuck "pending re-count" for lack of a row. This makes the
	 * table's post coverage complete (easier to debug), at the cost of one row per post.
	 *
	 * Because the walk starts at MAX(act_id) and descends, recent/visible content is scored
	 * within the first few ticks; the long tail is scanned in bounded windows. New posts going
	 * forward are covered by process_event_tail(); this backfill hydrates the historical tail.
	 * Runs only while the `peepso_ranking_backfill_cursor` option exists; deletes it when the
	 * walk passes act_id 0.
	 *
	 * @return int Rows backfilled
	 */
	public static function backfill_step()
	{
		$cursor = get_option('peepso_ranking_backfill_cursor', FALSE);
		if (FALSE === $cursor) {
			return 0;	// backfill complete
		}
		$cursor = intval($cursor);

		global $wpdb;
		$act_table  = $wpdb->prefix . PeepSoActivity::TABLE_NAME;
		$rank_table = $wpdb->prefix . PeepSoActivityRanking::TABLE;
		$posts      = $wpdb->posts;
		$cpt_post   = PeepSoActivityStream::CPT_POST;

		// act_id window scanned per tick (bounds work regardless of match density).
		$window = max(1, intval(apply_filters('peepso_filter_ranking_backfill_batch', 20000)));
		$low    = max(0, $cursor - $window);

		// By default hydrate EVERY published post root in (low, cursor] — cold zero-engagement posts
		// get a row too (recompute() scores them 0), so every post has a visible baseline. A very
		// large community that wants the ranking table proportional to ACTIVE content can flip this
		// filter off to restore engagement/recency-only hydration (any historical engagement within
		// the ~all-time default horizon, or created in the last few days). The per-tick act_id window
		// keeps each pass bounded either way.
		$include_cold = (bool) apply_filters('peepso_filter_ranking_backfill_include_cold', true);

		if ($include_cold) {
			$roots = $wpdb->get_col($wpdb->prepare(
				"SELECT a.act_id FROM `$act_table` a
					JOIN `$posts` p ON p.ID = a.act_external_id
						AND p.post_type = %s AND p.post_status = 'publish'
					WHERE a.act_comment_object_id = 0
						AND a.act_id > %d AND a.act_id <= %d
					ORDER BY a.act_id DESC",
				$cpt_post, $low, $cursor
			));
		} else {
			// Engagement/recency-only: a published root created in the last few days, or with any
			// historical reaction / like / comment (within the ~all-time default horizon). TIMESTAMP
			// columns (reaction/like) compare vs NOW(); post_date_gmt is UTC so it compares vs
			// UTC_TIMESTAMP() — the same time-domain rule scoring uses. Each branch is an index ref
			// lookup, so the predicate stays cheap even on a huge catalog (the point of this opt-out).
			// The repost signal is deliberately omitted: `rp.act_repost_id = a.act_id` cannot use its
			// index when reposts are sparse (a single dominant value) and mis-plans into a full-table
			// scan at scale — and a repost on an otherwise-cold old root is a rare case that
			// process_event_tail() hydrates going forward anyway.
			$likes    = $wpdb->prefix . PeepSoLike::TABLE;
			$react    = $wpdb->prefix . PeepSoReactionsModel::TABLE;
			$eng_days = max(1, intval(apply_filters('peepso_activity_ranking_backfill_engagement_days', 36525)));
			$new_days = max(1, intval(apply_filters('peepso_activity_ranking_backfill_new_days', 7)));
			$roots = $wpdb->get_col($wpdb->prepare(
				"SELECT a.act_id FROM `$act_table` a
					JOIN `$posts` p ON p.ID = a.act_external_id
						AND p.post_type = %s AND p.post_status = 'publish'
					WHERE a.act_comment_object_id = 0
						AND a.act_id > %d AND a.act_id <= %d
						AND (
							p.post_date_gmt >= (UTC_TIMESTAMP() - INTERVAL %d DAY)
							OR EXISTS (SELECT 1 FROM `$react` r
								WHERE r.reaction_act_id = a.act_id
									AND r.reaction_timestamp >= (NOW() - INTERVAL %d DAY))
							OR EXISTS (SELECT 1 FROM `$likes` l
								WHERE l.like_external_id = a.act_external_id
									AND l.like_module_id = a.act_module_id
									AND l.like_timestamp >= (NOW() - INTERVAL %d DAY))
							OR EXISTS (SELECT 1 FROM `$act_table` c
								INNER JOIN `$posts` cp ON cp.ID = c.act_external_id
								WHERE c.act_comment_object_id = a.act_external_id
									AND c.act_comment_module_id = a.act_module_id
									AND cp.post_date_gmt >= (UTC_TIMESTAMP() - INTERVAL %d DAY))
						)
					ORDER BY a.act_id DESC",
				$cpt_post, $low, $cursor, $new_days, $eng_days, $eng_days, $eng_days
			));
		}

		$count = 0;
		if (!empty($roots)) {
			$ids     = array_map('intval', $roots);
			$ids_csv = implode(',', $ids);

			// Ensure a rank row exists for each (recompute only UPDATEs existing rows). ON DUPLICATE
			// KEY is safe under the UNIQUE index — a cheap key-only upsert over the whole window.
			$wpdb->query(
				"INSERT INTO `$rank_table` (rank_act_id, rank_act_date)
					SELECT a.act_id, UTC_TIMESTAMP() FROM `$act_table` a WHERE a.act_id IN ($ids_csv)
					ON DUPLICATE KEY UPDATE rank_act_id = rank_act_id"
			);

			// Recompute in count-batch chunks so a dense window (e.g. a bulk import of publish posts)
			// never builds one huge multi-source aggregate UPDATE — the scan window and the recompute
			// batch stay independent, matching the cap the other passes use.
			$batch = max(1, intval(apply_filters('peepso_filter_ranking_count_batch', 500)));
			foreach (array_chunk($ids, $batch) as $chunk) {
				$count += self::recompute($chunk);
			}
		}

		// Advance to the bottom of this window; done once we pass act_id 0.
		if ($low <= 0) {
			delete_option('peepso_ranking_backfill_cursor');
		} else {
			update_option('peepso_ranking_backfill_cursor', $low);
		}

		return $count;
	}

	/* ---------------------------------------------------------------------- *
	 * Core recompute
	 * ---------------------------------------------------------------------- */

	/**
	 * Recompute all four scores + legacy counters for a batch of ROOT activity ids.
	 *
	 * The UPDATE is driven from the ranking rows LEFT JOINed to an aggregate of
	 * every event that rolls up to those roots, so a root that has lost all its
	 * engagement gets explicit zeros (not a stale score). The dirty flag is
	 * cleared for the whole batch.
	 *
	 * @param  array $ids Root activity ids (callers guarantee these are roots)
	 * @return int Rows updated
	 */
	public static function recompute($ids)
	{
		if (empty($ids) || !is_array($ids)) {
			return 0;
		}

		$ids = array_values(array_unique(array_map('intval', $ids)));
		$ids = array_filter($ids, function ($v) { return $v > 0; });
		if (empty($ids)) {
			return 0;
		}
		$ids_csv = implode(',', $ids);

		global $wpdb;
		$rank_table = $wpdb->prefix . PeepSoActivityRanking::TABLE;

		$w_top = self::weights_top();
		$w_hot = self::weights_hot();
		$rev   = self::weights_rev();
		$H     = self::half_life();
		$agg   = self::_aggregate_sql($ids_csv, $w_top, $w_hot, $H);

		// COALESCE everything so roots with no events (or an unpublished / deleted
		// root post that produced no seed row) collapse to zeros.
		$sql = "UPDATE `$rank_table` r
			LEFT JOIN ( $agg ) agg ON agg.root_act_id = r.rank_act_id
			SET
				r.rank_score_1d      = COALESCE(agg.s1d, 0),
				r.rank_score_7d      = COALESCE(agg.s7d, 0),
				r.rank_score_30d     = COALESCE(agg.s30d, 0),
				r.rank_score_365d    = COALESCE(agg.s365, 0),
				r.rank_score_all     = COALESCE(agg.sall, 0),
				r.rank_score_hot_7d  = CASE WHEN ABS(COALESCE(agg.shot, 0))    < " . self::HOT_FLOOR . " THEN 0 ELSE COALESCE(agg.shot, 0) END,
				r.rank_score_hot_1d  = CASE WHEN ABS(COALESCE(agg.shot1, 0))   < " . self::HOT_FLOOR . " THEN 0 ELSE COALESCE(agg.shot1, 0) END,
				r.rank_score_hot_30d = CASE WHEN ABS(COALESCE(agg.shot30, 0))  < " . self::HOT_FLOOR . " THEN 0 ELSE COALESCE(agg.shot30, 0) END,
				r.rank_score_hot_365d= CASE WHEN ABS(COALESCE(agg.shot365, 0)) < " . self::HOT_FLOOR . " THEN 0 ELSE COALESCE(agg.shot365, 0) END,
				r.rank_hot_ts        = UTC_TIMESTAMP(),
				r.rank_up_30d        = COALESCE(agg.up30, 0),
				r.rank_down_30d      = COALESCE(agg.down30, 0),
				r.rank_act_comments  = COALESCE(agg.c_cnt, 0),
				r.rank_act_likes     = COALESCE(agg.l_cnt, 0),
				r.rank_act_shares    = COALESCE(agg.s_cnt, 0),
				r.rank_last_engagement = CASE WHEN agg.min_age IS NULL THEN NULL
									  ELSE (UTC_TIMESTAMP() - INTERVAL GREATEST(agg.min_age, 0) SECOND) END,
				r.rank_rev           = $rev,
				r.rank_dirty         = 0
			WHERE r.rank_act_id IN ($ids_csv)";

		return intval($wpdb->query($sql));
	}

	/**
	 * The GROUP BY aggregate over the UNION ALL of every event source. Each source
	 * emits the same shape:
	 *   root_act_id, wv (weighted valence), age (seconds),
	 *   up_flag, down_flag (post-directed like/reaction direction),
	 *   for_windows (1 for real engagement, 0 for the Hot-only seed),
	 *   is_comment, is_like, is_share (legacy all-time counters).
	 *
	 * @return string
	 */
	private static function _aggregate_sql($ids_csv, $w_top, $w_hot, $H)
	{
		$D = self::DAY;
		$W = self::WEEK;
		$M = self::MONTH;
		$Y = self::YEAR;
		$H1   = self::half_life_1d();
		$H30  = self::half_life_30d();
		$H365 = self::half_life_365d();

		$union = self::_event_union_sql($ids_csv, $w_top, $w_hot);

		// #8037 Top windows sum the Top-weighted value (wv_top); the Hot columns sum the
		// Hot-weighted value (wv_hot). Every event carries both, computed once in the union.
		return "SELECT
				root_act_id,
				SUM(CASE WHEN for_windows = 1 AND age <= $D THEN wv_top ELSE 0 END) AS s1d,
				SUM(CASE WHEN for_windows = 1 AND age <= $W THEN wv_top ELSE 0 END) AS s7d,
				SUM(CASE WHEN for_windows = 1 AND age <= $M THEN wv_top ELSE 0 END) AS s30d,
				SUM(CASE WHEN for_windows = 1 AND age <= $Y THEN wv_top ELSE 0 END) AS s365,
				-- All-time Top: every engagement, no age cap (excludes only the Hot-only seed).
				-- Free here — the union already scans all events for the root; this just drops the window.
				SUM(CASE WHEN for_windows = 1 THEN wv_top ELSE 0 END) AS sall,
				SUM(wv_hot * POW(0.5, GREATEST(age, 0) / $H))    AS shot,
				SUM(wv_hot * POW(0.5, GREATEST(age, 0) / $H1))   AS shot1,
				-- The creation seed is a NEWNESS boost, so it feeds only the short Hots (1d/7d).
				-- Excluded from the long Hots: at a 6d / 73d half-life a one-time seed would
				-- linger as a near-constant +2 on every post for weeks/months (noise), and it
				-- would strand seed-only posts in a maintenance dead zone (no engagement, so no
				-- reconcile pass reaches them). The month/year Hots are pure engagement.
				SUM(CASE WHEN for_windows = 1 THEN wv_hot * POW(0.5, GREATEST(age, 0) / $H30)  ELSE 0 END) AS shot30,
				SUM(CASE WHEN for_windows = 1 THEN wv_hot * POW(0.5, GREATEST(age, 0) / $H365) ELSE 0 END) AS shot365,
				SUM(CASE WHEN age <= $M THEN up_flag ELSE 0 END) AS up30,
				SUM(CASE WHEN age <= $M THEN down_flag ELSE 0 END) AS down30,
				SUM(is_comment) AS c_cnt,
				SUM(is_like) AS l_cnt,
				SUM(is_share) AS s_cnt,
				MIN(CASE WHEN for_windows = 1 THEN age END) AS min_age
			FROM ( $union ) ev
			GROUP BY root_act_id";
	}

	/**
	 * UNION ALL of the five event sources (seed, comments, reactions, likes,
	 * reposts). Reactions/likes/comments are gathered through the target->root map
	 * so that engagement on a comment or reply rolls up to the thread root.
	 *
	 * #8037: every event carries TWO weighted values — wv_top (Top family) and wv_hot
	 * (Hot family) — so the two families can weight the same signals differently in a
	 * single scan. The aggregate sums wv_top into the Top windows and wv_hot into the
	 * Hot columns. Seed weights differ per family too (0 for Top, since it never feeds a
	 * Top window; the Hot seed weight from weights()).
	 */
	private static function _event_union_sql($ids_csv, $w_top, $w_hot)
	{
		global $wpdb;

		$act   = $wpdb->prefix . PeepSoActivity::TABLE_NAME;
		$posts = $wpdb->posts;
		$likes = $wpdb->prefix . PeepSoLike::TABLE;
		$react = $wpdb->prefix . PeepSoReactionsModel::TABLE;

		$cpt_post    = PeepSoActivityStream::CPT_POST;
		$cpt_comment = PeepSoActivityStream::CPT_COMMENT;

		$wt_comment = intval($w_top['comment']);
		$wt_repost  = intval($w_top['repost']);
		$wt_react   = intval($w_top['react']);
		$wt_seed    = intval($w_top['seed']);	// 0 — seed never feeds Top

		$wh_comment = intval($w_hot['comment']);
		$wh_repost  = intval($w_hot['repost']);
		$wh_react   = intval($w_hot['react']);
		$wh_seed    = intval($w_hot['seed']);

		$val = self::valence_sql('re.reaction_type');
		$map = self::_map_sql($ids_csv);

		// Column order for every SELECT:
		//   root_act_id, wv_top, wv_hot, age, up_flag, down_flag, for_windows, is_comment, is_like, is_share

		// Seed: the root post's own creation (Hot only -> for_windows = 0).
		$seed = "SELECT m.root AS root_act_id, $wt_seed AS wv_top, $wh_seed AS wv_hot,
				TIMESTAMPDIFF(SECOND, p.post_date_gmt, UTC_TIMESTAMP()) AS age,
				0 AS up_flag, 0 AS down_flag, 0 AS for_windows, 0 AS is_comment, 0 AS is_like, 0 AS is_share
			FROM ( $map ) m
			JOIN `$posts` p ON p.ID = m.t_ext AND p.post_type = '$cpt_post' AND p.post_status = 'publish'
			WHERE m.is_root = 1";

		// Comments / replies: every non-root node in the map is one comment event.
		$comments = "SELECT m.root, $wt_comment, $wh_comment,
				TIMESTAMPDIFF(SECOND, p.post_date_gmt, UTC_TIMESTAMP()),
				0, 0, 1, 1, 0, 0
			FROM ( $map ) m
			JOIN `$posts` p ON p.ID = m.t_ext AND p.post_type = '$cpt_comment' AND p.post_status = 'publish'
			WHERE m.is_root = 0";

		// Reactions on the root or any of its descendants. NOW() domain (TIMESTAMP).
		$reactions = "SELECT m.root, ($wt_react * $val), ($wh_react * $val),
				TIMESTAMPDIFF(SECOND, re.reaction_timestamp, NOW()),
				CASE WHEN m.is_root = 1 AND ($val) > 0 THEN 1 ELSE 0 END,
				CASE WHEN m.is_root = 1 AND ($val) < 0 THEN 1 ELSE 0 END,
				1, 0, 1, 0
			FROM ( $map ) m
			JOIN `$react` re ON re.reaction_act_id = m.t_act";

		// Classic likes (peepso_likes) — keyed on external_id + module_id. Always +1.
		$likes_src = "SELECT m.root, $wt_react, $wh_react,
				TIMESTAMPDIFF(SECOND, lk.like_timestamp, NOW()),
				CASE WHEN m.is_root = 1 THEN 1 ELSE 0 END, 0,
				1, 0, 1, 0
			FROM ( $map ) m
			JOIN `$likes` lk ON lk.like_external_id = m.t_ext AND lk.like_module_id = m.t_mod";

		// Reposts credit the reposted root directly (act_repost_id = root act_id).
		$reposts = "SELECT rep.act_repost_id,
				$wt_repost, $wh_repost,
				TIMESTAMPDIFF(SECOND, p.post_date_gmt, UTC_TIMESTAMP()),
				0, 0, 1, 0, 0, 1
			FROM `$act` rep
			JOIN `$posts` p ON p.ID = rep.act_external_id AND p.post_status = 'publish'
			WHERE rep.act_repost_id IN ($ids_csv)";

		return "$seed UNION ALL $comments UNION ALL $reactions UNION ALL $likes_src UNION ALL $reposts";
	}

	/**
	 * Target -> root map for a batch of root ids. Produces one row per node that
	 * can carry engagement (the root itself, its direct comments, and their
	 * replies — PeepSo threads are at most two levels deep):
	 *   t_act (activity id), t_ext (external/post id), t_mod (module id),
	 *   root (root activity id), is_root (1 for the root node).
	 *
	 * Filtering by `root IN (ids)` up front means only events belonging to the
	 * batch are ever scanned (indexed lookups down the comment tree).
	 */
	private static function _map_sql($ids_csv)
	{
		global $wpdb;
		$act = $wpdb->prefix . PeepSoActivity::TABLE_NAME;

		$roots = "SELECT a.act_id AS t_act, a.act_external_id AS t_ext, a.act_module_id AS t_mod,
				a.act_id AS root, 1 AS is_root
			FROM `$act` a WHERE a.act_id IN ($ids_csv)";

		$level1 = "SELECT c1.act_id, c1.act_external_id, c1.act_module_id, r.act_id, 0
			FROM `$act` r
			JOIN `$act` c1 ON c1.act_comment_object_id = r.act_external_id AND c1.act_comment_module_id = r.act_module_id
			WHERE r.act_id IN ($ids_csv)";

		$level2 = "SELECT c2.act_id, c2.act_external_id, c2.act_module_id, r.act_id, 0
			FROM `$act` r
			JOIN `$act` c1 ON c1.act_comment_object_id = r.act_external_id AND c1.act_comment_module_id = r.act_module_id
			JOIN `$act` c2 ON c2.act_comment_object_id = c1.act_external_id AND c2.act_comment_module_id = c1.act_module_id
			WHERE r.act_id IN ($ids_csv)";

		return "$roots UNION ALL $level1 UNION ALL $level2";
	}

	/* ---------------------------------------------------------------------- *
	 * Hidden rollout — one-time reveal of the Top/Hot sorts on the frontend
	 * ---------------------------------------------------------------------- */

	/**
	 * Published post roots and how many still await (re)counting — the universe the admin
	 * "posts pending" gauge measures. Shared by the gauge ajax and the rollout reveal so the two
	 * never drift.
	 * @return array total => int, pending => int
	 */
	public static function post_processing_stats()
	{
		global $wpdb;
		$table = $wpdb->prefix . PeepSoActivityRanking::TABLE;
		$posts = $wpdb->posts;
		$cpt   = PeepSoActivityStream::CPT_POST;
		$cur   = self::weights_rev();

		// Two cheap single-table counts instead of a posts × activities × ranking join — that join
		// does not scale (it is a multi-MINUTE query at ~1M posts). "total" = the published-post
		// universe (served by wp_posts' type_status_date index); "processed" = current-generation,
		// non-dirty ranking rows (≈ one row per hydrated post root, keyed by root act_id). A post is
		// pending until its row exists AND is scored at the current weights generation, so
		// pending = total − processed. Both counts are sub-second even at millions of rows.
		$total = intval($wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM `$posts` WHERE post_type = %s AND post_status = 'publish'",
			$cpt
		)));
		$processed = intval($wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM `$table` WHERE rank_dirty = 0 AND rank_rev >= %d",
			$cur
		)));

		return array(
			'total'   => $total,
			'pending' => max(0, $total - $processed),
		);
	}

	/**
	 * Reveal threshold: % of posts that must be processed before the Top/Hot sorts appear on the
	 * frontend. Filter-overridable; 0 disables the gate entirely (always visible).
	 * @return int 0-100
	 */
	public static function rollout_threshold()
	{
		return max(0, min(100, intval(apply_filters('peepso_filter_ranking_rollout_pct', 75))));
	}

	/**
	 * STICKY gate: may the Top/Hot stream sorts appear on the frontend yet? Hidden during the
	 * one-time initial index build until reveal_rollout_if_ready() flips the flag at the threshold;
	 * once revealed it STAYS revealed (rebuilds / weight changes never re-gate). Cheap — one
	 * (autoloaded) option read — so safe on every stream render.
	 * @return bool
	 */
	public static function rollout_ready()
	{
		if (get_option(self::ROLLOUT_READY_OPTION, 0)) {
			return TRUE;
		}
		return (0 === self::rollout_threshold());	// threshold 0 => gate disabled
	}

	/**
	 * #8117 VISIBILITY gate for a ranking stream-sort family. The ranking ENGINE is a core
	 * mechanic and keeps running regardless — groups/pages "Popular Posts" (rank_score_30d), the
	 * admin Community Pulse and new-app's popular-photos endpoint (hot_7d) all read the ranking
	 * table directly and are unaffected. This only decides whether users are OFFERED the Top / Hot
	 * sorts in the stream picker:
	 *
	 *   add_filter('peepso_filter_stream_family_top_enabled', '__return_false');
	 *   add_filter('peepso_filter_stream_family_hot_enabled', '__return_false');
	 *
	 * Orthogonal to rollout_ready(): that is a temporary "not indexed yet" gate, this is a
	 * permanent site-owner choice. Both call sites (the families closure and the leaves loop in
	 * peepso.php) share this one function so the two arrays can never disagree.
	 *
	 * @param  string $family 'top' or 'hot'
	 * @return bool
	 */
	public static function family_enabled($family)
	{
		return (bool) apply_filters('peepso_filter_stream_family_' . $family . '_enabled', TRUE);
	}

	/**
	 * Cron-side: flip the sticky reveal flag once the initial index reaches the threshold. Cheap
	 * no-op once revealed. Called each ranking tick (and each gauge poll) until it fires.
	 * @return void
	 */
	public static function reveal_rollout_if_ready()
	{
		if (get_option(self::ROLLOUT_READY_OPTION, 0)) {
			return;
		}
		$pct = self::rollout_threshold();
		if (0 === $pct) {
			update_option(self::ROLLOUT_READY_OPTION, 1);
			return;
		}
		$s = self::post_processing_stats();
		$processed = $s['total'] > 0 ? (($s['total'] - $s['pending']) / $s['total']) * 100 : 100;
		if ($processed >= $pct) {
			update_option(self::ROLLOUT_READY_OPTION, 1);
		}
	}

	/**
	 * The ranking stream-sort registry. Each key describes one Top or Hot sort:
	 *   type  'popular' (windowed weighted volume — sort the indexed column DESC) or
	 *         'hot' (read-time exponentially decayed — sort hot_order_sql() DESC).
	 *   col   the score column; hl (hot only) its read-time decay half-life (seconds).
	 * Both families cover the same periods, and every key is coded `<family>_<N>d`
	 * (popular_1d/7d/30d/365d, hot_1d/7d/30d/365d). Consumed by the forthcoming stream
	 * integration via order_by_sql(); defined here so the sort keys live next to the
	 * columns that populate them.
	 * @return array
	 */
	public static function stream_sorts()
	{
		return array(
			'popular_1d'   => array('type' => 'popular', 'col' => 'rank_score_1d'),
			'popular_7d'   => array('type' => 'popular', 'col' => 'rank_score_7d'),
			'popular_30d'  => array('type' => 'popular', 'col' => 'rank_score_30d'),
			'popular_365d' => array('type' => 'popular', 'col' => 'rank_score_365d'),
			// All-time Top (no window cap): the most-voted content ever, for old communities.
			// Top-only — an "all-time Hot" is a contradiction (Hot is by definition recency-decayed).
			'popular_all'  => array('type' => 'popular', 'col' => 'rank_score_all'),
			'hot_1d'       => array('type' => 'hot', 'col' => 'rank_score_hot_1d',   'hl' => self::half_life_1d()),
			'hot_7d'       => array('type' => 'hot', 'col' => 'rank_score_hot_7d',   'hl' => self::half_life()),
			'hot_30d'      => array('type' => 'hot', 'col' => 'rank_score_hot_30d',  'hl' => self::half_life_30d()),
			'hot_365d'     => array('type' => 'hot', 'col' => 'rank_score_hot_365d', 'hl' => self::half_life_365d()),
		);
	}

	/**
	 * Full ORDER BY body for a ranking stream sort: the score first (popular = the plain
	 * indexed column; hot = the read-time decayed value), then the secondary "new posts &
	 * comments" recency tie-breaker so zero / tied ranks fall back to most-recent activity —
	 * matching PeepSo's `new_and_commented` sort (wp_posts.post_modified_gmt DESC). This is
	 * what keeps a mostly-zero column (e.g. hot_1d) a usable ordering.
	 *
	 * @param  string $key         a stream_sorts() key
	 * @param  string $rank_alias  alias of the joined peepso_activity_ranking row
	 * @param  string $posts_table wp_posts table/alias for the tie-breaker (default $wpdb->posts)
	 * @return string|false        ORDER BY body, or FALSE for an unknown key
	 */
	public static function order_by_sql($key, $rank_alias = 'psrank', $posts_table = NULL)
	{
		$sorts = self::stream_sorts();
		if (!isset($sorts[$key])) {
			return FALSE;
		}

		global $wpdb;
		if (NULL === $posts_table) {
			$posts_table = $wpdb->posts;
		}

		$def = $sorts[$key];
		if ('hot' === $def['type']) {
			$primary = self::hot_order_sql($rank_alias, $def['col'], $def['hl']);
		} else {
			$col = preg_replace('/[^a-z0-9_]/', '', $def['col']);
			$primary = "COALESCE(`$rank_alias`.`$col`, 0)";
		}

		return " $primary DESC, `{$posts_table}`.`post_modified_gmt` DESC ";
	}
}

// EOF
