Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
53.91%
324 / 601
42.22%
19 / 45
 CRAP
0.00%
0 / 1
Useraccount
53.91%
324 / 601
42.22%
19 / 45
 5455.44
0.00%
0 / 1
 getUseraccountCacheVersion
85.71%
6 / 7
0.00%
0 / 1
 4.05
 registerCacheKeyForDepartments
92.31%
12 / 13
0.00%
0 / 1
 7.02
 deleteCacheKey
83.33%
5 / 6
0.00%
0 / 1
 3.04
 invalidateDepartmentCaches
89.47%
17 / 19
0.00%
0 / 1
 9.09
 getCacheIndexKey
100.00%
1 / 1
100.00%
1 / 1
 1
 extractDepartmentIdsFromEntity
66.67%
6 / 9
0.00%
0 / 1
 10.37
 readDepartmentIdsForLoginName
88.89%
8 / 9
0.00%
0 / 1
 4.02
 collectDepartmentIdsForInvalidation
100.00%
11 / 11
100.00%
1 / 1
 2
 collectUseraccountIdentifiers
87.50%
7 / 8
0.00%
0 / 1
 5.05
 applyWorkstationAccessFilter
16.67%
2 / 12
0.00%
0 / 1
 13.26
 sanitizeCacheKey
100.00%
1 / 1
100.00%
1 / 1
 1
 readIsUserExisting
100.00%
8 / 8
100.00%
1 / 1
 3
 readEntity
56.67%
17 / 30
0.00%
0 / 1
 18.14
 readResolvedReferences
100.00%
3 / 3
100.00%
1 / 1
 3
 readList
65.12%
28 / 43
0.00%
0 / 1
 29.27
 readListStatement
0.00%
0 / 11
0.00%
0 / 1
 42
 readAssignedDepartmentList
100.00%
6 / 6
100.00%
1 / 1
 2
 readAssignedDepartmentListsForAll
88.89%
8 / 9
0.00%
0 / 1
 4.02
 separateSuperusersFromRegularUsers
100.00%
7 / 7
100.00%
1 / 1
 3
 loadSuperuserDepartments
100.00%
7 / 7
100.00%
1 / 1
 2
 loadRegularUserDepartments
100.00%
5 / 5
100.00%
1 / 1
 1
 groupAssignmentsByUser
100.00%
7 / 7
100.00%
1 / 1
 3
 buildDepartmentListsForUsers
100.00%
23 / 23
100.00%
1 / 1
 10
 buildDepartmentList
90.91%
10 / 11
0.00%
0 / 1
 5.02
 readEntityByAuthKey
100.00%
7 / 7
100.00%
1 / 1
 2
 readEntityByUserId
100.00%
6 / 6
100.00%
1 / 1
 2
 readCollectionByDepartmentIds
56.52%
26 / 46
0.00%
0 / 1
 44.63
 writeEntity
100.00%
10 / 10
100.00%
1 / 1
 2
 assignTemporaryRoleForNewUser
47.37%
9 / 19
0.00%
0 / 1
 6.33
 readRoleIdByName
85.71%
6 / 7
0.00%
0 / 1
 3.03
 writeUpdatedEntity
100.00%
9 / 9
100.00%
1 / 1
 1
 deleteEntity
100.00%
9 / 9
100.00%
1 / 1
 3
 updateAssignedDepartments
100.00%
12 / 12
100.00%
1 / 1
 3
 readEntityIdByLoginName
100.00%
3 / 3
100.00%
1 / 1
 1
 deleteAssignedDepartments
100.00%
3 / 3
100.00%
1 / 1
 1
 buildSearchCacheKey
0.00%
0 / 6
0.00%
0 / 1
 20
 getCachedResult
0.00%
0 / 8
0.00%
0 / 1
 42
 setCachedResult
0.00%
0 / 7
0.00%
0 / 1
 12
 executeSearchQuery
0.00%
0 / 23
0.00%
0 / 1
 90
 executeSearchByDepartmentIdsQuery
0.00%
0 / 23
0.00%
0 / 1
 110
 readSearch
0.00%
0 / 13
0.00%
0 / 1
 12
 readSearchByDepartmentIds
0.00%
0 / 16
0.00%
0 / 1
 12
 readListRole
0.00%
0 / 37
0.00%
0 / 1
 156
 readListByRoleAndDepartmentIds
0.00%
0 / 41
0.00%
0 / 1
 240
 removeCache
63.33%
19 / 30
0.00%
0 / 1
 12.99
