<?php
/**
 * Squirrly SEO Importer
 *
 * Imports SEO data from Squirrly SEO into SEOvault.
 * Squirrly stores per-URL SEO in the custom {prefix}qss table, not wp_postmeta.
 *
 * @package SEOvaultSEO\Migration\Providers
 */

namespace SEOvaultSEO\Migration\Providers;

use SEOvaultSEO\Migration\BaseImporter;
use SEOvaultSEO\Migration\ImporterUtils;
use SEOvaultSEO\Utils\SafeUnserialize;

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

class Squirrly extends BaseImporter {

	const PLUGIN_NAME = 'Squirrly SEO';
	const PLUGIN_FILE = 'squirrly-seo/squirrly.php';
	const META_PREFIX = '_sq_';
	const QSS_TABLE   = 'qss';
	const OPTIONS_KEY = 'sq_options';

	const EXCLUDED_KEYS = array();

	const NOT_ALLOWED_TYPES = array(
		'elementor_library',
		'product_shipping_class',
	);

	const FALLBACK_META_KEYS = array(
		'title'       => '_sq_title',
		'description' => '_sq_description',
		'keywords'    => '_sq_keywords',
	);

	const PLACEHOLDERS = array(
		'{{title}}'                => '%post_title%',
		'{{sitename}}'             => '%site_name%',
		'{{sitedesc}}'             => '%site_description%',
		'{{excerpt}}'              => '%post_excerpt%',
		'{{date}}'                 => '%published_date%',
		'{{modified}}'             => '%modified_date%',
		'{{page}}'                 => '%page%',
		'{{term_title}}'           => '%term_name%',
		'{{term_description}}'     => '%term_description%',
		'{{category_description}}' => '%term_description%',
		'{{name}}'                 => '%author_name%',
		'{{currentdate}}'          => '%current_date%',
		'{{currentday}}'           => '%current_day%',
		'{{currentmonth}}'         => '%current_month%',
		'{{currentyear}}'          => '%current_year%',
		'{{category}}'             => '%category%',
		'{{tag}}'                  => '%tag%',
		'{{keyword}}'              => '%focus_keyword%',
	);

	const SEPARATOR_MAP = array(
		'sc-dash'   => '-',
		'sc-ndash'  => '–',
		'sc-mdash'  => '—',
		'sc-middot' => '·',
		'sc-bull'   => '•',
		'sc-star'   => '*',
		'sc-pipe'   => '|',
		'sc-tilde'  => '~',
		'sc-laquo'  => '«',
		'sc-raquo'  => '»',
		'sc-lt'     => '<',
		'sc-gt'     => '>',
	);

	/**
	 * Memoized qss.post column rows per blog ID.
	 *
	 * @var array<int, array|null>
	 */
	private static $qss_post_cache = array();

	/**
	 * Memoized qss table existence per blog ID.
	 *
	 * @var array<int, bool>
	 */
	private static $table_exists_cache = array();

	public function get_plugin_name() {
		return self::PLUGIN_NAME;
	}

	public function get_plugin_file() {
		return self::PLUGIN_FILE;
	}

	public function is_plugin_active() {
		return defined( 'SQ_OPTION' ) || defined( 'SQ_VERSION' );
	}

	protected function get_meta_key_prefix() {
		return self::META_PREFIX;
	}

	protected function get_excluded_meta_keys() {
		return self::EXCLUDED_KEYS;
	}

	protected function get_not_allowed_types() {
		return self::NOT_ALLOWED_TYPES;
	}

	protected function get_source_meta_data( $id, $is_taxonomy, $type = '' ) {
		return $this->get_seo_data( $id, $is_taxonomy );
	}

