# Eager-Loading Optimization & Filter Service Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Fix eager-loading performance issues across the API and extract `UnitSearchController` filtering logic into a reusable `FilterService`.

**Architecture:** Create a single `App\Services\Filter\FilterService` that applies location, price, availability, facility, payment-method, keyword, and sorting filters to a `Unit` query. Controllers delegate filter application to this service and only add owner-scoping and pagination. Eager loads are limited/select-ed to avoid unbounded data transfer.

**Tech Stack:** PHP 8.x, Laravel 11, Eloquent, Spatie Media Library, Pest/PHPUnit.

---

## File map

| File | Action | Responsibility |
|------|--------|----------------|
| `app/Services/Filter/FilterService.php` | Create | Centralized filter logic for units and buildings |
| `app/Http/Controllers/UnitSearchController.php` | Modify | Delegate to `FilterService`; limit eager loads |
| `app/Http/Controllers/UnitController.php` | Modify | Reuse `FilterService`; limit eager loads; remove broken `clone()` fallback |
| `app/Http/Controllers/BuildingController.php` | Modify | Limit eager loads; keep building filters |
| `app/Http/Resources/BuildingResource.php` | Modify | Use `OwnerResource` for `owner` |
| `app/Http/Controllers/InvoiceController.php` | Modify | Remove wasted eager load |
| `app/Http/Controllers/ReceiptController.php` | Modify | Remove wasted eager loads; replace `whereIn` with `whereHas` |
| `app/Http/Controllers/ReservationController.php` | Modify | Replace `whereIn` with `whereHas`; keep needed loads |
| `app/Http/Controllers/Api/Customer/CustomerController.php` | Modify | Move `with('user')` out of `whereHas` closure |
| `app/Http/Controllers/UnitAvailabilityController.php` | Modify | Select columns; avoid loading customer for empty slots |
| `app/Http/Controllers/RegionController.php` | Modify | Limit/select `buildings` eager load |
| `app/Http/Controllers/CountryController.php` | Modify | Limit/select `cities` eager load |
| `app/Http/Controllers/CityController.php` | Modify | Limit/select `regions` eager load |
| `app/Http/Controllers/CurrencyController.php` | Modify | Limit/select `countries` eager load |
| `app/Services/PromoCodeService.php` | Modify | Eager-load `building.owner` where needed |
| `app/Services/ReservationReminderScheduler.php` | Modify | Eager-load `unit.building` and `customer` |
| `app/Services/ReservationService.php` | Modify | Load minimal columns for unit/building comparisons |
| `app/Services/DashboardMetrics.php` | Modify | Memoize building ids; use joins where practical |
| `app/Console/Commands/SendDueScheduledNotifications.php` | Modify | Eager-load `notifiable` and `reservation` |
| `app/Console/Commands/DeleteUnverifiedUsers.php` | Modify | Use `chunkById` |

---

## Task 1: Create `FilterService`

**Files:**
- Create: `app/Services/Filter/FilterService.php`

- [ ] **Step 1: Create the service class**

