<?php

namespace WPSN\Modules\Audit;

use WPSN\Core\Logger;
use WPSN\Core\Settings;
use WPSN\SEO\MetaKeys;

defined('ABSPATH') || exit;

/**
 * Audit SQL Scan
 *
 * A fast “DB-only” scan mode that avoids the queue/cron worker.
 *
 * Why:
 * - On some shared hosts admin-ajax/cron is rate-limited and returns 503.
 * - This scan runs as a single admin-post request and relies mostly on SQL.
 *
 * Scope (intentionally limited):
 * - Missing title/description (based on overrides + core fields, with awareness of blank global templates)
 * - Duplicate title/description (based on overrides + core fields)
 * - Noindex overrides
 * - Canonical override: invalid / external
 * - Social image missing (OG/Twitter overrides + featured image)
 */
final class AuditSqlScanner
{
    private AuditIssueRepository $issues;
    private AuditScanRepository $scans;
    private AuditScanIssueRepository $scanIssues;
    private Settings $settings;
    private Logger $logger;

    public function __construct(
        AuditIssueRepository $issues,
        AuditScanRepository $scans,
        AuditScanIssueRepository $scanIssues,
        Settings $settings,
        Logger $logger
    ) {
        $this->issues = $issues;
        $this->scans = $scans;
        $this->scanIssues = $scanIssues;
        $this->settings = $settings;
        $this->logger = $logger;
    }

    /**
     * Derive lightweight template flags used by SQL-mode missing-meta checks.
     *
     * SQL mode does not render the full template engine for every object, but we still
     * need to detect the most common failure mode: a blank or separator-only global
     * title/description template. In that case a page can ship an empty title/description
     * even when the raw post/term title exists.
     *
     * @param array{title?:mixed,description?:mixed} $templates
     * @return array{title_blank:bool,description_blank:bool}
     */
    public static function templateProfile(array $templates = []): array
    {
        $title = isset($templates['title']) ? (string) $templates['title'] : '';
        $description = isset($templates['description']) ? (string) $templates['description'] : '';

        return [
            'title_blank' => AuditMetaSignal::isEffectivelyBlankTemplate($title),
            'description_blank' => AuditMetaSignal::isEffectivelyBlankTemplate($description),
        ];
    }

    /**
     * SQL quick-scan applies quality signals conservatively.
     *
     * Because SQL mode does not render the full template engine per object,
     * these checks are used only for explicit meta overrides (not template-derived output).
     *
     * @return array{min:int,max:int}
     */
    public static function metaLengthThresholds(string $field): array
    {
        if ($field === 'description') {
            $min = max(0, min(300, (int) apply_filters('wpsn/audit/description_min_len', 50)));
            $max = max(10, min(400, (int) apply_filters('wpsn/audit/description_max_len', 160)));
            if ($max < $min) {
                $max = $min;
            }

            return [
                'min' => $min,
                'max' => $max,
            ];
        }

        $min = max(0, min(120, (int) apply_filters('wpsn/audit/title_min_len', 20)));
        $max = max(10, min(200, (int) apply_filters('wpsn/audit/title_max_len', 60)));
        if ($max < $min) {
            $max = $min;
        }

        return [
            'min' => $min,
            'max' => $max,
        ];
    }

    /**
     * Build quality issue descriptors for an explicit SQL-mode meta override.
     *
     * @return array<int,array{issue_code:string,severity:int,message:string,suggestion:string,details:array<string,mixed>}>
     */
    public static function qualitySignalsForExplicitMeta(string $field, string $value): array
    {
        $field = $field === 'description' ? 'description' : 'title';
        $detailsKey = $field === 'title' ? 'rendered_title' : 'rendered_description';

        $info = AuditMetaSignal::analyzeRenderedMeta($value);
        if (!empty($info['blank'])) {
            $reason = (string) ($info['reason'] ?? AuditMetaSignal::REASON_BLANK);
            $isTitle = $field === 'title';

            return [[
                'issue_code' => $isTitle ? AuditRunner::ISSUE_MISSING_TITLE : AuditRunner::ISSUE_MISSING_DESCRIPTION,
                'severity' => $isTitle ? AuditRunner::SEVERITY_HIGH : AuditRunner::SEVERITY_MEDIUM,
                'message' => $reason === AuditMetaSignal::REASON_BLANK
                    ? ($isTitle ? __('Missing SEO title', 'wp-seo-next') : __('Missing meta description', 'wp-seo-next'))
                    : ($isTitle ? __('SEO title renders as empty or broken', 'wp-seo-next') : __('Meta description renders as empty or broken', 'wp-seo-next')),
                'suggestion' => $isTitle
                    ? __('Fix the SEO title override or adjust the global title template.', 'wp-seo-next')
                    : __('Fix the meta description override or adjust the description template.', 'wp-seo-next'),
                'details' => [
                    'reason' => $reason,
                    $detailsKey => (string) ($info['normalized'] ?? ''),
                ],
            ]];
        }

        $thresholds = self::metaLengthThresholds($field);
        $quality = AuditMetaSignal::classifyRenderedLength($value, (int) $thresholds['min'], (int) $thresholds['max']);
        $normalized = (string) ($quality['normalized'] ?? '');
        $length = (int) ($quality['length'] ?? 0);
        $isTitle = $field === 'title';

        if (!empty($quality['has_placeholder'])) {
            return [[
                'issue_code' => $isTitle ? AuditRunner::ISSUE_TITLE_TEMPLATE_BROKEN : AuditRunner::ISSUE_DESCRIPTION_TEMPLATE_BROKEN,
                'severity' => $isTitle ? AuditRunner::SEVERITY_HIGH : AuditRunner::SEVERITY_MEDIUM,
                'message' => $isTitle
                    ? __('SEO title contains unresolved template tokens', 'wp-seo-next')
                    : __('Meta description contains unresolved template tokens', 'wp-seo-next'),
                'suggestion' => $isTitle
                    ? __('Fix the SEO title override or remove unknown placeholders.', 'wp-seo-next')
                    : __('Fix the meta description override or remove unknown placeholders.', 'wp-seo-next'),
                'details' => [
                    'reason' => 'unresolved_placeholder',
                    $detailsKey => $normalized,
                    'length' => $length,
                ],
            ]];
        }

        $status = (string) ($quality['status'] ?? AuditMetaSignal::LENGTH_OK);
        if ($status === AuditMetaSignal::LENGTH_TOO_SHORT) {
            return [[
                'issue_code' => $isTitle ? AuditRunner::ISSUE_TITLE_TOO_SHORT : AuditRunner::ISSUE_DESCRIPTION_TOO_SHORT,
                'severity' => AuditRunner::SEVERITY_LOW,
                'message' => $isTitle ? __('SEO title is too short', 'wp-seo-next') : __('Meta description is too short', 'wp-seo-next'),
                'suggestion' => $isTitle
                    ? __('Make the title override more descriptive so search results are clearer.', 'wp-seo-next')
                    : __('Expand the description override so it explains the page better in search results.', 'wp-seo-next'),
                'details' => [
                    $detailsKey => $normalized,
                    'length' => $length,
                    'min' => (int) $thresholds['min'],
                    'max' => (int) $thresholds['max'],
                ],
            ]];
        }

        if ($status === AuditMetaSignal::LENGTH_TOO_LONG) {
            return [[
                'issue_code' => $isTitle ? AuditRunner::ISSUE_TITLE_TOO_LONG : AuditRunner::ISSUE_DESCRIPTION_TOO_LONG,
                'severity' => AuditRunner::SEVERITY_LOW,
                'message' => $isTitle ? __('SEO title is too long', 'wp-seo-next') : __('Meta description is too long', 'wp-seo-next'),
                'suggestion' => $isTitle
                    ? __('Shorten the title override so it is less likely to truncate in search results.', 'wp-seo-next')
                    : __('Shorten the description override so it is less likely to truncate in search results.', 'wp-seo-next'),
                'details' => [
                    $detailsKey => $normalized,
                    'length' => $length,
                    'min' => (int) $thresholds['min'],
                    'max' => (int) $thresholds['max'],
                ],
            ]];
        }

        return [];
    }

    /**
     * Compute derived SQL-scan options in a testable way.
     *
     * The main run() method is intentionally heavy and touches DB. This helper
     * isolates the decision logic (presets, clamping) so we can unit-test it.
     *
     * @param array<string,mixed> $options
     * @return array<string,mixed>
     */
    public static function plan(bool $includeTerms = true, bool $checkSocialImage = true, array $options = []): array
    {
        $ultraFast = !empty($options['ultra_fast']) || !empty($options['ultra_fast_minimal']) || !empty($options['minimal']);
        $skipDuplicates = $ultraFast || !empty($options['skip_duplicates']);
        $skipSocial = $ultraFast || !empty($options['skip_social']);
        $skipMetaQuality = $ultraFast || !empty($options['skip_meta_quality']);

        // Ultra-fast preset disables social image checks.
        if ($skipSocial) {
            $checkSocialImage = false;
        }

        // Refuse extremely large sites in sync mode.
        $hardLimit = isset($options['hard_limit_objects'])
            ? (int) $options['hard_limit_objects']
            : (int) apply_filters('wpsn/audit_sql/hard_limit_objects', 200000);
        $hardLimit = max(1000, min(200000, $hardLimit));

        // Temp cache thresholds (posts/terms).
        $minTmpPosts = isset($options['tmp_cache_min_posts'])
            ? (int) $options['tmp_cache_min_posts']
            : (int) apply_filters('wpsn/audit_sql/tmp_cache_min_posts', 200);
        $minTmpPosts = max(0, $minTmpPosts);

        $minTmpTerms = isset($options['tmp_cache_min_terms'])
            ? (int) $options['tmp_cache_min_terms']
            : (int) apply_filters('wpsn/audit_sql/tmp_cache_min_terms', 300);
        $minTmpTerms = max(0, $minTmpTerms);

        return [
            'include_terms' => (bool) $includeTerms,
            'check_social_image' => (bool) $checkSocialImage,
            'ultra_fast' => (bool) $ultraFast,
            'skip_duplicates' => (bool) $skipDuplicates,
            'skip_social' => (bool) $skipSocial,
            'skip_meta_quality' => (bool) $skipMetaQuality,
            'hard_limit_objects' => (int) $hardLimit,
            'tmp_cache_min_posts' => (int) $minTmpPosts,
            'tmp_cache_min_terms' => (int) $minTmpTerms,
        ];
    }

