<?php

namespace WPSN\Modules\BulkTable\Admin;

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

defined('ABSPATH') || exit;

final class TermBulkTableQuery
{
    private Container $container;

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

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

        $tax = isset($filters['tax']) ? (string) $filters['tax'] : 'any';
        $filter = isset($filters['filter']) ? (string) $filters['filter'] : '';
        $search = trim((string) ($filters['s'] ?? ''));

        $publicTax = get_taxonomies(['public' => true], 'names');
        if (!is_array($publicTax)) {
            $publicTax = [];
        }

        if ($tax !== 'any' && $tax !== '' && !in_array($tax, $publicTax, true)) {
            $tax = 'any';
        }

        $taxList = ($tax === 'any' || $tax === '') ? $publicTax : [$tax];
        if (empty($taxList)) {
            return [[], 0];
        }

        // OG template mismatch (override ≠ fallback) is computed in PHP to respect sanitization and fallbacks.
        if ($filter === 'template_mismatch_og') {
            return $this->fetchOgMismatch($taxList, $search, $limit, $offset);
        }

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

        $joins = [];
        $joins[] = "INNER JOIN {$terms} t ON t.term_id = tt.term_id";

        // Meta joins (single row per key is assumed; GROUP BY protects against duplicates).
        $joins[] = "LEFT JOIN {$tm} tm_title ON (tm_title.term_id = tt.term_id AND tm_title.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_desc ON (tm_desc.term_id = tt.term_id AND tm_desc.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_canon ON (tm_canon.term_id = tt.term_id AND tm_canon.meta_key = '" . esc_sql(MetaKeys::CANONICAL) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_ridx ON (tm_ridx.term_id = tt.term_id AND tm_ridx.meta_key = '" . esc_sql(MetaKeys::ROBOTS_INDEX) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_rfol ON (tm_rfol.term_id = tt.term_id AND tm_rfol.meta_key = '" . esc_sql(MetaKeys::ROBOTS_FOLLOW) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_ogt ON (tm_ogt.term_id = tt.term_id AND tm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_ogd ON (tm_ogd.term_id = tt.term_id AND tm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_ogi ON (tm_ogi.term_id = tt.term_id AND tm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')";

        $joins[] = "LEFT JOIN {$tm} tm_twt ON (tm_twt.term_id = tt.term_id AND tm_twt.meta_key = '" . esc_sql(MetaKeys::TWITTER_TITLE) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_twd ON (tm_twd.term_id = tt.term_id AND tm_twd.meta_key = '" . esc_sql(MetaKeys::TWITTER_DESCRIPTION) . "')";
        $joins[] = "LEFT JOIN {$tm} tm_twi ON (tm_twi.term_id = tt.term_id AND tm_twi.meta_key = '" . esc_sql(MetaKeys::TWITTER_IMAGE) . "')";

        $where = [];
        $params = [];

        $taxPlaceholders = implode(',', array_fill(0, count($taxList), '%s'));
        $where[] = "tt.taxonomy IN ({$taxPlaceholders})";
        foreach ($taxList as $tx) {
            $params[] = $tx;
        }

        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where[] = '(t.name LIKE %s OR t.slug LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

        switch ($filter) {
            case 'empty_title':
                $where[] = "(tm_title.meta_value IS NULL OR tm_title.meta_value = '')";
                break;
            case 'empty_desc':
                $where[] = "(tm_desc.meta_value IS NULL OR tm_desc.meta_value = '')";
                break;
            case 'empty_og_image':
                $where[] = "(tm_ogi.meta_value IS NULL OR tm_ogi.meta_value = '')";
                break;
            case 'has_overrides':
                $where[] = "(
                    COALESCE(tm_title.meta_value,'') <> '' OR
                    COALESCE(tm_desc.meta_value,'') <> '' OR
                    COALESCE(tm_canon.meta_value,'') <> '' OR
                    COALESCE(tm_ridx.meta_value,'') <> '' OR
                    COALESCE(tm_rfol.meta_value,'') <> '' OR
                    COALESCE(tm_ogt.meta_value,'') <> '' OR
                    COALESCE(tm_ogd.meta_value,'') <> '' OR
                    COALESCE(tm_ogi.meta_value,'') <> '' OR
                    COALESCE(tm_twt.meta_value,'') <> '' OR
                    COALESCE(tm_twd.meta_value,'') <> '' OR
                    COALESCE(tm_twi.meta_value,'') <> ''
                )";
                break;
            case 'no_overrides':
                $where[] = "(
                    COALESCE(tm_title.meta_value,'') = '' AND
                    COALESCE(tm_desc.meta_value,'') = '' AND
                    COALESCE(tm_canon.meta_value,'') = '' AND
                    COALESCE(tm_ridx.meta_value,'') = '' AND
                    COALESCE(tm_rfol.meta_value,'') = '' AND
                    COALESCE(tm_ogt.meta_value,'') = '' AND
                    COALESCE(tm_ogd.meta_value,'') = '' AND
                    COALESCE(tm_ogi.meta_value,'') = '' AND
                    COALESCE(tm_twt.meta_value,'') = '' AND
                    COALESCE(tm_twd.meta_value,'') = '' AND
                    COALESCE(tm_twi.meta_value,'') = ''
                )";
                break;
            default:
                break;
        }

        $whereSql = 'WHERE ' . implode(' AND ', $where);
        $joinSql = implode("\n", $joins);

        // Total count.
        $countSql = "SELECT COUNT(DISTINCT tt.term_taxonomy_id) FROM {$tt} tt\n{$joinSql}\n{$whereSql}";
        $total = (int) $wpdb->get_var($wpdb->prepare($countSql, $params));

        // Items.
        $sql = "SELECT
                tt.term_id AS term_id,
                tt.term_taxonomy_id AS term_taxonomy_id,
                tt.taxonomy AS taxonomy,
                t.name AS term_name,
                t.slug AS term_slug,
                COALESCE(tm_title.meta_value,'') AS seo_title,
                COALESCE(tm_desc.meta_value,'') AS meta_description,
                COALESCE(tm_canon.meta_value,'') AS canonical,
                COALESCE(tm_ridx.meta_value,'') AS robots_index,
                COALESCE(tm_rfol.meta_value,'') AS robots_follow,
                COALESCE(tm_ogt.meta_value,'') AS og_title,
                COALESCE(tm_ogd.meta_value,'') AS og_description,
                COALESCE(tm_ogi.meta_value,'') AS og_image,
                COALESCE(tm_twt.meta_value,'') AS twitter_title,
                COALESCE(tm_twd.meta_value,'') AS twitter_description,
                COALESCE(tm_twi.meta_value,'') AS twitter_image
            FROM {$tt} tt
            {$joinSql}
            {$whereSql}
            GROUP BY tt.term_taxonomy_id
            ORDER BY tt.term_id DESC
            LIMIT %d OFFSET %d";

        $rows = $wpdb->get_results(
            $wpdb->prepare($sql, array_merge($params, [$limit, $offset])),
            ARRAY_A
        );

        return [is_array($rows) ? $rows : [], $total];
    }

    /**
     * @param array<int,string> $taxList
     * @return array{0:array<int,array<string,mixed>>,1:int}
     */
    private function fetchOgMismatch(array $taxList, string $search, int $limit, int $offset): array
    {
        /** @var \WPSN\Core\Settings $settings */
        $settings = $this->container->get('settings');

        $cacheKey = 'wpsn_term_bulk_ogm_' . get_current_user_id() . '_' . md5(wp_json_encode([
            'tax' => $taxList,
            's' => $search,
            'tpl_title' => (string) $settings->get('templates.title', ''),
            'tpl_desc' => (string) $settings->get('templates.description', ''),
            'default_og' => (string) $settings->get('social.default_image_url', ''),
            'site_icon' => (int) get_option('site_icon'),
        ]));

        $ids = get_transient($cacheKey);
        if (!is_array($ids)) {
            $ids = $this->computeOgMismatchIds($taxList, $search);
            set_transient($cacheKey, $ids, 10 * MINUTE_IN_SECONDS);
        }

        $ids = array_values(array_unique(array_map('intval', $ids)));
        $total = count($ids);
        if ($total === 0) {
            return [[], 0];
        }

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

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

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

        $sql = "SELECT
                tt.term_id AS term_id,
                tt.term_taxonomy_id AS term_taxonomy_id,
                tt.taxonomy AS taxonomy,
                t.name AS term_name,
                t.slug AS term_slug,
                COALESCE(tm_title.meta_value,'') AS seo_title,
                COALESCE(tm_desc.meta_value,'') AS meta_description,
                COALESCE(tm_canon.meta_value,'') AS canonical,
                COALESCE(tm_ridx.meta_value,'') AS robots_index,
                COALESCE(tm_rfol.meta_value,'') AS robots_follow,
                COALESCE(tm_ogt.meta_value,'') AS og_title,
                COALESCE(tm_ogd.meta_value,'') AS og_description,
                COALESCE(tm_ogi.meta_value,'') AS og_image,
                COALESCE(tm_twt.meta_value,'') AS twitter_title,
                COALESCE(tm_twd.meta_value,'') AS twitter_description,
                COALESCE(tm_twi.meta_value,'') AS twitter_image
            FROM {$tt} tt
            INNER JOIN {$terms} t ON t.term_id = tt.term_id
            LEFT JOIN {$tm} tm_title ON (tm_title.term_id = tt.term_id AND tm_title.meta_key = '" . esc_sql(MetaKeys::TITLE) . "')
            LEFT JOIN {$tm} tm_desc ON (tm_desc.term_id = tt.term_id AND tm_desc.meta_key = '" . esc_sql(MetaKeys::DESCRIPTION) . "')
            LEFT JOIN {$tm} tm_canon ON (tm_canon.term_id = tt.term_id AND tm_canon.meta_key = '" . esc_sql(MetaKeys::CANONICAL) . "')
            LEFT JOIN {$tm} tm_ridx ON (tm_ridx.term_id = tt.term_id AND tm_ridx.meta_key = '" . esc_sql(MetaKeys::ROBOTS_INDEX) . "')
            LEFT JOIN {$tm} tm_rfol ON (tm_rfol.term_id = tt.term_id AND tm_rfol.meta_key = '" . esc_sql(MetaKeys::ROBOTS_FOLLOW) . "')
            LEFT JOIN {$tm} tm_ogt ON (tm_ogt.term_id = tt.term_id AND tm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')
            LEFT JOIN {$tm} tm_ogd ON (tm_ogd.term_id = tt.term_id AND tm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')
            LEFT JOIN {$tm} tm_ogi ON (tm_ogi.term_id = tt.term_id AND tm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')
            LEFT JOIN {$tm} tm_twt ON (tm_twt.term_id = tt.term_id AND tm_twt.meta_key = '" . esc_sql(MetaKeys::TWITTER_TITLE) . "')
            LEFT JOIN {$tm} tm_twd ON (tm_twd.term_id = tt.term_id AND tm_twd.meta_key = '" . esc_sql(MetaKeys::TWITTER_DESCRIPTION) . "')
            LEFT JOIN {$tm} tm_twi ON (tm_twi.term_id = tt.term_id AND tm_twi.meta_key = '" . esc_sql(MetaKeys::TWITTER_IMAGE) . "')
            WHERE tt.term_taxonomy_id IN ({$in})
            GROUP BY tt.term_taxonomy_id
            ORDER BY tt.term_id DESC";

        $rows = $wpdb->get_results($wpdb->prepare($sql, $pageIds), ARRAY_A);

        return [is_array($rows) ? $rows : [], $total];
    }

    /**
     * @param array<int,string> $taxList
     * @return array<int,int> term_taxonomy_id list
     */
    private function computeOgMismatchIds(array $taxList, string $search): array
    {
        global $wpdb;

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

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

        $taxPlaceholders = implode(',', array_fill(0, count($taxList), '%s'));

        $where = [];
        $params = [];

        $where[] = "tt.taxonomy IN ({$taxPlaceholders})";
        foreach ($taxList as $tx) {
            $params[] = $tx;
        }

        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where[] = '(t.name LIKE %s OR t.slug LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

        // Only rows that have OG overrides.
        $where[] = "(COALESCE(tm_ogt.meta_value,'') <> '' OR COALESCE(tm_ogd.meta_value,'') <> '' OR COALESCE(tm_ogi.meta_value,'') <> '')";

        $whereSql = 'WHERE ' . implode(' AND ', $where);

        $sqlBase = "SELECT
                tt.term_taxonomy_id,
                tt.term_id,
                tt.taxonomy,
                COALESCE(tm_ogt.meta_value,'') AS og_title,
                COALESCE(tm_ogd.meta_value,'') AS og_description,
                COALESCE(tm_ogi.meta_value,'') AS og_image
            FROM {$tt} tt
            INNER JOIN {$terms} t ON t.term_id = tt.term_id
            LEFT JOIN {$tm} tm_ogt ON (tm_ogt.term_id = tt.term_id AND tm_ogt.meta_key = '" . esc_sql(MetaKeys::OG_TITLE) . "')
            LEFT JOIN {$tm} tm_ogd ON (tm_ogd.term_id = tt.term_id AND tm_ogd.meta_key = '" . esc_sql(MetaKeys::OG_DESCRIPTION) . "')
            LEFT JOIN {$tm} tm_ogi ON (tm_ogi.term_id = tt.term_id AND tm_ogi.meta_key = '" . esc_sql(MetaKeys::OG_IMAGE) . "')
            {$whereSql}
            GROUP BY tt.term_taxonomy_id
            ORDER BY tt.term_taxonomy_id DESC";

        $batch = 300;
        $offset = 0;
        $out = [];

        while (true) {
            $sql = $sqlBase . " LIMIT %d OFFSET %d";
            $rows = $wpdb->get_results(
                $wpdb->prepare($sql, array_merge($params, [$batch, $offset])),
                ARRAY_A
            );

            if (!is_array($rows) || empty($rows)) {
                break;
            }

            foreach ($rows as $r) {
                $termId = (int) ($r['term_id'] ?? 0);
                $ttId = (int) ($r['term_taxonomy_id'] ?? 0);
                $taxonomy = (string) ($r['taxonomy'] ?? '');
                if ($termId <= 0 || $ttId <= 0 || $taxonomy === '') {
                    continue;
                }

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

                $ctx = new SeoDocumentContext(SeoDocumentContext::TYPE_TERM, $termId, $taxonomy, '', 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) {
                    $out[] = $ttId;
                }
            }

            if (count($rows) < $batch) {
                break;
            }
            $offset += $batch;
        }

        return $out;
    }
}