```php
<?php

namespace App\Services\Filter;

use Illuminate\Database\Eloquent\Builder;

class FilterService
{
    public function applyToUnitQuery(Builder $query, array $filters, bool $public = false): Builder
    {
        if ($public) {
            $query->where('status', 'available')
                ->whereHas('building', fn ($b) => $b->where('status', 'active'));
        }

        // Location
        if ($regionId = $filters['region_id'] ?? null) {
            $query->whereHas('building', fn ($b) => $b->where('region_id', $regionId));
        }

        if ($cityId = $filters['city_id'] ?? null) {
            $query->whereHas('building.region', fn ($r) => $r->where('city_id', $cityId));
        }

        if ($countryId = $filters['country_id'] ?? null) {
            $query->whereHas('building.region.city', fn ($c) => $c->where('country_id', $countryId));
        }

        // Guest capacity
        if (isset($filters['guests'])) {
            $query->whereRaw('(max_adults + max_children) >= ?', [(int) $filters['guests']]);
        }

        // Guest type
        if (! empty($filters['guest_type'])) {
            $guestTypes = array_unique([...(array) $filters['guest_type'], 'both']);
            $query->whereIn('guest_type', $guestTypes);
        }

        // Rooms
        if (! empty($filters['rooms'])) {
            $rooms = array_map('intval', array_unique((array) $filters['rooms']));
            $roomsPlusThreshold = 4;
            $exact = array_values(array_filter($rooms, fn (int $r) => $r < $roomsPlusThreshold));
            $includePlus = in_array($roomsPlusThreshold, $rooms, true);

            $query->where(function ($w) use ($exact, $includePlus, $roomsPlusThreshold) {
                if ($exact !== []) {
                    $w->whereIn('rooms', $exact);
                }

                if ($includePlus) {
                    $method = $exact !== [] ? 'orWhere' : 'where';
                    $w->{$method}('rooms', '>=', $roomsPlusThreshold);
                }
            });
        }

        // Price
        if (array_key_exists('min_price', $filters)) {
            $query->whereRaw('CAST(COALESCE(offer_price, base_price) AS REAL) >= ?', [(float) $filters['min_price']]);
        }

        if (array_key_exists('max_price', $filters)) {
            $query->whereRaw('CAST(COALESCE(offer_price, base_price) AS REAL) <= ?', [(float) $filters['max_price']]);
        }

        // Facilities
        if (! empty($filters['facilities'])) {
            foreach ((array) $filters['facilities'] as $fid) {
                $query->whereHas('facilities', fn ($f) => $f->where('facilities.id', $fid));
            }
        }

        // Payment methods
        if (! empty($filters['payment_method'])) {
            $paymentMethods = (array) $filters['payment_method'];
            $query->whereHas('building', function ($b) use ($paymentMethods) {
                $b->where(function ($w) use ($paymentMethods) {
                    foreach ($paymentMethods as $m) {
                        $w->orWhereJsonContains('payment_methods', $m);
                    }
                });
            });
        }

        // Availability
        if (! empty($filters['check_in']) && ! empty($filters['check_out'])) {
            $query->whereDoesntHave('availabilities', fn ($a) =>
                $a->whereIn('status', ['blocked', 'booked'])
                  ->where('date', '>=', $filters['check_in'])
                  ->where('date', '<', $filters['check_out']));
        }

        // Keyword
        if (! empty($filters['q'])) {
            $like = '%' . $filters['q'] . '%';
            $query->where(function ($w) use ($like) {
                $w->where('name_or_number', 'like', $like)
                  ->orWhereHas('building', fn ($b) => $b->where('name', 'like', $like))
                  ->orWhereHas('building.region', fn ($r) => $r->where('name', 'like', $like))
                  ->orWhereHas('building.region.city', fn ($c) => $c->where('name', 'like', $like));
            });
        }

        // Sorting
        $sort = $filters['sort'] ?? 'newest';
        match ($sort) {
            'price_asc' => $query->orderByRaw('COALESCE(offer_price, base_price) asc'),
            'price_desc' => $query->orderByRaw('COALESCE(offer_price, base_price) desc'),
            default => $query->latest(),
        };

        return $query;
    }
}
```

- [ ] **Step 2: Verify syntax**

Run: `php -l app/Services/Filter/FilterService.php`
Expected: `No syntax errors detected`

---

## Task 2: Refactor `UnitSearchController`

**Files:**
- Modify: `app/Http/Controllers/UnitSearchController.php`

- [ ] **Step 1: Replace the inline query with `FilterService`**

```php
<?php

namespace App\Http\Controllers;

use App\Http\Requests\SearchUnitsRequest;
use App\Http\Resources\UnitResource;
use App\Models\Unit;
use App\Services\Filter\FilterService;

class UnitSearchController extends Controller
{
    public function __construct(private FilterService $filterService)
    {
    }

    public function index(SearchUnitsRequest $request)
    {
        $v = $request->validated();
        $perPage = $v['per_page'] ?? 12;

        $query = $this->filterService->applyToUnitQuery(Unit::query(), $v, public: true);

        $units = $query
            ->with([
                'building.region.city.country' => fn ($q) => $q->select('id', 'name'),
                'facilities' => fn ($q) => $q->select('facilities.id', 'facilities.name'),
                'media' => fn ($q) => $q->take(5),
            ])
            ->paginate($perPage)
            ->appends($request->query());

        return UnitResource::collection($units);
    }
}
```

- [ ] **Step 2: Verify syntax**

Run: `php -l app/Http/Controllers/UnitSearchController.php`

---

## Task 3: Refactor `UnitController::index`

**Files:**
- Modify: `app/Http/Controllers/UnitController.php`

- [ ] **Step 1: Inject `FilterService` and replace filters**

