Skip to content

Open-source event management and ticket selling platform — perfect for concerts, conferences, and everything in between 🎟️ If you find this project helpful, please consider giving us a star ⭐️

21.9 KB
Raw Blame
<?php

declare(strict_types=1);

namespace HiEvents\Repository\Eloquent;

use Carbon\CarbonInterface;
use HiEvents\DomainObjects\AccountLifecycleNudgeDomainObject;
use HiEvents\DomainObjects\Enums\LifecycleNudgeType;
use HiEvents\DomainObjects\Enums\PaymentProviders;
use HiEvents\DomainObjects\Status\AccountDeletionRequestStatus;
use HiEvents\DomainObjects\Status\EventOccurrenceStatus;
use HiEvents\DomainObjects\Status\EventStatus;
use HiEvents\DomainObjects\Status\OrderPaymentStatus;
use HiEvents\DomainObjects\Status\OrderRefundStatus;
use HiEvents\DomainObjects\Status\OrderStatus;
use HiEvents\Models\AccountLifecycleNudge;
use HiEvents\Repository\DTO\LifecycleEmail\AccountOwnerNudgeCandidateDTO;
use HiEvents\Repository\DTO\LifecycleEmail\EventNudgeCandidateDTO;
use HiEvents\Repository\DTO\LifecycleEmail\LapsedPayerNudgeCandidateDTO;
use HiEvents\Repository\DTO\LifecycleEmail\LifecycleNudgeSendCountsDTO;
use HiEvents\Repository\DTO\LifecycleEmail\StripeOnboardingNudgeCandidateDTO;
use HiEvents\Repository\Interfaces\AccountLifecycleNudgeRepositoryInterface;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;

/**
 * @extends BaseRepository<AccountLifecycleNudgeDomainObject>
 */
class AccountLifecycleNudgeRepository extends BaseRepository implements AccountLifecycleNudgeRepositoryInterface
{
    private const QUIET_PERIOD_DAYS = 3;

    protected function getModel(): string
    {
        return AccountLifecycleNudge::class;
    }

    public function getDomainObject(): string
    {
        return AccountLifecycleNudgeDomainObject::class;
    }

    public function findNoEventCandidates(
        CarbonInterface $createdAfter,
        CarbonInterface $createdBefore,
        int $limit,
    ): Collection {
        $rows = $this->db->select(<<<'SQL'
            SELECT DISTINCT ON (a.id)
                a.id AS account_id, u.id AS user_id, u.email, u.first_name
            FROM accounts a
            JOIN account_users au ON au.account_id = a.id AND au.is_account_owner = true
                                  AND au.status = 'ACTIVE' AND au.deleted_at IS NULL
            JOIN users u          ON u.id = au.user_id AND u.deleted_at IS NULL
                                  AND u.lifecycle_emails_unsubscribed_at IS NULL
            WHERE a.deleted_at IS NULL
              AND a.account_verified_at IS NOT NULL
              AND a.suspended_at IS NULL
              AND a.created_at >= ?
              AND a.created_at <= ?
              AND NOT EXISTS (SELECT 1 FROM events e WHERE e.account_id = a.id)
              AND NOT EXISTS (SELECT 1 FROM account_deletion_requests adr
                              WHERE adr.account_id = a.id AND adr.status = ?)
              AND NOT EXISTS (SELECT 1 FROM account_lifecycle_nudges n
                              WHERE n.account_id = a.id
                                AND (n.nudge_type = ? OR n.created_at > ?))
            ORDER BY a.id
            LIMIT ?
        SQL, [
            $createdAfter->toDateTimeString(),
            $createdBefore->toDateTimeString(),
            AccountDeletionRequestStatus::REQUESTED->name,
            LifecycleNudgeType::NO_EVENT_CREATED->name,
            $this->quietPeriodStart(),
            $limit,
        ]);

        return collect($rows)->map(static fn (object $row) => new AccountOwnerNudgeCandidateDTO(
            accountId: (int) $row->account_id,
            userId: (int) $row->user_id,
            email: $row->email,
            firstName: $row->first_name,
        ));
    }

