<?php
/**
 * Durable counters for hostile-event accounting.
 *
 * Exists because the per-IP event ledger used to live in a single wp_options row
 * (csdt_autoblock_counts), read and rewritten on EVERY hostile request:
 *
 *   1. read the whole serialised blob
 *   2. append one timestamp
 *   3. write the whole blob back
 *
 * Two requests arriving together both read the same blob and both write it, so one
 * increment is lost. Concurrency is exactly what an automated attack has, which
 * means the counter undercounted precisely as the attack got faster: a threshold of
 * 30 could take 50 or more real attempts to trip. That is a correctness bug, not a
 * performance note, and it is the reason this class exists.
 *
 * It also fixed two lesser faults of the same design: the blob was rewritten by
 * every attacker request (so an attack cost the site's database more than the
 * attacker), and the ledger was capped at MAX_TRACKED_IPS = 500, so the 501st
 * attacking address in a window was not counted at all.
 *
 * Atomicity here comes from MySQL, `INSERT ... ON DUPLICATE KEY UPDATE hits =
 * hits + 1` is one indivisible statement. It deliberately does NOT come from the
 * object cache: the APCu drop-in this project used to ship implemented incr() as
 * get-then-set, so it had the identical lost-update bug, other drop-ins may too, and
 * a persistent object cache may not exist at all.
 *
 * @package CSDT_DevTools
 * @since   1.10.436
 */

defined( 'ABSPATH' ) || exit;

// The request-metadata accessor, loaded here as well as by the main file so this class
// can be loaded on its own (the build-time tests do) without a load-order dependency.
require_once __DIR__ . '/class-request.php';

class CSDT_Storage {

	/**
	 * Bump to re-run dbDelta. 2 added the firewall event log; 3 the activity log; 5 the live
	 * traffic feed.
	 */
	const SCHEMA_VERSION = '5';

	/**
	 * Autoloaded on purpose: it is read on the hostile-event path, and a
	 * non-autoloaded read would add a query to decide whether to add a query.
	 */
	const OPTION_SCHEMA_VERSION = 'csdt_storage_schema';

	/**
	 * Counter granularity.
	 *
	 * Fixed buckets rather than individual timestamps, because a bucket can be
	 * incremented atomically and a list of timestamps cannot. The cost is that a
	 * window boundary lands inside a bucket, so count_events() includes at most
	 * BUCKET_SECONDS of events that are marginally older than the window asked
	 * for. That errs toward blocking slightly SOONER than configured, never
	 * later, which is the safe direction for a defence. Against thresholds
	 * measured in hours it is not observable.
	 */
	const BUCKET_SECONDS = 60;

	/**
	 * How long counter rows are kept. Comfortably longer than the widest window
	 * the admin can select (24 hours), so pruning can never delete a row a live
	 * threshold still needs.
	 */
	const EVENT_RETENTION = 2 * DAY_IN_SECONDS;

	/**
	 * Rows deleted per DELETE. An unbounded DELETE on a table that grew during an
	 * attack holds locks long enough to take the site down while cleaning up
	 * after an attack that has already stopped.
	 */
	const PRUNE_BATCH = 500;

	/** Wall-clock budget for one prune run, seconds. */
	const PRUNE_BUDGET = 5;

	/**
	 * How long firewall log rows are kept.
	 *
	 * Longer than the counters because this log is read by a human deciding whether the
	 * mode can be promoted from Log to Block, and that decision wants weeks of the site's
	 * own traffic rather than hours.
	 */
	const LOG_RETENTION = 30 * DAY_IN_SECONDS;

	/**
	 * Hard row ceiling for the log, oldest-first, as a backstop.
	 *
	 * Retention alone is not enough: if cron stops running, which is exactly what happens
	 * on a struggling site under attack, a time-based cutoff deletes nothing and the table
	 * grows without limit. This is the second, independent bound.
	 */
	const LOG_MAX_ROWS = 100000;

	/** Longest path recorded. Truncated, never the query string. */
	const LOG_PATH_MAX = 255;

	/**
	 * How long live traffic rows are kept.
	 *
	 * A day, not the month the firewall log keeps. The feed answers "what is reaching this site
	 * right now", which nobody asks about last Tuesday, and every row is one INSERT paid by an
	 * ordinary visitor. Evidence worth keeping (a rule match, a block) is already recorded, deduped,
	 * in the firewall log and the auto-block log, which outlive this table on purpose.
	 */
	const TRAFFIC_RETENTION = DAY_IN_SECONDS;

	/**
	 * Hard row ceiling for the live traffic table.
	 *
	 * Enforced by id, not by COUNT(*): ids only grow, so every row with an id more than
	 * TRAFFIC_MAX_ROWS below the newest is past the ceiling. That is one primary-key range
	 * delete, cheap enough to run from the write path itself (see TRAFFIC_TRIM_EVERY), which is
	 * the point. A ceiling that only cron enforces is no ceiling on the site where cron has
	 * stopped, and a write-per-request table is exactly the one that must not grow unbounded.
	 * Gaps in the id sequence make it keep slightly FEWER rows, never more.
	 */
	const TRAFFIC_MAX_ROWS = 20000;

