-- @query date_summary
-- @db media 19.0
-- @db localContent 32.0
-- @sort asc  year ASC, month ASC
-- @sort desc year DESC, month DESC
-- @with date_filter
-- @param startDate TEXT
-- @param endDate TEXT
-- @end-with
SELECT year, month, SUM(cnt) AS count FROM (
  SELECT SUBSTR(cloud.takenDateTime, 1, 4) AS year,
         SUBSTR(cloud.takenDateTime, 6, 2) AS month,
         COUNT(*) AS cnt
  FROM media_properties AS cloud
  WHERE cloud.libraryTypeId = 1
    AND cloud.isTombstoned = 0 AND cloud.photo IS NOT NULL
    AND cloud.takenDateTime IS NOT NULL
    -- @with date_filter
    AND cloud.takenDateTime >= ?startDate
    AND cloud.takenDateTime <  ?endDate
    -- @end-with
  GROUP BY year, month
  UNION ALL
  SELECT SUBSTR(exif.TakenDateTimeCoalesced, 1, 4) AS year,
         SUBSTR(exif.TakenDateTimeCoalesced, 6, 2) AS month,
         COUNT(*) AS cnt
  FROM localContent.VisibleMediaMetadata AS exif
  INNER JOIN localContent.ItemMetadata AS local ON local.FileSystemID = exif.FileSystemID
  WHERE local.Tombstoned = 0
    AND exif.RegistrantAccountId IS NULL
    AND exif.TakenDateTimeCoalesced IS NOT NULL
    -- @with date_filter
    AND exif.TakenDateTimeCoalesced >= ?startDate
    AND exif.TakenDateTimeCoalesced <  ?endDate
    -- @end-with
  GROUP BY year, month
) GROUP BY year, month
ORDER BY {sort}