<?php
namespace JVBase\rest\routes;

use JVBase\rest\RestRouteManager;
use JVBase\integrations\Umami;
use JVBase\meta\MetaManager;
use JVBase\managers\TaxonomyRelationships;
use WP_Query;
use WP_Post;
use WP_Term;
use WP_REST_Request;
use WP_REST_Response;

if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly
}
/**
 * FeedRoutes - Optimized API endpoints for the feed block
 * TODO: Look at Content Routes' setup and make this more like that one; it's a bit more organized
 * Or NewsRoutes
 */
class FeedRoutes extends RestRouteManager
{
    protected int $per_page = 36;
    protected Umami $tracker;

    public function __construct()
    {
        $this->cache_name = 'feed';
        $this->cache_ttl = 86400;

        if (jvbSiteUsesUmami()) {
            $this->tracker = JVB()->connect('umami');
        }
        parent::__construct();
    }

    /**
     * Registers feed routes
     * @return void
     */
    public function registerRoutes():void
    {
        register_rest_route($this->namespace, '/feed', [
            'methods' => ['GET', 'POST'],
            'callback' => [$this, 'handleFeedRequest'],
            'permission_callback' => [$this, 'checkPermission'],
        ]);
    }

    /**
     * Formats an item
     * @param int $postID
     *
     * @return array
     */
    protected function formatItem(int $postID, string $type = 'post'):array
    {
        switch ($type) {
            case 'post':
                $post = get_post($postID);
                $type = jvbNoBase($post->post_type);
                $metaType = 'post';
                break;
            default:
                $post = get_term($postID, jvbCheckBase($type));
                $type = jvbNoBase($type);
                $metaType = 'term';
                break;
        }
        if (!$post || is_wp_error($post)) {
            return [];
        }
        $formatted = $this->cache->get($postID, $type);
//        $formatted = false;
        if ($formatted) {
            return $formatted;
        }


        $fields = apply_filters(
            'jvbFeedFields',
            [],
            $type
        );

        $meta = new MetaManager($postID, $metaType);
        $formatted = [
            'id'    => $postID,
            'icon'  => $type,
        ];

        if (jvbSiteUsesUmami()) {
            $args = ($metaType === 'post') ? [ 'owner_id' => $post->post_author] : [];
            $formatted['umami_view'] = $this->tracker->trackFeedView($postID, $type, $args);
            $formatted['umami_fav'] = $this->tracker->trackFavouriteToggle($postID, $type, false);
        }
        switch ($metaType) {
            case 'term':
                if (jvbSiteUsesUmami()) {
                    $formatted['umami_click'] = $this->tracker->trackTaxonomyClick($postID, $type);
                }
                $owner = (in_array($type, jvbContentTaxonomies()) ? $meta->getValue('owner') : null);
                if (!is_null($owner)) {
                    $formatted['user_id'] = $owner;
                }
                $formatted['url'] = get_term_link($postID, $type);
                break;
            default:
                $formatted = array_merge($formatted, [
                    'date'      => $post->post_date,
                    'user_id'   => (int)$post->post_author,
                    'url'       => get_the_permalink($postID),
                ]);
                break;
        }
        $order = array_keys($fields);
        foreach ($fields as $field => $config) {
            $value = [];
            if ($field === 'umami_click') {
                if ($config === 'profile') {
                    $formatted['umami_click'] = $this->tracker->trackProfileClick($postID, $type);
                }
                if ($config === 'contentTaxonomy') {
                    $formatted['umami_click'] = $this->tracker->trackContentTaxonomyClick($postID, $type);
                }
            } else {
                if (array_key_exists('field', $config)) {
                    if ($field === 'image' && array_key_exists('gallery', $config)) {
                        //Array === post types
                        if (is_array($config['gallery'])) {
                            $posts = get_posts([
                                'post_type' => array_map(function ($item) { return BASE.$item; }, $config['gallery']),
                                'author'    => $post->post_author,
                                'posts_per_page'    => 5,
                                'orderby'   => 'date',
                                'order'     => 'DESC',
                            ]);
                            $formatted['content'] = array_map(function ($content) {
                                return [
                                    'url'   => get_permalink($content->ID),
                                    'title' => $content->post_title,
                                    'image' => jvbImageData((int)get_post_thumbnail_id($content->ID)),
                                ];
                            }, $posts);
                        } else {
                            //String === $meta
                            $ids = explode(',', $meta->getValue($config['gallery']));
                            $formatted['content'] = array_map(function ($id) {
                                return [
                                    'image' => jvbImageData((int)$id)
                                ];
                            }, $ids);
                        }
                    }
                    switch ($config['field']) {
                        case 'post_author':
                            $author = $this->getAuthorData($post);
                            $value = [
                                'value' => $author['value'],
                                'url'   => $author['url']
                            ];
                            break;
                        case 'name':
                            $value = $post->name;
                            break;
                        case 'post_title':
                            $value = $post->post_title;
                            break;
                        case 'image':
                        case 'image_portrait':
                        case 'featured_image':
                            $value = $meta->getValue($config['field']);
                            $value =  jvbImageData((int)$value);
                            break;
                        case 'top_style':
                        case 'city':
                        case 'top_theme':
                            $terms = explode(',', $meta->getValue($field));
                            $terms = array_filter(array_map(function ($termID) use ($config, $postID, $type) {
                                $term = get_term($termID, jvbCheckBase($config['icon']));
                                if ($term && !is_wp_error($term)) {
                                    return $this->formatTaxonomy($term, $postID, $type);
                                }
                                return [];
                            }, $terms));
                            $value = [
                                'terms' => $terms
                            ];
                            break;
                        default:
                            $value = [
                                'value' => $meta->getValue($field)
                            ];
                    }

                } elseif (array_key_exists('taxonomy', $config)) {
                    $terms = get_the_terms($postID, BASE.$config['taxonomy']);
                    if ($terms && !is_wp_error($terms)) {
                        $terms = array_map(function ($term) use ($postID, $type) {
                            return $this->formatTaxonomy($term, $postID, $type);
                        }, $terms);
                        $value = [
                            'terms'     => $terms
                        ];
                    }
                }

                if (array_key_exists('label', $config)) {
                    $value['label'] = $config['label'];
                }
                if (array_key_exists('icon', $config)) {
                    $value['icon'] = $config['icon'];
                }
                $formatted[$field] = $value;
            }
        }

        $formatted['order'] = $order;
        $this->cache->set($postID, $formatted, $type);

        return $formatted;
    }

