DoctrineDonationRepository.php

<?php

declare(strict_types=1);

namespace MatchBot\Domain;

use DateTime;
use Doctrine\DBAL\Exception as DBALException;
use Doctrine\DBAL\LockMode;
use Doctrine\ORM\Query;
use GuzzleHttp\Exception\BadResponseException;
use GuzzleHttp\Exception\ConnectException;
use MatchBot\Application\Environment;
use MatchBot\Application\Messenger\DonationUpserted;
use MatchBot\Client\BadRequestException;
use MatchBot\Client\NotFoundException;
use Ramsey\Uuid\UuidInterface;
use Symfony\Component\Messenger\MessageBusInterface;

/**
 * @template-extends SalesforceProxyRepository<Donation, \MatchBot\Client\Donation>
 * @psalm-suppress MissingConstructor Doctrine get repo DI isn't very friendly to custom constructors.
 */
class DoctrineDonationRepository extends SalesforceProxyRepository implements DonationRepository
{
    /** Maximum of each type of pending object to process */
    private const int MAX_PER_BULK_PUSH = 5_000;

    private const int MAX_SALEFORCE_FIELD_UPDATE_TRIES = 3;

    #[\Override]
    public function findWithExpiredMatching(\DateTimeImmutable $now): array
    {
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT d.uuid FROM MatchBot\Domain\Donation d

            -- Only select donations with 1+ FWs. We don't need any further info about the FWs.
            INNER JOIN d.fundingWithdrawals fw
            WHERE d.donationStatus IN (:expireWithStatuses)
            AND d.fundsReservedUntil < :now

            -- First of a regular giving series is Pending during 3DS. If we ever make the timeout for
            -- that longer than the timeout for matching, we still want to ensure matching can't be
            -- lost while 3DS is in progress.
            AND d.mandate is null
            AND fw.releasedAt is null
            GROUP BY d.id
            DQL
        )
            ->setParameter('expireWithStatuses', [DonationStatus::Pending->value, DonationStatus::Cancelled->value])
            ->setParameter('now', $now);

        // As this is used by the only regular task working with donations,
        // `ExpireMatchFunds`, it makes more sense to opt it out of result caching
        // here rather than take the performance hit of a full query cache clear
        // after every single persisted donation.
        /** @var list<array{uuid: UuidInterface}> $rows */
        $rows = $query
            ->disableResultCache()
            ->getResult();

