Skip to content

High performance self-hosted photo and video management solution.

34.6 KB
Raw Blame
-- NOTE: This file is auto generated by ./sql-generator

-- PersonRepository.reassignFaces
update "asset_face"
set
  "personGroupId" = $1
from
  "asset"
where
  "asset_face"."assetId" = "asset"."id"
  and "asset_face"."personGroupId" = $2

-- PersonRepository.updateGroupId
begin
update "asset_face"
set
  "personGroupId" = $1
from
  "asset"
where
  "asset_face"."assetId" = "asset"."id"
  and "asset_face"."personGroupId" = $2
  and "asset"."ownerId" = $3
rollback

-- PersonRepository.unassignFaces
update "asset_face"
set
  "personGroupId" = $1
from
  "asset"
  inner join "user" on "user"."id" = "asset"."ownerId"
where
  "asset_face"."assetId" = "asset"."id"
  and "asset_face"."sourceType" = $2
  and "user"."clusterGroupId" = $3

-- PersonRepository.delete
delete from "person"
where
  "ownerId" = $1
  and "person"."personGroupId" in ($2)
returning
  "personGroupId",
  "ownerId",
  "thumbnailPath"

-- PersonRepository.deleteGroups
delete from "person_group"
where
  "person_group"."id" in ($1)

-- PersonRepository.deleteEmptyGroups
delete from "person_group"
where
  not exists (
    select
      "person"."personGroupId"
    from
      "person"
    where
      "person"."personGroupId" = "person_group"."id"
  )

-- PersonRepository.deleteOrphanedClusterGroups
delete from "cluster_group"
where
  not exists (
    select
      "user"."id"
    from
      "user"
    where
      "user"."clusterGroupId" = "cluster_group"."id"
  )

-- PersonRepository.getAllFaces
select
  "asset_face".*
from
  "asset_face"
  inner join "asset" on "asset"."id" = "asset_face"."assetId"
  inner join "user" on "user"."id" = "asset"."ownerId"
where
  "asset_face"."personGroupId" is null
  and "asset_face"."sourceType" = $1
  and "user"."clusterGroupId" = $2
  and "asset_face"."deletedAt" is null
  and "asset_face"."isVisible" is true

-- PersonRepository.forBirthdayMemories
select
  "person"."personGroupId",
  "person"."name",
  date_part('year', person."birthDate")::int as "birthYear",
  date_part('month', person."birthDate")::int as "birthMonth",
  date_part('day', person."birthDate")::int as "birthDay"
from
  "person"
where
  "person"."ownerId" = $1
  and "person"."isHidden" = $2
  and "person"."name" != $3
  and "person"."birthDate" is not null
  and (
    date_part('month', person."birthDate")::int = $4
    and date_part('day', person."birthDate")::int = $5
  )
  and date_part('year', person."birthDate")::int < $6

-- PersonRepository.forBirthdayMemories (leap day fallback)
select
  "person"."personGroupId",
  "person"."name",
  date_part('year', person."birthDate")::int as "birthYear",
  date_part('month', person."birthDate")::int as "birthMonth",
  date_part('day', person."birthDate")::int as "birthDay"
from
  "person"
where
  "person"."ownerId" = $1
  and "person"."isHidden" = $2
  and "person"."name" != $3
  and "person"."birthDate" is not null
  and (
    (
      date_part('month', person."birthDate")::int = $4
      and date_part('day', person."birthDate")::int = $5
    )
    or (
      date_part('month', person."birthDate")::int = $6
      and date_part('day', person."birthDate")::int = $7
    )
  )
  and date_part('year', person."birthDate")::int < $8

-- PersonRepository.getFileSamples
select
  "ownerId",
  "personGroupId",
  "thumbnailPath"
from
  "person"
where
  "thumbnailPath" != ''
limit
  3

