<?php

declare(strict_types=1);

namespace SiteFort\Modules\Scanner\Repository;

use SiteFort\Core\Database\SchemaInspector;
use SiteFort\Core\Installer;

// If this file is called directly, abort.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Persists scanner file state rows.
 */
class FileStateRepository {
	public const SKIP_REASON_DEEP_ANALYSIS_CREDIT_EXHAUSTED = 'deep_analysis_credit_exhausted';

	/**
	 * Guard to avoid repeated index checks in the same request.
	 *
	 * @var bool
	 */
	private static $indexes_checked = false;

	/**
	 * Set once the table has been reconciled in this request, so hot-path
	 * instantiations skip the schema sweep.
	 *
	 * @var bool
	 */
	private static $schema_synced = false;

	/**
	 * WordPress database object.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table_name;

	public function __construct( bool $ensure_schema = true ) {
		global $wpdb;
		$this->wpdb       = $wpdb;
		$this->table_name = $wpdb->prefix . 'sitefort_file_states';
		if ( $ensure_schema ) {
			$this->maybe_upgrade_table();
		}
	}

	public static function ensure_schema(): void {
		global $wpdb;

		Installer::ensure_file_states_table_schema();

		// If provisioning could not create the table (restrictive DB, unsupported
		// index length, etc.), running ALTERs against a missing table only masks the
		// real CREATE error. Installer already logged the underlying failure.
		if ( ! SchemaInspector::table_exists( $wpdb->prefix . 'sitefort_file_states' ) ) {
			return;
		}

		$repository = new self();
		$repository->maybe_upgrade_table();
	}

	public static function is_schema_current(): bool {
		global $wpdb;

		$table = $wpdb->prefix . 'sitefort_file_states';
		if ( ! SchemaInspector::table_engine_ready( $table ) ) {
			return false;
		}

		if ( ! SchemaInspector::has_columns(
			$table,
			[ 'file_path_hash', 'component_slug', 'component_version', 'component_locale', 'file_mtime_at_hash', 'skip_reason', 'available_at' ]
		) ) {
			return false;
		}

		return SchemaInspector::column_uses_collation( $table, 'file_path_hash', 'ascii_bin' )
			&& self::scan_file_unique_index_current( $table );
	}

	private static function scan_file_unique_index_current( string $table ): bool {
		global $wpdb;

		try {
			$table_identifier = SchemaInspector::table_identifier( $table );
		} catch ( \InvalidArgumentException $e ) {
			return false;
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Checks the exact shape of a plugin-owned migration index.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SHOW INDEX FROM {$table_identifier} WHERE Key_name = %s",
				'uk_scan_file'
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return false;
		}

		usort(
			$rows,
			static function ( array $a, array $b ): int {
				return (int) ( $a['Seq_in_index'] ?? 0 ) <=> (int) ( $b['Seq_in_index'] ?? 0 );
			}
		);

		$columns = array_map(
			static function ( array $row ): string {
				return (string) ( $row['Column_name'] ?? '' );
			},
			$rows
		);

		return $columns === [ 'scan_id', 'file_path_hash' ];
	}

	/**
	 * Add any columns missing from older installs.
	 *
	 * dbDelta only creates new tables; existing tables need ALTER TABLE.
	 */
	private function maybe_upgrade_table(): void {
		if ( self::$schema_synced ) {
			return;
		}

		// Schema map already current; skip the column/index sweep.
		if ( \SiteFort\Core\Database\MigrationManager::is_module_current( 'scanner_file_state' ) ) {
			self::$schema_synced = true;
			return;
		}

		if ( ! SchemaInspector::table_exists( $this->table_name ) ) {
			return;
		}

		SchemaInspector::ensure_table_uses_innodb( $this->table_name );

		$columns = [
			'file_path_hash'       => "ALTER TABLE {$this->table_identifier()} ADD COLUMN file_path_hash char(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '' COMMENT 'SHA-256 of relative path for portable uniqueness' AFTER file_path",
			'component_slug'       => "ALTER TABLE {$this->table_identifier()} ADD COLUMN component_slug varchar(191) DEFAULT NULL COMMENT 'Plugin/theme/core slug captured with the file hash' AFTER file_type",
			'component_version'    => "ALTER TABLE {$this->table_identifier()} ADD COLUMN component_version varchar(100) DEFAULT NULL COMMENT 'Component version captured with the file hash' AFTER component_slug",
			'component_locale'     => "ALTER TABLE {$this->table_identifier()} ADD COLUMN component_locale varchar(20) DEFAULT NULL COMMENT 'Core locale captured with the file hash' AFTER component_version",
			'file_mtime_at_hash'   => "ALTER TABLE {$this->table_identifier()} ADD COLUMN file_mtime_at_hash int(10) UNSIGNED DEFAULT NULL COMMENT 'File modification time captured with the file hash' AFTER component_locale",
			'available_at'         => "ALTER TABLE {$this->table_identifier()} ADD COLUMN available_at int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Earliest time a deferred file-state row may be processed' AFTER updated_at",
		];

		foreach ( $columns as $column => $statement ) {
			$row = $this->get_results_query(
				"SHOW COLUMNS FROM {$this->table_identifier()} LIKE '" . esc_sql( $column ) . "'"
			);
			if ( $row === null ) {
				sitefort_log( '[SiteFort] DB error checking ' . $column . ' column: ' . $this->wpdb->last_error );
				continue;
			}
			if ( empty( $row ) ) {
				$result = $this->query_prepared( $statement );
				if ( $result === false ) {
					sitefort_log( '[SiteFort] DB error adding file_states column ' . $column . ': ' . $this->wpdb->last_error );
				}
			}
		}

		$row = $this->get_results_query(
			"SHOW COLUMNS FROM {$this->table_identifier()} LIKE 'skip_reason'"
		);
		if ( $row === null ) {
			sitefort_log( '[SiteFort] DB error checking skip_reason column: ' . $this->wpdb->last_error );
			return;
		}
		if ( empty( $row ) ) {
			$result = $this->query_prepared(
				"ALTER TABLE {$this->table_identifier()}
				 ADD COLUMN skip_reason varchar(50) DEFAULT NULL
				 AFTER scan_result"
			);
			if ( $result === false ) {
				sitefort_log( '[SiteFort] DB error adding skip_reason column: ' . $this->wpdb->last_error );
			}
		}

		$this->ensure_file_path_hash_ascii();
		$this->realign_join_column_charsets();
		$this->backfill_file_path_hashes();
		$this->ensure_scan_file_unique_index();
		SchemaInspector::ensure_table_uses_innodb( $this->table_name );
		$this->ensure_hot_path_indexes();

		self::$schema_synced = true;
	}

