Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
99.47%
186 / 187
100.00%
8 / 8
 CRAP
100.00%
1 / 1
UniversalQueryBuilder
100.00%
186 / 186
100.00%
8 / 8
 19
100.00%
1 / 1
 __construct
100.00%
9 / 9
100.00%
1 / 1
 1
 buildAndExecute
100.00%
70 / 70
100.00%
1 / 1
 3
 fetchDataRows
100.00%
14 / 14
100.00%
1 / 1
 2
 fetchOrderedIds
100.00%
36 / 36
100.00%
1 / 1
 2
 resolveLimitOffset
100.00%
3 / 3
100.00%
1 / 1
 1
 resolveOrderClause
100.00%
3 / 3
100.00%
1 / 1
 2
 findRecordPosition
100.00%
29 / 29
100.00%
1 / 1
 5
 resolveQueryContext
100.00%
22 / 22
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\Core\Engine\Application\Query;
8
9defined('AMMONLY_APP') || exit('Direct script access is forbidden.');
10
11use App\Core\Engine\Application\Query\Clause\UniversalFilterClauseBuilder;
12use App\Core\Engine\Application\Query\Clause\UniversalJoinClauseBuilder;
13use App\Core\Engine\Domain\Model\FieldMetadata;
14use App\Core\Engine\Domain\Model\FilterMetadata;
15use App\Core\Engine\Domain\Model\ModuleMetadata;
16use App\Core\Engine\Domain\Model\PermissionContext;
17use App\Core\Grid\GridRequest;
18use App\Core\Grid\GridResult;
19use PDO;
20
21/**
22 * Universal Query Builder.
23 *
24 * Dynamically constructs and executes SQL SELECT queries for any module
25 * based on FieldMetadata definitions and FilterMetadata column visibility.
26 * Zero SELECT * - all columns are explicitly listed.
27 *
28 * @package App\Core\Engine\Application\Query
29 */
30final readonly class UniversalQueryBuilder
31{
32    private const string FORMAT_FROM_ALIAS = '`%s` `%s`';
33    private const string FORMAT_COL_ALIAS = '`%s`.`%s`';
34    private const string FORMAT_ORDER_COMPOSITE = '%s %s, %s %s';
35    private const string FORMAT_ORDER_SINGLE = '%s %s';
36
37    private UniversalFilterClauseBuilder $filterBuilder;
38    private UniversalJoinClauseBuilder $joinBuilder;
39    private EssentialProjectionResolver $projectionResolver;
40    private QueryStatusCountAggregator $countAggregator;
41
42    /**
43     * UniversalQueryBuilder constructor.
44     *
45     * @param PDO                               $pdo                Database connection.
46     * @param RelationResolver                  $relationResolver   Auto-JOIN generator for FK fields.
47     * @param UniversalFilterClauseBuilder|null $filterBuilder      Filter clause builder helper.
48     * @param UniversalJoinClauseBuilder|null   $joinBuilder        Join clause builder helper.
49     * @param EssentialProjectionResolver|null  $projectionResolver Projection and feature helper.
50     * @param QueryStatusCountAggregator|null   $countAggregator    Counting and status breakdown helper.
51     */
52    public function __construct(
53        private PDO                          $pdo,
54        private RelationResolver             $relationResolver,
55        ?UniversalFilterClauseBuilder        $filterBuilder = null,
56        ?UniversalJoinClauseBuilder          $joinBuilder = null,
57        ?EssentialProjectionResolver         $projectionResolver = null,
58        ?QueryStatusCountAggregator          $countAggregator = null,
59    ) {
60        $this->filterBuilder      = $filterBuilder ?? new UniversalFilterClauseBuilder();
61        $this->joinBuilder        = $joinBuilder ?? new UniversalJoinClauseBuilder();
62        $this->projectionResolver = $projectionResolver ?? new EssentialProjectionResolver($this->pdo);
63        $this->countAggregator    = $countAggregator ?? new QueryStatusCountAggregator(
64            $this->pdo,
65            $this->relationResolver,
66            $this->filterBuilder,
67            $this->projectionResolver
68        );
69    }
70
71    /**
72     * Executes a paginated list query and returns a typed GridResult.
73     *
74     * @param ModuleMetadata            $module      Module metadata with table info.
75     * @param array<int, FieldMetadata> $fields      All field definitions for the module.
76     * @param FilterMetadata            $filter      Active filter defining visible columns.
77     * @param GridRequest               $gridRequest Pagination, sort, and filter parameters.
78     * @param PermissionContext         $context     Security context for owner scope.
79     * @param bool                      $applyOwner  Whether to apply owner scope filter.
80     * @return GridResult Paginated query result with records and metadata.
81     */
82    public function buildAndExecute(
83        ModuleMetadata    $module,
84        array             $fields,
85        FilterMetadata    $filter,
86        GridRequest       $gridRequest,
87        PermissionContext $context,
88        bool              $applyOwner,
89    ): GridResult {
90        $visibleFields = $this->joinBuilder->filterVisible($fields, $filter);
91        $selectExprs   = $this->joinBuilder->buildSelectExpressions($visibleFields);
92        $fromSql       = sprintf(self::FORMAT_FROM_ALIAS, $module->tableName, $module->tableAlias);
93
94        [$whereSql, $params] = $this->filterBuilder->buildWhere(
95            $module,
96            $fields,
97            $filter,
98            $gridRequest,
99            $context,
100            $applyOwner
101        );
102
103        $sortColumn = $this->joinBuilder->resolveSortColumn($module, $fields, $filter, $gridRequest);
104        $sortOrder  = $this->joinBuilder->resolveSortOrder($filter, $gridRequest);
105
106        $neededRelFields = $this->joinBuilder->filterRequiredRelationalFields(
107            $fields,
108            $visibleFields,
109            $whereSql,
110            $sortColumn
111        );
112        $joinClause  = $this->relationResolver->buildJoins($neededRelFields, $module->tableAlias);
113        $joinSelects = $this->relationResolver->buildSelectExpressions($visibleFields);
114
115        if ($joinSelects !== []) {
116            $selectExprs = array_merge($selectExprs, $joinSelects);
117        }
118
119        $selectExprs = $this->projectionResolver->ensureEssentialSelects(
120            $selectExprs,
121            $module,
122            $fields,
123            $visibleFields,
124            $context
125        );
126        $selectSql = implode(', ', $selectExprs);
127
128        [$total, $statusCounts] = $this->countAggregator->resolveCounts(
129            $module,
130            $fields,
131            $filter,
132            $gridRequest,
133            [
134                'context'   => $context,
135                'applyOwner'=> $applyOwner,
136                'relFields' => $neededRelFields,
137            ],
138            [
139                'from'   => $fromSql,
140                'where'  => $whereSql,
141                'params' => $params,
142            ]
143        );
144
145        [$limit, $offset] = $this->resolveLimitOffset($gridRequest);
146
147        $pkCol    = $module->primaryKey !== '' ? $module->primaryKey : 'id';
148        $pkExpr   = sprintf(self::FORMAT_COL_ALIAS, $module->tableAlias, $pkCol);
149        $orderSql = $this->resolveOrderClause($sortColumn, $sortOrder, $pkExpr);
150
151        $rows = $this->fetchDataRows(
152            [
153                'select' => $selectSql,
154                'from'   => $fromSql,
155                'join'   => $joinClause,
156                'where'  => $whereSql,
157                'order'  => $orderSql,
158            ],
159            $limit,
160            $offset,
161            $params
162        );
163
164        return new GridResult(
165            rows:         $rows,
166            totalRecords: $total,
167            gridRequest:  $gridRequest,
168            columns:      [],
169            statusCounts: $statusCounts,
170        );
171    }
172
173    /**
174     * Executes data query and fetches rows.
175     *
176     * @param array{select: string, from: string, join: string, where: string, order: string} $clauses
177     * @param array<string, mixed> $params
178     * @return array<int, array<string, mixed>>
179     */
180    private function fetchDataRows(
181        array $clauses,
182        int $limit,
183        int $offset,
184        array $params
185    ): array {
186        $dataSql = sprintf(
187            'SELECT %s FROM %s %s %s ORDER BY %s LIMIT %d OFFSET %d',
188            $clauses['select'],
189            $clauses['from'],
190            $clauses['join'],
191            $clauses['where'],
192            $clauses['order'],
193            $limit,
194            $offset,
195        );
196
197        $dataStmt = $this->pdo->prepare($dataSql);
198        $dataStmt->execute($params);
199        $rows = $dataStmt->fetchAll(PDO::FETCH_ASSOC);
200
201        return is_array($rows) ? $rows : [];
202    }
203
204    /**
205     * Executes a lightweight query returning only record IDs and total count for navigation windows.
206     *
207     * @param ModuleMetadata            $module      Module metadata with table info.
208     * @param array<int, FieldMetadata> $fields      All field definitions for the module.
209     * @param FilterMetadata            $filter      Active filter defining visible columns.
210     * @param GridRequest               $gridRequest Pagination, sort, and filter parameters.
211     * @param PermissionContext         $context     Security context for owner scope.
212     * @param bool                      $applyOwner  Whether to apply owner scope filter.
213     * @return array{ids: array<int, int>, total: int} Ordered IDs and total record count.
214     */
215    public function fetchOrderedIds(
216        ModuleMetadata    $module,
217        array             $fields,
218        FilterMetadata    $filter,
219        GridRequest       $gridRequest,
220        PermissionContext $context,
221        bool              $applyOwner,
222    ): array {
223        [
224            $fromSql,
225            $whereSql,
226            $params,
227            $sortColumn,
228            $sortOrder,
229            $joinClause,
230            $neededRelFields
231        ] = $this->resolveQueryContext($module, $fields, $filter, $gridRequest, $context, $applyOwner);
232
233        $countJoinFields = array_filter(
234            $neededRelFields,
235            static fn(FieldMetadata $f): bool => str_contains($whereSql, 'rel_' . $f->fieldKey)
236        );
237        $countJoinSql = $this->relationResolver->buildJoins($countJoinFields, $module->tableAlias);
238        $total = $this->countAggregator->executeCountQuery($fromSql, $countJoinSql, $whereSql, $params);
239        [$limit, $offset] = $this->resolveLimitOffset($gridRequest);
240
241        $pkCol    = $module->primaryKey !== '' ? $module->primaryKey : 'id';
242        $pkExpr   = sprintf(self::FORMAT_COL_ALIAS, $module->tableAlias, $pkCol);
243        $orderSql = $this->resolveOrderClause($sortColumn, $sortOrder, $pkExpr);
244
245        $idSql = sprintf(
246            'SELECT %s AS `id` FROM %s %s %s ORDER BY %s LIMIT %d OFFSET %d',
247            $pkExpr,
248            $fromSql,
249            $joinClause,
250            $whereSql,
251            $orderSql,
252            $limit,
253            $offset,
254        );
255
256        $stmt = $this->pdo->prepare($idSql);
257        $stmt->execute($params);
258        /** @var array<int, int|string> $rows */
259        $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);
260
261        return [
262            'ids'   => array_map('intval', $rows),
263            'total' => $total,
264        ];
265    }
266
267    /**
268     * Resolves limit and offset integers from GridRequest.
269     *
270     * @return array{0: int, 1: int} [limit, offset] tuple.
271     */
272    private function resolveLimitOffset(GridRequest $gridRequest): array
273    {
274        $limit  = max(1, min(200, $gridRequest->limit));
275        $offset = max(0, ($gridRequest->page - 1) * $limit);
276        return [$limit, $offset];
277    }
278
279    /**
280     * Constructs SQL ORDER BY clause.
281     */
282    private function resolveOrderClause(string $sortColumn, string $sortOrder, string $pkExpr): string
283    {
284        return $sortColumn !== $pkExpr
285            ? sprintf(self::FORMAT_ORDER_COMPOSITE, $sortColumn, $sortOrder, $pkExpr, $sortOrder)
286            : sprintf(self::FORMAT_ORDER_SINGLE, $sortColumn, $sortOrder);
287    }
288
289    /**
290     * Resolves the 1-based sequential position of a record in the current filtered/sorted query.
291     *
292     * @param ModuleMetadata        $module      Target module metadata.
293     * @param array<FieldMetadata>  $fields      Field metadata list.
294     * @param FilterMetadata        $filter      Active filter metadata.
295     * @param GridRequest           $gridRequest Grid pagination, sort, and search parameters.
296     * @param PermissionContext     $context     User permission and tenancy context.
297     * @param bool                  $applyOwner  Whether owner filtering is enforced.
298     * @param int                   $recordId    Target record identifier.
299     * @return int 1-based position if found, 0 otherwise.
300     */
301    public function findRecordPosition(
302        ModuleMetadata    $module,
303        array             $fields,
304        FilterMetadata    $filter,
305        GridRequest       $gridRequest,
306        PermissionContext $context,
307        bool              $applyOwner,
308        int               $recordId,
309    ): int {
310        [
311            $fromSql,
312            $whereSql,
313            $params,
314            $sortColumn,
315            $sortOrder,
316            $joinClause
317        ] = $this->resolveQueryContext($module, $fields, $filter, $gridRequest, $context, $applyOwner);
318
319        $pkCol    = $module->primaryKey !== '' ? $module->primaryKey : 'id';
320        $pkExpr   = sprintf('`%s`.`%s`', $module->tableAlias, $pkCol);
321        $orderSql = $this->resolveOrderClause($sortColumn, $sortOrder, $pkExpr);
322        if (str_contains($orderSql, 'is_favorite')) {
323            $orderSql = $pkExpr . ' ' . $sortOrder;
324        }
325
326        $sql = sprintf(
327            'SELECT `row_num` FROM (' .
328            'SELECT %s AS `id`, ROW_NUMBER() OVER (ORDER BY %s) AS `row_num` ' .
329            'FROM %s %s %s' .
330            ') AS `ranked` WHERE `id` = :target_record_id LIMIT 1',
331            $pkExpr,
332            $orderSql,
333            $fromSql,
334            $joinClause,
335            $whereSql,
336        );
337
338        $params['target_record_id'] = $recordId;
339
340        $stmt = $this->pdo->prepare($sql);
341        $stmt->execute($params);
342        $pos = $stmt->fetchColumn();
343
344        return $pos !== false && $pos !== null ? (int) $pos : 0;
345    }
346
347    /**
348     * Resolves shared query structure, filtering, sorting and relations.
349     *
350     * @param ModuleMetadata       $module      Target module.
351     * @param array<FieldMetadata> $fields      Field metadata list.
352     * @param FilterMetadata       $filter      Active filter.
353     * @param GridRequest          $gridRequest Grid request parameters.
354     * @param PermissionContext    $context     Security context.
355     * @param bool                 $applyOwner  Owner filter enforcement.
356     * @return array{
357     *     0: string,
358     *     1: string,
359     *     2: array<string, mixed>,
360     *     3: string,
361     *     4: string,
362     *     5: string,
363     *     6: array<FieldMetadata>
364     * }
365     */
366    private function resolveQueryContext(
367        ModuleMetadata    $module,
368        array             $fields,
369        FilterMetadata    $filter,
370        GridRequest       $gridRequest,
371        PermissionContext $context,
372        bool              $applyOwner,
373    ): array {
374        $fromSql = sprintf(self::FORMAT_FROM_ALIAS, $module->tableName, $module->tableAlias);
375        [$whereSql, $params] = $this->filterBuilder->buildWhere(
376            $module,
377            $fields,
378            $filter,
379            $gridRequest,
380            $context,
381            $applyOwner
382        );
383
384        $sortColumn = $this->joinBuilder->resolveSortColumn($module, $fields, $filter, $gridRequest);
385        $sortOrder  = $this->joinBuilder->resolveSortOrder($filter, $gridRequest);
386
387        $neededRelFields = array_values(array_filter(
388            $fields,
389            static function (FieldMetadata $f) use ($whereSql, $sortColumn): bool {
390                if (!$f->hasRelation()) {
391                    return false;
392                }
393                $joinAlias = 'rel_' . $f->fieldKey;
394                return str_contains($whereSql, $joinAlias) || str_contains($sortColumn, $joinAlias);
395            }
396        ));
397        $joinClause = $this->relationResolver->buildJoins($neededRelFields, $module->tableAlias);
398
399        return [$fromSql, $whereSql, $params, $sortColumn, $sortOrder, $joinClause, $neededRelFields];
400    }
401}