Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
94.96%
226 / 238
75.00%
12 / 16
 CRAP
0.00%
0 / 1
SqlStructureRepository
94.94%
225 / 237
75.00%
12 / 16
 65.55
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 getTableName
100.00%
15 / 15
100.00%
1 / 1
 5
 findById
100.00%
12 / 12
100.00%
1 / 1
 3
 findByCode
83.33%
10 / 12
0.00%
0 / 1
 3.04
 findAllActive
85.71%
12 / 14
0.00%
0 / 1
 4.05
 getTree
100.00%
17 / 17
100.00%
1 / 1
 6
 save
100.00%
63 / 63
100.00%
1 / 1
 4
 delete
100.00%
5 / 5
100.00%
1 / 1
 1
 countChildNodes
100.00%
4 / 4
100.00%
1 / 1
 1
 countAssignedRecords
77.78%
21 / 27
0.00%
0 / 1
 4.18
 findHighestHierarchyLogo
81.82%
9 / 11
0.00%
0 / 1
 3.05
 fetchLogoFromQuery
100.00%
6 / 6
100.00%
1 / 1
 4
 hydrateNode
100.00%
36 / 36
100.00%
1 / 1
 17
 parseDateTime
100.00%
1 / 1
100.00%
1 / 1
 3
 parseCoOwners
100.00%
4 / 4
100.00%
1 / 1
 3
 populateMembership
100.00%
9 / 9
100.00%
1 / 1
 3
