-- @query gallery_time_years
-- @db localContent 32.0
-- @with account_scope
-- @db media 11.0
-- @param account_id TEXT
-- @end-with
SELECT year, SUM(cnt) AS count FROM (
    -- @with account_scope
    SELECT
        SUBSTR(cloud.takenDateTime, 1, 4) AS year,
        COUNT(*) AS cnt
    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
    WHERE cloud.photo IS NOT NULL
      AND cloud.isTombstoned = 0
      AND COALESCE(local.Tombstoned, 0) = 0
      AND cloud.video IS NULL
      AND drive.libraryType = 'Gallery'
      AND cloud.takenDateTime IS NOT NULL
    GROUP BY year
    UNION ALL
    -- @end-with
    SELECT
        SUBSTR(exif.TakenDateTimeCoalesced, 1, 4) AS year,
        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
    GROUP BY year
    -- @with account_scope
    UNION ALL
    SELECT
        SUBSTR(exif.TakenDateTimeCoalesced, 1, 4) AS year,
        COUNT(*) AS cnt
    FROM localContent.ItemMetadata AS local
    INNER JOIN localContent.VisibleMediaMetadata AS exif
        ON local.FileSystemID = exif.FileSystemID
    LEFT JOIN media.media_properties AS cloud ON local.DriveItemID = cloud.driveItemId
    WHERE local.Tombstoned = 0
      AND exif.RegistrantAccountId = ?account_id
      AND (local.DriveItemID IS NULL OR cloud.driveItemId IS NULL)
      AND exif.TakenDateTimeCoalesced IS NOT NULL
    GROUP BY year
    -- @end-with
)
GROUP BY year
ORDER BY year DESC