-- @query gallery_sorted_ids
-- @db localContent 32.0
-- @db mediacache 1.0
-- @param sort_column IDEN
-- @sample sort_column literal:takenDateTime
-- @sort date_desc sortDate DESC, id DESC
-- @sort date_asc  sortDate ASC, id ASC
-- @sort fav_desc  sortDate DESC, id DESC
-- @sort fav_asc   sortDate ASC, id ASC
-- @with date_range
-- @param start_date TEXT
-- @param end_date TEXT
-- @end-with
-- @with media_type_photo
-- @end-with
-- @with media_type_video
-- @end-with
-- @with album_id
-- @param album_id TEXT
-- @sample album_id sql:SELECT parentDriveItemId FROM media.media_properties WHERE parentDriveItemId IS NOT NULL AND isTombstoned = 0 GROUP BY parentDriveItemId ORDER BY COUNT(*) DESC, parentDriveItemId LIMIT 1
-- @end-with
-- @with folder
-- @param folder_ids TEXT
-- @end-with
-- @with local_folder_scope
-- @param local_id INT
-- @end-with
-- @with local_folders
-- @param local_folder_ids TEXT
-- @sample local_folder_ids sql:SELECT json_group_array(h) FROM (SELECT lower(hex(FileSystemID)) h FROM localContent.LocalFolders WHERE FileSystemID IS NOT NULL ORDER BY FileSystemID LIMIT 5)
-- @end-with
-- @with suppress_cloud
-- @end-with
-- @with suppress_local
-- @end-with
-- @with people_or
-- @param people_entity_ids TEXT
-- @end-with
-- @with people_and
-- @param people_entity_ids TEXT
-- @end-with
-- @with people_or_merged
-- @db media 22.0
-- @param people_entity_ids TEXT
-- @end-with
-- @with people_and_merged
-- @db media 22.0
-- @param people_entity_ids TEXT
-- @end-with
-- @with favorites
-- @end-with
-- @with favorites_dep
-- @end-with
-- @with dimension
-- @param dim_min_w INT
-- @param dim_max_w INT
-- @param dim_min_h INT
-- @param dim_max_h INT
-- @end-with
-- @with dim_landscape
-- @end-with
-- @with dim_portrait
-- @end-with
-- @with dim_square
-- @end-with
-- @with album
-- @param album_child_ids TEXT
-- @end-with
-- @with tags
-- @param tag_names TEXT
-- @end-with
-- @with account_scope
-- @db media 11.0
-- @param account_id TEXT
-- @end-with
-- @with gallery_cloud
-- @end-with
-- @with item_scope
-- @param item_ids  TEXT
-- @param local_ids TEXT
-- @end-with
-- @with require_drive_item_id
-- @end-with
-- @with drive_id
-- @param drive_id TEXT
-- @sample drive_id sql:SELECT driveId FROM media.media_properties WHERE photo IS NOT NULL AND isTombstoned = 0 AND libraryTypeId = 1 AND driveId IS NOT NULL GROUP BY driveId ORDER BY COUNT(*) DESC, driveId LIMIT 1
-- @end-with
-- @with gallery_library
-- @end-with
SELECT id, ?sort_column AS sortDate
FROM (
    -- @with gallery_cloud
    -- @with account_scope
    SELECT cloud.takenDateTime,
        cloud.driveItemId AS id
        -- @with favorites_dep
        , mf.favoritedDateTime
        -- @end-with
    FROM media.media_properties AS cloud
    -- @with favorites_dep
    INNER JOIN media.media_libraries AS drive USING(driveId)
    INNER JOIN media.media_favorites AS mf
        ON mf.listItemUniqueId = cloud.listItemUniqueId
       AND mf.siteId = drive.siteId AND mf.listId = drive.listId
    -- @end-with
    WHERE cloud.isTombstoned = 0
      AND (
          -- @with album_id
          1 OR
          -- @end-with
          NOT EXISTS (
              SELECT 1 FROM localContent.ItemMetadata AS local
              WHERE local.DriveItemID = cloud.driveItemId
                AND local.Tombstoned = 1
          )
      )
      AND cloud.photo IS NOT NULL
      -- @with gallery_library
      AND cloud.libraryTypeId = 1
      -- @end-with
      AND cloud.takenDateTime IS NOT NULL
      -- @with item_scope
      AND cloud.driveItemId IN (SELECT value FROM json_each(?item_ids))
      -- @end-with
      -- @with date_range
      AND cloud.takenDateTime >= ?start_date
      AND cloud.takenDateTime <= ?end_date
      -- @end-with
      -- @with media_type_video
      AND cloud.video IS NOT NULL
      -- @end-with
      -- @with media_type_photo
      AND cloud.video IS NULL
      -- @end-with
      -- @with album_id
      AND cloud.parentDriveItemId = ?album_id
      -- @end-with
      -- @with folder
      AND cloud.parentDriveItemId IN (SELECT value FROM json_each(?folder_ids))
      -- @end-with
      -- @with suppress_cloud
      AND 0
      -- @end-with
      -- @with local_folder_scope
      AND cloud.driveItemId IN (
          SELECT ims.DriveItemID
          FROM localContent.ItemMetadata AS ims
          WHERE ims.DriveItemID IS NOT NULL
            AND ims.Tombstoned = 0
            AND ims.ParentFSID = (SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id)
      )
      -- @end-with
      -- @with people_or
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId FROM recognized_entity_lookup rel
          WHERE rel.entityId IN (SELECT value FROM json_each(?people_entity_ids)))
      -- @end-with
      -- @with people_and
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId FROM recognized_entity_lookup rel
          WHERE rel.entityId IN (SELECT value FROM json_each(?people_entity_ids))
          GROUP BY rel.driveItemId
          HAVING COUNT(DISTINCT rel.entityId) = json_array_length(?people_entity_ids))
      -- @end-with
      -- @with people_or_merged
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId
          FROM recognized_entity_lookup rel
          JOIN media.recognized_entities re ON re.entityId = rel.entityId
          WHERE re.primaryEntityId IN (SELECT value FROM json_each(?people_entity_ids)))
      -- @end-with
      -- @with people_and_merged
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId
          FROM recognized_entity_lookup rel
          JOIN media.recognized_entities re ON re.entityId = rel.entityId
          WHERE re.primaryEntityId IN (SELECT value FROM json_each(?people_entity_ids))
          GROUP BY rel.driveItemId
          HAVING COUNT(DISTINCT re.primaryEntityId) = json_array_length(?people_entity_ids))
      -- @end-with
      -- @with dimension
      AND CAST(COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0) AS INTEGER) >= ?dim_min_w
      AND CAST(COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 999999) AS INTEGER) <= ?dim_max_w
      AND CAST(COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0) AS INTEGER) >= ?dim_min_h
      AND CAST(COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 999999) AS INTEGER) <= ?dim_max_h
      -- @end-with
      -- @with dim_landscape
      AND COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0) > COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0)
      -- @end-with
      -- @with dim_portrait
      AND COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0) > COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0)
      -- @end-with
      -- @with dim_square
      AND COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0) = COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0) AND COALESCE(json_extract(cloud.image, '$.width'), 0) > 0
      -- @end-with
      -- @with album
      AND cloud.driveItemId IN (SELECT value FROM json_each(?album_child_ids))
      -- @end-with
      -- @with tags
      AND cloud.driveItemId IN (
          SELECT mtl.driveItemId FROM media.media_tag_lookup mtl
          WHERE mtl.name IN (SELECT value FROM json_each(?tag_names))
          GROUP BY mtl.driveItemId
          HAVING COUNT(DISTINCT mtl.name) = json_array_length(?tag_names))
      -- @end-with
      -- @with drive_id
      AND cloud.driveId = ?drive_id
      -- @end-with
    UNION ALL
    -- @end-with
    -- @end-with
    -- @with favorites
    -- @with account_scope
    SELECT cloud.takenDateTime,
        cloud.driveItemId AS id,
        mf.favoritedDateTime
    FROM media.media_favorites AS mf
    CROSS JOIN media.media_properties AS cloud
    WHERE cloud.listItemUniqueId = mf.listItemUniqueId
      AND cloud.driveId = (
          SELECT drive.driveId
          FROM media.media_libraries AS drive
          WHERE drive.listId = mf.listId
            AND drive.siteId = mf.siteId
          LIMIT 1
      )
      AND cloud.isTombstoned = 0
      AND (
          -- @with album_id
          1 OR
          -- @end-with
          NOT EXISTS (
              SELECT 1 FROM localContent.ItemMetadata AS local
              WHERE local.DriveItemID = cloud.driveItemId
                AND local.Tombstoned = 1
          )
      )
      AND cloud.photo IS NOT NULL
      -- @with gallery_library
      AND cloud.libraryTypeId = 1
      -- @end-with
      AND cloud.takenDateTime IS NOT NULL
      -- @with item_scope
      AND cloud.driveItemId IN (SELECT value FROM json_each(?item_ids))
      -- @end-with
      -- @with date_range
      AND cloud.takenDateTime >= ?start_date
      AND cloud.takenDateTime <= ?end_date
      -- @end-with
      -- @with media_type_video
      AND cloud.video IS NOT NULL
      -- @end-with
      -- @with media_type_photo
      AND cloud.video IS NULL
      -- @end-with
      -- @with album_id
      AND cloud.parentDriveItemId = ?album_id
      -- @end-with
      -- @with folder
      AND cloud.parentDriveItemId IN (SELECT value FROM json_each(?folder_ids))
      -- @end-with
      -- @with suppress_cloud
      AND 0
      -- @end-with
      -- @with local_folder_scope
      AND cloud.driveItemId IN (
          SELECT ims.DriveItemID
          FROM localContent.ItemMetadata AS ims
          WHERE ims.DriveItemID IS NOT NULL
            AND ims.Tombstoned = 0
            AND ims.ParentFSID = (SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id)
      )
      -- @end-with
      -- @with people_or
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId FROM recognized_entity_lookup rel
          WHERE rel.entityId IN (SELECT value FROM json_each(?people_entity_ids)))
      -- @end-with
      -- @with people_and
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId FROM recognized_entity_lookup rel
          WHERE rel.entityId IN (SELECT value FROM json_each(?people_entity_ids))
          GROUP BY rel.driveItemId
          HAVING COUNT(DISTINCT rel.entityId) = json_array_length(?people_entity_ids))
      -- @end-with
      -- @with people_or_merged
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId
          FROM recognized_entity_lookup rel
          JOIN media.recognized_entities re ON re.entityId = rel.entityId
          WHERE re.primaryEntityId IN (SELECT value FROM json_each(?people_entity_ids)))
      -- @end-with
      -- @with people_and_merged
      AND cloud.driveItemId IN (
          SELECT rel.driveItemId
          FROM recognized_entity_lookup rel
          JOIN media.recognized_entities re ON re.entityId = rel.entityId
          WHERE re.primaryEntityId IN (SELECT value FROM json_each(?people_entity_ids))
          GROUP BY rel.driveItemId
          HAVING COUNT(DISTINCT re.primaryEntityId) = json_array_length(?people_entity_ids))
      -- @end-with
      -- @with dimension
      AND CAST(COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0) AS INTEGER) >= ?dim_min_w
      AND CAST(COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 999999) AS INTEGER) <= ?dim_max_w
      AND CAST(COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0) AS INTEGER) >= ?dim_min_h
      AND CAST(COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 999999) AS INTEGER) <= ?dim_max_h
      -- @end-with
      -- @with dim_landscape
      AND COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0) > COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0)
      -- @end-with
      -- @with dim_portrait
      AND COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0) > COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0)
      -- @end-with
      -- @with dim_square
      AND COALESCE(json_extract(cloud.image, '$.width'), json_extract(cloud.video, '$.width'), 0) = COALESCE(json_extract(cloud.image, '$.height'), json_extract(cloud.video, '$.height'), 0) AND COALESCE(json_extract(cloud.image, '$.width'), 0) > 0
      -- @end-with
      -- @with album
      AND cloud.driveItemId IN (SELECT value FROM json_each(?album_child_ids))
      -- @end-with
      -- @with tags
      AND cloud.driveItemId IN (
          SELECT mtl.driveItemId FROM media.media_tag_lookup mtl
          WHERE mtl.name IN (SELECT value FROM json_each(?tag_names))
          GROUP BY mtl.driveItemId
          HAVING COUNT(DISTINCT mtl.name) = json_array_length(?tag_names))
      -- @end-with
      -- @with drive_id
      AND cloud.driveId = ?drive_id
      -- @end-with
    UNION ALL
    -- @end-with
    -- @end-with
    SELECT exif.TakenDateTimeCoalesced AS takenDateTime,
        'lid:' || local.LocalID AS id
        -- @with favorites
        , local.FavoritedDateTime AS favoritedDateTime
        -- @end-with
        -- @with favorites_dep
        , local.FavoritedDateTime AS favoritedDateTime
        -- @end-with
    FROM localContent.ItemMetadata AS local
    INNER JOIN localContent.VisibleMediaMetadata AS exif ON local.FileSystemID = exif.FileSystemID
    WHERE local.Tombstoned = 0
      AND exif.RegistrantAccountId IS NULL
      AND exif.TakenDateTimeCoalesced IS NOT NULL
      -- @with item_scope
      AND local.LocalID IN (SELECT value FROM json_each(?local_ids))
      -- @end-with
      -- @with date_range
      AND exif.TakenDateTimeCoalesced >= ?start_date
      AND exif.TakenDateTimeCoalesced <= ?end_date
      -- @end-with
      -- @with media_type_photo
      AND (exif.MediaType IS NULL OR exif.MediaType = 1)
      -- @end-with
      -- @with media_type_video
      AND exif.MediaType = 2
      -- @end-with
      -- @with people_or
      AND 0
      -- @end-with
      -- @with people_and
      AND 0
      -- @end-with
      -- @with people_or_merged
      AND 0
      -- @end-with
      -- @with people_and_merged
      AND 0
      -- @end-with
      -- @with favorites
      AND local.FavoritedDateTime IS NOT NULL
      -- @end-with
      -- @with favorites_dep
      AND local.FavoritedDateTime IS NOT NULL
      -- @end-with
      -- @with album
      AND 0
      -- @end-with
      -- @with album_id
      AND 0
      -- @end-with
      -- @with dimension
      AND 0
      -- @end-with
      -- @with dim_landscape
      AND 0
      -- @end-with
      -- @with dim_portrait
      AND 0
      -- @end-with
      -- @with dim_square
      AND 0
      -- @end-with
      -- @with tags
      AND 0
      -- @end-with
      -- @with require_drive_item_id
      AND 0
      -- @end-with
      -- @with drive_id
      AND 0
      -- @end-with
      -- @with local_folder_scope
      AND local.ParentFSID = (
          SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id
      )
      -- @end-with
      -- @with local_folders
      AND local.ParentFSID IN (
          SELECT wrf.FileSystemID
          FROM localContent.WatchedRootFolders AS wrf
          WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          UNION
          SELECT lf.FileSystemID
          FROM localContent.LocalFolders AS lf
          WHERE lf.WatchedRootFolderID IN (
              SELECT wrf.WatchedRootFolderID
              FROM localContent.WatchedRootFolders AS wrf
              WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          )
          UNION
          SELECT child.FileSystemID
          FROM localContent.LocalFolders AS child
          INNER JOIN localContent.LocalFolders AS sel
              ON sel.WatchedRootFolderID = child.WatchedRootFolderID
             AND (child.RelativePath = sel.RelativePath
                  OR substr(child.RelativePath, 1, length(sel.RelativePath) + 1)
                     = sel.RelativePath || '/')
          WHERE lower(hex(sel.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
      )
      -- @end-with
      -- @with suppress_local
      AND 0
      -- @end-with
    -- @with account_scope
    UNION ALL
    -- @with gallery_cloud
    SELECT mcd.TakenDateTimeCoalesced AS takenDateTime,
        COALESCE(local.DriveItemID, 'lid:' || local.LocalID) AS id
        -- @with favorites_dep
        , NULL AS favoritedDateTime
        -- @end-with
    FROM mediacache.LocalItemsMissingCloudData AS mcd
    INNER JOIN localContent.VisibleMediaMetadata AS exif ON mcd.FileSystemID = exif.FileSystemID
    INNER JOIN localContent.ItemMetadata AS local ON mcd.FileSystemID = local.FileSystemID
    WHERE local.Tombstoned = 0
      AND mcd.RegistrantAccountId = ?account_id
      AND mcd.TakenDateTimeCoalesced IS NOT NULL
      -- @with item_scope
      AND (
          (local.DriveItemID IS NOT NULL AND local.DriveItemID IN (SELECT value FROM json_each(?item_ids)))
          OR local.LocalID IN (SELECT value FROM json_each(?local_ids))
      )
      -- @end-with
      -- @with date_range
      AND mcd.TakenDateTimeCoalesced >= ?start_date
      AND mcd.TakenDateTimeCoalesced <= ?end_date
      -- @end-with
      -- @with media_type_photo
      AND (exif.MediaType IS NULL OR exif.MediaType = 1)
      -- @end-with
      -- @with media_type_video
      AND exif.MediaType = 2
      -- @end-with
      -- @with people_or
      AND 0
      -- @end-with
      -- @with people_and
      AND 0
      -- @end-with
      -- @with people_or_merged
      AND 0
      -- @end-with
      -- @with people_and_merged
      AND 0
      -- @end-with
      -- @with album
      AND 0
      -- @end-with
      -- @with album_id
      AND 0
      -- @end-with
      -- @with dimension
      AND 0
      -- @end-with
      -- @with dim_landscape
      AND 0
      -- @end-with
      -- @with dim_portrait
      AND 0
      -- @end-with
      -- @with dim_square
      AND 0
      -- @end-with
      -- @with tags
      AND 0
      -- @end-with
      -- @with require_drive_item_id
      AND local.DriveItemID IS NOT NULL
      -- @end-with
      -- @with drive_id
      AND SUBSTR(local.DriveItemID, 1, LENGTH(?drive_id) + 1) = ?drive_id || '!'
      -- @end-with
      -- @with local_folder_scope
      AND local.ParentFSID = (
          SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id
      )
      -- @end-with
      -- @with local_folders
      AND local.ParentFSID IN (
          SELECT wrf.FileSystemID
          FROM localContent.WatchedRootFolders AS wrf
          WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          UNION
          SELECT lf.FileSystemID
          FROM localContent.LocalFolders AS lf
          WHERE lf.WatchedRootFolderID IN (
              SELECT wrf.WatchedRootFolderID
              FROM localContent.WatchedRootFolders AS wrf
              WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          )
          UNION
          SELECT child.FileSystemID
          FROM localContent.LocalFolders AS child
          INNER JOIN localContent.LocalFolders AS sel
              ON sel.WatchedRootFolderID = child.WatchedRootFolderID
             AND (child.RelativePath = sel.RelativePath
                  OR substr(child.RelativePath, 1, length(sel.RelativePath) + 1)
                     = sel.RelativePath || '/')
          WHERE lower(hex(sel.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
      )
      -- @end-with
      -- @with suppress_local
      AND 0
      -- @end-with
      -- @with favorites_dep
      AND 0
      -- @end-with
    -- @end-with
    -- @with favorites_dep
    UNION ALL
    -- @end-with
    -- @with favorites_dep
    SELECT mcd.TakenDateTimeCoalesced AS takenDateTime,
        COALESCE(local.DriveItemID, 'lid:' || local.LocalID) AS id
        -- @with favorites_dep
        , local.FavoritedDateTime AS favoritedDateTime
        -- @end-with
    FROM mediacache.LocalItemsMissingCloudData AS mcd
    INNER JOIN localContent.VisibleMediaMetadata AS exif ON mcd.FileSystemID = exif.FileSystemID
    INNER JOIN localContent.ItemMetadata AS local ON mcd.FileSystemID = local.FileSystemID
    WHERE local.Tombstoned = 0
      AND mcd.RegistrantAccountId = ?account_id
      AND mcd.TakenDateTimeCoalesced IS NOT NULL
      -- @with item_scope
      AND (
          (local.DriveItemID IS NOT NULL AND local.DriveItemID IN (SELECT value FROM json_each(?item_ids)))
          OR local.LocalID IN (SELECT value FROM json_each(?local_ids))
      )
      -- @end-with
      -- @with date_range
      AND mcd.TakenDateTimeCoalesced >= ?start_date
      AND mcd.TakenDateTimeCoalesced <= ?end_date
      -- @end-with
      -- @with media_type_photo
      AND (exif.MediaType IS NULL OR exif.MediaType = 1)
      -- @end-with
      -- @with media_type_video
      AND exif.MediaType = 2
      -- @end-with
      -- @with people_or
      AND 0
      -- @end-with
      -- @with people_and
      AND 0
      -- @end-with
      -- @with people_or_merged
      AND 0
      -- @end-with
      -- @with people_and_merged
      AND 0
      -- @end-with
      -- @with favorites_dep
      AND local.FavoritedDateTime IS NOT NULL
      -- @end-with
      -- @with album
      AND 0
      -- @end-with
      -- @with album_id
      AND 0
      -- @end-with
      -- @with dimension
      AND 0
      -- @end-with
      -- @with dim_landscape
      AND 0
      -- @end-with
      -- @with dim_portrait
      AND 0
      -- @end-with
      -- @with dim_square
      AND 0
      -- @end-with
      -- @with tags
      AND 0
      -- @end-with
      -- @with require_drive_item_id
      AND local.DriveItemID IS NOT NULL
      -- @end-with
      -- @with drive_id
      AND SUBSTR(local.DriveItemID, 1, LENGTH(?drive_id) + 1) = ?drive_id || '!'
      -- @end-with
      -- @with local_folder_scope
      AND local.ParentFSID = (
          SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id
      )
      -- @end-with
      -- @with local_folders
      AND local.ParentFSID IN (
          SELECT wrf.FileSystemID
          FROM localContent.WatchedRootFolders AS wrf
          WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          UNION
          SELECT lf.FileSystemID
          FROM localContent.LocalFolders AS lf
          WHERE lf.WatchedRootFolderID IN (
              SELECT wrf.WatchedRootFolderID
              FROM localContent.WatchedRootFolders AS wrf
              WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          )
          UNION
          SELECT child.FileSystemID
          FROM localContent.LocalFolders AS child
          INNER JOIN localContent.LocalFolders AS sel
              ON sel.WatchedRootFolderID = child.WatchedRootFolderID
             AND (child.RelativePath = sel.RelativePath
                  OR substr(child.RelativePath, 1, length(sel.RelativePath) + 1)
                     = sel.RelativePath || '/')
          WHERE lower(hex(sel.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
      )
      -- @end-with
      -- @with suppress_local
      AND 0
      -- @end-with
    -- @end-with
    -- @with favorites
    SELECT mcd.TakenDateTimeCoalesced AS takenDateTime,
        COALESCE(local.DriveItemID, 'lid:' || local.LocalID) AS id,
        mf.favoritedDateTime AS favoritedDateTime
    FROM media.media_favorites AS mf
    CROSS JOIN localContent.FileOnDemandMetadata AS fond
    CROSS JOIN localContent.ItemMetadata AS local
    CROSS JOIN mediacache.LocalItemsMissingCloudData AS mcd
    CROSS JOIN localContent.VisibleMediaMetadata AS exif
    WHERE fond.ListItemUniqueID = CAST(mf.listItemUniqueId AS TEXT)
      AND fond.ListID = CAST(mf.listId AS TEXT)
      AND fond.SiteID = CAST(mf.siteId AS TEXT)
      AND local.FileSystemID = fond.FileSystemID
      AND mcd.FileSystemID = local.FileSystemID
      AND exif.FileSystemID = mcd.FileSystemID
      AND local.Tombstoned = 0
      AND mcd.RegistrantAccountId = ?account_id
      AND mcd.TakenDateTimeCoalesced IS NOT NULL
      -- @with item_scope
      AND (
          (local.DriveItemID IS NOT NULL AND local.DriveItemID IN (SELECT value FROM json_each(?item_ids)))
          OR local.LocalID IN (SELECT value FROM json_each(?local_ids))
      )
      -- @end-with
      -- @with date_range
      AND mcd.TakenDateTimeCoalesced >= ?start_date
      AND mcd.TakenDateTimeCoalesced <= ?end_date
      -- @end-with
      -- @with media_type_photo
      AND (exif.MediaType IS NULL OR exif.MediaType = 1)
      -- @end-with
      -- @with media_type_video
      AND exif.MediaType = 2
      -- @end-with
      -- @with people_or
      AND 0
      -- @end-with
      -- @with people_and
      AND 0
      -- @end-with
      -- @with people_or_merged
      AND 0
      -- @end-with
      -- @with people_and_merged
      AND 0
      -- @end-with
      -- @with album
      AND 0
      -- @end-with
      -- @with album_id
      AND 0
      -- @end-with
      -- @with dimension
      AND 0
      -- @end-with
      -- @with dim_landscape
      AND 0
      -- @end-with
      -- @with dim_portrait
      AND 0
      -- @end-with
      -- @with dim_square
      AND 0
      -- @end-with
      -- @with tags
      AND 0
      -- @end-with
      -- @with require_drive_item_id
      AND local.DriveItemID IS NOT NULL
      -- @end-with
      -- @with drive_id
      AND SUBSTR(local.DriveItemID, 1, LENGTH(?drive_id) + 1) = ?drive_id || '!'
      -- @end-with
      -- @with local_folder_scope
      AND local.ParentFSID = (
          SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id
      )
      -- @end-with
      -- @with local_folders
      AND local.ParentFSID IN (
          SELECT wrf.FileSystemID
          FROM localContent.WatchedRootFolders AS wrf
          WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          UNION
          SELECT lf.FileSystemID
          FROM localContent.LocalFolders AS lf
          WHERE lf.WatchedRootFolderID IN (
              SELECT wrf.WatchedRootFolderID
              FROM localContent.WatchedRootFolders AS wrf
              WHERE lower(hex(wrf.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
          )
          UNION
          SELECT child.FileSystemID
          FROM localContent.LocalFolders AS child
          INNER JOIN localContent.LocalFolders AS sel
              ON sel.WatchedRootFolderID = child.WatchedRootFolderID
             AND (child.RelativePath = sel.RelativePath
                  OR substr(child.RelativePath, 1, length(sel.RelativePath) + 1)
                     = sel.RelativePath || '/')
          WHERE lower(hex(sel.FileSystemID)) IN (SELECT value FROM json_each(?local_folder_ids))
      )
      -- @end-with
      -- @with suppress_local
      AND 0
      -- @end-with
    -- @end-with
    -- @end-with
)
ORDER BY {sort}