    public function findStripeOnboardingCandidates(
        CarbonInterface $triggeredAfter,
        CarbonInterface $triggeredBefore,
        array $excludedTimezones,
        int $limit,
    ): Collection {
        $rows = $this->db->select(<<<'SQL'
            WITH first_paid_product AS (
                SELECT e.organizer_id, MIN(p.created_at) AS created_at
                FROM products p
                JOIN events e               ON e.id = p.event_id AND e.deleted_at IS NULL
                JOIN product_prices pp      ON pp.product_id = p.id AND pp.deleted_at IS NULL AND pp.price > 0
                LEFT JOIN event_settings es ON es.event_id = e.id
                WHERE p.deleted_at IS NULL
                  AND e.organizer_id IS NOT NULL
                  AND (es.payment_providers IS NULL OR es.payment_providers::jsonb @> ?::jsonb)
                GROUP BY e.organizer_id
            ),
            stripe_started AS (
                SELECT organizer_id, MIN(created_at) AS created_at
                FROM organizer_stripe_platforms
                WHERE deleted_at IS NULL AND stripe_account_id IS NOT NULL
                GROUP BY organizer_id
            )
            SELECT DISTINCT ON (a.id)
                a.id AS account_id, o.id AS organizer_id, o.name AS organizer_name,
                u.id AS user_id, u.email, u.first_name
            FROM first_paid_product fpp
            JOIN organizers o            ON o.id = fpp.organizer_id AND o.deleted_at IS NULL
            JOIN accounts a              ON a.id = o.account_id AND a.deleted_at IS NULL
                                         AND a.account_verified_at IS NOT NULL
                                         AND a.suspended_at IS NULL
            JOIN account_users au        ON au.account_id = a.id AND au.is_account_owner = true
                                         AND au.status = 'ACTIVE' AND au.deleted_at IS NULL
            JOIN users u                 ON u.id = au.user_id AND u.deleted_at IS NULL
                                         AND u.lifecycle_emails_unsubscribed_at IS NULL
            LEFT JOIN stripe_started ss  ON ss.organizer_id = o.id
            WHERE GREATEST(ss.created_at, fpp.created_at) >= ?
              AND GREATEST(ss.created_at, fpp.created_at) <= ?
              AND (a.timezone IS NULL OR a.timezone NOT IN (SELECT jsonb_array_elements_text(?::jsonb)))
              AND NOT EXISTS (SELECT 1 FROM organizer_stripe_platforms c
                              WHERE c.organizer_id = o.id AND c.deleted_at IS NULL
                                AND c.stripe_setup_completed_at IS NOT NULL)
              AND NOT EXISTS (SELECT 1 FROM account_deletion_requests adr
                              WHERE adr.account_id = a.id AND adr.status = ?)
              AND NOT EXISTS (SELECT 1 FROM account_lifecycle_nudges n
                              WHERE n.account_id = a.id
                                AND (n.nudge_type = ? OR n.created_at > ?))
            ORDER BY a.id, o.id
            LIMIT ?
        SQL, [
            json_encode([PaymentProviders::STRIPE->name]),
            $triggeredAfter->toDateTimeString(),
            $triggeredBefore->toDateTimeString(),
            json_encode(array_values($excludedTimezones)),
            AccountDeletionRequestStatus::REQUESTED->name,
            LifecycleNudgeType::STRIPE_ONBOARDING_INCOMPLETE->name,
            $this->quietPeriodStart(),
            $limit,
        ]);

        return collect($rows)->map(static fn (object $row) => new StripeOnboardingNudgeCandidateDTO(
            accountId: (int) $row->account_id,
            userId: (int) $row->user_id,
            organizerId: (int) $row->organizer_id,
            organizerName: $row->organizer_name,
            email: $row->email,
            firstName: $row->first_name,
        ));
    }

    public function findDraftEventCandidates(
        CarbonInterface $createdAfter,
        CarbonInterface $createdBefore,
        int $limit,
    ): Collection {
        $rows = $this->db->select(<<<'SQL'
            SELECT DISTINCT ON (a.id)
                a.id AS account_id, e.id AS event_id, e.title,
                u.id AS user_id, u.email, u.first_name
            FROM events e
            JOIN accounts a       ON a.id = e.account_id AND a.deleted_at IS NULL
                                  AND a.account_verified_at IS NOT NULL
                                  AND a.suspended_at IS NULL
            JOIN account_users au ON au.account_id = a.id AND au.is_account_owner = true
                                  AND au.status = 'ACTIVE' AND au.deleted_at IS NULL
            JOIN users u          ON u.id = au.user_id AND u.deleted_at IS NULL
                                  AND u.lifecycle_emails_unsubscribed_at IS NULL
            WHERE e.deleted_at IS NULL
              AND e.status = ?
              AND e.created_at >= ?
              AND e.created_at <= ?
              AND NOT EXISTS (SELECT 1 FROM events l WHERE l.account_id = a.id
                                AND l.deleted_at IS NULL AND l.status = ?)
              AND NOT EXISTS (SELECT 1 FROM account_deletion_requests adr
                              WHERE adr.account_id = a.id AND adr.status = ?)
              AND NOT EXISTS (SELECT 1 FROM account_lifecycle_nudges n
                              WHERE n.account_id = a.id
                                AND (n.nudge_type = ? OR n.created_at > ?))
            ORDER BY a.id, e.created_at
            LIMIT ?
        SQL, [
            EventStatus::DRAFT->name,
            $createdAfter->toDateTimeString(),
            $createdBefore->toDateTimeString(),
            EventStatus::LIVE->name,
            AccountDeletionRequestStatus::REQUESTED->name,
            LifecycleNudgeType::EVENT_DRAFT_STALE->name,
            $this->quietPeriodStart(),
            $limit,
        ]);

        return collect($rows)->map(static fn (object $row) => new EventNudgeCandidateDTO(
            accountId: (int) $row->account_id,
            userId: (int) $row->user_id,
            eventId: (int) $row->event_id,
            eventTitle: $row->title,
            email: $row->email,
            firstName: $row->first_name,
        ));
    }

