<?php
/**
 * Fixed Free-owned schema and deletion contract for optional saved searches and report copies.
 *
 * @package LoggedAI
 */

namespace LoggedAI;

defined( 'ABSPATH' ) || exit;

/** Fixed Free-owned schema and deletion contract for optional saved searches and report copies. */
final class ReportPrivacy {
	public const VERSION  = 1;
	public const SUFFIXES = array(
		'searches'  => 'logged_ai_pro_searches',
		'schedules' => 'logged_ai_pro_report_schedules',
		'jobs'      => 'logged_ai_pro_report_jobs',
		'chunks'    => 'logged_ai_pro_report_chunks',
		'members'   => 'logged_ai_pro_report_members',
		'periods'   => 'logged_ai_pro_report_periods',
	);
	/**
	 * Resolve the fixed optional report tables under the supplied native prefix.
	 *
	 * @param \wpdb $db Native database connection selected for this operation.
	 *
	 * @return array Report table names indexed by their owned storage kind.
	 */
	public static function tables( \wpdb $db ): array {
		return array_map( static fn( string $suffix ): string => $db->base_prefix . $suffix, self::SUFFIXES ); }
	/**
	 * Declare the fixed saved-search, schedule, job and report-copy column contracts.
	 *
	 * @return array Required column names and native SQL types for each owned table.
	 */
	public static function columns(): array {
		return array(
			'searches'  => array(
				'id'             => 'bigint unsigned',
				'network_id'     => 'bigint unsigned',
				'site_id'        => 'bigint unsigned',
				'uid'            => 'char(36)',
				'owner_id'       => 'bigint unsigned',
				'visibility'     => 'varchar(8)',
				'schema_version' => 'int unsigned',
				'revision'       => 'bigint unsigned',
				'title'          => 'varchar(160)',
				'definition'     => 'text',
				'created_at'     => 'datetime(6)',
				'changed_at'     => 'datetime(6)',
			),
			'schedules' => array(
				'id'           => 'bigint unsigned',
				'network_id'   => 'bigint unsigned',
				'site_id'      => 'bigint unsigned',
				'uid'          => 'char(36)',
				'search_uid'   => 'char(36)',
				'owner_id'     => 'bigint unsigned',
				'revision'     => 'bigint unsigned',
				'enabled'      => 'tinyint unsigned',
				'timezone'     => 'varchar(64)',
				'cadence'      => 'varchar(8)',
				'local_hour'   => 'tinyint unsigned',
				'format'       => 'varchar(8)',
				'notify_owner' => 'tinyint unsigned',
				'created_at'   => 'datetime(6)',
				'changed_at'   => 'datetime(6)',
			),
			'jobs'      => array(
				'id'                    => 'bigint unsigned',
				'network_id'            => 'bigint unsigned',
				'site_id'               => 'bigint unsigned',
				'uid'                   => 'char(36)',
				'schedule_uid'          => 'char(36)',
				'search_uid'            => 'char(36)',
				'owner_id'              => 'bigint unsigned',
				'period_key'            => 'char(64)',
				'definition'            => 'text',
				'period'                => 'text',
				'format'                => 'varchar(8)',
				'state'                 => 'varchar(24)',
				'reason'                => 'varchar(40)',
				'source_binding'        => 'char(64)',
				'cursor_state'          => 'text',
				'row_count'             => 'bigint unsigned',
				'byte_count'            => 'bigint unsigned',
				'chunk_count'           => 'int unsigned',
				'digest'                => 'char(64)',
				'manifest'              => 'text',
				'notify_owner'          => 'tinyint unsigned',
				'delivery_state'        => 'varchar(24)',
				'delivery_attempted_at' => 'datetime(6)',
				'created_at'            => 'datetime(6)',
				'expires_at'            => 'datetime(6)',
				'changed_at'            => 'datetime(6)',
			),
			'chunks'    => array(
				'id'              => 'bigint unsigned',
				'network_id'      => 'bigint unsigned',
				'site_id'         => 'bigint unsigned',
				'job_uid'         => 'char(36)',
				'sequence_number' => 'int unsigned',
				'ciphertext'      => 'mediumblob',
				'digest'          => 'char(64)',
				'plain_bytes'     => 'int unsigned',
			),
			'periods'   => array(
				'id'           => 'bigint unsigned',
				'network_id'   => 'bigint unsigned',
				'site_id'      => 'bigint unsigned',
				'schedule_uid' => 'char(36)',
				'period_key'   => 'char(64)',
				'created_at'   => 'datetime(6)',
				'expires_at'   => 'datetime(6)',
			),
			'members'   => array(
				'id'         => 'bigint unsigned',
				'network_id' => 'bigint unsigned',
				'site_id'    => 'bigint unsigned',
				'job_uid'    => 'char(36)',
				'source_id'  => 'bigint unsigned',
				'source_uid' => 'char(36)',
			),
		);
	}
	/**
	 * Declare the required unique and lookup indexes for optional report storage.
	 *
	 * @return array Index names, uniqueness and ordered fields grouped by table kind.
	 */
	public static function indexes(): array {
		$primary  = array( 'PRIMARY' => array( true, array( 'id' ) ) );
		$identity = array(
			'identity' => array( true, array( 'network_id', 'site_id', 'uid' ) ),
			'owner'    => array( false, array( 'network_id', 'site_id', 'owner_id', 'id' ) ),
		);
		return array(
			'searches'  => $primary + $identity,
			'schedules' => $primary + $identity + array(
				'search'  => array( false, array( 'network_id', 'site_id', 'search_uid', 'id' ) ),
				'enabled' => array( false, array( 'network_id', 'site_id', 'enabled', 'id' ) ),
			),
			'jobs'      => $primary + $identity + array(
				'period' => array( true, array( 'network_id', 'site_id', 'schedule_uid', 'period_key' ) ),
				'search' => array( false, array( 'network_id', 'site_id', 'search_uid', 'id' ) ),
				'expiry' => array( false, array( 'expires_at', 'id' ) ),
				'queue'  => array( false, array( 'network_id', 'site_id', 'state', 'id' ) ),
			),
			'chunks'    => $primary + array( 'chunk' => array( true, array( 'network_id', 'site_id', 'job_uid', 'sequence_number' ) ) ),
			'periods'   => $primary + array(
				'period' => array( true, array( 'network_id', 'site_id', 'schedule_uid', 'period_key' ) ),
				'expiry' => array( false, array( 'expires_at', 'id' ) ),
			),
			'members'   => $primary + array( 'member' => array( true, array( 'network_id', 'site_id', 'job_uid', 'source_id' ) ) ),
		);
	}
	/**
	 * Create a fixed report-privacy error with an unavailable-service status.
	 *
	 * @param string $message Fixed operation-specific error message supplied by installed code.
	 *
	 * @return \WP_Error Report privacy error containing the supplied operation message.
	 */
	public static function error( string $message ): \WP_Error {
		return new \WP_Error( 'report_privacy', $message, array( 'status' => 503 ) ); }
	/**
	 * Decode only the canonical historical saved-search definition representation.
	 *
	 * Historical v1 structure remains decodable without the Pro event catalog or Pro classes.
	 *
	 * @param mixed $schema_version Stored saved-search schema version to validate without coercion.
	 * @param mixed $json           Stored canonical saved-search definition JSON to validate.
	 *
	 * @return array|\WP_Error Validated normalized definition, or an incompatible-reference error.
	 */
	public static function definition( mixed $schema_version, mixed $json ): array|\WP_Error {
		$error = static fn(): \WP_Error => self::error( 'Saved-search privacy references are incompatible.' );
		if ( ! in_array( $schema_version, array( 1, '1' ), true ) || ! is_string( $json ) || strlen( $json ) > 8192 ) {
			return $error(); }
		$definition = json_decode( $json, true, 6 );
		if ( ! is_array( $definition ) || array_keys( $definition ) !== array( 'version', 'type', 'filters' ) || 1 !== $definition['version'] || ! in_array( $definition['type'], array( 'site_activity', 'user_activity', 'sensitive_changes', 'authentication', 'woocommerce', 'incidents' ), true ) || ! is_array( $definition['filters'] ) || count( $definition['filters'] ) > 20 ) {
			return $error(); }
		$filters = $definition['filters'];
		if ( 'incidents' === $definition['type'] ) {
			if ( array_diff( array_keys( $filters ), array( 'status' ) ) || ( isset( $filters['status'] ) && ! in_array( $filters['status'], array( 'open', 'investigating', 'resolved', 'false_positive' ), true ) ) || ( array_key_exists( 'status', $filters ) && ! is_string( $filters['status'] ) ) ) {
				return $error(); }
		} else {
			$keys  = array( 'actor_id', 'subject_id', 'object_id', 'from', 'to', 'request_id', 'code', 'severity', 'actor_kind', 'channel', 'outcome', 'category', 'action', 'object_type' );
			$enums = array(
				'severity'   => array( 'info', 'notice', 'warning', 'high', 'critical' ),
				'actor_kind' => array( 'authenticated_user', 'system', 'unknown' ),
				'channel'    => array( 'cli', 'cron', 'rest', 'xmlrpc', 'wp_admin', 'frontend' ),
				'outcome'    => array( 'stored_change', 'completed', 'failed', 'attempted', 'observed' ),
			);
			foreach ( $filters as $key => $value ) {
				if ( ! in_array( $key, $keys, true ) || ! is_string( $value ) || '' === $value || strlen( $value ) > 80 ) {
					return $error(); }
				if ( in_array( $key, array( 'actor_id', 'subject_id', 'object_id' ), true ) ) {
					if ( ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', $value ) ) {
						return $error(); }
				} elseif ( isset( $enums[ $key ] ) ) {
					if ( ! in_array( $value, $enums[ $key ], true ) ) {
						return $error(); }
				} elseif ( in_array( $key, array( 'from', 'to' ), true ) ) {
					$date = preg_match( '/\A[0-9]{4}-[0-9]{2}-[0-9]{2}\z/D', $value ) ? \DateTimeImmutable::createFromFormat( '!Y-m-d', $value, new \DateTimeZone( 'UTC' ) ) : false;
					if ( ! $date || $value !== $date->format( 'Y-m-d' ) ) {
						return $error(); }
				} elseif ( 'request_id' === $key ) {
					if ( ! preg_match( '/\A[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}\z/D', $value ) ) {
						return $error(); }
				} elseif ( ! preg_match( '/\A[a-zA-Z0-9_.:\/-]+\z/D', $value ) || str_contains( $value, '..' ) ) {
					return $error(); }
			}
			if ( ( isset( $filters['from'], $filters['to'] ) && $filters['from'] >= $filters['to'] ) || ( 'user_activity' === $definition['type'] && ! isset( $filters['actor_id'] ) ) || ( 'authentication' === $definition['type'] && 'identity' !== ( $filters['category'] ?? null ) ) || ( 'woocommerce' === $definition['type'] && 'commerce' !== ( $filters['category'] ?? null ) ) ) {
				return $error(); }
		}
		ksort( $filters );
		$definition['filters'] = $filters;
		// The writer persists this exact normalized representation. This also rejects duplicate keys,
		// hidden overwritten references, alternate envelopes and unknown future field semantics.
		return wp_json_encode( $definition ) === $json ? $definition : $error();
	}
	/**
	 * Check validated search ownership and explicit person-reference filters.
	 *
	 * @param array $search Retained search or job definition metadata with ownership references.
	 * @param int   $user   Selected registered user identifier for scoped privacy work.
	 *
	 * @return bool|\WP_Error Whether the search names the supplied user, or an identity/definition error.
	 */
	public static function search_matches_user( array $search, int $user ): bool|\WP_Error {
		$definition = self::definition( $search['schema_version'] ?? null, $search['definition'] ?? null );
		if ( is_wp_error( $definition ) ) {
			return $definition; }
		if ( $user < 1 || ! is_string( $search['uid'] ?? null ) || ! preg_match( '/\A[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}\z/D', $search['uid'] ) || ! ( is_int( $search['owner_id'] ?? null ) || is_string( $search['owner_id'] ?? null ) ) || ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', (string) $search['owner_id'] ) ) {
			return self::error( 'Saved-search identity references are incompatible.' ); }
		$filters = $definition['filters'];
		return (string) $search['owner_id'] === (string) $user || (string) ( $filters['actor_id'] ?? '' ) === (string) $user || (string) ( $filters['subject_id'] ?? '' ) === (string) $user
			|| ( ( ! isset( $filters['object_type'] ) || 'user' === $filters['object_type'] ) && (string) ( $filters['object_id'] ?? '' ) === (string) $user );
	}
	/**
	 * Distinguish absent optional report storage from complete compatible storage.
	 *
	 * @param \wpdb $db Native database connection selected for this operation.
	 *
	 * @return bool|\WP_Error False for total absence, true for ready storage, or an inspection/incompatibility error.
	 */
	public static function present( \wpdb $db ): bool|\WP_Error {
		$count = 0;
		foreach ( self::tables( $db ) as $table ) {
			if ( ! preg_match( '/\A[a-zA-Z0-9_]{1,64}\z/D', $table ) ) {
				return self::error( 'The report table identity is unsupported.' ); }
			$found = $db->get_var( $db->prepare( 'SHOW TABLES LIKE %s', $db->esc_like( $table ) ) );
			if ( '' !== $db->last_error || ( null !== $found && $table !== $found ) ) {
				return self::error( 'Optional report storage could not be inspected.' ); }
			$count += null === $found ? 0 : 1;
		}
		if ( 0 === $count ) {
			return false; }
		return count( self::SUFFIXES ) === $count && self::ready( $db ) ? true : self::error( 'Optional report storage is incomplete or incompatible; keep original privacy references for retry.' );
	}
	/**
	 * Verify report columns, storage engines and required complete indexes.
	 *
	 * @param \wpdb $db Native database connection selected for this operation.
	 *
	 * @return bool Whether every owned table satisfies its fixed schema contract.
	 */
	public static function ready( \wpdb $db ): bool {
		foreach ( self::tables( $db ) as $kind => $table ) {
			if ( ! preg_match( '/\A[a-zA-Z0-9_]{1,64}\z/D', $table ) ) {
				return false; }
			$columns = $db->get_results( $db->prepare( "SHOW COLUMNS FROM %i", $table ), ARRAY_A );
			/**
			 * Validate the untyped result from the supplied database adapter.
			 *
			 * @var mixed $columns
			 */
			if ( '' !== $db->last_error || ! is_array( $columns ) || count( $columns ) !== count( self::columns()[ $kind ] ) ) {
				return false; }
			$found = array_column( $columns, null, 'Field' );
			foreach ( self::columns()[ $kind ] as $field => $type ) {
				$column = $found[ $field ] ?? null;
				if ( ! $column || preg_replace( '/\b(bigint|int|tinyint)\([0-9]+\)/', '$1', strtolower( $column['Type'] ) ) !== $type || 'NO' !== $column['Null'] || ( 'id' === $field && 'auto_increment' !== $column['Extra'] ) ) {
					return false; }
			}
			$engine = $db->get_var( $db->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s', $table ) );
			if ( '' !== $db->last_error || 'InnoDB' !== $engine ) {
				return false; }
			$indexes = $db->get_results( $db->prepare( "SHOW INDEX FROM %i", $table ), ARRAY_A );
			/**
			 * Validate the untyped result from the supplied database adapter.
			 *
			 * @var mixed $indexes
			 */
			if ( '' !== $db->last_error || ! is_array( $indexes ) ) {
				return false;
			} $found = array();
			foreach ( $indexes as $index ) {
				if ( null !== $index['Sub_part'] ) {
					return false;
				} $found[ $index['Key_name'] ]['unique']                               = 0 === (int) $index['Non_unique'];
				$found[ $index['Key_name'] ]['fields'][ (int) $index['Seq_in_index'] ] = $index['Column_name']; }
			foreach ( self::indexes()[ $kind ] as $name => $expected ) {
				if ( ! isset( $found[ $name ] ) || $found[ $name ]['unique'] !== $expected[0] ) {
					return false; }
				ksort( $found[ $name ]['fields'] );
				if ( array_values( $found[ $name ]['fields'] ) !== $expected[1] ) {
					return false; }
			}
		}
		return true;
	}
	/**
	 * Capture the native connection and identity fingerprint without authorizing access.
	 *
	 * @phpstan-impure Reads current native authority or mutable context.
	 *
	 * @param \wpdb $db Native database connection selected for this operation.
	 *
	 * @return array Current native context used only for subsequent equality checks.
	 */
	private static function context( \wpdb $db ): array {
		return Derived::native_context( $db ); }
	/**
	 * Recheck current site context, derived mutation authority and mutex ownership.
	 *
	 * @phpstan-impure Reads current native authority or mutable context.
	 *
	 * @param \wpdb $db      Native database connection selected for this operation.
	 * @param array $origin  Native context captured before protected reads, callbacks or writes.
	 * @param int   $network Server-selected current network identifier.
	 * @param int   $site    Server-selected site identifier for this operation.
	 *
	 * @return bool Whether the selected connection still owns the authorized privacy operation.
	 */
	private static function held( \wpdb $db, array $origin, int $network, int $site ): bool {
		if ( $db !== $GLOBALS['wpdb'] || self::context( $db ) !== $origin || Scope::network() !== $network || get_current_blog_id() !== $site || ! Derived::mutation_allowed() ) {
			return false; }
		$held = $db->get_var( $db->prepare( 'SELECT IS_USED_LOCK(%s)=CONNECTION_ID()', Derived::lock_name() ) );
		return '' === $db->last_error && '1' === (string) $held && self::context( $db ) === $origin && Derived::mutation_allowed() && self::context( $db ) === $origin;
	}
	/**
	 * Remove bounded admission rows before their selected schedule identities disappear.
	 *
	 * Remove personal admission references before their matching schedule identities disappear.
	 *
	 * @param \wpdb  $db        Native database connection selected for this operation.
	 * @param array  $tables    Fixed table names resolved from the selected native database adapter.
	 * @param array  $origin    Native context captured before protected reads, callbacks or writes.
	 * @param int    $network   Server-selected current network identifier.
	 * @param int    $site      Server-selected site identifier for this operation.
	 * @param string $condition Internally prepared current-scope schedule selection predicate.
	 *
	 * @return array|\WP_Error Removed-row count and completion flag, or an inventory/authority/storage error.
	 */
	private static function erase_schedule_periods( \wpdb $db, array $tables, array $origin, int $network, int $site, ?string $search = null, ?int $owner = null ): array|\WP_Error {
		$ids = null !== $search
			? $db->get_col( $db->prepare( 'SELECT uid FROM %i WHERE network_id=%d AND site_id=%d AND search_uid=%s LIMIT 21 FOR UPDATE', $tables['schedules'], $network, $site, $search ) )
			: $db->get_col( $db->prepare( 'SELECT uid FROM %i WHERE network_id=%d AND site_id=%d AND owner_id=%d LIMIT 21 FOR UPDATE', $tables['schedules'], $network, $site, $owner ) );
		/**
		 * Validate schedule references before deleting matching admission history from the untyped wpdb adapter.
		 *
		 * @var mixed $ids
		 */
		if ( '' !== $db->last_error || ! is_array( $ids ) || count( $ids ) > 20 ) {
			return self::error( 'Report schedule admission inventory is unavailable.' ); }
		if ( ! $ids ) {
			return array(
				'removed' => 0,
				'done'    => true,
			); }
		$marks = implode( ',', array_fill( 0, count( $ids ), '%s' ) );

		if ( ! self::held( $db, $origin, $network, $site ) ) {
			return self::error( 'Report admission erasure authority changed.' ); }
		$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND schedule_uid IN ($marks) LIMIT 500", $tables['periods'], $network, $site, ...$ids ) );
		if ( false === $count ) {
			return self::error( 'Personal report admission metadata could not be erased.' ); }
		$left = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d AND schedule_uid IN ($marks) LIMIT 1 FOR UPDATE", $tables['periods'], $network, $site, ...$ids ) );
		if ( '' !== $db->last_error || ! self::held( $db, $origin, $network, $site ) ) {
			return self::error( 'Report admission cleanup could not be verified.' ); }
		return array(
			'removed' => $count,
			'done'    => null === $left,
		);
	}
	/**
	 * Cancel site report access and remove copied data before optional identity cleanup.
	 *
	 * Invalidate all report copies before source removal. Work remains bounded and retries keep source joins.
	 *
	 * @param int      $network Server-selected current network identifier.
	 * @param int      $site    Server-selected site identifier for this operation.
	 * @param int|null $user    User whose definitions and ownership are erased, or null for copy invalidation only.
	 *
	 * @return array|\WP_Error Reported removed-row count and bounded completion status, or an explicit retry error.
	 */
	public static function invalidate( int $network, int $site, ?int $user = null ): array|\WP_Error {
		global $wpdb;
		/**
		 * Current WordPress database adapter used by this operation.
		 *
		 * @var \wpdb $wpdb
		 */
		$db     = $wpdb;
		$origin = self::context( $db );
		if ( ( null !== $user && $user < 1 ) || ! self::held( $db, $origin, $network, $site ) ) {
			return self::error( 'Report erasure requires its held site privacy authority.' ); }
		$present = self::present( $db );
		if ( is_wp_error( $present ) ) {
			return $present;
		} if ( ! $present ) {
			return array(
				'removed' => 0,
				'done'    => true,
			); }
		$tables  = self::tables( $db );

		$removed = 0;
		if ( ! self::held( $db, $origin, $network, $site ) || false === $db->query( $db->prepare( "UPDATE %i SET state='cancelled',reason='source_privacy_changed',manifest='{}' WHERE network_id=%d AND site_id=%d AND (state<>'cancelled' OR manifest<>'{}') LIMIT 100", $tables['jobs'], $network, $site ) ) ) {
			return self::error( 'Report access could not be invalidated.' ); }
		$pending = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d AND (state<>'cancelled' OR manifest<>'{}') LIMIT 1 FOR UPDATE", $tables['jobs'], $network, $site ) );
		if ( '' !== $db->last_error ) {
			return self::error( 'Report invalidation completion is unavailable.' ); }
		if ( null !== $pending ) {
			return array(
				'removed' => 0,
				'done'    => false,
			); }
		if ( ! self::held( $db, $origin, $network, $site ) ) {
			return self::error( 'Report privacy authority changed.' ); }
		$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d LIMIT 500", $tables['chunks'], $network, $site ) );
		if ( false === $count ) {
			return self::error( 'Encrypted report copies could not be removed.' );
		} $removed += $count;
		$pending    = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d LIMIT 1 FOR UPDATE", $tables['chunks'], $network, $site ) );
		if ( '' !== $db->last_error ) {
			return self::error( 'Remaining report copies could not be verified.' ); }
		if ( null !== $pending ) {
			return array(
				'removed' => $removed,
				'done'    => false,
			); }
		if ( ! self::held( $db, $origin, $network, $site ) ) {
			return self::error( 'Report membership erasure authority changed.' ); }
		$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d LIMIT 500", $tables['members'], $network, $site ) );
		if ( false === $count ) {
			return self::error( 'Report source memberships could not be removed.' );
		} $removed += $count;
		$pending    = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d LIMIT 1 FOR UPDATE", $tables['members'], $network, $site ) );
		if ( '' !== $db->last_error ) {
			return self::error( 'Remaining report memberships could not be verified.' ); }
		if ( null !== $pending ) {
			return array(
				'removed' => $removed,
				'done'    => false,
			); }
		// Job definitions can themselves mention the erased user. Drop the bounded site job metadata,
		// including old copies owned by another investigator, rather than broadening saved filters.
		if ( null !== $user ) {
			if ( ! self::held( $db, $origin, $network, $site ) ) {
				return self::error( 'Report metadata erasure authority changed.' ); }
			$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d LIMIT 100", $tables['jobs'], $network, $site ) );
			if ( false === $count ) {
				return self::error( 'Report metadata erasure is pending.' );
			} $removed += $count;
			$searches   = $db->get_results( $db->prepare( "SELECT uid,owner_id,schema_version,CASE WHEN OCTET_LENGTH(definition)<=8192 THEN definition ELSE NULL END AS definition FROM %i WHERE network_id=%d AND site_id=%d LIMIT 101 FOR UPDATE", $tables['searches'], $network, $site ), ARRAY_A );
			/**
			 * Validate saved searches before inspecting personal definitions for erasure from the untyped wpdb adapter.
			 *
			 * @var mixed $searches
			 */
			if ( '' !== $db->last_error || ! is_array( $searches ) || count( $searches ) > 100 ) {
				return self::error( 'Saved-search privacy inventory exceeds its bound or is unavailable.' ); }
			foreach ( $searches as $search ) {
				$matches = self::search_matches_user( $search, $user );
				if ( is_wp_error( $matches ) ) {
					return $matches; }
				if ( ! $matches ) {
					continue; }
				$periods = self::erase_schedule_periods( $db, $tables, $origin, $network, $site, $search['uid'] );
				if ( is_wp_error( $periods ) ) {
					return $periods;
				} $removed += $periods['removed'];
				if ( ! $periods['done'] ) {
					return array(
						'removed' => $removed,
						'done'    => false,
					); }
				if ( ! self::held( $db, $origin, $network, $site ) ) {
					return self::error( 'Saved-search erasure authority changed.' ); }
				$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND search_uid=%s LIMIT 100", $tables['schedules'], $network, $site, $search['uid'] ) );
				if ( false === $count ) {
					return self::error( 'Saved-search schedules could not be removed.' );
				} $removed += $count;
				$pending    = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d AND search_uid=%s LIMIT 1 FOR UPDATE", $tables['schedules'], $network, $site, $search['uid'] ) );
				if ( '' !== $db->last_error ) {
					return self::error( 'Saved-search schedule erasure could not be verified.' ); }
				if ( null !== $pending ) {
					return array(
						'removed' => $removed,
						'done'    => false,
					); }
				if ( ! self::held( $db, $origin, $network, $site ) ) {
					return self::error( 'Saved-search erasure authority changed.' ); }
				$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND uid=%s", $tables['searches'], $network, $site, $search['uid'] ) );
				if ( false === $count ) {
					return self::error( 'Saved search could not be erased.' );
				} $removed += $count;
			}
			$periods = self::erase_schedule_periods( $db, $tables, $origin, $network, $site, null, $user );
			if ( is_wp_error( $periods ) ) {
				return $periods;
			} $removed += $periods['removed'];
			if ( ! $periods['done'] ) {
				return array(
					'removed' => $removed,
					'done'    => false,
				); }
			if ( ! self::held( $db, $origin, $network, $site ) ) {
				return self::error( 'Report schedule erasure authority changed.' ); }
			$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND owner_id=%d LIMIT 100", $tables['schedules'], $network, $site, $user ) );
			if ( false === $count ) {
				return self::error( 'The erased owner schedules could not be removed.' );
			} $removed += $count;
			$pending    = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d LIMIT 1 FOR UPDATE", $tables['jobs'], $network, $site ) );
			if ( '' !== $db->last_error ) {
				return self::error( 'Report metadata completion is unavailable.' ); }
			if ( null !== $pending ) {
				return array(
					'removed' => $removed,
					'done'    => false,
				); }
			$pending = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d AND owner_id=%d LIMIT 1 FOR UPDATE", $tables['schedules'], $network, $site, $user ) );
			if ( '' !== $db->last_error ) {
				return self::error( 'Owner schedule erasure completion is unavailable.' ); }
			if ( null !== $pending ) {
				return array(
					'removed' => $removed,
					'done'    => false,
				); }
		}
		return self::held( $db, $origin, $network, $site ) ? array(
			'removed' => $removed,
			'done'    => true,
		) : self::error( 'Report erasure authority changed before completion.' );
	}
	/**
	 * Validate source references and invalidate dependent site report copies.
	 *
	 * @param int   $network Server-selected current network identifier.
	 * @param int   $site    Selected site identifier; zero identifies network-origin evidence outside site reports.
	 * @param array $rows    Bounded selected source rows retaining their original IDs and UUIDs.
	 *
	 * @return array|\WP_Error Site-copy cleanup progress, a network/empty no-op, or a scope/reference error.
	 */
	public static function erase_events( int $network, int $site, array $rows ): array|\WP_Error {
		if ( ! Derived::mutation_allowed() || Scope::network() !== $network || count( $rows ) > 500 ) {
			return self::error( 'Report source cleanup requires its held scope authority.' ); }
		// Reports are site-only. Network-origin evidence is never a report member.
		if ( 0 === $site || ! $rows ) {
			return array(
				'removed' => 0,
				'done'    => true,
			); }
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) || ! preg_match( '/\A[1-9][0-9]{0,19}\z/D', (string) ( $row['id'] ?? '' ) ) || ! is_string( $row['uid'] ?? null ) || ! preg_match( '/\A[a-f0-9-]{36}\z/D', $row['uid'] ) ) {
				return self::error( 'Report source cleanup received invalid internal references.' ); }
		}
		return self::invalidate( $network, $site );
	}
	/**
	 * Require current site privacy authority before removing report identity references.
	 *
	 * @param Scope $scope Captured native authorization scope whose current grants are rechecked.
	 * @param int   $user  Selected registered user identifier for scoped privacy work.
	 *
	 * @return array|\WP_Error Report cleanup progress, or a current-authority or downstream cleanup error.
	 */
	public static function erase_user( Scope $scope, int $user ): array|\WP_Error {
		if ( $scope->site_id < 1 || ! $scope->valid_for_all( array( 'logged_ai_manage_settings', 'logged_ai_purge_data', 'manage_privacy_options' ) ) ) {
			return self::error( 'Report identity erasure requires scoped privacy and purge authority.' ); }
		return self::invalidate( $scope->network_id, $scope->site_id, $user );
	}
	/**
	 * Check current export authority while observing callback mutations and context drift.
	 *
	 * @phpstan-impure Reads current native authority or mutable context.
	 *
	 * @param Scope $scope  Captured native authorization scope whose current grants are rechecked.
	 * @param array $origin Native context captured before protected reads, callbacks or writes.
	 *
	 * @return bool Whether export remains authorized without an observed unsupported mutation.
	 */
	private static function export_allowed( Scope $scope, array $origin ): bool {
		$changed = false;
		$observe = static function ( string $sql ) use ( &$changed ): string {
			$statement = trim( $sql );
			$setup     = false;
			if ( in_array( $statement, array( 'SET SESSION autocommit=1', 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED' ), true ) ) {
				// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_debug_backtrace -- Inspect at most 24 argument-free frames to recognize only Scope permission-connection setup during the report privacy read observer.
				$trace = debug_backtrace( DEBUG_BACKTRACE_PROVIDE_OBJECT | DEBUG_BACKTRACE_IGNORE_ARGS, 24 );
				foreach ( $trace as $index => $frame ) {
					if ( 'query' !== ( $frame['function'] ) || ! ( ( $frame['object'] ?? null ) instanceof \wpdb ) ) {
						continue; }
					$next  = $trace[ $index + 1 ] ?? array();
					$setup = $frame['object'] instanceof WorkerConnection && Scope::class === ( $next['class'] ?? '' ) && 'committed_capabilities' === ( $next['function'] );
					break;
				}
			}
			if ( ! $setup && ! preg_match( '/\A(?:SELECT|SHOW|DESCRIBE|EXPLAIN)\s/i', $statement ) ) {
				$changed = true;
			} return $sql;
		};
		add_filter( 'query', $observe, PHP_INT_MAX );
		try {
			$allowed = $scope->valid_for_all( array( 'logged_ai_export_events' ) ); } finally {
			remove_filter( 'query', $observe, PHP_INT_MAX ); }
			return $allowed && ! $changed && self::context( $GLOBALS['wpdb'] ) === $origin;
	}
	/**
	 * Page validated personal saved searches, schedules and job metadata under privacy guards.
	 *
	 * Finite retained authored definitions and schedules, meaningful with Pro absent.
	 *
	 * @param Scope $scope Captured native authorization scope whose current grants are rechecked.
	 * @param int   $user  Selected registered user identifier for scoped privacy work.
	 * @param array $after Canonical decimal pagination cursors for searches, schedules and jobs.
	 * @param int   $limit Requested bounded privacy page size from one through fifty.
	 *
	 * @return array|\WP_Error Bounded records, per-kind cursors and completion status, or an explicit export error.
	 */
	public static function personal_projection( Scope $scope, int $user, array $after = array(
		'searches'  => '0',
		'schedules' => '0',
		'jobs'      => '0',
	), int $limit = 50 ): array|\WP_Error {
		global $wpdb;
		/**
		 * Current WordPress database adapter used by this operation.
		 *
		 * @var \wpdb $wpdb
		 */
		$origin = self::context( $wpdb );
		if ( $user < 1 || $scope->site_id < 1 || array_keys( $after ) !== array( 'searches', 'schedules', 'jobs' ) || $limit < 1 || $limit > 50 ) {
			return self::error( 'Use a supported report privacy page.' ); }
		foreach ( $after as $cursor ) {
			if ( ! is_string( $cursor ) || ! preg_match( '/\A(?:0|[1-9][0-9]{0,18})\z/D', $cursor ) ) {
				return self::error( 'The report privacy cursor is invalid.' ); }
		}
		if ( ! self::export_allowed( $scope, $origin ) ) {
			return self::error( 'Report privacy export authority is unavailable.' ); }
		$result = Derived::with_mutation(
			static function () use ( $scope, $user, $after, $limit, $origin ): array|\WP_Error {
				global $wpdb;
				/**
				 * Current WordPress database adapter used by this operation.
				 *
				 * @var \wpdb $wpdb
				 */
				$db      = $wpdb;
				$present = self::present( $db );
				if ( is_wp_error( $present ) ) {
					return $present; }
				$result = array(
					'contract_version' => 1,
					'availability'     => $present ? 'retained_configuration' : 'optional_storage_absent',
					'searches'         => array(),
					'schedules'        => array(),
					'jobs'             => array(),
					'next'             => $after,
					'done'             => true,
				);
				if ( ! $present ) {
					return $result;
				} $tables = self::tables( $db );

				$searches = $db->get_results( $db->prepare( "SELECT id,uid,owner_id,visibility,schema_version,revision,title,CASE WHEN OCTET_LENGTH(definition)<=8192 THEN definition ELSE NULL END AS definition,created_at,changed_at FROM %i WHERE network_id=%d AND site_id=%d ORDER BY id LIMIT 101 FOR UPDATE", $tables['searches'], $scope->network_id, $scope->site_id ), ARRAY_A );
				/**
				 * Validate saved searches before producing the bounded personal projection from the untyped wpdb adapter.
				 *
				 * @var mixed $searches
				 */
				if ( '' !== $db->last_error || ! is_array( $searches ) || count( $searches ) > 100 ) {
					return self::error( 'Report privacy search inventory is unavailable or exceeds its bound.' ); }
				$matched            = array();
				$all_search_matches = array();
				foreach ( $searches as $row ) {
					$match = self::search_matches_user( $row, $user );
					if ( is_wp_error( $match ) ) {
								return $match; }
					if ( ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', (string) $row['id'] ) || ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', (string) $row['revision'] ) || ! in_array( $row['visibility'], array( 'private', 'site' ), true ) || ! is_string( $row['title'] ) || strlen( $row['title'] ) > 160 || 1 !== preg_match( '//u', $row['title'] ) ) {
						return self::error( 'Saved-search privacy metadata is incompatible.' ); }
					if ( ! $match ) {
						continue;
					} $matched[] = $row['uid'];
					if ( self::id_after( (string) $row['id'], $after['searches'] ) ) {
						$row['definition']    = self::definition( $row['schema_version'], $row['definition'] );
						$all_search_matches[] = $row; }
				}
				$result['searches'] = array_slice( $all_search_matches, 0, $limit );
				$search_done        = count( $all_search_matches ) <= $limit;
				if ( $result['searches'] ) {
					$last                       = end( $result['searches'] );
					$result['next']['searches'] = (string) $last['id']; }
				$schedules = $db->get_results( $db->prepare( "SELECT id,uid,search_uid,owner_id,revision,enabled,timezone,cadence,local_hour,format,notify_owner,created_at,changed_at FROM %i WHERE network_id=%d AND site_id=%d ORDER BY id LIMIT 21 FOR UPDATE", $tables['schedules'], $scope->network_id, $scope->site_id ), ARRAY_A );
				/**
				 * Validate schedules before producing the bounded personal projection from the untyped wpdb adapter.
				 *
				 * @var mixed $schedules
				 */
				if ( '' !== $db->last_error || ! is_array( $schedules ) || count( $schedules ) > 20 ) {
					return self::error( 'Report privacy schedule inventory is unavailable or exceeds its bound.' ); }
				$periods = $db->get_results( $db->prepare( "SELECT id,schedule_uid,period_key,created_at,expires_at FROM %i WHERE network_id=%d AND site_id=%d ORDER BY id LIMIT 201 FOR UPDATE", $tables['periods'], $scope->network_id, $scope->site_id ), ARRAY_A );
				/**
				 * Validate admission periods before attaching them to personal schedules from the untyped wpdb adapter.
				 *
				 * @var mixed $periods
				 */
				if ( '' !== $db->last_error || ! is_array( $periods ) || count( $periods ) > 200 ) {
					return self::error( 'Report admission privacy inventory is unavailable or exceeds its bound.' ); }
				foreach ( $periods as $period ) {
					if ( ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', (string) $period['id'] ) || ! self::uuid( $period['schedule_uid'] ) || ! is_string( $period['period_key'] ) || ! preg_match( '/\A[a-f0-9]{64}\z/D', $period['period_key'] ) || ! self::timestamp( $period['created_at'] ) || ! self::timestamp( $period['expires_at'] ) ) {
						return self::error( 'Report admission privacy metadata is incompatible.' ); }
				}
				$all_schedule_matches = array();
				foreach ( $schedules as $row ) {
					foreach ( array( 'id', 'owner_id', 'revision' ) as $key ) {
						if ( ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', (string) $row[ $key ] ) ) {
										return self::error( 'Report schedule identity is incompatible.' ); }
					}
					foreach ( array( 'uid', 'search_uid' ) as $key ) {
						if ( ! is_string( $row[ $key ] ) || ! preg_match( '/\A[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}\z/D', $row[ $key ] ) ) {
										return self::error( 'Report schedule identity is incompatible.' ); }
					}
					if ( ! in_array( (string) $row['enabled'], array( '0', '1' ), true ) || ! in_array( (string) $row['notify_owner'], array( '0', '1' ), true ) || ! ctype_digit( (string) $row['local_hour'] ) || (int) $row['local_hour'] > 23 || ! in_array( $row['cadence'], array( 'daily', 'weekly' ), true ) || ! in_array( $row['format'], array( 'csv', 'json', 'html' ), true ) || ! is_string( $row['timezone'] ) || strlen( $row['timezone'] ) > 64 || ! in_array( $row['timezone'], \DateTimeZone::listIdentifiers( \DateTimeZone::ALL_WITH_BC ), true ) ) {
						return self::error( 'Report schedule metadata is incompatible.' ); }
					if ( ( (string) $row['owner_id'] === (string) $user || in_array( $row['search_uid'], $matched, true ) ) && self::id_after( (string) $row['id'], $after['schedules'] ) ) {
						$row['period_admissions'] = array_values( array_filter( $periods, static fn( array $period ): bool => $period['schedule_uid'] === $row['uid'] ) );
						$all_schedule_matches[]   = $row; }
				}
				$result['schedules'] = array_slice( $all_schedule_matches, 0, $limit );
				$schedule_done       = count( $all_schedule_matches ) <= $limit;
				if ( $result['schedules'] ) {
					$last                        = end( $result['schedules'] );
					$result['next']['schedules'] = (string) $last['id']; }
				$jobs = $db->get_results( $db->prepare( "SELECT id,uid,owner_id,schedule_uid,search_uid,CASE WHEN OCTET_LENGTH(definition)<=8192 THEN definition ELSE NULL END AS definition,CASE WHEN OCTET_LENGTH(period)<=4096 THEN period ELSE NULL END AS period,format,state,reason,row_count,byte_count,chunk_count,digest,notify_owner,delivery_state,delivery_attempted_at,created_at,expires_at,changed_at FROM %i WHERE network_id=%d AND site_id=%d ORDER BY id LIMIT 101 FOR UPDATE", $tables['jobs'], $scope->network_id, $scope->site_id ), ARRAY_A );
				/**
				 * Validate report jobs before producing the bounded personal projection from the untyped wpdb adapter.
				 *
				 * @var mixed $jobs
				 */
				if ( '' !== $db->last_error || ! is_array( $jobs ) || count( $jobs ) > 100 ) {
					return self::error( 'Report job privacy inventory is unavailable or exceeds its bound.' ); }
				$job_matches = array();
				foreach ( $jobs as $row ) {
					$clean = self::job_projection( $row );
					if ( is_wp_error( $clean ) ) {
								return $clean; }
					if ( ! self::id_after( (string) $row['id'], $after['jobs'] ) ) {
						continue; }
					$match = self::search_matches_user( $row + array( 'schema_version' => 1 ), $user );
					if ( is_wp_error( $match ) ) {
							return $match; }
					if ( ! $match ) {
						$match = self::job_member_matches( $db, $tables, $scope, $row['uid'], $clean['definition']['type'], $user );
						if ( is_wp_error( $match ) ) {
							return $match; }
					}
					if ( $match ) {
						$job_matches[] = $clean; }
				}
				$result['jobs'] = array_slice( $job_matches, 0, $limit );
				$job_done       = count( $job_matches ) <= $limit;
				if ( $result['jobs'] ) {
					$last                   = end( $result['jobs'] );
					$result['next']['jobs'] = (string) $last['id']; }
				$result['done'] = $search_done && $schedule_done && $job_done;
				return self::held( $db, $origin, $scope->network_id, $scope->site_id ) ? $result : self::error( 'Report privacy authority changed during the read.' );
			},
			static fn(): bool => self::export_allowed( $scope, $origin )
		);
		if ( is_wp_error( $result ) ) {
			return $result; }
		return self::export_allowed( $scope, $origin ) ? $result : self::error( 'Report privacy authority changed before disclosure.' );
	}
	/**
	 * Compare validated decimal identifiers with a cursor without integer conversion.
	 *
	 * @param string $value  Supplied value to inspect without narrowing its native input contract.
	 * @param string $cursor Validated canonical decimal continuation identifier.
	 *
	 * @return bool Whether the identifier lies strictly after the decimal cursor.
	 */
	private static function id_after( string $value, string $cursor ): bool {
		return strlen( $value ) > strlen( $cursor ) || ( strlen( $value ) === strlen( $cursor ) && strcmp( $value, $cursor ) > 0 ); }
	/**
	 * Check the supported lowercase stored UUID representation.
	 *
	 * @param mixed $value Supplied value to inspect without narrowing its native input contract.
	 *
	 * @return bool Whether the supplied value matches the stored UUID grammar.
	 */
	private static function uuid( mixed $value ): bool {
		return is_string( $value ) && 1 === preg_match( '/\A[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}\z/D', $value ); }
	/**
	 * Check an exact fractional-second UTC timestamp by native round trip.
	 *
	 * @param mixed $value Supplied value to inspect without narrowing its native input contract.
	 *
	 * @return bool Whether the supplied value is a supported stored timestamp.
	 */
	private static function timestamp( mixed $value ): bool {
		$date = is_string( $value ) ? \DateTimeImmutable::createFromFormat( '!Y-m-d H:i:s.u', $value, new \DateTimeZone( 'UTC' ) ) : false;
		return $date && $date->format( 'Y-m-d H:i:s.u' ) === $value; }
	/**
	 * Validate retained report metadata, its saved definition and calendar period.
	 *
	 * @param array $row Retained operation-specific metadata row to validate and project.
	 *
	 * @return array|\WP_Error Job metadata with decoded definition and period, or an incompatible-data error.
	 */
	private static function job_projection( array $row ): array|\WP_Error {
		foreach ( array( 'id', 'owner_id' ) as $field ) {
			if ( ! preg_match( '/\A[1-9][0-9]{0,18}\z/D', (string) $row[ $field ] ) ) {
				return self::error( 'Report job privacy identity is incompatible.' ); }
		}
		foreach ( array( 'uid', 'schedule_uid', 'search_uid' ) as $field ) {
			if ( ! self::uuid( $row[ $field ] ) ) {
				return self::error( 'Report job privacy identity is incompatible.' ); }
		}
		foreach ( array( 'created_at', 'changed_at', 'expires_at', 'delivery_attempted_at' ) as $field ) {
			if ( ! self::timestamp( $row[ $field ] ) ) {
				return self::error( 'Report job privacy dates are incompatible.' ); }
		}
		foreach ( array(
			'row_count'    => 10000,
			'byte_count'   => 16777216,
			'chunk_count'  => 102,
			'notify_owner' => 1,
		) as $field => $max ) {
			if ( ! ctype_digit( (string) $row[ $field ] ) || strlen( (string) $row[ $field ] ) > 10 || (int) $row[ $field ] > $max ) {
				return self::error( 'Report job privacy counters are incompatible.' ); }
		}
		if ( ! in_array( $row['format'], array( 'csv', 'json', 'html' ), true ) || ! in_array( $row['state'], array( 'scheduled', 'generating', 'generated', 'failed', 'cancelled', 'expired' ), true ) || ! in_array( $row['delivery_state'], array( 'disabled', 'pending', 'attempting', 'accepted_by_transport', 'failed', 'expired', 'cancelled' ), true ) || ! is_string( $row['reason'] ) || ! preg_match( '/\A[a-z_]{0,40}\z/D', $row['reason'] ) || ! is_string( $row['digest'] ) || ! preg_match( '/\A(?:[a-f0-9]{64})?\z/D', $row['digest'] ) ) {
			return self::error( 'Report job privacy metadata is incompatible.' ); }
		$row['definition'] = self::definition( 1, $row['definition'] );
		if ( is_wp_error( $row['definition'] ) ) {
			return $row['definition']; }
		$period = is_string( $row['period'] ) ? json_decode( $row['period'], true, 4 ) : null;
		$keys   = array( 'contract_version', 'key', 'timezone', 'cadence', 'from_utc', 'before_utc', 'intended_local_date', 'local_hour', 'duration_seconds' );
		if ( ! is_array( $period ) || array_diff( array_keys( $period ), $keys ) || 1 !== ( $period['contract_version'] ?? null ) || ! is_string( $period['key'] ?? null ) || ! preg_match( '/\A[a-f0-9]{64}\z/D', $period['key'] ) || ! is_string( $period['timezone'] ?? null ) || ! in_array( $period['timezone'], \DateTimeZone::listIdentifiers( \DateTimeZone::ALL_WITH_BC ), true ) || ! in_array( $period['cadence'] ?? null, array( 'manual', 'daily', 'weekly' ), true ) || ! self::timestamp( $period['from_utc'] ?? null ) || ! self::timestamp( $period['before_utc'] ?? null ) || $period['from_utc'] >= $period['before_utc'] ) {
			return self::error( 'Report period privacy metadata is incompatible.' ); }
		if ( 'manual' !== $period['cadence'] && ( ! is_string( $period['intended_local_date'] ?? null ) || ! self::timestamp( $period['intended_local_date'] . ' 00:00:00.000000' ) || ! is_int( $period['local_hour'] ?? null ) || $period['local_hour'] < 0 || $period['local_hour'] > 23 || ! is_int( $period['duration_seconds'] ?? null ) || $period['duration_seconds'] < 1 || $period['duration_seconds'] > 8 * DAY_IN_SECONDS ) ) {
			return self::error( 'Report calendar privacy metadata is incompatible.' ); }
		if ( 'manual' === $period['cadence'] && array_intersect( array_keys( $period ), array( 'intended_local_date', 'local_hour', 'duration_seconds' ) ) ) {
			return self::error( 'Manual report period contains unsupported calendar metadata.' ); }
		$row['period'] = $period;
		return $row;
	}
	/**
	 * Find a current event or incident membership relating a report job to a user.
	 *
	 * @param \wpdb  $db     Native database connection selected for this operation.
	 * @param array  $tables Fixed table names resolved from the selected native database adapter.
	 * @param Scope  $scope  Captured native authorization scope whose current grants are rechecked.
	 * @param string $uid    Selected exact stored UUID, or null where the native signature permits absence.
	 * @param string $type   Validated saved-report type selecting event or incident memberships.
	 * @param int    $user   Selected registered user identifier for scoped privacy work.
	 *
	 * @return bool|\WP_Error Whether a matching source relationship exists, or an unavailable-relationship error.
	 */
	private static function job_member_matches( \wpdb $db, array $tables, Scope $scope, string $uid, string $type, int $user ): bool|\WP_Error {
		$members = $tables['members'];

		if ( 'incidents' !== $type ) {
			$source = Store::table();

			$sql    = $db->prepare( "SELECT m.id FROM %i m INNER JOIN %i s ON s.network_id=m.network_id AND s.site_id=m.site_id AND s.id=m.source_id AND s.uid=m.source_uid WHERE m.network_id=%d AND m.site_id=%d AND m.job_uid=%s AND (s.actor_id=%d OR s.subject_id=%d OR (s.object_type='user' AND s.object_id=%d)) LIMIT 1 LOCK IN SHARE MODE", $members, $source, $scope->network_id, $scope->site_id, $uid, $user, $user, $user );
		} else {
			$present = IncidentPrivacy::present( $db );
			if ( is_wp_error( $present ) ) {
				return $present; }
			if ( ! $present ) {
				$member = $db->get_var( $db->prepare( "SELECT id FROM %i m WHERE m.network_id=%d AND m.site_id=%d AND m.job_uid=%s LIMIT 1 LOCK IN SHARE MODE", $members, $scope->network_id, $scope->site_id, $uid ) );
				return '' !== $db->last_error || null !== $member ? self::error( 'Retained incident report relationships are unavailable.' ) : false; }
			$incident = IncidentPrivacy::tables( $db );
			$sql      = $db->prepare( "SELECT m.id FROM %i m INNER JOIN %i s ON s.network_id=m.network_id AND s.site_id=m.site_id AND s.id=m.source_id AND s.uid=m.source_uid WHERE m.network_id=%d AND m.site_id=%d AND m.job_uid=%s AND (s.actor_id=%d OR s.assignee_id=%d OR EXISTS (SELECT 1 FROM %i e WHERE e.network_id=s.network_id AND e.site_id=s.site_id AND e.case_uid=s.uid AND (e.actor_id=%d OR e.subject_id=%d OR (e.object_type='user' AND e.object_id=%d))) OR EXISTS (SELECT 1 FROM %i h WHERE h.network_id=s.network_id AND h.site_id=s.site_id AND h.case_uid=s.uid AND (h.reviewer_id=%d OR h.before_assignee=%d OR h.after_assignee=%d))) LIMIT 1 LOCK IN SHARE MODE", $members, $incident['cases'], $scope->network_id, $scope->site_id, $uid, $user, $user, $incident['events'], $user, $user, $user, $incident['history'], $user, $user, $user );
		}
		$match = $db->get_var( $sql );
		return '' !== $db->last_error ? self::error( 'Report personal membership relationships could not be read.' ) : null !== $match;
	}
	/**
	 * Remove stale or invalid bounded report telemetry using its current storage binding.
	 *
	 * Fixed nonpersonal telemetry expires even when its optional writer is inactive.
	 *
	 * @param \wpdb $db      Native database connection selected for this operation.
	 * @param array $origin  Native context captured before protected reads, callbacks or writes.
	 * @param int   $network Server-selected current network identifier.
	 * @param int   $site    Server-selected site identifier for this operation.
	 *
	 * @return int|\WP_Error Number of removed diagnostic option rows, or a storage/context error.
	 */
	private static function expire_health( \wpdb $db, array $origin, int $network, int $site ): int|\WP_Error {
		$row = $db->get_row( $db->prepare( "SELECT option_id,CASE WHEN OCTET_LENGTH(option_value)<=4096 THEN option_value ELSE NULL END AS value FROM %i WHERE option_name='logged_ai_report_health' LIMIT 1 FOR UPDATE", $db->options ), ARRAY_A );
		if ( '' !== $db->last_error ) {
			return self::error( 'Report diagnostic retention could not be inspected.' ); }
		if ( null === $row ) {
			return 0; }
		$health = is_string( $row['value'] ) ? json_decode( $row['value'], true, 4 ) : null;
		$days   = min( 30, max( 1, (int) Settings::get()['diagnostic_days'] ) );
		if ( is_array( $health ) && 1 === ( $health['version'] ?? null ) && is_int( $health['at'] ?? null ) && $health['at'] > time() - $days * DAY_IN_SECONDS && $health['at'] <= time() + 300 ) {
			return 0; }
		if ( ! self::held( $db, $origin, $network, $site ) ) {
			return self::error( 'Report diagnostic retention context changed.' ); }
		$count = is_string( $row['value'] )
			? $db->query( $db->prepare( "DELETE FROM %i WHERE option_id=%d AND option_value=%s AND option_name='logged_ai_report_health'", $db->options, $row['option_id'], $row['value'] ) )
			: $db->query( $db->prepare( "DELETE FROM %i WHERE option_id=%d AND OCTET_LENGTH(option_value)>4096 AND option_name='logged_ai_report_health'", $db->options, $row['option_id'] ) );
		if ( false === $count ) {
			return self::error( 'Expired report diagnostic storage could not be removed.' ); }
		wp_cache_delete( 'logged_ai_report_health', 'options' );
		wp_cache_delete( 'alloptions', 'options' );
		return self::held( $db, $origin, $network, $site ) ? $count : self::error( 'Report diagnostic retention context changed during cleanup.' );
	}
	/**
	 * Clean bounded expired report admissions, copies and jobs under the shared mutation.
	 *
	 * Expiry is enforced by readers immediately. This physical cleanup preserves caller transaction ownership.
	 *
	 * @return array|\WP_Error Reported removed-row count and remaining-work status, or a retention error.
	 */
	public static function retention(): array|\WP_Error {
		return Derived::with_mutation(
			static function (): array|\WP_Error {
				global $wpdb;
				/**
				 * Current WordPress database adapter used by this operation.
				 *
				 * @var \wpdb $wpdb
				 */
				$db      = $wpdb;
				$network = Scope::network();
				$site    = get_current_blog_id();
				$origin  = self::context( $db );
				if ( ! self::held( $db, $origin, $network, $site ) ) {
					return self::error( 'Report retention scope is unavailable.' ); }
				$removed = self::expire_health( $db, $origin, $network, $site );
				if ( is_wp_error( $removed ) ) {
					return $removed; }
				$present = self::present( $db );
				if ( is_wp_error( $present ) ) {
					return $present;
				} if ( ! $present ) {
					return array(
						'removed' => $removed,
						'done'    => true,
					); }
				$tables = self::tables( $db );

				$now    = Store::now();
				if ( ! self::held( $db, $origin, $network, $site ) ) {
					return self::error( 'Report admission expiry authority changed.' ); }
				$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND expires_at<=%s LIMIT 500", $tables['periods'], $network, $site, $now ) );
				if ( false === $count ) {
					return self::error( 'Expired report admission metadata could not be erased.' );
				} $removed     += $count;
				$period_pending = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d AND expires_at<=%s LIMIT 1 FOR UPDATE", $tables['periods'], $network, $site, $now ) );
				if ( '' !== $db->last_error ) {
					return self::error( 'Report admission expiry completion is unavailable.' ); }

				$jobs          = $db->get_col( $db->prepare( "SELECT j.uid FROM %i j WHERE j.network_id=%d AND j.site_id=%d AND (j.expires_at<=%s OR (j.state IN ('cancelled','failed','expired') AND (EXISTS (SELECT 1 FROM %i c WHERE c.network_id=j.network_id AND c.site_id=j.site_id AND c.job_uid=j.uid) OR EXISTS (SELECT 1 FROM %i m WHERE m.network_id=j.network_id AND m.site_id=j.site_id AND m.job_uid=j.uid)))) ORDER BY j.id LIMIT 5 FOR UPDATE", $tables['jobs'], $network, $site, $now, $tables['chunks'], $tables['members'] ) );
				/**
				 * Validate expired job references before deleting their private report data from the untyped wpdb adapter.
				 *
				 * @var mixed $jobs
				 */
				if ( '' !== $db->last_error || ! is_array( $jobs ) ) {
					return self::error( 'Expired report inventory is unavailable.' ); }
				foreach ( $jobs as $uid ) {
					if ( ! is_string( $uid ) || ! preg_match( '/\A[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}\z/D', $uid ) ) {
						return self::error( 'Expired report identity is invalid.' ); }
					foreach ( array( 'chunks', 'members' ) as $kind ) {
						if ( ! self::held( $db, $origin, $network, $site ) ) {
							return self::error( 'Report retention authority changed.' ); }

						$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND job_uid=%s LIMIT 500", $tables[$kind], $network, $site, $uid ) );
						if ( false === $count ) {
							return self::error( 'Expired report copies could not be removed.' );
						} $removed += $count;
						$pending    = $db->get_var( $db->prepare( "SELECT id FROM %i WHERE network_id=%d AND site_id=%d AND job_uid=%s LIMIT 1 FOR UPDATE", $tables[$kind], $network, $site, $uid ) );
						if ( '' !== $db->last_error ) {
										return self::error( 'Expired report cleanup could not be verified.' ); }
						if ( null !== $pending ) {
							return array(
								'removed' => $removed,
								'done'    => false,
							); }
					}
					if ( ! self::held( $db, $origin, $network, $site ) ) {
						return self::error( 'Report expiry authority changed.' ); }
					$count = $db->query( $db->prepare( "DELETE FROM %i WHERE network_id=%d AND site_id=%d AND uid=%s AND expires_at<=%s", $tables['jobs'], $network, $site, $uid, $now ) );
					if ( false === $count ) {
								return self::error( 'Expired report metadata could not be removed.' );
					} $removed += $count;
				}
				$pending = $db->get_var( $db->prepare( "SELECT j.id FROM %i j WHERE j.network_id=%d AND j.site_id=%d AND (j.expires_at<=%s OR (j.state IN ('cancelled','failed','expired') AND (EXISTS (SELECT 1 FROM %i c WHERE c.network_id=j.network_id AND c.site_id=j.site_id AND c.job_uid=j.uid) OR EXISTS (SELECT 1 FROM %i m WHERE m.network_id=j.network_id AND m.site_id=j.site_id AND m.job_uid=j.uid)))) LIMIT 1 FOR UPDATE", $tables['jobs'], $network, $site, $now, $tables['chunks'], $tables['members'] ) );
				if ( '' !== $db->last_error || ! self::held( $db, $origin, $network, $site ) ) {
					return self::error( 'Report retention completion is unavailable.' ); }
				return array(
					'removed' => $removed,
					'done'    => null === $pending && null === $period_pending,
				);
			}
		);
	}
	/**
	 * Drop only owned report tables after repeated current uninstall-consent checks.
	 *
	 * Exact optional table ownership; no Pro code or broad path deletion is needed.
	 *
	 * @return true|\WP_Error True after verified deletion, or an authority/storage error preserving retry state.
	 */
	public static function uninstall_owned_tables(): bool|\WP_Error {
		global $wpdb;
		/**
		 * Current WordPress database adapter used by this operation.
		 *
		 * @var \wpdb $wpdb
		 */
		if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) || ! Derived::mutation_allowed() ) {
			return self::error( 'Report deletion requires the native held uninstall context.' ); }
		$origin = self::context( $wpdb );
		$tables = self::tables( $wpdb );
		foreach ( array( 'periods', 'chunks', 'members', 'jobs', 'schedules', 'searches' ) as $kind ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Fixed native uninstall-choice SELECT branches; main-network ID is prepared and each current read stays inside the DROP loop. Reread actual uninstall consent before each report table drop; cached settings are not authority.
			$choice = $wpdb->get_var( is_multisite() ? $wpdb->prepare( "SELECT meta_value FROM %i WHERE site_id=%d AND meta_key='logged_ai_delete_all_on_uninstall' LIMIT 1 LOCK IN SHARE MODE", $wpdb->sitemeta, get_main_network_id() ) : "SELECT option_value FROM $wpdb->options WHERE option_name='logged_ai_delete_all_on_uninstall' LIMIT 1 LOCK IN SHARE MODE" );
			if ( '' !== $wpdb->last_error || ! in_array( $choice, array( '1', 'b:1;' ), true ) || self::context( $wpdb ) !== $origin || ! Derived::mutation_allowed() || self::context( $wpdb ) !== $origin ) {
				return self::error( 'Current report deletion authorization is unavailable.' ); }
			$table = $tables[ $kind ];
			if ( ! preg_match( '/\A[a-zA-Z0-9_]{1,64}\z/D', $table ) ) {
				return self::error( 'Report table identity is unsupported.' ); }
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The loop selects only six literal owned report kinds from the fixed table map. Each resulting table must match ASCII alphanumeric/underscore 1..64 immediately before DROP; current native uninstall consent/authority are rechecked inside the loop. Drop exact owned report storage under current uninstall authority; cache clearing is insufficient. Fixed owned schema removal is intentional during authorized native uninstall.
			if ( false === $wpdb->query( $wpdb->prepare( "DROP TABLE IF EXISTS %i", $table ) ) ) {
				return self::error( 'An owned report table could not be removed. Preserve the deletion choice for retry.' ); }
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Verify actual report-table absence after DROP; cached metadata cannot prove cleanup completed.
			$left = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
			if ( '' !== $wpdb->last_error || null !== $left ) {
				return self::error( 'Report table deletion could not be verified.' ); }
		}
		return Derived::mutation_allowed() && self::context( $wpdb ) === $origin ? true : self::error( 'Report deletion authority changed before completion.' );
	}
}