11399

Committed 08 May 2026 04:18PM UTC coverage: 72.847% (-0.01%) from 72.861%

Build # 11399

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #404 from Freegle/fix/address-null-island-lat-lng

fix(address): use *float64 for lat/lng to store NULL instead of 0 (Null Island fix)

Coverage Stats

13763 of 20701 branches covered (66.48%)

Branch coverage included in aggregate %.

101962 of 138160 relevant lines covered (73.8%)

22.39 hits per line

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

92.21

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

<?php

namespace App\Services;

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

class EngageUpdateService
{
    // V1: Engage::USER_INACTIVE = 365 * 24 * 60 * 60 / 2
    private const USER_INACTIVE_DAYS = 182;

    // V1: Engage::LOOKBACK = 31
    private const LOOKBACK_DAYS = 31;

    private const RECENT_ACCESS_DAYS = 14;
    private const OCCASIONAL_TO_FREQUENT_POSTS = 3;  // > 3 in 90 days
    private const FREQUENT_TO_OBSESSED_POSTS = 4;    // >= 4 in 31 days
    private const FREQUENT_LOOKBACK_DAYS = 90;
    private const OBSESSED_LOOKBACK_DAYS = 31;

    /**
     * Run a WHERE-driven bulk UPDATE against `users` safely under Galera.
     *
     * Step 1: plucks every matching ID up front (read-only, releases when done).
     * Step 2: updates each row by primary key (narrow PK lock, no gap-lock scan).
     *
     * Matches the per-row pattern used elsewhere in the codebase. Avoids the
     * gap-lock deadlock a full-table-scan UPDATE causes when concurrent
     * writes on `users` overlap with the WHERE column.
     *
     * @param  callable  $applyWhere  receives the query builder, applies WHERE clauses
     * @param  array     $update      column => value to set
     * @return int                    total rows affected
     */
    private function bulkUpdate(callable $applyWhere, array $update): int
    {
        $query = DB::table('users');
        $applyWhere($query);
        $ids = $query->orderBy('id')->pluck('id');

        $total = 0;
        foreach ($ids as $id) {
            DB::table('users')->where('id', $id)->update($update);
            $total++;
        }

        return $total;
    }

    /**
     * Update engagement classifications for all users.
     * Mirrors V1 Engage::updateEngagement().
     *
     * @return int Number of users updated.
     */
    public function updateEngagement(bool $dryRun = false): array
    {
        $stats = [
            'null_to_new' => $this->setNewForRecentNulls($dryRun),
            'null_to_inactive' => $this->setInactiveForRemainingNulls($dryRun),
            'new_or_occasional_to_inactive' => $this->setInactiveForStaleNewOrOccasional($dryRun),
            'to_dormant' => $this->setDormantForLongInactive($dryRun),
            'to_occasional' => $this->setOccasionalForRecentlyActive($dryRun),
            'occasional_to_frequent' => $this->setFrequentForActiveOccasional($dryRun),
            'frequent_to_obsessed' => $this->setObsessedForVeryActiveFrequent($dryRun),
            'obsessed_to_frequent' => $this->setFrequentForDroppedObsessed($dryRun),
        ];
        $stats['total'] = array_sum($stats);

        Log::info('EngageUpdate: ' . ($dryRun ? 'would update ' : 'updated ') . "{$stats['total']} users", $stats);

        return $stats;
    }

    private function setNewForRecentNulls(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(self::LOOKBACK_DAYS)->startOfDay()->toDateString();

        $where = function ($q) use ($cutoff) {
            $q->whereNull('engagement')->where('added', '>=', $cutoff);
        };

        if ($dryRun) {
            $count = DB::table('users')->where($where)->count();
            Log::info("EngageUpdate: NULL => New: would-{$count}");
            return $count;
        }

        $affected = $this->bulkUpdate($where, ['engagement' => 'New']);
        Log::info("EngageUpdate: NULL => New: {$affected}");
        return $affected;
    }

    private function setInactiveForRemainingNulls(bool $dryRun = false): int
    {
        $where = function ($q) {
            $q->whereNull('engagement');
        };

        if ($dryRun) {
            $count = DB::table('users')->where($where)->count();
            Log::info("EngageUpdate: NULL => Inactive: would-{$count}");
            return $count;
        }

        $affected = $this->bulkUpdate($where, ['engagement' => 'Inactive']);
        Log::info("EngageUpdate: NULL => Inactive: {$affected}");
        return $affected;
    }

