-- @query people_gallery_photos_by_ids
-- @db media 22.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param drive_item_ids TEXT
-- @param entity_id TEXT
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') IN (
           SELECT entityId FROM media.recognized_entities WHERE primaryEntityId = ?entity_id
       )
       LIMIT 1) AS detectedEntityId
FROM media.media_properties AS cloud
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 cloud.driveItemId IN (SELECT value FROM json_each(?drive_item_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
ORDER BY cloud.takenDateTime DESC, cloud.driveItemId ASC