    public function findLiveEventWithoutSalesCandidates(
        CarbonInterface $readyAfter,
        CarbonInterface $readyBefore,
        CarbonInterface $upcomingAfter,
        int $limit,
    ): Collection {
        $rows = $this->db->select(<<<'SQL'
            SELECT DISTINCT ON (a.id)
                a.id AS account_id, e.id AS event_id, e.title,
                u.id AS user_id, u.email, u.first_name
            FROM events e
            JOIN accounts a       ON a.id = e.account_id AND a.deleted_at IS NULL
                                  AND a.account_verified_at IS NOT NULL
                                  AND a.suspended_at IS NULL
            JOIN account_users au ON au.account_id = a.id AND au.is_account_owner = true
                                  AND au.status = 'ACTIVE' AND au.deleted_at IS NULL
            JOIN users u          ON u.id = au.user_id AND u.deleted_at IS NULL
                                  AND u.lifecycle_emails_unsubscribed_at IS NULL
            JOIN LATERAL (
                SELECT MIN(osp.stripe_setup_completed_at) AS completed_at
                FROM organizer_stripe_platforms osp
                WHERE osp.organizer_id = e.organizer_id AND osp.deleted_at IS NULL
            ) stripe ON stripe.completed_at IS NOT NULL
            WHERE e.deleted_at IS NULL
              AND e.status = ?
              AND e.created_at >= ?
              AND GREATEST(e.updated_at, stripe.completed_at) >= ?
              AND GREATEST(e.updated_at, stripe.completed_at) <= ?
              AND EXISTS (SELECT 1 FROM products p
                          JOIN product_prices pp ON pp.product_id = p.id AND pp.deleted_at IS NULL
                          WHERE p.event_id = e.id AND p.deleted_at IS NULL AND pp.price > 0)
              AND EXISTS (SELECT 1 FROM event_occurrences eo
                          WHERE eo.event_id = e.id AND eo.deleted_at IS NULL
                            AND eo.status = ? AND eo.start_date > ?)
              AND NOT EXISTS (SELECT 1 FROM orders o
                              JOIN events oe ON oe.id = o.event_id
                              WHERE oe.account_id = a.id AND o.status IN (?, ?))
              AND NOT EXISTS (SELECT 1 FROM account_deletion_requests adr
                              WHERE adr.account_id = a.id AND adr.status = ?)
              AND NOT EXISTS (SELECT 1 FROM account_lifecycle_nudges n
                              WHERE n.account_id = a.id
                                AND (n.nudge_type = ? OR n.created_at > ?))
            ORDER BY a.id, e.created_at
            LIMIT ?
        SQL, [
            EventStatus::LIVE->name,
            $readyAfter->toDateTimeString(),
            $readyAfter->toDateTimeString(),
            $readyBefore->toDateTimeString(),
            EventOccurrenceStatus::ACTIVE->name,
            $upcomingAfter->toDateTimeString(),
            OrderStatus::COMPLETED->name,
            OrderStatus::AWAITING_OFFLINE_PAYMENT->name,
            AccountDeletionRequestStatus::REQUESTED->name,
            LifecycleNudgeType::LIVE_EVENT_NO_SALES->name,
            $this->quietPeriodStart(),
            $limit,
        ]);

        return collect($rows)->map(static fn (object $row) => new EventNudgeCandidateDTO(
            accountId: (int) $row->account_id,
            userId: (int) $row->user_id,
            eventId: (int) $row->event_id,
            eventTitle: $row->title,
            email: $row->email,
            firstName: $row->first_name,
        ));
    }