-- PersonRepository.getAllForUser
with
  "faces" as (
    select
      "visible"."personGroupId",
      count(*) as "faceCount"
    from
      (
        select
          "asset_face"."personGroupId"
        from
          "asset_face"
        where
          "asset_face"."deletedAt" is null
          and "asset_face"."isVisible" is true
          and "asset_face"."assetVisibility" = 'timeline'
          and "asset_face"."assetDeletedAt" is null
          and "asset_face"."personGroupId" in (
            select
              "person"."personGroupId"
            from
              "person"
            where
              "person"."ownerId" = $1
          )
          and "asset_face"."ownerId" = any ($2::uuid[])
        union all
        select
          "asset_face"."personGroupId"
        from
          "asset_face"
        where
          "asset_face"."deletedAt" is null
          and "asset_face"."isVisible" is true
          and "asset_face"."assetVisibility" = 'timeline'
          and "asset_face"."assetDeletedAt" is null
          and "asset_face"."personGroupId" in (
            select
              "person"."personGroupId"
            from
              "person"
            where
              "person"."ownerId" = $3
          )
          and not "asset_face"."ownerId" = any ($4::uuid[])
          and exists (
            select
              1 as "exists"
            from
              "album_asset"
              inner join "album" on "album"."id" = "album_asset"."albumId"
              and "album"."deletedAt" is null
              inner join "album_user" on "album_user"."albumId" = "album"."id"
              and "album_user"."userId" = $5
            where
              "album_asset"."assetId" = "asset_face"."assetId"
          )
      ) as "visible"
    group by
      "visible"."personGroupId"
  ),
  "people" as (
    select
      "person".*,
      "faces"."faceCount"
    from
      "person"
      inner join "faces" on "faces"."personGroupId" = "person"."personGroupId"
    where
      "person"."ownerId" = $6
      and (
        "person"."name" != $7
        or "faces"."faceCount" >= coalesce(
          (
            select
              cast(value -> 'people' ->> 'minimumFaces' as integer) as "value"
            from
              "user_metadata"
            where
              "userId" = $8
              and "key" = 'preferences'
          ),
          3
        )
      )
  ),
  "page" as (
    select
      "people".*
    from
      "people"
    where
      "people"."isHidden" = $9
    order by
      "people"."isHidden" asc,
      "people"."isFavorite" desc,
      "people"."name" = '',
      "people"."faceCount" desc,
      "people"."name",
      "people"."createdAt",
      "people"."personGroupId"
    limit
      $10
    offset
      $11
  )
select
  "counts"."total",
  "counts"."hidden",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "person".*,
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "person_user"."sharedById",
                  "person_user"."role",
                  "other"."name",
                  "other"."birthDate"
                from
                  "person" as "other"
                  inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
                  and "person_user"."sharedById" = "other"."ownerId"
                  and "person_user"."sharedWithId" = $12
                where
                  "other"."personGroupId" = "person"."personGroupId"
              ) as agg
          ) as "otherPeople",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedById"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedWithId" = $13
                order by
                  "user"."name"
              ) as agg
          ) as "sharedBy",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedWithId"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedById" = $14
                order by
                  "user"."name"
              ) as agg
          ) as "sharedWith"
        from
          "page" as "person"
      ) as agg
  ) as "people"
from
  (
    select
      count(*) as "total",
      count(*) filter (
        where
          "people"."isHidden" = $15
      ) as "hidden"
    from
      "people"
  ) as "counts"

-- PersonRepository.getAllForUser (closest person)
with
  "faces" as (
    select
      "visible"."personGroupId",
      count(*) as "faceCount"
    from
      (
        select
          "asset_face"."personGroupId"
        from
          "asset_face"
        where
          "asset_face"."deletedAt" is null
          and "asset_face"."isVisible" is true
          and "asset_face"."assetVisibility" = 'timeline'
          and "asset_face"."assetDeletedAt" is null
          and "asset_face"."personGroupId" in (
            select
              "person"."personGroupId"
            from
              "person"
            where
              "person"."ownerId" = $1
          )
          and "asset_face"."ownerId" = any ($2::uuid[])
        union all
        select
          "asset_face"."personGroupId"
        from
          "asset_face"
        where
          "asset_face"."deletedAt" is null
          and "asset_face"."isVisible" is true
          and "asset_face"."assetVisibility" = 'timeline'
          and "asset_face"."assetDeletedAt" is null
          and "asset_face"."personGroupId" in (
            select
              "person"."personGroupId"
            from
              "person"
            where
              "person"."ownerId" = $3
          )
          and not "asset_face"."ownerId" = any ($4::uuid[])
          and exists (
            select
              1 as "exists"
            from
              "album_asset"
              inner join "album" on "album"."id" = "album_asset"."albumId"
              and "album"."deletedAt" is null
              inner join "album_user" on "album_user"."albumId" = "album"."id"
              and "album_user"."userId" = $5
            where
              "album_asset"."assetId" = "asset_face"."assetId"
          )
      ) as "visible"
    group by
      "visible"."personGroupId"
  ),
  "people" as (
    select
      "person".*,
      "faces"."faceCount"
    from
      "person"
      inner join "faces" on "faces"."personGroupId" = "person"."personGroupId"
    where
      "person"."ownerId" = $6
      and (
        "person"."name" != $7
        or "faces"."faceCount" >= coalesce(
          (
            select
              cast(value -> 'people' ->> 'minimumFaces' as integer) as "value"
            from
              "user_metadata"
            where
              "userId" = $8
              and "key" = 'preferences'
          ),
          3
        )
      )
  ),
  "page" as (
    select
      "people".*
    from
      "people"
    where
      "people"."isHidden" = $9
    order by
      "people"."isHidden" asc,
      "people"."isFavorite" desc,
      (
        select
          "face_search"."embedding"
        from
          "face_search"
        where
          "face_search"."faceId" = "people"."faceAssetId"
      ) <=> (
        select
          "face_search"."embedding"
        from
          "face_search"
          inner join "person" as "closest" on "closest"."faceAssetId" = "face_search"."faceId"
        where
          "closest"."ownerId" = $10
          and "closest"."personGroupId" = $11
      ),
      "people"."name" = '',
      "people"."faceCount" desc,
      "people"."name",
      "people"."createdAt",
      "people"."personGroupId"
    limit
      $12
    offset
      $13
  )
