-- @query people_thumbnails_batch
-- Batch people face thumbnail resolution. Resolves multiple entities to their
-- best thumbnail IDs in a single query. Prefers the representative item
-- (CoverPhotoId from recognizedEntities) when available locally, falls back
-- to any photo containing the entity.
-- Used by: people-data-router.js (pre-cache on loadPeopleData)
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
--
-- @param entity_ids TEXT              -- JSON array of entity ID strings

SELECT
    re.entityId AS entity_id,
    COALESCE(
        -- Prefer representative item thumbnail (CoverPhotoId)
        MIN(COALESCE(repTmFs.id, repTmDi.id)),
        -- Fallback: any photo with this entity
        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
-- Representative item joins
-- repCloud gates on isTombstoned so a deleted cover photo never leaks through.
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