```php
use App\Services\Filter\FilterService;

class UnitController extends Controller
{
    use HandlesMediaPhotos;

    public function __construct(private FilterService $filterService)
    {
    }

    public function index(ListingFilterRequest $request, ?Building $building = null)
    {
        $query = Unit::query();

        if ($building) {
            if ($building->owner_id !== auth()->user()->owner?->id) {
                abort(403, 'You do not own this building.');
            }
            $query->where('building_id', $building->id);
        }

        $query = $this->filterService->applyToUnitQuery($query, $request->validated(), public: false);

        return UnitResource::collection(
            $query->with([
                'building' => fn ($q) => $q->select('id', 'name', 'slug'),
                'building.media' => fn ($q) => $q->take(1),
                'media' => fn ($q) => $q->take(5),
            ])->paginate(10)
        );
    }
```

- [ ] **Step 2: Remove the old `try/catch clone()` block and duplicated filter code**

The old code from lines 36-76 should be deleted; only the new `index` method body remains.

- [ ] **Step 3: Verify syntax**

Run: `php -l app/Http/Controllers/UnitController.php`

---

## Task 4: Optimize `BuildingController` eager loads

**Files:**
- Modify: `app/Http/Controllers/BuildingController.php`
- Modify: `app/Http/Resources/BuildingResource.php`

- [ ] **Step 1: Limit loads in `index`**

```php
$query = Building::with([
    'region.city.country.currency' => fn ($q) => $q->select('id', 'name', 'code', 'symbol'),
    'media' => fn ($q) => $q->take(5),
    'facilities' => fn ($q) => $q->select('facilities.id', 'facilities.name'),
    'owner' => fn ($q) => $q->select('id', 'status', 'whatsapp_number'),
    'owner.user' => fn ($q) => $q->select('id', 'name', 'email', 'phone'),
]);
```

- [ ] **Step 2: Limit loads in `show`, `store`, `update`, `addPhotos`, `disable`**

For single-building endpoints, do not eager-load `units.media`. Instead load only unit summary:
```php
$building->load([
    'region.city.country.currency' => fn ($q) => $q->select('id', 'name', 'code', 'symbol'),
    'units' => fn ($q) => $q->select('id', 'building_id', 'name_or_number', 'floor', 'status', 'base_price', 'offer_price'),
    'media' => fn ($q) => $q->take(5),
    'facilities' => fn ($q) => $q->select('facilities.id', 'facilities.name'),
    'owner' => fn ($q) => $q->select('id', 'status', 'whatsapp_number'),
    'owner.user' => fn ($q) => $q->select('id', 'name', 'email', 'phone'),
]);
```

- [ ] **Step 3: Switch `BuildingResource` to `OwnerResource`**

```php
'owner' => OwnerResource::make($this->whenLoaded('owner')),
```

- [ ] **Step 4: Verify syntax**

Run: `php -l app/Http/Controllers/BuildingController.php` and `php -l app/Http/Resources/BuildingResource.php`

---

## Task 5: Remove wasted eager loads

**Files:**
- Modify: `app/Http/Controllers/InvoiceController.php`
- Modify: `app/Http/Controllers/ReceiptController.php`

- [ ] **Step 1: `InvoiceController::index`**

```php
$query = Invoice::where('received', 'owner');
```

- [ ] **Step 2: `ReceiptController::index`**

```php
$query = Receipt::latest();
```

- [ ] **Step 3: `ReceiptController::store`**

```php
$invoice = Invoice::findOrFail($data['invoice_id']);
```

- [ ] **Step 4: `ReceiptController::store` return**

```php
return ReceiptResource::make($receipt);
```

- [ ] **Step 5: Verify syntax**

Run: `php -l` on both files.

---

## Task 6: Replace `pluck()->pluck()` with `whereHas`

**Files:**
- Modify: `app/Http/Controllers/ReservationController.php`
- Modify: `app/Http/Controllers/ReceiptController.php`

- [ ] **Step 1: `ReservationController::index` owner/employee branch**

```php
} elseif ($user->isOwner() || $user->isEmployee()) {
    $ownerId = $user->isOwner() ? $user->owner?->id : $user->employee?->owner?->id;
    $query->whereHas('unit.building', fn ($q) => $q->where('owner_id', $ownerId));
}
```

- [ ] **Step 2: `ReceiptController::index` owner/employee branch**