select
  "counts"."total",
  "counts"."hidden",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "person".*,
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "person_user"."sharedById",
                  "person_user"."role",
                  "other"."name",
                  "other"."birthDate"
                from
                  "person" as "other"
                  inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
                  and "person_user"."sharedById" = "other"."ownerId"
                  and "person_user"."sharedWithId" = $14
                where
                  "other"."personGroupId" = "person"."personGroupId"
              ) as agg
          ) as "otherPeople",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedById"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedWithId" = $15
                order by
                  "user"."name"
              ) as agg
          ) as "sharedBy",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedWithId"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedById" = $16
                order by
                  "user"."name"
              ) as agg
          ) as "sharedWith"
        from
          "page" as "person"
      ) as agg
  ) as "people"
from
  (
    select
      count(*) as "total",
      count(*) filter (
        where
          "people"."isHidden" = $17
      ) as "hidden"
    from
      "people"
  ) as "counts"

-- PersonRepository.getAllWithoutFaces
select
  "person".*
from
  "person"
  left join "asset_face" on "asset_face"."personGroupId" = "person"."personGroupId"
where
  "asset_face"."deletedAt" is null
  and (
    "asset_face"."isVisible" is null
    or "asset_face"."isVisible" = $1
  )
group by
  "person"."ownerId",
  "person"."personGroupId"
having
  count("asset_face"."assetId") = $2

-- PersonRepository.getFaces
select
  "asset_face".*,
  (
    select
      to_json(obj)
    from
      (
        select
          "person".*,
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "person_user"."sharedById",
                  "person_user"."role",
                  "other"."name",
                  "other"."birthDate"
                from
                  "person" as "other"
                  inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
                  and "person_user"."sharedById" = "other"."ownerId"
                  and "person_user"."sharedWithId" = $1
                where
                  "other"."personGroupId" = "person"."personGroupId"
              ) as agg
          ) as "otherPeople",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedById"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedWithId" = $2
                order by
                  "user"."name"
              ) as agg
          ) as "sharedBy",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedWithId"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedById" = $3
                order by
                  "user"."name"
              ) as agg
          ) as "sharedWith"
        from
          "person"
        where
          "person"."personGroupId" = "asset_face"."personGroupId"
          and "person"."ownerId" = $4
      ) as obj
  ) as "person"
from
  "asset_face"
where
  "asset_face"."assetId" = $5
  and "asset_face"."deletedAt" is null
  and "asset_face"."isVisible" = $6
order by
  "asset_face"."boundingBoxX1" asc

-- PersonRepository.getFaceById
select
  "asset_face".*,
  (
    select
      to_json(obj)
    from
      (
        select
          "person".*,
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "person_user"."sharedById",
                  "person_user"."role",
                  "other"."name",
                  "other"."birthDate"
                from
                  "person" as "other"
                  inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
                  and "person_user"."sharedById" = "other"."ownerId"
                  and "person_user"."sharedWithId" = $1
                where
                  "other"."personGroupId" = "person"."personGroupId"
              ) as agg
          ) as "otherPeople",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedById"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedWithId" = $2
                order by
                  "user"."name"
              ) as agg
          ) as "sharedBy",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedWithId"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedById" = $3
                order by
                  "user"."name"
              ) as agg
          ) as "sharedWith"
        from
          "person"
        where
          "person"."personGroupId" = "asset_face"."personGroupId"
          and "person"."ownerId" = $4
      ) as obj
  ) as "person"
