-- @query people_gallery_photos_v2
-- @db media 22.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,
    local.FileSystemID AS fileSystemId,
    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,
    (SELECT json_extract(de.value, '$.id')
       FROM json_each(ext.detectedEntities) AS de
       WHERE json_extract(de.value, '$.recognizedEntity.id') = rel.entityId
       LIMIT 1) AS detectedEntityId
FROM media.recognized_entities AS re
CROSS JOIN recognized_entity_lookup AS rel ON rel.entityId = re.entityId
CROSS 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
LEFT JOIN media.extended_media_properties AS ext ON ext.driveItemId = cloud.driveItemId
WHERE re.primaryEntityId 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 re.primaryEntityId) = ?entity_count
-- @end-with
ORDER BY cloud.takenDateTime DESC, cloud.driveItemId ASC