-- @query people_photos
-- @db media 11.0
-- @db localContent 32.0
-- @param entity_ids TEXT
-- @with and_mode
-- @param entity_count INT
-- @end-with
SELECT DISTINCT cloud.driveItemId, local.FileSystemID AS fileSystemId
FROM recognized_entity_lookup AS rel
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 local.DriveItemID = cloud.driveItemId AND local.Tombstoned = 0
    AND local.rowid = (
        SELECT MIN(l2.rowid) FROM localContent.ItemMetadata l2
        WHERE l2.DriveItemID = cloud.driveItemId AND l2.Tombstoned = 0
    )
WHERE rel.entityId IN (SELECT value FROM json_each(?entity_ids))
  AND cloud.isTombstoned = 0
  AND drive.libraryType = 'Gallery'
  AND cloud.takenDateTime IS NOT NULL
-- @with and_mode
GROUP BY cloud.driveItemId
HAVING COUNT(DISTINCT rel.entityId) = ?entity_count
-- @end-with