<?php
/**
 * Legacy prefix data migrator.
 *
 * @package Affilun\Infrastructure\Persistence
 */

declare(strict_types=1);

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Legacy migration SQL uses escaped identifiers or $wpdb->prepare() before execution.

namespace Affilun\Infrastructure\Persistence;

use RuntimeException;

/**
 * Copies data from pre-0.7.0 `afl_*` storage into the `affilun_*` storage.
 */
final class LegacyPrefixDataMigrator {

	public const OPTION_KEY = 'affilun_legacy_prefix_migrated';

	/**
	 * @var list<string>
	 */
	private const TABLE_SUFFIXES = array(
		'audit_events',
		'click_events',
		'click_rollups',
		'embed_items',
		'embeds',
		'logs',
		'product_meta',
		'product_urls',
		'products',
		'sync_jobs',
		'top_affiliate_candidates',
	);

	/**
	 * @var array<string,string>
	 */
	private const OPTION_MAP = array(
		'afl_affiliate_routing_templates'      => 'affilun_affiliate_routing_templates',
		'afl_backfill_amazon_last_search_at'   => 'affilun_backfill_amazon_last_search_at',
		'afl_click_counts'                     => 'affilun_click_counts',
		'afl_click_events'                     => 'affilun_click_events',
		'afl_cloud_ai_scan_no_candidate_posts' => 'affilun_cloud_ai_scan_no_candidate_posts',
		'afl_db_schema_version'                => 'affilun_db_schema_version',
		'afl_link_health_cron_state'           => 'affilun_link_health_cron_state',
		'afl_settings'                         => 'affilun_settings',
		'afl_settings_secrets'                 => 'affilun_settings_secrets',
		'afl_sync_worker_id'                   => 'affilun_sync_worker_id',
	);

	/**
	 * WordPress database adapter.
	 */
	private object $wpdb;

	/**
	 * Construct the migrator.
	 *
	 * @param object $wpdb WordPress database adapter.
	 */
	public function __construct( object $wpdb ) {
		$this->wpdb = $wpdb;
	}

	/**
	 * Copy legacy data once.
	 *
	 * @return array<string,mixed>
	 */
	public function run(): array {
		if ( $this->optionExists( self::OPTION_KEY ) ) {
			return array( 'status' => 'already_migrated' );
		}

		$result = array(
			'status'  => 'completed',
			'tables'  => array(),
			'options' => array(),
			'posts'   => array(),
		);

		foreach ( self::TABLE_SUFFIXES as $suffix ) {
			$result['tables'][ $suffix ] = $this->copyTableIfNeeded( $suffix );
		}

		foreach ( self::OPTION_MAP as $oldKey => $newKey ) {
			$result['options'][ $newKey ] = $this->copyOptionIfNeeded( $oldKey, $newKey );
		}
		$result['posts'] = $this->migratePostContentMarkup();

		$encoded = wp_json_encode( $result );
		if ( ! is_string( $encoded ) ) {
			$encoded = '{"status":"completed"}';
		}
		$this->insertOption( self::OPTION_KEY, $encoded, 'off' );

		return $result;
	}

	/**
	 * Copy one legacy table when the new table exists but is empty.
	 *
	 * @param string $suffix Bare table suffix after the plugin prefix.
	 *
	 * @return array<string,mixed>
	 */
	private function copyTableIfNeeded( string $suffix ): array {
		$old = $this->tableName( 'afl_' . $suffix );
		$new = $this->tableName( 'affilun_' . $suffix );

		if ( ! $this->tableExists( $old ) || ! $this->tableExists( $new ) ) {
			return array( 'status' => 'missing_table' );
		}

		$oldCount = $this->tableCount( $old );
		$newCount = $this->tableCount( $new );

		if ( $oldCount <= 0 ) {
			return array(
				'status'    => 'legacy_empty',
				'old_count' => $oldCount,
				'new_count' => $newCount,
			);
		}
		if ( $newCount > 0 ) {
			return array(
				'status'    => 'kept_existing',
				'old_count' => $oldCount,
				'new_count' => $newCount,
			);
		}

		$sql = sprintf(
			'INSERT INTO `%s` SELECT * FROM `%s`',
			self::escapeIdentifier( $new ),
			self::escapeIdentifier( $old )
		);
		$this->query( $sql );

		return array(
			'status'    => 'copied',
			'old_count' => $oldCount,
			'new_count' => $this->tableCount( $new ),
		);
	}

	/**
	 * Copy one legacy option when the new option does not exist.
	 *
	 * @param string $oldKey Legacy option name.
	 * @param string $newKey New option name.
	 */
	private function copyOptionIfNeeded( string $oldKey, string $newKey ): string {
		if ( $this->optionExists( $newKey ) ) {
			return 'kept_existing';
		}

		$row = $this->optionRow( $oldKey );
		if ( $row === null ) {
			return 'legacy_missing';
		}

		$this->insertOption( $newKey, $row['option_value'], $row['autoload'] );
		return 'copied';
	}

	/**
	 * Apply the WordPress table prefix to a logical name.
	 *
	 * @param string $logicalName Logical table name.
	 */
	private function tableName( string $logicalName ): string {
		$prefix = '';
		if ( property_exists( $this->wpdb, 'prefix' ) && is_string( $this->wpdb->prefix ) ) {
			$prefix = $this->wpdb->prefix;
		}
		return $prefix . $logicalName;
	}

