-- @query people_gallery_photos
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param entity_ids TEXT
-- @with and_mode
-- @param entity_count INT
-- @end-with
SELECT
    COALESCE(
        (SELECT MIN(tm.id) FROM thumbnailCache.thumbnail_metadata tm WHERE tm.fileSystemId = local.FileSystemID),
        (SELECT MIN(tm.id) FROM thumbnailCache.thumbnail_metadata tm WHERE local.FileSystemID IS NULL AND tm.driveItemId = cloud.driveItemId)
    ) AS thumbnailId,
    cloud.takenDateTime,
    cloud.driveItemId,
    cloud.driveId,
    COALESCE(cloud.name, local.Name) AS name,
    CAST(json_extract(cloud.image, '$.width') AS INTEGER) AS width,
    CAST(json_extract(cloud.image, '$.height') AS INTEGER) AS height
FROM recognized_entity_lookup AS rel
JOIN media.media_properties AS cloud ON rel.driveItemId = cloud.driveItemId
JOIN media.media_libraries AS drive ON cloud.driveId = drive.driveId
LEFT JOIN localContent.ItemMetadata AS local ON cloud.driveItemId = local.DriveItemID AND local.Tombstoned = 0
WHERE rel.entityId IN (SELECT value FROM json_each(?entity_ids))
  AND cloud.photo IS NOT NULL AND cloud.video IS NULL
  AND cloud.isTombstoned = 0 AND drive.libraryType = 'Gallery'
  AND cloud.takenDateTime IS NOT NULL
GROUP BY cloud.driveItemId
-- @with and_mode
HAVING COUNT(DISTINCT rel.entityId) = ?entity_count
-- @end-with