    /**
     * @param array<string,mixed> $options
     * @return array<string,mixed> scan summary
     */
    public function run(bool $includeTerms = true, bool $checkSocialImage = true, array $options = []): array
    {
        AuditInstaller::maybeInstall();

        if (function_exists('wp_raise_memory_limit')) {
            wp_raise_memory_limit('admin');
        }
        if (function_exists('set_time_limit')) {
            // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged
            @set_time_limit(0);
        }

        $startedAtTs = time();
        $startedAtMicro = microtime(true);

        $plan = self::plan($includeTerms, $checkSocialImage, $options);

        $includeTerms = (bool) ($plan['include_terms'] ?? $includeTerms);
        $checkSocialImage = (bool) ($plan['check_social_image'] ?? $checkSocialImage);

        $ultraFast = (bool) ($plan['ultra_fast'] ?? false);
        $skipDuplicates = (bool) ($plan['skip_duplicates'] ?? false);
        $skipSocial = (bool) ($plan['skip_social'] ?? false);
        $skipMetaQuality = (bool) ($plan['skip_meta_quality'] ?? false);
        $scanId = $this->scans->createScan($includeTerms, $checkSocialImage);
        if ($scanId <= 0) {
            throw new \RuntimeException(__('Failed to start scan (DB error).', 'wp-seo-next'));
        }

        $templateProfile = self::templateProfile([
            'title' => (string) $this->settings->get('templates.title', '%title% %sep% %sitename%'),
            'description' => (string) $this->settings->get('templates.description', '%excerpt|strip|truncate(160)%'),
        ]);
        $titleTemplateBlank = !empty($templateProfile['title_blank']);
        $descriptionTemplateBlank = !empty($templateProfile['description_blank']);


        $postTypes = $this->publicPostTypes();
        $taxonomies = $includeTerms ? $this->publicTaxonomies() : [];

        $totalPosts = $this->countPosts($postTypes);
        $totalTerms = $includeTerms ? $this->countTerms($taxonomies) : 0;

        // Refuse extremely large sites in sync mode.
        $hardLimit = (int) ($plan['hard_limit_objects'] ?? 200000);
        if (($totalPosts + $totalTerms) > $hardLimit) {
            $this->scans->finishScan(
                $scanId,
                [
                    'duration_sec' => 0,
                    'total_posts' => $totalPosts,
                    'total_terms' => $totalTerms,
                    'scanned_posts' => 0,
                    'scanned_terms' => 0,
                    'issues_total' => 0,
                    'issues_by_code' => [],
                ],
                'failed',
                sprintf(
                    /* translators: %d objects */
                    __('SQL scan is disabled on large sites (%d objects). Use the async scan instead.', 'wp-seo-next'),
                    (int) ($totalPosts + $totalTerms)
                )
            );
            throw new \RuntimeException(__('SQL scan is disabled on large sites. Use the async scan instead.', 'wp-seo-next'));
        }

        // Store totals + frozen config (re-using async init schema).
        $config = [
            'mode' => 'sql',
            'sql_version' => 3,
            'sql_preset' => $ultraFast ? 'ultra_fast_minimal' : 'default',
            'sql_skip_duplicates' => (bool) $skipDuplicates,
            'sql_skip_social' => (bool) $skipSocial,
            'sql_skip_meta_quality' => (bool) $skipMetaQuality,
            'sql_title_template_blank' => $titleTemplateBlank,
            'sql_description_template_blank' => $descriptionTemplateBlank,
            'note' => $ultraFast
                ? 'DB-only quick scan (minimal checks).'
                : 'DB-only quick scan (heuristic meta checks + explicit override quality signals; no full template/head rendering).',
        ];
        $this->scans->initAsyncScan($scanId, $totalPosts, $totalTerms, $config);
        $this->scans->setPhase($scanId, 'sql');

        $now = current_time('mysql');

        // Site URL helpers for links and internal/external canonical detection.
        $homeBase = rtrim((string) home_url(), '/');
        $homeBaseSql = esc_sql($homeBase);
        $homeForP = rtrim((string) home_url('/'), '/') . '/';
        $homeForPSql = esc_sql($homeForP);

        // Messages (localized) - embedded into SQL.
        $msgMissingTitle = esc_sql(__('Missing SEO title', 'wp-seo-next'));
        $sugMissingTitle = esc_sql(__('Set an SEO title override or adjust the global title template.', 'wp-seo-next'));

        $msgMissingDesc = esc_sql(__('Missing meta description', 'wp-seo-next'));
        $sugMissingDesc = esc_sql(__('Add a meta description (or excerpt) so search results look better.', 'wp-seo-next'));

        $msgNoindex = esc_sql(__('Noindex on a public page', 'wp-seo-next'));
        $sugNoindex = esc_sql(__('If this page should rank, set Robots to Index (remove noindex).', 'wp-seo-next'));

        $msgCanonInvalid = esc_sql(__('Invalid canonical URL', 'wp-seo-next'));
        $sugCanonInvalid = esc_sql(__('Fix the canonical field (it must be a valid URL or a site-relative path).', 'wp-seo-next'));

        $msgCanonExternal = esc_sql(__('Canonical points to an external domain', 'wp-seo-next'));
        $sugCanonExternal = esc_sql(__('If this is not intentional, remove the canonical override or point it to the correct URL.', 'wp-seo-next'));

        $msgDupeTitle = esc_sql(__('Duplicate SEO title', 'wp-seo-next'));
        $sugDupeTitle = esc_sql(__('Make titles unique to avoid keyword cannibalization.', 'wp-seo-next'));

        $msgDupeDesc = esc_sql(__('Duplicate meta description', 'wp-seo-next'));
        $sugDupeDesc = esc_sql(__('Write unique descriptions so search snippets are more relevant.', 'wp-seo-next'));

        $msgSocialMissing = esc_sql(__('Missing social share image', 'wp-seo-next'));
        $sugSocialMissing = esc_sql(__('Set an OG/Twitter image or add a featured image so shares look better.', 'wp-seo-next'));

        // Execute SQL scan.
        //
        // Performance note:
        // On large sites, repeating N joins to wp_postmeta can be very slow.
        // We try to build a temporary per-post cache table once (meta overrides for a small set of keys)
        // and run most checks against it. If temp tables are not allowed (rare), we fall back to the
        // legacy join-based queries.
        $tmpPosts = '';
        $tmpTerms = '';
        try {
            $useTmp = (bool) apply_filters('wpsn/audit_sql/use_tmp_cache', true, $totalPosts, $scanId);
            // Default thresholds were intentionally conservative at first.
            // In real-world shared hosting, repeating joins against large wp_postmeta/wp_termmeta
            // can be much slower than building a small temporary cache table even on mid-size sites.
            // These defaults are tuned to make the SQL-only audit significantly faster for common sites.
            $minTmpPosts = (int) ($plan['tmp_cache_min_posts'] ?? 200);
            if ($useTmp && $totalPosts >= $minTmpPosts) {
                $tmpPosts = $this->buildTempPostCacheTable($scanId, $postTypes, $checkSocialImage);
            }

            // Terms temp cache (optional).
            $useTmpTerms = (bool) apply_filters('wpsn/audit_sql/use_tmp_cache_terms', true, $totalTerms, $scanId);
            $minTmpTerms = (int) ($plan['tmp_cache_min_terms'] ?? 300);
            if ($includeTerms && !empty($taxonomies) && $useTmpTerms && $totalTerms >= $minTmpTerms) {
                $tmpTerms = $this->buildTempTermCacheTable($scanId, $taxonomies);
            }

            if ($tmpPosts !== '') {
                $this->insertPostMissingTitleFromCache($scanId, $tmpPosts, $homeForPSql, $now, $msgMissingTitle, $sugMissingTitle, $titleTemplateBlank);
                $this->insertPostMissingDescriptionFromCache($scanId, $tmpPosts, $homeForPSql, $now, $msgMissingDesc, $sugMissingDesc, $descriptionTemplateBlank);
                $this->insertPostNoindexFromCache($scanId, $tmpPosts, $homeForPSql, $now, $msgNoindex, $sugNoindex);
                $this->insertPostCanonicalInvalidFromCache($scanId, $tmpPosts, $homeForPSql, $now, $msgCanonInvalid, $sugCanonInvalid);
                $this->insertPostCanonicalExternalFromCache($scanId, $tmpPosts, $homeForPSql, $homeBaseSql, $now, $msgCanonExternal, $sugCanonExternal);
                if (!$skipDuplicates) {
                    $this->insertPostDuplicateTitle($scanId, $postTypes, $homeForPSql, $now, $msgDupeTitle, $sugDupeTitle, $tmpPosts);
                    $this->insertPostDuplicateDescription($scanId, $postTypes, $homeForPSql, $now, $msgDupeDesc, $sugDupeDesc, $tmpPosts);
                }

                if ($checkSocialImage) {
                    $this->insertPostSocialMissingImageFromCache($scanId, $tmpPosts, $homeForPSql, $now, $msgSocialMissing, $sugSocialMissing);
                }
            } else {
                // Legacy join-based scan.
                $this->insertPostMissingTitle($scanId, $postTypes, $homeForPSql, $now, $msgMissingTitle, $sugMissingTitle, $titleTemplateBlank);
                $this->insertPostMissingDescription($scanId, $postTypes, $homeForPSql, $now, $msgMissingDesc, $sugMissingDesc, $descriptionTemplateBlank);
                $this->insertPostNoindex($scanId, $postTypes, $homeForPSql, $now, $msgNoindex, $sugNoindex);
                $this->insertPostCanonicalInvalid($scanId, $postTypes, $homeForPSql, $now, $msgCanonInvalid, $sugCanonInvalid);
                $this->insertPostCanonicalExternal($scanId, $postTypes, $homeForPSql, $homeBaseSql, $now, $msgCanonExternal, $sugCanonExternal);
                if (!$skipDuplicates) {
                    $this->insertPostDuplicateTitle($scanId, $postTypes, $homeForPSql, $now, $msgDupeTitle, $sugDupeTitle);
                    $this->insertPostDuplicateDescription($scanId, $postTypes, $homeForPSql, $now, $msgDupeDesc, $sugDupeDesc);
                }

                if ($checkSocialImage) {
                    $this->insertPostSocialMissingImage($scanId, $postTypes, $homeForPSql, $now, $msgSocialMissing, $sugSocialMissing);
                }
            }

            if (!$skipMetaQuality) {
                $this->insertExplicitMetaQualityIssues($scanId, $postTypes, $taxonomies, $now, $tmpPosts, $tmpTerms);
            }

            if ($includeTerms && !empty($taxonomies)) {
                if ($tmpTerms !== '') {
                    $this->insertTermMissingTitleFromCache($scanId, $tmpTerms, $now, $msgMissingTitle, $sugMissingTitle, $titleTemplateBlank);
                    $this->insertTermMissingDescriptionFromCache($scanId, $tmpTerms, $now, $msgMissingDesc, $sugMissingDesc, $descriptionTemplateBlank);
                    $this->insertTermNoindexFromCache($scanId, $tmpTerms, $now, $msgNoindex, $sugNoindex);
                    $this->insertTermCanonicalInvalidFromCache($scanId, $tmpTerms, $now, $msgCanonInvalid, $sugCanonInvalid);
                    $this->insertTermCanonicalExternalFromCache($scanId, $tmpTerms, $homeBaseSql, $now, $msgCanonExternal, $sugCanonExternal);
                    if (!$skipDuplicates) {
                        $this->insertTermDuplicateTitle($scanId, $taxonomies, $now, $msgDupeTitle, $sugDupeTitle, $tmpTerms);
                        $this->insertTermDuplicateDescription($scanId, $taxonomies, $now, $msgDupeDesc, $sugDupeDesc, $tmpTerms);
                    }
                } else {
                    $this->insertTermMissingTitle($scanId, $taxonomies, $now, $msgMissingTitle, $sugMissingTitle, $titleTemplateBlank);
                    $this->insertTermMissingDescription($scanId, $taxonomies, $now, $msgMissingDesc, $sugMissingDesc, $descriptionTemplateBlank);
                    $this->insertTermNoindex($scanId, $taxonomies, $now, $msgNoindex, $sugNoindex);
                    $this->insertTermCanonicalInvalid($scanId, $taxonomies, $now, $msgCanonInvalid, $sugCanonInvalid);
                    $this->insertTermCanonicalExternal($scanId, $taxonomies, $homeBaseSql, $now, $msgCanonExternal, $sugCanonExternal);
                    if (!$skipDuplicates) {
                        $this->insertTermDuplicateTitle($scanId, $taxonomies, $now, $msgDupeTitle, $sugDupeTitle);
                        $this->insertTermDuplicateDescription($scanId, $taxonomies, $now, $msgDupeDesc, $sugDupeDesc);
                    }
                }
            }
        } catch (\Throwable $e) {
            $this->logger->error('Audit SQL scan failed: ' . $e->getMessage());

            $this->scans->finishScan(
                $scanId,
                [
                    'duration_sec' => (int) max(0, round(microtime(true) - $startedAtMicro)),
                    'total_posts' => $totalPosts,
                    'total_terms' => $totalTerms,
                    'scanned_posts' => 0,
                    'scanned_terms' => 0,
                    'issues_total' => 0,
                    'issues_by_code' => [],
                ],
                'failed',
                $e->getMessage()
            );

            throw $e;
        } finally {
            if ($tmpPosts !== '') {
                try {
                    $this->dropTempTable($tmpPosts);
                } catch (\Throwable $e) {
                    // Never fail the scan because of temp cleanup.
                }
            }
            if ($tmpTerms !== '') {
                try {
                    $this->dropTempTable($tmpTerms);
                } catch (\Throwable $e) {
                    // Never fail the scan because of temp cleanup.
                }
            }
        }


        // Build latest issues table from scan history (single copy).
        $this->rebuildLatestIssuesFromScan($scanId);

        // Accurate counts by code.
        $counts = $this->scanIssues->countsByCode($scanId);
        $issuesTotal = 0;
        foreach ($counts as $c) {
            $issuesTotal += (int) $c;
        }

        $duration = (int) max(0, round(microtime(true) - $startedAtMicro));
        $finishedAtTs = time();

        $summary = [
            'scan_id' => $scanId,
            'started_at' => $startedAtTs,
            'finished_at' => $finishedAtTs,
            'duration_sec' => $duration,
            'total_posts' => $totalPosts,
            'total_terms' => $totalTerms,
            'scanned_posts' => $totalPosts,
            'scanned_terms' => $totalTerms,
            'issues_total' => $issuesTotal,
            'issues_by_code' => $counts,
            'mode' => 'sql',
        ];

        update_option('wpsn_audit_last_scan', $summary, false);
        $this->scans->finishScan($scanId, $summary, 'completed');

        // Retention: keep last N scans.
        $keep = (int) $this->settings->get('audit.history_keep_scans', 20);
        if ($keep < 0) {
            $keep = 0;
        }
        if ($keep > 500) {
            $keep = 500;
        }
        if ($keep > 0) {
            $this->scans->purgeKeepLast($keep);
        }

        $this->logger->info('Audit scan completed (sql)', $summary);

        return $summary;
    }

