• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

Freegle / Iznik / 10548

07 May 2026 12:56AM UTC coverage: 72.712% (-0.002%) from 72.714%
10548

Pull #77

circleci

edwh
feat: unified digest revision — AMP, reply handling, header redesign, test hardening

Squash of 32 commits from feature/unified-digest-revision rebased onto master.

Core digest changes:
- AMP email support with inline reply forms per post
- DigestReplyNotice auto-response for replies to noreply@ digest emails
- Body-similarity deduplication (same subject + different body = distinct)
- Digest header redesign: OFFER/WANTED split, two-column card layout, anchor links
- Distance display and arrival time formatting in digest cards
- Remove SingleDigest/MultipleDigest, consolidate to UnifiedDigest only
- SpamAssassin protocol fix for 5x test speedup
- trackedImageUrl guard against null (no image/placeholder)

CI/test hardening:
- Ban waitUntil:'load' in gotoAndVerify to prevent 202500ms CI hangs
- Guard isVisible()/isEnabled()/isChecked() calls with timeouts
- Guard MutationObserver.observe() against null documentElement
- Guard disableGoogleAutoselect window access for SSR/test teardown
- Add missing sleep before async Go task query in location test
- Use domcontentloaded in postMessage fixture
- Playwright chat-list race condition fix
- Freeze-detection heartbeat timeout 10s→25s
- Add tests for handleDigestReply and IncomingMailServiceTest
- PostcodeRemapServiceTest: real group/user fixtures, NULL FK avoidance

fix(vitest): always render Previous button (:disabled not v-show)

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Pull Request #77: feat: Unified digest revision - AMP, body dedup, digest reply handling

13809 of 20784 branches covered (66.44%)

Branch coverage included in aggregate %.

191 of 255 new or added lines in 8 files covered. (74.9%)

66 existing lines in 8 files now uncovered.

98798 of 134083 relevant lines covered (73.68%)

22.94 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

64.29
/iznik-batch/app/Services/PostcodeRemapService.php
1
<?php
2

3
namespace App\Services;
4

5
use Illuminate\Support\Facades\DB;
6
use Illuminate\Support\Facades\Log;
7

8
/**
9
 * Remaps postcodes to their nearest enclosing area using PostGIS KNN queries.
10
 *
11
 * When a location area's geometry is created or modified, postcodes within
12
 * that geometry need to be reassigned to the correct (smallest, nearest) area.
13
 * This mirrors the V1 PHP Location::remapPostcodes() logic.
14
 *
15
 * Uses PostgreSQL/PostGIS for efficient K-nearest-neighbor spatial queries,
16
 * and MySQL for reading/writing the canonical locations data.
17
 */
