<?php

namespace App\Services;

use App\Models\UserAccessLog;
use Illuminate\Pagination\LengthAwarePaginator;

class UserAccessLogService
{
    public function getAllPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $query = UserAccessLog::with(['user:id,name,type'])
            ->orderBy('accessed_at', 'desc');

        if (!empty($filters['last_per_user'])) {
            $query->whereIn('id', function ($sub) {
                $sub->selectRaw('MAX(id)')
                    ->from('users_access_logs')
                    ->groupBy('user_id');
            });
        }

        if (!empty($filters['type'])) {
            $query->whereHas('user', fn($q) => $q->where('type', $filters['type']));
        }

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term) {
                $q->whereHas('user', function ($u) use ($term) {
                    $u->whereRaw('UNACCENT(LOWER(users.name)) LIKE UNACCENT(?)', [$term])
                      ->orWhereRaw('UNACCENT(LOWER(COALESCE(users.cpf, \'\'))) LIKE UNACCENT(?)', [$term])
                      ->orWhereRaw('UNACCENT(LOWER(COALESCE(users.registration, \'\'))) LIKE UNACCENT(?)', [$term])
                      ->orWhereRaw('LOWER(users.type) LIKE ?', [$term]);
                })
                ->orWhereRaw("TO_CHAR(users_access_logs.accessed_at, 'DD/MM/YYYY HH24:MI:SS') LIKE ?", [$term]);
            });
        }

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

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

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