<?php
/**
 * TamRank SEO Scanner
 *
 * Detects existing SEO plugins (Yoast, Rank Math) and scans the site
 * for SEO statistics used by the onboarding wizard.
 */

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

class TamRank_SEO_Scanner {

    /** @var string Transient key for cached scan results */
    const CACHE_KEY = 'tamrank_onboarding_scan';

    /** @var int Cache duration in seconds (1 hour) */
    const CACHE_TTL = 3600;

    /**
     * Every Yoast meta key the wizard can read, across all categories.
     *
     * The detection card says "N items with SEO data", so it has to look at
     * everything the migration can reach - not only title/description/focus
     * keyword. On the acceptance fixture the old three-key query reported 8
     * where 19 posts carry Yoast data: a page with nothing but a canonical, a
     * robots flag or a schema type was invisible on the very screen that asks
     * "do you want to bring this along?", and the plan step was skipped
     * altogether on a site that has only those.
     *
     * @var string[]
     */
    const YOAST_SOURCE_KEYS = array(
        '_yoast_wpseo_title',
        '_yoast_wpseo_metadesc',
        '_yoast_wpseo_focuskw',
        '_yoast_wpseo_meta-robots-noindex',
        '_yoast_wpseo_meta-robots-nofollow',
        '_yoast_wpseo_canonical',
        '_yoast_wpseo_opengraph-title',
        '_yoast_wpseo_opengraph-description',
        '_yoast_wpseo_opengraph-image',
        '_yoast_wpseo_schema_page_type',
        '_yoast_wpseo_schema_article_type',
    );

    /**
     * Same for Rank Math. See YOAST_SOURCE_KEYS for the why.
     *
     * @var string[]
     */
    const RANKMATH_SOURCE_KEYS = array(
        'rank_math_title',
        'rank_math_description',
        'rank_math_focus_keyword',
        'rank_math_robots',
        'rank_math_canonical_url',
        'rank_math_facebook_title',
        'rank_math_facebook_description',
        'rank_math_facebook_image',
        'rank_math_rich_snippet',
    );

    /**
     * Every SEO plugin TamRank knows about, in detection priority order, with
     * the plugin files (free + premium) that mark it as active.
     *
     * Single source of truth for those paths: both detect_all_seo_plugins()
     * and detect_active_seo_plugins() read them from here.
     *
     * @var array<string, array>
     */
    const SEO_PLUGINS = array(
        'yoast' => array(
            'name'  => 'Yoast SEO',
            'files' => array(
                'wordpress-seo/wp-seo.php',
                'wordpress-seo-premium/wp-seo-premium.php',
            ),
        ),
        'rankmath' => array(
            'name'  => 'Rank Math',
            'files' => array(
                'seo-by-rank-math/rank-math.php',
                'seo-by-rank-math-pro/rank-math-pro.php',
            ),
        ),
        'aioseo' => array(
            'name'  => 'All in One SEO',
            'files' => array(
                'all-in-one-seo-pack/all_in_one_seo_pack.php',
                'all-in-one-seo-pack-pro/all_in_one_seo_pack.php',
            ),
        ),
        'seopress' => array(
            'name'  => 'SEOPress',
            'files' => array(
                'wp-seopress/seopress.php',
                'wp-seopress-pro/seopress-pro.php',
            ),
        ),
    );

    /**
     * The subset of SEO_PLUGINS the wizard can actually import from, in the
     * same priority order.
     *
     * SEOPress is deliberately absent: there is no SEOPress migrator, so it
     * must never turn up as a source in the wizard. It is still detected as an
     * active plugin, because two SEO plugins writing to the same pages is worth
     * saying out loud even when we cannot take the data over.
     *
     * @var string[]
     */
    const MIGRATION_SOURCES = array( 'yoast', 'rankmath', 'aioseo' );

    /* ------------------------------------------------------------------
     * SEO Plugin Detection
     * ----------------------------------------------------------------*/

