# Filtering System

This guide explains the standard way to implement filtering, searching, and sorting in the application.

## Overview

The application provides multiple filtering approaches:
1. **Filterable Trait** - Automatic filtering via model configuration
2. **Filter Request Classes** - Validated filter parameters
3. **Service/Repository Pattern** - Complex filtering logic
4. **QueryBuilder Trait** - Generic query helpers

## Core Filterable Trait

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

The primary filtering mechanism that can be added to any model.

### Configuration Properties

```php
class YourModel extends BaseModel
{
    // Columns for exact-match filtering (single values)
    public static array $filtersCols = ['status', 'type', 'category_id'];

    // Columns for multi-value filtering (arrays)
    public static array $multiFiltersCols = ['id'];

    // Columns for text search
    public static array $searchCols = ['name', 'description', 'code'];

    // Enable date range filtering
    public static bool $searchDate = true;
}
```

### How It Works

The trait provides a `scopeFilter()` method that reads from the request and applies:

```php
public function scopeFilter($query)
{
    $this->searchWordsFilter($query);   // Text search across $searchCols
    $this->searchSelectFilter($query);  // Exact match on $filtersCols
    $this->searchDateFilter($query);    // Date range if $searchDate = true

    return $query;
}
```

### Basic Usage

```php
// In controller
public function index(): JsonResponse
{
    $items = YourModel::query()
        ->filter()  // Applies all filters from request
        ->with('relationships')
        ->latest()
        ->paginate(request('perPage', 15));

    return $this->sendPaginatedResponse(YourResource::collection($items));
}
```

### Request Parameters

| Parameter | Description | Example |
|-----------|-------------|---------|
| `search` | Text search across `$searchCols` | `?search=john` |
| `{column}` | Single value filter | `?status=active` |
| `{column}[]` | Multi-value filter | `?status[]=active&status[]=pending` |
| `date_from` | Start date (when `$searchDate = true`) | `?date_from=2024-01-01` |
| `date_to` | End date (when `$searchDate = true`) | `?date_to=2024-12-31` |

## Implementing Filtering

### Step 1: Configure Model

```php
<?php

namespace App\Domains\YourDomain\Models;

use App\Models\BaseModel;

class YourEntity extends BaseModel
{
    protected static bool $applyCompanyScope = true;

    // Define filterable columns
    public static array $filtersCols = [
        'company_id',
        'status',
        'category_id',
        'priority',
    ];

    // Define multi-select columns
    public static array $multiFiltersCols = ['id', 'assigned_to'];

    // Define searchable columns
    public static array $searchCols = [
        'name',
        'code',
        'description',
    ];

    // Enable date filtering
    public static bool $searchDate = true;

    protected $fillable = [
        'name', 'code', 'description', 'status',
        'category_id', 'priority', 'company_id',
    ];
}
```

### Step 2: Create Filter Request (Optional but Recommended)

**Location:** `app/Domains/YourDomain/Http/Requests/V1/FilterYourEntityRequest.php`

```php
<?php

namespace App\Domains\YourDomain\Http\Requests\V1;

use Illuminate\Foundation\Http\FormRequest;

class FilterYourEntityRequest extends FormRequest
{
    public function authorize(): bool
    {
        return true;
    }

    public function rules(): array
    {
        return [
            // Text search
            'search' => 'nullable|string|max:255',

            // Single value filters
            'status' => 'nullable|string|in:draft,active,completed,cancelled',
            'category_id' => 'nullable|integer|exists:categories,id',
            'priority' => 'nullable|string|in:low,medium,high,urgent',

            // Multi-value filters
            'assigned_to' => 'nullable|array',
            'assigned_to.*' => 'integer|exists:users,id',

            // Date range
            'date_from' => 'nullable|date|date_format:Y-m-d',
            'date_to' => 'nullable|date|date_format:Y-m-d|after_or_equal:date_from',

            // Sorting
            'sort_by' => 'nullable|string|in:name,created_at,status,priority',
            'sort_order' => 'nullable|string|in:asc,desc',

            // Pagination
            'per_page' => 'nullable|integer|min:1|max:100',
            'page' => 'nullable|integer|min:1',
        ];
    }
}
```

### Step 3: Implement in Controller

