content: {-# LANGUAGE TypeOperators #-}
  
  module Share.Notifications.Queries
    ( recordEvent,
      expectEvent,
      listNotificationHubEntryPayloads,
      updateNotificationHubEntries,
      listEmailDeliveryMethods,
      listWebhooks,
      createEmailDeliveryMethod,
      createWebhookDeliveryMethod,
      deleteEmailDeliveryMethod,
      deleteWebhookDeliveryMethod,
      updateEmailDeliveryMethod,
      listNotificationSubscriptions,
      createNotificationSubscription,
      deleteNotificationSubscription,
      updateNotificationSubscription,
      expectNotificationSubscription,
      hydrateEventPayload,
      hasUnreadNotifications,
      updateWatchProjectSubscription,
      isUserSubscribedToWatchProject,
      listProjectWebhooks,
      webhooksForSubscription,
      expectProjectWebhook,
    )
  where
  
  import Control.Lens
  import Data.Aeson qualified as Aeson
  import Data.Foldable qualified as Foldable
  import Data.Ord (clamp)
  import Data.Set qualified as Set
  import Data.Set.NonEmpty (NESet)
  import Share.Contribution
  import Share.IDs
  import Share.Notifications.API (GetHubEntriesCursor)
  import Share.Notifications.Types
  import Share.Postgres
  import Share.Postgres qualified as PG
  import Share.Postgres.Contributions.Queries qualified as ContributionQ
  import Share.Postgres.Releases.Queries qualified as ReleasesQ
  import Share.Postgres.Tickets.Queries qualified as TicketQ
  import Share.Postgres.Users.Queries qualified as UsersQ
  import Share.Prelude
  import Share.Release (Release (..))
  import Share.Ticket
  import Share.Utils.API (Cursor (..), CursorDirection (..), Paged (..), guardPaged, pagedOn)
  import Share.Web.Share.DisplayInfo.Queries qualified as DisplayInfoQ
  import Share.Web.Share.DisplayInfo.Types (UnifiedDisplayInfo)
  
  recordEvent :: (QueryA m) => NewNotificationEvent -> m ()
  recordEvent (NotificationEvent {eventScope, eventData, eventResourceId, eventProjectId, eventActor}) = do
    execute_
      [sql|
        INSERT INTO notification_events (topic, scope_user_id, actor_user_id, resource_id, project_id, data)
        VALUES (#{eventTopic eventData}::notification_topic, #{eventScope}, #{eventActor}, #{eventResourceId}, #{eventProjectId}, #{eventData})
      |]
  
  expectEvent :: (QueryM m) => NotificationEventId -> m PGNotificationEvent
  expectEvent eventId = do
    queryExpect1Row @PGNotificationEvent
      [sql|
        SELECT id, occurred_at, scope_user_id, actor_user_id, resource_id, project_id, topic, data
          FROM notification_events
        WHERE id = #{eventId}
      |]
  
  listNotificationHubEntryPayloads :: UserId -> Maybe Int -> Maybe (Cursor GetHubEntriesCursor) -> Maybe (NESet NotificationStatus) -> Transaction e (Paged GetHubEntriesCursor (NotificationHubEntry UnifiedDisplayInfo HydratedEventPayload))
  listNotificationHubEntryPayloads notificationUserId mayLimit mayCursor statusFilter = do
    let limit = clamp (0, 100) . fromIntegral @Int @Int32 . fromMaybe 50 $ mayLimit
    let mkCursorFilter = \case
          Nothing -> mempty
          Just (Cursor (beforeTime, entryId) Previous) -> [PG.sql| AND (hub.created_at, hub.id) > (#{beforeTime}, #{entryId})|]
          Just (Cursor (afterTime, entryId) Next) -> [PG.sql| AND (hub.created_at, hub.id) < (#{afterTime}, #{entryId})|]
    dbNotifications <- query limit (mkCursorFilter mayCursor)
    hydratedPayloads <- PG.pipelined $ forOf (traversed . traversed) dbNotifications hydrateEventPayload
    results <- hydratedPayloads & DisplayInfoQ.unifiedDisplayInfoForUserOf (traversed . hubEntryUserInfo_)
    let paged@(Paged {prevCursor, nextCursor}) =
          results
            & pagedOn (\(NotificationHubEntry {hubEntryId, hubEntryCreatedAt}) -> (hubEntryCreatedAt, hubEntryId))
    hasPrevPage <- not . null <$> query 1 (mkCursorFilter prevCursor)
    hasNextPage <- not . null <$> query 1 (mkCursorFilter nextCursor)
    pure $ guardPaged hasPrevPage hasNextPage paged
    where
      statusFilterList :: Maybe [NotificationStatus]
      statusFilterList = Foldable.toList <$> statusFilter
      query ::
        (QueryA m) =>
        Int32 ->
        PG.Sql ->
        m [NotificationHubEntry UserId NotificationEventData]
      query limit cursorFilter =
        queryListRows @(NotificationHubEntry UserId NotificationEventData)
          [sql|
            SELECT hub.id, hub.status, hub.created_at, event.id, event.occurred_at, event.scope_user_id, event.actor_user_id, event.resource_id, event.project_id, event.topic, event.data
              FROM notification_hub_entries hub
              JOIN notification_events event ON hub.event_id = event.id
            WHERE hub.user_id = #{notificationUserId}
                  AND (#{statusFilterList} IS NULL OR hub.status = ANY(#{statusFilterList}::notification_status[]))
                  -- By default omit notifications that are from the user themself.
                  AND event.actor_user_id <> #{notificationUserId}
                  ^{cursorFilter}
            ORDER BY hub.created_at DESC
            LIMIT #{limit}
          |]
  
  hasUnreadNotifications :: UserId -> Transaction e Bool
  hasUnreadNotifications notificationUserId = do
    queryExpect1Col
      [sql|
        SELECT EXISTS(
          SELECT
            FROM notification_hub_entries hub
            JOIN notification_events event ON hub.event_id = event.id
          WHERE hub.user_id = #{notificationUserId}
                AND hub.status = #{Unread}::notification_status
                AND event.actor_user_id <> #{notificationUserId}
        )
      |]
  
  updateNotificationHubEntries :: (QueryA m) => NESet NotificationHubEntryId -> NotificationStatus -> m ()
  updateNotificationHubEntries hubEntryIds status = do
    execute_
      [sql|
        WITH to_update(notification_id) AS (
          SELECT * FROM ^{singleColumnTable $ Foldable.toList hubEntryIds}
        )
        UPDATE notification_hub_entries
        SET status = #{status}::notification_status
        WHERE id IN (SELECT notification_id FROM to_update)
      |]
  
  listEmailDeliveryMethods :: (QueryA m) => UserId -> Maybe NotificationSubscriptionId -> m [NotificationEmailDeliveryConfig]
  listEmailDeliveryMethods userId maySubscriptionId = do
    queryListRows
      [sql|
        SELECT ne.id, ne.email
          FROM notification_emails ne
        WHERE ne.subscriber_user_id = #{userId}
          AND (#{maySubscriptionId} IS NULL
                OR ne.subscription_id = #{maySubscriptionId}
              )
        ORDER BY ne.email
      |]
  
  listWebhooks :: (QueryA m) => UserId -> Maybe NotificationSubscriptionId -> m [NotificationWebhookId]
  listWebhooks userId maySubscriptionId = do
    queryListCol
      [sql|
        SELECT nw.id
          FROM notification_webhooks nw
        WHERE nw.subscriber_user_id = #{userId}
          AND (#{maySubscriptionId} IS NULL
                OR nw.subscription_id = #{maySubscriptionId}
              )
          ORDER BY nw.created_at
      |]
  
  createEmailDeliveryMethod :: UserId -> Email -> Transaction e NotificationEmailDeliveryMethodId
  createEmailDeliveryMethod userId email = do
    existingEmailDeliveryMethodId <-
      query1Col
        [sql|
        SELECT id
          FROM notification_emails
        WHERE subscriber_user_id = #{userId}
              AND email = #{email}
      |]
    case existingEmailDeliveryMethodId of
      Just emailDeliveryMethodId -> pure emailDeliveryMethodId
      Nothing -> do
        queryExpect1Col
          [sql|
            INSERT INTO notification_emails (subscriber_user_id, email)
            VALUES (#{userId}, #{email})
            RETURNING id
          |]
  
  updateEmailDeliveryMethod :: UserId -> NotificationEmailDeliveryMethodId -> Email -> Transaction e ()
  updateEmailDeliveryMethod notificationUserId emailDeliveryMethodId email = do
    execute_
      [sql|
        UPDATE notification_emails
        SET email = #{email}
        WHERE id = #{emailDeliveryMethodId}
          AND subscriber_user_id = #{notificationUserId}
      |]
  
  deleteEmailDeliveryMethod :: UserId -> NotificationEmailDeliveryMethodId -> Transaction e ()
  deleteEmailDeliveryMethod notificationUserId emailDeliveryMethodId = do
    execute_
      [sql|
        DELETE FROM notification_emails
        WHERE id = #{emailDeliveryMethodId}
          AND subscriber_user_id = #{notificationUserId}
      |]
  
  createWebhookDeliveryMethod :: Text -> NotificationSubscriptionId -> Transaction e NotificationWebhookId
  createWebhookDeliveryMethod name subscriptionId = do
    queryExpect1Col
      [sql|
            INSERT INTO notification_webhooks (name, subscription_id)
            VALUES (#{name}, #{subscriptionId})
            RETURNING id
          |]
  
  deleteWebhookDeliveryMethod :: SubscriptionOwner -> NotificationWebhookId -> Transaction e ()
  deleteWebhookDeliveryMethod owner webhookDeliveryMethodId = do
    let ownerFilter = case owner of
          UserSubscriptionOwner userId -> [sql| nw.subscriber_user_id = #{userId} |]
          ProjectSubscriptionOwner projectId -> [sql| nw.subscriber_project_id = #{projectId} |]
    execute_
      [sql|
        DELETE FROM notification_webhooks
          USING notification_subscriptions ns
        WHERE id = #{webhookDeliveryMethodId}
          AND ns.id = nw.subscription_id
          AND ^{ownerFilter}
      |]
  
  listNotificationSubscriptions :: UserId -> Transaction e [NotificationSubscription NotificationSubscriptionId]
  listNotificationSubscriptions subscriberUserId = do
    queryListRows
      [sql|
        SELECT
          ns.id,
          ns.scope_user_id,
          ns.scope_project_id,
          ns.subscriber_user_id,
          ns.subscriber_project_id,
          ns.topics,
          ns.topic_groups,
          ns.filter,
          ns.created_at,
          ns.updated_at
          FROM notification_subscriptions ns
        WHERE ns.subscriber_user_id = #{subscriberUserId}
        ORDER BY ns.created_at DESC
      |]
  
  createNotificationSubscription :: SubscriptionOwner -> UserId -> Maybe ProjectId -> Set NotificationTopic -> Set NotificationTopicGroup -> Maybe SubscriptionFilter -> Transaction e NotificationSubscriptionId
  createNotificationSubscription owner subscriptionScope subscriptionProjectId subscriptionTopics subscriptionTopicGroups subscriptionFilter = do
    let (subscriberUserId, subscriberProjectId) = case owner of
          ProjectSubscriptionOwner projectId -> (Nothing, Just projectId)
          UserSubscriptionOwner userId -> (Just userId, Nothing)
    queryExpect1Col
      [sql|
        INSERT INTO notification_subscriptions(subscriber_user_id, subscriber_project_id, scope_user_id, scope_project_id, topics, topic_groups, filter)
        VALUES (#{subscriberUserId}, #{subscriberProjectId}, #{subscriptionScope}, #{subscriptionProjectId}, #{Foldable.toList subscriptionTopics}::notification_topic[], #{Foldable.toList subscriptionTopicGroups}::notification_topic_group[], #{subscriptionFilter})
        RETURNING id
      |]
  
  deleteNotificationSubscription :: SubscriptionOwner -> NotificationSubscriptionId -> Transaction e ()
  deleteNotificationSubscription owner subscriptionId = do
    let ownerFilter = case owner of
          UserSubscriptionOwner subscriberUserId -> [sql| subscriber_user_id = #{subscriberUserId}|]
          ProjectSubscriptionOwner projectOwnerUserId -> [sql| subscriber_project_id = #{projectOwnerUserId} |]
    execute_
      [sql|
        DELETE FROM notification_subscriptions
        WHERE id = #{subscriptionId}
          AND ^{ownerFilter}
      |]
  
  updateNotificationSubscription :: SubscriptionOwner -> NotificationSubscriptionId -> Maybe (Set NotificationTopic) -> Maybe (Set NotificationTopicGroup) -> Maybe SubscriptionFilter -> Transaction e ()
  updateNotificationSubscription _owner _subscriptionId Nothing Nothing Nothing = pure ()
  updateNotificationSubscription owner subscriptionId subscriptionTopics subscriptionTopicGroups subscriptionFilter = do
    let ownerFilter = case owner of
          UserSubscriptionOwner subscriberUserId -> [sql| subscriber_user_id = #{subscriberUserId}|]
          ProjectSubscriptionOwner projectOwnerUserId -> [sql| subscriber_project_id = #{projectOwnerUserId} |]
    execute_
      [sql|
        UPDATE notification_subscriptions
        SET topics = COALESCE(#{Foldable.toList <$> subscriptionTopics}::notification_topic[], topics),
            topic_groups = COALESCE(#{Foldable.toList <$> subscriptionTopicGroups}::notification_topic_group[], topic_groups),
            filter = COALESCE(#{subscriptionFilter}, filter)
        WHERE id = #{subscriptionId}
          AND ^{ownerFilter}
      |]
  
  expectNotificationSubscription :: SubscriptionOwner -> NotificationSubscriptionId -> Transaction e (NotificationSubscription NotificationSubscriptionId)
  expectNotificationSubscription subscriptionOwner subscriptionId = do
    let ownerFilter = case subscriptionOwner of
          UserSubscriptionOwner subscriberUserId -> [sql| ns.subscriber_user_id = #{subscriberUserId}|]
          ProjectSubscriptionOwner projectOwnerUserId -> [sql| ns.subscriber_project_id = #{projectOwnerUserId} |]
    queryExpect1Row
      [sql|
        SELECT
          ns.id,
          ns.scope_user_id,
          ns.scope_project_id,
          ns.subscriber_user_id,
          ns.subscriber_project_id,
          ns.topics,
          ns.topic_groups,
          ns.filter,
          ns.created_at,
          ns.updated_at
          FROM notification_subscriptions ns
        WHERE ns.id = #{subscriptionId}
          AND ^{ownerFilter}
      |]
  
  -- | Events are complex, so for now we hydrate them one at a time using a simple traverse
  -- (preferably pipelined).
  --
  -- If need be we can write a batch job in plpgsql to hydrate them all at once.
  hydrateEventPayload :: forall m. (QueryA m) => NotificationEventData -> m HydratedEventPayload
  hydrateEventPayload = \case
    ProjectBranchUpdatedData
      (ProjectData {projectId})
      (BranchData {branchId}) -> do
        (\projectPayload mkBranchPayload -> HydratedProjectBranchUpdatedPayload projectPayload (mkBranchPayload projectPayload))
          <$> hydrateProjectPayload projectId
          <*> hydrateBranchPayload branchId
    ProjectContributionCreatedData
      (ProjectData {projectId})
      (ContributionData {contributionId, fromBranchId, toBranchId, contributorUserId}) -> do
        (\projectPayload mkContributionPayload -> HydratedProjectContributionCreatedPayload projectPayload (mkContributionPayload projectPayload))
          <$> hydrateProjectPayload projectId
          <*> hydrateContributionInfo contributionId fromBranchId toBranchId contributorUserId
    ProjectContributionStatusUpdatedData
      (ProjectData {projectId})
      (ContributionData {contributionId, fromBranchId, toBranchId, contributorUserId})
      (StatusUpdateData {oldStatus, newStatus}) -> do
        let statusPayload = StatusUpdatePayload {oldStatus, newStatus}
        (\projectPayload mkContributionPayload -> HydratedProjectContributionStatusUpdatedPayload projectPayload (mkContributionPayload projectPayload) statusPayload)
          <$> hydrateProjectPayload projectId
          <*> hydrateContributionInfo contributionId fromBranchId toBranchId contributorUserId
    ProjectContributionCommentData
      (ProjectData {projectId})
      (ContributionData {contributionId, fromBranchId, toBranchId, contributorUserId})
      (CommentData {commentId, commentAuthorUserId}) -> do
        (\projectPayload mkContributionPayload -> HydratedProjectContributionCommentPayload projectPayload (mkContributionPayload projectPayload))
          <$> hydrateProjectPayload projectId
          <*> hydrateContributionInfo contributionId fromBranchId toBranchId contributorUserId
          <*> hydrateCommentPayload commentId commentAuthorUserId
    ProjectTicketCreatedData
      (ProjectData {projectId})
      (TicketData {ticketId, ticketAuthorUserId}) -> do
        HydratedProjectTicketCreatedPayload
          <$> hydrateProjectPayload projectId
          <*> hydrateTicketInfo ticketId ticketAuthorUserId
    ProjectTicketStatusUpdatedData
      (ProjectData {projectId})
      (TicketData {ticketId, ticketAuthorUserId})
      (StatusUpdateData {oldStatus, newStatus}) -> do
        let statusPayload = StatusUpdatePayload {oldStatus, newStatus}
        HydratedProjectTicketStatusUpdatedPayload
          <$> hydrateProjectPayload projectId
          <*> hydrateTicketInfo ticketId ticketAuthorUserId
          <*> pure statusPayload
    ProjectTicketCommentData
      (ProjectData {projectId})
      (TicketData {ticketId, ticketAuthorUserId})
      (CommentData {commentId, commentAuthorUserId}) -> do
        HydratedProjectTicketCommentPayload
          <$> hydrateProjectPayload projectId
          <*> hydrateTicketInfo ticketId ticketAuthorUserId
          <*> hydrateCommentPayload commentId commentAuthorUserId
    ProjectReleaseCreatedData
      (ProjectData {projectId})
      (ReleaseData {releaseId}) -> do
        projectInfo <- hydrateProjectPayload projectId
        releasePayload <- hydrateReleasePayload releaseId
        pure $ HydratedProjectReleaseCreatedPayload projectInfo releasePayload
    where
      hydrateReleasePayload :: ReleaseId -> m ReleasePayload
      hydrateReleasePayload releaseId = do
        release <- ReleasesQ.releaseById releaseId
        pure $
          ReleasePayload
            { releaseId,
              releaseVersion = release.version,
              releaseCreatedAt = release.createdAt
            }
      hydrateTicketInfo :: TicketId -> UserId -> m TicketPayload
      hydrateTicketInfo ticketId authorUserId = do
        author <- UsersQ.userDisplayInfoOf id authorUserId
        ticket <- TicketQ.ticketById ticketId
        pure $
          TicketPayload
            { ticketId,
              ticketNumber = ticket.number,
              ticketTitle = ticket.title,
              ticketDescription = ticket.description,
              ticketStatus = ticket.status,
              ticketAuthor = author,
              ticketCreatedAt = ticket.createdAt
            }
      hydrateContributionInfo :: ContributionId -> BranchId -> BranchId -> UserId -> m (ProjectPayload -> ContributionPayload)
      hydrateContributionInfo contributionId fromBranchId toBranchId authorUserId = do
        author <- UsersQ.userDisplayInfoOf id authorUserId
        targetBranch <- hydrateBranchPayload fromBranchId
        sourceBranch <- hydrateBranchPayload toBranchId
        contribution <- ContributionQ.contributionById contributionId
        pure $ \projectInfo ->
          ContributionPayload
            { contributionId,
              contributionNumber = contribution.number,
              contributionTitle = contribution.title,
              contributionDescription = contribution.description,
              contributionStatus = contribution.status,
              contributionAuthor = author,
              contributionSourceBranch = sourceBranch projectInfo,
              contributionTargetBranch = targetBranch projectInfo,
              contributionCreatedAt = contribution.createdAt
            }
      hydrateBranchPayload ::
        BranchId ->
        -- We return a func so we can convince GHC this whole thing is Applicative
        m (ProjectPayload -> BranchPayload)
      hydrateBranchPayload branchId = do
        queryExpect1Row
          [sql|
            SELECT b.name, contributor.id, contributor.handle
            FROM project_branches b
            LEFT JOIN users contributor ON b.contributor_id = contributor.id
            WHERE b.id = #{branchId}
            |]
          <&> \( branchName,
                 branchContributorUserId,
                 branchContributorHandle
                 )
               projectInfo ->
              let branchShortHand = BranchShortHand {contributorHandle = branchContributorHandle, branchName}
                  projectBranchShortHand = ProjectBranchShortHand {userHandle = projectInfo.projectOwnerHandle, projectSlug = projectInfo.projectSlug, contributorHandle = branchContributorHandle, branchName}
               in BranchPayload
                    { branchId,
                      branchName,
                      branchShortHand,
                      projectBranchShortHand,
                      branchContributorUserId,
                      branchContributorHandle
                    }
      hydrateProjectPayload :: ProjectId -> m ProjectPayload
      hydrateProjectPayload projectId = do
        queryExpect1Row
          [sql|
            SELECT p.slug, owner.handle, p.owner_user_id
            FROM projects p
            JOIN users owner ON p.owner_user_id = owner.id
            WHERE p.id = #{projectId}
            |]
          <&> \( projectSlug,
                 projectOwnerHandle,
                 projectOwnerUserId
                 ) ->
              let projectShortHand = ProjectShortHand {userHandle = projectOwnerHandle, projectSlug}
               in ProjectPayload
                    { projectId,
                      projectSlug,
                      projectShortHand,
                      projectOwnerHandle,
                      projectOwnerUserId
                    }
      hydrateCommentPayload :: CommentId -> UserId -> m CommentPayload
      hydrateCommentPayload commentId commentAuthorUserId = do
        let construct (commentId, commentContent, commentCreatedAt) commentAuthor =
              CommentPayload
                { commentId,
                  commentContent,
                  commentCreatedAt,
                  commentAuthor
                }
        construct
          <$> ( queryExpect1Row
                  [sql|
                  SELECT cc.comment_id, cc.content, cc.created_at, cc.updated_at
                  FROM comment_content cc
                  JOIN users author ON cc.author_id = author.id
                  WHERE cc.comment_id = #{commentId}
                |]
              )
          <*> (UsersQ.userDisplayInfoOf id commentAuthorUserId)
  
  -- | Subscribe or unsubscribe to watching a project
  updateWatchProjectSubscription :: UserId -> ProjectId -> Bool -> Transaction e (Maybe NotificationSubscriptionId)
  updateWatchProjectSubscription userId projId shouldBeSubscribed = do
    let filter = SubscriptionFilter $ Aeson.object []
    existing <- isUserSubscribedToWatchProject userId projId
    case existing of
      Just existingId
        | not shouldBeSubscribed -> do
            execute_
              [sql|
                DELETE FROM notification_subscriptions
                WHERE id = #{existingId}
                  AND subscriber_user_id = #{userId}
              |]
            pure Nothing
        | otherwise -> pure (Just existingId)
      Nothing | shouldBeSubscribed -> do
        -- Create a new subscription
        projectOwnerUserId <-
          queryExpect1Col
            [sql|
            SELECT p.owner_user_id
              FROM projects p
            WHERE p.id = #{projId}
          |]
        Just <$> createNotificationSubscription (UserSubscriptionOwner userId) projectOwnerUserId (Just projId) mempty (Set.singleton WatchProject) (Just filter)
      _ -> pure Nothing
  
  isUserSubscribedToWatchProject :: UserId -> ProjectId -> Transaction e (Maybe NotificationSubscriptionId)
  isUserSubscribedToWatchProject userId projId = do
    let filter = SubscriptionFilter $ Aeson.object []
    query1Col @NotificationSubscriptionId
      [sql|
        SELECT ns.id FROM notification_subscriptions ns
                     JOIN projects p ON p.id = #{projId}
          WHERE ns.subscriber_user_id = #{userId}
            AND ns.scope_user_id = p.owner_user_id
            AND ns.topic_groups = ARRAY[#{WatchProject}::notification_topic_group]
            AND ns.scope_project_id = #{projId}
            AND ns.filter = #{filter}::jsonb
            LIMIT 1
      |]
  
  -- | We provide a wrapper layer on top of notification subscriptions and webhooks
  -- to make the frontend experience a bit more intuitive.
  listProjectWebhooks :: ProjectId -> PG.Transaction e [(NotificationWebhookId, Text, NotificationSubscription NotificationSubscriptionId)]
  listProjectWebhooks projectId = do
    PG.queryListRows @((NotificationWebhookId, Text) PG.:. NotificationSubscription NotificationSubscriptionId)
      [PG.sql|
        -- Only get one webhook per subscription, there _shouldn't_ be multiple webhooks per
        -- subscription if everything is working correctly.
        SELECT DISTINCT ON (ns.id)
          nw.id,
          nw.name,
          -- We ignore topic groups here for now and only allow the user to configure
          -- individual topic events.
          ns.id,
          ns.scope_user_id,
          ns.scope_project_id,
          ns.subscriber_user_id,
          ns.subscriber_project_id,
          ns.topics,
          ns.topic_groups,
          ns.filter,
          ns.created_at,
          ns.updated_at
        FROM notification_subscriptions ns
        JOIN notification_webhooks nw ON nw.subscription_id = ns.id
        WHERE ns.subscriber_project_id = #{projectId}
      |]
      <&> fmap (\((webhookId, webhookName) PG.:. subscription) -> (webhookId, webhookName, subscription))
  
  -- | Gets the (first) webhook associated with a project webhook notification.
  expectProjectWebhook :: ProjectId -> NotificationSubscriptionId -> PG.Transaction e (NotificationWebhookId, Text)
  expectProjectWebhook projectId subscriptionId = do
    PG.queryExpect1Row @(NotificationWebhookId, Text)
      [PG.sql|
        SELECT nw.id, nw.name
          FROM notification_webhooks nw
          JOIN notification_subscriptions ns ON nw.subscription_id = ns.id
        WHERE nw.subscription_id = #{subscriptionId}
          AND ns.subscriber_project_id = #{projectId}
        LIMIT 1
      |]
  
  webhooksForSubscription :: NotificationSubscriptionId -> Transaction e [NotificationWebhookId]
  webhooksForSubscription subscriptionId = do
    PG.queryListCol
      [PG.sql|
        SELECT nw.id
          FROM notification_webhooks nw
        WHERE nw.subscription_id = #{subscriptionId}
      |]
  