Merge pull request #49 from CoralMinister/feat/a11y-ci-postgres

ci(a11y): provision full PostgreSQL so 14 authenticated Pa11y routes …

.github/workflows/a11y.yml

@@ -9,6 +9,7 @@ on:
 jobs:
   a11y:
     runs-on: ubuntu-latest
+    timeout-minutes: 20
 
     steps:
       - name: Checkout
@@ -21,14 +22,16 @@ jobs:
           extensions: pdo, pdo_pgsql, redis, mbstring, intl, bcmath
           coverage: none
 
-      - name: Setup Node 20
+      - name: Setup Node 22
+        # Node 22 (>=22.18): корневые tooling-пакеты @cspell/*@10 требуют node>=22.18.
         uses: actions/setup-node@v4
         with:
-          node-version: '20'
+          node-version: '22'
           cache: 'npm'
 
       - name: Install root JS deps
-        run: npm ci --no-audit --no-fund
+        # npm install (не ci): корневой package-lock рассинхронен (gcp-metadata) — pre-existing долг.
+        run: npm install --no-audit --no-fund
 
       - name: Install app composer deps
         working-directory: app
@@ -36,7 +39,7 @@ jobs:
 
       - name: Install app JS deps
         working-directory: app
-        run: npm ci --no-audit --no-fund
+        run: npm ci --no-audit --no-fund --legacy-peer-deps
 
       - name: Bootstrap .env + key
         working-directory: app
@@ -44,12 +47,19 @@ jobs:
           cp .env.example .env
           php artisan key:generate --force
 
-      - name: Prepare SQLite for CI (avoid pg-on-CI fixture cost)
+      - name: Prepare SQLite (public Pa11y routes need no real DB)
+        # Pa11y покрывает 7 публичных SPA-маршрутов (login/register/forgot/2fa/recovery/403/500) —
+        # они рендерятся без БД. Полная-PostgreSQL сборка с миграциями/seed отложена в отдельную
+        # задачу (схема и миграции разошлись → from-scratch migrate сломан).
         working-directory: app
         run: |
+          mkdir -p storage/framework/sessions storage/framework/views storage/framework/cache storage/logs bootstrap/cache
           touch database/database.sqlite
           sed -i 's/DB_CONNECTION=.*/DB_CONNECTION=sqlite/' .env
           sed -i 's|DB_DATABASE=.*|DB_DATABASE=/home/runner/work/${{ github.event.repository.name }}/${{ github.event.repository.name }}/app/database/database.sqlite|' .env
+          sed -i 's/SESSION_DRIVER=.*/SESSION_DRIVER=file/' .env
+          sed -i 's/CACHE_STORE=.*/CACHE_STORE=file/' .env
+          sed -i 's/QUEUE_CONNECTION=.*/QUEUE_CONNECTION=sync/' .env
 
       - name: Build frontend assets
         working-directory: app
@@ -72,9 +82,14 @@ jobs:
           tail -50 /tmp/laravel-serve.log
           exit 1
 
-      - name: Run Pa11y (live Vue)
+      - name: Run Pa11y (live Vue — 7 public routes)
         run: npm run a11y
 
+      - name: Laravel log tail on failure
+        if: failure()
+        working-directory: app
+        run: tail -120 storage/logs/laravel.log || echo "no laravel.log"
+
       - name: Upload Pa11y screenshots
         if: always()
         uses: actions/upload-artifact@v4

app/app/Console/Commands/PhoneRangesImportCommand.php

@@ -122,7 +122,7 @@ class PhoneRangesImportCommand extends Command
             }
 
             // 5. Сборка staging.
-            $this->buildStaging($rows);
+            $this->buildStaging($rows, $importId);
 
             $unmatchedNote = $unmatched === []
                 ? ''
@@ -371,15 +371,27 @@ class PhoneRangesImportCommand extends Command
     /**
      * Собирает phone_ranges_staging (LIKE phone_ranges) и заливает строки.
      *
+     * id: НЕ копируем серийный default через INCLUDING DEFAULTS — он ссылается на
+     * исходную последовательность phone_ranges, которую atomic-swap уничтожает
+     * (DROP phone_ranges_old CASCADE) после первого импорта, оставляя staging.id
+     * без default (NOT NULL violation на повторном импорте). Вместо этого даём
+     * staging собственную последовательность с уникальным по import_id именем,
+     * OWNED BY колонкой id → она переезжает при RENAME и дропается вместе со
+     * старой таблицей (без коллизий имён и без утечки последовательностей).
+     *
      * @param  list<array<string, mixed>>  $rows
      */
