<?php
/**
 * Cache Sync Manager
 * 
 * Handles synchronization between WooCommerce variations and cache table
 * 
 * @package VariationHub
 * @since 1.0.0
 * 
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
 * Reason: Syncs WooCommerce variation data to custom cache table (wp_varihu_variation_cache).
 * Direct INSERT/UPDATE queries required for bulk operations (500+ variations/second performance).
 * WordPress wp_cache_* does not support custom tables - this is intentional bypass for performance.
 */

namespace VariationHub\Core;

defined('ABSPATH') || exit;

class Cache_Sync {
    
    /**
     * Get optimized image URL (smallest thumbnail for performance)
     * 
     * @param int|null $image_id Attachment ID
     * @return string Image URL (smallest available thumbnail or placeholder)
     */
    private static function get_optimized_image_url($image_id) {
        if (!$image_id) {
            // No image: use smallest WooCommerce placeholder (100x100px)
            return wc_placeholder_img_src('woocommerce_gallery_thumbnail');
        }
        
        // Try to get smallest thumbnail (WordPress 'thumbnail' = 150x150px by default)
        $image_data = wp_get_attachment_image_src($image_id, 'thumbnail');
        if ($image_data && !empty($image_data[0])) {
            return $image_data[0];
        }
        
        // Fallback: try gallery thumbnail (100x100px)
        $image_data = wp_get_attachment_image_src($image_id, 'woocommerce_gallery_thumbnail');
        if ($image_data && !empty($image_data[0])) {
            return $image_data[0];
        }
        
        // Last resort: placeholder
        return wc_placeholder_img_src('woocommerce_gallery_thumbnail');
    }
    
    /**
     * Sync a single variation to cache
     * 
     * @param int $variation_id Variation post ID
     * @return bool Success
     */
    public static function sync_variation($variation_id) {
        global $wpdb;
        
        try {
            $variation = wc_get_product($variation_id);
            
            // Validation
            if (!$variation || !$variation->is_type('variation')) {
                return false;
            }
            
            $parent_id = $variation->get_parent_id();
            $parent = wc_get_product($parent_id);
            
            if (!$parent) {
                return false;
            }
            
            // Prepare data
            $data = [
                'id' => $variation_id,
                'parent_id' => $parent_id,
                'parent_name' => $parent->get_name(),
                'sku' => $variation->get_sku() ?: '',
                
                // Prices
                'price' => $variation->get_price() ?: 0,
                'regular_price' => $variation->get_regular_price() ?: null,
                'sale_price' => $variation->get_sale_price() ?: null,
                'date_on_sale_from' => self::format_date($variation->get_date_on_sale_from()),
                'date_on_sale_to' => self::format_date($variation->get_date_on_sale_to()),
                
                // Stock
                'stock_quantity' => $variation->get_stock_quantity() ?: 0,
                'stock_status' => $variation->get_stock_status() ?: 'instock',
                'manage_stock' => $variation->get_manage_stock() ? 1 : 0,
                'backorders' => $variation->get_backorders() ?: 'no',
                'low_stock_threshold' => $variation->get_low_stock_amount() ?: null,
                
                // Physical properties
                'weight' => $variation->get_weight() ?: null,
                'length' => $variation->get_length() ?: null,
                'width' => $variation->get_width() ?: null,
                'height' => $variation->get_height() ?: null,
                
                // Tax and type
                'tax_class' => $variation->get_tax_class() ?: '',
                'virtual' => $variation->is_virtual() ? 1 : 0,
                'downloadable' => $variation->is_downloadable() ? 1 : 0,
                
                // Description
                'description' => $variation->get_description() ?: null,
                
                // Attributes (JSON encode)
                'attributes' => wp_json_encode($variation->get_attributes() ?: []),
                
                // Image (use smallest thumbnail for performance: woocommerce_gallery_thumbnail = 100x100px)
                'image_id' => $variation->get_image_id() ?: null,
                'image_url' => self::get_optimized_image_url($variation->get_image_id()),
                
                // Status
                'post_status' => $variation->get_status() ?: 'publish',
            ];
            
            // UPSERT (INSERT or UPDATE)
            $table_name = Cache_Table::get_table_name();
            $result = $wpdb->replace(
                $table_name,
                $data,
                [
                    '%d', '%d', '%s', '%s',                          // id, parent_id, parent_name, sku
                    '%f', '%f', '%f', '%s', '%s',                    // price, regular_price, sale_price, dates
                    '%d', '%s', '%d', '%s', '%d',                    // stock fields
                    '%f', '%f', '%f', '%f',                          // physical properties
                    '%s', '%d', '%d',                                // tax_class, virtual, downloadable
                    '%s',                                            // description
                    '%s',                                            // attributes JSON
                    '%d', '%s',                                      // image_id, image_url
                    '%s'                                             // post_status
                ]
            );
            
            if ($result === false) {
                return false;
            }
            
            // ⚡ PERFORMANCE: No wp_cache needed! 
            // Plugin uses direct SQL on cache table (not wp_cache_get)
            // Removed wp_cache_set/delete to eliminate Redis overhead
            
            return true;
            
        } catch (\Exception $e) {
            return false;
        }
    }
    
