<?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;
}

/**
 * File State Repository - Database Access Layer for file_states table
 *
 * Provides clean CRUD operations for file state records.
 *
 */
class FileStateRepository {

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

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

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

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb       = $wpdb;
		$this->table_name = $wpdb->prefix . 'sitefort_file_states';
		$this->maybe_upgrade_table();
	}

	public static function ensure_schema(): void {
		Installer::ensure_file_states_table_schema();
		$repository = new self();
		$repository->maybe_upgrade_table();
	}

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

		return SchemaInspector::has_columns(
			$wpdb->prefix . 'sitefort_file_states',
			[ 'component_slug', 'component_version', 'component_locale', 'file_mtime_at_hash', 'skip_reason' ]
		);
	}

	/**
	 * Add any columns missing from older installs.
	 *
	 * dbDelta only creates new tables; existing tables need ALTER TABLE.
	 */
	private function maybe_upgrade_table(): void {
		$columns = [
			'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",
		];

		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_hot_path_indexes();
	}

	/**
	 * 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_created'       => 'ALTER TABLE %s ADD INDEX idx_scan_created (scan_id, created_at)',
		];

		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 );

		$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 );

			$values_sql[] = '(%s, %s, %s, %s, %d, %d, %d)';
			$values[]     = $file['scan_id'];
			$values[]     = $file['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, 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;
	}

	/**
	 * Get a file state by scan ID and file path.
	 *
	 * @param string $scan_id   Scan UUID.
	 * @param string $file_path File path.
	 * @return array|null File state record or null.
	 */
	public function get_by_scan_and_path( string $scan_id, string $file_path ): ?array {
		$result = $this->get_row_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s AND file_path = %s",
			$scan_id,
			$file_path
		);

		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();

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

		return false !== $result;
	}

	/**
	 * Get files by scan ID and state.
	 *
	 * @param string $scan_id Scan UUID.
	 * @param string $state   State filter.
	 * @param int    $limit   Limit.
	 * @param int    $offset  Offset.
	 * @return array File state records.
	 */
	public function get_by_scan_and_state( string $scan_id, string $state, int $limit = 50, int $offset = 0 ): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s AND state = %s
			ORDER BY created_at ASC
			LIMIT %d OFFSET %d",
			$scan_id,
			$state,
			$limit,
			$offset
		);

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

		return $results;
	}

	/**
	 * 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 files that appear stalled and should be recovered by watchdog.
	 *
	 * This targets pre-upload pipeline states where a retry is safe.
	 *
	 * @param int $stuck_after Consider files stale after this many seconds.
	 * @param int $limit       Maximum number of rows.
	 * @return array
	 */
	public function get_stuck_files( int $stuck_after = 300, int $limit = 100 ): array {
		$stuck_after = max( 60, (int) $stuck_after );
		$limit       = max( 1, (int) $limit );
		$cutoff_time = time() - $stuck_after;

		$results = $this->get_results_prepared(
			"SELECT *
			FROM {$this->table_identifier()}
			WHERE state IN ('cloud_hash_check', 'pending_upload')
			AND updated_at < %d
			ORDER BY updated_at ASC
			LIMIT %d",
			$cutoff_time,
			$limit
		);

		if ( $results === null ) {
			sitefort_log( '[SiteFort] DB error in get_stuck_files: ' . $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;
	}

	/**
	 * Get files that need re-scan due to signature version change.
	 *
	 * @param string $current_signature Current malware signature version.
	 * @return array Files needing re-scan.
	 */
	public function get_files_needing_rescan( string $current_signature ): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE hash_status = 'safe'
			AND state = 'safe'
			AND scanned_with_signature != %s
			ORDER BY created_at ASC
			LIMIT 100",
			$current_signature
		);

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

		return $results;
	}

	/**
	 * Count files by state for a scan.
	 *
	 * @param string $scan_id Scan UUID.
	 * @return array State counts.
	 */
	public function count_by_state( string $scan_id ): array {
		$results = $this->get_results_prepared(
			"SELECT state, COUNT(*) as count
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			GROUP BY state",
			$scan_id
		);

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

		$counts = [];
		foreach ( $results as $row ) {
			$counts[ $row['state'] ] = (int) $row['count'];
		}

		return $counts;
	}

	/**
	 * 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 total files for a scan.
	 *
	 * @param string $scan_id Scan UUID.
	 * @return int Total file count.
	 */
	public function count_by_scan( string $scan_id ): int {
		$count = $this->get_var_prepared(
			"SELECT COUNT(*) FROM {$this->table_identifier()} WHERE scan_id = %s",
			$scan_id
		);

		return (int) $count;
	}

	/**
	 * 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;
	}

	/**
	 * 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 files with errors.
	 *
	 * @param string $scan_id Scan UUID.
	 * @return array Files in error state.
	 */
	public function get_error_files( string $scan_id ): array {
		$results = $this->get_results_prepared(
			"SELECT * FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND state = 'error'
			ORDER BY updated_at DESC",
			$scan_id
		);

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

		return $results;
	}

	/**
	 * 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;
	}

	/**
	 * Clear all file states (FSM reset).
	 * Used when starting a fresh scan.
	 *
	 * @return bool Success.
	 */
	public function clear_all_active(): bool {
		$result = $this->query_prepared( "TRUNCATE TABLE {$this->table_identifier()}" );
		return false !== $result;
	}

	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 : '';
	}

}
