11404

Committed 08 May 2026 04:22PM UTC coverage: 72.752% (+0.06%) from 72.691%

Build # 11404

Build Type

push

circleci

Committed by

fnnbrr

Commit Message

Merge remote-tracking branch 'origin/master' into tn-integration-refactor

Coverage Stats

13726 of 20598 branches covered (66.64%)

Branch coverage included in aggregate %.

3277 of 4560 new or added lines in 82 files covered. (71.86%)

25 existing lines in 7 files now uncovered.

102718 of 139457 relevant lines covered (73.66%)

22.46 hits per line

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

91.18

/iznik-batch/app/Services/MessageSpatialService.php

<?php

namespace App\Services;

use App\Models\Message;
use App\Models\MessageGroup;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class MessageSpatialService
{
    // V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago"
    private const RECENT_DAYS = 31;
    private const SRID = 3857;

    public function updateSpatialIndex(bool $dryRun = false): array
    {
        $stats = [
            'upserted_recent' => $this->upsertRecentMessages($dryRun),
            'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),
            'removed_deleted' => $this->removeDeletedMessages($dryRun),
            'removed_old' => $this->removeOldMessages($dryRun),
            'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),
        ];

        $total = array_sum($stats);
        $stats['total'] = $total;

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

        return $stats;
    }

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

        $msgs = DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->join('users', 'users.id', '=', 'messages.fromuser')
            ->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
            ->where('messages_groups.arrival', '>=', $cutoff)
            ->whereNotNull('messages.lat')
            ->whereNotNull('messages.lng')
            ->whereNull('messages.deleted')
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            ->whereNull('users.deleted')
            ->where(function ($q) {
                // Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
                $q->whereNull('messages_outcomes.outcome')
                    ->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);
            })
            ->where(function ($q) {
                $q->whereNull('messages_spatial.msgid')
                    ->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')
                    ->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')
                    ->orWhereNull('messages_spatial.groupid')
                    ->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')
                    ->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');
            })
            ->select(
                'messages.id',
                'messages.lat',
                'messages.lng',
                'messages_groups.groupid',
                'messages_groups.arrival',
                'messages_groups.msgtype',
            )
            ->distinct()
            ->get();

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

                DB::statement(
                    "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
                     VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
                     ON DUPLICATE KEY UPDATE
                       point = ST_GeomFromText('$wkt', $srid),
                       groupid = ?,
                       msgtype = ?,
                       arrival = ?",
                    [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
                     $msg->groupid, $msg->msgtype, $msg->arrival]
                );
            }
            $count++;
        }

        return $count;
    }

    private function updateOutcomesAndPromises(bool $dryRun = false): int
    {
        $msgs = DB::table('messages_spatial')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')
            ->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')
            ->select(
                'messages_spatial.id',
                'messages_spatial.msgid',
                'messages_spatial.successful',
                'messages_spatial.promised',
                'messages_outcomes.outcome',
                'messages_promises.promisedat',
            )
            ->orderByDesc('messages_outcomes.timestamp')
            ->get();

        $count = 0;
        foreach ($msgs as $msg) {
            if ($msg->outcome === Message::OUTCOME_WITHDRAWN || $msg->outcome === Message::OUTCOME_EXPIRED) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->delete();
                }
                $count++;
            } elseif ($msg->outcome === Message::OUTCOME_TAKEN || $msg->outcome === Message::OUTCOME_RECEIVED) {
                if (!$msg->successful) {
                    if (!$dryRun) {
                        DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);
                    }
                    $count++;
                }
            } elseif ($msg->successful) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);
                }
                $count++;
            }

            if ($msg->promised && !$msg->promisedat) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);
                }
                $count++;
            } elseif (!$msg->promised && $msg->promisedat) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);
                }
                $count++;
            }
        }

        return $count;
    }

    private function removeDeletedMessages(bool $dryRun = false): int
    {
        $ids = DB::table('messages_spatial')
            ->join('messages', 'messages_spatial.msgid', '=', 'messages.id')
            ->leftJoin('users', 'users.id', '=', 'messages.fromuser')
            ->where(function ($q) {
                $q->whereNull('messages.fromuser')
                    ->orWhereNotNull('messages.deleted')
                    ->orWhereNotNull('users.deleted');
            })
            ->pluck('messages_spatial.id');

        if ($ids->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $ids)->delete();
        }

        return $ids->count();
    }

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

        $ids = DB::table('messages_spatial')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
            ->where('messages_groups.arrival', '<', $cutoff)
            ->pluck('messages_spatial.id');

        if ($ids->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $ids)->delete();
        }

        return $ids->count();
    }

    private function removeNonApprovedMessages(bool $dryRun = false): int
    {
        $ids = DB::table('messages_spatial')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
            ->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)
            ->pluck('messages_spatial.id');

        if ($ids->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $ids)->delete();
        }

        return $ids->count();
    }
}

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	public function updateSpatialIndex(bool $dryRun = false): array	5✔
17	{
18	$stats = [	5✔
19	'upserted_recent' => $this->upsertRecentMessages($dryRun),	5✔
20	'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),	5✔
21	'removed_deleted' => $this->removeDeletedMessages($dryRun),	5✔
22	'removed_old' => $this->removeOldMessages($dryRun),	5✔
23	'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),	5✔
24	];	5✔
25
26	$total = array_sum($stats);	5✔
27	$stats['total'] = $total;	5✔
28
29	Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);	5✔
30
31	return $stats;	5✔
32	}
33
34	private function upsertRecentMessages(bool $dryRun = false): int	5✔
35	{
36	$cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));	5✔
37
38	$msgs = DB::table('messages')	5✔
39	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	5✔
40	->join('users', 'users.id', '=', 'messages.fromuser')	5✔
41	->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')	5✔
42	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')	5✔
43	->where('messages_groups.arrival', '>=', $cutoff)	5✔
44	->whereNotNull('messages.lat')	5✔
45	->whereNotNull('messages.lng')	5✔
46	->whereNull('messages.deleted')	5✔
47	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	5✔
48	->whereNull('users.deleted')	5✔
49	->where(function ($q) {	5✔
50	// Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
51	$q->whereNull('messages_outcomes.outcome')	5✔
52	->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);	5✔
53	})	5✔
54	->where(function ($q) {	5✔
55	$q->whereNull('messages_spatial.msgid')	5✔
56	->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')	5✔
57	->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')	5✔
58	->orWhereNull('messages_spatial.groupid')	5✔
59	->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')	5✔
60	->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');	5✔
61	})	5✔
62	->select(	5✔
63	'messages.id',	5✔
64	'messages.lat',	5✔
65	'messages.lng',	5✔
66	'messages_groups.groupid',	5✔
67	'messages_groups.arrival',	5✔
68	'messages_groups.msgtype',	5✔
69	)	5✔
70	->distinct()	5✔
71	->get();	5✔
72
73	$count = 0;	5✔
74	foreach ($msgs as $msg) {	5✔
75	if (!$dryRun) {	5✔
76	// Coordinates come from DB, not user input — safe to embed in WKT.
77	$wkt = "POINT({$msg->lng} {$msg->lat})";	5✔
78	$srid = self::SRID;	5✔
79
80	DB::statement(	5✔
81	"INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)	5✔
82	VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)	5✔
83	ON DUPLICATE KEY UPDATE
84	point = ST_GeomFromText('$wkt', $srid),	5✔
85	groupid = ?,
86	msgtype = ?,
87	arrival = ?",	5✔
88	[$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,	5✔
89	$msg->groupid, $msg->msgtype, $msg->arrival]	5✔
90	);	5✔
91	}
92	$count++;	5✔
93	}
94
95	return $count;	5✔
96	}
97
98	private function updateOutcomesAndPromises(bool $dryRun = false): int	5✔
99	{
100	$msgs = DB::table('messages_spatial')	5✔
101	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')	5✔
102	->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')	5✔
103	->select(	5✔
104	'messages_spatial.id',	5✔
105	'messages_spatial.msgid',	5✔
106	'messages_spatial.successful',	5✔
107	'messages_spatial.promised',	5✔
108	'messages_outcomes.outcome',	5✔
109	'messages_promises.promisedat',	5✔
110	)	5✔
111	->orderByDesc('messages_outcomes.timestamp')	5✔
112	->get();	5✔
113
114	$count = 0;	5✔
115	foreach ($msgs as $msg) {	5✔
116	if ($msg->outcome === Message::OUTCOME_WITHDRAWN \|\| $msg->outcome === Message::OUTCOME_EXPIRED) {	5✔
117	if (!$dryRun) {	1✔
118	DB::table('messages_spatial')->where('id', $msg->id)->delete();	1✔
119	}
120	$count++;	1✔
121	} elseif ($msg->outcome === Message::OUTCOME_TAKEN \|\| $msg->outcome === Message::OUTCOME_RECEIVED) {	5✔
122	if (!$msg->successful) {	5✔
123	if (!$dryRun) {	5✔
124	DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);	5✔
125	}
126	$count++;	5✔
127	}
128	} elseif ($msg->successful) {	5✔
NEW 129	if (!$dryRun) {	×
NEW 130	DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);	×
131	}
NEW 132	$count++;	×
133	}
134
135	if ($msg->promised && !$msg->promisedat) {	5✔
NEW 136	if (!$dryRun) {	×
NEW 137	DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);	×
138	}
NEW 139	$count++;	×
140	} elseif (!$msg->promised && $msg->promisedat) {	5✔
NEW 141	if (!$dryRun) {	×
NEW 142	DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);	×
143	}
NEW 144	$count++;	×
145	}
146	}
147
148	return $count;	5✔
149	}
150
151	private function removeDeletedMessages(bool $dryRun = false): int	5✔
152	{
153	$ids = DB::table('messages_spatial')	5✔
154	->join('messages', 'messages_spatial.msgid', '=', 'messages.id')	5✔
155	->leftJoin('users', 'users.id', '=', 'messages.fromuser')	5✔
156	->where(function ($q) {	5✔
157	$q->whereNull('messages.fromuser')	5✔
158	->orWhereNotNull('messages.deleted')	5✔
159	->orWhereNotNull('users.deleted');	5✔
160	})	5✔
161	->pluck('messages_spatial.id');	5✔
162
163	if ($ids->isEmpty()) {	5✔
164	return 0;	4✔
165	}
166
167	if (!$dryRun) {	1✔
168	DB::table('messages_spatial')->whereIn('id', $ids)->delete();	1✔
169	}
170
171	return $ids->count();	1✔
172	}
173
174	private function removeOldMessages(bool $dryRun = false): int	5✔
175	{
176	$cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));	5✔
177
178	$ids = DB::table('messages_spatial')	5✔
179	->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')	5✔
180	->where('messages_groups.arrival', '<', $cutoff)	5✔
181	->pluck('messages_spatial.id');	5✔
182
183	if ($ids->isEmpty()) {	5✔
184	return 0;	4✔
185	}
186
187	if (!$dryRun) {	1✔
188	DB::table('messages_spatial')->whereIn('id', $ids)->delete();	1✔
189	}
190
191	return $ids->count();	1✔
192	}
193
194	private function removeNonApprovedMessages(bool $dryRun = false): int	5✔
195	{
196	$ids = DB::table('messages_spatial')	5✔
197	->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')	5✔
198	->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)	5✔
199	->pluck('messages_spatial.id');	5✔
200
201	if ($ids->isEmpty()) {	5✔
202	return 0;	5✔
203	}
204
NEW 205	if (!$dryRun) {	×
NEW 206	DB::table('messages_spatial')->whereIn('id', $ids)->delete();	×
207	}
208
NEW 209	return $ids->count();	×
210	}
211	}

Freegle / Iznik / 11404

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