18
class PostcodeRemapService
19
{
20
    /**
21
     * SRID used for spatial operations (Web Mercator).
22
     */
23
    private int $srid;
24

25
    public function __construct()
7✔
26
    {
27
        $this->srid = (int) config('freegle.srid', 3857);
7✔
28
    }
29

30
    /**
31
     * Remap postcodes within a given WKT polygon to their nearest area.
32
     *
33
     * Syncs the affected location to PostgreSQL first, then queries PostGIS
34
     * KNN to find the best area for each postcode.
35
     *
36
     * @param int|null $locationId The location that was modified (for incremental sync).
37
     * @param string|null $polygon WKT polygon to scope the remap. NULL = remap all.
38
     * @return int Number of postcodes remapped.
39
     */
40
    public function remapPostcodes(?int $locationId = NULL, ?string $polygon = NULL): int
5✔
41
    {
42
        if (! $this->postgresAvailable()) {
5✔
43
            Log::warning('PostcodeRemapService: PostgreSQL connection not available, skipping remap');
1✔
44

45
            return 0;
1✔
46
        }
47

48
        // Ensure PostgreSQL schema exists and sync location data.
49
        $this->ensurePostgresSchema();
4✔
50

51
        if ($polygon) {
4✔
52
            // Sync all locations within the polygon scope to PostgreSQL.
53
            // This is critical: the location_id in the task is the location that was edited,
54
            // but other locations within the polygon (e.g. newly created areas) may also need
55
            // syncing. V1 synced the edited location inline in setGeometry() before calling
56
            // remapPostcodes(), and relied on a nightly cron for everything else. We do better
57
            // by syncing all locations in the affected area.
58
            $this->syncLocationsInPolygon($polygon);
4✔
UNCOV
59
        } elseif ($locationId) {
×
60
            $this->syncSingleLocation($locationId);
×
61
        } else {
UNCOV
62
            $this->syncAllLocations();
×
63
        }
64

65
        $geomFilter = '';
4✔
66
        $params = [];
4✔
67

68
        if ($polygon) {
4✔
69
            // Include postcodes that either fall within the polygon OR currently point at
70
            // the affected location via areaid. The latter catches postcodes whose areaid
71
            // was assigned by KNN (buffered intersection / nearest-neighbour) rather than
72
            // strict containment — those sit outside the polygon but still need remapping
73
            // when the location they reference is excluded or its geometry changes.
74
            if ($locationId) {
4✔
75
                $geomFilter = "(ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) OR locations.areaid = ?) AND";
4✔
76
                $params[] = $polygon;
4✔
77
                $params[] = $locationId;
4✔
78
            } else {
UNCOV
79
                $geomFilter = "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) AND";
×
UNCOV
80
                $params[] = $polygon;
×
81
            }
82
        }
83

84
        // Fetch all full postcodes (contain a space) within the scope.
85
        $postcodes = DB::select("
4✔
86
            SELECT DISTINCT locations_spatial.locationid, locations.name,
87
                   locations.lat, locations.lng, locations.areaid
88
            FROM locations_spatial
89
            INNER JOIN locations ON locations_spatial.locationid = locations.id
90
            WHERE {$geomFilter} locations.type = 'Postcode'
4✔
91
            AND LOCATE(' ', locations.name) > 0
92
        ", $params);
4✔
93

94
        $count = 0;
4✔
95
        $updated = 0;
4✔
96

97
        foreach ($postcodes as $pc) {
4✔
98
            $newAreaId = $this->findNearestArea($pc->lng, $pc->lat);
4✔
99

100
            if ($newAreaId && $newAreaId != $pc->areaid) {
4✔
101
                DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [
4✔
102
                    $newAreaId,
4✔
103
                    $pc->locationid,
4✔
104
                ]);
4✔
105
                $updated++;
4✔
106
            }
107

108
            $count++;
4✔
109

110
            if ($count % 1000 === 0) {
4✔
111
                Log::info("PostcodeRemapService: processed {$count}/" . count($postcodes) . ", updated {$updated}");
×
112
            }
113
        }
114

115
        Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");
4✔
116

117
        return $updated;
4✔
118
    }
119

120
    /**
121
     * Find the nearest area location for a given point using PostGIS KNN.
122
     *
123
     * Uses expanding buffer intersection levels (matching V1 algorithm) to find
124
     * the smallest area that contains or is very close to the point.
125
     *
126
     * @param float $lng Longitude
127
     * @param float $lat Latitude
128
     * @return int|null Location ID of the best matching area, or null.
129
     */
130
    public function findNearestArea(float $lng, float $lat): ?int
5✔
131
    {
132
        $result = DB::connection('pgsql')->select("
5✔
133
            WITH ourpoint AS (
134
                SELECT ST_MakePoint(?, ?) AS p
135
            )
136
            SELECT locationid FROM (
137
                SELECT
138
                    locationid,
139
                    ST_Area(location) AS area,
140
                    CASE
141
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
142
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
143
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
144
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
145
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
146
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
147
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
148
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
149
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
150
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
151
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
152
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
153
                    END AS intersects
154
                FROM (
155
                    SELECT locationid,
156
                           location,
157
                           location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
158
                    FROM locations
159
                    WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
160
                    ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
161
                    LIMIT 10
162
                ) q
163
            ) candidates
164
            WHERE intersects IS NOT NULL
165
            ORDER BY intersects ASC, area ASC
166
            LIMIT 1
167
        ", [
5✔
168
            $lng, $lat,
5✔
169
            // 12 SRID params for the buffer intersections
170
            $this->srid, $this->srid, $this->srid, $this->srid,
5✔
171
            $this->srid, $this->srid, $this->srid, $this->srid,
5✔
172
            $this->srid, $this->srid, $this->srid, $this->srid,
5✔
173
            // 2 SRID params for the KNN subquery
174
            $this->srid, $this->srid,
5✔
175
        ]);
5✔
176

177
        if (count($result) > 0) {
5✔
178
            return (int) $result[0]->locationid;
5✔
179
        }
180

181
        return NULL;
1✔
182
    }
