# QueryOptimizer Integration Guide

## Quick Start

### 1. Initialize QueryOptimizer

```php
use MultilingualPressZone\Core\QueryOptimizer;
use MultilingualPressZone\Core\CacheManager;
use MultilingualPressZone\Core\LanguageManager;
use MultilingualPressZone\Core\ContentManager;

// Create instances
$cache = new CacheManager();
$language_manager = new LanguageManager($cache);
$content_manager = new ContentManager();

// Initialize optimizer
$optimizer = new QueryOptimizer(
    $cache,
    $language_manager,
    $content_manager
);
```

### 2. Replace Existing Query Patterns

#### Before (Inefficient)

```php
// ❌ N+1 query problem
$posts = get_posts(['numberposts' => 50]);
foreach ($posts as $post) {
    $translations = $content_manager->getTranslations($post->ID);
    // Each iteration = multiple queries!
}
```

#### After (Optimized)

```php
// ✅ Single batch query
$posts = get_posts(['numberposts' => 50]);
$post_ids = wp_list_pluck($posts, 'ID');

// Prefetch everything at once
$optimizer->prefetchTranslationsForPosts($post_ids);

foreach ($posts as $post) {
    $translations = $content_manager->getTranslations($post->ID);
    // Uses cache - no additional queries!
}
```

## Common Integration Patterns

### Pattern 1: WP_Query Loop Optimization

```php
// The Query
$query = new WP_Query([
    'post_type' => 'post',
    'posts_per_page' => 20,
]);

if ($query->have_posts()) {
    // Collect IDs first
    $post_ids = wp_list_pluck($query->posts, 'ID');

    // Prefetch all translation data
    $optimizer->prefetchTranslationsForPosts($post_ids);

    // Now loop efficiently
    while ($query->have_posts()) {
        $query->the_post();

        // Get translations (from cache - no queries!)
        $translations = $content_manager->getTranslations(get_the_ID());

        // Display with translations
        get_template_part('content', 'multilingual');
    }
    wp_reset_postdata();
}
```

### Pattern 2: Archive Page Optimization

```php
// In archive template
add_action('pre_get_posts', function($query) use ($optimizer) {
    if ($query->is_main_query() && !is_admin()) {
        // After query runs, prefetch translations
        add_action('loop_end', function() use ($query, $optimizer) {
            if ($query->posts) {
                $post_ids = wp_list_pluck($query->posts, 'ID');
                $optimizer->prefetchTranslationsForPosts($post_ids);
            }
        }, 10);
    }
});
```

### Pattern 3: REST API Optimization

```php
// Add optimizer to REST API
add_action('rest_api_init', function() use ($optimizer) {
    register_rest_route('mpz/v1', '/posts-with-translations', [
        'methods' => 'GET',
        'callback' => function($request) use ($optimizer) {
            $post_ids = $request->get_param('post_ids');
            $language = $request->get_param('language');

            // Single optimized query
            return $optimizer->getPostsWithTranslations(
                $post_ids,
                $language
            );
        },
        'permission_callback' => '__return_true',
        'args' => [
            'post_ids' => [
                'required' => true,
                'type' => 'array',
            ],
            'language' => [
                'required' => false,
                'type' => 'string',
            ],
        ],
    ]);
});
```

### Pattern 4: Admin Dashboard Optimization

```php
// Optimize translation overview page
add_action('admin_init', function() use ($optimizer) {
    if (isset($_GET['page']) && $_GET['page'] === 'mpz-translations') {
        // Get all posts for current view
        $posts = get_posts([
            'post_type' => 'any',
            'posts_per_page' => 50,
            'paged' => get_query_var('paged', 1),
        ]);

        $post_ids = wp_list_pluck($posts, 'ID');

        // Warm cache for dashboard
        $optimizer->warmQueryCache($post_ids);
    }
});
```

### Pattern 5: Widget Optimization

```php
class MultilingualWidget extends WP_Widget {
    private $optimizer;

    public function __construct($optimizer) {
        parent::__construct(/* ... */);
        $this->optimizer = $optimizer;
    }

    public function widget($args, $instance) {
        // Get recent posts
        $posts = get_posts([
            'numberposts' => 5,
            'post_type' => 'post',
        ]);

        $post_ids = wp_list_pluck($posts, 'ID');

        // Prefetch all at once
        $this->optimizer->prefetchTranslationsForPosts($post_ids);

        // Display
        foreach ($posts as $post) {
            $translations = $this->content_manager->getTranslations($post->ID);
            // Render with translations
        }
    }
}
```