	/**
	 * Resolve the physical options table name.
	 */
	private function optionsTable(): string {
		if ( property_exists( $this->wpdb, 'options' ) && is_string( $this->wpdb->options ) && $this->wpdb->options !== '' ) {
			return $this->wpdb->options;
		}
		return $this->tableName( 'options' );
	}

	/**
	 * Resolve the physical posts table name.
	 */
	private function postsTable(): string {
		if ( property_exists( $this->wpdb, 'posts' ) && is_string( $this->wpdb->posts ) && $this->wpdb->posts !== '' ) {
			return $this->wpdb->posts;
		}
		return $this->tableName( 'posts' );
	}

	/**
	 * Rewrite legacy block and shortcode markup in post content.
	 *
	 * @return array<string,int>
	 */
	private function migratePostContentMarkup(): array {
		$postsTable = $this->postsTable();
		$before     = $this->legacyPostMarkupCount( $postsTable );
		if ( $before <= 0 ) {
			return array(
				'before'  => 0,
				'updated' => 0,
				'after'   => 0,
			);
		}

		$replacedContent = "REPLACE(REPLACE(post_content, 'wp:afl/product-card', 'wp:affilun/product-card'), '[afl_product_card', '[affilun_product_card')";
		$whereLegacy     = "post_content LIKE '%%wp:afl/product-card%%' OR post_content LIKE '%%[afl_product_card%%'";
		$sql             = sprintf(
			'UPDATE `%s` SET post_content = %s WHERE %s',
			self::escapeIdentifier( $postsTable ),
			$replacedContent,
			$whereLegacy
		);
		$this->query( $sql );
		$after = $this->legacyPostMarkupCount( $postsTable );

		return array(
			'before'  => $before,
			'updated' => max( 0, $before - $after ),
			'after'   => $after,
		);
	}

	/**
	 * Count posts containing legacy affilun markup.
	 *
	 * @param string $postsTable Physical posts table.
	 */
	private function legacyPostMarkupCount( string $postsTable ): int {
		$whereLegacy = "post_content LIKE '%%wp:afl/product-card%%' OR post_content LIKE '%%[afl_product_card%%'";
		$sql         = sprintf( 'SELECT COUNT(*) FROM `%s` WHERE %s', self::escapeIdentifier( $postsTable ), $whereLegacy );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifier is escaped via escapeIdentifier().
		return (int) $this->wpdb->get_var( $sql );
	}

	/**
	 * Check whether a table exists.
	 *
	 * @param string $table Physical table name.
	 */
	private function tableExists( string $table ): bool {
		$sql = $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table );
		if ( ! is_string( $sql ) ) {
			throw new RuntimeException( 'affilun legacy prefix migration failed to prepare SQL.' );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above.
		$found = $this->wpdb->get_var( $sql );
		return is_string( $found ) && $found === $table;
	}

	/**
	 * Count rows in a physical table.
	 *
	 * @param string $table Physical table name.
	 */
	private function tableCount( string $table ): int {
		$sql = sprintf( 'SELECT COUNT(*) FROM `%s`', self::escapeIdentifier( $table ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifier is escaped via escapeIdentifier().
		return (int) $this->wpdb->get_var( $sql );
	}

	/**
	 * Check whether an option exists.
	 *
	 * @param string $key Option name.
	 */
	private function optionExists( string $key ): bool {
		$sql = $this->wpdb->prepare(
			'SELECT COUNT(*) FROM %i WHERE option_name = %s',
			$this->optionsTable(),
			$key
		);
		if ( ! is_string( $sql ) ) {
			throw new RuntimeException( 'affilun legacy prefix migration failed to prepare SQL.' );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above.
		return (int) $this->wpdb->get_var( $sql ) > 0;
	}

	/**
	 * Load a raw option row.
	 *
	 * @param string $key Option name.
	 *
	 * @return array{option_value:mixed,autoload:string}|null
	 */
	private function optionRow( string $key ): ?array {
		$sql = $this->wpdb->prepare(
			'SELECT option_value, autoload FROM %i WHERE option_name = %s',
			$this->optionsTable(),
			$key
		);
		if ( ! is_string( $sql ) ) {
			throw new RuntimeException( 'affilun legacy prefix migration failed to prepare SQL.' );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above.
		$row = $this->wpdb->get_row( $sql, ARRAY_A );
		if ( ! is_array( $row ) || ! array_key_exists( 'option_value', $row ) ) {
			return null;
		}

		return array(
			'option_value' => $row['option_value'],
			'autoload'     => is_string( $row['autoload'] ?? null ) ? $row['autoload'] : 'auto',
		);
	}

	/**
	 * Insert an option row.
	 *
	 * @param string $key      Option name.
	 * @param mixed  $value    Option value.
	 * @param string $autoload Autoload flag.
	 */
	private function insertOption( string $key, $value, string $autoload ): void {
		$inserted = $this->wpdb->insert(
			$this->optionsTable(),
			array(
				'option_name'  => $key,
				'option_value' => $value,
				'autoload'     => $autoload,
			)
		);
		if ( $inserted === false ) {
			throw new RuntimeException( 'Failed to insert affilun legacy prefix migration option.' );
		}
	}

	/**
	 * Execute an already escaped SQL statement.
	 *
	 * @param string $sql SQL statement.
	 */
	private function query( string $sql ): void {
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Callers provide escaped SQL.
		$result = $this->wpdb->query( $sql );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to copy affilun legacy prefix data.' );
		}
	}

	/**
	 * Escape backticks inside a SQL identifier.
	 *
	 * @param string $identifier SQL identifier.
	 */
	private static function escapeIdentifier( string $identifier ): string {
		return str_replace( '`', '``', $identifier );
	}
}