-- @query details_people
-- @db media 22.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param item_id TEXT
SELECT
    re.primaryEntityId AS entity_id,
    rel.boundingBoxLeft   AS bbox_left,
    rel.boundingBoxTop    AS bbox_top,
    rel.boundingBoxWidth  AS bbox_w,
    rel.boundingBoxHeight AS bbox_h,
    COALESCE(json_extract(cloud.image, '$.width'),
             json_extract(cloud.video, '$.width'))  AS photo_w,
    COALESCE(json_extract(cloud.image, '$.height'),
             json_extract(cloud.video, '$.height')) AS photo_h,
    COALESCE(tmFs.id, tmDi.id) AS tid,
    COALESCE(tmFs.boxSize, tmDi.boxSize) AS tid_box,
    COALESCE(repTmFs.id, repTmDi.id) AS rep_tid,
    COALESCE(repTmFs.boxSize, repTmDi.boxSize) AS rep_tid_box,
    rePrimary.representativeItemId AS rep_drive_item_id,
    (SELECT relRep.boundingBoxLeft FROM recognized_entity_lookup AS relRep
       JOIN media.recognized_entities AS reRep ON reRep.entityId = relRep.entityId
      WHERE relRep.driveItemId = rePrimary.representativeItemId
        AND reRep.primaryEntityId = re.primaryEntityId LIMIT 1) AS rep_face_l,
    (SELECT relRep.boundingBoxTop FROM recognized_entity_lookup AS relRep
       JOIN media.recognized_entities AS reRep ON reRep.entityId = relRep.entityId
      WHERE relRep.driveItemId = rePrimary.representativeItemId
        AND reRep.primaryEntityId = re.primaryEntityId LIMIT 1) AS rep_face_t,
    (SELECT relRep.boundingBoxWidth FROM recognized_entity_lookup AS relRep
       JOIN media.recognized_entities AS reRep ON reRep.entityId = relRep.entityId
      WHERE relRep.driveItemId = rePrimary.representativeItemId
        AND reRep.primaryEntityId = re.primaryEntityId LIMIT 1) AS rep_face_w,
    (SELECT relRep.boundingBoxHeight FROM recognized_entity_lookup AS relRep
       JOIN media.recognized_entities AS reRep ON reRep.entityId = relRep.entityId
      WHERE relRep.driveItemId = rePrimary.representativeItemId
        AND reRep.primaryEntityId = re.primaryEntityId LIMIT 1) AS rep_face_h,
    COALESCE(json_extract(repCloud.image, '$.width'),
             json_extract(repCloud.video, '$.width'))  AS rep_photo_w,
    COALESCE(json_extract(repCloud.image, '$.height'),
             json_extract(repCloud.video, '$.height')) AS rep_photo_h,
    rePrimary.displayName AS display_name
FROM recognized_entity_lookup AS rel
JOIN media.recognized_entities AS re ON re.entityId = rel.entityId
LEFT JOIN media.media_properties AS cloud ON cloud.driveItemId = rel.driveItemId
LEFT JOIN localContent.ItemMetadata AS local
    ON local.DriveItemID = rel.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
   AND tmFs.boxSize = (
       SELECT MAX(t2.boxSize) FROM thumbnailCache.thumbnail_metadata AS t2
       WHERE t2.fileSystemId = local.FileSystemID
   )
LEFT JOIN thumbnailCache.thumbnail_metadata AS tmDi
    ON tmDi.driveItemId = rel.driveItemId
   AND tmDi.boxSize = (
       SELECT MAX(t2.boxSize) FROM thumbnailCache.thumbnail_metadata AS t2
       WHERE t2.driveItemId = rel.driveItemId
   )
LEFT JOIN media.recognized_entities AS rePrimary
    ON rePrimary.entityId = re.primaryEntityId
LEFT JOIN media.media_properties AS repCloud
    ON repCloud.driveItemId = rePrimary.representativeItemId
LEFT JOIN localContent.ItemMetadata AS repLocal
    ON repLocal.DriveItemID = rePrimary.representativeItemId
   AND COALESCE(repLocal.Tombstoned, 0) = 0
LEFT JOIN thumbnailCache.thumbnail_metadata AS repTmFs
    ON repLocal.FileSystemID IS NOT NULL AND repTmFs.fileSystemId = repLocal.FileSystemID
   AND repTmFs.boxSize = (
       SELECT MAX(t2.boxSize) FROM thumbnailCache.thumbnail_metadata AS t2
       WHERE t2.fileSystemId = repLocal.FileSystemID
   )
LEFT JOIN thumbnailCache.thumbnail_metadata AS repTmDi
    ON repTmDi.driveItemId = rePrimary.representativeItemId
   AND repTmDi.boxSize = (
       SELECT MAX(t2.boxSize) FROM thumbnailCache.thumbnail_metadata AS t2
       WHERE t2.driveItemId = rePrimary.representativeItemId
   )
WHERE rel.driveItemId = ?item_id
   OR rel.driveItemId IN (
       SELECT DriveItemID FROM localContent.ItemMetadata
       WHERE FileSystemID = ?item_id AND COALESCE(Tombstoned, 0) = 0
   )