<?php
/**
 * Fast Variations Controller (Cache-based)
 * 
 * Ultra-fast REST API endpoint using cache table
 * 
 * @package VariationHub
 * @since 1.0.0
 * 
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
 * Reason: This controller uses a CUSTOM cache table (wp_varihu_variation_cache) for performance.
 * WordPress wp_cache_* functions do NOT work with custom tables - they only cache wp_posts/wp_postmeta.
 * Direct SQL queries on our custom table are intentional and 10x faster than WooCommerce's native API.
 */

namespace VariationHub\API;

defined('ABSPATH') || exit;

class Fast_Variations_Controller extends \WP_REST_Controller {
    
    /**
     * Namespace
     */
    protected $namespace = 'variation-hub/v1';
    
    /**
     * Route base
     */
    protected $rest_base = 'variations/fast';
    
    /**
     * Register routes
     */
    public function register_routes() {
        register_rest_route($this->namespace, '/' . $this->rest_base, [
            [
                'methods' => \WP_REST_Server::READABLE,
                'callback' => [$this, 'get_items_fast'],
                'permission_callback' => [$this, 'get_items_permissions_check'],
                'args' => $this->get_collection_params(),
            ],
        ]);
        
        register_rest_route($this->namespace, '/' . $this->rest_base . '/grouped', [
            [
                'methods' => \WP_REST_Server::READABLE,
                'callback' => [$this, 'get_items_grouped_fast'],
                'permission_callback' => [$this, 'get_items_permissions_check'],
                'args' => $this->get_collection_params(),
            ],
        ]);
    }
    
    /**
     * Permission check
     */
    public function get_items_permissions_check($request) {
        return current_user_can('manage_woocommerce');
    }
    
