<?php

/**
 * Data Sources Detection
 */

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

/**
 * Get all available data sources
 */
function askagento_get_data_sources($post_types = ['product'])
{
    return [
        'taxonomies' => askagento_get_taxonomy_sources($post_types),
        'meta'       => askagento_get_meta_sources($post_types),
    ];
}

/**
 * Normalize labels for Auto Filter data sources (title case each word for consistent UI).
 *
 * Fixes mixed casing from core/WooCommerce (e.g. "Product categories" → "Product Categories").
 *
 * @param string $label Raw label.
 * @return string
 */
function askagento_normalize_data_source_label($label)
{
    $label = trim(wp_strip_all_tags((string) $label));
    if ($label === '') {
        return '';
    }
    if (function_exists('mb_convert_case')) {
        return mb_convert_case($label, MB_CASE_TITLE, 'UTF-8');
    }

    return ucwords(strtolower($label));
}

/**
 * Get taxonomy sources
 */
function askagento_get_taxonomy_sources($post_types = ['product'])
{
    $result = [];

    foreach ($post_types as $post_type) {
        $taxonomies = get_object_taxonomies($post_type, 'objects');

        foreach ($taxonomies as $tax_name => $tax_obj) {
            // WooCommerce global attributes use pa_* taxonomies; core registers them as
            // non-public so they are omitted from storefront queries, but they are valid
            // filter dimensions - include them for product sources.
            $is_wc_global_attribute_taxonomy = (
                $post_type === 'product'
                && preg_match('/^pa_/i', (string) $tax_name)
                && taxonomy_exists($tax_name)
            );

            // Skip private taxonomies (except WooCommerce pa_* on products).
            if (!$tax_obj->public && !$tax_obj->publicly_queryable && !$is_wc_global_attribute_taxonomy) {
                continue;
            }

            // Skip if already added
            if (isset($result[$tax_name])) {
                continue;
            }

            // Terms count
            $terms_count = wp_count_terms(['taxonomy' => $tax_name, 'hide_empty' => false]);
            if (is_wp_error($terms_count)) {
                $terms_count = 0;
            }

            // Products count
            $products_count = askagento_count_posts_with_taxonomy($post_type, $tax_name);

            // Sample values
            $sample_terms = get_terms([
                'taxonomy'   => $tax_name,
                'hide_empty' => true,
                'number'     => 5,
                'orderby'    => 'count',
                'order'      => 'DESC',
                'fields'     => 'names',
            ]);

            if (is_wp_error($sample_terms)) {
                $sample_terms = [];
            }

            $result[$tax_name] = [
                'source'         => 'taxonomy:' . $tax_name,
                'name'           => $tax_name,
                'label'          => askagento_normalize_data_source_label($tax_obj->label),
                'type'           => 'taxonomy',
                'hierarchical'   => $tax_obj->hierarchical,
                'terms_count'    => (int) $terms_count,
                'values_count'   => (int) $terms_count,
                'products_count' => $products_count,
                'sample_values'  => $sample_terms,
                'suggested_type' => $tax_obj->hierarchical ? 'checkbox' : 'checkbox',
            ];
        }
    }

    // Sort by products count
    uasort($result, function ($a, $b) {
        return $b['products_count'] - $a['products_count'];
    });

    return $result;
}

/**
 * Get meta field sources
 */
