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

Freegle / Iznik / 22411

19 Jun 2026 07:57AM UTC coverage: 70.739% (-0.1%) from 70.834%
22411

push

circleci

web-flow
Merge pull request #823 from Freegle/feature/rippling-self-tuning

ripple 12/12: self-tuning loop + geographic hotspot detection (advisory) [post-go-live]

10473 of 11990 branches covered (87.35%)

Branch coverage included in aggregate %.

242 of 260 new or added lines in 4 files covered. (93.08%)

4050 existing lines in 66 files now uncovered.

120229 of 172776 relevant lines covered (69.59%)

35.53 hits per line

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

92.93
/iznik-batch/app/Services/MessageSpatialService.php
1
<?php
2

3
namespace App\Services;
4

5
use App\Models\Message;
6
use App\Models\MessageGroup;
7
use Illuminate\Support\Facades\DB;
8
use Illuminate\Support\Facades\Log;
9

10
class MessageSpatialService
11
{
12
    // V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago"
13
    private const RECENT_DAYS = 31;
14
    private const SRID = 3857;
15

16
    private SpatialAdminService $spatialAdmin;
17

18
    public function __construct(SpatialAdminService $spatialAdmin)
12✔
19
    {
20
        $this->spatialAdmin = $spatialAdmin;
12✔
21
    }
22

23
    public function updateSpatialIndex(bool $dryRun = false): array
8✔
24
    {
25
        $stats = [
8✔
26
            'upserted_recent' => $this->upsertRecentMessages($dryRun),
8✔
27
            'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),
8✔
28
            'removed_deleted' => $this->removeDeletedMessages($dryRun),
8✔
29
            'removed_old' => $this->removeOldMessages($dryRun),
8✔
30
            'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),
8✔
31
        ];
8✔
32

33
        $total = array_sum($stats);
8✔
34
        $stats['total'] = $total;
8✔
35

36
        Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);
8✔
37

38
        return $stats;
8✔
39
    }
40

41
    private function upsertRecentMessages(bool $dryRun = false): int
8✔
42
    {
43
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));
8✔
44

45
        $msgs = DB::table('messages')
8✔
46
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
8✔
47
            ->join('users', 'users.id', '=', 'messages.fromuser')
8✔
48
            ->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')
8✔
49
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
8✔
50
            ->where('messages_groups.arrival', '>=', $cutoff)
8✔
51
            ->whereNotNull('messages.lat')
8✔
52
            ->whereNotNull('messages.lng')
8✔
53
            ->whereNull('messages.deleted')
8✔
54
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
8✔
55
            ->whereNull('users.deleted')
8✔
56
            ->where(function ($q) {
8✔
57
                // Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
58
                $q->whereNull('messages_outcomes.outcome')
8✔
59
                    ->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);
8✔
60
            })
8✔
61
            ->where(function ($q) {
8✔
62
                $q->whereNull('messages_spatial.msgid')
8✔
63
                    ->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')
8✔
64
                    ->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')
8✔
65
                    ->orWhereNull('messages_spatial.groupid')
8✔
66
                    ->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')
8✔
67
                    ->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');
8✔
68
            })
8✔
69
            ->select(
8✔
70
                'messages.id',
8✔
71
                'messages.lat',
8✔
72
                'messages.lng',
8✔
73
                'messages_groups.groupid',
8✔
74
                'messages_groups.arrival',
8✔
75
                'messages_groups.msgtype',
8✔
76
            )
8✔
77
            ->distinct()
8✔
78
            ->get();
8✔
79

80
        $count = 0;