```php
} elseif ($user->isOwner() || $user->isEmployee()) {
    $ownerId = $user->isOwner() ? $user->owner?->id : $user->employee?->owner?->id;
    $query->whereHas('reservation.unit.building', fn ($q) => $q->where('owner_id', $ownerId));
}
```

- [ ] **Step 3: Verify syntax**

Run: `php -l` on both files.

---

## Task 7: Fix `CustomerController` misplaced `with`

**Files:**
- Modify: `app/Http/Controllers/Api/Customer/CustomerController.php`

- [ ] **Step 1: Move `with('user')` to outer query**

```php
} elseif ($user->hasRole('owner')) {
    $customers = Customer::with('user')
        ->whereHas('reservations.unit.building', fn ($query) =>
            $query->where('owner_id', $user->owner->id))
        ->paginate(15);
} elseif ($user->hasRole('employee')) {
    $customers = Customer::with('user')
        ->whereHas('reservations.unit.building', fn ($query) =>
            $query->where('owner_id', $user->employee->owner->id))
        ->paginate(15);
}
```

- [ ] **Step 2: Verify syntax**

Run: `php -l app/Http/Controllers/Api/Customer/CustomerController.php`

---

## Task 8: Optimize `UnitAvailabilityController`

**Files:**
- Modify: `app/Http/Controllers/UnitAvailabilityController.php`

- [ ] **Step 1: Replace huge `whereIn` with `whereHas`**

```php
$query = UnitAvailability::query()
    ->select('id', 'unit_id', 'date', 'status', 'reservation_id')
    ->with([
        'unit' => fn ($q) => $q->select('id', 'name_or_number', 'building_id'),
        'reservation.customer' => fn ($q) => $q->select('id', 'name'),
    ]);

$user = auth()->user();

if ($user->isOwner()) {
    $query->whereHas('unit.building', fn ($q) => $q->where('owner_id', $user->owner->id));
} elseif ($user->isEmployee()) {
    $query->whereHas('unit.building', fn ($q) => $q->where('owner_id', $user->employee->owner->id));
} elseif ($user->isCustomer()) {
    $query->whereHas('reservation', fn ($q) => $q
        ->where('customer_type', Customer::class)
        ->where('customer_id', $user->customer?->id));
}
```

- [ ] **Step 2: Keep month/paginate branches unchanged except the base query**

- [ ] **Step 3: Verify syntax**

Run: `php -l app/Http/Controllers/UnitAvailabilityController.php`

---

## Task 9: Limit single-resource location loads

**Files:**
- Modify: `app/Http/Controllers/RegionController.php`
- Modify: `app/Http/Controllers/CountryController.php`
- Modify: `app/Http/Controllers/CityController.php`
- Modify: `app/Http/Controllers/CurrencyController.php`

- [ ] **Step 1: `RegionController::store/show/update`**

```php
$region->load([
    'city' => fn ($q) => $q->select('id', 'name'),
    'buildings' => fn ($q) => $q->select('id', 'region_id', 'name', 'status')->take(20),
]);
```

- [ ] **Step 2: `CountryController::store/show/update`**

```php
$country->load([
    'currency' => fn ($q) => $q->select('id', 'name', 'code', 'symbol'),
    'cities' => fn ($q) => $q->select('id', 'country_id', 'name')->take(50),
]);
```

- [ ] **Step 3: `CityController::store/show/update`**

```php
$city->load([
    'country' => fn ($q) => $q->select('id', 'name'),
    'regions' => fn ($q) => $q->select('id', 'city_id', 'name')->take(50),
]);
```

- [ ] **Step 4: `CurrencyController::index/show`**

```php
Currency::with(['countries' => fn ($q) => $q->select('id', 'currency_id', 'name')])->paginate(5)
```

```php
return CurrencyResource::make($currency->load(['countries' => fn ($q) => $q->select('id', 'currency_id', 'name')]));
```

- [ ] **Step 5: Verify syntax**

Run: `php -l` on all four files.

---

## Task 10: Fix missing eager loads in services

**Files:**
- Modify: `app/Services/PromoCodeService.php`
- Modify: `app/Services/ReservationReminderScheduler.php`
- Modify: `app/Services/ReservationService.php`
- Modify: `app/Services/DashboardMetrics.php`

- [ ] **Step 1: `PromoCodeService::findValidForUnit`**

Add eager load at the top:
```php
$unit->loadMissing(['building.owner']);
$ownerId = $unit->building->owner->id;
```

