-- NOTE: This file is auto generated by ./sql-generator

-- AssetJobRepository.getForSearchDuplicatesJob
select
  "id",
  "type",
  "ownerId",
  "duplicateId",
  "stackId",
  "visibility",
  "smart_search"."embedding"
from
  "assets"
  left join "smart_search" on "assets"."id" = "smart_search"."assetId"
where
  "assets"."id" = $1::uuid
limit
  $2

-- AssetJobRepository.getForSidecarWriteJob
select
  "id",
  "sidecarPath",
  "originalPath",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "tags"."value"
        from
          "tags"
          inner join "tag_asset" on "tags"."id" = "tag_asset"."tagsId"
        where
          "assets"."id" = "tag_asset"."assetsId"
      ) as agg
  ) as "tags"
from
  "assets"
where
  "assets"."id" = $1::uuid
limit
  $2

-- AssetJobRepository.streamForThumbnailJob
select
  "assets"."id",
  "assets"."thumbhash",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_files"."id",
          "asset_files"."path",
          "asset_files"."type"
        from
          "asset_files"
        where
          "asset_files"."assetId" = "assets"."id"
      ) as agg
  ) as "files"
from
  "assets"
  inner join "asset_job_status" on "asset_job_status"."assetId" = "assets"."id"
where
  "assets"."deletedAt" is null
  and "assets"."visibility" != $1
  and (
    "asset_job_status"."previewAt" is null
    or "asset_job_status"."thumbnailAt" is null
    or "assets"."thumbhash" is null
  )

-- AssetJobRepository.getForMigrationJob
select
  "assets"."id",
  "assets"."ownerId",
  "assets"."encodedVideoPath",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_files"."id",
          "asset_files"."path",
          "asset_files"."type"
        from
          "asset_files"
        where
          "asset_files"."assetId" = "assets"."id"
      ) as agg
  ) as "files"
from
  "assets"
where
  "assets"."id" = $1

-- AssetJobRepository.getForGenerateThumbnailJob
select
  "assets"."id",
  "assets"."visibility",
  "assets"."originalFileName",
  "assets"."originalPath",
  "assets"."ownerId",
  "assets"."thumbhash",
  "assets"."type",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_files"."id",
          "asset_files"."path",
          "asset_files"."type"
        from
          "asset_files"
        where
          "asset_files"."assetId" = "assets"."id"
      ) as agg
  ) as "files",
  to_json("exif") as "exifInfo"
from
  "assets"
  inner join "exif" on "assets"."id" = "exif"."assetId"
where
  "assets"."id" = $1

-- AssetJobRepository.getForMetadataExtraction
select
  "assets"."id",
  "assets"."checksum",
  "assets"."deviceAssetId",
  "assets"."deviceId",
  "assets"."fileCreatedAt",
  "assets"."fileModifiedAt",
  "assets"."isExternal",
  "assets"."visibility",
  "assets"."libraryId",
  "assets"."livePhotoVideoId",
  "assets"."localDateTime",
  "assets"."originalFileName",
  "assets"."originalPath",
  "assets"."ownerId",
  "assets"."sidecarPath",
  "assets"."type",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_faces".*
        from
          "asset_faces"
        where
          "asset_faces"."assetId" = "assets"."id"
          and "asset_faces"."deletedAt" is null
      ) as agg
  ) as "faces"
from
  "assets"
where
  "assets"."id" = $1

-- AssetJobRepository.getAlbumThumbnailFiles
select
  "asset_files"."id",
  "asset_files"."path",
  "asset_files"."type"
from
  "asset_files"
where
  "asset_files"."assetId" = $1
  and "asset_files"."type" = $2

-- AssetJobRepository.streamForSearchDuplicates
select
  "assets"."id"
from
  "assets"
  inner join "smart_search" on "assets"."id" = "smart_search"."assetId"
  inner join "asset_job_status" as "job_status" on "job_status"."assetId" = "assets"."id"
where
  "assets"."deletedAt" is null
  and "assets"."visibility" in ('archive', 'timeline')
  and "job_status"."duplicatesDetectedAt" is null

-- AssetJobRepository.streamForEncodeClip
select
  "assets"."id"
from
  "assets"
  inner join "asset_job_status" as "job_status" on "assetId" = "assets"."id"
where
  "assets"."visibility" != $1
  and "assets"."deletedAt" is null
  and "job_status"."previewAt" is not null
  and not exists (
    select
    from
      "smart_search"
    where
      "assetId" = "assets"."id"
  )

-- AssetJobRepository.getForClipEncoding
select
  "assets"."id",
  "assets"."visibility",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_files"."id",
          "asset_files"."path",
          "asset_files"."type"
        from
          "asset_files"
        where
          "asset_files"."assetId" = "assets"."id"
          and "asset_files"."type" = $1
      ) as agg
  ) as "files"
from
  "assets"
where
  "assets"."id" = $2

-- AssetJobRepository.getForDetectFacesJob
select
  "assets"."id",
  "assets"."visibility",
  to_json("exif") as "exifInfo",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_faces".*
        from
          "asset_faces"
        where
          "asset_faces"."assetId" = "assets"."id"
      ) as agg
  ) as "faces",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_files"."id",
          "asset_files"."path",
          "asset_files"."type"
        from
          "asset_files"
        where
          "asset_files"."assetId" = "assets"."id"
          and "asset_files"."type" = $1
      ) as agg
  ) as "files"
