-- @query thumbnails_list_default
-- @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
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,
    0 AS isFavorite
  FROM media_properties mp
  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