My-KOPKB/be/Modules/User/Repositories/UserRepository.php

<?php

namespace Modules\User\Repositories;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Carbon;
use Modules\Auth\Entities\User;
use Modules\User\Repositories\Contracts\UserRepositoryInterface;

class UserRepository implements UserRepositoryInterface
{
    private function applySearch($query, string $search): void
    {
        if (empty($search)) {
            return;
        }

        $query->where(function ($q) use ($search) {
            $q->where('name', 'ILIKE', "%{$search}%")
                ->orWhere('ic_number', 'ILIKE', "%{$search}%")
                ->orWhere('email', 'ILIKE', "%{$search}%")
                ->orWhere('phone_number', 'ILIKE', "%{$search}%")
                ->orWhere('member_number', 'ILIKE', "%{$search}%")
                ->orWhereHas('roles', function ($roleQuery) use ($search) {
                    $roleQuery->whereRaw('LOWER(name) ILIKE ?', ['%' . strtolower($search) . '%']);
                });
        });
    }

    private function applyDateRangeFilters($query, array $dateFilters): void
    {
        if (! empty($dateFilters['join_date_from'])) {
            $query->whereDate('join_date', '>=', $dateFilters['join_date_from']);
        }

        if (! empty($dateFilters['join_date_to'])) {
            $query->whereDate('join_date', '<=', $dateFilters['join_date_to']);
        }

        if (! empty($dateFilters['leave_date_from'])) {
            $query->whereDate('leave_date', '>=', $dateFilters['leave_date_from']);
        }

        if (! empty($dateFilters['leave_date_to'])) {
            $query->whereDate('leave_date', '<=', $dateFilters['leave_date_to']);
        }
    }

    private function applyCompanyNameFilter($query, string $companyName): void
    {
        if ($companyName === '') {
            return;
        }

        $query->whereHas('employments', function ($employmentQuery) use ($companyName) {
            $employmentQuery
                ->where('company_name', $companyName)
                ->whereRaw('employments.id = (
                    SELECT e2.id
                    FROM employments e2
                    WHERE e2.user_id = employments.user_id
                    ORDER BY e2.is_current DESC, e2.start_date DESC
                    LIMIT 1
                )');
        });
    }

    private function applyListFilters($query, array $filters): void
    {
        $this->applyDateRangeFilters($query, $filters);
        $this->applyCompanyNameFilter($query, $filters['company_name'] ?? '');
    }

    /**
     * Get all Users with pagination and search
     */
    public function getAllPaginated(int $perPage = 10, string $search = '')
    {
        $query = User::excludeDevelopersUnlessDeveloper()->orderBy('name');

        $this->applySearch($query, $search);

        return $query->paginate($perPage);
    }

    /**
     * Get all Users with their relationships and pagination with search
     */
    public function getAllWithRelationsPaginated(
        int $perPage = 10,
        string $search = '',
        string $status = '',
        string $sortBy = 'name',
        string $sortOrder = 'asc',
        array $dateFilters = []
    ) {
        $allowedSortColumns = [
            'id',
            'name',
            'email',
            'position',
            'status',
            'member_number',
            'join_date',
            'leave_date',
            'created_at',
            'deleted_at',
        ];
        $sortBy = in_array($sortBy, $allowedSortColumns, true) ? $sortBy : 'name';
        $sortOrder = strtolower($sortOrder) === 'desc' ? 'desc' : 'asc';

        $query = User::excludeDevelopersUnlessDeveloper()
            ->with([
                'roles:id,name,guard_name',
                'employments' => function ($q) {
                    $q->select([
                        'id',
                        'user_id',
                        'company_name',
                        'is_current',
                        'start_date',
                    ])
                        ->orderByDesc('is_current')
                        ->orderByDesc('start_date');
                },
            ])
            ->orderBy($sortBy, $sortOrder);

        $this->applySearch($query, $search);

        if (! empty($status)) {
            $query->where('status', $status);
        }

        $this->applyListFilters($query, $dateFilters);

        return $query->paginate($perPage);
    }

    /**
     * Get soft-deleted Users with their relationships and pagination
     */
    public function getDeletedWithRelationsPaginated(
        int $perPage = 10,
        string $search = '',
        string $status = '',
        string $sortBy = 'deleted_at',
        string $sortOrder = 'desc'
    ) {
        $allowedSortColumns = [
            'id',
            'name',
            'email',
            'position',
            'status',
            'member_number',
            'join_date',
            'leave_date',
            'created_at',
            'deleted_at',
        ];
        $sortBy = in_array($sortBy, $allowedSortColumns, true) ? $sortBy : 'deleted_at';
        $sortOrder = strtolower($sortOrder) === 'desc' ? 'desc' : 'asc';

        $query = User::onlyTrashed()
            ->excludeDevelopersUnlessDeveloper()
            ->with([
                'roles:id,name,guard_name',
                'employments' => function ($q) {
                    $q->select([
                        'id',
                        'user_id',
                        'company_name',
                        'is_current',
                        'start_date',
                    ])
                        ->orderByDesc('is_current')
                        ->orderByDesc('start_date');
                },
            ])
            ->orderBy($sortBy, $sortOrder);

        $this->applySearch($query, $search);

        if (! empty($status)) {
            $query->where('status', $status);
        }

        return $query->paginate($perPage);
    }

    public function getListStats(
        string $search = '',
        string $status = '',
        array $dateFilters = []
    ): array {
        $baseQuery = User::excludeDevelopersUnlessDeveloper();

        $this->applySearch($baseQuery, $search);

        if (! empty($status)) {
            $baseQuery->where('status', $status);
        }

        $this->applyListFilters($baseQuery, $dateFilters);

        $total = (int) (clone $baseQuery)->count();

        $monthStart = Carbon::now()->startOfMonth()->toDateString();
        $monthEnd = Carbon::now()->endOfMonth()->toDateString();
        $joinedThisMonth = (int) (clone $baseQuery)
            ->whereDate('join_date', '>=', $monthStart)
            ->whereDate('join_date', '<=', $monthEnd)
            ->count();

        return [
            'total' => $total,
            'joined_this_month' => $joinedThisMonth,
        ];
    }

    /**
     * Get all Users with their relationships and search
     */
    public function getAllWithRelations(string $search = ''): Collection
    {
        $query = User::excludeDevelopersUnlessDeveloper()->orderBy('name');

        $this->applySearch($query, $search);

        return $query->get();
    }

    /**
     * Create a new User
     */
    public function create(array $data): User
    {
        return User::create($data);
    }

    /**
     * Find User by ID
     */
    public function findById(string $id): ?User
    {
        return User::with(['roles'])->find($id);
    }

    /**
     * Delete User (soft delete)
     */
    public function delete(string $id): bool
    {
        $User = User::find($id);
        if ($User) {
            return $User->delete();
        }

        return false;
    }

    /**
     * Find soft-deleted User by ID
     */
    public function findTrashedById(string $id): ?User
    {
        return User::onlyTrashed()->with(['roles'])->find($id);
    }

    /**
     * Restore a soft-deleted User
     */
    public function restore(string $id): bool
    {
        $user = User::onlyTrashed()->find($id);

        if ($user) {
            return (bool) $user->restore();
        }

        return false;
    }

    /**
     * Get all Ranks
     */
    public function all(string $search = ''): Collection
    {
        $query = User::excludeDevelopersUnlessDeveloper()->orderBy('name');

        $this->applySearch($query, $search);

        return $query->get();
    }
}