183

184
    /**
185
     * Ensure the PostgreSQL locations table and indexes exist.
186
     */
187
    public function ensurePostgresSchema(): void
4✔
188
    {
189
        $pgsql = DB::connection('pgsql');
4✔
190

191
        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');
4✔
192
        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');
4✔
193

194
        // Create the location_type enum if it doesn't exist.
195
        $typeExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type')");
4✔
196
        if (! $typeExists->exists) {
4✔
197
            $pgsql->statement("CREATE TYPE location_type AS ENUM('Road','Polygon','Line','Point','Postcode')");
×
198
        }
199

200
        // Create the locations table if it doesn't exist.
201
        $tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");
4✔
202
        if (! $tableExists->exists) {
4✔
203
            $pgsql->statement('CREATE TABLE locations (
×
204
                id serial PRIMARY KEY,
205
                locationid bigint UNIQUE NOT NULL,
206
                name text,
207
                type location_type,
208
                area numeric,
209
                location geometry
210
            )');
×
211
            $pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');
×
212
        }
213
    }
214

215
    /**
216
     * Sync a single location from MySQL to PostgreSQL (upsert).
217
     *
218
     * Matches V1's per-location sync in setGeometry() — fast for single updates.
219
     */
220
    private function syncSingleLocation(int $locationId): void
×
221
    {
222
        $loc = DB::selectOne("
×
223
            SELECT locations.id, name, type,
224
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
225
            FROM locations
226
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
227
            WHERE locations.id = ?
228
            AND le.locationid IS NULL
229
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
230
            AND type != 'Postcode'
231
        ", [$locationId]);
×
232

233
        if (! $loc || ! $loc->geom) {
×
234
            return;
×
235
        }
236

237
        DB::connection('pgsql')->statement(
×
238
            "INSERT INTO locations (locationid, name, type, area, location)
×
239
             VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
240
             ON CONFLICT (locationid) DO UPDATE SET
241
                 name = EXCLUDED.name,
242
                 type = EXCLUDED.type,
243
                 area = EXCLUDED.area,
244
                 location = EXCLUDED.location",
×
245
            [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
×
246
        );
×
247
    }
248

249
    /**
250
     * Sync all non-postcode polygon locations within a WKT polygon to PostgreSQL.
251
     *
252
     * When a location is created or its geometry is changed, the remap task carries
253
     * the polygon scope. We need to sync all locations intersecting that scope —
254
     * not just the edited location — because newly created or recently modified
255
     * locations in the area may not yet be in PostgreSQL.
256
     */
257
    private function syncLocationsInPolygon(string $polygon): void
4✔
258
    {
259
        // First remove any now-excluded locations within the polygon scope from PostgreSQL.
260
        // Without this, a location excluded after being synced would remain in PG's KNN
261
        // index until the nightly syncAllLocations table-swap rebuilds from scratch,
262
        // meaning exclusions wouldn't take effect for postcode remapping until then.
263
        $excludedInScope = DB::select("
4✔
264
            SELECT DISTINCT le.locationid
265
            FROM locations_excluded le
266
            INNER JOIN locations_spatial ls ON ls.locationid = le.locationid
267
            WHERE ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))
4✔
268
        ", [$polygon]);
4✔
269

270
        if (!empty($excludedInScope)) {
4✔
271
            $ids = array_map(fn ($r) => (int) $r->locationid, $excludedInScope);
2✔
272
            DB::connection('pgsql')->delete(
2✔
273
                'DELETE FROM locations WHERE locationid IN (' . implode(',', $ids) . ')'
2✔
274
            );
2✔
275
            Log::info('PostcodeRemapService: removed ' . count($ids) . ' excluded locations from PostgreSQL within polygon scope');
2✔
276
        }
277

278
        $locations = DB::select("
4✔
279
            SELECT locations.id, locations.name, locations.type,
280
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) AS geom
281
            FROM locations_spatial ls
282
            INNER JOIN locations ON ls.locationid = locations.id
283
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
284
            WHERE le.locationid IS NULL
285
            AND ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))
