Before:  5000ms  (N+1 queries, no cache, no index)
Step 1:  2500ms  (eager loading, fix N+1)
Step 2:   500ms  (add indexes + query optimization)
Step 3:   200ms  (Redis cache + OPcache)
Step 4:   100ms  (CDN + read replica)

// ❌ N+1: 1 query for tenants + N queries for shops
$tenants = Tenant::all();
foreach ($tenants as $tenant) {
    echo $tenant->shops->count(); // 1 extra query per tenant!
}
// Total: 1 + N queries

// ✅ Eager loading: 2 queries total
$tenants = Tenant::with('shops')->get();
foreach ($tenants as $tenant) {
    echo $tenant->shops->count(); // No extra query
}
// Total: 2 queries

// ❌ Before: Dashboard loads in 5 seconds
// Controller: 1 + 3*N queries per tenant
public function dashboard(Tenant $tenant)
{
    $orders = $tenant->orders()
        ->whereMonth('created_at', now()->month)
        ->get();                          // Query 1

    $total = $orders->sum('total');
    $byShop = $orders->groupBy('shop_id');  // Lazy loads shops: N queries
    $topProducts = $orders->flatMap->items; // Lazy loads items: N queries
    $customers = $orders->pluck('customer'); // Lazy loads customer: N queries
}

// ✅ After: Dashboard loads in 500ms
// Controller: 4 queries total
public function dashboard(Tenant $tenant)
{
    $orders = $tenant->orders()
        ->with(['shop:id,name', 'items.product:id,name,price', 'customer:id,name'])
        ->whereMonth('created_at', now()->month)
        ->select(['id', 'shop_id', 'customer_id', 'total', 'created_at'])
        ->get();

    $total = $orders->sum('total');
    $byShop = $orders->groupBy('shop.name');
    $topProducts = $orders->flatMap->items->take(10);
    $customers = $orders->pluck('customer.name');
}

// Execution Successful

// Process 5 million orders without memory overflow
// Option 1: chunk (processes 500 at a time)
Order::where('tenant_id', $tenant->id)
    ->chunk(500, function ($orders) {
        foreach ($orders as $order) {
            $this->calculateOrderMetrics($order);
        }
    });

// Option 2: chunkById (more stable with concurrent writes)
Order::where('tenant_id', $tenant->id)
    ->select(['id', 'total', 'status'])
    ->chunkById(500, function ($orders) {
        $metrics = $orders->groupBy('status')->map->sum('total');
        Cache::put("metrics:{$tenant->id}", $metrics, 3600);
    });

// Option 3: cursor (streaming, lowest memory)
$total = 0;
foreach (Order::where('tenant_id', $tenant->id)->cursor() as $order) {
    $total += $order->total;
}

// Option 4: lazy (collection methods available)
$highValue = Order::where('tenant_id', $tenant->id)
    ->lazy(500)
    ->filter(fn ($o) => $o->total > 1000)
    ->count();

// Execution Successful

// app/Services/DashboardService.php
class DashboardService
{
    public function getOverview(Tenant $tenant): array
    {
        return Cache::remember(
            "dashboard:{$tenant->id}:overview",
            now()->addMinutes(15),
            fn () => $this->calculateOverview($tenant)
        );
    }

    public function getTopProducts(Tenant $tenant, string $period = '7d'): Collection
    {
        return Cache::remember(
            "dashboard:{$tenant->id}:top-products:{$period}",
            now()->addHours(1),
            fn () => $this->calculateTopProducts($tenant, $period)
        );
    }

    public function getRevenueChart(Tenant $tenant, string $granularity = 'daily'): array
    {
        return Cache::remember(
            "dashboard:{$tenant->id}:revenue:{$granularity}",
            now()->addMinutes(30),
            fn () => $this->calculateRevenueChart($tenant, $granularity)
        );
    }

    public function invalidateTenantCache(Tenant $tenant): void
    {
        $prefix = "dashboard:{$tenant->id}";
        // Redis KEYS is slow on large datasets; use tags or prefix scan
        Cache::getStore()->getConnection()->del(
            ...Cache::getStore()->getConnection()->keys("{$prefix}:*")
        );
    }
}

// Controller
class DashboardController extends Controller
{
    public function __construct(
        private DashboardService $dashboard
    ) {}

    public function index(Tenant $tenant)
    {
        return response()->json([
            'overview' => $this->dashboard->getOverview($tenant),
            'top_products' => $this->dashboard->getTopProducts($tenant),
            'revenue_chart' => $this->dashboard->getRevenueChart($tenant),
        ]);
    }
}