    /**
     * Detect ALL SEO plugins with importable data, in priority order
     * (yoast, rankmath, aioseo). Each entry: plugin / plugin_name /
     * data_count. Used by the wizard's source-choice UI.
     *
     * Checks both plugin activation status AND presence of data,
     * so detection works even if the plugin was deactivated.
     *
     * @return array List of array { plugin, plugin_name, data_count }.
     */
    public function detect_all_seo_plugins() {
        $sources = array();

        foreach ( self::MIGRATION_SOURCES as $key ) {
            $data_count = $this->count_source_data( $key );

            if ( $this->is_seo_plugin_active( $key ) || $data_count > 0 ) {
                $sources[] = array(
                    'plugin'      => $key,
                    'plugin_name' => self::SEO_PLUGINS[ $key ]['name'],
                    'data_count'  => $data_count,
                );
            }
        }

        return $sources;
    }

    /**
     * Every SEO plugin that is active right now, in the same priority order
     * and with the same plugin / plugin_name values as
     * detect_all_seo_plugins().
     *
     * Where detect_all_seo_plugins() answers "whose data can we import?", this
     * answers "who else is writing to these pages today?" - leftover data in
     * the database does not count, and SEOPress does. Reads nothing but the
     * active_plugins option, so it is cheap enough for every admin page load.
     *
     * @return array List of array { plugin, plugin_name }.
     */
    public function detect_active_seo_plugins() {
        $active = array();

        foreach ( self::SEO_PLUGINS as $key => $plugin ) {
            if ( $this->is_seo_plugin_active( $key ) ) {
                $active[] = array(
                    'plugin'      => $key,
                    'plugin_name' => $plugin['name'],
                );
            }
        }

        return $active;
    }

    /**
     * Is any of a known SEO plugin's files (free or premium) active?
     *
     * @param string $key Key into SEO_PLUGINS.
     * @return bool
     */
    private function is_seo_plugin_active( $key ) {
        if ( ! isset( self::SEO_PLUGINS[ $key ] ) ) {
            return false;
        }

        foreach ( self::SEO_PLUGINS[ $key ]['files'] as $file ) {
            if ( $this->is_plugin_active( $file ) ) {
                return true;
            }
        }

        return false;
    }

    /**
     * How many items carry data for one migration source.
     *
     * @param string $key One of MIGRATION_SOURCES.
     * @return int
     */
    private function count_source_data( $key ) {
        switch ( $key ) {
            case 'yoast':
                return $this->count_posts_with_any_meta( self::YOAST_SOURCE_KEYS );

            case 'rankmath':
                return $this->count_posts_with_any_meta( self::RANKMATH_SOURCE_KEYS );

            case 'aioseo':
                // Custom aioseo_posts table, no postmeta.
                return $this->get_aioseo_migrator()->get_data_count();
        }

        return 0;
    }

    /**
     * First detected plugin (legacy shape). Output is bit-for-bit
     * identical to the pre-source-choice implementation for every caller.
     *
     * @return array {
     *     @type string|null $plugin       'yoast' | 'rankmath' | 'aioseo' | null
     *     @type string      $plugin_name  Human-readable name
     *     @type int         $data_count   Number of posts with SEO data
     * }
     */
    public function detect_seo_plugin() {
        $all = $this->detect_all_seo_plugins();
        if ( ! empty( $all ) ) {
            return $all[0];
        }
        return array(
            'plugin'      => null,
            'plugin_name' => '',
            'data_count'  => 0,
        );
    }

    /** @var TamRank_AIOSEO_Migrator|null Cached per scanner instance */
    private $aioseo_migrator = null;