4✔
286
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) = 2
287
            AND locations.type != 'Postcode'
288
        ", [$polygon]);
4✔
289

290
        $synced = 0;
4✔
291

292
        foreach ($locations as $loc) {
4✔
293
            if (! $loc->geom) {
3✔
294
                continue;
×
295
            }
296

297
            DB::connection('pgsql')->statement(
3✔
298
                "INSERT INTO locations (locationid, name, type, area, location)
3✔
299
                 VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
300
                 ON CONFLICT (locationid) DO UPDATE SET
301
                     name = EXCLUDED.name,
302
                     type = EXCLUDED.type,
303
                     area = EXCLUDED.area,
304
                     location = EXCLUDED.location",
3✔
305
                [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
3✔
306
            );
3✔
307

308
            $synced++;
3✔
309
        }
310

311
        Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");
4✔
312
    }
313

314
    /**
315
     * Full sync of all non-postcode polygon locations from MySQL to PostgreSQL.
316
     *
317
     * Uses a temp table + atomic swap matching V1's copyLocationsToPostgresql().
318
     * Used when no specific location ID is provided (full remap).
319
     */
UNCOV
320
    private function syncAllLocations(): void
×
321
    {
UNCOV
322
        $pgsql = DB::connection('pgsql');
×
UNCOV
323
        $uniq = '_' . uniqid();
×
324

UNCOV
325
        $pgsql->statement("DROP TABLE IF EXISTS locations_tmp{$uniq}");
×
UNCOV
326
        $pgsql->statement("CREATE TABLE locations_tmp{$uniq} (
×
327
            id serial PRIMARY KEY,
328
            locationid bigint UNIQUE NOT NULL,
329
            name text,
330
            type location_type,
331
            area numeric,
332
            location geometry
UNCOV
333
        )");
×
UNCOV
334
        $pgsql->statement("ALTER TABLE locations_tmp{$uniq} SET UNLOGGED");
×
335

336
        // Fetch non-excluded polygon locations from MySQL.
UNCOV
337
        $locations = DB::select("
×
338
            SELECT locations.id, name, type,
339
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
340
            FROM locations
341
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
342
            WHERE le.locationid IS NULL
343
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
344
            AND type != 'Postcode'
UNCOV
345
        ");
×
346

UNCOV
347
        foreach ($locations as $loc) {
×
348
            if (! $loc->geom) {
×
349
                continue;
×
350
            }
351

352
            $pgsql->insert(
×
353
                "INSERT INTO locations_tmp{$uniq} (locationid, name, type, area, location)
×
354
                 VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))",
×
355
                [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
×
356
            );
×
357
        }
358

359
        // Build index on temp table before swap.
UNCOV
360
        $pgsql->statement("CREATE INDEX idx_loc_tmp{$uniq} ON locations_tmp{$uniq} USING gist (location)");
×
361

362
        // Atomic swap: rename current to old, temp to current, drop old.
UNCOV
363
        $pgsql->statement("ALTER TABLE IF EXISTS locations RENAME TO locations_old{$uniq}");
×
UNCOV
364
        $pgsql->statement("ALTER TABLE locations_tmp{$uniq} RENAME TO locations");
×
UNCOV
365
        $pgsql->statement("DROP INDEX IF EXISTS idx_locations_location");
×
UNCOV
366
        $pgsql->statement("ALTER INDEX idx_loc_tmp{$uniq} RENAME TO idx_locations_location");
×
UNCOV
367
        $pgsql->statement("DROP TABLE IF EXISTS locations_old{$uniq}");
×
368

UNCOV
369
        Log::info('PostcodeRemapService: synced ' . count($locations) . ' locations to PostgreSQL');
×
370
    }
371

372
    /**
373
     * Check if the PostgreSQL connection is available.
374
     */
375
    public function postgresAvailable(): bool
5✔
376
    {
377
        try {
378
            DB::connection('pgsql')->getPdo();
5✔
379

380
            return TRUE;
4✔
381
        } catch (\Throwable $e) {
1✔
382
            return FALSE;
1✔
383
        }
384
    }
385
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc