SubscribersRepository.php — MailPoet – Newsletters, Email Marketing, and Automation 5.33.1

mailpoet / lib / Subscribers / SubscribersRepository.php

mailpoet / lib / Subscribers Last commit date

ConfirmationEmailTemplate 2 months ago ImportExport 1 month ago RestApi 1 month ago Statistics 2 months ago BulkActionController.php 3 months ago BulkActionException.php 3 months ago BulkConfirmationEmailResender.php 3 months ago ConfirmationEmailCustomizer.php 3 months ago ConfirmationEmailMailer.php 3 months ago ConfirmationEmailResolver.php 3 months ago EngagementDataBackfiller.php 3 months ago InactiveSubscribersController.php 1 month ago LinkTokens.php 3 months ago NewSubscriberNotificationMailer.php 3 months ago RequiredCustomFieldValidator.php 3 months ago SegmentsCountRecalculator.php 1 month ago Source.php 3 months ago SubscriberActions.php 3 months ago SubscriberCustomFieldRepository.php 3 years ago SubscriberIPsRepository.php 2 years ago SubscriberLimitNotificationEvaluator.php 3 months ago SubscriberLimitNotificationMailer.php 3 months ago SubscriberLimitNotificationScheduler.php 3 months ago SubscriberListingRepository.php 1 month ago SubscriberPersonalDataEraser.php 3 months ago SubscriberSaveController.php 3 months ago SubscriberSegmentRepository.php 1 month ago SubscriberSubscribeController.php 1 month ago SubscriberTagRepository.php 4 years ago SubscribersCountsController.php 1 month ago SubscribersEmailCountsController.php 1 month ago SubscribersRepository.php 1 month ago index.php 3 years ago

SubscribersRepository.php

1444 lines