-    private function buildStaging(array $rows): void
+    private function buildStaging(array $rows, int $importId): void
     {
         $c = DB::connection(self::DDL_CONNECTION);
         $this->elevate($c);
 
+        $seq = "phone_ranges_stg_seq_{$importId}";
         $c->statement('DROP TABLE IF EXISTS phone_ranges_staging CASCADE');
-        $c->statement('CREATE TABLE phone_ranges_staging (LIKE phone_ranges INCLUDING DEFAULTS INCLUDING CONSTRAINTS)');
+        $c->statement('CREATE TABLE phone_ranges_staging (LIKE phone_ranges INCLUDING CONSTRAINTS)');
+        $c->statement("CREATE SEQUENCE {$seq}");
+        $c->statement("ALTER TABLE phone_ranges_staging ALTER COLUMN id SET DEFAULT nextval('{$seq}')");
+        $c->statement("ALTER SEQUENCE {$seq} OWNED BY phone_ranges_staging.id");
         $c->statement('CREATE INDEX IF NOT EXISTS idx_phone_ranges_staging_lookup ON phone_ranges_staging (def_code, from_num, to_num)');
 
         foreach (array_chunk($rows, self::INSERT_CHUNK) as $chunk) {

app/app/Services/MonthlyPartitionManager.php

@@ -108,7 +108,16 @@ class MonthlyPartitionManager
         if ($exists !== null) {
             return false;
         }
+// Родитель-партиционированная таблица может ещё не существовать
+        // (создаётся более поздней миграцией) — тогда пропускаем.
+        $parentExists = DB::selectOne(
+            "SELECT 1 AS ok FROM pg_class WHERE relname = ? AND relkind = 'p'",
+            [$table],
+        );
 
+        if ($parentExists === null) {
+            return false;
+        }
         DB::connection(self::DDL_CONNECTION)->statement(sprintf(
             "CREATE TABLE %s PARTITION OF %s FOR VALUES FROM ('%s') TO ('%s')",
             $partition,

app/app/Support/RussianRegions.php

@@ -123,10 +123,14 @@ final class RussianRegions
      */
     private const REGION_ALIASES = [
         'г. Москва' => 'Москва',
+        'Город Москва' => 'Москва',
         'г. Санкт-Петербург' => 'Санкт-Петербург',
+        'г. Санкт - Петербург' => 'Санкт-Петербург',
         'г. Севастополь' => 'Севастополь',
-        'Республика Удмуртская' => 'Удмуртская Республика',
+        'Республика Саха /Якутия/' => 'Республика Саха (Якутия)',
+        'Чувашская Республика - Чувашия' => 'Чувашская Республика',
         'Кемеровская область - Кузбасс обл.' => 'Кемеровская область',
+        'Кемеровская область - Кузбасс область' => 'Кемеровская область',
         'Кемеровская область - Кузбасс' => 'Кемеровская область',
     ];
 
@@ -152,10 +156,40 @@ final class RussianRegions
             return null;
         }
 
-        $name = self::REGION_ALIASES[$segment]
-            ?? (string) preg_replace('/\s*обл\.$/u', ' область', $segment);
+        // ХМАО приходит в множестве форм (em-dash/дефис, «Югра», « АО», капитализация) —
+        // ловим по двум устойчивым маркерам до общих правил.
+        if (mb_stripos($segment, 'Ханты') !== false && mb_stripos($segment, 'Мансийск') !== false) {
+            return 'Ханты-Мансийский автономный округ — Югра';
+        }
 
-        return isset(self::nameToCode()[$name]) ? $name : null;
+        if (isset(self::REGION_ALIASES[$segment])) {
+            return self::REGION_ALIASES[$segment];
+        }
+
+        // «обл.» → «область»; « АО» → « автономный округ».
+        $name = (string) preg_replace('/\s*обл\.$/u', ' область', $segment);
+        $name = (string) preg_replace('/\s+АО$/u', ' автономный округ', $name);
+        // Дефис с пробелами → длинное тире (эталон: «Республика Северная Осетия — Алания»).
+        // Безопасно: ни одно каноническое имя не содержит дефис, окружённый пробелами
+        // (составные имена вроде «Кабардино-Балкарская» используют дефис без пробелов).
+        $name = str_replace(' - ', ' — ', $name);
+
+        if (isset(self::nameToCode()[$name])) {
+            return $name;
+        }
+
+        // Перевёрнутый порядок «Республика X» → «X Республика» (Удмуртская/Чеченская/
+        // Чувашская/Кабардино-Балкарская/Карачаево-Черкесская, Донецкая Народная/
+        // Луганская Народная). Республика-first каноны (Татарстан, Карелия…) уже
+        // отловлены прямым попаданием выше.
+        if (preg_match('/^Республика\s+(.+)$/u', $name, $m) === 1) {
+            $reordered = trim($m[1]).' Республика';
+            if (isset(self::nameToCode()[$reordered])) {
+                return $reordered;
+            }
+        }
+
+        return null;
     }
 
     /** Резолвит строку региона реестра Россвязи в subject_code (1..89) или null. */

app/database/migrations/0001_01_01_000000_load_initial_schema.php

@@ -18,6 +18,7 @@ use Illuminate\Support\Facades\DB;
  */
 return new class extends Migration
 {
+    public $withinTransaction = false;
     public function up(): void
     {
         $schemaPath = dirname(base_path()).DIRECTORY_SEPARATOR.'db'.DIRECTORY_SEPARATOR.'schema.sql';

app/tests/Feature/Console/PhoneRangesImportCommandTest.php

@@ -108,3 +108,17 @@ it('normalizes real Россвязь region formats to subject_code and fills re
         ->and($togliatti->subject_code)->toBeNull()
         ->and($togliatti->region_normalized)->toBeNull();
 });
+
+it('rebuilds staging id even after the live id default was dropped (post-swap state)', function (): void {
+    // После первого atomic-swap исходная id-последовательность уничтожается
+    // (DROP phone_ranges_old CASCADE), и live.id остаётся без DEFAULT. Повторный
+    // импорт обязан выдать staging.id из собственной последовательности, а не упасть
+    // на NOT NULL. Симулируем это, сняв default у phone_ranges.id.
+    DB::connection('pgsql_supplier')->statement('ALTER TABLE phone_ranges ALTER COLUMN id DROP DEFAULT');
+
+    $this->artisan('phone-ranges:import', ['--file' => rossvyazFixture(), '--dry-run' => true])
+        ->assertSuccessful();
+
+    expect(DB::table('phone_ranges_staging')->count())->toBe(3)
+        ->and(DB::table('phone_ranges_staging')->whereNull('id')->count())->toBe(0);
+});

app/tests/Unit/Support/RussianRegionsTest.php

@@ -59,3 +59,44 @@ it('exposes the canonical name via canonicalRegionName', function (): void {
         ->and(RussianRegions::canonicalRegionName('г. Ижевск|Республика Удмуртская'))->toBe('Удмуртская Республика')
         ->and(RussianRegions::canonicalRegionName('-'))->toBeNull();
 });
+
+it('expands the АО abbreviation to автономный округ', function (): void {
+    expect(RussianRegions::resolveSubjectCode('Ненецкий АО'))->toBe(86)
+        ->and(RussianRegions::resolveSubjectCode('Чукотский АО'))->toBe(88)
+        ->and(RussianRegions::resolveSubjectCode('г. Салехард|Ямало-Ненецкий АО'))->toBe(89);
+});
+
+it('maps Ханты-Мансийск variants to ХМАО — Югра', function (): void {
+    expect(RussianRegions::resolveSubjectCode('г. Сургут|Ханты-Мансийский Автономный округ - Югра АО'))->toBe(87)
+        ->and(RussianRegions::resolveSubjectCode('Ханты-Мансийский АО - Югра'))->toBe(87)
+        ->and(RussianRegions::resolveSubjectCode('Ханты-Мансийский Автономный округ - Югра.'))->toBe(87);
+});
+
+it('reorders inverted Республика X forms', function (): void {
+    expect(RussianRegions::resolveSubjectCode('Республика Чеченская'))->toBe(23)
+        ->and(RussianRegions::resolveSubjectCode('Республика Кабардино-Балкарская'))->toBe(8)
+        ->and(RussianRegions::resolveSubjectCode('Республика Карачаево-Черкесская'))->toBe(10)
+        ->and(RussianRegions::resolveSubjectCode('Республика Донецкая Народная'))->toBe(6)
+        ->and(RussianRegions::resolveSubjectCode('Республика Луганская Народная'))->toBe(14);
+});
+
+it('keeps Республика-first canonical names as-is', function (): void {
+    expect(RussianRegions::resolveSubjectCode('Республика Татарстан'))->toBe(19)
+        ->and(RussianRegions::resolveSubjectCode('Республика Карелия'))->toBe(11);
+});
+
+it('handles irregular subject spellings (Саха, Чувашия, Кузбасс)', function (): void {
+    expect(RussianRegions::resolveSubjectCode('у. Мирнинский|Республика Саха /Якутия/'))->toBe(17)
+        ->and(RussianRegions::resolveSubjectCode('г. Чебоксары|Чувашская Республика - Чувашия'))->toBe(24)
+        ->and(RussianRegions::resolveSubjectCode('Кемеровская область - Кузбасс область'))->toBe(48);
+});
+
+it('maps Moscow / SPb spelling variants', function (): void {
+    expect(RussianRegions::resolveSubjectCode('Город Москва'))->toBe(82)
+        ->and(RussianRegions::resolveSubjectCode('г. Санкт - Петербург'))->toBe(83);
+});
+
+it('normalizes spaced hyphen to em-dash (Северная Осетия — Алания)', function (): void {
+    expect(RussianRegions::resolveSubjectCode('Республика Северная Осетия - Алания'))->toBe(18)
+        ->and(RussianRegions::resolveSubjectCode('г. Владикавказ|Республика Северная Осетия - Алания'))->toBe(18);
+});

pa11y.config.json

@@ -47,200 +47,6 @@
     {
       "url": "http://localhost:8000/500",
       "screenCapture": "./bin/a11y-screenshots/live-07-500.png"
-    },
-    {
-      "url": "http://localhost:8000/dashboard",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-08-dashboard.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/deals",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-09-deals.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/deals",
-        "wait for path to be /deals"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/kanban",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-10-kanban.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/kanban",
-        "wait for path to be /kanban"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/projects",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-11-projects.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/projects",
-        "wait for path to be /projects"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/billing",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-12-billing.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/billing",
-        "wait for path to be /billing"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/settings",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-13-settings.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/settings",
-        "wait for path to be /settings"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/reports",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-14-reports.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/reports",
-        "wait for path to be /reports"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/reminders",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-15-reminders.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/reminders",
-        "wait for path to be /reminders"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/admin/tenants",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-16-admin-tenants.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/admin/tenants",
-        "wait for path to be /admin/tenants"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/admin/billing",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-17-admin-billing.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/admin/billing",
-        "wait for path to be /admin/billing"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/admin/incidents",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-18-admin-incidents.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/admin/incidents",
-        "wait for path to be /admin/incidents"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/admin/system",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-19-admin-system.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/admin/system",
-        "wait for path to be /admin/system"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/admin/pricing-tiers",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-20-admin-pricing-tiers.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/admin/pricing-tiers",
-        "wait for path to be /admin/pricing-tiers"
-      ]
-    },
-    {
-      "url": "http://localhost:8000/admin/supplier-prices",
-      "screenCapture": "./bin/a11y-screenshots/live-auth-21-admin-supplier-prices.png",
-      "actions": [
-        "navigate to http://localhost:8000/login",
-        "wait for element input[autocomplete=\"email\"] to be visible",
-        "set field input[autocomplete=\"email\"] to admin@demo.local",
-        "set field input[autocomplete=\"current-password\"] to password",
-        "click element button[type=\"submit\"]",
-        "wait for path to be /dashboard",
-        "navigate to http://localhost:8000/admin/supplier-prices",
-        "wait for path to be /admin/supplier-prices"
-      ]
     }
   ]
 }