        return array_map(static fn(array $row): UuidInterface => $row['uuid'], $rows);
    }

    #[\Override]
    public function findWithMatchingWhichCouldBeReplacedWithHigherPriorityAllocation(
        \DateTimeImmutable $campaignsClosedBefore,
        \DateTimeImmutable $donationsCollectedAfter,
    ): array {
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT d FROM MatchBot\Domain\Donation d
            -- Only select donations with 1+ FWs (i.e. some matching).
            INNER JOIN d.fundingWithdrawals fw
            INNER JOIN fw.campaignFunding donationCf
            INNER JOIN donationCf.fund donationFund
            INNER JOIN d.campaign c
            -- Join CampaignFundings allocated to campaign `c` with some amount available.
            INNER JOIN c.campaignFundings availableCf WITH availableCf.amountAvailable > 0
            INNER JOIN availableCf.fund availableFund
            WHERE c.endDate < :campaignsClosedBefore
            AND d.donationStatus IN (:collectedStatuses)
            AND d.collectedAt > :donationsCollectedAfter
            -- Only consider CampaignFundings with lower allocationOrder than `fw`'s.
            AND availableFund.allocationOrder < donationFund.allocationOrder
            AND fw.releasedAt is null
            GROUP BY d.id
            ORDER BY d.id ASC
        DQL
        );

        $query->setParameter('campaignsClosedBefore', $campaignsClosedBefore);
        $query->setParameter('collectedStatuses', DonationStatus::SUCCESS_STATUSES);
        $query->setParameter('donationsCollectedAfter', $donationsCollectedAfter);

        // Result caching rationale as per `findWithExpiredMatching()`.
        /** @var Donation[] $donations */
        $donations = $query
            ->disableResultCache()
            ->getResult();

        return $donations;
    }

    #[\Override]
    public function findByUuids(array $uuids): array
    {
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT d FROM MatchBot\Domain\Donation d
            WHERE d.uuid IN (:uuids)
            ORDER BY d.createdAt ASC
DQL);
        $query->setParameter('uuids', $uuids);
        /** @var Donation[] $donations */
        $donations = $query->getResult();

        return $donations;
    }

    #[\Override]
    public function findReadyToClaimGiftAid(bool $withResends): array
    {
        if ($withResends && getenv('APP_ENV') === 'production') {
            throw new \LogicException('Cannot re-send live donations');
        }

        // Stripe's weekly payout schedule uses a `weekly_anchor` of Monday and `delay_days` set to 14. However,
        // as of 5 July 2022, we see essentially undocumented behaviour such that donations on a Monday can have less
        // than 14 *full* days before they're paid. This led to discrepancies with when we could expect Gift Aid to be
        // sent for any donation collected between ~9am and midnight Mondays. To reduce future confusion, we now only
        // require a minimum 13 days from collection. Note that this condition has always been checked in concert with
        // the hard requirement that Stripe tell us the donation is Paid. Additionally, we wait for HMRC to tell
        // us we're approved as Agent – for charities new to us claiming Gift Aid, this is likely to be a couple
        // of months as of 2023.
        $cutoff = (new DateTime('now'))->sub(new \DateInterval('P13D'));

        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('d')
            ->from(Donation::class, 'd')
            ->innerJoin('d.campaign', 'campaign')
            ->innerJoin('campaign.charity', 'charity')
            ->where('d.donationStatus = :claimGiftAidWithStatus')
            ->andWhere('d.giftAid = TRUE')
            ->andWhere('d.tbgShouldProcessGiftAid = TRUE')
            ->andWhere('charity.tbgApprovedToClaimGiftAid = TRUE')
            ->andWhere('charity.hmrcReferenceNumber IS NOT NULL')
            ->andWhere('d.collectedAt < :claimGiftAidForDonationsBefore')
            ->orderBy('charity.id', 'ASC') // group donations for the same charity together in batches
            ->addOrderBy('d.collectedAt', 'ASC')
            ->setParameter('claimGiftAidWithStatus', DonationStatus::Paid->value)
            ->setParameter('claimGiftAidForDonationsBefore', $cutoff);

        if (!$withResends) {
            $qb = $qb->andWhere('d.tbgGiftAidRequestQueuedAt IS NULL');
        }

        /** @var Donation[] $result */
        $result = $qb->getQuery()->getResult();
        return $result;
    }

    #[\Override]
    public function findNotFullyMatchedToCampaignsWhichClosedSince(\DateTimeImmutable $closedSinceDate): array
    {
        $now = (new DateTime('now'));
        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('d')
            ->from(Donation::class, 'd')
            ->join('d.campaign', 'c')
            ->leftJoin('d.fundingWithdrawals', 'fw')
            ->where('d.donationStatus IN (:completeStatuses)')
            ->andWhere('c.isMatched = true')
            ->andWhere('c.endDate < :now')
            ->andWhere('c.endDate > :campaignClosedSince')
            ->groupBy('d.id')
            ->having(
                '(SUM(CASE WHEN fw.releasedAt is null THEN fw.amount ELSE 0 END) IS NULL
                      OR SUM(CASE WHEN fw.releasedAt is null THEN fw.amount ELSE 0 END) < d.amount)'
            ) // No withdrawals *or* less than donation
            ->orderBy('d.createdAt', 'ASC')
            ->setParameter(
                'completeStatuses',
                array_map(static fn(DonationStatus $s) => $s->value, DonationStatus::SUCCESS_STATUSES),
            )
            ->setParameter('campaignClosedSince', $closedSinceDate)
            ->setParameter('now', $now);

        // Result caching rationale as per `findWithExpiredMatching()`.
        /** @var Donation[] $result */
        $result = $qb->getQuery()
            ->disableResultCache()
            ->getResult();
        return $result;
    }


    /**
     * @psalm-suppress MixedReturnTypeCoercion
     */
    #[\Override]
    public function findRecentNotFullyMatchedToMatchCampaigns(\DateTimeImmutable $sinceDate): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('d')
            ->from(Donation::class, 'd')
            ->join('d.campaign', 'c')
            ->leftJoin('d.fundingWithdrawals', 'fw')
            ->where('d.donationStatus IN (:completeStatuses)')
            ->andWhere('c.isMatched = true')
            ->andWhere('d.createdAt >= :checkAfter')
            ->groupBy('d.id')
            ->having(
                '(SUM(CASE WHEN fw.releasedAt is null THEN fw.amount ELSE 0 END) IS NULL 
                      OR SUM(CASE WHEN fw.releasedAt is null THEN fw.amount ELSE 0 END) < d.amount)'
            ) // No withdrawals *or* less than donation
            ->orderBy('d.createdAt', 'ASC')
            ->setParameter(
                'completeStatuses',
                array_map(static fn(DonationStatus $s) => $s->value, DonationStatus::SUCCESS_STATUSES),
            )
            ->setParameter('checkAfter', $sinceDate);

        // Result caching rationale as per `findWithExpiredMatching()`, except this is
        // currently used only in the rarer case of manually invoking
        // `RetrospectivelyMatch`.
        $result = $qb->getQuery()
            ->disableResultCache()
            ->getResult();

        return $result;
    }

    #[\Override]
    public function findWithTransferIdInArray(array $transferIds): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('d')
            ->from(Donation::class, 'd')
            ->where('d.transferId IN (:transferIds)')
            ->setParameter('transferIds', $transferIds);

        /** @var Donation[] $donations */
        $donations = $qb->getQuery()->getResult();

        return $donations;
    }

    #[\Override]
    public function getRecentHighVolumeCompletionRatio(\DateTimeImmutable $nowish): ?float
    {
        $oneMinutePrior = $nowish->sub(new \DateInterval('PT1M'));
        $sixteenMinutesPrior = $nowish->sub(new \DateInterval('PT16M'));

        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT
            COUNT(d.id) as donationCount,
            SUM(CASE WHEN d.donationStatus IN (:completeStatuses) THEN 1 ELSE 0 END) as completeCount
            FROM MatchBot\Domain\Donation d
            LEFT JOIN d.fundingWithdrawals fw
            WHERE d.createdAt >= :start
            AND d.createdAt < :end
            HAVING (SUM(CASE WHEN fw.releasedAt is null THEN fw.amount ELSE 0 END )) > 0
        DQL
        );
        $query->setParameter('start', $sixteenMinutesPrior);
        $query->setParameter('end', $oneMinutePrior);
        $query->setParameter(
            'completeStatuses',
            array_map(static fn(DonationStatus $s) => $s->value, DonationStatus::SUCCESS_STATUSES),
        );

        /**
         * @var array{donationCount: int, completeCount: int}|null $result
         */
        $result = $query->getOneOrNullResult(Query::HYDRATE_ARRAY);

        if ($result === null || $result['donationCount'] < 20) {
            return null;
        }

        return (float) ($result['completeCount'] / $result['donationCount']);
    }

    #[\Override]
    public function countDonationsCreatedInMinuteTo(\DateTimeImmutable $end): int
    {
        $oneMinutePrior = $end->sub(new \DateInterval('PT1M'));
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT COUNT(d.id)
            FROM MatchBot\Domain\Donation d
            WHERE d.createdAt >= :start
            AND d.createdAt < :end
        DQL
        )
            ->setParameter('start', $oneMinutePrior)
            ->setParameter('end', $end);

        return (int) $query->getSingleScalarResult();
    }

    #[\Override]
    public function countDonationsCollectedInMinuteTo(\DateTimeImmutable $end): int
    {
        $oneMinutePrior = $end->sub(new \DateInterval('PT1M'));
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT COUNT(d.id)
            FROM MatchBot\Domain\Donation d
            WHERE d.collectedAt >= :start
            AND d.collectedAt < :end
        DQL
        )
            ->setParameter('start', $oneMinutePrior)
            ->setParameter('end', $end);

        return (int) $query->getSingleScalarResult();
    }

    #[\Override]
    public function abandonOldCancelled(): int
    {
        $twentyMinsAgo = (new DateTime('now'))
            ->sub(new \DateInterval('PT20M'));
        $pendingSFPushStatuses = [
            SalesforceWriteProxy::PUSH_STATUS_PENDING_CREATE,
            SalesforceWriteProxy::PUSH_STATUS_PENDING_UPDATE,
        ];

        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('d')
            ->from(Donation::class, 'd')
            ->where('d.donationStatus = :cancelledStatus')
            ->andWhere('d.salesforcePushStatus IN (:pendingSFPushStatuses)')
            ->andWhere('d.createdAt < :twentyMinsAgo')
            ->orderBy('d.createdAt', 'ASC')
            ->setParameter('cancelledStatus', DonationStatus::Cancelled->value)
            ->setParameter('pendingSFPushStatuses', $pendingSFPushStatuses)
            ->setParameter('twentyMinsAgo', $twentyMinsAgo);

        /** @var Donation[] $donations */
        $donations = $qb->getQuery()->getResult();
        if (count($donations) > 0) {
            foreach ($donations as $donation) {
                $donation->setSalesforcePushStatus(SalesforceWriteProxy::PUSH_STATUS_COMPLETE);
                $this->getEntityManager()->persist($donation);
            }

            $this->getEntityManager()->flush();
        }

        return count($donations);
    }

    #[\Override]
    public function findAndLockOneBy(array $criteria, ?array $orderBy = null): ?Donation
    {
        // We can't actually lock the row until we know the ID of the donation, so we fetch it first
        // using the criteria, and then call find once we know the ID to lock.
        $donation = $this->findOneBy($criteria, $orderBy);

        if ($donation === null) {
            return null;
        }

        $this->getEntityManager()->refresh($donation, LockMode::PESSIMISTIC_WRITE);

        return $donation;
    }

    /**
     * @psalm-suppress PossiblyUnusedReturnValue Psalm bug? Value is used in \MatchBot\Application\Commands\PushDonations::doExecute
     */
    #[\Override]
    public function pushSalesforcePending(\DateTimeImmutable $now, MessageBusInterface $bus): int
    {
        // We don't want to push donations that were created or modified in the last 5 minutes,
        // to avoid collisions with other pushes.
        $fiveMinutesAgo = $now->modify('-5 minutes');

        /** @var Donation[] $proxiesToCreate */
        $proxiesToCreate = $this->findBy(
            ['salesforcePushStatus' => SalesforceWriteProxy::PUSH_STATUS_PENDING_CREATE],
            ['updatedAt' => 'ASC'],
            self::MAX_PER_BULK_PUSH,
        );

        if ($proxiesToCreate !== []) {
            $count = count($proxiesToCreate);
            // Warning for now. SF blips happen, especially in sandboxes. So we think this is bad
            // enough to track on charts to see if volumes increase lots, but not to actively alert
            // on as `.ERROR`.
            $this->getLogger()->warning("pushSalesforcePending found $count pending items to push to SF, " .
                'suggests push via Symfony Messenger failed');

            $first3OrFewerProxies = array_slice($proxiesToCreate, 0, 3);
            $firstUUIDs = array_map(static fn(Donation $d) => $d->getUuid(), $first3OrFewerProxies);
            $this->getLogger()->info('pushSalesforcePending sample UUIDs: ' . implode(', ', $firstUUIDs));
        }

        foreach ($proxiesToCreate as $proxy) {
            if ($proxy->getUpdatedDate() > $fiveMinutesAgo) {
                // fetching the proxy just to skip it here is a bit wasteful but the performance cost is low
                // compared to working out how to do a findBy equivalent with multiple criteria
                // (i.e. using \Doctrine\ORM\EntityRepository::matching() method)
                continue;
            }

            $bus->dispatch(DonationUpserted::fromDonationEnveloped($proxy));
        }

        $proxiesToUpdate = $this->findBy(
            ['salesforcePushStatus' => SalesforceWriteProxy::PUSH_STATUS_PENDING_UPDATE],
            ['updatedAt' => 'ASC'],
            self::MAX_PER_BULK_PUSH,
        );

        foreach ($proxiesToUpdate as $proxy) {
            if ($proxy->getUpdatedDate() > $fiveMinutesAgo) {
                continue;
            }

            $bus->dispatch(DonationUpserted::fromDonationEnveloped($proxy));
        }

        return count($proxiesToCreate) + count($proxiesToUpdate);
    }

    /**
     * @param Salesforce18Id<Donation>|null $salesforceId
     */
    private function setSalesforceFieldsWithRetry(
        DonationUpserted $changeMessage,
        ?Salesforce18Id $salesforceId
    ): void {
        $tries = 0;

        // Try to safely set Salesforce ID, and other push tracking fields. If it
        // fails repeatedly, this should be safe to leave for a later update.
        // Salesforce has UUIDs so we won't lose the ability to reconcile the records.
        $uuid = $changeMessage->uuid;

        do {
            try {
                if ($tries > 0) {
                    /** @psalm-suppress InvalidCast There's a bug analysing do/while w.r.t. $tries */
                    $this->getLogger()->info("Retrying setting Salesforce fields for donation $uuid after $tries tries");
                }
                $this->setSalesforcePushComplete($uuid, $salesforceId);
                return;
            } catch (DBALException\RetryableException $exception) {
                $this->logInfo(sprintf(
                    '%s: Lock unavailable to set Salesforce fields on donation %s with Salesforce ID %s on try #%d',
                    get_class($exception),
                    $uuid,
                    $salesforceId->value ?? 'null',
                    $tries,
                ));
            } catch (DBALException\ConnectionLost $exception) {
                // Seen only at fairly quiet times *and* before we increased DB wait_timeout from 8 hours
                // to just over workers' max lifetime of 24 hours. Should happen rarely or never with new DB config.
                $this->logWarning(sprintf(
                    '%s: Connection lost while setting Salesforce fields on donation %s, try #%d',
                    get_class($exception),
                    $uuid,
                    $tries,
                ));
            }

            $tries++;
        } while ($tries < self::MAX_SALEFORCE_FIELD_UPDATE_TRIES);

        $this->logError(
            "Failed to set Salesforce fields for donation $uuid after $tries tries"
        );
    }

    /**
     * Sets a Salesforce ID (and general status things) without its own lock and importantly without the ORM, using
     * a raw DQL `UPDATE` that should make it safe irrespective of ORM work that could also be happening on the record.
     *
     *  Consider DRYing up duplication with MandateUpsertedHandler::setSalesforceFields before
     *  making a third copy
     * /
     *
     * @param Salesforce18Id<Donation>|null $salesforceId
     *
     * @throws DBALException\LockWaitTimeoutException if some other transaction is holding a lock
     */
    private function setSalesforcePushComplete(string $uuid, ?Salesforce18Id $salesforceId): void
    {
        $now = new \DateTimeImmutable('now');
        $query = $this->getEntityManager()->createQuery(
            <<<'DQL'
            UPDATE Matchbot\Domain\Donation donation
            SET
                donation.salesforceId = :salesforceId,
                donation.salesforcePushStatus = 'complete',
                donation.salesforceLastPush = :now
            WHERE donation.uuid = :uuid
            DQL
        );
        $query->setParameter('now', $now);
        $query->setParameter('salesforceId', $salesforceId?->value);
        $query->setParameter('uuid', $uuid);
        $query->execute();
    }

    /**
     * Flag a donation for a re-push. Only use after Salesforce callout failures. Should lead
     * to a scheduled job's new attempt in the next ~30 minutes in most cases.
     */
    private function setSalesforceRePushNeeded(string $donationUUID): void
    {
        $query = $this->getEntityManager()->createQuery(
            <<<'DQL'
            UPDATE Matchbot\Domain\Donation donation
            SET donation.salesforcePushStatus = :status
            WHERE donation.uuid = :uuid
            DQL
        );
        $query->setParameter('status', SalesforceWriteProxy::PUSH_STATUS_PENDING_UPDATE);
        $query->setParameter('uuid', $donationUUID);
        $query->execute();
    }

    #[\Override]
    public function push(DonationUpserted $changeMessage): void
    {
        try {
            $salesforceDonationId = $this->getClient()->createOrUpdate($changeMessage);
            if ($salesforceDonationId === null) {
                $this->logInfo(
                    "Could not push donation {$changeMessage->uuid} to Salesforce, not ready for push",
                );
            }
        } catch (NotFoundException) {
            // Thrown only for *sandbox* 404s -> quietly stop trying to push donation to a removed campaign.
            $this->logInfo(
                "Marking 404 campaign Salesforce donation {$changeMessage->uuid} as complete; " .
                'will not try to push again.'
            );
            $this->setSalesforceFieldsWithRetry($changeMessage, null);

            return;
        } catch (BadRequestException $exception) {
            if (Environment::current() !== Environment::Production) {
                $snapshot = json_encode($changeMessage->jsonSnapshot);
            } else {
                $snapshot = 'no-snapshot-in-prod';
            }

            // We throw a BadRequestException in one SF 500 case, so the actual HTTP code
            // upstream could be either 400 or 500.
            $this->logError(
                "Pushing Salesforce donation {$changeMessage->uuid} got 400/500: {$exception->getMessage()}, donation snapshot was: $snapshot"
            );

            return;
        } catch (BadResponseException | ConnectException $exception) {
            $this->setSalesforceRePushNeeded($changeMessage->uuid);
            $exceptionClass = get_class($exception);
            $this->logError(
                "Pushing Salesforce donation {$changeMessage->uuid} got $exceptionClass: {$exception->getMessage()}"
            );

            return;
        }

        $this->setSalesforceFieldsWithRetry($changeMessage, $salesforceDonationId);
    }

    #[\Override]
    public function findAllCompleteForCustomer(StripeCustomerId $stripeCustomerId): array
    {
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT donation from Matchbot\Domain\Donation donation
            WHERE donation.pspCustomerId = :pspCustomerId
            AND donation.donationStatus IN (:succcessStatus)
            ORDER BY donation.createdAt DESC
        DQL
        );

        $query->setParameter('pspCustomerId', $stripeCustomerId->stripeCustomerId);
        $query->setParameter('succcessStatus', DonationStatus::SUCCESS_STATUSES);

        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }

    /**
     * We only set Payment Intent on the day of the payment due to stripe limitations
     *
     */
    #[\Override]
    public function findDonationsToSetPaymentIntent(\DateTimeImmutable $atDateTime, int $maxBatchSize): array
    {
        $preAuthorized = DonationStatus::PreAuthorized->value;
        $active = MandateStatus::Active->value;
        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT donation from Matchbot\Domain\Donation donation JOIN donation.mandate mandate
            WHERE donation.donationStatus = '$preAuthorized'
            AND donation.transactionId is null
            AND donation.preAuthorizationDate <= :atDateTime
            AND mandate.status = '$active'  
        DQL
        );
        $query->setParameter('atDateTime', $atDateTime);
        $query->setMaxResults($maxBatchSize);

        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }

    #[\Override]
    public function findPreAuthorizedDonationsReadyToConfirm(\DateTimeImmutable $atDateTime, int $limit): array
    {
        $preAuthorized = DonationStatus::PreAuthorized->value;
        $active = MandateStatus::Active->value;

        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT donation from Matchbot\Domain\Donation donation JOIN donation.mandate mandate
            WHERE donation.donationStatus = '$preAuthorized'
            AND mandate.status = '$active'
            AND donation.preAuthorizationDate <= :atDateTime
        DQL
        );

        $query->setParameter('atDateTime', $atDateTime);
        $query->setMaxResults($limit);

        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }

    #[\Override]
    public function maxSequenceNumberForMandate(int $mandateId): ?DonationSequenceNumber
    {
        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT MAX(d.mandateSequenceNumber) from MatchBot\Domain\Donation d join d.mandate m
            WHERE m.id = :mandate_id 
        DQL
        );

        $query->setParameter('mandate_id', $mandateId);

        $number = $query->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
        \assert(is_int($number) || is_null($number));

        if ($number === null) {
            return null;
        }

        return DonationSequenceNumber::of($number);
    }

    #[\Override]
    public function findStaleDonationFundsTips(\DateTimeImmutable $atDateTime, \DateInterval $cancelationDelay): array
    {
        $pending = DonationStatus::Pending->value;

        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT donation.uuid from Matchbot\Domain\Donation donation join donation.campaign c
            WHERE donation.donationStatus = '$pending'
            AND donation.paymentMethodType = 'customer_balance'
            AND c.name = 'Big Give General Donations'
            AND donation.createdAt < :latestCreationDate
        DQL
        );

        $query->setParameter('latestCreationDate', $atDateTime->sub($cancelationDelay));
        $query->setMaxResults(100);

        /** @var list<array{uuid: UuidInterface}> $result */
        $result = $query->getResult();

        return array_map(static fn(array $array): UuidInterface => $array['uuid'], $result);
    }

    #[\Override]
    public function findPendingByDonorCampaignAndMethod(
        string $donorStripeId,
        Salesforce18Id $campaignId,
        PaymentMethodType $paymentMethodType,
    ): array {
        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT donation.uuid from Matchbot\Domain\Donation donation
            INNER JOIN donation.campaign campaign
            WHERE donation.donationStatus = :donationStatus
            AND donation.pspCustomerId = :donorStripeId
            AND campaign.salesforceId = :campaignId
            AND donation.paymentMethodType = :paymentMethodType
        DQL);
        $query->setParameter('donationStatus', DonationStatus::Pending->value);
        $query->setParameter('donorStripeId', $donorStripeId);
        $query->setParameter('campaignId', $campaignId->value);
        $query->setParameter('paymentMethodType', $paymentMethodType->value);

        /** @var list<array{uuid: UuidInterface}> $result */
        $result = $query->getResult();

        return array_map(static fn(array $row) => $row['uuid'], $result);
    }

    #[\Override]
    public function findAndLockOneByUUID(UuidInterface $donationId): ?Donation
    {
        return $this->findAndLockOneBy(['uuid' => $donationId->toString()]);
    }

    #[\Override]
    public function findPendingAndPreAuthedForMandate(UuidInterface $mandateId): array
    {
        $pending = DonationStatus::Pending->value;
        $preAuthorized = DonationStatus::PreAuthorized->value;

        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT d from Matchbot\Domain\Donation d JOIN d.mandate m
            WHERE m.uuid = :mandate_id
            AND d.donationStatus IN ('$preAuthorized', '$pending')
        DQL
        );

        $query->setParameter('mandate_id', $mandateId);

        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }

    #[\Override]
    public function findAllForMandate(UuidInterface $mandateId): array
    {
        $query = $this->getEntityManager()->createQuery(<<<DQL
            SELECT d from Matchbot\Domain\Donation d JOIN d.mandate m
            WHERE m.uuid = :mandate_id
        DQL
        );

        $query->setParameter('mandate_id', $mandateId);

        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }

    #[\Override]
    public function findOneByUUID(UuidInterface $donationUUID): ?Donation
    {
        return $this->findOneBy(['uuid' => $donationUUID]);
    }

    #[\Override]
    public function findAllByPayoutId(string $payoutId): array
    {
        return $this->findBy(['stripePayoutId' => $payoutId]);
    }

    #[\Override]
    public function countCompleteDonationsToCampaign(Campaign $campaign): int
    {
        $campaignId = $campaign->getId();

        if ($campaignId === null) {
            return 0;
        }

        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT COUNT(d.id)
            FROM MatchBot\Domain\Donation d
            WHERE d.campaign = :campaign_id
            AND d.donationStatus IN (:collectedStatuses)
        DQL
        );

        $query->setHint(
            Query::HINT_CUSTOM_OUTPUT_WALKER,
            ForceDonationPerCampaignIndexWalker::class
        );

        $query->setParameter('campaign_id', $campaignId);

        $query->setParameter('collectedStatuses', DonationStatus::SUCCESS_STATUSES);

        $count = (int)$query->getSingleScalarResult();

        \assert($count >= 0);

        return $count;
    }

    #[\Override]
    public function findOverMatchedDonations(): array
    {
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT d FROM MatchBot\Domain\Donation d
            LEFT JOIN d.fundingWithdrawals fw
            GROUP BY d.id
            HAVING (SUM(CASE WHEN fw.releasedAt is null THEN fw.amount ELSE 0 END )) > d.amount
        DQL
        );

        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }

    #[\Override]
    public function potentiallyCompetingDonations(Donation $donation): array
    {
        $query = $this->getEntityManager()->createQuery(<<<'DQL'
            SELECT d FROM MatchBot\Domain\Donation d
            WHERE d.createdAt > :earliest 
           AND d.createdAt < :latest
           and d.campaign = :campaign
           AND d.donationStatus IN (:incompleteStatuses)
        DQL
        );

        $query->setParameter('earliest', $donation->getCreatedDateImmutable()->sub(Donation::expiryInterval()));
        $query->setParameter('latest', $donation->getCreatedDateImmutable());
        $query->setParameter('incompleteStatuses', [DonationStatus::Pending, DonationStatus::PreAuthorized, DonationStatus::Cancelled, DonationStatus::Refunded]);
        $query->setParameter('campaign', $donation->getCampaign());


        /** @var list<Donation> $result */
        $result = $query->getResult();
        return $result;
    }
}