    protected function formatTaxonomy(WP_Term $term, int $postID, string $type)
    {
        return [
            'ID'    => $term->term_id,
            'title' => htmlspecialchars_decode($term->name),
            'url'   => get_term_link($term->term_id, $term->taxonomy),
            'umami_click'   => $this->tracker->trackTaxonomyClick($term->term_id, $term->taxonomy, [
                'from'  => $type.'_'.$postID
            ])
        ];
    }
    protected function getAuthorData(WP_Post $post)
    {
        $author = $this->cache->get($post->post_author, 'author_data');
        if (!$author) {
            $author = [
                'id'    => $post->post_author,
                'label' => 'Artist',
                'value' => get_the_author_meta('display_name', $post->post_author),
                'icon'  => 'artist',
                'url'   => get_the_permalink(get_user_meta($post->post_author, BASE.'link', true)),
            ];
            $this->cache->set($post->post_author, $author, 'author_data');
        }
        return $author;
    }

    protected function getTaxonomies(int $postID, string $content):array
    {
        $taxonomies = jvbTaxonomiesForContent($content);
        $out = [];
        foreach ($taxonomies as $tax) {
            $terms = get_the_terms($postID, $tax);
            $t = [];
            if ($terms && !is_wp_error($terms)) {
                $config = jvbNoBase($tax);
                $out[] = [
                    'icon'  => $config,
                    'title' => JVB_TAXONOMY[$config]['plural'],
                    'terms' => array_map(function ($term) use ($tax, $postID, $content) {
                        return [
                            'ID'    => $term->term_id,
                            'title' => htmlspecialchars_decode($term->name),
                            'url'   => get_term_link($term->term_id, $tax),
                            'umami_click'   => $this->tracker->trackTaxonomyClick($term->term_id, $tax, [
                                'from'  => $content.'_'.$postID
                            ])
                        ];
                    }, $terms),
                ];
            }
        }
        return $out;
    }


