# Performance Optimization

## Overview

This document covers performance optimization techniques used in the Building Management System, including database optimization, caching strategies, and code-level improvements.

## Database Optimization

### Eager Loading

Prevent N+1 query problems by eager loading relationships.

```php
// Bad - N+1 queries
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->customer?->trade_name;  // Query per iteration
}

// Good - Eager loading
$orders = Order::with('customer')->get();
foreach ($orders as $order) {
    echo $order->customer?->trade_name;  // No additional queries
}

// Multiple relationships
$orders = Order::with([
    'customer',
    'items.product',
    'payments',
    'shippingAddress',
])->get();

// Nested eager loading
$orders = Order::with([
    'customer.company',
    'items.product.category',
])->get();

// Conditional eager loading
$orders = Order::with([
    'items' => function ($query) {
        $query->where('quantity', '>', 0);
    },
])->get();

// Eager loading with counts
$products = Product::withCount('reviews')
    ->withAvg('reviews', 'rating')
    ->get();
```

### Query Optimization

```php
// Select only needed columns
$users = User::select(['id', 'name', 'email'])->get();

// Use chunking for large datasets
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process user
    }
});

// Lazy collection for memory efficiency
User::lazy()->each(function ($user) {
    // Process user
});

// Use cursor for streaming
foreach (User::cursor() as $user) {
    // Memory efficient iteration
}

// Aggregate queries instead of collection methods
// Bad
$total = Order::all()->sum('amount');

// Good
$total = Order::sum('amount');
$average = Order::avg('amount');
$count = Order::count();
```

### Indexing Strategy

```php
// Migration with indexes
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id')->constrained();
    $table->string('status');
    $table->decimal('total_amount', 12, 4);
    $table->timestamps();

    // Single column indexes for frequent filters
    $table->index('status');
    $table->index('created_at');

    // Composite index for common query patterns
    $table->index(['customer_id', 'status']);
    $table->index(['status', 'created_at']);
});

// Add indexes to existing tables
Schema::table('products', function (Blueprint $table) {
    $table->index('category_id');
    $table->index(['is_active', 'created_at']);
});
```

### Query Scopes for Optimization

```php
<?php

class Product extends Model
{
    // Optimized filtering trait
    use Filterable;

    public static array $filtersCols = ['category_id', 'status'];
    public static array $searchCols = ['name', 'sku', 'description'];

    // Scope with index-friendly queries
    public function scopeActive($query)
    {
        return $query->where('is_active', true);
    }

    public function scopeInStock($query)
    {
        return $query->where('quantity', '>', 0);
    }

    // Avoid functions on indexed columns
    // Bad - prevents index usage
    public function scopeCreatedInYear($query, int $year)
    {
        return $query->whereYear('created_at', $year);
    }

    // Good - allows index usage
    public function scopeCreatedInYear($query, int $year)
    {
        return $query->whereBetween('created_at', [
            "{$year}-01-01 00:00:00",
            "{$year}-12-31 23:59:59",
        ]);
    }
}
```

## Caching Strategies

### Query Caching

```php
<?php

use Illuminate\Support\Facades\Cache;

class ProductService
{
    public function getCategories(): Collection
    {
        return Cache::remember('categories.all', 3600, function () {
            return Category::with('children')->get();
        });
    }

    public function getProduct(int $id): ?Product
    {
        return Cache::remember("products.{$id}", 3600, function () use ($id) {
            return Product::with(['category', 'variants'])->find($id);
        });
    }

    // Cache with tags for easy invalidation
    public function getProductsByCategory(int $categoryId): Collection
    {
        return Cache::tags(['products', "category.{$categoryId}"])
            ->remember("products.category.{$categoryId}", 3600, function () use ($categoryId) {
                return Product::where('category_id', $categoryId)->get();
            });
    }

    // Invalidate related caches
    public function updateProduct(Product $product, array $data): Product
    {
        $product->update($data);

        // Clear specific cache
        Cache::forget("products.{$product->id}");

        // Clear tagged caches
        Cache::tags(['products', "category.{$product->category_id}"])->flush();

        return $product;
    }
}
```

### Configuration Caching

```php
// Cache company settings (singleton with cache)
$this->app->singleton('company_settings_data', function () {
    $companyId = app('current_company')->id;

    return Cache::rememberForever("cs.{$companyId}", function () {
        return CompanySetting::query()
            ->where('company_id', app('current_company')->id)
            ->first();
    });
});

// Helper function
function cs(string $key = null, $default = null)
{
    $settings = app('company_settings_data');

    if ($key === null) {
        return $settings;
    }

    return $settings?->{$key} ?? $default;
}
```

### Response Caching

```php
<?php

// Middleware for response caching
namespace App\Http\Middleware;

use Illuminate\Support\Facades\Cache;

class CacheResponse
{
    public function handle($request, $next, $ttl = 3600)
    {
        if ($request->method() !== 'GET') {
            return $next($request);
        }

        $key = 'response.' . md5($request->fullUrl());

        return Cache::remember($key, $ttl, function () use ($next, $request) {
            return $next($request);
        });
    }
}
```

### Model Caching

```php
<?php

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Cache;

class Product extends Model
{
    protected static function booted()
    {
        // Clear cache on save
        static::saved(function ($product) {
            Cache::forget("products.{$product->id}");
            Cache::tags(['products'])->flush();
        });

        // Clear cache on delete
        static::deleted(function ($product) {
            Cache::forget("products.{$product->id}");
            Cache::tags(['products'])->flush();
        });
    }
}
```

## Code Optimization

### BaseModel Money Field Rounding

**Location**: `app/Models/BaseModel.php`

```php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class BaseModel extends Model
{
    use Filterable;

    protected $autoRoundMoney = true;
    protected $decimalPlaces = 4;
    private ?array $cachedMoneyFields = null;

    protected $moneyFields = [
        'price', 'amount', 'total', 'subtotal',
        'tax', 'discount', 'cost', 'balance',
        'credit', 'debit', 'rate', 'quantity', 'net',
    ];

    /**
     * Automatically round money fields
     */
    public function setAttribute($key, $value)
    {
        if ($this->shouldRoundValue($key, $value) && is_numeric($value)) {
            $value = round($value, $this->decimalPlaces);
        }

        return parent::setAttribute($key, $value);
    }

    protected function shouldRoundValue($key, $value): bool
    {
        return $this->autoRoundMoney &&
               $value !== null &&
               is_numeric($value) &&
               $this->isMoneyField($key);
    }

    protected function isMoneyField($key): bool
    {
        // Cache field lookup for performance
        if ($this->cachedMoneyFields === null) {
            $this->cachedMoneyFields = array_flip($this->moneyFields);
        }

        return isset($this->cachedMoneyFields[$key]) ||
               $this->isMoneyFieldByPattern($key);
    }

    protected function isMoneyFieldByPattern($key): bool
    {
        return str_ends_with($key, '_amount') ||
               str_ends_with($key, '_price') ||
               str_ends_with($key, '_cost') ||
               str_ends_with($key, '_total');
    }
}
```

### Efficient Filtering Trait

**Location**: `app/Core/Traits/Filterable.php`

```php
<?php

namespace App\Core\Traits;

trait Filterable
{
    public static array $multiFiltersCols = ['id'];
    public static $searchDate = null;

    public static function getFiltersCols(): array
    {
        return array_unique(static::$filtersCols ?? []);
    }

    public static function getMultiFiltersCols(): array
    {
        return array_unique(static::$multiFiltersCols ?? []);
    }

    /**
     * Apply text search filter
     */
    public function searchWordsFilter($query)
    {
        $tableName = $this->getTable();

        return $query->when(request()->search, function ($query) use ($tableName) {
            $searchCols = property_exists($this, 'searchCols') ? static::$searchCols : [];

            $query->where(function ($query) use ($tableName, $searchCols) {
                foreach ($searchCols as $column) {
                    $query->orWhere("{$tableName}.{$column}", 'like', '%' . request()->search . '%');
                }
            });
        });
    }

    /**
     * Apply select filters
     */
    public function searchSelectFilter($query)
    {
        $tableName = $this->getTable();

        return $query->where(function ($query) use ($tableName) {
            // Single value filters
            foreach (self::getFiltersCols() as $column) {
                if (request($column) !== null) {
                    $query->where("{$tableName}.{$column}", request($column));
                }
            }

            // Multi-value filters (whereIn)
            foreach (self::getMultiFiltersCols() as $column) {
                if (request($column) !== null) {
                    $ids = is_array(request($column))
                        ? request($column)
                        : explode(',', request($column));

                    $query->whereIn("{$tableName}.{$column}", $ids);
                }
            }
        });
    }

    /**
     * Apply date filter
     */
    public function searchDateFilter($query)
    {
        $dateCol = static::$searchDate ?? 'created_at';

        return $query
            ->when(request()->date_from, function ($query) use ($dateCol) {
                $query->where($dateCol, '>=', request()->date_from);
            })
            ->when(request()->date_to, function ($query) use ($dateCol) {
                $query->where($dateCol, '<=', request()->date_to);
            });
    }

    /**
     * Apply all filters
     */
    public function scopeFilter($query)
    {
        $query = $this->searchWordsFilter($query);
        $query = $this->searchSelectFilter($query);

        return $this->searchDateFilter($query);
    }
}
```

### Lazy Loading Prevention

```php
// In AppServiceProvider boot method
public function boot(): void
{
    // Prevent lazy loading in development
    if ($this->app->environment('local')) {
        Model::preventLazyLoading();
    }

    // Or log instead of throwing
    Model::preventLazyLoading(function ($model, $relation) {
        Log::warning("Lazy loading detected on {$model} for relation {$relation}");
    });
}
```

## Queue Optimization

### Job Processing

```php
<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

class ProcessLargeDataset implements ShouldQueue
{
    use InteractsWithQueue, Queueable, SerializesModels;

    public int $tries = 3;
    public int $timeout = 300;
    public int $maxExceptions = 3;

    public function __construct(
        private int $batchId
    ) {}

    public function handle(): void
    {
        // Use chunking for large datasets
        Record::where('batch_id', $this->batchId)
            ->chunk(1000, function ($records) {
                foreach ($records as $record) {
                    $this->processRecord($record);
                }
            });
    }

    public function failed(\Throwable $exception): void
    {
        Log::error('Job failed', [
            'batch_id' => $this->batchId,
            'error' => $exception->getMessage(),
        ]);
    }
}
```

### Batch Processing

```php
<?php

use Illuminate\Bus\Batch;
use Illuminate\Support\Facades\Bus;

$batch = Bus::batch([
    new ProcessOrder($order1),
    new ProcessOrder($order2),
    new ProcessOrder($order3),
])
->then(function (Batch $batch) {
    // All jobs completed
})
->catch(function (Batch $batch, \Throwable $e) {
    // First batch job failure
})
->finally(function (Batch $batch) {
    // Batch finished (success or failure)
})
->dispatch();
```

## API Response Optimization

### Pagination

```php
<?php

class ProductController extends Controller
{
    public function index(Request $request): JsonResponse
    {
        $products = Product::query()
            ->filter()
            ->with(['category:id,name'])
            ->select(['id', 'name', 'price', 'category_id'])
            ->paginate($request->get('per_page', 15));

        return $this->sendPaginatedResponse(
            ProductResource::collection($products)
        );
    }
}
```

### Resource Optimization

```php
<?php

namespace App\Http\Resources\V1\Catalog;

use Illuminate\Http\Resources\Json\JsonResource;

class ProductResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'price' => $this->price,

            // Conditional loading - only include if loaded
            'category' => new CategoryResource($this->whenLoaded('category')),
            'variants' => VariantResource::collection($this->whenLoaded('variants')),

            // Conditional counts
            'reviews_count' => $this->when(
                $this->reviews_count !== null,
                $this->reviews_count
            ),

            // Conditional attributes
            'full_details' => $this->when($request->include_details, [
                'description' => $this->description,
                'specifications' => $this->specifications,
            ]),
        ];
    }
}
```

## Performance Monitoring

### Query Logging

```php
// Enable query logging in development
if (app()->environment('local')) {
    DB::listen(function ($query) {
        Log::channel('queries')->info($query->sql, [
            'bindings' => $query->bindings,
            'time' => $query->time,
        ]);
    });
}

// Telescope for debugging
// Access at /telescope
```

### Slow Query Detection

```php
// Log slow queries
DB::listen(function ($query) {
    if ($query->time > 1000) { // Over 1 second
        Log::warning('Slow query detected', [
            'sql' => $query->sql,
            'bindings' => $query->bindings,
            'time_ms' => $query->time,
        ]);
    }
});
```

## Production Optimization

### Caching Commands

```bash
# Cache configuration
php artisan config:cache

# Cache routes
php artisan route:cache

# Cache views
php artisan view:cache

# Optimize autoloader
composer install --optimize-autoloader --no-dev

# All in one
php artisan optimize
```

### Horizon Configuration

```php
// config/horizon.php
'production' => [
    'supervisor-1' => [
        'connection' => 'redis',
        'queue' => ['default', 'notifications'],
        'balance' => 'auto',
        'processes' => 10,
        'tries' => 3,
    ],
    'supervisor-2' => [
        'connection' => 'redis',
        'queue' => ['reports', 'exports'],
        'balance' => 'auto',
        'processes' => 5,
        'tries' => 3,
        'timeout' => 3600,
    ],
],
```

## Best Practices Checklist

### Database

- [ ] Use eager loading for relationships
- [ ] Add indexes for frequently queried columns
- [ ] Use database transactions for multi-step operations
- [ ] Paginate large result sets
- [ ] Select only needed columns

### Caching

- [ ] Cache expensive queries
- [ ] Use cache tags for grouped invalidation
- [ ] Cache configuration and routes in production
- [ ] Implement cache warming for critical data

### Code

- [ ] Use lazy collections for large datasets
- [ ] Implement proper query scopes
- [ ] Avoid N+1 queries
- [ ] Use job queues for long-running tasks
- [ ] Optimize API resources with conditional loading