-- @query thumbnails_list_favorites
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param limit INT
-- @param offset INT
-- @sort by_taken_desc     mp.takenDateTime DESC,   mp.driveItemId DESC
-- @sort by_taken_asc      mp.takenDateTime ASC,    mp.driveItemId ASC
-- @sort by_favorited_desc mf.favoritedDateTime DESC, mp.driveItemId DESC
-- @sort by_favorited_asc  mf.favoritedDateTime ASC,  mp.driveItemId ASC
WITH localMin AS (
  SELECT DriveItemID, MIN(rowid) AS minRowId
  FROM localContent.ItemMetadata WHERE Tombstoned = 0
  GROUP BY DriveItemID
), tmMax AS (
  SELECT driveItemId, MAX(id) AS maxId
  FROM thumbnailCache.thumbnail_metadata
  GROUP BY driveItemId
), page AS (
  SELECT mp.driveItemId, mp.name, mp.takenDateTime,
    COALESCE(json_extract(mp.image,'$.width'), json_extract(mp.video,'$.width')) AS width,
    COALESCE(json_extract(mp.image,'$.height'), json_extract(mp.video,'$.height')) AS height,
    local.FileSystemID AS fileSystemId, local.LocalID AS localId, tm.id AS thumbnailId,
    COALESCE(length(t.thumbnail), 0) AS thumbnailSize,
    mp.driveId, CASE WHEN mp.video IS NOT NULL THEN 1 ELSE 0 END AS isVideo,
    json_extract(mp.video,'$.duration') AS duration,
    CASE WHEN mf.listItemUniqueId IS NOT NULL THEN 1 ELSE 0 END AS isFavorite
  FROM media_properties mp
  JOIN media_libraries drive ON drive.driveId = mp.driveId
  JOIN media_favorites mf ON mf.listItemUniqueId = mp.listItemUniqueId
    AND mf.listId = drive.listId AND mf.siteId = drive.siteId
  LEFT JOIN localMin ON localMin.DriveItemID = mp.driveItemId
  LEFT JOIN localContent.ItemMetadata local ON local.DriveItemID = localMin.DriveItemID
    AND local.rowid = localMin.minRowId
  LEFT JOIN tmMax ON tmMax.driveItemId = mp.driveItemId
  LEFT JOIN thumbnailCache.thumbnail_metadata tm ON tm.id = tmMax.maxId
  LEFT JOIN thumbnailCache.thumbnails t ON t.thumbnailMetadataRef = tm.id
  WHERE mp.takenDateTime IS NOT NULL AND mp.takenDateTime != ''
    AND mp.isTombstoned = 0
  ORDER BY {sort}
  LIMIT ?limit OFFSET ?offset
)
SELECT * FROM page