SubscribersRepository.php — MailPoet – Newsletters, Email Marketing, and Automation 5.34.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 1 month 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 1 month 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 TrackingConsentController.php 1 month ago index.php 3 years ago

SubscribersRepository.php

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