    public function findLapsedPayerCandidates(
        CarbonInterface $lapsedAfter,
        CarbonInterface $lapsedBefore,
        CarbonInterface $upcomingAfter,
        int $limit,
    ): Collection {
        $rows = $this->db->select(<<<'SQL'
            WITH paid_sales AS (
                SELECT e.account_id, e.id AS event_id, e.organizer_id, e.title, e.status AS event_status,
                       eo.status AS occurrence_status, COALESCE(eo.end_date, eo.start_date) AS ended_at,
                       LOWER(o.email) AS buyer_email
                FROM orders o
                JOIN events e             ON e.id = o.event_id AND e.deleted_at IS NULL
                JOIN organizers og        ON og.id = e.organizer_id AND og.deleted_at IS NULL
                JOIN order_items oi       ON oi.order_id = o.id AND oi.deleted_at IS NULL
                JOIN event_occurrences eo ON eo.id = oi.event_occurrence_id AND eo.deleted_at IS NULL
                WHERE o.status = :completed
                  AND o.payment_status = :payment_received
                  AND o.refund_status IS DISTINCT FROM :refunded
                  AND o.deleted_at IS NULL
                  AND o.is_manually_created = false
                  AND o.payment_provider IS NOT NULL
                  AND o.total_gross - COALESCE(o.total_refunded, 0) > 0
                  AND COALESCE(eo.end_date, eo.start_date) >= :lapsed_after
                  AND NOT EXISTS (SELECT 1 FROM account_users bau
                                  JOIN users bu ON bu.id = bau.user_id
                                  WHERE bau.account_id = e.account_id AND LOWER(bu.email) = LOWER(o.email))
                  AND NOT EXISTS (SELECT 1 FROM organizers bog
                                  WHERE bog.account_id = e.account_id AND LOWER(bog.email) = LOWER(o.email))
            ),
            last_sold_date AS (
                SELECT DISTINCT ON (account_id) *
                FROM paid_sales
                WHERE account_id IN (SELECT account_id FROM paid_sales
                                     GROUP BY account_id HAVING COUNT(DISTINCT buyer_email) >= 2)
                ORDER BY account_id, ended_at DESC
            )
            SELECT * FROM (
                SELECT DISTINCT ON (a.id)
                    a.id AS account_id, lsd.event_id, lsd.organizer_id, lsd.title, lsd.event_status, lsd.ended_at,
                    u.id AS user_id, u.email, u.first_name
                FROM last_sold_date lsd
                JOIN accounts a       ON a.id = lsd.account_id AND a.deleted_at IS NULL
                                      AND a.account_verified_at IS NOT NULL
                                      AND a.suspended_at IS NULL
                JOIN account_users au ON au.account_id = a.id AND au.is_account_owner = true
                                      AND au.status = 'ACTIVE' AND au.deleted_at IS NULL
                JOIN users u          ON u.id = au.user_id AND u.deleted_at IS NULL
                                      AND u.lifecycle_emails_unsubscribed_at IS NULL
                WHERE lsd.ended_at <= :lapsed_before
                  AND lsd.event_status <> :pending_review
                  AND lsd.occurrence_status = :active
                  AND NOT EXISTS (SELECT 1 FROM events ne
                                  WHERE ne.account_id = a.id AND ne.created_at > :created_since)
                  AND NOT EXISTS (SELECT 1 FROM event_occurrences reo
                                  JOIN events re ON re.id = reo.event_id AND re.deleted_at IS NULL
                                  WHERE re.account_id = a.id AND reo.deleted_at IS NULL
                                    AND reo.status = :recent_active
                                    AND COALESCE(reo.end_date, reo.start_date) > :ran_since)
                  AND NOT EXISTS (SELECT 1 FROM event_occurrences ueo
                                  JOIN events ue ON ue.id = ueo.event_id AND ue.deleted_at IS NULL
                                  WHERE ue.account_id = a.id AND ueo.deleted_at IS NULL
                                    AND COALESCE(ueo.end_date, ueo.start_date) >= :upcoming_after)
                  AND NOT EXISTS (SELECT 1 FROM account_deletion_requests adr
                                  WHERE adr.account_id = a.id AND adr.status = :deletion_requested)
                  AND NOT EXISTS (SELECT 1 FROM account_lifecycle_nudges n
                                  WHERE n.account_id = a.id
                                    AND (n.nudge_type = :nudge_type OR n.created_at > :quiet_since))
                  AND NOT EXISTS (SELECT 1 FROM account_lifecycle_nudges sn
                                  JOIN account_users sau ON sau.account_id = sn.account_id
                                                         AND sau.is_account_owner = true
                                  WHERE sau.user_id = u.id AND sn.nudge_type = :owner_nudge_type)
                ORDER BY a.id
            ) candidates
            ORDER BY ended_at DESC
            LIMIT :limit
        SQL, [
            'completed' => OrderStatus::COMPLETED->name,
            'payment_received' => OrderPaymentStatus::PAYMENT_RECEIVED->name,
            'refunded' => OrderRefundStatus::REFUNDED->name,
            'lapsed_after' => $lapsedAfter->toDateTimeString(),
            'lapsed_before' => $lapsedBefore->toDateTimeString(),
            'pending_review' => EventStatus::PENDING_MANUAL_REVIEW->name,
            'active' => EventOccurrenceStatus::ACTIVE->name,
            'created_since' => $lapsedBefore->toDateTimeString(),
            'recent_active' => EventOccurrenceStatus::ACTIVE->name,
            'ran_since' => $lapsedBefore->toDateTimeString(),
            'upcoming_after' => $upcomingAfter->toDateTimeString(),
            'deletion_requested' => AccountDeletionRequestStatus::REQUESTED->name,
            'nudge_type' => LifecycleNudgeType::LAPSED_PAYER->name,
            'quiet_since' => $this->quietPeriodStart(),
            'owner_nudge_type' => LifecycleNudgeType::LAPSED_PAYER->name,
            'limit' => $limit,
        ]);

        return collect($rows)->map(static fn (object $row) => new LapsedPayerNudgeCandidateDTO(
            accountId: (int) $row->account_id,
            userId: (int) $row->user_id,
            eventId: (int) $row->event_id,
            organizerId: (int) $row->organizer_id,
            eventTitle: $row->title,
            isEventArchived: $row->event_status === EventStatus::ARCHIVED->name,
            lastSoldDateEndedAt: (string) $row->ended_at,
            email: $row->email,
            firstName: $row->first_name,
        ));
    }