    private function setInactiveForStaleNewOrOccasional(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(self::RECENT_ACCESS_DAYS)->startOfDay()->toDateString();

        $where = function ($q) use ($cutoff) {
            $q->whereIn('engagement', ['New', 'Occasional'])
                ->where(function ($qq) use ($cutoff) {
                    $qq->whereNull('lastaccess')
                        ->orWhere('lastaccess', '<', $cutoff);
                });
        };

        if ($dryRun) {
            $count = DB::table('users')->where($where)->count();
            Log::info("EngageUpdate: New/Occasional => Inactive: would-{$count}");
            return $count;
        }

        $affected = $this->bulkUpdate($where, ['engagement' => 'Inactive']);
        Log::info("EngageUpdate: New/Occasional => Inactive: {$affected}");
        return $affected;
    }

    private function setDormantForLongInactive(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(self::USER_INACTIVE_DAYS)->startOfDay()->toDateString();

        $where = function ($q) use ($cutoff) {
            $q->where('engagement', '!=', 'Dormant')
                ->where(function ($qq) use ($cutoff) {
                    $qq->whereNull('lastaccess')
                        ->orWhere('lastaccess', '<', $cutoff);
                });
        };

        if ($dryRun) {
            $count = DB::table('users')->where($where)->count();
            Log::info("EngageUpdate: * => Dormant: would-{$count}");
            return $count;
        }

        $affected = $this->bulkUpdate($where, ['engagement' => 'Dormant']);
        Log::info("EngageUpdate: * => Dormant: {$affected}");
        return $affected;
    }

    private function setOccasionalForRecentlyActive(bool $dryRun = false): int
    {
        $recentCutoff = now()->subDays(self::RECENT_ACCESS_DAYS)->startOfDay()->toDateString();
        $recentTimestamp = now()->subDays(self::RECENT_ACCESS_DAYS)->timestamp;

        // Find candidates: recently accessed + engagement in New/Inactive/Dormant
        $candidates = DB::table('users')
            ->whereIn('engagement', ['New', 'Inactive', 'Dormant'])
            ->where('lastaccess', '>=', $recentCutoff)
            ->pluck('id');

        $updated = 0;
        foreach ($candidates as $userId) {
            $lastActivity = $this->lastPostOrReply($userId);

            if ($lastActivity && strtotime($lastActivity) > $recentTimestamp) {
                if (!$dryRun) {
                    DB::table('users')->where('id', $userId)->update(['engagement' => 'Occasional']);
                }
                $updated++;
            }
        }

        Log::info('EngageUpdate: New/Inactive/Dormant => Occasional: ' . ($dryRun ? "would-{$updated}" : $updated));
        return $updated;
    }

    private function setFrequentForActiveOccasional(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(self::FREQUENT_LOOKBACK_DAYS)->toDateString();

        $occasional = DB::table('users')
            ->where('engagement', 'Occasional')
            ->pluck('id');

        $updated = 0;
        foreach ($occasional as $userId) {
            if ($this->postsSince($userId, $cutoff) > self::OCCASIONAL_TO_FREQUENT_POSTS) {
                if (!$dryRun) {
                    DB::table('users')->where('id', $userId)->update(['engagement' => 'Frequent']);
                }
                $updated++;
            }
        }

        Log::info('EngageUpdate: Occasional => Frequent: ' . ($dryRun ? "would-{$updated}" : $updated));
        return $updated;
    }

    private function setObsessedForVeryActiveFrequent(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(self::OBSESSED_LOOKBACK_DAYS)->toDateString();

        $frequent = DB::table('users')
            ->where('engagement', 'Frequent')
            ->pluck('id');

        $updated = 0;
        foreach ($frequent as $userId) {
            if ($this->postsSince($userId, $cutoff) >= self::FREQUENT_TO_OBSESSED_POSTS) {
                if (!$dryRun) {
                    DB::table('users')->where('id', $userId)->update(['engagement' => 'Obsessed']);
                }
                $updated++;
            }
        }

        Log::info('EngageUpdate: Frequent => Obsessed: ' . ($dryRun ? "would-{$updated}" : $updated));
        return $updated;
    }

    private function setFrequentForDroppedObsessed(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(self::FREQUENT_LOOKBACK_DAYS)->toDateString();

        $obsessed = DB::table('users')
            ->where('engagement', 'Obsessed')
            ->pluck('id');

        $updated = 0;
        foreach ($obsessed as $userId) {
            if ($this->postsSince($userId, $cutoff) <= self::OCCASIONAL_TO_FREQUENT_POSTS) {
                if (!$dryRun) {
                    DB::table('users')->where('id', $userId)->update(['engagement' => 'Frequent']);
                }
                $updated++;
            }
        }

        Log::info('EngageUpdate: Obsessed => Frequent: ' . ($dryRun ? "would-{$updated}" : $updated));
        return $updated;
    }