```php
<?php

namespace App\Domains\YourDomain\Http\Controllers\V1;

use App\Core\Http\Controllers\V1\ApiBaseController;
use App\Domains\YourDomain\Http\Requests\V1\FilterYourEntityRequest;
use App\Domains\YourDomain\Http\Resources\YourEntityResource;
use App\Domains\YourDomain\Models\YourEntity;

class YourEntityController extends ApiBaseController
{
    public function index(FilterYourEntityRequest $request): JsonResponse
    {
        $query = YourEntity::query()
            ->filter()  // Apply Filterable trait filters
            ->with(['category', 'assignedTo']);

        // Additional custom filters
        if ($request->filled('priority')) {
            $query->where('priority', $request->priority);
        }

        // Sorting
        $sortBy = $request->input('sort_by', 'created_at');
        $sortOrder = $request->input('sort_order', 'desc');
        $query->orderBy($sortBy, $sortOrder);

        // Pagination
        $perPage = $request->input('per_page', 15);
        $items = $query->paginate($perPage);

        return $this->sendPaginatedResponse(YourEntityResource::collection($items));
    }
}
```

## Advanced Filtering Patterns

### Pattern 1: Service-Based Filtering

For complex filtering logic, use a service class:

```php
<?php

namespace App\Domains\YourDomain\Services;

use App\Domains\YourDomain\Models\YourEntity;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;

class YourEntityService
{
    public function getFiltered(array $filters = []): LengthAwarePaginator
    {
        $query = YourEntity::query()
            ->with(['category', 'assignedTo']);

        // Apply filters conditionally
        $query = $this->applyFilters($query, $filters);

        // Apply sorting
        $sortBy = $filters['sort_by'] ?? 'created_at';
        $sortOrder = $filters['sort_order'] ?? 'desc';
        $query->orderBy($sortBy, $sortOrder);

        return $query->paginate($filters['per_page'] ?? 15);
    }

    private function applyFilters($query, array $filters)
    {
        // Text search
        if (!empty($filters['search'])) {
            $search = $filters['search'];
            $query->where(function ($q) use ($search) {
                $q->where('name', 'like', "%{$search}%")
                  ->orWhere('code', 'like', "%{$search}%")
                  ->orWhere('description', 'like', "%{$search}%");
            });
        }

        // Status filter (single or array)
        if (!empty($filters['status'])) {
            if (is_array($filters['status'])) {
                $query->whereIn('status', $filters['status']);
            } else {
                $query->where('status', $filters['status']);
            }
        }

        // Category filter
        if (!empty($filters['category_id'])) {
            $query->where('category_id', $filters['category_id']);
        }

        // Date range
        if (!empty($filters['date_from'])) {
            $query->whereDate('created_at', '>=', $filters['date_from']);
        }

        if (!empty($filters['date_to'])) {
            $query->whereDate('created_at', '<=', $filters['date_to']);
        }

        // Relationship filtering
        if (!empty($filters['assigned_to'])) {
            $query->whereIn('assigned_to', (array) $filters['assigned_to']);
        }

        return $query;
    }
}
```

### Pattern 2: Repository Pattern with Individual Filters

```php
<?php

namespace App\Domains\YourDomain\Repositories;

use App\Domains\YourDomain\Models\YourEntity;

class YourEntityRepository
{
    public function __construct(
        private YourEntity $model
    ) {}

    public function getFiltered(array $filters = [])
    {
        $query = $this->model->query();

        // Chain individual filter methods
        if (!empty($filters['status'])) {
            $query = $this->filterByStatus($query, $filters['status']);
        }

        if (!empty($filters['category_id'])) {
            $query = $this->filterByCategory($query, $filters['category_id']);
        }

        if (!empty($filters['date_from']) || !empty($filters['date_to'])) {
            $query = $this->filterByDateRange(
                $query,
                $filters['date_from'] ?? null,
                $filters['date_to'] ?? null
            );
        }

        if (!empty($filters['search'])) {
            $query = $this->searchKeyword($query, $filters['search']);
        }

        return $this->applySortingAndPagination($query, $filters);
    }

    public function filterByStatus($query, $status)
    {
        return $query->where('status', $status);
    }

    public function filterByCategory($query, $categoryId)
    {
        return $query->where('category_id', $categoryId);
    }

    public function filterByDateRange($query, $startDate, $endDate)
    {
        if ($startDate) {
            $query->whereDate('created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('created_at', '<=', $endDate);
        }
        return $query;
    }

    public function searchKeyword($query, $keyword)
    {
        return $query->where(function ($q) use ($keyword) {
            $q->where('name', 'like', "%{$keyword}%")
              ->orWhere('description', 'like', "%{$keyword}%");
        });
    }

    private function applySortingAndPagination($query, array $filters)
    {
        $sortBy = $filters['sort_by'] ?? 'created_at';
        $sortOrder = $filters['sort_order'] ?? 'desc';

        return $query
            ->orderBy($sortBy, $sortOrder)
            ->paginate($filters['per_page'] ?? 15);
    }
}
```