from
  "asset_face"
where
  "asset_face"."id" = $5
  and "asset_face"."deletedAt" is null

-- PersonRepository.getFaceForFacialRecognitionJob
select
  "asset_face"."id",
  "asset_face"."personGroupId",
  "asset_face"."sourceType",
  (
    select
      to_json(obj)
    from
      (
        select
          "asset"."ownerId",
          "asset"."visibility",
          "asset"."fileCreatedAt",
          "user"."clusterGroupId"
        from
          "asset"
          inner join "user" on "user"."id" = "asset"."ownerId"
        where
          "asset"."id" = "asset_face"."assetId"
      ) as obj
  ) as "asset",
  (
    select
      to_json(obj)
    from
      (
        select
          "face_search".*
        from
          "face_search"
        where
          "face_search"."faceId" = "asset_face"."id"
      ) as obj
  ) as "faceSearch"
from
  "asset_face"
where
  "asset_face"."id" = $1
  and "asset_face"."deletedAt" is null

-- PersonRepository.getDataForThumbnailGenerationJob
select
  "person"."ownerId",
  "asset_face"."boundingBoxX1" as "x1",
  "asset_face"."boundingBoxY1" as "y1",
  "asset_face"."boundingBoxX2" as "x2",
  "asset_face"."boundingBoxY2" as "y2",
  "asset_face"."imageWidth" as "oldWidth",
  "asset_face"."imageHeight" as "oldHeight",
  "asset"."type",
  "asset"."originalPath",
  "asset_exif"."orientation" as "exifOrientation",
  (
    select
      "asset_file"."path"
    from
      "asset_file"
    where
      "asset_file"."assetId" = "asset"."id"
      and "asset_file"."type" = 'preview'
      and "asset_file"."isEdited" = false
  ) as "previewPath"
from
  "person"
  inner join "asset_face" on "asset_face"."id" = "person"."faceAssetId"
  inner join "asset" on "asset_face"."assetId" = "asset"."id"
  left join "asset_exif" on "asset_exif"."assetId" = "asset"."id"
where
  "person"."ownerId" = $1
  and "person"."personGroupId" = $2
  and "asset_face"."deletedAt" is null

-- PersonRepository.reassignFace
update "asset_face"
set
  "personGroupId" = $1
where
  "asset_face"."id" = $2

-- PersonRepository.getByGroupId
select
  "person".*,
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "person_user"."sharedById",
          "person_user"."role",
          "other"."name",
          "other"."birthDate"
        from
          "person" as "other"
          inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
          and "person_user"."sharedById" = "other"."ownerId"
          and "person_user"."sharedWithId" = $1
        where
          "other"."personGroupId" = "person"."personGroupId"
      ) as agg
  ) as "otherPeople",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt",
          "person_user"."role"
        from
          "person_user"
          inner join "user" on "user"."id" = "person_user"."sharedById"
          and "user"."deletedAt" is null
        where
          "person_user"."personGroupId" = "person"."personGroupId"
          and "person_user"."sharedWithId" = $2
        order by
          "user"."name"
      ) as agg
  ) as "sharedBy",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt",
          "person_user"."role"
        from
          "person_user"
          inner join "user" on "user"."id" = "person_user"."sharedWithId"
          and "user"."deletedAt" is null
        where
          "person_user"."personGroupId" = "person"."personGroupId"
          and "person_user"."sharedById" = $3
        order by
          "user"."name"
      ) as agg
  ) as "sharedWith"
from
  "person"
where
  "person"."personGroupId" = $4
  and "person"."ownerId" = $5

-- PersonRepository.getForThumbnail
select
  (
    select
      "person"."thumbnailPath"
    from
      "person"
    where
      "person"."personGroupId" = "person_group"."id"
      and "person"."ownerId" = $1
  ) as "thumbnailPath",
  (
    select
      "person"."thumbnailPath"
    from
      "person"
      inner join "person_user" on "person_user"."personGroupId" = "person"."personGroupId"
      and "person_user"."sharedById" = "person"."ownerId"
      and "person_user"."sharedWithId" = $2
    where
      "person"."personGroupId" = "person_group"."id"
      and "person"."thumbnailPath" != $3
    limit
      $4
  ) as "sharedThumbnailPath"
