-- @query gallery_time_samples
-- Random photo samples for time view card mosaics. Returns random photo candidates
-- within a date range with thumbnail IDs, filtering stale thumbnails by imageModTime.
-- Used by: time.js (SQL_SAMPLES)
-- @db media 11.0
-- @db localContent 32.0
-- @db thumbnailCache 9.0
--
-- @param start_date TEXT          -- ISO-8601 lower bound (inclusive)
-- @param end_date TEXT            -- ISO-8601 upper bound (exclusive)
-- @param candidate_limit INT     -- max candidates to return (default 6)

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