    /**
     * Delete variation from cache
     * 
     * @param int $variation_id Variation post ID
     * @return bool Success
     */
    public static function delete_variation($variation_id) {
        global $wpdb;
        
        $table_name = Cache_Table::get_table_name();
        $result = $wpdb->delete(
            $table_name,
            ['id' => $variation_id],
            ['%d']
        );
        
        // ⚡ PERFORMANCE: No wp_cache needed!
        // Plugin uses direct SQL on cache table (not wp_cache_get)
        
        return $result !== false;
    }
    
    /**
     * Sync all variations for a parent product
     * 
     * @param int $parent_id Parent product ID
     * @return array Stats (synced, failed)
     */
    public static function sync_parent_variations($parent_id) {
        $parent = wc_get_product($parent_id);
        
        if (!$parent || !$parent->is_type('variable')) {
            return ['synced' => 0, 'failed' => 0];
        }
        
        $variation_ids = $parent->get_children();
        $stats = ['synced' => 0, 'failed' => 0];
        
        foreach ($variation_ids as $variation_id) {
            if (self::sync_variation($variation_id)) {
                $stats['synced']++;
            } else {
                $stats['failed']++;
            }
        }
        
        return $stats;
    }
    
    /**
     * Initial sync all variations (batch processing)
     * 
     * @param int $batch_size Variations per batch (default: 2000)
     * @param int $offset Starting offset
     * @return array Stats (total, synced, failed, offset)
     */
    public static function initial_sync_batch($batch_size = 2000, $offset = 0) {
        global $wpdb;
        
        // Get variation IDs (direct query for speed)
        $variation_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT ID FROM {$wpdb->posts} 
             WHERE post_type = 'product_variation' 
             AND post_status IN ('publish', 'private')
             ORDER BY ID
             LIMIT %d OFFSET %d",
            $batch_size,
            $offset
        ));
        
        if (empty($variation_ids)) {
            return [
                'total' => 0,
                'synced' => 0,
                'failed' => 0,
                'offset' => $offset,
                'completed' => true,
            ];
        }
        
        $stats = ['synced' => 0, 'failed' => 0];
        
        foreach ($variation_ids as $variation_id) {
            if (self::sync_variation($variation_id)) {
                $stats['synced']++;
            } else {
                $stats['failed']++;
            }
        }
        
        $new_offset = $offset + $batch_size;
        $completed = count($variation_ids) < $batch_size;
        
        return [
            'total' => count($variation_ids),
            'synced' => $stats['synced'],
            'failed' => $stats['failed'],
            'offset' => $new_offset,
            'completed' => $completed,
        ];
    }
    
    /**
     * Schedule full re-sync (background job)
     * Uses Action Scheduler for background processing
     */
    public static function schedule_full_sync() {
        // Check if Action Scheduler exists
        if (!function_exists('as_schedule_single_action')) {
            return false;
        }
        
        // Clear existing sync jobs
        as_unschedule_all_actions('varihu_cache_sync_batch');
        
        // Schedule first batch
        as_schedule_single_action(
            time(),
            'varihu_cache_sync_batch',
            ['offset' => 0],
            'variation-hub'
        );
        
        // Update sync status
        update_option('varihu_cache_sync_status', [
            'running' => true,
            'started' => current_time('mysql'),
            'total_synced' => 0,
            'total_failed' => 0,
        ]);
        
        return true;
    }
    
    /**
     * Process batch sync (Action Scheduler callback)
     * 
     * @param int $offset Current offset
     */
    public static function process_batch_sync($offset = 0) {
        $result = self::initial_sync_batch(2000, $offset);
        
        // Update sync status
        $status = get_option('varihu_cache_sync_status', []);
        $status['total_synced'] = ($status['total_synced'] ?? 0) + $result['synced'];
        $status['total_failed'] = ($status['total_failed'] ?? 0) + $result['failed'];
        $status['last_batch'] = current_time('mysql');
        
        if ($result['completed']) {
            // Sync completed
            $status['running'] = false;
            $status['completed'] = current_time('mysql');
            update_option('varihu_cache_sync_status', $status);
            update_option('varihu_cache_last_sync', current_time('mysql'));
        } else {
            // Schedule next batch
            update_option('varihu_cache_sync_status', $status);
            
            as_schedule_single_action(
                time() + 2, // 2 sec delay between batches (2000 variations per batch)
                'varihu_cache_sync_batch',
                ['offset' => $result['offset']],
                'variation-hub'
            );
        }
    }
    
    /**
     * ✅ SECURITY FIX: Bulk sync multiple variations (optimized, no race condition)
     * 
     * Used by bulk operations to avoid triggering 500+ individual cache syncs
     * 
     * @param array $variation_ids Array of variation IDs
     * @return array Stats (synced, failed)
     */
    public static function bulk_sync_variations($variation_ids) {
        global $wpdb;
        
        if (empty($variation_ids)) {
            return ['synced' => 0, 'failed' => 0];
        }
        
        $table_name = esc_sql(Cache_Table::get_table_name());
        $synced = 0;
        $failed = 0;
        $affected_parent_ids = []; // Track parent IDs for cache invalidation
        
        // Process in chunks of 100 to avoid huge SQL statements
        $chunks = array_chunk($variation_ids, 100);
        
        foreach ($chunks as $chunk) {
            $values = [];
            
            foreach ($chunk as $variation_id) {
                try {
                    $variation = wc_get_product($variation_id);
                    
                    if (!$variation || !$variation->is_type('variation')) {
                        $failed++;
                        continue;
                    }
                    
                    $parent_id = $variation->get_parent_id();
                    $parent = wc_get_product($parent_id);
                    
                    if (!$parent) {
                        $failed++;
                        continue;
                    }
                    
                    // Track parent ID for cache invalidation
                    $affected_parent_ids[] = $parent_id;
                    
                    // Collect variation data (NOT prepared - will prepare later in chunks)
                    $values[] = [
                        $variation_id,
                        $parent_id,
                        $parent->get_name(),
                        $variation->get_sku() ?: '',
                        $variation->get_price() ?: 0,
                        $variation->get_regular_price() ?: null,
                        $variation->get_sale_price() ?: null,
                        self::format_date($variation->get_date_on_sale_from()),
                        self::format_date($variation->get_date_on_sale_to()),
                        $variation->get_stock_quantity() ?: 0,
                        $variation->get_stock_status() ?: 'instock',
                        $variation->get_manage_stock() ? 1 : 0,
                        $variation->get_backorders() ?: 'no',
                        $variation->get_low_stock_amount() ?: null,
                        $variation->get_weight() ?: null,
                        $variation->get_length() ?: null,
                        $variation->get_width() ?: null,
                        $variation->get_height() ?: null,
                        $variation->get_tax_class() ?: '',
                        $variation->is_virtual() ? 1 : 0,
                        $variation->is_downloadable() ? 1 : 0,
                        $variation->get_description() ?: null,
                        wp_json_encode($variation->get_attributes() ?: []),
                        $variation->get_image_id() ?: null,
                        self::get_optimized_image_url($variation->get_image_id()),
                        $variation->get_status() ?: 'publish'
                    ];
                    
                    $synced++;
                } catch (\Exception $e) {
                    $failed++;
                }
            }
            
            // ✅ WORDPRESS.ORG COMPLIANT: Bulk INSERT with chunked placeholder generation (no variable interpolation!)
            // Process in chunks to avoid exceeding prepare() argument limits (50 variations × 26 fields = 1300 args, safe)
            if (!empty($values)) {
                $chunk_size = 50;
                $value_chunks = array_chunk($values, $chunk_size);
                
                foreach ($value_chunks as $chunk) {
                    $all_placeholders = [];
                    $all_values = [];
                    
                    foreach ($chunk as $value_row) {
                        // Generate placeholder string for one row (26 fields: %d,%d,%s,%s,%f,%f,%f,%s,%s,%d,%s,%d,%s,%d,%f,%f,%f,%f,%s,%d,%d,%s,%s,%d,%s,%s)
                        $all_placeholders[] = "(%d,%d,%s,%s,%f,%f,%f,%s,%s,%d,%s,%d,%s,%d,%f,%f,%f,%f,%s,%d,%d,%s,%s,%d,%s,%s)";
                        
                        // Add all 26 values from this variation
                        // Each $value_row contains: [id, parent_id, parent_name, sku, price, regular_price, sale_price, ...]
                        $all_values = array_merge($all_values, $value_row);
                    }
                    
                    // Build query with placeholders (NOT interpolated values!)
                    $placeholders_sql = implode(',', $all_placeholders); // Safe: contains "%d,%s" not actual values!
                    $sql = "REPLACE INTO {$table_name} 
                            (id, parent_id, parent_name, sku, price, regular_price, sale_price, 
                             date_on_sale_from, date_on_sale_to, stock_quantity, stock_status, 
                             manage_stock, backorders, low_stock_threshold, weight, length, width, height,
                             tax_class, `virtual`, `downloadable`, description, attributes, image_id, image_url, post_status)
                            VALUES {$placeholders_sql}";
                    
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is esc_sql() safe, $placeholders_sql contains only placeholders
                    $result = $wpdb->query($wpdb->prepare($sql, ...$all_values));
                    
                    if ($result === false) {
                        // Query failed, but don't log in production to avoid debug log spam
                    }
                }
            }
        }
        
        // ⚡ PERFORMANCE: No wp_cache needed!
        // Plugin uses direct SQL on cache table (not wp_cache_get)
        // Removed wp_cache_delete_multiple to eliminate Redis overhead
        
        return ['synced' => $synced, 'failed' => $failed];
    }
    
    /**
     * Format WooCommerce date object to MySQL datetime
     * 
     * @param \WC_DateTime|null $date
     * @return string|null
     */
    private static function format_date($date) {
        if (!$date || !($date instanceof \WC_DateTime)) {
            return null;
        }
        
        return $date->date('Y-m-d H:i:s');
    }
}
