<?php

namespace WPSN\Modules\BulkTable\Admin;

use WPSN\Core\Container;
use WPSN\Core\Settings;
use WPSN\Domain\SeoDocumentContext;
use WPSN\SEO\MetaKeys;
use WPSN\SEO\SeoMetaRepository;
use WPSN\SEO\TemplateEngine;

defined('ABSPATH') || exit;

/**
 * Data access for the Bulk Table.
 *
 * We intentionally query override meta values (postmeta), not computed templates,
 * so the table stays fast and predictable on large sites.
 */
final class BulkTableQuery
{
    private Container $container;

    public function __construct(Container $container)
    {
        $this->container = $container;
    }

    /**
     * @param array{pt:string,status:string,filter:string,s:string} $filters
     * @return array{0:array<int,array<string,mixed>>,1:int} items, total
     */
    public function fetch(array $filters, int $limit, int $offset): array
    {
        global $wpdb;

        $pt = $filters['pt'] ?? 'any';
        $status = $filters['status'] ?? 'publish';
        $filter = $filters['filter'] ?? '';
        $search = $filters['s'] ?? '';

        $postTypes = [];
        if ($pt === 'any' || $pt === '') {
            $postTypes = get_post_types(['public' => true], 'names');
        } else {
            $postTypes = [$pt];
        }
        unset($postTypes['attachment']);
        $postTypes = array_values(array_filter(array_map('sanitize_key', $postTypes)));
        if (empty($postTypes)) {
            $postTypes = ['post', 'page'];
        }

        $statuses = [];
        if ($status === 'any') {
            $statuses = ['publish', 'draft', 'pending', 'private', 'future'];
        } else {
            $statuses = [$status];
        }

        // "Template mismatch" filter is computed in PHP by comparing current overrides
        // against the current title/description templates. This is slower than pure SQL,
        // so we cache the resulting ID list for a short period.
        if ($filter === 'template_mismatch') {
            return $this->fetchTemplateMismatch($postTypes, $statuses, $search, $filters, $limit, $offset);
        }

        // "OG mismatch" filter is computed in PHP by comparing OG overrides
        // against the current fallback-calculation (no overrides). This lets users
        // find OG overrides that diverge from defaults.
        if ($filter === 'template_mismatch_og') {
            return $this->fetchOgMismatch($postTypes, $statuses, $search, $filters, $limit, $offset);
        }

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

        $ptPlaceholders = implode(',', array_fill(0, count($postTypes), '%s'));
        $stPlaceholders = implode(',', array_fill(0, count($statuses), '%s'));

        $joins = [];
        $joins[] = "LEFT JOIN {$pm} pm_title ON (pm_title.post_id = p.ID AND pm_title.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_desc ON (pm_desc.post_id = p.ID AND pm_desc.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_canon ON (pm_canon.post_id = p.ID AND pm_canon.meta_key = '" . esc_sql(MetaKeys::CANONICAL) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_rindex ON (pm_rindex.post_id = p.ID AND pm_rindex.meta_key = '" . esc_sql(MetaKeys::ROBOTS_INDEX) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_rfollow ON (pm_rfollow.post_id = p.ID AND pm_rfollow.meta_key = '" . esc_sql(MetaKeys::ROBOTS_FOLLOW) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogt ON (pm_ogt.post_id = p.ID AND pm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogd ON (pm_ogd.post_id = p.ID AND pm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogi ON (pm_ogi.post_id = p.ID AND pm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twt ON (pm_twt.post_id = p.ID AND pm_twt.meta_key = '" . esc_sql(MetaKeys::TWITTER_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twd ON (pm_twd.post_id = p.ID AND pm_twd.meta_key = '" . esc_sql(MetaKeys::TWITTER_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twi ON (pm_twi.post_id = p.ID AND pm_twi.meta_key = '" . esc_sql(MetaKeys::TWITTER_IMAGE) . "')";

        // Duplicates filter: join derived table of duplicate override titles.
        $dupJoin = '';
        $dupParams = [];
        if ($filter === 'duplicates_title') {
            $dupJoin = "INNER JOIN (\n"
                . "  SELECT pm.meta_value AS meta_value\n"
                . "  FROM {$pm} pm\n"
                . "  INNER JOIN {$posts} p2 ON p2.ID = pm.post_id\n"
                . "  WHERE pm.meta_key = '" . esc_sql(MetaKeys::TITLE) . "'\n"
                . "    AND pm.meta_value <> ''\n"
                . "    AND p2.post_type IN ({$ptPlaceholders})\n"
                . "    AND p2.post_status IN ({$stPlaceholders})\n"
                . "  GROUP BY pm.meta_value\n"
                . "  HAVING COUNT(*) > 1\n"
                . ") dup ON dup.meta_value = pm_title.meta_value";

            $dupParams = array_merge($postTypes, $statuses);
        }

        $where = "WHERE p.post_type IN ({$ptPlaceholders}) AND p.post_status IN ({$stPlaceholders})";
        $params = array_merge($postTypes, $statuses);

        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where .= ' AND (p.post_title LIKE %s OR p.post_name LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

        // Filters (override-based).
        if ($filter === 'empty_title') {
            $where .= " AND (pm_title.meta_value IS NULL OR pm_title.meta_value = '')";
        } elseif ($filter === 'empty_desc') {
            $where .= " AND (pm_desc.meta_value IS NULL OR pm_desc.meta_value = '')";
        } elseif ($filter === 'long_title') {
            $max = (int) $this->container->get('settings')->get('ai.local.title_max_length', 60);
            $where .= ' AND CHAR_LENGTH(COALESCE(pm_title.meta_value,\'\')) > %d';
            $params[] = $max;
        } elseif ($filter === 'long_desc') {
            $max = (int) $this->container->get('settings')->get('ai.local.description_max_length', 160);
            $where .= ' AND CHAR_LENGTH(COALESCE(pm_desc.meta_value,\'\')) > %d';
            $params[] = $max;
        } elseif ($filter === 'has_overrides') {
            $where .= " AND (\n"
                . "  COALESCE(pm_title.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_desc.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_canon.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_rindex.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_rfollow.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_ogt.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_ogd.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_ogi.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_twt.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_twd.meta_value,'') <> ''\n"
                . "  OR COALESCE(pm_twi.meta_value,'') <> ''\n"
                . ")";
        } elseif ($filter === 'no_overrides') {
            $where .= " AND (\n"
                . "  COALESCE(pm_title.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_desc.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_canon.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_rindex.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_rfollow.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_ogt.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_ogd.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_ogi.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_twt.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_twd.meta_value,'') = ''\n"
                . "  AND COALESCE(pm_twi.meta_value,'') = ''\n"
                . ")";
        }

        $joinSql = implode("\n", $joins);

        // Total
        $countSql = "SELECT COUNT(DISTINCT p.ID)\nFROM {$posts} p\n{$dupJoin}\n{$joinSql}\n{$where}";
        $countParams = array_merge($dupParams, $params);
        $total = (int) $wpdb->get_var($wpdb->prepare($countSql, $countParams));

        // Items
        $sql = "SELECT\n"
            . "  p.ID, p.post_title, p.post_type, p.post_status,\n"
            . "  COALESCE(pm_title.meta_value,'') AS seo_title,\n"
            . "  COALESCE(pm_desc.meta_value,'') AS meta_description,\n"
            . "  COALESCE(pm_canon.meta_value,'') AS canonical,\n"
            . "  COALESCE(pm_rindex.meta_value,'') AS robots_index,\n"
            . "  COALESCE(pm_rfollow.meta_value,'') AS robots_follow,\n"
            . "  COALESCE(pm_ogt.meta_value,'') AS og_title,\n"
            . "  COALESCE(pm_ogd.meta_value,'') AS og_description,\n"
            . "  COALESCE(pm_ogi.meta_value,'') AS og_image,\n"
            . "  COALESCE(pm_twt.meta_value,'') AS twitter_title,\n"
            . "  COALESCE(pm_twd.meta_value,'') AS twitter_description,\n"
            . "  COALESCE(pm_twi.meta_value,'') AS twitter_image\n"
            . "FROM {$posts} p\n"
            . "{$dupJoin}\n"
            . "{$joinSql}\n"
            . "{$where}\n"
            . "GROUP BY p.ID\n"
            . "ORDER BY p.ID DESC\n"
            . "LIMIT %d OFFSET %d";

        $itemParams = array_merge($dupParams, $params);
        $itemParams[] = $limit;
        $itemParams[] = $offset;

        $rows = $wpdb->get_results($wpdb->prepare($sql, $itemParams), ARRAY_A);
        if (!is_array($rows)) {
            $rows = [];
        }

        return [$rows, $total];
    }

    /**
     * @param array<int,string> $postTypes
     * @param array<int,string> $statuses
     * @param array{pt:string,status:string,filter:string,s:string} $filters
     * @return array{0:array<int,array<string,mixed>>,1:int}
     */
    private function fetchTemplateMismatch(array $postTypes, array $statuses, string $search, array $filters, int $limit, int $offset): array
    {
        global $wpdb;

        /** @var Settings $settings */
        $settings = $this->container->get('settings');
        /** @var TemplateEngine $templateEngine */
        $templateEngine = $this->container->get('template_engine');

        $tplTitle = (string) $settings->get('templates.title', '%title% %sep% %sitename%');
        $tplDesc  = (string) $settings->get('templates.description', '%excerpt%');

        // Cache key should change when templates change.
        $hash = md5(wp_json_encode([
            'pt' => $filters['pt'] ?? 'any',
            'status' => $filters['status'] ?? 'publish',
            's' => $search,
            'tpl_title' => $tplTitle,
            'tpl_desc' => $tplDesc,
        ]) ?: '');

        $cacheKey = 'wpsn_bulk_table_tm_' . (int) get_current_user_id() . '_' . $hash;
        $cached = get_transient($cacheKey);
        $ids = [];
        if (is_array($cached) && isset($cached['ids']) && is_array($cached['ids'])) {
            $ids = array_values(array_filter(array_map('intval', $cached['ids']), static fn($v) => $v > 0));
        }

        if (empty($ids)) {
            $ids = $this->computeTemplateMismatchIds($postTypes, $statuses, $search, $tplTitle, $tplDesc, $templateEngine);
            set_transient($cacheKey, ['ids' => $ids, 'ts' => time()], 10 * MINUTE_IN_SECONDS);
        }

        $total = count($ids);
        if ($total === 0) {
            return [[], 0];
        }

        $pageIds = array_slice($ids, $offset, $limit);
        if (empty($pageIds)) {
            return [[], $total];
        }

        // Fetch full row data for the current page IDs.
        $posts = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $joins = [];
        $joins[] = "LEFT JOIN {$pm} pm_title ON (pm_title.post_id = p.ID AND pm_title.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_desc ON (pm_desc.post_id = p.ID AND pm_desc.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_canon ON (pm_canon.post_id = p.ID AND pm_canon.meta_key = '" . esc_sql(MetaKeys::CANONICAL) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_rindex ON (pm_rindex.post_id = p.ID AND pm_rindex.meta_key = '" . esc_sql(MetaKeys::ROBOTS_INDEX) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_rfollow ON (pm_rfollow.post_id = p.ID AND pm_rfollow.meta_key = '" . esc_sql(MetaKeys::ROBOTS_FOLLOW) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogt ON (pm_ogt.post_id = p.ID AND pm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogd ON (pm_ogd.post_id = p.ID AND pm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogi ON (pm_ogi.post_id = p.ID AND pm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twt ON (pm_twt.post_id = p.ID AND pm_twt.meta_key = '" . esc_sql(MetaKeys::TWITTER_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twd ON (pm_twd.post_id = p.ID AND pm_twd.meta_key = '" . esc_sql(MetaKeys::TWITTER_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twi ON (pm_twi.post_id = p.ID AND pm_twi.meta_key = '" . esc_sql(MetaKeys::TWITTER_IMAGE) . "')";
        $joinSql = implode("\n", $joins);

        $inPlaceholders = implode(',', array_fill(0, count($pageIds), '%d'));

        $sql = "SELECT\n"
            . "  p.ID, p.post_title, p.post_type, p.post_status,\n"
            . "  COALESCE(pm_title.meta_value,'') AS seo_title,\n"
            . "  COALESCE(pm_desc.meta_value,'') AS meta_description,\n"
            . "  COALESCE(pm_canon.meta_value,'') AS canonical,\n"
            . "  COALESCE(pm_rindex.meta_value,'') AS robots_index,\n"
            . "  COALESCE(pm_rfollow.meta_value,'') AS robots_follow,\n"
            . "  COALESCE(pm_ogt.meta_value,'') AS og_title,\n"
            . "  COALESCE(pm_ogd.meta_value,'') AS og_description,\n"
            . "  COALESCE(pm_ogi.meta_value,'') AS og_image,\n"
            . "  COALESCE(pm_twt.meta_value,'') AS twitter_title,\n"
            . "  COALESCE(pm_twd.meta_value,'') AS twitter_description,\n"
            . "  COALESCE(pm_twi.meta_value,'') AS twitter_image\n"
            . "FROM {$posts} p\n"
            . "{$joinSql}\n"
            . "WHERE p.ID IN ({$inPlaceholders})\n"
            . "GROUP BY p.ID\n"
            . "ORDER BY p.ID DESC";

        $rows = $wpdb->get_results($wpdb->prepare($sql, $pageIds), ARRAY_A);
        if (!is_array($rows)) {
            $rows = [];
        }

        return [$rows, $total];
    }

    /**
     * @param array<int,string> $postTypes
     * @param array<int,string> $statuses
     * @return array<int,int> post IDs (DESC order)
     */
    private function computeTemplateMismatchIds(array $postTypes, array $statuses, string $search, string $tplTitle, string $tplDesc, TemplateEngine $templateEngine): array
    {
        global $wpdb;

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

        $ptPlaceholders = implode(',', array_fill(0, count($postTypes), '%s'));
        $stPlaceholders = implode(',', array_fill(0, count($statuses), '%s'));

        $joins = [];
        $joins[] = "LEFT JOIN {$pm} pm_title ON (pm_title.post_id = p.ID AND pm_title.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_desc ON (pm_desc.post_id = p.ID AND pm_desc.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')";
        $joinSql = implode("\n", $joins);

        $where = "WHERE p.post_type IN ({$ptPlaceholders}) AND p.post_status IN ({$stPlaceholders})";
        $params = array_merge($postTypes, $statuses);

        // Only rows that have overrides (otherwise no mismatch).
        $where .= " AND (COALESCE(pm_title.meta_value,'') <> '' OR COALESCE(pm_desc.meta_value,'') <> '')";

        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where .= ' AND (p.post_title LIKE %s OR p.post_name LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

        $batchSize = 500;
        $scanOffset = 0;
        $ids = [];

        do {
            $sql = "SELECT p.ID, p.post_type,\n"
                . "  COALESCE(pm_title.meta_value,'') AS seo_title,\n"
                . "  COALESCE(pm_desc.meta_value,'') AS meta_description\n"
                . "FROM {$posts} p\n"
                . "{$joinSql}\n"
                . "{$where}\n"
                . "ORDER BY p.ID DESC\n"
                . "LIMIT %d OFFSET %d";

            $batchParams = array_merge($params, [$batchSize, $scanOffset]);
            $rows = $wpdb->get_results($wpdb->prepare($sql, $batchParams), ARRAY_A);
            if (!is_array($rows) || empty($rows)) {
                break;
            }

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

                $overrideTitle = trim((string) ($row['seo_title'] ?? ''));
                $overrideDesc  = trim((string) ($row['meta_description'] ?? ''));
                $postType = (string) ($row['post_type'] ?? '');

                $ctx = new SeoDocumentContext(SeoDocumentContext::TYPE_SINGULAR, $id, $postType, '', 1);

                // Compute current template output.
                $templateTitle = $templateEngine->render($tplTitle, $ctx);
                $templateDesc  = $templateEngine->render($tplDesc, $ctx);

                // Apply the same filters as SeoMetaRepository would for these fields.
                $templateTitle = (string) apply_filters('wpsn/meta/title', $templateTitle, $ctx);
                $templateDesc  = (string) apply_filters('wpsn/meta/description', $templateDesc, $ctx);

                $templateTitle = trim($templateTitle);
                $templateDesc = trim($templateDesc);

                $mismatch = false;
                if ($overrideTitle !== '' && $overrideTitle !== $templateTitle) {
                    $mismatch = true;
                }
                if ($overrideDesc !== '' && $overrideDesc !== $templateDesc) {
                    $mismatch = true;
                }

                if ($mismatch) {
                    $ids[] = $id;
                }
            }

            $scanOffset += $batchSize;
        } while (true);

        return $ids;
    }

    private function fetchOgMismatch(array $postTypes, array $statuses, string $search, array $filters, int $limit, int $offset): array
    {
        global $wpdb;

        /** @var Settings $settings */
        $settings = $this->container->get('settings');

        // Cache key should change when templates or social defaults change.
        $hash = md5(wp_json_encode([
            'pt' => $filters['pt'] ?? 'any',
            'status' => $filters['status'] ?? 'publish',
            's' => $search,
            'tpl_title' => (string) $settings->get('templates.title', '%title% %sep% %sitename%'),
            'tpl_desc' => (string) $settings->get('templates.description', '%excerpt%'),
            'default_og' => (string) $settings->get('social.default_image_url', ''),
            'site_icon' => (int) get_option('site_icon'),
        ]) ?: '');

        $cacheKey = 'wpsn_bulk_table_ogm_' . (int) get_current_user_id() . '_' . $hash;
        $cached = get_transient($cacheKey);
        $ids = [];
        if (is_array($cached) && isset($cached['ids']) && is_array($cached['ids'])) {
            $ids = array_values(array_filter(array_map('intval', $cached['ids']), static fn($v) => $v > 0));
        }

        if (empty($ids)) {
            $ids = $this->computeOgMismatchIds($postTypes, $statuses, $search);
            set_transient($cacheKey, ['ids' => $ids, 'ts' => time()], 10 * MINUTE_IN_SECONDS);
        }

        $total = count($ids);
        if ($total === 0) {
            return [[], 0];
        }

        $pageIds = array_slice($ids, $offset, $limit);
        if (empty($pageIds)) {
            return [[], $total];
        }

        // Fetch full row data for the current page IDs.
        $posts = $wpdb->posts;
        $pm = $wpdb->postmeta;

        $joins = [];
        $joins[] = "LEFT JOIN {$pm} pm_title ON (pm_title.post_id = p.ID AND pm_title.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_desc ON (pm_desc.post_id = p.ID AND pm_desc.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_canon ON (pm_canon.post_id = p.ID AND pm_canon.meta_key = '" . esc_sql(MetaKeys::CANONICAL) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_rindex ON (pm_rindex.post_id = p.ID AND pm_rindex.meta_key = '" . esc_sql(MetaKeys::ROBOTS_INDEX) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_rfollow ON (pm_rfollow.post_id = p.ID AND pm_rfollow.meta_key = '" . esc_sql(MetaKeys::ROBOTS_FOLLOW) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogt ON (pm_ogt.post_id = p.ID AND pm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogd ON (pm_ogd.post_id = p.ID AND pm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogi ON (pm_ogi.post_id = p.ID AND pm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twt ON (pm_twt.post_id = p.ID AND pm_twt.meta_key = '" . esc_sql(MetaKeys::TWITTER_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twd ON (pm_twd.post_id = p.ID AND pm_twd.meta_key = '" . esc_sql(MetaKeys::TWITTER_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_twi ON (pm_twi.post_id = p.ID AND pm_twi.meta_key = '" . esc_sql(MetaKeys::TWITTER_IMAGE) . "')";
        $joinSql = implode("\n", $joins);

        $inPlaceholders = implode(',', array_fill(0, count($pageIds), '%d'));

        $sql = "SELECT\n"
            . "  p.ID, p.post_title, p.post_type, p.post_status,\n"
            . "  COALESCE(pm_title.meta_value,'') AS seo_title,\n"
            . "  COALESCE(pm_desc.meta_value,'') AS meta_description,\n"
            . "  COALESCE(pm_canon.meta_value,'') AS canonical,\n"
            . "  COALESCE(pm_rindex.meta_value,'') AS robots_index,\n"
            . "  COALESCE(pm_rfollow.meta_value,'') AS robots_follow,\n"
            . "  COALESCE(pm_ogt.meta_value,'') AS og_title,\n"
            . "  COALESCE(pm_ogd.meta_value,'') AS og_description,\n"
            . "  COALESCE(pm_ogi.meta_value,'') AS og_image,\n"
            . "  COALESCE(pm_twt.meta_value,'') AS twitter_title,\n"
            . "  COALESCE(pm_twd.meta_value,'') AS twitter_description,\n"
            . "  COALESCE(pm_twi.meta_value,'') AS twitter_image\n"
            . "FROM {$posts} p\n"
            . "{$joinSql}\n"
            . "WHERE p.ID IN ({$inPlaceholders})\n"
            . "GROUP BY p.ID\n"
            . "ORDER BY p.ID DESC";

        $rows = $wpdb->get_results($wpdb->prepare($sql, $pageIds), ARRAY_A);
        if (!is_array($rows)) {
            $rows = [];
        }

        return [$rows, $total];
    }

    /**
     * @param array<int,string> $postTypes
     * @param array<int,string> $statuses
     * @return array<int,int> post IDs (DESC order)
     */
    private function computeOgMismatchIds(array $postTypes, array $statuses, string $search): array
    {
        global $wpdb;

        /** @var SeoMetaRepository $metaRepo */
        $metaRepo = $this->container->get('meta_repo');

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

        $ptPlaceholders = implode(',', array_fill(0, count($postTypes), '%s'));
        $stPlaceholders = implode(',', array_fill(0, count($statuses), '%s'));

        $joins = [];
        $joins[] = "LEFT JOIN {$pm} pm_ogt ON (pm_ogt.post_id = p.ID AND pm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogd ON (pm_ogd.post_id = p.ID AND pm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$pm} pm_ogi ON (pm_ogi.post_id = p.ID AND pm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')";
        $joinSql = implode("\n", $joins);

        $where = "WHERE p.post_type IN ({$ptPlaceholders}) AND p.post_status IN ({$stPlaceholders})";
        $params = array_merge($postTypes, $statuses);

        // Only rows that have OG overrides.
        $where .= " AND (COALESCE(pm_ogt.meta_value,'') <> '' OR COALESCE(pm_ogd.meta_value,'') <> '' OR COALESCE(pm_ogi.meta_value,'') <> '')";

        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where .= ' AND (p.post_title LIKE %s OR p.post_name LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

        $batchSize = 400;
        $scanOffset = 0;
        $ids = [];

        do {
            $sql = "SELECT p.ID, p.post_type,\n"
                . "  COALESCE(pm_ogt.meta_value,'') AS og_title,\n"
                . "  COALESCE(pm_ogd.meta_value,'') AS og_description,\n"
                . "  COALESCE(pm_ogi.meta_value,'') AS og_image\n"
                . "FROM {$posts} p\n"
                . "{$joinSql}\n"
                . "{$where}\n"
                . "ORDER BY p.ID DESC\n"
                . "LIMIT %d OFFSET %d";

            $batchParams = array_merge($params, [$batchSize, $scanOffset]);
            $rows = $wpdb->get_results($wpdb->prepare($sql, $batchParams), ARRAY_A);
            if (!is_array($rows) || empty($rows)) {
                break;
            }

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

                $postType = (string) ($row['post_type'] ?? '');
                $ogTitle = trim((string) ($row['og_title'] ?? ''));
                $ogDesc  = trim((string) ($row['og_description'] ?? ''));
                $ogImg   = trim((string) ($row['og_image'] ?? ''));

                $ctx = new SeoDocumentContext(SeoDocumentContext::TYPE_SINGULAR, $id, $postType, '', 1);

                $metaWith = $metaRepo->forContextWithOverrides($ctx, []);
                $metaFallback = $metaRepo->forContextWithOverrides($ctx, [
                    MetaKeys::OG_TITLE => '',
                    MetaKeys::OG_DESCRIPTION => '',
                    MetaKeys::OG_IMAGE => '',
                ]);

                $mismatch = false;
                if ($ogTitle !== '' && (string) $metaWith->ogTitle !== (string) $metaFallback->ogTitle) {
                    $mismatch = true;
                }
                if (!$mismatch && $ogDesc !== '' && (string) $metaWith->ogDescription !== (string) $metaFallback->ogDescription) {
                    $mismatch = true;
                }
                if (!$mismatch && $ogImg !== '' && (string) $metaWith->ogImage !== (string) $metaFallback->ogImage) {
                    $mismatch = true;
                }

                if ($mismatch) {
                    $ids[] = $id;
                }
            }

            $scanOffset += $batchSize;
        } while (true);

        return $ids;
    }

}
