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