Queries.hs

{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE OverloadedRecordDot #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE RecordWildCards #-}
{-# LANGUAGE ScopedTypeVariables #-}
{-# LANGUAGE TypeOperators #-}

module Share.Postgres.Queries where

import Data.List qualified as List
import Data.Monoid (Sum (..))
import Data.Set qualified as Set
import Data.Text qualified as Text
import Data.Time (UTCTime)
import Share.Branch
import Share.Contribution
import Share.IDs
import Share.IDs qualified as IDs
import Share.Notifications.Queries qualified as NotifQ
import Share.Notifications.Types (BranchData (..), NotificationEvent (..), NotificationEventData (..), ProjectData (..))
import Share.OAuth.Types
import Share.Postgres (unrecoverableError)
import Share.Postgres qualified as PG
import Share.Postgres.IDs
import Share.Postgres.NameLookups.Types (NameLookupReceipt)
import Share.Postgres.Projects.Queries qualified as ProjectsQ
import Share.Postgres.Users.Queries qualified as UserQ
import Share.Prelude
import Share.Project
import Share.Release
import Share.Ticket (TicketStatus)
import Share.Ticket qualified as Ticket
import Share.User
import Share.Utils.API
import Share.Web.Authorization.Types (RolePermission (..))
import Share.Web.Errors (EntityMissing (EntityMissing), ErrorID (..))
import Share.Web.Share.Branches.Types (BranchKindFilter (..))
import Share.Web.Share.Projects.Types (ContributionStats (..), DownloadStats (..), FavData, ProjectOwner, TicketStats (..))
import Share.Web.Share.Releases.Types (ReleaseStatusFilter (..))
import Share.Web.Share.Types
import Unison.Util.List qualified as Utils
import Unison.Util.Monoid (intercalateMap)

projectById :: ProjectId -> PG.Transaction e (Maybe Project)
projectById projectId = do
  PG.query1Row
    [PG.sql|
        SELECT p.id, p.owner_user_id, p.slug, p.summary, p.tags, p.private, p.created_at, p.updated_at
        FROM projects p
        WHERE p.id = #{projectId}
      |]

expectProjectById :: ProjectId -> PG.Transaction e Project
expectProjectById projectId = do
  mayResult <- projectById projectId
  whenNothing mayResult $ unrecoverableError $ EntityMissing (ErrorID "project:missing") ("Project with id " <> IDs.toText projectId <> " not found")

-- | returns (project, favData, projectOwner, default branch, latest release version)
projectByIdWithMetadata :: Maybe UserId -> ProjectId -> PG.Transaction e (Maybe (Project, FavData, ProjectOwner, Maybe BranchName, Maybe ReleaseVersion))
projectByIdWithMetadata caller projectId = do
  PG.query1Row sql <&> fmap \(p PG.:. favData PG.:. projectOwner PG.:. (branchName, major, minor, patch)) -> (p, favData, projectOwner, branchName, releaseVersionFromInts major minor patch)
  where
    releaseVersionFromInts :: Maybe Int64 -> Maybe Int64 -> Maybe Int64 -> Maybe ReleaseVersion
    releaseVersionFromInts major minor patch =
      ReleaseVersion <$> major <*> minor <*> patch
    -- Select the project, also include the number of favs on the project and whether the
    -- caller has fav'd it, and the default branch and latest release version if they exist.
    sql =
      [PG.sql|
      SELECT p.id, p.owner_user_id, p.slug, p.summary, p.tags, p.private, p.created_at, p.updated_at,
        (SELECT COUNT(*) FROM project_favorites pf WHERE pf.project_id = p.id) AS fav_count,
        (SELECT EXISTS (SELECT FROM project_favorites pf WHERE pf.project_id = p.id AND pf.user_id = #{caller}) AS is_faved),
        owner.handle,
        owner.name,
        EXISTS (SELECT FROM org_members WHERE org_members.organization_user_id = owner.id),
        -- Check if the default branch exists
        (SELECT b.name FROM project_branches b WHERE b.project_id = p.id AND b.name = #{defaultBranchName} AND b.deleted_at IS NULL LIMIT 1),
        -- Get the latest release version
        release.major_version,
        release.minor_version,
        release.patch_version
      FROM projects p
        JOIN users owner ON owner.id = p.owner_user_id
        LEFT JOIN LATERAL (SELECT r.major_version, r.minor_version, r.patch_version FROM project_releases r WHERE r.project_id = p.id ORDER BY r.major_version DESC, r.minor_version DESC, r.patch_version DESC, r.id DESC LIMIT 1) release ON true
      WHERE p.id = #{projectId}
      |]

projectIDFromHandleAndSlug :: UserHandle -> ProjectSlug -> PG.Transaction e (Maybe ProjectId)
projectIDFromHandleAndSlug userHandle slug =
  PG.query1Col
    [PG.sql|
        SELECT projects.id
        FROM projects
        JOIN users on users.handle = #{userHandle}
        WHERE projects.owner_user_id = users.id
              AND projects.slug = #{slug}
      |]

projectIDFromUserIdAndSlug :: UserId -> ProjectSlug -> PG.Transaction e (Maybe ProjectId)
projectIDFromUserIdAndSlug userId slug =
  PG.query1Col
    [PG.sql|
        SELECT projects.id
        FROM projects
        WHERE projects.owner_user_id = #{userId}
              AND projects.slug = #{slug}
      |]

-- | Get the UserId of a project's owner.
projectOwner :: ProjectId -> PG.Transaction e (Maybe UserId)
projectOwner projectID = do
  PG.query1Col
    [PG.sql|
        SELECT owner_user_id
        FROM projects
        WHERE projects.id = #{projectID}
      |]

setProjectFav :: UserId -> ProjectId -> Bool -> PG.Transaction e ()
setProjectFav userId projectId fav = do
  case fav of
    True -> PG.execute_ favSql
    False -> PG.execute_ unfavSql
  where
    unfavSql =
      [PG.sql|
        DELETE FROM project_favorites
        WHERE project_id = #{projectId}
              AND user_id = #{userId}
      |]
    favSql =
      [PG.sql|
        INSERT INTO project_favorites (project_id, user_id)
        VALUES (#{projectId}, #{userId})
        ON CONFLICT DO NOTHING
      |]

-- | Perform a text search over projects and return Limit results in order of relevance.
--
-- The PG.queryListRows accepts strings as web search queries, see
-- https://www.postgresql.org/docs/current/textsearch-controls.html
searchProjects :: Maybe UserId -> Maybe UserId -> Query -> ProjectSearchKind -> Limit -> PG.Transaction e [(Project, UserHandle)]
searchProjects caller userIdFilter (Query query) psk limit = do
  let pskFilter = case psk of
        -- This is the default
        ProjectSearchKindWebSearch -> mempty
        -- Require that the query is a prefix of the slug
        ProjectSearchKindSlugPrefix -> [PG.sql| AND p.slug ILIKE (like_escape(#{query}) || '%') |]
        -- Require that the query is in the slug somewhere, but not necessarily at the start
        ProjectSearchKindSlugInfix -> [PG.sql| AND p.slug ILIKE ('%' || like_escape(#{query}) || '%') |]
  results <- case (userIdFilter, query) of
    (Nothing, "") -> pure []
    (Just userId, "") ->
      -- If we have a userId filter but no query, just return all the projects owned by that user
      -- which the caller has access to.
      PG.queryListRows @(Project PG.:. PG.Only UserHandle)
        [PG.sql|
        SELECT p.id, p.owner_user_id, p.slug, p.summary, p.tags, p.private, p.created_at, p.updated_at, owner.handle
          FROM projects p
            JOIN users owner ON p.owner_user_id = owner.id
          WHERE p.owner_user_id = #{userId}
            AND user_has_project_permission(#{caller}, p.id, #{ProjectView})
          ORDER BY p.created_at DESC
          LIMIT #{limit}
          |]
    _ -> do
      PG.queryListRows
        [PG.sql|
      SELECT p.id, p.owner_user_id, p.slug, p.summary, p.tags, p.private, p.created_at, p.updated_at, owner.handle
        FROM websearch_to_tsquery('english', #{query}) AS tokenquery, projects AS p
          JOIN users AS owner ON p.owner_user_id = owner.id
        WHERE (tokenquery @@ p.project_text_document OR p.slug ILIKE ('%' || like_escape(#{query}) || '%'))
        AND (#{userIdFilter} IS NULL OR p.owner_user_id = #{userIdFilter})
        AND user_has_project_permission(#{caller}, p.id, #{ProjectView})
        ^{pskFilter}
        ORDER BY
          p.slug = #{query} DESC,
          -- Prefer prefix matches
          p.slug ILIKE (like_escape(#{query}) || '%') DESC,
          -- Otherwise infix matches
          p.slug ILIKE ('%' || like_escape(#{query}) || '%') DESC,
          -- Prefer the caller's projects
          COALESCE(FALSE, p.owner_user_id = #{caller}) DESC,
          -- If a user has access to a private project, it's likely relevant
          p.private DESC,
          -- Prefer projects in the unison org
          COALESCE(FALSE, p.owner_user_id = (SELECT unison.id FROM users unison WHERE unison.handle = 'unison'))  DESC,
          -- Prefer projects in the catalog
          EXISTS(SELECT FROM project_categories pc WHERE pc.project_id = p.id) DESC,
          ts_rank_cd(p.project_text_document, tokenquery) DESC,
          -- Lastly sort by name, just so results are deterministic
          p.slug ASC
        LIMIT #{limit}
        |]
  pure (results <&> \(project PG.:. PG.Only handle) -> (project, handle))

-- | Returns the list of tours the user has completed.
getCompletedToursForUser :: (PG.QueryA m) => UserId -> m [TourId]
getCompletedToursForUser uid = do
  PG.queryListCol
    [PG.sql|
    SELECT tour_id
    FROM tours
    WHERE tours.user_id = #{uid}
      |]

-- | Marks a set of tours as completed.
completeToursForUser :: UserId -> NonEmpty TourId -> PG.Transaction e ()
completeToursForUser uid tours = do
  let toursToInsert = ((uid,) <$> toList tours)
  PG.execute_
    [PG.sql|
    INSERT INTO tours(user_id, tour_id)
      SELECT * FROM ^{PG.toTable toursToInsert}
      ON CONFLICT DO NOTHING
      |]

-- | Return all projects
allProjects :: PG.Transaction e [ProjectId]
allProjects = do
  PG.queryListCol
    [PG.sql|
        SELECT id FROM projects
      |]

-- | Returns all projects owned by that user.
-- Note that the user may still have access to other projects via orgs.
projectIdsOwnedByUser :: UserId -> Bool -> PG.Transaction e [ProjectId]
projectIdsOwnedByUser userId includePrivate = do
  PG.queryListCol
    [PG.sql|
        SELECT id FROM projects
          WHERE owner_user_id = #{userId}
            AND CASE
                WHEN #{includePrivate} THEN true
                ELSE private = false
                END
      |]

projectsOwnedByUser :: UserId -> Bool -> PG.Transaction e [Project]
projectsOwnedByUser userId includePrivate = do
  PG.queryListRows
    [PG.sql|
        SELECT id, owner_user_id, slug, summary, tags, private, created_at, updated_at FROM projects
          WHERE owner_user_id = #{userId}
            AND CASE
                WHEN #{includePrivate} THEN true
                ELSE private = false
                END
      |]

listProjectsByUserWithMetadata ::
  Maybe UserId ->
  UserId ->
  -- | (project, numFavs, isFavedByCaller)
  PG.Transaction e [(Project, FavData, ProjectOwner)]
listProjectsByUserWithMetadata callerUserId projectOwnerUserId = do
  unpackRows
    <$> PG.queryListRows @(Project PG.:. FavData PG.:. ProjectOwner)
      -- Selects all projects belonging to a user which are visible to the caller.
      -- Join in whether the caller has favorited the project,
      -- and how many favorites the project has in total.
      [PG.sql|
        SELECT
          p.id,
          p.owner_user_id,
          p.slug,
          p.summary,
          p.tags,
          p.private,
          p.created_at,
          p.updated_at,
          (SELECT COUNT(*) FROM project_favorites WHERE project_favorites.project_id = p.id) AS num_favs,
          (SELECT EXISTS(SELECT FROM project_favorites f WHERE f.project_id = p.id AND f.user_id = #{callerUserId}) AS is_faved),
          owner.handle,
          owner.name,
          EXISTS (SELECT FROM org_members WHERE org_members.organization_user_id = owner.id) AS is_org
        FROM projects p
          JOIN users owner ON owner.id = p.owner_user_id
        WHERE p.owner_user_id = #{projectOwnerUserId}
          AND user_has_project_permission(#{callerUserId}, p.id, #{ProjectView})
        ORDER BY p.created_at DESC
      |]
  where
    unpackRows :: [Project PG.:. FavData PG.:. ProjectOwner] -> [(Project, FavData, ProjectOwner)]
    unpackRows = fmap \(project PG.:. favData PG.:. projectOwner) ->
      (project, favData, projectOwner)

listProjectsFromCatalogWithMetadata ::
  Maybe UserId ->
  -- | (project, numFavs, isFavedByCaller)
  PG.Transaction e (Map CategoryName [(Project, FavData, ProjectOwner)])
listProjectsFromCatalogWithMetadata callerUserId = do
  projects :: [(Project PG.:. PG.Only CategoryName PG.:. FavData PG.:. ProjectOwner)] <- PG.queryListRows sql
  projects
    & Utils.groupBy (\(_ PG.:. PG.Only category PG.:. _) -> category)
    & (fmap . fmap) (\(p PG.:. _category PG.:. favData PG.:. projectOwner) -> (p, favData, projectOwner))
    & pure
  where
    -- Selects all projects belonging to a user, join in whether the caller
    -- has favorited the project, and how many favorites the project has in total.
    sql =
      [PG.sql|
        SELECT
          p.id,
          p.owner_user_id,
          p.slug,
          p.summary,
          p.tags,
          p.private,
          p.created_at,
          p.updated_at,
          cc.name,
          (SELECT COUNT(*) FROM project_favorites WHERE project_favorites.project_id = p.id) AS num_favs,
          (SELECT EXISTS(SELECT FROM project_favorites f WHERE f.project_id = p.id AND f.user_id = #{callerUserId}) AS is_faved),
          owner.handle,
          owner.name,
          EXISTS (SELECT FROM org_members WHERE org_members.organization_user_id = owner.id)
        FROM project_categories pc
             JOIN catalog_categories cc ON cc.id = pc.category_id
             JOIN projects p ON pc.project_id = p.id
             JOIN users owner ON p.owner_user_id = owner.id
        WHERE p.private = false
        ORDER BY cc.name
      |]

addProjectToCatalogCategory :: ProjectId -> CategoryID -> PG.Transaction e ()
addProjectToCatalogCategory projectId categoryId = do
  PG.execute_
    [PG.sql|
        INSERT INTO project_categories(project_id, category_id)
          VALUES (#{projectId}, #{categoryId})
          ON CONFLICT DO NOTHING
      |]

removeProjectFromCatalogCategory :: ProjectId -> CategoryID -> PG.Transaction e ()
removeProjectFromCatalogCategory projectId categoryId = do
  PG.execute_
    [PG.sql|
        DELETE FROM project_categories
          WHERE project_id = #{projectId} AND category_id = #{categoryId}
      |]

-- | Get or create catalog category.
getOrCreateCatalogCategory :: CategoryName -> PG.Transaction e CategoryID
getOrCreateCatalogCategory categoryName = do
  PG.query1Col getCatSql >>= \case
    Just catId -> pure catId
    Nothing -> PG.queryExpect1Col insertCatSql
  where
    getCatSql =
      [PG.sql|
        SELECT id FROM catalog_categories
          WHERE name = #{categoryName}
      |]
    insertCatSql =
      [PG.sql|
        INSERT INTO catalog_categories(name)
          VALUES (#{categoryName})
          RETURNING id
      |]

-- | Get or create catalog category.
getCatalogCategory :: CategoryName -> PG.Transaction e (Maybe CategoryID)
getCatalogCategory categoryName = do
  PG.query1Col
    [PG.sql|
        SELECT id FROM catalog_categories
          WHERE name = #{categoryName}
      |]

createProject :: UserId -> ProjectSlug -> Maybe Text -> Set ProjectTag -> ProjectVisibility -> PG.Transaction e ProjectId
createProject ownerUserId slug summary tags visibility = do
  let tagsList = Set.toList tags
  PG.queryExpect1Col
    [PG.sql|
        INSERT INTO projects(owner_user_id, slug, summary, tags, private)
          VALUES (#{ownerUserId}, #{slug}, #{summary}, #{tagsList}, #{visibility})
          RETURNING id;
      |]

-- | Returns false if project could not be found.
--
-- Must be called with a transaction to be safe.
updateProject :: ProjectId -> NullableUpdate Text -> SetUpdate ProjectTag -> Maybe ProjectVisibility -> PG.Transaction e Bool
updateProject projectId newSummary tagChanges newVisibility =
  -- This method is a bit naive, we just get the old project, update the fields accordingly,
  -- then save the entire project again.
  isJust <$> runMaybeT do
    Project {..} <- lift $ expectProjectById projectId
    let updatedSummary = fromNullableUpdate summary newSummary
    let updatedTags = Set.toList $ applySetUpdate tags tagChanges
    let updatedVisibility = fromMaybe visibility newVisibility
    lift $
      PG.execute_
        [PG.sql|
      UPDATE projects
      SET
        summary = #{updatedSummary},
        tags = #{updatedTags},
        private = #{updatedVisibility}
      WHERE
        projects.id = #{projectId}
      |]

-- | Deletes a project and anything referencing it entirely. No tombstones are left behind.
deleteProject :: ProjectId -> PG.Transaction e ()
deleteProject projectId =
  PG.execute_
    [PG.sql|
      DELETE FROM projects
      WHERE
        projects.id = #{projectId}
      |]

branchByProjectIdAndShortHand :: ProjectId -> BranchShortHand -> PG.Transaction e (Maybe (Branch CausalId))
branchByProjectIdAndShortHand projectId BranchShortHand {contributorHandle, branchName} = runMaybeT do
  mayContributorUserId <- case contributorHandle of
    Nothing -> pure Nothing
    Just handle -> do
      User {user_id} <- MaybeT (UserQ.userByHandle handle)
      pure (Just user_id)

  MaybeT $
    PG.query1Row
      [PG.sql|
        SELECT
          id,
          project_id,
          name,
          contributor_id,
          causal_id,
          merge_target_branch_id,
          created_at,
          updated_at,
          creator_id
        FROM project_branches
        WHERE project_id = #{projectId}
              AND (contributor_id = #{mayContributorUserId} OR (#{mayContributorUserId} IS NULL AND contributor_id IS NULL))
              AND name = #{branchName}
              AND deleted_at IS NULL
      |]

data DeletionFilter = IncludeDeleted | ExcludeDeleted
  deriving (Eq, Show)

branchById :: DeletionFilter -> BranchId -> PG.Transaction e (Maybe (Branch CausalId))
branchById deletionFilter branchId = do
  let includeDeleted = case deletionFilter of
        IncludeDeleted -> True
        ExcludeDeleted -> False
  PG.query1Row
    [PG.sql|
        SELECT
          pb.id,
          pb.project_id,
          pb.name,
          pb.contributor_id,
          pb.causal_id,
          pb.merge_target_branch_id,
          pb.created_at,
          pb.updated_at,
          pb.creator_id
        FROM project_branches pb
        WHERE pb.id = #{branchId}
              AND (#{includeDeleted} OR pb.deleted_at IS NULL)
      |]

branchByProjectBranchShortHand :: ProjectBranchShortHand -> PG.Transaction e (Maybe (Branch CausalId))
branchByProjectBranchShortHand ProjectBranchShortHand {userHandle, projectSlug, contributorHandle, branchName} = do
  PG.query1Row
    [PG.sql|
        SELECT
          b.id,
          b.project_id,
          b.name,
          b.contributor_id,
          b.causal_id,
          b.merge_target_branch_id,
          b.created_at,
          b.updated_at,
          b.creator_id
        FROM project_branches b
          JOIN projects p ON p.id = b.project_id
          JOIN users u ON u.id = p.owner_user_id
          LEFT JOIN users c ON c.id = b.contributor_id
        WHERE
          u.handle = #{userHandle}
          AND p.slug = #{projectSlug}
          AND (c.handle = #{contributorHandle} OR (#{contributorHandle} IS NULL AND c.handle IS NULL))
          AND b.deleted_at IS NULL
          AND b.name = #{branchName}
      |]

projectBranchShortHandByBranchId :: BranchId -> PG.Transaction e (Maybe ProjectBranchShortHand)
projectBranchShortHandByBranchId branchId = do
  PG.query1Row
    [PG.sql|
        SELECT
          u.handle,
          p.slug,
          c.handle,
          b.name
        FROM project_branches b
          JOIN projects p ON p.id = b.project_id
          JOIN users u ON u.id = p.owner_user_id
          LEFT JOIN users c ON c.id = b.contributor_id
        WHERE
          b.id = #{branchId}
          AND b.deleted_at IS NULL
      |]
    <&> fmap \(userHandle, projectSlug, contributorHandle, branchName) ->
      ProjectBranchShortHand
        { userHandle,
          projectSlug,
          contributorHandle,
          branchName
        }

-- | Sets the 'deleted_at' field on the branch so it won't appear
-- in queries, but the row is kept.
softDeleteBranch :: BranchId -> PG.Transaction e ()
softDeleteBranch branchId = do
  PG.execute_
    [PG.sql|
        UPDATE project_branches
        SET deleted_at = NOW()
        WHERE id = #{branchId}
      |]

projectByShortHand :: ProjectShortHand -> PG.Transaction e (Maybe Project)
projectByShortHand ProjectShortHand {userHandle, projectSlug} = do
  PG.query1Row
    [PG.sql|
        SELECT
          p.id,
          p.owner_user_id,
          p.slug,
          p.summary,
          p.tags,
          p.private,
          p.created_at,
          p.updated_at
        FROM projects p
          JOIN users u ON u.id = p.owner_user_id
        WHERE u.handle = #{userHandle}
          AND p.slug = #{projectSlug}
      |]

createBranch ::
  NameLookupReceipt ->
  ProjectId ->
  BranchName ->
  Maybe UserId ->
  CausalId ->
  Maybe BranchId ->
  UserId ->
  PG.Transaction e BranchId
createBranch !_nlReceipt projectId branchName contributorId causalId mergeTarget creatorId = PG.transactionSpan "createBranch" mempty $ do
  branchId <- PG.queryExpect1Col createBranchSQL
  PG.execute_ (updateReflogSQL branchId ("Branch Created" :: Text))
  recordNotificationEvent branchId contributorId
  pure branchId
  where
    createBranchSQL =
      [PG.sql|
        INSERT INTO project_branches(
          project_id,
          name,
          contributor_id,
          creator_id,
          causal_id,
          merge_target_branch_id
        )
        VALUES (
          #{projectId},
          #{branchName},
          #{contributorId},
          #{creatorId},
          #{causalId},
          -- It's possible the merge target is from a deleted project, so we select to null it out if it no longer exists.
          (SELECT merge_target.id FROM project_branches merge_target WHERE merge_target.id = #{mergeTarget})
        ) RETURNING id
      |]
    updateReflogSQL branchId description =
      [PG.sql|
        INSERT INTO project_branch_reflog(
          branch_id,
          old_causal_id,
          new_causal_id,
          user_id,
          description
        )
        SELECT
          #{branchId},
          NULL,
          #{causalId},
          #{creatorId},
          #{description}
        FROM project_branches
        WHERE id = #{branchId}
      |]

    recordNotificationEvent :: BranchId -> Maybe UserId -> PG.Transaction e ()
    recordNotificationEvent branchId branchContributorUserId = do
      (projectData, projectResourceId, projectOwnerUserId) <- ProjectsQ.projectNotificationData projectId
      let branchData =
            BranchData
              { branchId,
                branchContributorUserId
              }
      let notifEvent =
            NotificationEvent
              { eventId = (),
                eventOccurredAt = (),
                eventResourceId = projectResourceId,
                eventProjectId = Just projectId,
                eventData = ProjectBranchUpdatedData projectData branchData,
                eventScope = projectOwnerUserId,
                eventActor = creatorId
              }
      NotifQ.recordEvent notifEvent

setBranchCausalHash ::
  NameLookupReceipt ->
  Text ->
  UserId ->
  BranchId ->
  CausalId ->
  PG.Transaction e ()
setBranchCausalHash !_nameLookupReceipt description callerUserId branchId causalId = do
  PG.execute_ updateReflogSQL
  PG.execute_ setBranchSQL
  recordNotificationEvent
  where
    setBranchSQL =
      [PG.sql|
        UPDATE project_branches
        SET causal_id = #{causalId},
            updated_at = NOW()
        WHERE id = #{branchId}
      |]
    updateReflogSQL =
      [PG.sql|
        INSERT INTO project_branch_reflog(
          branch_id,
          old_causal_id,
          new_causal_id,
          user_id,
          description
        )
        SELECT
          #{branchId},
          project_branches.causal_id,
          #{causalId},
          #{callerUserId},
          #{description}
        FROM project_branches
        WHERE id = #{branchId}
      |]
    recordNotificationEvent = do
      (projectId, projectResourceId, projectOwnerUserId, branchContributorUserId, private) <-
        PG.queryExpect1Row
          [PG.sql|
        SELECT p.id, p.resource_id, p.owner_user_id, pb.contributor_id, p.private
          FROM project_branches pb
          JOIN projects p ON p.id = pb.project_id
        WHERE pb.id = #{branchId}
        |]
      let projectData =
            ProjectData
              { projectId,
                public = not private
              }
      let branchData =
            BranchData
              { branchId,
                branchContributorUserId
              }
      let notifEvent =
            NotificationEvent
              { eventId = (),
                eventOccurredAt = (),
                eventResourceId = projectResourceId,
                eventProjectId = Just projectId,
                eventData = ProjectBranchUpdatedData projectData branchData,
                eventScope = projectOwnerUserId,
                eventActor = callerUserId
              }
      NotifQ.recordEvent notifEvent

getCausalIdForBranch :: BranchId -> PG.Transaction e CausalId
getCausalIdForBranch branchId = do
  PG.queryExpect1Col sql
  where
    sql =
      [PG.sql|
        SELECT causal_id FROM project_branches
          WHERE id = #{branchId}
                AND deleted_at IS NULL
      |]

listBranchesByProject ::
  Limit ->
  Maybe (Cursor (UTCTime, BranchId)) ->
  Maybe Query ->
  -- Either a contributor user Id or contributor handle prefix
  Maybe (Either UserId Query) ->
  BranchKindFilter ->
  ProjectId ->
  -- | (branch, contributorHandle)
  PG.Transaction e (Paged (UTCTime, BranchId) (Branch CausalId, Maybe UserHandle))
listBranchesByProject limit mayCursor mayBranchNamePrefix mayContributorQuery kind projectId = do
  results <- query limit (mkCursorFilter mayCursor)
  let paged@(Paged {prevCursor, nextCursor}) =
        results
          & pagedOn (\(Branch {updatedAt, branchId}, _) -> (updatedAt, branchId))
  hasPrevPage <- not . null <$> query 1 (mkCursorFilter prevCursor)
  hasNextPage <- not . null <$> query 1 (mkCursorFilter nextCursor)
  pure $ guardPaged hasPrevPage hasNextPage paged
  where
    mkCursorFilter cursor = case cursor of
      Nothing -> mempty
      Just (Cursor (afterTime, branchId) Previous) -> [PG.sql| AND (b.updated_at, b.id) > (#{afterTime}, #{branchId})|]
      Just (Cursor (beforeTime, branchId) Next) -> [PG.sql| AND (b.updated_at, b.id) < (#{beforeTime}, #{branchId})|]
    kindFilter = case kind of
      AllBranchKinds -> ""
      OnlyContributorBranches -> "AND b.contributor_id IS NOT NULL"
      OnlyCoreBranches -> "AND b.contributor_id IS NULL"
    contributorFilter = case mayContributorQuery of
      Nothing -> mempty
      -- Allow null contributor here for the case where we're listing 'all' branch kinds.
      Just (Left contributorId) -> [PG.sql| AND (b.contributor_id IS NULL OR (b.contributor_id = #{contributorId})) |]
      Just (Right (Query contributorHandlePrefix)) -> [PG.sql| AND (contributor.handle IS NULL OR starts_with(contributor.handle, #{contributorHandlePrefix})) |]
    branchNameFilter = case mayBranchNamePrefix of
      Nothing -> mempty
      Just (Query branchNamePrefix) -> [PG.sql| AND starts_with(b.name, #{branchNamePrefix}) |]
    query :: Limit -> PG.Sql -> PG.Transaction e [(Branch CausalId, Maybe UserHandle)]
    query limit cursorFilter = do
      let sql =
            intercalateMap
              "\n"
              id
              [ ( [PG.sql|
            SELECT
              b.id,
              b.project_id,
              b.name,
              b.contributor_id,
              b.causal_id,
              b.merge_target_branch_id,
              b.created_at,
              b.updated_at,
              b.creator_id,
              contributor.handle
            FROM project_branches b
            LEFT JOIN users AS contributor ON contributor.id = b.contributor_id
            WHERE
              b.deleted_at IS NULL
              AND b.project_id = #{projectId}
              |]
                ),
                kindFilter,
                contributorFilter,
                branchNameFilter,
                cursorFilter,
                ( [PG.sql|
            ORDER BY b.updated_at DESC, b.id DESC
            LIMIT #{limit}
          |]
                )
              ]
      PG.queryListRows sql
        <&> fmap (\(branch PG.:. PG.Only contributorHandle) -> (branch, contributorHandle))

-- | List all BranchHashes which are reachable within a given user's codebase.
accessibleCausalsForUser ::
  UserId ->
  PG.Transaction e [CausalId]
accessibleCausalsForUser userId = do
  PG.queryListCol
    [PG.sql|
  -- include core branches
  SELECT DISTINCT causal_id
    FROM project_branches
      JOIN projects ON projects.id = project_branches.project_id
      JOIN users ON users.id = projects.owner_user_id
    WHERE
      project_branches.deleted_at IS NULL
      AND users.id = #{userId}
      AND project_branches.contributor_id IS NULL
  -- include contributor branches
  UNION
  SELECT DISTINCT causal_id
    FROM project_branches
    WHERE
      project_branches.deleted_at IS NULL
      AND project_branches.contributor_id = #{userId}
  -- include releases
  UNION
  SELECT DISTINCT squashed_causal_id
    FROM project_releases
      JOIN projects ON projects.id = project_releases.project_id
      JOIN users ON users.id = projects.owner_user_id
    WHERE
      project_releases.deleted_at IS NULL
      AND users.id = #{userId}
  UNION
  SELECT DISTINCT unsquashed_causal_id
    FROM project_releases
      JOIN projects ON projects.id = project_releases.project_id
      JOIN users ON users.id = projects.owner_user_id
    WHERE
      project_releases.deleted_at IS NULL
      AND users.id = #{userId}
  -- include loose code root
  UNION
  SELECT DISTINCT causal_id
    FROM loose_code_roots
    WHERE
      loose_code_roots.user_id = #{userId}
    |]

-- | List all contributor branches of a given user which the caller has access to.
listContributorBranchesOfUserAccessibleToCaller ::
  UserId ->
  Maybe UserId ->
  Limit ->
  Maybe (Cursor (UTCTime, BranchId)) ->
  Maybe Query ->
  Maybe ProjectId ->
  PG.Transaction e (Paged (UTCTime, BranchId) (Branch CausalId, Project, ProjectOwner))
listContributorBranchesOfUserAccessibleToCaller contributorUserId mayCallerUserId limit mayCursor mayBranchNamePrefix mayProjectId = do
  results <- query limit (mkCursorFilter mayCursor)
  let paged@(Paged {prevCursor, nextCursor}) =
        results
          & pagedOn (\(Branch {updatedAt, branchId}, _, _) -> (updatedAt, branchId))
  hasPrevPage <- not . null <$> query 1 (mkCursorFilter prevCursor)
  hasNextPage <- not . null <$> query 1 (mkCursorFilter nextCursor)
  pure $ guardPaged hasPrevPage hasNextPage paged
  where
    branchNameFilter = case mayBranchNamePrefix of
      Nothing -> mempty
      Just (Query branchNamePrefix) -> [PG.sql| AND starts_with(b.name, #{branchNamePrefix}) |]
    mkCursorFilter cursor = case cursor of
      Nothing -> mempty
      Just (Cursor (afterTime, branchId) Previous) -> [PG.sql| AND (b.updated_at, b.id) > (#{afterTime}, #{branchId}) |]
      Just (Cursor (beforeTime, branchId) Next) -> [PG.sql| AND (b.updated_at, b.id) < (#{beforeTime}, #{branchId}) |]
    projectFilter = case mayProjectId of
      Nothing -> mempty
      Just projId -> [PG.sql| AND b.project_id = #{projId} |]
    query :: Limit -> PG.Sql -> PG.Transaction e [(Branch CausalId, Project, ProjectOwner)]
    query limit cursorFilter = do
      let sql =
            intercalateMap
              "\n"
              id
              [ [PG.sql|
            SELECT
              b.id,
              b.project_id,
              b.name,
              b.contributor_id,
              b.causal_id,
              b.merge_target_branch_id,
              b.created_at,
              b.updated_at,
              b.creator_id,
              project.id,
              project.owner_user_id,
              project.slug,
              project.summary,
              project.tags,
              project.private,
              project.created_at,
              project.updated_at,
              project_owner.handle,
              project_owner.name,
              EXISTS (SELECT FROM org_members WHERE org_members.organization_user_id = project.owner_user_id)
            FROM project_branches b
            JOIN projects project ON project.id = b.project_id
            JOIN users AS project_owner ON project_owner.id = project.owner_user_id
            WHERE
              b.deleted_at IS NULL
              AND b.contributor_id = #{contributorUserId}
              AND user_has_project_permission(#{mayCallerUserId}, b.project_id, #{ProjectView})
              |],
                branchNameFilter,
                cursorFilter,
                projectFilter,
                [PG.sql|
            ORDER BY b.updated_at DESC, b.id DESC
            LIMIT #{limit}
          |]
              ]
      PG.queryListRows sql
        <&> fmap (\(branch PG.:. project PG.:. projectOwner) -> (branch, project, projectOwner))

-- | Returns Project Owner information, including whether that user is an organization or not.
projectOwnerByProjectId :: ProjectId -> PG.Transaction e (Maybe ProjectOwner)
projectOwnerByProjectId projectId = do
  listToMaybe
    <$> PG.queryListRows
      [PG.sql|
        SELECT
          project_owner.handle,
          project_owner.name,
          EXISTS (SELECT FROM org_members WHERE org_members.organization_user_id = project_owner.id)
        FROM projects
        JOIN users AS project_owner ON project_owner.id = projects.owner_user_id
        WHERE projects.id = #{projectId};
      |]

releaseDownloadStatsForProject :: ProjectId -> PG.Transaction e DownloadStats
releaseDownloadStatsForProject projectId = do
  downloadsInLast28Days <-
    PG.queryListCol
      [PG.sql|
        SELECT SUM(downloads)
        FROM project_release_daily_downloads
        JOIN project_releases ON project_releases.id = project_release_daily_downloads.release_id
        WHERE project_releases.project_id = #{projectId}
              AND day >= NOW() - INTERVAL '28 days'
        GROUP BY day, project_id
        ORDER BY day DESC;
      |]
  pure $ DownloadStats downloadsInLast28Days

contributionStatsForProject :: ProjectId -> PG.Transaction e ContributionStats
contributionStatsForProject projectId = do
  statusCounts <-
    PG.queryListRows @(ContributionStatus, Int64)
      [PG.sql|
      SELECT status, COUNT(*)
        FROM contributions
        WHERE project_id = #{projectId}
        GROUP BY status
      |]
  let (Sum draft, Sum inReview, Sum closed, Sum merged) =
        statusCounts
          & foldMap \case
            (Draft, count) -> (Sum $ fromIntegral count, 0, 0, 0)
            (InReview, count) -> (0, Sum $ fromIntegral count, 0, 0)
            (Closed, count) -> (0, 0, Sum $ fromIntegral count, 0)
            (Merged, count) -> (0, 0, 0, Sum $ fromIntegral count)
  pure $ ContributionStats {draft, inReview, closed, merged}

ticketStatsForProject :: ProjectId -> PG.Transaction e TicketStats
ticketStatsForProject projectId = do
  statusCounts <-
    PG.queryListRows @(TicketStatus, Int64)
      [PG.sql|
      SELECT status, COUNT(*)
        FROM tickets
        WHERE project_id = #{projectId}
        GROUP BY status
      |]
  let (Sum numOpenTickets, Sum numClosedTickets) =
        statusCounts
          & foldMap \case
            (Ticket.Open, count) -> (fromIntegral count, mempty)
            (Ticket.Closed, count) -> (mempty, fromIntegral count)
  pure $ TicketStats {numOpenTickets, numClosedTickets}

isUnisonEmployee :: UserId -> PG.Transaction e Bool
isUnisonEmployee uid = do
  PG.queryExpect1Col
    [PG.sql|
        SELECT EXISTS (SELECT FROM org_members
                      JOIN users AS org_user ON organization_user_id = org_user.id
                      WHERE member_user_id = #{uid}
                            AND org_user.handle = 'unison')
      |]

-- | Returns the handles of all orgs the provided user is a member of.
organizationMemberships :: (PG.QueryA m) => UserId -> m [OrgId]
organizationMemberships uid = do
  PG.queryListCol
    [PG.sql|
        SELECT om.org_id
          FROM org_members om
          WHERE om.member_user_id = #{uid}
      |]

releaseByProjectReleaseShortHand :: ProjectReleaseShortHand -> PG.Transaction e (Maybe (Release CausalId UserId))
releaseByProjectReleaseShortHand ProjectReleaseShortHand {userHandle, projectSlug, releaseVersion = ReleaseVersion {major, minor, patch}} = do
  PG.query1Row
    [PG.sql|
        SELECT
          release.id,
          release.project_id,
          release.unsquashed_causal_id,
          release.squashed_causal_id,
          release.created_at,
          release.updated_at,
          release.created_at,
          release.created_by,
          release.deprecated_at,
          release.deprecated_by,
          release.created_by,
          release.major_version,
          release.minor_version,
          release.patch_version
        FROM project_releases AS release
             JOIN projects AS project ON project.id = release.project_id
             JOIN users AS project_owner ON project_owner.id = project.owner_user_id
        WHERE project_owner.handle = #{userHandle}
              AND project.slug = #{projectSlug}
              AND release.major_version = #{major}
              AND release.minor_version = #{minor}
              AND release.patch_version = #{patch}
              AND release.deleted_at IS NULL
      |]

releaseByProjectIdAndReleaseShortHand :: ProjectId -> ReleaseShortHand -> PG.Transaction e (Maybe (Release CausalId UserId))
releaseByProjectIdAndReleaseShortHand projectId ReleaseShortHand {releaseVersion = ReleaseVersion {major, minor, patch}} = do
  PG.query1Row
    [PG.sql|
        SELECT
          release.id,
          release.project_id,
          release.unsquashed_causal_id,
          release.squashed_causal_id,
          release.created_at,
          release.updated_at,
          release.created_at,
          release.created_by,
          release.deprecated_at,
          release.deprecated_by,
          release.created_by,
          release.major_version,
          release.minor_version,
          release.patch_version
        FROM project_releases AS release
        WHERE release.project_id = #{projectId}
              AND release.major_version = #{major}
              AND release.minor_version = #{minor}
              AND release.patch_version = #{patch}
              AND release.deleted_at IS NULL
      |]

listReleasesByProject ::
  Limit ->
  Maybe (Cursor (Int64, Int64, Int64, ReleaseId)) ->
  Maybe Query ->
  ReleaseStatusFilter ->
  ProjectId ->
  PG.Transaction e [Release CausalId UserHandle]
listReleasesByProject limit mayCursor mayVersionPrefix status projectId = do
  let statusFilter = case status of
        AllReleases -> ""
        OnlyPublished -> [PG.sql| AND deprecated_at IS NULL |]
        OnlyDeprecated -> [PG.sql| AND deprecated_at IS NOT NULL |]
  let versionFilters :: [PG.Sql]
      versionFilters =
        case mayVersionPrefix of
          Nothing -> []
          Just (Query versionPrefix) ->
            let numericalPrefix =
                  versionPrefix
                    & Text.splitOn "."
                    & fmap (readMaybe @Int64 . Text.unpack)
                    & List.takeWhile isJust
                    & catMaybes
                numericalFilters = case numericalPrefix of
                  [] -> []
                  [major] -> do
                    [[PG.sql| AND major_version = #{major} |]]
                  [major, minor] -> do
                    [[PG.sql| AND major_version = #{major} AND minor_version = #{minor} |]]
                  -- Just ignore any trailing components
                  (major : minor : patch : _) -> do
                    [[PG.sql| AND major_version = #{major} AND minor_version = #{minor} AND patch_version = #{patch} |]]
             in numericalFilters
  let cursorFilter = case mayCursor of
        Nothing -> mempty
        Just (Cursor (major, minor, patch, releaseId) Previous) ->
          [PG.sql| AND (release.major_version, release.minor_version, release.patch_version, release.id) < (#{major}, #{minor}, #{patch}, #{releaseId}) |]
        Just (Cursor (major, minor, patch, releaseId) Next) ->
          [PG.sql| AND (release.major_version, release.minor_version, release.patch_version, release.id) >= (#{major}, #{minor}, #{patch}, #{releaseId}) |]
  let (sql) =
        intercalateMap
          "\n"
          id
          ( [ ( [PG.sql|SELECT
                release.id,
                release.project_id,
                release.unsquashed_causal_id,
                release.squashed_causal_id,
                release.created_at,
                release.updated_at,
                release.created_at,
                publisher.handle,
                release.deprecated_at,
                deprecator.handle,
                publisher.handle,
                release.major_version,
                release.minor_version,
                release.patch_version
                FROM project_releases AS release
                LEFT JOIN users AS publisher
                  ON publisher.id = release.created_by
                LEFT JOIN users AS deprecator
                  ON deprecator.id = release.deprecated_by
                WHERE release.project_id = #{projectId}
                      AND release.deleted_at IS NULL
                      |]
              )
            ]
              <> versionFilters
              <> [ statusFilter,
                   cursorFilter,
                   [PG.sql| ORDER BY release.major_version DESC, release.minor_version DESC, release.patch_version DESC, release.id DESC|],
                   [PG.sql| LIMIT #{limit} |]
                 ]
          )
  PG.queryListRows sql

-- | Add one to the daily downloads of a release.
incrementReleaseDownloads :: ReleaseId -> PG.Transaction e ()
incrementReleaseDownloads releaseId = do
  PG.execute_
    [PG.sql|
      INSERT INTO project_release_daily_downloads (release_id, day, downloads)
        VALUES (#{releaseId}, CURRENT_DATE, 1)
        ON CONFLICT (release_id, day)
          DO UPDATE
          SET downloads = project_release_daily_downloads.downloads + 1
      |]

-- | Add one to the daily downloads of a branch.
incrementBranchDownloads :: BranchId -> PG.Transaction e ()
incrementBranchDownloads branchId = do
  PG.execute_
    [PG.sql|
      INSERT INTO project_branch_daily_downloads (branch_id, day, downloads)
        VALUES (#{branchId}, CURRENT_DATE, 1)
        ON CONFLICT (branch_id, day)
          DO UPDATE
          SET downloads = project_branch_daily_downloads.downloads + 1
      |]

likeEscape :: Text -> Text
likeEscape = Text.replace "%" "\\%" . Text.replace "_" "\\_"

getOAuthConfigForClient :: OAuthClientId -> PG.Transaction e (Maybe OAuthClientConfig)
getOAuthConfigForClient clientId = do
  PG.query1Row
    [PG.sql|
        SELECT
          client_id,
          client_secret,
          redirect_host,
          approved_scopes,
          audience
        FROM oauth_clients
        WHERE client_id = #{clientId}
      |]