    protected function buildRequestArgs(WP_REST_Request $request):array
    {
        global $jvb_everything;
        $data = $request->get_params();
        error_log('Feed Request: '.print_r($data, true));
        $args = [
            'post_type'         => (array_key_exists($data['content'], $jvb_everything)) ?
                BASE.$data['content'] :
                BASE.array_key_first(JVB_CONTENT),
            'paged'             => intval($data['page'] ?? 1),
            'posts_per_page'    => $this->per_page,
        ];
        if (!empty($data['context'])) {
            $args = $this->applyContextFilters(
                $args,
                [
                    'id' => $data['source'],
                    'type'=>$data['context']
                ]
            );
        }
        $args = $this->applyContextFilters($args, $data);
        $args = $this->applyTaxonomyFilters($args, $data);
        $args = $this->applyOrderFilters($args, $data);
        $args = $this->applyDateFilters($args, $data);

        $args = $this->applyFavouritesFilter($args, $data);

        return $args;
    }
    /**
     * @param WP_REST_Request $request
     *
     * @return WP_REST_Response
     */
    public function handleFeedRequest(WP_REST_Request $request):WP_REST_Response
    {
        $args = $this->buildRequestArgs($request);

        error_log('Final Args: '.print_r($args, true));


        $key = $this->cache->generateKey($args);
        $cached = $this->cache->get($key);
        if ($cached) {
            if ($request->get_param('highlight')) {
                $highlight = json_decode($request->get_param('highlight'), true);
                $args['highlight'] = $highlight;
            }
            $cached['items'] = $this->processHighlightedItem($cached['items'], $args);
            return new WP_REST_Response($cached);
        }
        // Fetch and format items
        $items = $this->fetchFeedItems($args);


        $ttl = (str_contains($args['orderby'], 'RAND')) ? 1800 : $this->cache_ttl;
        $this->cache->set($key, $items, $ttl);

        if ($request->get_param('highlight')) {
            $highlight = json_decode($request->get_param('highlight'), true);
            $args['highlight'] = $highlight;
        }

        $items['items'] = $this->processHighlightedItem($items['items'], $args);
        return new WP_REST_Response($items);
    }

    /**
     * @param array $args Formatted Args for WP_Query
     * @param array $data parsed Request Data
     *
     * @return array|null
     */
    protected function processHighlightedItem(array $items, array $data):array
    {
        error_log('Data passed to processHighlightedItem:'.print_r($data, true));
        if (empty($data['highlight']??null)) {
            return $items;
        }

        // Convert to array if string
        if (is_string($data['highlight'])) {
            $data['highlight'] = json_decode($data['highlight'], true);
        }

        // Extract key and value
        $key = array_keys($data['highlight'])[0] ?? false;
        $value = array_values($data['highlight'])[0] ?? false;
        error_log('Highlighted item: '.$key);
        error_log('Highlighted item: '.$value);
        error_log('No Single Content Types: '.print_r(jvbNoSingleContentTypes(), true));
        error_log('Page: '.print_r($data['paged'], true));
        if (in_array($key, jvbNoSingleContentTypes()) && $value && $data['paged'] === 1) {
            error_log('Formatted Highlighted item: '.print_r($this->formatItem($value), true));
            error_log('Items: '.print_r($items, true));
            array_unshift($items, $this->formatItem($value));
            error_log('Items after unshift: '.print_r($items, true));
        }
        return $items;
    }

