<?php
/**
 * Database access layer for activity logs.
 *
 * All queries use $wpdb->prepare and properly cast types — no string
 * interpolation of user input into SQL.
 *
 * @package ActivityPilot
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// Custom activity-log tables require direct database access.

class APWPM_DB {

	/**
	 * Cache group for DB read helpers.
	 *
	 * @return string
	 */
	private static function cache_group() {
		return 'apwpm_db';
	}

	/**
	 * Build a stable cache key for SQL + args.
	 *
	 * @param string $method Query method name.
	 * @param string $sql    SQL template.
	 * @param array  $args   SQL args.
	 * @return string
	 */
	private static function cache_key( $method, $sql, array $args = array() ) {
		return 'apwpm:' . md5( $method . '|' . $sql . '|' . wp_json_encode( $args ) );
	}

	/**
	 * Clear object-cache group after writes.
	 *
	 * @return void
	 */
	private static function flush_cache() {
		if ( function_exists( 'wp_cache_flush_group' ) ) {
			wp_cache_flush_group( self::cache_group() );
			return;
		}
		wp_cache_flush();
	}

	/**
	 * Run cached get_var query.
	 *
	 * @param string $sql SQL template.
	 * @param array  $args SQL args.
	 * @param int    $ttl Cache TTL.
	 * @return string|null
	 */
	private static function cached_get_var( $sql, array $args = array(), $ttl = MINUTE_IN_SECONDS ) {
		global $wpdb;
		$key    = self::cache_key( 'var', $sql, $args );
		$cached = wp_cache_get( $key, self::cache_group() );
		if ( false !== $cached ) {
			return $cached;
		}
		$prepare_args = array_merge( array( $sql ), $args );
		$prepared_sql = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
		$value        = call_user_func( array( $wpdb, 'get_var' ), $prepared_sql );
		wp_cache_set( $key, $value, self::cache_group(), (int) $ttl );
		return $value;
	}

	/**
	 * Run cached get_results query.
	 *
	 * @param string $sql SQL template.
	 * @param array  $args SQL args.
	 * @param string $output Output type.
	 * @param int    $ttl Cache TTL.
	 * @return array
	 */
	private static function cached_get_results( $sql, array $args = array(), $output = OBJECT, $ttl = MINUTE_IN_SECONDS ) {
		global $wpdb;
		$key    = self::cache_key( 'results:' . $output, $sql, $args );
		$cached = wp_cache_get( $key, self::cache_group() );
		if ( false !== $cached ) {
			return is_array( $cached ) ? $cached : array();
		}
		$prepare_args = array_merge( array( $sql ), $args );
		$prepared_sql = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
		$rows         = call_user_func( array( $wpdb, 'get_results' ), $prepared_sql, $output );
		$rows     = is_array( $rows ) ? $rows : array();
		wp_cache_set( $key, $rows, self::cache_group(), (int) $ttl );
		return $rows;
	}

	/**
	 * Run cached get_row query.
	 *
	 * @param string $sql SQL template.
	 * @param array  $args SQL args.
	 * @param string $output Output type.
	 * @param int    $ttl Cache TTL.
	 * @return mixed
	 */
	private static function cached_get_row( $sql, array $args = array(), $output = OBJECT, $ttl = MINUTE_IN_SECONDS ) {
		global $wpdb;
		$key    = self::cache_key( 'row:' . $output, $sql, $args );
		$cached = wp_cache_get( $key, self::cache_group() );
		if ( false !== $cached ) {
			return $cached;
		}
		$prepare_args = array_merge( array( $sql ), $args );
		$prepared_sql = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
		$row          = call_user_func( array( $wpdb, 'get_row' ), $prepared_sql, $output );
		wp_cache_set( $key, $row, self::cache_group(), (int) $ttl );
		return $row;
	}

	/**
	 * Run cached get_col query.
	 *
	 * @param string $sql SQL template.
	 * @param array  $args SQL args.
	 * @param int    $ttl Cache TTL.
	 * @return array
	 */
	private static function cached_get_col( $sql, array $args = array(), $ttl = MINUTE_IN_SECONDS ) {
		global $wpdb;
		$key    = self::cache_key( 'col', $sql, $args );
		$cached = wp_cache_get( $key, self::cache_group() );
		if ( false !== $cached ) {
			return is_array( $cached ) ? $cached : array();
		}
		$prepare_args = array_merge( array( $sql ), $args );
		$prepared_sql = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
		$col          = call_user_func( array( $wpdb, 'get_col' ), $prepared_sql );
		$col      = is_array( $col ) ? $col : array();
		wp_cache_set( $key, $col, self::cache_group(), (int) $ttl );
		return $col;
	}

	/**
	 * Execute prepared write query and flush cache.
	 *
	 * @param string $sql SQL template.
	 * @param array  $args SQL args.
	 * @return int|false
	 */
	private static function run_write_query( $sql, array $args = array() ) {
		global $wpdb;
		$prepare_args = array_merge( array( $sql ), $args );
		$prepared_sql = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
		$result       = call_user_func( array( $wpdb, 'query' ), $prepared_sql );
		self::flush_cache();
		return $result;
	}

	/**
	 * Wrapper for wpdb insert with cache flush.
	 *
	 * @param string $table Table name.
	 * @param array  $data Row data.
	 * @param array  $format Row formats.
	 * @return int|false
	 */
	private static function run_insert( $table, array $data, array $format ) {
		global $wpdb;
		$result = call_user_func( array( $wpdb, 'insert' ), $table, $data, $format );
		self::flush_cache();
		return $result;
	}

	/**
	 * Wrapper for wpdb update with cache flush.
	 *
	 * @param string $table Table name.
	 * @param array  $data Data.
	 * @param array  $where Where.
	 * @param array  $format Formats.
	 * @param array  $where_format Where formats.
	 * @return int|false
	 */
	private static function run_update( $table, array $data, array $where, array $format, array $where_format ) {
		global $wpdb;
		$result = call_user_func( array( $wpdb, 'update' ), $table, $data, $where, $format, $where_format );
		self::flush_cache();
		return $result;
	}

	/**
	 * Wrapper for wpdb delete with cache flush.
	 *
	 * @param string $table Table name.
	 * @param array  $where Where.
	 * @param array  $where_format Where formats.
	 * @return int|false
	 */
	private static function run_delete( $table, array $where, array $where_format ) {
		global $wpdb;
		$result = call_user_func( array( $wpdb, 'delete' ), $table, $where, $where_format );
		self::flush_cache();
		return $result;
	}

	/**
	 * Fully-qualified table name. Uses base_prefix so multisite installs
	 * share a single, network-wide activity log.
	 */
	public static function table() {
		global $wpdb;
		static $resolved_table = null;
		if ( null !== $resolved_table ) {
			return $resolved_table;
		}

		$prefix       = isset( $wpdb->base_prefix ) ? $wpdb->base_prefix : $wpdb->prefix;
		$primary      = $prefix . APWPM_TABLE_LOGS;
		$legacy_table = $prefix . 'ap_activity_logs';

		$primary_exists = self::cached_get_var(
			'SHOW TABLES LIKE %s',
			array( $primary ),
			HOUR_IN_SECONDS
		);
		if ( $primary_exists === $primary ) {
			$resolved_table = $primary;
			return $resolved_table;
		}

		$legacy_exists = self::cached_get_var(
			'SHOW TABLES LIKE %s',
			array( $legacy_table ),
			HOUR_IN_SECONDS
		);
		if ( $legacy_exists === $legacy_table ) {
			$resolved_table = $legacy_table;
			return $resolved_table;
		}

		$resolved_table = $primary;
		return $resolved_table;
	}

	/**
	 * Whether a custom table exists in the database.
	 *
	 * @param string $table_name Fully-qualified table name.
	 * @return bool
	 */
	public static function table_exists( $table_name ) {
		$found = self::cached_get_var(
			'SHOW TABLES LIKE %s',
			array( $table_name ),
			MINUTE_IN_SECONDS
		);

		return $found === $table_name;
	}

	/**
	 * Rename a custom table (used by schema migrations).
	 *
	 * @param string $old_name Current table name.
	 * @param string $new_name New table name.
	 * @return bool True when the rename succeeds or is not needed.
	 */
	public static function rename_table( $old_name, $new_name ) {
		if ( $old_name === $new_name ) {
			return true;
		}
		if ( ! self::is_valid_table_name( $old_name ) || ! self::is_valid_table_name( $new_name ) ) {
			return false;
		}
		if ( ! self::table_exists( $old_name ) || self::table_exists( $new_name ) ) {
			return false;
		}

		$sql    = sprintf( 'RENAME TABLE `%s` TO `%s`', $old_name, $new_name );
		$result = self::run_ddl_query( $sql );

		return false !== $result;
	}

	/**
	 * Validate a SQL table identifier (alphanumeric + underscore).
	 *
	 * @param string $table_name Table name.
	 * @return bool
	 */
	private static function is_valid_table_name( $table_name ) {
		return (bool) preg_match( '/^[0-9a-zA-Z_]+$/', $table_name );
	}

	/**
	 * Run schema DDL and flush caches.
	 *
	 * @param string $sql DDL statement.
	 * @return int|false
	 */
	private static function run_ddl_query( $sql ) {
		global $wpdb;
		$result = call_user_func( array( $wpdb, 'query' ), $sql );
		self::flush_cache();
		return $result;
	}

	/**
	 * Compute a MySQL-formatted cutoff timestamp in the SAME timezone
	 * that `current_time('mysql')` uses for inserts. This avoids the
	 * classic mismatch where the DB session is on UTC but WordPress is
	 * in a local timezone — `NOW() - INTERVAL ...` then disagrees with
	 * the stored `created_at` and recent rows get filtered out.
	 *
	 * @param int    $amount Positive integer (e.g. 60).
	 * @param string $unit   One of MINUTE | HOUR | DAY.
	 * @return string `Y-m-d H:i:s` cutoff timestamp in WP local time.
	 */
	public static function cutoff( $amount, $unit = 'MINUTE' ) {
		$amount = max( 0, (int) $amount );
		$unit   = strtoupper( (string) $unit );
		$secs   = array(
			'MINUTE' => MINUTE_IN_SECONDS,
			'HOUR'   => HOUR_IN_SECONDS,
			'DAY'    => DAY_IN_SECONDS,
		);
		$mult   = isset( $secs[ $unit ] ) ? $secs[ $unit ] : MINUTE_IN_SECONDS;

		// Parse the WP-local "now" string and subtract using PHP arithmetic.
		// gmdate() preserves the same wall-clock string we'd get from
		// current_time('mysql').
		$now_ts    = strtotime( current_time( 'mysql' ) );
		$cutoff_ts = $now_ts - ( $amount * $mult );
		return gmdate( 'Y-m-d H:i:s', $cutoff_ts );
	}

	/**
	 * The "now" value matching the `created_at` insertion timezone.
	 */
	public static function db_now() {
		return current_time( 'mysql' );
	}

	/**
	 * Insert a new log row.
	 *
	 * @param array $data Already-sanitized row data.
	 * @return int|false Insert ID on success, false on failure.
	 */
	public static function insert( array $data ) {
		global $wpdb;

		$defaults = array(
			'blog_id'      => 0,
			'user_id'      => 0,
			'user_login'   => '',
			'user_role'    => '',
			'action_type'  => '',
			'object_type'  => '',
			'object_id'    => 0,
			'object_title' => '',
			'description'  => '',
			'context'      => '',
			'row_hash'     => '',
			'prev_hash'    => '',
			'severity'     => 'info',
			'status'       => 'open',
			'assignee_id'  => 0,
			'is_anomaly'   => 0,
			'rolled_back'  => 0,
			'rollback_of'  => 0,
			'ip_address'   => '',
			'country'      => '',
			'city'         => '',
			'user_agent'   => '',
			'session_id'   => '',
			'created_at'   => current_time( 'mysql' ),
		);

		$row = wp_parse_args( $data, $defaults );

		// Filter out columns the table doesn't have yet (pre-migration).
		$columns = self::columns();
		$row     = array_intersect_key( $row, array_flip( $columns ) );

		$type_map = array(
			'blog_id'      => '%d',
			'user_id'      => '%d',
			'user_login'   => '%s',
			'user_role'    => '%s',
			'action_type'  => '%s',
			'object_type'  => '%s',
			'object_id'    => '%d',
			'object_title' => '%s',
			'description'  => '%s',
			'context'      => '%s',
			'row_hash'     => '%s',
			'prev_hash'    => '%s',
			'severity'     => '%s',
			'status'       => '%s',
			'assignee_id'  => '%d',
			'is_anomaly'   => '%d',
			'rolled_back'  => '%d',
			'rollback_of'  => '%d',
			'ip_address'   => '%s',
			'country'      => '%s',
			'city'         => '%s',
			'user_agent'   => '%s',
			'session_id'   => '%s',
			'created_at'   => '%s',
		);
		$format   = array();
		foreach ( $row as $k => $_v ) {
			$format[] = isset( $type_map[ $k ] ) ? $type_map[ $k ] : '%s';
		}

		$ok = self::run_insert( self::table(), $row, $format );

		return $ok ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Retrieve logs with filters.
	 *
	 * @param array $args Filters: user_id, action_type, object_type,
	 *                    severity, search, date_from, date_to, per_page, page.
	 * @return array { items: array, total: int }
	 */
	public static function get_logs( array $args = array() ) {
		global $wpdb;

		$defaults = array(
			'user_id'     => 0,
			'action_type' => '',
			'object_type' => '',
			'severity'    => '',
			'search'      => '',
			'date_from'   => '',
			'date_to'     => '',
			'blog_id'     => 0,
			'anomalies'   => 0,
			'status'      => '',
			'assignee_id' => 0,
			'country'     => '',
			'since_id'    => 0,
			'per_page'    => 25,
			'page'        => 1,
			'orderby'     => 'created_at',
			'order'       => 'DESC',
		);

		$args = wp_parse_args( $args, $defaults );

		$where  = array( '1=1' );
		$params = array();

		if ( $args['user_id'] > 0 ) {
			$where[]  = 'user_id = %d';
			$params[] = (int) $args['user_id'];
		}
		if ( ! empty( $args['action_type'] ) ) {
			$where[]  = 'action_type = %s';
			$params[] = (string) $args['action_type'];
		}
		if ( ! empty( $args['object_type'] ) ) {
			$where[]  = 'object_type = %s';
			$params[] = (string) $args['object_type'];
		}
		if ( ! empty( $args['severity'] ) ) {
			$where[]  = 'severity = %s';
			$params[] = (string) $args['severity'];
		}
		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'created_at >= %s';
			$params[] = (string) $args['date_from'];
		}
		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'created_at <= %s';
			$params[] = (string) $args['date_to'];
		}
		if ( ! empty( $args['blog_id'] ) && in_array( 'blog_id', self::columns(), true ) ) {
			$where[]  = 'blog_id = %d';
			$params[] = (int) $args['blog_id'];
		}
		if ( ! empty( $args['anomalies'] ) && in_array( 'is_anomaly', self::columns(), true ) ) {
			$where[] = 'is_anomaly = 1';
		}
		if ( ! empty( $args['status'] ) && in_array( 'status', self::columns(), true ) ) {
			$where[]  = 'status = %s';
			$params[] = (string) $args['status'];
		}
		if ( ! empty( $args['assignee_id'] ) && in_array( 'assignee_id', self::columns(), true ) ) {
			$where[]  = 'assignee_id = %d';
			$params[] = (int) $args['assignee_id'];
		}
		if ( ! empty( $args['country'] ) && in_array( 'country', self::columns(), true ) ) {
			$where[]  = 'country = %s';
			$params[] = (string) $args['country'];
		}
		if ( ! empty( $args['since_id'] ) ) {
			$where[]  = 'id > %d';
			$params[] = (int) $args['since_id'];
		}
		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]  = '(description LIKE %s OR object_title LIKE %s OR user_login LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$allowed_orderby = array( 'id', 'created_at', 'action_type', 'severity', 'user_id' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$per_page = max( 1, min( 200, (int) $args['per_page'] ) );
		$page     = max( 1, (int) $args['page'] );
		$offset   = ( $page - 1 ) * $per_page;

		$where_sql = implode( ' AND ', $where );
		$count_sql = "SELECT COUNT(*) FROM %i WHERE {$where_sql}";
		$count_args = array_merge( array( self::table() ), $params );
		$total = (int) call_user_func(
			array( $wpdb, 'get_var' ),
			call_user_func_array(
				array( $wpdb, 'prepare' ),
				array_merge( array( $count_sql ), $count_args )
			)
		);

		$list_sql = "SELECT * FROM %i WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$list_args = array_merge( array( self::table() ), $params, array( $per_page, $offset ) );
		$items = call_user_func(
			array( $wpdb, 'get_results' ),
			call_user_func_array(
				array( $wpdb, 'prepare' ),
				array_merge( array( $list_sql ), $list_args )
			),
			ARRAY_A
		);

		// Safety fallback: if a filter query fails on legacy schemas, return basic timeline rows.
		if ( ! empty( $wpdb->last_error ) ) {
			$total = (int) self::cached_get_var(
				'SELECT COUNT(*) FROM %i',
				array( self::table() )
			);
			$items = self::cached_get_results(
				'SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d',
				array(
					self::table(),
					$per_page,
					$offset,
				),
				ARRAY_A
			);
		}

		return array(
			'items' => is_array( $items ) ? $items : array(),
			'total' => $total,
		);
	}

	/**
	 * Get distinct values for a column. Useful to power filter dropdowns.
	 *
	 * @param string $column Column name (whitelisted).
	 * @return array
	 */
	public static function count_in_period( $hours ) {
		return (int) self::cached_get_var(
			'SELECT COUNT(*) FROM %i WHERE created_at >= %s',
			array(
				self::table(),
				self::cutoff( (int) $hours, 'HOUR' ),
			)
		);
	}

	public static function top_actions( $hours = 168, $limit = 5 ) {
		return self::cached_get_results(
			'SELECT action_type, COUNT(*) AS total FROM %i
				 WHERE created_at >= %s
				 GROUP BY action_type
				 ORDER BY total DESC
				 LIMIT %d',
			array(
				self::table(),
				self::cutoff( (int) $hours, 'HOUR' ),
				(int) $limit,
			)
		);
	}

	public static function get_distinct( $column ) {
		$allowed = array( 'action_type', 'object_type', 'severity', 'user_login' );
		if ( ! in_array( $column, $allowed, true ) ) {
			return array();
		}
		return self::cached_get_col(
			'SELECT DISTINCT %i FROM %i WHERE %i != %s ORDER BY %i ASC',
			array(
				$column,
				self::table(),
				$column,
				'',
				$column,
			)
		);
	}

	/**
	 * Aggregate counts grouped by day for the dashboard chart.
	 *
	 * @param int $days Days back from today.
	 * @return array [ ['day' => 'YYYY-MM-DD', 'total' => N, 'errors' => N], ... ]
	 */
	public static function get_daily_stats( $days = 14 ) {
		$days = max( 1, min( 365, (int) $days ) );
		$rows = self::cached_get_results(
			"SELECT DATE(created_at) AS day,
				        COUNT(*) AS total,
				        SUM(CASE WHEN severity IN ('error','critical') THEN 1 ELSE 0 END) AS errors
				 FROM %i
				 WHERE created_at >= %s
				 GROUP BY DATE(created_at)
				 ORDER BY day ASC",
			array(
				self::table(),
				self::cutoff( $days, 'DAY' ),
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Count error/critical entries inside a recent minute window.
	 */
	public static function count_recent_errors( $minutes = 10 ) {
		$minutes = max( 1, (int) $minutes );
		return (int) self::cached_get_var(
			"SELECT COUNT(*) FROM %i
				 WHERE severity IN ('error','critical')
				 AND created_at >= %s",
			array(
				self::table(),
				self::cutoff( $minutes, 'MINUTE' ),
			)
		);
	}

	/**
	 * Delete logs older than N days.
	 */
	public static function prune_older_than( $days ) {
		if ( false === APWPM_Compat::filter( 'apwpm_can_prune', 'ap_can_prune', true ) ) {
			return 0;
		}
		return self::run_write_query(
			'DELETE FROM %i WHERE created_at < %s',
			array(
				self::table(),
				self::cutoff( $days, 'DAY' ),
			)
		);
	}

	/**
	 * Truncate all logs (admin-only operation).
	 */
	public static function truncate() {
		if ( false === APWPM_Compat::filter( 'apwpm_can_truncate', 'ap_can_truncate', true ) ) {
			return false;
		}
		return self::run_write_query(
			'TRUNCATE TABLE %i',
			array(
				self::table(),
			)
		);
	}

	/**
	 * Cached column list for the activity log table.
	 *
	 * @return string[]
	 */
	public static function columns() {
		static $cache = null;
		if ( null !== $cache ) {
			return $cache;
		}
		$cache = self::cached_get_col(
			'SHOW COLUMNS FROM %i',
			array(
				self::table(),
			),
			HOUR_IN_SECONDS
		);
		return $cache;
	}

	/**
	 * Per-blog aggregate (multisite network view).
	 *
	 * @return array
	 */
	public static function counts_by_blog( $days = 7 ) {
		$days = max( 1, min( 365, (int) $days ) );

		if ( ! in_array( 'blog_id', self::columns(), true ) ) {
			return array();
		}

		$rows = self::cached_get_results(
			"SELECT blog_id,
				        COUNT(*) AS total,
				        SUM(CASE WHEN severity IN ('error','critical') THEN 1 ELSE 0 END) AS errors,
				        SUM(CASE WHEN is_anomaly = 1 THEN 1 ELSE 0 END) AS anomalies
				 FROM %i
				 WHERE created_at >= %s
				 GROUP BY blog_id
				 ORDER BY total DESC",
			array(
				self::table(),
				self::cutoff( $days, 'DAY' ),
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Fetch a single row by id.
	 */
	public static function get_row( $id ) {
		$row = self::cached_get_row(
			'SELECT * FROM %i WHERE id = %d',
			array(
				self::table(),
				(int) $id,
			),
			ARRAY_A
		);
		return is_array( $row ) ? $row : null;
	}

	/**
	 * Comments table name.
	 */
	public static function comments_table() {
		global $wpdb;
		$prefix = isset( $wpdb->base_prefix ) ? $wpdb->base_prefix : $wpdb->prefix;
		return $prefix . 'apwpm_comments';
	}

	/**
	 * Saved views table name.
	 */
	public static function views_table() {
		global $wpdb;
		$prefix = isset( $wpdb->base_prefix ) ? $wpdb->base_prefix : $wpdb->prefix;
		return $prefix . 'apwpm_views';
	}

	/**
	 * Update fields on a log row.
	 *
	 * @param int   $log_id Log ID.
	 * @param array $data   Column data.
	 * @param array $format $wpdb formats.
	 * @return int|false
	 */
	public static function update_log_fields( $log_id, array $data, array $format ) {
		return self::run_update(
			self::table(),
			$data,
			array( 'id' => (int) $log_id ),
			$format,
			array( '%d' )
		);
	}

	/**
	 * Delete a single log row.
	 *
	 * @param int $log_id Log ID.
	 * @return int|false
	 */
	public static function delete_log_by_id( $log_id ) {
		return self::run_delete( self::table(), array( 'id' => (int) $log_id ), array( '%d' ) );
	}

	/**
	 * Generic insert helper.
	 *
	 * @param string $table  Table name.
	 * @param array  $row    Row data.
	 * @param array  $format Formats.
	 * @return int|false
	 */
	public static function insert_into( $table, array $row, array $format ) {
		return self::run_insert( $table, $row, $format );
	}

	/**
	 * Generic delete helper.
	 *
	 * @param string $table        Table name.
	 * @param array  $where        WHERE args.
	 * @param array  $where_format Formats.
	 * @return int|false
	 */
	public static function delete_from( $table, array $where, array $where_format ) {
		return self::run_delete( $table, $where, $where_format );
	}

	/**
	 * Last insert ID.
	 */
	public static function insert_id() {
		global $wpdb;
		return (int) $wpdb->insert_id;
	}

	/**
	 * Saved views for a user.
	 */
	public static function get_views_for_user( $user_id ) {
		return self::cached_get_results(
			'SELECT * FROM %i WHERE user_id = %d ORDER BY created_at DESC',
			array(
				self::views_table(),
				(int) $user_id,
			)
		);
	}

	/**
	 * Comments for a log entry.
	 */
	public static function get_comments_for_log( $log_id ) {
		return self::cached_get_results(
			'SELECT * FROM %i WHERE log_id = %d ORDER BY id ASC',
			array(
				self::comments_table(),
				(int) $log_id,
			)
		);
	}

	/**
	 * Comment counts keyed by log ID.
	 *
	 * @param array $log_ids Log IDs.
	 * @return array<int,int>
	 */
	public static function comment_counts_for_logs( array $log_ids ) {
		$log_ids = array_filter( array_map( 'intval', $log_ids ) );
		if ( empty( $log_ids ) ) {
			return array();
		}
		$rows = self::cached_get_results(
			'SELECT log_id, COUNT(*) AS c FROM %i GROUP BY log_id',
			array(
				self::comments_table(),
			)
		);
		$wanted = array_flip( $log_ids );
		$out    = array();
		foreach ( (array) $rows as $r ) {
			$lid = (int) $r->log_id;
			if ( isset( $wanted[ $lid ] ) ) {
				$out[ $lid ] = (int) $r->c;
			}
		}
		return $out;
	}

	/**
	 * Recent anomaly rows for the dashboard widget.
	 */
	public static function get_recent_anomalies( $limit = 5 ) {
		$limit = max( 1, min( 50, (int) $limit ) );
		$rows  = self::cached_get_results(
			"SELECT id, action_type, object_title, description, severity, created_at, context
				 FROM %i
				 WHERE is_anomaly = 1 OR action_type IN ('insight_anomaly','insight_spike','break_detected')
				 ORDER BY created_at DESC
				 LIMIT %d",
			array(
				self::table(),
				$limit,
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Mark a log row as anomalous.
	 */
	public static function mark_log_anomaly( $log_id ) {
		return self::update_log_fields( (int) $log_id, array( 'is_anomaly' => 1 ), array( '%d' ) );
	}

	/**
	 * Suspect rows for break detection.
	 */
	public static function get_break_suspects( $window_minutes ) {
		$window = max( 1, (int) $window_minutes );
		$rows   = self::cached_get_results(
			"SELECT id, action_type, object_type, object_title, user_login, created_at
				 FROM %i
				 WHERE created_at >= %s
				 AND action_type IN ('plugin_activated','plugin_deactivated','plugin_upgraded','theme_switched','theme_upgraded','option_updated','settings_page_saved','user_role_changed')
				 ORDER BY created_at DESC
				 LIMIT 10",
			array(
				self::table(),
				self::cutoff( $window, 'MINUTE' ),
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Total log count (cached briefly for Site Health).
	 */
	public static function count_all_logs() {
		$cache_key = 'apwpm_total_logs';
		$cached    = wp_cache_get( $cache_key, 'apwpm' );
		if ( false !== $cached ) {
			return (int) $cached;
		}
		$count = (int) self::cached_get_var(
			'SELECT COUNT(*) FROM %i',
			array(
				self::table(),
			)
		);
		wp_cache_set( $cache_key, $count, 'apwpm', MINUTE_IN_SECONDS );
		return $count;
	}

	/**
	 * Export batch for WP-CLI.
	 */
	public static function get_logs_export_batch( $per, $offset ) {
		return self::cached_get_results(
			'SELECT * FROM %i ORDER BY id ASC LIMIT %d OFFSET %d',
			array(
				self::table(),
				(int) $per,
				(int) $offset,
			)
		);
	}

	/**
	 * GDPR exporter batch.
	 */
	public static function get_user_log_export_batch( $user_id, $per, $offset ) {
		return self::cached_get_results(
			'SELECT id, action_type, object_type, object_title, description, severity, created_at
				 FROM %i WHERE user_id = %d ORDER BY id ASC LIMIT %d OFFSET %d',
			array(
				self::table(),
				(int) $user_id,
				(int) $per,
				(int) $offset,
			),
			ARRAY_A
		);
	}

	/**
	 * Redact PII fields for a user (GDPR eraser).
	 */
	public static function redact_user_logs( $user_id, $user_login ) {
		return self::run_write_query(
			"UPDATE %i
				 SET user_login = '', description = REPLACE(description, %s, '<erased>'), ip_address = '', user_agent = ''
				 WHERE user_id = %d",
			array(
				self::table(),
				$user_login,
				(int) $user_id,
			)
		);
	}

	/**
	 * Verify hash-chain rows.
	 */
	public static function get_logs_for_verify( $limit ) {
		$limit = max( 1, (int) $limit );
		return self::cached_get_results(
			'SELECT * FROM %i ORDER BY id ASC LIMIT %d',
			array(
				self::table(),
				$limit,
			),
			ARRAY_A
		);
	}

	/**
	 * Rollback helper: fetch row as object.
	 */
	public static function get_log_object( $log_id ) {
		return self::cached_get_row(
			'SELECT * FROM %i WHERE id = %d',
			array(
				self::table(),
				(int) $log_id,
			)
		);
	}

	/**
	 * Fetch one log row as an associative array.
	 *
	 * @param int $log_id Log ID.
	 * @return array|null
	 */
	public static function get_log_by_id( $log_id ) {
		$row = self::cached_get_row(
			'SELECT * FROM %i WHERE id = %d',
			array(
				self::table(),
				(int) $log_id,
			),
			ARRAY_A
		);
		return is_array( $row ) ? $row : null;
	}

	/**
	 * Analytics helpers.
	 */
	public static function get_analytics_heatmap( $table, $user_id, $cutoff ) {
		if ( $user_id ) {
			return self::cached_get_results(
				'SELECT DAYOFWEEK(created_at) AS dow, HOUR(created_at) AS hr, COUNT(*) AS c
					 FROM %i WHERE user_id = %d AND created_at >= %s GROUP BY dow, hr',
				array(
					$table,
					$user_id,
					$cutoff,
				),
				ARRAY_A
			);
		}
		return self::cached_get_results(
			'SELECT DAYOFWEEK(created_at) AS dow, HOUR(created_at) AS hr, COUNT(*) AS c
				 FROM %i WHERE created_at >= %s GROUP BY dow, hr',
			array(
				$table,
				$cutoff,
			),
			ARRAY_A
		);
	}

	public static function get_analytics_by_hour( $table, $user_id, $cutoff ) {
		if ( $user_id ) {
			return self::cached_get_results(
				'SELECT HOUR(created_at) AS hr, COUNT(*) AS c
					 FROM %i WHERE user_id = %d AND created_at >= %s GROUP BY hr ORDER BY hr',
				array(
					$table,
					$user_id,
					$cutoff,
				),
				ARRAY_A
			);
		}
		return self::cached_get_results(
			'SELECT HOUR(created_at) AS hr, COUNT(*) AS c
				 FROM %i WHERE created_at >= %s GROUP BY hr ORDER BY hr',
			array(
				$table,
				$cutoff,
			),
			ARRAY_A
		);
	}

	public static function get_analytics_top_users( $table, $cutoff ) {
		return self::cached_get_results(
			'SELECT user_id, user_login, COUNT(*) AS c
				 FROM %i WHERE created_at >= %s AND user_id > 0
				 GROUP BY user_id ORDER BY c DESC LIMIT 10',
			array(
				$table,
				$cutoff,
			),
			ARRAY_A
		);
	}

	public static function count_errors_since( $days ) {
		$days = max( 1, (int) $days );
		return (int) self::cached_get_var(
			"SELECT COUNT(*) FROM %i WHERE severity IN ('error','critical') AND created_at >= %s",
			array(
				self::table(),
				self::cutoff( $days, 'DAY' ),
			)
		);
	}

	public static function count_anomalies_since( $days ) {
		$days = max( 1, (int) $days );
		return (int) self::cached_get_var(
			'SELECT COUNT(*) FROM %i WHERE is_anomaly = 1 AND created_at >= %s',
			array(
				self::table(),
				self::cutoff( $days, 'DAY' ),
			)
		);
	}

	public static function count_since( $amount, $unit ) {
		return (int) self::cached_get_var(
			'SELECT COUNT(*) FROM %i WHERE created_at >= %s',
			array(
				self::table(),
				self::cutoff( (int) $amount, (string) $unit ),
			)
		);
	}

	public static function top_actions_since( $days, $limit = 6 ) {
		$rows = self::cached_get_results(
			'SELECT action_type, COUNT(*) AS total FROM %i
				 WHERE created_at >= %s
				 GROUP BY action_type
				 ORDER BY total DESC
				 LIMIT %d',
			array(
				self::table(),
				self::cutoff( $days, 'DAY' ),
				(int) $limit,
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	public static function delete_logs_by_severity_before( $severity, $days ) {
		return self::run_write_query(
			'DELETE FROM %i WHERE severity = %s AND created_at < %s',
			array(
				self::table(),
				$severity,
				self::cutoff( $days, 'DAY' ),
			)
		);
	}

	/**
	 * Insights queries.
	 */
	public static function get_hourly_action_counts() {
		return self::cached_get_results(
			'SELECT action_type, COUNT(*) AS c
				 FROM %i
				 WHERE created_at >= %s
				 GROUP BY action_type',
			array(
				self::table(),
				self::cutoff( 1, 'HOUR' ),
			),
			ARRAY_A
		);
	}

	public static function get_action_hourly_series( $action_type ) {
		return self::cached_get_col(
			"SELECT COUNT(*) FROM %i
				 WHERE action_type = %s
				 AND created_at >= %s
				 AND created_at < %s
				 GROUP BY DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H')",
			array(
				self::table(),
				$action_type,
				self::cutoff( 14, 'DAY' ),
				self::cutoff( 1, 'HOUR' ),
			)
		);
	}

	public static function count_failed_logins_by_ip( $ip, $minutes ) {
		return (int) self::cached_get_var(
			"SELECT COUNT(*) FROM %i
				 WHERE action_type = 'user_login_failed'
				 AND ip_address = %s
				 AND created_at >= %s",
			array(
				self::table(),
				$ip,
				self::cutoff( $minutes, 'MINUTE' ),
			)
		);
	}

	public static function count_user_actions( $user_id, $action_type, $minutes ) {
		return (int) self::cached_get_var(
			'SELECT COUNT(*) FROM %i
				 WHERE user_id = %d
				 AND action_type = %s
				 AND created_at >= %s',
			array(
				self::table(),
				(int) $user_id,
				(string) $action_type,
				self::cutoff( $minutes, 'MINUTE' ),
			)
		);
	}

	public static function get_user_activity_hours( $user_id ) {
		return self::cached_get_col(
			'SELECT HOUR(created_at) FROM %i
				 WHERE user_id = %d
				 AND created_at >= %s
				 AND created_at < %s',
			array(
				self::table(),
				(int) $user_id,
				self::cutoff( 30, 'DAY' ),
				self::cutoff( 1, 'HOUR' ),
			)
		);
	}
}