	/**
	 * Detect Squirrly data via the qss table (not wp_postmeta prefix scan).
	 *
	 * @param int $post_id Post ID.
	 * @return array
	 */
	public function detect_post( $post_id ) {
		if ( ! empty( $this->get_seo_data( $post_id, false ) ) ) {
			return ImporterUtils::build_response(
				sprintf(
					/* translators: 1: plugin name, 2: post ID */
					__( '%1$s data detected for post %2$d.', 'seovault-ai' ),
					$this->get_plugin_name(),
					$post_id
				),
				true
			);
		}

		ImporterUtils::update_migration_flag( $post_id );

		return ImporterUtils::build_response(
			sprintf(
				/* translators: 1: plugin name, 2: post ID */
				__( 'No %1$s data found for post %2$d.', 'seovault-ai' ),
				$this->get_plugin_name(),
				$post_id
			),
			false,
			array(),
			true
		);
	}

	/**
	 * Detect Squirrly term data via the qss table.
	 *
	 * @param int $term_id Term ID.
	 * @return array
	 */
	public function detect_term( $term_id ) {
		$term = get_term( $term_id );
		if ( ! $term || is_wp_error( $term ) ) {
			return ImporterUtils::not_valid_response( $term_id, true );
		}

		if ( ! empty( $this->get_seo_data( $term_id, true ) ) ) {
			return ImporterUtils::build_response(
				sprintf(
					/* translators: 1: plugin name, 2: term ID */
					__( '%1$s data detected for term %2$d.', 'seovault-ai' ),
					$this->get_plugin_name(),
					$term_id
				),
				true
			);
		}

		ImporterUtils::update_migration_flag( $term_id, false );

		return ImporterUtils::build_response(
			sprintf(
				/* translators: 1: plugin name, 2: term ID */
				__( 'No %1$s data found for term %2$d.', 'seovault-ai' ),
				$this->get_plugin_name(),
				$term_id
			),
			false,
			array(),
			true
		);
	}

	public function get_count_and_posts( $post_types, $batch_size, $offset ) {
		$all_ids     = $this->build_squirrly_post_ids( $post_types );
		$total_items = count( $all_ids );
		$post_ids    = array_slice( $all_ids, $offset, $batch_size );

		return array(
			'total_items' => $total_items,
			'post_ids'    => $post_ids,
		);
	}

	public function get_count_and_terms( $taxonomies, $taxonomies_objects, $batch_size, $offset ) {
		$all_ids     = $this->build_squirrly_term_ids( $taxonomies );
		$total_items = count( $all_ids );
		$term_ids    = array_slice( $all_ids, $offset, $batch_size );

		return array(
			'total_items' => $total_items,
			'term_ids'    => $term_ids,
		);
	}

	public function import_post_general_settings( $post_id ) {
		return $this->import_general_settings( $post_id, 'post', true );
	}

	public function import_term_general_settings( $term_id ) {
		return $this->import_general_settings( $term_id, 'term', false );
	}

	public function import_post_meta_robots( $post_id ) {
		return $this->import_robots_settings( $post_id, 'post' );
	}

	public function import_term_meta_robots( $term_id ) {
		return $this->import_robots_settings( $term_id, 'term' );
	}

	public function import_post_social( $post_id ) {
		return $this->import_social_settings( $post_id, 'post' );
	}

	public function import_term_social( $term_id ) {
		return $this->import_social_settings( $term_id, 'term' );
	}