	/**
	 * The unique index needs file_path_hash on a fixed-width ascii_bin column so it
	 * stays small on every server. Correct it if it was created any other way.
	 */
	private function ensure_file_path_hash_ascii(): void {
		if ( ! $this->column_exists( 'file_path_hash' ) ) {
			return;
		}
		if ( SchemaInspector::column_uses_collation( $this->table_name, 'file_path_hash', 'ascii_bin' ) ) {
			return;
		}

		$result = $this->query_prepared(
			"ALTER TABLE {$this->table_identifier()} MODIFY file_path_hash char(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT ''"
		);
		if ( false === $result ) {
			sitefort_log( '[SiteFort] DB error normalizing file_states file_path_hash charset: ' . $this->wpdb->last_error );
		}
	}

	/**
	 * Align join columns to the table charset so cross-table joins never mix charsets.
	 *
	 * scan_id and cloud_job_id join to scan_queue.job_id and cloud_scan_jobs.job_id.
	 * Only a column whose charset differs from the table is rewritten, so a healthy
	 * utf8mb4 install is left untouched.
	 */
	private function realign_join_column_charsets(): void {
		$table_collation = (string) $this->get_var_prepared(
			"SELECT TABLE_COLLATION FROM information_schema.tables
			 WHERE table_schema = DATABASE() AND table_name = %s
			 LIMIT 1",
			$this->table_name
		);
		if ( '' === $table_collation || ! preg_match( '/^[a-z0-9_]+$/', $table_collation ) ) {
			return;
		}

		$charset = explode( '_', $table_collation )[0];
		if ( '' === $charset ) {
			return;
		}

		$columns = [
			'scan_id'      => 'varchar(64) NOT NULL',
			'cloud_job_id' => 'varchar(64) DEFAULT NULL',
			'file_hash'    => 'varchar(64) DEFAULT NULL',
		];

		foreach ( $columns as $column => $tail ) {
			$info = SchemaInspector::get_column( $this->table_name, $column );
			$column_collation = is_array( $info ) ? (string) ( $info['Collation'] ?? '' ) : '';
			if ( '' === $column_collation || explode( '_', $column_collation )[0] === $charset ) {
				continue;
			}

			$sql = "ALTER TABLE {$this->table_identifier()} MODIFY {$column} varchar(64) CHARACTER SET {$charset} COLLATE {$table_collation} " . $tail;
			if ( false === $this->query_prepared( $sql ) ) {
				sitefort_log( '[SiteFort] DB error realigning file_states ' . $column . ' charset: ' . $this->wpdb->last_error );
			}
		}
	}

