<?php
/**
 * Cache Table Manager
 * 
 * Manages the custom database table for variation cache
 * 
 * @package VariationHub
 * @since 1.0.0
 * 
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
 * Reason: This class creates and manages a CUSTOM table (wp_varihu_variation_cache).
 * Schema changes (CREATE/ALTER/DROP TABLE) are intentional and necessary for plugin functionality.
 * Direct queries on custom table are required - wp_cache_* only works with core WordPress tables.
 */

namespace VariationHub\Core;

defined('ABSPATH') || exit;

class Cache_Table {
    
    /**
     * Table name (without prefix)
     */
    const TABLE_NAME = 'varihu_variation_cache';
    
    /**
     * Database version (for schema updates)
     */
    const DB_VERSION = '1.2.0'; // ⚡ Performance: Added idx_post_status + idx_post_status_id indexes
    
    /**
     * Get full table name with prefix
     */
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }
    
    /**
     * Create cache table
     */
    public static function create_table() {
        global $wpdb;
        
        $table_name = esc_sql(self::get_table_name());
        $charset_collate = $wpdb->get_charset_collate();
        
        // Ha a tábla már létezik, ellenőrizzük hogy frissíteni kell-e
        $current_version = get_option('varihu_cache_db_version', '0.0.0');
        if (self::table_exists() && version_compare($current_version, self::DB_VERSION, '>=')) {
            return;
        }
        
        // DIRECT SQL (dbDelta often fails - this works in production!)
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            `id` BIGINT UNSIGNED NOT NULL,
            `parent_id` BIGINT UNSIGNED NOT NULL,
            `parent_name` VARCHAR(200) NOT NULL DEFAULT '',
            `sku` VARCHAR(100) DEFAULT '',
            `price` DECIMAL(10,2) DEFAULT 0,
            `regular_price` DECIMAL(10,2) DEFAULT NULL,
            `sale_price` DECIMAL(10,2) DEFAULT NULL,
            `date_on_sale_from` DATETIME DEFAULT NULL,
            `date_on_sale_to` DATETIME DEFAULT NULL,
            `stock_quantity` INT DEFAULT 0,
            `stock_status` VARCHAR(20) DEFAULT 'instock',
            `manage_stock` TINYINT(1) DEFAULT 0,
            `backorders` VARCHAR(20) DEFAULT 'no',
            `low_stock_threshold` INT DEFAULT NULL,
            `weight` DECIMAL(10,4) DEFAULT NULL,
            `length` DECIMAL(10,4) DEFAULT NULL,
            `width` DECIMAL(10,4) DEFAULT NULL,
            `height` DECIMAL(10,4) DEFAULT NULL,
            `tax_class` VARCHAR(50) DEFAULT '',
            `virtual` TINYINT(1) DEFAULT 0,
            `downloadable` TINYINT(1) DEFAULT 0,
            `description` TEXT DEFAULT NULL,
            `attributes` LONGTEXT DEFAULT NULL,
            `image_id` BIGINT UNSIGNED DEFAULT NULL,
            `image_url` VARCHAR(500) DEFAULT '',
            `post_status` VARCHAR(20) DEFAULT 'publish',
            `last_synced` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_parent` (`parent_id`),
            KEY `idx_price` (`price`),
            KEY `idx_regular_price` (`regular_price`),
            KEY `idx_sale_price` (`sale_price`),
            KEY `idx_stock_qty` (`stock_quantity`),
            KEY `idx_stock_status` (`stock_status`),
            KEY `idx_sku` (`sku`(50)),
            KEY `idx_parent_status` (`parent_id`, `post_status`),
            KEY `idx_last_synced` (`last_synced`),
            KEY `idx_post_status` (`post_status`),
            KEY `idx_post_status_id` (`post_status`, `id`)
        ) ENGINE=InnoDB {$charset_collate};";
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- CREATE TABLE with safe variables
        $result = $wpdb->query($sql);
        
        if ($result === false) {
            return;
        }
        
        // ⚡ Version 1.2.0: Add missing performance indexes
        if (version_compare($current_version, '1.2.0', '<')) {
            // Check if indexes exist before creating (avoid duplicate key error)
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe, generated by $wpdb->prefix
            $existing_indexes = $wpdb->get_col("SHOW INDEX FROM {$table_name} WHERE Key_name IN ('idx_post_status', 'idx_post_status_id')");
            
            if (!in_array('idx_post_status', $existing_indexes)) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
                $wpdb->query("ALTER TABLE {$table_name} ADD KEY `idx_post_status` (`post_status`)");
            }
            
            if (!in_array('idx_post_status_id', $existing_indexes)) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
                $wpdb->query("ALTER TABLE {$table_name} ADD KEY `idx_post_status_id` (`post_status`, `id`)");
            }
        }
        
        // Save DB version
        update_option('varihu_cache_db_version', self::DB_VERSION);
    }
    
    /**
     * Drop cache table (for uninstall)
     */
    public static function drop_table() {
        global $wpdb;
        
        $table_name = esc_sql(self::get_table_name());
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
        $wpdb->query("DROP TABLE IF EXISTS {$table_name}");
        
        delete_option('varihu_cache_db_version');
        delete_option('varihu_cache_last_sync');
        delete_option('varihu_cache_sync_status');
    }
    
    /**
     * Check if table exists
     */
    public static function table_exists() {
        global $wpdb;
        
        $table_name = esc_sql(self::get_table_name());
        $query = $wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table_name));
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is prepared above with $wpdb->prepare()
        return $wpdb->get_var($query) === $table_name;
    }
    
    /**
     * Get cache statistics
     */
    public static function get_stats() {
        global $wpdb;
        
        if (!self::table_exists()) {
            return [
                'exists' => false,
                'total_rows' => 0,
                'last_synced' => null,
            ];
        }
        
        $table_name = esc_sql(self::get_table_name());
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
        $total = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
        $last_synced = $wpdb->get_var("SELECT MAX(last_synced) FROM {$table_name}");
        
        return [
            'exists' => true,
            'total_rows' => (int) $total,
            'last_synced' => $last_synced,
            'db_version' => get_option('varihu_cache_db_version'),
        ];
    }
    
    /**
     * Truncate cache table
     */
    public static function truncate() {
        global $wpdb;
        
        $table_name = esc_sql(self::get_table_name());
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
        $wpdb->query("TRUNCATE TABLE {$table_name}");
    }
    
    /**
     * Get cache health (compare with actual variation count)
     */
    public static function get_health() {
        global $wpdb;
        
        $stats = self::get_stats();
        
        if (!$stats['exists']) {
            return [
                'status' => 'missing',
                'message' => 'Cache table does not exist',
            ];
        }
        
        // Count actual variations
        $actual_count = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} 
             WHERE post_type = 'product_variation' 
             AND post_status = 'publish'"
        );
        
        $cached_count = $stats['total_rows'];
        $diff = abs($actual_count - $cached_count);
        
        if ($diff === 0) {
            return [
                'status' => 'healthy',
                'message' => 'Cache is in sync',
                'cached' => $cached_count,
                'actual' => $actual_count,
            ];
        } elseif ($diff < 10) {
            return [
                'status' => 'warning',
                'message' => "Cache slightly out of sync ({$diff} variations)",
                'cached' => $cached_count,
                'actual' => $actual_count,
            ];
        } else {
            return [
                'status' => 'error',
                'message' => "Cache out of sync ({$diff} variations missing)",
                'cached' => $cached_count,
                'actual' => $actual_count,
            ];
        }
    }
}