    /**
     * @return array<int,string>
     */
    private function publicPostTypes(): array
    {
        $types = get_post_types(['public' => true], 'names');
        $out = [];
        if (is_array($types)) {
            foreach ($types as $pt) {
                $pt = sanitize_key((string) $pt);
                if ($pt === '' || $pt === 'attachment') {
                    continue;
                }
                $out[] = $pt;
            }
        }
        if (empty($out)) {
            $out = ['post', 'page'];
        }
        return array_values(array_unique($out));
    }

    /**
     * @return array<int,string>
     */
    private function publicTaxonomies(): array
    {
        $tax = get_taxonomies(['public' => true], 'names');
        $out = [];
        if (is_array($tax)) {
            foreach ($tax as $t) {
                $t = sanitize_key((string) $t);
                if ($t === '' || $t === 'post_format') {
                    continue;
                }
                $out[] = $t;
            }
        }
        return array_values(array_unique($out));
    }

    /**
     * @param array<int,string> $values
     */
    private function sqlInList(array $values): string
    {
        $clean = [];
        foreach ($values as $v) {
            $v = sanitize_key((string) $v);
            if ($v === '') {
                continue;
            }
            $clean[] = "'" . esc_sql($v) . "'";
        }
        if (empty($clean)) {
            return "('')";
        }
        return '(' . implode(',', array_values(array_unique($clean))) . ')';
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function countPosts(array $postTypes): int
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $posts = $wpdb->posts;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $c = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$posts} WHERE post_status = 'publish' AND post_type IN {$in}");
        return max(0, $c);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function countTerms(array $taxonomies): int
    {
        global $wpdb;
        if (empty($taxonomies)) {
            return 0;
        }
        $in = $this->sqlInList($taxonomies);
        $tt = $wpdb->term_taxonomy;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $c = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tt} WHERE taxonomy IN {$in}");
        return max(0, $c);
    }

    /**
     * Run a SELECT that returns 12 issue columns and insert rows into scan history table.
     *
     * Latest issues table is rebuilt from the scan table once, at the end (faster than
     * running each heavy SELECT twice).
     */
    private function insertFromSelect(int $scanId, string $selectSql): void
    {
        global $wpdb;

        $scanIssuesTable = $wpdb->prefix . 'wpsn_audit_scan_issues';

        $cols = "object_type, object_id, object_subtype, object_title, object_url, issue_code, severity, message, suggestion, details, detected_at, updated_at";

        // History (per scan).
        // We wrap SELECT into a derived table so we don't need to duplicate SQL.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query("INSERT IGNORE INTO {$scanIssuesTable} (scan_id, {$cols}) SELECT " . (int) $scanId . ", s.* FROM ({$selectSql}) s");
    }

    /**
     * Rebuild the "latest issues" table from the scan history for the given scan.
     *
     * This avoids running each heavy SELECT twice (latest + history).
     */
    private function rebuildLatestIssuesFromScan(int $scanId): void
    {
        global $wpdb;

        $issuesTable = $wpdb->prefix . 'wpsn_audit_issues';
        $scanIssuesTable = $wpdb->prefix . 'wpsn_audit_scan_issues';

        $cols = "object_type, object_id, object_subtype, object_title, object_url, issue_code, severity, message, suggestion, details, detected_at, updated_at";

        // Replace latest issues in one shot (truncate + insert from scan table).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query("TRUNCATE TABLE {$issuesTable}");

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query($wpdb->prepare(
            "INSERT IGNORE INTO {$issuesTable} ({$cols}) SELECT {$cols} FROM {$scanIssuesTable} WHERE scan_id = %d",
            (int) $scanId
        ));
    }

    /**
     * Sanitize an SQL table name (best-effort).
     *
     * We only use this for internally-generated temp table names.
     */
    private function sanitizeTableName(string $name): string
    {
        $name = preg_replace('/[^A-Za-z0-9_]/', '', $name);
        return $name !== null ? (string) $name : '';
    }

    /**
     * @throws \RuntimeException
     */
    private function dropTempTable(string $tableName): void
    {
        global $wpdb;
        $t = $this->sanitizeTableName($tableName);
        if ($t === '') {
            return;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query("DROP TEMPORARY TABLE IF EXISTS {$t}");
    }

    /**
     * Build a per-post temporary cache table for the scan.
     *
     * This reduces repeated joins against wp_postmeta on large sites.
     *
     * @param array<int,string> $postTypes
     * @return string temp table name, or empty string on failure
     */
    private function buildTempPostCacheTable(int $scanId, array $postTypes, bool $includeSocial = true): string
    {
        global $wpdb;

        $tmp = $this->sanitizeTableName($wpdb->prefix . 'wpsn_audit_tmp_posts_' . (int) $scanId);
        if ($tmp === '') {
            return '';
        }

        $charset = $wpdb->get_charset_collate();
        $inPostTypes = $this->sqlInList($postTypes);

        // Only the meta keys we need for SQL-mode checks.
        $metaKeys = [
            MetaKeys::TITLE,
            MetaKeys::DESCRIPTION,
            MetaKeys::ROBOTS_INDEX,
            MetaKeys::CANONICAL,
        ];
        if ($includeSocial) {
            $metaKeys[] = MetaKeys::OG_IMAGE;
            $metaKeys[] = MetaKeys::TWITTER_IMAGE;
            $metaKeys[] = '_thumbnail_id';
        }
        $inMetaKeys = $this->sqlInList($metaKeys);

        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $kTitle = esc_sql(MetaKeys::TITLE);
        $kDesc = esc_sql(MetaKeys::DESCRIPTION);
        $kRobots = esc_sql(MetaKeys::ROBOTS_INDEX);
        $kCanon = esc_sql(MetaKeys::CANONICAL);
        $kOg = esc_sql(MetaKeys::OG_IMAGE);
        $kTw = esc_sql(MetaKeys::TWITTER_IMAGE);
        $kThumb = esc_sql('_thumbnail_id');

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DROP TEMPORARY TABLE IF EXISTS {$tmp}");

            // Keep columns minimal (TEXT) to reduce temp table pressure.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $ok = $wpdb->query("CREATE TEMPORARY TABLE {$tmp} (
                post_id bigint(20) unsigned NOT NULL,
                post_type varchar(64) NOT NULL DEFAULT '',
                post_title varchar(255) NOT NULL DEFAULT '',
                post_excerpt text DEFAULT NULL,
                meta_title text DEFAULT NULL,
                meta_desc text DEFAULT NULL,
                robots_index varchar(20) DEFAULT NULL,
                canonical text DEFAULT NULL,
                og_image text DEFAULT NULL,
                tw_image text DEFAULT NULL,
                thumb_id bigint(20) unsigned DEFAULT NULL,
				PRIMARY KEY (post_id),
				KEY post_type (post_type),
				KEY robots_index (robots_index),
				KEY canonical_prefix (canonical(191))
            ) {$charset}");

            if ($ok === false) {
                return '';
            }

            // Fill cache: one join to postmeta and conditional aggregation.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $ok2 = $wpdb->query("INSERT INTO {$tmp} (post_id, post_type, post_title, post_excerpt, meta_title, meta_desc, robots_index, canonical, og_image, tw_image, thumb_id)
                SELECT
                    p.ID AS post_id,
                    p.post_type AS post_type,
                    p.post_title AS post_title,
                    p.post_excerpt AS post_excerpt,
                    MAX(CASE WHEN pm.meta_key = '{$kTitle}' THEN pm.meta_value END) AS meta_title,
                    MAX(CASE WHEN pm.meta_key = '{$kDesc}' THEN pm.meta_value END) AS meta_desc,
                    MAX(CASE WHEN pm.meta_key = '{$kRobots}' THEN pm.meta_value END) AS robots_index,
                    MAX(CASE WHEN pm.meta_key = '{$kCanon}' THEN pm.meta_value END) AS canonical,
                    MAX(CASE WHEN pm.meta_key = '{$kOg}' THEN pm.meta_value END) AS og_image,
                    MAX(CASE WHEN pm.meta_key = '{$kTw}' THEN pm.meta_value END) AS tw_image,
                    MAX(CASE WHEN pm.meta_key = '{$kThumb}' THEN CAST(pm.meta_value AS UNSIGNED) END) AS thumb_id
                FROM {$p} p
                LEFT JOIN {$pm} pm ON (pm.post_id = p.ID AND pm.meta_key IN {$inMetaKeys})
                WHERE p.post_status = 'publish'
                  AND p.post_type IN {$inPostTypes}
                GROUP BY p.ID");

            if ($ok2 === false) {
                return '';
            }

            return $tmp;
        } catch (\Throwable $e) {
            // Best-effort: fallback to legacy scan.
            $this->logger->error('Audit SQL temp cache build failed: ' . $e->getMessage());
            try {
                $this->dropTempTable($tmp);
            } catch (\Throwable $e2) {
                // ignore
            }
            return '';
        }
    }

    /**
     * Build a per-term temporary cache table for the scan.
     *
     * This reduces repeated joins against wp_termmeta on large sites.
     *
     * @param array<int,string> $taxonomies
     * @return string temp table name, or empty string on failure
     */
    private function buildTempTermCacheTable(int $scanId, array $taxonomies): string
    {
        global $wpdb;

        $tmp = $this->sanitizeTableName($wpdb->prefix . 'wpsn_audit_tmp_terms_' . (int) $scanId);
        if ($tmp === '') {
            return '';
        }

        $charset = $wpdb->get_charset_collate();
        $inTax = $this->sqlInList($taxonomies);

        $tt = $wpdb->term_taxonomy;
        $t = $wpdb->terms;
        $tm = $wpdb->termmeta;

        $kTitle = esc_sql(MetaKeys::TITLE);
        $kDesc = esc_sql(MetaKeys::DESCRIPTION);
        $kRobots = esc_sql(MetaKeys::ROBOTS_INDEX);
        $kCanon = esc_sql(MetaKeys::CANONICAL);

        $metaKeys = [
            MetaKeys::TITLE,
            MetaKeys::DESCRIPTION,
            MetaKeys::ROBOTS_INDEX,
            MetaKeys::CANONICAL,
        ];
        $inMetaKeys = $this->sqlInList($metaKeys);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DROP TEMPORARY TABLE IF EXISTS {$tmp}");

            // Keep columns minimal to reduce temp table pressure.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $ok = $wpdb->query("CREATE TEMPORARY TABLE {$tmp} (
                term_id bigint(20) unsigned NOT NULL,
                taxonomy varchar(64) NOT NULL DEFAULT '',
                term_name varchar(255) NOT NULL DEFAULT '',
                term_description longtext DEFAULT NULL,
                meta_title text DEFAULT NULL,
                meta_desc text DEFAULT NULL,
                robots_index varchar(20) DEFAULT NULL,
                canonical text DEFAULT NULL,
				PRIMARY KEY (term_id),
				KEY taxonomy (taxonomy),
				KEY robots_index (robots_index),
				KEY canonical_prefix (canonical(191))
            ) {$charset}");

            if ($ok === false) {
                return '';
            }

            // Fill cache: one join to termmeta and conditional aggregation.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $ok2 = $wpdb->query("INSERT INTO {$tmp} (term_id, taxonomy, term_name, term_description, meta_title, meta_desc, robots_index, canonical)
                SELECT
                    tt.term_id AS term_id,
                    tt.taxonomy AS taxonomy,
                    t.name AS term_name,
                    tt.description AS term_description,
                    MAX(CASE WHEN tm.meta_key = '{$kTitle}' THEN tm.meta_value END) AS meta_title,
                    MAX(CASE WHEN tm.meta_key = '{$kDesc}' THEN tm.meta_value END) AS meta_desc,
                    MAX(CASE WHEN tm.meta_key = '{$kRobots}' THEN tm.meta_value END) AS robots_index,
                    MAX(CASE WHEN tm.meta_key = '{$kCanon}' THEN tm.meta_value END) AS canonical
                FROM {$tt} tt
                INNER JOIN {$t} t ON (t.term_id = tt.term_id)
                LEFT JOIN {$tm} tm ON (tm.term_id = tt.term_id AND tm.meta_key IN {$inMetaKeys})
                WHERE tt.taxonomy IN {$inTax}
                GROUP BY tt.term_id, tt.taxonomy, t.name, tt.description
            ");

            if ($ok2 === false) {
                return '';
            }

            return $tmp;
        } catch (\Throwable $e) {
            // Best-effort: fallback to legacy scan.
            $this->logger->error('Audit SQL term temp cache build failed: ' . $e->getMessage());
            try {
                $this->dropTempTable($tmp);
            } catch (\Throwable $e2) {
                // ignore
            }
            return '';
        }
    }

    private function insertPostMissingTitleFromCache(int $scanId, string $tmpPosts, string $homeForPSql, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        $tmp = $this->sanitizeTableName($tmpPosts);
        if ($tmp === '') {
            return;
        }
        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(tp.meta_title,''), ''))"
            : "TRIM(COALESCE(NULLIF(tp.meta_title,''), tp.post_title))";
        $select = "SELECT
            'post' AS object_type,
            tp.post_id AS object_id,
            tp.post_type AS object_subtype,
            tp.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', tp.post_id) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_TITLE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tp
        WHERE {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertPostMissingDescriptionFromCache(int $scanId, string $tmpPosts, string $homeForPSql, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        $tmp = $this->sanitizeTableName($tmpPosts);
        if ($tmp === '') {
            return;
        }
        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(tp.meta_desc,''), ''))"
            : "TRIM(COALESCE(NULLIF(tp.meta_desc,''), tp.post_excerpt))";
        $select = "SELECT
            'post' AS object_type,
            tp.post_id AS object_id,
            tp.post_type AS object_subtype,
            tp.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', tp.post_id) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_DESCRIPTION) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tp
        WHERE {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertPostNoindexFromCache(int $scanId, string $tmpPosts, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpPosts);
        if ($tmp === '') {
            return;
        }
        $select = "SELECT
            'post' AS object_type,
            tp.post_id AS object_id,
            tp.post_type AS object_subtype,
            tp.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', tp.post_id) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_NOINDEX) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tp
        WHERE tp.robots_index = 'noindex'";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertPostCanonicalInvalidFromCache(int $scanId, string $tmpPosts, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpPosts);
        if ($tmp === '') {
            return;
        }
        $select = "SELECT
            'post' AS object_type,
            tp.post_id AS object_id,
            tp.post_type AS object_subtype,
            tp.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', tp.post_id) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_INVALID) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            tp.canonical AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tp
        WHERE TRIM(COALESCE(tp.canonical,'')) <> ''
          AND tp.canonical NOT LIKE 'http://%'
          AND tp.canonical NOT LIKE 'https://%'
          AND tp.canonical NOT LIKE '/%'";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertPostCanonicalExternalFromCache(int $scanId, string $tmpPosts, string $homeForPSql, string $homeBaseSql, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpPosts);
        if ($tmp === '') {
            return;
        }
        $select = "SELECT
            'post' AS object_type,
            tp.post_id AS object_id,
            tp.post_type AS object_subtype,
            tp.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', tp.post_id) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_EXTERNAL) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            tp.canonical AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tp
        WHERE TRIM(COALESCE(tp.canonical,'')) <> ''
          AND (tp.canonical LIKE 'http://%' OR tp.canonical LIKE 'https://%')
          AND tp.canonical NOT LIKE '{$homeBaseSql}%'";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertPostSocialMissingImageFromCache(int $scanId, string $tmpPosts, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpPosts);
        if ($tmp === '') {
            return;
        }
        $select = "SELECT
            'post' AS object_type,
            tp.post_id AS object_id,
            tp.post_type AS object_subtype,
            tp.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', tp.post_id) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_SOCIAL_MISSING_IMAGE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tp
        WHERE (tp.thumb_id IS NULL OR tp.thumb_id = 0)
          AND (tp.og_image IS NULL OR TRIM(tp.og_image) = '')
          AND (tp.tw_image IS NULL OR TRIM(tp.tw_image) = '')";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostMissingTitle(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        global $wpdb;

        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(pm_t.meta_value,''), ''))"
            : "TRIM(COALESCE(NULLIF(pm_t.meta_value,''), p.post_title))";
        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_TITLE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        LEFT JOIN {$pm} pm_t ON (pm_t.post_id = p.ID AND pm_t.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}
          AND {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostMissingDescription(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(pm_d.meta_value,''), ''))"
            : "TRIM(COALESCE(NULLIF(pm_d.meta_value,''), p.post_excerpt))";
        // NOTE: this is a heuristic (templates/head rendering is not executed in SQL mode).
        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_DESCRIPTION) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        LEFT JOIN {$pm} pm_d ON (pm_d.post_id = p.ID AND pm_d.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}
          AND {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }
    private function insertTermMissingTitleFromCache(int $scanId, string $tmpTerms, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        $tmp = $this->sanitizeTableName($tmpTerms);
        if ($tmp === '') {
            return;
        }

        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(tt.meta_title,''), ''))"
            : "TRIM(COALESCE(NULLIF(tt.meta_title,''), tt.term_name))";

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            tt.term_name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_TITLE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tt
        WHERE {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertTermMissingDescriptionFromCache(int $scanId, string $tmpTerms, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        $tmp = $this->sanitizeTableName($tmpTerms);
        if ($tmp === '') {
            return;
        }

        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(tt.meta_desc,''), ''))"
            : "TRIM(COALESCE(NULLIF(tt.meta_desc,''), tt.term_description))";

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            tt.term_name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_DESCRIPTION) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tt
        WHERE {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }


    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostNoindex(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_NOINDEX) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        INNER JOIN {$pm} pm_r ON (pm_r.post_id = p.ID AND pm_r.meta_key = '" . esc_sql(MetaKeys::ROBOTS_INDEX) . "')
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}
          AND pm_r.meta_value = 'noindex'";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostCanonicalInvalid(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;
        $canonKey = esc_sql(MetaKeys::CANONICAL);

        // Invalid if override exists but does not look like an absolute URL or a site-relative path.
        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_INVALID) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            pm_c.meta_value AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        INNER JOIN {$pm} pm_c ON (pm_c.post_id = p.ID AND pm_c.meta_key = '{$canonKey}')
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}
          AND TRIM(pm_c.meta_value) <> ''
          AND pm_c.meta_value NOT LIKE 'http://%'
          AND pm_c.meta_value NOT LIKE 'https://%'
          AND pm_c.meta_value NOT LIKE '/%'";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostCanonicalExternal(int $scanId, array $postTypes, string $homeForPSql, string $homeBaseSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;
        $canonKey = esc_sql(MetaKeys::CANONICAL);

        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_EXTERNAL) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            pm_c.meta_value AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        INNER JOIN {$pm} pm_c ON (pm_c.post_id = p.ID AND pm_c.meta_key = '{$canonKey}')
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}
          AND TRIM(pm_c.meta_value) <> ''
          AND (pm_c.meta_value LIKE 'http://%' OR pm_c.meta_value LIKE 'https://%')
          AND pm_c.meta_value NOT LIKE '{$homeBaseSql}%'";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostSocialMissingImage(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $ogKey = esc_sql(MetaKeys::OG_IMAGE);
        $twKey = esc_sql(MetaKeys::TWITTER_IMAGE);

        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_SOCIAL_MISSING_IMAGE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        LEFT JOIN {$pm} pm_og ON (pm_og.post_id = p.ID AND pm_og.meta_key = '{$ogKey}')
        LEFT JOIN {$pm} pm_tw ON (pm_tw.post_id = p.ID AND pm_tw.meta_key = '{$twKey}')
        LEFT JOIN {$pm} pm_th ON (pm_th.post_id = p.ID AND pm_th.meta_key = '_thumbnail_id')
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}
          AND (pm_th.meta_value IS NULL OR TRIM(pm_th.meta_value) = '')
          AND (pm_og.meta_value IS NULL OR TRIM(pm_og.meta_value) = '')
          AND (pm_tw.meta_value IS NULL OR TRIM(pm_tw.meta_value) = '')";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostDuplicateTitle(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest, ?string $tmpPostsTable = null): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $scanTitles = $wpdb->prefix . 'wpsn_audit_scan_titles';
        $titleKey = esc_sql(MetaKeys::TITLE);

        // Build per-scan title keys in an indexed table and detect duplicates from it.
        // This is significantly faster than grouping directly on wp_posts/wp_postmeta for large sites.
        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare("DELETE FROM {$scanTitles} WHERE scan_id = %d", (int) $scanId));

            if ($tmpPostsTable !== null && $tmpPostsTable !== '') {
                $tmp = $this->sanitizeTableName($tmpPostsTable);
                $expr = "LOWER(TRIM(COALESCE(NULLIF(tp.meta_title,''), tp.post_title)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanTitles} (scan_id, post_id, post_type, title_key, created_at)
                    SELECT " . (int) $scanId . ", tp.post_id, tp.post_type, {$hash} AS title_key, '{$now}'
                    FROM {$tmp} tp
                    WHERE {$expr} <> ''");
            } else {
                $expr = "LOWER(TRIM(COALESCE(NULLIF(pm_t.meta_value,''), p.post_title)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanTitles} (scan_id, post_id, post_type, title_key, created_at)
                    SELECT " . (int) $scanId . ", p.ID, p.post_type, {$hash} AS title_key, '{$now}'
                    FROM {$p} p
                    LEFT JOIN {$pm} pm_t ON (pm_t.post_id = p.ID AND pm_t.meta_key = '{$titleKey}')
                    WHERE p.post_status = 'publish'
                      AND p.post_type IN {$in}
                      AND {$expr} <> ''");
            }
        } catch (\Throwable $e) {
            $this->logger->error('Audit SQL duplicate-title key build failed: ' . $e->getMessage());
            $this->insertPostDuplicateTitleFallback($scanId, $postTypes, $homeForPSql, $now, $msg, $suggest);
            return;
        }

        $dupes = "SELECT title_key, COUNT(*) AS c
            FROM {$scanTitles}
            WHERE scan_id = " . (int) $scanId . "
            GROUP BY title_key
            HAVING COUNT(*) > 1";

        if ($tmpPostsTable !== null && $tmpPostsTable !== '') {
            $tmp = $this->sanitizeTableName($tmpPostsTable);
            $select = "SELECT
                'post' AS object_type,
                st.post_id AS object_id,
                tp.post_type AS object_subtype,
                tp.post_title AS object_title,
                CONCAT('{$homeForPSql}', '?p=', st.post_id) AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_TITLE) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', st.title_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanTitles} st
            INNER JOIN ({$dupes}) d ON d.title_key = st.title_key
            INNER JOIN {$tmp} tp ON tp.post_id = st.post_id
            WHERE st.scan_id = " . (int) $scanId;
        } else {
            $select = "SELECT
                'post' AS object_type,
                st.post_id AS object_id,
                p.post_type AS object_subtype,
                p.post_title AS object_title,
                CONCAT('{$homeForPSql}', '?p=', st.post_id) AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_TITLE) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', st.title_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanTitles} st
            INNER JOIN ({$dupes}) d ON d.title_key = st.title_key
            INNER JOIN {$p} p ON p.ID = st.post_id
            WHERE st.scan_id = " . (int) $scanId . "
              AND p.post_status = 'publish'
              AND p.post_type IN {$in}";
        }

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostDuplicateDescription(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest, ?string $tmpPostsTable = null): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $scanDescriptions = $wpdb->prefix . 'wpsn_audit_scan_descriptions';
        $descKey = esc_sql(MetaKeys::DESCRIPTION);

        // Build per-scan description keys in an indexed table and detect duplicates from it.
        // This is significantly faster than grouping directly on wp_posts/wp_postmeta for large sites.
        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare("DELETE FROM {$scanDescriptions} WHERE scan_id = %d", (int) $scanId));

            if ($tmpPostsTable !== null && $tmpPostsTable !== '') {
                $tmp = $this->sanitizeTableName($tmpPostsTable);
                $expr = "LOWER(TRIM(COALESCE(NULLIF(tp.meta_desc,''), tp.post_excerpt)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanDescriptions} (scan_id, post_id, post_type, description_key, created_at)
                    SELECT " . (int) $scanId . ", tp.post_id, tp.post_type, {$hash} AS description_key, '{$now}'
                    FROM {$tmp} tp
                    WHERE {$expr} <> ''
                      AND CHAR_LENGTH({$expr}) >= 20");
            } else {
                $expr = "LOWER(TRIM(COALESCE(NULLIF(pm_d.meta_value,''), p.post_excerpt)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanDescriptions} (scan_id, post_id, post_type, description_key, created_at)
                    SELECT " . (int) $scanId . ", p.ID, p.post_type, {$hash} AS description_key, '{$now}'
                    FROM {$p} p
                    LEFT JOIN {$pm} pm_d ON (pm_d.post_id = p.ID AND pm_d.meta_key = '{$descKey}')
                    WHERE p.post_status = 'publish'
                      AND p.post_type IN {$in}
                      AND {$expr} <> ''
                      AND CHAR_LENGTH({$expr}) >= 20");
            }
        } catch (\Throwable $e) {
            $this->logger->error('Audit SQL duplicate-description key build failed: ' . $e->getMessage());
            $this->insertPostDuplicateDescriptionFallback($scanId, $postTypes, $homeForPSql, $now, $msg, $suggest);
            return;
        }

        $dupes = "SELECT description_key, COUNT(*) AS c
            FROM {$scanDescriptions}
            WHERE scan_id = " . (int) $scanId . "
            GROUP BY description_key
            HAVING COUNT(*) > 1";

        if ($tmpPostsTable !== null && $tmpPostsTable !== '') {
            $tmp = $this->sanitizeTableName($tmpPostsTable);
            $select = "SELECT
                'post' AS object_type,
                sd.post_id AS object_id,
                tp.post_type AS object_subtype,
                tp.post_title AS object_title,
                CONCAT('{$homeForPSql}', '?p=', sd.post_id) AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_DESCRIPTION) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_LOW . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', sd.description_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanDescriptions} sd
            INNER JOIN ({$dupes}) d ON d.description_key = sd.description_key
            INNER JOIN {$tmp} tp ON tp.post_id = sd.post_id
            WHERE sd.scan_id = " . (int) $scanId;
        } else {
            $select = "SELECT
                'post' AS object_type,
                sd.post_id AS object_id,
                p.post_type AS object_subtype,
                p.post_title AS object_title,
                CONCAT('{$homeForPSql}', '?p=', sd.post_id) AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_DESCRIPTION) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_LOW . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', sd.description_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanDescriptions} sd
            INNER JOIN ({$dupes}) d ON d.description_key = sd.description_key
            INNER JOIN {$p} p ON p.ID = sd.post_id
            WHERE sd.scan_id = " . (int) $scanId . "
              AND p.post_status = 'publish'
              AND p.post_type IN {$in}";
        }

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * Legacy duplicate-title detection (fallback when key table build fails).
     *
     * @param array<int,string> $postTypes
     */
    private function insertPostDuplicateTitleFallback(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;
        $titleKey = esc_sql(MetaKeys::TITLE);

        $expr = "LOWER(TRIM(COALESCE(NULLIF(pm_t.meta_value,''), p.post_title)))";

        $dupes = "SELECT {$expr} AS tkey, COUNT(*) AS c
            FROM {$p} p
            LEFT JOIN {$pm} pm_t ON (pm_t.post_id = p.ID AND pm_t.meta_key = '{$titleKey}')
            WHERE p.post_status = 'publish'
              AND p.post_type IN {$in}
            GROUP BY tkey
            HAVING tkey <> '' AND COUNT(*) > 1";

        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_TITLE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            CONCAT('key=', d.tkey, '; count=', d.c) AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        LEFT JOIN {$pm} pm_t ON (pm_t.post_id = p.ID AND pm_t.meta_key = '{$titleKey}')
        INNER JOIN ({$dupes}) d ON d.tkey = {$expr}
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * Legacy duplicate-description detection (fallback when key table build fails).
     *
     * @param array<int,string> $postTypes
     */
    private function insertPostDuplicateDescriptionFallback(int $scanId, array $postTypes, string $homeForPSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;
        $descKey = esc_sql(MetaKeys::DESCRIPTION);

        $expr = "LOWER(TRIM(COALESCE(NULLIF(pm_d.meta_value,''), p.post_excerpt)))";

        // Ignore super-short strings to reduce noise.
        $dupes = "SELECT {$expr} AS dkey, COUNT(*) AS c
            FROM {$p} p
            LEFT JOIN {$pm} pm_d ON (pm_d.post_id = p.ID AND pm_d.meta_key = '{$descKey}')
            WHERE p.post_status = 'publish'
              AND p.post_type IN {$in}
            GROUP BY dkey
            HAVING dkey <> '' AND CHAR_LENGTH(dkey) >= 20 AND COUNT(*) > 1";

        $select = "SELECT
            'post' AS object_type,
            p.ID AS object_id,
            p.post_type AS object_subtype,
            p.post_title AS object_title,
            CONCAT('{$homeForPSql}', '?p=', p.ID) AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_DESCRIPTION) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_LOW . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            CONCAT('key=', d.dkey, '; count=', d.c) AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$p} p
        LEFT JOIN {$pm} pm_d ON (pm_d.post_id = p.ID AND pm_d.meta_key = '{$descKey}')
        INNER JOIN ({$dupes}) d ON d.dkey = {$expr}
        WHERE p.post_status = 'publish'
          AND p.post_type IN {$in}";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermNoindexFromCache(int $scanId, string $tmpTerms, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpTerms);
        if ($tmp === '') {
            return;
        }

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            tt.term_name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_NOINDEX) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tt
        WHERE tt.robots_index = 'noindex'";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertTermCanonicalInvalidFromCache(int $scanId, string $tmpTerms, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpTerms);
        if ($tmp === '') {
            return;
        }

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            tt.term_name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_INVALID) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            tt.canonical AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tt
        WHERE TRIM(COALESCE(tt.canonical,'')) <> ''
          AND tt.canonical NOT LIKE 'http://%'
          AND tt.canonical NOT LIKE 'https://%'
          AND tt.canonical NOT LIKE '/%'";

        $this->insertFromSelect($scanId, $select);
    }

    private function insertTermCanonicalExternalFromCache(int $scanId, string $tmpTerms, string $homeBaseSql, string $now, string $msg, string $suggest): void
    {
        $tmp = $this->sanitizeTableName($tmpTerms);
        if ($tmp === '') {
            return;
        }

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            tt.term_name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_EXTERNAL) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            tt.canonical AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tmp} tt
        WHERE TRIM(COALESCE(tt.canonical,'')) <> ''
          AND (tt.canonical LIKE 'http://%' OR tt.canonical LIKE 'https://%')
          AND tt.canonical NOT LIKE '{$homeBaseSql}%'";

        $this->insertFromSelect($scanId, $select);
    }