- [ ] **Step 2: `ReservationReminderScheduler::scheduleFor`**

```php
public function scheduleFor(Reservation $reservation): void
{
    $reservation->loadMissing(['unit.building', 'customer']);
    $this->cancelPendingFor($reservation);
    // ... rest unchanged
}
```

- [ ] **Step 3: `ReservationService::updateReservation` unit change block**

```php
if ($unitChanged) {
    $newUnit = Unit::with(['building' => fn ($q) => $q->select('id', 'owner_id')])
        ->select('id', 'building_id')
        ->findOrFail($newUnitId);
    $reservation->loadMissing(['unit.building' => fn ($q) => $q->select('id', 'owner_id')]);
    $originalOwnerId = $reservation->unit->building->owner_id;
    // ...
}
```

- [ ] **Step 4: `DashboardMetrics` memoize building ids**

Add a private property:
```php
private ?Collection $ownerBuildingIds = null;
```

Add helper:
```php
private function buildingIdsFor(Owner $owner): Collection
{
    return $this->ownerBuildingIds ??= $owner->buildings()->pluck('id');
}
```

Replace all `$owner->buildings()->pluck('id')` with `$this->buildingIdsFor($owner)`.

- [ ] **Step 5: Verify syntax**

Run: `php -l` on all four files.

---

## Task 11: Optimize console commands

**Files:**
- Modify: `app/Console/Commands/SendDueScheduledNotifications.php`
- Modify: `app/Console/Commands/DeleteUnverifiedUsers.php`

- [ ] **Step 1: `SendDueScheduledNotifications`**

```php
ScheduledNotification::due()
    ->with(['notifiable', 'reservation'])
    ->cursor()
    ->each(...)
```

- [ ] **Step 2: `DeleteUnverifiedUsers`**

```php
User::query()
    ->where('is_verified', false)
    ->where('created_at', '<=', $cutoff)
    ->chunkById(100, function ($users) use (&$deleted) {
        foreach ($users as $user) {
            DB::transaction(function () use ($user) {
                $user->roles()->detach();
                $user->permissions()->detach();
                $user->tokens()->delete();
                $user->forceDelete();
            });
            $deleted++;
        }
    });
```

- [ ] **Step 3: Verify syntax**

Run: `php -l` on both files.

---

## Task 12: Run test suite and fix regressions

**Files:**
- All modified files

- [ ] **Step 1: Run static analysis / syntax check on all changed files**

```bash
for f in app/Services/Filter/FilterService.php \
         app/Http/Controllers/UnitSearchController.php \
         app/Http/Controllers/UnitController.php \
         app/Http/Controllers/BuildingController.php \
         app/Http/Resources/BuildingResource.php \
         app/Http/Controllers/InvoiceController.php \
         app/Http/Controllers/ReceiptController.php \
         app/Http/Controllers/ReservationController.php \
         app/Http/Controllers/Api/Customer/CustomerController.php \
         app/Http/Controllers/UnitAvailabilityController.php \
         app/Http/Controllers/RegionController.php \
         app/Http/Controllers/CountryController.php \
         app/Http/Controllers/CityController.php \
         app/Http/Controllers/CurrencyController.php \
         app/Services/PromoCodeService.php \
         app/Services/ReservationReminderScheduler.php \
         app/Services/ReservationService.php \
         app/Services/DashboardMetrics.php \
         app/Console/Commands/SendDueScheduledNotifications.php \
         app/Console/Commands/DeleteUnverifiedUsers.php; do
  php -l "$f"
done
```

Expected: `No syntax errors detected` for every file.

- [ ] **Step 2: Run tests**

Run: `php artisan test`
Expected: All tests pass. Fix any failures.

- [ ] **Step 3: Clear caches**

Run: `php artisan route:clear && php artisan cache:clear && php artisan config:clear`

---

## Self-review checklist

- [ ] Every audit finding from the eager-loading report maps to at least one task.
- [ ] No placeholders remain in code snippets.
- [ ] Method and class names are consistent across tasks.
- [ ] `FilterService` is used by both `UnitSearchController` and `UnitController`.
- [ ] `BuildingResource` owner field now uses `OwnerResource`.

---

## Execution handoff

Plan complete and saved to `docs/superpowers/plans/2026-06-24-eager-loading-and-filter-service-plan.md`.

**Execution approach:** Subagent-Driven (recommended) — dispatch a fresh coder subagent per task, review between tasks, iterate fast.