    /**
     * Get variations (FAST - from cache)
     * 
     * @param \WP_REST_Request $request
     * @return \WP_REST_Response
     */
    public function get_items_fast($request) {
        global $wpdb;
        
        $params = $request->get_params();
        
        // Extract params
        $search = $params['search'] ?? '';
        $attributes = $params['attributes'] ?? ''; // 🔥 ATTRIBUTES szűrő!
        $parent_id = $params['parent_id'] ?? null;
        // 🔥 Support both min_price AND price_min (frontend sends price_min)
        $min_price = $params['min_price'] ?? $params['price_min'] ?? null;
        $max_price = $params['max_price'] ?? $params['price_max'] ?? null;
        $stock_status = $params['stock_status'] ?? null;
        $filter_sale_price = $params['filter_sale_price'] ?? false;
        $sale_date_from = $params['sale_date_from'] ?? null;
        $sale_date_to = $params['sale_date_to'] ?? null;
        $only_active_sales = $params['only_active_sales'] ?? false;
        $limit = min($params['limit'] ?? 100, 500);
        $offset = $params['offset'] ?? 0;
        $orderby = $params['orderby'] ?? 'id';
        $order = strtoupper($params['order'] ?? 'ASC');
        $checked_only = $params['checked_only'] ?? false;
        
        $table_name = esc_sql(\VariationHub\Core\Cache_Table::get_table_name());
        
        // Build dynamic values array for WHERE clause
        $where_values = [];
        
        // Search filter (sanitized with esc_like)
        if (!empty($search)) {
            $search_like = '%' . $wpdb->esc_like($search) . '%';
            $where_values[] = $search_like;
            $where_values[] = $search_like;
        }
        
        // Attributes filter (JSON search, sanitized)
        if (!empty($attributes)) {
            $attr_like = '%' . $wpdb->esc_like($attributes) . '%';
            $where_values[] = $attr_like;
        }
        
        // Parent product ID filter
        if ($parent_id) {
            $where_values[] = $parent_id;
        }
        
        // Price range filters
        if ($min_price !== null) {
            $where_values[] = floatval($min_price);
        }
        
        if ($max_price !== null) {
            $where_values[] = floatval($max_price);
        }
        
        // Stock status filter
        if ($stock_status) {
            $where_values[] = $stock_status;
        }
        
        // Date range filter for sale periods (OVERLAP logic)
        if ($sale_date_from && $sale_date_to) {
            $where_values[] = $sale_date_to;
            $where_values[] = $sale_date_from;
        } elseif ($sale_date_from) {
            $where_values[] = $sale_date_from;
        } elseif ($sale_date_to) {
            $where_values[] = $sale_date_to;
        }
        
        // Active sales filter (currently running sales)
        if ($only_active_sales) {
            $current_date = current_time('Y-m-d');
            $where_values[] = $current_date;
            $where_values[] = $current_date;
        }
        
        // ✅ WORDPRESS.ORG COMPLIANT: Build WHERE clause with conditional concatenation (no variable interpolation!)
        // Build query string statically with placeholders - each condition appended directly to query string
        $where_clause = "post_status = 'publish'";
        
        // Search filter
        if (!empty($search)) {
            $where_clause .= " AND (parent_name LIKE %s OR sku LIKE %s)";
        }
        
        // Attributes filter
        if (!empty($attributes)) {
            $where_clause .= " AND attributes LIKE %s";
        }
        
        // Parent product ID filter
        if ($parent_id) {
            $where_clause .= " AND parent_id = %d";
        }
        
        // Price range filters
        if ($min_price !== null) {
            $where_clause .= " AND price >= %f";
        }
        if ($max_price !== null) {
            $where_clause .= " AND price <= %f";
        }
        
        // Stock status filter
        if ($stock_status) {
            $where_clause .= " AND stock_status = %s";
        }
        
        // Sale price filter
        if ($filter_sale_price) {
            $where_clause .= " AND sale_price IS NOT NULL AND sale_price != '' AND sale_price > 0";
        }
        
        // Sale date range filter
        if ($sale_date_from && $sale_date_to) {
            $where_clause .= " AND date_on_sale_from IS NOT NULL AND date_on_sale_to IS NOT NULL";
            $where_clause .= " AND DATE(date_on_sale_from) <= %s AND DATE(date_on_sale_to) >= %s";
        } elseif ($sale_date_from) {
            $where_clause .= " AND date_on_sale_to IS NOT NULL AND DATE(date_on_sale_to) >= %s";
        } elseif ($sale_date_to) {
            $where_clause .= " AND date_on_sale_from IS NOT NULL AND DATE(date_on_sale_from) <= %s";
        }
        
        // Active sales filter
        if ($only_active_sales) {
            $current_date = current_time('Y-m-d');
            $where_clause .= " AND sale_price IS NOT NULL AND sale_price > 0";
            $where_clause .= " AND date_on_sale_from IS NOT NULL AND date_on_sale_to IS NOT NULL";
            $where_clause .= " AND DATE(date_on_sale_from) <= %s AND DATE(date_on_sale_to) >= %s";
        }
        
        // Checked items filter
        if ($checked_only) {
            $where_clause .= " AND checked = 1";
        }
        
        // Count total (prepare with all values if any exist)
        $count_query = "SELECT COUNT(*) FROM {$table_name} WHERE " . $where_clause;
        if (!empty($where_values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe (wpdb->prefix), $where_clause built statically
            $total = (int) $wpdb->get_var($wpdb->prepare($count_query, ...$where_values));
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- No dynamic content, $table_name safe
            $total = (int) $wpdb->get_var($count_query);
        }
        
        // Validate orderby
        $allowed_orderby = ['id', 'parent_id', 'price', 'sale_price', 'stock_quantity', 'sku'];
        if (!in_array($orderby, $allowed_orderby)) {
            $orderby = 'id';
        }
        
        // Validate order
        if (!in_array($order, ['ASC', 'DESC'])) {
            $order = 'ASC';
        }
        
        // ✅ SECURITY FIX: Escape SQL identifiers to prevent injection
        $orderby_escaped = esc_sql($orderby);
        $order_escaped = esc_sql($order);
        
        // Get results
        $select_query = "SELECT * FROM {$table_name} WHERE " . $where_clause . " ORDER BY {$orderby_escaped} {$order_escaped} LIMIT %d OFFSET %d";
        $query_values = array_merge($where_values, [$limit, $offset]);
        
        $start_time = microtime(true);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name safe (wpdb->prefix), $where_clause built statically, $orderby/$order validated and escaped
        $results = $wpdb->get_results(
            $wpdb->prepare($select_query, ...$query_values),
            ARRAY_A
        );
        $query_time = round((microtime(true) - $start_time) * 1000, 2);
        
        // Decode JSON attributes
        foreach ($results as &$row) {
            $row['attributes'] = json_decode($row['attributes'], true) ?: [];
            
            // Convert numeric strings back to numbers for frontend
            $row['id'] = (int) $row['id'];
            $row['parent_id'] = (int) $row['parent_id'];
            $row['price'] = (float) $row['price'];
            $row['regular_price'] = $row['regular_price'] ? (float) $row['regular_price'] : null;
            $row['sale_price'] = $row['sale_price'] ? (float) $row['sale_price'] : null;
            $row['stock_quantity'] = (int) $row['stock_quantity'];
            $row['image_id'] = $row['image_id'] ? (int) $row['image_id'] : null;
            $row['manage_stock'] = (bool) $row['manage_stock'];
            $row['virtual'] = (bool) $row['virtual'];
            $row['downloadable'] = (bool) $row['downloadable'];
            $row['low_stock_threshold'] = $row['low_stock_threshold'] ? (int) $row['low_stock_threshold'] : null;
            
            // Physical properties
            $row['weight'] = $row['weight'] ? (float) $row['weight'] : null;
            $row['length'] = $row['length'] ? (float) $row['length'] : null;
            $row['width'] = $row['width'] ? (float) $row['width'] : null;
            $row['height'] = $row['height'] ? (float) $row['height'] : null;
            
            // Map post_status to status for frontend
            $row['status'] = $row['post_status'];
        }
        
        // Get valid tax classes for dropdown
        $tax_classes = \WC_Tax::get_tax_class_slugs();
        
        // Add 'parent' and empty string (standard) as special options
        $tax_class_options = array_merge(['parent', ''], $tax_classes);
        
        // Get WooCommerce currency symbol (decode HTML entities like &#36; → $)
        $currency_symbol = html_entity_decode(get_woocommerce_currency_symbol(), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        // Get WooCommerce formatting settings
        $wc_settings = [
            'currency_symbol' => $currency_symbol,
            'currency_position' => get_option('woocommerce_currency_pos', 'right_space'), // left, right, left_space, right_space
            'price_decimals' => wc_get_price_decimals(), // Usually 0, 2, or 3
            'price_decimal_separator' => wc_get_price_decimal_separator(), // . or ,
            'price_thousand_separator' => wc_get_price_thousand_separator(), // , or space or .
            'weight_unit' => get_option('woocommerce_weight_unit', 'kg'), // kg, g, lbs, oz
            'dimension_unit' => get_option('woocommerce_dimension_unit', 'cm'), // m, cm, mm, in, yd
        ];
        
        return new \WP_REST_Response([
            'success' => true,
            'data' => [
                'variations' => $results,
                'total' => $total,
                'offset' => $offset,
                'limit' => $limit,
                'query_time_ms' => $query_time,
                'source' => 'cache',
                'tax_class_options' => $tax_class_options, // 🎯 Tax class dropdown options
                'wc_settings' => $wc_settings, // 🎯 WooCommerce formatting settings
            ],
        ], 200);
    }
    
    /**
     * Get grouped variations (FAST - from cache)
     * 
     * @param \WP_REST_Request $request
     * @return \WP_REST_Response
     */
    public function get_items_grouped_fast($request) {
        global $wpdb;
        
        $params = $request->get_params();
        $search = $params['search'] ?? '';
        $attributes = $params['attributes'] ?? ''; // 🔥 ATTRIBUTES szűrő!
        // 🔥 Support both min_price AND price_min (frontend sends price_min)
        $min_price = $params['min_price'] ?? $params['price_min'] ?? null;
        $max_price = $params['max_price'] ?? $params['price_max'] ?? null;
        $stock_status = $params['stock_status'] ?? null;
        $filter_sale_price = $params['filter_sale_price'] ?? false;
        $sale_date_from = $params['sale_date_from'] ?? null;
        $sale_date_to = $params['sale_date_to'] ?? null;
        $only_active_sales = $params['only_active_sales'] ?? false;
        $checked_only = $params['checked_only'] ?? false;
        
        $table_name = esc_sql(\VariationHub\Core\Cache_Table::get_table_name());
        
        // Build dynamic values array for WHERE clause
        $where_values = [];
        
        // Search filter (sanitized with esc_like)
        if (!empty($search)) {
            $search_like = '%' . $wpdb->esc_like($search) . '%';
            $where_values[] = $search_like;
            $where_values[] = $search_like;
        }
        
        // Attributes filter (JSON search, sanitized)
        if (!empty($attributes)) {
            $attr_like = '%' . $wpdb->esc_like($attributes) . '%';
            $where_values[] = $attr_like;
        }
        
        // Price range filters
        if ($min_price !== null) {
            $where_values[] = floatval($min_price);
        }
        
        if ($max_price !== null) {
            $where_values[] = floatval($max_price);
        }
        
        // Stock status filter
        if ($stock_status) {
            $where_values[] = $stock_status;
        }
        
        // Date range filter for sale periods (OVERLAP logic)
        if ($sale_date_from && $sale_date_to) {
            $where_values[] = $sale_date_to;
            $where_values[] = $sale_date_from;
        } elseif ($sale_date_from) {
            $where_values[] = $sale_date_from;
        } elseif ($sale_date_to) {
            $where_values[] = $sale_date_to;
        }
        
        // Active sales filter (currently running sales)
        if ($only_active_sales) {
            $current_date = current_time('Y-m-d');
            $where_values[] = $current_date;
            $where_values[] = $current_date;
        }
        
        // ✅ WORDPRESS.ORG COMPLIANT: Build WHERE clause with conditional concatenation (no variable interpolation!)
        $where_clause = "post_status = 'publish'";
        
        // Search filter
        if (!empty($search)) {
            $where_clause .= " AND (parent_name LIKE %s OR sku LIKE %s)";
        }
        
        // Attributes filter
        if (!empty($attributes)) {
            $where_clause .= " AND attributes LIKE %s";
        }
        
        // Price range filters
        if ($min_price !== null) {
            $where_clause .= " AND price >= %f";
        }
        if ($max_price !== null) {
            $where_clause .= " AND price <= %f";
        }
        
        // Stock status filter
        if ($stock_status) {
            $where_clause .= " AND stock_status = %s";
        }
        
        // Sale price filter
        if ($filter_sale_price) {
            $where_clause .= " AND sale_price IS NOT NULL AND sale_price != '' AND sale_price > 0";
        }
        
        // Sale date range filter
        if ($sale_date_from && $sale_date_to) {
            $where_clause .= " AND date_on_sale_from IS NOT NULL AND date_on_sale_to IS NOT NULL";
            $where_clause .= " AND DATE(date_on_sale_from) <= %s AND DATE(date_on_sale_to) >= %s";
        } elseif ($sale_date_from) {
            $where_clause .= " AND date_on_sale_to IS NOT NULL AND DATE(date_on_sale_to) >= %s";
        } elseif ($sale_date_to) {
            $where_clause .= " AND date_on_sale_from IS NOT NULL AND DATE(date_on_sale_from) <= %s";
        }
        
        // Active sales filter
        if ($only_active_sales) {
            $current_date = current_time('Y-m-d');
            $where_clause .= " AND sale_price IS NOT NULL AND sale_price > 0";
            $where_clause .= " AND date_on_sale_from IS NOT NULL AND date_on_sale_to IS NOT NULL";
            $where_clause .= " AND DATE(date_on_sale_from) <= %s AND DATE(date_on_sale_to) >= %s";
        }
        
        // Checked items filter
        if ($checked_only) {
            $where_clause .= " AND checked = 1";
        }
        
        // Get all matching variations
        $select_query = "SELECT * FROM {$table_name} WHERE " . $where_clause . " ORDER BY parent_id, id";
        $start_time = microtime(true);
        if (!empty($where_values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name safe (wpdb->prefix), $where_clause built statically
            $variations = $wpdb->get_results($wpdb->prepare($select_query, ...$where_values), ARRAY_A);
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- No dynamic content, $table_name safe
            $variations = $wpdb->get_results($select_query, ARRAY_A);
        }
        $query_time = round((microtime(true) - $start_time) * 1000, 2);
        
        // Group by parent
        $parents_map = [];
        $filter_attributes_lower = !empty($attributes) ? strtolower($attributes) : '';
        
        foreach ($variations as $variation) {
            $parent_id = $variation['parent_id'];
            
            if (!isset($parents_map[$parent_id])) {
                $parents_map[$parent_id] = [
                    'id' => (int) $parent_id,
                    'name' => $variation['parent_name'],
                    'image_url' => '', // Will be set from first variation
                    'variations' => [],
                    'has_matches' => false, // 🔥 NEW: Track if parent has matched variations
                    'matched_variation_ids' => [], // 🔥 NEW: Track matched variation IDs
                ];
            }
            
            // Set parent image from first variation with image
            if (empty($parents_map[$parent_id]['image_url']) && !empty($variation['image_url'])) {
                $parents_map[$parent_id]['image_url'] = $variation['image_url'];
            }
            
            // 🔥 NEW: Check if variation matches attribute filter
            $matched = false;
            if (!empty($filter_attributes_lower)) {
                // Decode attributes JSON and check if any value matches
                $variation_attrs = json_decode($variation['attributes'], true) ?: [];
                foreach ($variation_attrs as $key => $value) {
                    if (stripos(strtolower($value), $filter_attributes_lower) !== false) {
                        $matched = true;
                        $parents_map[$parent_id]['has_matches'] = true;
                        $parents_map[$parent_id]['matched_variation_ids'][] = (int) $variation['id'];
                        break;
                    }
                }
            } else {
                // No filter = all variations are matched
                $matched = true;
                $parents_map[$parent_id]['has_matches'] = true;
                $parents_map[$parent_id]['matched_variation_ids'][] = (int) $variation['id'];
            }
            
            // Decode JSON and convert types
            $variation['attributes'] = json_decode($variation['attributes'], true) ?: [];
            $variation['id'] = (int) $variation['id'];
            $variation['parent_id'] = (int) $parent_id;
            $variation['price'] = (float) $variation['price'];
            $variation['regular_price'] = $variation['regular_price'] ? (float) $variation['regular_price'] : null;
            $variation['sale_price'] = $variation['sale_price'] ? (float) $variation['sale_price'] : null;
            $variation['stock_quantity'] = (int) $variation['stock_quantity'];
            $variation['image_id'] = $variation['image_id'] ? (int) $variation['image_id'] : null;
            $variation['manage_stock'] = (bool) $variation['manage_stock'];
            $variation['virtual'] = (bool) $variation['virtual'];
            $variation['downloadable'] = (bool) $variation['downloadable'];
            $variation['low_stock_threshold'] = $variation['low_stock_threshold'] ? (int) $variation['low_stock_threshold'] : null;
            
            // Physical properties
            $variation['weight'] = $variation['weight'] ? (float) $variation['weight'] : null;
            $variation['length'] = $variation['length'] ? (float) $variation['length'] : null;
            $variation['width'] = $variation['width'] ? (float) $variation['width'] : null;
            $variation['height'] = $variation['height'] ? (float) $variation['height'] : null;
            
            // Map post_status to status for frontend
            $variation['status'] = $variation['post_status'];
            
            // 🔥 NEW: Add matched flag
            $variation['matched'] = $matched;
            
            $parents_map[$parent_id]['variations'][] = $variation;
        }
        
        // Convert to array and add variation_count
        $parents_data = array_values($parents_map);
        foreach ($parents_data as &$parent) {
            $parent['variation_count'] = count($parent['variations']);
        }
        
        // Get valid tax classes for dropdown
        $tax_classes = \WC_Tax::get_tax_class_slugs();
        
        // Add 'parent' and empty string (standard) as special options
        $tax_class_options = array_merge(['parent', ''], $tax_classes);
        
        // Get WooCommerce currency symbol (decode HTML entities like &#36; → $)
        $currency_symbol = html_entity_decode(get_woocommerce_currency_symbol(), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        // Get WooCommerce formatting settings
        $wc_settings = [
            'currency_symbol' => $currency_symbol,
            'currency_position' => get_option('woocommerce_currency_pos', 'right_space'), // left, right, left_space, right_space
            'price_decimals' => wc_get_price_decimals(), // Usually 0, 2, or 3
            'price_decimal_separator' => wc_get_price_decimal_separator(), // . or ,
            'price_thousand_separator' => wc_get_price_thousand_separator(), // , or space or .
            'weight_unit' => get_option('woocommerce_weight_unit', 'kg'), // kg, g, lbs, oz
            'dimension_unit' => get_option('woocommerce_dimension_unit', 'cm'), // m, cm, mm, in, yd
        ];
        
        return new \WP_REST_Response([
            'success' => true,
            'data' => [
                'parents' => $parents_data,
                'total_parents' => count($parents_data),
                'total_variations' => count($variations),
                'mode' => 'grouped',
                'query_time_ms' => $query_time,
                'source' => 'cache',
                'tax_class_options' => $tax_class_options, // 🎯 Tax class dropdown options
                'wc_settings' => $wc_settings, // 🎯 WooCommerce formatting settings
            ],
        ], 200);
    }
    
    /**
     * Get collection params
     */
    public function get_collection_params() {
        return [
            'search' => [
                'description' => 'Search in product name, SKU',
                'type' => 'string',
                'sanitize_callback' => 'sanitize_text_field',
            ],
            'attributes' => [
                'description' => 'Filter by attribute values (e.g., "XS", "Kék")',
                'type' => 'string',
                'sanitize_callback' => 'sanitize_text_field',
            ],
            'parent_id' => [
                'description' => 'Filter by parent product ID',
                'type' => 'integer',
                'sanitize_callback' => 'absint',
            ],
            'min_price' => [
                'description' => 'Minimum price',
                'type' => 'number',
            ],
            'max_price' => [
                'description' => 'Maximum price',
                'type' => 'number',
            ],
            'stock_status' => [
                'description' => 'Stock status (instock, outofstock, onbackorder)',
                'type' => 'string',
                'enum' => ['instock', 'outofstock', 'onbackorder'],
            ],
            'limit' => [
                'description' => 'Limit results (max 500)',
                'type' => 'integer',
                'default' => 100,
            ],
            'offset' => [
                'description' => 'Offset for pagination',
                'type' => 'integer',
                'default' => 0,
            ],
            'orderby' => [
                'description' => 'Order by field',
                'type' => 'string',
                'default' => 'id',
            ],
            'order' => [
                'description' => 'Order direction (ASC, DESC)',
                'type' => 'string',
                'default' => 'ASC',
            ],
            'checked_only' => [
                'description' => 'Return only checked variations',
                'type' => 'boolean',
                'default' => false,
            ],
            'filter_sale_price' => [
                'description' => 'Filter only variations with sale price',
                'type' => 'boolean',
                'default' => false,
            ],
            'sale_date_from' => [
                'description' => 'Sale start date (YYYY-MM-DD)',
                'type' => 'string',
                'format' => 'date',
            ],
            'sale_date_to' => [
                'description' => 'Sale end date (YYYY-MM-DD)',
                'type' => 'string',
                'format' => 'date',
            ],
            'only_active_sales' => [
                'description' => 'Filter only currently active sales',
                'type' => 'boolean',
                'default' => false,
            ],
        ];
    }
}
