-- @query gallery_cloud_enrich
-- @db media 11.0
-- @db localContent 32.0
-- @param item_ids TEXT
SELECT
    cloud.driveItemId,
    COALESCE(cloud.name, local.Name) AS name,
    cloud.takenDateTime,
    COALESCE(json_extract(cloud.image, '$.width'),  json_extract(cloud.video, '$.width'),  exif.Width)  AS width,
    COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), exif.Height) AS height,
    (SELECT CAST(json_extract(value, '$.boundingBoxX') AS INTEGER)
        FROM json_each(json_extract(cloud.image, '$.cropRegions'))
        WHERE json_extract(value, '$.aspectRatio') = '1' LIMIT 1) AS faceCropX,
    (SELECT CAST(json_extract(value, '$.boundingBoxY') AS INTEGER)
        FROM json_each(json_extract(cloud.image, '$.cropRegions'))
        WHERE json_extract(value, '$.aspectRatio') = '1' LIMIT 1) AS faceCropY,
    (SELECT CAST(json_extract(value, '$.boundingBoxWidth') AS INTEGER)
        FROM json_each(json_extract(cloud.image, '$.cropRegions'))
        WHERE json_extract(value, '$.aspectRatio') = '1' LIMIT 1) AS faceCropW,
    (SELECT CAST(json_extract(value, '$.boundingBoxHeight') AS INTEGER)
        FROM json_each(json_extract(cloud.image, '$.cropRegions'))
        WHERE json_extract(value, '$.aspectRatio') = '1' LIMIT 1) AS faceCropH,
    local.FileSystemID AS fileSystemId,
    local.LocalID AS localId,
    cloud.driveId,
    cloud.originalItemUniqueId,
    CASE WHEN COALESCE(local.MediaType, CASE WHEN cloud.video IS NOT NULL THEN 2 ELSE 1 END) = 2
         THEN 1 ELSE 0 END AS isVideo,
    COALESCE(json_extract(cloud.video, '$.duration'), exif.Duration) AS duration,
    CASE WHEN EXISTS (
        SELECT 1 FROM media.media_favorites mf
        WHERE mf.listItemUniqueId = cloud.listItemUniqueId
          AND mf.siteId = drive.siteId
          AND mf.listId = drive.listId
    ) THEN 1 ELSE 0 END AS isFavorite,
    MAX(COALESCE(json_extract(cloud.fileSystemInfo, '$.lastModifiedDateTime'), 0),
        COALESCE(strftime('%Y-%m-%dT%H:%M:%SZ', local.ModTime, 'unixepoch'), 0)) AS lastModifiedDateTime,
    COALESCE(json_extract(cloud.fileSystemInfo, '$.createdDateTime'),
             strftime('%Y-%m-%dT%H:%M:%SZ', local.CreatedTime, 'unixepoch')) AS createdDateTime,
    COALESCE(cloud.size, local.Size) AS size,
    cloud.cTag AS cTag,
    local.ModTime AS localModTimeSec,
    fod.PlaceholderState AS placeholderState,
    0 AS isLocalOnly,
    0 AS isNotInCloud,
    CASE WHEN COALESCE(drive.webScope, '') != 'MySite' THEN 1 ELSE 0 END AS isShared,
    0 AS isFromICloud,
    cloud.libraryTypeId
FROM media.media_properties AS cloud
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)
LEFT JOIN localContent.VisibleMediaMetadata AS exif
    ON exif.FileSystemID = local.FileSystemID
LEFT JOIN localContent.FileOnDemandMetadata AS fod
    ON fod.FileSystemID = local.FileSystemID
LEFT JOIN media.media_libraries AS drive
    ON cloud.driveId = drive.driveId
WHERE cloud.driveItemId IN (SELECT value FROM json_each(?item_ids))
  AND cloud.isTombstoned = 0