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

mailpoet / lib / Subscribers / SubscribersRepository.php

mailpoet / lib / Subscribers Last commit date

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

SubscribersRepository.php

1533 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, bool $skipHooks = true): int {
814	$subscriberSegments = $this->addSubscribersToSegment($segment, $ids);
815	$this->changesNotifier->subscribersUpdated($ids);
816	if (!$skipHooks) {
817	$this->fireSegmentSubscribedHooks($subscriberSegments);
818	}
819	return count($subscriberSegments);
820	}
821
822	/**
823	* @return int - number of processed ids
824	*/
825	public function bulkMoveToSegment(SegmentEntity $segment, array $ids, bool $skipHooks = true): int {
826	if (empty($ids)) {
827	return 0;
828	}
829
830	$subscriberIdsAlreadyInSegment = [];
831	if (!$skipHooks) {
832	/** @var string[] $subscriberIdsAlreadyInSegment */
833	$subscriberIdsAlreadyInSegment = $this->entityManager
834	->createQueryBuilder()
835	->select('IDENTITY(ss.subscriber)')
836	->from(SubscriberSegmentEntity::class, 'ss')
837	->where('ss.subscriber IN (:ids)')
838	->andWhere('ss.segment = :segment')
839	->andWhere('ss.status = :status')
840	->setParameter('ids', $ids)
841	->setParameter('segment', $segment)
842	->setParameter('status', SubscriberEntity::STATUS_SUBSCRIBED)
843	->getQuery()
844	->getSingleColumnResult();
845	$subscriberIdsAlreadyInSegment = array_fill_keys(array_map('intval', $subscriberIdsAlreadyInSegment), true);
846	}
847
848	$this->removeSubscribersFromAllSegments($ids);
849	$subscriberSegments = $this->addSubscribersToSegment($segment, $ids);
850
851	$this->changesNotifier->subscribersUpdated($ids);
852	if (!$skipHooks) {
853	$this->fireSegmentSubscribedHooks($subscriberSegments, $subscriberIdsAlreadyInSegment);
854	}
855	return count($subscriberSegments);
856	}
857
858	public function bulkUnsubscribe(array $ids): int {
859	$this->entityManager->createQueryBuilder()
860	->update(SubscriberEntity::class, 's')
861	->set('s.status', ':status')
862	->where('s.id IN (:ids)')
863	->setParameter('status', SubscriberEntity::STATUS_UNSUBSCRIBED)
864	->setParameter('ids', $ids)
865	->getQuery()->execute();
866
867	$this->changesNotifier->subscribersUpdated($ids);
868	$this->changesNotifier->subscribersCountChanged($ids);
869	$this->invalidateTotalSubscribersCache();
870	return count($ids);
871	}
872
873	public function bulkUpdateLastSendingAt(array $ids, DateTimeInterface $dateTime): int {
874	if (empty($ids)) {
875	return 0;
876	}
877	$this->entityManager->createQueryBuilder()
878	->update(SubscriberEntity::class, 's')
879	->set('s.lastSendingAt', ':lastSendingAt')
880	->where('s.id IN (:ids)')
881	->setParameter('lastSendingAt', $dateTime)
882	->setParameter('ids', $ids)
883	->getQuery()
884	->execute();
885	return count($ids);
886	}
887
888	public function bulkUpdateEngagementScoreUpdatedAt(array $ids, ?DateTimeInterface $dateTime): void {
889	if (empty($ids)) {
890	return;
891	}
892	$this->entityManager->createQueryBuilder()
893	->update(SubscriberEntity::class, 's')
894	->set('s.engagementScoreUpdatedAt', ':dateTime')
895	->where('s.id IN (:ids)')
896	->setParameter('dateTime', $dateTime)
897	->setParameter('ids', $ids)
898	->getQuery()
899	->execute();
900	}
901
902	public function findWpUserIdAndEmailByEmails(array $emails): array {
903	return $this->entityManager->createQueryBuilder()
904	->select('s.wpUserId AS wp_user_id, LOWER(s.email) AS email')
905	->from(SubscriberEntity::class, 's')
906	->where('s.email IN (:emails)')
907	->setParameter('emails', $emails)
908	->getQuery()->getResult();
909	}
910
911	public function findIdAndEmailByEmails(array $emails): array {
912	return $this->entityManager->createQueryBuilder()
913	->select('s.id, s.email')
914	->from(SubscriberEntity::class, 's')
915	->where('s.email IN (:emails)')
916	->setParameter('emails', $emails)
917	->getQuery()->getResult();
918	}
919
920	/**
921	* @return int[]
922	*/
923	public function findIdsOfDeletedByEmails(array $emails): array {
924	$rows = $this->entityManager->createQueryBuilder()
925	->select('s.id')
926	->from(SubscriberEntity::class, 's')
927	->where('s.email IN (:emails)')
928	->andWhere('s.deletedAt IS NOT NULL')
929	->setParameter('emails', $emails)
930	->getQuery()->getResult();
931	return array_values(array_map('intval', array_column(is_array($rows) ? $rows : [], 'id')));
932	}
933
934	public function getCurrentWPUser(): ?SubscriberEntity {
935	$wpUser = WPFunctions::get()->wpGetCurrentUser();
936	if (empty($wpUser->ID)) {
937	return null; // Don't look up a subscriber for guests
938	}
939	return $this->findOneBy(['wpUserId' => $wpUser->ID]);
940	}
941
942	/**
943	* @return int[]
944	*/
945	public function findIdsByUpdatedScoreNotInLastMonth(int $limit): array {
946	$dateTime = (new Carbon())->subMonths(1);
947	$ids = $this->entityManager->createQueryBuilder()
948	->select('s.id')
949	->from(SubscriberEntity::class, 's')
950	->where('s.engagementScoreUpdatedAt IS NULL')
951	->orWhere('s.engagementScoreUpdatedAt < :dateTime')
952	->setParameter('dateTime', $dateTime)
953	->getQuery()
954	->setMaxResults($limit)
955	->getSingleColumnResult();
956	$intIds = [];
957	foreach ($ids as $id) {
958	if (is_numeric($id)) {
959	$intIds[] = (int)$id;
960	}
961	}
962	return $intIds;
963	}
964
965	public function maybeUpdateLastEngagement(SubscriberEntity $subscriberEntity): void {
966	$now = $this->getCurrentDateTime();
967	// Do not update engagement if was recently updated to avoid unnecessary updates in DB
968	if ($subscriberEntity->getLastEngagementAt() && $subscriberEntity->getLastEngagementAt() > $now->subMinute()) {
969	return;
970	}
971	// Update last engagement
972	$subscriberEntity->markEngaged($now);
973	$this->flush();
974	}
975
976	public function maybeUpdateLastOpenAt(SubscriberEntity $subscriberEntity): void {
977	$now = $this->getCurrentDateTime();
978	// Avoid unnecessary DB calls
979	if ($subscriberEntity->getLastOpenAt() && $subscriberEntity->getLastOpenAt() > $now->subMinute()) {
980	return;
981	}
982	$subscriberEntity->setLastOpenAt($now);
983	$subscriberEntity->markEngaged($now);
984	$this->flush();
985	}
986
987	public function maybeUpdateLastClickAt(SubscriberEntity $subscriberEntity): void {
988	$now = $this->getCurrentDateTime();
989	// Avoid unnecessary DB calls
990	if ($subscriberEntity->getLastClickAt() && $subscriberEntity->getLastClickAt() > $now->subMinute()) {
991	return;
992	}
993	$subscriberEntity->setLastClickAt($now);
994	$subscriberEntity->markEngaged($now);
995	$this->flush();
996	}
997
998	public function maybeUpdateLastPurchaseAt(SubscriberEntity $subscriberEntity): void {
999	$now = $this->getCurrentDateTime();
1000	// Avoid unnecessary DB calls
1001	if ($subscriberEntity->getLastPurchaseAt() && $subscriberEntity->getLastPurchaseAt() > $now->subMinute()) {
1002	return;
1003	}
1004	$subscriberEntity->setLastPurchaseAt($now);
1005	$subscriberEntity->markEngaged($now);
1006	$this->flush();
1007	}
1008
1009	public function maybeUpdateLastPageViewAt(SubscriberEntity $subscriberEntity): void {
1010	$now = $this->getCurrentDateTime();
1011	// Avoid unnecessary DB calls
1012	if ($subscriberEntity->getLastPageViewAt() && $subscriberEntity->getLastPageViewAt() > $now->subMinute()) {
1013	return;
1014	}
1015	$subscriberEntity->setLastPageViewAt($now);
1016	$subscriberEntity->markEngaged($now);
1017	$this->flush();
1018	}
1019
1020	public function getMaxSubscriberId(): int {
1021	$maxSubscriberId = $this->entityManager->createQueryBuilder()
1022	->select('MAX(s.id)')
1023	->from(SubscriberEntity::class, 's')
1024	->getQuery()
1025	->getSingleScalarResult();
1026
1027	return intval($maxSubscriberId);
1028	}
1029
1030	/**
1031	* Returns [count, maxId] of the next $batchSize subscriber rows with id >= $startId, ordered by id.
1032	* count === 0 means there are no more subscribers from $startId onward.
1033	*
1034	* @return array{0:int,1:int}
1035	*/
1036	public function getNextIdWindow(int $startId, int $batchSize): array {
1037	$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
1038	$result = $this->entityManager->getConnection()->executeQuery(
1039	"
1040	SELECT COUNT(ids.id) as count, COALESCE(MAX(ids.id), 0) as max FROM (
1041	SELECT s.id FROM {$subscribersTable} as s
1042	WHERE s.id >= :startId
1043	ORDER BY s.id
1044	LIMIT :batchSize
1045	) ids
1046	",
1047	[
1048	'startId' => $startId,
1049	'batchSize' => $batchSize,
1050	],
1051	[
1052	'startId' => ParameterType::INTEGER,
1053	'batchSize' => ParameterType::INTEGER,
1054	]
1055	)->fetchAssociative();
1056
1057	if (!is_array($result)) {
1058	return [0, 0];
1059	}
1060
1061	/** @var array{count: int, max: int} $result - it's required for PHPStan */
1062	return [intval($result['count']), intval($result['max'])];
1063	}
1064
1065	/**
1066	* Returns count of subscribers who subscribed after given date regardless of their current status.
1067	* @return int
1068	*/
1069	public function getCountOfLastSubscribedAfter(\DateTimeInterface $subscribedAfter): int {
1070	$result = $this->entityManager->createQueryBuilder()
1071	->select('COUNT(s.id)')
1072	->from(SubscriberEntity::class, 's')
1073	->where('s.lastSubscribedAt > :lastSubscribedAt')
1074	->andWhere('s.deletedAt IS NULL')
1075	->setParameter('lastSubscribedAt', $subscribedAfter)
1076	->getQuery()
1077	->getSingleScalarResult();
1078	return intval($result);
1079	}
1080
1081	/**
1082	* Returns count of subscribers who unsubscribed after given date regardless of their current status.
1083	* @return int
1084	*/
1085	public function getCountOfUnsubscribedAfter(\DateTimeInterface $unsubscribedAfter): int {
1086	$result = $this->entityManager->createQueryBuilder()
1087	->select('COUNT(DISTINCT s.id)')
1088	->from(StatisticsUnsubscribeEntity::class, 'su')
1089	->join('su.subscriber', 's')
1090	->andWhere('su.createdAt > :unsubscribedAfter')
1091	->andWhere('s.deletedAt IS NULL')
1092	->setParameter('unsubscribedAfter', $unsubscribedAfter)
1093	->getQuery()
1094	->getSingleScalarResult();
1095	return intval($result);
1096	}
1097
1098	/**
1099	* Returns count of subscribers who subscribed to a list after given date regardless of their current global status.
1100	*/
1101	public function getListLevelCountsOfSubscribedAfter(\DateTimeInterface $date): array {
1102	$data = $this->entityManager->createQueryBuilder()
1103	->select('seg.id, seg.name, seg.type, seg.averageEngagementScore, COUNT(ss.id) as count')
1104	->from(SubscriberSegmentEntity::class, 'ss')
1105	->join('ss.subscriber', 's')
1106	->join('ss.segment', 'seg')
1107	->where('ss.updatedAt > :date')
1108	->andWhere('ss.status = :segment_status')
1109	->andWhere('s.lastSubscribedAt > :date') // subscriber subscribed at some point after the date
1110	->andWhere('s.deletedAt IS NULL')
1111	->andWhere('seg.deletedAt IS NULL') // no trashed lists and disabled WP Users list
1112	->setParameter('date', $date)
1113	->setParameter('segment_status', SubscriberEntity::STATUS_SUBSCRIBED)
1114	->groupBy('ss.segment')
1115	->getQuery()
1116	->getArrayResult();
1117	return $data;
1118	}
1119
1120	/**
1121	* Returns count of subscribers who unsubscribed from a list after given date regardless of their current global status.
1122	*/
1123	public function getListLevelCountsOfUnsubscribedAfter(\DateTimeInterface $date): array {
1124	return $this->entityManager->createQueryBuilder()
1125	->select('seg.id, seg.name, seg.type, seg.averageEngagementScore, COUNT(ss.id) as count')
1126	->from(SubscriberSegmentEntity::class, 'ss')
1127	->join('ss.subscriber', 's')
1128	->join('ss.segment', 'seg')
1129	->where('ss.updatedAt > :date')
1130	->andWhere('ss.status = :segment_status')
1131	->andWhere('s.deletedAt IS NULL')
1132	->andWhere('seg.deletedAt IS NULL') // no trashed lists and disabled WP Users list
1133	->setParameter('date', $date)
1134	->setParameter('segment_status', SubscriberEntity::STATUS_UNSUBSCRIBED)
1135	->groupBy('ss.segment')
1136	->getQuery()
1137	->getArrayResult();
1138	}
1139
1140	/**
1141	* @return int - number of processed ids
1142	*/
1143	public function bulkAddTag(TagEntity $tag, array $ids, bool $skipHooks = true): int {
1144	$count = $this->addTagToSubscribers($tag, $ids, $skipHooks);
1145	$this->changesNotifier->subscribersUpdated($ids);
1146	return $count;
1147	}
1148
1149	/**
1150	* @return int - number of processed ids
1151	*/
1152	public function bulkRemoveTag(TagEntity $tag, array $ids, bool $skipHooks = true): int {
1153	if (empty($ids)) {
1154	return 0;
1155	}
1156
1157	$subscriberTags = [];
1158	if (!$skipHooks) {
1159	/** @var SubscriberTagEntity[] $subscriberTags */
1160	$subscriberTags = $this->entityManager
1161	->createQueryBuilder()
1162	->select('st')
1163	->from(SubscriberTagEntity::class, 'st')
1164	->where('st.subscriber IN (:ids)')
1165	->andWhere('st.tag = :tag')
1166	->setParameter('ids', $ids)
1167	->setParameter('tag', $tag)
1168	->getQuery()->execute();
1169	}
1170
1171	$subscriberTagsTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName();
1172	$count = (int)$this->entityManager->getConnection()->executeStatement("
1173	DELETE st FROM $subscriberTagsTable st
1174	WHERE st.`subscriber_id` IN (:ids)
1175	AND st.`tag_id` = :tag_id
1176	", ['ids' => $ids, 'tag_id' => $tag->getId()], ['ids' => ArrayParameterType::INTEGER]);
1177
1178	if (!$skipHooks) {
1179	// Fires the hook that triggers "Tag removed" automations (see SubscriberSaveController::updateTags()).
1180	foreach ($subscriberTags as $subscriberTag) {
1181	$this->entityManager->detach($subscriberTag);
1182	$this->wp->doAction('mailpoet_subscriber_tag_removed', $subscriberTag);
1183	}
1184	}
1185
1186	$this->changesNotifier->subscribersUpdated($ids);
1187	return $count;
1188	}
1189
1190	public function removeOrphanedSubscribersFromWpSegment(): void {
1191	global $wpdb;
1192
1193	$segmentId = $this->segmentsRepository->getWpUsersSegment()->getId();
1194
1195	$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
1196	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
1197	$segmentsTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName();
1198	$deletedAt = $this->getCurrentDateTime()->format('Y-m-d H:i:s');
1199
1200	$affectedIds = [];
1201	$this->entityManager->wrapInTransaction(function () use ($segmentId, $subscribersTable, $subscriberSegmentsTable, $segmentsTable, $deletedAt, $wpdb, &$affectedIds): void {
1202	// Hard-delete broken subscribers in the WP-Users segment when they have no
1203	// email, or when they have no WP user ID and no other list to belong to.
1204	$this->entityManager->getConnection()->executeStatement(
1205	"DELETE s
1206	FROM {$subscribersTable} s
1207	INNER JOIN {$subscriberSegmentsTable} ss ON s.id = ss.subscriber_id
1208	WHERE ss.segment_id = :segmentId
1209	AND (
1210	s.email = ''
1211	OR (
1212	s.wp_user_id IS NULL
1213	AND s.is_woocommerce_user = 0
1214	AND NOT EXISTS (
1215	SELECT 1 FROM {$subscriberSegmentsTable} ss_other
1216	INNER JOIN {$segmentsTable} seg ON seg.id = ss_other.segment_id
1217	WHERE ss_other.subscriber_id = s.id
1218	AND seg.type != :wpType
1219	AND seg.deleted_at IS NULL
1220	)
1221	)
1222	)",
1223	[
1224	'segmentId' => $segmentId,
1225	'wpType' => SegmentEntity::TYPE_WP_USERS,
1226	],
1227	[
1228	'segmentId' => ParameterType::INTEGER,
1229	'wpType' => ParameterType::STRING,
1230	]
1231	);
1232
1233	// Trash subscribers whose WP user is gone, who are only on the WP-Users list,
1234	// and who are not WC customers — they have nowhere left to belong, but we keep
1235	// them as soft-deleted so admins can recover them if needed.
1236	$this->entityManager->getConnection()->executeStatement(
1237	"UPDATE {$subscribersTable} s
1238	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1239	SET s.deleted_at = :deletedAt, s.status = :unconfirmed
1240	WHERE s.deleted_at IS NULL
1241	AND s.is_woocommerce_user = 0
1242	AND s.wp_user_id IS NOT NULL
1243	AND u.id IS NULL
1244	AND EXISTS (
1245	SELECT 1 FROM {$subscriberSegmentsTable} ss_wp
1246	WHERE ss_wp.subscriber_id = s.id AND ss_wp.segment_id = :segmentId
1247	)
1248	AND NOT EXISTS (
1249	SELECT 1 FROM {$subscriberSegmentsTable} ss_other
1250	INNER JOIN {$segmentsTable} seg ON seg.id = ss_other.segment_id
1251	WHERE ss_other.subscriber_id = s.id
1252	AND seg.type != :wpType
1253	AND seg.deleted_at IS NULL
1254	)",
1255	[
1256	'segmentId' => $segmentId,
1257	'unconfirmed' => SubscriberEntity::STATUS_UNCONFIRMED,
1258	'wpType' => SegmentEntity::TYPE_WP_USERS,
1259	'deletedAt' => $deletedAt,
1260	],
1261	[
1262	'segmentId' => ParameterType::INTEGER,
1263	'unconfirmed' => ParameterType::STRING,
1264	'wpType' => ParameterType::STRING,
1265	'deletedAt' => ParameterType::STRING,
1266	]
1267	);
1268
1269	// Capture subscribers whose WP-Users membership is subscribed before
1270	// deleting it — only those have a segments_count that needs updating.
1271	// Subscribers already handled by the soft-trash above (no other segments)
1272	// are included here too; their recalculation will be a no-op (re-derives 0).
1273	$subscribedStatus = SubscriberEntity::STATUS_SUBSCRIBED;
1274	$rows = $this->entityManager->getConnection()->executeQuery(
1275	"SELECT ss.subscriber_id
1276	FROM {$subscriberSegmentsTable} ss
1277	INNER JOIN {$subscribersTable} s ON s.id = ss.subscriber_id
1278	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1279	WHERE ss.segment_id = :segmentId
1280	AND (s.wp_user_id IS NULL OR u.id IS NULL)
1281	AND ss.status = :status",
1282	['segmentId' => $segmentId, 'status' => $subscribedStatus],
1283	['segmentId' => ParameterType::INTEGER, 'status' => ParameterType::STRING]
1284	)->fetchFirstColumn();
1285	$affectedIds = array_map(fn($id): int => is_numeric($id) ? (int)$id : 0, $rows);
1286
1287	// Remove WP-Users segment memberships for orphans.
1288	$this->entityManager->getConnection()->executeStatement(
1289	"DELETE ss
1290	FROM {$subscriberSegmentsTable} ss
1291	INNER JOIN {$subscribersTable} s ON s.id = ss.subscriber_id
1292	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1293	WHERE ss.segment_id = :segmentId
1294	AND (s.wp_user_id IS NULL OR u.id IS NULL)",
1295	['segmentId' => $segmentId],
1296	['segmentId' => ParameterType::INTEGER]
1297	);
1298
1299	// Detach subscribers from non-existent WP users and mark the source.
1300	$this->entityManager->getConnection()->executeStatement(
1301	"UPDATE {$subscribersTable} s
1302	LEFT JOIN {$wpdb->users} u ON u.id = s.wp_user_id
1303	SET s.wp_user_id = NULL, s.source = :source
1304	WHERE s.wp_user_id IS NOT NULL AND u.id IS NULL",
1305	['source' => Source::WORDPRESS_USER_DELETED],
1306	['source' => ParameterType::STRING]
1307	);
1308	});
1309
1310	if ($affectedIds !== []) {
1311	$this->segmentsCountRecalculator->recalculateForSubscribers($affectedIds);
1312	}
1313	}
1314
1315	public function removeByWpUserIds(array $wpUserIds) {
1316	if (empty($wpUserIds)) {
1317	return 0;
1318	}
1319
1320	$subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
1321	$subscriberIds = array_map(
1322	function($id): int {
1323	return $this->toInt($id);
1324	},
1325	$this->entityManager->getConnection()->executeQuery(
1326	"SELECT `id` FROM $subscriberTable WHERE `wp_user_id` IN (:wpUserIds)",
1327	['wpUserIds' => $wpUserIds],
1328	['wpUserIds' => ArrayParameterType::INTEGER]
1329	)->fetchFirstColumn()
1330	);
1331
1332	if (empty($subscriberIds)) {
1333	return 0;
1334	}
1335
1336	$count = 0;
1337	$this->entityManager->transactional(function (EntityManager $entityManager) use ($subscriberIds, &$count) {
1338	$this->removeSubscribersRelatedRows($subscriberIds);
1339
1340	$count = $entityManager->createQueryBuilder()
1341	->delete(SubscriberEntity::class, 's')
1342	->where('s.id IN (:ids)')
1343	->setParameter('ids', $subscriberIds)
1344	->getQuery()->execute();
1345	});
1346
1347	$this->changesNotifier->subscribersDeleted($subscriberIds);
1348	$this->invalidateTotalSubscribersCache();
1349
1350	return $count;
1351	}
1352
1353	/**
1354	* Removes rows in tables related to the given subscribers so no orphans are
1355	* left behind after the subscribers themselves are deleted. Unlike
1356	* removeSubscribersFromAllSegments() this removes every segment membership
1357	* regardless of segment type, since the subscribers are being fully removed.
1358	*/
1359	private function removeSubscribersRelatedRows(array $subscriberIds): void {
1360	if (empty($subscriberIds)) {
1361	return;
1362	}
1363
1364	$connection = $this->entityManager->getConnection();
1365	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
1366	$subscriberCustomFieldTable = $this->entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName();
1367	$subscriberTagTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName();
1368
1369	$connection->executeStatement(
1370	"DELETE FROM $subscriberSegmentsTable WHERE `subscriber_id` IN (:ids)",
1371	['ids' => $subscriberIds],
1372	['ids' => ArrayParameterType::INTEGER]
1373	);
1374
1375	$connection->executeStatement(
1376	"DELETE FROM $subscriberCustomFieldTable WHERE `subscriber_id` IN (:ids)",
1377	['ids' => $subscriberIds],
1378	['ids' => ArrayParameterType::INTEGER]
1379	);
1380
1381	$connection->executeStatement(
1382	"DELETE FROM $subscriberTagTable WHERE `subscriber_id` IN (:ids)",
1383	['ids' => $subscriberIds],
1384	['ids' => ArrayParameterType::INTEGER]
1385	);
1386	}
1387
1388	/**
1389	* @return int - number of processed ids
1390	*/
1391	private function removeSubscribersFromAllSegments(array $ids): int {
1392	if (empty($ids)) {
1393	return 0;
1394	}
1395
1396	$subscriberSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
1397	$segmentsTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName();
1398
1399	// Count unique subscribers that will have segments removed
1400	$uniqueSubscribersCount = $this->entityManager->getConnection()->executeQuery("
1401	SELECT COUNT(DISTINCT subscriber_id)
1402	FROM $subscriberSegmentsTable ss
1403	JOIN $segmentsTable s ON s.id = ss.segment_id AND s.`type` = :typeDefault
1404	WHERE ss.`subscriber_id` IN (:ids)
1405	", [
1406	'ids' => $ids,
1407	'typeDefault' => SegmentEntity::TYPE_DEFAULT,
1408	], ['ids' => ArrayParameterType::INTEGER])->fetchOne();
1409
1410	// Delete the segment relationships
1411	$this->entityManager->getConnection()->executeStatement("
1412	DELETE ss FROM $subscriberSegmentsTable ss
1413	JOIN $segmentsTable s ON s.id = ss.segment_id AND s.`type` = :typeDefault
1414	WHERE ss.`subscriber_id` IN (:ids)
1415	", [
1416	'ids' => $ids,
1417	'typeDefault' => SegmentEntity::TYPE_DEFAULT,
1418	], ['ids' => ArrayParameterType::INTEGER]);
1419
1420	$this->segmentsCountRecalculator->recalculateForSubscribers($ids);
1421
1422	return is_numeric($uniqueSubscribersCount) ? (int)$uniqueSubscribersCount : 0;
1423	}
1424
1425	/**
1426	* @param int[] $ids
1427	* @return SubscriberSegmentEntity[]
1428	*/
1429	private function addSubscribersToSegment(SegmentEntity $segment, array $ids): array {
1430	if (empty($ids)) {
1431	return [];
1432	}
1433
1434	$subscribers = $this->entityManager
1435	->createQueryBuilder()
1436	->select('s')
1437	->from(SubscriberEntity::class, 's')
1438	->leftJoin('s.subscriberSegments', 'ss', Join::WITH, 'ss.segment = :segment')
1439	->where('s.id IN (:ids)')
1440	->andWhere('ss.segment IS NULL')
1441	->setParameter('ids', $ids)
1442	->setParameter('segment', $segment)
1443	->getQuery()->execute();
1444
1445	$subscribers = is_array($subscribers) ? array_values(array_filter($subscribers, function ($s) {
1446	return $s instanceof SubscriberEntity;
1447	})) : [];
1448
1449	$subscriberSegments = [];
1450	$this->entityManager->transactional(function (EntityManager $entityManager) use ($subscribers, $segment, &$subscriberSegments) {
1451	foreach ($subscribers as $subscriber) {
1452	$subscriberSegment = new SubscriberSegmentEntity($segment, $subscriber, SubscriberEntity::STATUS_SUBSCRIBED);
1453	$entityManager->persist($subscriberSegment);
1454	$subscriberSegments[] = $subscriberSegment;
1455	}
1456	$entityManager->flush();
1457	});
1458
1459	if ($subscribers !== []) {
1460	$this->segmentsCountRecalculator->recalculateForSubscribers(array_map(function (SubscriberEntity $subscriber): int {
1461	return (int)$subscriber->getId();
1462	}, $subscribers));
1463	}
1464
1465	return $subscriberSegments;
1466	}
1467
1468	/**
1469	* @param SubscriberSegmentEntity[] $subscriberSegments
1470	* @param array<int, true> $subscriberIdsToSkip
1471	*/
1472	private function fireSegmentSubscribedHooks(
1473	array $subscriberSegments,
1474	array $subscriberIdsToSkip = []
1475	): void {
1476	foreach ($subscriberSegments as $subscriberSegment) {
1477	$subscriber = $subscriberSegment->getSubscriber();
1478	if (
1479	!$subscriber instanceof SubscriberEntity
1480	\|\| $subscriber->getStatus() !== SubscriberEntity::STATUS_SUBSCRIBED
1481	\|\| isset($subscriberIdsToSkip[(int)$subscriber->getId()])
1482	) {
1483	continue;
1484	}
1485	$this->wp->doAction('mailpoet_segment_subscribed', $subscriberSegment);
1486	}
1487	}
1488
1489	/**
1490	* @return int - number of processed ids
1491	*/
1492	private function addTagToSubscribers(TagEntity $tag, array $ids, bool $skipHooks): int {
1493	if (empty($ids)) {
1494	return 0;
1495	}
1496
1497	/** @var SubscriberEntity[] $subscribers */
1498	$subscribers = $this->entityManager
1499	->createQueryBuilder()
1500	->select('s')
1501	->from(SubscriberEntity::class, 's')
1502	->leftJoin('s.subscriberTags', 'st', Join::WITH, 'st.tag = :tag')
1503	->where('s.id IN (:ids)')
1504	->andWhere('st.tag IS NULL')
1505	->setParameter('ids', $ids)
1506	->setParameter('tag', $tag)
1507	->getQuery()->execute();
1508
1509	$subscriberTags = [];
1510	$this->entityManager->wrapInTransaction(function (EntityManager $entityManager) use ($subscribers, $tag, &$subscriberTags) {
1511	foreach ($subscribers as $subscriber) {
1512	$subscriberTag = new SubscriberTagEntity($tag, $subscriber);
1513	$entityManager->persist($subscriberTag);
1514	$subscriberTags[] = $subscriberTag;
1515	}
1516	$entityManager->flush();
1517	});
1518
1519	if (!$skipHooks) {
1520	// Fires the hook that triggers "Tag added" automations (see SubscriberSaveController::updateTags()).
1521	foreach ($subscriberTags as $subscriberTag) {
1522	$this->wp->doAction('mailpoet_subscriber_tag_added', $subscriberTag);
1523	}
1524	}
1525
1526	return count($subscribers);
1527	}
1528
1529	private function getCurrentDateTime(): Carbon {
1530	return Carbon::now()->setMilliseconds(0);
1531	}
1532	}
1533