### Pattern 6: AJAX Request Optimization

```php
// AJAX handler
add_action('wp_ajax_mpz_get_translations', function() use ($optimizer) {
    check_ajax_referer('mpz_ajax', 'nonce');

    $post_ids = $_POST['post_ids'] ?? [];
    $post_ids = array_map('intval', $post_ids);

    // Single optimized query
    $results = $optimizer->getPostsWithTranslations($post_ids);

    wp_send_json_success($results);
});
```

### Pattern 7: Sitemap Generation

```php
// Optimize multilingual sitemap generation
add_filter('wp_sitemaps_posts_query_args', function($args) {
    return $args;
});

add_action('wp_sitemaps_posts_pre_url_list', function($post_type) use ($optimizer) {
    // Get all posts for sitemap
    $posts = get_posts([
        'post_type' => $post_type,
        'posts_per_page' => -1,
        'post_status' => 'publish',
    ]);

    $post_ids = wp_list_pluck($posts, 'ID');

    // Prefetch all translations for sitemap
    $optimizer->prefetchTranslationsForPosts($post_ids);
});
```

## Performance Monitoring

### Add Performance Monitoring Dashboard

```php
// Add admin menu
add_action('admin_menu', function() {
    add_submenu_page(
        'multilingual-press-zone',
        'Query Performance',
        'Performance',
        'manage_options',
        'mpz-performance',
        'mpz_performance_page'
    );
});

function mpz_performance_page() {
    global $optimizer;

    $stats = $optimizer->getStats();
    $report = $optimizer->getEfficiencyReport();

    ?>
    <div class="wrap">
        <h1>Query Optimizer Performance</h1>

        <div class="mpz-stats">
            <h2>Statistics</h2>
            <table class="widefat">
                <tr>
                    <th>Queries Executed</th>
                    <td><?php echo esc_html($stats['queries_executed']); ?></td>
                </tr>
                <tr>
                    <th>Queries Saved</th>
                    <td><?php echo esc_html($stats['saved_queries']); ?></td>
                </tr>
                <tr>
                    <th>Cache Hit Ratio</th>
                    <td><?php echo esc_html($stats['cache_hit_ratio']); ?>%</td>
                </tr>
                <tr>
                    <th>Average Query Time</th>
                    <td><?php echo esc_html($stats['avg_time_ms']); ?> ms</td>
                </tr>
            </table>

            <h2>Performance Goals</h2>
            <table class="widefat">
                <tr>
                    <th>Goal</th>
                    <th>Status</th>
                </tr>
                <tr>
                    <td>Cache Hit Ratio ≥85%</td>
                    <td>
                        <?php echo $report['meets_performance_goals']['cache_hit_ratio']
                            ? '✓ PASS' : '✗ FAIL'; ?>
                    </td>
                </tr>
                <tr>
                    <td>Query Time &lt;20ms</td>
                    <td>
                        <?php echo $report['meets_performance_goals']['query_time']
                            ? '✓ PASS' : '✗ FAIL'; ?>
                    </td>
                </tr>
                <tr>
                    <td>Query Reduction ≥80%</td>
                    <td>
                        <?php echo $report['meets_performance_goals']['query_reduction']
                            ? '✓ PASS' : '✗ FAIL'; ?>
                    </td>
                </tr>
            </table>

            <?php if (!empty($report['recommendations'])): ?>
            <h2>Recommendations</h2>
            <ul>
                <?php foreach ($report['recommendations'] as $rec): ?>
                <li><?php echo esc_html($rec); ?></li>
                <?php endforeach; ?>
            </ul>
            <?php endif; ?>
        </div>
    </div>
    <?php
}
```

### Add Debug Logging

```php
// Enable detailed logging in development
if (WP_DEBUG) {
    add_action('shutdown', function() use ($optimizer) {
        $stats = $optimizer->getStats();

        error_log('=== MPZ Query Optimizer Stats ===');
        error_log('Queries: ' . $stats['queries_executed']);
        error_log('Cache hits: ' . $stats['cache_hits']);
        error_log('Cache misses: ' . $stats['cache_misses']);
        error_log('Hit ratio: ' . $stats['cache_hit_ratio'] . '%');
        error_log('Total time: ' . $stats['total_time_ms'] . 'ms');
    });
}
```

## Testing Integration

### Unit Test Example

