-- @query gallery_time_samples
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
-- @param start_date TEXT
-- @param end_date TEXT
-- @param candidate_limit INT
SELECT
    MAX(tm.id) AS thumbnailId,
    cloud.driveItemId,
    cloud.name,
    cloud.takenDateTime,
    SUBSTR(cloud.driveItemId, 1, INSTR(cloud.driveItemId, '!') - 1) AS driveId,
    COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width')) AS width,
    COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height')) AS height,
    json_extract(cloud.fileSystemInfo, '$.lastModifiedDateTime') AS lastModifiedDateTime
FROM media.media_properties AS cloud
JOIN media.media_libraries AS drive USING(driveId)
LEFT JOIN localContent.ItemMetadata AS local
    ON local.DriveItemID = cloud.driveItemId
LEFT JOIN thumbnailCache.thumbnail_metadata AS tm
    ON tm.driveItemId = cloud.driveItemId
    AND (tm.imageModTime IS NULL
         OR json_extract(cloud.fileSystemInfo, '$.lastModifiedDateTime') IS NULL
         OR tm.imageModTime >= CAST(strftime('%s', json_extract(cloud.fileSystemInfo, '$.lastModifiedDateTime')) AS INTEGER) * 1000)
WHERE cloud.photo IS NOT NULL
  AND cloud.isTombstoned = 0
  AND COALESCE(local.Tombstoned, 0) = 0
  AND drive.libraryType = 'Gallery'
  AND cloud.takenDateTime IS NOT NULL
  AND cloud.takenDateTime >= ?start_date
  AND cloud.takenDateTime < ?end_date
GROUP BY cloud.driveItemId
ORDER BY RANDOM()
LIMIT ?candidate_limit