1<?php
2
3namespace BO\Zmsdb;
4
5use BO\Zmsdb\Application as App;
6use BO\Zmsentities\Useraccount as Entity;
7use BO\Zmsentities\Collection\UseraccountList as Collection;
8
9/**
10 * @SuppressWarnings(Public)
11 * @SuppressWarnings(TooManyMethods)
12 * @SuppressWarnings(ExcessiveClassComplexity)
13 *
14 */
15class Useraccount extends Base
16{
17    private const CACHE_VERSION_KEY = 'useraccountCacheVersion';
18    private const CACHE_INDEX_PREFIX = 'useraccountCacheIndex-';
19    private const CACHE_INDEX_GLOBAL = 'all';
20    private const TEMP_ROLE_MAP_BY_BERECHTIGUNG = [
21        90 => 'system_admin',
22        40 => 'user_admin',
23        30 => 'appointment_admin',
24        5  => 'audit_viewer',
25        0  => 'agent_queue',
26    ];
27
28    /**
29     * Read or initialize the cache version for all useraccount-related cache entries.
30     */
31    protected function getUseraccountCacheVersion(): int
32    {
33        if (!App::$cache) {
34            return 1;
35        }
36
37        $version = App::$cache->get(self::CACHE_VERSION_KEY);
38        if (!is_int($version) || $version < 1) {
39            $version = 1;
40            App::$cache->set(self::CACHE_VERSION_KEY, $version);
41        }
42
43        return $version;
44    }
45
46    /**
47     * Register a cache key for one or more department IDs so we can invalidate
48     * only the affected department-based caches later on.
49     *
50     * Note: This method uses a non-atomic read-modify-write pattern which may
51     * experience race conditions under high concurrency. Lost index entries are
52     * handled by the version-bump fallback in removeCache(), ensuring eventual
53     * consistency at the cost of a full cache invalidation.
54     *
55     * @see https://github.com/it-at-m/eappointment/issues/1804
56     *      Migration to Redis for atomic operations is tracked in issue #1804.
57     *
58     * @param array $departmentIds Department IDs to associate with the cache key
59     * @param string $cacheKey The cache key to register
60     */
61    protected function registerCacheKeyForDepartments(array $departmentIds, string $cacheKey): void
62    {
63        if (!App::$cache || empty($departmentIds)) {
64            return;
65        }
66
67        $departmentIds = array_values(array_unique(array_filter($departmentIds, function ($id) {
68            return $id !== null && $id !== '';
69        })));
70
71        foreach ($departmentIds as $departmentId) {
72            $indexKey = $this->getCacheIndexKey($departmentId);
73            $existing = App::$cache->get($indexKey);
74            if (!is_array($existing)) {
75                $existing = [];
76            }
77            if (!in_array($cacheKey, $existing, true)) {
78                $existing[] = $cacheKey;
79                App::$cache->set($indexKey, $existing);
80            }
81        }
82    }
83
84    protected function deleteCacheKey(string $cacheKey): bool
85    {
86        if (!App::$cache) {
87            return false;
88        }
89
90        if (App::$cache->has($cacheKey)) {
91            App::$cache->delete($cacheKey);
92            return true;
93        }
94
95        return false;
96    }
97
98    protected function invalidateDepartmentCaches(array $departmentIds): bool
99    {
100        if (!App::$cache) {
101            return false;
102        }
103
104        $departmentIds = array_values(array_unique(array_filter($departmentIds, function ($id) {
105            return $id !== null && $id !== '';
106        })));
107
108        $foundAny = false;
109
110        foreach ($departmentIds as $departmentId) {
111            $indexKey = $this->getCacheIndexKey($departmentId);
112            $indexExists = App::$cache->has($indexKey);
113            $cacheKeys = $indexExists ? App::$cache->get($indexKey) : [];
114            if (!is_array($cacheKeys)) {
115                $cacheKeys = [];
116            }
117
118            if ($indexExists) {
119                $foundAny = true;
120            }
121
122            foreach ($cacheKeys as $cacheKey) {
123                if ($this->deleteCacheKey($cacheKey)) {
124                    $foundAny = true;
125                }
126            }
127
128            App::$cache->delete($indexKey);
129        }
130
131        return $foundAny;
132    }
133
134    protected function getCacheIndexKey($departmentId): string
135    {
136        return self::CACHE_INDEX_PREFIX . $departmentId;
137    }
138
139    protected function extractDepartmentIdsFromEntity($useraccount): array
140    {
141        if (!isset($useraccount->departments) || empty($useraccount->departments)) {
142            return [];
143        }
144
145        $ids = [];
146        foreach ($useraccount->departments as $department) {
147            if (is_object($department) && isset($department->id)) {
148                $ids[] = $department->id;
149            } elseif (is_array($department) && isset($department['id'])) {
150                $ids[] = $department['id'];
151            }
152        }
153
154        return array_values(array_unique(array_filter($ids)));
155    }
156
157    protected function readDepartmentIdsForLoginName($loginName): array
158    {
159        if (!$loginName) {
160            return [];
161        }
162
163        $query = Query\Useraccount::QUERY_READ_ASSIGNED_DEPARTMENTS;
164        $departmentData = $this->getReader()->fetchAll($query, ['useraccountName' => $loginName]);
165
166        $ids = [];
167        foreach ($departmentData as $row) {
168            if (isset($row['id'])) {
169                $ids[] = $row['id'];
170            }
171        }
172
173        return array_values(array_unique($ids));
174    }
175
176    protected function collectDepartmentIdsForInvalidation($useraccount, array $previousDepartmentIds = []): array
177    {
178        $currentFromEntity = $this->extractDepartmentIdsFromEntity($useraccount);
179        $currentFromDatabase = $this->readDepartmentIdsForLoginName($useraccount->id ?? null);
180
181        $departmentIds = array_merge(
182            $previousDepartmentIds,
183            $currentFromEntity,
184            $currentFromDatabase
185        );
186
187        $departmentIds[] = self::CACHE_INDEX_GLOBAL;
188
189        return array_values(array_unique(array_filter($departmentIds, function ($id) {
190            return $id !== null && $id !== '';
191        })));
192    }
193
194    protected function collectUseraccountIdentifiers($useraccount): array
195    {
196        $identifiers = [];
197
198        if (isset($useraccount->id)) {
199            $identifiers[] = $useraccount->id;
200        }
201
202        if (isset($useraccount->loginname) && $useraccount->loginname !== $useraccount->id) {
203            $identifiers[] = $useraccount->loginname;
204        }
205
206        return array_values(array_unique(array_filter($identifiers, function ($identifier) {
207            return $identifier !== null && $identifier !== '';
208        })));
209    }
210
211    protected function applyWorkstationAccessFilter(Query\Useraccount $query, $workstation): bool
212    {
213        if (!$workstation || $workstation->getUseraccount()->isSuperUser()) {
214            return true; // No filtering needed for superusers
215        }
216
217        $workstationUserId = $this->readEntityIdByLoginName($workstation->getUseraccount()->id);
218        $workstationDepartmentIds = $workstation->getDepartmentList()->getIds();
219
220        // If no departments loaded, return empty result for security
221        if (empty($workstationDepartmentIds)) {
222            return false; // Signal to return empty collection
223        }
224
225        $query->addConditionWorkstationAccess(
226            $workstationUserId,
227            $workstationDepartmentIds,
228            false // We already checked isSuperUser above
229        );
230
231        return true;
232    }
233    /**
234     * Sanitize cache key by replacing reserved characters
235     * Reserved characters: {}()/\@:
236     */
237    protected function sanitizeCacheKey($key)
238    {
239        return str_replace(['{', '}', '(', ')', '/', '\\', '@', ':'], '_', $key);
240    }
241
242    public function readIsUserExisting($loginName, $password = false)
243    {
244        $query = new Query\Useraccount(Query\Base::SELECT);
245        $query->addEntityMapping()
246            ->setResolveLevel(0)
247            ->addConditionLoginName($loginName);
248        if ($password) {
249            $query->addConditionPassword($password);
250        }
251        $useraccount = $this->fetchOne($query, new Entity());
252        return ($useraccount->hasId()) ? true : false;
253    }
254
255    public function readEntity($loginname, $resolveReferences = 1, $disableCache = false)
256    {
257        $version = $this->getUseraccountCacheVersion();
258        $cacheKey = $this->sanitizeCacheKey("useraccount-v{$version}-$loginname-$resolveReferences");
259        $useraccount = null;
260
261        if (!$disableCache && App::$cache && App::$cache->has($cacheKey)) {
262            $useraccount = App::$cache->get($cacheKey);
263            if ($useraccount && App::$log) {
264                App::$log->info('Useraccount cache hit', [
265                    'cache_key' => $cacheKey,
266                    'loginname' => $loginname,
267                    'resolveReferences' => $resolveReferences,
268                ]);
269            }
270        }
271
272        if (empty($useraccount)) {
273            $query = new Query\Useraccount(Query\Base::SELECT);
274            $query->addEntityMapping()
275            ->addResolvedReferences($resolveReferences)
276            ->addConditionLoginName($loginname);
277            $useraccount = $this->fetchOne($query, new Entity());
278            if (!$useraccount->hasId()) {
279                return null;
280            }
281
282            $useraccount = $this->readResolvedReferences($useraccount, $resolveReferences);
283
284            if (App::$cache) {
285                App::$cache->set($cacheKey, $useraccount);
286                if (App::$log) {
287                    App::$log->info('Useraccount cache set', [
288                        'cache_key' => $cacheKey,
289                        'loginname' => $loginname,
290                        'resolveReferences' => $resolveReferences,
291                        'useraccount_id' => $useraccount->id ?? null
292                    ]);
293                }
294            }
295        }
296
297        return $useraccount;
298    }
299
300    public function readResolvedReferences(\BO\Zmsentities\Schema\Entity $useraccount, $resolveReferences)
301    {
302        if (0 < $resolveReferences && $useraccount->toProperty()->id->get()) {
303            $useraccount->departments = $this->readAssignedDepartmentList($useraccount, $resolveReferences);
304        }
305        return $useraccount;
306    }
307
308    /**
309     * @SuppressWarnings(NPathComplexity)
310     */
311    public function readList($resolveReferences = 0, $disableCache = false, $workstation = null)
312    {
313        $version = $this->getUseraccountCacheVersion();
314        $workstationKey = '';
315        if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
316            $workstationKey = '-workstation-' . $workstation->getUseraccount()->id;
317        }
318        $cacheKey = "useraccountReadList-v{$version}-$resolveReferences$workstationKey";
319        $result = null;
320
321        if (!$disableCache && App::$cache && App::$cache->has($cacheKey)) {
322            $result = App::$cache->get($cacheKey);
323            if ($result && App::$log) {
324                App::$log->info('Useraccount list cache hit', [
325                    'cache_key' => $cacheKey,
326                    'resolveReferences' => $resolveReferences,
327                    'count' => $result->count()
328                ]);
329            }
330        }
331
332        if (empty($result)) {
333            $collection = new Collection();
334            $query = new Query\Useraccount(Query\Base::SELECT);
335            $query->addResolvedReferences($resolveReferences)
336            ->addEntityMapping()
337            ->addOrderByName();
338
339            // Apply workstation access filtering if provided
340            if (!$this->applyWorkstationAccessFilter($query, $workstation)) {
341                $result = new Collection();
342                return $result;
343            }
344
345            $result = $this->fetchList($query, new Entity());
346            if (count($result)) {
347                foreach ($result as $entity) {
348                    $collection->addEntity($entity);
349                }
350                if (0 < $resolveReferences) {
351                    $departmentMap = $this->readAssignedDepartmentListsForAll($collection, $resolveReferences - 1);
352                    foreach ($collection as $entity) {
353                        if (isset($departmentMap[$entity->id])) {
354                            $entity->departments = $departmentMap[$entity->id];
355                        }
356                    }
357                }
358            }
359            $result = $collection;
360
361            if (App::$cache) {
362                App::$cache->set($cacheKey, $result);
363                $this->registerCacheKeyForDepartments([self::CACHE_INDEX_GLOBAL], $cacheKey);
364                if (App::$log) {
365                    App::$log->info('Useraccount list cache set', [
366                        'cache_key' => $cacheKey,
367                        'resolveReferences' => $resolveReferences,
368                        'count' => $result->count()
369                    ]);
370                }
371            }
372        }
373
374        return $result;
375    }
376
377    protected function readListStatement($statement, $resolveReferences)
378    {
379        $query = new Query\Useraccount(Query\Base::SELECT);
380        $collection = new Collection();
381        while ($userAccountData = $statement->fetch(\PDO::FETCH_ASSOC)) {
382            $entity = new Entity($query->postProcessJoins($userAccountData));
383            $collection->addEntity($entity);
384        }
385        if (0 < $resolveReferences && count($collection) > 0) {
386            $departmentMap = $this->readAssignedDepartmentListsForAll($collection, $resolveReferences - 1);
387            foreach ($collection as $entity) {
388                if (isset($departmentMap[$entity->id])) {
389                    $entity->departments = $departmentMap[$entity->id];
390                }
391            }
392        }
393        return $collection;
394    }
395
396    public function readAssignedDepartmentList($useraccount, $resolveReferences = 0)
397    {
398        if ($useraccount->isSuperUser()) {
399            $query = Query\Useraccount::QUERY_READ_SUPERUSER_DEPARTMENTS;
400            $departmentData = $this->getReader()->fetchAll($query);
401        } else {
402            $query = Query\Useraccount::QUERY_READ_ASSIGNED_DEPARTMENTS;
403            $departmentData = $this->getReader()->fetchAll($query, ['useraccountName' => $useraccount->id]);
404        }
405        return $this->buildDepartmentList($departmentData, $resolveReferences);
406    }
407
408    protected function readAssignedDepartmentListsForAll(Collection $useraccounts, $resolveReferences = 0)
409    {
410        if (count($useraccounts) === 0) {
411            return [];
412        }
413
414        list($superusers, $regularUsers) = $this->separateSuperusersFromRegularUsers($useraccounts);
415        $result = [];
416
417        if (count($superusers) > 0) {
418            $result = array_merge($result, $this->loadSuperuserDepartments($superusers, $resolveReferences));
419        }
420
421        if (count($regularUsers) > 0) {
422            $result = array_merge($result, $this->loadRegularUserDepartments($regularUsers, $resolveReferences));
423        }
424
425        return $result;
426    }
427
428    protected function separateSuperusersFromRegularUsers(Collection $useraccounts)
429    {
430        $superusers = [];
431        $regularUsers = [];
432        foreach ($useraccounts as $useraccount) {
433            if ($useraccount->isSuperUser()) {
434                $superusers[] = $useraccount->id;
435            } else {
436                $regularUsers[] = $useraccount->id;
437            }
438        }
439        return [$superusers, $regularUsers];
440    }
441
442    protected function loadSuperuserDepartments(array $superusers, $resolveReferences = 0)
443    {
444        // Load all departments once - all superusers have access to all departments
445            $query = Query\Useraccount::QUERY_READ_SUPERUSER_DEPARTMENTS;
446            $departmentIds = $this->getReader()->fetchAll($query);
447        $departmentList = $this->buildDepartmentList($departmentIds, $resolveReferences);
448
449        // Reuse the same list for all superusers - no need to clone since they all get the same departments
450        $result = [];
451        foreach ($superusers as $useraccountName) {
452            $result[$useraccountName] = $departmentList;
453        }
454        return $result;
455    }
456
457    protected function loadRegularUserDepartments(array $regularUsers, $resolveReferences = 0)
458    {
459        $placeholders = str_repeat('?,', count($regularUsers) - 1) . '?';
460        $query = str_replace(':useraccountNames', $placeholders, Query\Useraccount::QUERY_READ_ASSIGNED_DEPARTMENTS_FOR_ALL);
461        $allAssignments = $this->getReader()->fetchAll($query, $regularUsers);
462
463        $assignmentsByUser = $this->groupAssignmentsByUser($allAssignments);
464        return $this->buildDepartmentListsForUsers($regularUsers, $assignmentsByUser, $resolveReferences);
465    }
466
467    protected function groupAssignmentsByUser(array $allAssignments)
468    {
469        $assignmentsByUser = [];
470        foreach ($allAssignments as $assignment) {
471            $useraccountName = $assignment['useraccountName'];
472            if (!isset($assignmentsByUser[$useraccountName])) {
473                $assignmentsByUser[$useraccountName] = [];
474            }
475            $assignmentsByUser[$useraccountName][] = $assignment;
476        }
477        return $assignmentsByUser;
478    }
479
480    protected function buildDepartmentListsForUsers(array $useraccountNames, array $assignmentsByUser, $resolveReferences = 0)
481    {
482        // Collect ALL unique department IDs from all useraccounts
483        $allDepartmentIds = [];
484        $departmentIdsByUser = [];
485
486        foreach ($useraccountNames as $useraccountName) {
487            $departmentIdsByUser[$useraccountName] = [];
488            if (isset($assignmentsByUser[$useraccountName])) {
489                foreach ($assignmentsByUser[$useraccountName] as $item) {
490                    $departmentId = $item['id'];
491                    if (!isset($allDepartmentIds[$departmentId])) {
492                        $allDepartmentIds[$departmentId] = true;
493                    }
494                    $departmentIdsByUser[$useraccountName][] = $departmentId;
495                }
496            }
497        }
498
499        // Load ALL departments in ONE query
500        $allDepartments = [];
501        if (!empty($allDepartmentIds)) {
502            $uniqueDepartmentIds = array_keys($allDepartmentIds);
503            $allDepartments = (new \BO\Zmsdb\Department())->readEntitiesByIds($uniqueDepartmentIds, $resolveReferences);
504        }
505
506        // Build department lists for each useraccount from the pre-loaded departments
507        $result = [];
508        foreach ($useraccountNames as $useraccountName) {
509            $departmentList = new \BO\Zmsentities\Collection\DepartmentList();
510            if (isset($departmentIdsByUser[$useraccountName])) {
511                foreach ($departmentIdsByUser[$useraccountName] as $departmentId) {
512                    if (isset($allDepartments[$departmentId])) {
513                        // Clone department so each useraccount gets its own instance
514                        $departmentList->addEntity(clone $allDepartments[$departmentId]);
515                    }
516                }
517            }
518            $result[$useraccountName] = $departmentList;
519        }
520
521        return $result;
522    }
523
524    protected function buildDepartmentList(array $items, $resolveReferences = 0)
525    {
526        $departmentList = new \BO\Zmsentities\Collection\DepartmentList();
527
528        if (empty($items)) {
529            return $departmentList;
530        }
531
532        $departmentIds = [];
533        foreach ($items as $item) {
534            $departmentIds[] = $item['id'];
535        }
536
537        $departments = (new \BO\Zmsdb\Department())->readEntitiesByIds($departmentIds, $resolveReferences);
538
539        foreach ($departmentIds as $id) {
540            if (isset($departments[$id])) {
541                // Clone department so the list has its own instances
542                $departmentList->addEntity(clone $departments[$id]);
543            }
544        }
545
546        return $departmentList;
547    }
548
549    public function readEntityByAuthKey($xAuthKey, $resolveReferences = 0)
550    {
551        $hashedAuthKey = hash('sha256', $xAuthKey);
552        $query = new Query\Useraccount(Query\Base::SELECT);
553        $query->addEntityMapping()
554            ->addResolvedReferences($resolveReferences)
555            ->addConditionXauthKey($hashedAuthKey);
556        $entity = ($hashedAuthKey) ? $this->fetchOne($query, new Entity()) : new Entity();
557        return $this->readResolvedReferences($entity, $resolveReferences);
558    }
559
560    public function readEntityByUserId($userId, $resolveReferences = 0)
561    {
562        $query = new Query\Useraccount(Query\Base::SELECT);
563        $query->addEntityMapping()
564            ->addResolvedReferences($resolveReferences)
565            ->addConditionUserId($userId);
566        $entity = ($userId) ? $this->fetchOne($query, new Entity()) : new Entity();
567        return $this->readResolvedReferences($entity, $resolveReferences);
568    }
569
570    /**
571     * @SuppressWarnings(NPathComplexity)
572     */
573    public function readCollectionByDepartmentIds($departmentIds, $resolveReferences = 0, $disableCache = false, $workstation = null)
574    {
575        sort($departmentIds);
576        $version = $this->getUseraccountCacheVersion();
577        $workstationKey = '';
578        if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
579            $workstationKey = '-workstation-' . $workstation->getUseraccount()->id;
580        }
581        $cacheKey = "useraccountReadByDepartmentIds-v{$version}-" . implode(',', $departmentIds) . "-$resolveReferences$workstationKey";
582        $result = null;
583
584        if (!$disableCache && App::$cache && App::$cache->has($cacheKey)) {
585            $result = App::$cache->get($cacheKey);
586            if ($result && App::$log) {
587                App::$log->info('Useraccount department list cache hit', [
588                    'cache_key' => $cacheKey,
589                    'department_ids' => $departmentIds,
590                    'resolveReferences' => $resolveReferences,
591                    'count' => $result->count()
592                ]);
593            }
594        }
595
596        if (empty($result)) {
597            $collection = new Collection();
598            $query = new Query\Useraccount(Query\Base::SELECT);
599            $query->addResolvedReferences($resolveReferences)
600            ->addConditionDepartmentIds($departmentIds)
601            ->addEntityMapping()
602            ->addOrderByName();
603
604            // Exclude superusers if workstation user is not superuser
605            if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
606                $query->addConditionExcludeSuperusers();
607            }
608
609            $result = $this->fetchList($query, new Entity());
610            if (count($result)) {
611                foreach ($result as $entity) {
612                    $collection->addEntity($entity);
613                }
614                if (0 < $resolveReferences) {
615                    $departmentMap = $this->readAssignedDepartmentListsForAll($collection, $resolveReferences - 1);
616                    foreach ($collection as $entity) {
617                        if (isset($departmentMap[$entity->id])) {
618                            $entity->departments = $departmentMap[$entity->id];
619                        }
620                    }
621                }
622            }
623            $result = $collection;
624
625            if (App::$cache) {
626                App::$cache->set($cacheKey, $result);
627                $this->registerCacheKeyForDepartments($departmentIds, $cacheKey);
628                if (App::$log) {
629                    App::$log->info('Useraccount department list cache set', [
630                        'cache_key' => $cacheKey,
631                        'department_ids' => $departmentIds,
632                        'resolveReferences' => $resolveReferences,
633                        'count' => $result->count()
634                    ]);
635                }
636            }
637        }
638
639        return $result;
640    }
641
642    public function writeEntity(\BO\Zmsentities\Useraccount $entity, $resolveReferences = 0)
643    {
644        if ($this->readIsUserExisting($entity->id)) {
645            throw new Exception\Useraccount\DuplicateEntry();
646        }
647        $query = new Query\Useraccount(Query\Base::INSERT);
648        $values = $query->reverseEntityMapping($entity);
649        $query->addValues($values);
650        $this->writeItem($query);
651        $this->assignTemporaryRoleForNewUser($entity);
652        $this->updateAssignedDepartments($entity);
653
654        $this->removeCache($entity);
655
656        return $this->readEntity($entity->getId(), $resolveReferences, true);
657    }
658
659    /**
660     * Temporary bridge while roles/permissions UI is missing.
661     * Mirrors the logic from migration 91771576480-migrate-users-to-new-roles.sql
662     * so newly created users get a role entry in user_role.
663     *
664     * @todo Remove legacy Berechtigung->role mapping after full roles/permissions rollout.
665     */
666    protected function assignTemporaryRoleForNewUser(\BO\Zmsentities\Useraccount $entity): void
667    {
668        $berechtigung = (int) $entity->getRightsLevel();
669        $roleName = self::TEMP_ROLE_MAP_BY_BERECHTIGUNG[$berechtigung] ?? null;
670        if (!$roleName) {
671            return;
672        }
673
674        try {
675            $userId = (int) $this->readEntityIdByLoginName($entity->id);
676            $roleId = $this->readRoleIdByName($roleName);
677            $this->perform(
678                'INSERT IGNORE INTO user_role (user_id, role_id) VALUES (?, ?)',
679                [$userId, $roleId]
680            );
681        } catch (\Throwable $e) {
682            if (App::$log) {
683                App::$log->warning('Temporary user_role assignment failed', [
684                    'useraccount' => $entity->id ?? null,
685                    'berechtigung' => $berechtigung,
686                    'role_name' => $roleName,
687                    'exception' => get_class($e),
688                    'message' => $e->getMessage(),
689                ]);
690            }
691        }
692    }
693
694    protected function readRoleIdByName(string $roleName): int
695    {
696        $row = $this->getReader()->fetchOne(
697            'SELECT id FROM role WHERE name = ?',
698            [$roleName]
699        );
700        if (!$row || !isset($row['id'])) {
701            throw new \RuntimeException('Role not found: ' . $roleName);
702        }
703        return (int) $row['id'];
704    }
705
706    public function writeUpdatedEntity($loginName, \BO\Zmsentities\Useraccount $entity, $resolveReferences = 0)
707    {
708        $previousDepartmentIds = $this->readDepartmentIdsForLoginName($loginName);
709        $query = new Query\Useraccount(Query\Base::UPDATE);
710        $query->addConditionLoginName($loginName);
711        $values = $query->reverseEntityMapping($entity);
712        $query->addValues($values);
713        $this->writeItem($query);
714        $this->updateAssignedDepartments($entity);
715
716        $this->removeCache($entity, $previousDepartmentIds, $loginName);
717
718        return $this->readEntity($entity->getId(), $resolveReferences, true);
719    }
720
721    public function deleteEntity($loginName)
722    {
723        // Read entity before deletion to get cache info
724        $entity = $this->readEntity($loginName, 0, true);
725        $previousDepartmentIds = $this->readDepartmentIdsForLoginName($loginName);
726
727        $query = new Query\Useraccount(Query\Base::DELETE);
728        $query->addConditionLoginName($loginName);
729        $this->deleteAssignedDepartments($loginName);
730        $result = $this->deleteItem($query);
731
732        if ($entity && $entity->hasId()) {
733            $this->removeCache($entity, $previousDepartmentIds, $loginName);
734        }
735
736        return $result;
737    }
738
739    protected function updateAssignedDepartments($entity)
740    {
741        $loginName = $entity->id;
742        if (!$entity->isSuperUser()) {
743            $this->deleteAssignedDepartments($loginName);
744            $userId = $this->readEntityIdByLoginName($loginName);
745            foreach ($entity->departments as $department) {
746                $this->perform(
747                    Query\Useraccount::QUERY_WRITE_ASSIGNED_DEPARTMENTS,
748                    array(
749                        $userId,
750                        $department['id']
751                    )
752                );
753            }
754        }
755    }
756
757    protected function readEntityIdByLoginName($loginName)
758    {
759        $query = Query\Useraccount::QUERY_READ_ID_BY_USERNAME;
760        $result = $this->getReader()->fetchOne($query, [$loginName]);
761        return $result['id'];
762    }
763
764    protected function deleteAssignedDepartments($loginName)
765    {
766        $query = Query\Useraccount::QUERY_DELETE_ASSIGNED_DEPARTMENTS;
767        $userId = $this->readEntityIdByLoginName($loginName);
768        return $this->perform($query, [$userId]);
769    }
770
771    protected function buildSearchCacheKey($prefix, $resolveReferences, $workstation, $queryString, array $departmentIds = [])
772    {
773        $version = $this->getUseraccountCacheVersion();
774        $workstationKey = '';
775        if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
776            $workstationKey = '-workstation-' . $workstation->getUseraccount()->id;
777        }
778        $departmentKey = empty($departmentIds) ? '' : '-' . implode(',', $departmentIds);
779        return "{$prefix}-v{$version}{$departmentKey}-$resolveReferences$workstationKey-query-" . md5($queryString);
780    }
781
782    protected function getCachedResult($cacheKey, $disableCache, $logMessage, array $logContext = [])
783    {
784        $result = null;
785        if (!$disableCache && App::$cache && App::$cache->has($cacheKey)) {
786            $result = App::$cache->get($cacheKey);
787            if ($result && App::$log) {
788                $logContext['cache_key'] = $cacheKey;
789                $logContext['count'] = $result->count();
790                App::$log->info($logMessage, $logContext);
791            }
792        }
793        return $result;
794    }
795
796    protected function setCachedResult($cacheKey, $result, array $departmentIds, $logMessage, array $logContext = [])
797    {
798        if (App::$cache) {
799            App::$cache->set($cacheKey, $result);
800            $this->registerCacheKeyForDepartments($departmentIds, $cacheKey);
801            if (App::$log) {
802                $logContext['cache_key'] = $cacheKey;
803                $logContext['count'] = $result->count();
804                App::$log->info($logMessage, $logContext);
805            }
806        }
807    }
808
809    protected function executeSearchQuery(array $parameter, $resolveReferences, $workstation)
810    {
811        $query = new Query\Useraccount(Query\Base::SELECT);
812        $query
813            ->addResolvedReferences($resolveReferences)
814            ->addEntityMapping()
815            ->addOrderByName();
816
817        // For superusers: select all users without department filtering
818        // For non-superusers: apply department-based access filtering
819        if (!$this->applyWorkstationAccessFilter($query, $workstation)) {
820            return new Collection();
821        }
822
823        if (isset($parameter['query'])) {
824            if (preg_match('#^\d+$#', $parameter['query'])) {
825                $query->addConditionUserId($parameter['query']);
826                $query->addConditionSearch($parameter['query'], true);
827            } else {
828                $query->addConditionSearch($parameter['query']);
829            }
830        }
831
832        $collection = new Collection();
833        $result = $this->fetchList($query, new Entity());
834        if (count($result)) {
835            foreach ($result as $entity) {
836                $collection->addEntity($entity);
837            }
838            if (0 < $resolveReferences) {
839                $departmentMap = $this->readAssignedDepartmentListsForAll($collection, $resolveReferences - 1);
840                foreach ($collection as $entity) {
841                    if (isset($departmentMap[$entity->id])) {
842                        $entity->departments = $departmentMap[$entity->id];
843                    }
844                }
845            }
846        }
847        return $collection;
848    }
849
850    protected function executeSearchByDepartmentIdsQuery(array $departmentIds, array $parameter, $resolveReferences, $workstation)
851    {
852        $query = new Query\Useraccount(Query\Base::SELECT);
853        $query->addResolvedReferences($resolveReferences)
854            ->addEntityMapping()
855            ->addOrderByName();
856
857        if (isset($parameter['query'])) {
858            if (preg_match('#^\d+$#', $parameter['query'])) {
859                $query->addConditionUserId($parameter['query']);
860                $query->addConditionDepartmentIdsAndSearch($departmentIds, $parameter['query'], true);
861            } else {
862                $query->addConditionDepartmentIdsAndSearch($departmentIds, $parameter['query']);
863            }
864        } else {
865            $query->addConditionDepartmentIds($departmentIds);
866        }
867
868        // Exclude superusers if workstation user is not superuser
869        if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
870            $query->addConditionExcludeSuperusers();
871        }
872
873        $collection = new Collection();
874        $result = $this->fetchList($query, new Entity());
875        if (count($result)) {
876            foreach ($result as $entity) {
877                $collection->addEntity($entity);
878            }
879            if (0 < $resolveReferences) {
880                $departmentMap = $this->readAssignedDepartmentListsForAll($collection, $resolveReferences - 1);
881                foreach ($collection as $entity) {
882                    if (isset($departmentMap[$entity->id])) {
883                        $entity->departments = $departmentMap[$entity->id];
884                    }
885                }
886            }
887        }
888        return $collection;
889    }
890
891    /**
892     * @SuppressWarnings(NPathComplexity)
893     */
894    public function readSearch(array $parameter, $resolveReferences = 0, $workstation = null, $disableCache = false)
895    {
896        $queryString = isset($parameter['query']) ? $parameter['query'] : '';
897        $cacheKey = $this->buildSearchCacheKey('useraccountReadSearch', $resolveReferences, $workstation, $queryString);
898        $result = $this->getCachedResult($cacheKey, $disableCache, 'Useraccount search cache hit', [
899            'query' => $queryString,
900            'resolveReferences' => $resolveReferences
901        ]);
902
903        if (empty($result)) {
904            $result = $this->executeSearchQuery($parameter, $resolveReferences, $workstation);
905            $this->setCachedResult($cacheKey, $result, [self::CACHE_INDEX_GLOBAL], 'Useraccount search cache set', [
906                'query' => $queryString,
907                'resolveReferences' => $resolveReferences
908            ]);
909        }
910
911        return $result;
912    }
913
914    /**
915     * @SuppressWarnings(NPathComplexity)
916     */
917    public function readSearchByDepartmentIds(array $departmentIds, array $parameter, $resolveReferences = 0, $workstation = null, $disableCache = false)
918    {
919        sort($departmentIds);
920        $queryString = isset($parameter['query']) ? $parameter['query'] : '';
921        $cacheKey = $this->buildSearchCacheKey('useraccountReadSearchByDepartmentIds', $resolveReferences, $workstation, $queryString, $departmentIds);
922        $result = $this->getCachedResult($cacheKey, $disableCache, 'Useraccount search by department cache hit', [
923            'department_ids' => $departmentIds,
924            'query' => $queryString,
925            'resolveReferences' => $resolveReferences
926        ]);
927
928        if (empty($result)) {
929            $result = $this->executeSearchByDepartmentIdsQuery($departmentIds, $parameter, $resolveReferences, $workstation);
930            $this->setCachedResult($cacheKey, $result, $departmentIds, 'Useraccount search by department cache set', [
931                'department_ids' => $departmentIds,
932                'query' => $queryString,
933                'resolveReferences' => $resolveReferences
934            ]);
935        }
936
937        return $result;
938    }
939
940    public function readListRole($roleLevel, $resolveReferences = 0, $workstation = null)
941    {
942        $version = $this->getUseraccountCacheVersion();
943        $workstationKey = '';
944        if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
945            $workstationKey = '-workstation-' . $workstation->getUseraccount()->id;
946        }
947        $cacheKey = "useraccountReadByRole-v{$version}-" . ($roleLevel ?? 'null') . "-$resolveReferences$workstationKey";
948        $result = null;
949
950        if (App::$cache && App::$cache->has($cacheKey)) {
951            $result = App::$cache->get($cacheKey);
952            if ($result && App::$log) {
953                App::$log->info('Useraccount role list cache hit', [
954                    'cache_key' => $cacheKey,
955                    'role_level' => $roleLevel,
956                    'resolveReferences' => $resolveReferences,
957                    'count' => $result->count()
958                ]);
959            }
960        }
961
962        if (empty($result)) {
963            $query = new Query\Useraccount(Query\Base::SELECT);
964            $query->addResolvedReferences($resolveReferences)
965            ->addEntityMapping()
966            ->addOrderByName();
967
968            if (isset($roleLevel)) {
969                $query->addConditionRoleLevel($roleLevel);
970            }
971
972            // Apply workstation access filtering if provided
973            if (!$this->applyWorkstationAccessFilter($query, $workstation)) {
974                return new Collection();
975            }
976
977            $statement = $this->fetchStatement($query);
978            $result = $this->readListStatement($statement, $resolveReferences);
979
980            if (App::$cache) {
981                App::$cache->set($cacheKey, $result);
982                $this->registerCacheKeyForDepartments([self::CACHE_INDEX_GLOBAL], $cacheKey);
983                if (App::$log) {
984                    App::$log->info('Useraccount role list cache set', [
985                        'cache_key' => $cacheKey,
986                        'role_level' => $roleLevel,
987                        'resolveReferences' => $resolveReferences,
988                        'count' => $result->count()
989                    ]);
990                }
991            }
992        }
993
994        return $result;
995    }
996
997    /**
998     * @SuppressWarnings(NPathComplexity)
999     */
1000    public function readListByRoleAndDepartmentIds($roleLevel, array $departmentIds, $resolveReferences = 0, $disableCache = false, $workstation = null)
1001    {
1002        sort($departmentIds);
1003        $version = $this->getUseraccountCacheVersion();
1004        $workstationKey = '';
1005        if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
1006            $workstationKey = '-workstation-' . $workstation->getUseraccount()->id;
1007        }
1008        $cacheKey = "useraccountReadByRoleAndDepartmentIds-v{$version}-$roleLevel-" . implode(',', $departmentIds) . "-$resolveReferences$workstationKey";
1009        $result = null;
1010
1011        if (!$disableCache && App::$cache && App::$cache->has($cacheKey)) {
1012            $result = App::$cache->get($cacheKey);
1013            if ($result && App::$log) {
1014                App::$log->info('Useraccount role and department list cache hit', [
1015                    'cache_key' => $cacheKey,
1016                    'role_level' => $roleLevel,
1017                    'department_ids' => $departmentIds,
1018                    'resolveReferences' => $resolveReferences,
1019                    'count' => $result->count()
1020                ]);
1021            }
1022        }
1023
1024        if (empty($result)) {
1025            $query = new Query\Useraccount(Query\Base::SELECT);
1026            $query->addResolvedReferences($resolveReferences)
1027              ->addEntityMapping()
1028              ->addOrderByName();
1029
1030            if (isset($roleLevel) && !empty($departmentIds)) {
1031                $query->addConditionRoleLevel($roleLevel);
1032                $query->addConditionDepartmentIds($departmentIds);
1033            }
1034
1035            // Exclude superusers if workstation user is not superuser
1036            if ($workstation && !$workstation->getUseraccount()->isSuperUser()) {
1037                $query->addConditionExcludeSuperusers();
1038            }
1039
1040            $statement = $this->fetchStatement($query);
1041            $result = $this->readListStatement($statement, $resolveReferences);
1042
1043            if (App::$cache) {
1044                App::$cache->set($cacheKey, $result);
1045                $this->registerCacheKeyForDepartments($departmentIds, $cacheKey);
1046                if (App::$log) {
1047                    App::$log->info('Useraccount role and department list cache set', [
1048                        'cache_key' => $cacheKey,
1049                        'role_level' => $roleLevel,
1050                        'department_ids' => $departmentIds,
1051                        'resolveReferences' => $resolveReferences,
1052                        'count' => $result->count()
1053                    ]);
1054                }
1055            }
1056        }
1057
1058        return $result;
1059    }
1060
1061    public function removeCache($useraccount, array $previousDepartmentIds = [], ?string $oldLoginName = null)
1062    {
1063        if (!App::$cache) {
1064            return;
1065        }
1066
1067        $currentVersion = $this->getUseraccountCacheVersion();
1068        $identifiers = $this->collectUseraccountIdentifiers($useraccount);
1069        // Add old loginname to identifiers if provided and not already in the list
1070        // This ensures cache keys for the old loginname are invalidated when loginname changes
1071        if ($oldLoginName !== null && !in_array($oldLoginName, $identifiers, true)) {
1072            $identifiers[] = $oldLoginName;
1073        }
1074        $removedEntityKeys = [];
1075
1076        foreach ($identifiers as $identifier) {
1077            for ($i = 0; $i <= 2; $i++) {
1078                $cacheKey = $this->sanitizeCacheKey("useraccount-v{$currentVersion}-{$identifier}-$i");
1079                if ($this->deleteCacheKey($cacheKey)) {
1080                    $removedEntityKeys[] = $cacheKey;
1081                }
1082            }
1083        }
1084
1085        $departmentIds = $this->collectDepartmentIdsForInvalidation($useraccount, $previousDepartmentIds);
1086        $removedDepartmentCaches = $this->invalidateDepartmentCaches($departmentIds);
1087
1088        $versionBumped = false;
1089        $newVersion = $currentVersion;
1090
1091        if (!$removedDepartmentCaches) {
1092            $newVersion = $currentVersion + 1;
1093            App::$cache->set(self::CACHE_VERSION_KEY, $newVersion);
1094            $versionBumped = true;
1095        }
1096
1097        if (App::$log) {
1098            App::$log->info('Useraccount caches invalidated after mutation', [
1099                'useraccount_id' => $useraccount->id ?? null,
1100                'identifiers' => $identifiers,
1101                'removed_entity_cache_keys' => $removedEntityKeys,
1102                'department_ids' => $departmentIds,
1103                'version_bumped' => $versionBumped,
1104                'old_version' => $currentVersion,
1105                'new_version' => $newVersion,
1106            ]);
1107        }
1108    }
1109}