	private function column_exists( string $column ): bool {
		$row = $this->get_results_prepared(
			"SHOW COLUMNS FROM {$this->table_identifier()} LIKE %s",
			$column
		);

		if ( $row === null ) {
			sitefort_log( '[SiteFort] DB error checking file_states column ' . $column . ': ' . $this->wpdb->last_error );
			return false;
		}

		return ! empty( $row );
	}

	private function backfill_file_path_hashes(): void {
		if ( ! $this->column_exists( 'file_path_hash' ) ) {
			return;
		}

		$batch_size = 500;

		do {
			$rows = $this->get_results_prepared(
				"SELECT id, file_path
				 FROM {$this->table_identifier()}
				 WHERE file_path_hash = '' OR file_path_hash IS NULL
				 ORDER BY id ASC
				 LIMIT %d",
				$batch_size
			);

			if ( $rows === null ) {
				sitefort_log( '[SiteFort] DB error loading file_states rows for file_path_hash backfill: ' . $this->wpdb->last_error );
				return;
			}

			if ( empty( $rows ) ) {
				return;
			}

			$updated = 0;
			foreach ( $rows as $row ) {
				$id = (int) ( $row['id'] ?? 0 );
				if ( $id <= 0 ) {
					continue;
				}

				$result = $this->wpdb->update(
					$this->table_name,
					[ 'file_path_hash' => self::file_path_hash( (string) ( $row['file_path'] ?? '' ) ) ],
					[ 'id' => $id ],
					[ '%s' ],
					[ '%d' ]
				);

				if ( false === $result ) {
					sitefort_log( '[SiteFort] DB error backfilling file_states file_path_hash row ' . $id . ': ' . $this->wpdb->last_error );
					continue;
				}

				$updated++;
			}

			if ( 0 === $updated ) {
				return;
			}
		} while ( count( $rows ) >= $batch_size );
	}

	private function ensure_scan_file_unique_index(): void {
		if ( ! $this->column_exists( 'file_path_hash' ) ) {
			return;
		}

		$rows = $this->get_results_prepared(
			"SHOW INDEX FROM {$this->table_identifier()} WHERE Key_name = %s",
			'uk_scan_file'
		);

		if ( $rows === null ) {
			sitefort_log( '[SiteFort] DB error checking file_states uk_scan_file index: ' . $this->wpdb->last_error );
			return;
		}

		usort(
			$rows,
			static function ( array $a, array $b ): int {
				return (int) ( $a['Seq_in_index'] ?? 0 ) <=> (int) ( $b['Seq_in_index'] ?? 0 );
			}
		);

		$columns = array_map(
			static function ( array $row ): string {
				$column = (string) ( $row['Column_name'] ?? '' );
				$prefix = isset( $row['Sub_part'] ) && null !== $row['Sub_part'] ? '(' . (int) $row['Sub_part'] . ')' : '';
				return $column . $prefix;
			},
			$rows
		);

		if ( $columns === [ 'scan_id', 'file_path_hash' ] ) {
			return;
		}

		// Never drop the working index until the replacement is guaranteed to apply.
		// Unhashed rows (an interrupted backfill) would collide on (scan_id, '') and
		// leave the table with no uniqueness guard; retry on the next migration pass.
		$unhashed = (int) $this->get_var_prepared(
			"SELECT COUNT(*) FROM {$this->table_identifier()} WHERE file_path_hash = '' OR file_path_hash IS NULL"
		);
		if ( $unhashed > 0 ) {
			sitefort_log( '[SiteFort] Deferring file_states uk_scan_file rekey: ' . $unhashed . ' rows still awaiting file_path_hash backfill.' );
			return;
		}

		if ( ! empty( $rows ) && false === $this->query_prepared( "ALTER TABLE {$this->table_identifier()} DROP INDEX uk_scan_file" ) ) {
			sitefort_log( '[SiteFort] DB error dropping legacy file_states uk_scan_file index: ' . $this->wpdb->last_error );
			return;
		}

		if ( false === $this->query_prepared( "ALTER TABLE {$this->table_identifier()} ADD UNIQUE KEY uk_scan_file (scan_id, file_path_hash)" ) ) {
			sitefort_log( '[SiteFort] DB error adding portable file_states uk_scan_file index: ' . $this->wpdb->last_error );
		}
	}