### Pattern 3: Relationship Filtering

```php
// Filter by related model attributes
$query->whereHas('category', function ($q) use ($filters) {
    $q->where('type', $filters['category_type']);
});

// Filter with nested relationships
$query->whereHas('project.client', function ($q) use ($filters) {
    $q->where('id', $filters['client_id']);
});

// Count-based filtering
$query->has('comments', '>=', 5);  // At least 5 comments
```

### Pattern 4: JSON Column Filtering

```php
// Filter on JSON column
$query->whereJsonContains('metadata->tags', 'urgent');

// Filter on nested JSON
$query->where('settings->notifications->email', true);
```

## QueryBuilder Trait

**Location:** `app/Traits/QueryBuilder.php`

Generic helpers for query building:

```php
trait QueryBuilder
{
    public function search(Builder $query, ?string $term, array $columns = []): Builder
    {
        if (empty($term)) {
            return $query;
        }

        return $query->where(function ($q) use ($term, $columns) {
            foreach ($columns as $column) {
                $q->orWhere($column, 'like', "%{$term}%");
            }
        });
    }

    public function filter(Builder $query, array $filters, array $columns = []): Builder
    {
        foreach ($filters as $key => $value) {
            if (in_array($key, $columns) && !is_null($value)) {
                if (is_array($value)) {
                    $query->whereIn($key, $value);
                } else {
                    $query->where($key, $value);
                }
            }
        }
        return $query;
    }

    public function sort(Builder $query, ?string $column, string $direction = 'asc'): Builder
    {
        if ($column) {
            $direction = in_array(strtolower($direction), ['asc', 'desc']) ? $direction : 'asc';
            $query->orderBy($column, $direction);
        }
        return $query;
    }
}
```

## Common Filter Types

### Status Enum Filters

```php
// In Request
'status' => 'nullable|string|in:' . implode(',', StatusEnum::values()),

// In Service
if (!empty($filters['status'])) {
    $query->where('status', StatusEnum::from($filters['status']));
}
```

### Boolean Filters

```php
// In Request
'is_active' => 'nullable|boolean',

// In Service
if (isset($filters['is_active'])) {
    $query->where('is_active', filter_var($filters['is_active'], FILTER_VALIDATE_BOOLEAN));
}
```

### Range Filters

```php
// In Request
'min_amount' => 'nullable|numeric|min:0',
'max_amount' => 'nullable|numeric|min:0|gte:min_amount',

// In Service
if (!empty($filters['min_amount'])) {
    $query->where('amount', '>=', $filters['min_amount']);
}
if (!empty($filters['max_amount'])) {
    $query->where('amount', '<=', $filters['max_amount']);
}
```

### Full-Text Search

```php
// For more advanced search
$query->whereFullText(['name', 'description'], $filters['search']);
```

## API Response Format

Standard paginated response structure:

```json
{
    "success": true,
    "data": [...],
    "meta": {
        "current_page": 1,
        "from": 1,
        "last_page": 10,
        "per_page": 15,
        "to": 15,
        "total": 150
    },
    "links": {
        "first": "...",
        "last": "...",
        "prev": null,
        "next": "..."
    }
}
```

## Best Practices

### DO:

1. **Always validate filter inputs** using FormRequest classes
2. **Define filterable columns explicitly** in the model
3. **Use conditional filter application** - only filter if parameter provided
4. **Optimize with eager loading** for relationship filters
5. **Index filtered columns** in database migrations
6. **Use enums for status filters** for type safety

### DON'T:

1. **Don't allow filtering on sensitive columns** without authorization
2. **Don't skip validation** - it prevents SQL injection
3. **Don't use raw queries** for user input
4. **Don't forget pagination** - always limit results
5. **Don't allow arbitrary column sorting** - whitelist allowed columns

## Performance Tips

1. **Add database indexes** on frequently filtered columns
2. **Use select()** to limit returned columns
3. **Eager load relationships** to avoid N+1 queries
4. **Cache frequently used filter results**
5. **Use cursor pagination** for large datasets