<?php
defined( 'ABSPATH' ) || exit;

final class EDRAD_Database {
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'edrad_events';
	}

	public static function capsule_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'edrad_capsules';
	}

	/**
	 * Check the two plugin-owned tables, scoped to the current blog prefix.
	 * Avoid probing tables during WordPress installation/runtime test setup.
	 * Direct queries are necessary to inspect plugin-owned schema metadata.
	 */
	public static function schema_exists() {
		global $wpdb;
		if ( function_exists( 'wp_installing' ) && wp_installing() ) {
			return false;
		}
		$events = self::table_name();
		$capsules = self::capsule_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time schema validation against plugin-owned tables; schema metadata must not be cached across installation/recovery.
		$events_found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $events ) ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time schema validation against plugin-owned tables; schema metadata must not be cached across installation/recovery.
		$capsules_found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $capsules ) ) );
		return $events_found === $events && $capsules_found === $capsules;
	}

	/**
	 * Do not write during site creation before Radar has installed its tables.
	 * Site options are blog-scoped, so this also prevents writes into freshly
	 * switched network blogs and temporary Plugin Check databases.
	 */
	public static function storage_ready() {
		if ( function_exists( 'wp_installing' ) && wp_installing() ) {
			return false;
		}
		return get_option( 'edrad_version' ) === EDRAD_VERSION;
	}

	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$table   = self::table_name();
		$charset = $wpdb->get_charset_collate();
		$sql     = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			created_at datetime NOT NULL,
			last_seen_at datetime NULL,
			event_type varchar(64) NOT NULL,
			severity tinyint(3) unsigned NOT NULL DEFAULT 1,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			summary varchar(255) NOT NULL,
			context longtext NULL,
			fingerprint_hash char(64) NOT NULL DEFAULT '',
			flood_hash char(64) NOT NULL DEFAULT '',
			occurrence_count int(10) unsigned NOT NULL DEFAULT 1,
			disposition varchar(24) NOT NULL DEFAULT 'recorded',
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY event_type (event_type),
			KEY severity (severity),
			KEY flood_hash (flood_hash),
			KEY disposition (disposition)
		) {$charset};";
		dbDelta( $sql );
		$capsules = self::capsule_table_name();
		$sql_capsules = "CREATE TABLE {$capsules} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			created_at datetime NOT NULL,
			trigger_event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			incident_type varchar(64) NOT NULL,
			component varchar(191) NOT NULL DEFAULT '',
			evidence_level varchar(16) NOT NULL DEFAULT 'LOW',
			event_count smallint(5) unsigned NOT NULL DEFAULT 0,
			seal_hash char(64) NOT NULL,
			payload longtext NOT NULL,
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY incident_type (incident_type),
			KEY component (component)
		) {$charset};";
		dbDelta( $sql_capsules );
	}

	public static function insert_event( array $event ) {
		if ( ! self::storage_ready() ) {
			return 0;
		}
		global $wpdb;
		$defaults = array(
			'event_type'      => 'system_change',
			'severity'        => 1,
			'user_id'         => get_current_user_id(),
			'summary'         => '',
			'context'         => array(),
			'fingerprint_hash'=> '',
		);
		$event = wp_parse_args( $event, $defaults );
		$event['event_type'] = sanitize_key( $event['event_type'] );
		$event['severity']   = min( 5, max( 1, absint( $event['severity'] ) ) );
		$event['context']    = is_array( $event['context'] ) ? $event['context'] : array();
		$family              = self::event_family( $event['event_type'] );
		$protected           = 5 === $event['severity'] || in_array( $event['event_type'], array( 'fatal_error', 'unattributed_drift' ), true );
		if ( ! $protected && ! self::family_enabled( $family ) ) {
			return 0;
		}
		$matched_pattern = $protected ? '' : self::matching_suppression_pattern( $event );
		$disposition     = $matched_pattern ? 'suppressed_noise' : 'recorded';
		$event['context']['signal_policy'] = array(
			'family' => $family, 'disposition' => $disposition, 'protected' => $protected,
			'matched_pattern' => $matched_pattern,
		);
		$component  = sanitize_text_field( $event['context']['component'] ?? '' );
		$message_key = sanitize_text_field( $event['context']['message_hash'] ?? '' );
		$flood_hash = hash( 'sha256', $event['event_type'] . '|' . sanitize_text_field( $event['summary'] ) . '|' . $component . '|' . $message_key . '|' . $disposition );
		$now        = current_time( 'mysql', true );
		if ( ! $protected && EDRAD_Settings::get( 'flood_guard' ) ) {
			$cutoff = gmdate( 'Y-m-d H:i:s', time() - absint( EDRAD_Settings::get( 'flood_window' ) ) );
			$existing = $wpdb->get_row( $wpdb->prepare( 'SELECT id, occurrence_count FROM %i WHERE flood_hash = %s AND last_seen_at >= %s ORDER BY id DESC LIMIT 1', self::table_name(), $flood_hash, $cutoff ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
			if ( is_array( $existing ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
				$wpdb->update( self::table_name(), array( 'last_seen_at' => $now, 'occurrence_count' => absint( $existing['occurrence_count'] ) + 1 ), array( 'id' => absint( $existing['id'] ) ), array( '%s', '%d' ), array( '%d' ) );
				return absint( $existing['id'] );
			}
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries.
		$inserted = $wpdb->insert(
			self::table_name(),
			array(
				'created_at'       => $now,
				'last_seen_at'     => $now,
				'event_type'       => $event['event_type'],
				'severity'         => $event['severity'],
				'user_id'          => absint( $event['user_id'] ),
				'summary'          => sanitize_text_field( $event['summary'] ),
				'context'          => wp_json_encode( $event['context'], JSON_UNESCAPED_SLASHES ),
				'fingerprint_hash' => sanitize_text_field( $event['fingerprint_hash'] ),
				'flood_hash'       => $flood_hash,
				'occurrence_count' => 1,
				'disposition'      => $disposition,
			),
			array( '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%d', '%s' )
		);
		return false === $inserted ? 0 : (int) $wpdb->insert_id;
	}

	private static function event_family( $type ) {
		if ( in_array( $type, array( 'update_completed', 'plugin_activated', 'plugin_deactivated', 'theme_switched' ), true ) ) { return 'deployment'; }
		if ( in_array( $type, array( 'user_created', 'user_deleted', 'user_role_changed' ), true ) ) { return 'identity'; }
		if ( in_array( $type, array( 'radar_settings_changed', 'compatibility_guard_applied' ), true ) ) { return 'configuration'; }
		if ( 'unattributed_drift' === $type ) { return 'drift'; }
		if ( 'fatal_error' === $type ) { return 'runtime'; }
		return 'system';
	}

	private static function family_enabled( $family ) {
		$key = array( 'deployment' => 'sensor_deployment', 'identity' => 'sensor_identity', 'configuration' => 'sensor_configuration', 'drift' => 'sensor_drift' );
		return ! isset( $key[ $family ] ) || (bool) EDRAD_Settings::get( $key[ $family ] );
	}

	private static function matching_suppression_pattern( array $event ) {
		$patterns = preg_split( '/\R/', (string) EDRAD_Settings::get( 'suppression_patterns' ) );
		$context  = $event['context'];
		$targets  = array( $event['event_type'], (string) ( $context['component'] ?? '' ) );
		foreach ( array_keys( (array) ( $context['settings'] ?? array() ) ) as $key ) { $targets[] = 'option:' . $key; }
		foreach ( is_array( $patterns ) ? $patterns : array() as $pattern ) {
			$pattern = trim( $pattern );
			if ( '' === $pattern ) { continue; }
			$regex = '/^' . str_replace( '\\*', '.*', preg_quote( $pattern, '/' ) ) . '$/i';
			foreach ( $targets as $target ) { if ( preg_match( $regex, (string) $target ) ) { return $pattern; } }
		}
		return '';
	}

	public static function telemetry() {
		global $wpdb;
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS rows_total, COALESCE(SUM(occurrence_count),0) AS signals_total, COALESCE(SUM(CASE WHEN disposition='suppressed_noise' THEN occurrence_count ELSE 0 END),0) AS suppressed_total, COALESCE(SUM(CASE WHEN occurrence_count > 1 THEN occurrence_count - 1 ELSE 0 END),0) AS condensed_total FROM %i", self::table_name() ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
		return array_map( 'absint', is_array( $row ) ? $row : array() );
	}

	public static function insert_capsule( array $capsule ) {
		if ( ! self::storage_ready() ) {
			return 0;
		}
		global $wpdb;
		$payload = wp_json_encode( $capsule['payload'], JSON_UNESCAPED_SLASHES );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries.
		$wpdb->insert( self::capsule_table_name(), array(
			'created_at' => current_time( 'mysql', true ),
			'trigger_event_id' => absint( $capsule['trigger_event_id'] ),
			'incident_type' => sanitize_key( $capsule['incident_type'] ),
			'component' => sanitize_text_field( $capsule['component'] ),
			'evidence_level' => sanitize_key( $capsule['evidence_level'] ),
			'event_count' => absint( $capsule['event_count'] ),
			'seal_hash' => hash( 'sha256', $payload ),
			'payload' => $payload,
		), array( '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s' ) );
		return (int) $wpdb->insert_id;
	}

	public static function recent_capsules( $limit = 10 ) {
		global $wpdb;
		$limit = min( 100, max( 1, absint( $limit ) ) );
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d', self::capsule_table_name(), $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
	}

	public static function get_capsule( $id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::capsule_table_name(), absint( $id ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
	}

	public static function capsule_is_valid( array $capsule ) {
		return ! empty( $capsule['payload'] ) && ! empty( $capsule['seal_hash'] ) && hash_equals( $capsule['seal_hash'], hash( 'sha256', $capsule['payload'] ) );
	}

	public static function recent_events( $limit = 100 ) {
		global $wpdb;
		$limit = min( 500, max( 1, absint( $limit ) ) );
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d', self::table_name(), $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
	}

	public static function filtered_events( array $filters, $limit = 200 ) {
		global $wpdb;
		// Table identifier is a trusted plugin-owned name, passed as a prepared %i placeholder.
		$sql    = 'SELECT * FROM %i WHERE 1=1';
		$params = array( self::table_name() );
		$family = sanitize_key( $filters['family'] ?? 'all' );
		$families = array(
			'changes'  => array( 'update_completed', 'plugin_activated', 'plugin_deactivated', 'theme_switched', 'radar_settings_changed' ),
			'faults'   => array( 'fatal_error', 'unattributed_drift' ),
			'identity' => array( 'user_created', 'user_deleted', 'user_role_changed' ),
			'noops'    => array( 'update_completed' ),
		);
		if ( 'suppressed' === $family ) {
			$sql .= " AND disposition = 'suppressed_noise'";
		}
		if ( isset( $families[ $family ] ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $families[ $family ] ), '%s' ) );
			$sql .= " AND event_type IN ({$placeholders})";
			$params = array_merge( $params, $families[ $family ] );
		}
		$severity = absint( $filters['severity'] ?? 0 );
		if ( $severity >= 1 && $severity <= 5 ) {
			$sql .= ' AND severity = %d';
			$params[] = $severity;
		}
		$hours = absint( $filters['hours'] ?? 0 );
		if ( $hours ) {
			$sql .= ' AND created_at >= %s';
			$params[] = gmdate( 'Y-m-d H:i:s', time() - min( 8760, $hours ) * HOUR_IN_SECONDS );
		}
		$search = sanitize_text_field( $filters['search'] ?? '' );
		if ( '' !== $search ) {
			$like = '%' . $wpdb->esc_like( $search ) . '%';
			$sql .= ' AND (event_type LIKE %s OR summary LIKE %s OR context LIKE %s)';
			$params[] = $like; $params[] = $like; $params[] = $like;
		}
		$sql .= ' ORDER BY id DESC LIMIT %d';
		$params[] = min( 500, max( 1, absint( $limit ) ) );
		$events = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used. Reviewed fixed WHERE fragments; %i identifier and all variable values are prepared with typed placeholders before execution.
		if ( 'noops' === $family ) {
			$events = array_values( array_filter( $events, array( 'EDRAD_Analyzer', 'is_noop' ) ) );
		}
		return $events;
	}

	public static function incident_count( $minutes = 30 ) {
		global $wpdb;
		$since = gmdate( 'Y-m-d H:i:s', time() - absint( $minutes ) * MINUTE_IN_SECONDS );
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s AND severity >= 2', self::table_name(), $since ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
	}

	public static function purge_old_events( $days ) {
		global $wpdb;
		$before = gmdate( 'Y-m-d H:i:s', time() - absint( $days ) * DAY_IN_SECONDS );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', self::table_name(), $before ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', self::capsule_table_name(), $before ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned forensic tables have no higher-level WordPress object API; values use typed wpdb operations/prepared queries. Live forensic state must reflect current captures and flood counts; no persistent result cache is used.
	}
}