    /**
     * Lazily instantiate the AIOSEO migrator (require_once precedent of
     * count_redirects). Cached per scanner instance so the migrator's own
     * table/column caches are shared across detection, missing-desc and
     * build_categories within one request.
     */
    private function get_aioseo_migrator() {
        if ( $this->aioseo_migrator === null ) {
            if ( ! class_exists( 'TamRank_AIOSEO_Migrator' ) ) {
                require_once TAM_RANK_PLUGIN_PATH . 'includes/onboarding/class-tamrank-aioseo-migrator.php';
            }
            $this->aioseo_migrator = new TamRank_AIOSEO_Migrator();
        }
        return $this->aioseo_migrator;
    }

    /**
     * Check if a plugin is active (works without including plugin.php).
     */
    private function is_plugin_active( $plugin ) {
        if ( function_exists( 'is_plugin_active' ) ) {
            return is_plugin_active( $plugin );
        }
        $active = (array) get_option( 'active_plugins', array() );
        return in_array( $plugin, $active, true );
    }

    /* ------------------------------------------------------------------
     * SEO Quick Scan
     * ----------------------------------------------------------------*/

    /**
     * Run a quick SEO scan of the site.
     *
     * Returns statistics and migration category counts.
     * Results are cached in a per-source transient.
     *
     * @param string|null $source Optional explicit source plugin
     *                            ('yoast'|'rankmath'|'aioseo'); null =
     *                            legacy first-match detection.
     * @return array
     */
    public function run_scan( $source = null ) {
        $cache_key = self::CACHE_KEY . ( $source ? '_' . $source : '' );

        $cached = get_transient( $cache_key );
        if ( $cached ) {
            return $cached;
        }

        global $wpdb;

        // Count published posts + pages
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- direct query on core wp_posts; admin-context onboarding read, caching not applicable
        $total_posts = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts}
             WHERE post_status = 'publish'
             AND post_type IN ('post', 'page')"
        );

        // Posts missing meta description (TamRank + source plugin)
        $missing_desc = $this->count_missing_meta_desc( $total_posts );

        // Duplicate titles
        $duplicate_titles = $this->count_duplicate_titles();

        // Schema coverage percentage
        $schema_pct = $this->get_schema_percentage( $total_posts );

        // Build migration categories based on the requested source, or the
        // first detected plugin (legacy path) when no source was given.
        $detection = null;
        if ( $source ) {
            foreach ( $this->detect_all_seo_plugins() as $candidate ) {
                if ( $candidate['plugin'] === $source ) {
                    $detection = $candidate;
                    break;
                }
            }
            if ( ! $detection ) {
                // Fail-closed: requested source has no data → no categories.
                $detection = array( 'plugin' => null, 'plugin_name' => '', 'data_count' => 0 );
            }
        } else {
            $detection = $this->detect_seo_plugin();
        }
        $categories = $this->build_categories( $detection );

        $result = array(
            'total_posts'          => $total_posts,
            'missing_desc'         => $missing_desc,
            'duplicate_titles'     => $duplicate_titles,
            'schema_pct'           => $schema_pct,
            'categories'           => $categories,
            // Redirects the wizard deliberately leaves behind. Not importing them
            // stays as it is; saying nothing about them does not.
            'unmigratable_redirects' => $this->count_unmigratable_redirects( $detection['plugin'] ),
            // Redirects the wizard DOES import that hand traffic to another
            // domain. Performed exactly as before; only now visible.
            'external_redirect_targets' => $this->count_external_redirect_targets( $detection['plugin'] ),
        );

        set_transient( $cache_key, $result, self::CACHE_TTL );

        return $result;
    }

    /**
     * Count posts without any meta description.
     *
     * When the AIOSEO table exists, its description column counts as
     * "has a description" too — otherwise the urgency stat overcounts
     * on AIOSEO sites (descriptions live in aioseo_posts, not postmeta).
     * On sites without the table the original query runs byte-identical.
     */
    private function count_missing_meta_desc( $total_posts ) {
        global $wpdb;

        // Posts that have a meta description in TamRank or source plugin
        $has_desc_sql =
            "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
             WHERE p.post_status = 'publish'
             AND p.post_type IN ('post', 'page')
             AND pm.meta_key IN (
                '_tam_rank_meta_description',
                '_yoast_wpseo_metadesc',
                'rank_math_description'
             )
             AND pm.meta_value != ''";

        $migrator = $this->get_aioseo_migrator();
        if ( $migrator->table_exists() ) {
            $has_desc_sql =
                "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
                 LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                    AND pm.meta_key IN (
                        '_tam_rank_meta_description',
                        '_yoast_wpseo_metadesc',
                        'rank_math_description'
                    )
                    AND pm.meta_value != ''
                 LEFT JOIN {$wpdb->prefix}aioseo_posts ap ON p.ID = ap.post_id
                    AND ap.description IS NOT NULL AND ap.description <> ''
                 WHERE p.post_status = 'publish'
                 AND p.post_type IN ('post', 'page')
                 AND ( pm.post_id IS NOT NULL OR ap.post_id IS NOT NULL )";
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared -- direct query on core wp_posts/wp_postmeta (+ third-party aioseo_posts when present); only table identifiers interpolated; admin-context onboarding read, caching not applicable
        $has_desc = (int) $wpdb->get_var( $has_desc_sql );

        return max( 0, $total_posts - $has_desc );
    }

    /**
     * Count posts with duplicate meta titles.
     */
    private function count_duplicate_titles() {
        global $wpdb;

        // Check TamRank titles first, then post_title as fallback
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- direct query on core wp_posts; admin-context onboarding read, caching not applicable
        $duplicates = (int) $wpdb->get_var(
            "SELECT SUM(cnt) FROM (
                SELECT COUNT(*) - 1 AS cnt
                FROM {$wpdb->posts} p
                WHERE p.post_status = 'publish'
                AND p.post_type IN ('post', 'page')
                AND p.post_title != ''
                GROUP BY p.post_title
                HAVING COUNT(*) > 1
             ) AS dupes"
        );

        return max( 0, $duplicates );
    }

    /**
     * Get percentage of posts with schema markup enabled.
     *
     * Mirrors the render logic in class-schema-render.php::is_schema_enabled():
     * schema defaults to ON when the meta is missing, and is only OFF when the
     * user explicitly disables it (`tamrank_schema_enabled = '0'` or
     * `tamrank_schema_bron = 'geen'`). Counting only posts with
     * `meta_value = '1'` produced 0% on every fresh install because the default
     * path leaves the meta unset.
     */
    private function get_schema_percentage( $total_posts ) {
        if ( $total_posts === 0 ) {
            return 0;
        }

        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- direct query on core wp_posts/wp_postmeta; admin-context onboarding read, caching not applicable
        $schema_count = (int) $wpdb->get_var(
            "SELECT COUNT(p.ID)
             FROM {$wpdb->posts} p
             LEFT JOIN {$wpdb->postmeta} pm_en
                    ON pm_en.post_id = p.ID
                   AND pm_en.meta_key = 'tamrank_schema_enabled'
             LEFT JOIN {$wpdb->postmeta} pm_br
                    ON pm_br.post_id = p.ID
                   AND pm_br.meta_key = 'tamrank_schema_bron'
             WHERE p.post_status = 'publish'
               AND p.post_type IN ('post','page')
               AND ( pm_en.meta_value IS NULL OR pm_en.meta_value <> '0' )
               AND ( pm_br.meta_value IS NULL OR pm_br.meta_value <> 'geen' )"
        );

        return min( 100, round( ( $schema_count / $total_posts ) * 100 ) );
    }

    /**
     * Build selectable migration categories with counts.
     */
    private function build_categories( $detection ) {
        if ( ! $detection['plugin'] ) {
            return array();
        }

        global $wpdb;

        $categories = array();

        if ( $detection['plugin'] === 'yoast' ) {
            $categories[] = $this->count_category(
                'meta_titles',
                __( 'Meta titles', 'tam-rank' ),
                '_yoast_wpseo_title'
            );
            $categories[] = $this->count_category(
                'meta_descriptions',
                __( 'Meta descriptions', 'tam-rank' ),
                '_yoast_wpseo_metadesc'
            );
            $categories[] = $this->count_category(
                'focus_keywords',
                __( 'Focus keywords', 'tam-rank' ),
                '_yoast_wpseo_focuskw'
            );
            // Yoast writes '1' for on and '2' for "use the global default", and it
            // keeps noindex and nofollow in two separate keys. Counting
            // "noindex is not empty" therefore counted a post whose robots are on
            // the default (migrates nothing) and missed a nofollow-only post
            // (migrates something). Same set the migrator writes for, see
            // TamRank_Yoast_Migrator::migrate_robots().
            $categories[] = $this->count_category_where(
                'robots',
                __( 'Robots (noindex/nofollow)', 'tam-rank' ),
                array(
                    array( 'key' => '_yoast_wpseo_meta-robots-noindex', 'value' => '1' ),
                    array( 'key' => '_yoast_wpseo_meta-robots-nofollow', 'value' => '1' ),
                )
            );
            $categories[] = $this->count_category(
                'canonical',
                __( 'Canonical URLs', 'tam-rank' ),
                '_yoast_wpseo_canonical'
            );
            $categories[] = $this->count_category(
                'social',
                __( 'Social media (OG)', 'tam-rank' ),
                '_yoast_wpseo_opengraph-title'
            );
            // A post carries its schema type in article_type, a page in page_type,
            // and TamRank_Yoast_Migrator::migrate_schema() migrates either.
            // Counting page_type alone left every post with only an article type
            // out of the plan.
            $categories[] = $this->count_category_where(
                'schema',
                __( 'Schema markup', 'tam-rank' ),
                array(
                    array( 'key' => '_yoast_wpseo_schema_page_type' ),
                    array( 'key' => '_yoast_wpseo_schema_article_type' ),
                )
            );
            $redirects_count = $this->count_redirects( $detection['plugin'] );
            if ( $redirects_count > 0 ) {
                $categories[] = array(
                    'key'   => 'redirects',
                    'label' => __( 'Redirects', 'tam-rank' ),
                    'count' => $redirects_count,
                );
            }
        } elseif ( $detection['plugin'] === 'rankmath' ) {
            $categories[] = $this->count_category(
                'meta_titles',
                __( 'Meta titles', 'tam-rank' ),
                'rank_math_title'
            );
            $categories[] = $this->count_category(
                'meta_descriptions',
                __( 'Meta descriptions', 'tam-rank' ),
                'rank_math_description'
            );
            $categories[] = $this->count_category(
                'focus_keywords',
                __( 'Focus keywords', 'tam-rank' ),
                'rank_math_focus_keyword'
            );
            // Rank Math stores a serialised list, and 'index'/'follow' are in it
            // on every post it has ever saved. Only 'noindex' and 'nofollow'
            // migrate (TamRank_RankMath_Migrator::migrate_robots()), so those are
            // what the card counts - quoted, so noimageindex cannot match.
            $categories[] = $this->count_category_where(
                'robots',
                __( 'Robots (noindex/nofollow)', 'tam-rank' ),
                array(
                    array( 'key' => 'rank_math_robots', 'contains' => '"noindex"' ),
                    array( 'key' => 'rank_math_robots', 'contains' => '"nofollow"' ),
                )
            );
            $categories[] = $this->count_category(
                'canonical',
                __( 'Canonical URLs', 'tam-rank' ),
                'rank_math_canonical_url'
            );
            $categories[] = $this->count_category(
                'social',
                __( 'Social media (OG)', 'tam-rank' ),
                'rank_math_facebook_title'
            );
            // 'off' is Rank Math's explicit "no schema here" and migrates nothing
            // (TamRank_RankMath_Migrator::migrate_schema() returns early on it).
            $categories[] = $this->count_category_where(
                'schema',
                __( 'Schema markup', 'tam-rank' ),
                array(
                    array( 'key' => 'rank_math_rich_snippet', 'not_value' => 'off' ),
                )
            );
            $redirects_count = $this->count_redirects( $detection['plugin'] );
            if ( $redirects_count > 0 ) {
                $categories[] = array(
                    'key'   => 'redirects',
                    'label' => __( 'Redirects', 'tam-rank' ),
                    'count' => $redirects_count,
                );
            }
        } elseif ( $detection['plugin'] === 'aioseo' ) {
            // AIOSEO stores everything in its own table — counts come from
            // the migrator so the card count equals the batch candidate set
            // (single source of truth: get_category_predicate()). Labels
            // reuse the exact __() literals above, so no new i18n keys.
            // No redirects category: AIOSEO Lite has no redirects table.
            $migrator = $this->get_aioseo_migrator();

            $aioseo_categories = array(
                'meta_titles'       => __( 'Meta titles', 'tam-rank' ),
                'meta_descriptions' => __( 'Meta descriptions', 'tam-rank' ),
                'focus_keywords'    => __( 'Focus keywords', 'tam-rank' ),
                'robots'            => __( 'Robots (noindex/nofollow)', 'tam-rank' ),
                'canonical'         => __( 'Canonical URLs', 'tam-rank' ),
                'social'            => __( 'Social media (OG)', 'tam-rank' ),
                'schema'            => __( 'Schema markup', 'tam-rank' ),
            );

            foreach ( $aioseo_categories as $key => $label ) {
                $categories[] = array(
                    'key'   => $key,
                    'label' => $label,
                    'count' => $migrator->count_category( $key ),
                );
            }
        }

        // robots.txt, the sitemap configuration and the site identity are site-wide,
        // not post-based, so they get their counts from the settings migrator instead
        // of a postmeta count. The redirects category is the existing precedent for a
        // non-post category.
        $settings_labels = array(
            'robots_txt'     => __( 'Robots.txt', 'tam-rank' ),
            'sitemap_config' => __( 'Sitemap settings', 'tam-rank' ),
            'site_identity'  => __( 'Brand details', 'tam-rank' ),
        );
        foreach ( $this->count_settings_categories( $detection['plugin'] ) as $key => $count ) {
            if ( ! isset( $settings_labels[ $key ] ) ) {
                continue;
            }
            $categories[] = array(
                'key'   => $key,
                'label' => $settings_labels[ $key ],
                'count' => $count,
            );
        }

        // Filter out categories with 0 items
        return array_values( array_filter( $categories, function ( $cat ) {
            return $cat['count'] > 0;
        } ) );
    }

    /**
     * Counts for the robots.txt and sitemap-settings cards.
     *
     * robots.txt counts as one item when there is anything to import at all; the
     * sitemap count is the number of exclusions that would carry over, plus one when
     * the source has its sitemap switched off entirely; the identity count is the
     * number of brand fields the source really carries. All three read options only -
     * no loopback request on a scan.
     *
     * @param string $plugin Source plugin slug.
     * @return array
     */
    private function count_settings_categories( $plugin ) {
        if ( ! class_exists( 'TamRank_Settings_Migrator' ) ) {
            require_once TAM_RANK_PLUGIN_PATH . 'includes/onboarding/class-tamrank-settings-migrator.php';
        }

        $migrator = new TamRank_Settings_Migrator( $plugin );

        return $migrator->category_counts();
    }

    /**
     * Posts carrying a value in any of the given meta keys.
     *
     * "Carries data", not "would migrate": this feeds the detection card, whose
     * sentence is "N items with SEO data". A post whose Yoast robots sit on the
     * default does carry a Yoast value, it just has nothing to hand over - the
     * moving-plan cards are the ones that answer that second question.
     *
     * @param string[] $meta_keys
     * @return int
     */
    private function count_posts_with_any_meta( array $meta_keys ) {
        global $wpdb;

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

        $placeholders = implode( ', ', array_fill( 0, count( $meta_keys ), '%s' ) );

        // phpcs:disable WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- only table identifiers and a runtime-built %s,%s placeholder list are interpolated; every value goes through prepare(); admin-context onboarding read, caching not applicable
        return (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT post_id) FROM {$wpdb->postmeta}
             WHERE meta_key IN ($placeholders) AND meta_value != ''",
            ...$meta_keys
        ) );
        // phpcs:enable WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Count a migration category from a set of OR-ed meta conditions.
     *
     * A card is a promise about what the migration will move, so it has to count
     * the same posts the migrator writes for - not merely the posts that have the
     * key. Each condition is one of:
     *
     *   array( 'key' => k )                     key present and not empty
     *   array( 'key' => k, 'value' => v )       key exactly v
     *   array( 'key' => k, 'not_value' => v )   key present, not empty and not v
     *   array( 'key' => k, 'contains' => str )  key contains str (serialised lists)
     *
     * @param string $key         Category key.
     * @param string $label       Translated label.
     * @param array  $conditions  OR-ed conditions.
     * @return array { @type string $key, @type string $label, @type int $count }
     */
    private function count_category_where( $key, $label, array $conditions ) {
        global $wpdb;

        $clauses = array();
        $params  = array();

        foreach ( $conditions as $condition ) {
            if ( empty( $condition['key'] ) ) {
                continue;
            }
            if ( isset( $condition['value'] ) ) {
                $clauses[] = '( meta_key = %s AND meta_value = %s )';
                $params[]  = $condition['key'];
                $params[]  = $condition['value'];
            } elseif ( isset( $condition['not_value'] ) ) {
                $clauses[] = "( meta_key = %s AND meta_value != '' AND meta_value <> %s )";
                $params[]  = $condition['key'];
                $params[]  = $condition['not_value'];
            } elseif ( isset( $condition['contains'] ) ) {
                $clauses[] = '( meta_key = %s AND meta_value LIKE %s )';
                $params[]  = $condition['key'];
                $params[]  = '%' . $wpdb->esc_like( $condition['contains'] ) . '%';
            } else {
                $clauses[] = "( meta_key = %s AND meta_value != '' )";
                $params[]  = $condition['key'];
            }
        }

        if ( empty( $clauses ) ) {
            return array( 'key' => $key, 'label' => $label, 'count' => 0 );
        }

        $where = implode( ' OR ', $clauses );

        // phpcs:disable WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- only the table identifier and the %s-placeholder clauses built above are interpolated; every value goes through prepare(); admin-context onboarding read, caching not applicable
        $count = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT post_id) FROM {$wpdb->postmeta} WHERE $where",
            ...$params
        ) );
        // phpcs:enable WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return array(
            'key'   => $key,
            'label' => $label,
            'count' => $count,
        );
    }

    /**
     * Count items for a migration category.
     */
    private function count_category( $key, $label, $meta_key ) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- direct query on core wp_postmeta; admin-context onboarding read, caching not applicable
        $count = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT post_id) FROM {$wpdb->postmeta}
             WHERE meta_key = %s AND meta_value != ''",
            $meta_key
        ) );

        return array(
            'key'   => $key,
            'label' => $label,
            'count' => $count,
        );
    }

    /**
     * Count importable redirects for the detected plugin.
     *
     * Two steps, and both are needed for the card to show what the import
     * really does. The migrator says which entries are importable at all —
     * regex, inactive and unsupported entries are already dropped there. The
     * redirect manager then says how many of those it can actually create: a
     * source already in the table keeps its existing rule (an import never
     * overwrites), so it is counted nowhere.
     *
     * Counting the migrator's list alone is what made the wizard promise 6 and
     * deliver 4 on a table that already had rules. On an empty table the two
     * numbers are equal by definition, which is why it stayed hidden.
     */
    /**
     * Redirects the source holds that this migration will not bring along.
     *
     * Only All in One SEO has such a set today: its redirects live in an
     * add-on table that AIOSEO Lite does not even create, so there is no
     * redirects category and the wizard used to fall completely silent about
     * them. Someone moving over from AIOSEO Pro then loses his redirects
     * without ever having been told. Leaving them behind stays the design; the
     * number is what the wizard owes the user.
     *
     * Yoast and Rank Math need nothing here: their unimportable entries are
     * already reported per run ("N imported, M skipped").
     *
     * @param string|null $plugin Source plugin slug.
     * @return int
     */
    private function count_unmigratable_redirects( $plugin ) {
        if ( 'aioseo' !== $plugin ) {
            return 0;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'aioseo_redirects';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- existence check for a third-party add-on table; admin-context onboarding read, caching not applicable
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
            return 0;
        }

        // phpcs:disable WordPress.DB.PreparedSQL, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- only the table identifier is interpolated, no user values; admin-context onboarding read, caching not applicable
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aioseo_redirects" );
        // phpcs:enable WordPress.DB.PreparedSQL, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Of the redirects this import creates, how many leave this site?
     *
     * Reads the same migrator list count_redirects() reads and asks the redirect
     * manager the same "would this really be created" question, so the number is
     * a subset of the Redirects category shown next to it and never contradicts
     * it. An install without the manager class reports nothing rather than a
     * number it cannot stand behind - the same defensive seam count_redirects()
     * uses.
     *
     * @param string|null $plugin Source plugin slug.
     * @return int
     */
    private function count_external_redirect_targets( $plugin ) {
        $migrator = $this->get_redirect_migrator( $plugin );

        if ( ! $migrator || ! class_exists( 'TamRank_Redirect_Manager' ) ) {
            return 0;
        }

        $result = $migrator->get_redirects_to_import();

        return TamRank_Redirect_Manager::count_external_targets( $result['import'] );
    }

    /**
     * The migrator that owns the redirect list for this source, if any.
     *
     * Split out of count_redirects() so the row count and the external-target
     * count read the very same list from the very same object.
     *
     * @param string|null $plugin Source plugin slug.
     * @return object|null
     */
    private function get_redirect_migrator( $plugin ) {
        if ( $plugin === 'yoast' ) {
            if ( ! class_exists( 'TamRank_Yoast_Migrator' ) ) {
                require_once TAM_RANK_PLUGIN_PATH . 'includes/onboarding/class-tamrank-yoast-migrator.php';
            }
            return new TamRank_Yoast_Migrator();
        }

        if ( $plugin === 'rankmath' ) {
            if ( ! class_exists( 'TamRank_RankMath_Migrator' ) ) {
                require_once TAM_RANK_PLUGIN_PATH . 'includes/onboarding/class-tamrank-rankmath-migrator.php';
            }
            return new TamRank_RankMath_Migrator();
        }

        return null;
    }

    private function count_redirects( $plugin ) {
        if ( $plugin === 'yoast' ) {
            if ( ! class_exists( 'TamRank_Yoast_Migrator' ) ) {
                require_once TAM_RANK_PLUGIN_PATH . 'includes/onboarding/class-tamrank-yoast-migrator.php';
            }
            $migrator = new TamRank_Yoast_Migrator();
        } elseif ( $plugin === 'rankmath' ) {
            if ( ! class_exists( 'TamRank_RankMath_Migrator' ) ) {
                require_once TAM_RANK_PLUGIN_PATH . 'includes/onboarding/class-tamrank-rankmath-migrator.php';
            }
            $migrator = new TamRank_RankMath_Migrator();
        } else {
            return 0;
        }

        $result = $migrator->get_redirects_to_import();

        if ( ! class_exists( 'TamRank_Redirect_Manager' ) ) {
            return count( $result['import'] );
        }

        return TamRank_Redirect_Manager::count_creatable_redirects( $result['import'] );
    }
}