	public function import_global_settings() {
		$sq_options = $this->get_sq_options();

		if ( empty( $sq_options ) ) {
			return ImporterUtils::build_response(
				__( 'No global settings found to import from Squirrly SEO.', 'seovault-ai' ),
				false
			);
		}

		$imported = false;
		$patterns = is_array( $sq_options['patterns'] ?? null ) ? $sq_options['patterns'] : array();
		$socials  = is_array( $sq_options['socials'] ?? null ) ? $sq_options['socials'] : array();

		if ( ! empty( $patterns['home']['title'] ) ) {
			\SEOvaultSEO\Core\Options::set_home_title(
				$this->replace_placeholders( (string) $patterns['home']['title'], $this->resolve_separator( $patterns['home']['sep'] ?? null ) )
			);
			$imported = true;
		}
		if ( ! empty( $patterns['home']['description'] ) ) {
			\SEOvaultSEO\Core\Options::set_home_description(
				$this->replace_placeholders( (string) $patterns['home']['description'], $this->resolve_separator( $patterns['home']['sep'] ?? null ) )
			);
			$imported = true;
		}

		if ( ! empty( $patterns['post']['title'] ) ) {
			\SEOvaultSEO\Core\Options::set_title_template(
				$this->replace_placeholders( (string) $patterns['post']['title'], $this->resolve_separator( $patterns['post']['sep'] ?? null ) )
			);
			$imported = true;
		}

		if ( ! empty( $socials['facebook_site'] ) ) {
			\SEOvaultSEO\Core\Options::set( 'facebook_page_url', (string) $socials['facebook_site'] );
			$imported = true;
		}

		$twitter = $socials['twitter_site'] ?? ( $socials['twitter'] ?? '' );
		if ( ! empty( $twitter ) ) {
			\SEOvaultSEO\Core\Options::set( 'twitter_username', ltrim( (string) $twitter, '@' ) );
			$imported = true;
		}

		return ImporterUtils::build_response(
			$imported
				? __( 'Global settings imported from Squirrly SEO.', 'seovault-ai' )
				: __( 'No global settings found to import from Squirrly SEO.', 'seovault-ai' ),
			$imported
		);
	}

	/**
	 * Import title, description, canonical, and focus keyword.
	 *
	 * @param int    $object_id   Post or term ID.
	 * @param string $object_kind 'post' or 'term'.
	 * @param bool   $import_focus Whether to import focus keyword (posts only).
	 * @return array
	 */
	private function import_general_settings( $object_id, $object_kind, $import_focus ) {
		$imported = false;
		$sep      = $this->resolve_separator();

		$title = (string) ( $this->source_meta['title'] ?? '' );
		if ( '' !== $title ) {
			$this->target_meta['seo_title'] = $this->replace_placeholders( $title, $sep );
			$imported                       = true;
		}

		$description = (string) ( $this->source_meta['description'] ?? '' );
		if ( '' !== $description ) {
			$this->target_meta['seo_description'] = $this->replace_placeholders( $description, $sep );
			$imported                             = true;
		}

		$canonical = esc_url_raw( (string) ( $this->source_meta['canonical'] ?? '' ) );
		if ( '' !== $canonical ) {
			$this->target_meta['canonical_url'] = $canonical;
			$imported                           = true;
		}

		if ( $import_focus ) {
			$focus_keyword = ImporterUtils::get_primary_keyword( (string) ( $this->source_meta['keywords'] ?? '' ) );
			if ( '' !== $focus_keyword ) {
				$this->target_meta['focus_keyword'] = $focus_keyword;
				$imported                           = true;
			}
		}

		return ImporterUtils::build_response(
			ImporterUtils::import_status_message( $imported, $object_id, $object_kind, 'general' ),
			$imported
		);
	}

	/**
	 * Import noindex / nofollow flags from Squirrly integer fields.
	 *
	 * @param int    $object_id   Post or term ID.
	 * @param string $object_kind 'post' or 'term'.
	 * @return array
	 */
	private function import_robots_settings( $object_id, $object_kind ) {
		$imported = false;

		if ( ! empty( $this->source_meta['noindex'] ) ) {
			$this->target_meta['robots_noindex'] = true;
			$imported                            = true;
		}
		if ( ! empty( $this->source_meta['nofollow'] ) ) {
			$this->target_meta['robots_nofollow'] = true;
			$imported                             = true;
		}

		return ImporterUtils::build_response(
			ImporterUtils::import_status_message( $imported, $object_id, $object_kind, 'meta_robots' ),
			$imported
		);
	}

