Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
34.19%
280 / 819
50.00%
2 / 4
 CRAP
0.00%
0 / 1
Migration420Alpha
34.19%
280 / 819
50.00%
2 / 4
 1086.16
0.00%
0 / 1
 getVersion
100.00%
1 / 1
100.00%
1 / 1
 1
 getDependencies
100.00%
1 / 1
100.00%
1 / 1
 1
 getDescription
0.00%
0 / 1
0.00%
0 / 1
 2
 up
34.07%
278 / 816
0.00%
0 / 1
 988.16
1<?php
2
3/**
4 * Migration for phpMyFAQ 4.2.0-alpha.
5 *
6 * This Source Code Form is subject to the terms of the Mozilla Public License,
7 * v. 2.0. If a copy of the MPL was not distributed with this file, You can
8 * obtain one at https://mozilla.org/MPL/2.0/.
9 *
10 * @package   phpMyFAQ
11 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
12 * @copyright 2023-2026 phpMyFAQ Team
13 * @license   https://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0
14 * @link      https://www.phpmyfaq.de
15 * @since     2026-01-25
16 */
17
18declare(strict_types=1);
19
20namespace phpMyFAQ\Setup\Migration\Versions;
21
22use phpMyFAQ\Enums\PermissionType;
23use phpMyFAQ\Setup\Migration\AbstractMigration;
24use phpMyFAQ\Setup\Migration\Operations\OperationRecorder;
25
26readonly class Migration420Alpha extends AbstractMigration
27{
28    public function getVersion(): string
29    {
30        return '4.2.0-alpha';
31    }
32
33    public function getDependencies(): array
34    {
35        return ['4.1.0-alpha.3'];
36    }
37
38    public function getDescription(): string
39    {
40        return 'Admin log hash columns, custom pages, chat messages, translation config, API rate limiting, queue jobs, mail provider config, API keys, OAuth2 tables, Keycloak subject storage, granular group-based category permissions, per-admin dashboard layouts';
41    }
42
43    /* @mago-expect lint:halstead - one migration step per schema change across four SQL dialects */
44    public function up(OperationRecorder $recorder): void
45    {
46        // Add hash columns to faqadminlog
47        if ($this->isMySql()) {
48            $recorder->addSql(
49                sprintf('ALTER TABLE %sfaqadminlog ADD COLUMN hash VARCHAR(64) AFTER text', $this->tablePrefix),
50                'Add hash column to faqadminlog (MySQL)',
51            );
52
53            $recorder->addSql(
54                sprintf(
55                    'ALTER TABLE %sfaqadminlog ADD COLUMN previous_hash VARCHAR(64) AFTER hash',
56                    $this->tablePrefix,
57                ),
58                'Add previous_hash column to faqadminlog (MySQL)',
59            );
60        }
61
62        if (!$this->isMySql() && $this->isSqlServer()) {
63            $recorder->addSql(
64                sprintf('ALTER TABLE %sfaqadminlog ADD hash VARCHAR(64)', $this->tablePrefix),
65                'Add hash column to faqadminlog (SQL Server)',
66            );
67
68            $recorder->addSql(
69                sprintf('ALTER TABLE %sfaqadminlog ADD previous_hash VARCHAR(64)', $this->tablePrefix),
70                'Add previous_hash column to faqadminlog (SQL Server)',
71            );
72        }
73
74        if (!$this->isMySql() && !$this->isSqlServer()) {
75            $recorder->addSql(
76                sprintf('ALTER TABLE %sfaqadminlog ADD COLUMN hash VARCHAR(64)', $this->tablePrefix),
77                'Add hash column to faqadminlog',
78            );
79
80            $recorder->addSql(
81                sprintf('ALTER TABLE %sfaqadminlog ADD COLUMN previous_hash VARCHAR(64)', $this->tablePrefix),
82                'Add previous_hash column to faqadminlog',
83            );
84        }
85
86        $recorder->addSql($this->createIndex('faqadminlog', 'idx_hash', 'hash'), 'Create hash index on faqadminlog');
87
88        // Create custom pages table
89        if ($this->isMySql()) {
90            $recorder->addSql(sprintf(
91                "CREATE TABLE IF NOT EXISTS %sfaqcustompages (
92                    id INT(11) NOT NULL,
93                    lang VARCHAR(5) NOT NULL,
94                    page_title VARCHAR(255) NOT NULL,
95                    slug VARCHAR(255) NOT NULL,
96                    content TEXT NOT NULL,
97                    author_name VARCHAR(255) NOT NULL,
98                    author_email VARCHAR(255) NOT NULL,
99                    active CHAR(1) NOT NULL DEFAULT 'n',
100                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
101                    updated TIMESTAMP NULL,
102                    seo_title VARCHAR(60) NULL,
103                    seo_description VARCHAR(160) NULL,
104                    seo_robots VARCHAR(50) NOT NULL DEFAULT 'index,follow',
105                    PRIMARY KEY (id, lang),
106                    INDEX idx_custompages_slug (slug, lang)
107                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB",
108                $this->tablePrefix,
109            ), 'Create custom pages table (MySQL)');
110        }
111
112        if (!$this->isMySql() && $this->isPostgreSql()) {
113            $recorder->addSql(sprintf(
114                "CREATE TABLE IF NOT EXISTS %sfaqcustompages (
115                    id INTEGER NOT NULL,
116                    lang VARCHAR(5) NOT NULL,
117                    page_title VARCHAR(255) NOT NULL,
118                    slug VARCHAR(255) NOT NULL,
119                    content TEXT NOT NULL,
120                    author_name VARCHAR(255) NOT NULL,
121                    author_email VARCHAR(255) NOT NULL,
122                    active CHAR(1) NOT NULL DEFAULT 'n',
123                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
124                    updated TIMESTAMP NULL,
125                    seo_title VARCHAR(60) NULL,
126                    seo_description VARCHAR(160) NULL,
127                    seo_robots VARCHAR(50) NOT NULL DEFAULT 'index,follow',
128                    PRIMARY KEY (id, lang)
129                )",
130                $this->tablePrefix,
131            ), 'Create custom pages table (PostgreSQL)');
132
133            $recorder->addSql(
134                sprintf(
135                    'CREATE INDEX IF NOT EXISTS idx_custompages_slug ON %sfaqcustompages (slug, lang)',
136                    $this->tablePrefix,
137                ),
138                'Create slug index on custom pages (PostgreSQL)',
139            );
140        }
141
142        if (!$this->isMySql() && !$this->isPostgreSql() && $this->isSqlite()) {
143            $recorder->addSql(sprintf("CREATE TABLE IF NOT EXISTS %sfaqcustompages (
144                    id INTEGER NOT NULL,
145                    lang VARCHAR(5) NOT NULL,
146                    page_title VARCHAR(255) NOT NULL,
147                    slug VARCHAR(255) NOT NULL,
148                    content TEXT NOT NULL,
149                    author_name VARCHAR(255) NOT NULL,
150                    author_email VARCHAR(255) NOT NULL,
151                    active CHAR(1) NOT NULL DEFAULT 'n',
152                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
153                    updated TIMESTAMP NULL,
154                    seo_title VARCHAR(60) NULL,
155                    seo_description VARCHAR(160) NULL,
156                    seo_robots VARCHAR(50) NOT NULL DEFAULT 'index,follow',
157                    PRIMARY KEY (id, lang)
158                )", $this->tablePrefix), 'Create custom pages table (SQLite)');
159
160            $recorder->addSql(
161                sprintf(
162                    'CREATE INDEX IF NOT EXISTS idx_custompages_slug ON %sfaqcustompages (slug, lang)',
163                    $this->tablePrefix,
164                ),
165                'Create slug index on custom pages (SQLite)',
166            );
167        }
168
169        if (!$this->isMySql() && !$this->isPostgreSql() && !$this->isSqlite() && $this->isSqlServer()) {
170            $recorder->addSql(
171                sprintf(
172                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqcustompages') AND type = 'U') "
173                    . "CREATE TABLE %sfaqcustompages (
174                    id INT NOT NULL,
175                    lang VARCHAR(5) NOT NULL,
176                    page_title VARCHAR(255) NOT NULL,
177                    slug VARCHAR(255) NOT NULL,
178                    content NVARCHAR(MAX) NOT NULL,
179                    author_name VARCHAR(255) NOT NULL,
180                    author_email VARCHAR(255) NOT NULL,
181                    active CHAR(1) NOT NULL DEFAULT 'n',
182                    created DATETIME NOT NULL DEFAULT GETDATE(),
183                    updated DATETIME NULL,
184                    seo_title VARCHAR(60) NULL,
185                    seo_description VARCHAR(160) NULL,
186                    seo_robots VARCHAR(50) NOT NULL DEFAULT 'index,follow',
187                    PRIMARY KEY (id, lang)
188                )",
189                    $this->tablePrefix,
190                    $this->tablePrefix,
191                ),
192                'Create custom pages table (SQL Server)',
193            );
194
195            $recorder->addSql(
196                sprintf(
197                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_custompages_slug'"
198                    . " AND object_id = OBJECT_ID(N'%sfaqcustompages'))"
199                    . ' CREATE INDEX idx_custompages_slug ON %sfaqcustompages (slug, lang)',
200                    $this->tablePrefix,
201                    $this->tablePrefix,
202                ),
203                'Create slug index on custom pages (SQL Server)',
204            );
205        }
206
207        // Add new permissions for custom pages
208        $recorder->grantPermission(PermissionType::PAGE_ADD->value, 'Right to add custom pages');
209        $recorder->grantPermission(PermissionType::PAGE_EDIT->value, 'Right to edit custom pages');
210        $recorder->grantPermission(PermissionType::PAGE_DELETE->value, 'Right to delete custom pages');
211
212        // Add configuration entries
213        $recorder->addConfig('main.termsURL', '');
214        $recorder->addConfig('main.imprintURL', '');
215        $recorder->addConfig('main.cookiePolicyURL', '');
216        $recorder->addConfig('main.accessibilityStatementURL', '');
217        $recorder->addConfig('api.onlyActiveFaqs', 'true');
218        $recorder->addConfig('api.onlyActiveCategories', 'true');
219        $recorder->addConfig('api.onlyPublicQuestions', 'true');
220        $recorder->addConfig('api.ignoreOrphanedFaqs', 'true');
221        $recorder->addConfig('api.rateLimit.requests', '100');
222
223        // Add durable Keycloak subject storage to user data
224        $columnType = $this->isSqlServer() ? 'NVARCHAR(255) NULL' : 'VARCHAR(255) NULL';
225
226        $recorder->addSql(
227            $this->addColumn('faquserdata', 'keycloak_sub', $columnType),
228            'Add keycloak_sub column to faquserdata',
229        );
230
231        $recorder->addSql(
232            $this->createUniqueIndex('faquserdata', 'idx_faquserdata_keycloak_sub', 'keycloak_sub'),
233            'Create unique keycloak_sub index on faquserdata',
234        );
235        $recorder->addConfig('api.rateLimit.interval', '3600');
236        $recorder->addConfig('queue.transport', 'database');
237        $recorder->addConfig('session.handler', 'files');
238        $recorder->addConfig('session.redisDsn', 'tcp://redis:6379?database=0');
239        $recorder->addConfig('mail.useQueue', 'true');
240        $recorder->addConfig('mail.provider', 'smtp');
241        $recorder->addConfig('mail.sendgridApiKey', '');
242        $recorder->addConfig('mail.sesAccessKeyId', '');
243        $recorder->addConfig('mail.sesSecretAccessKey', '');
244        $recorder->addConfig('mail.sesRegion', '');
245        $recorder->addConfig('mail.mailgunApiKey', '');
246        $recorder->addConfig('mail.mailgunDomain', '');
247        $recorder->addConfig('mail.mailgunRegion', 'eu');
248
249        // Translation service configuration
250        $recorder->addConfig('translation.provider', 'none');
251        $recorder->addConfig('translation.googleApiKey', '');
252        $recorder->addConfig('translation.deeplApiKey', '');
253        $recorder->addConfig('translation.deeplUseFreeApi', 'true');
254        $recorder->addConfig('translation.azureKey', '');
255        $recorder->addConfig('translation.azureRegion', '');
256        $recorder->addConfig('translation.amazonAccessKeyId', '');
257        $recorder->addConfig('translation.amazonSecretAccessKey', '');
258        $recorder->addConfig('translation.amazonRegion', 'us-east-1');
259        $recorder->addConfig('translation.libreTranslateUrl', 'https://libretranslate.com');
260        $recorder->addConfig('translation.libreTranslateApiKey', '');
261
262        $recorder->addConfig('main.enableCommentEditor', 'false');
263
264        // Redis support configuration
265        $recorder->addConfig('storage.useRedisForConfiguration', 'false');
266        $recorder->addConfig('storage.redisDsn', 'tcp://redis:6379?database=1');
267        $recorder->addConfig('storage.redisPrefix', 'pmf:config:');
268        $recorder->addConfig('storage.redisConnectTimeout', '1.0');
269        $recorder->addConfig('storage.cacheAdapter', 'filesystem');
270        $recorder->addConfig('storage.cacheRedisDsn', 'redis://redis:6379/2');
271        $recorder->addConfig('storage.cacheRedisPrefix', 'pmf_cache_');
272        $recorder->addConfig('storage.cacheRedisConnectTimeout', '1.0');
273        $recorder->addConfig('storage.cacheDefaultTtl', '3600');
274
275        // Create the chat messages table
276        if ($this->isMySql()) {
277            $recorder->addSql(sprintf(
278                'CREATE TABLE IF NOT EXISTS %sfaqchat_messages (
279                    id INT(11) NOT NULL AUTO_INCREMENT,
280                    sender_id INT(11) NOT NULL,
281                    recipient_id INT(11) NOT NULL,
282                    message TEXT NOT NULL,
283                    is_read TINYINT(1) NOT NULL DEFAULT 0,
284                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
285                    PRIMARY KEY (id),
286                    INDEX idx_chat_sender (sender_id),
287                    INDEX idx_chat_recipient (recipient_id),
288                    INDEX idx_chat_conversation (sender_id, recipient_id),
289                    INDEX idx_chat_created (created_at)
290                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
291                $this->tablePrefix,
292            ), 'Create chat messages table (MySQL)');
293        }
294
295        if (!$this->isMySql() && $this->isPostgreSql()) {
296            $recorder->addSql(sprintf(
297                'CREATE TABLE IF NOT EXISTS %sfaqchat_messages (
298                    id SERIAL NOT NULL,
299                    sender_id INTEGER NOT NULL,
300                    recipient_id INTEGER NOT NULL,
301                    message TEXT NOT NULL,
302                    is_read SMALLINT NOT NULL DEFAULT 0,
303                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
304                    PRIMARY KEY (id)
305                )',
306                $this->tablePrefix,
307            ), 'Create chat messages table (PostgreSQL)');
308
309            $recorder->addSql(
310                sprintf(
311                    'CREATE INDEX IF NOT EXISTS idx_chat_sender ON %sfaqchat_messages (sender_id)',
312                    $this->tablePrefix,
313                ),
314                'Create sender index on chat messages (PostgreSQL)',
315            );
316
317            $recorder->addSql(
318                sprintf(
319                    'CREATE INDEX IF NOT EXISTS idx_chat_recipient ON %sfaqchat_messages (recipient_id)',
320                    $this->tablePrefix,
321                ),
322                'Create recipient index on chat messages (PostgreSQL)',
323            );
324
325            $recorder->addSql(
326                sprintf(
327                    'CREATE INDEX IF NOT EXISTS idx_chat_conversation ON %sfaqchat_messages (sender_id, recipient_id)',
328                    $this->tablePrefix,
329                ),
330                'Create conversation index on chat messages (PostgreSQL)',
331            );
332
333            $recorder->addSql(
334                sprintf(
335                    'CREATE INDEX IF NOT EXISTS idx_chat_created ON %sfaqchat_messages (created_at)',
336                    $this->tablePrefix,
337                ),
338                'Create created_at index on chat messages (PostgreSQL)',
339            );
340        }
341
342        if (!$this->isMySql() && !$this->isPostgreSql() && $this->isSqlite()) {
343            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqchat_messages (
344                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
345                    sender_id INTEGER NOT NULL,
346                    recipient_id INTEGER NOT NULL,
347                    message TEXT NOT NULL,
348                    is_read INTEGER NOT NULL DEFAULT 0,
349                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
350                )', $this->tablePrefix), 'Create chat messages table (SQLite)');
351
352            $recorder->addSql(
353                sprintf(
354                    'CREATE INDEX IF NOT EXISTS idx_chat_sender ON %sfaqchat_messages (sender_id)',
355                    $this->tablePrefix,
356                ),
357                'Create sender index on chat messages (SQLite)',
358            );
359
360            $recorder->addSql(
361                sprintf(
362                    'CREATE INDEX IF NOT EXISTS idx_chat_recipient ON %sfaqchat_messages (recipient_id)',
363                    $this->tablePrefix,
364                ),
365                'Create recipient index on chat messages (SQLite)',
366            );
367
368            $recorder->addSql(
369                sprintf(
370                    'CREATE INDEX IF NOT EXISTS idx_chat_conversation ON %sfaqchat_messages (sender_id, recipient_id)',
371                    $this->tablePrefix,
372                ),
373                'Create conversation index on chat messages (SQLite)',
374            );
375
376            $recorder->addSql(
377                sprintf(
378                    'CREATE INDEX IF NOT EXISTS idx_chat_created ON %sfaqchat_messages (created_at)',
379                    $this->tablePrefix,
380                ),
381                'Create created_at index on chat messages (SQLite)',
382            );
383        }
384
385        if (!$this->isMySql() && !$this->isPostgreSql() && !$this->isSqlite() && $this->isSqlServer()) {
386            $recorder->addSql(
387                sprintf(
388                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqchat_messages') AND type = 'U') "
389                    . 'CREATE TABLE %sfaqchat_messages (
390                    id INT IDENTITY(1,1) NOT NULL,
391                    sender_id INT NOT NULL,
392                    recipient_id INT NOT NULL,
393                    message NVARCHAR(MAX) NOT NULL,
394                    is_read TINYINT NOT NULL DEFAULT 0,
395                    created_at DATETIME NOT NULL DEFAULT GETDATE(),
396                    PRIMARY KEY (id)
397                )',
398                    $this->tablePrefix,
399                    $this->tablePrefix,
400                ),
401                'Create chat messages table (SQL Server)',
402            );
403
404            $recorder->addSql(
405                sprintf(
406                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_chat_sender'"
407                    . " AND object_id = OBJECT_ID(N'%sfaqchat_messages'))"
408                    . ' CREATE INDEX idx_chat_sender ON %sfaqchat_messages (sender_id)',
409                    $this->tablePrefix,
410                    $this->tablePrefix,
411                ),
412                'Create sender index on chat messages (SQL Server)',
413            );
414
415            $recorder->addSql(
416                sprintf(
417                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_chat_recipient'"
418                    . " AND object_id = OBJECT_ID(N'%sfaqchat_messages'))"
419                    . ' CREATE INDEX idx_chat_recipient ON %sfaqchat_messages (recipient_id)',
420                    $this->tablePrefix,
421                    $this->tablePrefix,
422                ),
423                'Create recipient index on chat messages (SQL Server)',
424            );
425
426            $recorder->addSql(
427                sprintf(
428                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_chat_conversation'"
429                    . " AND object_id = OBJECT_ID(N'%sfaqchat_messages'))"
430                    . ' CREATE INDEX idx_chat_conversation ON %sfaqchat_messages (sender_id, recipient_id)',
431                    $this->tablePrefix,
432                    $this->tablePrefix,
433                ),
434                'Create conversation index on chat messages (SQL Server)',
435            );
436
437            $recorder->addSql(
438                sprintf(
439                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_chat_created'"
440                    . " AND object_id = OBJECT_ID(N'%sfaqchat_messages'))"
441                    . ' CREATE INDEX idx_chat_created ON %sfaqchat_messages (created_at)',
442                    $this->tablePrefix,
443                    $this->tablePrefix,
444                ),
445                'Create created_at index on chat messages (SQL Server)',
446            );
447        }
448
449        // Create a push subscriptions table
450        if ($this->isMySql()) {
451            $recorder->addSql(sprintf(
452                'CREATE TABLE IF NOT EXISTS %sfaqpush_subscriptions (
453                    id INT(11) NOT NULL AUTO_INCREMENT,
454                    user_id INT(11) NOT NULL,
455                    endpoint TEXT NOT NULL,
456                    endpoint_hash VARCHAR(64) NOT NULL,
457                    public_key TEXT NOT NULL,
458                    auth_token TEXT NOT NULL,
459                    content_encoding VARCHAR(50) NULL,
460                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
461                    PRIMARY KEY (id),
462                    INDEX idx_push_user_id (user_id),
463                    UNIQUE INDEX idx_push_endpoint_hash (endpoint_hash)
464                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
465                $this->tablePrefix,
466            ), 'Create push subscriptions table (MySQL)');
467        }
468
469        if (!$this->isMySql() && $this->isPostgreSql()) {
470            $recorder->addSql(sprintf(
471                'CREATE TABLE IF NOT EXISTS %sfaqpush_subscriptions (
472                    id SERIAL NOT NULL,
473                    user_id INTEGER NOT NULL,
474                    endpoint TEXT NOT NULL,
475                    endpoint_hash VARCHAR(64) NOT NULL,
476                    public_key TEXT NOT NULL,
477                    auth_token TEXT NOT NULL,
478                    content_encoding VARCHAR(50) NULL,
479                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
480                    PRIMARY KEY (id)
481                )',
482                $this->tablePrefix,
483            ), 'Create push subscriptions table (PostgreSQL)');
484
485            $recorder->addSql(
486                sprintf(
487                    'CREATE INDEX IF NOT EXISTS idx_push_user_id ON %sfaqpush_subscriptions (user_id)',
488                    $this->tablePrefix,
489                ),
490                'Create user_id index on push subscriptions (PostgreSQL)',
491            );
492
493            $recorder->addSql(
494                sprintf(
495                    'CREATE UNIQUE INDEX IF NOT EXISTS idx_push_endpoint_hash ON %sfaqpush_subscriptions (endpoint_hash)',
496                    $this->tablePrefix,
497                ),
498                'Create endpoint_hash unique index on push subscriptions (PostgreSQL)',
499            );
500        }
501
502        if (!$this->isMySql() && !$this->isPostgreSql() && $this->isSqlite()) {
503            $recorder->addSql(sprintf(
504                'CREATE TABLE IF NOT EXISTS %sfaqpush_subscriptions (
505                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
506                    user_id INTEGER NOT NULL,
507                    endpoint TEXT NOT NULL,
508                    endpoint_hash VARCHAR(64) NOT NULL,
509                    public_key TEXT NOT NULL,
510                    auth_token TEXT NOT NULL,
511                    content_encoding VARCHAR(50) NULL,
512                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
513                )',
514                $this->tablePrefix,
515            ), 'Create push subscriptions table (SQLite)');
516
517            $recorder->addSql(
518                sprintf(
519                    'CREATE INDEX IF NOT EXISTS idx_push_user_id ON %sfaqpush_subscriptions (user_id)',
520                    $this->tablePrefix,
521                ),
522                'Create user_id index on push subscriptions (SQLite)',
523            );
524
525            $recorder->addSql(
526                sprintf(
527                    'CREATE UNIQUE INDEX IF NOT EXISTS idx_push_endpoint_hash ON %sfaqpush_subscriptions (endpoint_hash)',
528                    $this->tablePrefix,
529                ),
530                'Create endpoint_hash unique index on push subscriptions (SQLite)',
531            );
532        }
533
534        if (!$this->isMySql() && !$this->isPostgreSql() && !$this->isSqlite() && $this->isSqlServer()) {
535            $recorder->addSql(
536                sprintf(
537                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqpush_subscriptions') AND type = 'U') "
538                    . 'CREATE TABLE %sfaqpush_subscriptions (
539                    id INT IDENTITY(1,1) NOT NULL,
540                    user_id INT NOT NULL,
541                    endpoint NVARCHAR(MAX) NOT NULL,
542                    endpoint_hash VARCHAR(64) NOT NULL,
543                    public_key NVARCHAR(MAX) NOT NULL,
544                    auth_token NVARCHAR(MAX) NOT NULL,
545                    content_encoding VARCHAR(50) NULL,
546                    created_at DATETIME NOT NULL DEFAULT GETDATE(),
547                    PRIMARY KEY (id)
548                )',
549                    $this->tablePrefix,
550                    $this->tablePrefix,
551                ),
552                'Create push subscriptions table (SQL Server)',
553            );
554
555            $recorder->addSql(
556                sprintf(
557                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_push_user_id'"
558                    . " AND object_id = OBJECT_ID(N'%sfaqpush_subscriptions'))"
559                    . ' CREATE INDEX idx_push_user_id ON %sfaqpush_subscriptions (user_id)',
560                    $this->tablePrefix,
561                    $this->tablePrefix,
562                ),
563                'Create user_id index on push subscriptions (SQL Server)',
564            );
565
566            $recorder->addSql(
567                sprintf(
568                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_push_endpoint_hash'"
569                    . " AND object_id = OBJECT_ID(N'%sfaqpush_subscriptions'))"
570                    . ' CREATE UNIQUE INDEX idx_push_endpoint_hash ON %sfaqpush_subscriptions (endpoint_hash)',
571                    $this->tablePrefix,
572                    $this->tablePrefix,
573                ),
574                'Create endpoint_hash unique index on push subscriptions (SQL Server)',
575            );
576        }
577
578        // Add Web Push configuration entries
579        $recorder->addConfig('push.enableWebPush', 'false');
580        $recorder->addConfig('push.vapidPublicKey', '');
581        $recorder->addConfig('push.vapidPrivateKey', '');
582        $recorder->addConfig('push.vapidSubject', '');
583
584        // Create a queue jobs table
585        if ($this->isMySql()) {
586            $recorder->addSql(sprintf(
587                "CREATE TABLE IF NOT EXISTS %sfaqjobs (
588                    id INT NOT NULL AUTO_INCREMENT,
589                    queue VARCHAR(100) NOT NULL DEFAULT 'default',
590                    body TEXT NOT NULL,
591                    headers TEXT NULL,
592                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
593                    available_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
594                    delivered_at TIMESTAMP NULL,
595                    PRIMARY KEY (id),
596                    INDEX idx_faqjobs_queue_available (queue, available_at),
597                    INDEX idx_faqjobs_delivered_at (delivered_at)
598                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB",
599                $this->tablePrefix,
600            ), 'Create queue jobs table (MySQL)');
601        }
602
603        if ($this->isPostgreSql()) {
604            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqjobs (
605                    id SERIAL NOT NULL,
606                    queue VARCHAR(100) NOT NULL DEFAULT \'default\',
607                    body TEXT NOT NULL,
608                    headers TEXT NULL,
609                    available_at TIMESTAMP NOT NULL,
610                    delivered_at TIMESTAMP NULL,
611                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
612                    PRIMARY KEY (id)
613                )', $this->tablePrefix), 'Create queue jobs table (PostgreSQL)');
614
615            $recorder->addSql(
616                sprintf(
617                    'CREATE INDEX IF NOT EXISTS idx_faqjobs_queue_available ON %sfaqjobs (queue, available_at)',
618                    $this->tablePrefix,
619                ),
620                'Create queue/available index on queue jobs (PostgreSQL)',
621            );
622
623            $recorder->addSql(
624                sprintf(
625                    'CREATE INDEX IF NOT EXISTS idx_faqjobs_delivered_at ON %sfaqjobs (delivered_at)',
626                    $this->tablePrefix,
627                ),
628                'Create delivered_at index on queue jobs (PostgreSQL)',
629            );
630        }
631
632        if ($this->isSqlite()) {
633            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqjobs (
634                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
635                    queue VARCHAR(100) NOT NULL DEFAULT \'default\',
636                    body TEXT NOT NULL,
637                    headers TEXT NULL,
638                    available_at DATETIME NOT NULL,
639                    delivered_at DATETIME NULL,
640                    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
641                )', $this->tablePrefix), 'Create queue jobs table (SQLite)');
642
643            $recorder->addSql(
644                sprintf(
645                    'CREATE INDEX IF NOT EXISTS idx_faqjobs_queue_available ON %sfaqjobs (queue, available_at)',
646                    $this->tablePrefix,
647                ),
648                'Create queue/available index on queue jobs (SQLite)',
649            );
650
651            $recorder->addSql(
652                sprintf(
653                    'CREATE INDEX IF NOT EXISTS idx_faqjobs_delivered_at ON %sfaqjobs (delivered_at)',
654                    $this->tablePrefix,
655                ),
656                'Create delivered_at index on queue jobs (SQLite)',
657            );
658        }
659
660        if ($this->isSqlServer()) {
661            $recorder->addSql(
662                sprintf(
663                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqjobs') AND type = 'U') "
664                    . "CREATE TABLE %sfaqjobs (
665                    id INT IDENTITY(1,1) NOT NULL,
666                    queue VARCHAR(100) NOT NULL DEFAULT 'default',
667                    body NVARCHAR(MAX) NOT NULL,
668                    headers NVARCHAR(MAX) NULL,
669                    available_at DATETIME NOT NULL,
670                    delivered_at DATETIME NULL,
671                    created DATETIME NOT NULL DEFAULT GETDATE(),
672                    PRIMARY KEY (id)
673                )",
674                    $this->tablePrefix,
675                    $this->tablePrefix,
676                ),
677                'Create queue jobs table (SQL Server)',
678            );
679
680            $recorder->addSql(
681                sprintf(
682                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_faqjobs_queue_available'"
683                    . " AND object_id = OBJECT_ID(N'%sfaqjobs'))"
684                    . ' CREATE INDEX idx_faqjobs_queue_available ON %sfaqjobs (queue, available_at)',
685                    $this->tablePrefix,
686                    $this->tablePrefix,
687                ),
688                'Create queue/available index on queue jobs (SQL Server)',
689            );
690
691            $recorder->addSql(
692                sprintf(
693                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_faqjobs_delivered_at'"
694                    . " AND object_id = OBJECT_ID(N'%sfaqjobs'))"
695                    . ' CREATE INDEX idx_faqjobs_delivered_at ON %sfaqjobs (delivered_at)',
696                    $this->tablePrefix,
697                    $this->tablePrefix,
698                ),
699                'Create delivered_at index on queue jobs (SQL Server)',
700            );
701        }
702
703        // Create API keys table
704        if ($this->isMySql()) {
705            $recorder->addSql(sprintf(
706                'CREATE TABLE IF NOT EXISTS %sfaqapi_keys (
707                    id INT(11) NOT NULL,
708                    user_id INT(11) NOT NULL,
709                    api_key VARCHAR(64) NOT NULL,
710                    name VARCHAR(255) NULL,
711                    scopes TEXT NULL,
712                    last_used_at TIMESTAMP NULL,
713                    expires_at TIMESTAMP NULL,
714                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
715                    PRIMARY KEY (id),
716                    UNIQUE INDEX idx_api_key_unique (api_key),
717                    INDEX idx_api_key_user (user_id)
718                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
719                $this->tablePrefix,
720            ), 'Create API keys table (MySQL)');
721        }
722
723        if ($this->isPostgreSql()) {
724            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqapi_keys (
725                    id INTEGER NOT NULL,
726                    user_id INTEGER NOT NULL,
727                    api_key VARCHAR(64) NOT NULL,
728                    name VARCHAR(255) NULL,
729                    scopes TEXT NULL,
730                    last_used_at TIMESTAMP NULL,
731                    expires_at TIMESTAMP NULL,
732                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
733                    PRIMARY KEY (id)
734                )', $this->tablePrefix), 'Create API keys table (PostgreSQL)');
735
736            $recorder->addSql(
737                sprintf(
738                    'CREATE UNIQUE INDEX IF NOT EXISTS idx_api_key_unique ON %sfaqapi_keys (api_key)',
739                    $this->tablePrefix,
740                ),
741                'Create api_key unique index on API keys (PostgreSQL)',
742            );
743
744            $recorder->addSql(
745                sprintf('CREATE INDEX IF NOT EXISTS idx_api_key_user ON %sfaqapi_keys (user_id)', $this->tablePrefix),
746                'Create user_id index on API keys (PostgreSQL)',
747            );
748        }
749
750        if ($this->isSqlite()) {
751            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqapi_keys (
752                    id INTEGER NOT NULL,
753                    user_id INTEGER NOT NULL,
754                    api_key VARCHAR(64) NOT NULL,
755                    name VARCHAR(255) NULL,
756                    scopes TEXT NULL,
757                    last_used_at DATETIME NULL,
758                    expires_at DATETIME NULL,
759                    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
760                    PRIMARY KEY (id)
761                )', $this->tablePrefix), 'Create API keys table (SQLite)');
762
763            $recorder->addSql(
764                sprintf(
765                    'CREATE UNIQUE INDEX IF NOT EXISTS idx_api_key_unique ON %sfaqapi_keys (api_key)',
766                    $this->tablePrefix,
767                ),
768                'Create api_key unique index on API keys (SQLite)',
769            );
770
771            $recorder->addSql(
772                sprintf('CREATE INDEX IF NOT EXISTS idx_api_key_user ON %sfaqapi_keys (user_id)', $this->tablePrefix),
773                'Create user_id index on API keys (SQLite)',
774            );
775        }
776
777        if ($this->isSqlServer()) {
778            $recorder->addSql(
779                sprintf(
780                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqapi_keys') AND type = 'U') "
781                    . 'CREATE TABLE %sfaqapi_keys (
782                    id INT NOT NULL,
783                    user_id INT NOT NULL,
784                    api_key VARCHAR(64) NOT NULL,
785                    name VARCHAR(255) NULL,
786                    scopes NVARCHAR(MAX) NULL,
787                    last_used_at DATETIME NULL,
788                    expires_at DATETIME NULL,
789                    created DATETIME NOT NULL DEFAULT GETDATE(),
790                    PRIMARY KEY (id)
791                )',
792                    $this->tablePrefix,
793                    $this->tablePrefix,
794                ),
795                'Create API keys table (SQL Server)',
796            );
797
798            $recorder->addSql(
799                sprintf(
800                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_api_key_unique'"
801                    . " AND object_id = OBJECT_ID(N'%sfaqapi_keys'))"
802                    . ' CREATE UNIQUE INDEX idx_api_key_unique ON %sfaqapi_keys (api_key)',
803                    $this->tablePrefix,
804                    $this->tablePrefix,
805                ),
806                'Create api_key unique index on API keys (SQL Server)',
807            );
808
809            $recorder->addSql(
810                sprintf(
811                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_api_key_user'"
812                    . " AND object_id = OBJECT_ID(N'%sfaqapi_keys'))"
813                    . ' CREATE INDEX idx_api_key_user ON %sfaqapi_keys (user_id)',
814                    $this->tablePrefix,
815                    $this->tablePrefix,
816                ),
817                'Create user_id index on API keys (SQL Server)',
818            );
819        }
820
821        // OAuth2 configuration entries
822        $recorder->addConfig('oauth2.enable', 'false');
823        $recorder->addConfig('oauth2.privateKeyPath', '');
824        $recorder->addConfig('oauth2.publicKeyPath', '');
825        $recorder->addConfig('oauth2.encryptionKey', '');
826        $recorder->addConfig('oauth2.accessTokenTTL', 'PT1H');
827        $recorder->addConfig('oauth2.refreshTokenTTL', 'P1M');
828        $recorder->addConfig('oauth2.authCodeTTL', 'PT10M');
829
830        // Keycloak configuration entries
831        $recorder->addConfig('keycloak.enable', 'false');
832        $recorder->addConfig('keycloak.baseUrl', '');
833        $recorder->addConfig('keycloak.realm', '');
834        $recorder->addConfig('keycloak.clientId', '');
835        $recorder->addConfig('keycloak.clientSecret', '');
836        $recorder->addConfig('keycloak.redirectUri', '');
837        $recorder->addConfig('keycloak.scopes', 'openid profile email');
838        $recorder->addConfig('keycloak.autoProvision', 'false');
839        $recorder->addConfig('keycloak.groupAutoAssign', 'false');
840        $recorder->addConfig('keycloak.groupSyncOnLogin', 'false');
841        $recorder->addConfig('keycloak.groupMapping', '');
842        $recorder->addConfig('keycloak.logoutRedirectUrl', '');
843
844        // Recent news widget
845        $recorder->addConfig('main.enableRecentNews', 'true');
846
847        // Layout mode configuration
848        $recorder->addConfig('layout.defaultLayoutMode', 'auto');
849        $recorder->addConfig('layout.allowUserLayoutMode', 'true');
850
851        // OAuth2 storage tables
852        if ($this->isMySql()) {
853            $recorder->addSql(sprintf(
854                'CREATE TABLE IF NOT EXISTS %sfaqoauth_clients (
855                    client_id VARCHAR(80) NOT NULL,
856                    client_secret VARCHAR(255) NULL,
857                    name VARCHAR(255) NOT NULL,
858                    redirect_uri TEXT NULL,
859                    grants VARCHAR(255) NULL,
860                    is_confidential TINYINT(1) NOT NULL DEFAULT 1,
861                    user_id INT(11) NULL,
862                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
863                    PRIMARY KEY (client_id)
864                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
865                $this->tablePrefix,
866            ), 'Create OAuth2 clients table (MySQL)');
867
868            $recorder->addSql(sprintf(
869                'CREATE TABLE IF NOT EXISTS %sfaqoauth_scopes (
870                    scope_id VARCHAR(80) NOT NULL,
871                    description VARCHAR(255) NULL,
872                    PRIMARY KEY (scope_id)
873                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
874                $this->tablePrefix,
875            ), 'Create OAuth2 scopes table (MySQL)');
876
877            $recorder->addSql(sprintf(
878                'CREATE TABLE IF NOT EXISTS %sfaqoauth_access_tokens (
879                    identifier VARCHAR(100) NOT NULL,
880                    client_id VARCHAR(80) NOT NULL,
881                    user_id VARCHAR(80) NULL,
882                    scopes TEXT NULL,
883                    revoked TINYINT(1) NOT NULL DEFAULT 0,
884                    expires_at TIMESTAMP NOT NULL,
885                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
886                    PRIMARY KEY (identifier),
887                    INDEX idx_oauth_access_client (client_id),
888                    INDEX idx_oauth_access_user (user_id)
889                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
890                $this->tablePrefix,
891            ), 'Create OAuth2 access tokens table (MySQL)');
892
893            $recorder->addSql(sprintf(
894                'CREATE TABLE IF NOT EXISTS %sfaqoauth_refresh_tokens (
895                    identifier VARCHAR(100) NOT NULL,
896                    access_token_identifier VARCHAR(100) NOT NULL,
897                    revoked TINYINT(1) NOT NULL DEFAULT 0,
898                    expires_at TIMESTAMP NOT NULL,
899                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
900                    PRIMARY KEY (identifier),
901                    INDEX idx_oauth_refresh_access (access_token_identifier)
902                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
903                $this->tablePrefix,
904            ), 'Create OAuth2 refresh tokens table (MySQL)');
905
906            $recorder->addSql(sprintf(
907                'CREATE TABLE IF NOT EXISTS %sfaqoauth_auth_codes (
908                    identifier VARCHAR(100) NOT NULL,
909                    client_id VARCHAR(80) NOT NULL,
910                    user_id VARCHAR(80) NULL,
911                    redirect_uri TEXT NULL,
912                    scopes TEXT NULL,
913                    revoked TINYINT(1) NOT NULL DEFAULT 0,
914                    expires_at TIMESTAMP NOT NULL,
915                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
916                    PRIMARY KEY (identifier),
917                    INDEX idx_oauth_code_client (client_id),
918                    INDEX idx_oauth_code_user (user_id)
919                ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB',
920                $this->tablePrefix,
921            ), 'Create OAuth2 auth codes table (MySQL)');
922        }
923
924        if ($this->isPostgreSql()) {
925            $recorder->addSql(sprintf(
926                'CREATE TABLE IF NOT EXISTS %sfaqoauth_clients (
927                    client_id VARCHAR(80) NOT NULL,
928                    client_secret VARCHAR(255) NULL,
929                    name VARCHAR(255) NOT NULL,
930                    redirect_uri TEXT NULL,
931                    grants VARCHAR(255) NULL,
932                    is_confidential SMALLINT NOT NULL DEFAULT 1,
933                    user_id INTEGER NULL,
934                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
935                    PRIMARY KEY (client_id)
936                )',
937                $this->tablePrefix,
938            ), 'Create OAuth2 clients table (PostgreSQL)');
939
940            $recorder->addSql(sprintf(
941                'CREATE TABLE IF NOT EXISTS %sfaqoauth_scopes (
942                    scope_id VARCHAR(80) NOT NULL,
943                    description VARCHAR(255) NULL,
944                    PRIMARY KEY (scope_id)
945                )',
946                $this->tablePrefix,
947            ), 'Create OAuth2 scopes table (PostgreSQL)');
948
949            $recorder->addSql(sprintf(
950                'CREATE TABLE IF NOT EXISTS %sfaqoauth_access_tokens (
951                    identifier VARCHAR(100) NOT NULL,
952                    client_id VARCHAR(80) NOT NULL,
953                    user_id VARCHAR(80) NULL,
954                    scopes TEXT NULL,
955                    revoked SMALLINT NOT NULL DEFAULT 0,
956                    expires_at TIMESTAMP NOT NULL,
957                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
958                    PRIMARY KEY (identifier)
959                )',
960                $this->tablePrefix,
961            ), 'Create OAuth2 access tokens table (PostgreSQL)');
962
963            $recorder->addSql(
964                sprintf(
965                    'CREATE INDEX IF NOT EXISTS idx_oauth_access_client ON %sfaqoauth_access_tokens (client_id)',
966                    $this->tablePrefix,
967                ),
968                'Create OAuth2 access client index (PostgreSQL)',
969            );
970            $recorder->addSql(
971                sprintf(
972                    'CREATE INDEX IF NOT EXISTS idx_oauth_access_user ON %sfaqoauth_access_tokens (user_id)',
973                    $this->tablePrefix,
974                ),
975                'Create OAuth2 access user index (PostgreSQL)',
976            );
977
978            $recorder->addSql(sprintf(
979                'CREATE TABLE IF NOT EXISTS %sfaqoauth_refresh_tokens (
980                    identifier VARCHAR(100) NOT NULL,
981                    access_token_identifier VARCHAR(100) NOT NULL,
982                    revoked SMALLINT NOT NULL DEFAULT 0,
983                    expires_at TIMESTAMP NOT NULL,
984                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
985                    PRIMARY KEY (identifier)
986                )',
987                $this->tablePrefix,
988            ), 'Create OAuth2 refresh tokens table (PostgreSQL)');
989            $recorder->addSql(
990                sprintf(
991                    'CREATE INDEX IF NOT EXISTS idx_oauth_refresh_access ON %sfaqoauth_refresh_tokens (access_token_identifier)',
992                    $this->tablePrefix,
993                ),
994                'Create OAuth2 refresh index (PostgreSQL)',
995            );
996
997            $recorder->addSql(sprintf(
998                'CREATE TABLE IF NOT EXISTS %sfaqoauth_auth_codes (
999                    identifier VARCHAR(100) NOT NULL,
1000                    client_id VARCHAR(80) NOT NULL,
1001                    user_id VARCHAR(80) NULL,
1002                    redirect_uri TEXT NULL,
1003                    scopes TEXT NULL,
1004                    revoked SMALLINT NOT NULL DEFAULT 0,
1005                    expires_at TIMESTAMP NOT NULL,
1006                    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1007                    PRIMARY KEY (identifier)
1008                )',
1009                $this->tablePrefix,
1010            ), 'Create OAuth2 auth codes table (PostgreSQL)');
1011            $recorder->addSql(
1012                sprintf(
1013                    'CREATE INDEX IF NOT EXISTS idx_oauth_code_client ON %sfaqoauth_auth_codes (client_id)',
1014                    $this->tablePrefix,
1015                ),
1016                'Create OAuth2 auth code client index (PostgreSQL)',
1017            );
1018            $recorder->addSql(
1019                sprintf(
1020                    'CREATE INDEX IF NOT EXISTS idx_oauth_code_user ON %sfaqoauth_auth_codes (user_id)',
1021                    $this->tablePrefix,
1022                ),
1023                'Create OAuth2 auth code user index (PostgreSQL)',
1024            );
1025        }
1026
1027        if ($this->isSqlite()) {
1028            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqoauth_clients (
1029                    client_id VARCHAR(80) NOT NULL,
1030                    client_secret VARCHAR(255) NULL,
1031                    name VARCHAR(255) NOT NULL,
1032                    redirect_uri TEXT NULL,
1033                    grants VARCHAR(255) NULL,
1034                    is_confidential INTEGER NOT NULL DEFAULT 1,
1035                    user_id INTEGER NULL,
1036                    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
1037                    PRIMARY KEY (client_id)
1038                )', $this->tablePrefix), 'Create OAuth2 clients table (SQLite)');
1039
1040            $recorder->addSql(sprintf('CREATE TABLE IF NOT EXISTS %sfaqoauth_scopes (
1041                    scope_id VARCHAR(80) NOT NULL,
1042                    description VARCHAR(255) NULL,
1043                    PRIMARY KEY (scope_id)
1044                )', $this->tablePrefix), 'Create OAuth2 scopes table (SQLite)');
1045
1046            $recorder->addSql(sprintf(
1047                'CREATE TABLE IF NOT EXISTS %sfaqoauth_access_tokens (
1048                    identifier VARCHAR(100) NOT NULL,
1049                    client_id VARCHAR(80) NOT NULL,
1050                    user_id VARCHAR(80) NULL,
1051                    scopes TEXT NULL,
1052                    revoked INTEGER NOT NULL DEFAULT 0,
1053                    expires_at DATETIME NOT NULL,
1054                    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
1055                    PRIMARY KEY (identifier)
1056                )',
1057                $this->tablePrefix,
1058            ), 'Create OAuth2 access tokens table (SQLite)');
1059            $recorder->addSql(
1060                sprintf(
1061                    'CREATE INDEX IF NOT EXISTS idx_oauth_access_client ON %sfaqoauth_access_tokens (client_id)',
1062                    $this->tablePrefix,
1063                ),
1064                'Create OAuth2 access client index (SQLite)',
1065            );
1066            $recorder->addSql(
1067                sprintf(
1068                    'CREATE INDEX IF NOT EXISTS idx_oauth_access_user ON %sfaqoauth_access_tokens (user_id)',
1069                    $this->tablePrefix,
1070                ),
1071                'Create OAuth2 access user index (SQLite)',
1072            );
1073
1074            $recorder->addSql(sprintf(
1075                'CREATE TABLE IF NOT EXISTS %sfaqoauth_refresh_tokens (
1076                    identifier VARCHAR(100) NOT NULL,
1077                    access_token_identifier VARCHAR(100) NOT NULL,
1078                    revoked INTEGER NOT NULL DEFAULT 0,
1079                    expires_at DATETIME NOT NULL,
1080                    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
1081                    PRIMARY KEY (identifier)
1082                )',
1083                $this->tablePrefix,
1084            ), 'Create OAuth2 refresh tokens table (SQLite)');
1085            $recorder->addSql(
1086                sprintf(
1087                    'CREATE INDEX IF NOT EXISTS idx_oauth_refresh_access ON %sfaqoauth_refresh_tokens (access_token_identifier)',
1088                    $this->tablePrefix,
1089                ),
1090                'Create OAuth2 refresh index (SQLite)',
1091            );
1092
1093            $recorder->addSql(sprintf(
1094                'CREATE TABLE IF NOT EXISTS %sfaqoauth_auth_codes (
1095                    identifier VARCHAR(100) NOT NULL,
1096                    client_id VARCHAR(80) NOT NULL,
1097                    user_id VARCHAR(80) NULL,
1098                    redirect_uri TEXT NULL,
1099                    scopes TEXT NULL,
1100                    revoked INTEGER NOT NULL DEFAULT 0,
1101                    expires_at DATETIME NOT NULL,
1102                    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
1103                    PRIMARY KEY (identifier)
1104                )',
1105                $this->tablePrefix,
1106            ), 'Create OAuth2 auth codes table (SQLite)');
1107            $recorder->addSql(
1108                sprintf(
1109                    'CREATE INDEX IF NOT EXISTS idx_oauth_code_client ON %sfaqoauth_auth_codes (client_id)',
1110                    $this->tablePrefix,
1111                ),
1112                'Create OAuth2 auth code client index (SQLite)',
1113            );
1114            $recorder->addSql(
1115                sprintf(
1116                    'CREATE INDEX IF NOT EXISTS idx_oauth_code_user ON %sfaqoauth_auth_codes (user_id)',
1117                    $this->tablePrefix,
1118                ),
1119                'Create OAuth2 auth code user index (SQLite)',
1120            );
1121        }
1122
1123        if ($this->isSqlServer()) {
1124            $recorder->addSql(
1125                sprintf(
1126                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqoauth_clients') AND type = 'U') "
1127                    . 'CREATE TABLE %sfaqoauth_clients (
1128                    client_id VARCHAR(80) NOT NULL,
1129                    client_secret VARCHAR(255) NULL,
1130                    name VARCHAR(255) NOT NULL,
1131                    redirect_uri NVARCHAR(MAX) NULL,
1132                    grants VARCHAR(255) NULL,
1133                    is_confidential TINYINT NOT NULL DEFAULT 1,
1134                    user_id INT NULL,
1135                    created DATETIME NOT NULL DEFAULT GETDATE(),
1136                    PRIMARY KEY (client_id)
1137                )',
1138                    $this->tablePrefix,
1139                    $this->tablePrefix,
1140                ),
1141                'Create OAuth2 clients table (SQL Server)',
1142            );
1143
1144            $recorder->addSql(
1145                sprintf(
1146                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqoauth_scopes') AND type = 'U') "
1147                    . 'CREATE TABLE %sfaqoauth_scopes (
1148                    scope_id VARCHAR(80) NOT NULL,
1149                    description VARCHAR(255) NULL,
1150                    PRIMARY KEY (scope_id)
1151                )',
1152                    $this->tablePrefix,
1153                    $this->tablePrefix,
1154                ),
1155                'Create OAuth2 scopes table (SQL Server)',
1156            );
1157
1158            $recorder->addSql(
1159                sprintf(
1160                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqoauth_access_tokens') AND type = 'U') "
1161                    . 'CREATE TABLE %sfaqoauth_access_tokens (
1162                    identifier VARCHAR(100) NOT NULL,
1163                    client_id VARCHAR(80) NOT NULL,
1164                    user_id VARCHAR(80) NULL,
1165                    scopes NVARCHAR(MAX) NULL,
1166                    revoked TINYINT NOT NULL DEFAULT 0,
1167                    expires_at DATETIME NOT NULL,
1168                    created DATETIME NOT NULL DEFAULT GETDATE(),
1169                    PRIMARY KEY (identifier)
1170                )',
1171                    $this->tablePrefix,
1172                    $this->tablePrefix,
1173                ),
1174                'Create OAuth2 access tokens table (SQL Server)',
1175            );
1176            $recorder->addSql(
1177                sprintf(
1178                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_oauth_access_client'"
1179                    . " AND object_id = OBJECT_ID(N'%sfaqoauth_access_tokens'))"
1180                    . ' CREATE INDEX idx_oauth_access_client ON %sfaqoauth_access_tokens (client_id)',
1181                    $this->tablePrefix,
1182                    $this->tablePrefix,
1183                ),
1184                'Create OAuth2 access client index (SQL Server)',
1185            );
1186            $recorder->addSql(
1187                sprintf(
1188                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_oauth_access_user'"
1189                    . " AND object_id = OBJECT_ID(N'%sfaqoauth_access_tokens'))"
1190                    . ' CREATE INDEX idx_oauth_access_user ON %sfaqoauth_access_tokens (user_id)',
1191                    $this->tablePrefix,
1192                    $this->tablePrefix,
1193                ),
1194                'Create OAuth2 access user index (SQL Server)',
1195            );
1196
1197            $recorder->addSql(
1198                sprintf(
1199                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqoauth_refresh_tokens') AND type = 'U') "
1200                    . 'CREATE TABLE %sfaqoauth_refresh_tokens (
1201                    identifier VARCHAR(100) NOT NULL,
1202                    access_token_identifier VARCHAR(100) NOT NULL,
1203                    revoked TINYINT NOT NULL DEFAULT 0,
1204                    expires_at DATETIME NOT NULL,
1205                    created DATETIME NOT NULL DEFAULT GETDATE(),
1206                    PRIMARY KEY (identifier)
1207                )',
1208                    $this->tablePrefix,
1209                    $this->tablePrefix,
1210                ),
1211                'Create OAuth2 refresh tokens table (SQL Server)',
1212            );
1213            $recorder->addSql(
1214                sprintf(
1215                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_oauth_refresh_access'"
1216                    . " AND object_id = OBJECT_ID(N'%sfaqoauth_refresh_tokens'))"
1217                    . ' CREATE INDEX idx_oauth_refresh_access ON %sfaqoauth_refresh_tokens (access_token_identifier)',
1218                    $this->tablePrefix,
1219                    $this->tablePrefix,
1220                ),
1221                'Create OAuth2 refresh index (SQL Server)',
1222            );
1223
1224            $recorder->addSql(
1225                sprintf(
1226                    "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'%sfaqoauth_auth_codes') AND type = 'U') "
1227                    . 'CREATE TABLE %sfaqoauth_auth_codes (
1228                    identifier VARCHAR(100) NOT NULL,
1229                    client_id VARCHAR(80) NOT NULL,
1230                    user_id VARCHAR(80) NULL,
1231                    redirect_uri NVARCHAR(MAX) NULL,
1232                    scopes NVARCHAR(MAX) NULL,
1233                    revoked TINYINT NOT NULL DEFAULT 0,
1234                    expires_at DATETIME NOT NULL,
1235                    created DATETIME NOT NULL DEFAULT GETDATE(),
1236                    PRIMARY KEY (identifier)
1237                )',
1238                    $this->tablePrefix,
1239                    $this->tablePrefix,
1240                ),
1241                'Create OAuth2 auth codes table (SQL Server)',
1242            );
1243            $recorder->addSql(
1244                sprintf(
1245                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_oauth_code_client'"
1246                    . " AND object_id = OBJECT_ID(N'%sfaqoauth_auth_codes'))"
1247                    . ' CREATE INDEX idx_oauth_code_client ON %sfaqoauth_auth_codes (client_id)',
1248                    $this->tablePrefix,
1249                    $this->tablePrefix,
1250                ),
1251                'Create OAuth2 auth code client index (SQL Server)',
1252            );
1253            $recorder->addSql(
1254                sprintf(
1255                    "IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'idx_oauth_code_user'"
1256                    . " AND object_id = OBJECT_ID(N'%sfaqoauth_auth_codes'))"
1257                    . ' CREATE INDEX idx_oauth_code_user ON %sfaqoauth_auth_codes (user_id)',
1258                    $this->tablePrefix,
1259                    $this->tablePrefix,
1260                ),
1261                'Create OAuth2 auth code user index (SQL Server)',
1262            );
1263        }
1264
1265        // faqgroup_right_category table for granular group-based category permissions
1266        $intType = $this->integerType();
1267
1268        if ($this->isMySql()) {
1269            $recorder->addSql(
1270                sprintf(
1271                    'CREATE TABLE IF NOT EXISTS %sfaqgroup_right_category (
1272                        group_id %s NOT NULL,
1273                        right_id %s NOT NULL,
1274                        category_id %s NOT NULL,
1275                        PRIMARY KEY (group_id, right_id, category_id)
1276                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci',
1277                    $this->tablePrefix,
1278                    $intType,
1279                    $intType,
1280                    $intType,
1281                ),
1282                'Create faqgroup_right_category table (MySQL)',
1283            );
1284        }
1285
1286        if ($this->isPostgreSql()) {
1287            $recorder->addSql(
1288                sprintf('CREATE TABLE IF NOT EXISTS %sfaqgroup_right_category (
1289                        group_id %s NOT NULL,
1290                        right_id %s NOT NULL,
1291                        category_id %s NOT NULL,
1292                        PRIMARY KEY (group_id, right_id, category_id)
1293                    )', $this->tablePrefix, $intType, $intType, $intType),
1294                'Create faqgroup_right_category table (PostgreSQL)',
1295            );
1296        }
1297
1298        if ($this->isSqlite()) {
1299            $recorder->addSql(
1300                sprintf('CREATE TABLE IF NOT EXISTS %sfaqgroup_right_category (
1301                        group_id %s NOT NULL,
1302                        right_id %s NOT NULL,
1303                        category_id %s NOT NULL,
1304                        PRIMARY KEY (group_id, right_id, category_id)
1305                    )', $this->tablePrefix, $intType, $intType, $intType),
1306                'Create faqgroup_right_category table (SQLite)',
1307            );
1308        }
1309
1310        if ($this->isSqlServer()) {
1311            $recorder->addSql(
1312                sprintf(
1313                    'IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = \'%sfaqgroup_right_category\') '
1314                    . 'CREATE TABLE %sfaqgroup_right_category (
1315                        group_id %s NOT NULL,
1316                        right_id %s NOT NULL,
1317                        category_id %s NOT NULL,
1318                        PRIMARY KEY (group_id, right_id, category_id)
1319                    )',
1320                    $this->tablePrefix,
1321                    $this->tablePrefix,
1322                    $intType,
1323                    $intType,
1324                    $intType,
1325                ),
1326                'Create faqgroup_right_category table (SQL Server)',
1327            );
1328        }
1329
1330        // faqadmindashboard table for per-admin dashboard widget layouts
1331        $textType = $this->textType();
1332
1333        if ($this->isMySql()) {
1334            $recorder->addSql(
1335                sprintf(
1336                    'CREATE TABLE IF NOT EXISTS %sfaqadmindashboard (
1337                        user_id %s NOT NULL,
1338                        config %s,
1339                        PRIMARY KEY (user_id)
1340                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci',
1341                    $this->tablePrefix,
1342                    $intType,
1343                    $textType,
1344                ),
1345                'Create faqadmindashboard table (MySQL)',
1346            );
1347        }
1348
1349        if ($this->isPostgreSql()) {
1350            $recorder->addSql(
1351                sprintf('CREATE TABLE IF NOT EXISTS %sfaqadmindashboard (
1352                        user_id %s NOT NULL,
1353                        config %s,
1354                        PRIMARY KEY (user_id)
1355                    )', $this->tablePrefix, $intType, $textType),
1356                'Create faqadmindashboard table (PostgreSQL)',
1357            );
1358        }
1359
1360        if ($this->isSqlite()) {
1361            $recorder->addSql(
1362                sprintf('CREATE TABLE IF NOT EXISTS %sfaqadmindashboard (
1363                        user_id %s NOT NULL,
1364                        config %s,
1365                        PRIMARY KEY (user_id)
1366                    )', $this->tablePrefix, $intType, $textType),
1367                'Create faqadmindashboard table (SQLite)',
1368            );
1369        }
1370
1371        if ($this->isSqlServer()) {
1372            $recorder->addSql(
1373                sprintf(
1374                    'IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = \'%sfaqadmindashboard\') '
1375                    . 'CREATE TABLE %sfaqadmindashboard (
1376                        user_id %s NOT NULL,
1377                        config %s,
1378                        PRIMARY KEY (user_id)
1379                    )',
1380                    $this->tablePrefix,
1381                    $this->tablePrefix,
1382                    $intType,
1383                    $textType,
1384                ),
1385                'Create faqadmindashboard table (SQL Server)',
1386            );
1387        }
1388    }
1389}