private function insertTermNoindex(int $scanId, array $taxonomies, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $rKey = esc_sql(MetaKeys::ROBOTS_INDEX);

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            t.name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_NOINDEX) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tt} tt
        INNER JOIN {$t} t ON (t.term_id = tt.term_id)
        INNER JOIN {$tm} tm_r ON (tm_r.term_id = tt.term_id AND tm_r.meta_key = '{$rKey}')
        WHERE tt.taxonomy IN {$in}
          AND tm_r.meta_value = 'noindex'";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermMissingTitle(int $scanId, array $taxonomies, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        global $wpdb;
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $titleKey = esc_sql(MetaKeys::TITLE);

        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(tm_t.meta_value,''), ''))"
            : "TRIM(COALESCE(NULLIF(tm_t.meta_value,''), t.name))";

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            t.name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_TITLE) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tt} tt
        INNER JOIN {$t} t ON (t.term_id = tt.term_id)
        LEFT JOIN {$tm} tm_t ON (tm_t.term_id = tt.term_id AND tm_t.meta_key = '{$titleKey}')
        WHERE tt.taxonomy IN {$in}
          AND {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermMissingDescription(int $scanId, array $taxonomies, string $now, string $msg, string $suggest, bool $templateBlank = false): void
    {
        global $wpdb;
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $descKey = esc_sql(MetaKeys::DESCRIPTION);

        $expr = $templateBlank
            ? "TRIM(COALESCE(NULLIF(tm_d.meta_value,''), ''))"
            : "TRIM(COALESCE(NULLIF(tm_d.meta_value,''), tt.description))";

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            t.name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_MISSING_DESCRIPTION) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            NULL AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tt} tt
        INNER JOIN {$t} t ON (t.term_id = tt.term_id)
        LEFT JOIN {$tm} tm_d ON (tm_d.term_id = tt.term_id AND tm_d.meta_key = '{$descKey}')
        WHERE tt.taxonomy IN {$in}
          AND {$expr} = ''";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermCanonicalInvalid(int $scanId, array $taxonomies, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $canonKey = esc_sql(MetaKeys::CANONICAL);

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            t.name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_INVALID) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            tm_c.meta_value AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tt} tt
        INNER JOIN {$t} t ON (t.term_id = tt.term_id)
        INNER JOIN {$tm} tm_c ON (tm_c.term_id = tt.term_id AND tm_c.meta_key = '{$canonKey}')
        WHERE tt.taxonomy IN {$in}
          AND TRIM(tm_c.meta_value) <> ''
          AND tm_c.meta_value NOT LIKE 'http://%'
          AND tm_c.meta_value NOT LIKE 'https://%'
          AND tm_c.meta_value NOT LIKE '/%'";

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermCanonicalExternal(int $scanId, array $taxonomies, string $homeBaseSql, string $now, string $msg, string $suggest): void
    {
        global $wpdb;
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $canonKey = esc_sql(MetaKeys::CANONICAL);

        $select = "SELECT
            'term' AS object_type,
            tt.term_id AS object_id,
            tt.taxonomy AS object_subtype,
            t.name AS object_title,
            '' AS object_url,
            '" . esc_sql(AuditRunner::ISSUE_CANONICAL_EXTERNAL) . "' AS issue_code,
            " . (int) AuditRunner::SEVERITY_HIGH . " AS severity,
            '{$msg}' AS message,
            '{$suggest}' AS suggestion,
            tm_c.meta_value AS details,
            '{$now}' AS detected_at,
            '{$now}' AS updated_at
        FROM {$tt} tt
        INNER JOIN {$t} t ON (t.term_id = tt.term_id)
        INNER JOIN {$tm} tm_c ON (tm_c.term_id = tt.term_id AND tm_c.meta_key = '{$canonKey}')
        WHERE tt.taxonomy IN {$in}
          AND TRIM(tm_c.meta_value) <> ''
          AND (tm_c.meta_value LIKE 'http://%' OR tm_c.meta_value LIKE 'https://%')
          AND tm_c.meta_value NOT LIKE '{$homeBaseSql}%'";

        $this->insertFromSelect($scanId, $select);
    }


    private function insertExplicitMetaQualityIssues(int $scanId, array $postTypes, array $taxonomies, string $now, string $tmpPosts = '', string $tmpTerms = ''): void
    {
        $this->insertPostExplicitMetaQualityIssues($scanId, $postTypes, $now, $tmpPosts);

        if (!empty($taxonomies)) {
            $this->insertTermExplicitMetaQualityIssues($scanId, $taxonomies, $now, $tmpTerms);
        }
    }

    /**
     * @param array<int,string> $postTypes
     */
    private function insertPostExplicitMetaQualityIssues(int $scanId, array $postTypes, string $now, string $tmpPosts = ''): void
    {
        global $wpdb;

        $chunk = 500;
        $lastId = 0;
        $homeForP = rtrim((string) home_url('/'), '/') . '/';
        $in = $this->sqlInList($postTypes);
        $p = $wpdb->posts;
        $pm = $wpdb->postmeta;
        $titleKey = esc_sql(MetaKeys::TITLE);
        $descKey = esc_sql(MetaKeys::DESCRIPTION);

        do {
            if ($tmpPosts !== '') {
                $tmp = $this->sanitizeTableName($tmpPosts);
                if ($tmp === '') {
                    return;
                }

                $sql = $wpdb->prepare(
                    "SELECT post_id, post_type, post_title, meta_title, meta_desc
                    FROM {$tmp}
                    WHERE post_id > %d
                      AND (TRIM(COALESCE(meta_title,'')) <> '' OR TRIM(COALESCE(meta_desc,'')) <> '')
                    ORDER BY post_id ASC
                    LIMIT %d",
                    $lastId,
                    $chunk
                );
            } else {
                $sql = $wpdb->prepare(
                    "SELECT
                        p.ID AS post_id,
                        p.post_type AS post_type,
                        p.post_title AS post_title,
                        MAX(CASE WHEN pm.meta_key = %s THEN pm.meta_value END) AS meta_title,
                        MAX(CASE WHEN pm.meta_key = %s THEN pm.meta_value END) AS meta_desc
                    FROM {$p} p
                    LEFT JOIN {$pm} pm ON (pm.post_id = p.ID AND pm.meta_key IN ('{$titleKey}', '{$descKey}'))
                    WHERE p.post_status = 'publish'
                      AND p.post_type IN {$in}
                      AND p.ID > %d
                    GROUP BY p.ID, p.post_type, p.post_title
                    HAVING TRIM(COALESCE(meta_title,'')) <> '' OR TRIM(COALESCE(meta_desc,'')) <> ''
                    ORDER BY p.ID ASC
                    LIMIT %d",
                    MetaKeys::TITLE,
                    MetaKeys::DESCRIPTION,
                    $lastId,
                    $chunk
                );
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $rows = $wpdb->get_results($sql, ARRAY_A);
            if (!is_array($rows) || $rows === []) {
                break;
            }

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

                $lastId = $postId;
                $postType = sanitize_key((string) ($row['post_type'] ?? ''));
                $postTitle = (string) ($row['post_title'] ?? '');
                $url = $homeForP . '?p=' . $postId;

                $metaTitle = isset($row['meta_title']) ? trim((string) $row['meta_title']) : '';
                if ($metaTitle !== '') {
                    $this->insertExplicitMetaQualityIssueRows($scanId, 'post', $postId, $postType, $postTitle, $url, 'title', $metaTitle, $now);
                }

                $metaDesc = isset($row['meta_desc']) ? trim((string) $row['meta_desc']) : '';
                if ($metaDesc !== '') {
                    $this->insertExplicitMetaQualityIssueRows($scanId, 'post', $postId, $postType, $postTitle, $url, 'description', $metaDesc, $now);
                }
            }
        } while (true);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermExplicitMetaQualityIssues(int $scanId, array $taxonomies, string $now, string $tmpTerms = ''): void
    {
        global $wpdb;

        $chunk = 500;
        $lastId = 0;
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $titleKey = esc_sql(MetaKeys::TITLE);
        $descKey = esc_sql(MetaKeys::DESCRIPTION);

        do {
            if ($tmpTerms !== '') {
                $tmp = $this->sanitizeTableName($tmpTerms);
                if ($tmp === '') {
                    return;
                }

                $sql = $wpdb->prepare(
                    "SELECT term_id, taxonomy, term_name, meta_title, meta_desc
                    FROM {$tmp}
                    WHERE term_id > %d
                      AND (TRIM(COALESCE(meta_title,'')) <> '' OR TRIM(COALESCE(meta_desc,'')) <> '')
                    ORDER BY term_id ASC
                    LIMIT %d",
                    $lastId,
                    $chunk
                );
            } else {
                $sql = $wpdb->prepare(
                    "SELECT
                        tt.term_id AS term_id,
                        tt.taxonomy AS taxonomy,
                        t.name AS term_name,
                        MAX(CASE WHEN tm.meta_key = %s THEN tm.meta_value END) AS meta_title,
                        MAX(CASE WHEN tm.meta_key = %s THEN tm.meta_value END) AS meta_desc
                    FROM {$tt} tt
                    INNER JOIN {$t} t ON (t.term_id = tt.term_id)
                    LEFT JOIN {$tm} tm ON (tm.term_id = tt.term_id AND tm.meta_key IN ('{$titleKey}', '{$descKey}'))
                    WHERE tt.taxonomy IN {$in}
                      AND tt.term_id > %d
                    GROUP BY tt.term_id, tt.taxonomy, t.name
                    HAVING TRIM(COALESCE(meta_title,'')) <> '' OR TRIM(COALESCE(meta_desc,'')) <> ''
                    ORDER BY tt.term_id ASC
                    LIMIT %d",
                    MetaKeys::TITLE,
                    MetaKeys::DESCRIPTION,
                    $lastId,
                    $chunk
                );
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $rows = $wpdb->get_results($sql, ARRAY_A);
            if (!is_array($rows) || $rows === []) {
                break;
            }

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

                $lastId = $termId;
                $taxonomy = sanitize_key((string) ($row['taxonomy'] ?? ''));
                $termName = (string) ($row['term_name'] ?? '');

                $metaTitle = isset($row['meta_title']) ? trim((string) $row['meta_title']) : '';
                if ($metaTitle !== '') {
                    $this->insertExplicitMetaQualityIssueRows($scanId, 'term', $termId, $taxonomy, $termName, '', 'title', $metaTitle, $now);
                }

                $metaDesc = isset($row['meta_desc']) ? trim((string) $row['meta_desc']) : '';
                if ($metaDesc !== '') {
                    $this->insertExplicitMetaQualityIssueRows($scanId, 'term', $termId, $taxonomy, $termName, '', 'description', $metaDesc, $now);
                }
            }
        } while (true);
    }

    private function insertExplicitMetaQualityIssueRows(int $scanId, string $objectType, int $objectId, string $objectSubType, string $objectTitle, string $objectUrl, string $field, string $value, string $now): void
    {
        $signals = self::qualitySignalsForExplicitMeta($field, $value);
        if ($signals === []) {
            return;
        }

        foreach ($signals as $signal) {
            $details = null;
            if (isset($signal['details']) && is_array($signal['details']) && $signal['details'] !== []) {
                $json = wp_json_encode($signal['details']);
                $details = is_string($json) && $json !== '' ? $json : null;
            }

            $this->scanIssues->insert($scanId, [
                'object_type' => $objectType,
                'object_id' => $objectId,
                'object_subtype' => $objectSubType,
                'object_title' => $objectTitle,
                'object_url' => $objectUrl,
                'issue_code' => (string) ($signal['issue_code'] ?? ''),
                'severity' => (int) ($signal['severity'] ?? AuditRunner::SEVERITY_LOW),
                'message' => (string) ($signal['message'] ?? ''),
                'suggestion' => (string) ($signal['suggestion'] ?? ''),
                'details' => $details,
                'detected_at' => $now,
                'updated_at' => $now,
            ]);
        }
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermDuplicateTitle(int $scanId, array $taxonomies, string $now, string $msg, string $suggest, ?string $tmpTermsTable = null): void
    {
        global $wpdb;

        $scanTitleKeys = $wpdb->prefix . 'wpsn_audit_scan_title_keys';
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $titleKey = esc_sql(MetaKeys::TITLE);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$scanTitleKeys} WHERE scan_id = %d AND object_type = %s",
                (int) $scanId,
                'term'
            ));

            if ($tmpTermsTable !== null && $tmpTermsTable !== '') {
                $tmp = $this->sanitizeTableName($tmpTermsTable);
                $expr = "LOWER(TRIM(COALESCE(NULLIF(tt.meta_title,''), tt.term_name)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanTitleKeys} (scan_id, object_type, object_id, object_subtype, title_key, created_at)
                    SELECT " . (int) $scanId . ", 'term', tt.term_id, tt.taxonomy, {$hash} AS title_key, '{$now}'
                    FROM {$tmp} tt
                    WHERE {$expr} <> ''");
            } else {
                $expr = "LOWER(TRIM(COALESCE(NULLIF(tm_t.meta_value,''), t.name)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanTitleKeys} (scan_id, object_type, object_id, object_subtype, title_key, created_at)
                    SELECT " . (int) $scanId . ", 'term', tt.term_id, tt.taxonomy, {$hash} AS title_key, '{$now}'
                    FROM {$tt} tt
                    INNER JOIN {$t} t ON (t.term_id = tt.term_id)
                    LEFT JOIN {$tm} tm_t ON (tm_t.term_id = tt.term_id AND tm_t.meta_key = '{$titleKey}')
                    WHERE tt.taxonomy IN {$in}
                      AND {$expr} <> ''");
            }
        } catch (\Throwable $e) {
            $this->logger->error('Audit SQL term duplicate-title key build failed: ' . $e->getMessage());
            return;
        }

        $dupes = "SELECT title_key, COUNT(*) AS c
            FROM {$scanTitleKeys}
            WHERE scan_id = " . (int) $scanId . "
              AND object_type = 'term'
            GROUP BY title_key
            HAVING COUNT(*) > 1";

        if ($tmpTermsTable !== null && $tmpTermsTable !== '') {
            $tmp = $this->sanitizeTableName($tmpTermsTable);
            $select = "SELECT
                'term' AS object_type,
                tk.object_id AS object_id,
                tk.object_subtype AS object_subtype,
                tt.term_name AS object_title,
                '' AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_TITLE) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', tk.title_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanTitleKeys} tk
            INNER JOIN ({$dupes}) d ON d.title_key = tk.title_key
            INNER JOIN {$tmp} tt ON tt.term_id = tk.object_id
            WHERE tk.scan_id = " . (int) $scanId . "
              AND tk.object_type = 'term'";
        } else {
            $select = "SELECT
                'term' AS object_type,
                tk.object_id AS object_id,
                tk.object_subtype AS object_subtype,
                t.name AS object_title,
                '' AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_TITLE) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_MEDIUM . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', tk.title_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanTitleKeys} tk
            INNER JOIN ({$dupes}) d ON d.title_key = tk.title_key
            INNER JOIN {$tt} tt ON tt.term_id = tk.object_id AND tt.taxonomy = tk.object_subtype
            INNER JOIN {$t} t ON t.term_id = tt.term_id
            WHERE tk.scan_id = " . (int) $scanId . "
              AND tk.object_type = 'term'
              AND tt.taxonomy IN {$in}";
        }

        $this->insertFromSelect($scanId, $select);
    }

    /**
     * @param array<int,string> $taxonomies
     */
    private function insertTermDuplicateDescription(int $scanId, array $taxonomies, string $now, string $msg, string $suggest, ?string $tmpTermsTable = null): void
    {
        global $wpdb;

        $scanDescriptionKeys = $wpdb->prefix . 'wpsn_audit_scan_description_keys';
        $in = $this->sqlInList($taxonomies);
        $t = $wpdb->terms;
        $tt = $wpdb->term_taxonomy;
        $tm = $wpdb->termmeta;
        $descKey = esc_sql(MetaKeys::DESCRIPTION);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$scanDescriptionKeys} WHERE scan_id = %d AND object_type = %s",
                (int) $scanId,
                'term'
            ));

            if ($tmpTermsTable !== null && $tmpTermsTable !== '') {
                $tmp = $this->sanitizeTableName($tmpTermsTable);
                $expr = "LOWER(TRIM(COALESCE(NULLIF(tt.meta_desc,''), tt.term_description)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanDescriptionKeys} (scan_id, object_type, object_id, object_subtype, description_key, created_at)
                    SELECT " . (int) $scanId . ", 'term', tt.term_id, tt.taxonomy, {$hash} AS description_key, '{$now}'
                    FROM {$tmp} tt
                    WHERE {$expr} <> ''
                      AND CHAR_LENGTH({$expr}) >= 20");
            } else {
                $expr = "LOWER(TRIM(COALESCE(NULLIF(tm_d.meta_value,''), tt.description)))";
                $hash = "MD5({$expr})";

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->query("INSERT IGNORE INTO {$scanDescriptionKeys} (scan_id, object_type, object_id, object_subtype, description_key, created_at)
                    SELECT " . (int) $scanId . ", 'term', tt.term_id, tt.taxonomy, {$hash} AS description_key, '{$now}'
                    FROM {$tt} tt
                    LEFT JOIN {$tm} tm_d ON (tm_d.term_id = tt.term_id AND tm_d.meta_key = '{$descKey}')
                    WHERE tt.taxonomy IN {$in}
                      AND {$expr} <> ''
                      AND CHAR_LENGTH({$expr}) >= 20");
            }
        } catch (\Throwable $e) {
            $this->logger->error('Audit SQL term duplicate-description key build failed: ' . $e->getMessage());
            return;
        }

        $dupes = "SELECT description_key, COUNT(*) AS c
            FROM {$scanDescriptionKeys}
            WHERE scan_id = " . (int) $scanId . "
              AND object_type = 'term'
            GROUP BY description_key
            HAVING COUNT(*) > 1";

        if ($tmpTermsTable !== null && $tmpTermsTable !== '') {
            $tmp = $this->sanitizeTableName($tmpTermsTable);
            $select = "SELECT
                'term' AS object_type,
                dk.object_id AS object_id,
                dk.object_subtype AS object_subtype,
                tt.term_name AS object_title,
                '' AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_DESCRIPTION) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_LOW . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', dk.description_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanDescriptionKeys} dk
            INNER JOIN ({$dupes}) d ON d.description_key = dk.description_key
            INNER JOIN {$tmp} tt ON tt.term_id = dk.object_id
            WHERE dk.scan_id = " . (int) $scanId . "
              AND dk.object_type = 'term'";
        } else {
            $select = "SELECT
                'term' AS object_type,
                dk.object_id AS object_id,
                dk.object_subtype AS object_subtype,
                t.name AS object_title,
                '' AS object_url,
                '" . esc_sql(AuditRunner::ISSUE_DUPLICATE_DESCRIPTION) . "' AS issue_code,
                " . (int) AuditRunner::SEVERITY_LOW . " AS severity,
                '{$msg}' AS message,
                '{$suggest}' AS suggestion,
                CONCAT('key_hash=', dk.description_key, '; count=', d.c) AS details,
                '{$now}' AS detected_at,
                '{$now}' AS updated_at
            FROM {$scanDescriptionKeys} dk
            INNER JOIN ({$dupes}) d ON d.description_key = dk.description_key
            INNER JOIN {$tt} tt ON tt.term_id = dk.object_id AND tt.taxonomy = dk.object_subtype
            INNER JOIN {$t} t ON t.term_id = tt.term_id
            WHERE dk.scan_id = " . (int) $scanId . "
              AND dk.object_type = 'term'
              AND tt.taxonomy IN {$in}";
        }

        $this->insertFromSelect($scanId, $select);
    }

}
