<?php

declare(strict_types=1);

namespace Instinctor\Services;

use Instinctor\Core\Config;
use Instinctor\Core\DB;


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

/**
 * Product Search Service
 * AJAX search with autocomplete and filtering
 */
class ProductSearchService
{
    /**
     * Search products
     */
    public static function search(string $query, array $filters = [], int $limit = 20, int $offset = 0): array
    {
        $p = DB::prefix();
        $products_table = DB::prefix() . 'instinctor_products';
        $categories_table = DB::prefix() . 'instinctor_product_categories';

        $where = ["p.status = 'published'"];
        $params = [];

        // Text search
        if (!empty($query)) {
            $search = '%' . DB::esc_like($query) . '%';
            $where[] = "(p.name LIKE %s OR p.sku LIKE %s OR p.description LIKE %s)";
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }

        // Category filter
        if (!empty($filters['category'])) {
            $categoryId = intval($filters['category']);
            $where[] = "p.id IN (SELECT product_id FROM $categories_table WHERE category_id = %d)";
            $params[] = $categoryId;
        }

        // Price range
        if (!empty($filters['min_price'])) {
            $where[] = "COALESCE(p.sale_price, p.price) >= %f";
            $params[] = floatval($filters['min_price']);
        }
        if (!empty($filters['max_price'])) {
            $where[] = "COALESCE(p.sale_price, p.price) <= %f";
            $params[] = floatval($filters['max_price']);
        }

        // Stock status
        if (!empty($filters['in_stock'])) {
            $where[] = "p.stock_status = 'instock'";
        }

        // On sale
        if (!empty($filters['on_sale'])) {
            $where[] = "p.sale_price IS NOT NULL AND p.sale_price < p.price";
        }

        // Build sort
        $orderBy = 'p.created_at DESC';
        if (!empty($filters['sort'])) {
            switch ($filters['sort']) {
                case 'price_asc':
                    $orderBy = 'COALESCE(p.sale_price, p.price) ASC';
                    break;
                case 'price_desc':
                    $orderBy = 'COALESCE(p.sale_price, p.price) DESC';
                    break;
                case 'name_asc':
                    $orderBy = 'p.name ASC';
                    break;
                case 'name_desc':
                    $orderBy = 'p.name DESC';
                    break;
                case 'popularity':
                    $orderBy = 'sales_count DESC';
                    break;
                case 'newest':
                default:
                    $orderBy = 'p.created_at DESC';
            }
        }

        $whereClause = implode(' AND ', $where);
        $params[] = $limit;
        $params[] = $offset;

        $sql = "SELECT p.*, 
                    COALESCE((SELECT SUM(quantity) FROM {$p}instinctor_order_items WHERE product_id = p.id), 0) as sales_count
                FROM $products_table p
                WHERE $whereClause
                ORDER BY $orderBy
                LIMIT %d OFFSET %d";

        $products = DB::get_results(DB::prepare($sql, ...$params));

        // Get total count
        $countParams = array_slice($params, 0, -2);
        $countSql = "SELECT COUNT(*) FROM $products_table p WHERE $whereClause";
        $total = (int) DB::get_var(DB::prepare($countSql, ...$countParams));

        // Add image URLs
        foreach ($products as &$product) {
            $product->image_url = $product->featured_image 
                ? wp_get_attachment_image_url($product->featured_image, 'medium') 
                : '';
            $product->display_price = $product->sale_price ?: $product->price;
        }

        return [
            'products' => $products,
            'total' => $total,
            'pages' => ceil($total / $limit),
        ];
    }

    /**
     * Autocomplete search (quick results)
     */
    public static function autocomplete(string $query, int $limit = 8): array
    {
        $table = DB::prefix() . 'instinctor_products';

        $search = '%' . DB::esc_like($query) . '%';

        $products = DB::get_results(DB::prepare(
            "SELECT id, name, slug, price, sale_price, featured_image, sku
             FROM $table
             WHERE status = 'published' AND (name LIKE %s OR sku LIKE %s)
             ORDER BY 
                CASE WHEN name LIKE %s THEN 0 ELSE 1 END,
                name ASC
             LIMIT %d",
            $search, $search, DB::esc_like($query) . '%', $limit
        ));

        foreach ($products as &$product) {
            $product->image_url = $product->featured_image 
                ? wp_get_attachment_image_url($product->featured_image, 'thumbnail') 
                : '';
            $product->url = home_url('/product/' . $product->slug);
            $product->formatted_price = CurrencyService::formatPrice($product->sale_price ?: $product->price);
        }

        return $products;
    }

