10548

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

Build # 10548

Build Type

Pull #77

circleci

Committed by

edwh

Commit Message

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 Pull Request #77: feat: Unified digest revision - AMP, body dedup, digest reply handling

Coverage Stats

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

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

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

    public function __construct()
    {
        $this->srid = (int) config('freegle.srid', 3857);
    }

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

            return 0;
        }

        // Ensure PostgreSQL schema exists and sync location data.
        $this->ensurePostgresSchema();

        if ($polygon) {
            // Sync all locations within the polygon scope to PostgreSQL.
            // This is critical: the location_id in the task is the location that was edited,
            // but other locations within the polygon (e.g. newly created areas) may also need
            // syncing. V1 synced the edited location inline in setGeometry() before calling
            // remapPostcodes(), and relied on a nightly cron for everything else. We do better
            // by syncing all locations in the affected area.
            $this->syncLocationsInPolygon($polygon);
        } elseif ($locationId) {
            $this->syncSingleLocation($locationId);
        } else {
            $this->syncAllLocations();
        }

        $geomFilter = '';
        $params = [];

        if ($polygon) {
            // Include postcodes that either fall within the polygon OR currently point at
            // the affected location via areaid. The latter catches postcodes whose areaid
            // was assigned by KNN (buffered intersection / nearest-neighbour) rather than
            // strict containment — those sit outside the polygon but still need remapping
            // when the location they reference is excluded or its geometry changes.
            if ($locationId) {
                $geomFilter = "(ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) OR locations.areaid = ?) AND";
                $params[] = $polygon;
                $params[] = $locationId;
            } else {
                $geomFilter = "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) AND";
                $params[] = $polygon;
            }
        }

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

        $count = 0;
        $updated = 0;

        foreach ($postcodes as $pc) {
            $newAreaId = $this->findNearestArea($pc->lng, $pc->lat);

            if ($newAreaId && $newAreaId != $pc->areaid) {
                DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [
                    $newAreaId,
                    $pc->locationid,
                ]);
                $updated++;
            }

            $count++;

            if ($count % 1000 === 0) {
                Log::info("PostcodeRemapService: processed {$count}/" . count($postcodes) . ", updated {$updated}");
            }
        }

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

        return $updated;
    }

    /**
     * Find the nearest area location for a given point using PostGIS KNN.
     *
     * Uses expanding buffer intersection levels (matching V1 algorithm) to find
     * the smallest area that contains or is very close to the point.
     *
     * @param float $lng Longitude
     * @param float $lat Latitude
     * @return int|null Location ID of the best matching area, or null.
     */
    public function findNearestArea(float $lng, float $lat): ?int
    {
        $result = DB::connection('pgsql')->select("
            WITH ourpoint AS (
                SELECT ST_MakePoint(?, ?) AS p
            )
            SELECT locationid FROM (
                SELECT
                    locationid,
                    ST_Area(location) AS area,
                    CASE
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
                    END AS intersects
                FROM (
                    SELECT locationid,
                           location,
                           location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
                    FROM locations
                    WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
                    ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
                    LIMIT 10
                ) q
            ) candidates
            WHERE intersects IS NOT NULL
            ORDER BY intersects ASC, area ASC
            LIMIT 1
        ", [
            $lng, $lat,
            // 12 SRID params for the buffer intersections
            $this->srid, $this->srid, $this->srid, $this->srid,
            $this->srid, $this->srid, $this->srid, $this->srid,
            $this->srid, $this->srid, $this->srid, $this->srid,
            // 2 SRID params for the KNN subquery
            $this->srid, $this->srid,
        ]);

        if (count($result) > 0) {
            return (int) $result[0]->locationid;
        }

        return NULL;
    }

    /**
     * Ensure the PostgreSQL locations table and indexes exist.
     */
    public function ensurePostgresSchema(): void
    {
        $pgsql = DB::connection('pgsql');

        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');
        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');

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

        // Create the locations table if it doesn't exist.
        $tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");
        if (! $tableExists->exists) {
            $pgsql->statement('CREATE TABLE locations (
                id serial PRIMARY KEY,
                locationid bigint UNIQUE NOT NULL,
                name text,
                type location_type,
                area numeric,
                location geometry
            )');
            $pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');
        }
    }

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

        if (! $loc || ! $loc->geom) {
            return;
        }

        DB::connection('pgsql')->statement(
            "INSERT INTO locations (locationid, name, type, area, location)
             VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
             ON CONFLICT (locationid) DO UPDATE SET
                 name = EXCLUDED.name,
                 type = EXCLUDED.type,
                 area = EXCLUDED.area,
                 location = EXCLUDED.location",
            [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
        );
    }

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

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

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

        $synced = 0;

        foreach ($locations as $loc) {
            if (! $loc->geom) {
                continue;
            }

            DB::connection('pgsql')->statement(
                "INSERT INTO locations (locationid, name, type, area, location)
                 VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
                 ON CONFLICT (locationid) DO UPDATE SET
                     name = EXCLUDED.name,
                     type = EXCLUDED.type,
                     area = EXCLUDED.area,
                     location = EXCLUDED.location",
                [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
            );

            $synced++;
        }

        Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");
    }

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

        $pgsql->statement("DROP TABLE IF EXISTS locations_tmp{$uniq}");
        $pgsql->statement("CREATE TABLE locations_tmp{$uniq} (
            id serial PRIMARY KEY,
            locationid bigint UNIQUE NOT NULL,
            name text,
            type location_type,
            area numeric,
            location geometry
        )");
        $pgsql->statement("ALTER TABLE locations_tmp{$uniq} SET UNLOGGED");

        // Fetch non-excluded polygon locations from MySQL.
        $locations = DB::select("
            SELECT locations.id, name, type,
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
            FROM locations
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
            WHERE le.locationid IS NULL
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
            AND type != 'Postcode'
        ");

        foreach ($locations as $loc) {
            if (! $loc->geom) {
                continue;
            }

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

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

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

        Log::info('PostcodeRemapService: synced ' . count($locations) . ' locations to PostgreSQL');
    }

    /**
     * Check if the PostgreSQL connection is available.
     */
    public function postgresAvailable(): bool
    {
        try {
            DB::connection('pgsql')->getPdo();

            return TRUE;
        } catch (\Throwable $e) {
            return FALSE;
        }
    }
}

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	}

Freegle / Iznik / 10548

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

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