# AUTO TESTER - Performance Optimization Guide

## Table of Contents

1. [Database Optimization](#database-optimization)
2. [PHP Performance](#php-performance)
3. [Frontend Optimization](#frontend-optimization)
4. [Caching Strategies](#caching-strategies)
5. [Server Configuration](#server-configuration)
6. [Monitoring and Profiling](#monitoring-and-profiling)

---

## Database Optimization

### 1. Add Strategic Indexes

```sql
-- Users table indexes
ALTER TABLE `users` ADD INDEX `idx_role_status` (`role`, `status`);
ALTER TABLE `users` ADD INDEX `idx_last_login` (`last_login`);
ALTER TABLE `users` ADD INDEX `idx_email_active` (`email`, `is_active`);

-- Inspection requests indexes
ALTER TABLE `inspection_requests` ADD INDEX `idx_status_created` (`status`, `created_at`);
ALTER TABLE `inspection_requests` ADD INDEX `idx_client_status` (`client_id`, `status`);
ALTER TABLE `inspection_requests` ADD INDEX `idx_inspector_status` (`inspector_id`, `status`);

-- Notifications indexes
ALTER TABLE `notifications` ADD INDEX `idx_user_read` (`user_id`, `read_at`);
ALTER TABLE `notifications` ADD INDEX `idx_type_created` (`type`, `created_at`);

-- Training system indexes
ALTER TABLE `trainees` ADD INDEX `idx_status_dates` (`status`, `training_start_date`, `training_end_date`);
ALTER TABLE `trainees` ADD INDEX `idx_university_status` (`university`, `status`);

-- Recruitment system indexes
ALTER TABLE `job_applications` ADD INDEX `idx_status_created` (`status`, `created_at`);
ALTER TABLE `job_applications` ADD INDEX `idx_job_status` (`job_id`, `status`);
```

### 2. Optimize Slow Queries

```php
// Before: Inefficient query
$users = $pdo->query("SELECT * FROM users WHERE role = 'inspector'");

// After: Optimized query
$users = $pdo->query("SELECT id, full_name, email, phone FROM users WHERE role = 'inspector' AND is_active = 1");

// Use LIMIT for pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 20;
$offset = ($page - 1) * $limit;

$sql = "SELECT id, full_name, email FROM users LIMIT $limit OFFSET $offset";
```

### 3. Database Connection Pooling

```php
// In Database.php
class Database {
    private static $connections = [];
    private static $maxConnections = 10;
    
    public static function getConnection() {
        $key = self::getConnectionKey();
        
        if (!isset(self::$connections[$key])) {
            if (count(self::$connections) >= self::$maxConnections) {
                // Remove oldest connection
                array_shift(self::$connections);
            }
            
            self::$connections[$key] = new PDO(
                "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME,
                DB_USER,
                DB_PASS,
                [
                    PDO::ATTR_PERSISTENT => true,
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                ]
            );
        }
        
        return self::$connections[$key];
    }
}
```

---

## PHP Performance

### 1. Enable OPcache

```ini
; In php.ini
opcache.enable=1
opcache.memory_consumption=128
opcache.interned_strings_buffer=8
opcache.max_accelerated_files=4000
opcache.revalidate_freq=60
opcache.fast_shutdown=1
opcache.enable_cli=1
```

### 2. Use Efficient Data Structures

```php
// Before: Multiple database calls
foreach ($userIds as $userId) {
    $user = getUserById($userId);
    // Process user
}

// After: Single database call
$users = getUsersByIds($userIds);
foreach ($users as $user) {
    // Process user
}
```

### 3. Optimize Loops and Conditions

```php
// Before: Inefficient loop
foreach ($items as $item) {
    if ($item['status'] == 'active') {
        // Process active items
    }
}

// After: Filter first, then process
$activeItems = array_filter($items, function($item) {
    return $item['status'] == 'active';
});

foreach ($activeItems as $item) {
    // Process active items
}
```

---

## Frontend Optimization

### 1. Image Optimization

```html
<!-- Use modern image formats with fallback -->
<picture>
    <source srcset="image.webp" type="image/webp">
    <source srcset="image.avif" type="image/avif">
    <img src="image.jpg" loading="lazy" alt="Description" width="300" height="200">
</picture>

<!-- Responsive images -->
<img srcset="image-small.jpg 480w, image-medium.jpg 768w, image-large.jpg 1024w"
     sizes="(max-width: 480px) 480px, (max-width: 768px) 768px, 1024px"
     src="image-medium.jpg" alt="Description">
```

### 2. CSS and JavaScript Minification

```bash
# Minify CSS
npx clean-css-cli -o styles.min.css styles.css

# Minify JavaScript
npx terser script.js -o script.min.js

# Combine files
cat styles1.css styles2.css > combined.css
```

### 3. Lazy Loading

```javascript
// Intersection Observer for lazy loading
const imageObserver = new IntersectionObserver((entries, observer) => {
    entries.forEach(entry => {
        if (entry.isIntersecting) {
            const img = entry.target;
            img.src = img.dataset.src;
            img.classList.remove('lazy');
            observer.unobserve(img);
        }
    });
});

document.querySelectorAll('img.lazy').forEach(img => {
    imageObserver.observe(img);
});
```

---

## Caching Strategies

### 1. Redis Caching

```php
class Cache {
    private static $redis = null;
    
    public static function getInstance() {
        if (self::$redis === null) {
            self::$redis = new Redis();
            self::$redis->connect('127.0.0.1', 6379);
        }
        return self::$redis;
    }
    
    public static function get($key, $default = null) {
        $value = self::getInstance()->get($key);
        return $value !== false ? json_decode($value, true) : $default;
    }
    
    public static function set($key, $value, $ttl = 3600) {
        return self::getInstance()->setex($key, $ttl, json_encode($value));
    }
    
    public static function remember($key, $callback, $ttl = 3600) {
        $value = self::get($key);
        if ($value === null) {
            $value = $callback();
            self::set($key, $value, $ttl);
        }
        return $value;
    }
}

// Usage example
$users = Cache::remember('active_users', function() {
    return getUsersByStatus('active');
}, 300); // Cache for 5 minutes
```

### 2. HTTP Caching

```php
// Set cache headers
function setCacheHeaders($ttl = 3600) {
    $expires = gmdate('D, d M Y H:i:s', time() + $ttl) . ' GMT';
    header("Expires: $expires");
    header("Cache-Control: public, max-age=$ttl");
    header("Last-Modified: " . gmdate('D, d M Y H:i:s') . ' GMT');
}

// For static assets
$assetTypes = [
    'css' => 86400,    // 1 day
    'js' => 86400,     // 1 day
    'jpg' => 604800,   // 1 week
    'png' => 604800,   // 1 week
    'webp' => 604800   // 1 week
];

$extension = pathinfo($file, PATHINFO_EXTENSION);
if (isset($assetTypes[$extension])) {
    setCacheHeaders($assetTypes[$extension]);
}
```

### 3. Database Query Caching

```php
class QueryCache {
    public static function remember($query, $params = [], $ttl = 300) {
        $key = 'query_' . md5($query . serialize($params));
        
        return Cache::remember($key, function() use ($query, $params) {
            global $pdo;
            $stmt = $pdo->prepare($query);
            $stmt->execute($params);
            return $stmt->fetchAll();
        }, $ttl);
    }
}

// Usage
$users = QueryCache::remember(
    "SELECT * FROM users WHERE role = ? AND is_active = 1",
    ['inspector'],
    600
);
```

---

## Server Configuration

### 1. Apache Optimization

```apache
# Enable compression
<IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/html text/css text/javascript application/javascript application/json
    AddOutputFilterByType DEFLATE text/xml application/xml text/x-component
    AddOutputFilterByType DEFLATE application/xhtml+xml application/rss+xml application/atom+xml
    AddOutputFilterByType DEFLATE image/svg+xml
</IfModule>

# Enable caching
<IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType text/css "access plus 1 year"
    ExpiresByType application/javascript "access plus 1 year"
    ExpiresByType image/png "access plus 1 month"
    ExpiresByType image/jpg "access plus 1 month"
    ExpiresByType image/gif "access plus 1 month"
    ExpiresByType image/webp "access plus 1 month"
</IfModule>

# Keep alive connections
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 5
```

### 2. Nginx Optimization

```nginx
# Enable gzip compression
gzip on;
gzip_vary on;
gzip_min_length 1024;
gzip_types text/plain text/css text/xml text/javascript application/javascript application/xml+rss application/json;

# Enable caching
location ~* \.(jpg|jpeg|png|gif|ico|css|js|webp)$ {
    expires 1y;
    add_header Cache-Control "public, immutable";
}

# FastCGI cache
fastcgi_cache_path /var/cache/nginx levels=1:2 keys_zone=APP_CACHE:100m inactive=60m;
fastcgi_cache_key "$scheme$request_method$host$request_uri";

location ~ \.php$ {
    fastcgi_cache APP_CACHE;
    fastcgi_cache_valid 200 60m;
    fastcgi_cache_bypass $cookie_nocache $arg_nocache;
    fastcgi_no_cache $cookie_nocache $arg_nocache;
}
```

### 3. PHP-FPM Optimization

```ini
; In php-fpm.conf
pm = dynamic
pm.max_children = 50
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35
pm.max_requests = 500

; Process priority
process.priority = -5

; Memory limit
php_admin_value[memory_limit] = 256M
```

---

## Monitoring and Profiling

### 1. Performance Monitoring

```php
class PerformanceMonitor {
    private static $startTime;
    private static $queries = [];
    
    public static function start() {
        self::$startTime = microtime(true);
    }
    
    public static function logQuery($sql, $params) {
        self::$queries[] = [
            'sql' => $sql,
            'params' => $params,
            'time' => microtime(true)
        ];
    }
    
    public static function end() {
        $totalTime = microtime(true) - self::$startTime;
        
        $log = [
            'timestamp' => date('Y-m-d H:i:s'),
            'total_time' => $totalTime,
            'queries' => count(self::$queries),
            'memory_usage' => memory_get_peak_usage(true),
            'url' => $_SERVER['REQUEST_URI']
        ];
        
        file_put_contents('storage/logs/performance.log', json_encode($log) . "\n", FILE_APPEND);
    }
}

// Usage
PerformanceMonitor::start();
// Your code here
PerformanceMonitor::end();
```

### 2. Database Query Profiling

```php
class QueryProfiler {
    private static $slowQueries = [];
    
    public static function profile($query, $params = []) {
        $start = microtime(true);
        
        // Execute query
        $result = executeQuery($query, $params);
        
        $executionTime = microtime(true) - $start;
        
        // Log slow queries (> 100ms)
        if ($executionTime > 0.1) {
            self::$slowQueries[] = [
                'query' => $query,
                'params' => $params,
                'time' => $executionTime,
                'timestamp' => date('Y-m-d H:i:s')
            ];
            
            error_log("Slow Query: {$query} - {$executionTime}s");
        }
        
        return $result;
    }
    
    public static function getSlowQueries() {
        return self::$slowQueries;
    }
}
```

### 3. Real-time Monitoring Dashboard

```javascript
// Performance monitoring dashboard
class PerformanceDashboard {
    constructor() {
        this.metrics = {
            responseTime: [],
            memoryUsage: [],
            queryCount: [],
            errorRate: []
        };
        
        this.init();
    }
    
    init() {
        this.startMetricsCollection();
        this.updateDashboard();
    }
    
    startMetricsCollection() {
        setInterval(() => {
            this.collectMetrics();
        }, 5000); // Collect every 5 seconds
    }
    
    async collectMetrics() {
        try {
            const response = await fetch('/api/performance/metrics');
            const metrics = await response.json();
            
            this.updateMetrics(metrics);
        } catch (error) {
            console.error('Failed to collect metrics:', error);
        }
    }
    
    updateMetrics(metrics) {
        this.metrics.responseTime.push(metrics.response_time);
        this.metrics.memoryUsage.push(metrics.memory_usage);
        this.metrics.queryCount.push(metrics.query_count);
        
        // Keep only last 20 data points
        Object.keys(this.metrics).forEach(key => {
            if (this.metrics[key].length > 20) {
                this.metrics[key].shift();
            }
        });
    }
    
    updateDashboard() {
        // Update charts and displays
        this.updateResponseTimeChart();
        this.updateMemoryUsageChart();
        this.updateQueryCountChart();
        
        setTimeout(() => this.updateDashboard(), 1000);
    }
}

// Initialize dashboard
new PerformanceDashboard();
```

---

## Performance Testing

### 1. Load Testing Script

```bash
#!/bin/bash
# load-test.sh - Simple load testing script

URL="http://localhost/unified-api.php"
CONCURRENT_USERS=10
REQUESTS_PER_USER=100

echo "Starting load test..."
echo "URL: $URL"
echo "Concurrent Users: $CONCURRENT_USERS"
echo "Requests per User: $REQUESTS_PER_USER"

for i in $(seq 1 $CONCURRENT_USERS); do
    (
        for j in $(seq 1 $REQUESTS_PER_USER); do
            curl -s -o /dev/null -w "%{http_code}\n" "$URL" &
        done
        wait
    ) &
done

wait

echo "Load test completed!"
```

### 2. Database Performance Test

```php
// Database performance testing
class DatabasePerformanceTest {
    public static function testConnectionSpeed() {
        $times = [];
        
        for ($i = 0; $i < 100; $i++) {
            $start = microtime(true);
            $pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
            $times[] = microtime(true) - $start;
        }
        
        $avgTime = array_sum($times) / count($times);
        echo "Average connection time: " . ($avgTime * 1000) . "ms\n";
    }
    
    public static function testQuerySpeed() {
        $pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
        
        $start = microtime(true);
        $pdo->query("SELECT * FROM large_table LIMIT 1000");
        $time = microtime(true) - $start;
        
        echo "Query time: " . ($time * 1000) . "ms\n";
    }
}
```

---

## Performance Checklist

### Database
- [ ] Add indexes for frequently queried columns
- [ ] Optimize slow queries
- [ ] Use connection pooling
- [ ] Enable query cache
- [ ] Monitor query performance

### PHP
- [ ] Enable OPcache
- [ ] Use efficient data structures
- [ ] Minimize memory usage
- [ ] Implement error handling
- [ ] Profile memory usage

### Frontend
- [ ] Optimize images
- [ ] Minify CSS/JS
- [ ] Implement lazy loading
- [ ] Use CDN for static assets
- [ ] Enable browser caching

### Server
- [ ] Enable compression
- [ ] Configure caching headers
- [ ] Optimize PHP-FPM
- [ ] Monitor server resources
- [ ] Implement load balancing

### Monitoring
- [ ] Set up performance monitoring
- [ ] Track response times
- [ ] Monitor error rates
- [ ] Profile database queries
- [ ] Create performance dashboards

---

*Last Updated: 2026-04-22*
*Version: 1.0*