1<?php
2
3declare(strict_types=1);
4
5/** @license For full copyright and license information, please see the LICENSE.md file. */
6
7namespace App\Modules\Structure\Infrastructure\Repository;
8
9defined('AMMONLY_APP') || exit('Direct script access is forbidden.');
10
11use App\Core\Database\Security\SqlIdentifierValidator;
12use App\Modules\Structure\Domain\Model\StructureNode;
13use App\Modules\Structure\Domain\Model\StructureType;
14use App\Modules\Structure\Domain\Repository\StructureMembershipRepositoryInterface;
15use App\Modules\Structure\Domain\Repository\StructureRepositoryInterface;
16use DateTimeImmutable;
17use PDO;
18
19/**
20 * SQL Implementation of Structure Repository.
21 *
22 * Persists and retrieves organizational structure nodes from database table a_mod_structure_records.
23 *
24 * @package App\Modules\Structure\Infrastructure\Repository
25 */
26final class SqlStructureRepository implements StructureRepositoryInterface
27{
28    /** @var array<int, StructureNode>|null In-memory cache of assembled active structure tree. */
29    private ?array $treeCache = null;
30
31    /**
32     * SqlStructureRepository constructor.
33     *
34     * @param PDO $pdo Database PDO connection.
35     * @param StructureMembershipRepositoryInterface|null $membershipRepo Optional membership repository.
36     * @param string $tablePrefix Database table prefix.
37     */
38    public function __construct(
39        private readonly PDO $pdo,
40        private readonly ?StructureMembershipRepositoryInterface $membershipRepo = null,
41        private readonly string $tablePrefix = 'a_'
42    ) {
43    }
44
45    /** @var string|null Cached resolved table name. */
46    private ?string $resolvedTableName = null;
47
48    /**
49     * Resolves existing table name for structure records.
50     *
51     * Tries table candidates in sequence: configured prefix table, c_mod_structure_records, a_mod_structure_records.
52     *
53     * @return string Validated table name.
54     */
55    private function getTableName(): string
56    {
57        if ($this->resolvedTableName !== null) {
58            return $this->resolvedTableName;
59        }
60
61        $candidates = array_unique([
62            $this->tablePrefix . 'mod_structure_records',
63            'c_mod_structure_records',
64            'a_mod_structure_records',
65        ]);
66
67        foreach ($candidates as $table) {
68            try {
69                $stmt = $this->pdo->query("SELECT 1 FROM `{$table}` LIMIT 1");
70                if ($stmt !== false) {
71                    $this->resolvedTableName = $table;
72                    return $table;
73                }
74            } catch (\Throwable) {
75                // Table does not exist in current database, try next
76            }
77        }
78
79        $this->resolvedTableName = $this->tablePrefix . 'mod_structure_records';
80        return $this->resolvedTableName;
81    }
82
83    /**
84     * {@inheritdoc}
85     */
86    public function findById(int $id): ?StructureNode
87    {
88        try {
89            $tableName = $this->getTableName();
90            $sql = "SELECT id, name, code, parent_id, structure_type, description, logo, sort_order,
91                           status, special_access, created_at, updated_at, created_by, owner, owner_type, co_owners,
92                           address_street, address_building_number, address_apartment_number,
93                           address_postal_code, address_city, address_country, address_latitude, address_longitude
94                    FROM {$tableName}
95                    WHERE id = :id LIMIT 1";
96
97            $stmt = $this->pdo->prepare($sql);
98            $stmt->execute([':id' => $id]);
99            $row = $stmt->fetch(PDO::FETCH_ASSOC);
100
101            if ($row === false) {
102                return null;
103            }
104
105            /** @var array<string, mixed> $row */
106            return $this->hydrateNode($row);
107        } catch (\Throwable) {
108            return null;
109        }
110    }
111
112    /**
113     * {@inheritdoc}
114     */
115    public function findByCode(string $code): ?StructureNode
116    {
117        try {
118            $tableName = $this->getTableName();
119            $sql = "SELECT id, name, code, parent_id, structure_type, description, logo, sort_order,
120                           status, special_access, created_at, updated_at, created_by, owner, owner_type, co_owners,
121                           address_street, address_building_number, address_apartment_number,
122                           address_postal_code, address_city, address_country, address_latitude, address_longitude
123                    FROM {$tableName}
124                    WHERE code = :code LIMIT 1";
125
126            $stmt = $this->pdo->prepare($sql);
127            $stmt->execute([':code' => $code]);
128            $row = $stmt->fetch(PDO::FETCH_ASSOC);
129
130            if ($row === false) {
131                return null;
132            }
133
134            /** @var array<string, mixed> $row */
135            return $this->hydrateNode($row);
136        } catch (\Throwable) {
137            return null;
138        }
139    }
140
141    /**
142     * {@inheritdoc}
143     */
144    public function findAllActive(): array
145    {
146        try {
147            $tableName = $this->getTableName();
148            $sql = "SELECT id, name, code, parent_id, structure_type, description, logo, sort_order,
149                           status, special_access, created_at, updated_at, created_by, owner, owner_type, co_owners,
150                           address_street, address_building_number, address_apartment_number,
151                           address_postal_code, address_city, address_country, address_latitude, address_longitude
152                    FROM {$tableName}
153                    WHERE status = 'active' AND special_access = 1
154                    ORDER BY sort_order ASC, id ASC";
155
156            $stmt = $this->pdo->query($sql);
157            if ($stmt === false) {
158                return [];
159            }
160
161            /** @var array<int, array<string, mixed>> $rows */
162            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
163            $nodes = [];
164
165            foreach ($rows as $row) {
166                $nodes[] = $this->hydrateNode($row);
167            }
168
169            return $nodes;
170        } catch (\Throwable) {
171            return [];
172        }
173    }
174
175    /**
176     * {@inheritdoc}
177     */
178    public function getTree(): array
179    {
180        if ($this->treeCache !== null) {
181            return $this->treeCache;
182        }
183
184        $allNodes = $this->findAllActive();
185        /** @var array<int, StructureNode> $nodesById */
186        $nodesById = [];
187        /** @var array<int, int|null> $parentIdsById */
188        $parentIdsById = [];
189
190        foreach ($allNodes as $node) {
191            $id = (int)$node->getId();
192            $nodesById[$id] = $node;
193            $parentIdsById[$id] = $node->getParentId();
194        }
195
196        /** @var array<int, StructureNode> $rootNodes */
197        $rootNodes = [];
198
199        foreach ($nodesById as $id => $node) {
200            $parentId = $parentIdsById[$id];
201            if ($parentId !== null && isset($nodesById[$parentId])) {
202                $nodesById[$parentId]->addChild($node);
203            } else {
204                $rootNodes[] = $node;
205            }
206        }
207
208        $this->treeCache = $rootNodes;
209        return $this->treeCache;
210    }
211
212    /**
213     * {@inheritdoc}
214     */
215    public function save(StructureNode $node): int
216    {
217        $tableName = $this->getTableName();
218        $now = date('Y-m-d H:i:s');
219        $coOwnersJson = !empty($node->getCoOwners()) ? (string)json_encode($node->getCoOwners()) : null;
220
221        if ($node->getId() === null || $node->getId() <= 0) {
222            $sql = "INSERT INTO {$tableName}
223                    (name, code, parent_id, structure_type, description, logo, sort_order,
224                     status, special_access, created_at, updated_at, created_by, owner, owner_type, co_owners,
225                     address_street, address_building_number, address_apartment_number,
226                     address_postal_code, address_city, address_country, address_latitude, address_longitude)
227                    VALUES
228                    (:name, :code, :parent_id, :structure_type, :description, :logo, :sort_order,
229                     :status, :special_access, :created_at, :updated_at, :created_by, :owner, :owner_type, :co_owners,
230                     :address_street, :address_building_number, :address_apartment_number,
231                     :address_postal_code, :address_city, :address_country, :address_latitude, :address_longitude)";
232
233            $stmt = $this->pdo->prepare($sql);
234            $stmt->execute([
235                ':name'                     => $node->getName(),
236                ':code'                     => $node->getCode(),
237                ':parent_id'                => $node->getParentId(),
238                ':structure_type'           => $node->getStructureType()->value,
239                ':description'              => $node->getDescription(),
240                ':logo'                     => $node->getLogo(),
241                ':sort_order'               => $node->getSortOrder(),
242                ':status'                   => $node->getStatus(),
243                ':special_access'           => $node->getSpecialAccess(),
244                ':created_at'               => $now,
245                ':updated_at'               => $now,
246                ':created_by'               => $node->getCreatedBy(),
247                ':owner'                    => $node->getOwner(),
248                ':owner_type'               => $node->getOwnerType(),
249                ':co_owners'                => $coOwnersJson,
250                ':address_street'           => $node->getAddressStreet(),
251                ':address_building_number'  => $node->getAddressBuildingNumber(),
252                ':address_apartment_number' => $node->getAddressApartmentNumber(),
253                ':address_postal_code'      => $node->getAddressPostalCode(),
254                ':address_city'             => $node->getAddressCity(),
255                ':address_country'          => $node->getAddressCountry(),
256                ':address_latitude'         => $node->getAddressLatitude(),
257                ':address_longitude'        => $node->getAddressLongitude(),
258            ]);
259
260            $this->treeCache = null;
261            return (int)$this->pdo->lastInsertId();
262        }
263
264        $sql = "UPDATE {$tableName}
265                SET name = :name, code = :code, parent_id = :parent_id, structure_type = :structure_type,
266                    description = :description, logo = :logo, sort_order = :sort_order,
267                    status = :status, special_access = :special_access,
268                    updated_at = :updated_at, owner = :owner, owner_type = :owner_type, co_owners = :co_owners,
269                    address_street = :address_street, address_building_number = :address_building_number,
270                    address_apartment_number = :address_apartment_number,
271                    address_postal_code = :address_postal_code, address_city = :address_city,
272                    address_country = :address_country, address_latitude = :address_latitude,
273                    address_longitude = :address_longitude
274                WHERE id = :id";
275
276        $stmt = $this->pdo->prepare($sql);
277        $stmt->execute([
278            ':id'                       => $node->getId(),
279            ':name'                     => $node->getName(),
280            ':code'                     => $node->getCode(),
281            ':parent_id'                => $node->getParentId(),
282            ':structure_type'           => $node->getStructureType()->value,
283            ':description'              => $node->getDescription(),
284            ':logo'                     => $node->getLogo(),
285            ':sort_order'               => $node->getSortOrder(),
286            ':status'                   => $node->getStatus(),
287            ':special_access'           => $node->getSpecialAccess(),
288            ':updated_at'               => $now,
289            ':owner'                    => $node->getOwner(),
290            ':owner_type'               => $node->getOwnerType(),
291            ':co_owners'                => $coOwnersJson,
292            ':address_street'           => $node->getAddressStreet(),
293            ':address_building_number'  => $node->getAddressBuildingNumber(),
294            ':address_apartment_number' => $node->getAddressApartmentNumber(),
295            ':address_postal_code'      => $node->getAddressPostalCode(),
296            ':address_city'             => $node->getAddressCity(),
297            ':address_country'          => $node->getAddressCountry(),
298            ':address_latitude'         => $node->getAddressLatitude(),
299            ':address_longitude'        => $node->getAddressLongitude(),
300        ]);
301
302        $this->treeCache = null;
303        return (int)$node->getId();
304    }
305
306    /**
307     * {@inheritdoc}
308     */
309    public function delete(int $id): bool
310    {
311        $quoted = SqlIdentifierValidator::quote($this->getTableName());
312        $stmt = $this->pdo->prepare("DELETE FROM {$quoted} WHERE id = :id");
313        $success = $stmt->execute([':id' => $id]);
314        $this->treeCache = null;
315
316        return $success;
317    }
318
319    /**
320     * {@inheritdoc}
321     */
322    public function countChildNodes(int $structureId): int
323    {
324        $quoted = SqlIdentifierValidator::quote($this->getTableName());
325        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM {$quoted} WHERE parent_id = :pid");
326        $stmt->execute([':pid' => $structureId]);
327
328        return (int)$stmt->fetchColumn();
329    }
330
331    /**
332     * {@inheritdoc}
333     */
334    public function countAssignedRecords(int $structureId): int
335    {
336        // 1. Check direct co-ownership table
337        $stmtCo = $this->pdo->prepare(
338            'SELECT COUNT(*) FROM `a_core_record_co_owners` '
339            . "WHERE `owner_type` = 'structure' AND `structure_id` = :sid"
340        );
341        $stmtCo->execute([':sid' => $structureId]);
342        $coCount = (int)$stmtCo->fetchColumn();
343
344        // 2. Inspect active core modules that have owner_type and owner column
345        $stmtMod = $this->pdo->query(
346            "SELECT table_name FROM a_core_module_records WHERE is_active = 1 AND table_name IS NOT NULL"
347        );
348        if ($stmtMod === false) {
349            return $coCount;
350        }
351
352        $totalCount = $coCount;
353        /** @var array<int, array<string, mixed>> $modules */
354        $modules = $stmtMod->fetchAll(PDO::FETCH_ASSOC);
355
356        foreach ($modules as $mod) {
357            $tableName = (string)$mod['table_name'];
358            // Check if table has owner_type and owner column
359            $chk = $this->pdo->prepare(
360                "SELECT COUNT(*) FROM information_schema.columns "
361                . "WHERE table_schema = DATABASE() AND table_name = :tname AND column_name = 'owner_type'"
362            );
363            $chk->execute([':tname' => $tableName]);
364            if ((int)$chk->fetchColumn() > 0) {
365                $q = $this->pdo->prepare(
366                    "SELECT COUNT(*) FROM `{$tableName}` WHERE `owner_type` = 'structure' AND `owner` = :sid"
367                );
368                $q->execute([':sid' => $structureId]);
369                $totalCount += (int)$q->fetchColumn();
370            }
371        }
372
373        return $totalCount;
374    }
375
376    /**
377     * {@inheritdoc}
378     */
379    public function findHighestHierarchyLogo(): ?string
380    {
381        try {
382            $tableName = $this->getTableName();
383
384            // 1. Highest priority: Root level active node (parent_id IS NULL) with configured logo
385            $sqlRoot = "SELECT logo FROM {$tableName}
386                        WHERE status = 'active' AND special_access = 1
387                          AND parent_id IS NULL
388                          AND logo IS NOT NULL
389                          AND logo != ''
390                        ORDER BY sort_order ASC, id ASC
391                        LIMIT 1";
392
393            $rootLogo = $this->fetchLogoFromQuery($sqlRoot);
394            if ($rootLogo !== null) {
395                return $rootLogo;
396            }
397
398            // 2. Secondary priority: Any active node with configured logo closest to root
399            $sqlAny = "SELECT logo FROM {$tableName}
400                       WHERE status = 'active' AND special_access = 1
401                         AND logo IS NOT NULL
402                         AND logo != ''
403                       ORDER BY parent_id ASC, sort_order ASC, id ASC
404                       LIMIT 1";
405
406            return $this->fetchLogoFromQuery($sqlAny);
407        } catch (\Throwable) {
408            return null;
409        }
410    }
411
412    /**
413     * Executes single-column logo fetch query.
414     *
415     * @param string $sql SQL query string.
416     * @return string|null Trimmed logo or null.
417     */
418    private function fetchLogoFromQuery(string $sql): ?string
419    {
420        $stmt = $this->pdo->query($sql);
421        if ($stmt !== false) {
422            $logo = $stmt->fetchColumn();
423            if (is_string($logo) && trim($logo) !== '') {
424                return trim($logo);
425            }
426        }
427
428        return null;
429    }
430
431    /**
432     * Hydrates raw database row into StructureNode entity.
433     *
434     * @param array<string, mixed> $row Raw database row.
435     * @return StructureNode Hydrated entity.
436     */
437    private function hydrateNode(array $row): StructureNode
438    {
439        $id = (int)$row['id'];
440        $typeStr = (string)($row['structure_type'] ?? 'department');
441        $type = StructureType::tryFrom($typeStr) ?? StructureType::DEPARTMENT;
442
443        $node = new StructureNode(
444            id: $id,
445            name: (string)$row['name'],
446            code: isset($row['code']) ? (string)$row['code'] : null,
447            parentId: isset($row['parent_id']) ? (int)$row['parent_id'] : null,
448            structureType: $type,
449            description: isset($row['description']) ? (string)$row['description'] : null,
450            logo: isset($row['logo']) && (string)$row['logo'] !== '' ? (string)$row['logo'] : null,
451            sortOrder: (int)($row['sort_order'] ?? 0),
452            isActive: ($row['status'] ?? 'active') === 'active' && ((int)($row['special_access'] ?? 1) === 1),
453            createdAt: $this->parseDateTime($row['created_at'] ?? null),
454            updatedAt: $this->parseDateTime($row['updated_at'] ?? null),
455            createdBy: (int)($row['created_by'] ?? 1),
456            owner: (int)($row['owner'] ?? 1),
457            ownerType: (string)($row['owner_type'] ?? 'user'),
458            coOwners: $this->parseCoOwners($row['co_owners'] ?? null),
459            status: (string)($row['status'] ?? 'active'),
460            specialAccess: (int)($row['special_access'] ?? 1),
461            addressStreet: isset($row['address_street']) ? (string)$row['address_street'] : null,
462            addressBuildingNumber: isset($row['address_building_number'])
463                ? (string)$row['address_building_number'] : null,
464            addressApartmentNumber: isset($row['address_apartment_number'])
465                ? (string)$row['address_apartment_number'] : null,
466            addressPostalCode: isset($row['address_postal_code']) ? (string)$row['address_postal_code'] : null,
467            addressCity: isset($row['address_city']) ? (string)$row['address_city'] : null,
468            addressCountry: isset($row['address_country']) ? (string)$row['address_country'] : null,
469            addressLatitude: isset($row['address_latitude']) && $row['address_latitude'] !== null
470                ? (float)$row['address_latitude'] : null,
471            addressLongitude: isset($row['address_longitude']) && $row['address_longitude'] !== null
472                ? (float)$row['address_longitude'] : null
473        );
474
475        $this->populateMembership($node, $id);
476
477        return $node;
478    }
479
480    private function parseDateTime(mixed $val): ?DateTimeImmutable
481    {
482        return is_string($val) && $val !== '' ? new DateTimeImmutable($val) : null;
483    }
484
485    /**
486     * @return list<int>
487     */
488    private function parseCoOwners(mixed $raw): array
489    {
490        if (empty($raw)) {
491            return [];
492        }
493        $decoded = json_decode((string)$raw, true);
494        return is_array($decoded) ? array_map('intval', $decoded) : [];
495    }
496
497    private function populateMembership(StructureNode $node, int $id): void
498    {
499        if ($this->membershipRepo === null) {
500            return;
501        }
502        $users = $this->membershipRepo->getStructureUsers($id);
503        $userIds = [];
504        $userNames = [];
505        foreach ($users as $u) {
506            $userIds[] = (int)$u['id'];
507            $userNames[] = (string)($u['full_name'] ?? $u['email'] ?? ('User #' . $u['id']));
508        }
509        $node->setAssignedUsers($userIds, $userNames);
510    }
511}