    private function lastPostOrReply(int $userId): ?string
    {
        $lastChat = DB::table('chat_messages')
            ->where('userid', $userId)
            ->max('date');

        $lastMessage = DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->where('messages.fromuser', $userId)
            ->max('messages_groups.arrival');

        if (!$lastChat && !$lastMessage) {
            return null;
        }

        if (!$lastChat) {
            return $lastMessage;
        }

        if (!$lastMessage) {
            return $lastChat;
        }

        return strtotime($lastChat) > strtotime($lastMessage) ? $lastChat : $lastMessage;
    }

    private function postsSince(int $userId, string $since): int
    {
        return DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->where('messages.fromuser', $userId)
            ->where('messages_groups.arrival', '>=', $since)
            ->count();
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use Illuminate\Support\Facades\DB;
6	use Illuminate\Support\Facades\Log;
7
8	class EngageUpdateService
9	{
10	// V1: Engage::USER_INACTIVE = 365 * 24 * 60 * 60 / 2
11	private const USER_INACTIVE_DAYS = 182;
12
13	// V1: Engage::LOOKBACK = 31
14	private const LOOKBACK_DAYS = 31;
15
16	private const RECENT_ACCESS_DAYS = 14;
17	private const OCCASIONAL_TO_FREQUENT_POSTS = 3; // > 3 in 90 days
18	private const FREQUENT_TO_OBSESSED_POSTS = 4; // >= 4 in 31 days
19	private const FREQUENT_LOOKBACK_DAYS = 90;
20	private const OBSESSED_LOOKBACK_DAYS = 31;
21
22	/**
23	* Run a WHERE-driven bulk UPDATE against `users` safely under Galera.
24	*
25	* Step 1: plucks every matching ID up front (read-only, releases when done).
26	* Step 2: updates each row by primary key (narrow PK lock, no gap-lock scan).
27	*
28	* Matches the per-row pattern used elsewhere in the codebase. Avoids the
29	* gap-lock deadlock a full-table-scan UPDATE causes when concurrent
30	* writes on `users` overlap with the WHERE column.
31	*
32	* @param callable $applyWhere receives the query builder, applies WHERE clauses
33	* @param array $update column => value to set
34	* @return int total rows affected
35	*/
36	private function bulkUpdate(callable $applyWhere, array $update): int	14✔
37	{
38	$query = DB::table('users');	14✔
39	$applyWhere($query);	14✔
40	$ids = $query->orderBy('id')->pluck('id');	14✔
41
42	$total = 0;	14✔
43	foreach ($ids as $id) {	14✔
44	DB::table('users')->where('id', $id)->update($update);	14✔
45	$total++;	14✔
46	}
47
48	return $total;	14✔
49	}
50
51	/**
52	* Update engagement classifications for all users.
53	* Mirrors V1 Engage::updateEngagement().
54	*
55	* @return int Number of users updated.
56	*/
57	public function updateEngagement(bool $dryRun = false): array	14✔
58	{
59	$stats = [	14✔
60	'null_to_new' => $this->setNewForRecentNulls($dryRun),	14✔
61	'null_to_inactive' => $this->setInactiveForRemainingNulls($dryRun),	14✔
62	'new_or_occasional_to_inactive' => $this->setInactiveForStaleNewOrOccasional($dryRun),	14✔
63	'to_dormant' => $this->setDormantForLongInactive($dryRun),	14✔
64	'to_occasional' => $this->setOccasionalForRecentlyActive($dryRun),	14✔
65	'occasional_to_frequent' => $this->setFrequentForActiveOccasional($dryRun),	14✔
66	'frequent_to_obsessed' => $this->setObsessedForVeryActiveFrequent($dryRun),	14✔
67	'obsessed_to_frequent' => $this->setFrequentForDroppedObsessed($dryRun),	14✔
68	];	14✔
69	$stats['total'] = array_sum($stats);	14✔
70
71	Log::info('EngageUpdate: ' . ($dryRun ? 'would update ' : 'updated ') . "{$stats['total']} users", $stats);	14✔
72
73	return $stats;	14✔
74	}
75
76	private function setNewForRecentNulls(bool $dryRun = false): int	14✔
77	{
78	$cutoff = now()->subDays(self::LOOKBACK_DAYS)->startOfDay()->toDateString();	14✔
79
80	$where = function ($q) use ($cutoff) {	14✔
81	$q->whereNull('engagement')->where('added', '>=', $cutoff);	14✔
82	};	14✔
83
84	if ($dryRun) {	14✔
85	$count = DB::table('users')->where($where)->count();	×
86	Log::info("EngageUpdate: NULL => New: would-{$count}");	×
87	return $count;	×
88	}
89
90	$affected = $this->bulkUpdate($where, ['engagement' => 'New']);	14✔
91	Log::info("EngageUpdate: NULL => New: {$affected}");	14✔
92	return $affected;	14✔
93	}
94
95	private function setInactiveForRemainingNulls(bool $dryRun = false): int	14✔
96	{
97	$where = function ($q) {	14✔
98	$q->whereNull('engagement');	14✔
99	};	14✔
100
101	if ($dryRun) {	14✔
102	$count = DB::table('users')->where($where)->count();	×
103	Log::info("EngageUpdate: NULL => Inactive: would-{$count}");	×
104	return $count;	×
105	}
106
107	$affected = $this->bulkUpdate($where, ['engagement' => 'Inactive']);	14✔
108	Log::info("EngageUpdate: NULL => Inactive: {$affected}");	14✔
109	return $affected;	14✔
110	}
111
112	private function setInactiveForStaleNewOrOccasional(bool $dryRun = false): int	14✔
113	{
114	$cutoff = now()->subDays(self::RECENT_ACCESS_DAYS)->startOfDay()->toDateString();	14✔
115
116	$where = function ($q) use ($cutoff) {	14✔
117	$q->whereIn('engagement', ['New', 'Occasional'])	14✔
118	->where(function ($qq) use ($cutoff) {	14✔
119	$qq->whereNull('lastaccess')	14✔
120	->orWhere('lastaccess', '<', $cutoff);	14✔
121	});	14✔
122	};	14✔
123
124	if ($dryRun) {	14✔
125	$count = DB::table('users')->where($where)->count();	×
126	Log::info("EngageUpdate: New/Occasional => Inactive: would-{$count}");	×
127	return $count;	×
128	}
129
130	$affected = $this->bulkUpdate($where, ['engagement' => 'Inactive']);	14✔
131	Log::info("EngageUpdate: New/Occasional => Inactive: {$affected}");	14✔
132	return $affected;	14✔
133	}
134
135	private function setDormantForLongInactive(bool $dryRun = false): int	14✔
136	{
137	$cutoff = now()->subDays(self::USER_INACTIVE_DAYS)->startOfDay()->toDateString();	14✔
138
139	$where = function ($q) use ($cutoff) {	14✔
140	$q->where('engagement', '!=', 'Dormant')	14✔
141	->where(function ($qq) use ($cutoff) {	14✔
142	$qq->whereNull('lastaccess')	14✔
143	->orWhere('lastaccess', '<', $cutoff);	14✔
144	});	14✔
145	};	14✔
146
147	if ($dryRun) {	14✔
148	$count = DB::table('users')->where($where)->count();	×
149	Log::info("EngageUpdate: * => Dormant: would-{$count}");	×
150	return $count;	×
151	}
152
153	$affected = $this->bulkUpdate($where, ['engagement' => 'Dormant']);	14✔
154	Log::info("EngageUpdate: * => Dormant: {$affected}");	14✔
155	return $affected;	14✔
156	}
157
158	private function setOccasionalForRecentlyActive(bool $dryRun = false): int	14✔
159	{
160	$recentCutoff = now()->subDays(self::RECENT_ACCESS_DAYS)->startOfDay()->toDateString();	14✔
161	$recentTimestamp = now()->subDays(self::RECENT_ACCESS_DAYS)->timestamp;	14✔
162
163	// Find candidates: recently accessed + engagement in New/Inactive/Dormant
164	$candidates = DB::table('users')	14✔
165	->whereIn('engagement', ['New', 'Inactive', 'Dormant'])	14✔
166	->where('lastaccess', '>=', $recentCutoff)	14✔
167	->pluck('id');	14✔
168
169	$updated = 0;	14✔
170	foreach ($candidates as $userId) {	14✔
171	$lastActivity = $this->lastPostOrReply($userId);	14✔
172
173	if ($lastActivity && strtotime($lastActivity) > $recentTimestamp) {	14✔
174	if (!$dryRun) {	14✔
175	DB::table('users')->where('id', $userId)->update(['engagement' => 'Occasional']);	14✔
176	}
177	$updated++;	14✔
178	}
179	}
180
181	Log::info('EngageUpdate: New/Inactive/Dormant => Occasional: ' . ($dryRun ? "would-{$updated}" : $updated));	14✔
182	return $updated;	14✔
183	}
184
185	private function setFrequentForActiveOccasional(bool $dryRun = false): int	14✔
186	{
187	$cutoff = now()->subDays(self::FREQUENT_LOOKBACK_DAYS)->toDateString();	14✔
188
189	$occasional = DB::table('users')	14✔
190	->where('engagement', 'Occasional')	14✔
191	->pluck('id');	14✔
192
193	$updated = 0;	14✔
194	foreach ($occasional as $userId) {	14✔
195	if ($this->postsSince($userId, $cutoff) > self::OCCASIONAL_TO_FREQUENT_POSTS) {	14✔
196	if (!$dryRun) {	1✔
197	DB::table('users')->where('id', $userId)->update(['engagement' => 'Frequent']);	1✔
198	}
199	$updated++;	1✔
200	}
201	}
202
203	Log::info('EngageUpdate: Occasional => Frequent: ' . ($dryRun ? "would-{$updated}" : $updated));	14✔
204	return $updated;	14✔
205	}
206
207	private function setObsessedForVeryActiveFrequent(bool $dryRun = false): int	14✔
208	{
209	$cutoff = now()->subDays(self::OBSESSED_LOOKBACK_DAYS)->toDateString();	14✔
210
211	$frequent = DB::table('users')	14✔
212	->where('engagement', 'Frequent')	14✔
213	->pluck('id');	14✔
214
215	$updated = 0;	14✔
216	foreach ($frequent as $userId) {	14✔
217	if ($this->postsSince($userId, $cutoff) >= self::FREQUENT_TO_OBSESSED_POSTS) {	2✔
218	if (!$dryRun) {	1✔
219	DB::table('users')->where('id', $userId)->update(['engagement' => 'Obsessed']);	1✔
220	}
221	$updated++;	1✔
222	}
223	}
224
225	Log::info('EngageUpdate: Frequent => Obsessed: ' . ($dryRun ? "would-{$updated}" : $updated));	14✔
226	return $updated;	14✔
227	}
228
229	private function setFrequentForDroppedObsessed(bool $dryRun = false): int	14✔
230	{
231	$cutoff = now()->subDays(self::FREQUENT_LOOKBACK_DAYS)->toDateString();	14✔
232
233	$obsessed = DB::table('users')	14✔
234	->where('engagement', 'Obsessed')	14✔
235	->pluck('id');	14✔
236
237	$updated = 0;	14✔
238	foreach ($obsessed as $userId) {	14✔
239	if ($this->postsSince($userId, $cutoff) <= self::OCCASIONAL_TO_FREQUENT_POSTS) {	2✔
240	if (!$dryRun) {	1✔
241	DB::table('users')->where('id', $userId)->update(['engagement' => 'Frequent']);	1✔
242	}
243	$updated++;	1✔
244	}
245	}
246
247	Log::info('EngageUpdate: Obsessed => Frequent: ' . ($dryRun ? "would-{$updated}" : $updated));	14✔
248	return $updated;	14✔
249	}
250
251	private function lastPostOrReply(int $userId): ?string	14✔
252	{
253	$lastChat = DB::table('chat_messages')	14✔
254	->where('userid', $userId)	14✔
255	->max('date');	14✔
256
257	$lastMessage = DB::table('messages')	14✔
258	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	14✔
259	->where('messages.fromuser', $userId)	14✔
260	->max('messages_groups.arrival');	14✔
261
262	if (!$lastChat && !$lastMessage) {	14✔
263	return null;	14✔
264	}
265
266	if (!$lastChat) {	14✔
267	return $lastMessage;	14✔
268	}
269
270	if (!$lastMessage) {	14✔
271	return $lastChat;	14✔
272	}
273
274	return strtotime($lastChat) > strtotime($lastMessage) ? $lastChat : $lastMessage;	14✔
275	}
276
277	private function postsSince(int $userId, string $since): int	14✔
278	{
279	return DB::table('messages')	14✔
280	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	14✔
281	->where('messages.fromuser', $userId)	14✔
282	->where('messages_groups.arrival', '>=', $since)	14✔
283	->count();	14✔
284	}
285	}

Freegle / Iznik / 11399

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