-- @query details_cloud_metadata
-- @db media 11.0
-- @param driveItemId TEXT
SELECT
    mp.size                                                    AS size,
    json_extract(mp.fileSystemInfo, '$.lastModifiedDateTime')  AS lastModified,
    json_extract(mp.fileSystemInfo, '$.createdDateTime')       AS uploaded,
    COALESCE(
        json_extract(mp.image, '$.width'),
        json_extract(mp.video, '$.width'),
        lmm.Width
    )                                                          AS width,
    COALESCE(
        json_extract(mp.image, '$.height'),
        json_extract(mp.video, '$.height'),
        lmm.Height
    )                                                          AS height,
    COALESCE(mp.description, lmm.Description)                  AS description,
    COALESCE(json_extract(mp.photo, '$.cameraMake'),  lmm.CameraMake)          AS cameraMake,
    COALESCE(json_extract(mp.photo, '$.cameraModel'), lmm.CameraModel)         AS cameraModel,
    COALESCE(json_extract(mp.photo, '$.fNumber'),     lmm.fNumber)             AS fNumber,
    COALESCE(json_extract(mp.photo, '$.exposureNumerator'),   lmm.ExposureNumerator)    AS exposureNumerator,
    COALESCE(json_extract(mp.photo, '$.exposureDenominator'), lmm.ExposureDenominator)  AS exposureDenominator,
    COALESCE(json_extract(mp.photo, '$.focalLength'), lmm.FocalLength)         AS focalLength,
    COALESCE(json_extract(mp.photo, '$.iso'),         lmm.ISO)                 AS iso,
    parent.path                                                AS parentPath,
    COALESCE(
        (SELECT json_group_array(mtl.name)
         FROM media.media_tag_lookup mtl
         WHERE mtl.driveItemId = mp.driveItemId),
        '[]'
    )                                                          AS tagsJson,
    CASE WHEN ml.webScope IS NOT NULL AND ml.webScope != 'MySite' THEN 1 ELSE 0 END AS isShared
FROM media.media_properties  mp
INNER JOIN media.media_folders   parent ON parent.driveItemId = mp.parentDriveItemId
INNER JOIN media.media_libraries ml     ON ml.driveId = mp.driveId
LEFT JOIN localContent.ItemMetadata  lim ON lim.DriveItemID  = mp.driveItemId
LEFT JOIN localContent.MediaMetadata lmm ON lmm.FileSystemID = lim.FileSystemID
WHERE mp.driveItemId = ?driveItemId
  AND mp.isTombstoned = 0
LIMIT 1