    /**
     * @param array $args
     * @param array $context
     *
     * @return array
     */
    protected function applyContextFilters(array $args, array $context):array
    {

        error_log('Args at Context Filters: '.print_r($args, true));
        error_log('Request at Context Filters: '.print_r($context, true));
        if (!isset($context['type'])) {
            return $args;
        }

        switch (true) {
            case contentIsJVBUserType($context['type']):
                $args['author'] = (int)get_post_meta($context['id'], BASE.'link', true);
                break;
            case taxIsJVBContentTax($context['type']):
                $args['post_type'] = (is_array($args['post_type'])) ? $args['post_type'] : explode(',',$args['post_type']);
                if (array_intersect($args['post_type'], array_map(function ($type) { return jvbCheckBase($type); },array_keys(jvbGlobalFeedContent())))) {
                    $artists = jvbGetContentUsers($context['id']);
                    if (!empty($artists)) {
                        $args['author__in'] = $artists;
                    }
                } else {
                    $args['tax_query'] = [
                        'relation'  => 'AND',
                        [
                            'taxonomy' => BASE.$context['type'],
                            'terms' => $context['id'],
                        ]
                    ];
                }
                break;
            case taxonomy_exists(jvbCheckBase($context['type'])):
                $args['tax_query'] = [
                    'relation'  => 'AND',
                    [
                        'taxonomy' => BASE.$context['type'],
                        'terms' => $context['id'],
                    ]
                ];
                break;
        }
        return $args;
    }

