-- @query people_thumbnails_batch_v2
-- @db media 22.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param entity_ids TEXT
WITH
cover_resolved AS (
    SELECT
        re.entityId,
        mp.driveItemId AS resolved_item_id
    FROM media.recognized_entities AS re
    JOIN media.media_properties AS mp
        ON mp.driveId    = re.driveId
       AND mp.listItemId = re.coverPhotoId
       AND mp.isTombstoned = 0
    WHERE re.coverPhotoId IS NOT NULL
      AND (re.entityId IN (SELECT value FROM json_each(?entity_ids))
           OR re.primaryEntityId IN (SELECT value FROM json_each(?entity_ids)))
),
merge_members AS (
    SELECT
        rePrimary.entityId AS primary_id,
        rePrimary.entityId AS member_id,
        1 AS is_primary,
        COALESCE(crPrimary.resolved_item_id, rePrimary.representativeItemId) AS rep_item_id
    FROM media.recognized_entities AS rePrimary
    LEFT JOIN cover_resolved AS crPrimary
        ON crPrimary.entityId = rePrimary.entityId
    WHERE rePrimary.entityId = rePrimary.primaryEntityId
      AND rePrimary.entityId IN (SELECT value FROM json_each(?entity_ids))
    UNION ALL
    SELECT
        rePrimary.entityId AS primary_id,
        reChild.entityId AS member_id,
        0 AS is_primary,
        COALESCE(crChild.resolved_item_id, reChild.representativeItemId) AS rep_item_id
    FROM media.recognized_entities AS rePrimary
    JOIN media.recognized_entities AS reChild
        ON reChild.primaryEntityId = rePrimary.entityId
        AND reChild.entityId != rePrimary.entityId
    LEFT JOIN cover_resolved AS crChild
        ON crChild.entityId = reChild.entityId
    WHERE rePrimary.entityId = rePrimary.primaryEntityId
      AND rePrimary.entityId IN (SELECT value FROM json_each(?entity_ids))
),
primary_crop_inputs AS (
    SELECT
        rePrimary.entityId AS primary_id,
        COALESCE(crPrimary.resolved_item_id, rePrimary.representativeItemId) AS rep_item_id,
        CAST(json_extract(repCloud.image, '$.width') AS INTEGER) AS src_w,
        CAST(json_extract(repCloud.image, '$.height') AS INTEGER) AS src_h,
        rel.boundingBoxLeft AS raw_x,
        rel.boundingBoxTop AS raw_y,
        rel.boundingBoxWidth AS raw_w,
        rel.boundingBoxHeight AS raw_h
    FROM media.recognized_entities AS rePrimary
    LEFT JOIN cover_resolved AS crPrimary
        ON crPrimary.entityId = rePrimary.entityId
    JOIN recognized_entity_lookup AS rel
        ON rel.entityId = rePrimary.entityId
       AND rel.driveItemId = COALESCE(crPrimary.resolved_item_id, rePrimary.representativeItemId)
    JOIN media.media_properties AS repCloud
        ON repCloud.driveItemId = COALESCE(crPrimary.resolved_item_id, rePrimary.representativeItemId)
       AND repCloud.isTombstoned = 0
    WHERE rePrimary.entityId = rePrimary.primaryEntityId
      AND rePrimary.entityId IN (SELECT value FROM json_each(?entity_ids))
      AND COALESCE(crPrimary.resolved_item_id, rePrimary.representativeItemId) IS NOT NULL
      AND repCloud.image IS NOT NULL
),
primary_crop_boxed AS (
    SELECT
        primary_id,
        rep_item_id,
        src_w,
        src_h,
        CASE
            WHEN raw_w * src_h > raw_h * src_w THEN raw_h * 2
            ELSE raw_w * 2
        END AS box_size,
        raw_x + raw_w / 2 AS mid_x,
        raw_y + raw_h / 2 AS mid_y
    FROM primary_crop_inputs
    WHERE src_w > 0 AND src_h > 0
      AND raw_w > 0 AND raw_h > 0
),
primary_crop AS (
    SELECT
        primary_id,
        rep_item_id,
        max(0, mid_x - box_size / 2) AS vl,
        max(0, mid_y - box_size / 2) AS vt,
        min(box_size, src_w - max(0, mid_x - box_size / 2)) AS vw,
        min(box_size, src_h - max(0, mid_y - box_size / 2)) AS vh
    FROM primary_crop_boxed
)
SELECT
    mm.primary_id AS entity_id,
    COALESCE(
        MIN(repTmCrop.id),
        MIN(CASE WHEN mm.is_primary = 1 THEN COALESCE(repTmFs.id, repTmDi.id) END),
        MIN(CASE WHEN mm.is_primary = 0 THEN COALESCE(repTmFs.id, repTmDi.id) END),
        MIN(COALESCE(tmFs.id, tmDi.id))
    ) AS tid,
    CASE WHEN MIN(repTmCrop.id) IS NULL THEN MIN(pc.rep_item_id) END AS crop_rep_item_id,
    CASE WHEN MIN(repTmCrop.id) IS NULL THEN MIN(pc.vl) END AS crop_vl,
    CASE WHEN MIN(repTmCrop.id) IS NULL THEN MIN(pc.vt) END AS crop_vt,
    CASE WHEN MIN(repTmCrop.id) IS NULL THEN MIN(pc.vw) END AS crop_vw,
    CASE WHEN MIN(repTmCrop.id) IS NULL THEN MIN(pc.vh) END AS crop_vh
FROM merge_members AS mm
JOIN recognized_entity_lookup AS rel ON rel.entityId = mm.member_id
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 mm.rep_item_id IS NOT NULL
    AND repCloud.driveItemId = mm.rep_item_id 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
LEFT JOIN primary_crop AS pc ON pc.primary_id = mm.primary_id
LEFT JOIN thumbnailCache.thumbnail_metadata AS repTmCrop
    ON pc.rep_item_id IS NOT NULL
    AND repTmCrop.driveItemId = pc.rep_item_id || '_crop_' ||
        pc.vl || '_' || pc.vt || '_' || pc.vw || '_' || pc.vh
WHERE cloud.isTombstoned = 0
GROUP BY mm.primary_id
HAVING tid IS NOT NULL OR MIN(pc.rep_item_id) IS NOT NULL