8✔
81
        foreach ($msgs as $msg) {
8✔
82
            if (!$dryRun) {
8✔
83
                // Coordinates come from DB, not user input — safe to embed in WKT.
84
                $wkt = "POINT({$msg->lng} {$msg->lat})";
8✔
85
                $srid = self::SRID;
8✔
86

87
                DB::statement(
8✔
88
                    "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
8✔
89
                     VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
8✔
90
                     ON DUPLICATE KEY UPDATE
91
                       point = ST_GeomFromText('$wkt', $srid),
8✔
92
                       groupid = ?,
93
                       msgtype = ?,
94
                       arrival = ?",
8✔
95
                    [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
8✔
96
                     $msg->groupid, $msg->msgtype, $msg->arrival]
8✔
97
                );
8✔
98
            }
99
            $count++;
8✔
100
        }
101

102
        return $count;
8✔
103
    }
104

105
    private function updateOutcomesAndPromises(bool $dryRun = false): int
8✔
106
    {
107
        $msgs = DB::table('messages_spatial')
8✔
108
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')
8✔
109
            ->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')
8✔
110
            ->select(
8✔
111
                'messages_spatial.id',
8✔
112
                'messages_spatial.msgid',
8✔
113
                'messages_spatial.successful',
8✔
114
                'messages_spatial.promised',
8✔
115
                'messages_outcomes.outcome',
8✔
116
                'messages_promises.promisedat',
8✔
117
            )
8✔
118
            ->orderByDesc('messages_outcomes.timestamp')
8✔
119
            ->get();
8✔
120

121
        $count = 0;
8✔
122
        $deletedMsgids = [];
8✔
123
        foreach ($msgs as $msg) {
8✔
124
            if ($msg->outcome === Message::OUTCOME_WITHDRAWN || $msg->outcome === Message::OUTCOME_EXPIRED) {
8✔
125
                if (!$dryRun) {
2✔
126
                    DB::table('messages_spatial')->where('id', $msg->id)->delete();
2✔
127
                    $deletedMsgids[] = $msg->msgid;
2✔
128
                }
129
                $count++;
2✔
130
            } elseif ($msg->outcome === Message::OUTCOME_TAKEN || $msg->outcome === Message::OUTCOME_RECEIVED) {
8✔
131
                if (!$msg->successful) {
8✔
132
                    if (!$dryRun) {
8✔
133
                        DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);
8✔
134
                    }
135
                    $count++;
8✔
136
                }
137
            } elseif ($msg->successful) {
8✔
138
                if (!$dryRun) {
×
139
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);
×
140
                }
141
                $count++;
×
142
            }
143

144
            if ($msg->promised && !$msg->promisedat) {
8✔
145
                if (!$dryRun) {
×
146
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);
×
147
                }
148
                $count++;
×
149
            } elseif (!$msg->promised && $msg->promisedat) {
8✔
150
                if (!$dryRun) {
×
151
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);
×
152
                }
153
                $count++;
×
154
            }
155
        }
156

157
        if (!empty($deletedMsgids)) {
8✔
158
            $this->spatialAdmin->removeItems('messages', $deletedMsgids);
2✔
159
        }
160

161
        return $count;
8✔
162
    }
163

164
    private function removeDeletedMessages(bool $dryRun = false): int
8✔
165
    {
166
        $rows = DB::table('messages_spatial')
8✔
167
            ->join('messages', 'messages_spatial.msgid', '=', 'messages.id')
8✔
168
            ->leftJoin('users', 'users.id', '=', 'messages.fromuser')
8✔
169
            ->where(function ($q) {
8✔
170
                $q->whereNull('messages.fromuser')
8✔
171
                    ->orWhereNotNull('messages.deleted')
8✔
172
                    ->orWhereNotNull('users.deleted');
8✔
173
            })
8✔
174
            ->select('messages_spatial.id', 'messages_spatial.msgid')
8✔
175
            ->get();
8✔
176

177
        if ($rows->isEmpty()) {
8✔
178
            return 0;
6✔
179
        }
180

181
        if (!$dryRun) {
2✔
182
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
2✔
183
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
2✔
184
        }
185

186
        return $rows->count();
2✔
187
    }
188

189
    private function removeOldMessages(bool $dryRun = false): int
8✔
190
    {
191
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));
8✔
192

193
        $rows = DB::table('messages_spatial')