    /**
     * @param array $args
     * @param array $filters
     *
     * @return array
     */
    protected function applyFavouritesFilter(array $args, array $filters):array
    {
        if (!array_key_exists('favourites', $filters)){
            return $args;
        }
        error_log('Proceeding to check for favourites:');
        global $wpdb;

        // Get post types for the current filter
        $post_types = explode(',', $args['post_type']);

        $favourites_table = $wpdb->prefix . BASE . 'favourites';
        $placeholders = implode(',', array_fill(0, count($post_types), '%s'));
        error_log('CurrentUser ID: '.print_r(get_current_user_id(), true));
        $favourited_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT target_id FROM {$favourites_table}
            WHERE user_id = %d AND type IN ($placeholders)",
            array_merge(
                [get_current_user_id()],
                $post_types
            )
        ));

        if (empty($favourited_ids)) {
            // Force empty results
            $args['post__in'] = [0];
            return $args;
        }

        $args['post__in'] = isset($args['post__in'])
            ? array_intersect($args['post__in'], $favourited_ids)
            : $favourited_ids;

        return $args;
    }

    /**
     * @param array $args
     *
     * @return array
     */
    protected function fetchFeedItems(array $args):array
    {
        if (in_array($args['post_type'], jvbContentTaxonomies())) {
            return $this->handleContentTaxonomies($args);
        }
        $args['fields'] = 'ids';
        // Get post IDs
        $query = new WP_Query($args);

        // Batch prefetch related data
        update_meta_cache('post', $query->posts);
        update_object_term_cache($query->posts, $args['post_type']);

        // Format regular items
        $items = array_map(function ($post) {
            return $this->formatItem($post);
        }, $query->posts);

        wp_reset_postdata();
        return [
            'items' => $items,
            'has_more' => $query->max_num_pages > $args['paged'],
            'total' => $query->found_posts
        ];
    }

    protected function handleContentTaxonomies(array $args):array
    {

        $taxonomy = jvbNoBase($args['post_type']);
        global $wpdb;
        $table = $wpdb->prefix.BASE.'content_'.$taxonomy;

        // Check if table exists
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table}'") !== $table) {
            return [
                'items' => [],
                'has_more' => false,
                'total' => 0
            ];
        }

        // Build the query components
        $queryBuilder = $this->buildCustomTableQuery($args, $table, $taxonomy);

        // Execute count query first
        $total = (int) $wpdb->get_var($queryBuilder['count_query']);

        // Execute main query if we have results
        $items = [];
        if ($total > 0) {
            $results = $wpdb->get_results($queryBuilder['main_query'],ARRAY_A);
            $items = array_map(function ($ID) use ($taxonomy) {
                return $this->formatItem($ID['term_id'], $taxonomy);
            }, $results);
        }

        $page = $args['paged'] ?? 1;
        $per_page = $args['posts_per_page'] ?? $this->per_page;
        $has_more = ($page * $per_page) < $total;

        return [
            'items' => $items,
            'has_more' => $has_more,
            'total' => $total
        ];
    }
    /**
     * Build SQL query components for custom table
     * @param array $args WP_Query style arguments
     * @param string $table Table name
     * @param string $taxonomy Taxonomy type
     * @return array Query components
     */
    protected function buildCustomTableQuery(array $args, string $table, string $taxonomy): array
    {
        global $wpdb;

        $where_conditions = ['1=1'];
        $joins = [];
        $params = [];

        // Handle search
        if (!empty($args['s'])) {
            $search = '%' . $wpdb->esc_like($args['s']) . '%';
            $where_conditions[] = "(ct.name LIKE %s OR ct.slug LIKE %s)";
            $params[] = $search;
            $params[] = $search;
        }

        // Handle context filters (e.g., filtering shops by style through relationships)
        if (!empty($args['context_filter'])) {
            $context_conditions = $this->buildContextConditions($args['context_filter'], $joins, $params, $taxonomy);
            if (!empty($context_conditions)) {
                $where_conditions[] = $context_conditions;
            }
        }

        // Handle taxonomy filters (tax_query)
        if (!empty($args['tax_query'])) {
            $tax_conditions = $this->buildTaxonomyConditions($args['tax_query'], $joins, $params, $taxonomy);
            if (!empty($tax_conditions)) {
                $where_conditions[] = $tax_conditions;
            }
        }

        // Handle meta queries (custom fields in the table)
        if (!empty($args['meta_query'])) {
            $meta_conditions = $this->buildMetaConditions($args['meta_query'], $joins, $params, $taxonomy);
            if (!empty($meta_conditions)) {
                $where_conditions[] = $meta_conditions;
            }
        }

        // Handle date queries
        if (!empty($args['date_query'])) {
            $date_conditions = $this->buildDateConditions($args['date_query'], $params);
            if (!empty($date_conditions)) {
                $where_conditions[] = $date_conditions;
            }
        }

        // Handle specific IDs
        if (!empty($args['include'])) {
            $placeholders = implode(',', array_fill(0, count($args['include']), '%d'));
            $where_conditions[] = "ct.term_id IN ({$placeholders})";
            $params = array_merge($params, $args['include']);
        }

        if (!empty($args['exclude'])) {
            $placeholders = implode(',', array_fill(0, count($args['exclude']), '%d'));
            $where_conditions[] = "ct.term_id NOT IN ({$placeholders})";
            $params = array_merge($params, $args['exclude']);
        }

        // Build ORDER BY
        $order_by = $this->buildOrderBy($args, $taxonomy);

        // Build LIMIT
        $page = $args['paged'] ?? 1;
        $per_page = $args['posts_per_page'] ?? $this->per_page;
        $offset = ($page - 1) * $per_page;

        // Combine everything
        $joins_sql = !empty($joins) ? implode(' ', $joins) : '';
        $where_sql = implode(' AND ', $where_conditions);

        $base_query = "FROM {$table} ct
                   LEFT JOIN {$wpdb->terms} t ON ct.term_id = t.term_id
                   LEFT JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
                   {$joins_sql}
                   WHERE {$where_sql}";

        $count_query = "SELECT COUNT(DISTINCT ct.term_id) {$base_query}";

        $main_query = "SELECT ct.term_id
                   {$base_query}
                   {$order_by}
                   LIMIT %d OFFSET %d";

        // Add limit parameters
        $count_params = $params;
        $main_params = array_merge($params, [$per_page, $offset]);

        return [
            'main_query' => $wpdb->prepare($main_query, $main_params),
            'count_query' => $wpdb->prepare($count_query, $count_params)
        ];
    }

    /**
     * Build context-based filter conditions (e.g., shops filtered by style relationships)
     * @param array $context_filter Context filter data
     * @param array &$joins Reference to joins array
     * @param array &$params Reference to params array
     * @param string $taxonomy Current taxonomy type
     * @return string SQL condition
     */
    protected function buildContextConditions(array $context_filter, array &$joins, array &$params, string $taxonomy): string
    {
        global $wpdb;

        $context_type = $context_filter['type'] ?? '';
        $context_id = $context_filter['id'] ?? 0;

        if (empty($context_type) || empty($context_id)) {
            return '';
        }

        $relationships_table = $wpdb->prefix . BASE . 'taxonomy_relationships';

        switch ($context_type) {
            case 'style':
            case 'theme':
            case 'pstyle':
                // For shops filtered by style/theme through relationships
                if ($taxonomy === 'shop') {
                    $joins[] = "INNER JOIN {$relationships_table} tr ON ct.term_id = tr.term_id";
                    $where_condition = "tr.related_term_id = %d AND tr.related_taxonomy = %s";
                    $params[] = $context_id;
                    $params[] = BASE . $context_type;
                    return $where_condition;
                }
                break;

            case 'city':
                // Filter by city
                if (isset($this->getCustomTableFields($taxonomy)['city'])) {
                    $params[] = $context_id;
                    return "ct.city = %d";
                }
                break;
        }

        return '';
    }

    /**
     * Build taxonomy filter conditions for custom table
     * @param array $tax_query Tax query array
     * @param array &$joins Reference to joins array
     * @param array &$params Reference to params array
     * @param string $taxonomy Current taxonomy type
     * @return string SQL condition
     */
    protected function buildTaxonomyConditions(array $tax_query, array &$joins, array &$params, string $taxonomy): string
    {
        global $wpdb;

        $conditions = [];
        $relation = $tax_query['relation'] ?? 'AND';

        foreach ($tax_query as $key => $query) {
            if ($key === 'relation' || !is_array($query)) {
                continue;
            }

            $query_taxonomy = $query['taxonomy'] ?? '';
            $terms = (array)($query['terms'] ?? []);
            $field = $query['field'] ?? 'term_id';
            $operator = $query['operator'] ?? 'IN';

            if (empty($query_taxonomy) || empty($terms)) {
                continue;
            }

            // Check if this taxonomy field exists in our custom table
            $custom_fields = $this->getCustomTableFields($taxonomy);
            $taxonomy_clean = str_replace(BASE, '', $query_taxonomy);

            if (isset($custom_fields[$taxonomy_clean])) {
                // Field exists in custom table - direct query
                $field_column = "ct.{$taxonomy_clean}";

                if ($field === 'slug') {
                    // Need to convert slugs to IDs first
                    $term_ids = [];
                    foreach ($terms as $slug) {
                        $term = get_term_by('slug', $slug, $query_taxonomy);
                        if ($term) {
                            $term_ids[] = $term->term_id;
                        }
                    }
                    $terms = $term_ids;
                }

                if (!empty($terms)) {
                    $placeholders = implode(',', array_fill(0, count($terms), '%d'));
                    $conditions[] = "{$field_column} {$operator} ({$placeholders})";
                    $params = array_merge($params, $terms);
                }
            } else {
                // Need to join with term relationships
                $join_alias = "tr_{$key}";
                $joins[] = "LEFT JOIN {$wpdb->term_relationships} {$join_alias} ON ct.term_id = {$join_alias}.object_id";
                $joins[] = "LEFT JOIN {$wpdb->term_taxonomy} tt_{$key} ON {$join_alias}.term_taxonomy_id = tt_{$key}.term_taxonomy_id";

                if ($field === 'slug') {
                    $joins[] = "LEFT JOIN {$wpdb->terms} t_{$key} ON tt_{$key}.term_id = t_{$key}.term_id";
                    $field_column = "t_{$key}.slug";
                    $term_values = $terms; // Use slugs directly
                } else {
                    $field_column = "tt_{$key}.term_id";
                    $term_values = array_map('intval', $terms);
                }

                $placeholders = implode(',', array_fill(0, count($term_values), $field === 'slug' ? '%s' : '%d'));
                $taxonomy_condition = "tt_{$key}.taxonomy = %s";
                $terms_condition = "{$field_column} {$operator} ({$placeholders})";

                $conditions[] = "({$taxonomy_condition} AND {$terms_condition})";
                $params[] = $query_taxonomy;
                $params = array_merge($params, $term_values);
            }
        }

        return !empty($conditions) ? '(' . implode(" {$relation} ", $conditions) . ')' : '';
    }

    /**
     * Build meta query conditions for custom table fields
     * @param array $meta_query Meta query array
     * @param array &$joins Reference to joins array
     * @param array &$params Reference to params array
     * @param string $taxonomy Taxonomy type
     * @return string SQL condition
     */
    protected function buildMetaConditions(array $meta_query, array &$joins, array &$params, string $taxonomy): string
    {
        global $wpdb;
        $conditions = [];
        $relation = $meta_query['relation'] ?? 'AND';

        // Get fields for this taxonomy to know which are in the custom table
        $custom_fields = $this->getCustomTableFields($taxonomy);

        foreach ($meta_query as $key => $query) {
            if ($key === 'relation' || !is_array($query)) {
                continue;
            }

            $meta_key = $query['key'] ?? '';
            $meta_value = $query['value'] ?? '';
            $compare = $query['compare'] ?? '=';

            if (empty($meta_key)) {
                continue;
            }

            // Remove BASE prefix if present
            $clean_key = str_replace(BASE, '', $meta_key);

            // Check if this field exists in our custom table
            if (isset($custom_fields[$clean_key])) {
                // Field is in custom table, query directly
                $column = "ct.{$clean_key}";
                $condition = $this->buildMetaComparison($column, $meta_value, $compare, $params);
                if ($condition) {
                    $conditions[] = $condition;
                }
            } else {
                // Field is in term meta, need to join
                $join_alias = "tm_{$key}";
                $joins[] = "LEFT JOIN {$wpdb->termmeta} {$join_alias} ON ct.term_id = {$join_alias}.term_id AND {$join_alias}.meta_key = %s";
                $params[] = $meta_key;

                $column = "{$join_alias}.meta_value";
                $condition = $this->buildMetaComparison($column, $meta_value, $compare, $params);
                if ($condition) {
                    $conditions[] = $condition;
                }
            }
        }

        return !empty($conditions) ? '(' . implode(" {$relation} ", $conditions) . ')' : '';
    }

    /**
     * Build comparison condition for meta fields
     * @param string $column Column name
     * @param mixed $value Value to compare
     * @param string $compare Comparison operator
     * @param array &$params Reference to params array
     * @return string SQL condition
     */
    protected function buildMetaComparison(string $column, $value, string $compare, array &$params): string
    {
        switch (strtoupper($compare)) {
            case 'LIKE':
                $params[] = '%' . $value . '%';
                return "{$column} LIKE %s";

            case 'NOT LIKE':
                $params[] = '%' . $value . '%';
                return "{$column} NOT LIKE %s";

            case 'IN':
                if (is_array($value)) {
                    $placeholders = implode(',', array_fill(0, count($value), '%s'));
                    $params = array_merge($params, $value);
                    return "{$column} IN ({$placeholders})";
                }
                break;

            case 'NOT IN':
                if (is_array($value)) {
                    $placeholders = implode(',', array_fill(0, count($value), '%s'));
                    $params = array_merge($params, $value);
                    return "{$column} NOT IN ({$placeholders})";
                }
                break;

            case 'BETWEEN':
                if (is_array($value) && count($value) === 2) {
                    $params[] = $value[0];
                    $params[] = $value[1];
                    return "{$column} BETWEEN %s AND %s";
                }
                break;

            case '!=':
            case '<>':
                $params[] = $value;
                return "{$column} != %s";

            case '>':
                $params[] = $value;
                return "{$column} > %s";

            case '>=':
                $params[] = $value;
                return "{$column} >= %s";

            case '<':
                $params[] = $value;
                return "{$column} < %s";

            case '<=':
                $params[] = $value;
                return "{$column} <= %s";

            case '=':
            default:
                $params[] = $value;
                return "{$column} = %s";
        }

        return '';
    }

    /**
     * Build date query conditions
     * @param array $date_query Date query array
     * @param array &$params Reference to params array
     * @return string SQL condition
     */
    protected function buildDateConditions(array $date_query, array &$params): string
    {
        $conditions = [];

        foreach ($date_query as $query) {
            if (!is_array($query)) continue;

            $column = $query['column'] ?? 'updated_at';
            $year = $query['year'] ?? null;
            $month = $query['month'] ?? null;
            $day = $query['day'] ?? null;
            $after = $query['after'] ?? null;
            $before = $query['before'] ?? null;

            if ($year) {
                $params[] = $year;
                $conditions[] = "YEAR(ct.{$column}) = %d";
            }

            if ($month) {
                $params[] = $month;
                $conditions[] = "MONTH(ct.{$column}) = %d";
            }

            if ($day) {
                $params[] = $day;
                $conditions[] = "DAY(ct.{$column}) = %d";
            }

            if ($after) {
                $params[] = $after;
                $conditions[] = "ct.{$column} > %s";
            }

            if ($before) {
                $params[] = $before;
                $conditions[] = "ct.{$column} < %s";
            }
        }

        return !empty($conditions) ? '(' . implode(' AND ', $conditions) . ')' : '';
    }

    /**
     * Build ORDER BY clause
     * @param array $args Query arguments
     * @param string $taxonomy Taxonomy type
     * @return string ORDER BY clause
     */
    protected function buildOrderBy(array $args, string $taxonomy): string
    {
        $orderby = $args['orderby'] ?? 'name';
        $order = $args['order'] ?? 'ASC';

        // Validate order direction
        if (!in_array($order, ['ASC', 'DESC'])) {
            $order = 'ASC';
        }

        if (str_contains($orderby, 'RAND')) {
            return "ORDER BY {$orderby}";
        }

        switch ($orderby) {
            case 'name':
                return "ORDER BY ct.name {$order}";

            case 'count':
                return "ORDER BY tt.count {$order}";

            case 'term_id':
            case 'id':
                return "ORDER BY ct.term_id {$order}";

            case 'slug':
                return "ORDER BY t.slug {$order}";

            case 'date':
            case 'updated':
                return "ORDER BY ct.updated_at {$order}";

            default:
                // Check if it's a custom field in our table
                $custom_fields = $this->getCustomTableFields($taxonomy);
                if (isset($custom_fields[$orderby])) {
                    return "ORDER BY ct.{$orderby} {$order}";
                }

                // Default to name
                return "ORDER BY ct.name {$order}";
        }
    }

    /**
     * Get custom table fields for a taxonomy
     * @param string $taxonomy Taxonomy type
     * @return array Field definitions
     */
    protected function getCustomTableFields(string $taxonomy): array
    {
        return jvbContentTaxonomiesTableFields($taxonomy)['fields'] ?? [];
    }
}