    public function claim(int $accountId, LifecycleNudgeType $nudgeType, ?int $eventId = null): bool
    {
        return $this->db->table('account_lifecycle_nudges')->insertOrIgnore([
            'account_id' => $accountId,
            'nudge_type' => $nudgeType->name,
            'event_id' => $eventId,
            'created_at' => now(),
            'updated_at' => now(),
        ]) === 1;
    }

    public function release(int $accountId, LifecycleNudgeType $nudgeType): void
    {
        $this->deleteWhere([
            'account_id' => $accountId,
            'nudge_type' => $nudgeType->name,
        ]);
    }

    public function recordProviderMessageId(int $accountId, LifecycleNudgeType $nudgeType, string $providerMessageId): void
    {
        $this->updateWhere(
            ['provider_message_id' => $providerMessageId],
            ['account_id' => $accountId, 'nudge_type' => $nudgeType->name],
        );
    }

    public function countsByType(CarbonInterface $recentSince): array
    {
        return $this->runQuery(fn () => $this->db->table('account_lifecycle_nudges as n')
            ->leftJoin('email_deliveries as d', 'd.provider_message_id', '=', 'n.provider_message_id')
            ->groupBy('n.nudge_type')
            ->select('n.nudge_type')
            ->selectRaw('COUNT(DISTINCT n.id) AS total')
            ->selectRaw('COUNT(DISTINCT n.id) FILTER (WHERE n.created_at >= ?) AS recent', [$recentSince->toDateTimeString()])
            ->selectRaw('COUNT(DISTINCT n.id) FILTER (WHERE d.delivered_at IS NOT NULL) AS delivered')
            ->selectRaw('COUNT(DISTINCT n.id) FILTER (WHERE d.bounced_at IS NOT NULL) AS bounced')
            ->selectRaw('COUNT(DISTINCT n.id) FILTER (WHERE d.first_opened_at IS NOT NULL) AS opened')
            ->selectRaw('COUNT(DISTINCT n.id) FILTER (WHERE d.first_clicked_at IS NOT NULL) AS clicked')
            ->get()
            ->mapWithKeys(static fn ($row) => [
                $row->nudge_type => new LifecycleNudgeSendCountsDTO(
                    total: (int) $row->total,
                    lastThirtyDays: (int) $row->recent,
                    delivered: (int) $row->delivered,
                    bounced: (int) $row->bounced,
                    opened: (int) $row->opened,
                    clicked: (int) $row->clicked,
                ),
            ])
            ->all());
    }

    private function quietPeriodStart(): string
    {
        return Carbon::now()->subDays(self::QUIET_PERIOD_DAYS)->toDateTimeString();
    }
}