-- @query gallery_time_months
-- @db localContent 32.0
-- @param year_start TEXT
-- @param year_end TEXT
-- @with account_scope
-- @db media 11.0
-- @param account_id TEXT
-- @end-with
SELECT month, SUM(cnt) AS count FROM (
    -- @with account_scope
    SELECT
        SUBSTR(cloud.takenDateTime, 6, 2) AS month,
        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 >= ?year_start
      AND cloud.takenDateTime < ?year_end
    GROUP BY month
    UNION ALL
    -- @end-with
    SELECT
        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 >= ?year_start
      AND exif.TakenDateTimeCoalesced < ?year_end
    GROUP BY month
    -- @with account_scope
    UNION ALL
    SELECT
        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
    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 >= ?year_start
      AND exif.TakenDateTimeCoalesced < ?year_end
    GROUP BY month
    -- @end-with
)
GROUP BY month
ORDER BY month ASC