from
  "assets"
  inner join "exif" on "assets"."id" = "exif"."assetId"
where
  "assets"."id" = $2

-- AssetJobRepository.getForSyncAssets
select
  "assets"."id",
  "assets"."isOffline",
  "assets"."libraryId",
  "assets"."originalPath",
  "assets"."status",
  "assets"."fileModifiedAt"
from
  "assets"
where
  "assets"."id" = any ($1::uuid[])

-- AssetJobRepository.getForAssetDeletion
select
  "assets"."id",
  "assets"."visibility",
  "assets"."libraryId",
  "assets"."ownerId",
  "assets"."livePhotoVideoId",
  "assets"."sidecarPath",
  "assets"."encodedVideoPath",
  "assets"."originalPath",
  to_json("exif") as "exifInfo",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_faces".*,
          "person" as "person"
        from
          "asset_faces"
          left join lateral (
            select
              "person".*
            from
              "person"
            where
              "asset_faces"."personId" = "person"."id"
          ) as "person" on true
        where
          "asset_faces"."assetId" = "assets"."id"
          and "asset_faces"."deletedAt" is null
      ) as agg
  ) as "faces",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_files"."id",
          "asset_files"."path",
          "asset_files"."type"
        from
          "asset_files"
        where
          "asset_files"."assetId" = "assets"."id"
      ) as agg
  ) as "files",
  to_json("stacked_assets") as "stack"
from
  "assets"
  left join "exif" on "assets"."id" = "exif"."assetId"
  left join "asset_stack" on "asset_stack"."id" = "assets"."stackId"
  left join lateral (
    select
      "asset_stack"."id",
      "asset_stack"."primaryAssetId",
      array_agg("stacked") as "assets"
    from
      "assets" as "stacked"
    where
      "stacked"."deletedAt" is not null
      and "stacked"."visibility" = $1
      and "stacked"."stackId" = "asset_stack"."id"
    group by
      "asset_stack"."id"
  ) as "stacked_assets" on "asset_stack"."id" is not null
where
  "assets"."id" = $2

-- AssetJobRepository.streamForVideoConversion
select
  "assets"."id"
from
  "assets"
where
  "assets"."type" = $1
  and (
    "assets"."encodedVideoPath" is null
    or "assets"."encodedVideoPath" = $2
  )
  and "assets"."visibility" != $3
  and "assets"."deletedAt" is null

-- AssetJobRepository.getForVideoConversion
select
  "assets"."id",
  "assets"."ownerId",
  "assets"."originalPath",
  "assets"."encodedVideoPath"
from
  "assets"
where
  "assets"."id" = $1
  and "assets"."type" = $2

-- AssetJobRepository.streamForMetadataExtraction
select
  "assets"."id"
from
  "assets"
  left join "asset_job_status" on "asset_job_status"."assetId" = "assets"."id"
where
  (
    "asset_job_status"."metadataExtractedAt" is null
    or "asset_job_status"."assetId" is null
  )
  and "assets"."visibility" != $1
  and "assets"."deletedAt" is null

-- AssetJobRepository.getForStorageTemplateJob
select
  "assets"."id",
  "assets"."ownerId",
  "assets"."type",
  "assets"."checksum",
  "assets"."originalPath",
  "assets"."isExternal",
  "assets"."sidecarPath",
  "assets"."originalFileName",
  "assets"."livePhotoVideoId",
  "assets"."fileCreatedAt",
  "exif"."timeZone",
  "exif"."fileSizeInByte"
from
  "assets"
  inner join "exif" on "assets"."id" = "exif"."assetId"
where
  "assets"."deletedAt" is null
  and "assets"."id" = $1

-- AssetJobRepository.streamForStorageTemplateJob
select
  "assets"."id",
  "assets"."ownerId",
  "assets"."type",
  "assets"."checksum",
  "assets"."originalPath",
  "assets"."isExternal",
  "assets"."sidecarPath",
  "assets"."originalFileName",
  "assets"."livePhotoVideoId",
  "assets"."fileCreatedAt",
  "exif"."timeZone",
  "exif"."fileSizeInByte"
from
  "assets"
  inner join "exif" on "assets"."id" = "exif"."assetId"
where
  "assets"."deletedAt" is null

-- AssetJobRepository.streamForDeletedJob
select
  "id",
  "isOffline"
from
  "assets"
where
  "assets"."deletedAt" <= $1

-- AssetJobRepository.streamForSidecar
select
  "assets"."id"
from
  "assets"
where
  (
    "assets"."sidecarPath" = $1
    or "assets"."sidecarPath" is null
  )
  and "assets"."visibility" != $2

-- AssetJobRepository.streamForDetectFacesJob
select
  "assets"."id"
from
  "assets"
  inner join "asset_job_status" as "job_status" on "assetId" = "assets"."id"
where
  "assets"."visibility" != $1
  and "assets"."deletedAt" is null
  and "job_status"."previewAt" is not null
  and "job_status"."facesRecognizedAt" is null
order by
  "assets"."createdAt" desc

-- AssetJobRepository.streamForMigrationJob
select
  "id"
from
  "assets"
where
  "assets"."deletedAt" is null
