-- @query folder_tree_cloud_gallery
-- @db media 11.0
-- @db localContent 32.0
SELECT
    mf.driveItemId AS id,
    mf.path,
    mf.driveId,
    COALESCE(cnts.photoCount, 0) AS photoCount
FROM media.media_folders AS mf
INNER JOIN media.media_libraries AS lib
    ON mf.driveId = lib.driveId
LEFT JOIN (
    SELECT mp2.parentDriveItemId AS pdid, COUNT(*) AS photoCount
    FROM media.media_properties AS mp2
    WHERE mp2.isTombstoned = 0
      AND mp2.takenDateTime IS NOT NULL
      AND NOT EXISTS (
          SELECT 1 FROM localContent.ItemMetadata AS local
          WHERE local.DriveItemID = mp2.driveItemId
            AND local.Tombstoned = 1
      )
    GROUP BY mp2.parentDriveItemId
) AS cnts ON cnts.pdid = mf.driveItemId
WHERE lib.libraryType = 'Gallery'
  AND mf.isMediaAlbum = 0
  AND mf.isTombstoned = 0
ORDER BY mf.driveId, mf.path