-- @query people_thumbnails_batch
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param entity_ids TEXT
SELECT
    re.entityId AS entity_id,
    COALESCE(
        MIN(COALESCE(repTmFs.id, repTmDi.id)),
        MIN(COALESCE(tmFs.id, tmDi.id))
    ) AS tid
FROM media.recognized_entities AS re
JOIN recognized_entity_lookup AS rel ON rel.entityId = re.entityId
JOIN media.media_properties AS cloud ON rel.driveItemId = cloud.driveItemId
LEFT JOIN localContent.ItemMetadata AS local
    ON cloud.driveItemId = local.DriveItemID AND COALESCE(local.Tombstoned, 0) = 0
LEFT JOIN thumbnailCache.thumbnail_metadata AS tmFs
    ON local.FileSystemID IS NOT NULL AND tmFs.fileSystemId = local.FileSystemID
LEFT JOIN thumbnailCache.thumbnail_metadata AS tmDi
    ON local.FileSystemID IS NULL AND tmDi.driveItemId = cloud.driveItemId
LEFT JOIN media.media_properties AS repCloud
    ON re.representativeItemId IS NOT NULL
    AND repCloud.driveItemId = re.representativeItemId AND repCloud.isTombstoned = 0
LEFT JOIN localContent.ItemMetadata AS repLocal
    ON repCloud.driveItemId IS NOT NULL
    AND repLocal.DriveItemID = repCloud.driveItemId AND COALESCE(repLocal.Tombstoned, 0) = 0
LEFT JOIN thumbnailCache.thumbnail_metadata AS repTmFs
    ON repLocal.FileSystemID IS NOT NULL AND repTmFs.fileSystemId = repLocal.FileSystemID
LEFT JOIN thumbnailCache.thumbnail_metadata AS repTmDi
    ON repCloud.driveItemId IS NOT NULL AND repLocal.FileSystemID IS NULL
    AND repTmDi.driveItemId = repCloud.driveItemId
WHERE re.entityId IN (SELECT value FROM json_each(?entity_ids))
  AND cloud.isTombstoned = 0
  AND COALESCE(tmFs.id, tmDi.id, repTmFs.id, repTmDi.id) IS NOT NULL
GROUP BY re.entityId