// Execution Successful

// Cache with tags (Redis/database drivers only)
Cache::tags(['dashboard', "tenant:{$tenant->id}"])->put(
    'overview',
    $data,
    3600
);

// Invalidate all dashboard cache for a tenant
Cache::tags(["tenant:{$tenant->id}"])->flush();

// Or use cache key conventions with prefix scan
Cache::forget("dashboard:{$tenant->id}:overview");
Cache::forget("dashboard:{$tenant->id}:top-products:7d");
Cache::forget("dashboard:{$tenant->id}:revenue:daily");

// Migration: Add indexes for ShopMetrics queries
Schema::table('orders', function (Blueprint $table) {
    // Single column index
    $table->index('tenant_id', 'idx_orders_tenant');
    $table->index('created_at', 'idx_orders_date');
    $table->index('status', 'idx_orders_status');

    // Composite index (most useful for multi-condition queries)
    $table->index(['tenant_id', 'created_at'], 'idx_orders_tenant_date');
    $table->index(['tenant_id', 'status', 'created_at'], 'idx_orders_tenant_status_date');

    // Unique index
    $table->unique(['tenant_id', 'external_id'], 'uniq_orders_tenant_external');
});

-- Analyze slow query
EXPLAIN SELECT * FROM orders 
WHERE tenant_id = 1 AND status = 'completed' 
ORDER BY created_at DESC LIMIT 20;

-- Look for:
-- type: ALL (full scan) → needs index
-- key: NULL (no index used) → needs index
-- rows: large number → needs optimization
-- Extra: Using filesort → needs composite index

-- ❌ Before: Full table scan on 5M rows (3000ms)
EXPLAIN SELECT * FROM orders WHERE tenant_id = 42;
-- type: ALL, rows: 5,000,000, key: NULL

-- Add index
ALTER TABLE orders ADD INDEX idx_orders_tenant (tenant_id);

-- ✅ After: Index scan (5ms)
EXPLAIN SELECT * FROM orders WHERE tenant_id = 42;
-- type: ref, rows: 50,000, key: idx_orders_tenant

-- ❌ Still slow: Sorting without index (500ms)
EXPLAIN SELECT * FROM orders 
WHERE tenant_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Extra: Using filesort

-- ✅ Composite index (3ms)
ALTER TABLE orders ADD INDEX idx_orders_tenant_date (tenant_id, created_at DESC);

CREATE TABLE

// config/database.php
'mysql' => [
    'read' => [
        'host' => env('DB_READ_HOST', 'read-replica.shopmetrics.internal'),
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', 'primary.shopmetrics.internal'),
    ],
    'sticky' => true,  // Read from write host after write in same request
    'driver' => 'mysql',
    'database' => env('DB_DATABASE', 'shopmetrics'),
    // ...
],

; php.ini or php.d/opcache.ini
opcache.enable=1
opcache.memory_consumption=256        ; 256MB for bytecode cache
opcache.interned_strings_buffer=32    ; 32MB for strings
opcache.max_accelerated_files=40000   ; Laravel has ~12000 files
opcache.validate_timestamps=0         ; OFF in production (manual reset)
opcache.save_comments=1               ; Required by Laravel annotations
opcache.jit=1255                      ; PHP 8.2 JIT: tracing mode
opcache.jit_buffer_size=128M          ; 128MB JIT buffer

; php-fpm.d/www.conf
pm = dynamic
pm.max_children = 50          ; = (RAM - OS) / avg_per_process
pm.start_servers = 10         ; = min_spare + (max - min) / 2
pm.min_spare_servers = 5
pm.max_spare_servers = 20
pm.max_requests = 1000        ; Prevent memory leaks

# After deployment, reset OPcache
php artisan optimize:clear
# Or via route
Route::get('/opcache-clear', function () {
    if (app()->environment('production')) {
        opcache_reset();
    }
    return 'OPcache cleared';
})->middleware('auth:api');

#!/bin/bash
# deploy-optimize.sh — Run after each deployment

set -e

echo "→ Clearing all caches..."
php artisan optimize:clear

echo "→ Caching config..."
php artisan config:cache

echo "→ Caching routes..."
php artisan route:cache

echo "→ Caching views..."
php artisan view:cache

echo "→ Running migrations..."
php artisan migrate --force

echo "→ Restarting queue workers..."
php artisan queue:restart

echo "→ Clearing OPcache..."
php artisan optimize

echo "✓ Optimization complete!"

# Command executed successfully

