-- @query folder_tree_local
-- @db localContent 32.0
-- @with account_scope
-- @db media 11.0
-- @param account_id TEXT
-- @end-with
WITH loc_counts AS (
    SELECT pfsid, COUNT(*) AS photoCount
    FROM (
        SELECT im.ParentFSID AS pfsid
        FROM localContent.ItemMetadata AS im
        INNER JOIN localContent.VisibleMediaMetadata AS mm
            ON im.FileSystemID = mm.FileSystemID AND mm.MediaType = 1
        WHERE im.Tombstoned = 0
          AND mm.RegistrantAccountId IS NULL
        -- @with account_scope
        UNION ALL
        SELECT im.ParentFSID AS pfsid
        FROM localContent.ItemMetadata AS im
        INNER JOIN localContent.VisibleMediaMetadata AS mm
            ON im.FileSystemID = mm.FileSystemID AND mm.MediaType = 1
        LEFT JOIN media.media_properties AS cloud ON im.DriveItemID = cloud.driveItemId
        WHERE im.Tombstoned = 0
          AND mm.RegistrantAccountId = ?account_id
          AND (im.DriveItemID IS NULL OR cloud.driveItemId IS NULL)
        -- @end-with
    )
    GROUP BY pfsid
)
SELECT
    wrf.FileSystemID  AS fsid,
    wrf.FolderName    AS path,
    COALESCE(lc.photoCount, 0) AS photoCount
FROM localContent.WatchedRootFolders AS wrf
LEFT JOIN loc_counts AS lc ON lc.pfsid = wrf.FileSystemID
WHERE wrf.SyncRootType = 0
  AND wrf.OwnerCID IS NULL
UNION ALL
SELECT
    lf.FileSystemID                            AS fsid,
    wrf.FolderName || '/' || lf.RelativePath   AS path,
    COALESCE(lc.photoCount, 0)                 AS photoCount
FROM localContent.LocalFolders AS lf
INNER JOIN localContent.WatchedRootFolders AS wrf
    ON lf.WatchedRootFolderID = wrf.WatchedRootFolderID
LEFT JOIN loc_counts AS lc ON lc.pfsid = lf.FileSystemID
WHERE wrf.SyncRootType = 0
  AND wrf.OwnerCID IS NULL
ORDER BY path