1	<?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing
2
3	namespace MailPoet\Subscribers;
4
5	if (!defined('ABSPATH')) exit;
6
7
8	use DateTimeInterface;
9	use MailPoet\Config\SubscriberChangesNotifier;
10	use MailPoet\Doctrine\Repository;
11	use MailPoet\Entities\SegmentEntity;
12	use MailPoet\Entities\StatisticsUnsubscribeEntity;
13	use MailPoet\Entities\SubscriberCustomFieldEntity;
14	use MailPoet\Entities\SubscriberEntity;
15	use MailPoet\Entities\SubscriberSegmentEntity;
16	use MailPoet\Entities\SubscriberTagEntity;
17	use MailPoet\Entities\TagEntity;
18	use MailPoet\Segments\SegmentsRepository;
19	use MailPoet\Subscribers\Source;
20	use MailPoet\Util\License\Features\Subscribers;
21	use MailPoet\WP\Functions as WPFunctions;
22	use MailPoetVendor\Carbon\Carbon;
23	use MailPoetVendor\Doctrine\DBAL\ArrayParameterType;
24	use MailPoetVendor\Doctrine\DBAL\ParameterType;
25	use MailPoetVendor\Doctrine\ORM\EntityManager;
26	use MailPoetVendor\Doctrine\ORM\Query\Expr\Join;
27
28	/**
29	* @extends Repository<SubscriberEntity>
30	*/
31	class SubscribersRepository extends Repository {
32	/** @var WPFunctions */
33	private $wp;
34
35	protected $ignoreColumnsForUpdate = [
36	'wp_user_id',
37	'is_woocommerce_user',
38	'email',
39	'created_at',
40	'last_subscribed_at',
41	];
42
43	/** @var SubscriberChangesNotifier */
44	private $changesNotifier;
45
46	/** @var SegmentsRepository */
47	private $segmentsRepository;
48
49	/** @var SegmentsCountRecalculator */
50	private $segmentsCountRecalculator;
51
52	public function __construct(
53	EntityManager $entityManager,
54	SubscriberChangesNotifier $changesNotifier,
55	WPFunctions $wp,
56	SegmentsRepository $segmentsRepository,
57	SegmentsCountRecalculator $segmentsCountRecalculator
58	) {
59	$this->wp = $wp;
60	parent::__construct($entityManager);
61	$this->changesNotifier = $changesNotifier;
62	$this->segmentsRepository = $segmentsRepository;
63	$this->segmentsCountRecalculator = $segmentsCountRecalculator;
64	}
65
66	protected function getEntityClassName() {
67	return SubscriberEntity::class;
68	}
69
70	public function getTotalSubscribers(): int {
71	return $this->getCountOfSubscribersForStates([
72	SubscriberEntity::STATUS_SUBSCRIBED,
73	SubscriberEntity::STATUS_UNCONFIRMED,
74	SubscriberEntity::STATUS_INACTIVE,
75	]);
76	}
77
78	public function getCountOfSubscribersForStates(array $states): int {
79	$query = $this->entityManager
80	->createQueryBuilder()
81	->select('count(n.id)')
82	->from(SubscriberEntity::class, 'n')
83	->where('n.deletedAt IS NULL AND n.status IN (:statuses)')
84	->setParameter('statuses', $states)
85	->getQuery();
86	return intval($query->getSingleScalarResult());
87	}
88
89	/**
90	* Per-status counts across all subscribers (the "All Lists" listing tabs).
91	* One grouped scan plus a trash count — exact, but O(subscribers), so it is
92	* cached and cron-warmed rather than run on every page load.
93	*
94	* @return array<string, int>
95	*/
96	public function getStatusStatisticsCount(): array {
97	$counts = [
98	'all' => 0,
99	'trash' => 0,
100	SubscriberEntity::STATUS_SUBSCRIBED => 0,
101	SubscriberEntity::STATUS_UNSUBSCRIBED => 0,
102	SubscriberEntity::STATUS_INACTIVE => 0,
103	SubscriberEntity::STATUS_UNCONFIRMED => 0,
104	SubscriberEntity::STATUS_BOUNCED => 0,
105	];
106
107	$rows = $this->entityManager->createQueryBuilder()
108	->select('s.status AS status, COUNT(s.id) AS subscribersCount')
109	->from(SubscriberEntity::class, 's')
110	->where('s.deletedAt IS NULL')
111	->groupBy('s.status')
112	->getQuery()->getResult();
113
114	$all = 0;
115	foreach ($rows as $row) {
116	$count = (int)$row['subscribersCount'];
117	$all += $count;
118	if (array_key_exists($row['status'], $counts)) {
119	$counts[$row['status']] = $count;
120	}
121	}
122	$counts['all'] = $all;
123
124	$counts['trash'] = (int)$this->entityManager->createQueryBuilder()
125	->select('COUNT(s.id)')
126	->from(SubscriberEntity::class, 's')
127	->where('s.deletedAt IS NOT NULL')
128	->getQuery()->getSingleScalarResult();
129
130	return $counts;
131	}
132
133	public function invalidateTotalSubscribersCache(): void {
134	$this->wp->deleteTransient(Subscribers::SUBSCRIBERS_COUNT_CACHE_KEY);
135	}
136
137	public function findBySegment(int $segmentId): array {
138	return $this->entityManager
139	->createQueryBuilder()
140	->select('s')
141	->from(SubscriberEntity::class, 's')
142	->join('s.subscriberSegments', 'ss', Join::WITH, 'ss.segment = :segment')
143	->setParameter('segment', $segmentId)
144	->getQuery()->getResult();
145	}
146
147	public function findExclusiveSubscribersBySegment(int $segmentId): array {
148	return $this->entityManager->createQueryBuilder()
149	->select('s')
150	->from(SubscriberEntity::class, 's')
151	->join('s.subscriberSegments', 'ss', Join::WITH, 'ss.segment = :segment')
152	->leftJoin('s.subscriberSegments', 'ss2', Join::WITH, 'ss2.segment <> :segment AND ss2.status = :subscribed')
153	->leftJoin('ss2.segment', 'seg', Join::WITH, 'seg.deletedAt IS NULL')
154	->groupBy('s.id')
155	->andHaving('COUNT(seg.id) = 0')
156	->setParameter('segment', $segmentId)
157	->setParameter('subscribed', SubscriberEntity::STATUS_SUBSCRIBED)
158	->getQuery()->getResult();
159	}
160
161	public function getWooCommerceSegmentSubscriber(string $email): ?SubscriberEntity {
162	$subscriber = $this->doctrineRepository->createQueryBuilder('s')
163	->join('s.subscriberSegments', 'ss')
164	->join('ss.segment', 'sg', Join::WITH, 'sg.type = :typeWcUsers')
165	->where('s.isWoocommerceUser = 1')
166	->andWhere('s.status IN (:subscribed, :unconfirmed)')
167	->andWhere('ss.status = :subscribed')
168	->andWhere('s.email = :email')
169	->setParameter('typeWcUsers', SegmentEntity::TYPE_WC_USERS)
170	->setParameter('subscribed', SubscriberEntity::STATUS_SUBSCRIBED)
171	->setParameter('unconfirmed', SubscriberEntity::STATUS_UNCONFIRMED)
172	->setParameter('email', $email)
173	->setMaxResults(1)
174	->getQuery()
175	->getOneOrNullResult();
176	return $subscriber instanceof SubscriberEntity ? $subscriber : null;
177	}
178
179	/**
180	* @return int - number of processed ids
181	*/
182	public function bulkTrash(array $ids): int {
183	if (empty($ids)) {
184	return 0;
185	}
186
187	$this->entityManager->createQueryBuilder()
188	->update(SubscriberEntity::class, 's')
189	->set('s.deletedAt', 'CURRENT_TIMESTAMP()')
190	->where('s.id IN (:ids)')
191	->setParameter('ids', $ids)
192	->getQuery()->execute();
193
194	$this->changesNotifier->subscribersUpdated($ids);
195	$this->changesNotifier->subscribersCountChanged($ids);
196	$this->invalidateTotalSubscribersCache();
197	return count($ids);
198	}
199
200	/**
201	* @return int - number of processed ids
202	*/
203	public function bulkRestore(array $ids): int {
204	if (empty($ids)) {
205	return 0;
206	}
207
208	$this->entityManager->createQueryBuilder()
209	->update(SubscriberEntity::class, 's')
210	->set('s.deletedAt', ':deletedAt')
211	->where('s.id IN (:ids)')
212	->setParameter('deletedAt', null)
213	->setParameter('ids', $ids)
214	->getQuery()->execute();
215
216	$this->changesNotifier->subscribersUpdated($ids);
217	$this->changesNotifier->subscribersCountChanged($ids);
218	$this->invalidateTotalSubscribersCache();
219	return count($ids);
220	}
221
222	/**
223	* @return int - number of processed ids
224	*/
225	public function bulkDelete(array $ids): int {
226	if (empty($ids)) {
227	return 0;
228	}
229
230	$ids = $this->findPermanentlyDeletableIds($ids);
231	if (empty($ids)) {
232	return 0;
233	}
234
235	$count = 0;
236	$this->entityManager->transactional(function (EntityManager $entityManager) use ($ids, &$count) {
237	// Delete subscriber segments
238	$this->removeSubscribersFromAllSegments($ids);
239
240	// Delete subscriber custom fields
241	$subscriberCustomFieldTable = $entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName();
242	$subscriberTable = $entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
243	$entityManager->getConnection()->executeStatement("
244	DELETE scs FROM $subscriberCustomFieldTable scs
245	JOIN $subscriberTable s ON s.`id` = scs.`subscriber_id`
246	WHERE scs.`subscriber_id` IN (:ids)
247	AND s.`is_woocommerce_user` = false
248	AND s.`wp_user_id` IS NULL
249	", ['ids' => $ids], ['ids' => ArrayParameterType::INTEGER]);
250
251	// Delete subscriber tags
252	$subscriberTagTable = $entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName();
253	$entityManager->getConnection()->executeStatement("
254	DELETE st FROM $subscriberTagTable st
255	JOIN $subscriberTable s ON s.`id` = st.`subscriber_id`
256	WHERE st.`subscriber_id` IN (:ids)
257	AND s.`is_woocommerce_user` = false
258	AND s.`wp_user_id` IS NULL
259	", ['ids' => $ids], ['ids' => ArrayParameterType::INTEGER]);
260
261	$queryBuilder = $entityManager->createQueryBuilder();
262	$count = $queryBuilder->delete(SubscriberEntity::class, 's')
263	->where('s.id IN (:ids)')
264	->andWhere('s.wpUserId IS NULL')
265	->andWhere('s.isWoocommerceUser = false')
266	->setParameter('ids', $ids)
267	->getQuery()->execute();
268	});
269
270	$this->changesNotifier->subscribersDeleted($ids);
271	$this->invalidateTotalSubscribersCache();
272	return $count;
273	}
274
275	/**
276	* @param int[] $ids
277	* @return int[]
278	*/
279	private function findPermanentlyDeletableIds(array $ids): array {
280	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
281	$deletableIds = $this->entityManager->getConnection()->executeQuery(
282	"SELECT `id`
283	FROM $subscriberTable
284	WHERE `id` IN (:ids)
285	AND `is_woocommerce_user` = false
286	AND `wp_user_id` IS NULL",
287	['ids' => $ids],
288	['ids' => ArrayParameterType::INTEGER]
289	)->fetchFirstColumn();
290
291	$ids = [];
292	foreach ($deletableIds as $id) {
293	$ids[] = $this->toInt($id);
294	}
295	return $ids;
296	}
297
298	public function sendPublicConfirmationEmailWithCap(
299	SubscriberEntity $subscriber,
300	int $maxConfirmationEmails,
301	callable $sendConfirmationEmail
302	): bool {
303	if (!$subscriber->getId()) {
304	return false;
305	}
306
307	$connection = $this->entityManager->getConnection();
308	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
309
310	$claimedRows = (int)$connection->executeStatement(
311	"UPDATE $subscriberTable
312	SET `count_confirmations` = `count_confirmations` + 1
313	WHERE `id` = :id
314	AND `count_confirmations` < :max_confirmation_emails",
315	[
316	'id' => $subscriber->getId(),
317	'max_confirmation_emails' => $maxConfirmationEmails,
318	],
319	[
320	'id' => ParameterType::INTEGER,
321	'max_confirmation_emails' => ParameterType::INTEGER,
322	]
323	);
324
325	if ($claimedRows !== 1) {
326	$this->entityManager->refresh($subscriber);
327	return false;
328	}
329
330	try {
331	if (!$sendConfirmationEmail()) {
332	$this->releasePublicConfirmationEmailClaim($subscriberTable, (int)$subscriber->getId());
333	$this->entityManager->refresh($subscriber);
334	return false;
335	}
336	} catch (\Throwable $throwable) {
337	$this->releasePublicConfirmationEmailClaim($subscriberTable, (int)$subscriber->getId());
338	$this->entityManager->refresh($subscriber);
339	throw $throwable;
340	}
341
342	$connection->executeStatement(
343	"UPDATE $subscriberTable
344	SET `last_confirmation_email_sent_at` = :sent_at
345	WHERE `id` = :id",
346	[
347	'id' => $subscriber->getId(),
348	'sent_at' => Carbon::now()->format('Y-m-d H:i:s'),
349	],
350	[
351	'id' => ParameterType::INTEGER,
352	'sent_at' => ParameterType::STRING,
353	]
354	);
355
356	$this->entityManager->refresh($subscriber);
357	return true;
358	}
359
360	/**
361	* @return array{claimed: bool, reason?: string, claim_time?: string, previous_last_confirmation_email_sent_at?: string\|null, previous_count_confirmations?: int}
362	*/
363	public function claimAdminConfirmationEmailResend(
364	SubscriberEntity $subscriber,
365	int $maxConfirmationEmails,
366	DateTimeInterface $recentCutoff,
367	?DateTimeInterface $oldestLifecycleDate = null
368	): array {
369	if (!$subscriber->getId()) {
370	return ['claimed' => false, 'reason' => 'not_found'];
371	}
372
373	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
374	$row = $this->getConfirmationResendState($subscriberTable, (int)$subscriber->getId());
375	$reason = $this->getConfirmationResendIneligibilityReasonFromRow($row, $maxConfirmationEmails, $recentCutoff, $oldestLifecycleDate);
376	if ($reason !== null) {
377	$this->entityManager->refresh($subscriber);
378	return ['claimed' => false, 'reason' => $reason];
379	}
380
381	$previousCountConfirmations = $this->toInt($row['count_confirmations'] ?? 0);
382	$previousLastConfirmationEmailSentAt = $this->toStringOrNull($row['last_confirmation_email_sent_at'] ?? null);
383	$claimTime = Carbon::now()->millisecond(0)->format('Y-m-d H:i:s');
384	$ageCondition = $oldestLifecycleDate instanceof DateTimeInterface
385	? 'AND COALESCE(`last_subscribed_at`, `created_at`) >= :oldest_lifecycle_date'
386	: '';
387	$lastConfirmationEmailSentAtCondition = $previousLastConfirmationEmailSentAt === null
388	? 'AND `last_confirmation_email_sent_at` IS NULL'
389	: 'AND `last_confirmation_email_sent_at` = :previous_last_confirmation_email_sent_at';
390	$parameters = [
391	'id' => $subscriber->getId(),
392	'status' => SubscriberEntity::STATUS_UNCONFIRMED,
393	'max_confirmation_emails' => $maxConfirmationEmails,
394	'recent_cutoff' => $recentCutoff->format('Y-m-d H:i:s'),
395	'claim_time' => $claimTime,
396	'previous_count_confirmations' => $previousCountConfirmations,
397	];
398	$types = [
399	'id' => ParameterType::INTEGER,
400	'max_confirmation_emails' => ParameterType::INTEGER,
401	'recent_cutoff' => ParameterType::STRING,
402	'claim_time' => ParameterType::STRING,
403	'previous_count_confirmations' => ParameterType::INTEGER,
404	];
405	if ($previousLastConfirmationEmailSentAt !== null) {
406	$parameters['previous_last_confirmation_email_sent_at'] = $previousLastConfirmationEmailSentAt;
407	$types['previous_last_confirmation_email_sent_at'] = ParameterType::STRING;
408	}
409	if ($oldestLifecycleDate instanceof DateTimeInterface) {
410	$parameters['oldest_lifecycle_date'] = $oldestLifecycleDate->format('Y-m-d H:i:s');
411	$types['oldest_lifecycle_date'] = ParameterType::STRING;
412	}
413
414	$claimedRows = (int)$this->entityManager->getConnection()->executeStatement(
415	"UPDATE $subscriberTable
416	SET `count_confirmations` = `count_confirmations` + 1,
417	`last_confirmation_email_sent_at` = :claim_time
418	WHERE `id` = :id
419	AND `status` = :status
420	AND `deleted_at` IS NULL
421	AND `count_confirmations` < :max_confirmation_emails
422	AND (`last_confirmation_email_sent_at` IS NULL OR `last_confirmation_email_sent_at` <= :recent_cutoff)
423	AND `count_confirmations` = :previous_count_confirmations
424	$lastConfirmationEmailSentAtCondition
425	$ageCondition",
426	$parameters,
427	$types
428	);
429
430	$this->entityManager->refresh($subscriber);
431	if ($claimedRows !== 1) {
432	$row = $this->getConfirmationResendState($subscriberTable, (int)$subscriber->getId());
433	return [
434	'claimed' => false,
435	'reason' => $this->getConfirmationResendIneligibilityReasonFromRow($row, $maxConfirmationEmails, $recentCutoff, $oldestLifecycleDate) ?? 'not_found',
436	];
437	}
438
439	return [
440	'claimed' => true,
441	'claim_time' => $claimTime,
442	'previous_last_confirmation_email_sent_at' => $previousLastConfirmationEmailSentAt,
443	'previous_count_confirmations' => $previousCountConfirmations,
444	];
445	}
446
447	public function releaseAdminConfirmationEmailResendClaim(
448	SubscriberEntity $subscriber,
449	string $claimTime,
450	?string $previousLastConfirmationEmailSentAt,
451	int $previousCountConfirmations
452	): void {
453	if (!$subscriber->getId()) {
454	return;
455	}
456
457	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
458	$this->entityManager->getConnection()->executeStatement(
459	"UPDATE $subscriberTable
460	SET `count_confirmations` = :previous_count_confirmations,
461	`last_confirmation_email_sent_at` = :previous_last_confirmation_email_sent_at
462	WHERE `id` = :id
463	AND `last_confirmation_email_sent_at` = :claim_time
464	AND `count_confirmations` = :claimed_count_confirmations",
465	[
466	'id' => $subscriber->getId(),
467	'claim_time' => $claimTime,
468	'previous_last_confirmation_email_sent_at' => $previousLastConfirmationEmailSentAt,
469	'previous_count_confirmations' => $previousCountConfirmations,
470	'claimed_count_confirmations' => $previousCountConfirmations + 1,
471	],
472	[
473	'id' => ParameterType::INTEGER,
474	'claim_time' => ParameterType::STRING,
475	'previous_last_confirmation_email_sent_at' => $previousLastConfirmationEmailSentAt === null ? ParameterType::NULL : ParameterType::STRING,
476	'previous_count_confirmations' => ParameterType::INTEGER,
477	'claimed_count_confirmations' => ParameterType::INTEGER,
478	]
479	);
480	$this->entityManager->refresh($subscriber);
481	}
482
483	public function completeAdminConfirmationEmailResendClaim(
484	SubscriberEntity $subscriber,
485	string $claimTime,
486	?string $previousLastConfirmationEmailSentAt,
487	int $previousCountConfirmations
488	): void {
489	if (!$subscriber->getId()) {
490	return;
491	}
492
493	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
494	$this->entityManager->getConnection()->executeStatement(
495	"UPDATE $subscriberTable
496	SET `last_confirmation_email_sent_at` = :previous_last_confirmation_email_sent_at
497	WHERE `id` = :id
498	AND `last_confirmation_email_sent_at` = :claim_time
499	AND `count_confirmations` = :claimed_count_confirmations",
500	[
501	'id' => $subscriber->getId(),
502	'claim_time' => $claimTime,
503	'previous_last_confirmation_email_sent_at' => $previousLastConfirmationEmailSentAt,
504	'claimed_count_confirmations' => $previousCountConfirmations + 1,
505	],
506	[
507	'id' => ParameterType::INTEGER,
508	'claim_time' => ParameterType::STRING,
509	'previous_last_confirmation_email_sent_at' => $previousLastConfirmationEmailSentAt === null ? ParameterType::NULL : ParameterType::STRING,
510	'claimed_count_confirmations' => ParameterType::INTEGER,
511	]
512	);
513	$this->entityManager->refresh($subscriber);
514	}
515
516	public function getAdminConfirmationEmailResendIneligibilityReason(
517	SubscriberEntity $subscriber,
518	int $maxConfirmationEmails,
519	DateTimeInterface $recentCutoff,
520	?DateTimeInterface $oldestLifecycleDate = null
521	): ?string {
522	if (!$subscriber->getId()) {
523	return 'not_found';
524	}
525	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
526	$row = $this->getConfirmationResendState($subscriberTable, (int)$subscriber->getId());
527	return $this->getConfirmationResendIneligibilityReasonFromRow($row, $maxConfirmationEmails, $recentCutoff, $oldestLifecycleDate);
528	}
529
530	/**
531	* @return array<string, mixed>\|false
532	*/
533	private function getConfirmationResendState(string $subscriberTable, int $subscriberId) {
534	return $this->entityManager->getConnection()->executeQuery(
535	"SELECT `id`, `status`, `deleted_at`, `count_confirmations`, `last_confirmation_email_sent_at`,
536	COALESCE(`last_subscribed_at`, `created_at`) AS lifecycle_date
537	FROM $subscriberTable
538	WHERE `id` = :id",
539	['id' => $subscriberId],
540	['id' => ParameterType::INTEGER]
541	)->fetchAssociative();
542	}
543
544	/**
545	* @param array<string, mixed>\|false $row
546	*/
547	private function getConfirmationResendIneligibilityReasonFromRow(
548	$row,
549	int $maxConfirmationEmails,
550	DateTimeInterface $recentCutoff,
551	?DateTimeInterface $oldestLifecycleDate
552	): ?string {
553	if (!$row) {
554	return 'not_found';
555	}
556	if (!empty($row['deleted_at'])) {
557	return 'deleted';
558	}
559	if (($row['status'] ?? null) !== SubscriberEntity::STATUS_UNCONFIRMED) {
560	return 'not_unconfirmed';
561	}
562	if ($this->toInt($row['count_confirmations'] ?? 0) >= $maxConfirmationEmails) {
563	return 'max_confirmations_reached';
564	}
565	$lastConfirmationEmailSentAt = $this->toStringOrNull($row['last_confirmation_email_sent_at'] ?? null);
566	if ($lastConfirmationEmailSentAt !== null && strtotime($lastConfirmationEmailSentAt) > $recentCutoff->getTimestamp()) {
567	return 'recently_sent';
568	}
569	$lifecycleDate = $this->toStringOrNull($row['lifecycle_date'] ?? null);
570	if ($oldestLifecycleDate instanceof DateTimeInterface && $lifecycleDate !== null && strtotime($lifecycleDate) < $oldestLifecycleDate->getTimestamp()) {
571	return 'too_old';
572	}
573	return null;
574	}
575
576	private function toInt($value): int {
577	if (is_int($value)) {
578	return $value;
579	}
580	if (is_string($value) \|\| is_float($value) \|\| is_bool($value)) {
581	return (int)$value;
582	}
583	return 0;
584	}
585
586	private function toStringOrNull($value): ?string {
587	if ($value === null \|\| $value === '') {
588	return null;
589	}
590	if (is_scalar($value)) {
591	return (string)$value;
592	}
593	return null;
594	}
595
596	private function releasePublicConfirmationEmailClaim(string $subscriberTable, int $subscriberId): void {
597	$this->entityManager->getConnection()->executeStatement(
598	"UPDATE $subscriberTable
599	SET `count_confirmations` = `count_confirmations` - 1
600	WHERE `id` = :id
601	AND `count_confirmations` > 0",
602	['id' => $subscriberId],
603	['id' => ParameterType::INTEGER]
604	);
605	}
606
607	/**
608	* @return int[]
609	*/
610	public function deleteUnconfirmedSubscribersForCleanup(DateTimeInterface $cutoff, int $limit): array {
611	if ($limit <= 0) {
612	return [];
613	}
614
615	$deletedIds = [];
616	$this->entityManager->transactional(function (EntityManager $entityManager) use ($cutoff, $limit, &$deletedIds) {
617	$subscriberTable = $entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
618	$subscriberCustomFieldTable = $entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName();
619	$subscriberTagTable = $entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName();
620
621	$confirmationDateIds = $this->findUnconfirmedSubscriberIdsForCleanup(
622	$subscriberTable,
623	's.`last_confirmation_email_sent_at` <= :cutoff',
624	$cutoff,
625	$limit
626	);
627
628	$legacyCreatedAtIds = $this->findUnconfirmedSubscriberIdsForCleanup(
629	$subscriberTable,
630	's.`last_confirmation_email_sent_at` IS NULL AND COALESCE(s.`last_subscribed_at`, s.`created_at`) <= :cutoff',
631	$cutoff,
632	$limit
633	);
634
635	$deletedIds = array_values(array_unique(array_merge($confirmationDateIds, $legacyCreatedAtIds)));
636	sort($deletedIds);
637	$deletedIds = array_slice($deletedIds, 0, $limit);
638
639	if (empty($deletedIds)) {
640	return;
641	}
642
643	$markedAt = Carbon::now()->format('Y-m-d H:i:s');
644	$entityManager->getConnection()->executeStatement(
645	"UPDATE $subscriberTable
646	SET `deleted_at` = :marked_at
647	WHERE `id` IN (:ids)
648	AND `status` = :status
649	AND `deleted_at` IS NULL
650	AND `wp_user_id` IS NULL
651	AND `is_woocommerce_user` = 0
652	AND (
653	`last_confirmation_email_sent_at` <= :cutoff
654	OR (
655	`last_confirmation_email_sent_at` IS NULL
656	AND COALESCE(`last_subscribed_at`, `created_at`) <= :cutoff
657	)
658	)",
659	[
660	'ids' => $deletedIds,
661	'status' => SubscriberEntity::STATUS_UNCONFIRMED,
662	'cutoff' => $cutoff->format('Y-m-d H:i:s'),
663	'marked_at' => $markedAt,
664	],
665	[
666	'ids' => ArrayParameterType::INTEGER,
667	'cutoff' => ParameterType::STRING,
668	'marked_at' => ParameterType::STRING,
669	]
670	);
671
672	$deletedIds = array_map(static function($id): int {
673	if (is_int($id)) {
674	return $id;
675	}
676	return is_string($id) ? (int)$id : 0;
677	}, $entityManager->getConnection()->executeQuery(
678	"SELECT `id`
679	FROM $subscriberTable
680	WHERE `id` IN (:ids)
681	AND `deleted_at` = :marked_at",
682	[
683	'ids' => $deletedIds,
684	'marked_at' => $markedAt,
685	],
686	[
687	'ids' => ArrayParameterType::INTEGER,
688	'marked_at' => ParameterType::STRING,
689	]
690	)->fetchFirstColumn());
691
692	if (empty($deletedIds)) {
693	return;
694	}
695
696	$this->removeSubscribersFromAllSegments($deletedIds);
697
698	$entityManager->getConnection()->executeStatement("
699	DELETE scs FROM $subscriberCustomFieldTable scs
700	WHERE scs.`subscriber_id` IN (:ids)
701	", ['ids' => $deletedIds], ['ids' => ArrayParameterType::INTEGER]);
702
703	$entityManager->getConnection()->executeStatement("
704	DELETE st FROM $subscriberTagTable st
705	WHERE st.`subscriber_id` IN (:ids)
706	", ['ids' => $deletedIds], ['ids' => ArrayParameterType::INTEGER]);
707
708	$deletedCount = (int)$entityManager->getConnection()->executeStatement(
709	"DELETE FROM $subscriberTable
710	WHERE `id` IN (:ids)
711	AND `deleted_at` = :marked_at",
712	[
713	'ids' => $deletedIds,
714	'marked_at' => $markedAt,
715	],
716	[
717	'ids' => ArrayParameterType::INTEGER,
718	'marked_at' => ParameterType::STRING,
719	]
720	);
721
722	if ($deletedCount !== count($deletedIds)) {
723	throw new \RuntimeException('Unconfirmed subscribers cleanup deleted an unexpected number of rows.');
724	}
725	});
726
727	if (!empty($deletedIds)) {
728	$this->changesNotifier->subscribersDeleted($deletedIds);
729	$this->invalidateTotalSubscribersCache();
730	}
731	return $deletedIds;
732	}
733
734	/**
735	* @return int[]
736	*/
737	private function findUnconfirmedSubscriberIdsForCleanup(
738	string $subscriberTable,
739	string $datePredicate,
740	DateTimeInterface $cutoff,
741	int $limit
742	): array {
743	return array_map(static function($id): int {
744	if (is_int($id)) {
745	return $id;
746	}
747	return is_string($id) ? (int)$id : 0;
748	}, $this->entityManager->getConnection()->executeQuery(
749	"SELECT s.`id`
750	FROM $subscriberTable s
751	WHERE s.`status` = :status
752	AND s.`deleted_at` IS NULL
753	AND s.`wp_user_id` IS NULL
754	AND s.`is_woocommerce_user` = 0
755	AND $datePredicate
756	ORDER BY s.`id` ASC
757	LIMIT :limit",
758	[
759	'status' => SubscriberEntity::STATUS_UNCONFIRMED,
760	'cutoff' => $cutoff->format('Y-m-d H:i:s'),
761	'limit' => $limit,
762	],
763	[
764	'cutoff' => ParameterType::STRING,
765	'limit' => ParameterType::INTEGER,
766	]
767	)->fetchFirstColumn());
768	}
769
770	/**
771	* Recalculate the denormalized segments_count for the given subscribers.
772	* Exposed for raw-SQL write paths (e.g. import) that bypass the repository's
773	* own segment mutators.
774	*
775	* @param int[] $subscriberIds
776	*/
777	public function recalculateSegmentsCount(array $subscriberIds): void {
778	$this->segmentsCountRecalculator->recalculateForSubscribers($subscriberIds);
779	}
780
781	/**
782	* @return int - number of processed ids
783	*/
784	public function bulkRemoveFromSegment(SegmentEntity $segment, array $ids): int {
785	if (empty($ids)) {
786	return 0;
787	}
788
789	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
790	$count = (int)$this->entityManager->getConnection()->executeStatement("
791	DELETE ss FROM $subscriberSegmentsTable ss
792	WHERE ss.`subscriber_id` IN (:ids)
793	AND ss.`segment_id` = :segment_id
794	", ['ids' => $ids, 'segment_id' => $segment->getId()], ['ids' => ArrayParameterType::INTEGER]);
795
796	$this->segmentsCountRecalculator->recalculateForSubscribers($ids);
797	$this->changesNotifier->subscribersUpdated($ids);
798	return $count;
799	}
800
801	/**
802	* @return int - number of processed ids
803	*/
804	public function bulkRemoveFromAllSegments(array $ids): int {
805	$count = $this->removeSubscribersFromAllSegments($ids);
806	$this->changesNotifier->subscribersUpdated($ids);
807	return $count;
808	}
809
810	/**
811	* @return int - number of processed ids
812	*/
813	public function bulkAddToSegment(SegmentEntity $segment, array $ids): int {
814	$count = $this->addSubscribersToSegment($segment, $ids);
815	$this->changesNotifier->subscribersUpdated($ids);
816	return $count;
817	}
818
819	/**
820	* @return int - number of processed ids
821	*/
822	public function bulkMoveToSegment(SegmentEntity $segment, array $ids): int {
823	if (empty($ids)) {
824	return 0;
825	}
826
827	$this->removeSubscribersFromAllSegments($ids);
828	$count = $this->addSubscribersToSegment($segment, $ids);
829
830	$this->changesNotifier->subscribersUpdated($ids);
831	return $count;
832	}
833
834	public function bulkUnsubscribe(array $ids): int {
835	$this->entityManager->createQueryBuilder()
836	->update(SubscriberEntity::class, 's')
837	->set('s.status', ':status')
838	->where('s.id IN (:ids)')
839	->setParameter('status', SubscriberEntity::STATUS_UNSUBSCRIBED)
840	->setParameter('ids', $ids)
841	->getQuery()->execute();
842
843	$this->changesNotifier->subscribersUpdated($ids);
844	$this->changesNotifier->subscribersCountChanged($ids);
845	$this->invalidateTotalSubscribersCache();
846	return count($ids);
847	}
848
849	public function bulkUpdateLastSendingAt(array $ids, DateTimeInterface $dateTime): int {
850	if (empty($ids)) {
851	return 0;
852	}
853	$this->entityManager->createQueryBuilder()
854	->update(SubscriberEntity::class, 's')
855	->set('s.lastSendingAt', ':lastSendingAt')
856	->where('s.id IN (:ids)')
857	->setParameter('lastSendingAt', $dateTime)
858	->setParameter('ids', $ids)
859	->getQuery()
860	->execute();
861	return count($ids);
862	}
863
864	public function bulkUpdateEngagementScoreUpdatedAt(array $ids, ?DateTimeInterface $dateTime): void {
865	if (empty($ids)) {
866	return;
867	}
868	$this->entityManager->createQueryBuilder()
869	->update(SubscriberEntity::class, 's')
870	->set('s.engagementScoreUpdatedAt', ':dateTime')
871	->where('s.id IN (:ids)')
872	->setParameter('dateTime', $dateTime)
873	->setParameter('ids', $ids)
874	->getQuery()
875	->execute();
876	}
877
878	public function findWpUserIdAndEmailByEmails(array $emails): array {
879	return $this->entityManager->createQueryBuilder()
880	->select('s.wpUserId AS wp_user_id, LOWER(s.email) AS email')
881	->from(SubscriberEntity::class, 's')
882	->where('s.email IN (:emails)')
883	->setParameter('emails', $emails)
884	->getQuery()->getResult();
885	}
886
887	public function findIdAndEmailByEmails(array $emails): array {
888	return $this->entityManager->createQueryBuilder()
889	->select('s.id, s.email')
890	->from(SubscriberEntity::class, 's')
891	->where('s.email IN (:emails)')
892	->setParameter('emails', $emails)
893	->getQuery()->getResult();
894	}
895
896	/**
897	* @return int[]
898	*/
899	public function findIdsOfDeletedByEmails(array $emails): array {
900	$rows = $this->entityManager->createQueryBuilder()
901	->select('s.id')
902	->from(SubscriberEntity::class, 's')
903	->where('s.email IN (:emails)')
904	->andWhere('s.deletedAt IS NOT NULL')
905	->setParameter('emails', $emails)
906	->getQuery()->getResult();
907	return array_values(array_map('intval', array_column(is_array($rows) ? $rows : [], 'id')));
908	}
909
910	public function getCurrentWPUser(): ?SubscriberEntity {
911	$wpUser = WPFunctions::get()->wpGetCurrentUser();
912	if (empty($wpUser->ID)) {
913	return null; // Don't look up a subscriber for guests
914	}
915	return $this->findOneBy(['wpUserId' => $wpUser->ID]);
916	}
917
918	public function findByUpdatedScoreNotInLastMonth(int $limit): array {
919	$dateTime = (new Carbon())->subMonths(1);
920	return $this->entityManager->createQueryBuilder()
921	->select('s')
922	->from(SubscriberEntity::class, 's')
923	->where('s.engagementScoreUpdatedAt IS NULL')
924	->orWhere('s.engagementScoreUpdatedAt < :dateTime')
925	->setParameter('dateTime', $dateTime)
926	->getQuery()
927	->setMaxResults($limit)
928	->getResult();
929	}
930
931	public function maybeUpdateLastEngagement(SubscriberEntity $subscriberEntity): void {
932	$now = $this->getCurrentDateTime();
933	// Do not update engagement if was recently updated to avoid unnecessary updates in DB
934	if ($subscriberEntity->getLastEngagementAt() && $subscriberEntity->getLastEngagementAt() > $now->subMinute()) {
935	return;
936	}
937	// Update last engagement
938	$subscriberEntity->markEngaged($now);
939	$this->flush();
940	}
941
942	public function maybeUpdateLastOpenAt(SubscriberEntity $subscriberEntity): void {
943	$now = $this->getCurrentDateTime();
944	// Avoid unnecessary DB calls
945	if ($subscriberEntity->getLastOpenAt() && $subscriberEntity->getLastOpenAt() > $now->subMinute()) {
946	return;
947	}
948	$subscriberEntity->setLastOpenAt($now);
949	$subscriberEntity->markEngaged($now);
950	$this->flush();
951	}
952
953	public function maybeUpdateLastClickAt(SubscriberEntity $subscriberEntity): void {
954	$now = $this->getCurrentDateTime();
955	// Avoid unnecessary DB calls
956	if ($subscriberEntity->getLastClickAt() && $subscriberEntity->getLastClickAt() > $now->subMinute()) {
957	return;
958	}
959	$subscriberEntity->setLastClickAt($now);
960	$subscriberEntity->markEngaged($now);
961	$this->flush();
962	}
963
964	public function maybeUpdateLastPurchaseAt(SubscriberEntity $subscriberEntity): void {
965	$now = $this->getCurrentDateTime();
966	// Avoid unnecessary DB calls
967	if ($subscriberEntity->getLastPurchaseAt() && $subscriberEntity->getLastPurchaseAt() > $now->subMinute()) {
968	return;
969	}
970	$subscriberEntity->setLastPurchaseAt($now);
971	$subscriberEntity->markEngaged($now);
972	$this->flush();
973	}
974
975	public function maybeUpdateLastPageViewAt(SubscriberEntity $subscriberEntity): void {
976	$now = $this->getCurrentDateTime();
977	// Avoid unnecessary DB calls
978	if ($subscriberEntity->getLastPageViewAt() && $subscriberEntity->getLastPageViewAt() > $now->subMinute()) {
979	return;
980	}
981	$subscriberEntity->setLastPageViewAt($now);
982	$subscriberEntity->markEngaged($now);
983	$this->flush();
984	}
985
986	public function getMaxSubscriberId(): int {
987	$maxSubscriberId = $this->entityManager->createQueryBuilder()
988	->select('MAX(s.id)')
989	->from(SubscriberEntity::class, 's')
990	->getQuery()
991	->getSingleScalarResult();
992
993	return intval($maxSubscriberId);
994	}
995
996	/**
997	* Returns [count, maxId] of the next $batchSize subscriber rows with id >= $startId, ordered by id.
998	* count === 0 means there are no more subscribers from $startId onward.
999	*
1000	* @return array{0:int,1:int}
1001	*/
1002	public function getNextIdWindow(int $startId, int $batchSize): array {
1003	$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
1004	$result = $this->entityManager->getConnection()->executeQuery(
1005	"
1006	SELECT COUNT(ids.id) as count, COALESCE(MAX(ids.id), 0) as max FROM (
1007	SELECT s.id FROM {$subscribersTable} as s
1008	WHERE s.id >= :startId
1009	ORDER BY s.id
1010	LIMIT :batchSize
1011	) ids
1012	",
1013	[
1014	'startId' => $startId,
1015	'batchSize' => $batchSize,
1016	],
1017	[
1018	'startId' => ParameterType::INTEGER,
1019	'batchSize' => ParameterType::INTEGER,
1020	]
1021	)->fetchAssociative();
1022
1023	if (!is_array($result)) {
1024	return [0, 0];
1025	}
1026
1027	/** @var array{count: int, max: int} $result - it's required for PHPStan */
1028	return [intval($result['count']), intval($result['max'])];
1029	}
1030
1031	/**
1032	* Returns count of subscribers who subscribed after given date regardless of their current status.
1033	* @return int
1034	*/
1035	public function getCountOfLastSubscribedAfter(\DateTimeInterface $subscribedAfter): int {
1036	$result = $this->entityManager->createQueryBuilder()
1037	->select('COUNT(s.id)')
1038	->from(SubscriberEntity::class, 's')
1039	->where('s.lastSubscribedAt > :lastSubscribedAt')
1040	->andWhere('s.deletedAt IS NULL')
1041	->setParameter('lastSubscribedAt', $subscribedAfter)
1042	->getQuery()
1043	->getSingleScalarResult();
1044	return intval($result);
1045	}
1046
1047	/**
1048	* Returns count of subscribers who unsubscribed after given date regardless of their current status.
1049	* @return int
1050	*/
1051	public function getCountOfUnsubscribedAfter(\DateTimeInterface $unsubscribedAfter): int {
1052	$result = $this->entityManager->createQueryBuilder()
1053	->select('COUNT(DISTINCT s.id)')
1054	->from(StatisticsUnsubscribeEntity::class, 'su')
1055	->join('su.subscriber', 's')
1056	->andWhere('su.createdAt > :unsubscribedAfter')
1057	->andWhere('s.deletedAt IS NULL')
1058	->setParameter('unsubscribedAfter', $unsubscribedAfter)
1059	->getQuery()
1060	->getSingleScalarResult();
1061	return intval($result);
1062	}
1063
1064	/**
1065	* Returns count of subscribers who subscribed to a list after given date regardless of their current global status.
1066	*/
1067	public function getListLevelCountsOfSubscribedAfter(\DateTimeInterface $date): array {
1068	$data = $this->entityManager->createQueryBuilder()
1069	->select('seg.id, seg.name, seg.type, seg.averageEngagementScore, COUNT(ss.id) as count')
1070	->from(SubscriberSegmentEntity::class, 'ss')
1071	->join('ss.subscriber', 's')
1072	->join('ss.segment', 'seg')
1073	->where('ss.updatedAt > :date')
1074	->andWhere('ss.status = :segment_status')
1075	->andWhere('s.lastSubscribedAt > :date') // subscriber subscribed at some point after the date
1076	->andWhere('s.deletedAt IS NULL')
1077	->andWhere('seg.deletedAt IS NULL') // no trashed lists and disabled WP Users list
1078	->setParameter('date', $date)
1079	->setParameter('segment_status', SubscriberEntity::STATUS_SUBSCRIBED)
1080	->groupBy('ss.segment')
1081	->getQuery()
1082	->getArrayResult();
1083	return $data;
1084	}
1085
1086	/**
1087	* Returns count of subscribers who unsubscribed from a list after given date regardless of their current global status.
1088	*/
1089	public function getListLevelCountsOfUnsubscribedAfter(\DateTimeInterface $date): array {
1090	return $this->entityManager->createQueryBuilder()
1091	->select('seg.id, seg.name, seg.type, seg.averageEngagementScore, COUNT(ss.id) as count')
1092	->from(SubscriberSegmentEntity::class, 'ss')
1093	->join('ss.subscriber', 's')
1094	->join('ss.segment', 'seg')
1095	->where('ss.updatedAt > :date')
1096	->andWhere('ss.status = :segment_status')
1097	->andWhere('s.deletedAt IS NULL')
1098	->andWhere('seg.deletedAt IS NULL') // no trashed lists and disabled WP Users list
1099	->setParameter('date', $date)
1100	->setParameter('segment_status', SubscriberEntity::STATUS_UNSUBSCRIBED)
1101	->groupBy('ss.segment')
1102	->getQuery()
1103	->getArrayResult();
1104	}
1105
1106	/**
1107	* @return int - number of processed ids
1108	*/
1109	public function bulkAddTag(TagEntity $tag, array $ids): int {
1110	$count = $this->addTagToSubscribers($tag, $ids);
1111	$this->changesNotifier->subscribersUpdated($ids);
1112	return $count;
1113	}
1114
1115	/**
1116	* @return int - number of processed ids
1117	*/
1118	public function bulkRemoveTag(TagEntity $tag, array $ids): int {
1119	if (empty($ids)) {
1120	return 0;
1121	}
1122
1123	$subscriberTagsTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName();
1124	$count = (int)$this->entityManager->getConnection()->executeStatement("
1125	DELETE st FROM $subscriberTagsTable st
1126	WHERE st.`subscriber_id` IN (:ids)
1127	AND st.`tag_id` = :tag_id
1128	", ['ids' => $ids, 'tag_id' => $tag->getId()], ['ids' => ArrayParameterType::INTEGER]);
1129
1130	$this->changesNotifier->subscribersUpdated($ids);
1131	return $count;
1132	}
1133
1134	public function removeOrphanedSubscribersFromWpSegment(): void {
1135	global $wpdb;
1136
1137	$segmentId = $this->segmentsRepository->getWpUsersSegment()->getId();
1138
1139	$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
1140	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
1141	$segmentsTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName();
1142	$deletedAt = $this->getCurrentDateTime()->format('Y-m-d H:i:s');
1143
1144	$affectedIds = [];
1145	$this->entityManager->wrapInTransaction(function () use ($segmentId, $subscribersTable, $subscriberSegmentsTable, $segmentsTable, $deletedAt, $wpdb, &$affectedIds): void {
1146	// Hard-delete broken subscribers in the WP-Users segment when they have no
1147	// email, or when they have no WP user ID and no other list to belong to.
1148	$this->entityManager->getConnection()->executeStatement(
1149	"DELETE s
1150	FROM {$subscribersTable} s
1151	INNER JOIN {$subscriberSegmentsTable} ss ON s.id = ss.subscriber_id
1152	WHERE ss.segment_id = :segmentId
1153	AND (
1154	s.email = ''
1155	OR (
1156	s.wp_user_id IS NULL
1157	AND s.is_woocommerce_user = 0
1158	AND NOT EXISTS (
1159	SELECT 1 FROM {$subscriberSegmentsTable} ss_other
1160	INNER JOIN {$segmentsTable} seg ON seg.id = ss_other.segment_id
1161	WHERE ss_other.subscriber_id = s.id
1162	AND seg.type != :wpType
1163	AND seg.deleted_at IS NULL
1164	)
1165	)
1166	)",
1167	[
1168	'segmentId' => $segmentId,
1169	'wpType' => SegmentEntity::TYPE_WP_USERS,
1170	],
1171	[
1172	'segmentId' => ParameterType::INTEGER,
1173	'wpType' => ParameterType::STRING,
1174	]
1175	);
1176
1177	// Trash subscribers whose WP user is gone, who are only on the WP-Users list,
1178	// and who are not WC customers — they have nowhere left to belong, but we keep
1179	// them as soft-deleted so admins can recover them if needed.
1180	$this->entityManager->getConnection()->executeStatement(
1181	"UPDATE {$subscribersTable} s
1182	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1183	SET s.deleted_at = :deletedAt, s.status = :unconfirmed
1184	WHERE s.deleted_at IS NULL
1185	AND s.is_woocommerce_user = 0
1186	AND s.wp_user_id IS NOT NULL
1187	AND u.id IS NULL
1188	AND EXISTS (
1189	SELECT 1 FROM {$subscriberSegmentsTable} ss_wp
1190	WHERE ss_wp.subscriber_id = s.id AND ss_wp.segment_id = :segmentId
1191	)
1192	AND NOT EXISTS (
1193	SELECT 1 FROM {$subscriberSegmentsTable} ss_other
1194	INNER JOIN {$segmentsTable} seg ON seg.id = ss_other.segment_id
1195	WHERE ss_other.subscriber_id = s.id
1196	AND seg.type != :wpType
1197	AND seg.deleted_at IS NULL
1198	)",
1199	[
1200	'segmentId' => $segmentId,
1201	'unconfirmed' => SubscriberEntity::STATUS_UNCONFIRMED,
1202	'wpType' => SegmentEntity::TYPE_WP_USERS,
1203	'deletedAt' => $deletedAt,
1204	],
1205	[
1206	'segmentId' => ParameterType::INTEGER,
1207	'unconfirmed' => ParameterType::STRING,
1208	'wpType' => ParameterType::STRING,
1209	'deletedAt' => ParameterType::STRING,
1210	]
1211	);
1212
1213	// Capture subscribers whose WP-Users membership is subscribed before
1214	// deleting it — only those have a segments_count that needs updating.
1215	// Subscribers already handled by the soft-trash above (no other segments)
1216	// are included here too; their recalculation will be a no-op (re-derives 0).
1217	$subscribedStatus = SubscriberEntity::STATUS_SUBSCRIBED;
1218	$rows = $this->entityManager->getConnection()->executeQuery(
1219	"SELECT ss.subscriber_id
1220	FROM {$subscriberSegmentsTable} ss
1221	INNER JOIN {$subscribersTable} s ON s.id = ss.subscriber_id
1222	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1223	WHERE ss.segment_id = :segmentId
1224	AND (s.wp_user_id IS NULL OR u.id IS NULL)
1225	AND ss.status = :status",
1226	['segmentId' => $segmentId, 'status' => $subscribedStatus],
1227	['segmentId' => ParameterType::INTEGER, 'status' => ParameterType::STRING]
1228	)->fetchFirstColumn();
1229	$affectedIds = array_map(fn($id): int => is_numeric($id) ? (int)$id : 0, $rows);
1230
1231	// Remove WP-Users segment memberships for orphans.
1232	$this->entityManager->getConnection()->executeStatement(
1233	"DELETE ss
1234	FROM {$subscriberSegmentsTable} ss
1235	INNER JOIN {$subscribersTable} s ON s.id = ss.subscriber_id
1236	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1237	WHERE ss.segment_id = :segmentId
1238	AND (s.wp_user_id IS NULL OR u.id IS NULL)",
1239	['segmentId' => $segmentId],
1240	['segmentId' => ParameterType::INTEGER]
1241	);
1242
1243	// Detach subscribers from non-existent WP users and mark the source.
1244	$this->entityManager->getConnection()->executeStatement(
1245	"UPDATE {$subscribersTable} s
1246	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1247	SET s.wp_user_id = NULL, s.source = :source
1248	WHERE s.wp_user_id IS NOT NULL AND u.id IS NULL",
1249	['source' => Source::WORDPRESS_USER_DELETED],
1250	['source' => ParameterType::STRING]
1251	);
1252	});
1253
1254	if ($affectedIds !== []) {
1255	$this->segmentsCountRecalculator->recalculateForSubscribers($affectedIds);
1256	}
1257	}
1258
1259	public function removeByWpUserIds(array $wpUserIds) {
1260	if (empty($wpUserIds)) {
1261	return 0;
1262	}
1263
1264	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
1265	$subscriberIds = array_map(
1266	function($id): int {
1267	return $this->toInt($id);
1268	},
1269	$this->entityManager->getConnection()->executeQuery(
1270	"SELECT `id` FROM $subscriberTable WHERE `wp_user_id` IN (:wpUserIds)",
1271	['wpUserIds' => $wpUserIds],
1272	['wpUserIds' => ArrayParameterType::INTEGER]
1273	)->fetchFirstColumn()
1274	);
1275
1276	if (empty($subscriberIds)) {
1277	return 0;
1278	}
1279
1280	$count = 0;
1281	$this->entityManager->transactional(function (EntityManager $entityManager) use ($subscriberIds, &$count) {
1282	$this->removeSubscribersRelatedRows($subscriberIds);
1283
1284	$count = $entityManager->createQueryBuilder()
1285	->delete(SubscriberEntity::class, 's')
1286	->where('s.id IN (:ids)')
1287	->setParameter('ids', $subscriberIds)
1288	->getQuery()->execute();
1289	});
1290
1291	$this->changesNotifier->subscribersDeleted($subscriberIds);
1292	$this->invalidateTotalSubscribersCache();
1293
1294	return $count;
1295	}
1296
1297	/**
1298	* Removes rows in tables related to the given subscribers so no orphans are
1299	* left behind after the subscribers themselves are deleted. Unlike
1300	* removeSubscribersFromAllSegments() this removes every segment membership
1301	* regardless of segment type, since the subscribers are being fully removed.
1302	*/
1303	private function removeSubscribersRelatedRows(array $subscriberIds): void {
1304	if (empty($subscriberIds)) {
1305	return;
1306	}
1307
1308	$connection = $this->entityManager->getConnection();
1309	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
1310	$subscriberCustomFieldTable = $this->entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName();
1311	$subscriberTagTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName();
1312
1313	$connection->executeStatement(
1314	"DELETE FROM $subscriberSegmentsTable WHERE `subscriber_id` IN (:ids)",
1315	['ids' => $subscriberIds],
1316	['ids' => ArrayParameterType::INTEGER]
1317	);
1318
1319	$connection->executeStatement(
1320	"DELETE FROM $subscriberCustomFieldTable WHERE `subscriber_id` IN (:ids)",
1321	['ids' => $subscriberIds],
1322	['ids' => ArrayParameterType::INTEGER]
1323	);
1324
1325	$connection->executeStatement(
1326	"DELETE FROM $subscriberTagTable WHERE `subscriber_id` IN (:ids)",
1327	['ids' => $subscriberIds],
1328	['ids' => ArrayParameterType::INTEGER]
1329	);
1330	}
1331
1332	/**
1333	* @return int - number of processed ids
1334	*/
1335	private function removeSubscribersFromAllSegments(array $ids): int {
1336	if (empty($ids)) {
1337	return 0;
1338	}
1339
1340	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
1341	$segmentsTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName();
1342
1343	// Count unique subscribers that will have segments removed
1344	$uniqueSubscribersCount = $this->entityManager->getConnection()->executeQuery("
1345	SELECT COUNT(DISTINCT subscriber_id)
1346	FROM $subscriberSegmentsTable ss
1347	JOIN $segmentsTable s ON s.id = ss.segment_id AND s.`type` = :typeDefault
1348	WHERE ss.`subscriber_id` IN (:ids)
1349	", [
1350	'ids' => $ids,
1351	'typeDefault' => SegmentEntity::TYPE_DEFAULT,
1352	], ['ids' => ArrayParameterType::INTEGER])->fetchOne();
1353
1354	// Delete the segment relationships
1355	$this->entityManager->getConnection()->executeStatement("
1356	DELETE ss FROM $subscriberSegmentsTable ss
1357	JOIN $segmentsTable s ON s.id = ss.segment_id AND s.`type` = :typeDefault
1358	WHERE ss.`subscriber_id` IN (:ids)
1359	", [
1360	'ids' => $ids,
1361	'typeDefault' => SegmentEntity::TYPE_DEFAULT,
1362	], ['ids' => ArrayParameterType::INTEGER]);
1363
1364	$this->segmentsCountRecalculator->recalculateForSubscribers($ids);
1365
1366	return is_numeric($uniqueSubscribersCount) ? (int)$uniqueSubscribersCount : 0;
1367	}
1368
1369	/**
1370	* @return int - number of processed ids
1371	*/
1372	private function addSubscribersToSegment(SegmentEntity $segment, array $ids): int {
1373	if (empty($ids)) {
1374	return 0;
1375	}
1376
1377	$subscribers = $this->entityManager
1378	->createQueryBuilder()
1379	->select('s')
1380	->from(SubscriberEntity::class, 's')
1381	->leftJoin('s.subscriberSegments', 'ss', Join::WITH, 'ss.segment = :segment')
1382	->where('s.id IN (:ids)')
1383	->andWhere('ss.segment IS NULL')
1384	->setParameter('ids', $ids)
1385	->setParameter('segment', $segment)
1386	->getQuery()->execute();
1387
1388	$subscribers = is_array($subscribers) ? array_values(array_filter($subscribers, function ($s) {
1389	return $s instanceof SubscriberEntity;
1390	})) : [];
1391
1392	$this->entityManager->transactional(function (EntityManager $entityManager) use ($subscribers, $segment) {
1393	foreach ($subscribers as $subscriber) {
1394	$subscriberSegment = new SubscriberSegmentEntity($segment, $subscriber, SubscriberEntity::STATUS_SUBSCRIBED);
1395	$this->entityManager->persist($subscriberSegment);
1396	}
1397	$this->entityManager->flush();
1398	});
1399
1400	if ($subscribers !== []) {
1401	$this->segmentsCountRecalculator->recalculateForSubscribers(array_map(function (SubscriberEntity $subscriber): int {
1402	return (int)$subscriber->getId();
1403	}, $subscribers));
1404	}
1405
1406	return count($subscribers);
1407	}
1408
1409	/**
1410	* @return int - number of processed ids
1411	*/
1412	private function addTagToSubscribers(TagEntity $tag, array $ids): int {
1413	if (empty($ids)) {
1414	return 0;
1415	}
1416
1417	/** @var SubscriberEntity[] $subscribers */
1418	$subscribers = $this->entityManager
1419	->createQueryBuilder()
1420	->select('s')
1421	->from(SubscriberEntity::class, 's')
1422	->leftJoin('s.subscriberTags', 'st', Join::WITH, 'st.tag = :tag')
1423	->where('s.id IN (:ids)')
1424	->andWhere('st.tag IS NULL')
1425	->setParameter('ids', $ids)
1426	->setParameter('tag', $tag)
1427	->getQuery()->execute();
1428
1429	$this->entityManager->wrapInTransaction(function (EntityManager $entityManager) use ($subscribers, $tag) {
1430	foreach ($subscribers as $subscriber) {
1431	$subscriberTag = new SubscriberTagEntity($tag, $subscriber);
1432	$entityManager->persist($subscriberTag);
1433	}
1434	$entityManager->flush();
1435	});
1436
1437	return count($subscribers);
1438	}
1439
1440	private function getCurrentDateTime(): Carbon {
1441	return Carbon::now()->setMilliseconds(0);
1442	}
1443	}
1444