<?php
/**
 * CSDT_Edge_Event_Store: the events the CloudScale Edge Gateway reports, in their own table.
 *
 * WHY A TABLE AND NOT AN OPTION
 * -----------------------------
 * The gateway posts up to 100 events every 10 seconds and every event id must be deduplicated
 * for 24 hours (a retry is always safe, contract section 5). An option holding the seen ids
 * would be rewritten whole on every post and race with itself; a UNIQUE key on the event id
 * makes "INSERT IGNORE" the dedupe, atomically, in MySQL. The same rows feed the panel, the
 * would-block counts, the Site Audit findings and upload-path learning, which needs 7 days of
 * shape events, so rows are kept 7 days and capped at MAX_ROWS, oldest first.
 *
 * The table is created when the first gateway is paired, never before: a site with no gateway
 * carries no table, no option and no query.
 *
 * @package CloudScale_Cyber_DevTools
 * @since   1.10.1070
 */

defined( 'ABSPATH' ) || exit;

/**
 * Table-backed event storage. Tests swap it for an in-memory double with the same methods.
 */
class CSDT_Edge_Event_Store {

	/** Bump to re-run dbDelta. */
	const SCHEMA_VERSION = '1';

	/** Schema version installed. Not autoloaded: read on the events route and admin only. */
	const OPTION_SCHEMA = 'csdt_edge_gw_schema';

	/** Table name without the WordPress prefix. */
	const TABLE = 'csdt_edge_events';

	/** Rows deleted per statement, so a prune never holds long locks. */
	const PRUNE_BATCH = 1000;

