<?php
/**
 * Bounded, structured Site Activity storage.
 *
 * @package AdminWatch
 */

defined( 'ABSPATH' ) || exit;

class WebStudio_Admin_Watch_Activity_Repository {

	const DB_VERSION = '1.0.0';
	/** @return string */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'admin_watch_activity';
	}

	/** Install only this module's schema; never modify the 404 schema version. */
	public static function install() {
		global $wpdb;
		$table = self::table_name();
		$collation = $wpdb->get_charset_collate();
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( "CREATE TABLE $table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_type varchar(64) NOT NULL,
			category varchar(32) NOT NULL,
			severity varchar(16) NOT NULL DEFAULT 'info',
			actor_user_id bigint(20) unsigned DEFAULT NULL,
			object_type varchar(32) DEFAULT NULL,
			object_id bigint(20) unsigned DEFAULT NULL,
			object_label varchar(191) DEFAULT NULL,
			event_data text DEFAULT NULL,
			occurrence_count int(10) unsigned NOT NULL DEFAULT 1,
			aggregation_subject varchar(60) DEFAULT NULL,
			bucket_start datetime DEFAULT NULL,
			created_at datetime NOT NULL,
			last_occurred_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY last_occurred_at (last_occurred_at),
			KEY actor_date (actor_user_id,last_occurred_at),
			KEY category_date (category,last_occurred_at),
			KEY object_reference (object_type,object_id),
			KEY failed_bucket_date (event_type,bucket_start),
			UNIQUE KEY failed_bucket (event_type,aggregation_subject,bucket_start)
		) $collation;" );
		// Do not mark a failed installation as complete.
		$sql = $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Prepared custom schema existence check after dbDelta.
		if ( $table === $wpdb->get_var( $sql ) ) {
			update_option( 'admin_watch_activity_db_version', self::DB_VERSION, false );
		}
	}

	/** Return complete defaults without overwriting existing site settings. */
	public static function settings() {
		$stored = get_option( 'admin_watch_settings', array() );
		return wp_parse_args( is_array( $stored ) ? $stored : array(), array(
			'monitor_404s' => 1, 'retention_days' => 30, 'delete_data' => 0,
			'monitor_activity' => 1, 'activity_retention_days' => 30, 'record_failed_logins' => 1,
		) );
	}

	/** Bound plain text without breaking multi-byte characters. */
	public static function text( $value, $length = 191 ) {
		return is_scalar( $value ) ? wp_html_excerpt( sanitize_text_field( (string) $value ), $length, '' ) : '';
	}

	/** A failed identifier is personal data, never an IP-derived grouping key. */
	public static function subject( $value ) {
		$value = strtolower( self::text( $value, 1000 ) );
		// Oversized identifiers join the unnamed group; never merge unrelated truncated names.
		return self::text( $value, 60 ) !== $value || filter_var( $value, FILTER_VALIDATE_IP ) ? '' : $value;
	}

	/** Store a known event; metadata is an event-specific allow-list. */
	public function record( $type, $actor = 0, $object_type = '', $object_id = 0, $label = '', $data = array(), $subject = null ) {
		global $wpdb;
		$definition = WebStudio_Admin_Watch_Activity_Events::definition( $type );
		$settings = self::settings();
		$disabling = 'settings_updated' === $type && isset( $data['monitor_activity'] ) && '1 → 0' === $data['monitor_activity'];
		if ( ! $definition || ( empty( $settings['monitor_activity'] ) && ! $disabling ) || self::DB_VERSION !== get_option( 'admin_watch_activity_db_version' ) ) {
			return false;
		}
		if ( 'login_failed' === $type && empty( $settings['record_failed_logins'] ) ) {
			return false;
		}
		$metadata = array();
		foreach ( $definition['fields'] as $field ) {
			if ( isset( $data[ $field ] ) && is_scalar( $data[ $field ] ) ) {
				$metadata[ $field ] = self::text( $data[ $field ], 191 );
			}
		}
		$now = gmdate( 'Y-m-d H:i:s' );
		$row = array(
			'event_type' => $type, 'category' => $definition['category'], 'severity' => $definition['severity'],
			'actor_user_id' => absint( $actor ) ?: null, 'object_type' => sanitize_key( $object_type ) ?: null,
			'object_id' => absint( $object_id ) ?: null, 'object_label' => self::text( $label ),
			'event_data' => $metadata ? wp_json_encode( $metadata ) : null,
			'created_at' => $now, 'last_occurred_at' => $now,
		);
		if ( 'login_failed' === $type ) {
			$row['aggregation_subject'] = self::subject( $subject );
			$row['object_label'] = $row['aggregation_subject'];
			$row['bucket_start'] = gmdate( 'Y-m-d H:i:s', (int) ( floor( time() / 900 ) * 900 ) );
			// One database advisory lock protects the per-bucket row cap under concurrency.
			$lock = 'aw-failed-' . substr( hash( 'sha256', self::table_name() ), 0, 32 );
			$lock_sql = $wpdb->prepare( 'SELECT GET_LOCK(%s, 1)', $lock );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Short custom-table aggregation lock; fail closed rather than grow under floods.
			if ( 1 !== (int) $wpdb->get_var( $lock_sql ) ) {
				return false;
			}
			try {
				$count_sql = $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE event_type = %s AND bucket_start = %s', self::table_name(), $type, $row['bucket_start'] );
				$exists_sql = $wpdb->prepare( 'SELECT id FROM %i WHERE event_type = %s AND aggregation_subject = %s AND bucket_start = %s', self::table_name(), $type, $row['aggregation_subject'], $row['bucket_start'] );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Prepared indexed aggregation lookup under the same lock.
				$exists = $wpdb->get_var( $exists_sql );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Count at most 101 indexed bucket rows.
				if ( ! $exists && (int) $wpdb->get_var( $count_sql ) >= 100 ) {
					// Empty subject is a bounded overflow group, not a fabricated username.
					$row['aggregation_subject'] = '';
					$row['object_label'] = '';
				}
				$sql = $wpdb->prepare( 'INSERT INTO %i (event_type,category,severity,object_label,aggregation_subject,bucket_start,created_at,last_occurred_at) VALUES (%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE occurrence_count = LEAST(occurrence_count + 1, 4294967295), last_occurred_at = VALUES(last_occurred_at)', self::table_name(), $type, $row['category'], $row['severity'], $row['object_label'], $row['aggregation_subject'], $row['bucket_start'], $now, $now );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Atomic prepared aggregate write; caching would lose counts.
				return false !== $wpdb->query( $sql );
			} finally {
				$release_sql = $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Always release the short aggregation lock.
				$wpdb->get_var( $release_sql );
			}
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Structured custom-table insert with wpdb's value preparation.
		return false !== $wpdb->insert( self::table_name(), $row );
	}

	/** Build SQL only from validated filters and fixed clauses. */
	private function where( $filters ) {
		global $wpdb;
		$clauses = array( '1=1' );
		if ( ! empty( $filters['category'] ) && isset( WebStudio_Admin_Watch_Activity_Events::categories()[ $filters['category'] ] ) ) {
			$clauses[] = $wpdb->prepare( 'category = %s', $filters['category'] );
		}
		if ( isset( $filters['user_id'] ) && is_numeric( $filters['user_id'] ) && (int) $filters['user_id'] > 0 ) {
			$clauses[] = $wpdb->prepare( '(actor_user_id = %d OR (object_type = %s AND object_id = %d))', absint( $filters['user_id'] ), 'user', absint( $filters['user_id'] ) );
		} elseif ( isset( $filters['user_id'] ) && 'system' === $filters['user_id'] ) {
			$clauses[] = "actor_user_id IS NULL AND event_type <> 'login_failed'";
		} elseif ( isset( $filters['user_id'] ) && 'anonymous' === $filters['user_id'] ) {
			$clauses[] = "event_type = 'login_failed'";
		}
		if ( ! empty( $filters['since'] ) ) {
			$clauses[] = $wpdb->prepare( 'last_occurred_at >= %s', $filters['since'] );
		}
		if ( ! empty( $filters['search'] ) ) {
			$like = '%' . $wpdb->esc_like( self::text( $filters['search'], 100 ) ) . '%';
			// Search bounded labels and core user fields, never arbitrary JSON.
			$search_clause = $wpdb->prepare( 'object_label LIKE %s OR event_type LIKE %s OR actor_user_id IN (SELECT ID FROM %i WHERE display_name LIKE %s OR user_login LIKE %s) OR (object_type = %s AND object_id IN (SELECT ID FROM %i WHERE display_name LIKE %s OR user_login LIKE %s))', $like, $like, $wpdb->users, $like, $like, 'user', $wpdb->users, $like, $like );
			foreach ( WebStudio_Admin_Watch_Activity_Events::search_types( $filters['search'] ) as $type ) {
				$search_clause .= $wpdb->prepare( ' OR event_type = %s', $type );
			}
			$clauses[] = '(' . $search_clause . ')';
		}
		if ( isset( $filters['privacy_email'] ) ) {
			$user_id = absint( $filters['privacy_user'] );
			$account = $user_id ? get_userdata( $user_id ) : false;
			$subjects = array( self::subject( $filters['privacy_email'] ) );
			if ( $account ) {
				$subjects[] = self::subject( $account->user_login );
			}
			$privacy_clause = $wpdb->prepare( 'actor_user_id = %d OR (object_type = %s AND object_id = %d)', $user_id, 'user', $user_id );
			foreach ( array_unique( $subjects ) as $subject ) {
				// Never link an unnamed overflow bucket to a privacy requester.
				if ( '' !== $subject ) {
					$privacy_clause .= $wpdb->prepare( ' OR (event_type = %s AND aggregation_subject = %s)', 'login_failed', $subject );
				}
			}
			$clauses[] = '(' . $privacy_clause . ')';
		}
		return implode( ' AND ', $clauses );
	}

	/** @return int */
	public function count( $filters = array() ) {
		global $wpdb;
		$sql = $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE ', self::table_name() ) . $this->where( $filters );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- where() returns only fixed clauses and wpdb-prepared values; the checker cannot trace a private clause builder.
		return (int) $wpdb->get_var( $sql );
	}

	/** Fetch one bounded page with stable ordering. */
	public function items( $filters = array(), $page = 1, $per_page = 20 ) {
		global $wpdb;
		$per_page = min( 100, max( 1, absint( $per_page ) ) );
		$sql = $wpdb->prepare( 'SELECT * FROM %i WHERE ', self::table_name() ) . $this->where( $filters ) . $wpdb->prepare( ' ORDER BY last_occurred_at DESC, id DESC LIMIT %d OFFSET %d', $per_page, ( max( 1, absint( $page ) ) - 1 ) * $per_page );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Identifiers, where() values and pagination are separately wpdb-prepared; no raw input is concatenated.
		return $wpdb->get_results( $sql );
	}

	/** Populate only a bounded recent-user filter, without loading activity rows. */
	public function users() {
		global $wpdb;
		$sql = $wpdb->prepare( 'SELECT actor_user_id AS user_id, MAX(last_occurred_at) AS seen FROM %i WHERE actor_user_id IS NOT NULL GROUP BY actor_user_id UNION SELECT object_id AS user_id, MAX(last_occurred_at) AS seen FROM %i WHERE object_type = %s AND object_id IS NOT NULL GROUP BY object_id ORDER BY seen DESC LIMIT 100', self::table_name(), self::table_name(), 'user' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Bounded custom history participant query for admin filters.
		return array_unique( array_map( 'absint', $wpdb->get_col( $sql ) ) );
	}

	/** Remove expiry in one indexed daily operation. */
	public function cleanup( $days ) {
		global $wpdb;
		$days = in_array( absint( $days ), array( 7, 30, 90 ), true ) ? absint( $days ) : 30;
		$sql = $wpdb->prepare( 'DELETE FROM %i WHERE last_occurred_at < %s', self::table_name(), gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Prepared daily retention deletion from indexed custom table.
		return $wpdb->query( $sql );
	}

	/** Clear only this module, after the caller checks capability and nonce. */
	public function clear() {
		global $wpdb;
		$sql = $wpdb->prepare( 'DELETE FROM %i', self::table_name() );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- Explicitly authorized deletion of only this module's table rows.
		return $wpdb->query( $sql );
	}

	/** Erase a first batch each time so deletion cannot skip shifted offsets. */
	public function erase( $user_id, $email ) {
		global $wpdb;
		$filters = array( 'privacy_user' => $user_id, 'privacy_email' => $email );
		$rows = $this->items( $filters, 1, 100 );
		foreach ( $rows as $row ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- wpdb prepares row IDs for a bounded personal-data deletion batch.
			if ( false === $wpdb->delete( self::table_name(), array( 'id' => $row->id ), array( '%d' ) ) ) {
				return array( 'items_removed' => false, 'items_retained' => true, 'messages' => array( __( 'Activity could not be erased. Please retry the privacy request.', 'adminwatch-central' ) ), 'done' => true );
			}
		}
		return array( 'items_removed' => ! empty( $rows ), 'items_retained' => false, 'messages' => array(), 'done' => 0 === $this->count( $filters ) );
	}
}