```php
use PHPUnit\Framework\TestCase;
use MultilingualPressZone\Core\QueryOptimizer;

class QueryOptimizerIntegrationTest extends TestCase {

    public function testN1Prevention() {
        $optimizer = $this->createOptimizer();
        $post_ids = [1, 2, 3, 4, 5];

        // Reset stats
        $optimizer->resetStats();

        // Prefetch
        $optimizer->prefetchTranslationsForPosts($post_ids);

        // Should use only 1 query
        $stats = $optimizer->getStats();
        $this->assertEquals(1, $stats['queries_executed']);
    }

    public function testCacheHitRatio() {
        $optimizer = $this->createOptimizer();
        $post_ids = [1, 2, 3];

        // First call
        $optimizer->getPostsWithTranslations($post_ids);

        // Second call should be cached
        $optimizer->resetStats();
        $optimizer->getPostsWithTranslations($post_ids);

        $ratio = $optimizer->getCacheHitRatio();
        $this->assertGreaterThanOrEqual(85, $ratio);
    }

    private function createOptimizer() {
        $cache = new CacheManager();
        $language_manager = new LanguageManager($cache);
        $content_manager = new ContentManager();

        return new QueryOptimizer(
            $cache,
            $language_manager,
            $content_manager
        );
    }
}
```

## Migration Checklist

### Step 1: Identify Query Hotspots

```php
// Add query monitoring
add_action('shutdown', function() {
    global $wpdb;

    // Log queries in development
    if (WP_DEBUG && defined('SAVEQUERIES') && SAVEQUERIES) {
        error_log('Total queries: ' . count($wpdb->queries));

        // Identify translation-related queries
        $translation_queries = 0;
        foreach ($wpdb->queries as $query) {
            if (strpos($query[0], 'mpz_translations') !== false) {
                $translation_queries++;
            }
        }
        error_log('Translation queries: ' . $translation_queries);
    }
});
```

### Step 2: Replace Loop Queries

Find patterns like:
```php
foreach ($posts as $post) {
    $data = $content_manager->getTranslations($post->ID);
}
```

Replace with:
```php
$optimizer->prefetchTranslationsForPosts($post_ids);
foreach ($posts as $post) {
    $data = $content_manager->getTranslations($post->ID);
}
```

### Step 3: Implement Cache Warming

```php
// Add to cron
add_action('mpz_warm_cache', function() use ($optimizer) {
    // Get popular posts
    $popular = get_posts([
        'meta_key' => 'views',
        'orderby' => 'meta_value_num',
        'posts_per_page' => 100,
    ]);

    $post_ids = wp_list_pluck($popular, 'ID');
    $optimizer->warmQueryCache($post_ids);
});

if (!wp_next_scheduled('mpz_warm_cache')) {
    wp_schedule_event(time(), 'hourly', 'mpz_warm_cache');
}
```

### Step 4: Monitor Performance

```php
// Track improvements
$before_stats = $optimizer->getStats();

// Your operations
do_work();

$after_stats = $optimizer->getStats();

// Log improvements
$saved = $after_stats['saved_queries'];
error_log("Saved {$saved} queries!");
```

## Best Practices Summary

1. **Always prefetch before loops** - Prevent N+1 queries
2. **Use batch operations** - Single query > multiple queries
3. **Monitor cache hit ratio** - Should be ≥85%
4. **Warm critical paths** - Popular content, admin pages
5. **Invalidate selectively** - Only what changed
6. **Test performance** - Use built-in test suite
7. **Log in development** - Monitor query counts

## Common Pitfalls

### Pitfall 1: Forgetting to Prefetch

```php
// ❌ Bad - N+1 problem
foreach ($posts as $post) {
    $translations = get_translations($post->ID);
}

// ✅ Good - Prefetch first
$optimizer->prefetchTranslationsForPosts($post_ids);
foreach ($posts as $post) {
    $translations = get_translations($post->ID);
}
```

### Pitfall 2: Over-Invalidation

```php
// ❌ Bad - Invalidates too much
$optimizer->invalidateQueryCache(0, 'all');

// ✅ Good - Invalidate only what changed
$optimizer->invalidateQueryCache($post_id, 'post');
```

### Pitfall 3: Ignoring Cache Limits

```php
// ❌ Bad - Exceeds batch size
$huge_array = range(1, 1000);
$optimizer->prefetchTranslationsForPosts($huge_array);

// ✅ Good - Chunk large arrays
$chunks = array_chunk($huge_array, 100);
foreach ($chunks as $chunk) {
    $optimizer->prefetchTranslationsForPosts($chunk);
}
```

## Support

For issues or questions:
1. Check the README documentation
2. Run the test suite to verify functionality
3. Check query logs and statistics
4. Review efficiency report for recommendations