function askagento_get_meta_sources($post_types = ['product'])
{
    global $wpdb;

    $result = [];
    $post_types = array_values(
        array_filter(
            array_map('sanitize_key', $post_types)
        )
    );

    if (empty($post_types)) {
        return array();
    }

    $post_type_placeholders = implode(
        ', ',
        array_fill(0, count($post_types), '%s')
    );

    // Match meta keys beginning with "_" or "wp_".
    $private_meta_pattern = $wpdb->esc_like('_') . '%';
    $wp_meta_pattern      = $wpdb->esc_like('wp_') . '%';

    $query_args = array_merge(
        $post_types,
        array(
            $private_meta_pattern,
            $wp_meta_pattern,
        )
    );

    // Get meta keys with counts.
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Direct query required to enumerate all meta keys with usage counts across configured post types for the data-source detection UI. $post_type_placeholders holds only generated %s tokens; the post types and LIKE patterns are bound through $query_args, passed as a single array that prepare() accepts but the sniff cannot count statically.
    $meta_keys = $wpdb->get_results(
        $wpdb->prepare(
            "
        SELECT
            pm.meta_key,
            COUNT(DISTINCT pm.post_id) AS post_count,
            COUNT(DISTINCT pm.meta_value) AS value_count,
            MIN(pm.meta_value) AS min_value,
            MAX(pm.meta_value) AS max_value
        FROM {$wpdb->postmeta} AS pm
        INNER JOIN {$wpdb->posts} AS p
            ON pm.post_id = p.ID
        WHERE p.post_type IN ({$post_type_placeholders})
            AND p.post_status IN ('publish', 'private')
            AND pm.meta_key NOT LIKE %s
            AND pm.meta_key NOT LIKE %s
            AND pm.meta_value != ''
        GROUP BY pm.meta_key
        HAVING COUNT(DISTINCT pm.post_id) > 0
        ORDER BY post_count DESC
        LIMIT 100
        ",
            $query_args
        )
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

    foreach ($meta_keys as $meta) {
        $is_numeric = askagento_is_numeric_meta($meta->meta_key, $post_types);

        // Sample values
        $sample_values = [];

        if ( ! $is_numeric && (int) $meta->value_count <= 50 ) {
            $query_args   = $post_types;
            $query_args[] = $meta->meta_key;

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Direct query required to fetch distinct sample meta values for low-cardinality fields shown in the data-source detection UI. $post_type_placeholders holds only generated %s tokens; the post types and meta key are bound through $query_args, passed as a single array that prepare() accepts but the sniff cannot count statically.
            $sample_values = $wpdb->get_col(
                $wpdb->prepare(
                    "
                    SELECT DISTINCT pm.meta_value
                    FROM {$wpdb->postmeta} AS pm
                    INNER JOIN {$wpdb->posts} AS p
                        ON pm.post_id = p.ID
                    WHERE p.post_type IN ({$post_type_placeholders})
                        AND p.post_status IN ('publish', 'private')
                        AND pm.meta_key = %s
                        AND pm.meta_value != ''
                    ORDER BY pm.meta_value
                    LIMIT 10
                    ",
                    $query_args
                )
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
        }

        // Suggest type
        if ($is_numeric) {
            $suggested_type = 'range';
        } elseif ($meta->value_count <= 10) {
            $suggested_type = 'checkbox';
        } elseif ($meta->value_count <= 30) {
            $suggested_type = 'dropdown';
        } else {
            $suggested_type = 'search';
        }

        $result[$meta->meta_key] = [
            'source'         => 'meta:' . $meta->meta_key,
            'name'           => $meta->meta_key,
            'label'          => askagento_normalize_data_source_label(askagento_format_meta_label($meta->meta_key)),
            'type'           => $is_numeric ? 'meta_numeric' : 'meta_string',
            'is_numeric'     => $is_numeric,
            'products_count' => (int) $meta->post_count,
            'values_count'   => (int) $meta->value_count,
            'min_value'      => $is_numeric ? (float) $meta->min_value : null,
            'max_value'      => $is_numeric ? (float) $meta->max_value : null,
            'sample_values'  => $sample_values,
            'suggested_type' => $suggested_type,
        ];
    }

    // WooCommerce fields
    if (in_array('product', $post_types) && class_exists('WooCommerce')) {
        $result = askagento_add_wc_fields($result);
    }

    return $result;
}

/**
 * Check if meta is numeric
 */
function askagento_is_numeric_meta($meta_key, $post_types)
{
    global $wpdb;

    $post_types = !empty($post_types) && is_array($post_types) ? array_values($post_types) : ['product'];
    $placeholders = implode(', ', array_fill(0, count($post_types), '%s'));

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Direct query required to sample meta values and determine whether a field is numeric for data-source type detection. $placeholders holds only generated %s tokens (array_fill over count()); the post types and meta key are bound through the array passed to prepare(), which the sniff cannot count statically.
    $values = $wpdb->get_col($wpdb->prepare("
        SELECT DISTINCT pm.meta_value
        FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
        WHERE p.post_type IN ({$placeholders})
          AND p.post_status IN ('publish', 'private')
          AND pm.meta_key = %s
          AND pm.meta_value != ''
        LIMIT 100
    ", array_merge($post_types, [$meta_key])));
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

    if (empty($values)) {
        return false;
    }

    $numeric_count = 0;
    foreach ($values as $value) {
        if (is_numeric($value)) {
            $numeric_count++;
        }
    }

    return ($numeric_count / count($values)) > 0.9;
}

/**
 * Add WooCommerce fields
 */
function askagento_add_wc_fields($result)
{
    global $wpdb;

    // Price
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query required to retrieve WooCommerce price min/max and product count for the data-source detection UI.
    $price_stats = $wpdb->get_row("
        SELECT MIN(CAST(meta_value AS DECIMAL(10,2))) as min_price,
               MAX(CAST(meta_value AS DECIMAL(10,2))) as max_price,
               COUNT(DISTINCT post_id) as count
        FROM {$wpdb->postmeta}
        WHERE meta_key = '_price'
          AND meta_value != ''
          AND meta_value REGEXP '^[0-9]+\.?[0-9]*$'
    ");

    if ($price_stats && $price_stats->count > 0) {
        $result['_price'] = [
            'source'         => 'wc:price',
            'name'           => '_price',
            'label'          => askagento_normalize_data_source_label(__('Price', 'askagento')),
            'type'           => 'wc_price',
            'is_numeric'     => true,
            'products_count' => (int) $price_stats->count,
            'values_count'   => null,
            'min_value'      => (float) $price_stats->min_price,
            'max_value'      => (float) $price_stats->max_price,
            'sample_values'  => [],
            'suggested_type' => 'range',
        ];
    }

    // Stock status
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query required to count products with a stock status meta key for the WooCommerce data-source detection UI.
    $stock_count = $wpdb->get_var("
        SELECT COUNT(DISTINCT post_id)
        FROM {$wpdb->postmeta}
        WHERE meta_key = '_stock_status'
    ");

    if ($stock_count > 0) {
        $result['_stock_status'] = [
            'source'         => 'wc:stock_status',
            'name'           => '_stock_status',
            'label'          => askagento_normalize_data_source_label(__('Stock Status', 'askagento')),
            'type'           => 'wc_stock',
            'is_numeric'     => false,
            'products_count' => (int) $stock_count,
            'values_count'   => 3,
            'min_value'      => null,
            'max_value'      => null,
            'sample_values'  => ['instock', 'outofstock', 'onbackorder'],
            'suggested_type' => 'checkbox',
        ];
    }

    return $result;
}

/**
 * Count posts with taxonomy
 */
function askagento_count_posts_with_taxonomy($post_type, $taxonomy)
{
    global $wpdb;

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query required to count distinct posts associated with a taxonomy across term relationships for the data-source detection UI.
    return (int) $wpdb->get_var($wpdb->prepare("
        SELECT COUNT(DISTINCT tr.object_id)
        FROM {$wpdb->term_relationships} tr
        INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
        INNER JOIN {$wpdb->posts} p ON tr.object_id = p.ID
        WHERE tt.taxonomy = %s
          AND p.post_type = %s
          AND p.post_status IN ('publish', 'private')
    ", $taxonomy, $post_type));
}

/**
 * Map the wc:* source suffix (the part after "wc:") to a WooCommerce post meta key.
 *
 * Registers use {@see askagento_add_wc_fields()} fragments `price` and `stock_status`; older configs may use `_price` / `_stock_status`.
 *
 * @param string $source_name Second segment of `wc:{source_name}`.
 * @return string|null Meta key (e.g. `_price`) or null if unknown.
 */
function askagento_wc_source_to_meta_key($source_name)
{
    $source_name = (string) $source_name;

    if ($source_name === 'price' || $source_name === '_price') {
        return '_price';
    }

    if ($source_name === 'stock_status' || $source_name === '_stock_status') {
        return '_stock_status';
    }

    return null;
}

/**
 * Format meta key as label
 */
function askagento_format_meta_label($meta_key)
{
    $label = preg_replace('/^(_|pa_|product_)/', '', $meta_key);
    $label = str_replace(['_', '-'], ' ', $label);
    return ucwords($label);
}
