-- @query photoglide_photos
-- @db media 11.0
-- @db thumbnailCache 9.0
-- @param max_photos INT
-- @with collection
-- @param collection_id TEXT
-- @end-with
SELECT
    mp.driveItemId,
    mp.name,
    mp.takenDateTime,
    mp.width,
    mp.height,
    tm.id AS thumbnailId,
    COALESCE(length(t.thumbnail), 0) AS thumbnailSize
FROM media.media_properties mp
LEFT JOIN thumbnailCache.thumbnail_metadata tm
    ON tm.driveItemId = mp.driveItemId
    AND (tm.imageModTime IS NULL
         OR json_extract(mp.fileSystemInfo, '$.lastModifiedDateTime') IS NULL
         OR tm.imageModTime >= CAST(strftime('%s', json_extract(mp.fileSystemInfo, '$.lastModifiedDateTime')) AS INTEGER) * 1000)
LEFT JOIN thumbnailCache.thumbnails t ON t.thumbnailMetadataRef = tm.id
WHERE mp.takenDateTime IS NOT NULL AND mp.takenDateTime != ''
  -- @with collection
  AND mp.driveItemId IN (
      SELECT driveItemId FROM collection_items WHERE collectionId = ?collection_id
  )
  -- @end-with
ORDER BY mp.takenDateTime DESC
LIMIT ?max_photos