-- @query thumbnail_resolve
-- @db thumbnailCache 9.0
-- @param fs_ids TEXT
-- @param di_ids TEXT
-- @with album_alias
-- @end-with
SELECT
    lower(hex(tm.fileSystemId)) AS fileSystemId,
    tm.driveItemId,
    tm.id,
    tm.boxSize,
    tm.imageModTime,
    tm.cTag
FROM thumbnailCache.thumbnail_metadata tm
WHERE tm.fileSystemId IS NOT NULL
  AND lower(hex(tm.fileSystemId)) IN (SELECT value FROM json_each(?fs_ids))
  -- @with album_alias
  AND 0
  -- @end-with
UNION ALL
SELECT
    lower(hex(tm.fileSystemId)) AS fileSystemId,
    tm.driveItemId,
    tm.id,
    tm.boxSize,
    tm.imageModTime,
    tm.cTag
FROM thumbnailCache.thumbnail_metadata tm
WHERE tm.driveItemId IN (SELECT value FROM json_each(?di_ids))
-- @with album_alias
UNION ALL
SELECT
    NULL AS fileSystemId,
    ta.aliasDriveItemId AS driveItemId,
    tm.id,
    tm.boxSize,
    tm.imageModTime,
    tm.cTag
FROM thumbnailCache.thumbnail_aliases ta
INNER JOIN thumbnailCache.thumbnail_metadata tm
    ON tm.driveItemId = ta.sourceDriveItemId
WHERE ta.aliasDriveItemId IN (SELECT value FROM json_each(?di_ids))
-- @end-with