	/** The table, with the site's prefix. */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Creates or upgrades the table. Safe to call repeatedly; does nothing when current.
	 *
	 * @return bool Whether the table is there afterwards.
	 */
	public function install(): bool {
		if ( self::SCHEMA_VERSION === (string) get_option( self::OPTION_SCHEMA, '' ) ) {
			return true;
		}
		global $wpdb;
		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		$table   = self::table();
		$charset = $wpdb->get_charset_collate();
		// dbDelta's own format: two spaces before PRIMARY KEY, one key per line.
		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_id varchar(64) NOT NULL,
			received_at int(10) unsigned NOT NULL,
			at int(10) unsigned NOT NULL,
			gateway varchar(64) NOT NULL DEFAULT '',
			key_id varchar(16) NOT NULL DEFAULT '',
			action varchar(8) NOT NULL,
			source varchar(16) NOT NULL,
			rule_id varchar(64) NOT NULL DEFAULT '',
			severity varchar(16) NOT NULL DEFAULT '',
			cve varchar(255) NOT NULL DEFAULT '',
			ip varchar(45) NOT NULL DEFAULT '',
			method varchar(10) NOT NULL DEFAULT '',
			host varchar(255) NOT NULL DEFAULT '',
			path varchar(512) NOT NULL DEFAULT '',
			ua varchar(256) NOT NULL DEFAULT '',
			content_type varchar(128) NOT NULL DEFAULT '',
			extra text NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY event_id (event_id),
			KEY received_at (received_at),
			KEY source_action (source,action)
		) {$charset};";
		dbDelta( $sql );
		// Confirm rather than assume: a dbDelta that failed says nothing.
		$there = $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- schema check after dbDelta
		if ( $there ) {
			update_option( self::OPTION_SCHEMA, self::SCHEMA_VERSION, false );
		}
		return $there;
	}

	/**
	 * Inserts events, ignoring ids already stored. Returns the ids that were new.
	 *
	 * @param array<int,array<string,mixed>> $rows Sanitised rows (CSDT_Edge_Gateway::event_row()).
	 * @return string[]|null Null when the table cannot be written.
	 */
	public function insert( array $rows ): ?array {
		global $wpdb;
		$table = self::table();
		$new   = array();
		foreach ( $rows as $r ) {
			$n = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- event write, INSERT IGNORE is the dedupe
				$wpdb->prepare(
					'INSERT IGNORE INTO %i (event_id, received_at, at, gateway, key_id, action, source, rule_id, severity, cve, ip, method, host, path, ua, content_type, extra)
					 VALUES (%s, %d, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
					$table,
					$r['event_id'],
					$r['received_at'],
					$r['at'],
					$r['gateway'],
					$r['key_id'],
					$r['action'],
					$r['source'],
					$r['rule_id'],
					$r['severity'],
					$r['cve'],
					$r['ip'],
					$r['method'],
					$r['host'],
					$r['path'],
					$r['ua'],
					$r['content_type'],
					$r['extra']
				)
			);
			if ( false === $n ) {
				return null;
			}
			if ( (int) $n > 0 ) {
				$new[] = (string) $r['event_id'];
			}
		}
		return $new;
	}

	/**
	 * Counts since a time: would-blocks (log), blocks, and the critical share of each.
	 *
	 * @param int $since Unix time.
	 * @return array{log:int,block:int,critical_log:int,critical_block:int}
	 */
	public function counts( int $since ): array {
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin and audit read
			$wpdb->prepare(
				"SELECT action, SUM(1) AS n, SUM(severity = 'critical') AS c FROM %i WHERE received_at >= %d AND source <> 'guard' GROUP BY action",
				self::table(),
				$since
			),
			ARRAY_A
		);
		$out = array(
			'log'            => 0,
			'block'          => 0,
			'critical_log'   => 0,
			'critical_block' => 0,
		);
		foreach ( (array) $rows as $r ) {
			if ( 'log' === $r['action'] || 'block' === $r['action'] ) {
				$out[ $r['action'] ]               = (int) $r['n'];
				$out[ 'critical_' . $r['action'] ] = (int) $r['c'];
			}
		}
		return $out;
	}

	/**
	 * The newest rows.
	 *
	 * @param int $limit Rows.
	 * @return array<int,array<string,mixed>>
	 */
	public function recent( int $limit ): array {
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin read
			$wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d', self::table(), $limit ),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Multipart requests the gateway saw outside an upload path, grouped by path.
	 *
	 * @param int $since Unix time.
	 * @param int $limit Paths.
	 * @return array<int,array{path:string,hits:int,ips:int,last:int}>
	 */
	public function upload_candidates( int $since, int $limit ): array {
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin read
			$wpdb->prepare(
				"SELECT path, COUNT(*) AS hits, COUNT(DISTINCT ip) AS ips, MAX(at) AS last FROM %i
				 WHERE source = 'shape' AND rule_id = 'shape.multipart_not_allowed' AND received_at >= %d
				 GROUP BY path ORDER BY hits DESC LIMIT %d",
				self::table(),
				$since,
				$limit
			),
			ARRAY_A
		);
		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[] = array(
				'path' => (string) $r['path'],
				'hits' => (int) $r['hits'],
				'ips'  => (int) $r['ips'],
				'last' => (int) $r['last'],
			);
		}
		return $out;
	}

	/**
	 * Deletes rows received before a time, then trims to a row ceiling, oldest first.
	 *
	 * @param int $before   Unix time.
	 * @param int $max_rows Ceiling.
	 * @return int Rows removed.
	 */
	public function prune( int $before, int $max_rows ): int {
		global $wpdb;
		$table   = self::table();
		$removed = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- retention
			$wpdb->prepare( 'DELETE FROM %i WHERE received_at < %d LIMIT %d', $table, $before, self::PRUNE_BATCH )
		);
		$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- retention
		if ( $count > $max_rows ) {
			$removed += (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- retention
				$wpdb->prepare( 'DELETE FROM %i ORDER BY id ASC LIMIT %d', $table, min( self::PRUNE_BATCH, $count - $max_rows ) )
			);
		}
		return $removed;
	}

	/**
	 * Drops the table and its schema option. Uninstall does the same without loading this class.
	 *
	 * @return void
	 */
	public function drop(): void {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- explicit removal
		delete_option( self::OPTION_SCHEMA );
	}
}