    /**
     * Get filter options for current results
     */
    public static function getFilterOptions(?int $categoryId = null): array
    {
        $products_table = DB::prefix() . 'instinctor_products';
        $categories_table = DB::prefix() . 'instinctor_categories';
        $product_categories_table = DB::prefix() . 'instinctor_product_categories';

        // Categories with counts
        $categories = DB::get_results(
            "SELECT c.id, c.name, c.slug, COUNT(pc.product_id) as count
             FROM $categories_table c
             LEFT JOIN $product_categories_table pc ON c.id = pc.category_id
             LEFT JOIN $products_table p ON pc.product_id = p.id AND p.status = 'published'
             GROUP BY c.id
             HAVING count > 0
             ORDER BY c.name ASC"
        );

        // Price range
        $priceRange = DB::get_row(
            "SELECT MIN(COALESCE(sale_price, price)) as min_price, MAX(COALESCE(sale_price, price)) as max_price
             FROM $products_table
             WHERE status = 'published'"
        );

        // Available attributes (from variations)
        $attributes = self::getAvailableAttributes($categoryId);

        return [
            'categories' => $categories,
            'price_range' => [
                'min' => floatval($priceRange->min_price ?? 0),
                'max' => floatval($priceRange->max_price ?? 1000),
            ],
            'attributes' => $attributes,
            'sort_options' => [
                'newest' => 'Newest',
                'price_asc' => 'Price: Low to High',
                'price_desc' => 'Price: High to Low',
                'name_asc' => 'Name: A to Z',
                'name_desc' => 'Name: Z to A',
                'popularity' => 'Best Selling',
            ],
        ];
    }

    /**
     * Get available attributes for filtering
     */
    private static function getAvailableAttributes(?int $categoryId = null): array
    {
        $variations_table = DB::prefix() . 'instinctor_product_variations';
        $products_table = DB::prefix() . 'instinctor_products';

        $sql = "SELECT v.attributes 
                FROM $variations_table v
                JOIN $products_table p ON v.product_id = p.id
                WHERE p.status = 'published' AND v.enabled = 1";

        if ($categoryId) {
            $product_categories_table = DB::prefix() . 'instinctor_product_categories';
            $sql .= DB::prepare(
                " AND p.id IN (SELECT product_id FROM $product_categories_table WHERE category_id = %d)",
                $categoryId
            );
        }

        $variations = DB::get_results($sql);

        $attributes = [];
        foreach ($variations as $v) {
            $attrs = json_decode($v->attributes, true) ?: [];
            foreach ($attrs as $key => $value) {
                if (!isset($attributes[$key])) {
                    $attributes[$key] = [];
                }
                if (!in_array($value, $attributes[$key])) {
                    $attributes[$key][] = $value;
                }
            }
        }

        // Sort values
        foreach ($attributes as &$values) {
            sort($values);
        }

        return $attributes;
    }

    /**
     * Get search suggestions based on popular searches
     */
    public static function getSuggestions(string $query): array
    {
        $p = DB::prefix();
        $table = DB::prefix() . 'instinctor_products';

        // Get matching product names
        $search = DB::esc_like($query) . '%';
        
        $suggestions = DB::get_col(DB::prepare(
            "SELECT DISTINCT name FROM $table WHERE status = 'published' AND name LIKE %s LIMIT 5",
            $search
        ));

        // Get matching categories
        $categories = DB::get_col(DB::prepare(
            "SELECT DISTINCT name FROM {$p}instinctor_categories WHERE name LIKE %s LIMIT 3",
            $search
        ));

        return array_merge($suggestions, array_map(fn($c) => "in $c", $categories));
    }

    /**
     * Log search for analytics
     */
    public static function logSearch(string $query, int $resultCount): void
    {
        // Future: implement search analytics
        // For now, just increment a counter
        $searches = Config::get('instinctor_search_log', []);
        $query = strtolower(trim($query));
        
        if (!isset($searches[$query])) {
            $searches[$query] = ['count' => 0, 'results' => $resultCount];
        }
        $searches[$query]['count']++;
        $searches[$query]['last_searched'] = current_time('mysql');

        // Keep only top 1000 searches
        if (count($searches) > 1000) {
            uasort($searches, fn($a, $b) => $b['count'] - $a['count']);
            $searches = array_slice($searches, 0, 1000, true);
        }

        Config::set('instinctor_search_log', $searches);
    }

    /**
     * Get popular searches
     */
    public static function getPopularSearches(int $limit = 10): array
    {
        $searches = Config::get('instinctor_search_log', []);
        uasort($searches, fn($a, $b) => $b['count'] - $a['count']);
        
        return array_slice(array_keys($searches), 0, $limit);
    }
}