	/**
	 * Ensure composite indexes exist for the hottest queue reads.
	 *
	 * @return void
	 */
	private function ensure_hot_path_indexes(): void {
		if ( self::$indexes_checked ) {
			return;
		}

		self::$indexes_checked = true;

		$required_indexes = [
			'idx_scan_state_created'       => 'ALTER TABLE %s ADD INDEX idx_scan_state_created (scan_id, state, created_at)',
			'idx_scan_state_available'     => 'ALTER TABLE %s ADD INDEX idx_scan_state_available (scan_id, state, available_at, created_at)',
			'idx_scan_state_manifest_wait' => 'ALTER TABLE %s ADD INDEX idx_scan_state_manifest_wait (scan_id, state, error_code, available_at)',
			'idx_scan_state_upload_ready'  => 'ALTER TABLE %s ADD INDEX idx_scan_state_upload_ready (scan_id, state, upload_attempt, updated_at, created_at)',
			'idx_scan_created'             => 'ALTER TABLE %s ADD INDEX idx_scan_created (scan_id, created_at)',
			'idx_scan_skip_reason'         => 'ALTER TABLE %s ADD INDEX idx_scan_skip_reason (scan_id, skip_reason)',
		];

		foreach ( $required_indexes as $index_name => $statement_template ) {
			$index = $this->get_var_prepared(
				"SELECT INDEX_NAME
				FROM information_schema.statistics
				WHERE table_schema = DATABASE()
				AND table_name = %s
				AND index_name = %s
				LIMIT 1",
				$this->table_name,
				$index_name
			);

			if ( $index ) {
				continue;
			}

			$statement = sprintf( $statement_template, $this->table_identifier() );
			if ( false === $this->query_prepared( $statement ) ) {
				sitefort_log( '[SiteFort] DB error adding file_states index ' . $index_name . ': ' . $this->wpdb->last_error );
			}
		}
	}

	/**
	 * Create a new file state record.
	 *
	 * @param array $data File data.
	 * @return int|false Inserted ID or false on failure.
	 */
	public function create( array $data ) {
		$defaults = [
			'state'      => 'discovered',
			'created_at' => time(),
			'updated_at' => time(),
		];

		$data = array_merge( $defaults, $data );
		if ( isset( $data['file_path'] ) && ! isset( $data['file_path_hash'] ) ) {
			$data['file_path_hash'] = self::file_path_hash( (string) $data['file_path'] );
		}

		$result = $this->wpdb->insert(
			$this->table_name,
			$data,
			$this->get_format_array( $data )
		);

		return $result ? $this->wpdb->insert_id : false;
	}

	/**
	 * Batch insert file state records.
	 *
	 * @param array $files Array of file data arrays.
	 * @return int Number of records inserted.
	 */
	public function batch_create( array $files ): int {
		if ( empty( $files ) ) {
			return 0;
		}

		$values_sql = [];
		$values     = [];

		$defaults = [
			'state'      => 'discovered',
			'created_at' => time(),
			'updated_at' => time(),
		];

		foreach ( $files as $file ) {
			$file = array_merge( $defaults, $file );
			$file_path = (string) ( $file['file_path'] ?? '' );

			$values_sql[] = '(%s, %s, %s, %s, %s, %d, %d, %d)';
			$values[]     = $file['scan_id'];
			$values[]     = $file_path;
			$values[]     = self::file_path_hash( $file_path );
			$values[]     = $file['state'];
			$values[]     = $file['file_type'];
			$values[]     = $file['file_size'] ?? 0;
			$values[]     = $file['created_at'];
			$values[]     = $file['updated_at'];
		}

		$result = $this->query_prepared(
			"INSERT IGNORE INTO {$this->table_identifier()}
			(scan_id, file_path, file_path_hash, state, file_type, file_size, created_at, updated_at)
			VALUES " . implode( ', ', $values_sql ),
			...$values
		);

		return $result ?: 0;
	}