	/**
	 * Import Open Graph fields from Squirrly social data.
	 *
	 * @param int    $object_id   Post or term ID.
	 * @param string $object_kind 'post' or 'term'.
	 * @return array
	 */
	private function import_social_settings( $object_id, $object_kind ) {
		$imported = false;
		$sep      = $this->resolve_separator();

		$og_title = (string) ( $this->source_meta['og_title'] ?? '' );
		if ( '' !== $og_title ) {
			$this->target_meta['og_title'] = $this->replace_placeholders( $og_title, $sep );
			$imported                      = true;
		}

		$og_description = (string) ( $this->source_meta['og_description'] ?? '' );
		if ( '' !== $og_description ) {
			$this->target_meta['og_description'] = $this->replace_placeholders( $og_description, $sep );
			$imported                            = true;
		}

		$og_image = esc_url_raw( (string) ( $this->source_meta['og_media'] ?? '' ) );
		if ( '' === $og_image ) {
			$og_image = esc_url_raw( (string) ( $this->source_meta['tw_media'] ?? '' ) );
		}
		if ( '' !== $og_image ) {
			$this->target_meta['og_image'] = $og_image;
			$imported                      = true;
		}

		return ImporterUtils::build_response(
			ImporterUtils::import_status_message( $imported, $object_id, $object_kind, 'social' ),
			$imported
		);
	}

	/**
	 * Get SEO data for a post or term from the qss table.
	 *
	 * @param int  $id          Post or term ID.
	 * @param bool $is_taxonomy Whether it is a taxonomy term.
	 * @return array
	 */
	private function get_seo_data( $id, $is_taxonomy ) {
		global $wpdb;

		if ( ! $this->qss_table_exists() ) {
			return $is_taxonomy ? array() : $this->get_postmeta_fallback( $id );
		}

		$hash_input = $this->build_url_hash_input( $id, $is_taxonomy );
		if ( '' === $hash_input ) {
			return array();
		}

		$table    = $wpdb->prefix . self::QSS_TABLE;
		$url_hash = md5( $hash_input );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Squirrly migration reads third-party qss tables.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT seo FROM `{$table}` WHERE blog_id = %d AND url_hash = %s",
				get_current_blog_id(),
				$url_hash
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! $row || empty( $row->seo ) ) {
			return $is_taxonomy ? array() : $this->get_postmeta_fallback( $id );
		}

