-- @query gallery_local_enrich
-- @db localContent 32.0
-- @db mediacache 1.0
-- @param drive_item_ids TEXT
-- @with by_local_id
-- @param local_ids TEXT
-- @end-with
SELECT
    local.DriveItemID AS driveItemId,
    local.Name AS name,
    exif.TakenDateTimeCoalesced AS takenDateTime,
    exif.Width AS width,
    exif.Height AS height,
    local.FileSystemID AS fileSystemId,
    local.LocalID AS localId,
    NULL AS driveId,
    CASE WHEN exif.MediaType = 2 THEN 1 ELSE 0 END AS isVideo,
    exif.Duration AS duration,
    CASE WHEN local.FavoritedDateTime IS NOT NULL THEN 1 ELSE 0 END AS isFavorite,
    local.ModTime AS modTimeSec,
    CASE WHEN exif.RegistrantAccountId IS NULL THEN 1 ELSE 0 END AS isLocalOnly,
    CASE WHEN mcd.FileSystemID IS NOT NULL THEN 1 ELSE 0 END AS isNotInCloud,
    COALESCE(fond.PlaceholderState, 0) AS placeholderState,
    CASE WHEN fond.OwnerCID IS NOT NULL AND wrf.OwnerCID IS NOT NULL AND fond.OwnerCID != wrf.OwnerCID THEN 1 ELSE 0 END AS isShared,
    CASE WHEN wrf.SyncRootType = 2 THEN 1 ELSE 0 END AS isFromICloud
FROM localContent.ItemMetadata AS local
INNER JOIN localContent.VisibleMediaMetadata AS exif ON local.FileSystemID = exif.FileSystemID
LEFT JOIN localContent.FileOnDemandMetadata AS fond ON fond.FileSystemID = local.FileSystemID
LEFT JOIN localContent.WatchedRootFolders AS wrf ON wrf.WatchedRootFolderID = local.WatchedRootFolderID
LEFT JOIN mediacache.LocalItemsMissingCloudData AS mcd ON mcd.FileSystemID = local.FileSystemID
WHERE local.DriveItemID IN (SELECT value FROM json_each(?drive_item_ids))
  AND local.Tombstoned = 0
-- @with by_local_id
UNION ALL
SELECT
    'lid:' || local.LocalID AS driveItemId,
    local.Name AS name,
    exif.TakenDateTimeCoalesced AS takenDateTime,
    exif.Width AS width,
    exif.Height AS height,
    local.FileSystemID AS fileSystemId,
    local.LocalID AS localId,
    NULL AS driveId,
    CASE WHEN exif.MediaType = 2 THEN 1 ELSE 0 END AS isVideo,
    exif.Duration AS duration,
    CASE WHEN local.FavoritedDateTime IS NOT NULL THEN 1 ELSE 0 END AS isFavorite,
    local.ModTime AS modTimeSec,
    CASE WHEN exif.RegistrantAccountId IS NULL THEN 1 ELSE 0 END AS isLocalOnly,
    CASE WHEN mcd.FileSystemID IS NOT NULL THEN 1 ELSE 0 END AS isNotInCloud,
    COALESCE(fond.PlaceholderState, 0) AS placeholderState,
    CASE WHEN fond.OwnerCID IS NOT NULL AND wrf.OwnerCID IS NOT NULL AND fond.OwnerCID != wrf.OwnerCID THEN 1 ELSE 0 END AS isShared,
    CASE WHEN wrf.SyncRootType = 2 THEN 1 ELSE 0 END AS isFromICloud
FROM localContent.ItemMetadata AS local
INNER JOIN localContent.VisibleMediaMetadata AS exif ON local.FileSystemID = exif.FileSystemID
LEFT JOIN localContent.FileOnDemandMetadata AS fond ON fond.FileSystemID = local.FileSystemID
LEFT JOIN localContent.WatchedRootFolders AS wrf ON wrf.WatchedRootFolderID = local.WatchedRootFolderID
LEFT JOIN mediacache.LocalItemsMissingCloudData AS mcd ON mcd.FileSystemID = local.FileSystemID
WHERE local.LocalID IN (SELECT value FROM json_each(?local_ids))
  AND local.Tombstoned = 0
-- @end-with