// k6-scripts/dashboard-load.js
import http from 'k6/http';
import { check, sleep } from 'k6';

export const options = {
  stages: [
    { duration: '30s', target: 20 },   // Ramp up to 20 users
    { duration: '2m', target: 20 },    // Stay at 20
    { duration: '30s', target: 50 },   // Ramp up to 50
    { duration: '2m', target: 50 },    // Stay at 50
    { duration: '30s', target: 0 },    // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'],  // 95% requests < 500ms
    http_req_failed: ['rate<0.01'],    // Error rate < 1%
  },
};

const BASE_URL = 'https://api.shopmetrics.io';
const TOKEN = __ENV.API_TOKEN;

export default function () {
  const params = {
    headers: { Authorization: `Bearer ${TOKEN}` },
  };

  // Test dashboard API
  const res = http.get(`${BASE_URL}/api/v1/dashboard`, params);

  check(res, {
    'status is 200': (r) => r.status === 200,
    'response time < 500ms': (r) => r.timings.duration < 500,
    'has overview data': (r) => JSON.parse(r.body).overview !== null,
  });

  sleep(1);
}

# Apache Bench: 100 concurrent users, 1000 total requests
ab -n 1000 -c 100 -H "Authorization: Bearer $TOKEN" \
   https://api.shopmetrics.io/api/v1/dashboard

# wrk: 50 connections for 30 seconds
wrk -t4 -c50 -d30s -H "Authorization: Bearer $TOKEN" \
    https://api.shopmetrics.io/api/v1/dashboard

# k6: Run load test with threshold checks
k6 run --env API_TOKEN=$TOKEN k6-scripts/dashboard-load.js

// ============================================
// Comprehensive: Dashboard API optimization
// Before vs After with all techniques applied
// ============================================

// ❌ BEFORE: Slow dashboard (5 seconds, 200+ queries)
class DashboardController extends Controller
{
    public function index(Request $request)
    {
        $tenant = $request->user()->tenant;

        $orders = $tenant->orders()
            ->whereMonth('created_at', now()->month)
            ->get();  // Loads ALL columns, no eager loading

        return response()->json([
            'total_revenue' => $orders->sum('total'),
            'order_count' => $orders->count(),
            'top_shops' => $orders->groupBy('shop_id')  // N+1 on shop
                ->map->sum('total')
                ->sortDesc()
                ->take(5),
            'recent_orders' => $orders->sortByDesc('created_at')
                ->take(10)
                ->map(fn ($o) => [
                    'id' => $o->id,
                    'customer' => $o->customer->name,  // N+1 on customer
                    'total' => $o->total,
                ]),
        ]);
    }
}

// ✅ AFTER: Fast dashboard (100ms, 3 queries, cached)
class DashboardController extends Controller
{
    public function index(Request $request)
    {
        $tenant = $request->user()->tenant;

        $data = Cache::remember(
            "dashboard:{$tenant->id}:overview",
            now()->addMinutes(15),
            fn () => $this->buildDashboardData($tenant)
        );

        return response()->json($data);
    }

    private function buildDashboardData(Tenant $tenant): array
    {
        // Single optimized query with aggregations
        $stats = DB::table('orders')
            ->where('tenant_id', $tenant->id)
            ->whereMonth('created_at', now()->month)
            ->select([
                DB::raw('SUM(total) as total_revenue'),
                DB::raw('COUNT(*) as order_count'),
            ])
            ->first();

        // Top shops: 1 query
        $topShops = DB::table('orders')
            ->join('shops', 'orders.shop_id', '=', 'shops.id')
            ->where('orders.tenant_id', $tenant->id)
            ->whereMonth('orders.created_at', now()->month)
            ->groupBy('shops.id', 'shops.name')
            ->orderByDesc('revenue')
            ->limit(5)
            ->select('shops.name', DB::raw('SUM(orders.total) as revenue'))
            ->get();

        // Recent orders with eager loading: 1 query
        $recentOrders = Order::with('customer:id,name')
            ->where('tenant_id', $tenant->id)
            ->select(['id', 'customer_id', 'total', 'created_at'])
            ->latest()
            ->limit(10)
            ->get()
            ->map(fn ($o) => [
                'id' => $o->id,
                'customer' => $o->customer->name,
                'total' => $o->total,
            ]);

        return [
            'total_revenue' => (float) $stats->total_revenue,
            'order_count' => (int) $stats->order_count,
            'top_shops' => $topShops,
            'recent_orders' => $recentOrders,
        ];
    }
}