Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
87.07%
128 / 147
9.09%
1 / 11
 CRAP
0.00%
0 / 1
SqlStructureMembershipRepository
86.99%
127 / 146
9.09%
1 / 11
 44.70
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 getTableName
80.00%
12 / 15
0.00%
0 / 1
 5.20
 getUserTableName
81.82%
9 / 11
0.00%
0 / 1
 4.10
 getUserStructureIds
80.00%
8 / 10
0.00%
0 / 1
 3.07
 getStructureUserIds
80.00%
8 / 10
0.00%
0 / 1
 3.07
 getStructureUsers
83.33%
10 / 12
0.00%
0 / 1
 3.04
 countUsersInStructure
71.43%
5 / 7
0.00%
0 / 1
 2.09
 assignUser
91.67%
11 / 12
0.00%
0 / 1
 4.01
 removeUser
87.50%
7 / 8
0.00%
0 / 1
 2.01
 syncUserStructures
93.33%
28 / 30
0.00%
0 / 1
 7.01
 syncStructureUsers
93.33%
28 / 30
0.00%
0 / 1
 7.01
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\Modules\Structure\Domain\Repository\StructureMembershipRepositoryInterface;
12use PDO;
13
14/**
15 * SQL Implementation of Structure Membership Repository.
16 *
17 * Persists and manages M:N assignments between users and structure nodes in a_rel_users_structure.
18 *
19 * @package App\Modules\Structure\Infrastructure\Repository
20 */
21final class SqlStructureMembershipRepository implements StructureMembershipRepositoryInterface
22{
23    private const string DATETIME_FORMAT = 'Y-m-d H:i:s';
24    private const string INSERT_VALUES_CLAUSE = 'VALUES (:user_id, :structure_id, :created_at)';
25    private const string WHERE_MEMBERSHIP_CLAUSE = 'WHERE `user_id` = :user_id AND `structure_id` = :structure_id';
26
27    /** @var string|null Cached resolved table name. */
28    private ?string $resolvedTableName = null;
29
30    /**
31     * SqlStructureMembershipRepository constructor.
32     *
33     * @param PDO $pdo Database connection.
34     * @param string $tablePrefix Database table prefix.
35     */
36    public function __construct(
37        private readonly PDO $pdo,
38        private readonly string $tablePrefix = 'c_'
39    ) {
40    }
41
42    /**
43     * Resolves existing table name for structure membership relations.
44     *
45     * @return string Validated table name.
46     */
47    private function getTableName(): string
48    {
49        if ($this->resolvedTableName !== null) {
50            return $this->resolvedTableName;
51        }
52
53        $candidates = array_unique([
54            $this->tablePrefix . 'rel_users_structure',
55            'c_rel_users_structure',
56            'a_rel_users_structure',
57        ]);
58
59        foreach ($candidates as $table) {
60            try {
61                $stmt = $this->pdo->query("SELECT 1 FROM `{$table}` LIMIT 1");
62                if ($stmt !== false) {
63                    $this->resolvedTableName = $table;
64                    return $table;
65                }
66            } catch (\Throwable) {
67                // Table does not exist in database, check next candidate
68            }
69        }
70
71        $this->resolvedTableName = $this->tablePrefix . 'rel_users_structure';
72        return $this->resolvedTableName;
73    }
74
75    /**
76     * Resolves existing users table name.
77     *
78     * @return string Validated users table name.
79     */
80    private function getUserTableName(): string
81    {
82        $candidates = array_unique([
83            $this->tablePrefix . 'mod_users_records',
84            'c_mod_users_records',
85            'a_mod_users_records',
86        ]);
87
88        foreach ($candidates as $table) {
89            try {
90                $stmt = $this->pdo->query("SELECT 1 FROM `{$table}` LIMIT 1");
91                if ($stmt !== false) {
92                    return $table;
93                }
94            } catch (\Throwable) {
95                // Check next
96            }
97        }
98
99        return $this->tablePrefix . 'mod_users_records';
100    }
101
102    /**
103     * {@inheritdoc}
104     */
105    public function getUserStructureIds(int $userId): array
106    {
107        try {
108            $tableName = $this->getTableName();
109            $sql = "SELECT `structure_id` FROM `{$tableName}` WHERE `user_id` = :uid ORDER BY `structure_id` ASC";
110
111            $stmt = $this->pdo->prepare($sql);
112            $stmt->execute([':uid' => $userId]);
113
114            $ids = [];
115            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
116                $ids[] = (int)$row['structure_id'];
117            }
118
119            return $ids;
120        } catch (\Throwable) {
121            return [];
122        }
123    }
124
125    /**
126     * {@inheritdoc}
127     */
128    public function getStructureUserIds(int $structureId): array
129    {
130        try {
131            $tableName = $this->getTableName();
132            $sql = "SELECT `user_id` FROM `{$tableName}` WHERE `structure_id` = :sid ORDER BY `user_id` ASC";
133
134            $stmt = $this->pdo->prepare($sql);
135            $stmt->execute([':sid' => $structureId]);
136
137            $ids = [];
138            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
139                $ids[] = (int)$row['user_id'];
140            }
141
142            return $ids;
143        } catch (\Throwable) {
144            return [];
145        }
146    }
147
148    /**
149     * {@inheritdoc}
150     */
151    public function getStructureUsers(int $structureId): array
152    {
153        try {
154            $relTable = $this->getTableName();
155            $userTable = $this->getUserTableName();
156
157            $sql = "SELECT u.id, u.username, u.email,
158                           COALESCE(NULLIF(TRIM(u.c_cn), ''), NULLIF(TRIM(CONCAT(COALESCE(u.first_name, ''), ' ',
159                           COALESCE(u.last_name, ''))), ''), u.username) AS full_name
160                    FROM `{$userTable}` AS u
161                    INNER JOIN `{$relTable}` AS rel ON rel.user_id = u.id
162                    WHERE rel.structure_id = :sid AND u.status = 'active' AND u.special_access = 1
163                    ORDER BY u.id ASC";
164
165            $stmt = $this->pdo->prepare($sql);
166            $stmt->execute([':sid' => $structureId]);
167
168            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
169
170            return is_array($rows) ? $rows : [];
171        } catch (\Throwable) {
172            return [];
173        }
174    }
175
176    /**
177     * {@inheritdoc}
178     */
179    public function countUsersInStructure(int $structureId): int
180    {
181        try {
182            $tableName = $this->getTableName();
183            $sql = "SELECT COUNT(*) FROM `{$tableName}` WHERE `structure_id` = :sid";
184
185            $stmt = $this->pdo->prepare($sql);
186            $stmt->execute([':sid' => $structureId]);
187
188            return (int)$stmt->fetchColumn();
189        } catch (\Throwable) {
190            return 0;
191        }
192    }
193
194    /**
195     * {@inheritdoc}
196     */
197    public function assignUser(int $userId, int $structureId): void
198    {
199        if ($userId <= 0 || $structureId <= 0) {
200            return;
201        }
202
203        try {
204            $tableName = $this->getTableName();
205            $sql = "INSERT IGNORE INTO `{$tableName}` (`user_id`, `structure_id`, `created_at`) "
206                . self::INSERT_VALUES_CLAUSE;
207            $stmt = $this->pdo->prepare($sql);
208            $stmt->execute([
209                ':user_id'      => $userId,
210                ':structure_id' => $structureId,
211                ':created_at'   => date(self::DATETIME_FORMAT),
212            ]);
213        } catch (\Throwable) {
214            // Ignore if structure is disabled or table is missing
215        }
216    }
217
218    /**
219     * {@inheritdoc}
220     */
221    public function removeUser(int $userId, int $structureId): void
222    {
223        try {
224            $tableName = $this->getTableName();
225            $sql = "DELETE FROM `{$tableName}` " . self::WHERE_MEMBERSHIP_CLAUSE;
226            $stmt = $this->pdo->prepare($sql);
227            $stmt->execute([
228                ':user_id'      => $userId,
229                ':structure_id' => $structureId,
230            ]);
231        } catch (\Throwable) {
232            // Ignore if structure is disabled or table is missing
233        }
234    }
235
236    /**
237     * {@inheritdoc}
238     */
239    public function syncUserStructures(int $userId, array $structureIds): void
240    {
241        if ($userId <= 0) {
242            return;
243        }
244
245        $current = $this->getUserStructureIds($userId);
246        $target = array_values(array_unique(array_filter(
247            array_map('intval', $structureIds),
248            static fn(int $id): bool => $id > 0
249        )));
250
251        $toAdd = array_diff($target, $current);
252        $toRemove = array_diff($current, $target);
253
254        try {
255            $tableName = $this->getTableName();
256            if ($toRemove !== []) {
257                $delSql = "DELETE FROM `{$tableName}` " . self::WHERE_MEMBERSHIP_CLAUSE;
258                $delStmt = $this->pdo->prepare($delSql);
259                foreach ($toRemove as $remSid) {
260                    $delStmt->execute([
261                        ':user_id'      => $userId,
262                        ':structure_id' => $remSid,
263                    ]);
264                }
265            }
266
267            if ($toAdd !== []) {
268                $insSql = "INSERT IGNORE INTO `{$tableName}` (`user_id`, `structure_id`, `created_at`) "
269                    . self::INSERT_VALUES_CLAUSE;
270                $insStmt = $this->pdo->prepare($insSql);
271                $now = date(self::DATETIME_FORMAT);
272                foreach ($toAdd as $addSid) {
273                    $insStmt->execute([
274                        ':user_id'      => $userId,
275                        ':structure_id' => $addSid,
276                        ':created_at'   => $now,
277                    ]);
278                }
279            }
280        } catch (\Throwable) {
281            // Ignore if table is missing
282        }
283    }
284
285    /**
286     * {@inheritdoc}
287     */
288    public function syncStructureUsers(int $structureId, array $userIds): void
289    {
290        if ($structureId <= 0) {
291            return;
292        }
293
294        $current = $this->getStructureUserIds($structureId);
295        $target = array_values(array_unique(array_filter(
296            array_map('intval', $userIds),
297            static fn(int $id): bool => $id > 0
298        )));
299
300        $toAdd = array_diff($target, $current);
301        $toRemove = array_diff($current, $target);
302
303        try {
304            $tableName = $this->getTableName();
305            if ($toRemove !== []) {
306                $delSql = "DELETE FROM `{$tableName}` " . self::WHERE_MEMBERSHIP_CLAUSE;
307                $delStmt = $this->pdo->prepare($delSql);
308                foreach ($toRemove as $remUid) {
309                    $delStmt->execute([
310                        ':user_id'      => $remUid,
311                        ':structure_id' => $structureId,
312                    ]);
313                }
314            }
315
316            if ($toAdd !== []) {
317                $insSql = "INSERT IGNORE INTO `{$tableName}` (`user_id`, `structure_id`, `created_at`) "
318                    . self::INSERT_VALUES_CLAUSE;
319                $insStmt = $this->pdo->prepare($insSql);
320                $now = date(self::DATETIME_FORMAT);
321                foreach ($toAdd as $addUid) {
322                    $insStmt->execute([
323                        ':user_id'      => $addUid,
324                        ':structure_id' => $structureId,
325                        ':created_at'   => $now,
326                    ]);
327                }
328            }
329        } catch (\Throwable) {
330            // Ignore if table is missing
331        }
332    }
333}