8✔
194
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
8✔
195
            ->where('messages_groups.arrival', '<', $cutoff)
8✔
196
            ->select('messages_spatial.id', 'messages_spatial.msgid')
8✔
197
            ->get();
8✔
198

199
        if ($rows->isEmpty()) {
8✔
200
            return 0;
7✔
201
        }
202

203
        if (!$dryRun) {
1✔
204
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
1✔
205
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
1✔
206
        }
207

208
        return $rows->count();
1✔
209
    }
210

211
    private function removeNonApprovedMessages(bool $dryRun = false): int
8✔
212
    {
213
        // Join on BOTH msgid AND groupid: messages_spatial holds one row per post (unique
214
        // msgid) for a specific group, so a spatial row must only be dropped when the
215
        // messages_groups row for ITS OWN group is non-approved. Joining on msgid alone
216
        // would let a rippled-in Pending row on another group (#6) delete the origin post's
217
        // approved spatial row, flickering it out of browse every spatial-index run.
218
        $rows = DB::table('messages_spatial')
8✔
219
            ->join('messages_groups', function ($join) {
8✔
220
                $join->on('messages_groups.msgid', '=', 'messages_spatial.msgid')
8✔
221
                    ->on('messages_groups.groupid', '=', 'messages_spatial.groupid');
8✔
222
            })
8✔
223
            ->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)
8✔
224
            ->select('messages_spatial.id', 'messages_spatial.msgid')
8✔
225
            ->get();
8✔
226

227
        if ($rows->isEmpty()) {
8✔
228
            return 0;
8✔
229
        }
230

UNCOV
231
        if (!$dryRun) {
×
UNCOV
232
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
×
UNCOV
233
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
×
234
        }
235

UNCOV
236
        return $rows->count();
×
237
    }
238

239
    /**
240
     * Add a single just-approved message to the spatial index immediately, so it
241
     * appears in browse/search without waiting for the every-5-minute reconciler.
242
     *
243
     * No-op unless the message is Approved, has a location, and has no outcome —
244
     * messages_spatial backs the public browse/map, so Pending/Spam/Rejected
245
     * messages must never be added here. Safe to call inside the same transaction
246
     * that set the collection to Approved (it reads its own uncommitted write).
247
     */
248
    public function addApprovedMessage(int $msgid): void
4✔
249
    {
250
        $msg = DB::table('messages')
4✔
251
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
4✔
252
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
4✔
253
            ->where('messages.id', $msgid)
4✔
254
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
4✔
255
            ->where('messages_groups.deleted', 0)
4✔
256
            ->whereNull('messages.deleted')
4✔
257
            ->whereNotNull('messages.lat')
4✔
258
            ->whereNotNull('messages.lng')
4✔
259
            ->whereNull('messages_outcomes.id')
4✔
260
            ->orderByDesc('messages_groups.arrival')
4✔
261
            ->select(
4✔
262
                'messages.id',
4✔
263
                'messages.lat',
4✔
264
                'messages.lng',
4✔
265
                DB::raw('messages.type as msgtype'),
4✔
266
                'messages_groups.groupid',
4✔
267
                'messages_groups.arrival',
4✔
268
            )
4✔
269
            ->first();
4✔
270

271
        if (!$msg) {
4✔
272
            return;
3✔
273
        }
274

275
        // Coordinates come from the DB, not user input — safe to embed in WKT.
276
        $wkt  = "POINT({$msg->lng} {$msg->lat})";
1✔
277
        $srid = self::SRID;
1✔
278

279
        DB::statement(
1✔
280
            "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
1✔
281
             VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
1✔
282
             ON DUPLICATE KEY UPDATE
283
               point = ST_GeomFromText('$wkt', $srid),
1✔
284
               groupid = ?,
285
               msgtype = ?,
286
               arrival = ?",
1✔
287
            [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
1✔
288
             $msg->groupid, $msg->msgtype, $msg->arrival]
1✔
289
        );
1✔
290
    }
291
}
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