from
  "person_group"
where
  "person_group"."id" = $5

-- PersonRepository.getByName
select
  "person".*,
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "person_user"."sharedById",
          "person_user"."role",
          "other"."name",
          "other"."birthDate"
        from
          "person" as "other"
          inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
          and "person_user"."sharedById" = "other"."ownerId"
          and "person_user"."sharedWithId" = $1
        where
          "other"."personGroupId" = "person"."personGroupId"
      ) as agg
  ) as "otherPeople",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt",
          "person_user"."role"
        from
          "person_user"
          inner join "user" on "user"."id" = "person_user"."sharedById"
          and "user"."deletedAt" is null
        where
          "person_user"."personGroupId" = "person"."personGroupId"
          and "person_user"."sharedWithId" = $2
        order by
          "user"."name"
      ) as agg
  ) as "sharedBy",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "id",
          "name",
          "email",
          "avatarColor",
          "profileImagePath",
          "profileChangedAt",
          "person_user"."role"
        from
          "person_user"
          inner join "user" on "user"."id" = "person_user"."sharedWithId"
          and "user"."deletedAt" is null
        where
          "person_user"."personGroupId" = "person"."personGroupId"
          and "person_user"."sharedById" = $3
        order by
          "user"."name"
      ) as agg
  ) as "sharedWith"
from
  "person"
where
  "person"."ownerId" = $4
  and f_unaccent ("person"."name") %> (
    select
      f_unaccent ($5)
    where
      set_config('pg_trgm.word_similarity_threshold', '0.5', true) is not null
  )
order by
  f_unaccent ("person"."name") <->>> f_unaccent ($6)
limit
  $7

-- PersonRepository.getDistinctNames
select distinct
  on (lower("person"."name")) "person"."personGroupId",
  "person"."name"
from
  "person"
where
  (
    "person"."ownerId" = $1
    and "person"."name" != $2
  )

-- PersonRepository.getStatistics
select
  count(distinct "visible"."assetId") as "assets"
from
  (
    select
      "asset_face"."assetId"
    from
      "asset_face"
    where
      "asset_face"."deletedAt" is null
      and "asset_face"."isVisible" is true
      and "asset_face"."assetVisibility" = 'timeline'
      and "asset_face"."assetDeletedAt" is null
      and "asset_face"."personGroupId" = $1
      and "asset_face"."ownerId" = any ($2::uuid[])
    union all
    select
      "asset_face"."assetId"
    from
      "asset_face"
    where
      "asset_face"."deletedAt" is null
      and "asset_face"."isVisible" is true
      and "asset_face"."assetVisibility" = 'timeline'
      and "asset_face"."assetDeletedAt" is null
      and "asset_face"."personGroupId" = $3
      and not "asset_face"."ownerId" = any ($4::uuid[])
      and exists (
        select
          1 as "exists"
        from
          "album_asset"
          inner join "album" on "album"."id" = "album_asset"."albumId"
          and "album"."deletedAt" is null
          inner join "album_user" on "album_user"."albumId" = "album"."id"
          and "album_user"."userId" = $5
        where
          "album_asset"."assetId" = "asset_face"."assetId"
      )
  ) as "visible"

-- PersonRepository.createGroup
insert into
  "person_group" ("clusterGroupId")
select
  "user"."clusterGroupId"
from
  "user"
where
  "user"."id" = $1
returning
  *

-- PersonRepository.reassignCluster
begin
update "person_group"
set
  "clusterGroupId" = $1
where
  "person_group"."id" in (
    select
      "person"."personGroupId"
    from
      "person"
    where
      "person"."ownerId" = $2
  )
  and not exists (
    select
      "person"."personGroupId"
    from
      "person"
    where
      "person"."personGroupId" = "person_group"."id"
      and "person"."ownerId" != $3
  )
with
  "shared" as (
    select distinct
      "person"."personGroupId" as "oldId"
    from
      "person"
    where
      "person"."ownerId" = $1
      and exists (
        select
          "other"."personGroupId"
        from
          "person" as "other"
        where
          "other"."personGroupId" = "person"."personGroupId"
          and "other"."ownerId" != $2
      )
  ),
  "mapping" as materialized (
    select
      "shared"."oldId",
      uuid_generate_v4 () as "newId"
    from
      "shared"
  ),
  "created" as (
    insert into
      "person_group" ("id", "clusterGroupId")
    select
      "mapping"."newId",
      $3 as "clusterGroupId"
    from
      "mapping"
  )
