-- @query gallery_timeline
-- @db localContent 32.0
-- @db mediacache 1.0
-- @sort date_desc year DESC, month DESC
-- @sort date_asc  year ASC, month ASC
-- @param taken_date_col IDEN
-- @sample taken_date_col literal:takenDateTimeNormalized
-- @with media_type_photo
-- @end-with
-- @with media_type_video
-- @end-with
-- @with tags
-- @param tag_names TEXT
-- @end-with
-- @with date_range
-- @param start_date TEXT
-- @param end_date TEXT
-- @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 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
-- @end-with
-- @with suppress_cloud
-- @end-with
-- @with suppress_local_only
-- @end-with
-- @with suppress_pending_upload
-- @end-with
-- @with suppress_icloud
-- @end-with
-- @with favorites
-- @param fav_sort INT
-- @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 account_scope
-- @db media 11.0
-- @param account_id 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
SELECT year, month, SUM(cnt) AS count FROM (
    -- @with account_scope
    SELECT
        SUBSTR(cloud.?taken_date_col, 1, 4) AS year,
        SUBSTR(cloud.?taken_date_col, 6, 2) AS month,
        COUNT(*) AS cnt
    FROM media.media_properties AS cloud
    WHERE cloud.isTombstoned = 0
      AND NOT EXISTS (
          SELECT 1 FROM localContent.ItemMetadata AS local
          WHERE local.DriveItemID = cloud.driveItemId
            AND local.Tombstoned = 1
      )
      AND cloud.?taken_date_col IS NOT NULL
      AND cloud.photo IS NOT NULL
      AND cloud.libraryTypeId = 1
      -- @with media_type_photo
      AND cloud.video IS NULL
      -- @end-with
      -- @with media_type_video
      AND cloud.video IS NOT NULL
      -- @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 date_range
      AND cloud.?taken_date_col >= ?start_date
      AND cloud.?taken_date_col < ?end_date
      -- @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 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 favorites
      AND EXISTS (
          SELECT 1 FROM media.media_favorites mf
          JOIN media.media_libraries lib ON mf.siteId = lib.siteId AND mf.listId = lib.listId
          WHERE mf.listItemUniqueId = cloud.listItemUniqueId
            AND lib.libraryType = 'Gallery'
      )
      -- @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 drive_id
      AND cloud.driveId = ?drive_id
      -- @end-with
    GROUP BY year, month
    UNION ALL
    -- @end-with
    SELECT
        SUBSTR(exif.TakenDateTimeCoalesced, 1, 4) AS year,
        SUBSTR(exif.TakenDateTimeCoalesced, 6, 2) AS month,
        COUNT(*) AS cnt
    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 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 album
      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 folder
      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_only
      AND 0
      -- @end-with
      -- @with suppress_icloud
      AND NOT EXISTS (
          SELECT 1 FROM localContent.WatchedRootFolders AS wrf_icloud
          WHERE wrf_icloud.WatchedRootFolderID = local.WatchedRootFolderID
            AND wrf_icloud.SyncRootType = 2
      )
      -- @end-with
    GROUP BY year, month
    -- @with account_scope
    UNION ALL
    SELECT
        SUBSTR(mcd.TakenDateTimeCoalesced, 1, 4) AS year,
        SUBSTR(mcd.TakenDateTimeCoalesced, 6, 2) AS month,
        COUNT(*) AS cnt
    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 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
      AND local.FavoritedDateTime IS NOT NULL
      -- @end-with
      -- @with album
      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 folder
      AND 0
      -- @end-with
      -- @with require_drive_item_id
      AND local.DriveItemID IS NOT NULL
      -- @end-with
      -- @with drive_id
      AND EXISTS (
          SELECT 1
          FROM localContent.FileOnDemandMetadata AS fond
          INNER JOIN media.media_libraries AS lib
              ON fond.ListID = lib.listId
             AND fond.SiteID = lib.siteId
          WHERE fond.FileSystemID = local.FileSystemID
            AND lib.driveId = ?drive_id
      )
      -- @end-with
      -- @with local_folder_scope
      AND local.ParentFSID = (
          SELECT FileSystemID FROM localContent.ItemMetadata WHERE LocalID = ?local_id
      )
      AND mcd.FileSystemID IN (
          SELECT ch.FileSystemID
          FROM localContent.ItemMetadata AS ch
          WHERE ch.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_pending_upload
      AND 0
      -- @end-with
    GROUP BY year, month
    -- @end-with
)
GROUP BY year, month
ORDER BY {sort}