Getting Started

Minimal Setup

Installation

composer require sarfraznawaz2005/aiqueryoptimizer
php artisan vendor:publish --provider="Sarfraznawaz2005\AIQueryOptimizer\AIQueryOptimizerServiceProvider"

Configuration
- Publish the config file and update your AI provider (Gemini/OpenAI) API key in .env:
```
AIQUERY_OPTIMIZER_PROVIDER=gemini
AIQUERY_OPTIMIZER_GEMINI_API_KEY=your_gemini_key_here
```
- Enable the package in config/aiqueryoptimizer.php:
```
'enabled' => env('AIQUERY_OPTIMIZER_ENABLED', true),
```
First Use Case
- Add the query analyzer button to your layout (e.g., resources/views/layouts/app.blade.php):
```
@aiqueryoptimizer
```
- Trigger a query (e.g., via a route or AJAX call) and click the floating button to see real-time analysis.

Implementation Patterns

Core Workflows

Query Capture & Analysis
- Automatic Capture: The package hooks into Laravel's query log to capture all SELECT queries. No manual instrumentation needed.
- Trigger Analysis: Click the floating button to analyze the last 10 queries (configurable via max_queries_to_analyze).
- AI Context: The AI receives:
  - Raw SQL query.
  - Database schema (tables, columns, indexes).
  - Query execution stats (duration, row count).
Integration with Existing Tools
- Debugbar: Works alongside Laravel Debugbar. Disable Debugbar's query logging to avoid duplication:
```
'debugbar' => [
    'enabled' => false,
],
```
- Telescope: Use the package's AJAX support to log queries in Telescope while still getting AI insights:
```
'ajax_support' => true,
```
Conditional Analysis
- Environment-Based: Disable in production or staging:
```
'environments' => ['local', 'staging'],
```
- Route-Based: Skip analysis for specific routes (e.g., APIs):
```
'ignored_routes' => ['api/*'],
```

Custom AI Prompts

Extend the default AI prompt template in config/aiqueryoptimizer.php:

'ai_prompt_template' => "Analyze this SQL query: {query}. Provide optimization suggestions tailored to this schema: {schema}. Focus on: {focus_areas}.",

Dynamically set focus_areas (e.g., "indexes", "joins") via middleware:

public function handle($request, Closure $next)
{
    config(['aiqueryoptimizer.focus_areas' => ['indexes']]);
    return $next($request);
}

Batch Processing

Analyze historical queries from the aiquery_optimizer_queries table:

use Sarfraznawaz2005\AIQueryOptimizer\Facades\AIQueryOptimizer;

$results = AIQueryOptimizer::analyzeBatch(
    now()->subHours(1),
    now(),
    50 // limit
);

Gotchas and Tips

Pitfalls

API Rate Limits
- The package caches AI responses by default (TTL: 24 hours). Adjust in config:
```
'cache' => [
    'enabled' => true,
    'ttl' => 3600, // 1 hour
],
```
- Monitor your AI provider's usage via the aiquery_optimizer_ai_calls table.
Schema Context Gaps
- The AI relies on the schema table in your database. Ensure it’s up-to-date:
```
php artisan aiqueryoptimizer:refresh-schema
```
- For large schemas, limit the context sent to the AI:
```
'schema_context_limit' => 5000, // characters
```
AJAX Query Dropping
- If AJAX queries aren’t captured, verify:
  - ajax_support is enabled in config.
  - Your AJAX requests include the CSRF token (if using Laravel’s middleware).
  - The aiqueryoptimizer middleware is registered in App\Http\Kernel.php:
```
'web' => [
    \Sarfraznawaz2005\AIQueryOptimizer\Http\Middleware\CaptureQueries::class,
],
```

False Positives in Suggestions

The AI may suggest indexes for queries that are rarely run. Filter suggestions in your code:

$analysis = AIQueryOptimizer::analyze($query);
$suggestions = collect($analysis['suggestions'])
    ->where('execution_count', '>', 10); // Only act on queries run >10 times

Performance Overhead
- Query analysis adds minimal overhead (~1-5ms per query). For high-traffic apps, disable in non-local environments:
```
'environments' => ['local'],
```

Debugging

Log Queries Manually

Force-log a query for testing:

use Sarfraznawaz2005\AIQueryOptimizer\Facades\AIQueryOptimizer;

AIQueryOptimizer::logQuery('SELECT * FROM users WHERE id = ?', [1]);

Inspect Raw AI Input/Output

Enable debug mode to log AI interactions:

'debug' => [
    'enabled' => true,
    'log_path' => storage_path('logs/aiquery_optimizer.log'),
],

Clear Cache

Reset cached AI responses:

php artisan cache:clear
php artisan aiqueryoptimizer:clear-cache

Extension Points

Custom AI Providers

Add a new provider by implementing Sarfraznawaz2005\AIQueryOptimizer\Contracts\AIProvider:

namespace App\Providers;

use Sarfraznawaz2005\AIQueryOptimizer\Contracts\AIProvider;

class CustomAIProvider implements AIProvider {
    public function analyze(string $query, array $context): string {
        // Your custom logic here
    }
}

'providers' => [
    'custom' => \App\Providers\CustomAIProvider::class,
],

Query Filtering

Extend the query capture logic by binding to the aiqueryoptimizer.capture event:

use Sarfraznawaz2005\AIQueryOptimizer\Events\QueryCaptured;

QueryCaptured::listen(function (QueryCaptured $event) {
    if (str_contains($event->query, 'temp_')) {
        $event->skip = true; // Skip temporary table queries
    }
});

UI Customization
- Override the default Blade view (resources/views/vendor/aiqueryoptimizer/modal.blade.php) to:
  - Change the button style.
  - Add custom fields to the analysis modal.
  - Integrate with your existing dashboard.

Post-Analysis Actions

Hook into the aiqueryoptimizer.analyzed event to automate optimizations:

use Sarfraznawaz2005\AIQueryOptimizer\Events\QueryAnalyzed;

QueryAnalyzed::listen(function (QueryAnalyzed $event) {
    if ($event->suggestions->contains('add_index', 'users_email')) {
        Artisan::call('make:index', [
            'table' => 'users',
            'column' => 'email',
            '--unique' => true,
        ]);
    }
});

Aiqueryoptimizer Laravel Package