	/**
	 * Get a file state by ID.
	 *
	 * @param int $id File state ID.
	 * @return array|null File state record or null.
	 */
	public function get_by_id( int $id ): ?array {
		$result = $this->get_row_prepared(
			"SELECT * FROM {$this->table_identifier()} WHERE id = %d",
			$id
		);

		return $result ?: null;
	}

	/**
	 * Update a file state record.
	 *
	 * @param int   $id   File state ID.
	 * @param array $data Data to update.
	 * @return bool Success.
	 */
	public function update( int $id, array $data ): bool {
		$data['updated_at'] = time();
		if ( isset( $data['file_path'] ) && ! isset( $data['file_path_hash'] ) ) {
			$data['file_path_hash'] = self::file_path_hash( (string) $data['file_path'] );
		}

		$result = $this->wpdb->update(
			$this->table_name,
			$data,
			[ 'id' => $id ],
			$this->get_format_array( $data ),
			[ '%d' ]
		);

		return false !== $result;
	}

	/**
	 * Get all file states for a scan.
	 *
	 * @param string $scan_id Scan UUID.
	 * @param int    $limit   Optional hard limit. Zero means no limit.
	 * @return array
	 */
	public function get_by_scan_id( string $scan_id, int $limit = 0 ): array {
		$limit = max( 0, (int) $limit );

		if ( $limit > 0 ) {
			$results = $this->get_results_prepared(
				"SELECT * FROM {$this->table_identifier()}
				WHERE scan_id = %s
				ORDER BY created_at ASC
				LIMIT %d",
				$scan_id,
				$limit
			);
		} else {
			$results = $this->get_results_prepared(
				"SELECT * FROM {$this->table_identifier()}
				WHERE scan_id = %s
				ORDER BY created_at ASC",
				$scan_id
			);
		}

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_by_scan_id: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	/**
	 * Get upload candidates in a starvation-resistant order.
	 *
	 * Order is chosen to avoid retrying the same early file forever:
	 * - lower upload_attempt first
	 * - then least-recently touched
	 *
	 * @param string $scan_id
	 * @param int    $limit
	 * @return array
	 */
	public function get_pending_upload_candidates( string $scan_id, int $limit = 40 ): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s AND state = 'pending_upload'
			ORDER BY upload_attempt ASC, updated_at ASC, created_at ASC
			LIMIT %d",
			$scan_id,
			$limit
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_pending_upload_candidates: ' . $this->wpdb->last_error );
			return [];
		}

