-- @query people_thumbnails
-- People face thumbnail resolution. Resolves an entity to its best thumbnail ID
-- by joining entity -> cloud -> local -> thumbnailCache. Prefers fileSystemId
-- lookup, falls back to driveItemId.
-- Used by: people.js, shell-people.js (person card thumbnails)
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
--
-- @param entity_id TEXT

SELECT MIN(COALESCE(tmFs.id, tmDi.id)) AS tid
FROM recognized_entity_lookup AS rel
JOIN media.media_properties AS cloud ON rel.driveItemId = cloud.driveItemId
LEFT JOIN localContent.ItemMetadata AS local
    ON cloud.driveItemId = local.DriveItemID AND local.Tombstoned = 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
WHERE rel.entityId = ?entity_id
  AND COALESCE(tmFs.id, tmDi.id) IS NOT NULL
LIMIT 1