select
  "mapping"."oldId",
  "mapping"."newId"
from
  "mapping"
commit

-- PersonRepository.createGroups
insert into
  "person_group" (
    "0",
    "1",
    "2",
    "3",
    "4",
    "5",
    "6",
    "7",
    "8",
    "9",
    "10",
    "11",
    "12",
    "13",
    "14",
    "15",
    "16",
    "17",
    "18",
    "19",
    "20",
    "21",
    "22",
    "23",
    "24",
    "25",
    "26",
    "27",
    "28",
    "29",
    "30",
    "31",
    "32",
    "33",
    "34",
    "35"
  )
values
  (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7,
    $8,
    $9,
    $10,
    $11,
    $12,
    $13,
    $14,
    $15,
    $16,
    $17,
    $18,
    $19,
    $20,
    $21,
    $22,
    $23,
    $24,
    $25,
    $26,
    $27,
    $28,
    $29,
    $30,
    $31,
    $32,
    $33,
    $34,
    $35,
    $36
  )
returning
  *

-- PersonRepository.refreshFaces
with
  "added_embeddings" as (
    insert into
      "face_search" ("faceId", "embedding")
    values
      ($1, $2)
  )
select
from
  (
    select
      1
  ) as "dummy"

-- PersonRepository.updateForWritableOwners
update "person"
set
  "name" = $1
where
  "person"."personGroupId" = $2
  and "person"."ownerId" in (
    select
      "person_user"."sharedById"
    from
      "person_user"
    where
      "person_user"."personGroupId" = $3
      and "person_user"."sharedWithId" = $4
      and "person_user"."role" in ($5, $6)
  )

-- PersonRepository.getFacesByIds
select
  "asset_face".*,
  (
    select
      to_json(obj)
    from
      (
        select
          "person".*,
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "person_user"."sharedById",
                  "person_user"."role",
                  "other"."name",
                  "other"."birthDate"
                from
                  "person" as "other"
                  inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
                  and "person_user"."sharedById" = "other"."ownerId"
                  and "person_user"."sharedWithId" = $1
                where
                  "other"."personGroupId" = "person"."personGroupId"
              ) as agg
          ) as "otherPeople",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedById"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedWithId" = $2
                order by
                  "user"."name"
              ) as agg
          ) as "sharedBy",
          (
            select
              coalesce(json_agg(agg), '[]')
            from
              (
                select
                  "id",
                  "name",
                  "email",
                  "avatarColor",
                  "profileImagePath",
                  "profileChangedAt",
                  "person_user"."role"
                from
                  "person_user"
                  inner join "user" on "user"."id" = "person_user"."sharedWithId"
                  and "user"."deletedAt" is null
                where
                  "person_user"."personGroupId" = "person"."personGroupId"
                  and "person_user"."sharedById" = $3
                order by
                  "user"."name"
              ) as agg
          ) as "sharedWith"
        from
          "person"
        where
          "person"."personGroupId" = "asset_face"."personGroupId"
          and "person"."ownerId" = $4
      ) as obj
  ) as "person"
from
  "asset_face"
where
  "asset_face"."assetId" in ($5)
  and "asset_face"."personGroupId" in ($6)
  and "asset_face"."deletedAt" is null

-- PersonRepository.getRandomFace
select
  "asset_face".*
from
  "asset_face"
where
  "asset_face"."personGroupId" = $1
  and "asset_face"."deletedAt" is null
  and "asset_face"."isVisible" is true

-- PersonRepository.getLatestFaceDate
select
  max("asset_job_status"."facesRecognizedAt")::text as "latestDate"
from
  "asset_job_status"

-- PersonRepository.deleteAssetFace
delete from "asset_face"
where
  "asset_face"."id" = $1

-- PersonRepository.softDeleteAssetFaces
update "asset_face"
set
  "deletedAt" = $1
where
  "asset_face"."id" = $2

-- PersonRepository.getForFeatureFaceUpdate
select
  "asset_face"."id"
from
  "asset_face"
  inner join "asset" on "asset"."id" = "asset_face"."assetId"
  and "asset"."isOffline" = $1
where
  "asset_face"."assetId" = $2
  and "asset_face"."personGroupId" = $3
  and "asset_face"."deletedAt" is null

-- PersonRepository.getForMergePerson
select
  "person".*
from
  "person"
where
  "person"."personGroupId" in ($1)