	/**
	 * One write in this many also trims rows past TRAFFIC_MAX_ROWS.
	 *
	 * Each trim removes up to PRUNE_BATCH rows while only TRAFFIC_TRIM_EVERY arrived since the
	 * last, so the inline trim always outruns the inserts, and the other 99 requests in a
	 * hundred pay for their INSERT and nothing else.
	 */
	const TRAFFIC_TRIM_EVERY = 100;

	/** Longest user agent recorded, characters. Enough to classify and recognise it. */
	const TRAFFIC_UA_MAX = 200;

	/**
	 * Request types a traffic row can carry. `kind`, not `type`, as the column name: TYPE is a
	 * MySQL keyword, and the reason signal_key is not called signal applies.
	 */
	const TRAFFIC_KINDS = array( 'human', 'crawler', 'blocked' );

	/** Per-request memo for is_available(), so the option is read once. */
	private static $available = null;

	// ── Schema ───────────────────────────────────────────────────────────

	/** Fully-qualified counter table name for the current site prefix. */
	public static function events_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'csdt_ip_events';
	}

	/** Fully-qualified firewall log table name for the current site prefix. */
	public static function log_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'csdt_firewall_log';
	}

	/** Fully-qualified activity log table name for the current site prefix. */
	public static function activity_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'csdt_activity_log';
	}

	/**
	 * Fully-qualified live traffic table name for the current site prefix.
	 */
	public static function traffic_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'csdt_live_traffic';
	}

	/**
	 * Every table this plugin creates. Single source of truth, so uninstall and
	 * the build gate that checks uninstall cannot drift from the installer.
	 *
	 * @return array<int,string>
	 */
	public static function all_tables(): array {
		return array( self::events_table(), self::log_table(), self::activity_table(), self::traffic_table() );
	}

	/**
	 * Creates or upgrades the schema. Idempotent, gated on a stored version, and
	 * safe to call on every request.
	 *
	 * The version option is only written once the table has been confirmed to
	 * exist, so it is a trustworthy proxy for availability rather than a record
	 * that dbDelta was merely attempted.
	 */
	public static function maybe_install(): void {
		if ( get_option( self::OPTION_SCHEMA_VERSION ) === self::SCHEMA_VERSION ) {
			return;
		}

		try {
			global $wpdb;
			// Guarded rather than unconditional: dbDelta() is already loaded during
			// an upgrade or a WP-CLI run, and requiring wp-admin from a front-end
			// request pulls in a large file for nothing.
			if ( ! function_exists( 'dbDelta' ) ) {
				require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			}

			$table    = self::events_table();
			$log      = self::log_table();
			$activity = self::activity_table();
			$traffic  = self::traffic_table();
			$charset  = $wpdb->get_charset_collate();

			// `signal` is a RESERVED WORD in MySQL 8, hence signal_key. Naming the
			// column `signal` produces a syntax error on every write, which would
			// have made this whole class silently inert.
			$sql = "CREATE TABLE {$table} (
				id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				ip          VARCHAR(45)     NOT NULL DEFAULT '',
				signal_key  VARCHAR(32)     NOT NULL DEFAULT '',
				bucket      INT UNSIGNED    NOT NULL DEFAULT 0,
				hits        INT UNSIGNED    NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				UNIQUE KEY ip_signal_bucket (ip, signal_key, bucket),
				KEY bucket (bucket)
			) {$charset};";

			// Firewall event log (Phase 2b). `minute_bucket`, not `minute`: MINUTE is a
			// MySQL keyword, and the whole reason signal_key is not called signal is that
			// a reserved column name fails on every write while looking perfectly fine in
			// the source. Not worth finding out which side of reserved MINUTE falls on.
			//
			// ip is VARCHAR(45) rather than the VARBINARY(16) the plan specified, to match
			// the counter table above. Two representations of an address inside one class
			// is a drift hazard worth more than the bytes saved, and the panel joins these
			// two tables by address.
			$sql .= "CREATE TABLE {$log} (
				id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				minute_bucket INT UNSIGNED    NOT NULL DEFAULT 0,
				ip            VARCHAR(45)     NOT NULL DEFAULT '',
				country       CHAR(2)         NOT NULL DEFAULT '',
				rule_id       VARCHAR(64)     NOT NULL DEFAULT '',
				action        VARCHAR(16)     NOT NULL DEFAULT '',
				method        VARCHAR(10)     NOT NULL DEFAULT '',
				path          VARCHAR(255)    NOT NULL DEFAULT '',
				hits          INT UNSIGNED    NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				UNIQUE KEY dedupe (minute_bucket, ip, rule_id, action),
				KEY minute_bucket (minute_bucket),
				KEY ip_minute (ip, minute_bucket)
			) {$charset};";

			// Administrator activity. NOT deduped, unlike the two above: the whole value of
			// an audit trail is that every individual act appears, with its own timestamp.
			// Volume is controlled by only recording events worth recording, plus retention
			// and a row ceiling, never by collapsing rows.
			//
			// user_login is stored alongside user_id on purpose. "User 47 was deleted" is
			// useless once user 47 no longer exists, and the deletion is exactly the event
			// most worth reading later.
			$sql .= "CREATE TABLE {$activity} (
				id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				ts        INT UNSIGNED    NOT NULL DEFAULT 0,
				user_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
				user_login VARCHAR(60)    NOT NULL DEFAULT '',
				ip        VARCHAR(45)     NOT NULL DEFAULT '',
				event     VARCHAR(40)     NOT NULL DEFAULT '',
				object    VARCHAR(191)    NOT NULL DEFAULT '',
				detail    VARCHAR(255)    NOT NULL DEFAULT '',
				severity  VARCHAR(10)     NOT NULL DEFAULT 'info',
				PRIMARY KEY  (id),
				KEY ts (ts),
				KEY event_ts (event, ts),
				KEY user_ts (user_id, ts)
			) {$charset};";

			// Live traffic (schema 5). One row per request that reached PHP, written on
			// `shutdown` while the feature is switched on. NOT deduped, unlike the firewall log:
			// a live feed that folds ten requests into one row is not showing you the requests.
			// The volume is bounded instead by being off by default, a one-day retention and a
			// row ceiling enforced from the write path.
			//
			// Two indexes and no more, because every index is paid for by every visitor's
			// INSERT. The primary key serves the panel's "newer than the last id I saw" poll and
			// the ceiling trim; `ts` serves the retention delete. The filter chips read at most a
			// few hundred rows backwards along the primary key, on an admin screen, so they do
			// not earn an index of their own.
			//
			// No query string, body, cookie or referrer column exists, so none can be stored.
			$sql .= "CREATE TABLE {$traffic} (
				id         BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
				ts         INT UNSIGNED      NOT NULL DEFAULT 0,
				ip         VARCHAR(45)       NOT NULL DEFAULT '',
				country    CHAR(2)           NOT NULL DEFAULT '',
				method     VARCHAR(10)       NOT NULL DEFAULT '',
				path       VARCHAR(255)      NOT NULL DEFAULT '',
				status     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
				user_agent VARCHAR(200)      NOT NULL DEFAULT '',
				user_id    BIGINT UNSIGNED   NOT NULL DEFAULT 0,
				kind       VARCHAR(10)       NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				KEY ts (ts)
			) {$charset};";

			dbDelta( $sql );

			// Confirm rather than assume, for EVERY table. A dbDelta that failed on a
			// read-only replica or a full disk returns no error we can rely on, and
			// confirming only the first table would mark the schema current while the
			// second was missing, which is a control reporting success while doing
			// nothing.
			$missing = array();
			foreach ( self::all_tables() as $expected ) {
				$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $expected ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- schema check, cannot be cached
				if ( $found !== $expected ) {
					$missing[] = $expected;
				}
			}
			if ( empty( $missing ) ) {
				self::purge_automation_noise( $activity );
				update_option( self::OPTION_SCHEMA_VERSION, self::SCHEMA_VERSION, true );
				self::$available = true;
			} else {
				self::$available = false;
			}
		} catch ( \Throwable $e ) {
			// Never fatal on a schema problem: the counters degrade to unavailable
			// and CSDT_Auto_Block goes inert, which is this engine's documented
			// posture when it cannot see what it needs.
			self::$available = false;
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
		}
	}

	/**
	 * Remove activity rows that were the site's own automation, not a person.
	 *
	 * Shipped fault, found by looking at the panel rather than the code: the activity log
	 * watched `csdt_ip_blocklist`, and CSDT_Auto_Block rewrites that option on EVERY
	 * automatic block. Enabling threshold blocking therefore turned each block into a red
	 * "Security setting changed" row attributed to the attacker's address and to nobody at
	 * all, 137 of them in fifteen hours, which is an audit trail whose contents are entirely
	 * its own side effects.
	 *
	 * CSDT_Activity_Log::is_automation_write() stops new ones being written. This clears the
	 * backlog, because a log that has to be read past is not being read. It deletes only the
	 * unattended blocklist writes: rows with a user behind them are administrative acts and
	 * stay, as does every other event type.
	 *
	 * Runs once, from the schema-4 upgrade. Losing these rows loses nothing, every block is
	 * recorded in `csdt_autoblock_log` and, where a rule fired, the firewall log.
	 *
	 * @since 1.10.512
	 * @param string $activity Fully-qualified activity table name.
	 * @return int Rows removed.
	 */
	private static function purge_automation_noise( string $activity ): int {
		global $wpdb;

		$deleted = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time upgrade cleanup
			$wpdb->prepare(
				'DELETE FROM %i WHERE event = %s AND object IN ( %s, %s ) AND user_id = 0',
				$activity,
				'option_changed',
				'csdt_ip_blocklist',
				'csdt_ip_blocklist_active'
			)
		);

		return is_numeric( $deleted ) ? (int) $deleted : 0;
	}

	/**
	 * Whether the counter table is usable. Attempts a one-time install if the
	 * stored version is absent, so a site that upgraded without `init` running
	 * still gets its schema.
	 */
	public static function is_available(): bool {
		if ( null !== self::$available ) {
			return self::$available;
		}
		if ( get_option( self::OPTION_SCHEMA_VERSION ) === self::SCHEMA_VERSION ) {
			self::$available = true;
			return true;
		}
		self::maybe_install();
		return (bool) self::$available;
	}

	/**
	 * Forgets the per-request availability memo.
	 *
	 * Exists for the tests, which install several schemas in one process. The memo is a
	 * per-request cache in production, so nothing else should ever need this, but a
	 * private static that tests reach by reflection is a contract nobody can see, and two
	 * test files were about to do exactly that.
	 *
	 * @since 1.10.486
	 */
	public static function reset_availability_memo(): void {
		self::$available = null;
	}

	/** Drops everything this class created. Called from uninstall.php. */
	public static function drop_tables(): void {
		global $wpdb;
		foreach ( self::all_tables() as $table ) {
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- uninstall path
		}
	}

	// ── Counters ─────────────────────────────────────────────────────────

	/** The bucket a timestamp falls in. */
	public static function bucket_for( int $ts ): int {
		return (int) floor( $ts / self::BUCKET_SECONDS );
	}

	/**
	 * Records one hostile event.
	 *
	 * ONE atomic statement. Not a read, a modify and a write, that is the bug
	 * this class replaces, and any change here that reads the row before writing
	 * it reintroduces the bug in full.
	 *
	 * @return bool Whether the event was durably recorded.
	 */
	public static function record_event( string $ip, string $signal, ?int $now = null ): bool {
		if ( '' === $ip || '' === $signal || ! self::is_available() ) {
			return false;
		}
		try {
			global $wpdb;
			$table  = self::events_table();
			$bucket = self::bucket_for( null === $now ? time() : $now );

			$result = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- counter increment, must not be cached
				$wpdb->prepare(
					'INSERT INTO %i (ip, signal_key, bucket, hits) VALUES (%s, %s, %d, 1)
					 ON DUPLICATE KEY UPDATE hits = hits + 1',
					$table,
					$ip,
					$signal,
					$bucket
				)
			);
			return false !== $result;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return false;
		}
	}

	/**
	 * Events recorded for this address and signal at or after $since.
	 *
	 * @return int|null Null when the counters are unavailable, which the caller
	 *                  must treat as "cannot judge" rather than as zero.
	 */
	public static function count_events( string $ip, string $signal, int $since ): ?int {
		if ( '' === $ip || '' === $signal || ! self::is_available() ) {
			return null;
		}
		try {
			global $wpdb;
			$table = self::events_table();
			// COALESCE, not a bare SUM. A bare SUM over no matching rows returns SQL
			// NULL, which is indistinguishable from the NULL $wpdb hands back when
			// the query itself failed, so a broken read would be reported as "no
			// events", the caller would never block, and nothing would say why.
			// With COALESCE a successful query always yields a number, so NULL here
			// means failure and only failure.
			$sum = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- live counter read, caching would defeat it
				$wpdb->prepare(
					'SELECT COALESCE(SUM(hits), 0) FROM %i WHERE ip = %s AND signal_key = %s AND bucket >= %d',
					$table,
					$ip,
					$signal,
					self::bucket_for( $since )
				)
			);
			return null === $sum ? null : (int) $sum;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return null;
		}
	}

	/**
	 * Forgets every counter for one address, so a block that later expires starts
	 * the address from zero rather than from an already-tripped tally.
	 */
	public static function clear_events( string $ip ): void {
		if ( '' === $ip || ! self::is_available() ) {
			return;
		}
		try {
			global $wpdb;
			$table = self::events_table();
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- targeted delete
				$wpdb->prepare( 'DELETE FROM %i WHERE ip = %s', $table, $ip )
			);
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
		}
	}

	/**
	 * Summary of current counter activity, for the admin panel.
	 *
	 * Exists so the blocker is visible while it is WORKING, not only when it blocks
	 * someone. Two aggregate reads over an indexed column, on an admin screen only.
	 *
	 * @param int|null $since Unix time to count from; defaults to the last hour.
	 * @return array{ips:int,events:int}|null Null when the counters are unavailable.
	 */
	public static function recent_activity( ?int $since = null ): ?array {
		if ( ! self::is_available() ) {
			return null;
		}
		try {
			global $wpdb;
			$table  = self::events_table();
			$bucket = self::bucket_for( null === $since ? time() - HOUR_IN_SECONDS : $since );
			$row    = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- live counter summary for the admin panel
				$wpdb->prepare(
					'SELECT COUNT(DISTINCT ip) AS ips, COALESCE(SUM(hits), 0) AS events FROM %i WHERE bucket >= %d',
					$table,
					$bucket
				),
				ARRAY_A
			);
			if ( ! is_array( $row ) ) {
				return null;
			}
			return array(
				'ips'    => (int) ( $row['ips'] ?? 0 ),
				'events' => (int) ( $row['events'] ?? 0 ),
			);
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return null;
		}
	}

	// ── Firewall event log (Phase 2b) ────────────────────────────────────

	/** Actions a logged match can carry. */
	const LOG_ACTIONS = array( 'logged', 'blocked', 'rate_limited' );

	/**
	 * Truncates a path for storage, and strips anything after the path.
	 *
	 * Belt and braces: CSDT_Firewall::path_only() already removed the query string before
	 * any rule saw it, but this is the last point before the value is durably stored, and
	 * the cost of a query string reaching the log is a security log full of other people's
	 * credentials. So it is cut here too rather than trusted to have been cut earlier.
	 *
	 * @since 1.10.486
	 * @param string $path Raw path.
	 * @return string Safe, truncated path.
	 */
	public static function truncate_path( string $path ): string {
		$path = (string) substr( $path, 0, strcspn( $path, "?#\0" ) );
		// Control characters would make the panel unreadable and could smuggle escape
		// sequences into a terminal reading the log.
		$path = (string) preg_replace( '/[^\x20-\x7E]/', '', $path );
		return (string) substr( $path, 0, self::LOG_PATH_MAX );
	}

	/**
	 * The visitor's country, from the Cloudflare header ONLY.
	 *
	 * Deliberately not CSDT_Geo::get_country(): that falls back to a 30 MB MaxMind
	 * database read, and this runs on the request path of every matched request, which is
	 * to say, on every request during an attack, when the site has least to spare. When the
	 * header is absent the column stays empty and the panel can resolve it later if anyone
	 * cares.
	 *
	 * @since 1.10.486
	 * @return string Two-letter code, or '' when unknown.
	 */
	public static function country_from_header(): string {
		// Every caller is on plugins_loaded, before wp_magic_quotes(), so $unslash is false.
		// Rejected below unless exactly two ASCII letters.
		$cc = strtoupper( substr( CSDT_Request::server( 'HTTP_CF_IPCOUNTRY', 8, false ), 0, 2 ) );
		if ( 2 !== strlen( $cc ) || ! ctype_alpha( $cc ) || 'XX' === $cc || 'T1' === $cc ) {
			return ''; // XX is Cloudflare for unknown; T1 is Tor.
		}
		return $cc;
	}

	/**
	 * Records one firewall match.
	 *
	 * ONE atomic statement, deduped per (minute, ip, rule, action) with a hits counter.
	 * That dedupe is the whole design: without it, logging every match makes the log the
	 * amplification vector under attack, one hostile request becomes one row, and an
	 * attacker chooses how many requests to send. It matters MORE in Log-only mode, not
	 * less, because that mode logs everything and blocks nothing.
	 *
	 * Rule id, action, method and truncated path only. Never the query string, never the
	 * POST body, never a header. A matched payload is attacker-controlled by definition and
	 * routinely carries credentials or tokens lifted from a compromised source, so storing
	 * it would turn a security log into a liability that outlives the attack.
	 *
	 * @since 1.10.486
	 * @param string   $ip      Client address.
	 * @param string   $rule_id Matched rule id.
	 * @param string   $action  One of LOG_ACTIONS.
	 * @param array    $ctx     Optional method/path/country overrides.
	 * @param int|null $now     Unix time, for tests.
	 * @return bool Whether the row was durably written.
	 */
	public static function record_match( string $ip, string $rule_id, string $action, array $ctx = array(), ?int $now = null ): bool {
		if ( '' === $ip || '' === $rule_id || ! self::is_available() ) {
			return false;
		}
		// An unrecognised action is refused rather than stored. A typo would otherwise
		// create a fourth action nothing queries, so the row would exist and no panel
		// would ever show it.
		if ( ! in_array( $action, self::LOG_ACTIONS, true ) ) {
			return false;
		}
		try {
			global $wpdb;
			$table  = self::log_table();
			$bucket = self::bucket_for( null === $now ? time() : $now );

			// Every caller is on plugins_loaded, before wp_magic_quotes(), so $unslash is
			// false. The method is reduced to A-Z and capped at 10 below; truncate_path()
			// strips the query string, the fragment and every non-printable byte.
			$method = strtoupper( (string) ( $ctx['method'] ?? CSDT_Request::server( 'REQUEST_METHOD', 16, false ) ) );
			$method = (string) substr( preg_replace( '/[^A-Z]/', '', $method ), 0, 10 );
			$path   = self::truncate_path( (string) ( $ctx['path'] ?? CSDT_Request::server( 'REQUEST_URI', 8192, false ) ) );
			$cc     = array_key_exists( 'country', $ctx ) ? (string) $ctx['country'] : self::country_from_header();

			$result = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- deduped counter write, must not be cached
				$wpdb->prepare(
					'INSERT INTO %i (minute_bucket, ip, country, rule_id, action, method, path, hits)
					 VALUES (%d, %s, %s, %s, %s, %s, %s, 1)
					 ON DUPLICATE KEY UPDATE hits = hits + 1',
					$table,
					$bucket,
					$ip,
					substr( $cc, 0, 2 ),
					substr( $rule_id, 0, 64 ),
					$action,
					$method,
					$path
				)
			);
			return false !== $result;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return false;
		}
	}

	/**
	 * Most recent logged matches, newest first.
	 *
	 * @since 1.10.486
	 * @param int      $limit Maximum rows.
	 * @param int|null $since Unix time to read from; defaults to the last 7 days.
	 * @return array<int,array<string,mixed>>|null Null when the log is unavailable, which
	 *                                             the caller must not read as "no matches".
	 */
	public static function recent_matches( int $limit = 50, ?int $since = null ): ?array {
		if ( ! self::is_available() ) {
			return null;
		}
		$limit = max( 1, min( 500, $limit ) );
		try {
			global $wpdb;
			$table  = self::log_table();
			$bucket = self::bucket_for( null === $since ? time() - ( 7 * DAY_IN_SECONDS ) : $since );
			$rows   = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin panel read of a live log
				$wpdb->prepare(
					'SELECT minute_bucket, ip, country, rule_id, action, method, path, hits FROM %i
					 WHERE minute_bucket >= %d
					 ORDER BY minute_bucket DESC, id DESC LIMIT %d',
					$table,
					$bucket,
					$limit
				),
				ARRAY_A
			);
			if ( ! is_array( $rows ) ) {
				return null;
			}
			foreach ( $rows as $i => $row ) {
				$rows[ $i ]['ts']   = (int) $row['minute_bucket'] * self::BUCKET_SECONDS;
				$rows[ $i ]['hits'] = (int) $row['hits'];
			}
			return $rows;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return null;
		}
	}

	/**
	 * Hits per rule, so the panel can show which rules are earning their place.
	 *
	 * A rule matching nothing over weeks of real traffic is either tuned wrong or covering
	 * something that never happens here, and both are worth seeing.
	 *
	 * @since 1.10.486
	 * @param int|null $since Unix time to count from; defaults to the last 7 days.
	 * @return array<string,array{hits:int,ips:int,blocked:int}>|null Null when unavailable.
	 */
	public static function match_counts( ?int $since = null ): ?array {
		if ( ! self::is_available() ) {
			return null;
		}
		try {
			global $wpdb;
			$table  = self::log_table();
			$bucket = self::bucket_for( null === $since ? time() - ( 7 * DAY_IN_SECONDS ) : $since );
			$rows   = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin panel aggregate of a live log
				$wpdb->prepare(
					"SELECT rule_id, COALESCE(SUM(hits), 0) AS hits, COUNT(DISTINCT ip) AS ips, COALESCE(SUM(CASE WHEN action = 'blocked' THEN hits ELSE 0 END), 0) AS blocked FROM %i
					 WHERE minute_bucket >= %d GROUP BY rule_id",
					$table,
					$bucket
				),
				ARRAY_A
			);
			if ( ! is_array( $rows ) ) {
				return null;
			}
			$out = array();
			foreach ( $rows as $row ) {
				$out[ (string) $row['rule_id'] ] = array(
					'hits'    => (int) $row['hits'],
					'ips'     => (int) $row['ips'],
					'blocked' => (int) $row['blocked'],
				);
			}
			return $out;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return null;
		}
	}

	/**
	 * Deletes log rows past LOG_RETENTION, then enforces LOG_MAX_ROWS oldest-first.
	 *
	 * Two independent bounds, because the time bound alone fails in the one case that
	 * matters: if cron stops running during an attack, nothing is older than the cutoff yet
	 * and the table grows unchecked.
	 *
	 * @since 1.10.486
	 * @param int|null $now Unix time, for tests.
	 * @return int Rows removed, so a prune that has quietly stopped covering anything is
	 *             visible rather than reassuring.
	 */
	public static function prune_log( ?int $now = null ): int {
		if ( ! self::is_available() ) {
			return 0;
		}
		$now     = null === $now ? time() : $now;
		$cutoff  = self::bucket_for( $now - self::LOG_RETENTION );
		$removed = 0;

		try {
			global $wpdb;
			$table    = self::log_table();
			$deadline = $now + self::PRUNE_BUDGET;

			do {
				$rows = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- maintenance delete
					$wpdb->prepare(
						'DELETE FROM %i WHERE minute_bucket < %d LIMIT %d',
						$table,
						$cutoff,
						self::PRUNE_BATCH
					)
				);
				$rows     = (int) $rows;
				$removed += $rows;
			} while ( $rows >= self::PRUNE_BATCH && time() < $deadline );

			// The row ceiling, independent of any date.
			$total  = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- maintenance count
				$wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table )
			);
			$excess = $total - self::LOG_MAX_ROWS;
			while ( $excess > 0 && time() < $deadline ) {
				$batch = (int) min( $excess, self::PRUNE_BATCH );
				$rows  = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- maintenance delete
					$wpdb->prepare(
						'DELETE FROM %i ORDER BY id ASC LIMIT %d',
						$table,
						$batch
					)
				);
				if ( $rows <= 0 ) {
					break; // Nothing removed: stop rather than spin to the deadline.
				}
				$removed += $rows;
				$excess  -= $rows;
			}
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
		}

		return $removed;
	}

	// ── Live traffic ─────────────────────────────────────────────────────

	/**
	 * Records one request in the live traffic feed.
	 *
	 * ONE INSERT, and on one call in TRAFFIC_TRIM_EVERY one primary-key range delete. That is the
	 * whole write-path budget, because this runs on every uncached request while the feature is on
	 * and the visitor pays for it. Nothing is read first: no COUNT(*), no "does this row exist".
	 *
	 * Every field is reduced here, at the last point before it is durably stored, rather than
	 * trusted to have been reduced by the caller: the path goes through truncate_path() (query
	 * string, fragment and control bytes cut), the method to A-Z, the user agent to printable
	 * ASCII and TRAFFIC_UA_MAX characters, the kind to TRAFFIC_KINDS.
	 *
	 * @param array    $row ip, country, method, path, status, user_agent, user_id, kind.
	 * @param int|null $now Unix time, for tests.
	 * @return bool Whether the row was durably written.
	 */
	public static function record_traffic( array $row, ?int $now = null ): bool {
		$ip   = (string) ( $row['ip'] ?? '' );
		$kind = (string) ( $row['kind'] ?? '' );
		// An unknown kind is refused rather than stored, for the reason record_match() refuses an
		// unknown action: a row no filter chip can ever select is a row nobody sees.
		if ( '' === $ip || ! in_array( $kind, self::TRAFFIC_KINDS, true ) || ! self::is_available() ) {
			return false;
		}
		try {
			global $wpdb;
			$table  = self::traffic_table();
			$method = (string) substr( (string) preg_replace( '/[^A-Z]/', '', strtoupper( (string) ( $row['method'] ?? '' ) ) ), 0, 10 );
			$ua     = (string) substr( (string) preg_replace( '/[^\x20-\x7E]/', '', (string) ( $row['user_agent'] ?? '' ) ), 0, self::TRAFFIC_UA_MAX );
			$cc     = (string) ( $row['country'] ?? '' );
			$cc     = ( 2 === strlen( $cc ) && ctype_alpha( $cc ) ) ? strtoupper( $cc ) : '';

			$result = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one append per request, must not be cached
				$wpdb->prepare(
					'INSERT INTO %i (ts, ip, country, method, path, status, user_agent, user_id, kind)
					 VALUES (%d, %s, %s, %s, %s, %d, %s, %d, %s)',
					$table,
					null === $now ? time() : $now,
					substr( $ip, 0, 45 ),
					$cc,
					$method,
					self::truncate_path( (string) ( $row['path'] ?? '' ) ),
					max( 0, min( 999, (int) ( $row['status'] ?? 0 ) ) ),
					$ua,
					max( 0, (int) ( $row['user_id'] ?? 0 ) ),
					$kind
				)
			);
			if ( false === $result ) {
				return false;
			}
			$id = (int) $wpdb->insert_id;
			if ( $id > 0 && 0 === $id % self::TRAFFIC_TRIM_EVERY ) {
				self::trim_traffic( $id );
			}
			return true;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return false;
		}
	}

	/**
	 * Deletes up to PRUNE_BATCH rows whose id is more than TRAFFIC_MAX_ROWS below $newest.
	 *
	 * @param int $newest The newest id in the table.
	 * @return int Rows removed.
	 */
	private static function trim_traffic( int $newest ): int {
		$floor = $newest - self::TRAFFIC_MAX_ROWS;
		if ( $floor <= 0 ) {
			return 0;
		}
		global $wpdb;
		$rows = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- bounded ceiling delete on the primary key
			$wpdb->prepare(
				'DELETE FROM %i WHERE id <= %d LIMIT %d',
				self::traffic_table(),
				$floor,
				self::PRUNE_BATCH
			)
		);
		return max( 0, (int) $rows );
	}

	/** Filters the panel's chips can ask for. */
	const TRAFFIC_FILTERS = array( 'all', 'human', 'crawler', 'blocked', 'user' );

	/**
	 * Live traffic rows newer than $after_id, newest first.
	 *
	 * Three fixed statements rather than one assembled WHERE clause, so every query this method
	 * can send is visible, whole, on one line, and each is prepared as written.
	 *
	 * @param int    $after_id Only rows with a greater id. 0 for the newest $limit rows.
	 * @param string $filter   One of TRAFFIC_FILTERS; anything else reads as 'all'.
	 * @param int    $limit    Maximum rows, capped at 100.
	 * @return array<int,array<string,mixed>>|null Null when the table is unavailable, which the
	 *                                             caller must not read as "no traffic".
	 */
	public static function recent_traffic( int $after_id = 0, string $filter = 'all', int $limit = 100 ): ?array {
		if ( ! self::is_available() ) {
			return null;
		}
		$limit    = max( 1, min( 100, $limit ) );
		$after_id = max( 0, $after_id );
		$filter   = in_array( $filter, self::TRAFFIC_FILTERS, true ) ? $filter : 'all';
		try {
			global $wpdb;
			$table = self::traffic_table();
			if ( 'user' === $filter ) {
				$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin panel read of a live feed
					$wpdb->prepare( 'SELECT id, ts, ip, country, method, path, status, user_agent, user_id, kind FROM %i WHERE id > %d AND user_id > 0 ORDER BY id DESC LIMIT %d', $table, $after_id, $limit ),
					ARRAY_A
				);
			} elseif ( 'all' === $filter ) {
				$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin panel read of a live feed
					$wpdb->prepare( 'SELECT id, ts, ip, country, method, path, status, user_agent, user_id, kind FROM %i WHERE id > %d ORDER BY id DESC LIMIT %d', $table, $after_id, $limit ),
					ARRAY_A
				);
			} else {
				$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin panel read of a live feed
					$wpdb->prepare( 'SELECT id, ts, ip, country, method, path, status, user_agent, user_id, kind FROM %i WHERE id > %d AND kind = %s ORDER BY id DESC LIMIT %d', $table, $after_id, $filter, $limit ),
					ARRAY_A
				);
			}
			if ( ! is_array( $rows ) ) {
				return null;
			}
			$out = array();
			foreach ( $rows as $r ) {
				$out[] = array(
					'id'         => (int) ( $r['id'] ?? 0 ),
					'ts'         => (int) ( $r['ts'] ?? 0 ),
					'ip'         => (string) ( $r['ip'] ?? '' ),
					'country'    => (string) ( $r['country'] ?? '' ),
					'method'     => (string) ( $r['method'] ?? '' ),
					'path'       => (string) ( $r['path'] ?? '' ),
					'status'     => (int) ( $r['status'] ?? 0 ),
					'user_agent' => (string) ( $r['user_agent'] ?? '' ),
					'user_id'    => (int) ( $r['user_id'] ?? 0 ),
					'kind'       => (string) ( $r['kind'] ?? '' ),
				);
			}
			return $out;
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
			return null;
		}
	}

	/**
	 * Deletes live traffic rows past TRAFFIC_RETENTION, then enforces TRAFFIC_MAX_ROWS.
	 *
	 * Runs on the same hourly prune as the firewall log, with the same batch size and wall-clock
	 * budget. The ceiling half repeats the write path's inline trim on purpose: that trim only runs
	 * when traffic arrives, and this one also covers rows written before a lower ceiling shipped.
	 *
	 * @param int|null $now Unix time, for tests.
	 * @return int Rows removed, so a prune that has quietly stopped covering anything is visible.
	 */
	public static function prune_traffic( ?int $now = null ): int {
		if ( ! self::is_available() ) {
			return 0;
		}
		$now     = null === $now ? time() : $now;
		$cutoff  = $now - self::TRAFFIC_RETENTION;
		$removed = 0;

		try {
			global $wpdb;
			$table    = self::traffic_table();
			$deadline = $now + self::PRUNE_BUDGET;

			do {
				$rows = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- maintenance delete
					$wpdb->prepare(
						'DELETE FROM %i WHERE ts < %d LIMIT %d',
						$table,
						$cutoff,
						self::PRUNE_BATCH
					)
				);
				$removed += max( 0, $rows );
			} while ( $rows >= self::PRUNE_BATCH && time() < $deadline );

			// The row ceiling, independent of any date.
			$newest = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- maintenance read of the primary key
				$wpdb->prepare( 'SELECT MAX(id) FROM %i', $table )
			);
			do {
				$rows     = self::trim_traffic( $newest );
				$removed += $rows;
			} while ( $rows >= self::PRUNE_BATCH && time() < $deadline );
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
		}

		return $removed;
	}

	/**
	 * Deletes counter rows older than EVENT_RETENTION, in bounded batches.
	 *
	 * @return int Rows removed, so the caller can say so rather than reporting a
	 *             silent success. A prune that has quietly stopped covering
	 *             anything should be visible.
	 */
	public static function prune( ?int $now = null ): int {
		if ( ! self::is_available() ) {
			return 0;
		}
		$now     = null === $now ? time() : $now;
		$cutoff  = self::bucket_for( $now - self::EVENT_RETENTION );
		$removed = 0;

		try {
			global $wpdb;
			$table    = self::events_table();
			$deadline = $now + self::PRUNE_BUDGET;

			do {
				$rows = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- maintenance delete
					$wpdb->prepare(
						'DELETE FROM %i WHERE bucket < %d LIMIT %d',
						$table,
						$cutoff,
						self::PRUNE_BATCH
					)
				);
				$rows     = (int) $rows;
				$removed += $rows;
			} while ( $rows >= self::PRUNE_BATCH && time() < $deadline );
		} catch ( \Throwable $e ) {
			if ( class_exists( 'CSDT_Monitor' ) ) {
				CSDT_Monitor::log_exception( $e, __METHOD__ );
			}
		}

		return $removed;
	}
}