		return $results;
	}

	/**
	 * Delete file states by scan ID.
	 *
	 * @param string $scan_id Scan UUID.
	 * @return bool Success.
	 */
	public function delete_by_scan_id( string $scan_id ): bool {
		$result = $this->wpdb->delete(
			$this->table_name,
			[ 'scan_id' => $scan_id ],
			[ '%s' ]
		);

		return false !== $result;
	}

	/**
	 * Count files by scan and state.
	 *
	 * @param string $scan_id Scan UUID.
	 * @param string $state   File state.
	 * @return int File count.
	 */
	public function count_by_scan_and_state( string $scan_id, string $state ): int {
		$count = $this->get_var_prepared(
			"SELECT COUNT(*) FROM {$this->table_identifier()} WHERE scan_id = %s AND state = %s",
			$scan_id,
			$state
		);

		return (int) $count;
	}

	/**
	 * Count files skipped for a specific scan reason.
	 *
	 * @param string $scan_id     Scan UUID.
	 * @param string $skip_reason Skip reason.
	 * @return int File count.
	 */
	public function count_by_scan_and_skip_reason( string $scan_id, string $skip_reason ): int {
		$count = $this->get_var_prepared(
			"SELECT COUNT(*) FROM {$this->table_identifier()} WHERE scan_id = %s AND skip_reason = %s",
			$scan_id,
			$skip_reason
		);

		return (int) $count;
	}

	/**
	 * Aggregate count and size metrics for one scan/state pair.
	 *
	 * @param string $scan_id Scan UUID.
	 * @param string $state   File state.
	 * @return array{count:int,total_size:int,average_size:int}
	 */
	public function get_scan_state_metrics( string $scan_id, string $state ): array {
		$row = $this->get_row_prepared(
			"SELECT
				COUNT(*) AS item_count,
				COALESCE(SUM(file_size), 0) AS total_size,
				COALESCE(AVG(NULLIF(file_size, 0)), 0) AS average_size
			FROM {$this->table_identifier()}
			WHERE scan_id = %s AND state = %s",
			$scan_id,
			$state
		);

		if ( ! is_array( $row ) ) {
			return [
				'count'        => 0,
				'total_size'   => 0,
				'average_size' => 0,
			];
		}

		return [
			'count'        => (int) ( $row['item_count'] ?? 0 ),
			'total_size'   => (int) ( $row['total_size'] ?? 0 ),
			'average_size' => (int) ceil( (float) ( $row['average_size'] ?? 0 ) ),
		];
	}

	/**
	 * Aggregate average file size across multiple states without hydrating rows.
	 *
	 * @param string $scan_id Scan UUID.
	 * @param array  $states  File states.
	 * @return int Average size in bytes.
	 */
	public function get_average_file_size_by_states( string $scan_id, array $states ): int {
		$states = array_values(
			array_filter(
				array_map(
					static fn( $state ): string => sanitize_text_field( (string) $state ),
					$states
				),
				static fn( string $state ): bool => '' !== $state
			)
		);

		if ( empty( $states ) ) {
			return 0;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $states ), '%s' ) );
		$params       = array_merge(
			[ $scan_id ],
			$states
		);

		$average = $this->get_var_prepared(
			"SELECT COALESCE(AVG(NULLIF(file_size, 0)), 0)
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND state IN ({$placeholders})",
			...$params
		);

		return (int) ceil( (float) $average );
	}

	/**
	 * Get format array for wpdb operations based on data types.
	 *
	 * @param array $data Data array.
	 * @return array Format array.
	 */
	private function get_format_array( array $data ): array {
		$formats = [];

		foreach ( $data as $value ) {
			if ( is_int( $value ) ) {
				$formats[] = '%d';
			} elseif ( is_float( $value ) ) {
				$formats[] = '%f';
			} else {
				$formats[] = '%s';
			}
		}

		return $formats;
	}

	private static function file_path_hash( string $file_path ): string {
		return hash( 'sha256', $file_path );
	}

	private function table_identifier(): string {
		if ( $this->table_name === '' || strlen( $this->table_name ) > 64 || ! preg_match( '/^[A-Za-z0-9_]+$/', $this->table_name ) ) {
			throw new \InvalidArgumentException( 'Invalid table identifier.' );
		}

		return '`' . str_replace( '`', '``', $this->table_name ) . '`';
	}

	private function get_results_query( string $sql ): ?array {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository query.
			$results = $this->wpdb->get_results( $sql, ARRAY_A );
		return is_array( $results ) ? $results : null;
	}

	private function get_results_prepared( string $sql, ...$params ): ?array {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			$results = $this->wpdb->get_results( $prepared, ARRAY_A );
		return is_array( $results ) ? $results : null;
	}

	private function get_row_prepared( string $sql, ...$params ): ?array {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			$result = $this->wpdb->get_row( $prepared, ARRAY_A );
		return is_array( $result ) ? $result : null;
	}

	private function get_var_prepared( string $sql, ...$params ) {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			return $this->wpdb->get_var( $prepared );
	}

	private function query_prepared( string $sql, ...$params ) {
		$prepared = empty( $params ) ? $sql : $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return false;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			return $this->wpdb->query( $prepared );
	}

	private function prepare_sql( string $sql, ...$params ): string {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
			$prepared = $this->wpdb->prepare( $sql, ...$params );
		return is_string( $prepared ) ? $prepared : '';
	}

}