		return $this->parse_seo_column( (string) $row->seo );
	}

	/**
	 * Build the hash input Squirrly uses for qss.url_hash.
	 *
	 * @param int  $id          Post or term ID.
	 * @param bool $is_taxonomy Whether it is a taxonomy term.
	 * @return string
	 */
	private function build_url_hash_input( $id, $is_taxonomy ) {
		if ( $is_taxonomy ) {
			$term = get_term( $id );
			if ( ! $term || is_wp_error( $term ) ) {
				return '';
			}

			if ( 'post_tag' === $term->taxonomy ) {
				return 'tag' . (string) $id;
			}
			if ( 'category' === $term->taxonomy ) {
				return 'category' . (string) $id;
			}

			return 'tax-' . $term->taxonomy . (string) $id;
		}

		$post_type = get_post_type( $id );
		if ( ! $post_type ) {
			return '';
		}

		$standard_types = array( 'post', 'page', 'product', 'attachment', 'cartflows_step' );
		if ( in_array( $post_type, $standard_types, true ) ) {
			return (string) $id;
		}

		return $post_type . (string) $id;
	}

	/**
	 * Parse a serialised qss.seo column into a flat array.
	 *
	 * @param string $raw Raw serialised value.
	 * @return array
	 */
	private function parse_seo_column( $raw ) {
		$seo = SafeUnserialize::maybe( $raw );

		if ( ! $seo ) {
			return array();
		}

		if ( is_object( $seo ) && ! ( $seo instanceof \__PHP_Incomplete_Class ) ) {
			return array(
				'title'          => (string) ( $seo->title ?? '' ),
				'description'    => (string) ( $seo->description ?? '' ),
				'keywords'       => (string) ( $seo->keywords ?? '' ),
				'canonical'      => (string) ( $seo->canonical ?? '' ),
				'noindex'        => (int) ( $seo->noindex ?? 0 ),
				'nofollow'       => (int) ( $seo->nofollow ?? 0 ),
				'og_title'       => (string) ( $seo->og_title ?? '' ),
				'og_description' => (string) ( $seo->og_description ?? '' ),
				'og_media'       => (string) ( $seo->og_media ?? '' ),
				'tw_title'       => (string) ( $seo->tw_title ?? '' ),
				'tw_description' => (string) ( $seo->tw_description ?? '' ),
				'tw_media'       => (string) ( $seo->tw_media ?? '' ),
				'tw_type'        => (string) ( $seo->tw_type ?? '' ),
			);
		}

		if ( ! is_array( $seo ) ) {
			return array();
		}

		return array(
			'title'          => (string) ( $seo['title'] ?? '' ),
			'description'    => (string) ( $seo['description'] ?? '' ),
			'keywords'       => (string) ( $seo['keywords'] ?? '' ),
			'canonical'      => (string) ( $seo['canonical'] ?? '' ),
			'noindex'        => (int) ( $seo['noindex'] ?? 0 ),
			'nofollow'       => (int) ( $seo['nofollow'] ?? 0 ),
			'og_title'       => (string) ( $seo['og_title'] ?? '' ),
			'og_description' => (string) ( $seo['og_description'] ?? '' ),
			'og_media'       => (string) ( $seo['og_media'] ?? '' ),
			'tw_title'       => (string) ( $seo['tw_title'] ?? '' ),
			'tw_description' => (string) ( $seo['tw_description'] ?? '' ),
			'tw_media'       => (string) ( $seo['tw_media'] ?? '' ),
			'tw_type'        => (string) ( $seo['tw_type'] ?? '' ),
		);
	}

	/**
	 * Legacy wp_postmeta fallback when qss has no row for a post.
	 *
	 * @param int $post_id Post ID.
	 * @return array
	 */
	private function get_postmeta_fallback( $post_id ) {
		$data = array();

		foreach ( self::FALLBACK_META_KEYS as $field => $meta_key ) {
			$value = get_post_meta( $post_id, $meta_key, true );
			if ( ! empty( $value ) ) {
				$data[ $field ] = $value;
			}
		}

		return $data;
	}

	/**
	 * Decode Squirrly global sq_options JSON.
	 *
	 * @return array
	 */
	private function get_sq_options() {
		$raw = get_option( self::OPTIONS_KEY, '' );
		if ( empty( $raw ) ) {
			return array();
		}

		$decoded = json_decode( $raw, true );

		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Build post IDs that have Squirrly data in qss or legacy postmeta.
	 *
	 * @param array $post_types Public post types.
	 * @return array
	 */
	private function build_squirrly_post_ids( array $post_types ) {
		global $wpdb;

		if ( empty( $post_types ) ) {
			return array();
		}

		$qss_ids       = array();
		$candidate_ids = array_filter(
			array_column( $this->get_qss_post_entries(), 'id' ),
			static function ( $id ) {
				return $id > 0;
			}
		);

		if ( ! empty( $candidate_ids ) ) {
			$id_placeholders = implode( ',', array_fill( 0, count( $candidate_ids ), '%d' ) );

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Squirrly migration reads third-party qss tables.
			$type_rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT ID, post_type FROM {$wpdb->posts}
					 WHERE ID IN ({$id_placeholders})
					   AND post_status NOT IN ('auto-draft', 'trash', 'inherit')",
					array_values( $candidate_ids )
				),
				ARRAY_A
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

			$id_to_type = array_column( $type_rows, 'post_type', 'ID' );

			foreach ( $candidate_ids as $post_id ) {
				$actual_type = $id_to_type[ $post_id ] ?? '';
				if ( $actual_type && in_array( $actual_type, $post_types, true ) ) {
					$qss_ids[] = (int) $post_id;
				}
			}
		}

		$posttype_placeholders = implode( ',', array_fill( 0, count( $post_types ), '%s' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Squirrly migration reads third-party qss tables.
		$meta_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT p.ID
				 FROM {$wpdb->posts} p
				 INNER JOIN {$wpdb->postmeta} pm
				         ON p.ID = pm.post_id AND pm.meta_key = %s
				 WHERE p.post_type IN ({$posttype_placeholders})
				   AND p.post_status != 'auto-draft'",
				array_merge( array( '_sq_title' ), $post_types )
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$meta_ids = array_map( 'intval', $meta_ids ? $meta_ids : array() );
		$all_ids  = array_unique( array_merge( $qss_ids, $meta_ids ) );

		if ( empty( $all_ids ) ) {
			return array();
		}

		return $this->exclude_migrated_ids( $all_ids, true );
	}

	/**
	 * Build term IDs that have Squirrly data in qss.
	 *
	 * @param array $taxonomies Public taxonomy slugs.
	 * @return array
	 */
	private function build_squirrly_term_ids( array $taxonomies ) {
		$all_ids = array();

		foreach ( $this->get_qss_term_entries() as $entry ) {
			if ( in_array( $entry['taxonomy'], $taxonomies, true ) ) {
				$all_ids[] = $entry['term_id'];
			}
		}

		$all_ids = array_unique( $all_ids );

		if ( empty( $all_ids ) ) {
			return array();
		}

		return $this->exclude_migrated_ids( $all_ids, false );
	}

	/**
	 * Remove IDs already marked as migrated.
	 *
	 * @param array $ids     Post or term IDs.
	 * @param bool  $is_post Whether IDs are posts.
	 * @return array
	 */
	private function exclude_migrated_ids( array $ids, $is_post ) {
		global $wpdb;

		if ( empty( $ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$params       = array_merge( array( ImporterUtils::MIGRATION_FLAG ), $ids );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Squirrly migration reads third-party qss tables.
		if ( $is_post ) {
			$migrated = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT post_id FROM {$wpdb->postmeta}
					 WHERE meta_key = %s AND post_id IN ({$placeholders})",
					$params
				)
			);
		} else {
			$migrated = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT term_id FROM {$wpdb->termmeta}
					 WHERE meta_key = %s AND term_id IN ({$placeholders})",
					$params
				)
			);
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$migrated_ids = array_map( 'intval', $migrated ? $migrated : array() );

		return array_values( array_diff( $ids, $migrated_ids ) );
	}

	/**
	 * Fetch post-related entries from the qss table.
	 *
	 * @return array<int, array{id: int, post_type: string}>
	 */
	private function get_qss_post_entries() {
		$entries = array();

		foreach ( $this->fetch_qss_post_column() as $row ) {
			if ( empty( $row->post ) ) {
				continue;
			}

			$post_data = SafeUnserialize::maybe( $row->post );
			if ( ! $post_data ) {
				continue;
			}

			list( $id, $post_type, , $taxonomy ) = $this->extract_qss_post_fields( $post_data );

			if ( $id > 0 && '' === $taxonomy ) {
				$entries[] = array(
					'id'        => $id,
					'post_type' => $post_type,
				);
			}
		}

		return $entries;
	}

	/**
	 * Fetch term-related entries from the qss table.
	 *
	 * @return array<int, array{term_id: int, taxonomy: string}>
	 */
	private function get_qss_term_entries() {
		$entries = array();

		foreach ( $this->fetch_qss_post_column() as $row ) {
			if ( empty( $row->post ) ) {
				continue;
			}

			$post_data = SafeUnserialize::maybe( $row->post );
			if ( ! $post_data ) {
				continue;
			}

			list( , , $term_id, $taxonomy ) = $this->extract_qss_post_fields( $post_data );

			if ( $term_id > 0 && '' !== $taxonomy ) {
				$entries[] = array(
					'term_id'  => $term_id,
					'taxonomy' => $taxonomy,
				);
			}
		}

		return $entries;
	}

	/**
	 * Read all qss.post column values for the current blog.
	 *
	 * @return array
	 */
	private function fetch_qss_post_column() {
		global $wpdb;

		$blog_id = get_current_blog_id();

		if ( array_key_exists( $blog_id, self::$qss_post_cache ) ) {
			return self::$qss_post_cache[ $blog_id ] ?? array();
		}

		if ( ! $this->qss_table_exists() ) {
			self::$qss_post_cache[ $blog_id ] = null;
			return array();
		}

		$table = $wpdb->prefix . self::QSS_TABLE;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Squirrly migration reads third-party qss tables.
		$result = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT post FROM `{$table}` WHERE blog_id = %d",
				$blog_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

		self::$qss_post_cache[ $blog_id ] = $result;

		return $result ? $result : array();
	}

	/**
	 * Extract post/term fields from a deserialised qss.post value.
	 *
	 * @param mixed $post_data Deserialised qss.post value.
	 * @return array
	 */
	private function extract_qss_post_fields( $post_data ) {
		if ( is_object( $post_data ) && ! ( $post_data instanceof \__PHP_Incomplete_Class ) ) {
			$id       = isset( $post_data->ID ) ? (int) $post_data->ID : (int) ( $post_data->id ?? 0 );
			$type     = (string) ( $post_data->post_type ?? '' );
			$term_id  = (int) ( $post_data->term_id ?? 0 );
			$taxonomy = (string) ( $post_data->taxonomy ?? '' );

			return array( $id, $type, $term_id, $taxonomy );
		}

		if ( is_array( $post_data ) ) {
			$id       = isset( $post_data['ID'] ) ? (int) $post_data['ID'] : (int) ( $post_data['id'] ?? 0 );
			$type     = (string) ( $post_data['post_type'] ?? '' );
			$term_id  = (int) ( $post_data['term_id'] ?? 0 );
			$taxonomy = (string) ( $post_data['taxonomy'] ?? '' );

			return array( $id, $type, $term_id, $taxonomy );
		}

		return array( 0, '', 0, '' );
	}

	/**
	 * Check whether the qss table exists.
	 *
	 * @return bool
	 */
	private function qss_table_exists() {
		global $wpdb;

		$blog_id = get_current_blog_id();

		if ( array_key_exists( $blog_id, self::$table_exists_cache ) ) {
			return self::$table_exists_cache[ $blog_id ];
		}

		$table = $wpdb->prefix . self::QSS_TABLE;
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix; SHOW TABLES uses esc_like().
		self::$table_exists_cache[ $blog_id ] = $wpdb->get_var( "SHOW TABLES LIKE '" . $wpdb->esc_like( $table ) . "'" ) === $table;

		return self::$table_exists_cache[ $blog_id ];
	}

	/**
	 * Replace Squirrly {{variable}} placeholders with SEOvault equivalents.
	 *
	 * @param string      $value     Value containing placeholders.
	 * @param string|null $separator Separator character for {{sep}}.
	 * @return string
	 */
	private function replace_placeholders( $value, $separator = null ) {
		if ( ! is_string( $value ) ) {
			return (string) $value;
		}

		$placeholders            = self::PLACEHOLDERS;
		$placeholders['{{sep}}'] = null !== $separator ? $separator : $this->resolve_separator();

		return str_replace( array_keys( $placeholders ), array_values( $placeholders ), $value );
	}

	/**
	 * Resolve a Squirrly separator code to a character.
	 *
	 * @param string|null $override Explicit separator code or character.
	 * @return string
	 */
	private function resolve_separator( $override = null ) {
		if ( null !== $override && '' !== $override ) {
			return self::SEPARATOR_MAP[ $override ] ?? (string) $override;
		}

		$patterns = $this->get_sq_options()['patterns'] ?? array();
		$post_sep = is_array( $patterns['post'] ?? null ) ? ( $patterns['post']['sep'] ?? '' ) : '';

		if ( '' !== $post_sep ) {
			return self::SEPARATOR_MAP[ $post_sep ] ?? (string) $post_sep;
		}

		return '-';
	}
}
